En la publicación anterior presentamos las funciones de matriz dinámica y cómo escribiendo la fórmula UNICOS( ) en una sola celda los resultados se derraman a las demás, según sea necesario. Presentaremos aquí otra función similar y un truco para evitar tener que actualizar las referencias de rangos cuando la data aumenta.
Escribamos, por ejemplo, =ORDENAR(UNICOS( B2:B701))
Obtendremos la misma lista anterior de cinco países pero ordenados alfabéticamente en orden ascendente.
Para ordenarlos descendentemente habría que escribir =ORDENAR(UNICOS( B2:B701); ; -1)
Pongamos un obstáculo en este momento. ¿Qué pasaría si los datos aumentaran? Es decir, ¿qué pasaría si la última fila no sea siempre la 701 sino que esta siga creciendo 705, 720 según pasan los días y no quisiera tener que ajustar mis fórmulas a cada momento? Nuestra primera idea podría ser hacer la referencia a una fila mayor, como por ejemplo
=ORDENAR(UNICOS( B2:B1000)) como a continuación
Pero ahora vemos que aparece un elemento extraño adicional, un cero. Al hacer referencia a filas que aún no tienen datos entonces tenemos este problema. ¿Hay solución? ¡Claro! Usamos el operador . (punto) de la siguiente manera:
Esto se llama «referencia recortada» (trim references, en inglés) y es algo que se introdujo en Excel el 2025. Con ello, por más que la referencia original sea hasta la celda 1000, la referencia real acaba en la última celda con datos (en nuestro caso, la 701) y es muy útil para permitir que crezca la data sin tener problemas con nuestras fórmulas. En ese sentido, el punto también puede ir antes de los dos puntos ( B2.:.B701) y así recortar también las primeras filas en blanco. Nótese que las filas en blanco intermedias no serán excluidas de ninguna manera. Este comportamiento se puede replicar con la función TRIMRANGE( ) que presenta un poco más de flexibilidad pero la fórmula «se agranda» y no es tan compacta como usar el punto.
Veamos otra función muy útil: FILTRAR( ) escribiendo en la misma celda R3: =FILTRAR(A2:.P1000; B2:.B1000=»Canada»)
Nótese que estamos usando el punto («.») para la referencia recortada previamente explicada.
El resultado es toda la base de datos filtrada por la condición que la segunda columna (país) sea igual a «Canada». Se puede poner más de una condición «multiplicando» las condiciones previamente encerradas entre paréntesis. Por ejemplo, escribamos ahora =FILTRAR( A2:.P1000; (B2:.B1000=»Canada») * (C2:.C1000=»Carretera»))
Ahora la respuesta es la base de datos filtrada por las dos condiciones mencionadas: que el país sea Canada y que además el producto sea Carretera. Como nota al margen, cuando las fórmulas comienzan a crecer, prefiero darles formato como se muestra arriba, separando al menos cada argumento en su propia fila (usando Alt+Enter) y el paréntesis solo en su propia fila al final. No es solo cuestión de gustos sino que encuentro que me ayuda a evitar problemas de argumentos equivocados y, sobre todo, me ayuda a entender fácilmente qué hace una fórmula cuando vuelvo a revisar un Excel hecho varios meses atrás.
Terminemos usando la función ELEGIRCOLS( ). Con esta función podemos elegir qué números de columna queremos ver. Escribamos: =ELEGIRCOLS( FILTRAR( A2:.P1000; (B2:.B1000=»Canada») * (C2:.C1000=»Carretera»)); 1;2;3;5;8;10)
Vemos que de las dieciséis columnas que tiene la data (desde la columna A hasta la P), solo nos devuelve las seis columnas solicitadas. No incluimos las cabeceras porque de hacerlo (poniendo el rango desde la fila 1, no desde la 2) no funcionaría el filtro porque la cabecera no cumpliría con la condición de ser «Canada» o «Carretera». Para tener las cabeceras tendríamos que hacer otra función como la siguiente (escribirla en la celda R2) =ELEGIRCOLS(A1:P1;1;2;3;5;8;10)
En la fórmula, que incluye todas las columnas de la A a la P pero solo una fila (rango A1:P1), seleccionamos las mismas columnas (1, 2, 3, 5, 8 y 10) que seleccionáramos en la celda R3 y asunto arreglado. O casi. ¿Por qué «casi»? Porque ahora tenemos dos formulas que mantener: la de la celda R2 y la de la celda R3. Si queremos cambiar las columnas a retornar o algo tenemos que hacerlo en dos lugares. ¿Se puede hacer una sola fórmula para obtener todo? Sí. La solución está en usar APILARV( )
Usemos APILARV( ) y como argumentos le damos las funciones ELEGIRCOLS( ) que escribiéramos en la celda R2 y en la R3. Así:
=APILARV( ELEGIRCOLS(A1:P1;1;2;3;5;8;10); ELEGIRCOLS( FILTRAR( A2:.P1000; (B2:.B1000=»Canada») * (C2:.C1000=»Carretera»)); 1;2;3;5;8;10))
Nota: si no eliminamos lo que está en la celda R3 al escribir esta nueva fórmula en R2 saldrá el error del que habláramos en la publicación anterior:
Bueno. La fórmula final está un poco larga y podría parecer ininteligible. Pero la indentación presentada antes ayuda mucho a clarificarla. Es más, la indentación se ve mejor en la barra de fórmulas (no directamente en la celda). Cierro esta publicación mostrando cómo se ve la última fórmula indentada en la barra de fórmulas.
P. D. La imagen que acompaña esta publicación corresponde a la pintura La Escuela de Atenas, hecha por Rafael Sanzio da Urbino. Si la entrega anterior de este blog hablaba de la revolución, esta segunda aborda lo que suele venir después de toda revolución: la construcción de un nuevo orden. La obra de Rafael simboliza precisamente esa búsqueda de estructura, organización y conocimiento. En el fresco aparecen filósofos, matemáticos y científicos de la antigüedad como alegoría del progreso de la humanidad. Salvando distancias, las funciones presentadas representan justamente un gran progreso.
Power BI Ninja 