jueves, 22 de enero de 2015

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)))

4 comentarios:

  1. Otra manera que creo que es más fácil

    Nombrar el rango de datos como “Datos” (muy original).

    Copiar y arrastrar estas fórmulas en columnas sucesivas.

    =INDICE(Datos;FILA(A1)*2-1;1)

    =INDICE(Datos;FILA(A1)*2;1)

    =INDICE(Datos;FILA(A1)*2-1;2)

    =INDICE(Datos;FILA(A1)*2;2)

    Opcionalmente, añadir a las fórmulas SI.ERROR = “” para evitar problemas de desbordamiento. La primera fórmula quedaría así (el resto es igual):

    =SI.ERROR(INDICE(Datos;FILA(A1)*2-1;1);"")

    Enhorabuena por el blog.

    Daniel

    ResponderEliminar
  2. Muchisimas gracias, habia pasado toda la noche buscando una solucion, en todos los sitios publica puro codio VB pero uno que no tiene idea de programacion esta perdido, tu explicacion fue clara, sencilla y facil de implementar. Muchisimas gracias.

    ResponderEliminar