Jednou z častých otázok v diskusnej skupine je, ako používať funkciu SumIf pri dvoch rôznych podmienkach. Odpoveď, bohužiaľ, je, že SumIf nedokáže zvládnuť dve rôzne podmienky.
Ak chcete splniť dve podmienky, musíte použiť pomerne zložitý vzorec poľa. Doplnok Sprievodca podmienenou sumou vám umožňuje ľahko zadávať tieto zložité vzorce.
Tu je hárok programu Excel so stĺpcami pre produkt, obchodný zástupca a predaj. Údaje sú v bunkách A2: C29.
Ak chcete spočítať tržby, bude fungovať jednoduchá funkcia SUM (). =SUM(C2:C29).
Mnoho vynikajúcich objavuje funkciu SumIf. Pomocou tejto funkcie je pomerne ľahké zistiť celkový predaj produktu ABC.=SUMIF(A2:A29,E2,C2:C29)
Je tiež ľahké zistiť celkový predaj, ktorý uskutočnil obchodný zástupca Joe =SUMIF(B2:B29,E2,C2:C29).
Potom by ste predpokladali, že je možné zistiť celkový predaj produktu ABC uskutočneného Joeom. S funkciou SumIf to však nijako nejde. Ukazuje sa, že musíte použiť pomerne zložité pole alebo vzorec CSE.
Zmierte sa s tým - vzorec Sum je Excel 101. Vzorec SumIf v zložitosti nezaostáva. Vzorec CSE na výpočet celkového predaja ABC uskutočneného Joeom však stačí na to, aby sa mi zatočila hlava.
Dobrá správa - Microsoft ponúka Sprievodcu podmieneným súčtom, ktorý umožňuje nováčikovi zadávať zložité podmienené vzorce založené na 1, 2 alebo viacerých podmienkach. Sprievodca podmienenou sumou je doplnok. Ak chcete túto funkciu pridať do programu Excel, prejdite do ponuky Nástroje a vyberte položku Doplnky. V dialógovom okne Doplnky začiarknite políčko vedľa položky Sprievodca podmienenou sumou a kliknite na tlačidlo OK. Je možné, že v tomto okamihu budete potrebovať inštalačné CD, pretože spoločnosť Microsoft nezahŕňa sprievodcu do predvolenej inštalácie.
Po úspešnom zapnutí doplnku bude v dolnej časti ponuky Nástroje voľba Podmienená suma …
Vyberte jednu bunku vo svojej množine údajov a vyberte Nástroje - Podmienečný súčet. Za predpokladu, že sú vaše údaje pekne naformátované jedným riadkom nadpisov, Excel správne odhadne rozsah vašich údajov. Vyberte Ďalej.
V kroku 2 vyberte stĺpec, ktorý chcete sčítať. V tomto prípade už sprievodca uhádol, že chcete sčítať prvý (a jediný) číselný stĺpec - Predaj. V strede dialógového okna sú tri ovládacie prvky rozbaľovacej ponuky. Stávajú sa správne pre prvú podmienku - Produkt sa rovná ABC, takže zvoľte tlačidlo Pridať podmienku.
Potom môžete pridať svoju druhú podmienku. V takom prípade chcete určiť, že obchodným zástupcom je Joe. Vyberte šípku pre prvú rozbaľovaciu ponuku. Excel ponúka abecedný zoznam dostupných názvov stĺpcov. Vyberte obchodného zástupcu
Stredová rozbaľovacia ponuka je správna, ale pre úplnosť tu vidíte, že ste si mohli zvoliť rovné, menšie ako, väčšie ako, menšie ako alebo rovnaké, väčšie ako alebo rovnaké alebo nie rovnaké.
V tretej rozbaľovacej ponuke vyberte položku Joe.
Vyberte tlačidlo Pridať podmienku.
Teraz ste pripravení prejsť na krok 3. Stlačte tlačidlo Ďalej.
V kroku 3 máte dve možnosti. Pri prvej voľbe Sprievodca zadá jeden vzorec s hodnotami „ABC“ a „Joe“ pevne zakódovanými do vzorca. Dá vám odpoveď, ale nebude možné ľahko zmeniť vzorec. Pri druhej možnosti vytvorí Excel novú bunku s hodnotou „ABC“ a novú bunku s hodnotou „Joe“. Tretia bunka bude obsahovať vzorec, ktorý vytvorí podmienený súčet na základe týchto dvoch hodnôt. Pomocou tejto možnosti môžete do buniek vpísať nové hodnoty, aby ste videli celkový počet XYZ predaných Adamom.
Sprievodca sa potom opýta, kde chcete hodnotu pre ABC. Vyberte bunku a zvoľte Ďalej. Opakujte, keď vás Sprievodca žiada, aby ste vybrali bunku pre Joea a vzorec.
Keď v poslednom kroku vyberiete možnosť Dokončiť, Excel vytvorí mierne odlišnú (ale platnú) verziu vzorca VVN.
Tento vzorec počíta, že Joe predal spoločnosť ABC vo výške 33 338 dolárov.
Ak zmeníte vstupnú bunku produktu z ABC na DEF, vzorec sa prepočíta a ukáže sa, že Joe predal DEF vo výške 24 478 dolárov.
Sprievodca podmieneným súčtom dáva zložité vzorce do dosahu všetkých vlastníkov programu Excel.
Ďalšie informácie:Ak chcete zostaviť tabuľku, ktorá bude uvádzať predaj každého produktu každým obchodným zástupcom, je o týchto vzorcoch potrebné poznať špeciálnu „starostlivosť a kŕmenie“. Zadajte každého obchodného zástupcu do hornej časti rozsahu. Zadajte každý produkt do ľavého stĺpca rozsahu. Upravte vzorec poskytnutý sprievodcom. Na obrázku nižšie vzorec ukazuje na produkt v bunke E6. Táto referencia musí skutočne byť $ 6. Ak necháte odkaz ako E6 a skopírujete vzorec do stĺpca G, vzorec by sa pozeral na F6 namiesto E6 a bolo by to nesprávne. Pridanie znaku dolára pred znak E v E6 zabezpečí, že vzorec bude vždy pozerať na produkt v stĺpci E. Vzorec tiež ukazuje na obchodného zástupcu v bunke F5. Táto referencia musí skutočne byť 5 USD. Ak ste nechali odkaz ako F5 a skopírovali ste sa do riadku 7,referencia F5 sa zmení na F6 a to nie je správne. Pridaním znaku dolára pred číslo riadku dôjde k uzamknutiu čísla riadku a referencia bude vždy smerovať na riadok 5.
V režime úprav (vyberte bunku a upravte ju stlačením F2) zadajte $ pred E. Zadajte znak dolára pred 5 v F5. Zatiaľ netlačte na Enter!
Tento vzorec je špeciálny typ vzorca. Ak stlačíte Enter, dostanete 0, čo nie je správne.
Namiesto písania klávesu Enter podržte stlačené klávesy Ctrl a Shift a súčasne stlačte kláves Enter. Táto magická kombinácia C TRL + S hift + E nter je dôvod, prečo hovorím tieto CSE vzorcov.
Pred kopírovaním vzorca do zvyšku tabuľky je ešte posledná úvaha. Váš sklon by mohol byť kopírovanie F6 a vkladanie do F6: G8. Ak to vyskúšate, program Excel vám zobrazí záhadnú správu „Nie je možné zmeniť časť poľa“. Excel sa sťažuje, že nemôžete vložiť vzorec VVN do rozsahu, ktorý obsahuje pôvodný vzorec VVN.
Je ľahké to obísť. Kópia F6. Prilepte do F7: F8.
Kópia F6: F8. Prilepte do G6: G8. Budete mať tabuľku vzorcov VVN, ktorá zobrazuje súčty založené na dvoch podmienkach.
