Atsisiųskite darbaknygės pavyzdį
Ši pamoka parodo, kaip naudotis „Excel“ SUMPRODUCT funkcija „Excel“.
SUMPRODUCT funkcijų apžvalga
Funkcija SUMPRODUCT Padaugina skaičių masyvus ir sumuoja gautą masyvą.
Norėdami naudoti „SUMPRODUCT Excel“ darbalapio funkciją, pasirinkite langelį ir įveskite:
(Atkreipkite dėmesį, kaip atrodo formulės įvestys)
SUMPRODUCT funkcija Sintaksė ir įvestys:
| 1 | = SUMPRODUCT (masyvas1, masyvas2, masyvas3) |
masyvas1 - Skaičių masyvai.
Kas yra SUMPRODUCT funkcija?
Funkcija SUMPRODUCT yra viena iš galingiausių „Excel“ funkcijų. Jo pavadinimas gali paskatinti jus manyti, kad jis skirtas tik pagrindiniams matematiniams skaičiavimams, tačiau jis gali būti naudojamas daug daugiau.
Masyvai
SUMPRODUCT reikalauja masyvų įvesties.
Taigi, pirmiausia, ką turime omenyje sakydami „masyvas“? Masyvas yra paprasta elementų grupė (pvz., Skaičiai), išdėstyta tam tikra tvarka, kaip ir ląstelių diapazonas. Taigi, jei langeliuose A1: A3 būtų skaičiai 1, 2, 3, „Excel“ tai skaitytų kaip masyvą {1,2,3}. Tiesą sakant, galite įvesti {1,2,3} tiesiai į „Excel“ formules ir jis atpažins masyvą.
Toliau kalbėsime apie masyvus, bet pirmiausia pažvelkime į paprastą pavyzdį.
Bazinė matematika
Pažvelkime į pagrindinį SUMPRODUCT pavyzdį, naudojant jį bendram pardavimui apskaičiuoti.
Turime savo produktų lentelę ir norime apskaičiuoti bendrą pardavimą. Jums kyla pagunda tiesiog pridėti naują stulpelį, paimti parduotą kiekį * kainą ir tada apibendrinti naują stulpelį. Tačiau vietoj to galite tiesiog naudoti funkciją SUMPRODUCT. Eikime per formulę:
| 1 | = SUMPRODUCT (A2: A4, B2: B4) |
Funkcija įkelia skaičių diapazonus į masyvus, daugina juos vienas prieš kitą ir tada sumuoja rezultatus:
| 1234 | = SUMPRODUCT ({100, 50, 10}, {6, 7, 5})= SUMPRODUCT ({100 * 6, 50 * 7, 10 * 5})= SUMPRODUCT ({600, 350, 50}= 1000 |
„SUMPRODUCT Funciton“ sugebėjo padauginti visus skaičius ir sudaryti sumas.
Svertinis vidurkis
Kitas atvejis, kai naudinga naudoti SUMPRODUCT, yra tada, kai reikia apskaičiuoti svertinį vidurkį. Dažniausiai tai atsitinka atliekant mokyklos darbus, todėl panagrinėkime šią lentelę.
Mes galime pamatyti, kiek viktorinos, testai ir namų darbai yra verti bendrojo pažymio, taip pat koks yra dabartinis kiekvieno konkretaus elemento vidurkis. Tada mes galime apskaičiuoti bendrą pažymį rašydami
| 1 | = SUMPRODUCT (B2: B4, C2: C4) |
Mūsų funkcija dar kartą padaugina kiekvieną masyvo elementą prieš sumuojant sumą. Tai veikia taip
| 123 | = SUMPRODUCT ({30%, 50%, 20%}, {73%, 90%, 95%})= SUMPRODUCT ({22%, 45%, 19%})= 86% |
Keli stulpeliai
Kita vieta, kur galime naudoti SUMPRODUCT, yra dar daugiau stulpelių, kuriuos reikia padauginti vienas nuo kito. Pažvelkime į pavyzdį, kai turime apskaičiuoti tūrį medienos gabalėliais.
Užuot sukūrę pagalbinį stulpelį, kad apskaičiuotume bendrą kiekvienos eilutės pardavimą, galime tai padaryti naudodami vieną formulę. Mūsų formulė bus
| 1 | = SUMPRODUCT (B2: B5, C2: C5, D2: D5) |
Pirmieji kiekvieno masyvo elementai daugės vienas prieš kitą (pvz., 4 * 2 * 1 = 8). Tada antrasis (4 * 2 * 2 = 16) ir 3rdir tt Apskritai tai sukuria daugybę produktų, kurie atrodo kaip {8, 16, 16, 32). Tada bendras tūris būtų to masyvo suma, 72.
Vienas kriterijus
Gerai, pridėkime dar vieną sudėtingumo sluoksnį. Mes matėme, kad „SUMPRODUCT“ gali tvarkyti skaičių masyvus, bet ką daryti, jei norime patikrinti kriterijus? Taip pat galite sukurti logines reikšmių masyvus (loginės vertės yra reikšmės, kurios yra TRUE arba FALSE).
Pavyzdžiui, paimkite pagrindinį masyvą {1, 2, 3}. Sukurkime atitinkamą masyvą, kuris nurodytų, ar kiekvienas skaičius yra didesnis nei 1. Šis masyvas atrodytų kaip {FALSE, TRUE, TRUE}.
Tai labai naudinga formulėse, nes mes galime lengvai konvertuoti TRUE / FALSE į 1 / 0. Pažvelkime į pavyzdį.
Naudodami toliau pateiktą lentelę norime apskaičiuoti „Kiek parduotų vienetų buvo raudona?“
Mes galime tai padaryti pagal šią formulę:
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "raudona")) |
"Palauk! Kas ten su dvigubo minuso simboliu? " tu sakai. Prisimeni, kaip sakiau, kad galėtume konvertuoti iš „True/False“ į 1/0? Tai darome priversdami kompiuterį atlikti matematinę operaciją. Šiuo atveju mes sakome: „paimkite neigiamą vertę, tada vėl paimkite neigiamą“. Tai išrašius, mūsų masyvas pasikeis taip:
| 123 | {Tiesa, tiesa, melas}{-1, -1, 0}{1, 1, 0} |
Taigi, grįžtant prie visos SUMPRODUCT formulės, ji bus įkelta į mūsų masyvus ir tada padaugės, kaip tai
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0})= SUMPRODUCT ({100, 50, 0})= 150 |
Atkreipkite dėmesį, kaip 3rd elementas tapo 0, nes viskas, padauginta iš 0, tampa nulis.
Keli kriterijai
Į savo funkciją galime įkelti iki 255 masyvų, todėl tikrai galime įkelti daugiau kriterijų. Pažvelkime į šią didesnę lentelę, kurioje pridėjome parduotą mėnesį.
Jei norime sužinoti, kiek parduotų prekių buvo raudona ir buvo vasario mėnesį, galėtume parašyti savo formulę taip
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "raudona"), -(C2: C4 = "vasaris")) |
Tada kompiuteris įvertins mūsų masyvus ir dauginsis. Mes jau aptarėme, kaip „True/False“ masyvai keičiami į 1/0, todėl kol kas šį žingsnį praleisiu.
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0}, {0, 1, 1})= SUMPRODUCT ({0, 50, 0})= 50 |
Mūsų pavyzdyje buvo tik viena eilutė, kuri atitiko visus kriterijus, tačiau turėdami realius duomenis galbūt turėjote sudėti kelias eilutes, kurias reikėjo sudėti.
Sudėtingi kriterijai
Gerai, iki šiol jūs nebūsite sužavėti, nes visi mūsų pavyzdžiai galėjo būti padaryti naudojant kitas funkcijas, pvz., SUMIF arba COUNTIF. Dabar mes padarysime kažką tų kitų funkcijų negali daryti. Anksčiau mūsų mėnesio stulpelyje buvo nurodyti tikrieji mėnesių pavadinimai. O jei vietoj to būtų datos?
Dabar negalime atlikti SUMIF, nes SUMIF negali susidoroti su mums reikalingais kriterijais. Tačiau SUMPRODUCT gali mums padėti manipuliuoti masyvu ir atlikti gilesnį testą. Mes jau manipuliavome masyvais, kai išvertėme tiesą/klaidą į 1/0. Mes manipuliuosime šiuo masyvu naudodami MONTH funkciją. Čia yra visa formulė, kurią naudosime
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "raudona"), -(MĖNESIS (C2: C4) = 2)) |
Pažvelkime į 3rd masyvu atidžiau. Pirma, mūsų formulė išgauna mėnesio numerį iš kiekvienos datos C2: C4. Tai suteiks mums {1, 2, 2}. Toliau patikriname, ar ši vertė lygi 2. Dabar mūsų masyvas atrodo kaip {False, True, True}. Mes darome dvigubą minusą ir turime {0, 1, 1}. Dabar grįžome į panašią vietą, kurią turėjome 3 pavyzdyje, ir pagal mūsų formulę galime pasakyti, kad vasarį buvo parduota 50 raudonų vienetų.
Dvigubas minusas prieš dauginimą
Jei anksčiau matėte naudojamą SUMPRODUCT funkciją, galbūt matėte šiek tiek kitokį žymėjimą. Užuot naudoję dvigubą minusą, galite rašyti
| 1 | = SUMPRODUCT (A2: A4*(B2: B4 = "raudona")*(MĖNESIS (C2: C4) = 2)) |
Formulė vis tiek veiks taip pat, mes tik rankiniu būdu sakome kompiuteriui, kad norime padauginti masyvus. „SUMPRODUCT“ vis tiek ketino tai padaryti, todėl matematika nesikeičia. Atlikdami matematinę operaciją, mūsų tiesa/klaidinga paverčiama 1/0 to paties. Taigi, kodėl skirtumas?
Dažniausiai tai nėra per daug svarbu, o tai priklauso nuo vartotojo pageidavimų. Tačiau yra bent vienas atvejis, kai reikia dauginti.
Kai naudojate SUMPRODUCT, kompiuteris tikisi, kad visi argumentai (masyvas1, masyvas2 ir kt.) Bus vienodo dydžio. Tai reiškia, kad jie turi tą patį eilučių ar stulpelių skaičių. Tačiau naudodami SUMPRODUCT galite atlikti tai, kas žinoma kaip dviejų matmenų masyvo skaičiavimas, kurį pamatysime kitame pavyzdyje. Kai tai darote, masyvai yra skirtingų dydžių, todėl turime apeiti tą „visų to paties dydžio“ patikrinimą.
Du matmenys
Visuose ankstesniuose pavyzdžiuose mūsų masyvai vyko ta pačia kryptimi. „SUMPRODUCT“ gali tvarkyti dalykus dviem kryptimis, kaip matysime kitoje lentelėje.
Štai mūsų parduodamų vienetų lentelė, tačiau duomenys pertvarkomi ten, kur kategorijos yra viršuje. Jei norime sužinoti, kiek daiktų buvo raudonos ir A kategorijos, galime parašyti
| 1 | = SUMPRODUCT ((A2: A4 = "raudona")*(B1: C1 = "A")*B2: C4) |
Kas čia vyksta?? Pasirodo, mes dauginsime dviem skirtingomis kryptimis. Tai vizualizuoti yra sunkiau, kai parašyta sakiniu, todėl turime keletą vaizdų, kurie mums padės. Pirma, mūsų eilutės kriterijai (ar tai raudona?) Padaugės iš kiekvienos masyvo eilutės.
| 1 | = SUMPRODUCT ((A2: A4 = "RED")*B2: C4) |
Tada stulpelio kriterijai (ar tai yra A kategorija?) Padaugės iš kiekvieno stulpelio
| 1 | = SUMPRODUCT ((A2: A4 = "raudona")*(B1: C1 = "A")*B2: C4) |
Abiem kriterijams atlikus savo darbą, lieka tik nuliai ir 5 ir 10. „SUMPRODUCT“ atsakys iš viso 15.
Prisiminkite, kaip mes kalbėjome apie tai, kad masyvai turi būti vienodo dydžio, nebent darote du matmenis? Tai buvo iš dalies teisinga. Dar kartą peržiūri masyvus, kuriuos naudojome savo formulėje. The aukščio iš dviejų mūsų masyvų yra tas pats, o plotis iš dviejų mūsų masyvų yra vienodi. Taigi, jūs vis dar turite įsitikinti, kad viskas bus išdėstyta teisingai, tačiau galite tai padaryti skirtingais matmenimis.
Du matmenys ir sudėtingas
Daug kartų mums pateikiami duomenys, kurie nėra tinkamiausiu mūsų formulėms. Galėtume pabandyti rankiniu būdu jį pertvarkyti arba galime būti protingesni naudodami savo formules. Apsvarstykite šią lentelę.
Čia mes turime kiekvieno mėnesio mūsų prekių ir pardavimų duomenis. Kaip galėtume sužinoti, kiek daiktų Bobas pardavė per metus?
Norėdami tai padaryti, naudosime dvi papildomas funkcijas: SEARCH ir ISNUMBER. Funkcija PAIEŠKA leis mums ieškoti raktinio žodžio „elementai“ antraštės langeliuose. Šios funkcijos rezultatas bus rodomas skaičiumi arba klaida (jei raktinis žodis nerastas). Tada konvertavimui naudosime ISNUMBER kad į mūsų loginę vertę. Mūsų formulė atrodys žemiau.
Jūs jau turėtumėte būti gerai susipažinę su pirmuoju masyvu. Tai sukurs tokią išvestį kaip {0, 1, 0, 1}. Kitas kriterijų masyvas, apie kurį ką tik kalbėjome. Bus sukurtas visų langelių skaičius su elementais ir klaida kitiems {5, #N/A !, 5, #N/A!}. Tada ISNUMBER konvertuoja tai į loginę reikšmę {True, False, True, False}. Tada, kai padauginsime, tai išlaikys tik pirmojo ir trečiojo stulpelio reikšmes. Po to, kai visi masyvai dauginasi vienas nuo kito, vieninteliai ne nuliniai skaičiai, kuriuos turime, yra čia paryškinti:
| 1 | = SUMPRODUCT ((A2: A5 = "Bob")*(ISNUMBER (SEARCH ("Elementai", B1: E1))*B2: E5)) |
Tada „SUMPRODUCT“ visa tai pridės ir gausime galutinį rezultatą 29.
SUMPRODUCT Or
Pasitaiko daug situacijų, kai norėtume susumuoti vertes, jei mūsų kriterijų stulpelyje yra viena reikšmė ARBA kita reikšmė. Tai galite padaryti naudodamiesi „SUMPRODUCT“ pridėję du kriterijų masyvus vienas prieš kitą.
Šiame pavyzdyje norime pridėti vienetų, parduodamų tiek raudonai, tiek mėlynai.
Mūsų formulė atrodys taip
| 1 | = SUMPRODUCT (A2: A7, (B2: B7 = "raudona")+(B2: B7 = "mėlyna")) |
Pažvelkime į raudonų kriterijų masyvą. Bus sukurtas masyvas, kuris atrodo taip: {1, 1, 0, 0, 0, 0}. „Blue“ kriterijų masyvas atrodys kaip {0, 0, 1, 0, 1, 0}. Sudėjus juos, naujas masyvas atrodys kaip {1, 1, 1, 0, 1, 0}. Matome, kaip abu masyvai susiliejo į vieną kriterijų masyvą. Tada funkcija padaugins ją iš pirmojo masyvo ir gausime {100, 50, 10, 0, 75, 0}. Atkreipkite dėmesį, kad žaliosios vertės buvo nulinės. Paskutinis „SUMPRODUCT“ žingsnis yra sudėti visus skaičius, kad būtų pasiektas 235 sprendimas.
Čia vienas atsargumo žodis. Būkite atsargūs, kai kriterijų masyvai vienas kito nesudaro. Mūsų pavyzdyje B stulpelio vertės gali būti raudonos arba mėlynos, tačiau žinojome, kad niekada negali būti abi. Pagalvokime, ar būtume parašę šią formulę:
| 1 | = SUMPRODUCT (A2: A7, (A2: A7> = 50)+(B2: B7 = "mėlyna")) |
Mūsų tikslas yra rasti mėlynų elementų, kurie buvo parduoti arba kurių kiekis buvo didesnis nei 50. Tačiau šios sąlygos nėra išskirtinės, nes viena eilutė gali būti didesnė nei 50 A stulpelyje. ir būk mėlyna. Dėl to pirmasis kriterijų masyvas atrodytų kaip {1, 1, 0, 1, 1, 0}, o antrasis kriterijų masyvas yra {0, 0, 1, 0, 1, 0}. Sujungus juos, buvo gauta {1, 1, 1, 1, 2, 0}. Ar matai, kaip dabar turime 2? Jei paliktumėte vieną, SUMPRODUCT galiausiai padvigubintų šios eilutės vertę, pakeisdamas 75 į 150, ir gautume neteisingą rezultatą. Norėdami tai ištaisyti, savo masyvui atliekame išorinių kriterijų patikrinimą, pavyzdžiui:
| 1 | = SUMPRODUCT (A2: A7, -((A2: A7> = 50)+(B2: B7 = "mėlyna")> 0)) |
Dabar, sudėjus du vidinius kriterijų masyvus, patikrinsime, ar rezultatas yra didesnis nei 0. Taip atsikratysime 2, kuriuos turėjome anksčiau, ir turėsime masyvą, pvz., {1, 1, 1 , 1, 1, 0}, kuris duos teisingą rezultatą.
SUMPRODUCT Tiksliai
Dauguma „Excel“ funkcijų neskiria didžiųjų ir mažųjų raidžių, tačiau kartais turime sugebėti atlikti paiešką, atsižvelgdami į didžiųjų ir mažųjų raidžių reikšmes. Kai norimas rezultatas yra skaitinis, mes galime tai padaryti naudodami funkciją TIKSLAS funkcijoje SUMPRODUCT. Apsvarstykite šią lentelę:
Norime rasti punkto „ABC123“ balą. Paprastai funkcija EXACT palygins du elementus ir grąžins loginę išvestį, nurodydama, ar abu elementai yra tiksliai tas pats. Tačiau, kadangi esame SUMPRODUCT, mūsų kompiuteris žinos, kad turime reikalų su masyvais, ir galės palyginti vieną elementą su kiekvienu masyvo elementu. Mūsų formulė atrodys taip
| 1 | = SUMPRODUCT (-TIKSLAS ("ABC123", A2: A5), B2: B5) |
Funkcija TIKSLAS patikrins kiekvieną A2: A5 elementą, kad pamatytų, ar jis atitinka reikšmę ir didžiąsias ir mažąsias raides. Taip bus sukurtas masyvas, panašus į {0, 1, 0, 0}. Padauginus iš B2: B5, masyvas tampa {0, 2, 0, 0}. Po galutinio apibendrinimo gauname 2 sprendimą.
SUMPRODUCT „Google“ skaičiuoklėse
Funkcija SUMPRODUCT „Google“ skaičiuoklėse veikia taip pat, kaip ir „Excel“:
SUMPRODUCT VBA pavyzdžiai
Taip pat VBA galite naudoti SUMPRODUCT funkciją. Tipas: application.worksheetfunction.sumproduct (masyvas1, masyvas2, masyvas3)
Vykdydami šiuos VBA teiginius
| 1 | Diapazonas ("B10") = Application.WorksheetFunction.SumProduct (Diapazonas ("A2: A7"), diapazonas ("B2: B7")) |
duos šiuos rezultatus
Funkcijų argumentams (masyvas1 ir kt.) Galite juos įvesti tiesiai į funkciją arba nustatyti kintamuosius, kuriuos naudoti.
