Generisk formel
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Sammendrag
For å søke i flere regneark i en arbeidsbok for en verdi og returnere en telling, kan du bruke en formel basert på COUNTIF og INDIRECT-funksjonene. Med noen foreløpige oppsett kan du bruke denne tilnærmingen til å søke i en hel arbeidsbok for en bestemt verdi. I eksemplet vist er formelen i C5:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Kontekst - eksempeldata
Arbeidsboken inneholder totalt 4 regneark. Ark1 , Ark2 og Ark3 inneholder hver 1000 tilfeldige fornavn som ser slik ut:
Forklaring
Området B7: B9 inneholder arknavnene vi vil ha med i søket. Dette er bare tekststrenger, og vi må gjøre noe for å få dem til å bli anerkjent som gyldige arkreferanser.
Arbeidet fra innsiden og ut, dette uttrykket brukes til å bygge en fullarkreferanse:
"'"&B7&"'!"&"1:1048576"
Enkelt anførselstegn legges til for å tillate arknavn med mellomrom, og utropstegnet er en standard syntaks for områder som inkluderer et arknavn. Teksten "1: 1048576" er et område som inkluderer hver rad i regnearket.
Etter at B7 er evaluert, og verdier er sammenkoblet, returnerer uttrykket ovenfor:
"'Sheet1'!1:1048576"
som går inn i INDIRECT-funksjonen som 'ref_text' argument. INDIRECT evaluerer denne teksten og returnerer en standard referanse til hver celle i Sheet1 . Dette går inn i COUNTIF-funksjonen som området. Kriteriene er gitt som en absolutt referanse til C4 (låst slik at formelen kan kopieres ned i kolonne C).
COUNTIF returnerer deretter en telling av alle celler med en verdi lik "mary", 25 i dette tilfellet.
Merk: COUNTIF er ikke mellom store og små bokstaver.
Inneholder vs. like
Hvis du vil telle alle celler som inneholder verdien i C4, kan du i stedet for alle celler lik C4 legge til jokertegn i kriteriene slik:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Nå vil COUNTIF telle celler med understrengen "John" hvor som helst i cellen.
Opptreden
Generelt er det ikke god praksis å spesifisere et område som inkluderer alle regnearkcellene. Å gjøre det kan føre til ytelsesproblemer, siden området inkluderer millioner og millioner av celler. I dette eksemplet er problemet sammensatt, siden formelen bruker INDIRECT-funksjonen, som er en flyktig funksjon. Flyktige funksjoner beregner om hver endring av regnearket, slik at ytelsen kan bli stor.
Når det er mulig, begrenser du områdene til en fornuftig størrelse. Hvis du for eksempel vet at data ikke vises etter rad 1000, kan du søke i de første 1000 radene slik:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
