Iščite vrednost na več delovnih listih

Če želite v delovnem zvezku iskati vrednost in vrniti štetje, lahko uporabite formulo, ki temelji na COUNTIF in POSREDNO funkcije. Z nekaj predhodnimi nastavitvami lahko ta pristop uporabite za iskanje določene vrednosti v celotnem delovnem zvezku. V prikazanem primeru je formula v C5:

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

Kontekst - vzorčni podatki

Delovni zvezek vsebuje skupaj 4 delovne liste. List1 , List 2 , in List 3 vsaka vsebuje 1000 naključnih imen, ki izgledajo tako:

Obseg B7: B9 vsebuje imena listov, ki jih želimo vključiti v iskanje. To so samo besedilni nizi in moramo narediti nekaj dela, da jih prepoznamo kot veljavne sklice na liste.

kako lahko prikažete seznam imen obsegov za lažje urejanje ali brisanje?

Ta izraz, ki deluje od znotraj navzven, se uporablja za izdelavo sklica celotnega lista:

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

Posamezni narekovaji so dodani, da omogočijo imena listov s presledki, klicaj pa je standardna sintaksa za obsege, ki vključujejo ime lista. Besedilo '1: 1048576' je obseg, ki vključuje vsako vrstico na delovnem listu.

Ko je B7 ovrednoten in vrednosti združene, se zgornji izraz vrne:

kako odšteti dneve v

 
'''&B7&''!'&'1:1048576'

ki gre v Neposredna funkcija kot argument 'ref_text'. INDIRECT ovrednoti to besedilo in vrne standardni sklic na vsako celico v List1 . To gre za funkcijo COUNTIF kot obseg. Merilo je podano kot absolutna referenca do C4 (zaklenjeno, da se formula lahko kopira v stolpec C).

COUNTIF nato vrne štetje vseh celic z vrednostjo, ki je enaka 'mary', 25 v tem primeru.

sporočilo excel vba da ne

Opomba: COUNTIF ne razlikuje med velikimi in malimi črkami.

Vsebuje v primerjavi z enakimi

Če želite prešteti vse celice vsebujejo vrednost v C4, namesto vseh celic enako v C4, lahko dodate nadomestnih znakov po takih merilih:

 
''Sheet1'!1:1048576'

Zdaj COUNTIF šteje celice s podnizom 'John' kjer koli v celici.

Izvedba

Na splošno ni dobra praksa določiti obseg, ki vključuje vse celice delovnega lista. To lahko povzroči težave pri delovanju, saj obseg vključuje milijone in milijone celic. V tem primeru je težava zapletena, saj formula uporablja funkcijo INDIRECT, ki je a hlapna funkcija . Hlapne funkcije se preračunajo pri vsaki spremembi delovnega lista, zato je lahko vpliv na zmogljivost ogromen.

Kadar je mogoče, omejite obseg na razumno velikost. Če na primer veste, da podatki ne bodo prikazani mimo 1000 vrstice, lahko poiščete le prvih 1000 vrstic tako:

 
= COUNTIF ( INDIRECT ('''&B7&''!'&'1:1048576'),'*'&C4&'*')