Bendroji formulė
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Santrauka
Norėdami dinamiškai rūšiuoti ir išskirti unikalias reikšmes iš duomenų sąrašo, galite naudoti masyvo formulę, kad nustatytumėte reitingą pagalbiniame stulpelyje, tada naudokite specialiai sukurtą INDEX ir MATCH formulę, kad išskirtumėte unikalias reikšmes. Parodytame pavyzdyje formulė nustatyti rangą C5: C13 yra:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
kur „duomenys“ yra įvardytas diapazonas B5: B13.
Pastaba: tai yra daugelio langelių masyvo formulė, įvesta paspaudus „Control + Shift + Enter“.
Paaiškinimas
Pastaba: pagrindinė šios formulės idėja pritaikyta pagal puikios Mike'o Girvino knygos „Control + Shift + Enter“ pavyzdį.
Pateiktame pavyzdyje naudojamos kelios formulės, kurios aprašytos toliau. Aukštame lygyje funkcija MMULT naudojama skaičiuojant pagalbinį stulpelį (C stulpelis), tada šį laipsnį naudoja G rodyklės INDEX ir MATCH formulės, kad išgautų unikalias reikšmes.
Reitingo duomenų vertės
MMULT funkcija atlieka matricos dauginimą ir naudojama kiekvienai vertei priskirti skaitinį rangą. Pirmasis masyvas sukurtas su tokia išraiška:
--(data>TRANSPOSE(data))
Čia mes naudojame funkciją TRANSPOSE, kad sukurtume horizontalų duomenų masyvą , o visos vertės yra lyginamos viena su kita. Iš esmės kiekviena vertė yra lyginama su visomis kitomis vertėmis, kad būtų atsakyta į klausimą „ar ši vertė yra didesnė už visas kitas“. Dėl to gaunamas dvimatis masyvas, 9 stulpeliai x 9 eilutės, užpildytos TRUE ir FALSE reikšmėmis. Dvigubas neigiamas (-) naudojamas versti TRUE FALSE reikšmes į 1s ir nulius. Gautą masyvą galite vizualizuoti taip:
Iš 1s ir nulio aukščiau pateiktos matricos tampa 1 masyvas viduje MMULT funkcija. Masyvas2 sukurtas naudojant šią išraišką:
ROW(data)^0
Kiekvienas „duomenų“ eilutės skaičius pakeliamas iki nulio galios, kad būtų sukurtas vienmatis masyvas, 1 stulpelis x 9 eilutės, užpildytos skaičiumi 1. Tada MMULT pateikia dviejų masyvų, kurie tampa reitingo stulpelyje matomos vertės.
Mes sugrąžiname visus 9 reitingus vienu metu masyve, todėl turime visus rezultatus iškart įdėti į skirtingas langelius. Priešingu atveju kiekviename langelyje bus rodoma tik pirmoji grąžinto masyvo reikšmė.
Pastaba: tai yra daugelio langelių masyvo formulė, įvesta kartu su „Control“ + „Shift“ + Enter, diapazone C5: C13.
Tuščių langelių tvarkymas
Tušti langeliai tvarkomi pagal šią reitingavimo formulės dalį:
=IF(data="",ROWS(data)
Čia, prieš paleidžiant MMULT, mes patikriname, ar esama langelis „duomenyse“ yra tuščias. Jei taip, priskiriame rango vertę, lygią eilučių skaičiui duomenyse. Tai daroma norint tuščias langelius priversti patekti į sąrašo apačią, kur vėliau juos bus galima lengvai išskirti, kai išgaunamos unikalios vertės (paaiškinta toliau).
Skaičiuojamos unikalios vertės
Norint suskaičiuoti unikalias reikšmes duomenyse, formulė E5 yra:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Kadangi aukščiau esančioje reitingavimo formulėje kiekvienai reikšmei priskiriamas skaitinis reitingas, unikalioms reikšmėms suskaičiuoti galime naudoti funkciją FREQUENCY su SUM. Ši formulė čia išsamiai paaiškinta. Tada iš rezultato atimame 1, jei duomenyse yra tuščių langelių:
-(blank>0)
kur „blank“ yra pavadintas diapazonas E8 ir jame yra ši formulė:
=COUNTBLANK(data)
Iš esmės unikalų skaičių sumažiname vienu, jei duomenyse yra tuščių langelių, nes jų neįtraukiame į rezultatus. Unikalus skaičius langelyje E5 pavadintas „unikalus“ (unikaliam skaičiui) ir naudojamas pagal INDEX ir MATCH formulę tuščioms ląstelėms filtruoti (aprašyta toliau).
Išskleidžiamos unikalios vertės
Norėdami išskirti unikalias vertes, G5 yra tokia žemyn nukopijuota formulė:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Prieš vykdydami INDEX ir MATCH formules, pirmiausia patikriname, ar dabartinis eilučių skaičius ištraukimo srityje yra didesnis nei unikalus skaičius, pavadintas diapazonu „unikalus“ (E5):
=IF(ROWS($G$5:G5)>unique,"",
Jei taip, mes išgauname unikalias reikšmes ir grąžiname tuščią eilutę (""). Jei ne, vykdome ekstrahavimo formulę:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Atkreipkite dėmesį, kad čia yra dvi MATCH funkcijos, viena kita. Vidiniame MATCH masyvui naudojamas besiplečiantis diapazonas, o paieškos vertei nurodytas diapazonas „duomenys“:
MATCH(data,$G$4:G4,0)
Atkreipkite dėmesį, kad besiplečiantis diapazonas prasideda „viršuje“, pavyzdžio 4 eilutėje. Vidinio MATCH rezultatas yra masyvas, kuriame kiekvienoje duomenų reikšmėje yra skaitinė padėtis (vertė jau išgauta) arba klaida # N / A (vertė dar neišgauta). Tada mes naudojame IF ir ISNA filtruoti šiuos rezultatus ir grąžinti visų dar neišgautų „duomenų“ reikšmių reitingo vertę:
IF(ISNA(results),rank))
Atlikus šią operaciją gaunamas masyvas, kuris įvedamas į MIN funkciją, norint gauti dar neišgautų duomenų reikšmių „mažiausią reitingo vertę“. MIN funkcija grąžina šią vertę į išorinę MATCH kaip paieškos vertę ir įvardytą diapazoną „rangas“ kaip masyvą:
MATCH(min_not_extracted,rank)),rank,0)
Galiausiai „MATCH“ grąžina žemiausios reitingo vertės poziciją INDEX kaip eilutės numerį, o „INDEX“ - duomenų vertę dabartinėje ištraukimo diapazono eilutėje.
