Iki šiol mes išmokome apibendrinti visą „Excel“ lentelės stulpelį. Bet kaip mes susumuojame vertes, kai reikia suderinti stulpelį ir eilutę. Šiame straipsnyje mes sužinosime, kaip sudaryti atitinkamų eilučių ir stulpelių sumą.
Yra dvi formulės, kaip tai padaryti, bet pirmiausia pažvelkime į scenarijų.
Čia turiu lentelę, kurioje registruojami darbuotojų pardavimai skirtingais mėnesiais. Darbuotojų pavardės gali kartotis. Žiūrėkite žemiau esantį paveikslėlį:
Turime gauti gegužės mėnesio, kai pardavėjas yra Donaldas, sumą.
1 metodas: atitinkamų stulpelių ir eilučių antraščių apibendrinimas Naudojant SUMPRODUCT funkciją.
The SUMPRODUCT funkcija yra pati universaliausia funkcija, kai reikia sumuoti ir skaičiuoti vertes pagal sudėtingus kriterijus. Bendroji funkcija, kurią reikia sudaryti pagal stulpelį ir eilutę, yra tokia:
| = SUMPRODUCT ((stulpeliai)*(column_headers = column_heading)*(row_headers = row_heading) |
Stulpeliai:Tai 2 matmenų stulpelių diapazonas, kurį norite sudėti. Jame neturėtų būti antraščių. Aukščiau esančioje lentelėje yra C3: N7.
column_headers:Tai yra antraščių diapazonasstulpelių kurį norite apibendrinti. Aukščiau pateiktuose duomenyse tai yra C2: N2.
column_heading: Tai yra antraštė, kurią norite suderinti. Aukščiau pateiktame pavyzdyje jis yra B13.
Nedelsdami naudokime formulę.
row_headers:Tai yra antraščių diapazonaseilutės kurį norite apibendrinti. Aukščiau pateiktuose duomenyse tai yra B3: B10.
row_heading: Tai antraštė, kurią norite suderinti eilutėse. Aukščiau pateiktame pavyzdyje tai yra F13.
Nedelsdami naudokime formulę.
Parašykite šią formulę langelyje D13 ir leiskite „Excel“ padaryti tai magija (nėra tokio dalyko kaip magija)…
| = SUMPRODUCT ((C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)) |
Tai grąžina vertę:
Dabar, kai pakeisite mėnesį ar pardavėją, suma pasikeis pagal eilutės ir stulpelio antraštes.
Kaip tai veikia?
Ši paprasta loginė logika.
(C2: N2 = B13): Šis teiginys grąžins TIESA ir NETIESA masyvą. Visos atitinkančios stulpelio vertės yra teisingos, o kitos - klaidingos. Šiuo atveju turėsime tik vieną „True“, nes diapazone C2: N2 yra tik vienas egzempliorius gegužės 5 dtūkst Vieta.
(B3: B10 = E13): Tai veiks taip pat, kaip aprašyta aukščiau, ir pateiks masyvą TRUE ir FALSE. Visos atitinkančios vertės bus TRUE, o kitos - FALSE. Šiuo atveju turėsime 2 TRUE, nes diapazonas B3: B10 turi du „Donald“ atvejus.
(C2: N2 = B13)*(B3: B10 = E13): Dabar mes dauginame masyvus, pateiktus teiginiais. Tai įgyvendins ir logiką, ir mes gausime 1 ir 0 masyvą. Dabar turėsime 2D masyvą, kuriame bus 2 1 ir poilsio 0.
(C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)= Galiausiai padauginame 2D masyvą su 2D lentele. Jis vėl grąžins 0 masyvą ir kriterijus atitinkančius skaičius.
Galiausiai, SUMPRODUCT funkcija apibendrins masyvą, kurio rezultatas bus norimas.
2 metodas: atitinkamų stulpelių ir eilučių antraščių apibendrinimas Naudojant SUM ir IF funkciją
Bendra formulė, kaip susumuoti atitinkančią eilutę ir stulpelį naudojant SUM ir IF „Excel“ funkciją, yra tokia:
| = SUM (IF (column_headers = column_heading, IF (row_headers = row_heading, stulpeliai))) |
Visi kintamieji yra tokie patys, kaip ir aukščiau aprašytame metode. Čia juos tiesiog reikia naudoti kita tvarka.
D13 langelyje parašykite šią formulę:
| = SUM (IF (C2: N2 = B13, IF (B3: B10 = E13, C3: N10))) |
Tai grąžina teisingą atsakymą. Žr. Ekrano kopiją žemiau:
Kaip tai veikia?
Logika yra tokia pati kaip ir pirmojo SUMPRODCUT metodo, tik mechanizmas skiriasi. Jei trumpai paaiškinsiu, vidinė IF funkcija grąžina 2D masyvą, kurio matmenys yra tokie patys kaip lentelės. Šiame masyve yra dviejų suderintų eilučių skaičius. Tada vidinė IF funkcija atitinka šio masyvo dviejų stulpelių antraštes ir grąžina 2D masyvą, kuriame yra tik skaičiai, atitinkantys ir stulpelį, ir antraštę. Visi kiti masyvo elementai bus klaidingi.
Galiausiai funkcija SUM apibendrina šį masyvą ir mes gauname savo sumą.
Taigi taip, vaikinai, taip galite apibendrinti atitinkamas „Excel“ lentelės eilutes ir stulpelius. Tikiuosi, kad tai buvo jums aiškinama ir naudinga. Jei turite kokių nors abejonių šia tema arba turite kitų su „Excel“/VBA susijusių abejonių, paklauskite žemiau esančiame komentarų skyriuje.
Kaip apibendrinti stulpelį „Excel“ pagal atitinkamą antraštę | Jei norite gauti stulpelio sumą tiesiog naudodami stulpelio pavadinimą, „Excel“ galite tai padaryti trimis paprastais būdais. SUMPRODUCT metodo sintaksė į sumos atitikimo stulpelį yra tokia:
SUMIF su 3D nuoroda „Excel“ |Įdomus faktas yra tas, kad įprasta „Excel“ 3D nuoroda neveikia su sąlyginėmis funkcijomis, tokiomis kaip SUMIF funkcija. Šiame straipsnyje mes išmoksime, kaip gauti 3D nuorodą naudojant SUMIF funkciją.
Santykinė ir absoliuti nuoroda „Excel“ | Nuorodos „Excel“ yra svarbi tema kiekvienam pradedančiajam. Net patyrę „Excel“ vartotojai daro nuorodų klaidų.
Dinaminio darbalapio nuoroda | Dinamiškai pateikite informacinius lapus naudodami „Excel“ netiesioginę funkciją. Tai paprasta…
„Excel“ nuorodų išplėtimas | Išplečiama nuoroda išplečiama, kai nukopijuojama žemyn arba į dešinę. Tam naudojame $ ženklą prieš stulpelį ir eilutės numerį. Štai vienas pavyzdys…
Viskas apie absoliučią nuorodą | Numatytasis „Excel“ nuorodos tipas yra santykinis, tačiau jei norite, kad ląstelių ir diapazonų nuoroda būtų absoliuti, naudokite $ ženklą. Čia yra visi absoliučios nuorodos į „Excel“ aspektai.
Populiarūs straipsniai:
50 „Excel“ nuorodų, skirtų produktyvumui padidinti | Greičiau atlikite savo užduotį. Šie 50 sparčiųjų klavišų dar labiau pagreitins jūsų darbą naudojant „Excel“.
„VLOOKUP“ funkcija „Excel“ | Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama norint ieškoti vertės iš įvairių diapazonų ir lapų.
COUNTIF „Excel 2016“ | Naudodami šią nuostabią funkciją, suskaičiuokite reikšmes su sąlygomis. Norint skaičiuoti konkrečią vertę, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.
Kaip naudoti „SUMIF“ funkciją „Excel“ | Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.