Mes hasta la fecha: consejos de Excel

Cómo mostrar las ventas del mes hasta la fecha en una tabla dinámica. Este es un episodio de Dueling Excel.

Ver video

• El método de Bill
• Agregue una celda auxiliar con una fórmula MTD
• =AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
• Agregue ese campo como Slicer donde = True
• Consejo adicional: fechas diarias grupales hasta años
• Agregue un cálculo fuera de la tabla dinámica evitando GetPivotData
• El enfoque de Mike:
• Convierta los datos en una tabla usando Ctrl + T. Esto permite agregar más datos a la tabla y actualizar las fórmulas.
• SUMIFS con funciones DATE, MONTH, DAY
• Al presionar F4 tres veces, se bloquea una referencia solo a la columna.
• Tenga cuidado: si arrastra una fórmula de tabla hacia los lados, las columnas cambian. Copiar y pegar: sin problemas
• Usando TEXT (fecha, formato. Buen truco con 1 para insertar el número 1 en el texto

Transcripción del video

Bill Jelen: Oye, bienvenido. Es hora de otro Podcast de Dueling Excel. Soy Bill Jelen de. Me acompañará Mike Girvin de Excel Is Fun.

Este es nuestro Episodio 181: Tabla dinámica del mes hasta la fecha.

Bueno, oye, la pregunta de hoy. La idea de hoy para este duelo la envía Mike. Él dice: "¿Puede crear un informe del mes hasta la fecha en una tabla dinámica?"

Bien, vamos. Así que esto es lo que tenemos, tenemos dos años de fechas desde enero de 2016 hasta 2017. Ahora, por supuesto, estoy grabando esto en abril, es el 15 de abril en este momento cuando estoy grabando mi parte del duelo. Y aquí tenemos una tabla dinámica que muestra Días en el lado izquierdo, Categoría en la parte superior e Ingresos en el corazón de la tabla dinámica.

Ahora, para crear un informe Mes hasta la fecha, lo que voy a hacer es decir que voy a agregar una nueva columna de ayuda aquí a mis datos originales y eso va a verificar dos cosas. Y como estoy comprobando dos cosas, voy a usar la función AND, ambas cosas tienen que ser True para que sea Month To Date. Y voy a usar una función aquí llamada HOY. HOY, está bien, entonces quiero saber si el MES de HOY ()) es = al MES de esa fecha en la Columna A. Si eso es cierto, si es el mes actual, en otras palabras, si es abril, entonces verifique y vea si el día de esa fecha allá en A2 es <= DÍA de HOY. Lo bueno es que cuando abramos este libro mañana o dentro de una semana, el día de hoy se actualizará automáticamente y hacemos doble clic para copiarlo.

Muy bien ahora, tenemos que obtener estos datos adicionales en nuestra tabla dinámica, así que vengo aquí Tabla dinámica, Analizar y no es tan difícil cambiar la fuente de datos, simplemente haga clic en ese botón grande y diga que queremos ir a la Columna D , haga clic en Aceptar. Muy bien, ahora tenemos ese campo adicional, voy a Insertar un Rebanador basado en ese campo Mes hasta la fecha y solo quiero ver cómo nuestro Mes hasta la fecha es verdadero. Ahora, ¿necesitamos que ese Slice sea tan grande? No, probablemente podamos hacer que sean dos columnas y simplemente tenerlo discreto en el lado derecho. Así que ahora lo que tenemos son todas las fechas de 2016 y todas las fechas de 2017; aunque sería genial compararlos uno al lado del otro. Así que voy a tomar ese campo Fecha y Analizar. Voy a agrupar el campo, lo voy a agrupar en solo años. Yo no'En realidad, me preocupan los días individuales. Solo quiero saber el mes hasta la fecha. Ahora, ¿dónde estamos? Así que lo agruparé en Años y terminaremos con estos 2 años allí y luego reorganizaré esto, pondré esos Años para cruzar, Categorías para bajar. Y ahora veo dónde estábamos el año pasado y dónde estábamos este año. Muy bien, debido a que terminé de agrupar, ya no puedo crear un campo calculado dentro de la tabla dinámica. Si quisiera tener una cantidad de año tras año allí, haría clic derecho, Eliminar total general, de acuerdo, y ahora estamos, entonces,% de cambio, estamos fuera de una tabla dinámica apuntando dentro de la tabla dinámica . Tenemos que asegurarnos de desactivar GetPivotData o simplemente construir una fórmula como esta: = J4 / I4-1 y eso crea una fórmula que podemos copiar sin ningún tipo de molestia, así.Muy bien, Mike, veamos qué tienes.

Mike Girvin: Gracias. Sí, envié la pregunta a porque lo hice con fórmulas y no pude averiguar cómo hacerlo con una tabla dinámica estándar y luego recordé haber visto a lo largo de los años, hacer un montón de videos geniales sobre columnas auxiliares y tablas dinámicas . Esa es una fórmula hermosa y una solución hermosa. Así es como se hace con una tabla dinámica, veamos cómo se hace con una fórmula.

Ahora, estoy haciendo esto dos días después de que él lo hizo. F2 Tengo la función HOY, que siempre será la información de fecha para la fecha actual de hoy que será utilizada por las fórmulas aquí abajo porque queremos que se actualice. También he usado una tabla de Excel y se llama FSales. Si presiono Ctrl + Flecha hacia abajo, veo que es 4/14 pero quiero poder agregar los registros más recientes y tener la actualización de nuestras fórmulas incluida cuando saltemos al próximo mes. Ctrl + Flecha arriba. Muy bien, tengo Criterios de año como encabezados de columna, Categoría como encabezados de fila, y luego los detalles del mes y el día vendrán de esa celda. Así que simplemente voy a usar la función SUMIFS ya que estamos agregando con múltiples condiciones, el rango de suma aquí está el ingreso, vamos a usar ese gran truco para una tabla de Excel.Justo en la parte superior vemos esa flecha negra que apunta hacia abajo, ¡BAM! Eso pone el nombre correcto de la tabla y luego entre corchetes el nombre del campo, coma. Rango de criterios, tendremos que usar Date dos veces, así que empezaré con Date. Haga clic en, ahí está la columna de fecha, coma. Ahora estoy en abril, así que necesito crear la condición> = al 1 de abril. Entonces, los operadores comparativos “> =” entre comillas dobles y me voy a unir. Ahora tengo que crear una fórmula de fecha que siempre se vea aquí y cree el primer día del mes para este año en particular. Entonces voy a usar la función FECHA. Año, bueno, tengo el Año justo como el encabezado de la columna y voy a presionar la tecla F4 una, dos veces para bloquear la fila pero no la columna, así que cuando se mueva aquí pasaremos a 2017, coma, el Mes - I 'Voy a usar la función MES para obtener el número de mes del 1 al 12. Ese es el mes que esté en esa celda, F4 para bloquearlo en todas las direcciones, cierre paréntesis y luego coma, 1 siempre será el primero de la mes, no importa qué mes sea, cierre paréntesis.

Muy bien, ese es el criterio. Siempre será> = el primero del mes, coma, rango de criterios dos. Voy a obtener mi columna Fecha, coma. Criterio dos, bueno, esto va a ser <= el límite superior, entonces en “<=” y el &. Voy a hacer trampa, mira esto. Solo voy a copiar esto desde aquí ya que es lo mismo, Ctrl-C Ctrl-V excepto por el Día, tenemos que usar la función DÍA y siempre obtener como límite superior cualquiera que sea el día de este mes en particular . F4 para bloquearlo en todas las direcciones, cierre paréntesis en Fecha. Muy bien, ese es nuestro criterio dos: coma. Rango de criterios 3, es Categoría. Ahí está, coma y está nuestro encabezado de fila. Así que este tenemos que F4 uno dos tres veces, bloquear la columna pero no la fila, así que cuando copiemos la fórmula, nos moveremos a Gizmo y Widget,cierre paréntesis y esa es la fórmula. Arrastre, haga doble clic y envíelo. Puedo ver que hay problemas. Será mejor que vaya a la última celda diagonalmente más alejada. Presiona F2. Ahora, el comportamiento predeterminado para la Nomenclatura de fórmulas de tabla es que cuando copia las fórmulas al lado, las columnas reales se mueven como si fueran referencias de celda mixtas. Ahora podríamos bloquearlos pero no voy a hacer eso esta vez. Ahora observe que cuando lo copia, funciona bien, pero cuando lo copia hacia el lado, es cuando se mueven las columnas reales. Así que mira esto, voy a Ctrl + C y Ctrl + V y luego eso evita que F en las columnas se mueva cuando lo copias al lado. Haga doble clic y envíelo. Ahora nuestra fórmula de% de cambio = la cantidad final / la cantidad inicial -1, Ctrl + Enter, haga doble clic y envíelo.Arrastre, haga doble clic y envíelo. Puedo ver que hay problemas. Será mejor que vaya a la última celda diagonalmente más alejada. Presiona F2. Ahora, el comportamiento predeterminado para la Nomenclatura de fórmulas de tabla es que cuando copia las fórmulas al lado, las columnas reales se mueven como si fueran referencias de celda mixtas. Ahora podríamos bloquearlos, pero no voy a hacer eso esta vez. Ahora observe que cuando lo copia, funciona bien, pero cuando lo copia hacia el lado, es cuando se mueven las columnas reales. Así que mira esto, voy a Ctrl + C y Ctrl + V y luego eso evita que F en las columnas se mueva cuando lo copias al lado. Haga doble clic y envíelo. Ahora nuestra fórmula de% de cambio = la cantidad final / la cantidad inicial -1, Ctrl + Enter, haga doble clic y envíelo.Arrastre, haga doble clic y envíelo. Puedo ver que hay problemas. Será mejor que vaya a la última celda diagonalmente más alejada. Presiona F2. Ahora, el comportamiento predeterminado para la Nomenclatura de fórmulas de tabla es que cuando copia las fórmulas al lado, las columnas reales se mueven como si fueran referencias de celda mixtas. Ahora podríamos bloquearlos pero no voy a hacer eso esta vez. Ahora observe que cuando lo copia, funciona bien, pero cuando lo copia hacia el lado, es cuando se mueven las columnas reales. Así que mira esto, voy a Ctrl + C y Ctrl + V y luego eso evita que F en las columnas se mueva cuando lo copias al lado. Haga doble clic y envíelo. Ahora nuestra fórmula de% de cambio = la cantidad final / la cantidad inicial -1, Ctrl + Enter, haga doble clic y envíelo.Será mejor que vaya a la última celda diagonalmente más alejada. Presiona F2. Ahora, el comportamiento predeterminado para la Nomenclatura de fórmulas de tabla es que cuando copia las fórmulas al lado, las columnas reales se mueven como si fueran referencias de celda mixtas. Ahora podríamos bloquearlos pero no voy a hacer eso esta vez. Ahora observe que cuando lo copia, funciona bien, pero cuando lo copia hacia el lado, es cuando se mueven las columnas reales. Así que mira esto, voy a Ctrl + C y Ctrl + V y luego eso evita que F en las columnas se mueva cuando lo copias al lado. Haga doble clic y envíelo. Ahora nuestra fórmula de% de cambio = la cantidad final / la cantidad inicial -1, Ctrl + Enter, haga doble clic y envíelo.Será mejor que vaya a la última celda diagonalmente más alejada. Presiona F2. Ahora, el comportamiento predeterminado para la Nomenclatura de fórmulas de tabla es que cuando copia las fórmulas al lado, las columnas reales se mueven como si fueran referencias de celda mixtas. Ahora podríamos bloquearlos pero no voy a hacer eso esta vez. Ahora observe que cuando lo copia, funciona bien, pero cuando lo copia hacia el lado, es cuando se mueven las columnas reales. Así que mira esto, voy a Ctrl + C y Ctrl + V y luego eso evita que F en las columnas se mueva cuando lo copias al lado. Haga doble clic y envíelo. Ahora nuestra fórmula de% de cambio = la cantidad final / la cantidad inicial -1, Ctrl + Enter, haga doble clic y envíelo.las columnas reales se mueven como si fueran referencias de celda mixtas. Ahora podríamos bloquearlos pero no voy a hacer eso esta vez. Ahora observe que cuando lo copia, funciona bien, pero cuando lo copia al lado, es cuando se mueven las columnas reales. Así que mira esto, voy a Ctrl + C y Ctrl + V y luego eso evita que F en las columnas se mueva cuando lo copias al lado. Haga doble clic y envíelo. Ahora nuestra fórmula de% de cambio = la cantidad final / la cantidad inicial -1, Ctrl + Enter, haga doble clic y envíelo.las columnas reales se mueven como si fueran referencias de celda mixtas. Ahora podríamos bloquearlos pero no voy a hacer eso esta vez. Ahora observe que cuando lo copia, funciona bien, pero cuando lo copia al lado, es cuando se mueven las columnas reales. Así que mira esto, voy a Ctrl + C y Ctrl + V y luego eso evita que F en las columnas se mueva cuando lo copias al lado. Haga doble clic y envíelo. Ahora nuestra fórmula de% de cambio = la cantidad final / la cantidad inicial -1, Ctrl + Enter, haga doble clic y envíelo.Voy a Ctrl + C y Ctrl + V y luego eso evita que F en las columnas se mueva cuando lo copia al lado. Haga doble clic y envíelo. Ahora nuestra fórmula de% de cambio = la cantidad final / la cantidad inicial -1, Ctrl + Enter, haga doble clic y envíelo.Voy a Ctrl + C y Ctrl + V y luego eso evita que F en las columnas se mueva cuando lo copia al lado. Haga doble clic y envíelo. Ahora nuestra fórmula de% de cambio = la cantidad final / la cantidad inicial -1, Ctrl + Enter, haga doble clic y envíelo.

Ahora, antes de probarlo, agregue algunos registros nuevos. De hecho, quiero crear esta etiqueta aquí para que sea dinámica. Y la forma en que voy a hacer eso es decir = firmar y vamos a hacer una fórmula de texto, así que cada vez que queramos texto y una fórmula, debes ponerlo en: "y estoy voy a escribir Sales Between, space ”& y ahora necesito extraer de esa única fecha allí, el primero del mes hasta el final del mes. Voy a usar la función TEXTO. La función TEXT puede tomar un número de fechas o números de serie, coma y usar algún formato de número personalizado en ”. Siempre quiero ver una abreviatura de tres letras para el mes, mmm, siempre la quiero como la primera. Ahora, si pongo un 1 aquí, espacio de coma yyy, eso no funcionará. Quiere ver que eso nos da un valor o porque no le gusta eso 1. Pero nosotros 'Se nos permite insertar un solo carácter si usamos barra inclinada, que está en formato de Número personalizado. El formato de número personalizado entenderá el mm y el aa como mes y año y ahora el formato de número personalizado entenderá insertar el número 1. F2 y ahora simplemente vamos a: & "-" & TEXT de esa coma y ahora Solo usaré formato de número directo: “mmm espacioD, aaa”) Ctrl + Enter.

Now let's just, before we add some data, let's just change this. Pretending that today was showing: 3/15/2017 just like that, all the formulas are updating and our text formula is also, Ctrl+Z. Now, let's go down to the bottom of the data set, Ctrl+Down Arrow I want to add one new record. I'm in the last cell of the data set, I hit Tab to add a new record to our data set. I'm simply going to copy this record over here, Ctrl+Up Arrow, and there we can see the difference. If we wanted to check these formula values against the ones did: =relative cell reference= click on the sheet, we're going to click right in I4. We could see our formula up there, Ctrl+Enter. Actually I'm going to drag it down. Ctrl+Enter just populated everything I had highlighted. And of course, FALSE FALSE. Well guess what? = that amount right there -, click Ctrl+Down Arrow, Ctrl+Backspace, so I'm going to subtract that just to check and sure enough that was the exact amount we could look back there.

That is a little fun with some IFS and some date calculations, TODAY and even some text formula fun. Alright, throw back to.

Bill Jelen: Alright, Mike, that's awesome. So to wrap it up, Mike took the data and turned it into a table using Ctrl+T that allows more data to be added to the table, and the formulas will update, create that great little formula with SUMIFS, DATE, MONTH and DAY functions. Remember pressing F4 three times, locks the reference to just the column though watch out if you drag a table formula sideways using the Fill Handle, the columns change but Copy and Paste alleviates that problem. I never knew that one.

And then nice trick there using the heading with the text date format and that 1 to insert a number 1 in the text, in any character. I would allow to insert something, so you might have to do something like COOL to get an entire word in there but it would work.

Alright, my method was using a pivot table, I added a helper column with a MONTH TO DATE formula that one there using =AND checking if the MONTH and the DAY match. Add that field as a Slicer, set the Slicer =True. And then bonus tip: Group Daily Dates up to Years and then added a calculation outside of the pivot table while avoiding GetPivotData. And I'm interested, I still don't know how Mike did it with his formula. He managed to use the mouse to point to this equal to something, it’s on my pivot table and didn't get GetPivotData. Maybe, maybe he's turned it off.

Muy bien, bueno, oye, quiero agradecer a todos por pasar. Nos vemos la próxima vez para otro Dueling Excel Podcast de y Excel Is Fun.

Descargar archivo

Descarga el archivo de muestra aquí: Duel181.xlsm