Trikovi u Excelu 130. deo – Kako od više formula da dobijete jednu

PROBLEM KORISNIKA: Pre no što je Excel uveo formulu SUMIFS u verziji Excel 2007, morali ste da koristite funkciju SUMPRODUCT da rešite probleme koji se mogu rešavati pomoću funkcije SUMIFS.

REŠENJE: Funkcija SUMPRODUCT je sjajna kada zaista naučite kako se koristi.

    Recimo da hoćete da izračunate prodaju majica za model Green XL EasyXL. Na ovoj slici se koristi 37 formula za izračunavanje iznosa od 6800 $ (Slika 1).

    Funkcija SUMPRODUCT zamenjuje tih 37 formula jednom jedinom. Evo osnovne strukture: =SUMPRODUCT((Kriterijum1)*(Kriterijum2)*(Kriterijum3),(Brojevi)).

Pretpostavljam da je funkcija SUMPRODUCT dodata u Excel radi obavljanja matričnog množenja – da se jedan pravougaoni opseg pomnoži s drugim i da se ti proizvodi saberu.

    Pre pojavljivanja Excel-a 2007, majstori Excel-a su počeli da koriste SUMPRODUCT da obave SUMIFS pre no što je formula SUMIFS uopšte i izmišljena.

    U suštini, ovim naređujete Excel-u da pomnoži četiri niza vrednosti.

    Prvi niz je (A4:A12=”Green”). On daje niz TRUE/FALSE (TAČNO/NETAČNO) vrednosti prikazanih u opsegu ćelija F4:F12.

    Sledeći niz je (B4:B12=”XL”). Obratite pažnju da ove logičke testove uvek stavljate u zagrade da biste primorali Excel da prvo njih obradi. Taj niz rezultuje vrednostima prikazanim u opsegu ćelija G4:G12.

    Naredni niz je (C4:C12=”EasyXL”). On daje vrednosti u opsegu ćelija H4:H12.

    Poslednji niz su brojevi u opsegu ćelija D4:D12.

    Imajte na umu šta se događa kada pomnožite neki broj vrednošću TRUE ili FALSE. Broj puta TRUE daje taj broj. Broj puta FALSE daje nulu (Slika 2).

    Kada shvatite da je TRUE kao 1 a FALSE kao 0, možete videti da jedini iznosi koji stižu do kolone J jesu oni kod kojih su oba uslova TRUE (TAČNI).

JOŠ POJEDINOSTI: Izračunavanja u mreži ćelija obavljaju se malo drugačije nego izračunavanja pomoću funkcija. 10*TRUE*TRUE u mreži ćelija daje 10. Međutim, funkcija =SUMPRODUCT(10, TRUE, TRUE) neće funkcionisati. Program SUMPRODUCT odbija da odmah konvertuje vrednosti TRUE u broj 1. Ne znam da li je to neka programska greška ili se prosto radi o tome da je originalna namena funkcije SUMPRODUCT bila da množi matrice brojeva.

    Dakle, prema sintaksi iz Excel Help-a, mogli bismo da pomislimo da bi bila dobra sledeća formula:

=SUMPRODUCT(A4:A12=”Green”,B4:B12=”XL”,C4:C12=”EasyXL”,D4:D12).

    Ali nije. Postoje zaobilazna rešenja a više zaista pametnih ljudi ne slaže se koje je od njih najbolje. Svi se slažemo da morate da izvršite neke matematičke operacije na tim logičkim nizovima kako biste ih naterali da se iz TRUE/FALSE transformišu u 1/0. Više ljudi to radi pomoću postupka u dva koraka. Oni koriste jedan minus da promene TRUE u -1 a FALSE u 0. Potom moraju da upotrebe još jedan minus da -1 vrate u 1. Njihova formula bi mogla da izgleda ovako:

=SUMPRODUCT(–(A4:A12=”Green”),–(B4:B12=”XL”),–(C4:C12=”Easy XL”),4:D12).

    Ova formula ima negativne strane. Pomoću ovog metoda, Excel mora da dotakne svaku logičku formulu dva puta, po jednom za svaki minus. Osim toga, u jednom kratkom periodu, Excel 2007 je dozvoljavao da opcija AutoCorrect ispravi dva minusa u dugu crtu. To je odlično za Word ali nikako nije korisno u Excel formuli.

    Kada koristim funkciju SUMPRODUCT, ne koristim dvostruki minus. Umesto toga, ja množim sve logičke nizove zajedno. Ovo izračunavanje je kao i izračunavanje u mreži ćelija, pa automatski konvertuje TRUE/FALSE u 1/0:

=SUMPRODUCT((A4:A12=”Green”)*(B4:B12=”XL”)*(C4:C12=”EasyXL”)*(D4:D12)).

    Protivnici ovog metoda kažu da Excel Help ukazuje da nizove treba razdvojiti zapetama a ne zagradama. Kažu da primenom ovog metoda sami radite sav posao u Excel-u i da ne dozvoljavate funkciji SUMPRODUCT da obavlja ikakvo množenje. Ako se da Excel-u da obavi taj posao, jedino što preostaje da se uradi jeste da se saberu (funkcija SUM) rezultati množenja koje je obavio Excel. Ne prihvatam taj argument. Činjenica je da nešto mora da obavi to množenje, a meni je svejedno da li je to Excel-ov program za računanje ili funkcija SUMPRODUCT. Ako u tom slučaju funkciju SUMPRODUCT svodim na funkciju SUM, onda neka tako i bude.

    Ako zaista želite da pustite SUMPRODUCT da obavi neka množenja, onda upotrebite hibrid ova dva pristupa (Slika 3):

=SUMPRODUCT((A4:A12=”Green”)*(B4:B12=”XL”)*(C4:C12=”EasyXL”),D4:D12).

PAŽNJA: Mada je funkcija SUMPRODUCT moćna, ugrađena funkcija SUMIFS ponekad će računati 1000 puta brže od funkcije SUMPRODUCT. Ako svi koji koriste vašu radnu tabelu upotrebljavaju Excel 2007 ili noviji, onda koristite funkciju SUMIFS.

PAŽNJA: Zaključno s Excel-om 2007 postojao je jedan čudan dodatni program pod nazivom Conditional Sum Wizard. Taj program ne bi koristio SUMPRODUCT niti SUMIFS. On bi napravio formulu kao što je sledeća:

{=SUM(IF(A4:A12=”Green”,IF(B4:B12=”XL”,IF(C4:C12=”EasyXL”,D4:D12,0),0),0))}.

Svaki put kada vidite velike zagrade oko formule, to je supertajna vrsta formule koju Microsoft naziva “formula niza”. Ako pokušate da uređujete tu formulu, morate da je završite tako što ćete držati Ctrl+Shift i istovremeno pritisnuti Enter.