Recimo da u Excel tablici imate popis proizvoda s njihovom cijenom. Sada morate stvoriti pakete koji mogu sadržavati različite proizvode od dostupnih stavki. Sada, na kraju, želite izračunati ukupnu cijenu svakog paketa. Jedno vrijeme to možete učiniti ručno, ali ako je to vaš redovan zadatak, bolje je automatizirati ovaj zadatak nekim elegantnim formulama. I ovome služi ovaj članak. Naučit ćemo kako izračunati cijene paketa ili grupa artikala koristeći jednu formulu.
Opća formula:
| = SUMPRODUCT (raspon_cijena,-(kontrolni raspon = "y")) |
Raspon cijena:To je asortiman koji sadrži cijenu proizvoda.
raspon_ provjere: To je raspon u koji želimo staviti ček. Ako je proizvod dio snopa, stavljamo y u presjek snopa i proizvoda.
"y"= Ovo je provjera koju stavljamo na uključivanje proizvoda u paket.
Uzmimo primjer kako bismo raščistili koncept.
Primjer: Izradite formulu za određivanje cijena paketa u Excelu.
Uzimamo isti scenarij o kojem smo govorili na početku. Pripremili smo tablicu u rasponu B2: F9. Identificirajmo varijable koje su nam potrebne.
Raspon cijena:Raspon cijena je C2: C9. Budući da je fiksan, možemo nazvati raspon ili upotrijebiti njegovu apsolutnu referencu. U ovom primjeru koristit ću apsolutnu referencu $ C $ 2: $ C $ 9.
Raspon provjere:Ovo je raspon koji sadrži provjere (stupac snopa). Oni su D3: D9, E3: E9 i F3: f9.
Stavimo ove vrijednosti u opću formulu.
Napišite ovu formulu u D10 da biste izračunali cijenu paketa.
| = SUMPRODUCT ($ C $ 3: $ C $ 9,-(D3: D9 = "y")) |
Pritisnite enter. Cijena paketa 1 snopa izračunata je u ćeliji D10. Kopirajte ovu formulu u susjedne ćelije da biste izračunali cijenu svih snopova.
Kako radi?
Formula djeluje iznutra. Dakle prvo -(D3: D9 = "y") je riješeno.
Ovo vraća niz od 1 i 0 kao. 1 za svaki y i 0 bilo što drugo u rasponu D3: D9.
| {1;1;0;1;0;1;0} |
Zatim se $ C $ 3: $ C $ 9 pretvara u niz koji sadrži cijenu svake stavke/proizvoda.
| {100;200;20;10;12;15;25} |
Sada funkcija SUMPRODUCT ima ovo u sebi.
| = PODIZVOD ({100; 200; 20; 10; 12; 15; 25}, {1; 1; 0; 1; 0; 1; 0}) |
Sada, kao što radi funkcija SUMPRODUCT, ona množi svaku vrijednost u jednom nizu na isti indeksirani niz u drugom nizu i na kraju sažima te vrijednosti. To znači da se svaka cijena koja odgovara 0 u drugom nizu pretvara u 0. {100; 200; 0; 10; 0; 15; 0}. Sada se ovaj niz zbraja. To nam daje 325 za paket 1. Isto se radi za sve pakete.
Alternativna formula:
Alternativna formula je, naravno, funkcija SUMIF i SUMIFS.
| = SUMIF (D3: D9, "y", $ C $ 3: $ C $ 9) |
i
| = SUMIFS ($ C $ 3: $ C $ 9, D3: D9, "y") |
Ovo su klasični odgovori, ali i formula SUMPRODUCT je brža i fensi.
Pa da, dečki, ovako možete lako izračunati cijenu paketa u Excelu. Nadam se da je bilo dovoljno objašnjenje. Ako sam propustio bilo što ili imate bilo kakvih nedoumica u vezi ovog članka ili bilo koje druge sumnje vezane uz Excel, pitajte to u odjeljku komentara u nastavku.
Broji ukupna podudaranja u dva raspona u Excelu | Naučite brojati ukupna podudaranja u dva raspona pomoću funkcije SUMPROUDCT.
ZBIRA pomoću logike AND-OR | SUMIFS se može koristiti i s logikom ILI. Logička logika koju SUMIFS koristi je logika AND.
POVEZIVANJE s IF logikom | Naučite kako koristiti SUMPRODUCT s IF logikom bez upotrebe IF funkcije u formuli.
50 Excel prečaca za povećanje vaše produktivnosti | Budite brži u izvršavanju svojih zadataka. Ovih 50 prečaca učinit će da radite još brže na Excelu.
Funkcija VLOOKUP u Excelu | Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova.
COUNTIF u Excelu 2016 | Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste računali određenu vrijednost. Funkcija Countif bitna je za pripremu vaše nadzorne ploče.
Kako koristiti funkciju SUMIF u Excelu | Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti u posebnim uvjetima.