Labi, tāpēc pirmajās datu analīzes dienās man radās nepieciešamība automātiski iegūt unikālos vienumus programmā Excel no saraksta, nevis katru reizi noņemt dublētos vienumus. Un es izdomāju veidu, kā to izdarīt. Pēc tam mans Excel informācijas panelis bija vēl dinamiskāks nekā iepriekš. Tātad, redzēsim, kā mēs to varam izdarīt…
Lai no saraksta iegūtu unikālo vērtību sarakstu, mēs izmantosim INDEX, MATCH un COUNTIF. Es arī izmantošu IFERROR, lai iegūtu tīrus rezultātus, tas nav obligāts.
Un jā, tā būs masīva formula … Tātad, darīsim to …
Vispārīga formula unikālu vērtību iegūšanai programmā Excel
{= INDEKSS (atsauces saraksts, MATCH (0, COUNTIF (izvēršamā_izvades diapazons, atsauces saraksts), 0))}
Ref_list: Saraksts, no kura vēlaties iegūt unikālas vērtības
Expanding_output_range: Tagad tas ir ļoti svarīgi. Šis ir diapazons, kurā vēlaties redzēt savu iegūto sarakstu. Šis diapazons jābūt atsevišķam virsrakstam, kas nav daļa no saraksta, un jūsu formula būs zem virsraksta (ja virsraksts ir E1, tad formula būs E2).
Tagad paplašināšana nozīmē, ka, velkot lejup formulu, tai vajadzētu paplašināties izvades diapazonā. Lai to izdarītu, jums jānorāda virsraksts kā $ 1 $: E1 (Mans virsraksts ir E1). Kad es to velk uz leju, tas paplašinās. E2 tas būs $ 1 $: E1. E3 tas būs $ 1 $: E2. E2 tas būs $ E $ 1: E3 un tā tālāk.
Tagad aplūkosim piemēru. Tas padarīs to skaidru.
Unikālu vērtību iegūšana Excel piemērs
Tātad šeit man ir šis klientu saraksts slejā A diapazonā A2: A16. Tagad E slejā es vēlos iegūt unikālas vērtības tikai no klientiem. Tagad arī šis diapazons A2: A16 var palielināties, tāpēc es vēlos, lai mana formula no saraksta iegūtu jebkuru jaunu klienta vārdu ikreiz, kad saraksts palielinās.
Labi, tagad, lai iegūtu slejas A unikālās vērtības, ierakstiet šo formulu Šūna E2, un sit CTRL+SHIFT+ENTER lai tā būtu masīva formula.
{= INDEKSS ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: E1, $ A $ 2: A16), 0))}
A $ 2: A16: Es ceru, ka šis saraksts tiks paplašināts, un tam var būt jaunas unikālas vērtības, kuras es vēlētos iegūt. Tāpēc es neesmu atstājis to atvērtu no apakšas, ne vienmēr atsaucoties uz A16. Tas ļaus tai paplašināties ikreiz, kad kopējat zemāk esošo formulu.
Tātad mēs zinām, kā darbojas INDEX un MATCH funkcija. Galvenā daļa šeit ir:
COUNTIF ($ E $ 1: E1, $ A $ 2: A16): Šī formula atgriezīs 1s un 0s masīvu. Ikreiz, kad vērtība ir diapazonā $ 1 $: E1 ir atrodams kritēriju sarakstā $ A $ 2: A16, vērtība tiek pārvērsta par 1 savā pozīcijā $ A $ 2: A16.
Tagad, izmantojot funkciju MATCH, mēs meklējam vērtības 0. Atbilstība atgriezīs masīvā atrasto pirmo 0 pozīciju, ko atgriezusi funkcija COUNTIF. Nekā INDEX izskatīs A $ 2: A16 lai atgrieztu vērtību, kas atrodama indeksā, kuru atgriež funkcija MATCH.
Varbūt to ir nedaudz grūti saprast, bet tas darbojas. 0 saraksta beigās norāda, ka vairs nav unikālu vērtību. Ja beigās neredzat 0, jums vajadzētu nokopēt formulu zemāk esošajās šūnās.
Tagad, lai izvairītos #NA jūs varat izmantot Excel IFERROR funkciju.
{= IFERROR (INDEX ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: $ E1, $ A $ 2: A16), 0)), "")}
Jā, jūs varat izmantot šo formulu, lai no saraksta iegūtu unikālas vērtības. Programmā Excel 2019 ar abonementu Office 365 Microsoft piedāvā funkciju UNIQUE. Tas vienkārši ņem diapazonu kā argumentu un atgriež unikālu vērtību masīvu. Tas nav pieejams Microsoft Excel 2016 vienreizējā pirkumā.
Lejupielādēt failu:
Excel formula, lai no saraksta iegūtu unikālas vērtībasExcel formula, lai no saraksta iegūtu unikālas vērtības
Kā saskaitīt unikālas vērtības programmā Excel
Populāri raksti:
50 Excel saīsnes, lai palielinātu produktivitāti
Kā lietot funkciju VLOOKUP programmā Excel
Kā programmā Excel izmantot funkciju COUNTIF
Kā lietot funkciju SUMIF programmā Excel