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