Перейти до основного матеріалу

Знайдіть і отримайте весь стовпець

Щоб знайти та отримати весь стовпець, зіставивши певне значення, an ІНДЕКС та MATCH формула зробить вам послугу.

знайти та отримати весь стовпець 1

Знайдіть і отримайте весь стовпець на основі певного значення
Підсумуйте весь стовпець на основі певного значення
Подальший аналіз цілого стовпця на основі певного значення


Знайдіть і отримайте весь стовпець на основі певного значення

Щоб отримати список продажів за 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))

знайти та отримати весь стовпець 2

Пояснення формули

=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)))

знайти та отримати весь стовпець 3

Пояснення формули

=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 INDEX повертає відображене значення на основі заданої позиції з діапазону або масиву.

Функція Excel MATCH

Функція Excel MATCH здійснює пошук певного значення в діапазоні комірок і повертає відносне положення значення.


Пов’язані формули

Пошук і отримання всього рядка

Для пошуку та отримання цілого рядка даних шляхом відповідності певному значенню можна використовувати функції INDEX і MATCH для створення формули масиву.

Точна відповідність із INDEX та MATCH

Якщо вам потрібно дізнатися перелічену в Excel інформацію про певний продукт, фільм чи особу тощо, вам слід добре використати поєднання функцій INDEX та MATCH.

Приблизний збіг з INDEX та MATCH

Бувають випадки, коли нам потрібно знайти приблизні збіги в Excel, щоб оцінити продуктивність працівників, оцінити результати учнів, обчислити поштові витрати на основі ваги тощо. У цьому підручнику ми поговоримо про те, як використовувати функції INDEX та MATCH для отримання потрібні нам результати.

Пошук з урахуванням регістру

Можливо, вам відомо, що ви можете поєднати функції INDEX та MATCH або скористатися функцією VLOOKUP для пошуку значень у Excel. Однак пошук не враховує регістр. Отже, щоб виконати збіг з урахуванням регістру, слід скористатися функціями EXACT та CHOOSE.


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel - допомагає виділитися з натовпу

Популярні функції: Знайдіть, виділіть або визначте дублікати  |  Видалити порожні рядки  |  Об’єднайте стовпці або клітинки без втрати даних  |  Раунд без Формули ...
Супер VLookup: Кілька критеріїв  |  Множинне значення  |  На кількох аркушах  |  Нечіткий пошук...
Adv. Випадаючий список: Легкий спадний список  |  Залежний спадний список  |  Виберіть розкривний список, що вибирається...
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  Порівняти стовпці з Виберіть однакові та різні клітини ...
Особливості: Фокус сітки  |  Перегляд дизайну  |  Велика панель формул  |  Диспетчер робочих книг і аркушів | Бібліотека ресурсів (автотекст)  |  Вибір дати  |  Об’єднайте робочі аркуші  |  Шифрування/розшифрування клітинок  |  Надсилайте листи за списком  |  Супер фільтр  |  Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи ...)  |  50 + Графік типи (діаграма Ганта ...)  |  40+ Практичний Формули (Розрахуйте вік на основі дня народження ...)  |  19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху ...)  |  12 Перетворення Tools (Числа до слів, Валютна конверсія ...)  |  7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділити клітинки Excel ...)  |  ... і більше

Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...


Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations