Fórmula Excel para extraer los valores únicos de una lista
Bien, en mis primeros días de análisis de datos, tuve la necesidad de obtener automáticamente los elementos únicos en Excel de una lista, en lugar de eliminar los duplicados cada vez. Y descubrí una manera de hacerlo. Después de eso, mi tablero de Excel fue aún más dinámico que antes. Así que veamos cómo podemos hacerlo…
Para extraer una lista de valores únicos de una lista, usaremos link: / lookup-formulas-excel-index-function [INDEX], link: / lookup-formulas-excel-match-function [MATCH]
y enlace: / tips-countif-in-microsoft-excel [COUNTIF].
También usaré enlace: / fórmulas-lógicas-excel-iferror-function [IFERROR], solo para tener resultados limpios, es opcional.
Y sí, será una fórmula de matriz … Así que hagámoslo … ===== Fórmula genérica para extraer valores únicos en Excel
{=INDEX(ref_list,MATCH(0,COUNTIF(expanding_output_range,ref_list),0))}
Ref_list: La lista de la que desea extraer valores únicos enlace: / excel-range-name-expand-reference-in-excel [Expanding_output_range:]
Ahora bien, esto es muy importante. Este es el rango en el que desea ver su lista extraída. Este rango debe tener un encabezado distinto que no sea parte de la lista y su fórmula estará debajo del encabezado (si el encabezado está en E1, la Fórmula estará en E2). Ahora expandir significa que cuando arrastre hacia abajo su fórmula debería expandirse sobre el rango de salida. Para hacerlo, debe dar una referencia al encabezado como $ E $ 1: E1 (Mi encabezado está en E1). Cuando lo arrastre hacia abajo, se expandirá. En E2, será $ E $ 1: E1. En E3, será $ E $ 1: E2. En E2, será $ E $ 1: E3 y así sucesivamente.
Ahora veamos un ejemplo. Lo dejará claro.
Extracción de valores únicos Ejemplo de Excel Así que aquí tengo esta lista de clientes en la Columna A en el rango A2: A16.
Ahora, en la columna E, quiero obtener valores únicos solo de los clientes. Ahora, este rango A2: A16 también puede aumentar, por lo que quiero que mi fórmula obtenga cualquier nombre de cliente nuevo de la lista, siempre que la lista aumente.
Bien, ahora para obtener valores únicos de la columna A, escriba esta fórmula en la celda E2 y presione CTRL + MAYÚS + ENTRAR para convertirla en una fórmula de matriz.
{=INDEX($A$2:A16,MATCH(0,COUNTIF($E$1:E1,$A$2:A16),0))}
A $ 2: A16: Espero que la lista se expanda y pueda tener nuevos valores únicos que me gustaría extraer. Por eso lo he dejado abierto desde abajo por referencia no absoluta de A16. Le permitirá expandirse cada vez que copie la fórmula a continuación.
Así que sabemos enlace: / buscar-fórmulas-recuperar-valores-coincidentes-de-una-lista-no-adyacente [cómo funciona la función INDICE y COINCIDIR]. La parte principal aquí es:
CONTAR.SI ($ E $ 1: E1, $ A $ 2: A16): esta fórmula devolverá una matriz de 1 y 0. Siempre que se encuentre un valor en el rango $ E $ 1: E1 en la lista de criterios $ A $ 2: A16, el valor se convierte en 1 en su posición en el rango $ A $ 2: A16.
Ahora, usando la función COINCIDIR, estamos buscando valores 0. Coincidir devolverá la posición del primer 0 encontrado en la matriz devuelta por la función CONTAR. Entonces INDEX buscará en A $ 2: A16 para devolver el valor encontrado en el índice devuelto por la función COINCIDIR.
Quizás sea un poco difícil de entender, pero funciona. El 0 al final de la lista indica que no hay más valores únicos. Si no ve ese 0 al final, debe copiar la fórmula en las celdas siguientes.
Ahora, para evitar #NA en, puede usar la función IFERROR de Excel.
{=IFERROR(INDEX($A$2:A16,MATCH(0,COUNTIF($E$1:$E1,$A$2:A16),0)),"")}
Entonces, sí, puede usar esta fórmula para obtener valores únicos de una lista. En Excel 2019 con suscripción a Office 365, Microsoft ofrece una función denominada ÚNICA. Simplemente toma un rango como argumento y devuelve una matriz de valores únicos. No está disponible en la compra única de Microsoft Excel 2016.