Адной з самых карысных функцый праграмы Эксэля з'яўляецца аператар індэкс. Ён вырабляе пошук дадзеных у дыяпазоне на скрыжаванні названых радка і слупка, вяртаючы вынік у загадзя пазначаную вочка. Але цалкам магчымасці гэтай функцыі раскрываюцца пры выкарыстанні яе ў складаных формулах ў камбінацыі з іншымі аператарамі. Давайце разгледзім розныя варыянты яе прымянення.
Выкарыстанне функцыі індэкс
Аператар індэкс ставіцца да групы функцый з катэгорыі «Спасылкі і масівы». Ён мае дзве разнавіднасці: для масіваў і для спасылак.Варыянт для масіваў мае наступны сінтаксіс:
= Індэкс (масіў; номер_строки; номер_столбца)
Пры гэтым два апошніх аргументу ў формуле можна выкарыстоўваць, як разам, так і любы адзін з іх, калі масіў аднамерны. Пры шматмерным дыяпазоне варта ўжываць абодва значэння. Трэба таксама ўлічыць, што пад нумарам радка і слупка разумеецца не нумар на каардынатах ліста, а парадак ўнутры самога названага масіву.
Сінтаксіс для спасылкавага варыянту выглядае так:
= Індэкс (спасылка; номер_строки; номер_столбца; [номер_области])
Тут сапраўды гэтак жа можна выкарыстоўваць толькі адзін аргумент з двух: «Нумар радка» або «Нумар слупка». Аргумент "Нумар вобласці» наогул з'яўляецца неабавязковым і ён ужываецца толькі тады, калі ў аперацыі ўдзельнічаюць некалькі дыяпазонаў.
Такім чынам, аператар шукае дадзеныя ва ўстаноўленым дыяпазоне пры ўказанні радкі або слупкі. Дадзеная функцыя сваімі магчымасцямі вельмі падобная на аператара ВПР , Але ў адрозненне ад яго можа вырабляць пошук практычна ўсюды, а не толькі ў крайнім левым слупку табліцы.
Спосаб 1: выкарыстанне аператара індэкс для масіваў
Давайце, перш за ўсё, разбяром на найпростым прыкладзе алгарытм выкарыстання аператара індэкс для масіваў.
Маем табліцу заробкаў. У першым яе слупку адлюстраваны прозвішчы работнікаў, у другім - дата выплаты, а ў трэцім - велічыня сумы заробку. Нам трэба вывесці імя работніка ў трэцім радку.
- Вылучаем вочка, у якой будзе выводзіцца вынік апрацоўкі. Клікаем па значку «Уставіць функцыю», які размешчаны адразу злева ад радка формул.
- Адбываецца працэдура актывацыі Майстры функцый. У катэгорыі «Спасылкі і масівы" дадзенага інструмента або «Поўны алфавітны пералік» шукаем найменне «індэкс». Пасля таго, як знайшлі гэтага аператара, вылучаем яго і пстрыкаем па кнопцы «OK», якая размяшчаецца ў ніжняй частцы акна.
- Адкрываецца невялікае акенца, у якім трэба выбраць адзін з тыпаў функцыі: «Масіў» або «Спасылка». Патрэбны нам варыянт «Масіў». Ён размешчаны першым і па змаўчанні выдзелены. Таму нам застаецца проста націснуць на кнопку «OK».
- Адкрываецца акно аргументаў функцыі індэкс. Як вышэй гаварылася, у яе маецца тры аргументу, а адпаведна і тры палі для запаўнення.
У полі «Масіў» трэба пазначыць адрас апрацоўванага дыяпазону дадзеных. Яго можна ўбіць ўручную. Але для палягчэння задачы мы паступім інакш. Ставім курсор ў адпаведнае поле, а затым абводзім увесь дыяпазон таблічных дадзеных на лісце. Пасля гэтага адрас дыяпазону тут жа адлюструецца ў поле.
У полі «Нумар радка» ставім лічбу «3», так як па ўмове нам трэба вызначыць трэцяе імя ў спісе. У полі "Нумар слупка» усталёўваем лік «1», так як калонка з імёнамі з'яўляецца першай у вылучаным дыяпазоне.
Пасля таго, як усе паказаныя налады учынены, пстрыкаем па кнопцы «OK».
- Вынік апрацоўкі выводзіцца ў вочка, якая існавала ў першым пункце дадзенай інструкцыі. Менавіта выведзеная прозвішча з'яўляецца трэцяй у спісе ў вылучаным дыяпазоне дадзеных.
Мы разабралі прымяненне функцыі індэкс у шматмернага масіве (некалькі слупкоў і радкоў). Калі б дыяпазон быў аднамерны, то запаўненне дадзеных у акне аргументаў было б яшчэ прасцей. У полі «Масіў» тым жа метадам, што і вышэй, мы паказваем яго адрас. У дадзеным выпадку дыяпазон даных складаецца толькі з значэнняў ў адной калонцы «Імя». У полі «Нумар радка» паказваем значэнне «3», бо трэба даведацца дадзеныя з трэцяга радка. Поле «Нумар слупка» наогул можна пакінуць пустым, так як у нас аднамерны дыяпазон, у якім выкарыстоўваецца толькі адзін слупок. Ціснем на кнопку «OK».
Вынік будзе сапраўды такі ж, што і вышэй.
Гэта быў найпросты прыклад, каб вы ўбачылі, як працуе дадзеная функцыя, але на практыцы падобны варыянт яе выкарыстання ўжываецца ўсё-такі рэдка.
Урок: Майстар функцый у Эксэля
Спосаб 2: прымяненне ў комплексе з аператарам ПОИСКПОЗ
На практыцы функцыя індэкс часцей за ўсё ўжываецца разам з аргументам ПОИСКПОЗ. Звязак індэкс - ПОИСКПОЗ з'яўляецца наймагутным інструментам пры працы ў Эксэля, які па сваім функцыянале больш гнуткі, чым яго бліжэйшы аналаг - аператар ВПР.
Асноўнай задачай функцыі ПОИСКПОЗ з'яўляецца ўказанне нумара па парадку пэўнага значэння ў вылучаным дыяпазоне.
Сінтаксіс аператара ПОИСКПОЗ такі:
= ПОИСКПОЗ (искомое_значение, просматриваемый_массив, [тип_сопоставления])
- Шуканае значэнне - гэта значэнне, пазіцыю якога ў дыяпазоне мы шукаем;
- Прагляданы масіў - гэта дыяпазон, у якім знаходзіцца гэта значэнне;
- Тып супастаўлення - гэта неабавязковы параметр, які вызначае, дакладна ці прыблізна шукаць значэння. Мы будзем шукаць дакладныя значэнні, таму дадзены аргумент не выкарыстоўваецца.
З дапамогай гэтай прылады можна аўтаматызаваць ўвядзенне аргументаў «Нумар радка» і «Нумар слупка» у функцыю індэкс.
Паглядзім, як гэта можна зрабіць на канкрэтным прыкладзе. Працуем усё з той жа табліцай, пра якую ішла гаворка вышэй. Асобна ў нас маецца два дадатковых поля - «Імя» і «Сума». Трэба зрабіць так, што пры ўвядзенні імя работніка аўтаматычна адлюстроўвалася сума заробленых ім грошай. Паглядзім, як гэта можна ўвасобіць на практыцы, ужыўшы функцыі індэкс і ПОИСКПОЗ.
- Перш за ўсё, даведаемся, якую заработную плату атрымлівае работнік Парфёнаў Д. Ф. Упісваем яго імя ў адпаведнае поле.
- Вылучаем вочка ў полі «Сума», у якой будзе выводзіцца выніковы вынік. Запускаем акно аргументаў функцыі індэкс для масіваў.
У полі «Масіў» ўносім каардынаты слупка, у якім знаходзяцца сумы заробкаў работнікаў.
Поле «Нумар слупка» пакідаем пустым, так як мы выкарыстоўваем для прыкладу аднамерны дыяпазон.
А вось у поле «Нумар радка» нам як раз трэба будзе запісаць функцыю ПОИСКПОЗ. Для яе запісу прытрымліваемся таго сінтаксісу, пра які ішла гаворка вышэй. Адразу ў поле ўпісваем найменне самога аператара «ПОИСКПОЗ» без двукоссяў. Затым адразу ж адкрываем дужку і паказваем каардынаты шуканага значэння. Гэта каардынаты той вочкі, у якую мы асобна запісалі прозвішча работніка Парфёнава. Ставім кропку з коскай і паказваем каардынаты праглядваемыя дыяпазону. У нашым выпадку гэта адрас слупка з імёнамі супрацоўнікаў. Пасля гэтага зачыняем дужку.
Пасля таго, як усе значэння ўнесены, ціснем на кнопку «OK».
- Вынік колькасці заробку Парфёнава Д. Ф. пасля апрацоўкі выводзіцца ў полі «Сума».
- Цяпер, калі ў полі «Імя» мы зменім змесціва з «Парфёнаў Д.Ф.», на, напрыклад, «Папова М. Д.», то аўтаматычна зменіцца і значэнне заработнай платы ў поле «Сума».
Спосаб 3: апрацоўка некалькіх табліц
Цяпер паглядзім, як з дапамогай аператара індэкс можна апрацаваць некалькі табліц. Для гэтых мэтаў будзе прымяняцца дадатковы аргумент «Нумар вобласці».
Маем тры табліцы. У кожнай табліцы адлюстравана заработная плата работнікаў за асобны месяц. Нашай задачай з'яўляецца даведацца заработную плату (трэці слупок) другога работніка (другая радок) за трэці месяц (трэцяя вобласць).
- Вылучаем вочка, у якой будзе рабіцца выснова выніку і звычайным спосабам адкрываем Майстар функцый, але пры выбары тыпу аператара выбіраем спасылачныя выгляд. Гэта нам трэба таму, што менавіта гэты тып падтрымлівае працу з аргументам «Нумар вобласці».
- Адкрываецца акно аргументаў. У полі «Спасылка» нам трэба паказаць адрасы ўсіх трох дыяпазонаў. Для гэтага ўсталёўваны курсор у полі і вылучаем першы дыяпазон з заціснутай левай кнопкай мышы. Затым ставім кропку з коскай. Гэта вельмі важна, бо калі вы адразу пяройдзеце да выдзялення наступнага масіва, то яго адрас проста заменіць каардынаты папярэдняга. Такім чынам, пасля ўвядзення кропкі з коскі вылучаем наступны дыяпазон. Затым зноў ставім кропку з коскай і вылучаем апошні масіў. Усе выраз, якое знаходзіцца ў поле «Спасылка» бярэм у дужкі.
У полі «Нумар радка» паказваем лічбу "2", так як шукаем другую прозвішча ў спісе.
У полі "Нумар слупка» паказваем лічбу «3», так як калонка з заробкам з'яўляецца трэцяй па ліку ў кожнай табліцы.
У полі «Нумар вобласці» ставім лічбу «3», так як нам трэба знайсці дадзеныя ў трэцяй табліцы, у якой змяшчаецца інфармацыя аб заробку за трэці месяц.
Пасля таго, як усе дадзеныя ўведзеныя, пстрыкаем па кнопцы «OK».
- Пасля гэтага ў папярэдне выдзеленую вочка выводзяцца вынікі вылічэнні. Там адлюстроўваецца сума заработнай платы другога па ліку работніка (Сафронава В. М.) за трэці месяц.
Спосаб 4: вылічэнне сумы
Спасылачныя форма не так часта ўжываецца, як форма масіва, але яе можна выкарыстоўваць не толькі пры працы з некалькімі дыяпазонамі, але і для іншых патрэб. Напрыклад, яе можна ўжываць для разліку сумы ў камбінацыі з аператарам сум.
Пры складанні сумы сум мае наступны сінтаксіс:
= Сум (адрес_массива)
У нашым канкрэтным выпадку суму заробку ўсіх работнікаў за месяц можна вылічыць пры дапамозе наступнай формулы:
= Сум (C4: C9)
Але можна яе трохі мадыфікаваць, выкарыстаўшы функцыю індэкс. Тады яна будзе мець наступны выгляд:
= Сум (C4: індэкс (C4: C9; 6))
У гэтым выпадку ў каардынатах пачатку масіва паказваецца вочка, з якой ён пачынаецца. А вось у каардынатах ўказанні заканчэння масіва выкарыстоўваецца аператар індэкс. У дадзеным выпадку першы аргумент аператара індэкс паказвае на дыяпазон, а другі - на апошнюю яго вочка - шостую.
Урок: Карысныя функцыі Excel
Як бачым, функцыю індэкс можна выкарыстоўваць у Эксэля для вырашэння даволі рознапланавых задач. Хоць мы разгледзелі далёка не ўсе магчымыя варыянты яе прымянення, а толькі самыя запатрабаваныя. Існуе два тыпу гэтай функцыі: спасылачныя і для масіваў. Найбольш эфектыўна яе можна ўжываць у камбінацыі з іншымі аператарамі. Створаныя такім спосабам формулы змогуць вырашаць самыя складаныя задачы.