Generisk formel
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Sammanfattning
För att dynamiskt sortera och extrahera unika värden från en lista med data kan du använda en matrisformel för att skapa en rang i en hjälpkolumn och sedan använda en speciellt konstruerad INDEX- och MATCH-formel för att extrahera unika värden. I exemplet som visas är formeln för att fastställa rang i C5: C13:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
där "data" är det namngivna området B5: B13.
Obs: detta är en formel med flera celler, inmatad med kontroll + skift + enter.
Förklaring
Obs: kärnidén med denna formel är anpassad från ett exempel i Mike Girvins utmärkta bok Control + Shift + Enter.
Det visade exemplet använder flera formler som beskrivs nedan. På en hög nivå används MMULT-funktionen för att beräkna en numerisk rang i en hjälpkolumn (kolumn C), och denna rang används sedan av en INDEX- och MATCH-formel i kolumn G för att extrahera unika värden.
Rankning av datavärden
MMULT-funktionen utför matrixmultiplikation och används för att tilldela en numerisk rangordning till varje värde. Den första matrisen skapas med följande uttryck:
--(data>TRANSPOSE(data))
Här använder vi TRANSPOSE-funktionen för att skapa en horisontell uppsättning data , och alla värden jämförs med varandra. I grund och botten jämförs varje värde med alla andra värden för att svara på frågan "är detta värde större än alla andra värden". Detta resulterar i en tvådimensionell matris, 9 kolumner x 9 rader, fyllda med SANT och FALSKA värden. Den dubbla negativa (-) används för att tvinga de sanna FALSKA värdena till 1s och nollor. Du kan visualisera den resulterande matrisen så här:
Matrisen på 1s och nollor ovan blir array1 inuti MMULT-funktionen. Array2 skapas med detta uttryck:
ROW(data)^0
Här höjs varje radnummer i "data" till kraften noll för att skapa en endimensionell matris, 1 kolumn x 9 rader, fylld med siffran 1. MMULT returnerar sedan matrisprodukten för de två matriserna, som blir värden som visas i rangkolumnen.
Vi får tillbaka alla nio rankningarna samtidigt i en matris, så vi måste lägga resultaten i olika celler samtidigt. Annars visar varje cell bara det första rankningsvärdet i matrisen som returneras.
Obs: detta är en formel med flera celler, inmatad med kontroll + shift + enter, i intervallet C5: C13.
Hantera tomma celler
Tomma celler hanteras med denna del av rankningsformeln:
=IF(data="",ROWS(data)
Här, innan vi kör MMULT, kontrollerar vi om den aktuella cellen i "data" är tom. Om så är fallet tilldelar vi ett rangvärde som motsvarar radantalet i data. Detta görs för att tvinga tomma celler till botten av listan, där de lätt kan uteslutas senare när unika värden extraheras (förklaras nedan).
Räknar unika värden
För att räkna unika värden i data är formeln i E5:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Eftersom rankningsformeln ovan tilldelar en numerisk rangordning för varje värde kan vi använda FREKVENS-funktionen med SUM för att räkna unika värden. Denna formel förklaras i detalj här. Vi subtraherar sedan 1 från resultatet om det finns några tomma celler i data:
-(blank>0)
där "tomt" är det namngivna intervallet E8 och innehåller denna formel:
=COUNTBLANK(data)
I huvudsak minskar vi det unika antalet med en om det finns tomma celler i data, eftersom vi inte inkluderar dessa i resultat. Det unika antalet i cell E5 heter "unikt" (för unikt antal) och används av INDEX- och MATCH-formeln för att filtrera bort tomma celler (beskrivs nedan).
Extraherar unika värden
För att extrahera unika värden innehåller G5 följande formel, kopierad:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Innan vi kör INDEX- och MATCH-formeln kontrollerar vi först om det aktuella radantalet i extraktionsområdet är större än det unika antalet det namngivna intervallet "unikt" (E5):
=IF(ROWS($G$5:G5)>unique,"",
Om så är fallet är vi färdiga med att extrahera unika värden och vi returnerar en tom sträng (""). Om inte, kör vi extraktionsformeln:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Observera att det finns två MATCH-funktioner här, en inuti den andra. Den inre MATCHEN använder ett expanderande intervall för en matris och det namngivna intervallet "data" för uppslagsvärdet:
MATCH(data,$G$4:G4,0)
Lägg märke till att det expanderande intervallet börjar på "raden ovan", rad 4 i exemplet. Resultatet från den inre MATCHEN är en matris som för varje värde i data innehåller antingen en numerisk position (värdet har redan extraherats) eller # N / A-felet (värdet har ännu inte extraherats). Vi använder sedan IF och ISNA för att filtrera dessa resultat och returnera rangvärdet för alla värden i "data" som ännu inte extraherats:
IF(ISNA(results),rank))
Denna operation resulterar i en matris som matas in i MIN-funktionen för att få "minsta rankningsvärde" för datavärden som ännu inte extraherats. Funktionen MIN returnerar det här värdet till den yttre MATCH som ett uppslagsvärde och det namngivna området "rankas" som matrisen:
MATCH(min_not_extracted,rank)),rank,0)
Slutligen returnerar MATCH positionen för det lägsta rangvärdet till INDEX som ett radnummer och INDEX returnerar datavärdet i den aktuella raden i extraktionsområdet.
