Екцел ИНДЕКС функција - Примери формула + БЕСПЛАТАН видео

Екцел ИНДЕКС функција (примери + видео)

Када користити Екцел ИНДЕКС функцију

Екцел ИНДЕКС функција се може користити када желите да преузмете вредност из табеларних података и имате број реда и број колоне тачке података. На пример, у доњем примеру можете да користите функцију ИНДЕКС да бисте добили ознаке „Том“ у физици када знате број реда и број колоне у скупу података.

Шта враћа

Враћа вредност из табеле за наведени број реда и број колоне.

Синтакса

= ИНДЕКС (низ, ред_број, [број_колона])
= ИНДЕКС (низ, број редака, [број_колона], [број_подручја])

ИНДЕКС функција има 2 синтаксе. Први се користи у већини случајева, међутим, у случају тросмерног тражења, користи се други (обрађен у примеру 5).

Улазни аргументи

• низ - а опсег ћелија или константа низа.
• ред_број - број реда из којег треба да се преузме вредност.
• [број_колона] - број колоне из које треба да се преузме вредност. Иако је ово изборни аргумент, али ако број_реда није наведен, потребно га је навести.
• [број_подручја] - (Опционално) Ако се аргумент низа састоји од више опсега, овај број ће се користити за одабир референце из свих распона.

Додатне напомене (досадне ствари … али важно је знати)

• Ако је број реда или број колоне 0, враћа вредности читавог реда или колоне.
• Ако се ИНДЕКС функција користи испред референце ћелије (као што је А1 :), она враћа референцу ћелије уместо вредности (погледајте примере испод).
• Најчешће се користи заједно са функцијом МАТЦХ.
• За разлику од ВЛООКУП -а, функција ИНДЕКС може вратити вриједност с лијеве стране вриједности тражења.
• ИНДЕКС функција има два облика - облик поља и образац референце
  • „Образац низа“ је место где доносите вредност засновану на броју реда и колоне из дате табеле.
  • „Референтни образац“ је место где постоји више табела и користите аргумент ареа_нум за избор табеле, а затим доносите вредност унутар ње користећи број редака и колоне (погледајте пример испод испод).

Екцел ИНДЕКС функција - Примери

Ево шест примера коришћења Екцел ИНДЕКС функције.

Пример 1 - Проналажење Томових ознака у физици (двосмерно тражење)

Претпоставимо да имате скуп података као што је приказано испод:

Да бисте пронашли Томове ознаке у физици, користите формулу испод:

= ИНДЕКС ($ Б $ 3: $ Е $ 10,3,2)

Ова ИНДЕКС формула наводи низ као $ Б $ 3: $ Е $ 10 који има ознаке за све предмете. Затим користи број ретка (3) и број колоне (2) да дохвати Томове ознаке у физици.

Пример 2 - Креирање ЛООКУП вредности динамичком помоћу функције МАТЦХ

Можда није увек могуће ручно навести број реда и број колоне. Можда имате огроман скуп података или га желите учинити динамичним тако да аутоматски идентификује име и/или предмет наведен у ћелијама и даје тачан резултат.

Нешто као што је приказано испод:

Ово се може урадити комбинацијом ИНДЕКС и МАТЦХ функције.

Ево формуле која ће учинити тражење вредности динамичким:

= ИНДЕКС ($ Б $ 3: $ Е $ 10, МАТЦХ ($ Г $ 5, $ А $ 3: $ А $ 10,0), МАТЦХ ($ Х $ 4, $ Б $ 2: $ Е $ 2,0))

У горњој формули, уместо кодирања броја реда и броја колоне, функција МАТЦХ се користи да би била динамична.

• Број динамичког реда је дат следећим делом формуле - МАТЦХ ($ Г $ 5, $ А $ 3: $ А $ 10,0). Скенира имена ученика и идентификује вредност тражења (у овом случају $ 5 УСД). Затим враћа број реда вредности тражења у скупу података. На пример, ако је вредност за претраживање Матт, вратиће 1, ако је Боб, вратиће 2 и тако даље.
• Број динамичке колоне је дат следећим делом формуле - МАТЦХ ($ Х $ 4, $ Б $ 2: $ Е $ 2,0). Скенира имена субјеката и идентификује вредност тражења ($ Х $ 4 у овом случају). Затим враћа број колоне вредности тражења у скупу података. На пример, ако је вредност за претраживање Матх, вратиће 1, ако је Пхисицс, вратиће 2 итд.

Пример 3 - Коришћење падајућих листа као вредности за тражење

У горњем примеру морамо ручно унети податке. То би могло одузети много времена и склоно грешкама, посебно ако имате огромну листу вредности за тражење.

Добра идеја у таквим случајевима је стварање падајуће листе вредности за тражење (у овом случају то могу бити имена ученика и предмети), а затим једноставно изабрати са листе. На основу избора, формула би аутоматски ажурирала резултат.

Нешто као што је приказано испод:

Ово чини добру компоненту контролне табле јер можете имати огроман скуп података са стотинама ученика на задњој страни, али крајњи корисник (рецимо наставник) може брзо добити оцене ученика у предмету једноставним избором из падајући мени.

Како ово направити:

Формула која се користи у овом случају иста је као и у примеру 2.

= ИНДЕКС ($ Б $ 3: $ Е $ 10, МАТЦХ ($ Г $ 5, $ А $ 3: $ А $ 10,0), МАТЦХ ($ Х $ 4, $ Б $ 2: $ Е $ 2,0))

Вредности за претраживање су претворене у падајуће листе.

Ево корака за креирање падајуће листе програма Екцел:

• Одаберите ћелију у којој желите падајућу листу. У овом примеру, у Г4, желимо имена ученика.
• Идите на Подаци -> Алати за податке -> Валидација података.
• У дијалогу Валидација података, на картици поставки, изаберите Листа са падајућег менија Дозволи.
• У извору изаберите $ А $ 3: $ А $ 10
• Притисните ОК.

Сада ћете имати падајућу листу у ћелији Г5. Слично, можете створити један у Х4 за субјекте.

Пример 4 - Враћене вредности из целог реда/колоне

У горњим примерима користили смо Екцел ИНДЕКС функцију за двосмерно тражење и добијање једне вредности.

Шта ако желите да добијете све оцене ученика. Ово вам може омогућити да пронађете максимални/минимални резултат тог ученика или укупне оцјене постигнуте у свим предметима.

На једноставном енглеском језику, прво желите да добијете читав низ бодова за ученика (рецимо Боба), а затим унутар тих вредности идентификујте највећи резултат или збир свих оцена.

Ево трика.

У Екцел ИНДЕКС функцији, када унесете број колоне као 0, вратиће вредности целог тог реда.

Дакле, формула за ово би била:

= ИНДЕКС ($ Б $ 3: $ Е $ 10, МАТЦХ ($ Г $ 5, $ А $ 3: $ А $ 10,0), 0)

Сада ова формула. ако се користи како јесте, вратиће #ВАЛУЕ! грешка. Иако приказује грешку, у позадини враћа низ који има све оцене за Тома - {57,77,91,91}.

Ако изаберете формулу у режиму уређивања и притиснете Ф9, моћи ћете да видите низ који враћа (као што је приказано испод):

Слично, на основу онога што је вредност за претраживање, када је број колоне наведен као 0 (или је остављен празан), враћа све вредности у реду за вредност тражења

Сада за израчунавање укупног резултата који је Том постигао можемо једноставно користити горњу формулу унутар функције СУМ.

= СУМ (ИНДЕКС ($ Б $ 3: $ Е $ 10, МАТЦХ ($ Г $ 5, $ А $ 3: $ А $ 10,0), 0))

На сличним линијама, за израчунавање највећег резултата, можемо користити МАКС/ЛАРГЕ, а за израчунавање минималног можемо користити МИН/СМАЛЛ.

Пример 5 - Тространо тражење помоћу ИНДЕКС/МАТЦХ

Екцел ИНДЕКС функција је направљена за руковање тросмерним тражењима.

Шта је тросмерно тражење?

У горњим примерима користили смо једну табелу са оценама за ученике из различитих предмета. Ово је пример двосмерног претраживања јер користимо две променљиве за добијање резултата (име ученика и предмет).

Претпоставимо да за годину дана студент има три различита нивоа испита, јединични тест, средњи и завршни испит (то сам имао док сам био студент).

Тросмерно тражење било би могућност да се студенту дају оцене за одређени предмет са наведеног нивоа испита. Ово би учинило тросмерно тражење јер постоје три променљиве (име ученика, име субјекта и ниво испита).

Ево примера тросмерног тражења:

У горњем примеру, осим одабира имена ученика и назива предмета, можете изабрати и ниво испита. На основу нивоа испита, враћа одговарајућу вредност из једне од три табеле.

Ево формуле која се користи у ћелији Х4:

= ИНДЕКС (($ Б $ 3: $ Е $ 7, $ Б $ 11: $ Е $ 15, $ Б $ 19: $ Е $ 23), МАТЦХ ($ Г $ 4, $ А $ 3: $ А $ 7,0), МАТЦХ ($ Х $ 3, $ Б $ 2: $ Е $ 2,0), ИФ ($ Х $ 2 = "Унит Тест", 1, ИФ ($ Х $ 2 = "Мидтерм", 2,3)))

Разложимо ову формулу да бисмо разумели како функционише.

Ова формула узима четири аргумента. ИНДЕКС је једна од оних функција у Екцелу која има више од једне синтаксе.

= ИНДЕКС (низ, ред_број, [број_колона])
= ИНДЕКС (низ, број редака, [број_колона], [број_подручја])

До сада смо у свим горњим примерима користили прву синтаксу, али да бисмо извршили тросмерно тражење, морамо да користимо другу синтаксу.

Погледајмо сада сваки део формуле на основу друге синтаксе.

• низ - ($ Б $ 3: $ Е $ 7, $ Б $ 11: $ Е $ 15, $ Б $ 19: $ Е $ 23): Уместо да користимо један низ, у овом случају смо користили три низа унутар заграда.
• ров_нум - МАТЦХ ($ Г $ 4, $ А $ 3: $ А $ 7,0): МАТЦХ функција се користи за проналажење положаја имена ученика у ћелији $ Г $ 4 на листи имена ученика.
• цол_нум - МАТЦХ ($ Х $ 3, $ Б $ 2: $ Е $ 2,0): МАТЦХ функција се користи за проналажење позиције имена субјекта у ћелији $ Х $ 3 на листи имена субјекта.
• [ареа_нум] - ИФ ($ Х $ 2 = ”Унит Тест”, 1, ИФ ($ Х $ 2 = ”Мидтерм”, 2,3)): Вредност броја подручја говори функцији ИНДЕКС који низ треба изабрати. У овом примеру имамо три низа у првом аргументу. Ако изаберете Унит Тест из падајућег менија, функција ИФ враћа 1, а функције ИНДЕКС одабиру први низ из три низа (што је $ Б $ 3: $ Е $ 7).

Пример 6 - Креирање референце помоћу функције ИНДЕКС (динамички именовани опсези)

Ово је једна дивља употреба Екцел ИНДЕКС функције.

Узмимо једноставан пример.

Имам списак имена као што је приказано испод:

Сада могу користити једноставну ИНДЕКС функцију да добијем презиме на листи.

Ево формуле:

= ИНДЕКС ($ А $ 2: $ А $ 9, ЦОУНТА ($ А $ 2: $ А $ 9))

Ова функција једноставно броји број ћелија које нису празне и враћа последњу ставку са ове листе (ради само када на листи нема празних места).

Е сад, овде долази магија.

Ако формулу ставите испред референце ћелије, формула би вратила референцу ћелије одговарајуће вредности (уместо саме вредности).

= А2: ИНДЕКС ($ А $ 2: $ А $ 9, ЦОУНТА ($ А $ 2: $ А $ 9))

Очекивали бисте да горња формула врати = А2: „Јосх“ (где је Јосх последња вредност на листи). Међутим, враћа = А2: А9 и стога добијате низ имена као што је приказано испод:

Један практичан пример где ова техника може бити од помоћи је креирање динамичких именованих опсега.

То је то у овом водичу. Покушао сам да обухватим главне примере коришћења Екцел ИНДЕКС функције. Ако желите да видите још примера додатих овој листи, обавестите ме у одељку за коментаре.

Напомена: Дао сам све од себе да прочитам овај водич, али у случају да пронађете грешке или правописне грешке, обавестите ме 🙂

Екцел ИНДЕКС функција - видео водич

• Екцел ВЛООКУП функција.
• Екцел ХЛООКУП функција.
• Екцел ИНДИРЕКТНА функција.
• Екцел МАТЦХ функција.
• Екцел ОФФСЕТ функција.

Можда ће вам се допасти и следећи Екцел водичи:

• ВЛООКУП Вс. ИНДЕКС/МАТЦХ
• Подударање Екцел индекса
• Тражење и враћање вредности у целом реду/колони.