Дата: 3 cічня 2016 Категорія:

Пошук символів в рядку - найважливіше завдання, яке дозволяє автоматизувати роботу з текстом. Наприклад, вона полегшує, коли становище цільової рядки невідомо.

Інструмент пошуку в Ексель я торкнувся в, а тут ми розглянемо функції, які виконують пошук тексту в рядку і повертають його початкову позицію.

Для пошуку тексту в Microsoft Excel є дві функції:

1. ШУКАТИ () . Ця функція має 2 обов'язкових аргументу і один необов'язковий:
   • Текст для пошуку - та послідовність символів, яку потрібно знайти. Це обов'язковий аргумент
   • Початковий текст - рядок, в якій здійснюється пошук. обов'язковий аргумент
   • Початкова позиція - порядковий номер символу в початковому тексті, з якого починається пошук. необов'язковий аргумент. Якщо він не заданий, пошук починається з першого символу

функція ШУКАТИ враховує і не підтримує використання групових символів (Про них читайте далі).

Функція ШУКАТИ в Ексель

1. ПОШУК (Текст для пошуку; Початковий текст; [Початкова позиція]) має аналогічний синтаксис і аргументи. Відрізняється тим, що не враховує регістр і підтримує використання постановочних символів.

Функція ПОШУК в Excel

Як бачите, ця функція дала інший результат, оскільки регістр символів не враховувався в пошуку

Ви запитаєте, що таке групові символи? це спеціальні символи, Що дозволяють припустити невідому частину тексту для пошуку:

• ? - один будь-який символ
• * - будь-яка послідовність символів

Якщо знак питання або зірочка - частина рядка для пошуку, а не підстановлювальний символ, поставте перед ними знак тильда (~).

Давайте запишемо таку формулу: \u003d ПОШУК ( «?? чн»; A1; 1). Функція буде шукати рядок з двох будь-яких символів, після яких йдуть літери «чн». Пошуку відповідає частина слова «Табличний» в тексті прикладу, тобто комбінація символів «личн». Результатом функції буде значення «4».

Групові символи в Excel

Тепер запишемо формулу \u003d ПОШУК ( «п * с»; A2; 1). Функція шукає комбінацію «п» + будь-яку кількість будь-яких символів + «с». У прикладі - це частина слова «процесор». Результат виконання функції - 11.

Підстановлювальний символ-зірочка в Ексель

Володіючи функціями пошуку, вам простіше відстежити положення потрібного тексту, тому простіше його або замінити. Обов'язково практикуйтеся в їх застосуванні. У цьому вам допоможе мій пост-практикум по текстовим функцій.

А наступна стаття буде присвячена. Важлива тема, яку обов'язково потрібно вивчити!

Задавайте питання в коментарях і підписуйтесь на оновлення, щоб нічого не пропустити!

Робота з VB проектом (12)
Умовне форматування (5)
Списки і діапазони (5)
Макроси (VBA процедури) (63)
Різне (39)
Баги і глюки Excel (3)

Знайти в осередку будь-яке слово зі списку

Припустимо, ви отримуєте від постачальника / замовника / клієнта заповнену таблицю з переліком продукції:

І цю таблицю необхідно порівняти з артикулами / кодами товару в наявному у вас каталозі продукції:

Як видно - в нашому каталозі тільки артикули без найменувань. У замовника же крім артикулів ще й назва товару, тобто багато зайвого. І вам треба зрозуміти які товари присутні в вашому каталозі, а які ні:


Стандартних формул в Excel для подібного пошуку та порівняння немає. Звичайно, можна спробувати застосувати ВВР з підстановочних символами спочатку до однієї таблиці, а потім до іншого. Але якщо подібну операцію необхідно проробляти раз по раз, то прописувати по кілька формул до кожної таблиці прямо скажемо - не комільфо.
Тому я і вирішив сьогодні продемонструвати формулу, яка без всяких доп. маніпуляцій допоможе таке порівняння зробити. Щоб розібратися самостійно рекомендую скачати файл:
Завантажити файл:

(49,5 KiB, 13 249 завантажень)

На аркуші "Замовлення" в цьому файлі таблиця, отримана від замовника, а на аркуші "Каталог" наші артикули.
Сама формула на прикладі файлу буде виглядати так:

ПЕРЕГЛЯД (2; 1 / ПОШУК (Каталог! $ A $ 2: $ A $ 11; A2); Каталог! $ A $ 2: $ A $ 11)
\u003d LOOKUP (2,1 / SEARCH (Каталог! $ A $ 2: $ A $ 11, A2), Каталог! $ A $ 2: $ A $ 11)
ця формула поверне назву артикулу, якщо в тексті є хоч один артикул з каталогу і # Н / Д (# N / A) якщо артикул не знайдений в каталозі.
Перш ніж облагородити цю формулу всякими доповненнями (на кшталт вигляді прибирання непотрібних # Н / Д) давайте розберемося як вона працює.
Функція ПЕРЕГЛЯД (LOOKUP) шукає задане значення (2) в зазначеному діапазоні (масиві - другий аргумент). Як діапазону зазвичай наводиться масив осередків, але функція ПЕРЕГЛЯД має першу потрібну нам особливість - вона намагається перетворити безпосередньо в масив будь-який вираз, записане другим аргументом. Іншими словами вона обчислює вираз в цьому аргументі, ніж ми і користуємося, підставивши в якості другого аргументу вираз: 1 / ПОШУК (Каталог! $ A $ 2: $ A $ 11; A2). Частина ПОШУК (Каталог! $ A $ 2: $ A $ 11; A2) шукає по черзі кожне значення зі списку Каталогу в осередку A2 (найменування з таблиці Замовника). Якщо значення знайдено, то повертається номер позиції першого символу знайденого значення. Якщо значення, не знайдено - повертається значення помилки # значить! (# VALUE!). Тепер друга особливість: функція вимагає розташування даних в масиві в порядку зростання. Якщо дані розташовані інакше - функція буде переглядати масив до тих пір, поки не знайде значення більше шуканого, але максимально до нього наближений (хоча якщо дані дозволяють - для більш точного пошуку все ж краще впорядкувати список за зростанням). Тому спочатку ми 1 ділимо на вираз ПОШУК (Каталог! $ A $ 2: $ A $ 11; A2), щоб отримати масив виду: (+0,0181818181818182: # значить!: # Значить!: # Значить!: # Значить!: # Значить !: # значить!: # значить!: # значить!: # значить !}
Ну а в якості шуканого значення ми підсовує функції число 2 - свідомо більше число, ніж може взагалі зустрітися масиві (т.к. Одиниця, поділена на будь-яке число буде менше двох). І як результат ми отримаємо позицію в масиві, в якій зустрічається останній збіг з каталогу. Після чого функція ПЕРЕГЛЯД запам'ятає цю позицію і поверне значення з масиву Каталог! $ A $ 2: $ A $ 11 (третій аргумент), записане в цьому масиві для цієї позиції.
Ви можете переглянути етапи обчислення функції самостійно для кожного осередку, я тут просто приведу етапи трохи в розширеному для розуміння вигляді:

1. \u003d ПЕРЕГЛЯД (2; 1 / ПОШУК (Каталог! $ A $ 2: $ A $ 11; A2); Каталог! $ A $ 2: $ A $ 11)
2. \u003d ПЕРЕГЛЯД (2;
   1 / (55: # значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить;!}
   Каталог! $ A $ 2: $ A $ 11)
3. \u003d ПЕРЕГЛЯД (2; (+0,0181818181818182: # значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить;Каталог!$A$2:$A$11)!}
4. \u003d ПЕРЕГЛЯД (2;
   1;
   ( "FM2-3320": "CV455689": "Q5949X": "CE321A": "CE322A": "CE323A": "00064073": "CX292708": "CX292709": "CX292710"))
5. \u003d "FM2-3320"

Тепер трохи облагородимо функцію і зробимо ще пару реалізацій
Реалізація 1:
Замість артикулів і # Н / Д виведемо для знайдених позицій "Є", а для відсутніх "Не знайдено в каталозі":
\u003d ЕСЛИ (ЕНД (VIEW (2; 1 / ПОШУК (Каталог! $ A $ 2: $ A $ 11; A2))); "Не знайдено в каталозі"; "Є")
\u003d IF (ISNA (LOOKUP (2,1 / SEARCH (Каталог! $ A $ 2: $ A $ 11, A2))), "Не знайдено в каталозі", "Є")
робота функції проста - з ПЕРЕГЛЯД (LOOKUP) розібралися, тому залишилися тільки ЕНД і ЯКЩО.
ЕНД (ISNA) повертає ІСТИНА (TRUE) якщо вираз всередині неї повертає значення помилки # Н / Д (# N / A) і БРЕХНЯ (FALSE) якщо вираз всередині не повертає значення цієї помилки.
ЯКЩО (IF) повертає те, що зазначено другим аргументом якщо вираз в першому одно ІСТИНА (TRUE) і те, що зазначено третім аргументом, якщо вираз першого аргументу БРЕХНЯ (FALSE).

Реалізація 2:
Замість # Н / Д виведемо "Не знайдено в каталозі", але при цьому якщо артикули знайдені - виведемо назви цих артикулів:
\u003d ЕСЛІОШІБКА (VIEW (2; 1 / ПОШУК (Каталог! $ A $ 2: $ A $ 11; A2); Каталог! $ A $ 2: $ A $ 11); "Немає в каталозі")
\u003d IFERROR (LOOKUP (2,1 / SEARCH (Каталог! $ A $ 2: $ A $ 11, A2), Каталог! $ A $ 2: $ A $ 11), "Ні в каталозі")
Про функція ЕСЛІОШІБКА (IFERROR) я детально розповідав в цій статті: Як в осередку з формулою замість помилки показати 0.
Якщо коротко, то якщо вираз, заданий першим аргументом функції, повертає значення будь-якої помилки, то функція поверне те, що записано другим аргументом (в нашому випадку це текст "Не знайдено в каталозі"). Якщо ж вираз не повертає помилку, то функція ЕСЛІОШІБКА запише те значення, яке було отримано виразом в першому аргументі (в нашому випадку це буде найменування артикулу).

Реалізація 3
Треба не просто визначити якому артикулу відповідає, а й вивести ціну для найменування з цього артикулу (самі ціни повинні бути розташовані в стовпці B листа Каталог):
\u003d ЕСЛІОШІБКА (VIEW (2; 1 / ПОШУК (Каталог! $ A $ 2: $ A $ 11; A2); Каталог! $ B $ 2: $ B $ 11); "")
\u003d IFERROR (LOOKUP (2,1 / SEARCH (Каталог! $ A $ 2: $ A $ 11, A2), Каталог! $ B $ 2: $ B $ 11), "")

Пара важливих зауважень:

• дані на аркуші з артикулами не повинні містити порожніх клітинок. Інакше з великою часткою ймовірності формула буде повертати значення саме порожній осередку, а не те, яке підходить під умови пошуку
• формула здійснює пошук таким чином, що шукається будь-який збіг. Наприклад, в якості артикулу записана цифра 1 , А в рядку найменувань може зустрічатися крім цілої 1 ще й 123 , 651123 , FG1412NM і т.п. Для всіх цих найменувань може бути підібраний артикул 1, тому що він міститься в кожному найменуванні. Як правило це може статися, якщо артикул 1 розташований в кінці списку

Тому бажано перед використанням формули впорядкувати список по зростанню (від меншого до більшого, від А до Я).

У доданому на початку статті прикладі ви знайдете все розібрані варіанти.

Якщо ж вам знадобиться виводити всі найменування, то можна скористатися функцією СОДЕРЖІТ_ОДНО_ІЗ з моєї надбудови MulTEx.

Стаття допомогла? Поділися посиланням з друзями! Відео уроки

( "Bottom bar" :( "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1" 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity \u003d 60); "," titlecss ":" display: block; position: relative; font: bold 14px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; font: 12px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", sans-serif, Arial; color: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40))

Основне призначення офісної програми Excel - здійснення розрахунків. Документ цієї програми (Книга) може містити багато листів з довгими таблицями, заповненими числами, текстом або формулами. автоматизований швидкий пошук дозволяє знайти в них необхідні комірки.

простий пошук

Щоб зробити пошук значення в таблиці Excel, Необхідно на вкладці «Головна» відкрити список, що випадає інструменту «Знайти і замінити» і клацнути пункт «Знайти». Той же ефект можна отримати, використовуючи поєднання клавіш Ctrl + F.

У найпростішому випадку у вікні «Знайти і замінити» треба ввести шукане значення і клацнути «Знайти все».

Як видно, в нижній частині діалогового вікна з'явилися результати пошуку. Знайдені значення підкреслені червоним в таблиці. Якщо замість «Знайти всі» клацнути «Знайти далі», то спочатку буде проведений пошук першого осередку з цим значенням, а при повторному натисканні - другий.

Аналогічно проводиться пошук тексту. В цьому випадку в рядку пошуку набирається шуканий текст.

Якщо дані або текст шукається не у всій екселевскій таблиці, то область пошуку попередньо повинна бути виділена.

Розширений пошук

Припустимо, що потрібно знайти всі значення в діапазоні від 3000 до 3999. У цьому випадку в рядку пошуку слід набрати 3 ???. Підстановлювальний знак «?» замінює собою будь-який інший.

Аналізуючи результати зробленого пошуку, можна відзначити, що, поряд з правильними 9 результатами, програма також видала несподівані, підкреслені червоним. Вони пов'язані з наявністю в осередку або формулою цифри 3.

Можна задовольнятися більшістю отриманих результатів, ігноруючи неправильні. Але функція пошуку в Ексель 2010 здатна працювати набагато точніше. Для цього призначений інструмент «Параметри» в діалоговому вікні.

Натиснувши «Параметри», користувач отримує можливість здійснювати розширений пошук. Перш за все, звернемо увагу на пункт «Зона пошуку», в якому за замовчуванням виставлено значення «Формули».

Це означає, що пошук проводився, в тому числі і в тих осередках, де знаходиться не значення, а формула. Наявність в них цифри 3 дало три неправильних результату. Якщо в якості області пошуку вибрати «Значення», то буде проводитися тільки пошук даних і неправильні результати, пов'язані з осередками формул, зникнуть.

Для того щоб позбутися від єдиного, що залишився неправильного результату на першому рядку, у вікні розширеного пошуку потрібно вибрати пункт «Осередок повністю». Після цього результат пошуку стаємо точним на 100%.

Такий результат можна було б забезпечити, відразу вибравши пункт «Осередок повністю» (навіть залишивши в «Області пошуку» значення «Формули»).

Тепер звернемося до пункту «Шукати».

Якщо замість встановленого за замовчуванням «На аркуші» вибрати значення «У книзі», то немає необхідності знаходитися на аркуші шуканих осередків. На скріншоті видно, що користувач ініціював пошук, перебуваючи на порожньому аркуші 2.

Наступний пункт вікна розширеного пошуку - «Переглядати», який має два значення. За умовчанням встановлено «по рядках», що означає послідовність сканування осередків по рядках. Вибір іншого параметра - «по стовпцях», поміняє тільки напрямок пошуку і послідовність видачі результатів.

При пошуку в документах Microsoft Excel, можна використовувати і інший підстановлювальний знак - «*». Якщо розглянутий «?» означав будь-який символ, то «*» замінює собою не один, а будь-яку кількість символів. Нижче представлений скріншот пошуку по слову Louisiana.

Іноді при пошуку необхідно враховувати регістр символів. Якщо слово louisiana буде написано з маленької букви, то результати пошуку не зміняться. Але якщо у вікні розширеного пошуку вибрати «Враховувати регістр», то пошук виявиться безуспішним. Програма стане вважати слова Louisiana і louisiana різними, і, природно, не знайде перше з них.

різновиди пошуку

Пошук збігів

Іноді буває необхідно виявити в таблиці повторювані значення. Щоб зробити пошук збігів, спочатку потрібно виділити діапазон пошуку. Потім, на тій же вкладці «Головна» в групі «Стилі», відкрити інструмент «Умовне форматування». Далі послідовно вибрати пункти «Правила виділення осередків» і «Що повторюються значення».

Результат представлений на скріншоті нижче.

При необхідності користувач може поміняти колір візуального відображення збіглися осередків.

фільтрація

Інший різновид пошуку - фільтрація. Припустимо, що користувач хоче в стовпці B знайти числові значення в діапазоні від 3000 до 4000.

Як видно, відображатися стали тільки рядки, що задовольняють введеним умовою. Всі інші виявилися тимчасово прихованими. Для повернення до початкового стану слід повторити крок 2.

Різні варіанти пошуку були розглянуті на прикладі Excel 2010. Як зробити пошук в Ексель інших версій? Різниця в переході до фільтрації є в версії 2003. У меню «Дані» слід послідовно вибрати команди «Фільтр», «Автофільтр», «Умова» і «Користувацький автофильтр».

Відео: Пошук в таблиці Excel

В Excel Online для Windows можна успішно працювати за допомогою зовнішньої клавіатури. Для користувачів з обмеженими фізичними можливостями або поганим зором поєднання клавіш можуть бути більш зручними, ніж робота з сенсорним екраном або мишею. У цій статті описані сполучення клавіш, доступні при роботі з додатком Excel Online для Windows.

Примітки:

Сполучення клавіш у цій статті відповідають прийнятій в США розкладці клавіатури. Сполучення клавіш для інших розкладок клавіатури можуть відрізнятися від наведених тут.

У сполученнях, де одночасно натискається кілька клавіш, їх назви розділяються знаком плюс (+). Якщо ж клавіші натискаються послідовно, вони розділяються комою (,).

Часто використовувані сполучення клавіш

Тут перераховані найпоширеніші поєднання клавіш для Excel Online.

Дія
Перехід до певної осередку
переміщення вниз	PAGE DOWN або СТРІЛКА ВНИЗ
переміщення вгору	PAGE UP або зі стрілкою вгору
копіювання
вирізання
скасування операції
відкриття книги
закриття книги
напівжирний шрифт
Відкриття контекстного меню	клавіатура Windows: Клавіша виклику контекстного меню Windows+ F10. Клавіша виклику контекстного меню Windows знаходиться зліва між клавішами ALT і CTRL. Інші клавіатури: SHIFT + F10
помічник
	CTRL + F або SHIFT + F3
Повтор пошуку (вниз)
Повтор пошуку (вгору)
вставка діаграми

Клавіші доступу: поєднання клавіш для роботи зі стрічкою

В Excel Online тепер доступні клавіші доступу - поєднання клавіш для переміщення стрічкою. Вони використовуються так само, як і в класичній версії Excel, і дозволяють заощадити час при роботі.

В Excel 2016 року для Windows такі поєднання клавіш починалися з клавіші ALT. В Excel Online все такі поєднання починаються з клавіш ALT + WIN, разом з якими натискається кнопка, відповідна вкладці. Наприклад, вкладка "Рецензування" відкривається клавішами ALT + WIN + Р.

Якщо ви використовуєте Excel Online на комп'ютері Mac, Для початку роботи натисніть клавіші CTRL + OPT.

Щоб перейти на стрічку, натисніть клавіші ALT + WIN або за допомогою клавіш CTRL + F6, поки не опинитеся на вкладці "Головна".

Для переходу між вкладками стрічки натискайте клавішу TAB.

Щоб приховати стрічку і отримати більше місця для роботи, натисніть клавіші CTRL + F1. Щоб знову відобразити стрічку, повторіть цей крок.

Використання клавіш доступу для стрічки

Щоб перейти до вкладки на стрічці, натисніть одну із зазначених нижче клавіш доступу.

Дія
Перехід в поле "Що ви хочете зробити?" на стрічці для введення слова для пошуку.
Перехід на вкладку "Файл" для роботи з поданням Backstage.
Перехід на вкладку "Головна" і форматування тексту і чисел або використання інших інструментів (наприклад, для пошуку).
Перехід на вкладку "Вставка" і вставка функції, таблиці, діаграми, гіперпосилання або написи.
Перехід на вкладку "Дані" та оновлення зв'язків або використання коштів для роботи з даними.
Перехід на вкладку "Рецензування" і перевірка читаності або робота з примітками.
Відкрийте вкладку "Вид" для вибору уявлення, закріплення рядків або стовпців книги або відображення сітки або заголовків.

Робота з вкладками і меню стрічки

Сполучення клавіш з цієї таблиці допоможуть вам заощадити час при роботі з вкладками і меню стрічки.

Сполучення клавіш для Excel Online

Примітка: Якщо електронна таблиця в Excel Online відкривається в режимі читання, команди редагування не працюють. Щоб переключитися в режим редагування, перемістіть фокус за межі аркуша (натисніть CTRL + F6), а потім за допомогою клавіші TAB перейдіть до списку редагувати книгу. Натисніть ПРОБІЛ, потім клавішу СТРІЛКА ВНИЗ, а потім виберіть команду Змінити в Excel Online.

Команди Excel Online: редагування осередків

Дія	клавіші
Вставка рядків таблиці зверху	ALT + WIN + Я, ВВ, А
Вставка стовпців таблиці зліва	ALT + WIN + Я, ВВ, L
вирізання
копіювати
скасування операції
повтор операції
Перехід на новий рядок в осередку
вставка таблиці
вставка функції
Збільшення розміру шрифту
Зменшення розміру шрифту
застосування фільтру
Повторне застосування фільтра

Команди Excel Online: введення даних

Команди Excel Online: редагування даних у клітинці

Дія	клавіші
Редагування виділеної осередки
Технічна підтримка користувачів з обмеженими можливостями Корпорація Майкрософт прагне до того, щоб всі наші клієнти отримували найкращі продукти та обслуговування. Якщо у вас обмежені можливості або вас цікавлять питання, пов'язані зі спеціальними можливостями, Зверніться в службу Microsoft Disability Answer Desk для отримання технічної підтримки. Фахівці Microsoft Disability Answer Desk знайомі з багатьма популярними спеціальними можливостями і можуть надавати підтримку англійською, іспанською, французькою мовами, а також на американському жестовом мовою. Перейдіть на сайт Microsoft Disability Answer Desk, щоб дізнатися контактну інформацію для вашого регіону. Якщо ви представник державної установи або комерційної організації, зверніться в службу

У цій статті розглядається синтаксис функції ПОИСКПОЗ Excel, А також як використовувати функцію ПОИСКПОЗ в Excel з прикладами формул.

Також в цій статті розглядається комбінація формул ІНДЕКС ПОИСКПОЗ в Excel.

У Microsoft Excel існує безліч різних функцій пошуку, які можуть допомогти знайти певне значення в ряді осередків, а ПОИСКПОЗ - одна з них. ПОИСКПОЗ англійською MATCH, в основному, ідентифікує відносне положення елемента в діапазоні осередків. Однак функція ПОИСКПОЗ Excel в поєднанні з іншими функціями може зробити набагато більше.

Функція ПОИСКПОЗ в Excel - синтаксис і використання

Функція ПОИСКПОЗ в Excel шукає задане значення в діапазоні осередків і повертає відносне положення цього значення.

синтаксис формули ПОИСКПОЗ виглядає наступним чином:

ПОИСКПОЗ (шукане_значення; просматріваемий_массів; [тіп_сопоставленія])

шукане_значення (Обов'язковий аргумент) - значення, яке ви хочете знайти. Це може бути числове, текстове або логічне значення, а також посилання на осередок.

Просматріваемий_массів (Обов'язковий аргумент) - діапазон комірок для пошуку.

Тіп_сопоставленія (необов'язковий аргумент) - визначає тип відповідності. Це може бути одне з наступних значень: 1, 0, -1.

• 1 або відсутній (за замовчуванням) - знаходить найбільше значення в масиві, яке менше або дорівнює значенню пошуку. Масив пошуку повинен бути відсортований в порядку зростання, від найменшого до найбільшого або від A до Z.
• 0 - знаходить перше значення в масиві, яке в точності так само значенням пошуку. Сортування не потрібно.
• -1 - знаходить найменше значення в масиві, яке більше або дорівнює значенню пошуку. Масив пошуку повинен бути відсортований в порядку убування, від найбільшого до найменшого або від Z до A.

Щоб краще розібратися в даній функції, давайте напишемо просту формулу ПОИСКПОЗ, Засновану на наступних даних: прізвища студентів в стовпці A і їхні бали по іспитах в стовпці B, відсортовані від найвищих до найнижчих. Щоб дізнатися, де серед інших знаходиться конкретний студент (наприклад, студентка Виноградова), використовуйте цю просту формулу:

ПОИСКПОЗ (E1; A2: A8; 0)

При бажанні ви можете помістити значення пошуку в клітинку (E1 в даному прикладі) І послатися на цей осередок у формулі ПОИСКПОЗ Excel:

Функція ПОИСКПОЗ в Excel - Приклад використання функції ПОИСКПОЗ в Excel

Як ви бачите на зображенні вище, прізвища студентів вводяться в довільному порядку, тому ми встановлюємо аргумент тіп_сопоставленія рівним 0 (точний збіг), оскільки тільки цей тип відповідності не вимагає сортування значень в масиві пошуку. Технічно формула ПОИСКПОЗ повертає відносне положення студентки Виноградової в досліджуваному діапазоні. Але оскільки оцінки упорядковано від найвищих до найнижчих, це також говорить нам про те, що Виноградова п'ята за рахунком серед всіх учнів.

Особливості функції ПОИСКПОЗ Excel

Як ви тільки що бачили, використовувати ПОИСКПОЗ в Excel легко. Однак, як і у будь-якої функції Excel, у неї є кілька особливостей, про які слід знати:

• Функція ПОИСКПОЗ повертає відносне положення значення пошуку в масиві, а не самого значення.
• ПОИСКПОЗ не залежить від регістра, Що означає, що вона не розрізняє великі та малі символи при роботі з текстовими значеннями.
• Якщо масив пошуку містить кілька входжень шуканого значення, повертається позиція першого значення.
• Якщо шукане значення, не знайдено в просматриваемом масиві, повертається помилка # Н / Д.

Використання ПОИСКПОЗ в Excel - приклади формул

Тепер, коли ви знаєте основні можливості функції ПОИСКПОЗ в Excel, Давайте розглянемо ще кілька прикладів формул, які виходять за рамки основ.

Формула ПОИСКПОЗ Excel з підстановочних знаками

Як і багато інших функцій Excel, ПОИСКПОЗ розпізнає такі символи узагальнення:

• Знак питання (?) - замінює будь-який символ
• Зірочка (*) - замінює будь-яку послідовність символів

Примітка. Символи узагальнення можуть використовуватися тільки в формулах ПОИСКПОЗ з параметром тіп_сопоставленія рівному 0.

Формула ПОИСКПОЗ з підстановочних знаками корисна в ситуаціях, коли потрібно здійснити пошук не всієї текстової рядки, а тільки деяких символів або деякої частини рядка. Щоб проілюструвати це, розглянемо наступний приклад.

Припустимо, що у вас є список компаній і дані їх продажів за останній тиждень. Ви хочете знайти відносне положення певної компанії в списку (відсортовані за обсягом продажів в порядку убування), але ви не можете точно пригадати його ім'я, хоча ви пам'ятаєте кілька перших букв.

Припускаючи, що назви компаній знаходяться в діапазоні A2: A8, і ви шукаєте назву, що починається з «схід», формула виглядає наступним чином:

ПОИСКПОЗ ( "схід *"; A2: A8; 0)

Щоб зробити нашу формулу ПОИСКПОЗ більш універсальною, ви можете ввести значення пошуку в будь-якому осередку (E1 в цьому прикладі) і цей осередок з Групові символи, наприклад:

ПОИСКПОЗ (E1 & "*"; A2: A8; 0)

Як показано на зображенні нижче, формула повертає 2 позицію, яка відповідає компанії «Схід-авто»:

Функція ПОИСКПОЗ в Excel - Приклад функції ПОИСКПОЗ Excel з підстановочних знаками

Щоб змінити лише один символ в значенні пошуку, використовуйте підстановлювальний оператор «?», Наприклад:

ПОИСКПОЗ ( "б? Йкер"; A2: A8; 0)

Вищевказана формула буде відповідати назві «Бейкер» і поверне його відносне положення, яке дорівнює 5.

Формула ПОИСКПОЗ з урахуванням регістру

Як уже згадувалося на початку цієї статті, функція ПОИСКПОЗ Excel не розрізняє символи верхнього і нижнього регістра. Щоб створити формулу відповідності з урахуванням регістра, використовуйте ПОИСКПОЗ в поєднанні з функцією збігаючись, яка в точності порівнює осередки, включаючи регістр символу.

Чутливість до регістру формули для зіставлення даних в Excel полягає в наступному:

ПОИСКПОЗ (ІСТИНА; збігаючись (шукане_значення; просматріваемий_массів); 0)

Логіка цієї формули така:

• Функція збігаючись порівнює значення пошуку з кожним елементом проглядається масиву. Якщо порівнювані осередки в точності рівні, функція повертає істину, або БРЕХНЯ в іншому випадку.
• Далі функція ПОИСКПОЗ порівнює істину (яка є її іскомим_значеніем) з кожним значенням в масиві, повернутому збігаючись, і повертає позицію першого збігу

Зверніть увагу, Що це формула масиву, яка вимагає натиснення Ctrl + Shift + Enter для правильної обробки.

Нехай значення пошуку знаходиться в осередку E1, а масив пошуку - A2: A8, тоді формула виглядає наступним чином:

ПОИСКПОЗ (ІСТИНА; збігаючись (A2: A8; E1); 0)

На наступному зображенні демонструється формула ПОИСКПОЗ з урахуванням регістра в Excel:

Функція ПОИСКПОЗ в Excel - Приклад функції ПОИСКПОЗ Excel з урахуванням регістру

Порівняти 2 стовпці на збіги і відмінності (ЕНД і ПОИСКПОЗ)

Перевірка двох списків на збіги і відмінності - одна з найбільш поширених завдань в Excel, і це можна зробити різними способами. Використання формули ЕНД / ПОИСКПОЗ є одним з них:

ЯКЩО (ЕНД (ПОИСКПОЗ (1-е значення у Списку 1; Список 2; 0)); "Немає в Списку 1"; "")

Для будь-якого значення зі Списку 2, відсутнього в Списку 1, формула повертає «Ні в Списку 1». І ось як це відбувається:

• Функція ПОИСКПОЗ виконує пошук значення зі Списку 1 у Списку 2. Якщо значення знайдено, вона повертає відносне положення, в іншому випадку помилку # Н / Д.
• Функція ЕНД в Excel виконує тільки одне: перевіряє наявність помилок # Н / Д (що означає «недоступно»). Якщо задане значення є помилкою # Н / Д, функція повертає ІСТИНА, в іншому випадку - БРЕХНЯ. У нашому випадку ІСТИНА означає, що значення зі Списку 1 цієї статті не знайдено в Списку 2 (ПОИСКПОЗ повертає помилку # Н / Д).
• Оскільки для інших користувачів буде складно інтерпретувати результати в форматі ІСТИНА / БРЕХНЯ, то ми будемо використовувати функцію ЯКЩО для відображення тексту «Ні в Списку 1» для значень, яких немає в Списку 1.

Припустимо, що нам необхідно порівняти значення в стовпці B зі значеннями в стовпці A, формула прийме таку форму (де B2 - сама верхня осередок):

ЯКЩО (ЕНД (ПОИСКПОЗ (B2; A: A; 0)); "Немає в Списку 1"; "")

Як ви пам'ятаєте, функція ПОИСКПОЗ Excel сама по собі не враховує регістр. Щоб змусити її розрізняти регістр, вставте функцію збігаючись в аргумент просматріваемий_массів і не забудьте натиснути Ctrl + Shift + Enter, щоб завершити цю формулу масиву:

ЯКЩО (ЕНД (ПОИСКПОЗ (ІСТИНА; збігаючись (A: A; B2); 0)); "Немає в Списку 1"; "")

На наступному зображенні демонструються обидві формули в дії:

Функція ПОИСКПОЗ в Excel - Порівняння 2 стовпців на збіги з використанням ЕНД і ПОИСКПОЗ

ІНДЕКС та ПОИСКПОЗ в Excel

З функцією ПОИСКПОЗ Excel ми вже знайомі. Давайте тепер розглянемо, як можна використовувати комбінацію ІНДЕКС ПОИСКПОЗ в Excel. Почнемо з синтаксису функції ІНДЕКС.

Синтаксис і використання функції ІНДЕКС

функція ІНДЕКС Excel повертає значення в масиві, засноване на зазначених вами рядках і шпальтах. Синтаксис функції ІНДЕКС простий:

ІНДЕКС (масив; номер_рядка; [номер_стовпчика])

Ось дуже просте пояснення кожного параметра:

• масив - це діапазон комірок, з якого ви хочете повернути значення.
• номер_рядка - номер рядка в масиві, з якого ви хочете повернути значення. Якщо цей параметр опущений, потрібно номер_стовпчика.
• номер_стовпчика - номер стовпця в масиві, з якого ви хочете повернути значення. Якщо цей параметр опущений, потрібно номер_рядка.

Якщо використовуються обидва параметри номер_рядка і номер_стовпчика, функція ІНДЕКС повертає значення в осередку на перетині зазначеного рядка і стовпця.

ось найпростіший приклад формули ІНДЕКС:

ІНДЕКС (A1: C10; 2; 3)

Формула шукає в осередках від A1 до C10 і повертає значення клітинки в другому рядку і третьому стовпці, тобто в осередку C2.

Дуже легко, чи не так? Однак при роботі з реальними даними ви навряд чи знаєте, які рядки і стовпці вам потрібні, тому вам потрібна допомога функції ПОИСКПОЗ.

ІНДЕКС ПОИСКПОЗ в Excel приклад

Тепер, коли ви знаєте синтаксис і основи цих двох функцій, у вас напевно вже склалося розуміння того, як працюють ІНДЕКС ПОИСКПОЗ Excel.

функція ПОИСКПОЗ визначає відносне положення значення пошуку в зазначеному діапазоні осередків. А функція ІНДЕКС приймає це число і повертає значення в відповідну клітинку.

комбінацію ІНДЕКС ПОИСКПОЗ в Excel можна уявити таким чином:

ІНДЕКС (стовпець для повернення значення, ПОИСКПОЗ (значення пошуку, стовпець для пошуку, 0))

Для кращого розуміння розглянемо наочний приклад. Припустимо, у нас є список країн з населенням, подібних до цього:

Функція ПОИСКПОЗ в Excel - Вихідні дані для формули ІНДЕКС ПОИСКПОЗ в Excel

Знайдемо чисельність населення, наприклад, Росії, використовуючи наступну формулу ІНДЕКС ПОИСКПОЗ:

ІНДЕКС ($ C $ 2: $ C $ 11; ПОИСКПОЗ ( "Росія"; $ B $ 2: $ B $ 11; 0))

Тепер давайте розберемо, що насправді виконує кожен компонент цієї формули:

• Функція ПОИСКПОЗ виконує пошук значення «Росія» в стовпці B, точніше в осередках B2: B11 і повертає номер 7, тому що «Росія» знаходиться на сьомому місці в списку.
• Функція ІНДЕКС приймає «7» у другому параметрі (номер_рядка), який вказує, з якого рядка ви хочете повернути значення, і перетворюється в просту формулу \u003d ІНДЕКС ($ C $ 2: $ C $ 11, 7). Тобто формула здійснює пошук в осередках C2-C11 і повертає значення клітинки в сьомому рядку, тобто осередку C8, тому що ми починаємо відлік з другого рядка.

І ось результат, який ми отримуємо в Excel:

Функція ПОИСКПОЗ в Excel - ІНДЕКС ПОИСКПОЗ приклад формули

Зверніть увагу! Кількість рядків і стовпців в масиві ІНДЕКС повинна відповідати значенням в параметрах номер_рядка і / або номер_стовпчика функції ПОИСКПОЗ відповідно. В іншому випадку формула поверне невірний результат.