Spuneți că doriți să puteți număra elemente unice dintr-o listă, dar cu o răsucire. Și spuneți că lucrați cu această foaie de lucru:
Coloana D contorizează numărul de rânduri din fiecare secțiune din coloana B, iar coloana C contează numărul de secțiuni unice pe baza primelor cinci caractere ale coloanei A pentru acea secțiune. Celulele B2: B11 conțin ARG și puteți număra opt elemente unice în primele cinci caractere din A2: A11 deoarece A7: A9 conțin fiecare 11158, deci cele două duplicate nu sunt numărate. În mod similar, 5 din D12 vă spune că există cinci rânduri pentru BRD, dar în rândurile 12:16, există trei elemente unice din primele cinci caractere, deoarece 11145 se repetă și 11173 se repetă.
Dar cum îi spui Excel să facă acest lucru? Și ce formulă ai putea folosi în C2 care ar putea fi copiată în C12 și C17?
Formula simplă de numărare din D2 ,, =COUNTIF(B:B,B2)contează de câte ori există B2 (ARG) în coloana B.
Folosiți o coloană de ajutor pentru a izola primele cinci caractere ale coloanei A, ca în această figură:
Apoi, trebuie să indicați cumva că pentru ARG, sunteți interesat doar de celulele F2: F11 pentru a găsi numărul de articole unice. În general, veți găsi această valoare utilizând formula matricei prezentată în această figură:
Folosiți temporar celula C3 doar pentru a afișa formula; puteți vedea că nu este prezent în C3 în figurile anterioare. (Veți afla în scurt timp cum funcționează această formulă.)
Deci, care este formula din C2, C12 și C17? Răspunsul surprinzător (și cool) este prezentat în această figură:
Vai! Cum funcționează asta?
Aruncați o privire la Răspuns în numele definite în această figură:
Este aceeași formulă dintr-o figură anterioară, dar în loc să folosească intervalul F2: F11, folosește un interval numit Rg. De asemenea, formula a fost o formulă matrice, dar formulele denumite sunt tratate ca și cum ar fi formule matrice! Adică, =Answernu este introdus cu Ctrl + Shift + Enter, ci este pur și simplu introdus ca de obicei.
Deci, cum este definit Rg? Dacă este selectată celula C1 (care este un pas important pentru înțelegerea acestui truc), atunci este definită ca în această figură:
Asta e =OFFSET(Loan_Details!$F$1,MATCH(Loan_Details!$B1,Loan_Details!$B:$B,0)-1,0,COUNTIF(Loan_Details!$B:$B,Loan_Details!$B1),1).
Loan_Details este numele foii, dar puteți analiza această formulă fără numele lung al foii. O modalitate ușoară de a face acest lucru este să denumiți temporar foaia ceva simplu, cum ar fi x, și apoi să vă uitați din nou la numele definit:
Această formulă este mai ușor de citit!
Puteți vedea că această formulă se potrivește cu $ B1 (rețineți referința relativă la rândul curent) cu toate coloanele B și scade 1. Scadeți 1 pentru că utilizați OFFSET din F1. Acum, că știți despre formula pentru C, aruncați o privire la cea pentru C2:
Partea MATCH($B2,$B:$B,0)formulei este 2, deci formula (fără referința la numele foii) este:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
sau:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
sau:
=OFFSET($F$1,1,0,10,1)
Deoarece COUNTIF($B:$B,$B2)este 10, există 10 ARG-uri. Aceasta este gama F2: F11. De fapt, dacă este selectată celula C2 și apăsați F5 pentru a merge la Rg, vedeți acest lucru:
Dacă celula de pornire a fost C12, apăsarea F5 pentru a merge la Rg produce acest lucru:
Deci, acum, cu Răspunsul definit ca =SUM(1/COUNTIF(rg,rg)), ați terminat!
Să ne uităm mai atent la modul în care funcționează această formulă, folosind un exemplu mult mai simplu. În mod normal, sintaxa pentru COUNTIF este =COUNTIF(range,criteria), cum ar fi =COUNTIF(C1:C10, "b")în această figură:
Acest lucru ar da 2 ca număr de b din interval. Dar trecerea intervalului în sine ca criteriu utilizează fiecare element din interval ca criteriu. Dacă evidențiați această porțiune a formulei:
și apăsați F9, vedeți:
Fiecare articol din interval este evaluat, iar această serie de numere înseamnă că există unul a și există două b, trei c și patru d. Aceste numere sunt împărțite în 1, dând 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, după cum puteți vedea aici:
Așadar, aveți 2 jumătăți, 3 treimi, 4 sferturi și 1 întreg și adăugarea lor produce 4. Dacă un articol ar fi repetat de 7 ori, atunci ați avea 7 șeptimi și așa mai departe. Destul de la moda! (Îi plăcem lui David Hager pentru că a descoperit / inventat această formulă.)
Dar stai un minut. În starea actuală, trebuie să introduceți această formulă numai în C2, C12 și C17. Nu ar fi mai bine dacă ai putea să-l introduci în C2 și să-l completezi și să-l arăți numai în celulele corecte? De fapt, puteți face acest lucru. Puteți modifica formula din C2 pentru a fi =IF(B1B2,Answer,"")și, atunci când o completați, face treaba:
Dar de ce să ne oprim aici? De ce să nu transformați formula într-o formulă numită, așa cum se arată aici:
Pentru ca acest lucru să funcționeze, celula C2 trebuie să fie celula activă (sau formula ar trebui să fie diferită). Acum puteți înlocui formulele coloanei C cu =Answer2:
Puteți vedea că C3 are =Answer2, la fel ca toate celulele din coloana C. De ce să nu continuați acest lucru în coloana D? Formula din D2, după aplicarea, de asemenea, a comparației cu B1 și B2, este prezentată aici:
Deci, dacă păstrați celula D2 selectată și definiți o altă formulă, spuneți Răspuns 3:
apoi puteți introduce =Answer3în celula D2 și completați:
Iată partea de sus a foii de lucru, cu formule afișate, urmată de aceeași captură de ecran cu valori afișate:
Când alte persoane încearcă să descopere acest lucru, s-ar putea să se zgârie la cap la început!
Acest articol invitat este de la Excel MVP Bob Umlas. Este din cartea, Mai mult Excel în afara cutiei. Pentru a vedea celelalte subiecte din carte, faceți clic aici.
