Recimo, da imate v Excelovi tabeli seznam izdelkov z njihovo ceno. Zdaj morate ustvariti svežnje, ki lahko vsebujejo različne izdelke iz razpoložljivih izdelkov. Zdaj bi na koncu želeli izračunati skupno ceno vsakega svežnja. Enkrat lahko to storite ročno, če pa je to vaša redna naloga, je bolje, da to nalogo avtomatizirate z nekaj elegantnimi formulami. In temu služi ta članek. Naučili se bomo, kako izračunati cene svežnjev ali skupin predmetov z eno formulo.
Splošna formula:
| = SUMPRODUCT (cenovni razpon,-(kontrolni obseg = "y")) |
cenovni razpon:To je paleta, ki vsebuje ceno izdelkov.
check_range: To je obseg, v katerega želimo postaviti svoj ček. Če je izdelek del svežnja, postavimo y v prerez svežnja in izdelka.
"y"= To je preverjanje za vključitev izdelka v sveženj.
Dajmo primer, da razčistimo koncept.
Primer: Ustvarite formulo za določanje cen paketov v Excelu.
Vzamemo isti scenarij, o katerem smo razpravljali na začetku. Pripravili smo tabelo v območju B2: F9. Opredelimo spremenljivke, ki jih potrebujemo.
Cenovni razpon:Cenovni razpon je C2: C9. Ker je fiksno, lahko imenujemo obseg ali uporabimo njegovo absolutno referenco. V tem primeru bom uporabil absolutno referenco $ C $ 2: $ C $ 9.
Območje preverjanja:To je obseg, ki vsebuje preverjanja (stolpec svežnja). So D3: D9, E3: E9 in F3: f9.
Postavimo te vrednosti v splošno formulo.
Za izračun cene svežnja zapišite to formulo v D10.
| = PODROČJE ($ C $ 3: $ C $ 9,-(D3: D9 = "y")) |
Pritisnite enter. Cena celice svežnja 1 je izračunana v celici D10. Kopirajte to formulo v sosednje celice, da izračunate ceno vseh svežnjev.
Kako deluje?
Formula deluje navznoter. Torej najprej -(D3: D9 = "y") je rešeno.
To vrne polje 1 in 0 kot. 1 za vsak y in 0 karkoli drugega v razponu D3: D9.
| {1;1;0;1;0;1;0} |
Nato se $ C $ 3: $ C $ 9 pretvori v matriko, ki vsebuje ceno vsakega predmeta/izdelka.
| {100;200;20;10;12;15;25} |
Zdaj ima funkcija SUMPRODUCT to v sebi.
| = PODROČJE ({100; 200; 20; 10; 12; 15; 25}, {1; 1; 0; 1; 0; 1; 0}) |
Tako kot funkcija SUMPRODUCT pomnoži vsako vrednost v enem nizu v isto indeksirano matriko v drugem nizu in na koncu povzame te vrednosti. To pomeni, da se vsaka cena, ki se ujema z 0 v drugem nizu, spremeni v 0. {100; 200; 0; 10; 0; 15; 0}. Zdaj se ta niz sešteje. Tako dobimo 325 za sveženj 1. Enako velja za vse svežnje.
Alternativna formula:
Alternativna formula je seveda funkcija SUMIF in SUMIFS.
| = SUMIF (D3: D9, "y", $ C $ 3: $ C $ 9) |
in
| = SUMIFS ($ C $ 3: $ C $ 9, D3: D9, "y") |
To so klasični odgovori, vendar je formula SUMPRODUCT hitrejša in domišljija.
Torej, fantje, na ta način lahko preprosto izračunate ceno svežnja v Excelu. Upam, da je bilo dovolj razlagalno. Če sem kaj zamudil ali če dvomite glede tega članka ali katerega koli drugega dvoma, povezanega z Excelom, ga vprašajte v spodnjem razdelku za komentarje.
Štetje skupnih ujemanj v dveh obsegih v Excelu | Naučite se šteti skupno ujemanje v dveh obsegih s funkcijo SUMPROUDCT.
SUMIFS z logiko AND-OR | SUMIFS se lahko uporablja tudi z logiko OR. Logična logika, ki jo uporablja SUMIFS, je logika AND.
PODROČJE z logiko IF | Naučite se uporabljati SUMPRODUCT z logiko IF brez uporabe funkcije IF v formuli.
50 bližnjic v Excelu za povečanje produktivnosti | Poskrbite, da bo vaša naloga hitrejša. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Funkcija VLOOKUP v Excelu | To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
COUNTIF v Excelu 2016 | Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določene vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu | To je še ena bistvena funkcija armaturne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.