¿Cuáles son los últimos cinco meses de lluvia? Aprenda a resolver este problema utilizando una tabla dinámica.
Ver video
- Las tablas dinámicas creadas en 2013 no se pueden actualizar en 2007
- Necesita crear la tabla dinámica en 2007 para permitir que sea actualizable
- El objetivo es encontrar los cinco meses con la menor cantidad de lluvia
- Cree una tabla dinámica grande con lluvia por mes
- Ordenar por lluvia ascendente
- Cambiar a forma tabular
- Utilice los filtros de valor, Top 10, para obtener los 5 últimos!
- Eliminar la fila Gran Total
- Tenga en cuenta que un empate puede hacer que este informe le proporcione 6 o más filas
- Una vez que tenga la primera tabla dinámica, cópiela en su lugar y cree la siguiente tabla dinámica
- Cuando cambia de un campo de valor a otro, debe volver a ordenar y filtrar
- Cuando cambia de un campo de fila a otro, debe volver a ordenar y filtrar
- Consejo adicional: crear una tabla dinámica con filas y columnas
Transcripción del video
Aprenda Excel del Podcast, Episodio 2063: Los cinco meses o años superiores o inferiores utilizando una tabla dinámica.
Oye, bienvenido de nuevo al netcast, soy Bill Jelen. La pregunta de hoy enviada por Ken. Ken tiene una hoja de cálculo increíble aquí con años y años y años de fechas de lluvia diaria, que se remontan a 1999. Tiene una colección de datos realmente impresionante, y Ken tenía algunas fórmulas increíbles para tratar de encontrar el mes con más lluvia, en menos lluvia. Así que ahora, ya sabes, esto será mucho más fácil con una tabla dinámica.
Muy bien, Ken nunca creó una tabla dinámica y para complicar aún más las cosas, estoy aquí en Excel 2016, Ken está usando Excel 2007. Mis tablas dinámicas que creé en 2016, podía verlo pero no podía actualizarlas. Muy bien, este video es la tabla dinámica 101: cómo crear su primera tabla dinámica.
Primero, Ken tiene esta fecha en la columna A, fechas reales, ¿estamos bien? Eso es asombroso, ¿verdad? Y luego uso: inserto un par de fórmulas adicionales aquí en la función = AÑO para obtener el año, la función = MES para obtener el mes, la función = DÍA. Y luego concatenarlos de nuevo, de hecho usé la función = TEXT en YYYY-MM, de esa manera tengo año y mes abajo. Estos son los datos de Ken, los datos de lluvia aquí y luego agregué algunas fórmulas. Ken tiene menos de .5 milímetros, no cuenta como un día de lluvia, así que hay una fórmula ahí. Y luego, del Episodio 735, vuelve y échale un vistazo para ver cómo calculé la racha de días con lluvia y la racha de días sin lluvia. Eso no se va a usar hoy, se usó para otra cosa.
Entonces, venimos aquí. Y primero, queremos seleccionar los datos para nuestra tabla dinámica. Ahora, en la mayoría de los casos, podría seleccionar todos los datos para poder elegir una celda aquí, pero en este caso, hay un rango de Nombre que define los datos hasta, en este caso, 2016. Estamos sentados aquí. Estoy registrando esto a principios de 2017. Los datos de Ken solo llegan hasta finales de 2016. Por lo tanto, vamos a seleccionar solo esos datos. Y luego en la pestaña Insertar - pestaña Insertar. Excel 2007, es la primera vez que las tablas dinámicas se mueven de la pestaña Datos a la pestaña Insertar. Entonces elegimos: Tabla dinámica, y nuestros datos seleccionados serán los datos a partir de los cuales construimos. Y, no queremos ir a una nueva hoja de trabajo, vamos a ir a una hoja de trabajo existente y la voy a poner aquí en la Columna; vayamos con la Columna N.Ahora, en última instancia, quiero que estos datos de años con la precipitación más baja aparezcan aquí, pero sé que mientras estoy construyendo esta tabla dinámica, necesitará muchas más filas que esas 5, ¿verdad? Entonces, lo estoy construyendo a un lado aquí, está bien. Y hacemos clic en Aceptar.
Muy bien, ahora esto es lo que obtienes. Aquí es donde irá el informe y aquí hay una lista de todos los campos que tenemos en nuestro pequeño conjunto de datos. Y luego tenemos, porque lo que yo llamo horriblemente llamado abandona. Las filas son los elementos que desea en el lado izquierdo. Los valores son lo que desea resumir y luego las columnas son las cosas que desea en la parte superior. Podríamos usar esto al final. No vamos a utilizar filtros hoy. Entonces, solo estamos construyendo una pequeña tabla dinámica con la precipitación total por año, por lo que tomo el campo Año y lo arrastro aquí hacia abajo, al lado izquierdo. Hay una lista de todos nuestros años, ¿de acuerdo? Y luego, piénsalo. Para obtener esta fórmula aquí sin una tabla dinámica, ¿qué estaría haciendo? SUMIF, oh sí, SUMIF. Incluso podría usar la parte posterior de SUMIF en Excel 2007. Entonces,Voy a tomar el campo Rain y arrastrarlo aquí. Ahora, cuidado con … Mira, eligieron Count of Rain, eso es porque hay algunos días en los datos o Ken tiene una celda en blanco, una celda vacía en lugar de un 0. Y sí, deberíamos revisar y arreglar eso, pero son los datos de Ken. Son 20 años de datos. No voy a pasar ni siquiera con Buscar y reemplazar. Muy bien, solo … Por alguna razón, respetaré que Ken tenga una razón para tenerlos, como si les permitiera quedarse en blanco. Y aquí, en Count of Rain, me aseguraré de elegir una celda en la columna Count of Rain, ir a Configuración de campo y cambiar eso de Count a Sum, ¿de acuerdo? Así que están todos nuestros años y cuánta lluvia tuvimos cada año. Y buscamos los años con las precipitaciones más bajas.Ahora, cuidado con el - Mira, eligieron Count of Rain, eso es porque hay algunos días en los datos o Ken tiene una celda en blanco, una celda vacía en lugar de un 0. Y sí, deberíamos revisar y arreglar eso, pero son los datos de Ken. Son 20 años de datos. No voy a pasar ni siquiera usando Buscar y reemplazar. Muy bien, yo solo- Por la razón que sea, respetaré que Ken tenga una razón para tenerlos, como si les permitiera quedarse en blanco. Y aquí, en Count of Rain, me aseguraré de elegir una celda en la columna Count of Rain, ir a Configuración de campo y cambiar eso de Count a Sum, ¿de acuerdo? Así que están todos nuestros años y cuánta lluvia tuvimos cada año. Y buscamos los años con las precipitaciones más bajas.Ahora, cuidado con … Mira, eligieron Count of Rain, eso es porque hay algunos días en los datos o Ken tiene una celda en blanco, una celda vacía en lugar de un 0. Y sí, deberíamos revisar y arreglar eso, pero son los datos de Ken. Son 20 años de datos. No voy a pasar ni siquiera usando Buscar y reemplazar. Muy bien, solo … Por alguna razón, respetaré que Ken tenga una razón para tenerlos, como si les permitiera quedarse en blanco. Y aquí, en Count of Rain, me aseguraré de elegir una celda en la columna Count of Rain, ir a Configuración de campo y cambiar eso de Count a Sum, ¿de acuerdo? Así que están todos nuestros años y cuánta lluvia tuvimos cada año. Y buscamos los años con las precipitaciones más bajas.s porque hay algunos días en los datos o Ken tiene una celda en blanco, una celda vacía en lugar de un 0. Y sí, deberíamos revisar y arreglar eso, pero son los datos de Ken. Son 20 años de datos. No voy a pasar ni siquiera usando Buscar y reemplazar. Muy bien, yo solo- Por la razón que sea, respetaré que Ken tenga una razón para tenerlos, como si les permitiera quedarse en blanco. Y aquí, en Count of Rain, me aseguraré de elegir una celda en la columna Count of Rain, ir a Configuración de campo y cambiar eso de Count a Sum, ¿de acuerdo? Así que están todos nuestros años y cuánta lluvia tuvimos cada año. Y buscamos los años con las precipitaciones más bajas.s porque hay algunos días en los datos o Ken tiene una celda en blanco, una celda vacía en lugar de un 0. Y sí, deberíamos revisar y arreglar eso, pero son los datos de Ken. Son 20 años de datos. No voy a pasar ni siquiera con Buscar y reemplazar. Muy bien, solo … Por alguna razón, respetaré que Ken tenga una razón para tenerlos, como si les permitiera quedarse en blanco. Y aquí, en Count of Rain, me aseguraré de elegir una celda en la columna Count of Rain, ir a Configuración de campo y cambiar eso de Count a Sum, ¿de acuerdo? Así que están todos nuestros años y cuánta lluvia tuvimos cada año. Y buscamos los años con las precipitaciones más bajas.s datos. Son 20 años de datos. No voy a pasar ni siquiera usando Buscar y reemplazar. Muy bien, yo solo- Por la razón que sea, respetaré que Ken tenga una razón para tenerlos, como si les permitiera quedarse en blanco. Y aquí, en Count of Rain, me aseguraré de elegir una celda en la columna Count of Rain, ir a Configuración de campo y cambiar eso de Count a Sum, ¿de acuerdo? Así que están todos nuestros años y cuánta lluvia tuvimos cada año. Y buscamos los años con las precipitaciones más bajas.s datos. Son 20 años de datos. No voy a pasar ni siquiera con Buscar y reemplazar. Muy bien, solo … Por alguna razón, respetaré que Ken tenga una razón para tenerlos, como si les permitiera quedarse en blanco. Y aquí, en Count of Rain, me aseguraré de elegir una celda en la columna Count of Rain, ir a Configuración de campo y cambiar eso de Count a Sum, ¿de acuerdo? Así que están todos nuestros años y cuánta lluvia tuvimos cada año. Y buscamos los años con las precipitaciones más bajas.Me aseguraré de elegir una celda en la columna Recuento de lluvia, ir a Configuración de campo y cambiar eso de Recuento a Suma, ¿de acuerdo? Así que están todos nuestros años y cuánta lluvia tuvimos cada año. Y buscamos los años con las precipitaciones más bajas.Me aseguraré de elegir una celda en la columna Recuento de lluvia, ir a Configuración de campo y cambiar eso de Recuento a Suma, ¿de acuerdo? Así que están todos nuestros años y cuánta lluvia tuvimos cada año. Y buscamos los años con las precipitaciones más bajas.
Muy bien, una cosa que me molesta es esta palabra aquí Etiquetas de fila. Eso nos empezó a pasar en Excel 2007, ¿de acuerdo? Y yo … 10 años después, todavía lo desprecio. Voy a la pestaña Diseño, abro Diseño de informe y digo Mostrar en forma tabular, y todo eso. En este caso particular hay un rumbo real del año, ¿verdad? Y prefiero el rumbo real. En este momento, queremos ver solo la parte superior o, en este caso, los años con la precipitación más baja. Así que ordenaré estos datos de forma ascendente. Ahora hay dos formas de hacer esto. Puede abrir este menú desplegable, ir a Más opciones de clasificación, elegir Enviar en función de la suma de lluvia, pero también es posible ingresar aquí en Datos, de la A a la Z para ordenar las cosas de menor a mayor. Pero no quiero ver solo los 5 años principales, por lo que los años con la precipitación más baja,Vengo aquí al encabezado Año, abro este pequeño menú desplegable y elijo Filtros de valor. Y estoy buscando Bottom 5. Bueno, no hay filtro para Bottom 5. Ahh, pero este para los diez primeros es increíblemente poderoso. Muy bien, no tiene por qué ser superior. Puede ser superior o inferior. No tiene que ser 10; puede ser 5. Por lo tanto, pregunte por los 5 elementos principales según la suma de lluvia, haga clic en Aceptar. Y ahí está nuestro informe.
Now in this case, it would be really highly unusual if we had exactly 2 years with 767.7 inches or millimeters of rain exactly, right? Just not going to happen. But you have to be cognizant of the fact that when you asked for the Top 5, if there is a tie for that position, you might get a sixth row. If there's a 3-way tie, you might get a seventh row. Alright, so just be prepared for that. Grand total here really makes no sense since we're showing just the Top 5, and they're not even 5 consecutive years. So I’m going to right-click on the word Grand Total and say Remove Grand Total. Remove Grand Total. I’m wondering if that was there in Excel 2007. If it's not there in Excel 2007, go to the Design tab, Grand Totals, Off for Rows and Columns. We’ll do the same thing, alright. So now that we have this first pivot table and it's sized correctly, I’m going to copy that pivot table, Ctrl+C, make sure to choose the entire pivot table and go there - Years with Lowest Rainfall.
Now another thing that Ken wants is the years with highest, highest rainfall. Alright, so in this case, we're going to Sort the data, Z-A descending. And then here, come back into the Value Filters, go back into Top 10 and simply change it from Bottom to Top, click OK. Alright, so once you get the first pivot table built, pivot tables are so flexible. It's incredible how easy it is to just keep changing the pivot table. Alright now, here's the- here's the gotcha, right. The thing that makes us a little bit difficult. Now we want to look at the years with the least number of rain days, alright. How many days do we have- the fewest number of days with rain? Alright, so now this is going to change the pivot table a little because I want to take the Sum of Rain out, and I’m going to replace it with Rain Day. Alright, and see that one automatically came in as sum because my formula here is always returning a numeric values. We didn't have to change it to Account. And we're looking for the years with the least rain day so we're going to sort this ascending, alright, so that gets our sort back in but we've lost the Value Filter, the Top 5. Because we took the field that it was using, Rain out. So we have to reapply that: Value Filters and say Top - Actually we want the bottom, with the Bottom 5, like that.
Alright, so every time that I take a field in or out over here in the Values area, you have to be prepared to redo the Sort and redo the Filter. So Ctrl+C to copy that and paste right here, Years with the Least Rain Days.
Now, Ken has a lot of other statistics. I'll leave this up to Ken how to do this, but see here when we change the months, months with the highest rainfall. Alright, so now, I’m going to be changing a field in the row area. So we take Year out, put the Month field in like that and then this goes back to Rain, instead of Rain Days we put Rain in. Again, they forgotten that we want a sum so you have to go back in. In 2007, it’s called the Analyze tab, it's the first pivot table tools tab. Go to Field Settings, choose something like that - beautiful. And what are we looking for? We're looking for the months with the highest rainfall. So we're going to Sort Descending: Data, Z to A, and then again here, go back in to the Value Filters, Top 10 and we will ask for the Top 5 like that, alright? So very, very flexible. You can figure out the months with the least rain days, the most rain days and so on. So copy here, Ctrl+C and come and paste.
Now, one of the thing that Ken was building, he’s building a beautiful master table. It took this daily data and summarized it by year and month. So, let's just do that. Let's put Years down the left-hand side like this. Year's down the left-hand side, I got lazy there. I tried to check Market, it went to the wrong spot. We have some rainfall but we want to see months going across. And I know I have some other data out here to the right so I’m just going to insert a whole bunch of extra columns. Insert columns that way, I know that my pivot table won't crash into that. Beautiful thing here that Month field, the 1 through 12, I take that, drag it to the columns. And I now have a report showing years down the side, months going across the top and the summary of how much rain we had in each one. Those pivot tables are just an amazing, amazing feature.
Alright, if you're new to pivot tables, my new book, Power Excel with. This book hit the bookstores January 1st , what about - 36 days ago. But the new e-book versions for the Kindle, for your iPad, and we are PDF. Those are all now available at. If you buy the book from me, if you buy the print book for me, you get all 3 of those eBook formats for free. Minor have no DRM, no hassles. We believe in no hassles. You buy the book, you get all the formats. And what’s - Click the link down there in the YouTube description to get to my page where you can buy that book.
So wrap- up: Pivot tables created in Excel 2013 or 2016, can’t be refreshed in 2007. You have to create the pivot table in 2007 to allow it to be refreshable. So our goal is to find the five months with the least rainfall. Created a large pivot table with a rainfall by month, I knew that was going to fit in more than five rows. I built it off to the side. Sort by rainfall, actually, ascending is what we did here. Change the tabular form and then open that drop-down in the first column using the Value Filters, Top 5. It’s weird, you asked for the Top 10 and you get the bottom 5. Took the Grand Total row out. Even though we're asking for 5, you might get 6 if there's ever a tie - 6 or 7. And then, once we have the first pivot table, copied it into place and then created the next pivot table and just kept doing that. Although couple of gotchas when you change from one value field to another, you have to redo the Sort & Filter. You might have to redo the Count to Sum. When you change from one row field to another you have to redo the Sort & Filter. And then, right there at the end, showed you how to create a pivot table with rows and column, alright.
Bueno, oye, quiero agradecer a Ken por enviar esa pregunta. Quiero agradecerle por pasar. Nos vemos la próxima vez para otro netcast de.
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2063.xlsm
