El otro día, estaba a punto de crear una combinación única de dos columnas no adyacentes en Excel. Normalmente hago esto con Eliminar duplicados o con Filtro avanzado, pero pensé que intentaría hacerlo con la nueva función ÚNICA que llegará a Office 365 en 2019. Probé varias ideas y ninguna funcionaría. Entonces, fui al maestro de Dynamic Arrays, Joe McDaid, para obtener ayuda. La respuesta es muy buena y estoy seguro de que la olvidaré, así que la estoy documentando para ti y para mí. Estoy seguro de que, dentro de dos años, buscaré en Google cómo hacer esto y me daré cuenta "¡Oh, mira! ¡Yo soy el que escribió el artículo sobre esto!"
Antes de llegar a la función ÚNICA, eche un vistazo a lo que estoy tratando de hacer. Quiero cada combinación única de Representante de ventas de la columna B y Producto de la columna C. Normalmente, seguiría estos pasos:
- Copie los títulos de B1 y D1 en una sección en blanco de la hoja de trabajo
- Desde B1, elija Datos, Filtro, Avanzado
- En el cuadro de diálogo Filtro avanzado, elija Copiar en una nueva ubicación
- Especifique los títulos del Paso 1 como Rango de salida
- Elija la casilla Solo para valores únicos
- Haga clic en Aceptar
El resultado es cada combinación única de los dos campos. Tenga en cuenta que el Filtro avanzado no ordena los elementos, aparecen en la secuencia original.
Este proceso se hizo más fácil en Excel 2010 gracias al comando Eliminar duplicados en la pestaña Datos de la cinta. Sigue estos pasos:
- Seleccione B1: D227 y Ctrl + C para copiar
-
Pegue en una sección en blanco de la hoja de trabajo.
Haga una copia de los datos, ya que Eliminar duplicados es destructivo - Elija datos, elimine duplicados
- En el cuadro de diálogo Eliminar duplicados, anule la selección de Fecha. Esto le dice a Excel que solo mire Rep y Producto.
-
Haga clic en Aceptar
Dile a Eliminar duplicados que solo considere Rep y Fecha
Los resultados son casi perfectos, solo tiene que eliminar la columna Fecha.
La pregunta: ¿Hay alguna forma de que la función ÚNICA mire solo las columnas B y D? (Si aún no ha visto la nueva función ÚNICA, lea: Función ÚNICA en Excel).
Si solicita =UNIQUE(B2:D227), obtendrá todas las combinaciones únicas de Representante, Fecha y Producto, que no es lo que estamos buscando.
Cuando se introdujeron las matrices dinámicas en septiembre, dije que nunca más tendríamos que preocuparnos por las complejidades de las fórmulas Ctrl + Shift + Enter. Pero para resolver este problema, utilizará un concepto llamado Lifting. Es de esperar que a estas alturas ya haya descargado mi libro electrónico de Matrices dinámicas de Excel Directo al punto. Consulte las páginas 31-33 para obtener una explicación completa de la elevación.
Tome una función de Excel que espera un valor único. Por ejemplo, =CHOOSE(Z1,"Apple","Banana")devolvería Apple o Banana dependiendo de si Z1 contiene 1 (para Apple) o 2 (para Banana). La función CHOOSE espera un escalar como primer argumento.
Pero en cambio, pasará una constante de matriz de (1,2) como primer argumento para ELEGIR. Excel realizará la operación de elevación y calculará ELEGIR dos veces. Para el valor de 1, desea los representantes de ventas en B2: B227. Para el valor de 2, desea los productos en D2: D227.
Normalmente, en el antiguo Excel, la intersección implícita habría arruinado los resultados. Pero ahora que Excel puede derramar resultados en muchas celdas, la fórmula anterior devuelve con éxito una matriz de todas las respuestas en B y D:
Siento que estaría insultando tu inteligencia para escribir el resto del artículo, porque a partir de aquí es súper simple.
Envuelva la fórmula de la captura de pantalla anterior en ÚNICO y obtendrá solo las combinaciones únicas de Representante de ventas y Producto que usa =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).
Para comprobar su comprensión, intente cambiar la fórmula anterior para devolver todas las combinaciones únicas de tres columnas: Representante de ventas, Producto, Color.
Primero, cambie la constante de matriz para hacer referencia a (1,2,3).
A continuación, añadir un cuarto argumento a elegir devolver el color de E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).
Sería bueno ordenar esos resultados, por lo que pasamos a Ordenar con una fórmula usando SORT y SORTBY.
Normalmente, la función para ordenar por la primera columna ascendente sería =SORT(Array)o =SORT(Array,1,1).
Para ordenar por tres columnas, necesita hacer algunos levantamientos por pares con =SORT(Array,(1,2,3),(1,1,1)). En esta fórmula, cuando llega al segundo argumento de SORT, Excel quiere saber por qué columna ordenar. En lugar de un solo valor, envíe tres columnas dentro de una matriz constante: (1,2,3). Cuando llegue al tercer argumento donde especifique 1 para Ascendente o -1 para Descendente, envíe una matriz constante con tres unos para indicar Ascendente, Ascendente, Ascendente. Se muestra la siguiente captura de pantalla =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).
Al menos hasta finales de 2018, puede descargar el libro de Matrices dinámicas de Excel de forma gratuita utilizando el enlace al final de esta página.
Me alienta descubrir que la respuesta a la pregunta de hoy es un poco complicada. Cuando salió Dynamic Arrays, pensé instantáneamente en todas las fórmulas asombrosas publicadas en el Tablero de mensajes por Aladin Akyurek y otros y cómo esas fórmulas se volverían mucho más simples en el nuevo Excel. Pero el ejemplo de hoy muestra que todavía habrá una necesidad de que los genios de las fórmulas creen nuevas formas de usar las matrices dinámicas.
Ver video
Descargar archivo de Excel
Para descargar el archivo de Excel: exclusivo de columnas no adyacentes.xlsx
Pensamiento del día de Excel
Les he pedido a mis amigos de Excel Master sus consejos sobre Excel. Pensamiento de hoy para reflexionar:
"Reglas para listas: sin filas en blanco, sin columnas en blanco, encabezados de una celda, como con"
Anne Walsh
