Vienas iš dažniausių pranešimų lentos klausimų yra tai, kaip naudoti „SumIf“ funkciją dviem skirtingomis sąlygomis. Deja, atsakymas yra toks, kad „SumIf“ negali išspręsti dviejų skirtingų sąlygų.
Norėdami atlikti dvi sąlygas, turite naudoti gana sudėtingą masyvo formulę. Sąlyginės sumos vedlio papildinys leidžia lengvai įvesti šias sudėtingas formules.
Čia yra „Excel“ darbalapis su produkto, pardavimo atstovo ir pardavimo stulpeliais. Duomenys yra langeliuose A2: C29.
Jei norite susumuoti pardavimus, veiks paprasta SUM () funkcija. =SUM(C2:C29).
Daugelis „Excellers“ atranda „SumIf“ funkciją. Naudojant šią funkciją, gana lengva išsiaiškinti bendrą ABC produkto pardavimą.=SUMIF(A2:A29,E2,C2:C29)
Taip pat lengva išsiaiškinti bendrą pardavimų atstovo Joe pardavimo skaičių =SUMIF(B2:B29,E2,C2:C29).
Tada manytumėte, kad įmanoma išsiaiškinti bendrą Joe produkto ABC pardavimą. Tačiau jokiu būdu negalima to padaryti naudojant „SumIf“ funkciją. Pasirodo, kad reikia naudoti gana sudėtingą masyvo arba TPP formulę.
Pripažinkime, kad sumos formulė yra „Excel 101.“. SumIf formulė nėra toli už sudėtingumo. Tačiau norint užpildyti net mano galvą, pakanka CSE formulės, kad būtų galima apskaičiuoti bendrą Joe parduotą ABC pardavimą.
Geros naujienos - „Microsoft“ siūlo sąlyginės sumos vedlį, kuris net naujokui leidžia įvesti sudėtingas sąlygines formules, pagrįstas 1, 2 ar daugiau sąlygų. Sąlyginės sumos vedlys yra priedas. Norėdami pridėti šią funkciją prie „Excel“, eikite į meniu Įrankiai ir pasirinkite Priedai. Dialogo lange Priedai pažymėkite žymimąjį laukelį šalia Sąlyginės sumos vedlys ir pasirinkite Gerai. Gali būti, kad šiuo metu jums gali prireikti diegimo kompaktinio disko, nes „Microsoft“ neįtraukia vedlio į numatytąjį diegimą.
Kai papildinys bus sėkmingai įjungtas, meniu „Įrankiai“ apačioje bus pasirinkta „Conditonal Sum…“.
Pasirinkite vieną langelį savo duomenų rinkinyje ir pasirinkite Įrankiai - sąlyginė suma. Darant prielaidą, kad jūsų duomenys yra tinkamai suformatuoti vienoje antraščių eilutėje, „Excel“ tinkamai atspės jūsų duomenų diapazoną. Pasirinkite Kitas.
2 veiksme pasirinkite stulpelį, kurį norite susumuoti. Tokiu atveju vedlys jau spėjo, kad norite susumuoti pirmąjį (ir vienintelį) skaitinį stulpelį - Pardavimai. Dialogo lango viduryje yra trys išskleidžiamieji valdikliai. Tai būna teisinga pagal pirmąją sąlygą - produktas lygus ABC, todėl pasirinkite mygtuką Pridėti sąlygą.
Tada galite pridėti savo antrąją sąlygą. Tokiu atveju norite nurodyti, kad pardavimo atstovas yra Joe. Pasirinkite rodyklę pirmajam išskleidžiamajam meniu. „Excel“ siūlo abėcėlinį galimų stulpelių pavadinimų sąrašą. Pasirinkite pardavimo atstovą.
Centrinis išskleidžiamasis meniu yra teisingas, tačiau norint išsamumo, čia galite pamatyti, kad galėjote pasirinkti vienodą, mažesnį, didesnį, mažesnį arba lygų, didesnį arba lygų arba ne lygų.
Trečiajame išskleidžiamajame meniu pasirinkite Joe.
Pasirinkite mygtuką Pridėti sąlygą.
Dabar esate pasirengę pereiti prie 3 žingsnio. Paspauskite mygtuką Kitas.
3 žingsnyje turite du pasirinkimus. Pirmuoju pasirinkimu vedlys į formulę įves vieną formulę su vertėmis „ABC“ ir „Joe“. Tai suteiks jums atsakymą, tačiau nebus galimybės lengvai pakeisti formulę. Pasirinkus antrą pasirinkimą, „Excel“ sukurs naują langelį, kurio vertė yra „ABC“, ir naują langelį, kurio vertė bus „Joe“. Trečiame langelyje bus formulė, kuri sudarys sąlyginę sumą, pagrįstą tomis dviem reikšmėmis. Pasirinkę šią parinktį, langeliuose galite įvesti naujas reikšmes, kad pamatytumėte visą Adomo parduotą XYZ.
Tada vedlys paklaus, kur norite ABC reikšmės. Pasirinkite langelį ir pasirinkite Kitas. Pakartokite, kai vedlys paprašys pasirinkti langelį Joe ir formulę.
Kai paskutiniame etape pasirinksite Baigti, „Excel“ sukurs šiek tiek kitokią (bet galiojančią) TPV formulės versiją.
Pagal šią formulę apskaičiuojama, kad Džo pardavė 33 338 USD ABC.
Jei pakeisite produkto įvesties langelį iš ABC į DEF, formulė bus apskaičiuota iš naujo, kad parodytų, jog Joe pardavė 24 478 USD DEF.
Sąlyginės sumos vedlys suteikia sudėtingas formules visiems „Excel“ savininkams lengvai pasiekiamoje vietoje.
Papildoma informacija:Jei norite sudaryti lentelę, kurioje bus parodyti kiekvieno pardavimo pardavimai kiekvienam atstovui, yra keletas specialių „priežiūros ir maitinimo“ būdų, kuriuos turėsite žinoti apie šias formules. Įveskite kiekvieną prekybos atstovą diapazono viršuje. Įveskite kiekvieną produktą kairiajame diapazono stulpelyje. Redaguokite vedlio pateiktą formulę. Žemiau esančiame paveikslėlyje formulė nurodo produktą E6 langelyje. Ši nuoroda tikrai turi būti $ E6. Jei paliksite nuorodą kaip E6 ir nukopijuosite formulę į G stulpelį, formulė atrodys ne E6, o F6, ir tai bus neteisinga. Pridėjus dolerio ženklą prieš E E6, įsitikinsite, kad formulė visada žiūri į produktą E stulpelyje. Formulė taip pat rodo pardavimo atstovą langelyje F5. Ši nuoroda tikrai turi būti 5 USD. Jei paliksite nuorodą kaip F5 ir nukopijuosite į 7 eilutę,F5 nuoroda pasikeis į F6 ir tai nėra teisinga. Pridėjus dolerio ženklą prieš eilutės numerį, eilutės numeris bus užrakintas, o nuoroda visada rodys 5 eilutę.
Redagavimo režimu (pasirinkite langelį ir paspauskite F2, kad galėtumėte redaguoti) įveskite $ prieš E. Įveskite dolerio ženklą prieš 5 F5. Dar nespauskite „Enter“!
Ši formulė yra specialus formulės tipas. Jei paspausite Enter, gausite 0, kuris nėra teisingas.
Užuot vedę Enter, laikykite nuspaudę „Ctrl“ ir „Shift“ klavišus, kol paspausite Enter. Šis stebuklingas C trl + S hift + E nter derinys yra tai, kodėl aš vadinu šias TPP formules.
Prieš nukopijuojant formulę į likusią lentelės dalį, reikia atsižvelgti į paskutinį dalyką. Jūsų noras gali būti kopijuoti F6 ir įklijuoti į F6: G8. Jei bandysite tai, „Excel“ suteiks jums mįslingą pranešimą „Jūs negalite pakeisti masyvo dalies“. „Excel“ skundžiasi, kad negalima įklijuoti TPV formulės į diapazoną, kuriame yra originali TPP formulė.
Tai lengva išspręsti. F6 kopija. Įklijuoti į F7: F8.
F6 kopija: F8. Įklijuoti į G6: G8. Turėsite TPV formulių lentelę, kurioje bus rodomos sumos pagal dvi sąlygas.
