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