Copiar Registros Intercalados

"Tengo cientos de registros relativos a números de unidades vendidas y debajo de cada registro el porcentaje que representa cada uno respecto a la suma total. Necesito copiar un listado pero sólo de las unidades vendidas sin dejar filas intercalas en el medio". 

Tal y como les prometí, este post va dedicado a mis alumnos del Master in Management del IE Business School, que con tanta paciencia me soportan los lunes y martes...

Partimos del siguiente ejemplo:

Lo que queremos conseguir es hacer una sola fórmula que podamos copiar hacia abajo para obtener un listado como el que aparece en la siguiente imagen con las cantidades de cada dato:

Para ello vamos a utilizar varias funciones, a saber: INDIRECTO, DIRECCION, FILA y COLUMNA.

Nos situamos en la celda G4 y escribimos la siguiente fórmula que copiaremos hasta G11:

=INDIRECTO(DIRECCION(FILA(C4)*2-4;COLUMNA(C4)))

La función FILA nos devuelve el número de fila de la celda en cuestión. En nuestro ejemplo FILA(C4) nos devolverá el valor 4. multiplicamos el resultado por 2 para generar una serie de números pares. FILA(C4)*2=8; FILA(C5)*2=10; FILA(C6)*2=12; etc. Como nuestro primer dato se encuentra en la fila número 4 (y no en la 8) tendremos que restarle 4 para que la serie comience en dicho número, en 4: FILA(C4)*2-4=4; FILA(C5)*2-4=6; FILA(C6)*2-4=8; etc. Nuestro datos precisamente se encuentran en las filas 4, 6, 8, etcétera. La columna es siempre la misma COLUMNA(C4). Si colocamos estas funciones dentro de la función DIRECCION, lo que obtendremos es la dirección de las celdas en cuestión, a saber:

=DIRECCION(FILA(C4)*2-4;COLUMNA(C4)) resulta C4

=DIRECCION(FILA(C5)*2-4;COLUMNA(C5)) resulta C6

=DIRECCION(FILA(C6)*2-4;COLUMNA(C6)) resulta C8

=DIRECCION(FILA(C7)*2-4;COLUMNA(C7)) resulta C10, etcétera.

De esta manera ya tenemos las direcciones de las celdas que queremos obtener. Sólo nos falta utilizar una función que transforme el nombre de la celda en el valor que contiene la misma. Esta función es INDIRECTO. 

Aunque el problema ya está resuelto, nos podríamos encontrar un pequeño problema y es que si ahora añadimos filas por encima de la fila 4 entonces dejará de funcionar. Para evitar esto podemos hacer uso del siguiente "truco". Creamos un nombre para el primer dato. Nos situamos en la celda C4, vamos a la lista de nombres (a la izquierda de la barra de fórmulas) y escribimos el nombre, por ejemplo, Dato1 y pulsamos Enter. A partir de ahora la celda C4 se llama Dato1. Nos situamos en G2 y escribimos la fórmula:

=FILA(Dato1)

De esta manera obtendremos el número de fila en el que se encuentra el primer dato  de forma variable. Podemos ahora incorporarlo a nuestra fórmula original y problema resuelto:

=INDIRECTO(DIRECCION(FILA(C4)*2-$G$2;COLUMNA(C4)))

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)