Last ned eksemplet på arbeidsbok
Denne opplæringen vil demonstrere hvordan du beregner "delsum hvis", og bare teller synlige rader med kriterier.
SUBTOTAL Funksjon
SUBTOTAL -funksjonen kan utføre forskjellige beregninger på en rekke data (telling, sum, gjennomsnitt, etc.). Viktigst, det kan brukes til å beregne på bare synlige (filtrerte) rader. I dette eksemplet bruker vi funksjonen til å telle (COUNTA) synlige rader ved å sette SUBTOTAL function_num argumentet til 3 (En fullstendig liste over mulige funksjoner finnes her.)
= SUBTOTALT (3, $ D $ 2: $ D $ 14)
Legg merke til hvordan resultatene endres når vi filtrerer rader manuelt.
SUBTOTAL HVIS
For å lage et “Delsum hvis”, bruker vi en kombinasjon av SUMPRODUCT, SUBTOTAL, OFFSET, ROW og MIN i en matriseformel. Ved å bruke denne kombinasjonen kan vi i hovedsak lage en generisk “SUBTOTAL IF” -funksjon. La oss gå gjennom et eksempel.
Vi har en liste over medlemmer og deres tilstedeværelsesstatus for hvert arrangement:
Anta at vi blir bedt om å telle antall medlemmer som har deltatt på et arrangement dynamisk mens vi filtrerer listen manuelt slik:
For å oppnå dette kan vi bruke denne formelen:
= 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 bruker Excel 2022 og tidligere, må du angi matriseformelen ved å trykke CTRL + SKIFT + ENTER for å fortelle Excel at du skriver inn en matriseformel. Du vet at formelen ble angitt riktig som en matriseformel når krøllete parenteser vises rundt formelen (se bildet ovenfor).
Hvordan fungerer formelen?
Formelen fungerer ved å multiplisere to matriser inne i SUMPRODUCT, der den første matrisen omhandler kriteriene våre og den andre matrisefiltrene til bare synlige rader:
= SUMPRODUCT (*)
Kriteria -oppstillingen
Kriteriet array evaluerer hver rad i vårt verdiområde ("Attended" Status i dette eksemplet) og genererer en array som denne:
=(=)
= (D2: D14 = "Deltokt")
Produksjon:
{EKTE; FALSK; FALSK; EKTE; FALSK; TURE; TURE; TURE; FALSK; FALSK; EKTE; FALSK; EKTE}
Vær oppmerksom på at utdataene i den første matrisen i formelen vår ignorerer om raden er synlig eller ikke, og det er der den andre serien kommer inn for å hjelpe.
Synlighetsmatrisen
Ved å bruke SUBTOTAL til å ekskludere ikke-synlige rader i vårt utvalg, kan vi generere vårt synlighetsarray. Imidlertid vil SUBTOTAL alene returnere en enkelt verdi, mens SUMPRODUCT venter en rekke verdier. For å omgå dette bruker vi OFFSET til å passere en rad om gangen. Denne teknikken krever mating av OFFSET en matrise som inneholder ett tall om gangen. Den andre serien ser slik ut:
= SUBTOTAL (3, OFFSET (, RAD ()-MIN (ROW ()), 0))
= SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))
Produksjon:
{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 å legge til flere kriterier, bare flere flere kriterier sammen i SUMPRODUCT slik:
= 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 Regneark
SUBTOTAL IF -funksjonen fungerer nøyaktig det samme i Google Sheets som i Excel:
