Búsqueda de elementos idénticos en distintas tablas

Es muy habitual que la información con la que vamos a trabajar se encuentre dispersa en distintas hojas de un libro o incluso en distintos libros (archivos). Sin entrar en la discusión de cómo se debe plantear correctamente nuestro modelos en Excel para sacarle el mayor rendimiento posible con el mínimo esfuerzo (artículo/s que prometo abordar en este blog) me planteáis la siguiente consulta:

“Dentro de un archivo tengo los nombres de 32 comerciales y 10 hojas distintas con información relativa a sus salarios, ventas, comisiones, etcétera. Cuando deseo realizar un resumen que incluya toda esta información acabo copiando y pegando a mano ¿Hay alguna manera de que, por ejemplo, busquemos los nombres de los comerciales y nos devuelva toda la información relativa a éstos que se encuentre en las distintas hojas?”. 

En este caso que me proponéis, la solución es bastante sencilla, ya que la información sigue un mismo patrón aunque se encuentre dispersa en distintas hojas. A saber: todas las tablas tienen dos columnas, siendo la primera el nombre de los comerciales y dónde la segunda contiene la información sobre ventas, comisiones, etcétera. En nuestro ejemplo vamos a plantear el caso para 10 comerciales y 3 tablas repartidas en 3 hojas distintas: 

1. En la hoja Salarios tenemos nuestra tabla en el rango A6:B16 y lo primero que tenemos que hacer es, precisamente darle nombre al rango. Para ello seleccionamos A7:B16 (no incluimos los nombres de campo), hacemos clic en el cuadro de nombres ( a la izquierda de la barra de fórmulas) y escribimos salarios:

2. En la hoja Ventas tenemos nuestra tabla en el rango A2:B12 y hacemos lo mismo que en el caso anterior: seleccionamos A3:B12, hacemos clic en el cuadro de nombres y  escribimos ventas.

3. En la hoja Comisiones tenemos nuestra tabla en el rango A13:B23 y hacemos lo mismo que en el caso anterior: seleccionamos A14:B23, hacemos clic en el cuadro de nombres y  escribimos comisiones.

4. Nos situamos en la hoja Resumen y preparamos el cuadro (es imprescindible que los nombres de campo (rango B3:D3) sean IDÉNTICOS a los nombres que hemos creado para los distintos rangos):

5. Nos situamos en la celda B4 y escribimos la siguiente fórmula:

=BUSCARV($A4;INDIRECTO(B$3);2;FALSO)

Fíjese que en el segundo argumento de la función BUSCARV (matriz_buscar_en) hemos anidado la función INDIRECTO. El objetivo es aprovechar el nombre del campo, por ejemplo salarios, para que BUSCARV localice en dicha tabla la información deseada. Cuando copiemos la fórmula de B4 hacia la derecha la matriz_buscar_en irá variando con el nombre del campo. Para ello hemos utilizado referencias mixtas fijando la columna para los nombres de los comerciales ($A4) y fijando la fila para los nombres de las tablas (B$3). De esta manera podremos copiar hacia la derecha y hacia abajo la fórmula de B4 y con una única fórmula habremos terminado nuestro resumen. 

 

Ajuste Automático de Rangos para Inserción de Filas

El siguiente ejercicio es bastante sencillo pero se trata de una pregunta que de manera reiterativa me realizan mis alumnos. Cuando tenemos columnas con cifras y justo al final añadimos, como suele ser habitual, el sumatorio total de dichas cifras, si queremos insertar una nueva fila tenemos que hacerlo en el medio de dicha lista para que el sumatorio recoja el nuevo concepto ¿Hay alguna manera de poder añadir nuevas filas al final de una lista y que el sumatorio sea correcto?

Como veremos a continuación la solución es razonablemente sencilla anidando la función DESREF dentro de la función SUMA.

Supongamos que tenemos la siguiente lista de gastos de oficina:

Como se puede ver en la celda C12 hemos calculado el sumatorio de los gastos. Si necesitamos añadir, por ejemplo, dos nuevos conceptos a continuación de la última referencia entonces tendremos que replantear nuestra fórmula de C12 para que recoja los nuevos importes. Necesitamos generar un rango que se ajuste automáticamente a las nuevas entradas. La fórmula para conseguirlo es la siguiente:

=SUMA(C4:DESREF(C12;-1;0;1;1))

Fíjese que hemos mantenido el comienzo del rango de suma igual =SUMA(C4:   pero hemos hecho "dinámico" el final del rango con la función DESREF. Lo que le estamos indicando con esta función es que se "desvíe" desde C12  -1 filas, es decir, una fila más arriba y 0 columnas, ya que ya nos encontramos en la columna que queremos sumar. EL resto de argumentos alto 1;ancho 1, podríamos omitirlos ya que no son obligatorios y por defecto ya establecen tales valores (para ver cómo funcionan estos argumentos puede consultar "Cálculos con Rangos Dinámicos").

Pruebe a insertar nuevas filas a partir de la fila 12 y comprobará como el sumatorio contemplará los nuevos importes que introduzca:

Apunte sobre Inserción de Filas: Existen muchas formas para insertar nuevas filas. A continuación detallamos varios (el tercero es quizás menos conocido pero muy práctico y rápido):

1. Nos colocamos en la celda donde queremos que inserte la fila y vamos al menú Insertar/Fila.

2. Seleccionamos toda la fila donde queremos insertar una nueva fila y pulsamos Ctrl y la tecla +.

Estos dos sistemas insertan una fila justo encima de la fila donde nos encontremos.

3. Para que se entienda mejor este método lo aplicaremos a nuestro ejemplo. Seleccionamos el rango A11:D11 y nos situamos en la parte inferior derecha de este rango como si fueramos a copiar hacia abajo (encima del pequeño cuadrado negro). Pulsamos las teclas Ctrl+Shift y con estas teclas pulsadas arrastramos hacia abajo tantas filas como queramos añadir. Excel insertará las nuevas filas debajo del rango seleccionado.

Cálculos por fechas para varios productos (rangos dinámicos)

Partiendo del ejemplo del artículo anterior, "Cálculos con Rangos Dinámicos y Cuadros Combinados", me habéis planteado la siguiente cuestión: cómo podemos seleccionar varios productos a la vez (y no sólo uno como en el artículo citado) y que me calcule las ventas acumuladas en los meses que seleccione. Para resolver esta duda vamos a manejar, además de las herramientas y funciones utilizadas en el artículo citado, casillas de verificación, condicionales anidados y formato condicional.

1. Lo primero que tenemos que hacer es anular la parte de entrada de datos del ejercicio citado que nos permitía seleccionar un producto concreto, ya que vamos a habilitar la posibilidad de seleccionar varios a la vez. Tendremos que borrar A2, B2 y el cuadro combinado que teníamos en B2. También borraremos las celdas donde obteníamos el resultado para un sólo producto, es decir, A6 y B6 (la primera imagen es lo que teníamos y la segunda cómo lo debemos dejar):

2. A continuación procedemos a generar las casillas de verificación que serán 10 (tantas como referencias o productos tengamos). Para ello habilitamos la barra de herramientas de Formulario (menú Ver /barras de herramientas/Formularios) y hacemos clic encima de la casilla de verificación:

3. Volvemos a la hoja y nos situamos encima de la celda A10 (a la altura del final de la celda y dibujamos la casilla de verificación (haciendo clic en el botón izquierdo del ratón y sin soltar el clic lo arrastramos hacia la derecha). Una vez dibujado procedemos a borrar el texto que aparezca (que será algo como casilla de verificación 1). El motivo de dejarlo en blanco es que como lo hemos situado en A10 el nombre del producto queda justo a continuación.

4. Copiamos esta casilla de verificación y la pegamos 9 veces (así tendremos las diez que necesitamos) y las vamos colocando hasta que queden como en la imagen (cada casilla al lado de su producto):

5. Este paso que explicaré a continuación tendremos que repetirlo para cada una de las casillas de verificación. Lo único que variará es la celda con la que vincularemos cada una de las casillas. Encima de la casilla de verificación hacemos clic con el botón DERECHO del ratón y, del menú emergente que se abrirá, seleccionamos Formato de control. En la pestaña Control en Valor seleccionamos Sin Activar y en Vincular con la celda hacemos clic en la celda Q10 (vamos a utilizar el rango Q10:Q19 para vincular cada una de las casilla de verificación, así que la siguiente la vincularemos con Q11 y así sucesivamente). Pulsamos Aceptar.

Fíjese que ahora cuando pulsamos dentro de la casilla de verificación, por ejemplo la que hemos colocado en la celda A10, la celda con la que la hemos vinculado (Q10) presenta el valor VERDADERO, mientras que si dejamos la casilla en blanco en Q10 presenta el valor FALSO. Esto mismo debe ocurrir en todo el rango Q10:Q19 cuando activemos o desactivemos sus correspondientes casillas.

6. Una vez hayamos vinculado todas y cada una de las casillas nos situamos en la celda A10 y escribimos la siguiente fórmula que enseguida explico:

=SI(Q10=FALSO;"";SI($B$3>$B$4;"Error fechas";SUMA(DESREF(B10;;$B$3;1;$B$4-$B$3+1))))

Como se puede ver estamos anidando dos condicionales (introduciendo un SI dentro de otra función SI). El primero comprueba una cosa muy sencilla: si hemos activado o no la casilla de verificación que tenemos dibujada en la celda A10. En concreto lo que comprueba es si NO la hemos activado. En tal caso le pedimos que no haga nada (que deje la celda "en blanco"). Si esta condición se cumple entonces ya no seguirá leyendo el resto de la fórmula, pues esta es la manera con la que Excel trabaja con los condicionales anidados. En caso de que Q10 no sea igual a FALSO (por tanto será igual a VERDADERO) entonces lo que pasamos a comprobar es si las fechas de la entrada de datos son correctas (básicamente que la fecha "desde" sea menor que la fecha "hasta"). En caso de que dicha comprobación tampoco se cumpla, y que por lo tanto las fechas sean correctas, entonces continúa leyendo el resto de la fórmula, que es dónde procedemos a realizar el cálculo en si (parte que puede consultar en el artículo al que hacíamos referencia al principio:"Cálculos con Rangos Dinámicos y Cuadros Combinados"

7. Copiamos la fórmula de A10 hasta A19.

8. Ya sólo nos queda el último paso para terminar nuestro modelo: sombrear el nombre de los productos cuyas casillas de verificación estén marcadas. Para ello seleccionamos el rango B10:B19 y vamos al menú Formato/Formato condicional. En el primer recuadro seleccionamos fórmula (en vez de valor de la celda) y escribimos (en el recuadro de la derecha):

=SI(Q10=VERDADERO;1;0)   Fíjese que Q10 no lleva dólares.

Pulsamos el botón Formato y, por ejemplo, seleccionamos fuente de color blanco y con estilo negrita y, además, trama granate. Aceptamos.

El modelo ya está terminado. Ahora ya puede seleccionar un mes inicial y un mes final, marcar los productos que le interesen y obtendrá las ventas acumuladas de éstos en el período indicado, como se puede ver a continuación (he omitido parte de la tabla de ventas para que la imagen sea más nítida):

Cálculos con Rangos Dinámicos (DESREF) y Cuadros Combinados

Supongamos que tenemos las ventas anuales, con detalle mensual de una serie de productos (en nuestro ejemplo utilizaremos 10 para hacerlo más visual pero, como suelo decir, me da igual que sean 10 que 800 productos: la solución es la misma). Lo que queremos hacer es ser capaces de sumar las ventas acumuladas del producto que elijamos entre los meses que elijamos. Para ello utilizaremos, entre otras, la función DESREF y cuadros combinados. Nuestros datos de partida (productos y ventas) son los que se muestran en la siguiente tabla:

Nuestra entrada de datos la diseñamos en las celdas que se muestran en la imagen, donde B2 será el producto que queremos analizar; B3 el mes inicial de la suma; B4 el mes final de la suma; y de la siguiente manera,y B6 el resultado final:

1. Lo primero que tenemos que hacer es generar una lista con los meses del año que utilizaremos más adelante. Lo hacemos en el rango, por ejemplo B22:B33 (en realidad, todo este tipo de tablas y operaciones intermedias necesarias para alguna función y/o herramienta se suelen poner en una zona apartada de la hoja y no visible). Es necesario indicar que no podemos utilizar para nuestra lista de meses el rango C9:N9 porque la herramienta que usaremos (cuadro combinado) no permite que los orígenes de las listas se encuentren en disposición horizontal.

2. A continuación vamos a gener nuestros cuadros combinados. Para ello vamos al menú Ver/Barras de herramientas/Formularios y hacemos un clic encima de cuadros combinados, como se observa en la imagen:

3. Nos situamos en la hoja a la altura de las celda C2 y hacemos clic con el botón izquierdo del ratón y sin soltar el clic arrastramos hacia la derecha para dibujar nuestro cuadro combinado:

4. Una vez dibujado le hacemos clic encima con el botón DERECHO del ratón y del menú emergente seleccionamos la opción Formato de control. En la pestaña Control nos situamos en el recuadro Rango de entrada y seleccionamos el rango B10:B19, que es donde se encuentran los productos que queremos que aparezcan en nuestra lista. En el recuadro Vincular con la celda seleccionamos B2. Y finalmente en el recuadro Lineas de unión verticales escribimos 10. Este último parámetro determina cuántos productos podremos ver a la vez (sin necesidad de utilizar la barra de desplazamiento de nuestro cuadro combinado) cuando despleguemos la lista del cuadro combinado. Pulsamos Aceptar.

A partir de este momento ya tenemos nuestro cuadro combinado generado (si despliega la lista verá los distintos productos) y vinculado con la celda B2. ES IMPORTANTE destacar las diferencias de estos cuadros combinados con las listas desplegables que podemos generar con Validación de Datos. Los cuadros combinados son fijos, es decir, si los coloca encima de una celda, como haremos, siempre estarán visibles, mientras que las listas de Validación de Datos sólo se activan cuando hacemos clic en la celda en la que se encuentran. Pero la diferencia quizas más importante es que cuadros combinados trabaja con números de orden. Puede comprobar que si selecciona uno de los productos de la lista en la celda B2 pondrá NO el nombre del producto sino el número de orden que ocupa en la lista (a diferencia de Validación de Datos que pone exactamente el nombre del elemento seleccionado de la lista). En nuestro caso esta diferencia nos permitirá formular directamente con la función DESREF como veremos enseguida.

5. Ahora tenemos que generar los dos cuadros combinados referentes a los meses. Seguimos el mismo proceso para dibujarlos que en el paso anterior y, una vez abierto el formato de control en la pestaña Control, nos situamos en el recuadro Rango de entrada y seleccionamos el rango B22:B33, que es donde tenemos los nombres de los meses (este rango será el mismo para los dos cuadros combinados que hemos dibujado). En el recuadro Vincular con la celda seleccionamos B3 para uno de los cuadros combinados y B4 para el otro. Y finalmente en el recuadro Lineas de unión verticales escribimos 12.

6. Colocamos cada cuadro combinado encima de su celda correspondiente como se puede ver en la imagen:

7. Ya tenemos terminada nuestra entrada de datos. Ya sólo nos queda realizar la fórmula que calcule la suma de los miles de litros vendidos del producto que seleccionemos y entre los meses que le indiquemos. Nos situamos en B6 y escribimos la siguiente fórmula:

=SUMA(DESREF(B9;B2;B3;1;B4-B3+1))

La función DESREF tiene la siguiente sintaxis: =DESREF(ref;filas;columnas;alto;ancho). Nos permite realizar una desviación desde una celda concreta (argumento ref). Desde esta celda "de partida" nos podemos mover un número de filas hacia arriba o hacia abajo (argumento fila) y un número de columnas hacia la derecha o izquierda (argumento columnas). Una vez hecha la desviación podemos indicarle el alto y el ancho de la matriz a tener en cuenta. En nuestro ejemplo partimos de B9 y le indicamos que desde B9 se mueva tantas filas hacia abajo como indique la celda B2 (que es donde tenemos el número de producto). Si por ejemplo seleccionamos el producto Coke L, en B2 aparecerá (aunque no lo vea porque tiene el cuadro combinado encima) el número 3, por lo que partiendo desde B9 se situará precisamente en B12. En el argumento columnas le indicamos B3, que es el número de mes. Si en B3 seleccionamos, por ejemplo, Abril entonces el número que aparecerá será el 4. De esta manera, y dado que "nos encontrabamos" en B12 la nueva posición será F12. Fíjese que ya nos encontramos en la celda del producto y mes inicial que queremos sumar. Sólo nos resta indicarle hasta donde debe realizar dicha suma. Para eso utilizamos los argumento Alto y Ancho. Como sólo nos interesan los datos del producto seleccionado el Alto será 1 (la misma fila) pero el ancho será la resta entre el mes final, por ejemplo junio, y el inicial: Junio-Abril = 6-4. De esta resta obtenemos el resultado de 2 al que siempre tendremos que sumar 1 para que incluya el mes inicial: 2+1=3 que son los meses que queremos que sume desde la celda F12.

Aunque pueda parecer un poco "lioso" en cuanto utilice esta función un par de veces se familizará con ella rápidamente (cosa que, por otro lado, es muy recomendable).

8. Para cerrar nuestro modelo le añadiremos un condicional a la fórmula con el objetivo de evitar errores en la introducción de fechas (mes final debe ser mayor o igual que el inicial). A saber:

=SI(B3>B4;"Error Fechas";SUMA(DESREF(B9;B2;B3;1;B4-B3+1)))

Comprobar registros con Fórmulas Matriciales y Formato Condicional

Hoy, 16 de abril, quiero felicitar el cumpleaños a mi hermano Miguel que, aunque ya no está entre nosotros, le seguimos queriendo, echando mucho de menos y teniendo SIEMPRE muy presente ¡Felicidades MIGUELÓN!

Supongamos que tenemos una tabla con numerosas entradas de números de factura, importes, etcétera (en nuestro ejemplo utilizaremos pocas entradas para que se vea bien la solución pero ésta es la misma para 5 entradas que para 5.000). Lo que queremos hacer es tener una celda de entrada de datos donde escribamos, por ejemplo, un número de factura y la hoja me indique si dicha referencia existe o no. Además queremos que si existe me sombree la celda dónde se encuentra y si no existe entonces que me sombree la celda donde he escrito la referencia y que me aparezca tachada. Nuestro modelo de partida es el siguiente:

Buscamos que en B4 nos indique si existe o no el número de factura que escribamos en B2. Si existe pondrá VERDADERO y coloreará la celda dónde se encuentra. Si no existe pondrá FALSO y coloreará la celda B2 y tachará su contenido (para indicarnos que dicha referencia es incorrecta). Manos a la obra:

1. Seleccionamos el rango D3:D22 y hacemos clic en el cuadro de nombres (a la derecha de la barra de fórmulas) y escribimos el nombre facturas.

2. Nos situamos en B4 y escribimos: =O(B2=facturas) y NO PULSAMOS ENTER. Lo que vamos a hacer ahora es realizar una entrada matricial. Para ello debemos pulsar las teclas Ctrl+Shift+Enter. Al hacerlo observará que la fórmula aparece encerrada por unas llaves { } quedando de la siguiente manera:

{=O(B2=facturas)}

Estas llaves no puede introducirlas manualmente porque entonces excel no lo reconocerá como una entrada matricial y no funcionará. La entrada matricial nos permite, en este caso, comprobar un valor, B2, contra todo un rango, D3:D22, rango al que hemos llamado facturas. Esta fórmula nos devolverá dos valores posibles: VERDADERO ó FALSO. Ya hemos conseguido que nos indique la existencia o no del número de factura solicitado. Sigamos con nuestro modelo:

3. Seleccionamos el rango facturas y vamos al menú Formato/Formato condicional y ponemos valor de la celda igual a y en el recuadro de la derecha hacemos clic en B2, que aparecerá como $B$2 (y así debemos dejarlo, con los dólares).

4. Pulsamos el botón Formato y seleccionamos, por ejemplo, trama (sombreado) de color naranja y pulsamos Aceptar. De esta manera cuando escribamos un número de factura que exista en B2 la celda que contenga dicho número se coloreará de naranja, haciendo muy sencilla su localización.5. Nos situamos en B2 y vamos al menú Formato/Formato condicional y donde pone valor de la celda lo cambiamos por fórmula. En el recuadro de la derecha escribimos:

=SI(B4=FALSO;1;0)

6. Pulsamos el botón Formato y en la pestaña Fuentes seleccionamos Tachado dentro del apartado Estilo. Además vamos a la pestaña Tramas y seleccionamos, por ejemplo, el color naranja "chillón". Pulsamos Aceptar.