Generisk formel
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Sammendrag
For å dynamisk sortere og trekke ut unike verdier fra en liste over data, kan du bruke en matriseformel for å etablere en rangering i en hjelpekolonne, og deretter bruke en spesialkonstruert INDEX- og MATCH-formel for å trekke ut unike verdier. I eksemplet vist er formelen for å etablere rangering i C5: C13:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
der "data" er det navngitte området B5: B13.
Merk: dette er en formel med flere celler, angitt med kontroll + skift + enter.
Forklaring
Merk: kjernetanken til denne formelen er tilpasset et eksempel i Mike Girvins utmerkede bok Control + Shift + Enter.
Eksemplet som vises bruker flere formler, som er beskrevet nedenfor. På et høyt nivå brukes MMULT-funksjonen til å beregne en numerisk rangering i en hjelpekolonne (kolonne C), og denne rangen brukes deretter av en INDEKS- og MATCH-formel i kolonne G for å trekke ut unike verdier.
Rangering av dataverdier
MMULT-funksjonen utfører matrisemultiplikasjon og brukes til å tilordne en numerisk rangering til hver verdi. Den første matrisen er opprettet med følgende uttrykk:
--(data>TRANSPOSE(data))
Her bruker vi TRANSPOSE-funksjonen til å opprette et horisontalt utvalg av data , og alle verdier sammenlignes med hverandre. I hovedsak blir hver verdi sammenlignet med annenhver verdi for å svare på spørsmålet "er denne verdien større enn alle andre verdier". Dette resulterer i en todimensjonal matrise, 9 kolonner x 9 rader, fylt med SANNE og FALSE verdier. Dobbeltnegativet (-) brukes til å tvinge de SANNE FALSE verdiene til 1s og nuller. Du kan visualisere den resulterende matrisen slik:
Matrisen på 1s og nuller ovenfor blir array1 inne i MMULT-funksjonen. Array2 er opprettet med dette uttrykket:
ROW(data)^0
Her blir hvert radnummer i "data" hevet til kraften null for å lage en endimensjonal matrise, 1 kolonne x 9 rader, fylt med tallet 1. MMULT returnerer deretter matriksproduktet til de to matriser, som blir verdier sett i rangkolonnen.
Vi får tilbake alle 9 rangeringene samtidig i en matrise, så vi må legge resultatene inn i forskjellige celler samtidig. Ellers vil hver celle bare vise den første rangeringsverdien i matrisen som returneres.
Merk: dette er en formel med flere celler, angitt med kontroll + skift + enter, i området C5: C13.
Håndtering av blanke celler
Tomme celler håndteres med denne delen av rangeringsformelen:
=IF(data="",ROWS(data)
Her, før vi kjører MMULT, sjekker vi om den nåværende cellen i "data" er tom. I så fall tildeler vi en rangverdi som tilsvarer antall rader i data. Dette gjøres for å tvinge blanke celler til bunnen av listen, der de lett kan ekskluderes senere når unike verdier blir ekstrahert (forklart nedenfor).
Teller unike verdier
For å telle unike verdier i dataene, er formelen i E5:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Siden rangeringsformelen ovenfor tildeler en numerisk rangering til hver verdi, kan vi bruke FREKVENS-funksjonen med SUM for å telle unike verdier. Denne formelen er forklart i detalj her. Vi trekker deretter 1 fra resultatet hvis det er noen tomme celler i dataene:
-(blank>0)
hvor "blank" er det navngitte området E8, og inneholder denne formelen:
=COUNTBLANK(data)
I hovedsak reduserer vi det unike antallet med en hvis det er tomme celler i dataene, siden vi ikke inkluderer disse i resultatene. Den unike tellingen i celle E5 heter "unik" (for unik telling), og brukes av INDEX og MATCH-formelen for å filtrere ut tomme celler (beskrevet nedenfor).
Henter unike verdier
For å trekke ut unike verdier inneholder G5 følgende formel, kopiert ned:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Før vi kjører INDEX- og MATCH-formelen, sjekker vi først om det nåværende radtallet i ekstraksjonsområdet er større enn det unike antallet det navngitte området "unikt" (E5):
=IF(ROWS($G$5:G5)>unique,"",
I så fall er vi ferdige med å trekke ut unike verdier, og vi returnerer en tom streng (""). Hvis ikke, kjører vi ekstraksjonsformelen:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Merk at det er to MATCH-funksjoner her, den ene i den andre. Den indre MATCH bruker et utvidende område for en matrise og det navngitte området "data" for oppslagsverdien:
MATCH(data,$G$4:G4,0)
Legg merke til at det utvidende området begynner på "rad over", rad 4 i eksemplet. Resultatet fra den indre MATCH er en matrise som for hver verdi i data inneholder enten en numerisk posisjon (verdien er allerede ekstrahert) eller # N / A-feilen (verdien er ikke ekstrahert ennå). Vi bruker deretter IF og ISNA for å filtrere disse resultatene, og returnerer rangverdien for alle verdier i "data" som ennå ikke er ekstrahert:
IF(ISNA(results),rank))
Denne operasjonen resulterer i en matrise som mates inn i MIN-funksjonen for å få "minimum rangverdi" for dataverdier som ennå ikke er ekstrahert. MIN-funksjonen returnerer denne verdien til den ytre MATCH som en oppslagsverdi, og det navngitte området "rangeres" som matrisen:
MATCH(min_not_extracted,rank)),rank,0)
Til slutt returnerer MATCH posisjonen til den laveste rangverdien til INDEX som et radnummer, og INDEX returnerer dataverdien i gjeldende rad i ekstraksjonsområdet.
