Üks levinud küsimustest teadetetahvlil on see, kuidas kasutada funktsiooni SumIf kahe erineva tingimusega. Kahjuks on vastus, et SumIf ei saa hakkama kahe erineva tingimusega.
Kahe tingimuse tegemiseks peate kasutama üsna keerukat massiivivalemit. Tingimusliku summa nõustaja lisandmoodul võimaldab teil hõlpsalt neid keerulisi valemeid sisestada.
Siin on Exceli tööleht, kus on veerud toote, müügiesindaja ja müügi jaoks. Andmed on lahtrites A2: C29.
Kui soovite müügi kokku panna, töötab lihtne funktsioon SUM (). =SUM(C2:C29).
Paljud Excellersid avastavad funktsiooni SumIf. Selle funktsiooni abil on üsna lihtne välja selgitada toote ABC kogu müük.=SUMIF(A2:A29,E2,C2:C29)
Samuti on lihtne välja selgitada müügiesindaja Joe müügi kogusumma =SUMIF(B2:B29,E2,C2:C29).
Seejärel eeldate, et on võimalik välja selgitada Joe toote ABC kogumüük. Funktsiooniga SumIf pole seda aga kuidagi võimalik teha. Selgub, et peate kasutama üsna keerukat massiivi või CSE-valemit.
Olgem ausad - summa valem on Excel 101. SumIfi valem pole keerukuses kaugel taga. Kuid CSE-valem Joe tehtud ABC-müügi koguarvutuse arvutamiseks on piisav, et isegi mu pea ringi käiks.
Hea uudis - Microsoft pakub viisurit Tingimuslik summa, mis võimaldab isegi algajal sisestada keerukaid tingimusvalemeid 1, 2 või enama tingimuse põhjal. Tingimusliku summa viisard on lisandmoodul. Selle funktsiooni lisamiseks Excelisse minge menüüsse Tööriistad ja valige pistikprogrammid. Dialoogiboksis Pistikprogrammid märkige tingimusliku summa viisardi kõrval olev märkeruut ja valige OK. Võimalik, et vajate sel hetkel oma installi-CD-d, kuna Microsoft ei hõlma viisardit vaikepakendusse.
Kui lisandmoodul on edukalt sisse lülitatud, on menüü Tööriistad allservas valik Conditonal Sum….
Valige oma andmekogus üks lahter ja valige Tööriistad - Tingimuslik summa. Eeldades, et teie andmed on kenasti vormistatud ühe rida päistega, arvab Excel teie andmete vahemiku korralikult ära. Valige Järgmine.
2. etapis valige summeeritav veerg. Sellisel juhul arvas viisard juba, et soovite summeerida esimese (ja ainsa) numbriveeru - Müük. Dialoogiboksi keskel on kolm rippmenüüd. Need on esimese tingimuse puhul õiged - toode võrdub ABC-ga, seega valige nupp Lisa tingimus.
Siis saate lisada oma teise tingimuse. Sellisel juhul soovite täpsustada, et müügiesindaja on Joe. Valige esimese rippmenüü nool. Excel pakub saadaolevate veergude nimede tähestikulist loendit. Valige müügiesindaja.
Keskmine rippmenüü on õige, kuid siin on täielikkuse huvides näha, et oleksite võinud valida võrdse, väiksema, suurema, väiksema või võrdse, suurema või võrdse või mitte võrdse.
Valige kolmandast rippmenüüst Joe.
Valige nupp Lisa tingimus.
Nüüd olete valmis minema 3. sammu juurde. Vajutage nuppu Järgmine.
3. etapis on teil kaks valikut. Esimese valiku korral sisestab viisard valemisse ühe valemi, mille väärtused on kõvakoodiga "ABC" ja "Joe". See annab teile vastuse, kuid puudub võimalus valemit hõlpsalt muuta. Teise valiku korral seab Excel uue lahtri väärtusega "ABC" ja uue lahtri väärtusega "Joe". Kolmas lahter sisaldab valemit, mis teeb tingimusliku summa nende kahe väärtuse põhjal. Selle suvandi abil saate lahtritesse sisestada uued väärtused, et näha Adami poolt müüdud XYZ-i koguarvu.
Seejärel küsib viisard, kust soovite ABC väärtust. Valige lahter ja valige Järgmine. Korrake, kui viisard palub teil valida Joe jaoks lahtrit ja valemi.
Kui valite viimases etapis Lõpeta, loob Excel CSE-valemist veidi erineva (kuid kehtiva) versiooni.
Selle valemi järgi arvutas Joe 33 338 dollarit ABC-d.
Kui muudate toote sisendraku ABC-st DEF-iks, arvutatakse valem uuesti, näidates, et Joe müüs DEF-i 24 478 dollarit.
Tingimusliku summa viisard paneb keerukad valemid kõigile Exceli omanikele hästi käeulatuses.
Lisainformatsioon:Kui soovite koostada tabeli, mis näitab iga toote müüki iga müügiesindaja poolt, on olemas mõni spetsiaalne "hooldus ja söötmine", mida peate nende valemite kohta teadma. Sisestage kõik müügiesindajad vahemiku ülaossa. Sisestage iga toode vahemiku vasakusse veergu. Muutke viisardi pakutavat valemit. Alloleval pildil osutab valem lahtris E6 olevale korrutisele. See viide peab tõesti olema $ E6. Kui jätate viite E6-ks ja kopeerite valemi veergu G, vaataks valem E6 asemel F6 ja see oleks vale. Kui lisate dollarites E6 enne dollarimärki, veenduge, et valem vaataks alati veerus E olevat toodet. Valem osutab ka lahtris F5 müügiesindajale. See viide peab tõesti olema 5 dollarit. Kui jätsite viite F5-ks ja kopeerite alla 7. reale,F5 viide muutub F6-ks ja see pole õige. Dollarimärgi lisamine enne rea numbrit lukustab rea numbri ja viide osutab alati 5. reale.
Redigeerimisrežiimis (valige lahter ja muutmiseks vajutage klahvi F2) tippige enne E. klahvi $. Sisestage dollari märk enne tähte F5 tähte 5. Ärge vajutage veel sisestusklahvi!
See valem on spetsiaalne valemitüüp. Kui vajutate sisestusklahvi Enter, saate 0, mis pole õige.
Enter-i sisestamise asemel hoidke Enter-klahvi all hoides all klahve Ctrl ja Shift. See võlukombinatsioon C trl + S hift + E nter on see, miks ma neid CSE-valemeid nimetan.
Enne valemi kopeerimist ülejäänud tabelisse tuleb veel üks kaaluda. Teie soov võib olla F6 kopeerimine ja kleepimine F6: G8. Kui proovite seda, annab Excel teile mõistatusliku teate "Massiivi osa ei saa muuta". Excel kurdab, et te ei saa CSE-valemit kleepida vahemikku, mis sisaldab algset täiusliku täiuslikkuse valemit.
Selle ümber on lihtne ümber käia. Koopia F6. Kleebi F7-le: F8.
Eksemplar F6: F8. Kleebi G6-le: G8. Teil on CSE-valemite tabel, mis näitab kahel tingimusel põhinevaid kogusummasid.
