Завантажте зразок робочої книги
У цьому підручнику буде показано, як підсумувати результати кількох функцій VLOOKUP за один крок у Excel та Google Таблицях.
Використання SUM з функцією VLOOKUP
Функцію VLOOKUP можна використовувати для пошуку одного значення, але ви також можете шукати та підсумовувати кілька значень, вкладаючи функцію VLOOKUP всередині функції SUM.
У цьому прикладі буде показано, як обчислити Загальний дохід від продажу конкретного Магазин більше 3 місяців за допомогою функції масиву з SUM та VLOOKUP:
| 1 | {= SUM (VLOOKUP (P3, B3: N6, {2,3,4}, FALSE))} |
Це еквівалентно використанню наступних 3 звичайних функцій VLOOKUP для підсумовування доходів за місяці січень, лютий та березень.
| 1 | = VLOOKUP (P3, B3: N6,2, FALSE)+VLOOKUP (P3, B3: N6,3, FALSE)+VLOOKUP (P3, B3: N6,4, FALSE) |
Ми можемо об’єднати ці функції разом, виконавши такі дії:
По -перше, ми встановили функцію VLOOKUP для повернення стовпців 2, 3 і 4 як вихідних даних масиву:
| 1 | = VLOOKUP (P3, B3: N6, {2,3,4}, FALSE) |
Це дасть результат масиву:
| 1 | {98, 20, 76} |
Далі, щоб підсумувати результат масиву разом, ми використовуємо функцію SUM.
Важливо! Якщо ви використовуєте Excel версії 2022 або раніше, вам потрібно ввести формулу, натиснувши CTRL + SHIFT + ENTER, щоб створити формулу масиву. Ви дізнаєтесь, що зробили це правильно, коли фігурні дужки з’являться навколо формули. Це не потрібно в Excel 365 (або новіших версіях Excel).
Використання більших розмірів масивів у функції VLOOKUP
Ми можемо збільшити розмір вхідного масиву, щоб представити більше даних. У наступному прикладі буде обчислено Загальний дохід від продажу конкретного Магазин протягом 12 місяців за допомогою функції масиву, що містить функцію SUM, щоб об’єднати 12 застосувань функції VLOOKUP в одну клітинку.
| 1 | {= SUM (VLOOKUP (P3, B3: N6, {2,3,4,5,6,7,8,9,10,11,12,13}, НЕВІРНО))} |
Інші підсумкові функції та VLOOKUP
Інші підсумкові функції можна використовувати так само, як і функцію SUM для створення альтернативних зведенних статистичних даних. Наприклад, ми можемо використовувати функції MAX, MIN, AVERAGE, MEDIAN, SUM та COUNT для узагальнення Виручка від продажів з січня по березень:
| 1 | = MAX (VLOOKUP (J3, B3: H6, {2,3,4}, FALSE)) |
| 1 | = MIN (VLOOKUP (J3, B3: H6, {2,3,4}, FALSE)) |
| 1 | = СЕРЕДНЯ (VLOOKUP (J3, B3: H6, {2,3,4}, FALSE)) |
| 1 | = СЕРЕДНЯ (VLOOKUP (J3, B3: H6, {2,3,4}, FALSE)) |
| 1 | = SUM (VLOOKUP (J3, B3: H6, {2,3,4}, FALSE)) |
| 1 | = COUNT (VLOOKUP (J3, B3: H6, {2,3,4}, FALSE)) |
Блокування посилань на клітинки
Щоб полегшити читання наших формул, ми показали формули без заблокованих посилань на клітинки:
| 1 | = SUM (VLOOKUP (P3, B3: N6, {2,3,4}, FALSE)) |
Але ці формули не працюватимуть належним чином при копіюванні та вставці в інше місце у файлі. Замість цього, ви повинні використовувати заблоковані посилання на клітинки, як це:
| 1 | {= SUM (VLOOKUP (P3, $ B $ 3: $ N $ 6, {2,3,4}, FALSE))} |
Прочитайте нашу статтю про блокування посилань на клітинки, щоб дізнатися більше.
Використання SUM з функцією VLOOKUP у Таблицях Google
Ці формули працюють так само в Google Таблицях, як і в Excel, за винятком того, що функцію ARRAYFORMULA потрібно використовувати в Таблицях Google для правильної оцінки результатів. Це можна автоматично додати, натиснувши клавіші CTRL + SHIFT + ENTER під час редагування формули.
| 1 | =ArrayFormula(SUM(VLOOKUP(O2,А2: М5,{2,3,4},ПОМИЛКОВИЙ))) |
