V tem članku se bomo naučili vračati ceno SUM po delavnikih v Excelu.
Z enostavnimi besedami, pri delu s podatki, napolnjenimi s cenami. Včasih moramo najti vsoto vrednosti, ki ima pogoj. Pogoj je, da dobite vsoto vrednosti cen v ločenih stolpcih.
Za ta članek bomo potrebovali uporabo naslednjih funkcij:
- SUMPRODUCT funkcija
- Funkcija COLUMN
- Funkcija MOD
Zdaj bomo iz teh funkcij naredili formulo. Tu bomo dobili seznam vrednosti in vnesti moramo vsoto vrednosti v vsakem večkratniku danega n -tega stolpca.
Uporabite formulo:
= SUMPRODUCT ( - (MOD (COLUMN (seznam) - COLUMN (prvi) + 1, n) = 0), seznam)
seznam : seznam vrednosti vzdolž stolpca
prvi : prva celica seznama, ki vrne številko stolpca
n : vrednosti n stolpca za seštevanje.
Primer:
Vse to je lahko zmedeno za razumevanje. Torej, preizkusimo to formulo tako, da jo zaženemo na spodnjem primeru.
Tu smo podali podatke, ki vsebujejo številke. Tu moramo poiskati SUM številk v vsakem večkratniku tretjega stolpca. Zato smo za formulo dodelili sezname v stolpcu.
Zdaj bomo uporabili spodnjo formulo, da dobimo SUM številk na 3., 6., 9., 12. in tako naprej, dokler se seznam ne konča
Formula:
= SUMPRODUCT ( - (MOD (STOLPEK (seznam) - STOLPEK (C5) + 1, P5) = 0), seznam)
list: seznam vrednosti vzdolž stolpca, podan z uporabo imenovanih obsegov.
C5: prva celica seznama, ki vrne številko stolpca, ki je navedena kot referenca celice
P5: vrednost n podana kot referenca celice
Pojasnilo:
- MOD (COLUMN (seznam) -COLUMN (C5)+1, P5) vrne relativno številko stolpca za vsak n interval. Všeč mi je
{ 1 , 2 , 0 , 1 , 2 , 0 , 1 , 2 , 0 , 1 , 2 , 0 , 1 }
- Zgornji niz se bo ujemal z vrednostjo 0 in vrne niz True & False.
{FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE}
- Funkcija SUMPRODUCT upošteva vrednost 1 za vsako prejeto vrednost TRUE in 0 za vrednost FALSE, kot je prikazano spodaj.
= 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})
- - pretvori TRUE v 1 in FALSE v 0.
- Vrednosti, ki ustrezajo 1s, se bodo seštevale le, kot je prikazano v zgoraj pojasnjeni formuli.
- Zdaj funkcija SUMPRODUCT sešteje vse vrednosti, kot je prikazano spodaj.
= PODROČJE ({0, 0, 51, 0, 0, 30, 0, 0, 34, 0, 0, 56, 0})
SUM vrednosti {51 + 30 + 34 + 56} mora vrniti 171.
Tu je matrika funkciji podana kot imenovano območje, celica pa kot cell_reference. Pritisnite Enter, da dobite rezultat.
Kot lahko vidite na zgornjem posnetku, vsota vrednosti, pridobljenih pri vsakem tretjem intervalu, vrne 171. Vrednosti so označene pod rdečimi polji, kot je prikazano na zgornjem posnetku.
Lahko pa uporabite matriko seznamov po spodnji formuli:
= PODROČJE ( - (MOD (STOLPEC (C5: O5) - STOLPEK (C5) + 1, P5) = 0), C5: O5)
Zgornja formula bo vrnila enak rezultat. Obstaja samo en nadomestni seznam z imenom range se nadomesti z obsegom matrike (C5: O5).
Kot lahko vidite, formula deluje dobro. Zdaj moramo kopirati formulo za druge sezname. Zato uporabite CTRL + D ali povlecite možnost excela navzdol.
Kot lahko vidite iz zgornje formule, lahko dobite pogojne vrednosti.
Opombe:
- Funkcija SUMPRODUCT šteje neštevilčne vrednosti kot 0.
- Funkcija SUMPRODUCT upošteva logično vrednost TRUE kot 1 in False kot 0.
- Niz argumentov mora biti enake dolžine, sicer formula vrne napako.
Upam, da je ta članek o tem, kako vrniti SUM le vsak n -ti stolpec s formulami v Excelu, razložljiv. Več člankov o funkcijah SUMPRODUCT najdete tukaj. Prosimo, delite svoje vprašanje spodaj v polju za komentar. Mi vam bomo pomagali.
Kako uporabljati funkcijo SUMPRODUCT v Excelu: Vrne SUM po množenju vrednosti v več matrikah v Excelu.
SUM, če je datum med : Vrne SUM vrednosti med danimi datumi ali obdobjem v Excelu.
Vsota, če je datum večji od navedenega datuma: Vrne SUM vrednosti po danem datumu ali obdobju v Excelu.
2 načina seštevanja po mesecih v Excelu: Vrne SUM vrednosti v določenem mesecu v Excelu.
Kako sešteti več stolpcev s pogojem: Vrne SUM vrednosti v več stolpcih s pogojem v excelu
Priljubljeni članki
50 Bližnjica v Excelu za večjo produktivnost
Uredite spustni seznam
Absolutna referenca v Excelu
Če s pogojnim oblikovanjem
Če z nadomestnimi znaki
Vpogled po datumu
Pretvorite palce v stopala in palce v Excelu 2016
Pridružite se imenu in priimku v Excelu
Preštejte celice, ki ustrezajo bodisi A ali B