Sano, että haluat pystyä laskemaan ainutlaatuisia kohteita luettelosta, mutta vääntämällä. Ja sano, että työskentelet tämän laskentataulukon kanssa:
Sarake D laskee rivien lukumäärän sarakkeen B jokaisessa osassa, ja sarake C laskee yksilöllisten osioiden lukumäärän kyseisen osan sarakkeen A viiden ensimmäisen merkin perusteella. Solut B2: B11 sisältävät ARG: n, ja voit laskea kahdeksan ainutlaatuista kohdetta A2: A11: n viiteen ensimmäiseen merkkiin, koska A7: A9 sisältää kukin 11158, joten kahta kaksoiskappaletta ei lasketa. Samoin D12: n 5 kertoo sinulle, että BRD: llä on viisi riviä, mutta rivillä 12:16 on kolme ainutlaatuista kohdetta viidestä ensimmäisestä merkistä, koska 11145 toistetaan ja 11173 toistetaan.
Mutta kuinka käsket Excelin tekemään tämän? Ja mitä kaavaa voit käyttää C2: ssä, joka voidaan kopioida C12: een ja C17: een?
D2: n yksinkertainen laskukaava =COUNTIF(B:B,B2)laskee sarakkeessa B olevien B2 (ARG) kertojen määrän.
Voit eristää sarakkeen A viisi ensimmäistä merkkiä apusarakkeen avulla, kuten tässä kuvassa:
Seuraavaksi sinun on jotenkin ilmoitettava, että ARG: lle olet kiinnostunut vain soluista F2: F11 löytääksesi yksilöllisten kohteiden määrän. Yleensä löydät tämän arvon käyttämällä tässä kuvassa esitettyä taulukon kaavaa:
Käytät solua C3 väliaikaisesti vain kaavan näyttämiseen; voit nähdä, että sitä ei ole edellisissä kuvioissa C3: ssa. (Opit pian, kuinka tämä kaava toimii.)
Joten mikä on kaava C2: ssä, C12: ssa ja C17: ssä? Yllättävä (ja siisti) vastaus on esitetty tässä kuvassa:
Vau! Miten tämä toimii?
Katso Vastaa tämän kuvan määritetyissä nimissä:
Se on sama kaava kuin aikaisemmassa kuvassa, mutta sen sijaan, että käyttäisi aluetta F2: F11, se käyttää aluetta nimeltä Rg. Kaava oli myös matriisikaava, mutta nimettyjä kaavoja käsitellään ikään kuin ne olisivat matriisikaavoja! Eli sitä =Answerei syötetä Ctrl + Vaihto + Enter, vaan syötetään yksinkertaisesti tavalliseen tapaan.
Joten miten Rg määritellään? Jos solu C1 on valittu (mikä on tärkeä askel tämän temppun ymmärtämiseksi), se määritetään kuten tässä kuvassa:
Se on =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).
Lainan_tiedot on taulukon nimi, mutta voit tarkastella tätä kaavaa ilman pitkää arkin nimeä. Helppo tapa tehdä tämä on nimetä taulukko väliaikaisesti jollekin yksinkertaiselle, kuten x, ja katsoa sitten määritettyä nimeä uudelleen:
Tätä kaavaa on helpompi lukea!
Voit nähdä, että tämä kaava vastaa $ B1: tä (huomaa suhteellinen viittaus nykyiseen riviin) kaikkiin sarakkeisiin B ja vähentää 1. Vähennät 1, koska käytät OFFSETia F1: stä. Nyt kun tiedät C-kaavasta, katsokaa C2-kaavaa:
MATCH($B2,$B:$B,0)Osa, jolla on kaava on 2, niin kaavan (ilman viittausta levyn nimi) on:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
tai:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
tai:
=OFFSET($F$1,1,0,10,1)
Koska COUNTIF($B:$B,$B2)on 10, ARG-arvoja on 10. Tämä on alue F2: F11. Itse asiassa, jos solu C2 on valittu ja painat F5 siirtyäksesi kohtaan Rg, näet tämän:
Jos aloitussolu oli C12, siirtyminen Rg-painikkeeseen F5 tuottaa tämän:
Joten nyt, kun vastaus on määritelty =SUM(1/COUNTIF(rg,rg)), olet kaikki valmis!
Tarkastellaan tarkemmin tämän kaavan toimintaa käyttämällä paljon yksinkertaisempaa esimerkkiä. Normaalisti COUNTIF-syntaksin muoto on =COUNTIF(range,criteria), kuten =COUNTIF(C1:C10, "b")tässä kuvassa:
Tämä antaisi 2 b: n lukumääräksi alueella. Alueen itse ylittäminen kriteereinä käyttää kuitenkin kaikkia alueen kohteita kriteereinä. Jos korostat tämän kaavan osan:
ja paina F9, näet:
Jokainen alueen arvo arvioidaan, ja tämä numerosarja tarkoittaa, että siellä on yksi a ja kaksi b: tä, kolme c: tä ja neljä d: tä. Nämä luvut on jaettu yhteen, jolloin saadaan 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, kuten näet täällä:
Joten sinulla on 2 puoliskoa, 3 kolmasosaa, 4 neljäsosaa ja 1 kokonaisuus, ja niiden yhteenlaskeminen tuottaa 4. Jos joku kohta toistettaisiin 7 kertaa, sinulla olisi 7 seitsemäsosaa ja niin edelleen. Aika siistiä! (Hattu pois David Hagerille tämän kaavan löytämisestä / keksimisestä.)
Mutta pidä hetki. Nykytilassa sinun on syötettävä tämä kaava vain C2, C12 ja C17. Eikö olisi parempi, jos voisit kirjoittaa sen C2: een ja täyttää ja näyttää vain oikeissa soluissa? Itse asiassa voit tehdä tämän. Voit muokata C2: n kaavaa olevan =IF(B1B2,Answer,""), ja kun täytät sen, se tekee työn:
Mutta miksi pysähtyä täällä? Miksi ei tehdä kaavasta nimettyä kaavaa, kuten tässä on esitetty:
Tämän toimimiseksi solun C2 on oltava aktiivinen solu (tai kaavan on oltava erilainen). Nyt voit korvata sarakkeen C kaavat seuraavilla =Answer2:
Voit nähdä, että C3: lla on =Answer2, kuten kaikissa sarakkeen C soluissa. Miksi et jatkaisi sitä sarakkeessa D? Kun D2: n kaava on sovellettu myös B1: een ja B2: een, näkyy tässä:
Joten jos pidät solun D2 valittuna ja määrität toisen kaavan, sano Answer3:
sitten voit kirjoittaa =Answer3soluun D2 ja täyttää:
Tässä on laskentataulukon yläosa, jossa näytetään kaavat, jota seuraa sama kuvakaappaus, jossa on arvot:
Kun muut ihmiset yrittävät selvittää tämän, he saattavat aluksi naarmuttaa päänsä!
Tämä vierasartikkeli on peräisin Excel MVP Bob Umlasilta. Se on kirjassa More Excel Outside the Box. Näet muut kirjan aiheet napsauttamalla tätä.
