Jei norite gauti stulpelio sumą tiesiog naudodami stulpelio pavadinimą, „Excel“ galite tai padaryti trimis paprastais būdais. Panagrinėkime šiuos būdus.
Skirtingai nuo kitų straipsnių, pirmiausia pažvelkime į scenarijų.
Čia turiu pardavimų lentelę, kurią skirtingi mėnesiai atliko skirtingi pardavėjai.
Dabar užduotis yra gauti kiekvieno mėnesio pardavimo sumą ląstelėje C10. Jei pakeisime B10 mėnesį, suma turėtų pasikeisti ir grąžins to mėnesio sumą, nieko nekeisdama formulėje.
1 metodas: sumokite visą lentelės stulpelį naudodami funkciją SUMPRODUCT.
SUMPRODUCT metodo sintaksė į sumos atitikimo stulpelį yra tokia:
| = SUMPRODUCT ((stulpeliai)*(antraštės = antraštė)) |
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.
Antraštės:Tai yra antraščių diapazonas stulpelių kurį norite apibendrinti. Aukščiau pateiktuose duomenyse tai yra C2: N2.
Antraštė: Tai yra antraštė, kurią norite suderinti. Aukščiau pateiktame pavyzdyje jis yra B10.
Nedelsdami naudokime formulę.
| = SUMPRODUCT ((C3: N7)*(C2: N2 = B10)) |
ir tai grįš:
Kaip tai veikia?
Tai paprasta. Formulėje teiginysC2: N2 = B10 grąžina masyvą, kuriame yra visos FALSE reikšmės, išskyrus tą, kuri atitinka B10. Dabar formulė yra
| =SUMPRODUCT ((C3: N7)*{FALSE, FALSE, FALSE, FALSE,TIESA, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE}) |
Dabar C3: N7 padauginamas iš kiekvienos šio masyvo vertės. Kiekvienas stulpelis tampa nulis, išskyrus stulpelį, kuris dauginamas iš TIESA. Dabar formulė tampa tokia:
| = SUMPRODUCT ({0,0,0,0,6,0,0,0,0,0,0,0; 0,0,0,0,6,12,0,0,0,0,0,0,0, 0; 0,0,0,0,15,0,0,0,0,0,0,0; 0,0,0,0,15,0,0,0,0,0,0,0,0; 0,0,0,0,8,0,0,0,0,0,0,0,0}) |
Dabar šis masyvas yra apibendrintas ir gauname stulpelio, atitinkančio langelio B10 stulpelį, sumą.
2 metodas: sumokite visą stulpelį lentelėje, naudodami funkciją INDEX-MATCH.
Atitinkamo stulpelio antraštės „Excel“ sumavimo metodo sintaksė yra tokia:
| = SUM (INDEX (stulpeliai ,, MATCH (antraštė, antraštės, 0))) |
Visi šio metodo kintamieji yra tokie patys kaip SUMPRODUCT metodas. Tiesiog įgyvendinkime tai, kad išspręstume problemą. Parašykite šią formulę C10.
| = SUMA (RODIKLIS (C3: N7,, MATCH (B10, C2: N2,0))) |
Tai grąžina:
Kaip tai veikia?
Formulė išspręsta iš vidaus. Pirma, funkcija MATCH grąžina atitinkamo mėnesio indeksą iš diapazono C2: N2. Kadangi B1o turime gegužę, gauname 5. Dabar formulė tampa
| = SUMA (RODIKLIS (C3: N7,, 5)) |
Toliau funkcija INDEX grąžina vertes iš 5 -ojo C3 stulpelio: N7. Dabar formulė tampa tokia:
| = SUMA ({6; 12; 15; 15; 8}) |
Ir galiausiai, mes gauname šių verčių sumą.
3 metodas: sumokite visą stulpelį lentelėje, naudodami pavadintą diapazoną ir netiesioginę funkciją
Viskas bus paprasta, jei savo diapazonus pavadinsite stulpelių antraštėmis. Taikant šį metodą, pirmiausia turime pavadinti stulpelius kaip jų antraščių pavadinimus.
Pasirinkite lentelę su antraštėmis ir paspauskite CTRL+SHIFT+F3. Bus atidarytas dialogo langas, kuriame bus sukurtas pavadinimas iš diapazonų. Patikrinkite viršutinę eilutę ir paspauskite mygtuką Gerai.
Jis pavadins visus duomenų stulpelius kaip jų antraštes.
Dabar bendroji formulė, kaip sudėti atitinkantį stulpelį, bus tokia:
| = SUMA (NETIESIOGINĖ (antraštė)) |
Antraštė: Tai stulpelio, kurį norite apibendrinti, pavadinimas. Šiame pavyzdyje jau dabar yra B10.
Norėdami įgyvendinti šią bendrąją formulę, parašykite šią formulę langelyje C10.
| = SUMA (NETIESIOGINĖ (B10)) |
Tai grąžina gegužės mėnesio sumą:
Kitas metodas yra panašus į šį. Šiuo metodu mes naudojame „Excel“ lenteles ir struktūrizuotus pavadinimus. Tarkime, jei aukščiau pateiktą lentelę pavadinote table1. Tada ši formulė veiks taip pat, kaip aukščiau pateikta formulė.
| = SUMA (NETIESIOGINĖ ("1 lentelė [" & B10 & "]")) |
Kaip tai veikia?
Šioje formulėje funkcija INDIRECT paima vardo nuorodą ir paverčia ją faktine vardo nuoroda. Toliau procedūra yra paprasta. Funkcija SUM apibendrina pavadintą diapazoną.
Taigi taip, vaikinai, taip galite apibendrinti atitinkamą „Excel“ stulpelį. Tikiuosi, kad tai jums naudinga ir paaiškinanti. Jei turite kokių nors abejonių dėl šio straipsnio ar kitos su „Excel“/VBA susijusios temos, paklauskite žemiau esančiame komentarų skyriuje.
Kaip susumuoti, suderinant eilutę ir stulpelį „Excel“ |SUMPRODUCT yra pati universaliausia funkcija, kai reikia sudėti ir suskaičiuoti vertes pagal sudėtingus kriterijus. Bendroji funkcija, kurią reikia sudėti pagal stulpelį ir eilutę, yra…
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.