Ladda ner exempelarbetsboken
Denna handledning kommer att visa hur man beräknar "delsumma om" och endast räknar synliga rader med kriterier.
SUBTOTAL funktion
SUBTOTAL -funktionen kan utföra olika beräkningar på ett dataområde (antal, summa, genomsnitt, etc.). Viktigast av allt, det kan användas för att beräkna på endast synliga (filtrerade) rader. I det här exemplet kommer vi att använda funktionen för att räkna (COUNTA) synliga rader genom att ställa in argumentet SUBTOTAL function_num till 3 (En fullständig lista över möjliga funktioner finns här.)
= SUBTOTALT (3, $ D $ 2: $ D $ 14)
Lägg märke till hur resultaten ändras när vi filtrerar rader manuellt.
SUBTOTALT OM
För att skapa ett ”Delsumma om” använder vi en kombination av SUMPRODUCT, SUBTOTAL, OFFSET, ROW och MIN i en matrisformel. Med denna kombination kan vi i huvudsak skapa en generisk ”SUBTOTAL IF” -funktion. Låt oss gå igenom ett exempel.
Vi har en lista över medlemmar och deras närvarostatus för varje evenemang:
Antag att vi blir ombedda att räkna antalet medlemmar som har deltagit i ett evenemang dynamiskt när vi manuellt filtrerar listan så här:
För att uppnå detta kan vi använda denna formel:
= SUMPRODUCT ((=)*(SUBTOTAL (3, OFFSET (, RAD ()-MIN (ROW ()), 0))))
= SUMPRODUCT ((D2: D14 = "Attended")*(SUBTOTAL (3, OFFSET (D2, RAD (D2: D14) -MIN (ROW (D2: D14)), 0)))))
När du använder Excel 2022 och tidigare måste du ange matrisformeln genom att trycka på CTRL + SKIFT + ENTER för att berätta för Excel att du anger en matrisformel. Du vet att formeln angavs korrekt som en matrisformel när lockiga parenteser visas runt formeln (se bilden ovan).
Hur fungerar formeln?
Formeln fungerar genom att multiplicera två matriser inuti SUMPRODUCT, där den första matrisen behandlar våra kriterier och den andra matrisfiltret endast till synliga rader:
= SUMPRODUKT (*)
Kriterierna
Kriteriet array utvärderar varje rad i vårt värdeintervall ("Attended" Status i detta exempel) och genererar en array så här:
=(=)
= (D2: D14 = "Besökt")
Produktion:
{SANN; FALSK; FALSK; SANN; FALSK; TURE; TURE; TURE; FALSK; FALSK; SANN; FALSK; SANN}
Observera att utdata i den första matrisen i vår formel ignorerar om raden är synlig eller inte, det är där vår andra matris kommer in för att hjälpa.
Synlighetsmatrisen
Genom att använda SUBTOTAL för att utesluta osynliga rader i vårt sortiment kan vi generera vår synlighetsmatris. Emellertid returnerar SUBTOTAL ensam ett värde, medan SUMPRODUCT förväntar sig en rad värden. För att undvika detta använder vi OFFSET för att passera en rad i taget. Denna teknik kräver matning OFFSET en array som innehåller ett nummer i taget. Den andra matrisen ser ut så här:
= SUBTOTAL (3, OFFSET (, RAD ()-MIN (RAD ()), 0))
= SUBTOTAL (3, OFFSET (D2, RAD (D2: D14) -MIN (RAD (D2: D14)), 0))
Produktion:
{1;1;0;0;1;1}
Sy ihop de två:
= SUMPRODUCT ({TRUE; TRUE; FALSE; FALSE; TRUE} * {1; 1; 0; 0; 1; 1})
= 4
SUBTOTAL OM med flera kriterier
För att lägga till flera kriterier, helt enkelt flera fler kriterier tillsammans i SUMPRODUCT så här:
= SUMPRODUCT ((=)*(=)*(SUBTOTAL (3, OFFSET (, RAD ()-MIN (ROW ()), 0))))
= SUMPRODUCT ((E2: E14 = "Attended")*(B2: B14 = 2019)*(SUBTOTAL (3, OFFSET (E2, ROW (E2: E14) -MIN (ROW (E2: E14)), 0)) )))
SUBTOTALT OM i Google Kalkylark
SUBTOTAL IF -funktionen fungerar exakt samma i Google Kalkylark som i Excel:
