Знайдіть і отримайте весь стовпець
Щоб знайти та отримати весь стовпець, зіставивши певне значення, an ІНДЕКС та MATCH формула зробить вам послугу.
Знайдіть і отримайте весь стовпець на основі певного значення
Підсумуйте весь стовпець на основі певного значення
Подальший аналіз цілого стовпця на основі певного значення
Знайдіть і отримайте весь стовпець на основі певного значення
Щоб отримати список продажів за 2 квартал згідно з наведеною вище таблицею, ви можете спочатку використовувати функцію MATCH, щоб повернути позицію продажів за 2 квартал, і яка буде подана в INDEX для отримання значень у позиції.
Родовий синтаксис
=INDEX(return_range,0,MATCH(lookup_value,lookup_array,0))
√ Примітка: Це формула масиву, яка вимагає введення з Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у .
- діапазон_повернення: Діапазон, з якого потрібно, щоб формула комбінації повертала список продажів за 2 квартал. Тут йдеться про асортимент продажу.
- пошукове_значення: Значення, використане формулою комбінації для пошуку відповідної інформації про продажі. Тут йдеться про даний квартал.
- пошуковий_масив: Діапазон комірок, у якому потрібно відповідати lookup_value. Тут йдеться про заголовки кварталу.
- match_type 0: Змушує MATCH знайти перше значення, яке точно дорівнює lookup_value.
Отримати список продажів за 2 квартал, скопіюйте або введіть формулу нижче в клітинку I6, натисніть Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у , а потім двічі клацніть клітинку та натисніть F9 щоб отримати результат:
= ІНДЕКС (C5:F11,0,СПІВНІСТИ("Q2",C4:F4, 0))
Або використовуйте посилання на клітинку, щоб зробити формулу динамічною:
= ІНДЕКС (C5:F11,0,СПІВНІСТИ(I5,C4:F4, 0))
Пояснення формули
=INDEX(C5:F11,0,MATCH(I5,C4:F4,0))
- ВІДПОВІДАННЯ(I5,C4:F4,0): Команда match_type 0 змушує функцію MATCH повертати позицію Q2, значення в I5, в асортименті C4:F4, Яка є 2.
- ІНДЕКС (C5:F11, 0,ВІДПОВІДАННЯ(I5,C4:F4,0)) = ІНДЕКС (C5:F11, 0,2): Функція INDEX повертає всі значення в 2й стовпець діапазону C5:F11 в такому масиві: {7865;4322;8534;5463;3252;7683;3654}. Зверніть увагу: щоб зробити масив видимим у Excel, вам слід двічі клацнути клітинку, де ви ввели формулу, а потім натисніть F9.
Підсумуйте весь стовпець на основі певного значення
Оскільки зараз у нас є список продажів, щоб отримати Загальний обсяг продажів у 2 кварталі була б для нас легка справа. Все, що нам потрібно зробити, це додати функцію SUM до формули, щоб підсумувати всі значення продажів зі списку.
Родовий синтаксис
=SUM(INDEX(return_range,0,MATCH(lookup_value,lookup_array,0)))
У цьому самому прикладі, щоб отримати Загальний обсяг продажів у 2 кварталі, скопіюйте або введіть формулу нижче в клітинку I8 і натисніть Що натомість? Створіть віртуальну версію себе у щоб отримати результат:
=SUM(ІНДЕКС(C5:F11,0,СПІВНІСТИ(I5,C4:F4,0)))
Пояснення формули
=SUM(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))
- ВІДПОВІДАННЯ(I5,C4:F4,0): Команда match_type 0 змушує функцію MATCH повертати позицію Q2, значення в I5, в асортименті C4:F4, Яка є 2.
- ІНДЕКС (C5:F11, 0,ВІДПОВІДАННЯ(I5,C4:F4,0)) = ІНДЕКС (C5:F11, 0,2): Функція INDEX повертає всі значення в 2й стовпець діапазону C5:F11 в такому масиві: {7865;4322;8534;5463;3252;7683;3654}.
- СУМА(ІНДЕКС (C5:F11, 0,ВІДПОВІДАННЯ(I5,C4:F4,0))) = СУММ({7865;4322;8534;5463;3252;7683;3654}): Функція SUM підсумовує всі значення в масиві, а потім отримує загальний обсяг продажів за 2 квартал, $40,773.
Подальший аналіз цілого стовпця на основі певного значення
Для додаткової обробки списку продажів за 2 квартал ви можете просто додати до формули інші функції, такі як СУМА, СРЕДНЄ, МАКСИМАЛЬНА, МІН., ВЕЛИКИЙ тощо.
Наприклад, щоб отримати середній обсяг продажів протягом другого кварталу, можна використовувати формулу:
=СЕРЕДНЄ(ІНДЕКС(C5:F11,0,СПІВНІСТИ(I5,C4:F4,0)))
Щоб дізнатися, найвищі продажі протягом 2 кварталу, скористайтеся однією з наведених нижче формул:
=МАКС(ІНДЕКС(C5:F11,0,СПІВНІСТИ(I5,C4:F4,0)))
OR
=ВЕЛИКИЙ(ІНДЕКС(C5:F11,0,СПІВНІСТИ(I5,C4:F4,0)),1)
Супутні функції
Функція Excel INDEX повертає відображене значення на основі заданої позиції з діапазону або масиву.
Функція Excel MATCH здійснює пошук певного значення в діапазоні комірок і повертає відносне положення значення.
Пов’язані формули
Пошук і отримання всього рядка
Для пошуку та отримання цілого рядка даних шляхом відповідності певному значенню можна використовувати функції INDEX і MATCH для створення формули масиву.
Точна відповідність із INDEX та MATCH
Якщо вам потрібно дізнатися перелічену в Excel інформацію про певний продукт, фільм чи особу тощо, вам слід добре використати поєднання функцій INDEX та MATCH.
Приблизний збіг з INDEX та MATCH
Бувають випадки, коли нам потрібно знайти приблизні збіги в Excel, щоб оцінити продуктивність працівників, оцінити результати учнів, обчислити поштові витрати на основі ваги тощо. У цьому підручнику ми поговоримо про те, як використовувати функції INDEX та MATCH для отримання потрібні нам результати.
Можливо, вам відомо, що ви можете поєднати функції INDEX та MATCH або скористатися функцією VLOOKUP для пошуку значень у Excel. Однак пошук не враховує регістр. Отже, щоб виконати збіг з урахуванням регістру, слід скористатися функціями EXACT та CHOOSE.
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel - допомагає виділитися з натовпу
Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...
Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)
- Одна секунда для перемикання між десятками відкритих документів!
- Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
- Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
- Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
