Використання функції СУМЕСЛІМН в Excel її особливості приклади

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

Синтаксис СУМЕСЛІМН та поширені помилки

Аргументи функції СУМІСЛІМН:

1. Діапазон осередків для знаходження суми. Обов'язковий аргумент, де вказані дані для підсумовування.
2. Діапазон осередків для перевірки умови 1. Обов'язковий аргумент, до якого застосовується задана умова пошуку. Знайдені у цьому масиві дані сумуються у межах діапазону для підсумовування (першого аргументу).
3. Умова 1. Обов'язковий аргумент, який становить пару попереднього. Критерій, яким визначаються осередки для підсумовування в діапазоні умови 1. Умова може мати числовий формат, текстовий; "сприймає" математичні оператори. Наприклад, 45; « , = та інших.).


Приклади функції СУМЕСЛІМН в Excel

У нас є таблиця з даними про послуги клієнтам з різних міст з номерами договорів.

Припустимо, нам необхідно підрахувати кількість послуг у певному місті з урахуванням виду послуги.

Як використовувати функцію СУМІСЛІМН в Excel:

1. Викликаємо "Майстер функцій". У категорії «Математичні» знаходимо СУМІСЛІМН. Можна поставити в комірці знак "рівно" і почати вводити назву функції. Excel покаже список функцій, які мають у назві такий початок. Вибираємо необхідну подвійним клацанням миші або просто зміщуємо курсор стрілкою на клавіатурі вниз за списком і тиснемо клавішу TAB.
2. У нашому прикладі діапазон підсумовування – це діапазон осередків із кількістю наданих послуг. Як перший аргумент вибираємо стовпець «Кількість» (Е2:Е11).Назву стовпця не потрібно включати.
3. Перша умова, яку потрібно дотриматись при знаходженні суми, – певне місто. Діапазон осередків перевірки умови 1 – стовпець з назвами міст (С2:С11). Умова 1 – це назва міста, для якого необхідно підсумувати послуги. Припустимо, «Кемерово». Умова 1 – посилання на комірку з назвою міста (С3).
4. Для обліку виду послуг задаємо другий діапазон умов – стовпець «Послуга» (D2: D11). Умова 2 – це посилання певну послугу. Зокрема послугу 2 (D5).
5. Ось так виглядає формула з двома умовами для підсумовування: = Сумісний (E2: E11; C2: C11; C3; D2: D11; D5).

Результат розрахунку – 68.

Набагато зручніше для цього прикладу зробити випадаючий список для міст:

Тепер можна подивитися, скільки послуг 2 надано в тому чи іншому місті (а не тільки в Кемерово). Формулу трохи видозмінимо: = Сумісний ($ E $ 2: $ E $ 11; $ C $ 2: $ C $ 11; F $ 2; $ D $ 2: $ D $ 11; $ D $ 5).

Усі діапазони для підсумовування та перевірки умов необхідно закріпити (кнопка F4). Умова 1 – назва міста – посилання на перший осередок списку, що випадає. Посилання на умову 2 теж робимо постійним. Для перевірки зі списку міст оберемо «Кемерово»:

Результат той самий – 68.

За таким же принципом можна зробити список, що випадає для послуг.

• Створити таблицю
• Форматування
• Функції Excel
• Формули та діапазони
• Фільтр та сортування
• Діаграми та графіки
• Зведені таблиці
• Друк документів
• Бази даних та XML
• Можливості Excel
• Налаштування параметри
• Уроки Excel
• Макроси VBA
• Завантажити приклади

Функція СУМІСЛІМН

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

Синтаксис

СУМЕСЛІМН(діапазон_сумування; діапазон_умови1; умова1; [діапазон_умови2; умова2]; …)

• =СУМІСЛІМН(A2:A9; B2:B9; "=Я*"; C2:C9; "Артем")
• =СУМІСЛІМН(A2:A9; B2:B9; "<>Банани"; C2:C9; "Артем")

Діапазон_підсумовування (обов'язковий аргумент)

Діапазон комірок для підсумовування.

Діапазон_условія1 (обов'язковий аргумент)

Діапазон, у якому перевіряється Умова1.

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

Умова1 (обов'язковий аргумент)

Умова, яка визначає, які осередки підсумовуються в аргументі Діапазон_условія1. Наприклад, умови можуть бути введені в наступному вигляді: 32, ">32", B4, "яблука" або "32".

Диапазон_условия2, Условие2, … (Необов'язковий аргумент)

Додаткові діапазони та умови для них. Можна ввести до 127 пар діапазонів та умов.

Приклади

Щоб використати ці приклади в Excel, виділіть потрібні дані в таблиці, клацніть їх правою кнопкою миші та виберіть команду Копіювати. На новому аркуші клацніть правою кнопкою миші комірку A1 і в розділі Параметри вставки виберіть команду Використовувати формати кінцевих осередків.

Продана кількість

=СУМІСЛІМН(A2:A9; B2:B9; "=Я*"; C2:C9; "Артем")

Підсумовує кількість продуктів, назви яких починаються з Я та які були продані продавцем Артем. Підстановковий знак (*) у аргументі Умова1 ("=Я*") використовується для пошуку відповідних назв продуктів у діапазоні осередків, заданих аргументом Діапазон_условія1 (B2: B9). Крім того, функція виконує пошук імені "Артем" у діапазоні осередків, заданих аргументом Діапазон_условія2 (C2: C9). Потім функція підсумовує відповідні обох умов значення в діапазоні осередків, заданому аргументом Діапазон_підсумовування (A2: A9). Результат - 20.

=СУМІСЛІМН(A2:A9; B2:B9; "<>Банани"; C2:C9; "Артем")

Підсумовує кількість продуктів, які не є бананами та які були продані продавцем на ім'я Артем. За допомогою оператора <> в аргументі Умова1 з пошуку виключаються банани ("<>Банани"). Крім того, функція виконує пошук імені "Артем" у діапазоні осередків, заданих аргументом Діапазон_условія2 (C2: C9). Потім функція підсумовує відповідні обох умов значення в діапазоні осередків, заданому аргументом Діапазон_підсумовування (A2: A9). Результат - 30.

Поширені проблеми

Замість очікуваного результату відображається 0 (нуль).

Якщо виконується пошук текстових значень, наприклад імені людини, переконайтеся, що значення аргументів Умові1, 2 укладені в лапки.

Невірний результат повертається в тому випадку, якщо діапазон комірок, заданий аргументом Діапазон_підсумовування, містить значення ІСТИНА або БРЕХНЯ.

Значення ІСТИНА і БРЕХНЯ в діапазоні осередків, заданих аргументом Діапазон_підсумовування, Оцінюються по-різному, що може призводити до непередбачуваних результатів при їх підсумовуванні.

Осередки в аргументі Діапазон_підсумовування, яким присвоєно значення ІСТИНА, оцінюються як 1. Осередки, яким присвоєно значення брехня, оцінюються як 0 (нуль).

Рекомендації

Необхідні дії

Використання підстановочних знаків

Підстановочні знаки, такі як знак питання (?) або зірочка (*), в аргументах Умові1, 2 можна використовувати для пошуку подібних, але не збігаються значень.

Знак питання відповідає будь-якому окремо взятому символу. Зірочка – будь-якої послідовності символів. Якщо потрібно знайти саме знак питання або зірочку, слід ввести значок тильди (~) перед знаком питання.

Наприклад, формула =СУМЕСЛІМН(A2:A9; B2:B9; "=Я*"; C2:C9; "Арте?") буде підсумовувати всі значення з ім'ям, що починається на "Арті" і закінчується будь-якою літерою.

Відмінності між функціями СУМІСЛИ та СУМІСЛИМН

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

При копіюванні та зміні цих схожих формул слід стежити за правильним порядком аргументів.

Одинака кількість рядків і стовпців для аргументів, що задають діапазони осередків

Аргумент Діапазон_умови повинен мати таку ж кількість рядків та стовпців, що й аргумент Діапазон_підсумовування.

Додаткові відомості

Ви завжди можете поставити запитання експерту в Excel Tech Community або отримати підтримку у спільнотах.