De acuerdo, 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 incluso más dinámico que antes. Así que veamos cómo podemos hacerlo …
Para extraer una lista de valores únicos de una lista, usaremos ÍNDICE, COINCIDIR y CONTAR.SI. También usaré 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 (expand_output_range, ref_list), 0))}
Ref_list: La lista de la que desea extraer valores únicos.
Expanding_output_range: Ahora esto es muy importante. Este es el rango donde 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 la referencia del encabezado como $ E $ 1: E1 (Mi título 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 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.
Ok, ahora para obtener valores únicos de la columna A, escriba esta fórmula en Celda E2, y golpea CTRL + MAYÚS + ENTRAR para convertirlo en una fórmula de matriz.
{= ÍNDICE ($ A $ 2: A16, COINCIDIR (0, CONTAR.SI ($ 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. Es por eso que lo he dejado abierto desde abajo por referencia no absoluta de A16. Permitirá que se expanda siempre que copie la fórmula a continuación.
Entonces sabemos 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 unos y ceros. Siempre que un valor en el rango $ E $ 1: E1 se encuentra 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 buscamos valores 0. Coincidir devolverá la posición del primer 0 encontrado en la matriz devuelta por la función CONTAR. Que INDEX analizará 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 #N / A en puede usar la función IFERROR de Excel.
{= SIERROR (ÍNDICE ($ A $ 2: A16, COINCIDIR (0, CONTAR.SI ($ 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.
Descargar archivo:
Fórmula de Excel para extraer valores únicos de una listaFórmula de Excel para extraer valores únicos de una lista
Cómo contar valores únicos en Excel
Articulos populares:
50 accesos directos de Excel para aumentar su productividad
Cómo utilizar la función BUSCARV en Excel
Cómo usar la función CONTAR.SI en Excel
Cómo utilizar la función SUMIF en Excel
