Download eksemplet på projektmappe
Denne vejledning vil demonstrere, hvordan man beregner "subtotal if", og tæller kun synlige rækker med kriterier.
SUBTOTAL funktion
SUBTOTAL -funktionen kan udføre forskellige beregninger på en række data (tælling, sum, gennemsnit osv.). Vigtigst af alt, det kan bruges til at beregne på kun synlige (filtrerede) rækker. I dette eksempel vil vi bruge funktionen til at tælle (COUNTA) synlige rækker ved at indstille SUBTOTAL function_num argumentet til 3 (En komplet liste over mulige funktioner findes her.)
= SUBTOTAL (3, $ D $ 2: $ D $ 14)
Læg mærke til, hvordan resultaterne ændres, når vi filtrerer rækker manuelt.
SUBTOTAL HVIS
For at oprette et "Subtotal If" vil vi bruge en kombination af SUMPRODUCT, SUBTOTAL, OFFSET, ROW og MIN i en matrixformel. Ved hjælp af denne kombination kan vi i det væsentlige oprette en generisk “SUBTOTAL HVIS” -funktion. Lad os gå gennem et eksempel.
Vi har en liste over medlemmer og deres tilstedeværelsesstatus for hvert arrangement:
Antages, at vi bliver bedt om at tælle antallet af medlemmer, der har deltaget i en begivenhed dynamisk, da vi manuelt filtrerer listen sådan:
For at opnå dette kan vi bruge denne formel:
= SUMPRODUCT ((=)*(SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0))))
= SUMPRODUCT ((D2: D14 = "Attended")*(SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))))
Når du bruger Excel 2022 og tidligere, skal du indtaste matrixformlen ved at trykke på CTRL + SKIFT + ENTER for at fortælle Excel, at du indtaster en matrixformel. Du ved, at formlen blev angivet korrekt som en matrixformel, når krøllede parenteser vises omkring formlen (se billedet ovenfor).
Hvordan fungerer formlen?
Formlen fungerer ved at multiplicere to arrays inde i SUMPRODUCT, hvor det første array omhandler vores kriterier og det andet array -filtre kun til synlige rækker:
= SUMPRODUCT (*)
Kriterierne
Kriteriets array evaluerer hver række i vores værdiområde ("Attended" Status i dette eksempel) og genererer en array som denne:
=(=)
= (D2: D14 = "Deltaget")
Produktion:
{SAND; FALSK; FALSK; SAND; FALSK; TURE; TURE; TURE; FALSK; FALSK; SAND; FALSK; SAND}
Bemærk, at output i det første array i vores formel ignorerer, om rækken er synlig eller ej, og det er her, vores andet array kommer ind for at hjælpe.
Synlighedsarrayet
Ved at bruge SUBTOTAL til at ekskludere ikke-synlige rækker i vores sortiment kan vi generere vores synlighedsarray. Imidlertid returnerer SUBTOTAL alene en enkelt værdi, mens SUMPRODUCT forventer en række værdier. For at omgå dette bruger vi OFFSET til at passere en række ad gangen. Denne teknik kræver fodring OFFSET et array, der indeholder et nummer ad gangen. Det andet array ser sådan ud:
= SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0))
= SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))
Produktion:
{1;1;0;0;1;1}
Sy de to sammen:
= SUMPRODUCT ({TRUE; TRUE; FALSE; FALSE; TRUE} * {1; 1; 0; 0; 1; 1})
= 4
SUBTOTAL HVIS med flere kriterier
For at tilføje flere kriterier skal du blot flere flere kriterier sammen i SUMPRODUCT'et sådan:
= SUMPRODUCT ((=)*(=)*(SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0))))
= SUMPRODUCT ((E2: E14 = "Attended")*(B2: B14 = 2019)*(SUBTOTAL (3, OFFSET (E2, ROW (E2: E14) -MIN (ROW (E2: E14)), 0)) ))
SUBTOTAL HVIS i Google Sheets
SUBTOTAL IF -funktionen fungerer nøjagtig det samme i Google Sheets som i Excel:
