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