Tässä artikkelissa opimme palauttamaan SUM -hinnan arkipäivinä Excelissä.
Yksinkertaisesti sanottuna, kun työskentelet hintatietojen kanssa. Joskus meidän on löydettävä arvon summa, jolla on ehto. Ehto on saada hinta -arvojen summa erillisiin sarakkeisiin.
Tässä artikkelissa tarvitsemme seuraavien toimintojen käyttöä:
- SUMPRODUCT -toiminto
- COLUMN -toiminto
- MOD -toiminto
Nyt teemme kaavan näistä funktioista. Tässä meille annetaan luettelo arvoista ja meidän on saatava arvojen summa jokaisen annetun n: nnen sarakkeen moninkertaisena.
Käytä kaavaa:
= SUMPRODUCT ( - (MOD (COLUMN (list) - COLUMN (first) + 1, n) = 0), list))
lista : sarakkeen arvojen luettelo
ensimmäinen : luettelon ensimmäinen solu, joka palauttaa sarakkeen numeron
n : n: nnen sarakkeen arvot laskettavaksi.
Esimerkki:
Kaikkien näiden ymmärtäminen voi olla hämmentävää. Joten testataan tätä kaavaa suorittamalla se alla olevassa esimerkissä.
Tässä olemme antaneet tietoja, jotka sisältävät numeroita. Täältä meidän on löydettävä numeroiden SUMMA jokaisesta kolmannen sarakkeen monikertaisesta. Joten sitä varten olemme määrittäneet luettelot kaavan sarakkeessa.
Nyt käytämme alla olevaa kaavaa saadaksemme numeroiden summan 3., 6., 9., 12. ja niin edelleen, kunnes luettelo päättyy
Kaava:
= SUMPRODUCT ( - (MOD (COLUMN (list) - COLUMN (C5) + 1, P5) = 0), list)
luettelo: sarakkeen arvojen luettelo, joka annetaan nimettyjen alueiden avulla.
C5: luettelon ensimmäinen solu, joka palauttaa soluviittauksena annetun sarakkeen numeron
P5: solun viitteenä annettu n: n arvo
Selitys:
- MOD (COLUMN (list) -COLUMN (C5)+1, P5) palauttaa suhteellisen sarakkeen numeron kullekin n aikavälille. Kuten
{ 1 , 2 , 0 , 1 , 2 , 0 , 1 , 2 , 0 , 1 , 2 , 0 , 1 }
- Yllä oleva taulukko vastaa arvoa 0 ja palauttaa taulukon True & False.
{FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE}
- SUMPRODUCT -funktio ottaa huomioon arvon 1 jokaiselta vastaanotetulta TOSI -arvolta ja 0 -arvon EPÄTOSI -arvolta, kuten alla on esitetty.
= SUMPRODUCT ( - ({0, 0, 1, 0, 0, 1, 0, 0, 1, 0, 0, 1, 0}), {40, 36, 51, 33, 42, 30, 92, 67, 34, 54, 69, 56, 50})
- - muuntaa TOSI 1: ksi ja EPÄTOSI 0: ksi.
- Arvoja, jotka vastaavat 1: tä, lasketaan yhteen vain edellä selitetyn kaavan mukaisesti.
- Nyt SUMPRODUCT -toiminto laskee yhteen kaikki alla esitetyt arvot.
= SUMPRODUCT ({0, 0, 51, 0, 0, 30, 0, 0, 34, 0, 0, 56, 0})
Arvojen {51 + 30 + 34 + 56} SUM on palautettava 171.
Tässä funktion matriisi on nimetty alueeksi ja solu solun viitteeksi. Saat tuloksen painamalla Enter.
Kuten yllä olevasta tilannekuvasta näkyy, kullakin kolmannella aikavälillä kerättyjen arvojen summa palauttaa 171. Arvot on merkitty punaisiin laatikoihin yllä olevan tilannekuvan mukaisesti.
Tai voit käyttää luettelotaulukkoa alla olevan kaavan avulla:
= SUMPRODUCT ( - (MOD (COLUMN (C5: O5) - COLUMN (C5) + 1, P5) = 0), C5: O5)
Yllä oleva kaava palauttaa saman tuloksen. Vain yksi korvausluettelo nimeltä alue korvataan taulukkoalueella (C5: O5).
Kuten näet, kaava toimii hyvin. Nyt meidän on kopioitava muiden luettelojen kaava. Käytä siis CTRL + D tai vedä Excel -vaihtoehto alas.
Kuten yllä olevasta kaavasta näet, voit saada ehdollisia arvoja.
Huomautuksia:
- SUMPRODUCT -toiminto pitää muita kuin numeerisia arvoja 0: na.
- SUMPRODUCT -funktio pitää logiikka -arvoa TRUE 1: nä ja False 0: na.
- Argumenttitaulukon on oltava yhtä pitkä, muuten kaava palauttaa virheen.
Toivottavasti tämä artikkeli siitä, kuinka palauttaa SUM vain vain joka n. Sarake, jossa on kaavoja Excelissä, on selittävä. Lisää artikkeleita SUMPRODUCT -toiminnoista löydät täältä. Jaa kyselysi alla olevaan kommenttikenttään. Autamme sinua.
SUMPRODUCT -toiminnon käyttäminen Excelissä: Palauttaa summan Excelin useiden matriisien arvojen kertomisen jälkeen.
SUM, jos päivämäärä on välillä : Palauttaa Excelissä annettujen päivämäärien tai ajanjaksojen välisten arvojen SUMMA.
Summa, jos päivämäärä on suurempi kuin annettu päivämäärä: Palauttaa arvojen SUMMAN annetun päivämäärän tai jakson jälkeen Excelissä.
2 tapaa laskea yhteen kuukausittain Excelissä: Palauttaa arvojen SUMMA tietyn kuukauden aikana Excelissä.
Kuinka laskea useita sarakkeita ehdolla: Palauttaa useiden sarakkeiden arvojen summan, joiden ehto on Excel
Suosittuja artikkeleita
50 Excel -pikakuvake tuottavuuden lisäämiseksi
Muokkaa avattavaa luetteloa
Absoluuttinen viittaus Excelissä
Jos ehdollinen muotoilu
Jos käytetään jokerimerkkejä
Vlookup päivämäärän mukaan
Muunna tuumat jalkoiksi ja tuumiksi Excel 2016: ssa
Liity etu- ja sukunimeen Excelissä
Laske solut, jotka vastaavat joko A: ta tai B: tä