Povedzme, že máte v tabuľke programu Excel zoznam produktov s ich cenou. Teraz z dostupných položiek musíte vytvoriť balíky, ktoré môžu obsahovať rôzne produkty. Na konci by ste chceli vypočítať celkovú cenu každého balíka. Jedenkrát to môžete urobiť ručne, ale ak je to vaša pravidelná úloha, je lepšie túto úlohu zautomatizovať pomocou elegantných vzorcov. A práve na to slúži tento článok. Naučíme sa vypočítať ceny balíkov alebo skupín položiek pomocou jedného vzorca.
Všeobecný vzorec:
| = SUMPRODUCT (price_range,-(check_range = "y")) |
cenové rozpätie:Je to sortiment, ktorý obsahuje cenu produktov.
rozsah_kontroly: Je to rozsah, do ktorého chceme dať šek. Ak je výrobok súčasťou zväzku, vložíme y do prierezu zväzku a produktu.
"r"= Toto je kontrola, ktorú vkladáme do zahrnutia produktu do balíka.
Na objasnenie konceptu uveďme príklad.
Príklad: Vytvorte vzorec na stanovovanie cien balíkov v programe Excel.
Berieme rovnaký scenár, o ktorom sme hovorili na začiatku. Tabuľku sme pripravili v rozsahu B2: F9. Identifikujme premenné, ktoré potrebujeme.
Cenové rozpätie:Cenový rozsah je C2: C9. Pretože je pevný, môžeme buď pomenovať rozsah, alebo použiť jeho absolútnu referenciu. V tomto prípade použijem absolútnu referenciu $ C $ 2: $ C $ 9.
Rozsah kontroly:Toto je rozsah, ktorý obsahuje šeky (stĺpec zväzku). Sú to D3: D9, E3: E9 a F3: f9.
Vložme tieto hodnoty do generického vzorca.
Napíšte tento vzorec v D10 a vypočítajte cenu balíka.
| = SUMPRODUCT ($ C $ 3: $ C $ 9,-(D3: D9 = "y")) |
Stlačte Enter. Náklady na balík 1. balíka máte vypočítané v bunke D10. Skopírujte tento vzorec do susedných buniek a vypočítajte cenu všetkých balíkov.
Ako to funguje?
Vzorec funguje naruby. Tak najskôr -(D3: D9 = "y") je vyriešený.
Vráti pole 1 a 0 ako. 1 pre každé y a 0 čokoľvek iné v rozsahu D3: D9.
| {1;1;0;1;0;1;0} |
Ďalej je $ C $ 3: $ C $ 9 prevedených na pole, ktoré obsahuje cenu každej položky/produktu.
| {100;200;20;10;12;15;25} |
Teraz to má v sebe funkcia SUMPRODUCT.
| = SUMPRODUCT ({100; 200; 20; 10; 12; 15; 25}, {1; 1; 0; 1; 0; 1; 0}) |
Teraz, keď to robí funkcia SUMPRODUCT, znásobí každú hodnotu v jednom poli na rovnaké indexované pole v inom poli a nakoniec tieto hodnoty zhrnie. Znamená to, že každá cena, ktorá sa v inom poli zhoduje s 0, sa zmení na 0. {100; 200; 0; 10; 0; 15; 0}. Teraz je toto pole zhrnuté. To nám dáva 325 pre balík 1. To isté platí pre všetky zväzky.
Alternatívny vzorec:
Alternatívnym vzorcom je samozrejme funkcia SUMIF a SUMIFS.
| = SUMIF (D3: D9, "y", $ C $ 3: $ C $ 9) |
a
| = SUMIFS ($ C $ 3: $ C $ 9, D3: D9, „y“) |
Toto sú klasické odpovede, ale vzorec SUMPRODUCT je rýchlejší a efektnejší.
Takže áno, chlapci, takto môžete ľahko vypočítať cenu balíka v programe Excel. Dúfam, že to bolo dostatočne vysvetľujúce. Ak som zmeškal nejaký bod alebo máte akékoľvek pochybnosti týkajúce sa tohto článku alebo akékoľvek iné pochybnosti súvisiace s programom Excel, opýtajte sa ho v sekcii komentárov nižšie.
Spočítajte celkové zhody v dvoch rozsahoch v programe Excel | Naučte sa počítať celkové zhody v dvoch rozsahoch pomocou funkcie SUMPROUDCT.
SUMIFY pomocou logiky AND-OR | SUMIFS je možné použiť aj s logikou ALEBO. Absolútna logika, ktorú SUMIFS používa, je logika AND.
SUMPRODUCT s logikou IF | Naučte sa používať SUMPRODUCT s logikou IF bez použitia funkcie IF vo vzorci.
50 skratiek programu Excel na zvýšenie produktivity | Vykonajte svoju úlohu rýchlejšie. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Funkcia VLOOKUP v programe Excel | Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
COUNTIF v Exceli 2016 | Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnej hodnoty nepotrebujete filtrovať svoje údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel | Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.