Säg att du vill kunna räkna unika objekt från en lista, men med en twist. Och säg att du arbetar med det här kalkylbladet:
Kolumn D räknar antalet rader i var och en av sektionerna från kolumn B och kolumn C räknar antalet unika sektioner baserat på de fem första tecknen i kolumn A för det avsnittet. Celler B2: B11 innehåller ARG och du kan räkna åtta unika objekt i de första fem tecknen i A2: A11 eftersom A7: A9 vardera innehåller 11158, så de två dubbletterna räknas inte. På samma sätt säger 5 i D12 att det finns fem rader för BRD, men inom raderna 12:16 finns det tre unika objekt med de fem första tecknen, eftersom 11145 upprepas och 11173 upprepas.
Men hur säger du till Excel att göra detta? Och vilken formel kan du använda i C2 som kan kopieras till C12 och C17?
Den enkla räkningsformeln i D2, =COUNTIF(B:B,B2)räknar antalet gånger B2 (ARG) finns i kolumn B.
Du använder en hjälpkolumn för att isolera de fem första tecknen i kolumn A, som i den här figuren:
Därefter måste du på något sätt ange att för ARG är du bara intresserad av celler F2: F11 för att hitta antalet unika objekt. I allmänhet skulle du hitta detta värde med hjälp av matrisformeln som visas i den här figuren:
Du använder cell C3 tillfälligt bara för att visa formeln; Du kan se att det inte finns i C3 i tidigare siffror. (Du lär dig snart hur denna formel fungerar.)
Så vad är formeln i C2, C12 och C17? Det överraskande (och coola) svaret visas i den här figuren:
Oj! Hur fungerar detta?
Ta en titt på Svar i de definierade namnen i den här figuren:
Det är samma formel från en tidigare figur, men istället för att använda intervallet F2: F11 använder det ett intervall som heter Rg. Formeln var också en matrisformel, men namngivna formler behandlas som om de är matrisformler! Det vill säga, =Answerskrivs inte in med Ctrl + Skift + Enter utan anges helt enkelt som vanligt.
Så hur definieras Rg? Om cell C1 väljs (vilket är ett viktigt steg för att förstå detta trick), definieras det som i den här figuren:
Det är =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 är namnet på arket, men du kan titta på den här formeln utan namnet på det långa arket. Ett enkelt sätt att göra detta är att tillfälligt namnge arket något enkelt, som x, och sedan titta igen på det definierade namnet:
Denna formel är lättare att läsa!
Du kan se att denna formel matchar $ B1 (notera den relativa referensen till den aktuella raden) mot kolumn B och subtraherar 1. Du subtraherar 1 eftersom du använder OFFSET från F1. Nu när du vet om formeln för C, ta en titt på den för C2:
Delen MATCH($B2,$B:$B,0)av formeln är 2, så formeln (utan hänvisning till arknamnet) är:
=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)
Eftersom det COUNTIF($B:$B,$B2)är 10 finns det 10 ARG. Detta är intervall F2: F11. Faktum är att om cell C2 väljs och du trycker på F5 för att gå till Rg, ser du detta:
Om startcellen var C12, trycker du på F5 för att gå till Rg producerar detta:
Så nu, med svaret definierat som =SUM(1/COUNTIF(rg,rg)), är du klar!
Låt oss titta närmare på hur denna formel fungerar, med ett mycket enklare exempel. Normalt är syntaxen för COUNTIF =COUNTIF(range,criteria), som =COUNTIF(C1:C10, "b")i den här figuren:
Detta skulle ge 2 som antalet b i intervallet. Men att skicka själva intervallet som kriterier använder varje objekt i intervallet som kriterier. Om du markerar den här delen av formeln:
och tryck på F9, du ser:
Varje artikel i intervallet utvärderas och den här serien av siffror betyder att det finns en a och det finns två b, tre c och fyra d. Dessa siffror är uppdelade i 1, vilket ger 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, som du kan se här:
Så du har 2 halvor, 3 tredjedelar, 4 fjärdedelar och 1 helhet, och att lägga till dem ger avkastning 4. Om ett objekt upprepades 7 gånger, skulle du ha 7 sjunde och så vidare. Ganska cool! (Hattar av David Hager för att ha upptäckt / uppfunnit denna formel.)
Men vänta en stund. Som det står måste du bara ange denna formel i C2, C12 och C17. Skulle det inte vara bättre om du kunde skriva in det i C2 och fylla i det och bara visa det i rätt celler? I själva verket kan du göra detta. Du kan ändra formeln i C2 så att den blir =IF(B1B2,Answer,""), och när du fyller i den gör den jobbet:
Men varför stanna här? Varför inte göra formeln till en namngiven formel, som visas här:
För att detta ska fungera måste cell C2 vara den aktiva cellen (annars måste formeln vara annorlunda). Nu kan du ersätta kolumn C: s formler med =Answer2:
Du kan se att C3 har =Answer2, liksom alla celler i kolumn C. Varför inte fortsätta detta i kolumn D? Formeln i D2, efter att ha tillämpat jämförelsen på B1 och B2, visas här:
Så om du håller cellen D2 markerad och definierar en annan formel, säg Answer3:
då kan du gå in =Answer3i cell D2 och fylla i:
Här är den översta delen av kalkylbladet, med formler som visas, följt av samma skärmdump med värden som visar:
När andra försöker lista ut detta kan de först klia på huvudet!
Den här gästartikeln kommer från Excel MVP Bob Umlas. Det är från boken, Mer Excel utanför lådan. Klicka här för att se de andra ämnena i boken.
