Sumar Grupos de n Filas con DESREF

"Tengo un listado con muchos valores y tengo que realizar sumas de varias filas. Estas sumas pueden cambiar, es decir, en ocasiones necesito sumar de 2 en 2 valores; otras de 4 en 4 valores; etcétera. Me gustaría saber si se puede automatizar con fórmulas".

Se puede. Veamos cómo. Partimos del siguiente ejemplo, en el que vamos a manejar una lista de 30 valores:

En el rango B5:B34 generamos un número de orden del 1 al 30:

Habilitamos como entrada de datos el valor de "n", es decir, el valor de "cada cuántas filas" queremos sumar. Para ello disponemos una entrada de datos en las celdas B2 y C2:

Nos situamos en la celda D5 y escribimos la siguiente fórmula:

=SI(RESIDUO(B5;$C$2)=0;SUMA(DESREF(C5;;;-$C$2));"")

y la copiamos hasta el final de la lista de valores (en nuestro caso hasta la celda D34).

La primera parte de la fórmula comprueba si el número de orden es múltiplo del valor de n (el valor introducido en C2). Si no lo es, el residuo del cociente no será cero y, por lo tanto, no pondrá nada, ya que es lo que le hemos indicado en la segunda parte del condicional (que escriba ""). Si el número de orden es múltiplo del valor introducido en C2, entonces el residuo del cociente será cero y ejecutará la parte de la fórmula:

SUMA(DESREF(C5;;;-$C$2))  

En nuestro ejemplo, en la celda D7 excel se encontrará que el número de orden de esa fila es el 3 y que dicho número sí es múltiplo del introducido en C2, que es 3. Al estar 2 celdas más abajo que la fórmula original ésta se habrá transformado en (la parte que hace referencia a la suma) SUMA(DESREF(C7;;;-$C$2))  que significa que sume tres celdas hacia arriba desde C7 (incluyendo C7). Es hacia arriba porque al 4º argumento de la función DESREF, Alto, le hemos puesto signo negativo. Veamos el resultado con distintos valores en la celda C2 (valores de n):

Series con Repetición

"Necesito generar una serie, con incremento de uno en uno, que repita los valores un determinado número de veces. Por ejemplo 1-1-1, 2-2-2, 3-3-3, 4-4-4, etc o, por indicar otro ejemplo, 4-4-4-4-4, 5-5-5-5-5, 6-6-6-6-6, etc".

Para resolver este problema, utilizaremos una única fórmula con las funciones SI, CONTARA y RESIDUO.

Partimos del siguiente modelo:

En la celda C3 tenemos el número de veces que queremos que se repita cada valor, y en E3 el número en el que queremos que comience la serie.

Nos situamos en la celda E4 y escribimos la siguiente fórmula (que copiamos hacia abajo hasta la celda que necesitemos en función del tamaño de la serie que queramos generar):

=SI(RESIDUO(CONTARA($E$3:E3);$C$3)=0;E3+1;E3)

Intercalar 1 Fila en Blanco cada n Filas (sin macros)

"Necesito dejar una fila en blanco cada 4 filas, es decir, que aparezcan 4 registros y en el quinto que incorpore una fila en blanco, y así sucesivamente hasta un total de 5.000 registros ¿Hay alguna manera de hacerlo sin hacer uso de macros?".

Para evitar solucionarlo de manera manual, lo que nos llevaría bastante tiempo, existe una manera razonablemente sencilla y razonablemente automática y que no requiere de programación. Partimos de una tabla con distintos datos. En nuestro ejemplo utilizaremos 50 registros:

Vamos a manejar dos hojas dentro del archivo para mayor seguridad. La tabla de la imagen la tenemos en la hoja 2. Lo primero que debemos hacer es crear un número de orden para los registros. Para ello escribimos un 1 en A3, un 2 en A4 y seleccionamos ambos (A3:A4). Hacemos doble clic en la parte inferior derecha del rango seleccionado y de esta manera rellenamos la serie hasta el último dato. 

Nos vamos ahora a la HOJA1. Preparamos la siguiente entrada de datos:

En D1 introducimos el número de fila que debe quedar en blanco. Si queremos dejar en blanco la quinta fila de nuestra tabla escribiremos un 5. En D2 introducimos el valor del último dato de nuestra tabla de la HOJA2. En nuestro ejemplo hay 50 registros y, por lo tanto, el valor será 50.

En E4 vamos a calcular cuántas filas se van a insertar en total. La fórmula es:

=SI(RESIDUO(D2;D1-1)=0;(D2/(D1-1))-1;TRUNCAR(D2/(D1-1)))

En E5 calculamos cuál será el último número de nuestra nueva lista. La fórmula es:

=E4+D2  es decir, el número de filas inicial más las que se van a insertar.

Ahora a partir de la celda C8 generamos nuevamente la serie comenzando con el número 1 y acabando con el 50 (igual que hicimos en la hoja 2).

En D8 escribimos un 1. En D9 la siguiente fórmula:

=SI(RESIDUO(D8+1;$D$1)=0;D8+2;D8+1)  y hacemos doble clic para completar la Nueva Lista.

En E8 la siguiente fórmula:

=SI($D$1*C8<=$E$5;$D$1*C8;"- - -")   y hacemos doble clic para completar la Lista de Múltiplos. El resultado será el siguiente:

Copiamos la Nueva Lista y la pegamos COMO VALORES en la HOJA2 encima del rango A3:A52  y obtenemos lo siguiente:

Ya estamos terminando... Ahora volvemos a la hoja 1 y seleccionamos el rango que contiene números en nuestra Lista de Múltiplos. En nuestro caso el rango E8:E19 de la HOJA1. Pulsamos Copiar y vamos a la HOJA2 y lo pegamos COMO VALORES a continuación del último dato, es decir, a partir de la fila 53.

Ya sólo nos queda ir a Ordenar y filtrar y seleccionar Orden Personalizado. En la ventana que se abre seleccionamos Ordenar por la Columna A de menor a mayor:

Aceptamos y... problema resuelto! (podríamos ahora borrar los número de orden generados en la hoja2 en la columna A):

Aunque pueda parecer un poco tedioso, una vez realizada la plantilla no lleva más de 30 segundos resolver cada caso que se nos plantee.

Validación de Múltiplos

"Necesitamos que al realizar la introducción de cantidad en la Entrada de Datos sólo permita introducir cantidades múltiplos de un determinado número (por ejemplo 50)".

¡¡Lo primerísimo hoy es felicitar a mi maravillosísima mujercita que está de cumpleaños!!

Y ahora a trabajar un poquito que buena falta nos hace... Este problema se puede solucionar de diferentes maneras. Vamos  a resolverlo con dos fórmulas distintas:

1. Solución con Validación de Datos y la función REDOND.MULT

Partimos del siguiente ejemplo:

Lo que queremos es que excel nos permita introducir cantidades en C2 múltiplos de la indicada en E3. Para ello nos situamos en la celda C2 y vamos a la ficha Datos y seleccionamos Validación de datos. En Permitir seleccionamos Personalizada. En Fórmula escribimos la siguiente:

=C2=REDOND.MULT(C2;E3)

Pulsamos Aceptar y ya está. A partir de este momento, y como se puede ver en las siguientes imágenes, excel sólo permitirá introducir en C2 números múltiplos de aquel que indiquemos en la celda E3. En nuestro ejemplo sólo permitirá múltiplos de 50, esto es, 50, 100, 150, 200, etcétera.

La función REDOND.MULT redondea un número al múltiplo deseado. Su sintaxis es REDOND.MULT(numero;multiplo) donde numero es el argumento del valor que se quiere redondear y multiplo el argumento del múltiplo al que se quiere redondear el número.

2. Solución con Validación de Datos y la función RESIDUO 

Seguimos exactamente los mismos pasos pero la fórmula que escribimos en la Validación de datos es:

=RESIDUO(C2;E3)=0

La sintaxis de la función RESIDUO es RESIDUO(número; núm_divisor) Esta función devuelve el residuo o resto de la división entre número y núm_divisor. Cuando un número es múltiplo de otro, al dividirlos su resto o residuo es cero, que es precisamente lo que contrastamos con la fórmula propuesta.

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á:

Formato de Celda Tipo DNI

Descargar el Archivo

"Todos los días tengo que introducir numerosos datos referentes al DNI de clientes. Me gustaría introducir en la celda una entrada del tipo 33444555b y que automáticamente se transformara en 33.444.555-B"

El problema tiene solución "razonablemente fácil" mediante fórmula en una nueva columna. Pero no podemos resolverlo directamente con Formato de Celda Personalizado porque excel reconoce números o textos pero no ambos a la vez.

Voy a proponer dos planteamientos distintos, a saber:

1. Tenemos dos columnas de entrada de datos. En la primera introducimos el número del DNI y en la segunda la letra

2. Tenemos una sola celda de entrada donde escribimos directamente el número y letra del DNI.

Aclaración: Los DNI utilizados en los siguientes ejemplos son inventados y, por lo tanto, no tienen por qué corresponderse con la letra adecuada. Al final de este post incluyo una fórmula para obtener la letra correspondiente a cada DNI.

Planteamiento 1:

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

=TEXTO(B4;"#.##0-")&MAYUSC(C4)

(Consultar la función TEXTO) Es importante destacar que el formato establecido dentro de la función TEXTO entre comillas concluye con un guión al final.

El símbolo & corresponde a la función CONCATENAR.

Finalmente la función MAYUSC convierte un texto a mayúsculas. El resultado será:

Planteamiento 2:

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

=TEXTO(IZQUIERDA(B10;LARGO(B10)-1);"#.##0-")&MAYUSC(DERECHA(B10;1))

Evidentemente la cosa se ha complicado un poco. El motivo es que pueden existir DNI con distintos números de dígitos. Empezando por el final:

MAYUSC(DERECHA(B10;1)) Esta parte de la fórmula convierte en MAYÚSCULA el caracter(uno solo) que se encuentre a la DERECHA del dato introducido en B10. Es decir, que extraemos la letra del final y la ponemos en mayúscula.

Ahora nos queda extraer los dígitos que compongan el número del DNI y darles el formato adecuado. Para extraerlos utilizamos la función LARGO anidada en la función IZQUIERDA. La función LARGO nos indica el número de caracteres que hay en una celda. Si por ejemploescribieramos en una celda la fórmula =LARGO ("hola") el resultado sería 4, que son el número de caracteres de dicha palabra. La parte de nuestra fórmula LARGO(B10)-1 nos devolverá el número total de caracteres existentes en B10 menos 1 (este menos 1 es para restarle la letra). Este número restante será el número de dígitos que compongan el DNI. Ya sólo nos queda extraer dicho número con la función IZQUIERDA: IZQUIERDA(B10;LARGO(B10)-1) y trabajo casiresuelto. Digo casi porque nos faltaría darle el formato adecuado, tarea que realizaremos, como ya sabemos, con la función TEXTO: TEXTO(IZQUIERDA(B10;LARGO(B10)-1);"#.##0-")

A estas alturas de la película ya tenemos el número por un lado y la letra por otro. Los unimos con el símbolo & (CONCATENAR) y trabajo resuelto.

Fórmula para el cálculo de la letra del DNI:

Con la siguiente fórmula puede obtener la letra correspondiente a un DNI determinado:

=ELEGIR(RESIDUO(B4;23)+1;"T";"R";"W";"A";"G";"M";"Y";"F";"P";"D";"X";"B";"N";"J"; "Z";"S";"Q";"V";"H";"L";"C";"K";"E";"T")

Haciendo uso de esta fórmula en nuestro primer planteamiento podríamos resolver directamente, sin necesidad de introducir la letra correspondiente en la columna C, de la forma: (pruebe a escribirlo en la celda, por ejemplo, E4)

=TEXTO(B4;"#.##0-")&

ELEGIR(RESIDUO(B4;23)+1;"T";"R";"W";"A";"G";"M";"Y";"F";"P";"D";"X";"B";"N";"J"; "Z";"S";"Q";"V";"H";"L";"C";"K";"E";"T")

Suma Columnas Pares con Rango Dinámico

Descargar Archivo

Por acabar de "rizar el rizo" vamos a ver como podemos sumar el contenido de las columnas pares en base al mes que introduzcamos en nuestra entrada de datos. Siguiendo con el ejemplo del artículo anterior tenemos las ventas mensuales y el porcentaje que representan sobre el presupuesto semestral. Queremos calcular la suma acumulada hasta el mes que le indiquemos.

Haciendo "zoom":

Como se puede apreciar en la imagen, he puesto como entrada de datos el mes (B3) hasta el cuál queremos calcular la suma acumulada de las columnas pares. Hecho esto nos situamos en O6 y escribimos la siguiente fórmula matricial:

{=SUMA((RESIDUO(COLUMNA(DESREF(B6;;;;$B$3*2));2)=0)*(DESREF(B6;;;;$B$3*2)))}

Como se puede ver he introducido como dinámico el argumento de la función COLUMNA, ya que la suma que debe realizar dependerá del mes que introduzcamos en B3. En la siguiente imagen puede comprobar que cuando modificamos el número del mes en B3 e introducimos, por ejemplo, el 3 (marzo) el cálculo del total acumulado cambia también adaptándose al nuevo rango:

Suma de Columnas Pares con Fórmula Matricial

A raíz del artículo "Suma de filas Impares" me habéis preguntado en varias ocasiones si se puede resolver dicho problema con una única fórmula. La respuesta es sí. Para ello debemos hacer uso de las fórmulas matriciales. Supongamos que tenemos el siguiente modelo:

Para que se vea mejor hagamos "zoom" sobre las primeras celdas:

Como se puede comprobar, tenemos la cifra de ventas de cada mes del primer semestre y a continuación el porcentaje que representa sobre el presupuesto para dicho semestre ¿Podemos realizar una única fórmula en O4 que realice el sumatorio de los seis meses? Sí. A saber:

{=SUMA((RESIDUO(COLUMNA(B4:M4);2)=0)*(B4:M4))}

Recuerde que por tratarse de una fórmula matricial al acabar de escribir dicha fórmula NO debemos pulsar Enter sino Ctrl+Shift+Enter.

Evidentemente esta misma fórmula la podemos aplicar para sumar columnas impares sustituyendo el cero por un uno como argumento de la función RESIDUO.

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:

Sombrear Filas y/o Columnas de una Tabla

Respondiendo a una pregunta que me habéis realizado, vamos a ver hoy como podemos "sombrear" las filas y/o columnas pares de una tabla para diferenciarlas visualmente de las impares. La solución es muy sencilla utilizando la herramienta Formato Condicional y las funciones SI, RESIDUO, FILA, y COLUMNA.

Supongamos que tenemos la siguiente tabla, donde podemos encontrar el número de alumnos clasificados por ciudad y por trimestre:

Queremos que las filas pares de la tabla aparezcan sombreadas y que las impares se mantengan en el formato original. La solución de "andar por casa" será seleccionar manualmente las filas pares de la tabla y "colorearlas" como se desee. Esta solución será la más eficiente si tenemos una tabla pequeña con pocas filas. Pero qué ocurre si nuestra tabla tiene, por ejemplo, 200 filas...

para solucionar este planteamiento tendremos que seguir los siguientes pasos:

1. Seleccionamos el rango C5:F15

2. Vamos al menú Formato/Formato condicional.

3. En el primer cuadro seleccionamos Fórmula (en vez de valor de la celda).

4. En el siguiente cuadro escribimos la fórmula:

=SI(RESIDUO(FILA(C5);2);0;1)

La función Fila nos devuelve el número de fila de la celda que le indiquemos

La función Residuo tiene dos argumentos, a saber, número y número divisor. Esta función nos devuelve el resto entre número y número divisor. En nuestro caso el argumento número será el número de fila. El argumento número divisor será 2. De esta manera es muy fácil saber qué filas son pares y cuáles impares. Como ya se habrá dado cuenta, al utilizar como divisor el número 2, el resto será siempre cero cuando estemos en una fila par y 1 cuando estemos en una fila impar.

Anidando estas funciones dentro de un condicional (SI) el problema está resuelto:

Si lo que queremos conseguir es sombrear las columnas pares en vez de las filas, entonces la fórmula a aplicar sería:

=SI(RESIDUO(COLUMNA(C5);2);0;1)