Generieke formule
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Samenvatting
Als u unieke waarden dynamisch wilt sorteren en extraheren uit een lijst met gegevens, kunt u een matrixformule gebruiken om een rangschikking in een hulpkolom vast te stellen, en vervolgens een speciaal samengestelde INDEX- en MATCH-formule gebruiken om unieke waarden te extraheren. In het getoonde voorbeeld is de formule om de rang in C5: C13 vast te stellen:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
waarbij "data" het benoemde bereik B5: B13 is.
Opmerking: dit is een matrixformule met meerdere cellen, ingevoerd met control + shift + enter.
Uitleg
Opmerking: het kernidee van deze formule is aangepast aan een voorbeeld in het uitstekende boek Control + Shift + Enter van Mike Girvin.
Het getoonde voorbeeld maakt gebruik van verschillende formules, die hieronder worden beschreven. Op een hoog niveau wordt de functie MMULT gebruikt om een numerieke rangorde in een hulpkolom (kolom C) te berekenen, en deze rangschikking wordt vervolgens gebruikt door een INDEX- en MATCH-formule in kolom G om unieke waarden te extraheren.
Gegevenswaarden rangschikken
De MMULT-functie voert matrixvermenigvuldiging uit en wordt gebruikt om een numerieke rangschikking aan elke waarde toe te wijzen. De eerste array wordt gemaakt met de volgende uitdrukking:
--(data>TRANSPOSE(data))
Hier gebruiken we de functie TRANSPONEREN om een horizontale reeks gegevens te maken en alle waarden worden met elkaar vergeleken. In wezen wordt elke waarde vergeleken met elke andere waarde om de vraag "is deze waarde groter dan elke andere waarde" te beantwoorden. Dit resulteert in een tweedimensionale array, 9 kolommen x 9 rijen, gevuld met WAAR en ONWAAR waarden. De dubbele negatieve (-) wordt gebruikt om de TRUE FALSE-waarden te dwingen tot enen en nullen. U kunt de resulterende array als volgt visualiseren:
De matrix van 1s en nullen hierboven wordt array1 binnen de MMULT-functie. Array2 wordt gemaakt met deze uitdrukking:
ROW(data)^0
Hier wordt elk rijnummer in "data" verhoogd tot de macht nul om een eendimensionale array te maken, 1 kolom x 9 rijen, gevuld met het getal 1. MMULT retourneert vervolgens het matrixproduct van de twee arrays, die de waarden die worden gezien in de rangschikkingskolom.
We krijgen alle 9 ranglijsten tegelijkertijd terug in een array, dus we moeten de resultaten allemaal tegelijk in verschillende cellen plaatsen. Anders toont elke cel alleen de eerste rangschikkingswaarde in de matrix die wordt geretourneerd.
Opmerking: dit is een matrixformule met meerdere cellen, ingevoerd met control + shift + enter, in het bereik C5: C13.
Omgaan met lege cellen
Lege cellen worden afgehandeld met dit deel van de rangschikkingsformule:
=IF(data="",ROWS(data)
Hier, voordat we MMULT uitvoeren, controleren we of de huidige cel in "data" leeg is. Als dit het geval is, kennen we een rangwaarde toe die gelijk is aan het aantal rijen in gegevens. Dit wordt gedaan om lege cellen onder aan de lijst te plaatsen, waar ze later gemakkelijk kunnen worden uitgesloten wanneer unieke waarden worden geëxtraheerd (hieronder uitgelegd).
Unieke waarden tellen
Om unieke waarden in de gegevens te tellen, is de formule in E5:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Omdat de bovenstaande rangschikkingsformule aan elke waarde een numerieke rangorde toekent, kunnen we de functie FREQUENCY met SUM gebruiken om unieke waarden te tellen. Deze formule wordt hier in detail uitgelegd. We trekken dan 1 af van het resultaat als er lege cellen in de gegevens zijn:
-(blank>0)
waarbij "blanco" het benoemde bereik E8 is, en deze formule bevat:
=COUNTBLANK(data)
In wezen verminderen we het unieke aantal met één als er lege cellen in de gegevens staan, aangezien we deze niet in de resultaten opnemen. De unieke telling in cel E5 wordt "uniek" genoemd (voor unieke telling) en wordt gebruikt door de INDEX- en MATCH-formule om lege cellen uit te filteren (hieronder beschreven).
Unieke waarden extraheren
Om unieke waarden te extraheren, bevat G5 de volgende formule, naar beneden gekopieerd:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Voordat we de INDEX- en MATCH-formule uitvoeren, controleren we eerst of het huidige aantal rijen in het extractiegebied groter is dan het unieke aantal, het benoemde bereik "uniek" (E5):
=IF(ROWS($G$5:G5)>unique,"",
Als dat het geval is, zijn we klaar met het extraheren van unieke waarden en retourneren we een lege string (""). Zo niet, dan voeren we de extractieformule uit:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Merk op dat er hier twee MATCH-functies zijn, de een in de ander. De binnenste MATCH gebruikt een uitbreidend bereik voor een array en het benoemde bereik "data" voor de opzoekwaarde:
MATCH(data,$G$4:G4,0)
Merk op dat het uitbreidende bereik begint op de "rij erboven", rij 4 in het voorbeeld. Het resultaat van de binnenste MATCH is een array die, voor elke waarde in data, ofwel een numerieke positie bevat (de waarde is al geëxtraheerd) of de # N / A-fout (de waarde is nog niet geëxtraheerd). We gebruiken vervolgens IF en ISNA om deze resultaten te filteren en de rangwaarde te retourneren voor alle waarden in "gegevens" die nog niet zijn geëxtraheerd:
IF(ISNA(results),rank))
Deze bewerking resulteert in een array die wordt ingevoerd in de MIN-functie om de "minimum rangwaarde" te krijgen voor gegevenswaarden die nog niet zijn geëxtraheerd. De MIN-functie retourneert deze waarde naar de buitenste MATCH als een opzoekwaarde en het benoemde bereik "rank" als de array:
MATCH(min_not_extracted,rank)),rank,0)
Ten slotte retourneert MATCH de positie van de laagste rangwaarde naar INDEX als een rijnummer, en INDEX retourneert de gegevenswaarde in de huidige rij van het extractiebereik.
