Kako prešteti filtrirane celice z besedilom v Excelu?
V Excelu je štetje celic z besedilom preprosto. Ko pa gre za štetje filtriranih celic z besedilom, stvari postanejo zapletene. Ta vadnica bo predstavila tri načine za štetje celic z besedilom s filtriranega seznama.
Preštejte filtrirane celice z besedilom s pomožnim stolpcem
S funkcijo COUNTIF lahko poleg tega s pomočjo pomožnega stolpca enostavno preštejemo filtrirane celice z besedilom. Prosimo, naredite naslednje.
1. Kopirajte spodnjo formulo v celico D2, nato pritisnite tipko Enter, da dobite prvi rezultat.
=Vmesni SKUPAJ (103, A2)
Opomba: Pomožni stolpec s formulo SUBTOTAL je namenjen preverjanju, ali je vrstica filtrirana ali ne. in 103 stoji za COUNTA funkcijo v funkcija_številka prepir.
2. Potem povlecite ročico za polnjenje navzdol v celice, za katere želite uporabiti tudi to formulo.
3. Prepirajte spodnjo formulo v celico F2, nato pritisnite Vnesite ključ do končnega rezultata.
=COUNTIFS(A2:A18,"*", D2:D18, 1)
Vidimo, da obstajajo 4 celice z besedilom v filtriranih podatkih.
Drug način štetja filtriranih celic z besedilom je uporaba kombinacija od SUMPRODUCT, SUBTOTAL, INDIRECT, ROW & ISTEXT Funkcije. Prosimo, naredite naslednje.
Kopirajte spodnjo formulo v celico E2, nato pritisnite Vnesite ključ, da dobite rezultat.
=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A18))), --(ISTEXT(A2:A18)))
Razlaga formule:
- VRSTICA(A2:A18) vrne ustrezne številke vrstic obsega A2:A18.
- POSREDNO("A"&VRSTICA(A2:A18)) vrne veljavne sklice na celice iz danega obsega.
- Vmesni SKUPAJ(103, POSREDNO("A"&VRSTICA(A2:A18))) preizkusi, ali je vrstica filtrirana ali ne, in vrne 1 za vidne celice, 0 za skrite in prazne celice.
- ISTEXT(A2:A18) preveri, ali vsaka celica v obsegu A2:A18 vsebuje besedilo, in vrne True za celice z besedilom, False za druge celice. Dvojni unarni operator (--) pretvori vrednosti TRUE in FALSE v 1 in 0.
- SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A18))), --(ISTEXT(A2:A18))) lahko gledamo kot SUMPRODUCT({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1}). Nato SUMPRODUCT pomnoži obe matriki in vrne vsoto vrednosti, ki je 4.
Tretja metoda štetja celic z besedilom iz filtriranih podatkov je veriženje o SUMPRODUCT, SUBTOTAL, OFFSET, MIN, ROW & ISTEXT Funkcije. Prosimo, naredite naslednje.
Kopirajte spodnjo formulo v celico E2, nato pritisnite Vnesite ključ, da dobite rezultat.
=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)), -- (ISTEXT(A2:A18)) )
Razlaga formule:
- OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1) vrne posamezne sklice na celice iz obsega A2:A18.
- SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)) preveri, ali je vrstica filtrirana ali ne, in vrne 1 za vidne celice, 0 za skrite in prazne celice.
- ISTEXT(A2:A18) preveri, ali vsaka celica v obsegu A2:A18 vsebuje besedilo, in vrne True za celice z besedilom, False za druge celice. Dvojni unarni operator (--) pretvori vrednosti TRUE in FALSE v 1 in 0.
- SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)), -- (ISTEXT(A2:A18))) lahko gledamo kot SUMPRODUCT({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1}). Nato SUMPRODUCT pomnoži obe matriki in vrne vsoto vrednosti, ki je 4.
Druge operacije (članki)
Združite funkciji COUNTIF in LEFT za štetje celic, ki se začnejo z določenim znakom v Excelu
Če želite prešteti število celic, ki se začnejo z določenim znakom v Excelu, lahko kombinirate funkcije COUNTIF in LEFT, da to storite. Pravzaprav obstajajo različne vrste formul, ki jih lahko dosežemo. Ta vadnica vas bo vodila korak za korakom.
Kako v Excelu prešteti celice, ki vsebujejo številke ali ne?
Če obstaja celica, nekatere vsebujejo številke, druge pa besedilo, kako lahko v Excelu štejete celice, ki vsebujejo številke, ali ne hitro?
Kako prešteti celice, če je v Excelu izpolnjen eden od več kriterijev?
Kaj pa, če štetje celic vsebuje enega od več kriterijev? Tukaj bom delil načine za štetje celic, če vsebujejo X ali Y ali Z … itd. v Excelu.
Kako prešteti celice z določenim besedilom in barvo polnila/pisave v Excelu?
Ali znate šteti celice z več pogoji? Na primer, preštejte število celic, ki vsebujejo določeno besedilo in barvo pisave/polnila. Ta članek vam bo pokazal rešitev.
Najboljša orodja za pisarniško produktivnost
Kutools za Excel rešuje večino vaših težav in poveča vašo produktivnost za 80%
- Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
- Združi celice / vrstice / stolpce in vodenje podatkov; Vsebina razdeljenih celic; Združite podvojene vrstice in vsoto / povprečje... prepreči podvojene celice; Primerjaj obsege...
- Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
- Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
- Priljubljene in hitro vstavite formule, Obsegi, grafikoni in slike; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
- Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
- Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
- Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
- Razvrščanje vrtilne tabele po številka tedna, dan v tednu in še več ... Prikaži odklenjene, zaklenjene celice po različnih barvah; Označite celice s formulo / imenom...
- Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
- Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
- Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
