Excel SUMIFS: Sum Only Values ​​Среща множество критерии

Функцията SUMIFS разширява полезността на функцията SUMIF, като ви позволява да посочите от 2 до 127 критерия, а не само една, както в SUMIF.

Обикновено SUMIFS работи с редове от данни, наречени записи. В записа всички данни във всяка клетка или поле в реда са свързани - например име, адрес и телефонен номер на компанията.

SUMIFS търси конкретни критерии в две или повече полета в записа и само ако установи съвпадение за всяко посочено поле, данните са обобщени за този запис.

01 от 10

Как функционира функцията SUMIFS

В стъпка по стъпка стъпка по стъпка на SUMIF съпоставихме единния критерий за агентите по продажбите, които продадоха повече от 250 поръчки за една година.

В този урок ще зададем две условия, като използваме SUMIFS - това на агентите за продажби в района за продажби на Изток, които са имали по-малко от 275 продажби през изминалата година.

Настройването на повече от две условия може да се направи, като се посочат допълнителни параметри Criteria_range и Criteria for SUMIFS.

Следвайки стъпките в уроческите теми по-долу, ще ви преведем в създаването и използването на функцията SUMIFS, показана в горното изображение.

Уроци по теми

• Въвеждане на данните за урока
• Синтаксисът на функцията SUMIFS
• Стартиране на функцията SUMIFS
• Аргументът Sum_range
• Аргументът Criteria_range1
• Аргументът за критериите
• Аргументът
• Аргументът за критериите

02 от 10

Въвеждане на данните за урока

Първата стъпка към използването на функцията SUMIFS в Excel е да въведете данните.

Въведете данните в клетки D1 до F11 на работен лист на Excel, както е показано на изображението по-горе.

Функцията SUMIFS и критериите за търсене (по-малко от 275 поръчки и агенти за продажби от региона за продажби на Изток) ще бъдат добавени към ред 12 под данните.

Указанията за начинаещи не включват стъпки за форматиране на работния лист.

Това няма да повлияе на завършването на урока. Работният ви лист ще изглежда различно от показания пример, но функцията SUMIFS ще ви даде едни и същи резултати.

03 от 10

Синтаксисът на функцията SUMIFS

В Excel, синтаксисът на функцията се отнася до оформлението на функцията и включва името на функцията, скобите и аргументите .

Синтаксисът за функцията SUMIFS е:

= SUMIFS (Sum_range, Criteria_range1, Criteria1, Criteria_range2, Criteria2, ...)

Забележка: В тази функция могат да бъдат зададени до 127 двойки Критерии / Критерии .

Аргументите на функцията SUMIFS

Аргументите на функцията разкриват на функцията какви условия се изпитват и какъв обхват от данни се сумират, когато са изпълнени тези условия.

Всички аргументи в тази функция са задължителни.

Sum_range - данните в този диапазон от клетки се сумират, когато се намери съвпадение между всички посочени критерии и съответните им аргументи Criteria_range .

Criteria_range - групата от клетки, функцията е да се търси съвпадение на съответния аргумент на критерия .

Критерии - тази стойност се сравнява с данните в съответния Критерий_регулатор . За този аргумент могат да бъдат въведени действителни данни или препратка към данните.

04 от 10

Стартиране на функцията SUMIFS

Въпреки че е възможно просто да въведете функцията SUMIFS в клетка в работен лист , много хора смятат, че е по-лесно да се използва диалоговият прозорец на функцията, за да се въведе функцията.

Инструкции Стъпки

1. Кликнете върху клетката F12, за да станете активната клетка . Тук ще въведем функцията SUMIFS.
2. Кликнете върху раздела Формули .
3. Кликнете върху иконата Math & Trig на лентата, за да отворите падащия списък на функциите.
4. Кликнете върху SUMIFS в списъка, за да изведете диалоговия прозорец на функцията SUMIFS.

Данните , които влизаме в празните линии в диалоговия прозорец, ще формират аргументите на функцията SUMIFS.

Тези аргументи разкриват на функцията какви условия изпитваме и какъв обхват от данни се сумират, когато са изпълнени тези условия.

05 от 10

Въвеждане на аргумента Sum_range

Аргументът Sum_range съдържа клетките с данните, които искаме да добавим.

Когато функцията намери съвпадение между всички зададени критри и критерии за аргументи за запис, полето Sum_range за този запис е включено в общата сума.

В този урок данните за аргумента Sum_range се намират в колоната " Общо продажби ".

Инструкции Стъпки

1. Кликнете върху реда Sum_range в диалоговия прозорец .
2. Маркирайте клетките F3 до F9 в работния лист, за да добавите тези препратки към линията Sum_range .

06 от 10

Въвеждане на аргумента Criteria_range1

В този урок ние се опитваме да съчетаем два критерия във всеки запис на данни:

1. Търговски агенти от района за продажби на Изток.
2. Продажбите агенти, които са направили по-малко от 275 продажби тази година.

Аргументът Criteria_range1 показва обхвата на клетките, които SUMIFS трябва да търси, когато се опитват да отговорят на първия критерий - регионът за продажби на Изток.

Инструкции Стъпки

1. В диалоговия прозорец кликнете върху реда Criteria_order1 .
2. Маркирайте клетки D3 до D9 в работния лист, за да въведете тези референтни клетки като обхват, който трябва да бъде претърсен от функцията .

07 от 10

Въвеждане на аргумента Criteria1

В този урок първият критерий, който търсим, е дали данните в диапазона D3: D9 се равняват на изток .

Въпреки че в диалоговия прозорец за този аргумент могат да бъдат въведени актуални данни - например думата " Изток" - най-добре е да добавите данните към клетка в работния лист и след това да въведете тази клетка в диалоговия прозорец.

Инструкции Стъпки

1. Кликнете върху реда Criteria1 в диалоговия прозорец .
2. Кликнете върху клетка D12, за да въведете референтната клетка. Функцията ще търси в диапазона, избран в предишната стъпка, за данни, които отговарят на този критерий.
3. Думата за търсене (Изток) ще бъде добавена към клетка D12 в последната стъпка на урока.

Как референтните клетки увеличават гъвкавостта на SUMIFS

Ако референтната клетка, като например D12, е въведена като аргумент за критерии , функцията SUMIFS ще търси съвпадения с данните, които са били въведени в тази клетка в работния лист .

Така че след намирането на сумата на продажбите за Източния регион ще бъде лесно да намерите едни и същи данни за друг регион на продажбите, просто като промените Изтока на Север или Запад в клетка D12. Функцията автоматично ще актуализира и покаже новия резултат.

08 от 10

Въвеждане на аргумента Criteria_range2

Както вече споменахме, в този урок ние се опитваме да съчетаем два критерия във всеки запис на данни:

1. Търговски агенти от района за продажби на Изток.
2. Продажбите агенти, които са направили по-малко от 275 продажби тази година.

Аргументът Criteria_range2 посочва обхвата на клетките, които SUMIFS трябва да търси, когато се опитват да отговорят на втория критерий - агенти по продажбите, които са продали по-малко от 275 поръчки през тази година.

Инструкции Стъпки

1. В диалоговия прозорец кликнете върху реда Criteria_range2 .
2. Highligt клетки E3 до E9 в работния лист, за да въведете тези референтни клетки като втори диапазон, който трябва да бъде претърсен от функцията .

09 от 10

Въвеждане на аргумента за критерий2

В този урок, вторият критерий, който търсим, е дали данните в обхвата E3: E9 са по-малко от 275 поръчки за продажба.

Както при аргумента Criteria1 , ние ще въведем клетката, отнасяща се до местоположението на Criteria2, в диалоговия прозорец, а не самите данни.

Инструкции Стъпки

1. Кликнете върху линията Criteria2 в диалоговия прозорец .
2. Кликнете върху клетка E12, за да въведете референтната клетка. Функцията ще търси в диапазона, избран в предишната стъпка, за данни, които отговарят на този критерий.
3. Кликнете върху OK, за да завършите функцията SUMIFS и затворете диалоговия прозорец.
4. Отговор от нула (0) ще се появи в клетка F12 - в клетката, в която въведохме функцията - защото все още не сме добавили данните към полетата Criteria1 и Criteria2 (C12 и D12). Докато не го направим, нищо не може да се добави, така че общият брой остава нулев.
5. Критериите за търсене ще бъдат добавени в следващата стъпка на урока.

10 от 10

Добавяне на критериите за търсене и завършване на урока

Последната стъпка в урока е да добавите данни към клетките в работен лист, идентифициран като съдържащ аргументите за критерии .

Инструкции Стъпки

За помощ с този пример вижте изображението по-горе.

1. В клетка D12 тип East и натиснете клавиша Enter на клавиатурата.
2. В клетка E12 тип <275 и натиснете клавиша Enter на клавиатурата ("<" е символ за по-малко от Excel).
3. Отговорът $ 119,719.00 трябва да се появи в клетка F12.
4. Само две записи в редове 3 и 4 отговарят на двата критерия и следователно само общите продажби за тези две записи се сумират от функцията.
5. Сумата от $ 49,017 и $ 70,702 е $ 119,719.
6. Когато кликнете върху клетка F12, пълната функция
   = Формулите (F3: F9, D3: D9, D12, E3: E9, E12) се появяват във формулата на лентата над работния лист .