Sumif с множество критерии, базирани на ИЛИ и И логика
Когато работите върху работни листове на Excel, може да се наложи да сумирате стойности въз основа на множество критерии. Понякога множеството критерии са от една и съща колона (логика ИЛИ), но понякога от различни колони (логика И). В този случай как бихте могли да се справите с тази задача в Excel?
- Sumi с множество критерии, базирани на ИЛИ логика
- Sumif с множество критерии, базирани на И логика чрез използване на функцията SUMIFS
Sumi с множество критерии, базирани на ИЛИ логика
Да предположим, че имате списък с продукти, който съдържа доставчика и количеството, както е показано на екранната снимка по-долу, сега искате да получите всички общи количества, които се доставят от доставчика John and David. Тук ще ви представя две прости формули.
Чрез използване на SUMIF + SUMIF +... формула
Ако искате да сумирате числа, които отговарят на някой от критериите (ИЛИ логика) от множество критерии, можете да добавите няколко функции SUMIF в една формула, общият синтаксис е:
- criteria_range: Диапазонът от клетки, които искате да отговарят на критериите;
- criteria1: Първият критерий, който се използва за определяне кои клетки трябва да бъдат сумирани;
- criteria2: Вторият критерий, който се използва за определяне кои клетки трябва да бъдат сумирани;
- sum_range: Диапазонът от клетки, от който искате да сумирате.
Сега, моля, копирайте или въведете някоя от формулите по-долу в празна клетка и натиснете Въведете ключ за получаване на резултата:
=SUMIF(B4:B14,E4,C4:C14) + SUMIF(B4:B14,E5,C4:C14) (Use a cell reference)
Обяснение на формулата:
=SUMIF(B4:B14,"John",C4:C14) + SUMIF(B4:B14,"David",C4:C14)
- Първи SUMIF(B4:B14;"Джон";C4:C14) намира редовете на Джон и сумира общите количества;
- Вторият SUMIF(B4:B14;"Дейвид";C4:C14) намира редовете на Давид и сумира общите количества;
- След това добавете тези две формули SUMIF, за да получите всички общи количества, доставени от Джон и Дейвид.
Чрез използване на функции SUM и SUMIF
Горната формула е много лесна за използване, ако има само няколко критерия, но ако искате да сумирате стойности с множество ИЛИ условия, горната формула може да е излишна. В този случай по-добра формула, която е създадена на базата на функции SUM и SUMIF, може да ви направи услуга. Общият синтаксис е:
Обща формула с твърдо кодиран текст:
- criteria_range: Диапазонът от клетки, които искате да отговарят на критериите;
- criteria1: Първият критерий, който се използва за определяне кои клетки трябва да бъдат сумирани;
- criteria2: Вторият критерий, който се използва за определяне кои клетки трябва да бъдат сумирани;
- sum_range: Диапазонът от клетки, от който искате да сумирате.
Обща формула с препратки към клетки:
Array formula, should press Ctrl + Shift + Enter keys together.
- criteria_range: Диапазонът от клетки, които искате да отговарят на критериите;
- criteria_cells: Клетките, които съдържат критериите, които искате да използвате;
- sum_range: Диапазонът от клетки, от който искате да сумирате.
Моля, въведете или копирайте някоя от формулите по-долу, от които се нуждаете, в празна клетка и след това получете резултата:
=SUM(SUMIF(B4:B14, E4:E5, C4:C14)) (Use cell references, array formula, should press Ctrl + Shift + Enter keys)
Обяснение на формулата:
=SUM(SUMIF(B4:B14, {"Джон","Дейвид"}, C4:C14))
> SUMIF(B4:B14, {"Джон","Дейвид"}, C4:C14):
- {„Джон“, „Дейвид“}: Константа на масив, която е набор от множество критерии, предоставени във фигурни скоби.
- SUMIF(B4:B14;"Дейвид";C4:C14) Константата на масива, използваща логика ИЛИ, принуждава функцията SUMIF да сумира числата в C4:C14 въз основа на един от множеството критерии („Джон“ и „Дейвид“) и ще върне два отделни резултата: {410,1520}.
> SUM(SUMIF(B4:B14, {"Джон","Дейвид"}, C4:C14))=SUM({410,1520}): И накрая, тази функция SUM събира тези резултати от масива, за да върне резултата: 1930.
Sumif с множество критерии, базирани на И логика чрез използване на функцията SUMIFS
Ако искате да сумирате стойности с множество критерии в различни колони, можете да използвате функцията SUMIF, за да решите бързо тази задача. Общият синтаксис е:
- sum_range: Диапазонът от клетки, от който искате да сумирате;
- criteria_range1: Диапазонът, където се прилага criteira1;
- criteria1: Първият критерий, който се проверява спрямо критерии_обхват1 и определя кои клетки да се добавят; (типът критерии може да бъде: число, логически израз, препратка към клетка, текст, дата или друга функция на Excel.)
- criteria_range2, criteria2…: Други допълнителни диапазони и свързаните с тях критерии. (можете да настроите 127 двойки критерии_обхват и критерии във формула SUMIFS.)
Да кажем, че имам таблица с колони за продукт, доставчик, количество и цена, както е показано на екранната снимка по-долу. Сега искам да разбера сумата от общата цена на продукта Apple и количеството, което е по-голямо или равно на 200.
Моля, приложете някоя от формулите по-долу в празна клетка и натиснете Въведете ключ за връщане на резултата:
=SUMIFS(C4:C14,A4:A14,G3,C4:C14,G4) (Use cell references)
Обяснение на формулата:
=SUMIFS(D4:D14,A4:A14,"Apple",C4:C14,">=200")
- Диапазонът A4:A14 проверява за продукта „Apple“, а диапазонът C4:C14 извлича всички количества, по-големи или равни на 200, след което функцията SUMIFS сумира само съответстващите клетки от диапазона D4:D14.
Използвана относителна функция:
- SUMIF:
- Функцията SUMIF може да помогне за сумиране на клетки въз основа на един критерий.
- SUMIFS:
- Функцията SUMIFS в Excel може да помогне за сумиране на стойности на клетки въз основа на множество критерии.
Още статии:
- Сума, ако започва с или завършва с конкретен текст или знаци
- За да сумирате стойности, ако съответните клетки започват с или завършват с определена стойност, можете да приложите функцията SUMIF със заместващ знак (*), за да я изтеглите. Тази статия ще запознае подробно как да използвате формулата.
- Сума, ако клетките съдържат конкретен текст в друга колона
- Този урок ще ви покаже как да сумирате стойности, ако клетките съдържат специфичен или частичен текст в друга колона. Вземете диапазона от данни по-долу като пример, за да получите общите количества на продуктите, съдържащи текста „Тениска“, както функцията SUMIF, така и функцията SUMPRODUCT могат да се справят с тази задача в Excel.
- Сумирайте най-малките или най-долните N стойности в Excel
- В Excel за нас е лесно да сумираме диапазон от клетки с помощта на функцията SUM. Понякога може да се наложи да сумирате най-малките или долните 3, 5 или n числа в диапазон от данни, както е показано на екранната снимка по-долу. В този случай SUMPRODUCT заедно с функцията SMALL може да ви помогне да разрешите този проблем в Excel.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
