Yleinen kaava
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Yhteenveto
Voit etsiä arvoa useista laskentataulukoista työkirjasta ja palauttaa määrän käyttämällä kaavaa, joka perustuu COUNTIF- ja INDIRECT-funktioihin. Joissakin alustavissa määrityksissä voit käyttää tätä lähestymistapaa etsimään tiettyä arvoa koko työkirjasta. Esitetyssä esimerkissä kaava kohdassa C5 on:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Konteksti - näytetiedot
Työkirja sisältää yhteensä 4 laskentataulukkoa. Sheet1 , Taul2 ja Taul3 kukin sisältää 1000 satunnaisia etunimiä, jotka näyttävät tältä:
Selitys
Alue B7: B9 sisältää arkkien nimet, jotka haluamme sisällyttää hakuun. Nämä ovat vain tekstimerkkijonoja, ja meidän on tehtävä jonkin verran työtä, jotta ne tunnistetaan kelvollisiksi taulukoviitteiksi.
Työskentelemällä sisältä ulospäin, tätä lauseketta käytetään koko arkin viitteen rakentamiseen:
"'"&B7&"'!"&"1:1048576"
Yksittäiset lainausmerkit lisätään, jotta välilehtien nimet voivat olla välilyöntejä, ja huutomerkki on vakiosyntaksi alueille, jotka sisältävät taulukon nimen. Teksti "1: 1048576" on alue, joka sisältää laskentataulukon kaikki rivit.
Kun B7 on arvioitu ja arvot ketjutettu, yllä oleva lauseke palaa:
"'Sheet1'!1:1048576"
joka menee INDIRECT-funktioon "ref_text" -argumenttina. INDIRECT arvioi tämän tekstin ja palauttaa vakioviitteen jokaiselle Sheet1- solulle . Tämä siirtyy COUNTIF-funktioon alueena. Kriteerit annetaan absoluuttisena viitteenä C4: ään (lukittu, jotta kaava voidaan kopioida sarakkeeseen C).
COUNTIF palauttaa sitten kaikkien solujen määrän, joiden arvo on "mary", tässä tapauksessa 25.
Huomaa: COUNTIF ei eroa isoja ja pieniä kirjaimia.
Sisältää vs. yhtäläiset
Jos haluat laskea kaikki solut, jotka sisältävät arvon C4: ssä, kaikkien C4: tä vastaavien solujen sijaan, voit lisätä jokerimerkkejä seuraaviin kriteereihin:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Nyt COUNTIF laskee solut, joiden alaotsikko on "John" missä tahansa solussa.
Esitys
Yleensä ei ole hyvä käytäntö määrittää alue, joka sisältää kaikki laskentataulukon solut. Se voi aiheuttaa suorituskykyongelmia, koska alue sisältää miljoonia ja miljoonia soluja. Tässä esimerkissä ongelma on yhdistetty, koska kaava käyttää epäsuoraa funktiota, joka on haihtuva funktio. Haihtuvat toiminnot lasketaan uudelleen jokaisesta laskentataulukon muutoksesta, joten vaikutus suorituskykyyn voi olla valtava.
Kun mahdollista, rajoita alueet järkevään kokoon. Jos esimerkiksi tiedät, että tietoja ei näy rivin 1000 jälkeen, voit etsiä vain ensimmäisiltä 1000 riviltä näin:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
