Комбіноване використання sumif (vlookup)
Sumif з VLOOKUP - це комбінація двох різних умовних функцій, SUMIF використовується для підсумовування комірок на основі якоїсь умови, яка бере аргументи діапазону, що має дані, а потім критерії або умову та комірки для підсумовування, замість критеріїв, які ми використовуємо VLOOKUP як критерій, коли в кількох стовпцях доступний великий обсяг даних.
SUMIF - це функція, представлена в Excel з версії 2007 року для підсумовування різних значень за критеріями. VLOOKUP - одна з найкращих формул для збору даних з інших таблиць. Коли є кілька умов і стовпців, для виконання кількох обчислень на аркуші Excel використовується sumif (vlookup). Недолік функції SUMIF, що повертає єдине число, долається за допомогою VLOOKUP. VLOOKUP допомагає повернути будь-які дані з таблиці на основі відповідних критеріїв.
Пояснення
Функція SUMIF: Функція тригонометрії та математики підсумовує значення, коли встановлена умова відповідає дійсності. Сумарне значення отримують, виходячи лише з одного критерію.
Коли ми маємо справу з функцією SUMIF в Excel, використовується наступна формула
- Діапазон: Це діапазон клітин, що використовується для оцінки встановлених критеріїв
- Критерії: Це умова підсумовування значень. Це може бути посилання на клітинку, номер та інша функція Excel. Коли ми хочемо поєднати SUMIF і VLOOKUP, функція vlookup буде введена замість критеріїв
- Діапазон суми: Це діапазон комірок, вказаний для підсумовування числових значень.
Тепер формула змінена на
Формула = SUMIF (Діапазон, Vlookup (значення пошуку, масив таблиці, номер індексу стовпця, [пошук діапазону]), [діапазон суми])
- Значення пошуку: воно визначає значення, яке потрібно шукати в таблиці. Це може бути посилання або значення.
- Масив таблиці: Це діапазон таблиці, що містить два або більше двох стовпців.
- Номер індексу стовпця: Це відносний індекс стовпця, який потрібно вказати для повернення необхідних даних із конкретного стовпця.
- [Пошук діапазону]: значення 0 або 1 визначає, чи слід повертати точне значення, або приблизне значення. Але це не обов’язково для користувача. 0 означає точну відповідність, а 1 - приблизну відповідність.
Як використовувати SUMIF з функцією VLOOKUP?
Комбіноване використання sumif (vlookup) корисно для пошуку даних на основі єдиних критеріїв. Вони багато в чому використовуються в Excel для обчислення за допомогою пошуку даних. Вони колективно використовуються в діловому середовищі для виконання різних завдань для прийняття правильних рішень. Щоб ефективно використовувати ці функції разом,
По-перше, функцію SUMIF слід ввести за допомогою двох методів.
Перший спосіб: Формулу потрібно вводити з клавіатури, як показано на малюнку нижче.
Другий спосіб: функцію SUMIF можна вставити з вкладки «Формула», як показано на зображенні.
Після введення функції SUMIF формула VLOOKUP вводиться всередину функції SUMIF, замінюючи елемент «Критерії». Усі параметри VLOOKUP, включаючи значення пошуку, масив таблиці, номер індексу стовпця та пошук діапазону. Вони повинні бути вкладені в дужки, щоб уникнути помилок з формулою. Діапазон значень, що підсумовуються, включається в елементи діапазону суми функції SUMIF. Нарешті, клавіші CTRL, SHIFT та ENTER натиснули, щоб полегшити значення у вигляді масиву.
Приклади
Завантажити цей шаблон SUMIF With VLOOKUP Excel можна тут - SUMIF With VLOOKUP Excel TemplateПриклад №1 - Використання sumif (vlookup) разом для визначення деякого значення
Цей приклад показує, як використовувати суміф (vlookup) разом, щоб знайти суму продажів за той самий місяць у різні роки. Наступні дані розглядаються для цього прикладу, як показано на скріншоті.
Розглянута таблиця підстановки показана, як зазначено нижче. Він включав референтні значення за місяці з січня по грудень.
Після введення даних в основну таблицю та таблицю пошуку, функція SUMIF використовується для визначення загального обсягу продажів, згенерованих у різні місяці року. Тут значенням підстановки вважається місяць. Формула, що поєднує суміф (vlookup), відображається як,
Загальний обсяг продажів у січневому місяці визначається як 17263,3 . Коли ми змінили значення зовнішнього вигляду на інший місяць, генеруються відповідні загальні продажі.
Приклад №2 - Визначення суми на основі критеріїв відповідності в різних робочих аркушах
У цьому прикладі таблиця пошуку та основна таблиця беруться на різних аркушах, а не на одному аркуші. Дані таблиці пошуку наведені на знімку екрана нижче.
Дані основної таблиці показані на скріншоті нижче.
Щоб визначити загальний обсяг продажів, ім’я продавця приймається як значення підстановки, а ідентифікатори працівника використовуються для довідкових цілей. Формула вводиться, як представлено нижче, і порівняно з першим прикладом у формулі спостерігається мало змін.
У цьому прикладі замість вибору масиву підстановки згадується лише Lookup_table. Натисканням трьох клавіш, включаючи CTRL, SHIFT та ENTER, отримуються точні результати.
Коли ім’я продавця змінюється на імена, згадані у підсумку, сума продажів змінюється і дає новий результат.
Переваги
Нижче наведено переваги використання цих функцій.
- Ми можемо легко витягти значення з іншої таблиці для виконання обчислень.
- Визначення суми цінностей, представлених у діапазоні, який відповідає критеріям, згаданим у різних аспектах бізнесу.
Що слід пам’ятати
- Під час використання функції VLOOKUP номер індексу стовпця не повинен бути нижче 1, щоб уникнути помилок.
- Індекси слід подавати до стовпців таблиці пошуку, вказуючи цифрами 1, 2, 3 тощо.
- Замість введення ключа слід використовувати CTRL + SHIFT + ENTER у Excel, оскільки VLOOKUP вводиться як формула масиву.
- Визначення двох таблиць, включаючи Main та Lookup, потрібно для вилучення значень та визначення суми значень масиву.
- SUMIF забезпечує точні результати лише для числових даних, для інших типів даних він не працюватиме.