У цьому уроці Ви знайдете кілька цікавих прикладів, які демонструють як використовувати функцію ВПР (VLOOKUP) разом з СУММ (SUM) або СУММЕСЛИ (SUMIF) в Excel, щоб виконувати пошук і підсумовування значень по одному або декільком критеріям.
Завдання можуть відрізнятися, але їх зміст однаковий - необхідно знайти і підсумувати значення по одному або декільком критеріям в Excel. Що це за значення? Будь-які числові. Що це за критерії? Будь-які ... Починаючи з числа або посилання на осередок, що містить потрібне значення, і закінчуючи логічними операторами і результатами формул Excel.
ВПР і СУМ в Excel - обчислюємо розмір знайденої співпадаючих значень
Якщо Ви працюєте з числовими даними в Excel, то досить часто Вам доводиться не тільки отримувати пов'язані дані з іншої таблиці, але і підсумовувати декілька стовпців або рядків. Для цього Ви можете комбінувати функції СУММ і ВПР. як це показано нижче.
Припустимо, що у нас є список товарів з даними про продажі за кілька місяців, з окремим стовпцем для кожного місяця. Джерело даних - лист Monthly Sales:
Тепер нам необхідно зробити таблицю підсумків з сумами продажів по кожному товару.
Вирішення цього завдання - використовувати масив констант в аргументі col_index_num (номер_стовпчика) функції ВПР. Ось приклад формули:
= SUM (VLOOKUP (lookup value, lookup range,, FALSE))
= СУМ (ВВР (шукане_значення; таблиця ;; БРЕХНЯ))
Як бачите, ми використовували масив для третього аргументу, щоб виконати пошук кілька разів в одній функції ВПР. і отримати суму значень в стовпцях 2. 3 і 4.
Тепер давайте застосуємо цю комбінацію ВВР і СУМ до даних в нашій таблиці, щоб знайти загальну суму продажів в шпальтах з B по M:
Важливо! Якщо Ви вводите формулу масиву, то обов'язково натисніть комбінацію Ctrl + Shift + Enter замість звичайного натискання Enter. Microsoft Excel укладе Вашу формулу в фігурні дужки:
Якщо ж обмежитися простим натисканням Enter. обчислення буде зроблено тільки за першим значенням масиву, що призведе до невірного результату.
Можливо, Вам стало цікаво, чому формула на малюнку вище відображає [@Product]. як шукане значення. Це відбувається тому, що мої дані були перетворені в таблицю за допомогою команди Table (Таблиця) на вкладці Insert (Вставка). Мені зручніше працювати з повнофункціональними таблицями Excel, ніж з простими діапазонами. Наприклад, коли Ви вводите формулу в одну з комірок, Excel автоматично копіює її на весь стовпець, що економить кілька дорогоцінних секунд.
Як бачите, використовувати функції ВПР і СУМ в Excel досить просто. Однак, це далеко не ідеальне рішення, особливо, якщо доводиться працювати з великими таблицями. Справа в тому, що використання формул масиву може уповільнити роботу додатку, так як кожне значення в масиві робить окремий виклик функції ВПР. Виходить, що чим більше значень в масиві, тим більше формул масиву в робочій книзі і тим повільніше працює Excel.
Цю проблему можна подолати, використовуючи комбінацію функцій INDEX (ЗМІСТ) і MATCH (ПОИСКПОЗ) замість VLOOKUP (ВВР) і SUM (СУМ). Далі в цій статті Ви побачите кілька прикладів таких формул.
Виконуємо інші обчислення, використовуючи функцію ВПР в Excel
Тільки що ми розібрали приклад, як можна витягти значення з кількох стовпців таблиці і обчислити їх суму. Таким же чином Ви можете виконати інші математичні операції з результатами, які повертає функція ВПР. Ось кілька прикладів формул:
Обчислюємо середнє:
Формула шукає значення з комірки A2 на аркуші Lookup table і обчислює середнє арифметичне значень, які знаходяться на перетині знайденої рядки і стовпців B, C і D.
Знаходимо максимум:
Формула шукає значення з комірки A2 на аркуші Lookup table і повертає максимальне з значень, які знаходяться на перетині знайденої рядки і стовпців B, C і D.
Знаходимо мінімум:
Формула шукає значення з комірки A2 на аркуші Lookup table і повертає мінімальне зі значень, які знаходяться на перетині знайденої рядки і стовпців B, C і D.
Обчислюємо% від суми:
Формула шукає значення з комірки A2 на аркуші Lookup table. потім підсумовує значення, які перебувають на перетині знайденої рядки і стовпців B, C і D, і лише потім обчислює 30% від суми.
Якщо ми додамо перераховані вище формули в таблицю з попереднього прикладу. результат буде виглядати так:
Давайте розберемо приклад, щоб Вам стало зрозуміліше, про що йде розмова. Припустимо, у нас є таблиця, в якій перераховані імена клієнтів, куплені товари та їх кількість (таблиця Main table). Крім цього, є друга таблиця, яка містить ціни товарів (таблиця Lookup table). Наше завдання - написати формулу, яка знайде суму всіх замовлень заданого клієнта.
Так як це формула масиву, не забудьте натиснути комбінацію Ctrl + Shift + Enter при завершенні введення.
Lookup table - це назва листа, де знаходиться проглядається діапазон.
Давайте проаналізуємо складові частини формули, щоб Ви розуміли, як вона працює, і могли налаштувати її під свої потреби. Функцію СУМ поки залишимо осторонь, так як її мета очевидна.
Так як наша формула - це формула масиву, вона повторює описані вище дії для кожного значення в масиві пошуку. На завершення, функція СУММ обчислює суму значень, одержані в результаті множення. Зовсім не складно, Ви згодні?
ВПР і СУММЕСЛИ - знаходимо і підсумовуємо значення, що задовольняють певному критерію
Функція СУММЕСЛИ (SUMIF) в Excel схожа на СУММ (SUM), яку ми тільки що розбирали, оскільки вона теж підсумовує значення. Різниця лише в тому, що СУММЕСЛИ підсумовує тільки ті значення, які задовольняють заданому Вами критерієм. Наприклад, найпростіша формула з СУММЕСЛИ:
- підсумовує всі значення осередків в діапазоні A2: A10. які більше 10.
Дуже просто, правда? А тепер давайте розглянемо трохи складніший приклад. Припустимо, що у нас є таблиця, в якій перераховані імена продавців і їх номери ID (Lookup table). Крім цього, є ще одна таблиця, в якій ті ж ID пов'язані з даними про продажі (Main table). Наше завдання - знайти суму продажів для заданого продавця. Тут є 2 обтяжуючих обставини:
- Основна таблиця (Main table) містить безліч записів для одного ID у випадковому порядку.
- Ви не можете додати стовпець з іменами продавців до основної таблиці.
Давайте запишемо формулу, яка знайде всі продажі, зроблені заданим продавцем, а також підсумує знайдені значення.
Перед тим, як ми почнемо, дозвольте нагадати Вам синтаксис функції СУММЕСЛИ (SUMIF):
- range (діапазон) - аргумент говорить сам за себе. Це просто діапазон комірок, які Ви хочете оцінити заданим критерієм.
- criteria (критерій) - умова, яке говорить формулою, які значення підсумовувати. Може бути числом, посиланням на осередок, виразом або іншою функцією Excel.
- sum_range (діапазон_суммірованія) - необов'язковий, але дуже важливий для нас аргумент. Він визначає діапазон пов'язаних осередків, які будуть підсумовуватися. Якщо він не вказаний, Excel підсумовує значення осередків, в першому аргументі функції.
Зібравши всі воєдино, давайте визначимо третій аргумент для нашої функції СУММЕСЛИ. Як Ви пам'ятаєте, ми хочемо підсумувати всі продажі, вчинені певним продавцем, чиє ім'я задано в осередку F2 (дивіться малюнок, наведений вище).
- range (діапазон) - так як ми шукаємо по ID продавця, значеннями цього аргументу будуть значення в стовпці B основної таблиці (Main table). Можна задати діапазон B: B (весь стовпець) або, перетворивши дані в таблицю, використовувати ім'я стовпця Main_table [ID].
criteria (критерій) - так як імена продавців записані в переглядається таблиці (Lookup table), використовуємо функцію ВПР для пошуку ID. відповідного заданому продавцю. Ім'я записано в комірці F2, тому для пошуку використовуємо формулу:
Звичайно, Ви могли б ввести ім'я як шукане значення безпосередньо в функцію ВПР. але краще використовувати абсолютне посилання на комірку, оскільки так ми створюємо універсальну формулу, яка буде працювати для будь-якого значення, введеного в цей осередок.
Все, що Вам залишилося зробити, це з'єднати частини в одне ціле, і формула СУММЕСЛИ + ВПР буде готова:
