Una dintre întrebările obișnuite din forumul de mesaje este cum să utilizați funcția SumIf cu două condiții diferite. Din păcate, răspunsul este că SumIf nu poate suporta două condiții diferite.
Pentru a face două condiții, trebuie să utilizați o formulă de matrice destul de complicată. Programul de completare cu asistent sumă condițională vă permite să introduceți cu ușurință aceste formule complicate.
Iată o foaie de lucru Excel cu coloane pentru produs, reprezentant de vânzări și vânzări. Datele se află în celulele A2: C29.
Dacă doriți să totalizați vânzările, va funcționa o funcție simplă SUM (). =SUM(C2:C29).
Mulți Excellers descoperă funcția SumIf. Folosind această funcție, este destul de ușor să calculați totalul vânzărilor pentru produsul ABC.=SUMIF(A2:A29,E2,C2:C29)
De asemenea, este ușor să vă dați seama de totalul vânzărilor realizate de reprezentantul de vânzări Joe =SUMIF(B2:B29,E2,C2:C29).
Ați presupune că este posibil să vă dați seama de vânzările totale ale produsului ABC realizate de Joe. Cu toate acestea, nu există nicio modalitate de a face acest lucru cu funcția SumIf. Se pare că trebuie să utilizați o matrice destul de complexă sau o formulă CSE.
Să recunoaștem - formula Sum este Excel 101. Formula SumIf nu rămâne în urmă în complexitate. Cu toate acestea, formula CSE pentru a calcula totalul vânzărilor ABC realizate de Joe este suficientă pentru a-mi face chiar și capul să se rotească.
Vestea bună - Microsoft oferă Asistență sumă condițională care permite chiar și unui începător să introducă formule condiționale complexe bazate pe 1, 2 sau mai multe condiții. Expertul pentru suma condiționată este un program de completare. Pentru a adăuga această funcționalitate la Excel, accesați meniul Instrumente și selectați Suplimente. În caseta de dialog Add-Ins, bifați caseta de selectare de lângă Conditional Sum Wizard și alegeți OK. Este posibil să aveți nevoie de CD-ul de instalare în acest moment, deoarece Microsoft nu include expertul în instalarea implicită.
Odată ce programul de completare este activat, va exista o sumă Conditonală … alegere în partea de jos a meniului Instrumente.
Selectați o singură celulă din setul de date și alegeți Instrumente - Sumă condiționată. Presupunând că datele dvs. sunt frumos formatate cu un singur rând de titluri, Excel va ghici în mod corespunzător gama de date. Alegeți Următorul.
La pasul 2, selectați coloana de sumat. În acest caz, expertul a ghicit deja că doriți să însumați prima (și singura) coloană numerică - Vânzări. În mijlocul casetei de dialog sunt trei controale derulante. Acestea se întâmplă să fie corecte pentru prima condiție - Produsul este egal cu ABC, deci alegeți butonul Adăugare condiție.
Apoi, puteți adăuga a doua condiție. În acest caz, doriți să specificați că reprezentantul de vânzări este Joe. Alegeți săgeata pentru primul meniu derulant. Excel oferă o listă alfabetică a numelor de coloane disponibile. Alegeți Rep. Vânzări
Meniul derulant central este corect, dar pentru completare aici, puteți vedea că ați fi putut alege egal, mai mic decât, mai mare decât, mai mic decât sau egal, mai mare decât sau egal sau nu egal.
Din al treilea meniu derulant, selectați Joe.
Alegeți butonul Adăugare condiție.
Acum sunteți gata să treceți la Pasul 3. Apăsați butonul Următor.
La pasul 3, aveți două opțiuni. În prima alegere, vrăjitorul va introduce o singură formulă cu valorile „ABC” și „Joe” codificate în formulă. Vă va oferi răspunsul, dar nu va exista nicio ocazie de a schimba cu ușurință formula. Cu a doua alegere, Excel va configura o nouă celulă cu valoarea „ABC” și o nouă celulă cu valoarea „Joe”. O a treia celulă va conține formula care face o sumă condițională pe baza acestor două valori. Cu această opțiune, puteți introduce noi valori în celule pentru a vedea numărul total de XYZ vândute de Adam.
Vrăjitorul vă va întreba unde doriți valoarea pentru ABC. Selectați o celulă și alegeți Următorul. Repetați când vrăjitorul vă cere să selectați o celulă pentru Joe și formula.
Când alegeți Finalizare în ultimul pas, Excel va crea o versiune ușor diferită (dar validă) a formulei CSE.
Această formulă calculează că Joe a vândut 33 338 dolari ABC.
Dacă schimbați celula de intrare a produsului din ABC în DEF, formula se va recalcula pentru a arăta că Joe a vândut 24.478 USD de DEF.
Expertul pentru suma condiționată pune formule complexe la îndemâna tuturor proprietarilor Excel.
Informatii suplimentare:Dacă doriți să construiți un tabel care să afișeze vânzările fiecărui produs de către fiecare reprezentant de vânzări, există câteva „îngrijire și hrănire” speciale pe care va trebui să le cunoașteți despre aceste formule. Tastați fiecare reprezentant de vânzări din partea de sus a gamei. Tastați fiecare produs în coloana din stânga a gamei. Editați formula furnizată de expert. În imaginea de mai jos, formula indică un produs în celula E6. Această referință trebuie să fie cu adevărat de $ E6. Dacă lăsați referința ca E6 și copiați formula în coloana G, formula ar privi F6 în loc de E6 și acest lucru ar fi greșit. Adăugarea unui semn de dolar înainte de E în E6 se va asigura că formula se uită întotdeauna la produsul din coloana E. Formula indică, de asemenea, un reprezentant de vânzări în celula F5. Această referință trebuie să fie cu adevărat de 5 USD. Dacă ați lăsat referința ca F5 și copiați în rândul 7,referința F5 se va schimba în F6 și acest lucru nu este corect. Adăugarea unui semn de dolar înainte de numărul rândului va bloca numărul rândului și referința va indica întotdeauna rândul 5.
În modul Editare (selectați celula și apăsați F2 pentru a edita), tastați $ înainte de E. Tastați un semn de dolar înainte de 5 în F5. Nu apăsați încă pe Enter!
Această formulă este un tip special de formulă. Dacă apăsați Enter, veți obține un 0, care nu este corect.
În loc să tastați Enter, țineți apăsate tastele Ctrl și Shift în timp ce apăsați Enter. Această combinație magică de C trl + S hift + E nter este motivul pentru care numesc aceste formule CSE.
Există o ultimă considerație înainte de a copia formula pe restul tabelului. Înclinarea dvs. ar putea fi copierea F6 și lipirea în F6: G8. Dacă încercați acest lucru, Excel vă va oferi mesajul nedumeritor „Nu puteți schimba o parte dintr-o matrice”. Excel se plânge că nu puteți lipi o formulă CSE într-un interval care conține formula CSE originală.
Este ușor să rezolvați acest lucru. Copiați F6. Lipiți la F7: F8.
Copiați F6: F8. Lipiți la G6: G8. Veți avea un tabel cu formule CSE care arată totalurile pe baza a două condiții.
