Все одно певної суми. Приклади використання функції СУММЕСЛІ в excel

Для підсумовування значень, що задовольняють заданому критерію (умовою), використовується функція СУММЕСЛИ () , Англійська версія SUMIF ().

синтаксис функції

СУММЕСЛИ(Діапазон; Умова; [Діапазон_суммірованія])

Діапазон - діапазон комірок, в якому шукаються значення відповідні аргументу Умова. Діапазон може містити числа, дати, текстові значення або посилання на інші осередки. У разі, якщо інший аргумент - Діапазон_суммірованія - опущений, то аргумент Діапазон також є діапазоном, за яким проводиться підсумовування значень задовольняють аргументу Умова (в цьому випадку Діапазон повинен містити числа).

Умова - критерій у формі числа, виразу або тексту, що визначає, які комірки повинні підсумовуватися. Наприклад, аргумент Умова може бути виражений як 32, "яблука" або "\u003e 32".

Діапазон_суммірованія - діапазон комірок містить числа, які будуть підсумовані, в разі якщо відповідні їм осередки, зазначені в аргументі Діапазонвідповідають аргументу Умова. Діапазон_суммірованія - необов'язковий аргумент. Якщо він опущений, то підсумовування буде проводитися за діапазоном осередків, вказаною в аргументі Діапазон.

приклади

Розглянемо випадок, коли аргумент Діапазон_суммірованія опущений. В цьому випадку підсумовування буде проводитися за діапазоном осередків, зазначеного в першому аргументі Діапазон (Тобто він повинен містити числа). У ньому ж буде проводитись пошук значень відповідних аргументу Умова, Які потім і будуть підсумовані. Нехай це буде діапазон B5: B15 , См. Файл прикладу.

Вирішимо завдання:

знайти суму всіх чисел більше або рівних 10. Відповідь: 175. Формула: \u003d СУММЕСЛИ (B5: B15; "\u003e \u003d 10")
знайти суму всіх чисел менше або рівних 10. Відповідь: 42. Формула: \u003d СУММЕСЛИ (B5: B15; "<=10")
знайти суму всіх позитивних чисел в діапазоні. Формула: \u003d СУММЕСЛИ (B5: B15; "\u003e 0"). Альтернативний варіант з використанням функції СУММПРОИЗВ () виглядає так: \u003d СУММПРОИЗВ ((B5: B15) * (B5: B15\u003e 0))

форма завдання умови(Критерію) досить гнучка. Наприклад, у формулі \u003d СУММЕСЛИ (B5: B15; D7 & D8) критерій<=56 задан через ссылку D7&D8 : в D7 міститься текстове значення<=, а в D8 - число 56 (див. Малюнок нижче). Користувач, наприклад, може легко змінити критерій за допомогою в осередку D7 . еквівалентна формула \u003d СУММЕСЛИ (B5: B15; "<=56") или \u003d СУММЕСЛИ (B5: B15; "<="&56) або \u003d СУММЕСЛИ (B5: B15; "<="&D8) або \u003d СУММЕСЛИ (B5: B15; D7 & 56).

Функція СУММЕСЛИ використовується, якщо необхідно підсумувати значення діапазону, відповідні вказаному критерію.

опис функції

Функція СУММЕСЛИ використовується, якщо необхідно підсумувати значення діапазону, відповідні вказаному критерію. Наприклад, в стовпці з числами необхідно підсумувати тільки значення, що перевищують число 5. Для цього можна використовувати зазначену нижче формулу:
\u003d СУММЕСЛИ (B2: B25, "\u003e 5") В даному прикладі на відповідність критерію перевіряються підсумовувані значення. При необхідності умова можна застосувати до одного діапазону, а підсумувати відповідні значення з іншого діапазону. Наприклад, формула:
\u003d СУММЕСЛИ (B2: B5; "Іван"; C2: C5) підсумовує тільки ті значення з діапазону C2: C5, для яких відповідні значення з діапазону B2: B5 рівні "Іван".
Ще один приклад використання функції представлений на малюнку нижче:

Приклад використання СУММЕСЛИ для підрахунку прибутку з умовою

синтаксис

\u003d СУММЕСЛИ (діапазон; умова; [діапазон_суммірованія])

аргументи

діапазон умова діапазон_суммірованія

Обов'язковий аргумент. Діапазон осередків, які оцінюються за критеріями. Осередки в кожному діапазоні повинні містити числа, імена, масиви або посилання на числа. Порожні комірки та комірки, що містять текстові значення, пропускаються.

Обов'язковий аргумент. Умова в формі числа, виразу, посилання на осередок, тексту або функції, що визначає, які комірки необхідно підсумувати. Наприклад, умова може бути представлено в наступному вигляді: 32, "\u003e 32", B5, "32", "яблука" або СЬОГОДНІ ().

Важливо! Всі текстові умови і умови з логічними і математичними знаками необхідно укладати в подвійні лапки ( "). Якщо умовою є число, використовувати лапки не потрібно.

Необов'язковий аргумент. Осередки, значення з яких підсумовуються, якщо вони відрізняються від осередків, зазначених в якості діапазону. Якщо аргумент діапазон_суммірованія опущений, Microsoft Excel підсумовує осередку, зазначені в аргументі діапазон (ті ж осередку, до яких застосовується умова).

зауваження

В аргументі умова можна використовувати символи узагальнення: знак питання (?) І зірочку (*). Знак питання відповідає одному будь-якому символу, а зірочка - будь-якій послідовності символів. Якщо потрібно знайти безпосередньо знак питання (або зірочку), необхідно поставити перед ним знак "тильда" (~).
Функція СУММЕСЛИ повертає неправильні результати, якщо вона використовується для зіставлення рядків довше 255 символів з рядком # значить !.
Аргумент діапазон_суммірованія може не збігатися з розмірами з аргументом діапазон. При визначенні фактичних осередків, які підлягають підсумовування, в якості початкової використовується верхня ліва комірка аргументу діапазон_суммірованія, а потім сумуються осередку частини діапазону, що відповідає за розмірами аргументу діапазон.

Зробимо складання значень знаходяться в рядках, поля яких задовольняють відразу двом критеріям (Умова І). Розглянемо Текстові критерії, Числові і критерії в форматі Дат. Розберемо функцію СУММЕСЛІМН ( ), Англійська версія SUMIFS ().

В якості вихідної таблиці візьмемо таблицю з двома стовпцями (полями): текстовим « фрукти»І числовим« Кількість на складі»(Див. Файл прикладу).

Задача1 (1 текстовий критерій і 1 числовий)

Знайдемо кількість ящиків товару з певним фруктом І, в яких Залишок ящиків на складі не менше мінімального. Наприклад, кількість ящиків з товаром персики (комірка D 2 ), у яких залишок ящиків на складі\u003e \u003d 6 (комірка E 2 ) . Ми повинні отримати результат 64. Підрахунок можна реалізувати безліччю формул, наведемо кілька (див. Файл прикладу Лист Текст і Число):

1. =СУММЕСЛІМН (B2: B13; A2: A13; D2; B2: B13; "\u003e \u003d" & E2)

Синтаксис функції: СУММЕСЛІМН (інтервал_суммірованія; інтервал_условія1; условіе1; інтервал_условія2; условіе2 ...)

B2: B13 Інтервал_суммірованія - осередки для підсумовування, що включають імена, масиви або посилання, що містять числа. Порожні значення і текст ігноруються.
A2: A13і B2: B13 Інтервал_условія1; інтервал_условія2; ... являють собою від 1 до 127 діапазонів, в яких перевіряється відповідне умова.
D2і "\u003e \u003d" & E2 Условіе1; условіе2; ... являють собою від 1 до 127 умов у вигляді числа, виразу, посилання на клітинку або тексту, що визначають, які клітинки будуть підсумовані.

Порядок аргументів різний у функціях СУММЕСЛІМН () і СУММЕСЛИ (). У СУММЕСЛІМН () аргумент інтервал_суммірованія є першим аргументом, а в СУММЕСЛИ () - третім. При копіюванні і редагуванні цих схожих функцій необхідно стежити за тим, щоб аргументи були зазначені в правильному порядку.

2. інший варіант \u003d СУММПРОИЗВ ((A2: A13 \u003d D2) * (B2: B13); - (B2: B13\u003e \u003d E2))
Розберемо докладніше використання функції СУММПРОИЗВ ():

Результатом обчислення A2: A13 \u003d D2 є масив (БРЕХНЯ: БРЕХНЯ: БРЕХНЯ: БРЕХНЯ: ІСТИНА: ІСТИНА: ІСТИНА: БРЕХНЯ: БРЕХНЯ: БРЕХНЯ: БРЕХНЯ: БРЕХНЯ) Значення ІСТИНА відповідає збігу значення з шпальти А критерієм, тобто слову персики. Масив можна побачити, виділивши в A2: A13 \u003d D2, а потім натиснувши;
Результатом обчислення B2: B13 є (3: 5: 11: 98: 4: 8: 56: 2: 4: 6: 10: 11), тобто просто значення з шпальти B ;
Результатом поелементного множення масивів (A2: A13 \u003d D2) * (B2: B13) є (0: 0: 0: 0: 4: 8: 56: 0: 0: 0: 0: 0). При множенні числа на значення БРЕХНЯ виходить 0; а на значення ІСТИНА (\u003d 1) виходить саме число;
Розберемо друга умова: Результатом обчислення - (B2: B13\u003e \u003d E2) є масив (0: 0: 1: 1: 0: 1: 1: 0: 0: 1: 1: 1). Значення в стовпці « Кількість ящиків на складі», Які відповідають критеріям\u003e \u003d E2 (тобто\u003e \u003d 6) відповідають 1;
Далі, функція СУММПРОИЗВ () попарно перемножує елементи масивів і підсумовує отримані твори. Отримуємо - 64.

3. Іншим варіантом використання функції СУММПРОИЗВ () є формула \u003d СУММПРОИЗВ ((A2: A13 \u003d D2) * (B2: B13) * (B2: B13\u003e \u003d E2)).

При необхідності дати можуть бути введені безпосередньо в формулу \u003d СУММЕСЛІМН (B6: B17; A6: A17; "\u003e \u003d 15.01.2010"; A6: A17; "<=06.03.2010")

Щоб вивести умови відбору в текстовому рядку використовуючи формулу \u003d "Обсяг продажів за період з" & ТЕКСТ (D6; "дд.мм.гг") & "по" & ТЕКСТ (E6; "дд.мм.гг")

В останній формулі використаний.

Задача4 (Місяць)

Трохи модифікуємо умова попередньої задачі: знайдемо сумарні продажів за місяць (див. Файл прикладу лист Місяць).

Формули будуються аналогічно завданню 3, але користувач вводити не 2 дати, а назва місяця (передбачається, що в таблиці дані в рамках 1 року).

Для вирішення 3-й завдання таблиця з налаштованим Автофільтри виглядає так (див. Файл прикладу Лист 2 Дати).

Попередньо таблицю потрібно перетворити в і включити рядок Підсумків.

Добрий день, шановні передплатники і відвідувачі блога сайт. Зовсім недавно ми розібралися з формулою ВВР, і відразу навздогін я вирішив написати про іншу дуже корисної функції Excel - СУММЕСЛИ. Обидві ці функції вміють «пов'язувати» дані з різних джерел (таблиць) по ключовому полю і при деяких умовах є взаємозамінними. У той же час є і суттєві відмінності в їх призначенні і використанні.

Якщо призначення ВВР в тому, щоб просто «підтягнути» дані з одного місця Excel в інше, то СУММЕСЛИ використовують для того, щоб числові дані підсумувати по заданому критерію. Основне призначення цієї формули - підсумовування даних відповідно до деякого умовою. Приблизно так написано в довідці. Однак цей інструмент згодиться і для інших цілей. Я, наприклад, формулу СУММЕСЛИ часто використовую замість ВПР, так як вона не видає помилок # Н / Д, коли щось не може відшукати. А це здорово полегшує і прискорює роботу.

Функцію СУММЕСЛИ можна успішно пристосувати для вирішення найрізноманітніших завдань. Тому ми в цій статті розглянемо Не 1 (один), а цілих 2 (два) приклад. Перший буде пов'язаний з підсумовуванням по заданому критерію, другий - з «підтягуванням» даних, тобто в якості альтернативи ВВР.

Приклад підсумовування з використанням функції СУММЕСЛИ

У таблиці вказані позиції, їх кількості, а також належність до тієї чи іншої групи товарів (перший стовпець). Розглянемо поки спрощене використання СУММЕСЛИ, коли нам потрібно порахувати суму тільки за тими позиціями, значення за якими відповідають деякому умові. Наприклад, ми хочемо дізнатися, скільки було продано топових позицій, тобто тих, значення яких перевищує 70 од. Шукати такі товари очима, а потім підсумувати вручну не дуже зручно і швидко, тому функція СУММЕСЛИ тут дуже доречна.

Насамперед виділяємо клітинку, де буде підрахована сума. Далі викликаємо Майстра функцій. це значок fx в рядку формул. Далі шукаємо в списку функцію СУММЕСЛИ і натискаємо на неї. Відкривається діалогове вікно, де для вирішення саме цієї задачі потрібно заповнити всього два (перші) поля з трьох запропонованих.

Тому я і назвав такий приклад спрощеним. Чому 2 (два) з 3 (трьох)? Тому що наш критерій знаходиться в самому діапазоні підсумовування.

В поле «Діапазон» вказується та область таблиці Excel, де знаходяться всі вихідні значення, з яких потрібно щось відібрати і потім скласти. Здається зазвичай за допомогою мишки.

В поле «Критерій» вказується то умова, за яким формула буде проводити відбір. У нашому випадку ми вказуємо «\u003e 70». Якщо не поставити лапки, то вони потім самі Доріс. Але взагалі подібний критерій повинен бути в лапках.

Останнє поле «Дапазон_суммірованія" не заповнюємо, так як він вже вказано в першому полі.

Таким чином, функція СУММЕСЛИ бере критерій і починає відбирати все значення з зазначеного діапазону, що задовольняють заданому критерію. Після цього всі відібрані значення складаються. Ось так працює алгоритм функції.

Заповнивши в Майстрі функцій необхідні поля, натискаємо на клавіатурі великою кнопку «Enter», або в віконці Майстри тиснемо мишкою на «Ок». За сім дійством на місці введеної функції має з'явитися розраховане значення. У моєму прикладі вийшло 224шт. Тобто сумарне значення проданих товарів в кількості більше 70 штук склало 224шт. (Це видно в нижньому лівому кутку вікна Майстра ще до натискання «ок»). От і все. Це був спрощений приклад, коли критерій і діапазон підсумовування знаходяться в одному місці.

Тепер давайте розглянемо, приклад, коли критерій не збігається з діапазоном підсумовування. Така ситуація зустрічається набагато частіше. Розглянемо ті ж умовні дані. Нехай нам потрібно дізнатися суму не більше або менше якогось значення, а суму конкретної групи товарів, припустимо, групи Г.

Для цього знову виділяємо клітинку з майбутнім підрахунком суми і викликаємо Майстра функцій. Тепер в першому віконці вказуємо діапазон, де міститься критерій, за яким буде проводитися відбір для підсумовування, тобто в нашому випадку це стовпець з назвами груп товарів. Далі сам критерій прописуємо або вручну, залишивши в відповідному полі запис «група Г», або просто вказуємо мишкою осередок з потрібним критерієм. Останнє віконце - це діапазон, де знаходяться підсумовувані дані. Його вказуємо мишкою, але любителям друк не забороняється набрати вручну.

Результатом буде сума проданих товарів з групи Г - 153шт.

Отже, ми подивилися, як розрахувати одну суму по одному конкретному критерію. Однак частіше виникає завдання, коли потрібно розрахувати кілька сум для декількох критеріїв. Немає нічого простіше! Наприклад, нам потрібно дізнатися суми проданих товарів по кожній групі. Тобто цікавить 4 (чотири) значення по 4-м (чотирьом) групам (А, Б, В і Г). Для цього зазвичай десь на просторах Excel робиться список груп у вигляді окремої таблички. Ясна річ, що назви груп повинні в точності збігатися з назвами груп у вихідній таблиці. Відразу додамо підсумкову сходинку, де сума поки дорівнює нулю.

Потім прописується формула для першої групи і простягається на всі інші. Тут тільки треба звернути увагу на відносність посилань. Діапазон з критеріями і діапазон підсумовування повинні бути абсолютним посиланнями, щоб при протягуванні формули вони не «поїхали вниз», а ось сам критерій, по-перше потрібно вказати мишкою (а не прописати вручну), по-друге, повинен бути відносної посиланням, так як кожна сума має свій критерій підсумовування.

Заповнені поля Майстра функцій при подібному розрахунку будуть виглядати приблизно так.

Як видно, для першої групи А сума проданих товарів склала 161шт (нижній лівий кут малюнка). Тепер натискаємо Ентер і простягаємо формулу вниз.

Всі суми розрахувалися, а їх загальний підсумок дорівнює 535, що збігається з підсумком у вихідних даних. Значить, все значення підсумувати, нічого не пропустили. Останній варіант використання СУММЕСЛИ мені особисто зустрічається досить-таки часто.

Приклад використання функції СУММЕСЛИ для зіставлення даних

Як я вже неодноразово зазначав, функцію СУММЕСЛИ можна толково використовувати для зв'язки даних. Дійсно, якщо підсумувати одне значення, то вийде саме це значення. Дане арифметичне властивість не гріх використовувати на благо. Можливо навіть, що конструктори Excel і не знають про такий варварський використанні їхнього дітища.

Коротше, СУММЕСЛИ легко пристосувати для зв'язки даних як альтернативу функції ВПР. Навіщо використовувати СУММЕСЛИ, якщо існує ТВП? Пояснюю. По-перше, СУММЕСЛИ на відміну від ВПР нечутлива до формату даних і не видає помилку там, де її найменше чекаєш; по-друге, СУММЕСЛИ замість помилок через відсутність значень по заданому критерію видає 0 (нуль), що дозволяє без зайвих рухів тіла підраховувати підсумки діапазону з формулою СУММЕСЛИ. Однак є і один мінус. Якщо в шуканої таблиці будь-якої критерій повториться, то відповідні значення підсумуйте, що не завжди є «підтягування». Краще бути насторожі. З іншого боку часто це і потрібно - підтягнути значення в заданий місце, а задублірованние позиції при цьому скласти. Потрібно просто знати властивості функції СУММЕСЛИ і використовувати згідно з інструкцією по експлуатації.

Тепер розглянемо приклад того, як функція СУММЕСЛИ виявляється більш придатною для підтягування даних, ніж ВВР. Нехай дані з прикладу ваше - це продажу деяких товарів за січень. Ми хочемо дізнатися, як вони змінилися в лютому. Порівняння зручно зробити в цій же табличці, попередньо додавши ще один стовпець праворуч і заповнивши його даними за лютий. Десь в іншому екселевскій файлі є статистика за лютий по всьому асортименту, але нам хочеться проаналізувати саме ці позиції, для чого потрібно з великого файлу зі статистикою продажів всіх товарів підтягти потрібні значення в нашу табличку. Для початку давайте спробуємо скористатися формулою ВВР. В якості критерію будемо використовувати код товару. Результат на малюнку.

Чітко видно, що одна позиція не підтяглася, і замість числового значення видається помилка # Н / Д. Швидше за все, в лютому цей товар просто не продавався і тому він відсутній в базі даних за лютий. Як наслідок помилка # Н / Д показується і в сумі. Якщо позицій не багато, то проблема не велика, досить вручну видалити помилку і сума буде коректно перерахована. Однак кількість рядків може вимірюватися сотнями, і розраховувати на ручну коригування не зовсім вірне рішення. Тепер скористаємося формулою СУММЕСЛИ замість ВПР.

Результат той же, тільки замість помилки # Н / Д СУММЕСЛИ видає нуль, що дозволяє нормально розрахувати суму (або інший показник, наприклад, середню) в підсумковому рядку. Ось це і є основна ідея, чому СУММЕСЛИ іноді слід використовувати замість ВПР. При великій кількості позицій ефект буде ще більш відчутним.

У реальності часто доводиться працювати з великими масивами даних, які багаторазово перевищують розміри монітора. Побачити і відстежити помилки дуже важко. Зате все косяки спливають в подальших розрахунках. Такі приклади не зручно розбирати за допомогою скріншотів в статті, тому я запрошую подивитися спеціально записаний відеурок про функції СУММЕСЛИ.

А ось ще один урок по СУММЕСЛИ