Bine, așa că, în primele zile de analiză a datelor, am avut nevoie să obțin automat elementele unice din Excel dintr-o listă, mai degrabă decât să le elimin de fiecare dată. Și am găsit o modalitate de a o face. După aceea, tabloul meu de bord Excel a fost chiar mai dinamic decât înainte. Deci, să vedem cum o putem face …
Pentru a extrage lista valorilor unice dintr-o listă, vom folosi INDEX, MATCH și COUNTIF. De asemenea, voi folosi IFERROR, doar pentru a avea rezultate curate, opțional.
Și da, va fi o formulă matrice … Deci, să o facem …
Formula generică pentru a extrage valori unice în Excel
{= INDEX (ref_list, MATCH (0, COUNTIF (expanding_output_range, ref_list), 0))}
Ref_list: Lista din care doriți să extrageți valori unice
Expanding_output_range: Acum acest lucru este foarte important. Acesta este intervalul în care doriți să vedeți lista extrasă. Această gamă trebuie să aibă un titlu distinct care nu face parte din listă și formula dvs. va fi sub titlu (dacă titlul este în E1, atunci Formula va fi în E2).
Acum, extinderea înseamnă că atunci când trageți în jos formula, aceasta ar trebui să se extindă peste intervalul de ieșire. Pentru a face acest lucru, trebuie să indicați titlul ca $ E $ 1: E1 (Rubrica mea este în E1). Când îl voi trage în jos, se va extinde. În E2, va fi $ E $ 1: E1. În E3, va fi $ E $ 1: E2. În E2, va fi $ E $ 1: E3 și așa mai departe.
Acum să vedem un exemplu. O va clarifica.
Extragerea valorilor unice Excel Exemplu
Deci, aici am această listă de clienți în Coloană A în raza de acțiune A2: A16. Acum, în coloana E, vreau să obțin valori unice numai de la clienți. Acum, acest interval A2: A16 poate crește și, așa că vreau ca formula mea să aducă orice nume de client nou din listă, ori de câte ori lista crește.
Bine, acum pentru a prelua valori unice din coloana A scrieți această formulă în Celula E2, și a lovit CTRL + SHIFT + ENTER pentru a-l face o formulă matrice.
{= INDEX ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: E1, $ A $ 2: A16), 0))}
A $ 2: A16: Mă aștept ca această listă să se extindă și să aibă noi valori unice pe care să-mi placă să le extrag. De aceea l-am lăsat deschis de jos fără referință absolută a A16. Îi va permite să se extindă ori de câte ori copiați formula de mai jos.
Deci, știm cum funcționează funcția INDEX și MATCH. Partea principală aici este:
COUNTIF ($ E $ 1: E1, $ A $ 2: A16): Această formulă va returna o matrice de 1 și 0. Ori de câte ori o valoare în interval $ E $ 1: E1 se găsește în lista de criterii $ A $ 2: A16, valoarea se convertește în 1 la poziția sa în intervalul $ A $ 2: A16.
Acum, folosind funcția MATCH, căutăm valorile 0. Match va întoarce poziția primului 0 găsit în matrice returnat de funcția COUNTIF. Decât se va analiza INDEX A $ 2: A16 pentru a returna valoarea găsită la indexul returnat de funcția MATCH.
Poate că este puțin greu de înțeles, dar funcționează. 0 din sfârșitul listei indică faptul că nu mai există valori unice. Dacă nu vedeți acel 0 la final, ar trebui să copiați formula în celulele de mai jos.
Acum, pentru a evita #N / A în puteți utiliza funcția IFERROR de excel.
{= IFERROR (INDEX ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: $ E1, $ A $ 2: A16), 0)), "")}
Deci, da, puteți utiliza această formulă pentru a obține valori unice dintr-o listă. În Excel 2019 cu abonament Office 365, Microsoft oferă o funcție numită UNIQUE. Pur și simplu ia un interval ca argument și returnează o serie de valori unice. Nu este disponibil în Microsoft Excel 2016 o singură achiziție.
Descărcare fișier:
Formula Excel pentru a extrage valori unice dintr-o listăFormula Excel pentru a extrage valori unice dintr-o listă
Cum se numără valorile unice în Excel
Articole populare:
50 de comenzi rapide Excel pentru a vă crește productivitatea
Cum se utilizează funcția VLOOKUP în Excel
Cum se utilizează funcția COUNTIF în Excel
Cum se utilizează funcția SUMIF în Excel