Repasando mentalmente formas de trabajar, al escribir días atrás sobre la función desbordada UNICOS recordé una fórmula clásica (escrita por Mynda Treacy de myonlinetraininghub.com, creo recordar) como alternativa a estas funciones novedosas aún no completamente extendidas a todos los usuarios.
La idea de hoy es obtener un listado de registros únicos a partir de una lista con elementos repetidos o no:
La primera opción, simple y fácil de interpretar, es emplear la funciones desbordadas ORDENAR y UNICOS, así en D3 insertaremos: =ORDENAR(UNICOS(TblPAIS[País]))
con lo que obtenemos el listado ordenado de los países únicos... tal cual leemos de la fórmula.
Lo bueno de esta fórmula es que es desbordada, i.e., no tiene restricción de celdas, el rango desbordado en D3:D8 del ejemplo crecerá según su necesidad sin requerir atención por nuestra parte!!.
Una alternativa potente, para todos los usuarios, es emplear funciones clásicas como INDICE, COINCIDIR o CONTAR.SI.
Una primera opción nos retornará sin orden alguna, según aparezcan, los distintos países únicos... Así en E3 insertamos: =SI.ERROR(INDICE(TblPAIS[País];COINCIDIR(0;INDICE(CONTAR.SI($E$2:E2;TblPAIS[País]);0);0);0);"")
posteriormente copiaremos manualmente hasta el rango deseado E3:E12.
OJO, por que si los elementos únicos sobrepasan el tamaño del rango perderemos elementos únicos en nuestro listado!!. La explicación y clave de esta fórmula es el uso de CONTAR.SI($E$2:E5;TblPAIS[País])
dentro de la fórmula, ya que nos devuelve una matriz de 1 y 0 de un tamaño igual al listado de países sobre los que trabajar, donde asocia el valor 1 si ya se ha evaluado o 0 si no se ha asociado ya.
Por ejemplo, si editamos la celda E6, cuando ya se han listado, en E3:E5 los países ES, FR e IT, la matriz generada sería: {1;1;1;1;1;0;0;0;1;0}
comparado con la Tabla de países uno a uno tendríamos:
ES 1 - ya está listado en E3:E5
FR 1 - ya está listado en E3:E5
IT 1 - ya está listado en E3:E5
IT 1 - ya está listado en E3:E5
ES 1 - ya está listado en E3:E5
DE 0 - aún NO está listado en E3:E5
UK 0 - aún NO está listado en E3:E5
BE 0 - aún NO está listado en E3:E5
ES 1 - ya está listado en E3:E5
UK 0 - aún NO está listado en E3:E5
obviamente es una situación cambiante según arrastramos nuestra fórmula en el rango de trabajo E3:E12.
Para trabajar con ese vector/matriz de 0 y 1 comentado, aplicamos la función INDICE dando como argumento de fila el valor 0, lo que ha recuperar la totalidad del vector: INDICE(CONTAR.SI($E$2:E2;TblPAIS[País]);0)
La siguiente función COINCIDIR recupera la primera posición del valor cero que encuentre, esto es, el primer país aún NO listado: COINCIDIR(0;INDICE(CONTAR.SI($E$2:E5;TblPAIS[País]);0);0)
Esta posición absoluta recuperada nos sirve para obtener el país directamente de nuestra tabla original con un uso estándar de INIDICE: INDICE(TblPAIS[País];COINCIDIR(0;INDICE(CONTAR.SI($E$2:E5;TblPAIS[País]);0);0);0)
Finalmente para depurar errores en el rango de trabajo E3:E12 aplicamos SI.ERROR
Sin duda más elaborado y complejo de entender, requiriendo en algunos casos ejecutar matricialmente la fórmula (presionado Ctrl+Mayusc+Enter).
Por rizar el rizo.. te habrás dado cuenta que la fórmula anterior ni siquiera devuelve datos ordenados!!... vaya contrariedad
:D
Solución o alternativa real a ORDENAR y UNICOS.
Aplicamos la siguiente fórmula en F3 para luego copiar hasta F12: =SI.ERROR(INDICE(TblPAIS[País];COINCIDIR(K.ESIMO.MENOR(SI(CONTAR.SI($F$2:F2;TblPAIS[País])=0;CONTAR.SI(TblPAIS[País];"<"&TblPAIS[País]); ""); 1);CONTAR.SI(TblPAIS[País];"<"&TblPAIS[País]); 0));"")
La esencia del cálculo es similar a la explicación anterior... con la dificultad añadida de procesar el orden empleando la función K.ESIMO.MENOR.
Funciona perfectamente... pero con la misma limitación. Si el número de elementos únicos sobrepasa el número de celdas del rango de trabajo F3:F12 perderemos dichos elementos o países.
En el post de hoy veremos como se simplifica el uso de las validaciones tipo Lista anidadas o dependientes con el uso de las nuevas funciones desbordadas (dynamyc arrays formulas), como UNICOS, FILTRAR...
Supongamos un listado de ventas por Fecha, Región, Provincia, Comercial... a partir de la cual queremos recuperar los registros que cumplan ciertos criterios dependientes (según la Región seleccionada - según Provincia de dicha REgión - según Comercial que hubiera operado en dichas ubicaciones).
Comenzaremos generando unos rangos auxiliares (habitualmente en otra hoja!!) para recuperar Regiones únicas empleadas...
Asé en N5 escribimos la fórmula desbordada: =UNICOS(TblDatos[Comunidad Autónoma];FALSO;FALSO)
que lista de forma única las CCAA registradas en nuestra Tabla.
En la celda I2 añadimos una Validación de celda tipo lista que se alimente de ese rango desbordado:= $N$5#
usando el caracter numeral # para referirnos a él.
Para el siguiente rango desbordado en O5 nos apoyamos en la selección de I2: =UNICOS(FILTRAR(TblDatos[Provincia];TblDatos[Comunidad Autónoma]=Hoja1!$I$2))
lo que lista solo Provincias asociadas a la CCAA elegida.
Y de forma similar, en la celda J2 insertamos una regla de Validación de datos tipo Lista que se nutra del rango anterior.
Recuerda referirte a la celda O5 con el numeral: =$O$5#
Paso siguiente.
Sobre las dos elecciones anteriores en I2 y J2 generamos el último rango auxiliar desbordado en P5: =UNICOS(FILTRAR(TblDatos[Comercial];(TblDatos[Comunidad Autónoma]=Hoja1!$I$2)*(TblDatos[Provincia]=Hoja1!$J$2)))
y finalmente en K2 creamos nuevamente una validación de datos tipo lista: =$P$5#
Como en los casos anteriores.
Así tendríamos nuestras validaciones dependientes / anidadas.
Damos un último paso recuperando los registros que cumplan los múltiples criterios seleccionados (en I2, J2 y K2) con nuestras validaciones....
Así en H5 insertamos la fórmula desbordada: =FILTRAR(TblDatos;(TblDatos[Comunidad Autónoma]=$I$2)*(TblDatos[Provincia]=$J$2)*(TblDatos[Comercial]=$K$2))
Sin duda alguna, la flexibilidad aportada por estas nuevas funciones, cambiarán nuestra forma de trabajar.
Claro está, cuando se implementen de forma general a todas las versiones...
;-)
Aplicaremos y daremos hoy un uso de la función desbordada FILTRAR aplicándole diferentes criterios Y - O.
Revisa, por favor, este post previo.
Igual que otras muchas funciones 'tradicionales', FILTRAR admite el uso de múltiples criterios... y en el ejemplo de hoy veremos un caso.
Tenemos un listado de ventas por comercial y país.. del cuál queremos recuperar solo las ventas de aquellos que cumplan ciertas condiciones.
Observamos la fórmula desbordada incluida en J3: =FILTRAR(Tabla1;((Tabla1[Comercial]=$G$3)*(Tabla1[País]=$H$3))+((Tabla1[Comercial]=$G$4)*(Tabla1[País]=$H$4)))
y cómo hemos construido el doble criterio Y-O ((Tabla1[Comercial]=$G$3)*(Tabla1[País]=$H$3))+((Tabla1[Comercial]=$G$4)*(Tabla1[País]=$H$4))
donde los productos replican el operador Y, esto es, verifica la condición de 'comercial' y 'país', mientras que la suma replica el operador lógico O, i.e., o una condición doble Eva y ES o Juan y DE.
El inconveniente de esta forma de operar sería tener que incorporar un número indeterminado de dobles criterios...
Para este caso plantearé una alternativa más tradicional, pero combinándolo con la nueva función FILTRAR. Añadiré para el caso una columna calculada en la tabla con la fórmula siguiente:
Notemos la fórmula insertada en el campo: =SI.ERROR(CONTAR.SI.CONJUNTO($G$22:$G$24;[@Comercial];$H$22:$H$24;[@País]);0)
Esto devuelve un campo/vector de valores enteros de 0 y 1, siendo 1 cuando exista esa doble coincidencia de 'comercial' y 'país'... para a continuación aplicar en J22 la fórmula: =FILTRAR(Tabla13[[Comercial]:[Unidades]];Tabla13[Control])
Son muchas las novedades y funciones que Excel ha puesto a nuestra disposición en las últimas semanas (revisa los post anteriores a este sobre fórmulas desbordadas o dynamics arrays)... pero nunca debemos olvidar el pasado u origen de Excel, y en particular las funciones macro vb 4.0, reminiscencias del arcaico origen de la programación.
Hoy veremos una función interesante que nos devuelve un listado / matriz de los nombres de los ficheros incluidos en una carpeta o directorio especificado. Hablo de la función =ARCHIVO(ruta as text)
Algo interesante es que el argumento de la función admite los comodines estándar de Excel (* y ?) para recuperar rutas.
Si no informáramos de ruta alguna, la función devolverá el listado de ficheros de la ruta corriente/actual donde se encuentre nuestro fichero de trabajo.
Para poder usar esta función deberemos crear un Nombre definido (que llamaré 'ListadoFicheros') con la función indicada:
No olvides guardar tu fichero como '.xlsm Libro habilitado para macros'.
Algunos usos...
En la celda A5 indicamos nuestra ruta:
E:\excelforo\Cambios\*
el asterisco final fuerza a listar una matriz con TODOS los ficheros contenidos.
Para recuperar este listado, en el rango B5:K5 añadimos =SI.ERROR(INDICE(ListadoFicheros;1;COLUMNAS($B$4:B4));"-")
con la función INDICE recuperamos cada elemento de la matriz de ficheros...
Otro ejemplo en A7 donde insertamos la ruta:
E:\excelforo\Cambios\*.xls*
los comodines finales recuperarán únicamente los ficheros de Excel (.xls*).
Lo que hacemos en B7:K7 con la misma fórmula anterior: =SI.ERROR(INDICE(ListadoFicheros;1;COLUMNAS($B$6:B6));"-")
Similar caso en A9 con la ruta
E:\excelforo\Cambios\*Facturas*
que permite recuperar cualquier archivo que contenga la palabra 'Facturas'.
Idéntica función en B9:K9 =SI.ERROR(INDICE(ListadoFicheros;1;COLUMNAS($B$8:B8));"-")
Otro uso sería convertir en vínculo cada elemento recuperado para, en un futuro, abrir el fichero en particular.
En A12 añadimos una ruta cualquiera, con o sin comodines
E:\excelforo\Cambios\*.xls*
y en B12:K12 añadimos la fórmula que incorpora el hipervínculo, empleando la función HIPERVINCULO: =SI.ERROR(HIPERVINCULO(IZQUIERDA($A12;LARGO($A12)-6)&INDICE(ListadoFicheros;1;COLUMNAS($B$4:B4));INDICE(ListadoFicheros;1;COLUMNAS($B$4:B4)));"-")
Un último uso para el post de hoy nos responderá a una pregunta habitual... ¿Existe tal fichero en esa ruta???.
En A15 incluimos la ruta de la carpeta y en A16 el nombre del fichero...
Así en B15 insertamos: =SI.ND(SI(COINCIDIR(A16;ListadoFicheros;0)>0;"Sí existe");"No existe")
donde se lee fácilmente que con los condicionales SI.ND y SI, junto con COINCIDIR, determinamos si existe o no el fichero en la ruta dada.
Hace ya bastante tiempo escribí un artículo que nos permitía saber cuál era el lunes de cualquier semana a partir de una fecha (ver aquí).
Esta fórmula no era complicada pero quizá sí poco intuitiva... hoy nos aprovecharemos de todas las funciones desbordadas (dynamic arrays formulas) nuevas vista en los posts anteriores de este blog para resolverlo de una manera alternativa.
Desmontamos la fórmula desbordada de C3: =BUSCARX(B3;FILTRAR(SECUENCIA(366;1;"1/1/2020";1);DIASEM(SECUENCIA(366;1;"1/1/2020";1);2)=1);FILTRAR(SECUENCIA(366;1;"1/1/2020";1);DIASEM(SECUENCIA(366;1;"1/1/2020";1);2)=1);"-";-1;1)
En primer lugar tenemos el bloque: FILTRAR(SECUENCIA(366;1;"1/1/2020";1);DIASEM(SECUENCIA(366;1;"1/1/2020";1);2)=1)
el cual genera una secuencia de 366 días desde el 1/1/2020 con SECUENCIA(366;1;"1/1/2020";1)
a partir de estos 366 días con FILTRAR nos quedamos solo con aquellos cuyo DIASEM sea igual a 1 (a lunes), al aplicar el criterio de filtro DIASEM(SECUENCIA(366;1;"1/1/2020";1);2)=1
Esto generaría un listado de fechas con todos los lunes del 2020...
Finalmente sobre este listado de 'lunes' aplicamos la función BUSCARX con los argumentos: valor_buscado: celda B3 (una fecha cualquiera del 2020) matriz_buscada: listado de lunes matriz_devuelta: mismo listado de lunes [si_no_encuentra]: un caracter concreto, por ejemplo, guión medio [modo_de_coincidencia]: -1 (coincidencia exacta o el siguiente elemento menor) [modo_de_búsqueda]: 1 (búsqueda del primero al último.
Sin duda una opción intuitiva a un problema complejo.
Pocos días atrás los usuarios de Office 365 canal mensual (pronto demás canales) disponemos de una función mejorada: COINCIDIRX o XMATCH: = BUSCARX(valor_buscado; matriz_buscada; matriz_devuelta; [si_no_se_encuentra]; [modo_coincidencia]; [modo_búsqueda])
siendo: Valor_buscado: El valor de búsqueda
Matriz_buscada: La matriz o rango para buscar...
Matriz_devuelta: La matriz o rango que se devolverá...
[si_no_se_encuentra]: Cuando no se encuentre una coincidencia válida, devuelva el texto indicado.
Si no se encuentra una coincidencia válida y falta este parámetro se devolverá #N/D.
[modo_coincidencia]:El tipo de coincidencia:
0: coincidencia exacta (predeterminado). Si no se encuentra ninguna, devuelve #N/D.
-1: coincidencia exacta o el siguiente elemento menor
1: coincidencia exacta o el siguiente elemento de mayor tamaño
2: coincidencia de caracteres comodín (cuando *, ? y ~ tiene un significado especial).
[modo_búsqueda]: el tipo de búsqueda:
1: Búsqueda del primero al último (predeterminado).
-1: Búsqueda del último al primero (búsqueda inversa).
2: Búsqueda binaria en orden ascendente (si no está ordenado, se devolverán resultados no válidos).
-2: Búsqueda binaria en orden descendente (si no está ordenado, se devolverán resultados no válidos).
Esta función es una 'mezcla' potente de la conocida BUSCARV / BUSCARH y de la no tanto BUSCAR... concluyendo que se convertirá pronto en la función (una de ellas) más empleada por todos los usuarios... Ya que la función BUSCARX busca elementos en una tabla o en un rango por filas; con BUSCARX podremos buscar en una columna un término de búsqueda y devolver un resultado de la misma fila en otra columna, independientemente del lado de la columna devuelto (a diferencia de BUSCARV que exige se encuentre a su derecha...).
Otro aspecto relevante de BUSCARX es que, a diferencia de BUSCARV, BUSCAR puede devolver una matriz con varios elementos!!.
Veamos algunos ejemplos.
En la imagen de nuestro listado aplicamos dos usos sencillos de búsqueda:
Vemos en G3 la fórmula: =BUSCARX(F3;Tabla1[Empleado];Tabla1[Num empl];"no encontrado";0;1)
que localiza un dato a la izquierda del rango donde realizamos la búsqueda...
Además indicamos qué texto queremos ver (qué hacer) si no hay coincidencia exacta.
En G7 insertamos la fórmula: =BUSCARX(F7;Tabla1[Empleado];Tabla1[[País]:[Ventas]];"-";0;1)
la cual se desborda en dos columnas automáticamente!!, ya que nuestro argumento de 'matriz_devuelta' consta de dos columnas!.
En la imagen siguiente vemos un ejemplo donde hacemos un doble uso de BUSCARX.
En este ejemplo en la celda D12 insertamos (y luego copiaremos en E12 y F12): =BUSCARX(D$11;$B$3:$B$8;BUSCARX($C$12;$C$2:$G$2;$C$3:$G$8))
en esta función identificamos primero el texto 'CN' ('MARGEN' o 'RDI') en el rango B3:B8.
la matriz devuelta la obtenemos con BUSCARX ( BUSCARX($C$12;$C$2:$G$2;$C$3:$G$8) ), la cual realiza la búsqueda horizontal del año.
Un ejemplo más, donde podemos emplear BUSCARX como identificador de referencias, al igual que nos permitiría INDICE o incluso BUSCAR.
En la celda D16 vemos la siguiente fórmula: =SUMA(BUSCARX(D$15;$B$3:$B$8;BUSCARX($B$16;$C$2:$G$2;$C$3:$G$8)):BUSCARX(D$15;$B$3:$B$8;BUSCARX($C$16;$C$2:$G$2;$C$3:$G$8)))
generada a partir del ejemplo previo... el cual identifica una posición dentro del rango, una celda.
En concreto para el ejemplo:
BUSCARX(D$15;$B$3:$B$8;BUSCARX($B$16;$C$2:$G$2;$C$3:$G$8))
equivaldría a la celda D3
mientras que
BUSCARX(D$15;$B$3:$B$8;BUSCARX($C$16;$C$2:$G$2;$C$3:$G$8))
equivaldría a la celda F3
todo combinado, y usando el resultado como referencia, tendríamos la SUMA(D3:F3), de manera dinámica.
Quizá una de mis funciones favoritas siempre ha sido COINCIDIR (hay en este mismo blog multitud de aplicaciones): =COINCIDIR(valor_buscado; vector_donde_buscar; [tipo de coincidencia])
donde solo disponíamos de tres tipos de coincidencia exigida:
1 : menor que (predeterminado)
0 : coincidencia exacta
-1 : mayor que
Pues desde hace pocos días los usuarios de Office 365 canal mensual (pronto demás canales) disponemos de una función mejorada: COINCIDIRX o XMATCH: = COINCIDIRX(valor_buscado; matriz_buscada; [modo_coincidencia]; [modo_búsqueda])
siendo: Valor_buscado: El valor de búsqueda
Matriz_buscada: La matriz o rango para buscar... OJO, debe ser un vector!!
[modo_coincidencia]:El tipo de coincidencia:
0: coincidencia exacta (predeterminado)
-1: coincidencia exacta o el siguiente elemento menor
1: coincidencia exacta o el siguiente elemento de mayor tamaño
2: coincidencia de caracteres comodín (cuando *, ? y ~ tiene un significado especial).
[modo_búsqueda]: el tipo de búsqueda:
1: Búsqueda del primero al último (predeterminado).
-1: Búsqueda del último al primero (búsqueda inversa).
2: Búsqueda binaria en orden ascendente (si no está ordenado, se devolverán resultados no válidos).
-2: Búsqueda binaria en orden descendente (si no está ordenado, se devolverán resultados no válidos).
Se observa rápidamente que esta versión mejorada de COINCIDIR funciona en cualquier dirección y nos devuelve las coincidencias exactas de forma predeterminada, lo que hace que sea más fácil y conveniente de usar que su predecesora.
Veamos un cuadro aplicado sobre un ejemplo con todas las opciones mostradas.
En H4 para luego arrastrar al rango H4:J7: =COINCIDIRX($G$3;$B$3:$B$16;H$3;$G4)
Y haciendo uso de comodines en la celda L4 para arrastrar a L4:L7: =COINCIDIRX($K$3&"*";$B$3:$B$16;L$3;$G4)
Especial atención al emplear las opciones de búsquedas binarias, ya que se requiere la ordenación previa de los rangos para obtener resultados congruentes.
Igualmente interesante es la localización de la primera coincidencia (del primero al último) como la localización de la última coincidencia (del último al primero). Esto nos permite identificar fácilmente situaciones ocurridas en primer y último termino.
Veamos un ejemplo aplicado, combinando COINCIDIRX con su inseparable INDICE, pero además empleando las funciones desbordadas ORDENARPOR:
En la celda H11: =COINCIDIRX(G11;ORDENARPOR(D3:D16;D3:D16;1);1;1)
obtenemos la posición que ocupa si buscamos 25 unidades vendidas, si estuvieran ordenadas de manera ASCENDENTE estas unidades, además buscamos la 'coincidencia exacta o el valor mayor'.
Mientras que en H14: =COINCIDIRX(G11;ORDENARPOR(D3:D16;D3:D16;1);-1;1)
conseguimos la posición si la búsqueda fuera mediante la 'coincidencia exacta o el valor mayor'.
Lo vemos directamente sobre nuestro rango ordenado...
Con estas posiciones es fácil, mediante el uso de INDICE, conseguir el país correspondiente.
En I11 =INDICE(ORDENARPOR(B3:B16;D3:D16;1);H11)
Una variante de la función ORDENAR (SORT) es la función desbordada ORDENARPOR o SORTBY con la siguiente sintaxis: = ORDENARPOR(matriz; por_matriz1; [orden1]; [por_matriz2]; [orden2];...)
siendo matriz: La matriz o rango para ordenar por_matriz1: La matriz o rango en la que ordenar [orden1]: El orden utilizado. 1 para ascendente,-1 para descendente. El valor predeterminado es ascendente. [por_matriz2]: La matriz o rango en la que ordenar [orden2]: El orden utilizado. 1 para ascendente,-1 para descendente. El valor predeterminado es ascendente.
Una ventaja de emplear ORDENARPOR en lugar de ORDENAR es que la función ORDENAR se utiliza para ordenar datos en una matriz...
Si quisiéramos ordenar los datos de nuestro rango, es mejor usar la función ORDENARPOR, ya que es más flexible.
ORDENARPOR respetará las adiciones y eliminaciones de columnas, ya que hace referencia a un rango concreto, mientras que ORDENAR hace referencia a un número de índice de columna.
Un apunte interesante de todas las funciones desbordadas (dynamics arrays formulas) es que Excel ofrece soporte limitado para éstas entre libros, ya que solo se ofrece soporte para este escenario cuando ambos libros están abiertos. Si se cierra el libro de origen, las fórmulas desbordadas vinculadas devolverán un error #REF! al actualizarse.
Veamos algún ejemplo de la función desbordada ORDENARPOR.
Partimos de nuestra tabla de empleados+departamentos+unidades, la cual queremos ordenar en base a múltiples criterios de ordenación.
Por ejemplo, queremos ordenar nuestra Tabla según el departamento y además por las unidades vendidas de mayor a menor...
La fórmula desbordada buscada es: =ORDENARPOR(TblDatos3;TblDatos3[Departamento];1;TblDatos3[Uds vendidas];-1)
A esta función, igualmente a su 'hermana menor', la podemos anidar con otras funciones como FILTRAR: =FILTRAR(ORDENARPOR(TblDatos3;TblDatos3[Departamento];1;TblDatos3[Uds vendidas];-1);TblDatos3[Uds vendidas]>40)
que muestra nuestra tabla ordenada por los dos criterios comentados (departamento y unidades), pero solo para aquellos casos en que se hayan vendido más de 40 uds...
Tras emplear en diversas situaciones las dos funciones de ordenar (ORDENAR y ORDENARPOR), sin duda recomiendo por su fortaleza ante modificaciones y cambios en los rangos de trabajo, se utilice ORDENARPOR por el motivo comentado con anterioridad:respetará las adiciones y eliminaciones de columnas, ya que hace referencia a un rango concreto
