Lataa esimerkkityökirja
Tämä opetusohjelma näyttää kuinka laskea "välisumma, jos" laskemalla vain näkyvät rivit kriteereillä.
VAIHTOEHTO -toiminto
SUBTOTAL -toiminto voi suorittaa erilaisia laskelmia erilaisille tiedoille (luku, summa, keskiarvo jne.). Mikä tärkeintä, sitä voidaan käyttää vain näkyvillä (suodatetuilla) riveillä laskemiseen. Tässä esimerkissä käytämme funktiota laskemaan (COUNTA) näkyvät rivit asettamalla SUBTOTAL function_num -argumentiksi 3 (täydellinen luettelo mahdollisista funktioista löytyy täältä.)
= VAIHTOEHTO (3, $ D $ 2: $ D $ 14)
Huomaa, miten tulokset muuttuvat, kun suodatamme rivit manuaalisesti.
VAIHTOEHTO JOS
"Välisumman" luomiseksi käytämme yhdistelmää SUMPRODUCT, VAHVISUMMA, SIIRTO, RIVI ja MIN taulukkokaavassa. Käyttämällä tätä yhdistelmää voimme luoda olennaisesti yleisen "VAIHTOEHDO" -toiminnon. Käydään esimerkki läpi.
Meillä on luettelo jäsenistä ja heidän läsnäolotilanteensa jokaisesta tapahtumasta:
Olettaen, että meitä pyydetään laskemaan tapahtumaan osallistuneiden jäsenten määrä dynaamisesti, kun suodatamme luettelon manuaalisesti seuraavasti:
Tämän saavuttamiseksi voimme käyttää tätä kaavaa:
= SUMPRODUCT ((=)*(OSAVUOTO (3, OFFSET (, RIVI ()-MIN (RIVI ()), 0))))
= TUMMA
Kun käytät Excel 2022 ja sitä vanhempia versioita, sinun on annettava taulukon kaava painamalla CTRL + VAIHTO + ENTER kertoa Excelille, että kirjoitat matriisikaavan. Tiedät, että kaava on syötetty oikein taulukkokaavaksi, kun kaavan ympärille tulee kaarevat hakasulkeet (katso yllä oleva kuva).
Miten kaava toimii?
Kaava toimii kertomalla kaksi taulukkoa SUMPRODUCTin sisällä, jossa ensimmäinen taulukko käsittelee ehtojamme ja toinen matriisin suodattimet vain näkyville riveille:
= SUMPRODUCT (*)
Criteria Array
Kriteeritaulukko arvioi jokaisen rivin arvoalueellamme ("Attended" -tila tässä esimerkissä) ja luo seuraavanlaisen taulukon:
=(=)
= (D2: D14 = "Osallistunut")
Lähtö:
{TOTTA; VÄÄRÄ; VÄÄRÄ; TOTTA; VÄÄRÄ; TURE; TURE; TURE; VÄÄRÄ; VÄÄRÄ; TOTTA; VÄÄRÄ; TOTTA}
Huomaa, että kaavamme ensimmäisen taulukon tulos jättää huomiotta, onko rivi näkyvissä vai ei, missä toinen matriisi auttaa.
Näkyvyysjärjestelmä
Käyttämällä SUBTOTAL-arvoa poistaaksemme näkymättömät rivit valikoimastamme voimme luoda näkyvyysmatriisin. Kuitenkin vain VAIHTEISTO palauttaa yhden arvon, kun taas SUMPRODUCT odottaa joukon arvoja. Tämän kiertämiseksi käytämme OFFSETia kulkemaan yksi rivi kerrallaan. Tämä tekniikka vaatii syöttämään OFFSET -taulukon, joka sisältää yhden numeron kerrallaan. Toinen matriisi näyttää tältä:
= VAIHTOEHTO (3, SIIRTO (, RIVI ()-MIN (RIVI ()), 0))
= VAIHTOEHTO (3, SIIRTO (D2, RIVI (D2: D14) -MIN (RIVI (D2: D14)), 0))
Lähtö:
{1;1;0;0;1;1}
Näiden kahden ompeleminen yhteen:
= SUMPRODUCT ({TRUE; TRUE; FALSE; FALSE; TRUE; TRUE} * {1; 1; 0; 0; 1; 1})
= 4
OSAOSA JOS useita kriteerejä
Jos haluat lisätä useita ehtoja, yksinkertaisesti useita muita ehtoja yhdessä SUMPRODUCTissa, kuten:
= SUMPRODUCT ((=)*(=)*(OSAVUOTO (3, OFFSET (, Rivi ()-MIN (RIVI ()), 0))))
= SUMPRODUCT ((E2: E14 = "Attended")*(B2: B14 = 2019)*(OSAVUOTO (3, OFFSET (E2, RIVI (E2: E14) -MIN (RIVI (E2: E14)), 0)) ))
VAIHTOEHTO JOS Google Sheetsissä
VAIHTEISO IF -toiminto toimii täsmälleen samalla tavalla Google Sheetsissä kuin Excelissä: