Sig, at du vil være i stand til at tælle unikke emner fra en liste, men med et twist. Og sig, at du arbejder med dette regneark:
Kolonne D tæller antallet af rækker i hver af sektionerne fra kolonne B, og kolonne C tæller antallet af unikke sektioner baseret på de første fem tegn i kolonne A for det afsnit. Celler B2: B11 indeholder ARG, og du kan tælle otte unikke elementer i de første fem tegn i A2: A11, fordi A7: A9 hver indeholder 11158, så de to dubletter tælles ikke. På samme måde fortæller 5 i D12 dig, at der er fem rækker til BRD, men inden for rækker 12:16 er der tre unikke emner med de første fem tegn, da 11145 gentages og 11173 gentages.
Men hvordan fortæller du Excel at gøre dette? Og hvilken formel kan du bruge i C2, der kan kopieres til C12 og C17?
Den enkle optællingsformel i D2, =COUNTIF(B:B,B2)tæller antallet af gange B2 (ARG) findes i kolonne B.
Du bruger en hjælpekolonne til at isolere de første fem tegn i kolonne A, som i denne figur:
Dernæst skal du på en eller anden måde angive, at for ARG er du kun interesseret i celler F2: F11 for at finde antallet af unikke genstande. Generelt finder du denne værdi ved hjælp af matrixformlen vist i denne figur:
Du bruger celle C3 midlertidigt bare for at vise formlen; du kan se, at det ikke er til stede i C3 i tidligere tal. (Du lærer hurtigt, hvordan denne formel fungerer.)
Så hvad er formlen i C2, C12 og C17? Det overraskende (og seje) svar vises i denne figur:
Whoa! Hvordan virker det?
Se svaret i de definerede navne i denne figur:
Det er den samme formel fra en tidligere figur, men i stedet for at bruge området F2: F11 bruger det et interval ved navn Rg. Formlen var også en matrixformel, men navngivne formler behandles som om de er matrixformler! Det vil sige, =Answerer ikke indtastet med Ctrl + Shift + Enter, men indtastes simpelthen som normalt.
Så hvordan er Rg defineret? Hvis celle C1 er valgt (hvilket er et vigtigt skridt til at forstå dette trick), er det defineret som i denne figur:
Det er =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 er navnet på arket, men du kan se på denne formel uden navnet på det lange ark. En nem måde at gøre dette på er midlertidigt at navngive arket til noget simpelt som x og derefter se igen på det definerede navn:
Denne formel er lettere at læse!
Du kan se, at denne formel matcher $ B1 (bemærk den relative henvisning til den aktuelle række) mod hele kolonne B og fratrækker 1. Du trækker 1, fordi du bruger OFFSET fra F1. Nu hvor du kender formlen til C, skal du se på den til C2:
Den MATCH($B2,$B:$B,0)del af formlen er 2, så formlen (uden henvisning til arknavnet) er:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
eller:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
eller:
=OFFSET($F$1,1,0,10,1)
Fordi COUNTIF($B:$B,$B2)er 10, er der 10 ARG'er. Dette er område F2: F11. Faktisk, hvis celle C2 er valgt, og du trykker på F5 for at gå til Rg, ser du dette:
Hvis startcellen var C12, frembringes dette ved at trykke på F5 for at gå til Rg:
Så nu, med svar defineret som =SUM(1/COUNTIF(rg,rg)), er du færdig!
Lad os se nærmere på, hvordan denne formel fungerer ved hjælp af et meget enklere eksempel. Normalt er syntaksen for COUNTIF =COUNTIF(range,criteria)som =COUNTIF(C1:C10, "b")i denne figur:
Dette ville give 2 som antallet af b'er i området. Men at overføre selve området som kriterier bruger hvert element i området som kriterier. Hvis du fremhæver denne del af formlen:
og tryk på F9, du ser:
Hvert element i området evalueres, og denne række af tal betyder, at der er en a, og der er to b'er, tre c'er og fire d'er. Disse tal er opdelt i 1, hvilket giver 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, som du kan se her:
Så du har 2 halvdele, 3 tredjedele, 4 fjerdedele og 1 helhed, og sammenlægning giver udbytter 4. Hvis et emne blev gentaget 7 gange, ville du have 7 syvendedele osv. Temmelig sejt! (Hat af David Hager for at have opdaget / opfundet denne formel.)
Men vent et øjeblik. Som det står, skal du kun indtaste denne formel i C2, C12 og C17. Ville det ikke være bedre, hvis du kunne indtaste det i C2 og udfylde og kun vise det i de rigtige celler? Faktisk kan du gøre dette. Du kan ændre formlen i C2 til at være =IF(B1B2,Answer,""), og når du udfylder den, gør den jobbet:
Men hvorfor stoppe her? Hvorfor ikke gøre formlen til en navngiven formel, som vist her:
For at dette skal fungere, skal celle C2 være den aktive celle (eller formlen skal være anderledes). Nu kan du erstatte kolonne Cs formler med =Answer2:
Du kan se, at C3 har =Answer2, ligesom alle cellerne i kolonne C. Hvorfor ikke fortsætte dette i kolonne D? Formlen i D2, efter anvendelse af sammenligningen til B1 og B2, vises her:
Så hvis du holder celle D2 valgt og definerer en anden formel, skal du sige Svar3:
så kan du indtaste =Answer3i celle D2 og udfylde:
Her er den øverste del af regnearket med formler, efterfulgt af det samme skærmbillede med værdier, der viser:
Når andre mennesker prøver at finde ud af dette, kan de måske først ridse på hovedet!
Denne gæsteartikel er fra Excel MVP Bob Umlas. Det er fra bogen, Mere Excel uden for boksen. Klik her for at se de andre emner i bogen.
