Descărcați exemplul de registru de lucru
Acest tutorial arată cum să utilizați fișierul Funcția SUBTOTAL Excel în Excel pentru a calcula statisticile de sinteză.
SUBTOTAL Prezentare generală a funcției
Funcția SUBTOTAL Calculează o statistică sumară pentru o serie de date. Statisticile disponibile includ, dar nu se limitează la medie, deviația standard, număr, min și max. Vedeți lista completă de mai jos în secțiunea de intrări de funcții:
Pentru a utiliza funcția Foaie de lucru SUBTOTAL Excel, selectați o celulă și tastați:
(Observați cum apar datele introduse de formulă)
SUBTOTAL Sintaxa funcției și intrările:
| 1 | = SUBTOTAL (număr_funcție, REF1) |
function_num - Un număr care reprezintă operațiunea de efectuat.
REF1 - Gama sau referințele care conțin date de calculat.
Ce este funcția SUBTOTAL?
SUBTOTALUL este una dintre funcțiile unice din foile de calcul, deoarece poate face diferența dintre celulele ascunse și celulele non-ascunse. Acest lucru se poate dovedi a fi destul de util atunci când aveți de-a face cu intervale filtrate sau când trebuie să configurați calcule bazate pe diferite selecții ale utilizatorilor. Deoarece știe, de asemenea, să ignore alte funcții SUBTOTAL din calculele sale, îl putem folosi și în cadrul unor date rezumate de mari dimensiuni, fără teama de contorizare dublă.
Rezumat de bază cu SUBTOTAL
Să presupunem că ați avut un tabel cu vânzările sortate ale produselor și ați dorit să creați totaluri pentru fiecare produs, precum și să creați un total general. Puteți utiliza un tabel pivot sau puteți insera câteva formule. Luați în considerare acest aspect:
Am plasat câteva funcții SUBTOTAL în celulele B5 și B8 care arată
| 1 | = SUBTOTAL (9, B2: B4) |
Din sintaxă, puteți utiliza o varietate de numere pentru primul argument. În cazul nostru specific, folosim 9 pentru a indica că dorim să facem o sumă.
Să ne concentrăm asupra celulei B9. Are această formulă, care include întreaga gamă de date a coloanei B, dar nu include celelalte subtotale.
| 1 | = SUBTOTAL (9, B2: B8) |
NOTĂ: Dacă nu doriți să scrieți singur toate formulele de rezumat, puteți accesa panglica de date și puteți utiliza vrăjitorul contur - subtotal. Acesta va insera automat rânduri și va plasa formulele pentru dvs.
Diferența în primele argumente
În primul exemplu, am folosit un 9 pentru a indica că vrem să facem o sumă. Diferența dintre utilizarea 9 și 109 ar fi modul în care dorim ca funcția să gestioneze rândurile ascunse. Dacă utilizați denumirile 1XX, funcția nu va include rânduri care au fost ascunse sau filtrate manual.
Iată masa noastră dinainte. Am schimbat funcțiile, astfel încât să putem vedea diferența dintre argumentele 9 și 109. Cu toate vizibile, rezultatele sunt aceleași.
Dacă aplicăm un filtru pentru a filtra valoarea 6 în coloana B, cele două funcții rămân aceleași.
Dacă ascundem manual rândurile, vedem diferența. Funcția 109 a putut ignora rândul ascuns, în timp ce funcția 9 nu.
Schimbați operația matematică cu SUBTOTAL
S-ar putea să vă placă uneori să oferiți utilizatorului posibilitatea de a modifica tipul de calcule efectuate. De exemplu, vor să obțină suma sau media. Deoarece SUBTOTAL controlează operația matematică printr-un număr de argument, puteți scrie aceasta într-o singură formulă. Iată configurarea noastră:
Am creat un meniu derulant în D2 în care utilizatorul poate selecta „Sumă” sau „Medie”. Formula din E2 este:
| 1 | = SUBTOTAL (IF (D2 = "Media", 1, IF (D2 = "Suma", 9)), B2: B4) |
Aici, funcția IF va determina ce argument numeric să se dea SUBTOTALULUI. Dacă A5 este „Mediu”, atunci va genera un 1 și SUBTOTAL va da media B2: B4. Sau, dacă A5 este egal cu „Sumă”, atunci IF produce un 9 și obținem un rezultat diferit.
Puteți extinde această capacitate utilizând un tabel de căutare pentru a enumera și mai multe tipuri de operații pe care doriți să le efectuați. Tabelul dvs. de căutare ar putea arăta astfel
Apoi, puteți schimba formula din E2 pentru a fi
| 1 | = SUBTOTAL (VLOOKUP (A5, LookupTable, 2, 0), B2: B4) |
Formule condiționate cu SUBTOTAL
Deși SUBTOTAL are multe operații pe care le poate face, nu poate verifica criteriile de unul singur. Cu toate acestea, îl putem folosi într-o coloană de ajutor pentru a efectua această operațiune. Când aveți o coloană de date pe care știți că o va avea mereu aveți o bucată de date în ea, puteți utiliza abilitatea SUBTOTAL pentru a detecta rândurile ascunse.
Iată tabelul cu care vom lucra în acest exemplu. În cele din urmă, am dori să putem rezuma valorile pentru „Apple”, dar să lăsăm utilizatorului să filtreze coloana Qty.
Mai întâi, creați o coloană de ajutor care va găzdui funcția SUBTOTAL. În C2, formula este:
| 1 | = SUBTOTAL (103, A2) |
Amintiți-vă că 103 înseamnă că vrem să facem un COUNTA. Vă recomand să utilizați COUNTA, deoarece puteți apoi să completați celula de referință A2 fie numere sau text. Acum veți avea un tabel care arată astfel:
Acest lucru nu pare util la început, deoarece toate valorile sunt doar 1. Cu toate acestea, dacă ascundem rândul 3, acel „1” din C3 se va schimba la 0 deoarece indică un rând ascuns. Deși este imposibil să aveți o imagine care să arate valoarea specifică a celulei ascunse, o puteți verifica ascunzând rândul și apoi scriind o formulă de bază ca aceasta pentru a verifica.
| 1 | = C3 |
Acum, că avem o coloană care se va schimba în funcție de dacă este ascunsă sau nu, suntem gata să scriem ecuația finală. SUMIFII noștri vor arăta astfel
În această formulă, vom suma sumelor din coloana B doar atunci când coloana A este egală cu „Apple”, și valoarea din coloana C este 1 (de asemenea, rândul nu este ascuns). Să presupunem că utilizatorul nostru dorește să filtreze 600, deoarece pare anormal de mare. Putem vedea că formula noastră dă rezultate corecte.
Cu această abilitate, puteți aplica un cec unui COUNTIFS, SUMIFS sau chiar unui SUMPRODUCT. Adăugați posibilitatea de a permite utilizatorilor să controleze unele feliere de masă și sunteți gata să creați un tablou de bord minunat.
SUBTOTAL în Foi de calcul Google
Funcția SUBTOTAL funcționează exact la fel în Foi de calcul Google ca în Excel:
SUBTOTAL Exemple în VBA
De asemenea, puteți utiliza funcția SUBTOTAL în VBA. Tip:application.worksheetfunction.subtotal (function_num, reh1)
Executarea următoarelor instrucțiuni VBA
| 1234567891011121314151617 | Range ("C7") = Application.WorksheetFunction.Subtotal (1, Range ("C2: C5"))Range ("C8") = Application.WorksheetFunction.Subtotal (2, Range ("C2: C5"))Range ("C9") = Application.WorksheetFunction.Subtotal (4, Range ("C2: C5"))Range ("C10") = Application.WorksheetFunction.Subtotal (5, Range ("C2: C5"))Range ("C11") = Application.WorksheetFunction.Subtotal (9, Range ("C2: CE5"))Range ("D7") = Application.WorksheetFunction.Subtotal (1, Range ("D2: D5"))Range ("D8") = Application.WorksheetFunction.Subtotal (2, Range ("D2: D5"))Range ("D9") = Application.WorksheetFunction.Subtotal (4, Range ("D2: D5"))Range ("D10") = Application.WorksheetFunction.Subtotal (5, Range ("D2: D5"))Range ("D11") = Application.WorksheetFunction.Subtotal (9, Range ("D2: D5"))Range ("E7") = Application.WorksheetFunction.Subtotal (1, Range ("E2: E5"))Range ("E8") = Application.WorksheetFunction.Subtotal (2, Range ("E2: E5"))Range ("E9") = Application.WorksheetFunction.Subtotal (4, Range ("E2: E5"))Range ("E10") = Application.WorksheetFunction.Subtotal (5, Range ("E2: E5"))Range ("E11") = Application.WorksheetFunction.Subtotal (9, Range ("E2: E5")) |
va produce următoarele rezultate
Pentru argumentele funcției (funcția_num, etc.), le puteți introduce direct în funcție sau puteți defini variabile de utilizat.
Reveniți la lista tuturor funcțiilor din Excel
