V redu, zato sem v prvih dneh analize podatkov moral samodejno dobiti edinstvene postavke v Excelu s seznama, namesto da jih vsakič odstranim. In ugotovil sem, kako to narediti. Po tem je bila moja nadzorna plošča Excel še bolj dinamična kot prej. Pa poglejmo, kako lahko to naredimo …
Za izvlečenje seznama edinstvenih vrednosti s seznama bomo uporabili INDEX, MATCH in COUNTIF. Uporabil bom tudi IFERROR, samo za čiste rezultate, neobvezno.
In ja, to bo formula matrike … Torej, naredimo to …
Splošna formula za ekstrahiranje edinstvenih vrednosti v Excelu
{= INDEX (ref_list, MATCH (0, COUNTIF (razširjanje_izstopnega_ obsega, ref_list), 0))}
Ref_list: Seznam, iz katerega želite izvleči edinstvene vrednosti
Expanding_output_range: Zdaj je to zelo pomembno. To je obseg, kjer želite videti svoj izvlečeni seznam. Ta razpon mora imeti ločen naslov, ki ni del seznama, vaša formula pa bo pod naslovom (če je naslov v E1, bo formula v E2).
Zdaj razširitev pomeni, da se mora formula, ko povlečete navzdol, razširiti na izhodno območje. Če želite to narediti, se morate sklicevati na naslov kot $ E $ 1: E1 (Moj naslov je v E1). Ko ga povlečem navzdol, se bo razširil. V E2 bo tako $ E $ 1: E1. V E3 bo tako $ E $ 1: E2. V E2 bo tako $ E $ 1: E3 in tako naprej.
Zdaj pa poglejmo primer. Postalo bo jasno.
Ekstrahiranje edinstvenih vrednosti Primer Excel
Torej imam tukaj seznam strank v stolpcu A v dosegu A2: A16. Zdaj v stolpcu E želim edinstvene vrednosti dobiti le od strank. Zdaj se lahko poveča tudi to območje A2: A16, zato želim, da moja formula pridobi vsako novo ime stranke s seznama, kadar koli se seznam poveča.
V redu, zdaj za pridobitev edinstvenih vrednosti iz stolpca A vnesite to formulo Celica E2, in zadel CTRL+SHIFT+ENTER da bo formula matrike.
{= INDEX ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: E1, $ A $ 2: A16), 0))}
2 USD: A16: Pričakujem, da se bo ta seznam razširil in morda imel nove edinstvene vrednosti, ki jih bom rad izvlekel. Zato sem pustil odprto od spodaj brez absolutnega sklicevanja na A16. Omogočil bo, da se razširi vsakič, ko kopirate spodnjo formulo.
Tako vemo, kako delujeta funkciji INDEX in MATCH. Glavni del tukaj je:
COUNTIF ($ E $ 1: E1, $ A $ 2: A16): Ta formula bo vrnila polje 1s in 0s. Kadar koli je vrednost v razponu $ E $ 1: E1 je na seznamu meril $ A $ 2: A16, se vrednost pretvori v 1 na svojem položaju v območju $ A $ 2: A16.
Zdaj z uporabo funkcije MATCH iščemo vrednosti 0. Ujemanje bo vrnilo položaj prvih 0, najdenih v matriki, ki jo vrne funkcija COUNTIF. To bo INDEX preučil 2 USD: A16 vrniti vrednost, najdeno pri indeksu, ki ga vrne funkcija MATCH.
Morda je malo težko razumeti, vendar deluje. 0 na koncu seznama označuje, da ni več edinstvenih vrednosti. Če na koncu ne vidite 0, morate formulo kopirati v spodnje celice.
Zdaj se je treba izogniti #NA lahko uporabite funkcijo IFERROR v Excelu.
{= IFERROR (INDEX ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: $ E1, $ A $ 2: A16), 0)), "")}}
Torej, ja, s to formulo lahko dobite edinstvene vrednosti s seznama. V Excelu 2019 s naročnino na Office 365 Microsoft ponuja funkcijo UNIQUE. Preprosto vzame obseg kot argument in vrne niz edinstvenih vrednosti. Enkratni nakup v programu Microsoft Excel 2016 ni na voljo.
Prenesite datoteko:
Excelova formula za ekstrahiranje edinstvenih vrednosti s seznamaExcelova formula za ekstrahiranje edinstvenih vrednosti s seznama
Kako šteti edinstvene vrednosti v Excelu
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost
Kako uporabljati funkcijo VLOOKUP v Excelu
Kako uporabljati funkcijo COUNTIF v Excelu
Kako uporabljati funkcijo SUMIF v Excelu
