Cree un calendario en Excel con una fórmula utilizando la fórmula ingresada por matriz.
Mira esta figura:
Esa fórmula =Cooles la misma fórmula en todas las celdas de B5: H10. Mira:
Se ingresó por matriz una vez que se seleccionó por primera vez B5: H10. En este artículo verás qué hay detrás de la fórmula.
Por cierto, hay una celda que aún no se muestra, cuál es el mes para mostrar. Es decir, la celda J1 contiene =TODAY()(y estoy escribiendo esto en diciembre) pero si lo cambia a 5/8/2012, verá:
Estamos en mayo de 2012. OK, definitivamente genial! Empiece desde el principio y avance hasta llegar a esta fórmula en el calendario y vea cómo funciona.
Además, suponga que hoy es 8 de mayo de 2012.
Primero, mire esta figura:
La fórmula realmente no tiene sentido. Lo haría, si estuviera rodeado =SUM, pero desea ver qué hay detrás de la fórmula, por lo que la expandirá seleccionándola y presionando la tecla F9.
La figura de arriba se convierte en la figura de abajo cuando se presiona la tecla F9.
Observe que hay un punto y coma después del 3; esto indica una nueva fila. Las nuevas columnas están representadas por una coma. Así que vas a aprovechar eso.
El número de semanas en un mes varía, pero ningún calendario necesita más de seis filas para representar cualquier mes y, por supuesto, todos tienen siete días. Mira esta figura:
Ingrese manualmente los valores de 1 a 42 en B5: H10, y si ingresa =B5:H10en una celda y luego expande la barra de fórmulas, verá lo que se muestra aquí:
Observe la ubicación de los puntos y comas, después de cada múltiplo de 7, que indica una nueva fila. Este es el comienzo de la fórmula, pero en lugar de una tan larga, puede usar esta fórmula más corta. Seleccione B5: H10. Tipo
=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)
como la fórmula, pero no presione Entrar.
Para decirle a Excel que esta es una fórmula de matriz, debe mantener presionadas las teclas Ctrl + Shift con la mano izquierda. Mientras mantiene presionadas las teclas Ctrl + Shift, presione Entrar con la mano derecha. Luego, suelte Ctrl + Shift. En el resto de este artículo, este conjunto de pulsaciones de teclas se llamará Ctrl + Shift + Enter.
Si hizo Ctrl + Shift + Enter correctamente, aparecerán llaves alrededor de la fórmula en la barra de fórmulas y los números del 1 al 42 aparecerán en B5: H10 como se muestra aquí:
Observe que está tomando los números del 0 al 5 separados por punto y coma (una nueva fila para cada uno) y multiplicándolos por 7, lo que efectivamente da esto:
La orientación vertical de estos valores sumada a la orientación horizontal de los valores del 1 al 7 produce los mismos valores que se muestran. La expansión de esto es idéntica a la que tenías antes. Suponga que ahora agrega HOY a estos números.
Nota: Editar una fórmula de matriz existente es muy complicado. Siga estos pasos con cuidado: Seleccione B5: H10. Haga clic en en la barra de fórmulas para editar la fórmula existente. Escriba + J1 pero no presione Entrar. Para aceptar la fórmula editada, presione Ctrl + Shift + Enter.
El resultado del 8 de mayo de 2012 es:
Estos números son números de serie (el número de días desde el 1/1/1900). Si los formatea como fechas cortas:
Claramente no está bien, pero lo logrará. ¿Qué sucede si los formatea simplemente como "d" para el día del mes?
Casi parece un mes, pero ningún mes comienza con el nueve del mes. Ah, aquí hay un problema. Usó J1 que contiene 5/8/2012, y realmente necesita usar la fecha del primer día del mes. Así que supongamos que pones =DATE(YEAR(J1),MONTH(J1),1)J2:
La celda J1 contiene 5/8/2012 y la celda J2 cambia eso al primero del mes de lo que se ingrese en J1. Entonces, si cambia J1 en la fórmula del calendario a J2:
Más cerca, pero todavía no está bien. Se necesita un ajuste adicional, y es que debe restar el día de la semana del primer día. Es decir, la celda J3 contiene =WEEKDAY(J2). 3 representa el martes. Entonces, si resta J3 de esta fórmula, obtiene:
¡Y eso es lo correcto para mayo de 2012!
Está bien, estás muy cerca. Lo que todavía está mal es que los días 29 y 30 de abril aparecen en el calendario de mayo, y también aparecen del 1 al 9 de junio. Necesitas borrar estos.
Puede darle un nombre a la fórmula para facilitar la referencia. Llámalo "Cal" (todavía no es "genial"). Vea esta figura:
Luego puede cambiar la fórmula para que simplemente sea =Cal(aún Ctrl + Shift + Enter):
Ahora puede cambiar la fórmula para leer que si el resultado está en la fila 5 y el resultado es superior a 20, digamos, entonces ese resultado debe estar en blanco. La fila 5 contendrá la primera semana de cualquier mes, por lo que nunca debería ver ningún valor superior a 20 (o cualquier número superior a siete sería incorrecto; un número como 29 que ve en la celda B5 de la figura anterior es del mes anterior). Entonces puedes usar =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):
Primero, observe que las celdas B5: D5 están en blanco. La fórmula ahora dice "si esta es la fila 5, entonces si el DÍA del resultado es mayor de 20, muestre en blanco".
Puede continuar eliminando los números bajos al final, los valores del próximo mes. A continuación se explica cómo hacerlo fácilmente.
Edite la fórmula y seleccione la referencia final a "Cal"
Comience a escribir IF (ROW ()> 8, IF (DAY (Cal) <15, "", Cal), Cal) para reemplazar la Cal final.
La fórmula final debe ser
=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))
Presione Ctrl + Shift + Enter. El resultado debería ser:
Quedan dos cosas por hacer. Puede tomar esta fórmula y darle un nombre, "Genial":
Luego usa eso en la fórmula que se muestra aquí:
Por cierto, los nombres definidos se tratan como si estuvieran ingresados en una matriz.
Lo que queda por hacer es formatear las celdas y poner los Días de la semana y el nombre del mes. Entonces ensancha las columnas, aumenta la altura de la fila, aumenta el tamaño de la fuente y alinea el texto:
Luego coloque bordes alrededor de las celdas:
Fusionar y centrar el mes y el año y darle formato:
Luego apague las líneas de cuadrícula y listo:
Este artículo invitado es de Bob Umlas, MVP de Excel. Es del libro Excel Outside the Box. Para ver los otros temas del libro, haga clic aquí.
