Az Üzenőtáblán az egyik gyakori kérdés az, hogy miként lehet használni a SumIf függvényt két különböző feltétellel. Sajnos a válasz az, hogy a SumIf nem képes két különböző feltételt kezelni.
Két feltétel teljesítéséhez meglehetősen bonyolult tömbképletet kell használnia. A feltételes összegű varázsló bővítmény segítségével könnyedén megadhatja ezeket a bonyolult képleteket.
Itt található egy Excel munkalap oszlopokkal a termékről, az értékesítési képviselőről és az értékesítésről. Az adatok az A2: C29 cellákban találhatók.
Ha összesíteni szeretné az eladásokat, akkor egy egyszerű SUM () függvény fog működni. =SUM(C2:C29).
Sok kiváló felfedezi a SumIf függvényt. Ezzel a funkcióval meglehetősen könnyű kitalálni az ABC termék összes eladását.=SUMIF(A2:A29,E2,C2:C29)
Azt is könnyű kitalálni, hogy Joe értékesítési képviselő teljes eladást hajt végre =SUMIF(B2:B29,E2,C2:C29).
Ezután feltételezheti, hogy meg lehet állapítani a Joe által készített ABC termék teljes eladását. Erre azonban nincs mód a SumIf függvénnyel. Kiderült, hogy meglehetősen összetett tömböt vagy CSE-képletet kell használnia.
Nézzünk szembe a tényekkel - a Sum képlet az Excel 101. A SumIf képlet összetettségében nem sokkal marad el. Azonban a CSE-képlet Joe teljes ABC-eladásának kiszámításához elég ahhoz, hogy még a fejem is megpördüljön.
Jó hír - a Microsoft felajánlja a Feltételes összeg varázslót, amely lehetővé teszi, hogy egy kezdő is összetett feltételes képleteket adjon meg 1, 2 vagy több feltétel alapján. A Feltételes összeg varázsló egy kiegészítő. A funkció hozzáadásához az Excelbe lépjen az Eszközök menübe, és válassza a Bővítmények lehetőséget. A Bővítmények párbeszédpanelen jelölje be a Feltételes összeg varázsló melletti jelölőnégyzetet, és válassza az OK lehetőséget. Lehetséges, hogy ezen a ponton szüksége lehet a telepítő CD-re, mert a Microsoft nem tartalmazza a varázslót az alapértelmezett telepítésben.
Miután a kiegészítő sikeresen be van kapcsolva, az Eszközök menü alján lesz egy Conditonal Sum… választás.
Válasszon ki egy cellát az adatkészletből, és válassza az Eszközök - Feltételes összeg lehetőséget. Feltéve, hogy az adatai szépen vannak formázva egyetlen címsorral, az Excel megfelelően kitalálja az adatok tartományát. Válassza a Tovább lehetőséget.
A 2. lépésben válassza ki az oszlopot az összegzéshez. Ebben az esetben a varázsló már sejtette, hogy összegezni szeretné az első (és egyetlen) numerikus oszlopot - Értékesítés. A párbeszédpanel közepén három legördülő menü található. Ezek véletlenül helyesek az első feltételhez - A termék megegyezik az ABC-vel, ezért kattintson a Feltétel hozzáadása gombra.
Ezután hozzáadhatja második feltételét. Ebben az esetben meg szeretné adni, hogy az értékesítési képviselő Joe. Válassza ki az első legördülő menü nyílját. Az Excel felajánlja a rendelkezésre álló oszlopnevek betűrendes listáját. Válassza az értékesítési képviselőt.
A középső legördülő menü megfelelő, de a teljesség kedvéért láthatja, hogy választhatott volna egyenlő, kisebb, nagyobb, kisebb vagy egyenlő, nagyobb vagy egyenlő vagy nem egyenlő.
A harmadik legördülő menüből válassza ki Joe lehetőséget.
Kattintson a Feltétel hozzáadása gombra.
Most már készen áll a 3. lépés folytatására. Nyomja meg a Tovább gombot.
A 3. lépésben két lehetősége van. Az első választásnál a varázsló egyetlen képletet ír be, az "ABC" és a "Joe" értékekkel kemény kódolással a képletbe. Megadja a választ, de nem lesz lehetőség a képlet egyszerű megváltoztatására. A második választással az Excel új cellát állít be "ABC" értékkel és egy új cellát "Joe" értékkel. Egy harmadik cella tartalmazza azt a képletet, amely feltételes összeget ad e két érték alapján. Ezzel az opcióval új értékeket írhat be a cellákba annak érdekében, hogy megtekinthesse Adam által értékesített XYZ összértékét.
A varázsló ekkor megkérdezi, hogy hol szeretné az ABC értékét. Válasszon egy cellát, majd válassza a Tovább lehetőséget. Ismételje meg, amikor a Varázsló arra kéri, hogy válasszon ki egy cellát Joe-hoz és a képletet.
Amikor az utolsó lépésben a Befejezés lehetőséget választja, az Excel létrehoz egy kicsit más (de érvényes) verziót a CSE-képletből.
Ez a képlet kiszámítja, hogy Joe 33 338 dollárt adott el ABC-ből.
Ha a termék beviteli celláját ABC-ről DEF-re változtatja, a képlet újból kiszámítja, hogy megmutassa, hogy Joe 24 478 dollárt adott el DEF-ból.
A Feltételes összeg varázsló az összetett képleteket könnyen elérhetővé teszi az Excel összes tulajdonosának.
További információ:Ha olyan táblázatot szeretne készíteni, amely bemutatja az egyes termékek értékesítését az egyes értékesítési képviselőknél, van néhány speciális "gondozás és táplálás", amelyet tudnia kell ezekről a képletekről. Írja be az egyes értékesítési képviselőket a tartomány tetejére. Írja be az egyes termékeket a tartomány bal oszlopába. Szerkessze a varázsló által megadott képletet. Az alábbi képen a képlet egy terméket mutat az E6 cellában. Ennek a referenciának valóban $ E6-nak kell lennie. Ha a hivatkozást E6-nak hagyja, és a képletet átmásolja a G oszlopba, a képlet az E6 helyett az F6-ot nézi, és ez helytelen lenne. Ha egy dollárjelet adunk az E elé az E6-ban, akkor meggyőződhetünk arról, hogy a képlet mindig az E. oszlopban szereplő terméket nézi. Ennek a referenciának valóban 5 dollárnak kell lennie. Ha a hivatkozást F5-ösként hagyta, és lemásolta a 7. sorba,az F5 hivatkozás F6-ra változik, és ez nem helyes. Ha dollárjelet ad a sorszám elé, akkor a sorszám zárolódik, és a hivatkozás mindig az 5. sorra mutat.
Szerkesztés módban (válassza ki a cellát, és a szerkesztéshez nyomja meg az F2 billentyűt) írja be a $ értéket az E. elé. Írjon egy dollárjelet az F5-be az 5 elé. Még ne nyomja meg az Enter billentyűt!
Ez a képlet egy speciális képlettípus. Ha megnyomja az Enter billentyűt, 0-t kap, ami nem helyes.
Az Enter beírása helyett tartsa lenyomva a Ctrl és a Shift billentyűt, miközben megnyomja az Enter billentyűt. A C trl + S hift + E nter ezen varázslatos kombinációja miatt hívom ezeket a CSE-képleteket.
A képlet másolása a táblázat többi részébe még egy utolsó szempont. Előfordulhat, hogy másolja az F6-ot, és illessze be az F6: G8-ba. Ha megpróbálja ezt, az Excel megkapja a rejtélyes "Nem módosíthatja a tömb egy részét" üzenetet. Az Excel arra panaszkodik, hogy nem illeszthet be egyéni keresőmotor-képletet abba a tartományba, amely az eredeti egyéni keresőmotor-képletet tartalmazza.
Ezt könnyű megkerülni. F6 másolat. Illessze be az F7-be: F8.
F6: F8 másolat. Illessze be a G6-ba: G8. Lesz egy táblája CSE-képletekről, amelyek két feltétel alapján mutatják az összesítést.
