Excel

Søg i flere regneark efter værdi

Search Multiple Worksheets

Excel-formel: Søg i flere regneark efter værdiGenerisk formel
= COUNTIF ( INDIRECT ('''&sheetname&''!'&'range'),criteria)
Resumé

Hvis du vil søge i flere regneark i en projektmappe efter en værdi og returnere et antal, kan du bruge en formel baseret på TÆLHVIS og INDIREKTE funktioner. 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)

Kontekst - eksempeldata

Arbejdsmappen indeholder i alt 4 regneark. Ark1 , Ark2 og Ark3 hver indeholder 1000 tilfældige fornavne, der ser sådan ud:



Eksempeldata - søg i hele projektmappen eller flere ark



Forklaring

Området B7: B9 indeholder de arknavne, vi vil medtage i søgningen. Dette er bare tekststrenge, og vi skal gøre noget for at få dem til at blive anerkendt som gyldige arkreferencer.

konvertere tekst til nummer i excelformel

Arbejder indefra og ud, dette udtryk bruges til at opbygge 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:

hvad er en formel i Excel
 
''Sheet1'!1:1048576'

der går ind i INDIREKTE funktion som 'ref_text' argumentet. INDIRECT evaluerer denne tekst og returnerer en standardreference til hver celle i Ark1 . Dette går ind i COUNTIF-funktionen som rækkevidde. Kriterierne er angivet som en absolut reference 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.

hvordan man laver et cirkeldiagram i Excel

Bemærk: COUNTIF er ikke store og små bogstaver.

Indeholder vs. lige

Hvis du vil tælle alle celler, der indeholde værdien i C4 i stedet for alle celler lige 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 specificere 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 a flygtig funktion . Flygtige funktioner genberegner hver ændring af regnearket, så indvirkningen på ydeevnen kan være enorm.

Når det er muligt, begræns området til en fornuftig størrelse. Hvis du f.eks. Ved, at data ikke vises over række 1000, kan du bare søge på de første 1000 rækker sådan:

 
= COUNTIF ( INDIRECT ('''&B7&''!'&'1:1000'),$C)
Forfatter Dave Bruns


^