Note: The other languages of the website are Google-translated. Back to English

Cum să rezumați doar celulele filtrate sau vizibile în Excel?

Poate că vă este ușor să însumați o coloană de numere în Excel, dar uneori trebuie să filtrați sau să ascundeți unele date pentru a vă îndeplini criteriile. După ascundere sau filtrare, și acum doriți doar să adăugați doar valorile filtrate sau vizibile. Dacă aplicați funcția Sumă în Excel, toate valorile, inclusiv datele ascunse, vor fi adăugate, în acest caz, cum ați putea însuma numai valorile filtrate sau vizibile ale celulei în Excel?


Sumați numai valorile filtrate sau vizibile ale celulei cu formula

Cu acest SUBTOTAL funcție care ignoră rândurile care au fost excluse de un filtru, puteți adăuga cu ușurință doar celulele vizibile. Puteți face acest lucru:

Presupunând că aveți o serie de date și că acestea au fost filtrate după cum aveți nevoie, consultați captura de ecran:

1. Într-o celulă goală, C13, de exemplu, introduceți această formulă: = Subtotal (109, C2: C12) (109 indică când sumați numerele, valorile ascunse vor fi ignorate; C2: C12 este intervalul pe care îl veți suma ignorând rândurile filtrate.) și apăsați tasta Intrați cheie.

Notă: Această formulă vă poate ajuta, de asemenea, să rezumați numai celulele vizibile dacă există rânduri ascunse în foaia de lucru. Cu toate acestea, această formulă nu poate însuma ignorarea celulelor din coloanele ascunse.

Suma / Numărul / Celulele medii vizibile numai într-un interval specificat, ignorând celulele / rândurile / coloanele ascunse sau filtrate

Funcția normală SUM / Număr / Medie va număra toate celulele din intervalul specificat pe celule de materie sunt ascunse / filtrate sau nu. În timp ce funcția Subtotal poate însuma / număra / medie doar ignorând rândurile ascunse. Cu toate acestea, Kutools pentru Excel SUMVISIBIL / CONTRAVIZIBIL / MEDIEVIZIBIL funcțiile vor calcula cu ușurință intervalul specificat ignorând orice celule, rânduri sau coloane ascunse. Încercare gratuită completă de 30 de zile!
Numărul sumelor publicitare medii numai celule vizibile

Sumați numai valorile filtrate sau vizibile ale celulei cu funcția definită de utilizator

Dacă sunteți interesat de următorul cod, vă poate ajuta, de asemenea, să rezumați doar celulele vizibile.

1. Țineți apăsat butonul ALT + F11 tastele și deschide fișierul Microsoft Visual Basic pentru aplicații fereastră.

2. Clic Insera > Moduleși lipiți următorul cod în fereastra Module.

Function SumVisible(WorkRng As Range) As Double
'Update 20130907
Dim rng As Range
Dim total As Double
For Each rng In WorkRng
    If rng.Rows.Hidden = False And rng.Columns.Hidden = False Then
        total = total + rng.Value
    End If
Next
SumVisible = total
End Function

3. Salvați acest cod și introduceți formula = SumVisible (C2: C12) într-o celulă goală. Vedeți captura de ecran:

4. Apoi apăsați Intrați cheie și veți obține rezultatul dorit.


Sumă / numărare / medie numai celule filtrate sau vizibile cu Kutools pentru Excel

Dacă aveți instalat Kutools pentru Excel, puteți calcula rapid suma / numărarea / media numai celulele vizibile sau filtrate în Excel cu ușurință.

Kutools pentru Excel - Include mai mult de 300 de instrumente la îndemână pentru Excel. Versiune completă de încercare gratuită de 30 de zile, nu este necesar card de credit! Ia-L Acum

De exemplu, doriți să însumați numai celulele vizibile, selectați celula la care veți plasa rezultatul însumării, tastați formula = SUMVISIBLE (C3: C12) (C3: C13 este intervalul în care veți însuma numai celulele vizibile) în el și apăsați tasta Intrați cheie.

Și apoi rezultatul însumării este calculat cu ignorarea tuturor celulelor ascunse. Vedeți captura de ecran:

Pentru a număra numai celulele vizibile, vă rugăm să aplicați această formulă = COUNTVISIBLE (C3: C12); Pentru a face medie numai celulelor vizibile, vă rugăm să aplicați această formulă = AVERAGEVISIBLE (C3: C12).

Notă: Dacă nu vă puteți aminti exact formulele, puteți urma pașii de mai jos pentru a însuma / număra / media doar celulele vizibile cu ușurință:

1. Selectați celula în care veți plasa rezultatul însumării și faceți clic Kutools > funcţii > Statistică și matematică > SUMVISIBIL (Sau MEDIEVISBLE, CONTRAVIZIBIL după cum aveți nevoie). Vedeți captura de ecran:

2. În caseta de dialog Argumente funcție de deschidere, vă rugăm să specificați intervalul în care veți suma ignorând celulele ascunse și faceți clic pe OK buton. Vedeți captura de ecran:

Kutools pentru Excel - Include mai mult de 300 de instrumente la îndemână pentru Excel. Versiune completă de încercare gratuită de 30 de zile, nu este necesar card de credit! Ia-L Acum

Și apoi rezultatul însumării este calculat cu ignorarea tuturor celulelor ascunse.


Demo: sumă / numărare / medie numai celule filtrate sau vizibile


Kutools pentru Excel include mai mult de 300 de instrumente la îndemână pentru Excel, gratuite pentru a încerca fără limitări în 30 de zile. Descărcați și încercați gratuit acum!

Sumați / numărați cu ușurință doar celulele filtrate / vizibile prin ștergerea rândurilor ascunse în Excel

La însumarea / numărarea celulelor filtrate în Excel, funcția SUM sau funcția Număr nu va ignora celulele ascunse. Dacă rândurile ascunse / filtrate sunt eliminate, putem suma sau număra celulele vizibile doar cu ușurință. Puteți încerca Kutools pentru Excel Ștergeți rândurile și coloanele ascunse (vizibile) utilitate pentru a o rezolva. Încercare gratuită completă de 30 de zile!
anunț ștergeți rândurile ascunse coloanele 3

Legate de articole:


Cele mai bune instrumente de productivitate Office

Kutools pentru Excel vă rezolvă majoritatea problemelor și vă crește productivitatea cu 80%

  • reutilizarea: Introduceți rapid formule complexe, diagrame și orice ai folosit anterior; Criptați celulele cu parola; Creați o listă de corespondență și trimiteți e-mailuri ...
  • Super Formula Bar (editați cu ușurință mai multe linii de text și formulă); Layout de citire (citiți și editați cu ușurință un număr mare de celule); Lipiți la interval filtrat...
  • Merge celule / rânduri / coloane fără a pierde date; Conținut de celule divizate; Combinați rânduri / coloane duplicate... Prevenirea celulelor duplicate; Comparați gamele...
  • Selectați Duplicat sau Unic Rânduri; Selectați Rânduri goale (toate celulele sunt goale); Super Find și Fuzzy Find în multe cărți de lucru; Selectare aleatorie ...
  • Copie exactă Mai multe celule fără modificarea referinței formulelor; Creați automat referințe la foi multiple; Introduceți gloanțe, Casete de selectare și multe altele ...
  • Extrageți textul, Adăugați text, eliminați după poziție, Eliminați spațiul; Creați și imprimați subtotaluri de paginare; Convertiți conținutul dintre celule și comentarii...
  • Super Filtru (salvați și aplicați scheme de filtrare altor foi); Sortare avansată după lună / săptămână / zi, frecvență și multe altele; Filtru special cu bold, italic ...
  • Combinați cărți de lucru și foi de lucru; Merge Tables pe baza coloanelor cheie; Împărțiți datele în mai multe foi; Conversia în loturi xls, xlsx și PDF...
  • Peste 300 de funcții puternice. Suportă Office / Excel 2007-2019 și 365. Suportă toate limbile. Implementare ușoară în întreprindere sau organizație. Funcții complete de încercare gratuită de 30 de zile. Garanție de restituire a banilor de 60 de zile.
fila kte 201905

Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară

  • Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
  • Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ​​ferestrele noi.
  • Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse în fiecare zi!
fundul officetab
Comentarii (21)
Încă nu există evaluări. Fii primul care evaluează!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună tuturor, aș dori să adaug două valori în coloane diferite, dar numai cu celule vizibile când folosesc un filtru. de exemplu. B3=B2+A3 Am încercat să folosesc funcția SUBTOTAL, dar nu funcționează. Va rog sa ne sfatuiti, multumesc anticipat! Salutari,
Acest comentariu a fost redus la minimum de moderatorul de pe site
Creați un rând deasupra rândului pe care veți alege să aplicați filtrul. În coloana pentru care aveți nevoie de total scrieți formula =subtotal(9,{start coloana nr.},{end coloana nr.}) De exemplu, dacă pentru coloana A am nevoie de subtotalurile bazate pe valoarea din coloana b, atunci dacă rândul antetului pentru datele sunt în rândul 2 și rândul 1 este necompletat, apoi puneți formula în coloana A1 ca =subtotal(9,A2:a999) Pe măsură ce modificați condiția de filtru pe coloana B, veți vedea schimbarea totală.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Cum adaug criterii la a doua opțiune. De exemplu, dacă am o gamă de valori, vreau să selecteze valori care au criterii de „X” într-o coloană.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Când afișați rândurile, atunci „=SumVisible()” reflectă eroarea. Este posibil să schimbați „Funcție” pentru a reflecta fie „Zero”, fie „Fără rânduri ascunse”?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Mulțumesc Milinda - Am introdus modulul atât în ​​„Cartea 1”, cât și în „Personal.xlsb”, dar când deschid un fișier nou, nu par să pot accesa funcția. Te rog cu ce am gresit?
Acest comentariu a fost redus la minimum de moderatorul de pe site
salut când dau în judecată această funcție și filtrez, totalurile nu se afișează. De ce?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Am avut o problemă similară în care am vrut să calculez diferențele de la două celule din rânduri diferite, unde folosesc un filtru de date pentru a discrimina ce rânduri sunt afișate. Am rezolvat problema în următoarea metodă: 1. Creați o nouă coloană [sau Rând în funcție de ceea ce este ascuns] și etichetați-o Vizibil. 2. Creați o nouă coloană care va fi folosită pentru a menține valoarea din ultimul rând „vizibil” pe care doriți să îl reduceți. 3. În coloana „Vizibil”, utilizați formula: =+SUBTOTAL(102,cell_in_row_or_column) Acesta va avea un 1 imediat ce îl introduceți, dar va fi 0 dacă celula nu este vizibilă. 4. În coloana creată pentru pasul 2, aveți nevoie de o formulă simplă: +IF(A,B,C); unde A=celula care returnează starea vizibilă, B valoarea pe care doriți să operați de pe același rând [doar o copie], C valoarea din aceeași celulă din rândul anterior, deoarece celula nu este vizibilă. În acest fel, ultimul element „Vizibil” este propagat în culise la rândul de deasupra rândului(e) vizibil(e). Acest lucru vă permite să creați formule simple efectuând operațiile de pe rândul precedent. Acest lucru funcționează pentru multe situații în care trebuie să vă ocupați de puncte de date individuale, spre deosebire de intervale care sunt mai bune cu Subtotal.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Mulțumesc mult!! A fost de mare ajutor :)
Acest comentariu a fost redus la minimum de moderatorul de pe site
Soluția a funcționat pentru mine. :D
Acest comentariu a fost redus la minimum de moderatorul de pe site
Pot să vă fac VBA pentru =SUMVISIBLE de mai sus să funcționeze bine. Cu toate acestea, doar dacă datele mele rulează vertical și ascund rânduri. Există vreo modalitate de a-l programa astfel încât să puteți rula datele pe orizontală și să funcționeze în continuare atunci când ascundeți coloanele? MULȚUMIRI!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Îmi pare rău că am greșit codul de securitate al postărilor din partea de jos și se pare că întrebarea mea ar fi fost ștearsă, deoarece mi-a cerut să încerc altul. Pot obține codul VBA pentru SUMVISIBLE să funcționeze bine dacă datele mele sunt verticale și ascund rândurile. Cu toate acestea, nu se întâmplă dacă datele mele rulează orizontal și vreau să ascund coloanele. Există vreo modalitate de a programa asta? Mulțumiri!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Asta e grozav! Multumesc mult.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Încerc să folosesc funcția AVERAGEVISIBLE pentru a face media celor 12 cele mai mari valori dintr-o coloană, aceasta funcționează în funcția normală AVERAGE ca = AVERAGE(LARGE(E971:E1540,{1,2,3,4,5,6,7,8,9,10,11,12) ,XNUMX})) totuși, când încerc să folosesc funcția vizibilă medie, returnează #VALOARE!, vreo idee?
Acest comentariu a fost redus la minimum de moderatorul de pe site
cum pot sări peste celulele ascunse din formulele Excel în foaia filtrată
Acest comentariu a fost redus la minimum de moderatorul de pe site
cum să omiteți celulele ascunse din formulele Excel în foaia filtrată...

celulele de culoare galbenă implică celule ascunse din cauza filtrului....
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună,
Ce fel de calcule trebuie să faci? Kutools pentru Excel acceptă trei funcții pentru a număra/suma/medie ignorând toate celulele ascunse.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Când aplic formula G3-K2 în foaia Excel filtrată și trageți formula, aceasta include celule ascunse

de exemplu



Valabilitate Lifting Cant
27054.59 123.41
26931.18 330.98
26600.20 493.66
26106.54 476.38
25630.16 480.64
25149.52 577.06


acum valoarea în valabilitate vine după ce ați scăzut cu cantitatea ridicată (cum ar fi a doua valoare 2=26931.18-27054.59)

A 3-a valoare în valabilitate=a 2-a valoare în valabilitate-a 2-a valoare în cantitate ridicată. si asa mai departe.....

acum această foaie este filtrată și când trag formulele în coloana de valabilitate, include celule ascunse, datorită filtrului.

ceea ce îmi dă un rezultat greșit
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună,
Normal =G3-K2 nu va ignora nicio celulă/rând/coloană ascunse prin tragerea pentru a copia. Îmi pare rău că nu pot găsi o formulă potrivită pentru dvs. Toate formulele sau metodele discutate în acest articol sunt doar despre sumă/număr/medie ignorând celulele ascunse.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Valabilitate Lifting Cant
27054.59 123.41
26931.18 330.98
26600.20 (26600.2=26931.18-330.98) 493.66
26106.54 476.38
25630.16 480.64
25149.52 577.06
Acest comentariu a fost redus la minimum de moderatorul de pe site
Când aplic formula G3-K2 în foaia Excel filtrată și trageți formula, aceasta include celule ascunse

de exemplu



Valabilitate Lifting Cant
27054.59 123.41
26931.18 330.98
26600.20 493.66
26106.54 476.38
25630.16 480.64
25149.52 577.06


acum valoarea în valabilitate vine după ce ați scăzut cu cantitatea ridicată (cum ar fi a doua valoare 2=26931.18-27054.59)

A 3-a valoare în valabilitate=a 2-a valoare în valabilitate-a 2-a valoare în cantitate ridicată. si asa mai departe.....

acum această foaie este filtrată și când trag formulele în coloana de valabilitate, include celule ascunse, datorită filtrului.

ceea ce îmi dă un rezultat greșit
Acest comentariu a fost redus la minimum de moderatorul de pe site
sau așa



coloana a coloana d

100 10

90 10

80 10



unde 90=100-10, 80=90-10 și așa mai departe.........

tragerea formulei include celule ascunse în formulă
Nu există comentarii postate aici încă
Lăsa comentarii
Postare ca invitat
×
Evaluează această postare:
0   Caractere
Locații sugerate