Пошук і отримання всього рядка
Щоб знайти та отримати цілий рядок даних, зіставляючи певне значення, можна використовувати ІНДЕКС та MATCH функції для створення формули масиву.
Пошук і отримання цілого рядка на основі певного значення
Підсумуйте весь рядок на основі певного значення
Подальший аналіз цілого рядка на основі певного значення
Пошук і отримання цілого рядка на основі певного значення
Щоб отримати список продажів, здійснених Джиммі згідно з наведеною вище таблицею, ви можете спочатку використовувати функцію MATCH, щоб повернути позицію продажів, зроблених Джиммі, і яка буде подана в INDEX для отримання значень у позиції.
Родовий синтаксис
=INDEX(return_range,MATCH(lookup_value,lookup_array,0),0)
√ Примітка: Це формула масиву, яка вимагає введення з Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у .
- діапазон_повернення: Діапазон, що містить весь рядок, який потрібно повернути. Тут йдеться про асортимент продажу.
- пошукове_значення: Значення, використане формулою комбінації для пошуку відповідної інформації про продажі. Тут йдеться про даного продавця.
- пошуковий_масив: Діапазон комірок, у якому потрібно відповідати lookup_value. Тут йдеться про діапазон імен.
- match_type 0: Змушує MATCH знайти перше значення, яке точно дорівнює lookup_value.
Отримати список продажів Джиммі, скопіюйте або введіть формулу нижче в клітинку I6, натисніть Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у , а потім двічі клацніть клітинку та натисніть F9 щоб отримати результат:
= ІНДЕКС (C5:F11, MATCH ("Джиммі",B5: B11, 0), 0)
Або використовуйте посилання на клітинку, щоб зробити формулу динамічною:
= ІНДЕКС (C5:F11, MATCH (I5,B5: B11, 0), 0)
Пояснення формули
=INDEX(C5:F11,MATCH(I5,B5:B11,0),0)
- ВІДПОВІДАННЯ(I5,B5:B11,0): Команда match_type 0 змушує функцію MATCH повертати позицію Джиммі, значення в I5, в асортименті B5: B11, Яка є 7 з Джиммі знаходиться в 7й позиція.
- ІНДЕКС (C5:F11,ВІДПОВІДАННЯ(I5,B5:B11,0),0) = ІНДЕКС (C5:F11,7, 0): Функція INDEX повертає всі значення в 7й рядок діапазону C5:F11 в такому масиві: 6678,3654,3278,8398 {}. Зверніть увагу: щоб зробити масив видимим у Excel, вам слід двічі клацнути клітинку, де ви ввели формулу, а потім натисніть F9.
Підсумуйте весь рядок на основі певного значення
Тепер, коли ми маємо всю інформацію про продажі, зроблену Джиммі, щоб отримати її річний обсяг продажів Джиммі, ми можемо просто додати функцію SUM до формули, щоб підсумувати всі значення продажів зі списку.
Родовий синтаксис
=SUM(INDEX(return_range,MATCH(lookup_value,lookup_array,0),0))
У цьому прикладі, щоб отримати річний обсяг продажів Джиммі, скопіюйте або введіть формулу нижче в клітинку I7 і натисніть Що натомість? Створіть віртуальну версію себе у щоб отримати результат:
=SUM(ІНДЕКС(C5:F11, MATCH (I5,B5: B11,0),0))
Пояснення формули
=SUM(INDEX(C5:F11,MATCH(I5,B5:B11,0),0))
- ВІДПОВІДАННЯ(I5,B5:B11,0): Команда match_type 0 змушує функцію MATCH повертати позицію Джиммі, значення в I5, в асортименті B5: B11, Яка є 7 з Джиммі знаходиться в 7й позиція.
- ІНДЕКС (C5:F11,ВІДПОВІДАННЯ(I5,B5:B11,0), 0) = ІНДЕКС (C5:F11,7, 0): Функція INDEX повертає всі значення в 7й рядок діапазону C5:F11 в такому масиві: 6678,3654,3278,8398 {}. Зверніть увагу: щоб зробити масив видимим у Excel, вам слід двічі клацнути клітинку, де ви ввели формулу, а потім натисніть F9.
- СУМА(ІНДЕКС (C5:F11,ВІДПОВІДАННЯ(I5,B5:B11,0)),0) = СУММ(6678,3654,3278,8398 {}): Функція SUM підсумовує всі значення в масиві, а потім отримує річний обсяг продажів, здійснений Джиммі, $22,008.
Подальший аналіз цілого рядка на основі певного значення
Для додаткової обробки продажів, здійснених Джиммі, ви можете просто додати до формули інші функції, такі як СУМА, СРЕДНІЙ, МАКС, МІН, ВЕЛИКИЙ тощо.
Наприклад, щоб отримати середній обсяг продажів Джиммі за кожен квартал, можна використовувати формулу:
=СЕРЕДНЄ(ІНДЕКС(C5:F11, MATCH (I5,B5: B11,0),0))
Щоб дізнатися, найнижчі продажі Джиммі, скористайтеся однією з наведених нижче формул:
=MIN(ІНДЕКС(C5:F11, MATCH (I5,B5: B11,0),0))
OR
=МАЛИЙ(ІНДЕКС(C5:F11, MATCH (I5,B5: B11,0),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.
