Excel

Sök efter värde i flera kalkylblad

Search Multiple Worksheets

Excel -formel: Sök i flera kalkylblad efter värdeGenerisk formel | _+_ | Sammanfattning

Om du vill söka efter ett värde i flera arbetsblad i en arbetsbok och returnera ett antal kan du använda en formel baserad på RÄKTA och INDIREKT funktioner. Med lite preliminär installation kan du använda detta tillvägagångssätt för att söka i en hel arbetsbok efter ett visst värde. I exemplet som visas är formeln i C5:



= COUNTIF ( INDIRECT ('''&sheetname&''!'&'range'),criteria)

Sammanhang - exempeldata

Arbetsboken innehåller totalt 4 kalkylblad. Ark 1 , Ark 2 , och Blad 3 varje innehåller 1000 slumpmässiga förnamn som ser ut så här:

Exempeldata - sök igenom hela arbetsboken eller flera blad





Förklaring

Området B7: B9 innehåller de bladnamn vi vill inkludera i sökningen. Det här är bara textsträngar, och vi måste göra lite arbete för att få dem att erkännas som giltiga bladreferenser.

hur man gör en sammanfattningstabell i Excel

Detta uttryck arbetar inifrån och ut och används för att bygga en fullständig arkreferens:



 
= COUNTIF ( INDIRECT ('''&B7&''!'&'1:1048576'),$C)

De enda citaten läggs till för att tillåta bladnamn med mellanslag, och utropstecknet är en standardsyntax för intervall som innehåller ett bladnamn. Texten '1: 1048576' är ett område som innehåller varje rad i kalkylbladet.

Efter att B7 har utvärderats och värden är sammanfogade returnerar uttrycket ovan:

hur numrerar jag rader i Excel
 
'''&B7&''!'&'1:1048576'

som går in i INDIRECT -funktion som argumentet 'ref_text'. INDIRECT utvärderar denna text och returnerar en standardreferens till varje cell i Ark 1 . Detta går in i COUNTIF -funktionen som intervallet. Kriterierna tillhandahålls som en absolut referens till C4 (låst så att formeln kan kopieras ner i kolumn C).

COUNTIF returnerar sedan en räkning av alla celler med ett värde lika med 'mary', 25 i detta fall.

hur man använder Visual Basic i Excel

Obs! COUNTIF är inte skiftlägeskänsligt.

Innehåller kontra lika

Om du vill räkna alla celler det innehålla värdet i C4, istället för alla celler likvärdig till C4 kan du lägga till jokertecken till kriterierna så här:

 
''Sheet1'!1:1048576'

Nu kommer COUNTIF att räkna celler med delsträngen 'John' var som helst i cellen.

Prestanda

I allmänhet är det inte bra att ange ett intervall som innehåller alla kalkylbladsceller. Om du gör det kan det orsaka prestandaproblem, eftersom intervallet inkluderar miljontals celler. I det här exemplet kompliceras problemet eftersom formeln använder funktionen INDIRECT, vilket är a flyktig funktion . Flyktiga funktioner räknas om vid varje ändring av kalkylblad, så påverkan på prestanda kan bli enorm.

Om möjligt, begränsa intervall till en vettig storlek. Om du till exempel vet att data inte kommer att visas efter rad 1000 kan du söka på bara de första 1000 raderna så här:

 
= COUNTIF ( INDIRECT ('''&B7&''!'&'1:1048576'),'*'&C4&'*')
Författare Dave Bruns


^