Las fórmulas de matriz de Excel son superpoderosas. Puede reemplazar miles de fórmulas con una sola fórmula una vez que aprenda el truco Ctrl + Shift + Enter. Hoy, una sola fórmula de matriz hace 86,000 cálculos.
Triskaidekaphobia es el miedo del viernes 13. Este tema no curará nada, pero le mostrará una fórmula absolutamente asombrosa que reemplaza 110,268 fórmulas. En la vida real, nunca tengo que contar cuántos viernes 13 han ocurrido en mi vida, pero el poder y la belleza de esta fórmula ilustra el poder de Excel.
Digamos que tiene un amigo que es supersticioso sobre el viernes 13. Quieres ilustrar cuántos viernes 13 ha vivido tu amigo.
Configure la hoja de trabajo simple a continuación, con la fecha de nacimiento en B1 y =TODAY()en B2. Luego, una fórmula salvaje en B6 evalúa todos los días que su amigo ha estado vivo para calcular cuántos de esos días fueron viernes y cayeron el día 13 del mes. Para mí, el número es 86. No hay nada que temer.
Por cierto, el 17/02/1965 es realmente mi cumpleaños. Pero no quiero que me envíes una tarjeta de cumpleaños. En cambio, para mi cumpleaños, quiero que me dejes explicar cómo funciona esa fórmula increíble, un pequeño paso a la vez.
¿Has utilizado alguna vez la función INDIRECTO? Cuando lo solicite =INDIRECT("C3"), Excel irá a C3 y devolverá lo que esté en esa celda. Pero INDIRECT es más poderoso cuando calcula la referencia de celda sobre la marcha. Puede configurar una rueda de premios en la que alguien elija una letra entre A y C y luego elija un número entre 1 y 3. Cuando concatene las dos respuestas, tendrá una dirección de celda, y lo que esté en esa dirección de celda es el premio. . Parece que gané un libro de fotos en lugar de la estadía en el resort.
¿Sabes cómo almacena Excel las fechas? Cuando Excel muestra el 17/2/1965, está almacenando 23790 en la celda, porque el 17/2/1965 fue el día 23790 del siglo XX. En el corazón de la fórmula hay una concatenación que une la fecha de inicio y dos puntos y la fecha de finalización. Excel no usa la fecha formateada. En cambio, utiliza el número de serie detrás de escena. Entonces se B3&":"&B4convierte en 23790: 42167. Lo crea o no, esa es una referencia de celda válida. Si quisiera sumar todo en las filas 3 a 5, podría usar =SUM(3:5). Entonces, cuando pasa 23790: 42167 a la función INDIRECTA, apunta a todas las filas.
Lo siguiente que hace la fórmula asesina es pedir el ROW(23790:42167). Normalmente, pasa una sola celda: =ROW(D17)es 17. Pero en este caso, está pasando miles de celdas. Cuando solicita ROW(23790:42167)y finaliza la fórmula con Ctrl + Shift + Enter, Excel en realidad devuelve todos los números desde 23790, 23791, 23792, y así sucesivamente hasta 42167.
Este paso es el paso asombroso. En este paso, pasamos de dos números y “sacamos” una matriz de 18378 números. Ahora, tenemos que hacer algo con ese conjunto de respuestas. La celda B9 de la figura anterior solo cuenta cuántas respuestas obtenemos, lo cual es aburrido, pero demuestra que ROW(23790:42167)devuelve 18378 respuestas.
Simplifiquemos drásticamente la pregunta original para que pueda ver qué está sucediendo. En este caso, encontraremos el número de viernes de julio de 2015. La fórmula que se muestra a continuación en B7 proporciona la respuesta correcta en B6.
En el corazón de la fórmula está ROW(INDIRECT(B3&":"&B4)). Esto devolverá las 31 fechas en julio de 2015. Pero la fórmula luego pasa esas 31 fechas a la WEEKDAY(,2)función. Esta función devolverá un 1 para el lunes, 5 para el viernes y así sucesivamente. Entonces, la gran pregunta es cuántas de esas 31 fechas devuelven un 5 cuando se pasan a la WEEKDAY(,2)función.
Puede ver cómo se calcula la fórmula en cámara lenta utilizando el comando Evaluar fórmula en la pestaña Fórmula de la cinta.
Esto es después de que INDIRECT convierta las fechas en una referencia de fila.
En el siguiente paso, Excel está a punto de pasar 31 números a la función WEEKDAY. Ahora, en la fórmula del asesino, pasaría 18 378 números en lugar de 31.
Estos son los resultados de las 31 funciones WEEKDAY. Recuerde, queremos contar cuántos son 5.
Verificar si la matriz anterior es 5 devuelve un montón de valores verdaderos / falsos. Hay 5 valores verdaderos, uno para cada viernes.
No puedo mostrarles lo que sucede a continuación, pero puedo explicarlo. Excel no puede SUMAR un montón de valores verdaderos y falsos. Va contra las reglas. Pero si multiplica esos valores Verdadero y Falso por 1 o si usa la función doble negativo o N (), convierte los valores Verdaderos a 1 y los valores Falsos a 0. Envíelos a SUM o SUMPRODUCT, y obtenga el recuento de los valores verdaderos.
Aquí hay un ejemplo similar para contar cuántos meses tienen un día 13 en ellos. Es trivial pensar en esto: cada mes tiene un 13, por lo que la respuesta para todo un año es mejor 12. Excel está haciendo los cálculos, generando 365 fechas, enviándolas todas a la función DAY () y calculando cuántas terminan hasta el día 13 del mes. La respuesta, como se esperaba, es 12.
La siguiente figura es una hoja de trabajo que hace toda la lógica de la fórmula asesina que se muestra al comienzo de este tema. He creado una fila por cada día que he estado vivo. En la columna B, obtengo el DÍA () de esa fecha. En la columna C, obtengo el WEEKDAY () de la fecha. En la columna D, ¿B es igual a 13? En la columna E, ¿C = 5? Luego multiplico D * E para convertir Verdadero / Falso a 1/0.
He ocultado muchas filas, pero les muestro tres días aleatorios en el medio, que resultan ser viernes y 13.
El total en F18381 es el mismo 86 que devolvió mi fórmula original. Una gran señal. Pero esta hoja de trabajo tiene 110,268 fórmulas. Mi fórmula asesina original hace toda la lógica de estas 110,268 fórmulas en una sola fórmula.
Espere. Quiero aclarar. No hay nada mágico en la fórmula original que se vuelva inteligente y acorte la lógica. Esa fórmula original realmente está haciendo 110,268 pasos, probablemente incluso más, porque la fórmula original tiene que calcular la matriz ROW () dos veces.
Encuentre una manera de usar esto ROW(INDIRECT(Date:Date))en la vida real y envíelo por correo electrónico (pub en punto com). Enviaré un premio a las primeras 100 personas que respondan. Probablemente no sea una estancia en un resort. Más probablemente una Big Mac. Pero así es con los premios. Un montón de Big Macs y no muchas estancias en complejos turísticos.
Vi por primera vez esta fórmula publicada en el tablero de mensajes en 2003 por Ekim. Se le dio crédito a Harlan Grove. La fórmula también apareció en el libro de Bob Umlas This Isn't Excel, It's Magic. Mike Delaney, Meni Porat y Tim Sheets sugirieron el truco menos / menos. SUMPRODUCT fue sugerido por Audrey Lynn y Steven White. Gracias a todos.
Ver video
- Hay una clase secreta de fórmulas llamadas fórmulas de matriz.
- Una fórmula de matriz puede realizar miles de cálculos intermedios.
- A menudo requieren que presione Ctrl + Shift + Enter, pero no siempre.
- El mejor libro sobre fórmulas de matriz es Ctrl + Shift + Enter de Mike Girvin.
- INDIRECTO le permite usar la concatenación para construir algo que parezca una referencia de celda.
- Las fechas están bien formateadas, pero se almacenan como una cantidad de días desde el 1 de enero de 1900.
- La concatenación de dos fechas apuntará a un rango de filas en Excel.
- Al pedir el
ROW(INDIRECT(Date1:Date2))"saldrá" una matriz de muchos números consecutivos - Usando la función WEEKDAY para averiguar si una fecha es viernes.
- ¿Cuántos viernes ocurren en este julio?
- Para ver cómo se calcula una fórmula en cámara lenta, use la herramienta Evaluar fórmula
- ¿Cuántos 13 se producen este año?
- ¿Cuántos viernes 13 sucedieron entre dos fechas?
- Verifique cada fecha para ver si el WEEKDAY es viernes
- Verifique cada fecha para ver si el DÍA es 13
- Multiplica esos resultados usando SUMPRODUCT
- Use - para convertir Verdadero / Falso a 1/0
Transcripción del video
Aprenda Excel del podcast, episodio 2026: ¡Mi fórmula favorita en todo Excel!
Podcasting este libro completo, haga clic en la "i" en la esquina superior derecha para acceder a la lista de reproducción.
Muy bien, era el tema número 30 del libro, estábamos al final de la sección de fórmulas o en medio de la sección de fórmulas, y dije que tenía que incluir mi fórmula favorita de todos los tiempos. Esta es solo una fórmula increíble, ya sea que tenga que contar el número el viernes 13 o no, ¡abre un mundo en un área secreta completa de Excel llamada fórmulas de matriz! Ingrese una fecha de inicio, ingrese una fecha de finalización, y esta fórmula calcula el número de viernes 13 que ocurrió entre esas dos fechas. En realidad, está haciendo cinco cálculos cada día entre esas dos fechas, 91895 cálculos + SUMA, 91896 cálculos que ocurren dentro de esta pequeña fórmula, de acuerdo. Ahora, al final de este episodio, estarán tan intrigados por las fórmulas de matriz. Quiero señalar,mi amigo Mike Girvin tiene el mejor libro sobre fórmulas de matriz llamado "Ctrl + Shift" Enter ", esta es una impresión reciente con la cubierta azul, solía ser una cubierta amarilla y verde. Ahora, cualquiera que obtenga, es un gran libro, el mismo contenido tanto en amarillo como en verde.
Muy bien, comencemos por dentro de esto, con una fórmula que quizás no hayas escuchado llamada INDIRECTA. INDIRECTO nos permite concatenar, o de alguna manera construir un poco de texto que parece una referencia de celda. Muy bien, digamos que tenemos una rueda de premios aquí, y solo te pedí que elijas entre A, B y C. Bien, entonces eliges esto y eliges C, y luego eliges esto y eliges 3, está bien, y tu premio es una estadía en un resort, porque eso es lo que se almacena en C3. Y la fórmula aquí se concatena junto con lo que sea desde C5 y lo que esté en C6 usando & y luego pasándolo al INDIRECTO. Entonces = INDIRECTO (C5 y C6), en este caso, es C3, que tiene que ser una referencia equilibrada. INDIRECTO dice "Oye, vamos a ir a C3 y devolver la respuesta de eso, ¿de acuerdo?" En Lotus 1-2-3, esto se llamaba la función @@,en Excel lo cambiaron de nombre a INDIRECTO. Muy bien, lo tienes en el INDIRECTO, ahora aquí está lo increíble que está sucediendo dentro de allí.
Tenemos dos fechas, ¿cómo almacena Excel las fechas? 17/2/1965, eso es realmente solo formato. Si miramos el número real detrás de eso, es 23790, lo que significa que son 23790 días desde el 1/1/1900 y 42167 días desde el 1/1/1980. En una Mac será desde el 1/1/1904, por lo que las fechas serán aproximadamente 3000 menos. Muy bien, así es como Excel lo almacena, aunque nos lo está mostrando, gracias a este formato de número como una fecha, pero si concatenamos B3 y a: y B4, en realidad nos daría los números almacenados detrás de escena. Entonces = B3 & ”:” & B4, y si lo pasamos a INDIRECTO, en realidad apuntará a todas las filas desde 23790 hasta 42167.
Entonces está el INDIRECTO de B6, pedí la FILA de eso, eso me dará un montón de respuestas, y para averiguar cuántas respuestas usé contar, está bien. Y para que esto funcione, si solo presiono Enter, no funciona, tengo que mantener presionadas las teclas Ctrl y Shift y presionar Enter, y mira, eso agrega el () alrededor de la fórmula aquí. Le dice a Excel que entre en modo de súper fórmula, modo de fórmula de matriz y haga todos los cálculos para todo lo que surgió de esa matriz, 18378, está bien. Entonces, ese es un truco asombroso, indirecto de date1: date2, páselo a la función ROW, y aquí hay un pequeño ejemplo.
Así que solo queremos averiguar cuántos viernes ocurrieron en este mes de julio, aquí hay una fecha de inicio, aquí una fecha de finalización, y para cada una de esas filas voy a pedir el WEEKDAY. WEEKDAY nos dice qué día de la semana es, y aquí en el argumento, 2 viernes tendrán un valor de 5. Entonces, estoy buscando la respuesta, y vamos a elegir esta fórmula, ve a Fórmulas, y Evaluar fórmula, y Evaluar fórmula es una excelente manera de ver cómo se calcula una fórmula en cámara lenta. Entonces está el B3, el 1 de julio, y ves que cambia a un número, y luego unimos los dos puntos, correcto, está el B4, que cambiará a un número, y ahora obtenemos el texto, 42186: 42216. En este punto, pasamos eso a la FILA, y esa pequeña expresión simple se convertirá en 31 valores aquí mismo.
Ahora, en el ejemplo en el que tuve todo desde 1965 hasta 2015, aparecerían 86000 valores, ¿verdad, y no quieres hacer eso y evaluar la fórmula, porque sería un poco loco, de acuerdo? Pero pueden ver lo que está sucediendo aquí con el 31, y ahora paso esos 31 días a la función WEEKDAY, y obtenemos 3-4-5. Entonces, 3 significa que fue miércoles, luego 4 significa que fue jueves, y luego 5 significa que fue viernes. Tome todos esos 31 valores y vea si son = 5, que es un viernes, y obtendremos un montón de FALSOS y VERDADEROS, por lo que miércoles, jueves, viernes y luego 7 celdas más tarde sería el siguiente VERDADERO. ¡increíble!
Muy bien, en este caso tenemos 5 VERDADEROS y 26 FALSOS, para sumarlos, necesito convertir el FALSO en 0 y el VERDADERO en 1, y una forma muy común de hacerlo es usar el - . Muy bien, desafortunadamente no mostró la respuesta allí, donde vimos un montón de 1 y 0, pero eso en realidad es lo que sucede, y luego SUMPRODUCT lo suma y nos lleva al 5. Aquí abajo, si queremos calcule cuántos del 13 del mes hubo este año, desde esta fecha de inicio hasta esta fecha de finalización, un proceso muy similar. Aunque vamos a tener 365, pase eso a la función DÍA y verifique cuántos son 13, está bien. Para el ejemplo de la fila 92000, ya sabes, obtenemos el día, obtenemos el día de la semana, verificando si el, DÍA = 13, verificando si el DÍA DE LA SEMANA = FALSO, multiplicando esto * esto,y solo en los casos en los que es un viernes 13, eso termina siendo VERDADERO. El SUMPRODUCTO luego dice “Sume todos esos”, y así es como obtenemos los 86, literalmente 91895 cálculos + SUM, 91896 sucediendo dentro de esta fórmula, ¡es increíblemente poderoso! Ve a comprar el libro de Mike, es un libro increíble, te abrirá un mundo entero de fórmulas de Excel y, de hecho, deberías comprar ambos libros. Compre mi libro, compre el libro de Mike y tendrá una colección increíble que lo ayudará a pasar el resto del año.le abrirá todo un mundo de fórmulas de Excel y, de hecho, debería comprar ambos libros. Compre mi libro, compre el libro de Mike y tendrá una colección increíble que lo ayudará a pasar el resto del año.le abrirá todo un mundo de fórmulas de Excel y, de hecho, debería comprar ambos libros. Compre mi libro, compre el libro de Mike y tendrá una colección increíble que lo ayudará a pasar el resto del año.
Muy bien, recapitulemos: hay una clase secreta de fórmulas llamadas fórmulas de matriz, y la fórmula de matriz puede hacer miles de cálculos intermedios. Por lo general, requieren que presione Ctrl + Shift + Enter, pero no siempre, y el mejor libro sobre fórmulas de matriz es el libro “Ctrl + Shift + Enter” de Mike Girvin. Muy bien, entonces INDIRECTO le permite usar la concatenación para construir algo que parece una referencia de celda, y luego INDIRECTO va a esa referencia de celda. Concatenar dos fechas con dos puntos apuntará a un rango de filas en Excel, y luego pedir la FILA del INDIRECTO de date1: date2 mostrará una matriz de muchos números consecutivos, tal vez 31, tal vez 365 o tal vez 85000. Verificar cada fecha para ver si el DÍA DE LA SEMANA = viernes, verifique cada día para ver si el DÍA = 13, multiplique esas dos matrices de VERDADEROS y FALSOS usando el SUMPRODUCTO. En muchos casos nosotros 'Usaré - para convertir VERDADERO / FALSO en 1 y 0 para permitir que SUMPRODUCTO funcione. Es una fórmula increíble, no la creé, la encontré en el tablero de mensajes, mientras trabajaba en ella, dije: "¡Vaya, esto es realmente genial!"
Muy bien, quiero agradecerles por pasar, ¡nos vemos la próxima vez para otro netcast de!
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2026.xlsx
