Kako seštevati po barvah v Excelu? (2 uporabni metodi)
Najvišji 2 metodi za seštevanje po barvah v Excelu so naslednji -
- Uporaba formule SUBTOTAL v Excelu in filtriranje po barvni funkciji.
- Uporaba formule GET.CELL z definiranjem imena na zavihku formule in uporabo formule SUMIF v Excelu za strnitev vrednosti po barvnih kodah.
Pogovorimo se o vsakem od njih podrobno -
# 1 - Vsota po barvah z uporabo funkcije vmesnega seštevka
Da bi razumeli pristop k izračunu vsote vrednosti glede na barve ozadja, si oglejmo spodnjo podatkovno tabelo, ki vsebuje podrobnosti o zneskih v ameriških dolarjih po regijah in mesecih.
- 1. korak: Recimo, da bi radi označevanja označili tiste celice, ki imajo negativne vrednosti, to lahko dosežemo z uporabo pogojnega oblikovanja ali z ročnim označevanjem celic, kot je prikazano spodaj.
- 2. korak: Zdaj, če želite doseči vsoto celic, ki so obarvane v Excelu, pod tabelo s podatki vnesite formulo za SUBTOTAL. Sintaksa formule SUBTOTAL je prikazana spodaj.
Formula, ki jo vnesemo za izračun vsote, je
= VSEBINA (9, D2: D13)
Tu se številka '9' v argumentu function_num nanaša na funkcijo vsote , referenčni argument pa je podan kot obseg celic, ki jih je treba izračunati. Spodaj je posnetek zaslona za isto.
- 3. korak: Kot je razvidno iz zgornjega posnetka zaslona, je bil za izračun zneskov, označenih v svetlo rdeči barvi ozadja, izračunan seštevek zneska USD. Uporabite filter za podatkovno tabelo tako, da odprete Podatki in nato izberete filter.
- Korak 4: Nato izberite filter po barvi in izberite svetlo rdečo celico pod "Filtriraj po barvi celic". Spodaj je posnetek zaslona za boljši opis filtra.
- 5. korak: Ko je uporabljen filter excel, bo podatkovna tabela filtrirana samo za celice svetlo rdečega ozadja, formula vmesnega seštevka, uporabljena na dnu podatkovne tabele, pa bo prikazala vsoto barvnih celic, ki so filtrirane, kot je prikazano spodaj.
Kot je prikazano na zgornjem posnetku zaslona, je izračun barvne celice dosežen v celici E17, vmesni seštevek formule.
# 2 - Vsota po barvah s pomočjo funkcije Get.Cell
Drugi pristop je razložen tako, da pride do vsote barvnih celic v Excelu, kot je razloženo v spodnjem primeru. Za boljše razumevanje metodologije upoštevajte tabelo podatkov.
- 1. korak: Zdaj označimo seznam celic v stolpcu Znesek USD, za katere smo pripravljeni priti do želene vsote barvnih celic, kot je prikazano spodaj.
- 2. korak: Kot vidimo na zgornjem posnetku zaslona, imamo v nasprotju s prvim primerom več barv. S tem bomo uporabili formulo = GET.CELL, tako da jo določimo v polju z imenom in ne neposredno v Excelu.
- Korak 3: Ko se enkrat prikaže pogovorno okno za določanje imena, vnesite »Ime in formula, ki pozablja .CELL« v »Glej«, kot je prikazano na spodnjem posnetku zaslona.
Kot je razvidno iz zgornjega posnetka zaslona, je za funkcijo vneseno ime 'CellColor', v 'Refers to' pa vnesite formulo = GET.CELL (38, 'Primer 2! $ D2). V formuli se številka 38 nanaša na informacije o kodi celice, drugi argument pa je številka celice D2, ki se nanaša na referenčno celico. Zdaj kliknite V redu.
- 4. korak: Zdaj vnesite funkcijo Ime 'CellColor' v celico poleg barvno kodirane celice, ki je bila definirana v pogovornem oknu, kot je razloženo v 3. koraku.
Kot je razvidno iz zgornjega posnetka zaslona, je vnesena funkcija 'CellColor' , ki vrne barvno kodo za barvo celice v ozadju.
Podobno se formula povleče za celoten stolpec.
- 5. korak: Zdaj, da bomo prišli do vsote vrednosti po barvah v Excelu, bomo vnesli formulo SUMIF. Sintaksa formule SUMIF je naslednja:
Kot je razvidno iz zgornjega posnetka zaslona, so v formulo SUMIF vneseni naslednji argumenti: -
- Argument obsega se vnese za obseg celic E2: E13.
- Merila so vpisana kot G2, katerih povzete vrednosti so potrebne za iskanje.
- Obseg celic je vnesen kot D2: D13.
Formula SUMIF se povleče navzdol za vse številke barvnih kod, za katere je treba sešteti vrednosti.
Stvari, ki si jih je treba zapomniti
- Medtem ko uporabljamo pristop s formulo SUBTOTAL, ta funkcionalnost uporabnikom omogoča filtriranje samo ene barve filtra hkrati. Poleg tega je to funkcijo mogoče uporabiti za dodajanje samo enega stolpca vrednosti po barvah filtra. Če je v različnih stolpcih več stolpcev z različnimi barvami po vrsticah, lahko SUBTOTAL prikaže pravi rezultat samo za en filter po barvi samo za določen stolpec.
- Formula GET.CELL v kombinaciji s pristopom SUMIF odpravlja to omejitev, saj bi to funkcijo lahko uporabili za povzemanje po barvah za več barvnih kod v ozadju celice.
