Lataa esimerkkityökirja
Tämä opetusohjelma osoittaa, miten Excel -VAIHTOEHTO -toiminto Excelissä yhteenvetotilastojen laskemiseksi.
VAIHTOEHTO Toimintojen yleiskatsaus
ALAOSA -funktio Laskee yhteenvetotilaston tietosarjalle. Käytettävissä olevat tilastot sisältävät, mutta eivät rajoitu niihin, keskiarvon, keskihajonnan, määrän, minimin ja enimmäisarvon. Katso täydellinen luettelo alla toiminnon tulot -osiosta:
Jos haluat käyttää Excelin välilaskutoimintoa, valitse solu ja kirjoita:
(Huomaa, miten kaavasyötteet näkyvät)
VAIHTOEHTO Toimintojen syntaksi ja tulot:
| 1 | = VAIHTOEHTO (funktion_numero, VIITE1) |
funktion_numero - Numero, joka edustaa suoritettavaa toimintoa.
OHJE 1 - Alueet tai viitteet, jotka sisältävät laskettavia tietoja.
Mikä on SUBTOTAL -toiminto?
Välisumma on yksi laskentataulukoiden ainutlaatuisista toiminnoista, koska se voi erottaa piilotettujen ja ei-piilotettujen solujen välisen eron. Tämä voi osoittautua varsin hyödylliseksi käsiteltäessä suodatettuja alueita tai kun sinun on määritettävä laskelmat eri käyttäjien valintojen perusteella. Koska se tietää myös jättää huomiotta muut ALESUMMA-funktiot laskelmissaan, voimme käyttää sitä myös suurissa yhteenvetotiedoissa ilman kaksinkertaista laskemista.
Perusyhteenveto ja VAIHTO
Oletetaan, että sinulla oli taulukko lajitelluista tuotemyynneistä ja halusit luoda kokonaissummat kullekin tuotteelle sekä luoda kokonaissumman. Voit käyttää pivot -taulukkoa tai lisätä kaavoja. Harkitse tätä asettelua:
Olen sijoittanut joitain VAIHTO -toimintoja soluihin B5 ja B8, jotka näyttävät
| 1 | = VAIHTO (9, B2: B4) |
Syntaksista voit käyttää erilaisia numeroita ensimmäisessä argumentissa. Meidän tapauksessamme käytämme yhdeksää osoittamaan haluamme tehdä summan.
Keskitymme soluun B9. Siinä on tämä kaava, joka sisältää koko sarakkeen B tietoalueen, mutta ei muita välisummia.
| 1 | = OSAVUOTO (9, B2: B8) |
HUOMAUTUS: Jos et halua kirjoittaa kaikkia yhteenvetokaavoja itse, voit siirtyä Data -valintanauhaan ja käyttää ohjattua ääriviivaa - Välisumma. Se lisää automaattisesti rivit ja asettaa kaavat puolestasi.
Ero ensimmäisissä väitteissä
Ensimmäisessä esimerkissä osoitimme 9: llä, että haluamme tehdä summan. Ero 9: n ja 109: n välillä olisi se, miten haluamme toiminnon käsittelevän piilotettuja rivejä. Jos käytät 1XX -nimityksiä, toiminto ei sisällä manuaalisesti piilotettuja tai suodatettuja rivejä.
Tässä meidän pöytä aiemmalta. Olemme siirtäneet toimintoja, jotta voimme nähdä eron 9 ja 109 argumentin välillä. Kun kaikki näkyvät, tulokset ovat samat.
Jos käytämme suodatinta sarakkeen B arvon 6 suodattamiseksi, kaksi toimintoa pysyvät samana.
Jos piilotamme rivit manuaalisesti, näemme eron. 109 -toiminto pystyi ohittamaan piilotetun rivin, kun taas 9 -toiminto ei.
Vaihda matemaattista toimintaa VAIHTOEHDOLLA
Haluat ehkä joskus antaa käyttäjälle mahdollisuuden muuttaa suoritettavia laskelmia. Haluavatko he esimerkiksi saada summan tai keskiarvon. Koska SUBTOTAL ohjaa matemaattista operaatiota argumenttiluvulla, voit kirjoittaa tämän yhteen kaavaan. Tässä meidän kokoonpanomme:
Olemme luoneet D2: n avattavan valikon, jossa käyttäjä voi valita joko "Summa" tai "Keskiarvo". E2: n kaava on:
| 1 | = VAHVISUMMA (JOS (D2 = "Keskiarvo", 1, JOS (D2 = "Summa", 9)), B2: B4) |
Tässä IF -funktio määrittää, mitkä numeeriset argumentit annetaan VAIHTOEHDOLLE. Jos A5 on "keskiarvo", se antaa tuloksen 1 ja VAIHTEISTO antaa keskiarvon B2: B4. Tai jos A5 on sama kuin summa, IF antaa 9 ja saamme erilaisen tuloksen.
Voit laajentaa tätä ominaisuutta käyttämällä hakutaulukkoa, jossa luetellaan vielä useampia toimintoja, jotka haluat suorittaa. Hakutaulukko saattaa näyttää tältä
Sitten voit muuttaa E2: n kaavan olevan
| 1 | = VAIHTOEHTO (VLOOKUP (A5, Hakutaulukko, 2, 0), B2: B4) |
Ehdolliset kaavat, joissa on VAIHTOEHTO
Vaikka SUBTOTALilla on monia toimintoja, joita se voi tehdä, se ei voi tarkistaa ehtoja yksin. Voimme kuitenkin käyttää sitä auttajasarakkeessa tämän toiminnon suorittamiseen. Kun sinulla on sarake tietoa, jonka tiedät aina Jos sinulla on tietoja, voit käyttää SUBTOTALs -ominaisuutta piilotettujen rivien havaitsemiseen.
Tässä on taulukko, jonka kanssa työskentelemme tässä esimerkissä. Lopulta haluaisimme pystyä laskemaan yhteen "Apple" -arvot, mutta myös antaa käyttäjän suodattaa Määrä -sarakkeen.
Luo ensin avustajasarake, jossa on SUBTOTAL -toiminto. C2: ssa kaava on:
| 1 | = VAIHTOEHTO (103, A2) |
Muista, että 103 tarkoittaa, että haluamme tehdä COUNTAn. Suosittelen COUNTAn käyttöä, koska voit sitten täyttää A2 -vertailusolusi jompikumpi numeroita tai tekstiä. Saat nyt taulukon, joka näyttää tältä:
Tämä ei vaikuta aluksi hyödylliseltä, koska kaikki arvot ovat vain 1. Kuitenkin, jos piilotamme rivin 3, C3: n "1" muuttuu 0: ksi, koska se osoittaa piilotettua riviä. Vaikka on mahdotonta saada kuvaa, joka näyttää tietyn piilotetun solun arvon, voit tarkistaa sen piilottamalla rivin ja kirjoittamalla tämän kaltaisen peruskaavan tarkistettavaksi.
| 1 | = C3 |
Nyt kun meillä on sarake, jonka arvo muuttuu sen mukaan, onko se piilotettu vai ei, olemme valmiita kirjoittamaan lopullisen yhtälön. SUMIFSimme näyttävät tältä
Tässä kaavassa laskemme yhteen sarakkeen B arvot vain, kun sarake A on "Apple", ja sarakkeen C arvo on 1 (eli rivi ei ole piilossa). Oletetaan, että käyttäjämme haluaa suodattaa pois 600, koska se näyttää epätavallisen korkealta. Voimme nähdä, että kaavamme antaa oikean tuloksen.
Tällä kyvyllä voit käyttää shekkiä COUNTIFS, SUMIFS tai jopa SUMPRODUCT. Kun lisäät mahdollisuuden antaa käyttäjiesi hallita joitain taulukonleikkureita, olet valmis luomaan mahtavan hallintapaneelin.
SUBTOTAL Google Sheetsissä
SUBTOTAL -toiminto toimii täsmälleen samalla tavalla Google Sheetsissä kuin Excelissä:
VAIHTOESIMERKKI Esimerkkejä VBA: sta
Voit käyttää myös VÄLIOSA -toimintoa VBA: ssa. Tyyppi:application.worksheetfunction.subtotal (function_num, reh1)
Seuraavien VBA -lausekkeiden suorittaminen
| 1234567891011121314151617 | Alue ("C7") = Application.WorksheetFunction.Subtotal (1, alue ("C2: C5"))Alue ("C8") = Application.WorksheetFunction.Subtotal (2, alue ("C2: C5"))Alue ("C9") = Application.WorksheetFunction.Subtotal (4, alue ("C2: C5"))Alue ("C10") = Application.WorksheetFunction.Subtotal (5, alue ("C2: C5"))Alue ("C11") = Application.WorksheetFunction.Subtotal (9, alue ("C2: CE5"))Alue ("D7") = Application.WorksheetFunction.Subtotal (1, alue ("D2: D5"))Alue ("D8") = Application.WorksheetFunction.Subtotal (2, alue ("D2: D5"))Alue ("D9") = Application.WorksheetFunction.Subtotal (4, alue ("D2: D5"))Alue ("D10") = Application.WorksheetFunction.Subtotal (5, alue ("D2: D5"))Alue ("D11") = Application.WorksheetFunction.Subtotal (9, alue ("D2: D5"))Alue ("E7") = Application.WorksheetFunction.Subtotal (1, alue ("E2: E5"))Alue ("E8") = Application.WorksheetFunction.Subtotal (2, alue ("E2: E5"))Alue ("E9") = Application.WorksheetFunction.Subtotal (4, alue ("E2: E5"))Alue ("E10") = Application.WorksheetFunction.Subtotal (5, alue ("E2: E5"))Alue ("E11") = Application.WorksheetFunction.Subtotal (9, alue ("E2: E5")) |
tuottaa seuraavat tulokset
Funktioargumenteille (funktion_numero jne.) Voit joko syöttää ne suoraan funktioon tai määrittää sen sijaan käytettäviä muuttujia.
Palaa Excelin kaikkien toimintojen luetteloon
