Функция Excel INDEX (Примери + видео)
Кога да използвате функцията Excel INDEX
Функцията Excel INDEX може да се използва, когато искате да извлечете стойността от таблични данни и имате номер на ред и номер на колона на точката с данни. Например в примера по -долу можете да използвате функцията INDEX, за да получите знаците на „Том“ във Физика, когато знаете номера на реда и номера на колоната в набора от данни.
Какво връща
Той връща стойността от таблица за посочения номер на ред и номер на колона.
Синтаксис
= ИНДЕКС (масив, ред_ номер, [кол_номер])
= ИНДЕКС (масив, ред_ номер, [номер_кол], [номер_на площ])
Функцията INDEX има 2 синтаксиса. Първият се използва в повечето случаи, но в случай на тристранно търсене се използва вторият (обхванат в пример 5).
Аргументи за въвеждане
- масив - а диапазон от клетки или константа на масив.
- ред_ номер - номера на реда, от който трябва да се извлече стойността.
- [col_num] - номера на колоната, от която трябва да се извлече стойността. Въпреки че това е незадължителен аргумент, но ако row_num не е предоставен, той трябва да бъде даден.
- [номер на площ] - (По избор) Ако аргументът на масива се състои от множество диапазони, това число ще се използва за избор на препратка от всички диапазони.
Допълнителни бележки (скучни неща … но важно да знаете)
- Ако номерът на реда или колоната е 0, той връща стойностите съответно на целия ред или колона.
- Ако функцията INDEX се използва пред препратка към клетка (като A1 :), тя връща препратка към клетка вместо стойност (вижте примерите по -долу).
- Най -широко използван заедно с функцията MATCH.
- За разлика от VLOOKUP, функцията INDEX може да върне стойност отляво на стойността за търсене.
- Функцията INDEX има две форми - форма на масив и референтна форма
- „Форма на масива“ е мястото, където извличате стойност въз основа на номера на ред и колона от дадена таблица.
- „Референтен формуляр“ е мястото, където има няколко таблици и използвате аргумента area_num, за да изберете таблицата и след това да извлечете стойност в нея, като използвате номера на ред и колона (вижте живия пример по -долу).
Функция Excel INDEX - Примери
Ето шест примера за използване на Excel INDEX функция.
Пример 1 - Намиране на Томовите знаци във физиката (двупосочно търсене)
Да предположим, че имате набор от данни, както е показано по -долу:
За да намерите белезите на Том във физиката, използвайте следната формула:
= ИНДЕКС ($ B $ 3: $ E $ 10,3,2)
Тази формула на INDEX определя масива като $ B $ 3: $ E $ 10, който има маркировки за всички теми. След това използва номера на ред (3) и номер на колона (2), за да извлече белезите на Том във Физиката.
Пример 2 - Осъществяване на динамична стойност на LOOKUP с помощта на функцията MATCH
Не винаги е възможно ръчно да посочите номера на реда и номера на колоната. Може да имате огромен набор от данни или да го направите динамичен, така че той автоматично да идентифицира името и/или темата, посочени в клетките, и да даде правилния резултат.
Нещо, както е показано по -долу:
Това може да стане с помощта на комбинация от INDEX и функцията MATCH.
Ето формулата, която ще направи стойностите за търсене динамични:
= ИНДЕКС ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
В горната формула, вместо да кодирате твърдо номера на реда и номера на колоната, функцията MATCH се използва, за да я направи динамична.
- Динамичният номер на ред се дава от следната част от формулата - MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0). Той сканира името на учениците и идентифицира стойността за търсене (в този случай $ G $ 5). След това връща номера на ред на стойността за търсене в набора от данни. Например, ако стойността за търсене е Мат, тя ще върне 1, ако е Боб, ще върне 2 и така нататък.
- Динамичният номер на колоната се дава от следната част от формулата - MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0). Той сканира имената на обектите и идентифицира стойността за търсене (в този случай $ H $ 4). След това връща номера на колоната на стойността за търсене в набора от данни. Например, ако стойността за търсене е Math, тя ще върне 1, ако е Physics, тя ще върне 2 и така нататък.
Пример 3 - Използване на падащи списъци като стойности за търсене
В горния пример трябва ръчно да въведем данните. Това може да отнеме много време и да доведе до грешки, особено ако имате огромен списък от стойности за търсене.
Добра идея в такива случаи е да създадете падащ списък със стойностите за търсене (в този случай това може да са имена на ученици и предмети) и след това просто да изберете от списъка. Въз основа на избора формулата автоматично ще актуализира резултата.
Нещо, както е показано по -долу:
Това прави добър компонент на таблото за управление, тъй като можете да имате огромен набор от данни със стотици студенти в задната част, но крайният потребител (да речем учител) може бързо да получи оценките на ученик по предмет, като просто направи избора от падащото меню.
Как да направите това:
Формулата, използвана в този случай, е същата, използвана в пример 2.
= ИНДЕКС ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
Стойностите за търсене са преобразувани в падащи списъци.
Ето стъпките за създаване на падащия списък на Excel:
- Изберете клетката, в която искате падащия списък. В този пример в G4 искаме имената на учениците.
- Отидете на Данни -> Инструменти за данни -> Проверка на данни.
- В диалоговия прозорец Проверка на данни в раздела с настройки изберете Списък от падащото меню Разрешаване.
- В източника изберете $ A $ 3: $ A $ 10
- Щракнете върху OK.
Сега ще имате падащия списък в клетка G5. По същия начин можете да създадете такъв в H4 за субектите.
Пример 4 - Връщане на стойности от цял ред/колона
В горните примери използвахме функцията Excel INDEX, за да направим двупосочно търсене и да получим единична стойност.
Сега, ако искате да получите всички оценки на студент. Това може да ви позволи да намерите максималния/минималния резултат на този ученик или общия брой точки, получени по всички предмети.
На прост английски език искате първо да получите целия ред резултати за ученик (да речем Боб) и след това в рамките на тези стойности да идентифицирате най -високия резултат или общата сума на всички резултати.
Тук е трикът.
В Excel INDEX функция, когато въведете номер на колона като 0, той ще върне стойностите на целия този ред.
Така че формулата за това би била:
= ИНДЕКС ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)
Сега тази формула. ако се използва както е, ще върне #VALUE! грешка. Въпреки че показва грешката, в задната част тя връща масив, който има всички резултати за Том - {57,77,91,91}.
Ако изберете формулата в режим на редактиране и натиснете F9, ще можете да видите масива, който връща (както е показано по -долу): 
По същия начин, въз основа на стойността за търсене, когато номерът на колоната е зададен като 0 (или е оставен празен), той връща всички стойности в реда за стойността за търсене
Сега, за да изчислим общия резултат, получен от Том, можем просто да използваме горната формула в рамките на функцията SUM.
= SUM (ИНДЕКС ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))
На подобни линии, за да изчислим най -високия резултат, можем да използваме MAX/LARGE и за изчисляване на минимум, можем да използваме MIN/SMALL.
Пример 5 - Трипосочно търсене с помощта на INDEX/MATCH
Функцията Excel INDEX е създадена за обработка на тристранни справки.
Какво е тристранно търсене?
В горните примери използвахме една таблица с оценки за ученици по различни предмети. Това е пример за двупосочно търсене, тъй като използваме две променливи за извличане на резултата (името на ученика и предмета).
Да предположим, че след една година един студент има три различни нива на изпити, единичен тест, междинен и финален изпит (това имах, когато бях студент).
Тристранно търсене би било възможността да получите оценки на студент за определен предмет от определеното ниво на изпит. Това ще го направи тристранно търсене, тъй като има три променливи (името на студента, името на субекта и нивото на изпита).
Ето пример за тристранно търсене:
В горния пример, освен да изберете името на студента и името на предмета, можете да изберете и нивото на изпита. Въз основа на нивото на изпита връща съответстващата стойност от една от трите таблици.
Ето формулата, използвана в клетка H4:
= INDEX (($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23), MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0), MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0), IF ($ H $ 2 = "Unit Test", 1, IF ($ H $ 2 = "Midterm", 2,3)))
Нека разбием тази формула, за да разберем как работи.
Тази формула приема четири аргумента. INDEX е една от онези функции в Excel, които имат повече от един синтаксис.
= ИНДЕКС (масив, ред_ номер, [кол_номер])
= INDEX (масив, номер на ред, [номер на колона], [номер на площ])
Досега във всички примери по-горе използвахме първия синтаксис, но за да направим тристранно търсене, трябва да използваме втория синтаксис.
Сега нека видим всяка част от формулата въз основа на втория синтаксис.
- масив - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): Вместо да използваме един масив, в този случай сме използвали три масива в скоби.
- row_num - MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0): Функцията MATCH се използва за намиране на позицията на името на ученика в клетка $ G $ 4 в списъка с имената на ученика.
- col_num - MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0): Функцията MATCH се използва за намиране на позицията на името на обекта в клетка $ H $ 3 в списъка с името на субекта.
- [area_num] - IF ($ H $ 2 = ”Unit Test”, 1, IF ($ H $ 2 = ”Midterm”, 2,3)): Стойността на номера на областта указва на функцията INDEX кой масив да избере. В този пример имаме три масива в първия аргумент. Ако изберете Unit Test от падащото меню, функцията IF връща 1, а функциите INDEX избират 1-ви масив от трите масива (което е $ B $ 3: $ E $ 7).
Пример 6 - Създаване на справка с помощта на функцията INDEX (динамични именовани диапазони)
Това е едно диво използване на функцията Excel INDEX.
Нека вземем прост пример.
Имам списък с имена, както е показано по -долу:
Сега мога да използвам проста функция INDEX, за да получа фамилията в списъка.
Ето формулата:
= ИНДЕКС ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Тази функция просто брои броя на клетките, които не са празни, и връща последния елемент от този списък (работи само когато няма празни места в списъка).
И тук идва магията.
Ако поставите формулата пред препратка към клетка, формулата ще върне препратка към клетка със съответстващата стойност (вместо самата стойност).
= A2: INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Очаквате горната формула да върне = A2: „Джош“ (където Джош е последната стойност в списъка). Въпреки това, той връща = A2: A9 и следователно получавате масив от имена, както е показано по -долу:
Един практичен пример, при който тази техника може да бъде полезна, е създаването на динамични именувани диапазони.
Това е всичко в този урок. Опитах се да покрия основните примери за използване на функцията Excel INDEX. Ако искате да видите още примери, добавени към този списък, уведомете ме в секцията за коментари.
Забележка: Опитах всичко възможно да прочета прочетете този урок, но в случай, че откриете някакви грешки или правописни грешки, моля, уведомете ме 🙂
Функция Excel INDEX - Видеоурок
- Функция Excel VLOOKUP.
- Функция Excel HLOOKUP.
- Функция Excel INDIRECT.
- Функция Excel MATCH.
- Функция Excel OFFSET.
Може да харесате и следните уроци по Excel:
- VLOOKUP Vs. ИНДЕКС/МАТЧ
- Съвпадение на индекса на Excel
- Търсене и връщане на стойности в цял ред/колона.
