Kaip „Excel“ susumuoti pagal spalvas? (2 naudingi metodai)
2 populiariausi „Excel“ pagal spalvas susumavimo metodai yra šie:
- Formulės SUBTOTAL naudojimas programoje „Excel“ ir filtras pagal spalvų funkciją.
- Taikant formulę GET.CELL, formulės skirtuke apibrėžiant pavadinimą ir taikant „Excel“ formulę SUMIF, kad vertės būtų apibendrintos pagal spalvų kodus.
Aptarkime kiekvieną iš jų išsamiai -
# 1 - suma pagal spalvas naudojant tarpinę funkciją
Norėdami suprasti vertybių sumos skaičiavimo pagal fono spalvas metodą, apsvarstykite toliau pateiktą duomenų lentelę, kurioje pateikiama išsami informacija apie sumas JAV doleriais pagal regioną ir mėnesį.
- 1 žingsnis: Tarkime, kad norėtume paryškinti tas langelius, kurių vertės yra neigiamos nurodant indikaciją, tai galima pasiekti taikant sąlyginį formatavimą arba paryškinant langelius rankiniu būdu, kaip parodyta žemiau.
- 2 žingsnis: Dabar, norėdami pasiekti „Excel“ spalvos langelių sumą, po duomenų lentele įveskite SUBTOTAL formulę. Formulės SUBTOTAL sintaksė parodyta žemiau.
Formulė, kuri įvedama apskaičiuojant sumą, yra
= SUBTOTAL (9, D2: D13)
Čia skaičius „9“ argumente function_num nurodo sumos funkcionalumą, o nuorodos argumentas nurodomas kaip apskaičiuojamų langelių diapazonas. Žemiau pateikiama to paties ekrano kopija.
- 3 žingsnis: Kaip matyti pirmiau pateiktoje ekrano kopijoje, buvo apskaičiuota USD sumos suma, kad būtų galima apskaičiuoti sumas, paryškintas šviesiai raudona fono spalva. Taikykite filtrą duomenų lentelėje eidami į Duomenys, tada pasirinkdami filtrą.
- 4 žingsnis: Tada pasirinkite filtrą pagal spalvą ir skiltyje „Filtruoti pagal langelio spalvą“ pasirinkite šviesiai raudoną langelio spalvą. Žemiau pateikiama ekrano kopija, kad geriau apibūdintumėte filtrą.
- 5 žingsnis: pritaikius „Excel“ filtrą, duomenų lentelė bus filtruojama tik šviesiai raudonos spalvos fono langeliams, o duomenų lentelės apačioje pritaikytoje tarpinės sumos formulėje būtų rodomas spalvotų langelių, kurie filtruojami kaip parodyta, suvestinė žemiau.
Kaip parodyta pirmiau pateiktame ekrano paveikslėlyje, spalvotos langelio apskaičiavimas atliekamas ląstelėje E17, tarpinės formulės.
# 2 - suma pagal spalvas, naudojant funkciją „Get.Cell“
Antrasis požiūris paaiškinamas siekiant gauti spalvų ląstelių sumą excel, kaip aptarta toliau pateiktame pavyzdyje. Apsvarstykite duomenų lentelę, kad geriau suprastumėte metodiką.
- 1 žingsnis: Dabar paryškinkime langelių sąrašą stulpelyje USD suma, kurią norime gauti norimą spalvotų langelių sumą, kaip parodyta žemiau.
- 2 žingsnis: Kaip matome aukščiau pateiktoje ekrano kopijoje, skirtingai nei pirmame pavyzdyje, mes turime kelias spalvas. Tokiu būdu mes naudosime formulę = GET.CELL , apibrėždami ją pavadinimo laukelyje ir tiesiogiai nenaudodami „Excel“.
- 3 žingsnis: Dabar, kai pasirodys dialogo langas „Apibrėžti vardą“, įveskite pavadinimą ir formulę, pamiršdami .CELL „Žiūrėti“, kaip parodyta žemiau esančiame ekrano kopijoje.
Kaip matyti iš pirmiau pateikto ekrano, funkcijos įvestas vardas yra „CellColor“, o formulė = GET.CELL (38, „2 pavyzdys! $ D2“) turi būti įvesta į „Nurodo“. Formulėje skaitmuo 38 nurodo langelio kodo informaciją, o antrasis argumentas yra langelio numeris D2 nurodo etaloninę ląstelę. Dabar spustelėkite Gerai.
- 4 žingsnis: Dabar langelyje šalia spalva pažymėtos langelio, kuris buvo apibrėžtas dialogo lange, kaip paaiškinta 3 žingsnyje, įveskite funkciją Pavadinimas „CellColor“ .
Kaip matyti iš pirmiau pateikto ekrano, įvedama funkcija „CellColor“ , kuri grąžina fono langelio spalvos spalvos kodą.
Panašiai formulė traukiama visam stulpeliui.
- 5 žingsnis: Dabar, norėdami pasiekti „Excel“ spalvų reikšmių sumą, įvesime formulę SUMIF. SUMIF formulės sintaksė yra tokia: -
Kaip matyti iš pirmiau pateiktos ekrano kopijos, į SUMIF formulę įrašomi šie argumentai: -
- E2: E13 langelių diapazone įvedamas diapazono argumentas.
- Kriterijai įrašomi kaip G2, kurio apibendrintas reikšmes reikia gauti.
- Langelių diapazonas yra D2: D13.
Visų spalvų kodų numerių, kurių reikšmės turi būti pridėtos, formulė SUMIF vilkiama žemyn.
Ką reikia atsiminti
- Naudojant metodą pagal SUBTOTAL formulę, ši funkcija leidžia vartotojams vienu metu filtruoti tik vieną filtro spalvą. Be to, šia funkcija galima pridėti tik vieną reikšmių stulpelį pagal filtro spalvas. Jei skirtinguose stulpeliuose yra daugiau nei vienas stulpelis su skirtingomis spalvomis pagal eilutes, SUBTOTAL gali rodyti teisingus vieno filtro pagal spalvas rezultatus tik konkrečiam stulpeliui.
- GET.CELL formulė kartu su SUMIF metodu pašalina šį apribojimą, nes šią funkciją galima panaudoti apibendrinant spalvas keliems spalvų kodams langelio fone.
