Vispārēja formula
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Kopsavilkums
Lai dinamiski kārtotu un iegūtu unikālas vērtības no datu saraksta, varat izmantot masīva formulu, lai izveidotu rangu palīga kolonnā, pēc tam izmantojiet īpaši izveidotu INDEX un MATCH formulu, lai iegūtu unikālas vērtības. Parādītajā piemērā formula, lai noteiktu rangu C5: C13 ir:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
kur "dati" ir nosauktais diapazons B5: B13.
Piezīme: šī ir vairāku šūnu masīva formula, kas ievadīta ar vadības taustiņu + shift + enter.
Paskaidrojums
Piezīme: šīs formulas pamatideja ir pielāgota no piemēra Maika Girvina lieliskajā grāmatā Control + Shift + Enter.
Parādītajā piemērā tiek izmantotas vairākas formulas, kuras ir aprakstītas tālāk. Augstā līmenī funkciju MMULT izmanto, lai aprēķinātu skaitlisko rangu palīgkolonnā (kolonna C), un pēc tam šo rangu izmanto G slejas INDEX un MATCH formula, lai iegūtu unikālas vērtības.
Datu vērtību ranžēšana
Funkcija MMULT veic matricas reizināšanu un tiek izmantota, lai katrai vērtībai piešķirtu skaitlisku rangu. Pirmais masīvs tiek izveidots ar šādu izteiksmi:
--(data>TRANSPOSE(data))
Šeit mēs izmantojam TRANSPOSE funkciju, lai izveidotu horizontālu datu masīvu , un visas vērtības tiek salīdzinātas viena ar otru. Būtībā katra vērtība tiek salīdzināta ar katru citu vērtību, lai atbildētu uz jautājumu "vai šī vērtība ir lielāka par visām citām vērtībām". Rezultātā tiek iegūts divdimensiju masīvs, 9 kolonnas x 9 rindas, kas aizpildītas ar TRUE un FALSE vērtībām. Dubultnegatīvs (-) tiek izmantots, lai TRUE FALSE vērtības piespiestu 1s un nullēm. Iegūto masīvu varat vizualizēt šādi:
No 1s un nullēm matrica iepriekš kļūst masīvs1 iekšpusē MMULT funkciju. Masīvs2 ir izveidots ar šo izteicienu:
ROW(data)^0
Katrs rindas skaitlis "datos" tiek palielināts līdz nulles jaudai, lai izveidotu viendimensiju masīvu, 1 kolonna x 9 rindas, kas piepildītas ar skaitli 1. Pēc tam MMULT atgriež divu masīvu matricas reizinājumu, kas kļūst par vērtības, kas redzamas rangu slejā.
Mēs atgūstam visus 9 klasifikācijas vienlaicīgi masīvā, tāpēc mums ir jāievieto rezultāti vienlaikus dažādās šūnās. Pretējā gadījumā katrā šūnā tiks parādīta tikai pirmā vērtējuma vērtība masīvā, kas tiek atgriezta.
Piezīme: šī ir vairāku šūnu masīva formula, kas ievadīta ar vadības taustiņu + shift + enter diapazonā C5: C13.
Darbība ar tukšajām šūnām
Tukšās šūnas tiek apstrādātas ar šo ranžēšanas formulas daļu:
=IF(data="",ROWS(data)
Šeit, pirms palaidām MMULT, mēs pārbaudām, vai pašreizējā šūna "datos" ir tukša. Ja tā, mēs piešķiram ranga vērtību, kas vienāda ar rindu skaitu datos. Tas tiek darīts, lai tukšās šūnas piespiestu saraksta apakšai, kur tās vēlāk var viegli izslēgt, kad tiek iegūtas unikālas vērtības (paskaidrots tālāk).
Tiek skaitītas unikālas vērtības
Lai datos uzskaitītu unikālas vērtības, formula E5 ir šāda:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Tā kā augstāk esošā vērtēšanas formula katrai vērtībai piešķir skaitlisku rangu, unikālo vērtību skaitīšanai mēs varam izmantot funkciju FREQUENCY ar SUM. Šī formula ir detalizēti paskaidrota šeit. Tad no rezultāta mēs atņemam 1, ja datos ir tukšas šūnas:
-(blank>0)
kur "tukšs" ir nosauktais diapazons E8 un satur šo formulu:
=COUNTBLANK(data)
Būtībā mēs samazinām unikālo skaitu par vienu, ja datos ir tukšas šūnas, jo mēs tos neiekļaujam rezultātos. Unikālais skaits šūnā E5 tiek nosaukts par "unikālu" (unikālajam skaitam), un to izmanto formula INDEX un MATCH, lai filtrētu tukšās šūnas (aprakstīts tālāk).
Unikālu vērtību iegūšana
Lai iegūtu unikālas vērtības, G5 ir šāda kopēta formula:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Pirms palaižam formulu INDEX un MATCH, mēs vispirms pārbaudām, vai pašreizējais rindu skaits ekstrakcijas apgabalā ir lielāks par unikālo skaitu, kuru nosauktais diapazons ir "unikāls" (E5):
=IF(ROWS($G$5:G5)>unique,"",
Ja tā, mēs esam pabeiguši unikālu vērtību iegūšanu un mēs atgriežam tukšu virkni (""). Ja nē, mēs izpildām ekstrakcijas formulu:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Ņemiet vērā, ka šeit ir divas MATCH funkcijas, viena iekšpusē. Iekšējā MATCH masīvam izmanto paplašināšanas diapazonu un nosaukto diapazonu "dati" uzmeklēšanas vērtībai:
MATCH(data,$G$4:G4,0)
Ievērojiet, ka paplašināšanas diapazons sākas ar “rindu virs”, 4. rindā piemērā. Iekšējā MATCH rezultāts ir masīvs, kas katrai datu vērtībai satur vai nu skaitlisku pozīciju (vērtība jau ir izvilkta), vai kļūdu # N / A (vērtība vēl nav izvilkta). Pēc tam mēs izmantojam IF un ISNA, lai filtrētu šos rezultātus, un atgriežam rangu vērtību visām vēl neiekļautajiem parametriem "dati":
IF(ISNA(results),rank))
Šīs operācijas rezultātā tiek iegūts masīvs, kas tiek ievadīts MIN funkcijā, lai iegūtu vēl neizņemto datu vērtību "minimālo vērtību". MIN funkcija atgriež šo vērtību uz ārējo MATCH kā uzmeklēšanas vērtību un nosaukto diapazonu "rank" kā masīvu:
MATCH(min_not_extracted,rank)),rank,0)
Visbeidzot, MATCH atgriež zemākās pakāpes vērtības pozīciju INDEX kā rindas numuru, un INDEX atgriež datu vērtību pašreizējā ekstrakcijas diapazona rindā.
