Número de Días de un Mes

"Tengo una tabla con fechas y para periodificar unos datos necesito que a la derecha de la fecha me aparezca el número de días que tiene el mes de dicha fecha ¿Alguna solución sencilla?"

Por supuesto no problemo. Veamos el ejemplo de partida:

La solución es bien sencilla utilizando las funciones DIA y FIN.MES . En concreto vamos a anidar FIN.MES dentro de la función DIA. Seguimos los siguientes pasos:

1. En la celda C3 escribimos la siguiente fórmula:

=FIN.MES(B3;0)

La función FIN.MES tiene dos argumentos: Fecha_inicial y Meses. Fecha_inicial es el número de serie que representa la fecha inicial; Meses es el número de meses antes o después de la Fecha_inicial. En nuestro caso la fecha inicial es la fecha en cuestión que nos interesa y, por lo tanto, hay que añadirle cero meses. De esta manera la fórmula indicada calculará el último día del mes de la celda B3. Si ahora anidamos esta fórmula dentro de la función DIA (que nos devuelve el número de día de una fecha en concreto) ya tendremos el número de días del mes de la fecha en cuestión.

2. En la celda C3 añadimos a la fórmula que teníamos la función DIA. Debe quedar como sigue:

=DIA(FIN.MES(B3;0))

3. Nos situamos en la parte inferior derecha de la celda C3 y hacemos doble clic (copiado inteligente) para rellenar el resto de celdas. El resultado obtenido será el buscado:

Truco en el Copiado Inteligente por Bloques

Descargar el Archivo

"Tengo una tabla con datos de ingresos y gastos mensuales. Necesito calcular el margen bruto bimestral. He utilizado el sistema de copiado inteligente por bloques. A continuación he necesitado añadir una nueva columna de reporte con el mismo dato del margen bruto pero en porcentaje. El problema es que como las primeras celdas de la izquierda están en blanco no funciona el copiado inteligente por bloques ¿Hay alguna manera de solucionarlo?"

Hay una manera muy sencilla y rápida. Para entender mejor el problema recomiendo la lectura del post Copiado Inteligente . Partimos del siguiente ejemplo:

Queremos añadir en la columna F el dato del margen bruto en porcentaje. Para ello realizamos los siguientes pasos:

1. En la celda F4 escribimos la fórmula =E4/C4

2. Si quisiéramos aplicar el copiado inteligente por bloques seleccionaríamos el rango F3:F4 y haríamos doble clic en la parte inferior derecha de la selección. Si lo hacemos no ocurre nada ya que la celda E3 está en blanco. Y este es precisamente el problema que me planteáis.

3. SOLUCIÓN: Ocultamos la columna E.

4. Seleccionamos el rango F3:F4 y ahora sí aplicamos el copiado inteligente por bloques como indico en el punto 2:

5. Volvemos a mostrar la columna E y problema resuelto:

Cálculo Repetición de un Día de la Semana entre Fechas

Descargar el Archivo

"Necesito saber cuántos domingos hay entre dos fechas determinadas, siendo, evidentemente, dichas fechas variables".

No existe ninguna función específica para resolver directamente este problema, por lo que tendremos que realizar algunos pasos y fórmulas. Vamos a desarrollar el modelo para calcular cuántos domingos, o cualquier otro día de la semana que queramos especificar, hay entre dos fechas determinadas.

Lo primero que vamos a realizar es un calendario diario de tos el año 2009. Evidentemente si las fechas que necesitemos manejar se encuentran entre 2009 y 2010, por ejemplo, entonces deberíamos realizar dicho calendario diario. Para ello nos situamos en la celda G1 y escribimos 1/1/2009. En G2 escribimos la fórmula =G1+1 y copiamos dicha fórmula hasta que aparezca el 31/12/2009 (que terminará lógicamente en la fila 365):

Como se puede apreciar en la imagen le hemos dado el formato de fecha en el que aparece también el nombre del día de la semana. Seleccionamos el rango H1:H365 y le damos el nombre tablafecha.

Preparamos ahora la entrada de datos de la forma que se muestra en la imagen e introducimos dos fecha, por ejemplo del 7 de Febrero al 15 de Mayo:

A continuación nos situamos en la celda H1 y escribimos la siguiente fórmula:

=RESIDUO(G1;7)

Hacemos doble clic en la parte inferior derecha de la celda H1 (copiado inteligente) y la fórmula se copiará hasta la fila 365. Si nos fijamos en esta nueva columna calculada la función RESIDUO nos devuelve el mismo resultado para cada uno de los días de la semana. A saber:

Preparamos la siguiente tabla:

Seleccionamos el rango J2:K8 y le damos el nombre diaresiduo.

Nos situamos en C4 y vamos a Datos/Validación. Seleccionamos permitir Lista y en Origen seleccionamos el rango J2:J8. De esta manera ya tendremos nuestra lista desplegable en la celda C6 para elegir el día de la semana.

En la celda B6 escribimos la fórmula =C4

A continuación necesitamos realizar algunos "cálculos intermedios" para llegar a nuestra solución. Para ello preparamos las siguientes celdas:

Nos situamos en K12 y vamos a calcular la fila de la tabla de fechas en la que se encuentra la fecha inicial. Para ello escribimos la siguiente fórmula:

=COINCIDIR(C2;tablafecha)

en K13 escribimos (para calcular la fila con la que se corresponde la fecha final):

=COINCIDIR(C3;tablafecha)

Ya tenemos la fila que se corresponde con las fechas de inicio y de fin introducidas en C2 y C3. Ahora, en K14, concatenamos estos resultados para generar un rango:

="H"&K11&":"&"H"&K12

Fíjese que la letra de la columna la hemos introducido como dato (no la calculamos) porque todas las fechas se encuentran en la columna H. El resultado de la fórmula indicada será el texto: H38:H135. También podríamos escribir estas tres fórmulas que acabamos de realizar en una sola:

="H"&COINCIDIR(C2;tablafecha)&":"&"H"&COINCIDIR(C3;tablafecha)

Para calcular cuántos, por ejemplo, domingos hay entre las fechas 7 de febrero y 15 de mayo, sólo nos queda contar cuántas veces se repite entre dichas fechas el número de residuo

Una vez hecho esto, nos situamos en la celda C6 y escribimos la siguiente fórmula que explicamos a continuación:

=CONTAR.SI(INDIRECTO(K16);"="&BUSCARV(C4;diaresiduo;2;FALSO))

La función CONTAR.SI nos permite realizar la cuenta en un rango de aquellas celdas que cumplan una determinada condición. El rango que nos interesa (en función de las fechas introducidas) es el que tenemos en la celda K16. El problema es que en K16 tenemos un texto que representa a un rango. Para convertir dicho texto en referencia valida para excel debemos utilizar la función INDIRECTO. De esta manera INDIRECTO(K16) es lo mismo que si introdujéramos manualmente el rango H38:H135 .

Una vez tenemos el rango considerado nos queda establecer la condición que debe cumplir para que excel proceda a contar. La manera de hacer esto es poniendo la condición entre comillas. Por ejemplo, si queremos que cuente el número de celdas del rango H38:H135 que son igual a 1 escribiríamos =CONTAR.SI(H38:H135;"=1") . Ya hemos visto como resolver la parte del rango de manera automática (con la función INDIRECTO). Si dejáramos la solución así sólo podríamos calcular el número de domingos entre dos fechas. Como queremos tener la posibilidad de calcular el número de repeticiones de cualquier día de la semana entre dos fechas, necesitamos dejar como variable la condición. En realidad sólo necesitamos dejar como variable el residuo de la condición. Como ya hemos visto, cada día de la semana se corresponde con un residuo. Dicha información la tenemos en la tabla que hemos llamado diaresiduo (J2:K8). Por lo tanto podemos asociar el día de la semana seleccionado en la celda C4 con el número de residuo de dicho día). Esto es lo que hace la parte de la fórmula BUSCARV(C4;diaresiduo;2;FALSO)

El resultado será:

Contar Fechas

Hoy no puedo estar más feliz... Mi hijo Miguel cumple 2 añitos (todo un personaje...)

¡¡ FELIZ CUMPLEAÑOS HIJO !!

"Tengo una lista de fechas con formato 27/11/2009 y quiero contar el número de fechas de mi lista que pertenecen a un determinado mes"

Partimos del siguiente ejemplo:

Una manera sencilla de solucionar este problema es con Tablas Dinámicas:

1. Nos situamos en E3 y vamos a Datos/Informe de Tablas y gráficos dinámicos:

2. Pulsamos Siguiente.

3. Seleccionamos el rango E2:E22:

4. Seleccionamos Diseño:

5. Arrastramos el campo Fecha a la zona DATOS y también a FILA. Aceptamos y pulsamos Finalizar:

6. Obtendremos la siguiente Tabla Dinámica:

7. Nos situamos en cualquier celda del rango A5:A24 y pulsamos el botón derecho del ratón. En el menú emergente seleccionamos Agrupar y mostrar detalle/Agrupar. En la ventana que aparecerá realizamos la siguiente elección y aceptamos:

8. El resultado obtenido será el objetivo buscado:

Solución con FÓRMULAS

1. Nos situamos en F3 y escribimos la fórmula: =MES(E3)

2. Hacemos doble clic en la esquina inferior derecha de la celda F3 (Copiado inteligente) y obtendremos el siguiente resultado:

3. Preparamos la siguiente entrada de datos en B2:C2 para introducir el número de mes que deseamos contar (puede incluir un Control de número en C2 para facilitar el uso). Nos situamos en C4 y escribimos la fórmula que puede ver en la barra de fórmulas de la imagen:

De esta manera obtendremos el número de veces que se repite en nuestra tabla el mes que escribamos en C2.

Búsqueda de los Meses de Más y Menos Ventas

Descargar el Archivo

"Tengo la siguiente tabla con las cifras de ventas mensuales de los últimos 6 años. Me gustaría saber cuál fue la cifra máxima y mínima de ventas de cada año y en qué mes ocurrieron".

La solución es bastante sencilla utilizando las funciones: MAX, MIN, BUSCARH y COINCIDIR. A saber:

1. Diseñamos la siguiente salida de datos:

2. Nos situamos en B11 y escribimos la siguiente fórmula:

=MAX($B3:$M3)

3. Copiamos la anterior fórmula y la pegamos en D11 y sustituimos la función MAX por MIN:

=MIN($B3:$M3)

4. Nos situamos en B1 y escribimos un 1. En C1 un 2. Seleccionamos estas dos celdas y rellenamos hasta M1, con lo que obtenemos una serie de números del 1 al 12.

5. Seleccionamos el rango B1:M2 y vamos al Cuadro de nombres (a la izquierda de la barra de fórmulas) y escrinbimos el nombre meses. Pulsamos enter.

6. Nos situamos en la celda C11 y escribimos la siguiente fórmula:

=BUSCARH(COINCIDIR(B11;$B3:$M3;0);meses;2;FALSO)

La función COINCIDIR nos devolverá en qué número de columna se encuentra el valor máximo (B11). El número se encontrará entre 1 y 12, ya que es el número de columnas que abarca el rango consultado (B3:M3). Una vez tenemos este número lo aplicamos a la "función hermana" de BUSCARV, que es BUSCARH. Esta función realiza el mismo trabajo que BUSCARV pero en vez de realizar la búsqueda verticalmente lo hace horizontalmente (por filas).

7. Copiamos la fórmula de C11 en E11.

8. Seleccionamos el rango B11:E11 y hacemos doble clic en la esquina inferior derecha (Copiado inteligente). Trabajo terminado:

Cálculos con Rangos Tridimensionales

Descargar el Archivo

En el artículo de hoy vamos a ver cómo podemos trabajar con lo que se denomina Rangos Tridimensionales en Excel. Un Rango Tridimensional lo podemos definir como aquel rango que se extiende a varias hojas. Supongamos, por ejemplo, que tenemos en varias hojas las ventas relativas a varios comerciales en distintas zonas:

En la hoja Zona1 tenemos:

En la hoja Zona2 tenemos:

En la hoja Zona3 tenemos:

En la hoja Zona4 tenemos:

Finalmente tenemos una hoja RESUMEN donde queremos recoger las ventas totales de cada comercial y el promedio de cada uno de ellos:

Para realizar estos cálculos NO es necesaria la siguiente fórmula en B3:

=Zona1!B3+Zona2!B3+Zona3!B3+Zona4!B3

Para solucionar este cálculo de una manera más sencilla y rápida debe hacer lo siguiente:

1. Nos situamos en la hoja RESUMEN en la celda B3 y escribimos =SUMA(

2. A continuación hacemos clic en la hoja Zona1 y en la celda B3.

3. A continuación pulsamos SHIFT (Mayús) y manteniendo dicha tecla pulsada hacemos clic en la última hoja, es decir, en la hoja Zona4, y pulsamos enter.

El resultado es la siguiente fórmula:

=SUMA(Zona1:Zona4!B3)

Como puede comprobar estamos sumando un rango que se extiende a las hojas zona1, zona2, zona3 y zona4. Esto es lo que se denomina un rango tridimensional en excel.

4. Nos situamos en C3 de la hoja RESUMEN y escribimos =PROMEDIO( y repetimos los mismos pasos que en el caso anterior para solucionar esta fórmula:

=PROMEDIO(Zona1:Zona4!B3)

5. Seleccionamos B3 y C3 y hacemos doble clic en la parte inferior derecha de dicha selección (copiado inteligente) para rellenar todas las fórmulas:

La ayuda de Excel nos indica qué funciones pueden utilizarse con referencias 3D. A saber: "Pueden utilizarse referencias 3D a las celdas de otras hojas para definir nombres y crear fórmulas mediante las siguientes funciones: SUMA, PROMEDIO, PROMEDIOA, CONTAR, CONTARA, MAX, MAXA, MIN, MINA, PRODUCTO, DESVEST, DESVESTA, DESVESTP, DESVESTPA, VAR, VARA, VARP y VARPA. No pueden utilizarse referencias 3D en fórmulas matriciales".

Como es habitual en las "ayudas" de excel no nos cuenta toda la verdad... De hecho hay muchas más funciones que permiten referencias 3D. Estas son algunas que he comprobado personalmente (y seguro que hay más) y que no aparecen en el listado de la ayuda:

CUARTIL, K.ESIMO.MENOR, K.ESIMO.MAYOR, JERARQUÍA, Y, O, PERCENTIL.

Suma de Filas Impares (o Pares)

Descargar el Archivo

En un artículo anterior vimos como se podía dar formato a filas y/o columnas pares (o impares) de una tabla. En esta ocasión me habéis planteado el siguiente caso:

"Tengo una tabla donde registro las ventas relativas a más de 150 referencias que se venden en 10 áreas. Debajo de la cifra de ventas de cada referencia tengo el porcentaje que supone respecto a las ventas totales por área. El problema es que precisamente para calcular las ventas totales no puedo aplicar la Autosuma porque sólo me interesaría sumar las filas donde están las cifras de venta y no las de los porcentajes".

Resumiendo el problema en 10 referencias y 3 áreas (la solución es exactamente la misma), la situación de partida sería la siguiente:

Y queremos calcular las ventas totales de cada área y colocarlas, por ejemplo, en B25:D25.

1. Nos situamos en E5 y escribimos la siguiente fórmula:

=RESIDUO(FILA(B5);2)

La función FILA devuelve el número de fila de la celda en cuestión. FILA(B5) devolverá 5 que es el número de fila de dicha referencia. La función RESIDUO, por su parte, calcula el resto resultante de dividir un número (que en nuestro caso será el número de fila) y el divisor (que en nuestro caso es 2). El resto de cualquier número impar dividido por 2 siempre es 1. Si el número es par el resto será siempre 0.

2. Copiamos la fórmula de E5 hasta E24 (o utilizamos el copiado inteligente y hacemos doble clic en la parte inferior derecha de la celda E5).

3. Ya tenemos identificadas las filas pares y las impares. Nos situamos en B25 y escribimos la siguiente fórmula:

=SUMAR.SI($E$5:$E$24;1;B5:B24)

De esta manera estamos comprobando que celdas del rango E5:E24 valen 1 (que serán precisamente las filas impares) y le estamos pidiendo que sume las filas del rango B5:B24 que cumplan previamente este criterio.

4. Copiamos la fórmula de B25 en C25 y D25.

5. Ocultamos la columna E para que no se vean los unos y ceros que hemos utilizado para identificar las filas pares e impares. El resultado será el siguiente:

El 100% que aparece en el rango B26:D26 es la suma de las filas pares. Puede calcularlo copiando las fórmulas de B25:D25 hacia abajo y sustituyendo el criterio con valor cero por un uno.

Si además quiere calcular el sumatorio de cada referencia para las tres áreas puede resolverlo directamente con el copiado inteligente por bloques (que ya vimos en otro artículo). Para ello debe hacer lo siguiente:

1. Nos situamos en la celda F5 y calculamos la primera suma, es decir, =SUMA(B5:D5)

2. Seleccionamos las celdas F5:F6 y hacemos doble clic encima del pequeño cuadrado negro que aparecerá en la parte inferior derecha de dicha selección y... problema resuelto:

Copiado Inteligente

Descargar el Archivo

Hoy explicaré una funcionalidad de Excel que, me temo, muchos usuarios habituales desconocen: el Copiado Inteligente. Como veremos a continuación, se trata de una funcionalidad muy sencilla pero tremendamente práctica. Partamos como siempre de un ejemplo:

Supongamos que tenemos los ingresos y gastos de una pequeña empresa durante los doce meses del año y queremos calcular el margen mensual en euros, en porcentaje y, finalmente, el margen trimestral (en euros): 

Estará de acuerdo conmigo que la formulación de este modelo no puede ser más sencilla:

1. Nos situamos en E3 y escribimos la fórmula:

=C3-D3

2. El siguiente paso que van a realizar un alto porcentaje de usuarios de Excel es situarse nuevamente en la celda E3 y colocar el puntero encima del pequeño cuadrado negro que se encuentra en la parte inferior derecha de la celda para "arrastrar" hasta E14 y de esta manera copiar la fórmula realizada. NO ES NECESARIO. Simplemente ponga el puntero encima del citado pequeño cuadrado negro y haga DOBLE CLIC encima de éste. Automáticamente Excel copiará su fórmula hasta E14. Esto es el Copiado Inteligente.

¿Cómo funciona el Copiado Inteligente?

Cuando usted hace el doble clic al que me acabo de referir ¿cómo sabe Excel hasta donde debe copiar? Realmente no lo sabe. Lo que hace es rellenar hacia abajo (la fórmula existente en la celda) hasta que en las celdas adyacentes de la izquierda aparezca una celda en blanco. En nuestro ejemplo, si no tuviéramos cifra de gastos en, por ejemplo, junio, Excel detendría el copiado en la celda E7, ya que en D8 se encontraría la primera celda adyacente a la izquierda en blanco:

Cuando la celda adyacente de la izquierda está en blanco, Excel "mira" a la celda adyacente de la derecha para utilizarla como guía. Pero siempre lo hace en este orden indicado: primero comprueba la celda adyacente de la izquierda y sólo si se encuentra en blanco se fija en la celda adyacente de la derecha. En caso de tener contenido ambas se seguirá fijando en la de la izquierda (y, por supuesto, si están ambas en blanco pues no hará nada). 

Ya ha visto como utilizar el copiado inteligente para rellenar con un doble clic una fórmula ¿Se podría utilizar para varias fórmulas a la vez? Sí. Haga lo siguiente para comprobarlo:

3. Borre el contenido del rango E4:E14 (mantenga la fórmula realizada en E3)

4. Vaya en la celda F3 y escriba la siguiente fórmula para calcular el margen en porcentaje:

=(C3-D3)/C3 

5. Ahora seleccione el rango E3:F3

6. Coloque el puntero encima del cuadrado negro de la parte inferior derecha de la selección

7. Haga doble clic y trabajo terminado...

Copiado Inteligentes "por bloques"

En la columna G de nuestro ejemplo queremos calcular el margen en euros obtenido en cada trimestre. Para calcularlo haremos lo siguiente:

1. Nos situamos en G5 y escribimos la fórmula:

=SUMA(E3:E5)

Hasta aquí nada nuevo. Pero en vez de ponerse a continuación a copiar esta fórmula cada tres meses, es decir, en G8, G11 y G14 (y no me diga que no lo iba a hacer así...) le propongo que haga lo siguiente:

2. Seleccione el rango G3:G5

3. Coloque el puntero en el dichoso cuadradito negro de la parte inferior derecha de la selección.

4. Haga doble clic encima.

Por utilizar uno de los "trucos" vistos en la función SUMA, si ahora desea calcular el total de las columnas C, D y E seleccione el rango C3:E14 y directamente pulse el icono de Autosuma. Automáticamente le aparecerán dichos totales en el rango C15:E15 (puede utilizar también otras funciones como PROMEDIO, MAX, MIN, etcétera, en vez de la SUMA)