Как да сумираме по цвят в Excel? (2 полезни метода)
Най-добрите 2 метода за сумиране по цветове в Excel са както следва -
- Използване на формула SUBTOTAL в Excel и филтриране по цветна функция.
- Прилагане на формула GET.CELL чрез дефиниране на името в раздела за формула и прилагане на формулата SUMIF в Excel за обобщаване на стойностите по цветови кодове.
Нека обсъдим всеки от тях подробно -
# 1 - Сума по цвят с помощта на междинна функция
За да разберем подхода за изчисляване на сумата от стойности по цветове на фона, нека разгледаме таблицата с данни по-долу, която предоставя подробности за сумите в щатски долари по региони и месеци.
- Стъпка 1: Да предположим, че бихме искали да маркираме онези клетки, които имат отрицателни стойности с цел индикация, това може да се постигне чрез прилагане на условно форматиране или чрез ръчно маркиране на клетките, както е показано по-долу.
- Стъпка 2: Сега, за да постигнете сумата от клетки, оцветени в Excel, въведете формулата за SUBTOTAL под таблицата с данни. Синтаксисът за формулата SUBTOTAL е показан по-долу.
Формулата, която се въвежда за изчисляване на сумирането е
= ОБЩО (9, D2: D13)
Тук номер '9' в аргумента function_num се отнася до функционалността на сумата , а референтният аргумент е даден като диапазон от клетки, които трябва да бъдат изчислени. По-долу е екранна снимка за същото.
- Стъпка 3: Както се вижда на горната екранна снимка, е изчислено сумиране на сумата в щатски долари, за да се изчислят сумите, маркирани в светлочервен цвят на фона. Приложете филтър към таблицата с данни, като отидете на Данни, след което изберете филтър.
- Стъпка 4: След това изберете филтър по цвят и изберете светлочервения цвят под „Филтриране по цвят на клетката“. По-долу е екранната снимка за по-добро описание на филтъра.
- Стъпка 5: След като Excel филтърът бъде приложен, таблицата с данни ще бъде филтрирана само за светлочервени фонови клетки, а междинната формула, приложена в долната част на таблицата с данни, ще покаже сумирането на цветните клетки, които се филтрират, както е показано По-долу.
Както е показано на горната екранна снимка, изчислението на цветната клетка се постига в клетка E17, междинна формула.
# 2 - Сума по цвят с помощта на функцията Get.Cell
Обяснява се вторият подход, за да се стигне до сумата на цветните клетки в Excel, както е обсъдено в примера по-долу. Помислете за таблицата с данни, за да разберете по-добре методологията.
- Стъпка 1: Нека сега подчертаем списъка с клетки в колоната USD USD, които сме готови да получим желаната сума от цветни клетки, както е показано по-долу.
- Стъпка 2: Както виждаме на горната екранна снимка, за разлика от първия пример тук, имаме множество цветове. По този начин ще използваме формулата = GET.CELL, като я дефинираме в полето за име, а не директно да я използваме в Excel.
- Стъпка 3: Сега, след като се появи диалоговият прозорец за „Определяне на име“, въведете Името и формулата забравяйки .CELL в „Препратка към“, както е показано на екрана по-долу.
Както се вижда на горната екранна снимка, името, въведено за функцията, е „CellColor“ и формулата = GET.CELL (38, „Пример 2! $ D2) трябва да бъде въведена в„ Отнася се за. “ Във формулата числовото 38 се отнася до информацията за кода на клетката, а вторият аргумент е номерът на клетката D2 се отнася до референтната клетка. Сега щракнете, OK.
- Стъпка 4: Сега въведете функцията Name 'CellColor' в клетката до цветно кодираната клетка, която беше дефинирана в диалоговия прозорец, както е обяснено в стъпка 3.
Както може да се види на горната екранна снимка, се въвежда функцията „CellColor“ , която връща цветовия код за цвета на фона на клетката.
По същия начин формулата се плъзга за цялата колона.
- Стъпка 5: Сега, за да стигнем до сумата на стойностите по цветове в Excel, ще въведем формулата SUMIF. Синтаксисът за формулата SUMIF е както следва:
Както се вижда от горната екранна снимка, във формулата SUMIF се въвеждат следните аргументи: -
- Аргументът за диапазон се въвежда за диапазон на клетки E2: E13.
- Критериите се въвеждат като G2, чиито обобщени стойности са необходими за извличане.
- Диапазонът от клетки се въвежда като D2: D13.
Формулата SUMIF се плъзга надолу за всички номера на цветовите кодове, за които трябва да се добавят стойности.
Неща за запомняне
- Докато използва подхода от формулата SUBTOTAL, тази функционалност позволява на потребителите да филтрират само по един цвят на филтъра наведнъж. Освен това, тази функционалност може да се използва за добавяне само на една колона със стойности чрез цветове на филтъра. Ако в различните колони има повече от една колона с различни цветове по редове, SUBTOTAL може да покаже правилния резултат само за един филтър по цвят само за конкретна колона.
- Формулата GET.CELL, в комбинация с подхода SUMIF, премахва това ограничение, тъй като тази функционалност може да се използва за обобщаване по цветове за множество цветови кодове във фона на клетката.
