У цій статті я розповім Вам про три способи здійснення цього завдання.

1. ИІНДЕКС (INDEX)

Ця функція дозволяє вибрати значення з діапазону осередків за номером рядка та стовпця.

Аргументи функції виглядають так

:

Масив – посилання на діапазон осередків, у якому нам потрібно здійснити пошук;

Номер_рядка - рядок, з якого потрібно вибрати значення;

Номер_стовпця - стовпець, з якого потрібно вибрати значення.

Функція ИНДЕКС також може отримувати значення з різних виділених областей, але в цій статті я не зупинятимуся на цій можливості.

Схематично роботу функції можна зобразити так:

Для автоматичного пошуку рядка/стовпця функцію ИНДЕКС, як правило, доповнюють двома функціями ПОИСКПОЗ (MATCH).

У такому разі формула виглядає приблизно так:

=ИНДЕКС($C$7:$F$9;ПОИСКПОЗ($I$5;$B$7:$B$9;0);ПОИСКПОЗ($I$6;$C$6:$F$6;0))

При зміні параметрів пошуку, значення комірки, що перетинається, буде змінюватися автоматично.

2. СУМПРОВИЗВ (SUMPRODUCT)

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

Для вирішення нашого завдання формула виглядатиме так:

=СУММПРОИЗВ((C6:F6=I6)*(B7:B9=I5)*(C7:F9))

Простими словами синтаксис функції :

=СУММПРОИЗВ((условие_1)*(условие_2)*(что_суммировать))

Сума множень 0, 1 та значень таблиці витягне потрібне нам значення.

3. ДВССЫЛ (INDIRECT)

Третій спосіб, який особисто мені дуже подобається своєю простотою. Єдина умова для його роботи – назви в стовпцях та рядках мають бути без пробілів (їх можна видалити або замінити на нижнє підкреслення «_»).

Функція ДВССЫЛ повертає посилання, задане текстовим рядком. Посилання негайно обчислюються для виведення вмісту. Функція ДВССЫЛ використовується, якщо потрібно змінити посилання на комірку у формулі без зміни самої формули.

Якщо на перший погляд функція виглядає простою та малокорисною, то при подальшому її вивченні Ви дізнаєтеся, що з її допомогою ми можемо переміщатися по аркушах, транспонувати таблиці, відбирати парні (непарні) рядки та багато іншого. Адже для неї аргумент Посилання_на_осередок – лише текстовий рядок, який можна змінювати формулами.

Щоб знайти значення комірки на перетині певних рядків і стовпця, нам знадобиться відразу дві функції ДВССЫЛ.

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

Виділяємо стовпці із заголовками та натискаємо комбінацію клавіш Ctrl+Shift+F3

Аналогічну операцію робимо і для рядків.

Сама формула матиме такий вигляд:

=ДВССЫЛ(I5) ДВССЫЛ(I6)

Зверніть увагу, що функції розділені пробілом.

Excel надає можливість використовувати кілька варіантів рішення для однієї задачи. Там, де неможливо застосувати один спосіб, практично завжди є можливість підібрати інший. Дуже рекомендую Вам вивчити функції, розглянуті в цій статті, та Ваша робота з даними, стане набагато ефективнішою.

Ще більше про Excel — на нашому курсі

Автор: Михайло Беленчук, тренер DATA bi

Дізнайтеся більше про повну програму "EXCEL: бізнес-аналіз і прогнозування"