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