Tarkime, kad norite suskaičiuoti unikalius elementus iš sąrašo, bet sukdami. Ir pasakykite, kad dirbate su šiuo darbalapiu:
D stulpelyje skaičiuojamas eilučių skaičius kiekvienoje skiltyje iš B stulpelio, o C stulpelyje - unikalių sekcijų skaičius, pagrįstas pirmaisiais penkiais tos skilties A stulpelio simboliais. Langeliuose B2: B11 yra ARG, o pirmuose penkiuose A2: A11 simboliuose galite suskaičiuoti aštuonis unikalius elementus, nes A7: A9 kiekviename yra 11158, todėl du dublikatai neskaičiuojami. Panašiai 5 iš 12 rodo, kad yra penkios BRD eilutės, tačiau 12:16 eilutėse yra trys unikalūs pirmųjų penkių simbolių elementai, nes kartojamas 11145 ir 11173.
Bet kaip liepti „Excel“ tai padaryti? Kokią formulę galėtumėte naudoti C2, kurią būtų galima nukopijuoti į C12 ir C17?
Paprasta skaičiavimo formulė D2, =COUNTIF(B:B,B2)skaičiuoja B2 (ARG) B stulpelyje kartų skaičių.
Naudodami pagalbinį stulpelį išskirkite pirmuosius penkis A stulpelio simbolius, kaip šiame paveikslėlyje:
Tada turite kažkaip nurodyti, kad ARG jus domina tik langeliai F2: F11, kad rastumėte unikalių elementų skaičių. Apskritai šią vertę rasite naudodami masyvo formulę, parodytą šiame paveikslėlyje:
C3 langelį laikinai naudojate tik tam, kad parodytumėte formulę; matote, kad ankstesniuose paveiksluose jo nėra C3. (Netrukus sužinosite, kaip veikia ši formulė.)
Taigi kokia formulė yra C2, C12 ir C17? Stebinantis (ir šaunus) atsakymas parodytas šiame paveiksle:
Oi! Kaip tai veikia?
Pažvelkite į atsakymą šiame paveiksle apibrėžtais pavadinimais:
Tai ta pati formulė iš ankstesnio paveikslo, tačiau vietoj to, kad naudotų diapazoną F2: F11, jis naudoja diapazoną pavadinimu Rg. Be to, formulė buvo masyvo formulė, tačiau pavadintos formulės traktuojamos taip, tarsi jos būtų masyvo formulės! Tai yra, =Answerjis neįvedamas paspaudus „Ctrl“ + „Shift“ + „Enter“, o tiesiog įvedamas kaip įprasta.
Taigi kaip apibrėžiamas Rg? Jei pasirinkta ląstelė C1 (tai yra svarbus žingsnis norint suprasti šį triuką), tada ji apibrėžiama taip, kaip šiame paveiksle:
Tai =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).
Paskolos_Duomenys yra lapo pavadinimas, tačiau į šią formulę galite žiūrėti be ilgojo lapo pavadinimo. Paprastas būdas tai padaryti yra laikinai pavadinti lapą kažkuo paprastu, pvz., X, ir tada dar kartą pažvelgti į apibrėžtą pavadinimą:
Šią formulę lengviau perskaityti!
Galite pamatyti, kad ši formulė atitinka $ B1 (atkreipkite dėmesį į santykinę nuorodą į dabartinę eilutę) su visu B stulpeliu ir atimkite 1. Atimkite 1, nes naudojate OFFSET iš F1. Dabar, kai žinote apie C formulę, pažvelkite į C2 formulę:
MATCH($B2,$B:$B,0), Kurio formulė dalis yra 2, todėl formulė (be nuoroda į lapo pavadinimas) yra:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
arba:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
arba:
=OFFSET($F$1,1,0,10,1)
Kadangi COUNTIF($B:$B,$B2)yra 10, yra 10 ARG. Tai diapazonas F2: F11. Tiesą sakant, jei pasirinkta ląstelė C2 ir paspaudę F5 eisite į Rg, pamatysite tai:
Jei pradinė ląstelė buvo C12, paspaudus F5 pereiti prie Rg gaunama:
Taigi dabar, kai atsakymas apibrėžtas taip =SUM(1/COUNTIF(rg,rg)), viskas baigta!
Panagrinėkime atidžiau, kaip veikia ši formulė, naudodami daug paprastesnį pavyzdį. Paprastai COUNTIF sintaksė yra =COUNTIF(range,criteria)tokia, kaip =COUNTIF(C1:C10, "b")šiame paveiksle:
Tai duotų 2 kaip b skaičių diapazone. Tačiau perėjus patį diapazoną kaip kriterijų, kiekvienas diapazono elementas naudojamas kaip kriterijus. Jei pažymėsite šią formulės dalį:
ir paspauskite F9, pamatysite:
Kiekvienas diapazono elementas yra įvertinamas, o ši skaičių serija reiškia, kad yra vienas a ir yra du b, trys c ir keturi d. Šie skaičiai yra padalyti į 1, suteikiant 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, kaip matote čia:
Taigi jūs turite 2 kėlinius, 3 trečdalius, 4 ketvirčius ir 1 sveiką daiktą, o juos susumavus gaunama 4. Jei elementas būtų kartojamas 7 kartus, turėtumėte 7 septintąsias dalis ir pan. Gana kietas! (Kepurė prieš Davidą Hagerį, kuris atrado / išrado šią formulę.)
Bet laikykis minutėlę. Dabartinėje formulėje šią formulę turite įvesti tik C2, C12 ir C17. Ar ne geriau, jei galėtumėte jį įvesti į C2, užpildyti ir parodyti tik tinkamose ląstelėse? Tiesą sakant, jūs galite tai padaryti. Galite pakeisti formulę C2, kad ji būtų =IF(B1B2,Answer,""), o kai ją užpildysite, ji atliks šį darbą:
Bet kam čia sustoti? Kodėl gi nepadarius formulės pavadinta formule, kaip parodyta čia:
Kad tai veiktų, ląstelė C2 turi būti aktyvi ląstelė (arba formulė turėtų skirtis). Dabar galite pakeisti C stulpelio formules =Answer2:
Galite pamatyti, kad C3 =Answer2, kaip ir visos C stulpelio ląstelės, turi tą patį. Kodėl gi ne tęsti tai D stulpelyje? D2 formulė, taip pat pritaikius palyginimą su B1 ir B2, rodoma čia:
Taigi, jei paliksite langelį D2 pažymėtą ir apibrėžsite kitą formulę, pasakykite Atsakymas3:
tada galite įvesti =Answer3langelį D2 ir užpildyti:
Čia yra viršutinė darbalapio dalis su rodomomis formulėmis, po to ta pati ekrano kopija su rodomomis reikšmėmis:
Kai kiti žmonės bandys tai išsiaiškinti, jie iš pradžių gali pakrapštyti galvą!
Šis svečio straipsnis yra iš „Excel MVP Bob Umlas“. Tai iš knygos „Daugiau„ Excel “už dėžutės“. Norėdami pamatyti kitas knygos temas, spustelėkite čia.
