Generisk formel
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Resumé
For dynamisk at sortere og udtrække unikke værdier fra en dataliste kan du bruge en matrixformel til at etablere en rang i en hjælpekolonne og derefter bruge en specielt konstrueret INDEX- og MATCH-formel til at udtrække unikke værdier. I det viste eksempel er formlen til at etablere rang i C5: C13:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
hvor "data" er det navngivne område B5: B13.
Bemærk: dette er en formel med flere celler, indtastet med kontrol + skift + enter.
Forklaring
Bemærk: kerneideen med denne formel er tilpasset fra et eksempel i Mike Girvins fremragende bog Control + Shift + Enter.
Det viste eksempel bruger flere formler, som er beskrevet nedenfor. På et højt niveau bruges MMULT-funktionen til at beregne en numerisk rang i en hjælpekolonne (kolonne C), og denne rang bruges derefter af en INDEX- og MATCH-formel i kolonne G for at udtrække unikke værdier.
Rangordning af dataværdier
MMULT-funktionen udfører matrixmultiplikation og bruges til at tildele en numerisk rang til hver værdi. Den første matrix oprettes med følgende udtryk:
--(data>TRANSPOSE(data))
Her bruger vi TRANSPOSE-funktionen til at oprette et vandret array af data , og alle værdier sammenlignes med hinanden. I det væsentlige sammenlignes hver værdi med hver anden værdi for at besvare spørgsmålet "er denne værdi større end enhver anden værdi". Dette resulterer i et todimensionelt array, 9 kolonner x 9 rækker, fyldt med SAND og FALSK værdier. Den dobbelte negative (-) bruges til at tvinge de SANDE FALSKE værdier til 1s og nuller. Du kan visualisere det resulterende array som dette:
Matricen på 1s og nuller ovenfor bliver array1 inde i MMULT-funktionen. Array2 oprettes med dette udtryk:
ROW(data)^0
Her hæves hvert række i "data" til magten nul for at skabe et endimensionelt array, 1 kolonne x 9 rækker, fyldt med tallet 1. MMULT returnerer derefter matrixproduktet for de to arrays, som bliver værdier set i rangkolonnen.
Vi får alle 9 placeringer tilbage på samme tid i en matrix, så vi er nødt til at placere resultaterne i forskellige celler på én gang. Ellers viser hver celle kun den første placeringsværdi i det array, der returneres.
Bemærk: dette er en formel med flere celler, der er indtastet med kontrol + skift + enter, i området C5: C13.
Håndtering af blanke celler
Tomme celler håndteres med denne del af rangformlen:
=IF(data="",ROWS(data)
Her, før vi kører MMULT, kontrollerer vi, om den aktuelle celle i "data" er tom. I så fald tildeler vi en rangværdi, der svarer til antallet af rækker i data. Dette gøres for at tvinge tomme celler til bunden af listen, hvor de let kan udelukkes senere, når unikke værdier ekstraheres (forklaret nedenfor).
Tæller unikke værdier
For at tælle unikke værdier i dataene er formlen i E5:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Da rangeringsformlen ovenfor tildeler en numerisk rang til hver værdi, kan vi bruge FREKVENS-funktionen med SUM til at tælle unikke værdier. Denne formel forklares detaljeret her. Vi trækker derefter 1 fra resultatet, hvis der er tomme celler i dataene:
-(blank>0)
hvor "blank" er det navngivne område E8 og indeholder denne formel:
=COUNTBLANK(data)
I det væsentlige reducerer vi det unikke antal med en, hvis der er tomme celler i dataene, da vi ikke inkluderer disse i resultaterne. Den unikke optælling i celle E5 hedder "unik" (til unik optælling) og bruges af INDEX og MATCH-formlen til at filtrere tomme celler ud (beskrevet nedenfor).
Uddrag af unikke værdier
For at udtrække unikke værdier indeholder G5 følgende formel, kopieret 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 kører INDEX- og MATCH-formlen, kontrollerer vi først, om det aktuelle rækkeantal i ekstraktionsområdet er større end det unikke antal det navngivne interval "unikt" (E5):
=IF(ROWS($G$5:G5)>unique,"",
Hvis ja, er vi færdige med at udtrække unikke værdier, og vi returnerer en tom streng (""). Hvis ikke, kører vi ekstraktionsformlen:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Bemærk, at der er to MATCH-funktioner her, den ene inden i den anden. Den indre MATCH bruger et ekspanderende interval for en matrix og det navngivne interval "data" til opslagsværdien:
MATCH(data,$G$4:G4,0)
Bemærk det udvidede interval begynder på "rækken over", række 4 i eksemplet. Resultatet fra den indre MATCH er en matrix, der for hver værdi i data indeholder enten en numerisk position (værdien er allerede blevet ekstraheret) eller # N / A-fejlen (værdien er endnu ikke blevet ekstraheret). Vi bruger derefter IF og ISNA til at filtrere disse resultater og returnere rangværdien for alle værdier i "data", der endnu ikke er ekstraheret:
IF(ISNA(results),rank))
Denne operation resulterer i en matrix, der føres ind i MIN-funktionen for at få den "mindste rangværdi" for dataværdier, der endnu ikke er ekstraheret. MIN-funktionen returnerer denne værdi til den ydre MATCH som en opslagsværdi, og det navngivne interval "rangeres" som arrayet:
MATCH(min_not_extracted,rank)),rank,0)
Endelig returnerer MATCH placeringen af den laveste rangværdi til INDEX som et række nummer, og INDEX returnerer dataværdien i den aktuelle række i ekstraktionsområdet.
