Mondja el, hogy egyedi elemeket szeretne felsorolni egy listából, de csavarással. És mondd, hogy ezzel a munkalapdal dolgozol:
A D oszlop megszámolja a sorok számát a B oszlop egyes szakaszaiban, a C oszlop pedig az egyedi szakaszok számát az A oszlop első öt karaktere alapján. A B2: B11 cellák ARG-t tartalmaznak, és nyolc egyedi elemet számolhat az A2: A11 első öt karakterében, mert az A7: A9 egyenként 11158-at tartalmaz, így a két ismétlődést nem számoljuk. Hasonlóképpen, a D12-ben szereplő 5 azt mondja, hogy öt sor van a BRD számára, de a 12:16 sorokon belül az első öt karakter három egyedi eleme van, mivel az 11145 ismétlődik, az 11173 pedig megismétlődik.
De hogyan szólítsd meg az Excel-t erre? És milyen képletet használhatna a C2-ben, amelyet át lehet másolni a C12-be és a C17-be?
A D2 egyszerű számlálási képlete =COUNTIF(B:B,B2)megszámolja a B (oszlop) B2 (ARG) előfordulásának számát.
Segédoszlop segítségével izolálja az A oszlop első öt karakterét, ahogy ez az ábra:
Ezután valahogy meg kell jelölnie, hogy az ARG esetében csak az F2: F11 cellák érdeklik, hogy megtalálja az egyedi elemek számát. Általában ezt az értéket az ábrán látható tömbképlet segítségével találja meg:
A C3 cellát ideiglenesen csak a képlet megjelenítésére használja; láthatja, hogy az előző ábrákon nem szerepel a C3-ban. (Rövidesen megtudja, hogyan működik ez a képlet.)
Tehát mi a képlet C2, C12 és C17? A meglepő (és jó) választ ez az ábra mutatja:
Hú! Hogy működik ez?
Vessen egy pillantást a Válasz elemre az ábrán megadott nevekkel:
Ugyanaz a képlet, mint egy korábbi ábrán, de az F2: F11 tartomány használata helyett az Rg nevű tartományt használja. A képlet tömbképlet volt, de a megnevezett képleteket úgy kezeljük, mintha tömbképletek lennének! Vagyis =Answernem a Ctrl + Shift + Enter billentyűkombinációval írja be, hanem egyszerűen a szokásos módon írja be.
Tehát hogyan határozható meg Rg? Ha a C1 cellát választjuk (ami fontos lépés ennek a trükknek a megértéséhez), akkor az ebben az ábrában meghatározott módon van meghatározva:
Ez az =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).
A Loan_Details a lap neve, de ezt a képletet megnézheti a hosszú lap neve nélkül is. Ennek egyszerű módja, ha ideiglenesen elnevezzük a lapot valami egyszerűnek, például x-nek, majd nézzük meg újra a megadott nevet:
Ez a képlet könnyebben olvasható!
Láthatja, hogy ez a képlet megegyezik a B1 dollárral (vegye figyelembe az aktuális sorra vonatkozó relatív hivatkozást) az összes B oszloppal és az 1. kivonással. Kivonja az 1 értéket, mert az OFF1-et használja az F1-ből. Most, hogy tud a C képletről, nézze meg a C2 képletet:
A MATCH($B2,$B:$B,0)képlet része 2, tehát a képlet (a lap nevére való hivatkozás nélkül):
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
vagy:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
vagy:
=OFFSET($F$1,1,0,10,1)
Mivel COUNTIF($B:$B,$B2)10, 10 ARG van. Ez az F2: F11 tartomány. Valójában, ha a C2 cellát választja, és az F5 megnyomásával megy az Rg-hez, akkor ezt látja:
Ha a kezdő cella C12 volt, akkor az F5 megnyomásával az Rg-re léphet:
Tehát most, a Válasz definíciója szerint =SUM(1/COUNTIF(rg,rg)), készen állsz!
Nézzük meg alaposabban, hogyan működik ez a képlet, egy sokkal egyszerűbb példával. Normális esetben, a szintaxis COUNTIF van =COUNTIF(range,criteria), mint például =COUNTIF(C1:C10, "b")ezen az ábrán:
Ez 2-et adna a tartományban lévő b-k számának. De maga a tartomány kritériumként való átengedése a tartomány minden elemét használja kritériumként. Ha kiemeli a képlet ezen részét:
és nyomja meg az F9 billentyűt, és látja:
A tartomány minden egyes elemét kiértékelik, és ez a számsor azt jelenti, hogy van egy a és két b, három c és négy d. Ezeket a számokat 1-re osztjuk, így 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, amint itt láthatja:
Tehát van 2 feled, 3 harmadod, 4 negyeded és 1 egészed, és összeadod őket. Ha egy elemet 7-szer ismételnének, akkor 7 hetedet és így tovább. Elég jó! (Kalap le David Hager előtt, mert felfedezte / feltalálta ezt a képletet.)
De tartson egy percet. Jelenlegi állapotában ezt a képletet csak C2, C12 és C17 mezőbe kell beírnia. Nem lenne jobb, ha beírhatnád C2-be, kitölthetnéd és csak a megfelelő cellákban mutatnád meg? Valójában ezt megteheti. Módosíthatja a C2 képletét, hogy legyen =IF(B1B2,Answer,""), és amikor ezt kitölti, elvégzi a feladatot:
De miért álljon meg itt? Miért ne lehetne a képletet megnevezett képletté tenni az alábbiak szerint:
Ahhoz, hogy ez működjön, a C2 cellának kell aktív cellának lennie (vagy a képletnek másnak kell lennie). Most a C oszlop képleteit lecserélheti =Answer2:
Láthatja, hogy a C3-nak van =Answer2, csakúgy, mint a C oszlop összes cellájának. Miért ne folytatná ezt a D oszlopban? A D2 képlet, miután összehasonlítást alkalmaztunk a B1-re és a B2-re is, itt látható:
Tehát, ha a D2 cellát kiválasztva tartja, és meghatároz egy másik képletet, akkor mondja ki a Answer3 szót:
akkor beírhatja =Answer3a D2 cellába és kitöltheti:
Itt van a munkalap felső része, ahol a képletek láthatók, majd ugyanaz a képernyőkép követi az értékeket:
Amikor mások megpróbálják ezt kitalálni, először elkaphatják a fejüket!
Ez a vendég cikk az Excel MVP Bob Umlas-tól származik. A Több Excel a dobozon kívül című könyvből származik. A könyv többi témájának megtekintéséhez kattintson ide.
