Generisk formel
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Resumé
For at søge i flere regneark i en projektmappe efter en værdi og returnere et antal kan du bruge en formel baseret på COUNTIF og INDIRECT-funktionerne. Med nogle foreløbige opsætninger kan du bruge denne tilgang til at søge i en hel projektmappe efter en bestemt værdi. I det viste eksempel er formlen i C5:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Kontekst - eksempeldata
Arbejdsmappen indeholder i alt 4 regneark. Ark1 , Ark2 og Ark3 indeholder hver 1000 tilfældige fornavne, der ser sådan ud:
Forklaring
Området B7: B9 indeholder de arknavne, vi vil medtage i søgningen. Dette er kun tekststrenge, og vi skal gøre noget for at få dem til at blive anerkendt som gyldige arkreferencer.
Arbejder indefra og ud, dette udtryk bruges til at oprette en fuldarkreference:
"'"&B7&"'!"&"1:1048576"
De enkelte anførselstegn tilføjes for at tillade arknavne med mellemrum, og udråbstegnet er en standardsyntaks for områder, der inkluderer et arknavn. Teksten "1: 1048576" er et interval, der inkluderer hver række i regnearket.
Efter at B7 er evalueret, og værdier er sammenkædet, returnerer udtrykket ovenfor:
"'Sheet1'!1:1048576"
som går ind i INDIRECT-funktionen som 'ref_text' argument. INDIRECT evaluerer denne tekst og returnerer en standardreference til hver celle i Sheet1 . Dette går ind i COUNTIF-funktionen som rækkevidde. Kriterierne er angivet som en absolut henvisning til C4 (låst, så formlen kan kopieres ned i kolonne C).
COUNTIF returnerer derefter et antal af alle celler med en værdi lig med "mary", 25 i dette tilfælde.
Bemærk: COUNTIF er ikke store og små bogstaver.
Indeholder vs. lige
Hvis du vil tælle alle celler, der indeholder værdien i C4, i stedet for alle celler svarende til C4, kan du tilføje jokertegn til kriterierne som dette:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Nu tæller COUNTIF celler med understrengen "John" hvor som helst i cellen.
Ydeevne
Generelt er det ikke en god praksis at angive et interval, der inkluderer alle regnearkceller. Dette kan forårsage ydeevneproblemer, da området omfatter millioner af og millioner af celler. I dette eksempel er problemet sammensat, da formlen bruger INDIRECT-funktionen, som er en flygtig funktion. Flygtige funktioner genberegnes på hver regnearksændring, så indvirkningen på ydeevnen kan være enorm.
Når det er muligt, skal du begrænse intervaller til en fornuftig størrelse. For eksempel, hvis du ved, at data ikke vises efter række 1000, kan du bare søge på de første 1000 rækker sådan:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
