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

Cuadro Combinado con Contenido Variable

Descargar el Archivo

En el post de hoy veremos como realizar cuadros combinados cuyo contenido dependa de lo que seleccionemos en los botones de opción. Para entender mejor nuestro objetivo veamos la siguiente figura:

Lo que queremos conseguir es que una vez seleccionemos la opción deseada en el botón de opción correspondiente, nos aparezca el contenido adecuado en el cuadro combinado.

Los pasos que debemos seguir son los siguientes:

1. Creamos los Botones de Opción (si no recuerda cómo insertar dichos botones consulte Formato Condicional con Botones de Opción) y los vinculamos con la celda D11.

2. Dibujamos el cuadro combinado (véase el post Cálculos con Rangos Dinámicos y Cuadros Combinados) pero todavía no procedemos a configurarlo.

3. Nos situamos en la celda D13 y escribimos la siguiente fórmula:

=DIRECCION(12;D11)&":"&DIRECCION(19;D11)

La función DIRECCION, ya tratada en este blog, crea una referencia de celda con formato de texto, una vez indicada la fila y la columna. En nuestro ejemplo la fila en la que comienzan las tres listas (fijos, eventuales y extras) es la número 12. La columna dependerá de lo que seleccionemos en el botón de opción. Dicho botón lo hemos vinculado con la celda D11 y nos devolverá 1, 2 ó 3. Si por ejemplo seleccionamos la opción Fijos entonces D11 valdrá 1 por lo que la función Direccion nos devolverá como texto la referencia de la columna 1 y fila 12, es decir, A12. De esta manera ya tenemos la celda en la que comienza el rango seleccionado por medio del botón de opción. Ahora tenemos que conseguir la celda en la que concluye dicho rango y eso lo logramos con DIRECCION (19;D11).

Ya tenemos la celda inicial y la celda final de un rango. Para unir ambas utilizamos la función CONCATENAR (o lo que es lo mismo &) y colocamos en medio los dos puntos que identifican un rango:

celda inicial A12

celda final A19

A12&":"&A19

Nos devuelve el rango requerido en forma de texto: A12:A19. Para transformar esta referencia de texto en referencia válida para excel utilizaremos la función INDIRECTO.

4. Vamos al menú Insertar/Nombre/Definir. Escribimos el nombre, por ejemplo, Rango_lista. En el cuadro Se refiere a escribimos la siguiente fórmula y pulsamos Aceptar:

=INDIRECTO($D$13)

5. Nos situamos encima del Cuadro Combinado, hacemos clic en el botón derecho y seleccionamos Formato de Control. En Rango de entrada introducimos Rango_lista (que es precisamente el rango variable que hemos generado) y vinculamos, por ejemplo, con la celda D12. Pulsamos Aceptar.

A partir de este momento cuando pulsemos un botón de opción la lista que aparecerá en el cuadro combinado será la correspondiente al botón seleccionado:

Obtener Datos de una tabla con COINCIDIR y BUSCARV

Supongamos que tenemos una lista de empleados con su número de empleado, su nombre y sus ganancias y queremos saber cuánto gana el que más y el que menos y sus nombres.

Lo solucionaremos de dos maneras diferentes. En nuestra primera solución calcularemos la cifra máxima y mínima de ganancia por medio de las funciones MAX y MIN respectivamente:

1. Nos situamos en la celda F2 y escribimos =MAX(C2:C21)

2. Nos situamos en la celda F3 y escribimos =MIN(C2:C21)

En estos momentos ya tenemos las cifras máximas y mínimas pero no sabemos los empleados a los que corresponden. Como el campo a buscar serán estas cifras no podemos utilizar directamente la función BUSCARV ya que ésta busca siempre en la primera columna de una matriz y nos devuelve un valor de la misma columna o de n columnas hacia la derecha(partiendo de dicha primera columna de la tabla). Lo que hacemos entonces es utilizar la función COINCIDIR, que nos proporciona la posición (no el resultado) que ocupa en una matriz el valor buscado:

3. Nos situamos en la celda G2 y escribimos la fórmula: =COINCIDIR(F2;$C$2:$C$21;0)

4. Copiamos esta fórmula en G3.

De esta manera ya sabemos que el empleado que más gana ocupa la posición 18 del rango C2:C21 y el que menos la posición 14. Como hemos identificado cada empleado con un número en el rango A2:A21 ya sólo nos queda utilizar BUSCARV para obtener sus correspondientes nombres. A saber:

5. Le damos el nombre de "Empleados" a la tabla (costumbre siempre muy recomendable...) seleccionando el rango A2:C21 y yendo al menú Insertar/Nombre/Definir. Escribimos dicho nombre(Empleados) y aceptamos directamente.

6. Nos situamos en la celda H2 y escribimos: =BUSCARV(G2;Empleados;2;Falso)

7. Copiamos esta fórmula en H3.

Hemos "desgranado" la fórmula pero podríamos escribir directamente las dos partes. A saber:

Nos situamos, por ejemplo, en la celda E8 y escribimos:

=BUSCARV(COINCIDIR(F2;$C$2:$C$21;0);Empleados;2;Falso)

Copiamos esta fórmula una celda más abajo y obtendremos los dos nombres buscados.

Otra manera de solucionar este problema es utilizando las funciones DESREF, INDIRECTO y DIRECCION tal y como se muestra en la figura: