Welcome to this complete course on pivot tables from scratch and step by step I am Miguel Vela from your channel El Tio Tech and with this video we want you to have no doubts and have an absolute command of pivot tables well before starting With this, I recommend that you go to the description of this same video, there you will find a link so that you can download this material, this spreadsheet, and guide yourself as we progress with the course, so you can also practice looking at this same tutorial well let 's start now yes and the first question we ask ourselves is that it is a pivot table not a pivot table it is a summary of statistical data that is obtained from a database or a table of greater practically group you the data the dynamic tables can summarize the information either with sums you can also count you can get the average maximum minimum or another type of calculation that will be obtained in a way Automatic tables are called pivot tables because they do not have a fixed structure but we can organize them in one way or another until we find the useful information. In a few words, we can say that a pivot table is a flexible report, that is, a report where we can easily change the columns . and the rows that we want to visualize on the screen so that you understand me that it is a dynamic table I have an example here look here I have an example that I am going to show here the columns look hide them so they are not distracted and I am going to show them for that they understand me that it is a pivot table here I have a little table eye this is not a pivot table but here are my data we can also call it as the database here we have the date look here we have the date it is in months January February March So on, here we have the products and sales by product and by month that were made in the year 2021, do not look at the products that I have here, it is repeated and s products the only products sorry are calculators pens wallet holster and backpack ok these products which are actually five are repeated here ok here they are repeated in different months and depending on the month for example in calculator it was sold in the month of January 13 thousand soles no if we continue looking for the calculator it will also be repeated look at the marked area it is a cartridge belt that I have here it is yellow no in January it sold 10 thousand 600 in April 14 thousand 150 in June in September and so on the dynamic table well in all this table that I have here he summarizes it for me he summarizes it for me in this tablet because there is so that you have an idea here I have my only 5 products wallet calculator pencil case pens and backpack ok look this is the summary of this entire table the dynamic table is the smallest one we have here how do we find out if it is a pivot table or not if you click on any part of this table look I want you to observe that here you are going to enable two options the first option in the tab above mini pivot table analysis and design ok so we already know that we are talking about a pivot table the opposite thing look how it will disappear if I click here on our I am going to click on the database and I want you to observe that nothing appears anymore, not instead if I click here on the pivot table these two options appear that are indicating to me that if it is a pivot table what it is doing This dynamic table is a sum. For example, look at the cartridge belt product since it is repeated many times and we have it here. Look, we have January cartridge belt 10,600, April cartridge belt 14,150, June 13,000, September 25,000 and so on. Not if I put together all the cartridge belt products, look at the I am pointing and the dynamic table what it is going to do to me here we have another one what it is going to do to me is a sum automatically of course you can also put a function to this and to add you up it won't take forever what the pivot table makes it easier for you is that it does it automatically you don't have to put any function it is grouping you all by pencil case it could also group you by month ok for example January and know how much has been sold in January 1 February and so on but that dynamic table is organized by products and the total sum that was made in the year 2021 this is a sum it is a very simple example to start after that look here it is adding you and here's the reunion' look at us instead of adding in replace adding we can count and know how many products have been sold for example of wallet three of calculator 3 cartridge 6 have been sold not in the year 2021 that is not only it can add but it can also get an account after that you can also suddenly get the ones that have sold the most to the products that have sold the least you can also get the average and many more calculations that we are going to let's see little by little well let's go now like this with the first exercise look I always use very small tables so that you don't get confused seeing so many columns no but we are going to start from the simplest and then we are going to see examples you could say with more data so that you know what to do right only the three columns in reality it is the same little table as we created our first pivot table look we are going to select the entire table and we are also going to select the entire little table we are going to go here to the insert tab click on insert and here it says clearly pivot table ok to the right it also says recommended pivot table look we are going to click here first recommended pivot tables one click here will skip you look this time and here it gives you some suggestions look you can organize in such a way by months or also by products you can easily take any of these ok they are recommendations but as we are just starting we are going to create it from scratch so and we are going to click where it says dynamic table or a click here will pop up this little window that we are observing look at the range and it is already selected here we have it ourselves we select them and if we go down look here where it says choose where you want to place the Pivot table report you can create it in a new sheet or you can also create it here on the side as we are starting I think we are going to create the gay side here so we select the second option that says existing spreadsheet in location we place ourselves where we want not for example here a click here and we give it OK I want you to now see that I get this white box and it tells me to generate a report choose the fields from the list of fields of the dynamic table on the right look on the right they are all the elements all the fields with which we need to work our dynamic table come up here look up here all the headings appear date products and see nts of the year 2021 look at the dates here we have the first heading date we already have it there the second heading products here it is products and the third heading sales 2021 well if there were more columns if there were more columns then they would also appear here we are only working with three columns yes we go down a little more we will always always observe these four boxes to four fields where we are going to put our columns ok the first field we have are the filters the filters could go dates names products etcetera we also have the columns the rows and the values and the values look at the values always always and always the numbers will go in this case which field or which column have numbers the sales don't for that reason I tell you don't forget in values the numbers always go so that you understand me a little more I have here look at the structure ok the structure if I look here I have the columns up here if I for example look for example here it takes columns to products look what is going to happen I have to drag this field I dragged it and it took it to columns and look now my products are in columns here look look calculator pencil case backpacks all these products look look at all these products that I have here now I have them look now I have them in columns that is here then the structure look if I take products to columns I will have it in such a way not here look in the part that says column the same thing happens if I take these products he selected them and dragged it to rows look that now it will appear in rows as we are observing now I have rows if I go here the structure mier in the rows are where on the left not in such a way that we are observing if the rows rows no now look at the values they will always be here between the columns and the rows if I drag if I drag to the sales do not forget the numbers they will always go in values I will sel eect the sales and I am going to drag them to values now what I have look now what I have is in rows look in rows I have the products here are the rows and in values I have the sales as we are observing now if you ask me what if I took products to columns there is no problem that's why they are dynamic tables it's up to you how you want to organize them to products I'm going to take them to columns with a determined click and now look at my products in columns and here also my sales look here my sales are also in a single row no and in columns this is how this works ok this is how this works if we go to campus filters look I like it better than I do I see it better organized and products I put it in a row ok I put it in a row look and you are going to see each other in such a way that it depends on you here dates I am going to take you to the filter look at the date I am going to take you to the filter and I am going to have you up here a thousand dates dates here if I select I click to see look home I am going to click on the dates and I can only show January look I am going to select January I accept and I have the sum I have the sum of the sales only for the month of January of how many products 1234 because there are not the five products have not been sold the five products in the month of January if I click here again and I give him in February he accepted and look that now I only have in February it has been sold calculate 14 thousand soles well if you ask me what happens if I look if I put dates I put dates in a row and products I put in filters look what is happening now I have sales for the months of January 47 thousand 100 were sold in February 14 thousand were sold in March 337 thousand 500 were sold and now look at the filters and I already have it up here but now it is by product I want to see, for example, wallet when it has been sold throughout the year and in what months not, for example, wallet, I give it to accept and look that in three months only in three months I have eleven thousand 500 in March 14 thousand and in august 18 thousand no or one click here I give it in pens and I accept and it's the same look at the product pens in January 12 thousand were sold and in March 11 thousand soles were sold not to confuse us not to confuse us we are going to do This is easier because we are starting from scratch and step by step, no dates. I am going to take it out. Look, I dragged it out and it has already gone to products. I am going to bring it to rows and I am only going to work with rows and here the sales are the sum of the sales. no what we are going to do now is learn some configuration options of our dynamic tables look how we can observe this dynamic table is quite simple not in rows we have the products and in another column also in rows we have the sum of our sales well yes For example, we want to put the type of currency or put a decimal on these numbers, etc., we have to do the following, look here in values, because the configurations are mostly in values, we are going to click on it , look, we are going to give click on it and here we are going to go where it says value field configuration and we are going to click here value field configuration it will pop up this little window that we are observing well the same look in the same way you can also click a click on any number gives you a right click because that's what they look for the same value field configuration option or a click here will pop up the same little window but look we did it with a right click here in our dynamic table not in the personalized name you can change it I am going to put it here sales only sales 2021 so that it is not too long look I accept and look to see to see the name they determine dynamics and there is if you have to give it a different name we are going to put sales sum for example I do not accept they must be accepted and it has already changed me well and as we are observing here only the title well again we are going to go to configuration a click on any number right click and we give it in configuration of the value field or a click here after that look here we have to summarize values by let's go in order ok what this dynamic table is doing to us is the sum that is adding to us for example wallet to all the sales that say here wallet not here we have one here we have another one and the total amount of bills is 43 thousand 500 all of a sudden I don't want him to add me, that is , add me the whole amount of sales I don't want him to tell me look here he is if the next one tell me how many sales I have done well in this case in the year 2021 2 if I accept it look how it changes in numbers ok look wallet sold three times this year calculator three times cartridge belt six times pencil twice and backpack in this case four times not for this case does not apply does not apply much because we have for months ok suddenly your sales are daily and you can know there in a more precise way how many sales have been made in a month or suddenly a year does not work like that let's change it with a click of a button right, again, configuration of the value field and we also have the average, the average is basically of all the sales , for example, of the wallet, of all the sales of the wallet, it takes the average, come on, look, let's click accept, well, look, here we have 14 thousand fourteen thousand 500 that means that it has added up look here we have a wallet plus this wallet and over here another one and there are only three the sum of those three between your number no, that is between 3 and the average we have it here see right click again value field configuration we can also get the maximum sales, that is, from the three times that it is repeated, wallet will show us the highest value in this case, look at wallet 18,000 s 18,000 we will observe it, we accept and see that the maximum sale and we have it 18 thousand in click right value field configuration and we have over here also the same thing not the minimum product count number the after the after paste and so on to the right hand look I'm going to leave it in sum because it is the classic thing mostly pivot tables almost always work with sum I am going to leave you I am going to click accept look until the sum again right click value field configuration and here we have another option another little tab tells me to show values as look here we have values such as I am going to click and you can show the percentage, that is, wallet, what percentage represents of the total sum, for example, look at a click here, look at the percentage of the general total, we click here, we accept, and look, here we will always have the 100% ok in the grand total it will be 100% wallet represents 16 points 32 percent of all sales for the year 2021 not of each product calculator also represents 15 percent thirty-five pens eight backpack door 23.52 ok right click value field configuration and here again show values as look here we also have the percentage of the total per column when there are more columns then look is 100% will vary ok or it will work based on the column at 100% and in each column we will see look at the base at 100% according to the columns or it may also be the case that you want to work with rows ok that is 100% it will be now if you work with rows to the right ok to the right we will see it later we will see it later right click value field configuration come here look at show values as I am going to leave it without calculation ok without calculation as it was and I am going to leave it now we are going to see this option too which is very interesting which is the number format we are going to click here on number format and to don't look for a moment I'm going to accept to go to put to put forgiveness once and for all the currency format we do the following the same thing right click value field configuration and we go to number format here or click in number format this little window that we are observing will pop up and after that come here are the different formats well we have a number if you want to put decimals and not currency there is no problem here you can upload it look at 34 decimals where it says sample here it shows you as it will be seen in your pivot table ok Let's look, I'm going to leave you only in two decimal places and in currency, I think that is what interests us the most, the symbol, I am going to look for the sun, look here, even you look for the currency of your country, no, I am going to look for the sun and here I made a sample and look here I have the sample ok you can work with accounting look if they work with date also get date hour percentage scientific expression etcetera but for this case currency decimal position two can upload it don't forget 34 many I am going to leave it in two and we simply click accept and then accept and look now that I already have my currency format here not everything has returned to the Peruvian currency which is the sun let's see we are going to complicate that dynamic table a little more We are going to complicate that dynamic tablet a little more assuming look that I want to add the date field ok that is the months because because look the date is in months January February March April May etc. I am not going to bring you the date look I am going to bring you the date to columns or a salary click dragged him I want you to see I want you to see now look I want you to see now that in the field in the columns and I already have the months here are the months look January February March April ok now I have it in columns and the products are still in rows we do not have it there but if I see in the best of cases that the months are many and the columns actually get too big then what we could do is look at the power of the columns to the dates as rows sorry I'm going to put the dates as rows and the products as columns, that is, I'm going to put all these rows as columns , for that they are dynamic tables, they adapt very quickly, they are flexible both in columns and in rows, not so ces to columns I am going to bring here rows sorry to dates to rows and products I am going to bring to columns I want you to observe I want you to observe that now look and well now what I have that I have here the months of January to December of the year 2021 and I have here look at my data ok according to the month and according to the product and we are going to center it a little look I am going to center all this as if it were a text start in the middle and in the middle let's see let's see let's see the middle yes there we have it I think I think it's fine there or to the left it could also be look to the left better to the left there I have it well it separates us a little what I want you to observe here is look what I want you to observe here is that for example February look in February it does not have it does not have wallet data it is quite obvious because no wallet sales were made in the month of February but supposing that you do not want to have these gaps you do not want to have those gaps that we are observing no suddenly you want I do not know how to put give it a text or put the number zero or any symbol then for this we click on any part of the numbers of the dynamic table we right click and we give it here look here up to dynamic table options and we no longer give it in configuration of value field that is the option that we were using a moment ago we give it in dynamic table options a click here will pop up this little window that we are observing right here in format design we are going to go down a little and where it says to do the empty show what you want to show here suddenly you want to show a text or suddenly you want to show a number or don't look at it for example I can show the zero I put the zero I accept and look that all the data that doesn't have values now is here with the number zero no, but I kind of got a little dizzy I see too many zeros so I right-click it I give it pivot table options again and here I can put a dash quoted that seems more prudent to me nte not two hyphens one two thousand only to show accepted and look that now it seems to me more orderly no there are not so many zeros that mark us here we have it right in all the hyphens milestones are because the data does not exist well look another other se I could say another of the things with which they can collide that in excel it is the following look I am going to put products under the date ok so that it shows me everything in a single one in a single column in different rows look at the product I am going to bring you here below the date I bring it below I release it and look that now it shows me all the products look but according to the month month of January look this is another structure of the dynamic table not that it can also help you to see your data but notice that in In the month of February, the other data is not observed. For example, look, in January I have 12,344 products, it seems to me that there are 5 and that is gone, it seems to me that there are 5, but I only have 4, and in February I only have one product, but if I wanted all pr Products are displayed by months if they don't have sales so they don't have sales just so I don't make a mistake we can do the following look we're going to click here on the products a right click and we're going to look for the field configuration option and no longer table options dynamic but field configuration or a click here will jump them look at the next little window and we excuse us we will click where it says design and printing a click here in this design and printing tab we will click here and after that we will search look at this option that says show elements without data we activate this option show elements without data we activate it we accept and look that now per month per month it is already throwing me one two three four five this product was not backpack door look it comes out empty ok wallet of vacuum universal and all the products by months appear but obviously they appear with the two milestone scripts that we ourselves put, not what we are going to do now it is for us to change the design of our pivot table a little well here we have our little table we click anywhere in the table and we are going to go to the top here where we enable those two healthy dynamic table analyzes and here the design we are going to click on design and on the left hand side I want you to observe that here it says subtotals and totals the subtotals would come to be, for example, all the sales for January, not here is this 47,100 would come to be the subtotals because only they belong to January in the same way we have February we have March they are all subtotals April also subtotals and the end and at the end we have here the general total, that is, it is the sum of all the months that our pivot table has just calculated automatically so yes we go here to the design tab on the left hand side we have the subtotals and we click on it look we are going to give it a click not to show subtotals and look how it has disappeared All the subtotals of each month appeared, ok, we are going to go down to the last one, but look here we have the general total, it has not disappeared, but I want you to observe if we click on subtotals again, here we have to show all the subtotals to show them again, but in the lower part, I mean below , look, I'm going to click and it appears, look here, the subtotal appears as the total for November and it puts it here last, no, but if it bothers you, you want it to be as it was a moment ago, subtotals , you can show all the subtotals at the top here short from the top one click and look it reappeared as the first format not in the upper part we have the sub totals and at the end the total again we go to the design tab and here now we have look at the general totals if we give it a click on grand totals look it tells me deactivated for rows and columns in this case look at the grand totals we only have it in one row and one column not here at the last imo well when we have when we work with rows and several columns there if this is going to help us but look we are going to deactivate it anyway we are going to see change we click on deactivate and now only the total has disappeared well only the total has just disappeared let's go to put it back because if I am interested in having the general total and we activate it for rows and columns ok one click here and again we have it there then look here then here to the side we have report design look at this is also something nice because you can change the appearance of your pivot table or the structure or the order look report design one click here the compact report is the one we are always going to see one click there is no change, it is the one we are seeing report design show in outline format look one click here and it changed me, as if it enlarges you a little and gives you more space so that you can say to analyze it in a better way, not if that is how you see it, if that design it's convenient we're going to click on report design and we're going to show it in tabular format or click here and look also just changed my appearance a little well I'm going to put the compact design which is the first one you want to show in compact which is the first one that we were seeing there is no other type of design look at another type of design that I also find very interesting if you right click on it look if you right click on it you go to dynamic table options one click here and let's see let's see we're going to see where is show show if here in the show tab we have this option that says classic pivot table design then you go look you only have to activate two buttons the first one that brings up classic pivot table design that is in the tab show and here the format design well in combining and centering cells with labels we click accept and look that it has just shown another type we could say order not now if we have the dates in the center of the products next to it to the right and here the totals we do not have it right here and that is another design if you want to return to normality you go to the report design again and show it in compact format one click here and again we have look at the same design that is wait a minute we knew we hadn't selected it right come on come on come on we continue here in design one click on design to see what we missed blank rows here look if I click here and insert rows after each element one click I want that observe that a row is inserted here look at this row this row is inserted ok if you see those blue lines it is because we have not deactivated it we are going to deactivate the right click look what we activated a moment ago what dynamic table options well let's see in show we had activated the classic design we remove it design and remove the combine, show classic dynamic table design we remove it and in design and format we remove combine and center we accept so that they do not come out and those lines that can suddenly annoy us don't look here is the blank row here we activate it here look at blank row insert blank line if we remove remove and blank look it returns to normal again blank row insert blank line one click for me it seems better to always have a space to separate each of each date in this case each month not here look to the right pivot table style options row header look you can turn it off and the end header line disappears row look the line disappears if here column headers there is a click disappears here what is on top look what if they use it they can paint it rows with bands look a row of a different color appears we deactivate it and columns with bands that we have here look at a column of one color and another column of your color not here on the right look you can also choose a style the style you like for example this let's see what else it could be no I know let's see we are going to put a style that is not very this for example it seems to me very classic I like light blue and we already have it there no let's see what else we could see here I think everything that the little design tab shows us is already there let's click now yes on pivot table analysis or click here pivot table analysis and what we are going to do now look is expand and contract ok this option we see you here look here here active field where it says here we have a plus and here we have a minus, the minus is to contract and the plus is to expand, if I click here on contract, look, a click automatically only shows me the month and its total that has been sold in that month, here is the option, look if you want to go back to expand one more click, do not forget the pivot table analysis tab and again in the more and expand again this option is mostly useful when you have more data, you could say hierarchically, for example here not here in the same row you would suddenly have another o campo que despliegue la fecha y fecha despliega a producto no y de esta manera puedes contraer y expandir, miren estos símbolos o este rectángulo que que siempre me trae el más y el menos también lo puedes utilizar manualmente no hay ningún problema pero lo puedes utilizar manualmente pero puede puede que tú no lo quieras ver en caso de que le contraigas no tú no quieres ver este más por ejemplo no quieres que las otras personas lo observen entonces le das clic derecho te vas a opciones de tabla dinámica y nos vamos donde dice a ver acá mostrar y miren la primera opción que te sale es mostrar botones para expandir y contraer si tú quitas esta opción miren aceptas y ya no aparece ningún botón para comprimir o expandir no si quieres volver a mostrarlo nuevamente clic derecho con opciones de tabla dinámica y mostrar lo volvemos a activar aquí un clic miren aceptamos y nuevamente ya nos aparece las opciones de contraer y expandir bien pero nosotros nuevamente le vamos a expandir acá todo análisis de tabla dinámica y nuevamente le damos un clic en el más otra de las cosas que sinceramente es muy importante y alguna vez lo van a tener que ver son los llamados campos calculados aquí miren vamos a dar un clic en cualquier parte de nuestra tabla dinámica vamos a ir nuevamente análisis de tablas dinámica y por acá miren acá dice cálculos miren cálculos y campos elementos y conjuntos un clic acá y luego de eso le vamos a dar donde dice campo calculado donde dice nombre miren este va a ser el nombre del encabezado que vamos a crear por ejemplo miren por ejemplo yo voy a calcular suponiendo unas metas para el año 2022 por ejemplo acá tenemos ventas del año 2021 yo quiero calcular las metas que se deberían lograr para el año 2022 suponiendo que le vamos a aumentar un 5% o un 10% no nuevamente damos un clic en la tabla dinámica nos vamos a análisis de tabla dinámica nos vamos a campos elementos y conjunto y nuevamente campo calculado acá vamos a poner el nombre del campo calcula do por ejemplo yo voy a poner acá o el nombre de la columna que se va a crear dentro de nuestra tabla dinámica yo voy a poner te metás 2022 miren voy a borrar esto pongo igual y en este caso a ventas miren le voy a multiplicar por el 5% ok o sea miren hasta acá me va a calcular el 5% nada más si yo pongo paréntesis pongo acá al final parece decir más nuevamente ventas del año 2021 y de esta manera voy a tener el 5% de la de las ventas 2021 más las mismas ventas del año 2021 no le doy en aceptar miren le doy en aceptar acá a ver perdón en aceptar y automáticamente ya se acaba de crear miren ya se acaba de crear acá miren se vendió en enero 47 mil 100 para las metas del año 2022 yo quiero que se venda el 5% más entonces tenemos 49 mil 455 ver en acá se cambió el formato vamos a cambiarlo clic derecho configuración de campos de valor y acá formato de número lo que hicimos al inicio formato de número moneda no haber moneda ahí lo tenemos el sol aceptamos aceptamos y ahora si miren todo est á monedas no en moneda sol peruano fíjense que este es un campo calculado y yo lo acabo de crear recién acabo de crear esta columna con la fórmula que yo mismo le acabo de indicar no otra de las opciones muy interesantes que tiene miren las tablas dinámicas es generarte filtros en cada página de informes por ejemplo a qué me refiero para que tengas una idea vamos a dar un clic acá en análisis de tabla dinámica y nos vamos a ir a la parte izquierda acá donde dice tabla dinámica ojo nombre de tabla de dinámica también puedes ponerle un nombre cuando ya trabajes con macros y quieras manipularlo por ahora lo pueden dejar ahí porque no vamos a pronto a profundizar con macros en este tema si le damos un clic donde dice opciones un clic acá miren acá dice mostrar páginas de filtro de informes pero para acá está para que esta opción se habilite necesariamente tú miren necesariamente tú tienes que ponerle acá no sé por ejemplo mira para que me entiendan voy a poner a fecha ok en filtro le voy a poner la fecha un clic acá miren cambio mi tablita dinámica no si yo le doy un clic acá miren en fecha un clic y seleccione enero entonces acepto miren los datos cambian y por ejemplo el producto billetera que se vendió 11 mil 500 en el mes de enero y su meta es 12 mil 75 no también también en el campo calculado cambia por ejemplo quiero ver febrero aceptó miren cambia también los valores no miren el 5% es 700 en este caso no pero miren a lo que yo quiero llegar es que le voy a dar en todas aceptó voy a dar en todas acepto y nuevamente acá miren voy a dar un clic en cualquier parte en cualquier parte de la tabla dinámica nuevamente pestañita análisis de tabla dinámica a la izquierda dice opciones un clic en opciones y ahora sí habilitado esta opción que dice mostrar páginas de filtro de informes voy a dar un clic acá mostrar página de filtro de informes un clic y miren qué es lo que va a pasar me va a saltar esta ventanita y solamente me aparece la opción que tengo acá arriba en el fi ltro en fecha acá miren este esta opción oeste oest encabezado que llevamos a filtro al área de filtro que es fecha lo tengo acá o sea si hubiera llevado productos han filtro entonces también lo tendría acá pero solamente tenemos fecha le voy a dar en aceptar y miren acá abajo miren cómo se van a crear las hojas de manera automática le doy a aceptar para notar tenemos enero miren que enero el informe a casa miren el informe de enero todos los datos peter a calculadora cartuchera solamente de enero si me voy a febrero miren los datos van a ser diferente ahora tenemos miren en febrero solamente se vendió calculadora acá y también tenemos las metas no y así sucesivamente miren los datos van a ir cambiando de acuerdo al mes miren a casan todos los meses mayo junio julio agosto septiembre octubre noviembre y en noviembre y diciembre no por ejemplo en diciembre miren también tenemos una venta de cartuchera de 15.800 no la meta para el año 2022 es 16.590 yo voy a regresar acá miren acá hay un aspa la quitas por si no se dan cuenta para que puedan navegar dentro de las hojas yo voy a regresar a ver a ver a ver en donde estábamos si acá en ejercicio 1 miren ahora lo que yo voy a hacer es lo siguiente a producto le voy a poner arriba a fecha le voy a poner abajo para que ustedes mismos se den cuenta miren acá en productos si doy un clic tengo billetera calculadora cartuchera lapicero y mochila porta bien si yo selecciono billetera acepto miren cambia ok solamente tengo ventas en enero marzo y agosto pero le vamos a dar nuevamente todas y acepto estos nombres que aparecen acá en el filtro que es producto por ejemplo billetera calculadora cartuchera lapiceras mochila porta todos esos nombres ahora quiero que observen que van a aparecer abajo como perdón perdón van a aparecer abajo como hojas miren como hojas porque está el filtro acá porque el filtro es por producto todos esos nombres van a aparecer ahora como hojas vamos a probarlo o un clic acá en la tabla dinámica ya conocem os análisis de tablas dinámica y luego de eso opciones y vamos a ir mostrar página de filtro de informes o un clic acá y miren me aparece la única opción productos que es acá en filtros que están productos ahora no ante estaba fecha horas a productos aceptó y miren qué es lo que pasa ahora tengo billetera mire y tengo sus datos y sus ventas por meses ahora tengo calculadora y tengo sus meses que se ha vendido y la suma y sus metas ahora tengo cartuchera miren cartucheras y se ha vendido bastantes meses sus datos y sus metas no y así sucesivamente ahora tengo en hojas miren tengo en hojas todos miren todos estos datos no que me sirven como reporte bien eso vendría a ser la opción miren que tenemos acá mostrar páginas de filtro de informe lo que ahora vamos a ver es la creación de gráficos pero esta vez con tablas dinámicas bien para crear los gráficos yo pienso miren podemos trabajar tanto con ventas y con metas ok pero para que no se confundan y como quien aprendemos a eliminar este ca mpo calculado lo vamos a sacar bien en mi ningún clic en cualquier parte de la tabla ya sabemos nos vamos a análisis de tabla dinámica y nos vamos donde dice campos y elementos de conjuntos damos un clic en campo calculado y tenemos esta ventanita que estamos observando donde dice nombre vamos a despegar acá y le vamos a seleccionar miren el campo que vamos de crear que es metas 2022 simplemente eliminamos a la derecha dice eliminar aceptamos y de esta manera y ya quitamos lo que vendría a ser el campo calculado no miren acá fácilmente en filtros podríamos poner los productos en columnas miren para tenerlo de tal manera no nosotros fácilmente podemos crear un gráfico de lo que estamos observando y no hay ningún problema pero lo recomendable miren lo recomendable sería de la siguiente manera no si vas a crear un gráfico que sea por mes o también lo puedes crear por producto ok miren de tal manera por producto y su venta vamos a crearlo de esta manera producto y sus ventas ok acá lo tene mos fila hemos puesto el campo producto y valores hemos puesto el campo de suma de las ventas vamos a seleccionar vamos a ver un clic en cualquier parte miren damos un clic en cualquier parte de la tabla dinámica nos vamos otra vez análisis de tabla dinámica ya la derecha fíjense que dice gráfico dinámico vamos a dar un clic acá y acá tenemos vienen todos los gráficos para que ustedes elijan con cuál quieren trabajar no hay ningún problema trabajar con columnas que es uno de los más utilizados no le doy en aceptar automáticamente miren me salta mi gráfico acá lo tenemos bien si yo doy un clic miren yo dio un clic donde dice producto acá un clic en producto miren puedo de repente comparar billetera con calculadora de seleccionó los otros productos acepto y miren qué es lo que me salta, tengo la comparación de billetera con calculadora o un clic acá nuevamente producto y puedo poner de repente mochila activo y ahora tengo miren los tres gráficos de billetera calculadora y mochila yo voy a mostrar todos voy a mostrar todos para que estén ahí todos acepto y lo tenemos de tal manera no de repente no quieren mostrar ustedes la suma quieren mostrar la cantidad de productos que se han vendido fácil miren acá en suma le damos un clic derecho clic derecho le damos donde dice haber perdón clic derecho clic derecho configuración de campo de valor a casa de repente quiere mostrar las ventas máximas o de repente quiere mostrar recuento que vendría a ser el número de ventas que sea que se ha dado cada producto no le damos en aceptar y miren que acá acá ya me cambió pérez en soles no debería estar en soles y lo tengo acá el producto que más se ha vendido es el de cartuchera ok voy a voy a cambiar le voy a poner a soles clic derecho perdón configuración de campo le voy a poner a suma ok en lo que estaba le damos de aceptar y tranquilos no ahora sí vamos a dar un clic nuevamente en cualquier parte de la tabla dinámica vamos a ir otra vez al diseño análisis de tabla dinámica y podemos insertar por acá la segmentación de datos vamos a dar un clic en segmentación de datos me va a saltar esta ventanita miren a carne aparecen los encabezados yo quiero verlo por fecha por ejemplo miren doy un clic en fecha aceptó y me acaba de insertar mi segmentación esta segmentación miren vamos a dejar un poquito lo puedes poner a un costadito y quieres ver por ejemplo los productos que se han vendido en enero, miren ya me actualizo los productos solamente de enero febrero marzo miren puedo seleccionar dos también con el control acá miren a casa me tecladito con la tecla control pueden seleccionar varios en este caso meses por ejemplo enero febrero y marzo en los tres últimos meses cuántos productos se ha vendido mi en el que más se ha vendido así la calculadora vamos a seguir viendo en abril y en mayo miren a la calculadora sigue teniendo mayores ingresos a ver vamos a ver en el último mes vamos a ver en el último mes qué producto se ha vendido más noviembre diciembre creo que está el revés acá se miren cartuchera no entre noviembre de diciembre solamente 1 octubre dos productos septiembre solamente 2 agosto julio junio a ver abril marzo mayo hay de beber casi todos no 12 36 a 25 productos igual acá cartuchera miren desde el mes de marzo hasta noviembre ha tenido la mayoría de ventas no si quieres quitar los filtros le das un clic acá borrar filtro y eso prácticamente sería todo miren acá acá también pueden hacer algunas configuraciones de repente quieren que esté cien mil no sea sin irse a 120.000 por ejemplo no clic derecho dar formato al eje ya la derecha miren hasta la derecha tenemos máximo bien en máximo 100.000 yo lo voy a poner 120 para que llegue presiono enter para que lleguen bien en el dato hasta donde hasta 120 no suponiendo es una suposición para que ustedes varíe estos valores luego de eso acá miren miren va de 20.000 en 20.000 y yo no quiero que vaya de 20.000 en 20.000 y yo quiero que vale vaya de 10.000 en 10.000 presiono Enter y miren que los d atos se pegan más o que jesper más y ahora van de 10.000 en 10.000 puedo poner el texto más pequeño obviamente acá miren como si fuera cualquier texto algo más pequeñito para que se acomode y ahí lo tengo miren ahí lo tengo puedo poner la negrita cursiva y cambiarle de color si así lo desea lo mismo sucede acá los productos damos un clic y hacemos más pequeñito le ponemos negrita cursiva y de repente otro color que nos llame la atención y esto prácticamente sería todo seguramente que por ahí nos olvidamos algunos temitas pero si tienes alguna duda si algo no te quedó claro comenta este vídeo para ello responder conmigo será hasta acá no te olvides de suscribirte a tu canal El Tío Tech y nos vemos hasta la próxima.
