Si at du vil være i stand til å telle unike elementer fra en liste, men med en vri. Og si at du jobber med dette regnearket:
Kolonne D teller antall rader i hver av seksjonene fra kolonne B, og kolonne C teller antall unike seksjoner basert på de fem første tegnene i kolonne A for den delen. Cellene B2: B11 inneholder ARG, og du kan telle åtte unike elementer i de fem første tegnene i A2: A11 fordi A7: A9 hver inneholder 11158, slik at de to duplikatene ikke telles. På samme måte forteller 5 i D12 deg at det er fem rader for BRD, men innenfor rader 12:16 er det tre unike elementer av de fem første tegnene, siden 11145 gjentas og 11173 gjentas.
Men hvordan ber du Excel gjøre dette? Og hvilken formel kan du bruke i C2 som kan kopieres til C12 og C17?
Den enkle tellingsformelen i D2, =COUNTIF(B:B,B2)teller antall ganger B2 (ARG) eksisterer i kolonne B.
Du bruker en hjelpekolonne for å isolere de fem første tegnene i kolonne A, som i denne figuren:
Deretter må du på en eller annen måte indikere at for ARG er du bare interessert i celler F2: F11 for å finne antall unike gjenstander. Generelt vil du finne denne verdien ved å bruke matriseformelen vist i denne figuren:
Du bruker celle C3 midlertidig bare for å vise formelen; du kan se at den ikke er tilstede i C3 i tidligere figurer. (Du får snart vite hvordan denne formelen fungerer.)
Så hva er formelen i C2, C12 og C17? Det overraskende (og kule) svaret er vist i denne figuren:
Whoa! Hvordan virker dette?
Ta en titt på Svar i de definerte navnene i denne figuren:
Det er den samme formelen fra en tidligere figur, men i stedet for å bruke området F2: F11, bruker det et område som heter Rg. Formelen var også en matriseformel, men navngitte formler behandles som om de er matriseformler! Det vil si at =Answerdet ikke er skrevet inn med Ctrl + Shift + Enter, men bare blir lagt inn som vanlig.
Så hvordan er Rg definert? Hvis celle C1 er valgt (som er et viktig trinn for å forstå dette trikset), er det definert som i denne figuren:
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 formelen uten navnet på det lange arket. En enkel måte å gjøre dette på er å navngi arket midlertidig til noe enkelt, som x, og deretter se igjen på det definerte navnet:
Denne formelen er lettere å lese!
Du kan se at denne formelen samsvarer med $ B1 (merk den relative referansen til den gjeldende raden) mot hele kolonne B og subtraherer 1. Du trekker fra 1 fordi du bruker OFFSET fra F1. Nå som du vet om formelen for C, ta en titt på den for C2:
Den MATCH($B2,$B:$B,0)delen av formelen er 2, slik at formelen (uten 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 det COUNTIF($B:$B,$B2)er 10, er det 10 ARGer. Dette er område F2: F11. Faktisk, hvis celle C2 er valgt og du trykker F5 for å gå til Rg, ser du dette:
Hvis startcellen var C12, trykker du på F5 for å gå til Rg produserer dette:
Så nå, med svar definert som =SUM(1/COUNTIF(rg,rg)), er du ferdig!
La oss se nærmere på hvordan denne formelen fungerer, ved å bruke et mye enklere eksempel. Normalt er syntaksen for COUNTIF =COUNTIF(range,criteria), slik som =COUNTIF(C1:C10, "b")i denne figuren:
Dette vil gi 2 som antall b-er i området. Men å passere selve området som kriteriene bruker hvert element i området som kriteriene. Hvis du markerer denne delen av formelen:
og trykk på F9, du ser:
Hvert element i området blir evaluert, og denne serien med tall betyr at det er ett a og det er to b, tre c og fire d. Disse tallene er delt inn i 1, og gir 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, som du kan se her:
Så du har to halvdeler, 3 tredjedeler, 4 fjerdedeler og 1 helhet, og når du legger dem sammen gir du avkastning 4. Hvis en vare ble gjentatt 7 ganger, ville du ha 7 syvendedeler og så videre. Ganske kult! (Hatter av David Hager for å oppdage / oppfinne denne formelen.)
Men vent litt. Slik det står, må du bare skrive inn denne formelen i C2, C12 og C17. Ville det ikke vært bedre om du kunne legge det inn i C2 og fylle ut og bare vise det i de riktige cellene? Faktisk kan du gjøre dette. Du kan endre formelen i C2 for å være =IF(B1B2,Answer,""), og når du fyller den ut, gjør den jobben:
Men hvorfor stoppe her? Hvorfor ikke lage formelen til en navngitt formel, som vist her:
For at dette skal fungere, må celle C2 være den aktive cellen (ellers må formelen være annerledes). Nå kan du erstatte kolonne Cs formler med =Answer2:
Du kan se at C3 har =Answer2, som alle cellene i kolonne C. Hvorfor ikke fortsette dette i kolonne D? Formelen i D2, etter å også ha brukt sammenligningen til B1 og B2, vises her:
Så hvis du holder celle D2 valgt og definerer en annen formel, si svar 3:
så kan du gå inn =Answer3i celle D2 og fylle ned:
Her er den øverste delen av regnearket, med formler som vises, etterfulgt av samme skjermbilde med verdier som viser:
Når andre prøver å finne ut av dette, kan de klø på hodet i begynnelsen!
Denne gjesteartikkelen er fra Excel MVP Bob Umlas. Det er fra boken, Mer Excel utenfor boksen. For å se de andre emnene i boka, klikk her.
