Функцыя "індэкс" у Excel: апісанне, прымяненне і прыклады

На дадзены момант праграма Excel па сваёй папулярнасці саступае толькі Word. Яна дазваляе з лёгкасцю ажыццяўляць самыя разнастайныя эканоміка-статыстычныя разлікі над вялікай колькасцю дадзеных. Для гэтай мэты ў ёй прадугледжана вялікая колькасць убудаваных функцый, у тым ліку дапаможных. Некаторыя з іх здольныя ажыццяўляць дзеянні, у тым ліку над масівамі дадзеных. Да іх адносіцца і функцыя "індэкс". У Excel яна выкарыстоўваецца як асобна, так і з "ПОИСКПОЗ", пра якую будзе расказана ніжэй.

апісанне

Функцыя "індэкс" ў Excel вяртае значэнне (спасылку на значэнне) змесціва вочка, зададзенай нумарамі радка і слупка табліцы альбо пайменаванага дыяпазону.

Яе сінтаксіс нескладаны і выглядае наступным чынам: індэкс (масіў, № радкі, № слупка).

Дадзеная функцыя можа працаваць таксама з адзінай радком або з адзіным слупком. У такім выпадку пасля ўказанні аднамернага масіва выстаўляецца адно лік. Яно абазначае нумар радка, калі масіў ўяўляе сабой слупок, і наадварот.

Функцыя "індэкс" ў Excel часам выдае значэнне «# спасылаючыся!». Часцей за ўсё гэта адбываецца, калі вочка, размешчаная на скрыжаванні названых радка і слупка, знаходзіцца па-за названага дыяпазону.

прыклады прымянення

Разгледзім некалькі выпадкаў выкарыстання функцыі "індэкс" на практыцы.

Выкажам здагадку, маецца масіў, які складаецца з 4 слупкоў і 4 радкоў (гл. Табліцу). Калі ўвесці ў адну з вочак табліцы размешчанае па-за дыяпазону А1: Е5 выраз «= індэкс (В2: Е5, 2, 3)» (без двукоссяў) і націснуць на «Увод», то ў адказ будзе выдадзена значэнне «бягонія».

Калі патрабуецца даведацца, колькі навучэнцаў Групы 2 атрымалі адзнаку «нездавальняюча», то ў адпаведную вочка варта ўвесці выраз: індэкс (С2: С5, 1).

Функцыя "ПОИСКПОЗ" у Excel

Абодва прыкладу, прыведзеныя вышэй, не будуць працаваць з вялікімі масівамі дадзеных. Справа ў тым, што выкарыстанне функцыі "індэкс" ў Excel мяркуе ўвод нумара радка і слупка не самай табліцы, а масіва дадзеных. Гэта досыць цяжка зрабіць, калі гаворка ідзе пра вялікі ліку элементаў. Вырашыць праблему можа дапамагчы яшчэ адна эксэлеўскі функцыя.

Разгледзім выпадак, калі масіў складаецца з адзінай радкі.

Дыяпазон значэнняў у гэтым выпадку В3: В6.

Выбіраем вочка ў іншы радку, напрыклад D1. Ўводзім у яе назву фрукта, пазіцыю якога хочам знайсці, у дадзеным выпадку «апельсіны». У вочку (Е1), куды хочам запісаць нумар адпаведнай радкі, уводны «= ПОИСКПОЗ (D1; В3: В6; 0)» (гл. Табліцу). У выніку там з'яўляецца лік 3. Менавіта такі нумар у дыяпазоне В3: В6 ў выказвання «апельсіны».

Апошні 0 азначае, што патрабуецца знайсці дакладнае супадзенне са значэннем D1.

Як знайсці ўсе тэкставыя значэння, якія задавальняюць няма каму крытэру

У выглядзе, прадстаўленым вышэй, функцыя "ПОИСКПОЗ" вяртае толькі адно значэнне (самае першае, т. Е. Верхняе). Але што рабіць, калі ў спісе ёсць паўтарэння. У такім выпадку дапамагаюць формулы масіва. Для іх выкарыстання варта вылучыць увесь дыяпазон дадзеных і выкарыстоўваць спалучэнне клавіш «Ctrl + Shift + Enter». Аднак яе разгляд не з'яўляецца прадметам дадзенага артыкула.

Функцыя "індэкс" і "ПОИСКПОЗ" у Excel: прыклады

Уявіце, што вам трэба выбраць з досыць вялікага масіва толькі пэўныя дадзеныя. Разгледзім для прастаты выпадак з невялікім лікам элементаў. Напрыклад, у вас ёсць справаздачу аб паспяховасці некалькіх груп студэнтаў і іх ацэнкі. Выкажам здагадку, вы хочаце, каб у вочку H2 з'явілася колькасць студэнтаў, якія атрымалі адзнаку «нездавальняюча».

Для гэтага лепш за ўсё сумесна выкарыстоўваць абедзве функцыі. Каб даведацца, што неабходна ўвесці ў H2, спачатку разгледзім самае простае выраз, якое можна выкарыстоўваць для гэтай мэты. У прыватнасці, шуканае значэнне можна атрымаць, калі запісаць у гэтую вочка «= індэкс (А2: Е5; 1; 2)». Тут мы выкарыстоўвалі варыянт з папярэдніх прыкладаў, калі нумар радка і слупка вылічваецца ўручную. Аднак наша мэта - аўтаматызаваць гэты працэс. Для гэтага варта замест двойкі і адзінкі, якія паказваюць на шуканыя радок і слупок, у масіве запісаць адпаведныя функцыі "ПОИСКПОЗ", што выдаюць гэтыя нумары. Звярніце ўвагу, што мы шукаем выраз «вуд», размешчанае ў вочку G2 і «гр. 2 »з H2. Акрамя таго, нам патрэбныя дакладныя супадзення, таму ў якасці апошняга, трэцяга, аргументу ў абодвух выпадках указваецца 0.

Тады замест 1 у формуле індэкс (А2: Е5; 1; 2) варта запісаць: ПОИСКПОЗ (G2; A2: A5; 0), а замест 2 - ПОИСКПОЗ (H2; А2: Е2; 0).

Пасля падстаноўкі маем: індэкс (А2: E5; ПОИСКПОЗ (G2; A2: A5; 0); ПОИСКПОЗ (H2; А2: Е2; 0)). У выніку, націснуўшы «Увод», маем у гэтай вочку значэнне "10".

Як распаўсюдзіць дзеянне атрыманай формулы на нейкі дыяпазон

Як вядома, функцыя "індэкс" ў Excel можа быць «выцягнутая» на нейкі дыяпазон. У прыкладзе, разгляданым вышэй, гэта ўсё 4 ячэйкі з H2: J3. У сувязі з гэтым неабходна высветліць, як зрабіць так, каб, «выцягнуўшы» гэтую формулы направа і ўніз, атрымаць правільныя значэння.

Галоўная складанасць заключаецца ў тым, што масіў А2: Е5 мае адносны адрас. Каб выправіць гэта, варта ператварыць яго ў абсалютны. Для гэтага масіў запісваецца ў выглядзе $ А $ 2: $ Е $ 5. Тое ж варта зрабіць і для абедзвюх ўбудаваных функцый, т. Е. Яны павінны выглядаць як ПОИСКПОЗ ($ G $ 2; $ A $ 2: $ A $ 5; 0) і ПОИСКПОЗ ($ H $ 2; А $ 2: $ Е2; 0).

Канчатковы выгляд формулы будзе: індэкс ($ А $ 2: $ Е $ 5; ПОИСКПОЗ ($ G $ 2; $ А $ 2: А $ 5; 0); ПОИСКПОЗ ($ H $ 2; $ А $ 2: $ Е $ 2; 0)).

У выніку будзем мець табліцу, намаляваную ніжэй

Для атрымання карэктнага выніку трэба сачыць, каб тэкставыя значэння былі запісаныя дакладна, у тым ліку не ўтрымлівалі памылкі друку і лішніх прабелаў. У адваротным выпадку праграма не будзе разглядаць іх як аднолькавыя.

Цяпер вы ведаеце, як выкарыстоўваецца функцыя "індэкс" ў Excel. Прыклады яе сумеснага выкарыстання з "ПОИСКПОЗ" вам таксама вядомыя, і вы зможаце карэктна ўжываць іх для вырашэння многіх практычных задач.