por Hola a todos 🙂
Hoy os voy a mostrar la posibilidad de ordenar y mostrar una serie de datos mediante la utilización de una fórmula combinada. A priori puede resultar un tanto compleja, pero sin duda es una buena alternativa a tener que utilizar VBA si no controláis de programación o no queréis utilizar macros.
Para enseñar qué es lo que quiero hacer lo mejor es mostraros mediante una imagen de que datos disponemos inicialmente y cómo queremos presentarlos. Imaginad que tenemos un listado de empleados de nuestra empresa y en la columna siguiente el idioma que acreditan saber, lógicamente un empleado puede saber varios idiomas y por ello tendremos también registros únicos por cada idioma y empleado, fijaos en la imagen:
Con esta información, que es la típica que cualquier base de datos almacenaría, debemos construir una matriz que haga más visual la presentación de la información, de modo que muestre los datos como un eje de coordenadas entre los empleados y los idiomas, en la imagen de como quedaría la información lo veréis más claro:
En el ejemplo he puesto que se marque con una «X» el idioma correspondiente, pero se podría poner cualquier indicador. Como podéis ver ahora sí tenemos una información mucho más clara y ordenada que nos permitirá trabajarla con tablas dinámicas y otras herramientas. Pero vayamos a la fórmula que nos permite hacerlo. Aquí os la dejo para ir analizándola por partes:
=SI.ERROR(((SI(+INDICE(IDIOMAS!$B$2:$B$20011;COINCIDIR(1;INDICE((IDIOMAS!$A$2:$A$20011=TABLA_IDIOMAS!A2)*(IDIOMAS!$B$2:$B$20011=TABLA_IDIOMAS!$B$1););0))="INGLES";"X")));" ")
Es una fórmula combinada que utiliza las funciones «Indice», «Coincidir», «Si» y «Si.error» (esta última solo funciona en versiones de Excel 2007 o superiores).
Si tenemos en cuenta que la información donde tenemos a todos los empleados con registros duplicados (la primera imagen que he subido), está en la pestaña que vamos a llamar «IDIOMAS» y la tabla matriz resultando en otra hoja que vamos a llamar «TABLA_IDIOMAS», podremos entender la lógica de la fórmula.
Primero introducimos la fórmula para que busque, ordene y muestre la posición donde se encuentra el idioma:
=COINCIDIR(1;INDICE((IDIOMAS!$A$2:$A$20011=TABLA_IDIOMAS!A11)*(IDIOMAS!$B$2:$B$20011=TABLA_IDIOMAS!$H$1););0)
La función «Coincidir» buscará la posición del valor que le estamos especificando en la matriz, de decir, si el empleado «VICTOR» sabe Ruso, en la celda aparecerá la posición respecto a la matriz la que se encuentra el idioma en la pestaña «IDIOMAS», en este caso es en la posición 23 (recuerda que empieza a contar desde la celda B2):
Si hacemos doble clic en la fórmula veremos a que celdas hace referencia:
Pero ahora queremos afinar un poco más la búsqueda y que en vez de la posición nos muestre el idioma, para ello debemos completar la fórmula:
=+INDICE(IDIOMAS!$B$2:$B$20011;COINCIDIR(1;INDICE((IDIOMAS!$A$2:$A$20011=TABLA_IDIOMAS!A11)*(IDIOMAS!$B$2:$B$20011=TABLA_IDIOMAS!$H$1););0))
Ahora la función «INDICE» que hemos incluido, en base a la matriz de datos que hemos seleccionado «IDIOMAS» ordena y muestra los datos que le hemos datos previamente con la función coincidir, de manera que muestra que la celda 23 se corresponde con idioma «RUSO»:
Con esta información sería suficiente, pero como queremos que cuando identifique el idioma ponga una «X», debemos introducir un «SI» condicional y además también un control SI.ERROR para que no muestre los mensajes de error si no hay datos.
=SI.ERROR(((SI(+INDICE(IDIOMAS!$B$2:$B$20011;COINCIDIR(1;INDICE((IDIOMAS!$A$2:$A$20011=TABLA_IDIOMAS!A11)*(IDIOMAS!$B$2:$B$20011=TABLA_IDIOMAS!$H$1););0))="RUSO";"X")));" ")
Es decir, la fórmula que os puse al principio, que ahora está completa, donde ordenamos que si, por ejemplo, el resultado de la fórmula es «RUSO» entonces me pone una «X». En caso de que no fuese así, Excel mostraría un error y eso visualmente a la hora de presentar un informe no queda nada bien, para ello introducimos la fórmula «SI.ERROR», que no deja ser otro condicional, y que cuando aparezca un error nos muestre vacío » «, (se podría poner lo que quisiéramos, «SIN DATOS», «SIN COMPETENCIA», etc …
Pues bien, esta ha sido la práctica de hoy, espero que os ayude a realizar grandes informes y sobresalir un poco más en vuestro trabajo.
Os dejo el enlace como siempre 😉
Descarga el archivo pulsando en: ORDENAR Y MOSTRAR DATOS EN UNA MATRIZ A PARTIR DE REGISTROS DUPLICADOS
