Formula generică
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
rezumat
Pentru a sorta și extrage dinamic valori unice dintr-o listă de date, puteți utiliza o formulă matrice pentru a stabili un rang într-o coloană de ajutor, apoi puteți utiliza o formulă specială INDEX și MATCH pentru a extrage valori unice. În exemplul prezentat, formula pentru stabilirea rangului în C5: C13 este:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
unde „date” este intervalul numit B5: B13.
Notă: aceasta este o formulă de matrice cu mai multe celule, introdusă cu control + shift + enter.
Explicaţie
Notă: ideea de bază a acestei formule este adaptată dintr-un exemplu din cartea excelentă a lui Mike Girvin Control + Shift + Enter.
Exemplul prezentat folosește mai multe formule, care sunt descrise mai jos. La un nivel înalt, funcția MMULT este utilizată pentru a calcula un rang numeric într-o coloană helper (coloana C), iar acest rang este apoi utilizat de o formulă INDEX și MATCH din coloana G pentru a extrage valori unice.
Valorile datelor de clasificare
Funcția MMULT efectuează multiplicarea matricei și este utilizată pentru a atribui un rang numeric fiecărei valori. Prima matrice este creată cu următoarea expresie:
--(data>TRANSPOSE(data))
Aici, folosim funcția TRANSPOSE pentru a crea o matrice orizontală de date , iar toate valorile sunt comparate între ele. În esență, fiecare valoare este comparată cu orice altă valoare pentru a răspunde la întrebarea „este această valoare mai mare decât orice altă valoare”. Acest lucru are ca rezultat un tablou bidimensional, 9 coloane x 9 rânduri, umplut cu valori ADEVĂRATE și FALSE. Dublul negativ (-) este folosit pentru a constrânge valorile TRUE FALSE la 1s și zerouri. Puteți vizualiza matricea rezultată astfel:
Matricea de 1s și zerouri de mai sus devine array1 în interiorul funcției MMULT. Array2 este creat cu această expresie:
ROW(data)^0
Aici, fiecare număr de rând din „date” este ridicat la puterea zero pentru a crea o matrice unidimensională, 1 coloană x 9 rânduri, completată cu numărul 1. MMULT returnează apoi produsul matricial al celor două matrice, care devin valorile văzute în coloana de rang.
Recuperăm toate cele 9 clasamente în același timp într-o matrice, deci trebuie să punem rezultatele în celule diferite simultan. În caz contrar, fiecare celulă va afișa numai prima valoare de clasare în matricea returnată.
Notă: aceasta este o formulă cu mai multe celule, introdusă cu control + shift + enter, în intervalul C5: C13.
Manipularea celulelor goale
Celulele goale sunt tratate cu această parte a formulei de clasare:
=IF(data="",ROWS(data)
Aici, înainte de a rula MMULT, verificăm dacă celula curentă din „date” este goală. Dacă da, atribuim o valoare de rang care este egală cu numărul de rânduri din date. Acest lucru se face pentru a forța celulele goale în partea de jos a listei, unde pot fi ușor excluse ulterior pe măsură ce valorile unice sunt extrase (explicate mai jos).
Numărarea valorilor unice
Pentru a număra valori unice în date, formula din E5 este:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Deoarece formula de clasare de mai sus atribuie un rang numeric fiecărei valori, putem folosi funcția FREQUENCY cu SUM pentru a număra valori unice. Această formulă este explicată în detaliu aici. Apoi scădem 1 din rezultat dacă există celule goale în date:
-(blank>0)
unde "gol" este intervalul numit E8 și conține această formulă:
=COUNTBLANK(data)
În esență, reducem numărul unic cu unul dacă există celule goale în date, deoarece nu le includem în rezultate. Numărul unic în celula E5 este denumit „unic” (pentru număr unic) și este utilizat de formula INDEX și MATCH pentru a filtra celulele goale (descrise mai jos).
Extragerea valorilor unice
Pentru a extrage valori unice, G5 conține următoarea formulă, copiată:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Înainte de a rula formula INDEX și MATCH, verificăm mai întâi dacă numărul curent de rânduri din zona de extracție este mai mare decât numărul unic al intervalului numit „unic” (E5):
=IF(ROWS($G$5:G5)>unique,"",
Dacă, așa, am terminat de extras valori unice și returnăm un șir gol (""). Dacă nu, rulăm formula de extracție:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Rețineți că aici sunt două funcții MATCH, una în cealaltă. MATCH-ul interior folosește un interval extins pentru o matrice și intervalul numit „date” pentru valoarea de căutare:
MATCH(data,$G$4:G4,0)
Observați că intervalul de extindere începe pe „rândul de deasupra”, rândul 4 din exemplu. Rezultatul din MATCH-ul interior este un tablou care, pentru fiecare valoare din date, conține fie o poziție numerică (valoarea a fost deja extrasă), fie eroarea # N / A (valoarea nu a fost încă extrasă). Apoi folosim IF și ISNA pentru a filtra aceste rezultate și returnăm valoarea rangului pentru toate valorile din „date” care nu au fost încă extrase:
IF(ISNA(results),rank))
Această operație are ca rezultat un tablou, care este introdus în funcția MIN pentru a obține „valoarea minimă de rang” pentru valorile de date care nu au fost încă extrase. Funcția MIN returnează această valoare la MATCH-ul exterior ca valoare de căutare și intervalul numit „rank” ca matrice:
MATCH(min_not_extracted,rank)),rank,0)
În cele din urmă, MATCH returnează poziția celei mai mici valori de rang la INDEX ca număr de rând, iar INDEX returnează valoarea datelor din rândul curent al intervalului de extracție.
