Маємо таблицю, в якій записані обсяги продажу певних товарів у різних місяцях. Необхідно в таблиці знайти дані, а критерієм пошуку будуть заголовки рядків та стовпців. Але пошук має бути виконаний окремо по діапазону рядка чи стовпця. Тобто використовуватиметься лише один із критеріїв. Тому тут не можна застосувати функцію ІНДЕКС, а потрібна спеціальна формула.

Пошук значень у таблиці Excel

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

Лист з таблицею для пошуку значень по вертикалі та горизонталі:

Над самою таблицею розташований рядок з результатами. У комірку B1 ведемо критерій для пошукового запиту, тобто заголовок стовпця або назву рядка. А в осередку D1 формула пошуку має повертати результат обчислення відповідного значення. Після чого в осередку F1 спрацює друга формула, яка вже буде використовувати значення осередків B1 і D1 як критерії для пошуку відповідного місяця.

Пошук значення у рядку Excel

Тепер дізнаємося, в якому максимальному обсязі та в якому місяці був максимальний продаж Товару 4.

Щоб виконати пошук по стовпцям слід:

Знайдено в якому місяці і який був найбільший продаж Товару 4 протягом двох кварталів.

Принцип дії формули пошуку значення у рядку Excel:

У першому аргументі функції ВПР (Вертикальний перегляд) вказується посилання на комірку де знаходиться критерій пошуку. У другому аргументі вказується діапазон осередків перегляду у процесі пошуку. У третьому аргументі функції ВПР повинен вказуватися номер стовпця, з якого слід взяти значення на рядок з ім'ям Товар 4. Але так як нам заздалегідь не відомий цей номер ми за допомогою функції СТІЛКА створюємо масив номерів стовпців для діапазону B4:G15.

Це дозволяє функції ВВР зібрати цілий масив значень. В результаті в пам'яті зберігається всі відповідні значення кожного стовпця по рядку Товар 4 (а саме: 360; 958; 201; 605; 462; 832). Після чого функції МАКС залишається лише взяти з цього масиву максимальне число і повернути як значення для клітинки D1, як результат обчислення формули.

Як видно, конструкція формули проста і лаконічна. На її основі можна схожий спосіб знаходити для певного товару та інші показники. Наприклад, мінімальне або середнє значення обсягу продажів використовуючи для цього функції МІН або СРЗНАЧ. Вам ні що не перешкоджає, щоб цей скелет формули застосувати з використанням більш складних функцій для реалізації максимально комфортного аналізу звіту з продажу.

Як отримати заголовки стовпців із зачинення одного осередку?

Наприклад, як ефектно ми відобразили місяць, у якому був максимальний продаж, за допомогою другої формули. Не важко помітити, що у другій формулі ми використовували скелет першої формули без функції МАКС. Головна структура формули: ВПР (B1; A5: G14; СТІЛБЕЦЬ (B5: G14); 0). Ми замінили функцію МАКС на ПОШУКПОЗ, яка у першому аргументі використовує значення, отримане попередньою формулою. Воно тепер виступає як критерій для пошуку місяця. І в результаті функція ПОШУКПОЗ нам повертає номер стовпця 2, де знаходиться максимальне значення обсягу продажів для товару 4. Після чого в роботу включається функція ІНДЕКС, яка повертає значення за номером терміни та стовпця з визначеного в її аргументах діапазону. Оскільки ми маємо номер стовпця 2, а номер рядка в діапазоні де зберігаються назви місяців у будь-які випадки буде 1. Тоді нам залишилося функцією ІНДЕКС отримати відповідне значення з діапазону B4:G4 – Лютий (другий місяць).



Пошук значення у стовпці Excel

Другим варіантом завдання буде пошук за таблицею з використанням назви місяця як критерій. У такі випадки ми повинні змінити скелет нашої формули: функцію ВПР замінити ДПР, а функція СТІЛКА замінюється на РЯДОК.

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

Щоб знайти який товар мав максимальний обсяг продажів у визначеному місяці слід:

Принцип дії формули пошуку значення у стовпці Excel:

У першому аргументі функції ГПР (Горизонтальний перегляд) вказуємо посилання на комірку з критерієм для пошуку. У другому аргументі вказано посилання на діапазон таблиці, що переглядається. Третій аргумент генерує функція РЯДКУ, яка створює в пам'яті масив номерів рядків із 10 елементів. Оскільки в табличній частині ми маємо 10 рядків.

Далі функція ДПР по черзі використовуючи кожен номер рядка створює масив відповідних значень продажів з таблиці за певним місяцем (червнем). Далі функції МАКС залишилося лише вибрати максимальне значення із цього масиву.

УВАГА! При використанні скелета формули для інших завдань завжди звертайте увагу на другий та третій аргумент пошукової функції ГПР. Кількість охоплених рядків у діапазоні зазначеного в аргументі має збігатися з кількістю рядків у таблиці. А також нумерація має починатися з другого рядка!

Доброго дня, шановні хаброжителі!

Іноді деяким (а може й більше, ніж деяким) з нас доводиться стикатися із завданнями з обробки невеликих масивів даних, починаючи від складання та аналізу домашнього бюджету і закінчуючи будь-якими розрахунками по роботі, навчанню тощо. Мабуть, найбільш підходящим інструментом для цього є Microsoft Excel (або інші його аналоги, але вони менш поширені).

Пошук видав мені лише одну статтю на Хабре за схожою тематикою - "Талмуд за формулами в Google SpreadSheet". У ній дано хороший опис базових речей для роботи в Excel (хоча він і не 100% про сам Excel).

Таким чином, накопичивши певний пул запитів/завдань, з'явилася ідея їх типізувати та запропонувати можливі рішення (нехай не всі можливі, але такі, що швидко дають результат).

Йтиметься про вирішення найпоширеніших завдань, з якими стикаються користувачі.

Опис рішень побудовано так – дається кейс, що містить вихідне завдання, яке поступово ускладнюється, до кожного кроку дано розгорнуте рішення з поясненнями. Найменування функцій будуть даватися російською мовою, але в дужках при першій згадці наводиться оригінальне найменування англійською мовою (т.к. з досвіду у переважної більшості користувачів встановлена ​​російськомовна версія).

Кейс_1: Логічні функції та функції пошуку збігів
«У мене є набір значень у табличці і необхідно, щоб при виконанні певної умови/набору умов виводилося певне значення» (с) Користувач

Дані, як правило, представлені в табличній формі:

Умова:

• якщо значення у стовпці «Кількість» більше 5,
• то потрібно вивести в колонці "Результат" значення "Замовлення не потрібно",

У цьому нам допоможе формула «ЯКЩО» (IF), яка відноситься до логічних формул і може видавати у вирішенні будь-які значення, які ми заздалегідь записуємо у формулі. Звертаю увагу, що будь-які текстові значення записуються, використовуючи лапки.

Синтаксис формули наступний:
ЯКЩО(лог_вираз, [значення_якщо_істина], [значення_якщо_брехня])

• Лог_вираз - вираз, що дає в результаті значення ІСТИНА або брехня.
• Значення_якщо_істина - значення, яке виводиться, якщо логічне вираз істинно
• Значення_якщо_брехня - значення, яке виводиться, якщо логічне вираз хибне

Синтаксис формули для вирішення:

=якщо(C5>5;«Замовлення не потрібно»;«Необхідне замовлення»)

На виході отримуємо результат:

Буває, що умова носить складніший характер, наприклад виконання 2-х і більше умов:

• якщо значення в стовпці "Кількість" більше 5, а значення в колонці "Тип" дорівнює "А"

В даному випадку ми вже не можемо обмежитися використанням однієї лише формули «ЯКЩО», необхідно додати до її синтаксису іншу формулу. І це буде ще одна логічна формула І (AND).
Синтаксис формули наступний:
І(логічне_значення1, [логічне_значення2], ...)

• Логічне_значення1-2 і т.д. - Перевірена умова, обчислення якої дає значення ІСТИНА або брехня

Виведення результату в осередок D2:

=ЯКІ(І(C2>5;B2=«А»);1;0)

Таким чином, використовуючи поєднання 2-х формул, ми знаходимо рішення нашої задачі та отримуємо результат:

Спробуємо ускладнити завдання – нова умова:

• якщо значення в стовпці "Кількість" дорівнює 10, а значення в колонці "Тип" дорівнює "А"
• або значення в стовпці «Кількість» більше або дорівнює 5, а значення «Тип» дорівнює «Б»
• то потрібно вивести в колонці «Результат» значення «1», інакше «0».

Синтаксис рішення буде наступним:
Виведення результату в осередок D2:

=якщо(АБО(І(C2=10;B2=«А»); І(C2>=5;B2=«Б»));1;0)

Як видно з запису, у формулу «ЯКЩО» включено одну умову «АБО» (OR) та дві умови з використанням формули «І», включених до нього. Якщо хоча б одна з умов 2-го рівня має значення «ІСТИНА», то в колонку «Результат» буде виведено результат «1», інакше буде «0».
Результат:

Тепер перейдемо до наступної ситуації:
Уявимо, що в залежності від значення в стовпці «Умова» має виводитися певна умова в стовпці «Результат», нижче наведено відповідність значень та результату.
Умова:

• 1 = А
• 2 = Б
• 3 = В
• 4 = Г

При розв'язанні задачі за допомогою функції «ЯКЩО» синтаксис буде наступним:

=якщо(A2=1;«А»; ЯКЩО(A2=2;«Б»; ЯКЩО(A2=3;«В»; ЯКЩО(A2=4;«Г»;0))))

Результат:

Як видно, написання подібної формули не тільки не дуже зручно та громіздко, але й може зайняти деякий час на її редагування у недосвідченого користувача у разі помилки.
Мінус подібного підходу в тому, що він застосовується для невеликої кількості умов, адже всі їх доведеться набирати вручну і «роздмухувати» нашу формулу до великих розмірів, однак підхід відрізняє повна «всеїдність» до значень та універсальність використання.

Альтернативне рішення_1:
Використання формули «ВИБІР» (CHOOSE),
Синтаксис функції:
ВИБІР(номер_індексу, значення1, [значення2], ...)

• Номер_індексу - номер вибирається аргумент-значення. Номер індексу повинен бути числом від 1 до 254 формулою або посиланням на комірку, що містить число в діапазоні від 1 до 254.
• Значение1, значение2,… - значення від 1 до 254 аргументів-значень, у тому числі функція «ВИБІР», використовуючи номер індексу, вибирає значення чи виконувану дію. Аргументи можуть бути числами, посиланнями на комірки, певними іменами, формулами, функціями чи текстом.

При її використанні ми відразу заносимо результати умов залежно від зазначених значень.
Умова:

• 1 = А
• 2 = Б
• 3 = В
• 4 = Г

Синтаксис формули:

= ВИБІР (A2; "А"; "Б"; "В"; "Г")

Результат аналогічний рішенню з ланцюжком функцій «ЯКЩО» вище.
При застосуванні цієї формули існують такі обмеження:
У комірку «А2» (номер індексу) можуть бути вказані лише цифри, а значення результату виводитимуться в порядку зростання від 1 до 254 значень.
Іншими словами, функція буде працювати лише якщо в осередку «А2» вказані цифри від 1 до 254 у порядку зростання, і це накладає певні обмеження при використанні цієї формули.
Тобто. якщо ми захочемо, щоб значення «Г» виводилося при вказівці числа 5,

• 1 = А
• 2 = Б
• 3 = В
• 5 = Г

то формула матиме наступний синтаксис:
Виведення результату в осередок B2:

=ВИБІР(A31;«А»;«Б»;«В»;;«Г»)

Як видно, значення "4" у формулі нам доводиться залишити порожнім і перенести результат "Г" на порядковий номер "5".

Альтернативне рішення_2:
Ось ми і підійшли до однієї з найпопулярніших функцій Excel, оволодіння якою автоматично перетворює будь-якого офісного працівника на «досвідченого користувача excel» /sarcasm/.
Синтаксис формули:
ВПР(шукане_значення, таблиця, номер_стовпця, [інтервальний_перегляд])

• Шукане значення - значення, пошук якого здійснюється функцією.
• Таблиця - діапазон осередків, що містить дані. Саме в цих осередках відбуватиметься пошук. Значення можуть бути текстовими, числовими чи логічними.
• Номер_стовпця - номер стовпця в аргументі «Таблиця», з якого виводитиметься значення у разі збігу. Важливо розуміти, що відлік стовпців відбувається за загальною сіткою листа (A.B,C,D тощо.), а всередині масиву, вказаного в аргументі «Таблиця».
• Інтервальний_перегляд – визначає, який збіг має знайти функція – точне або приблизне.

Важливо:функція «ВПР» шукає збіг тільки за першим унікальним записом, якщо шукане значення присутнє в аргументі «Таблиця» кілька разів і має різні значення, то функція «ВПР» знайде тільки самий ПЕРШИЙ збіг, результати по всіх інших збігах показані не будуть» Використання формули «ВПР» (VLOOKUP) пов'язане із ще одним підходом у роботі з даними, а саме з формуванням «довідників».
Суть підходу у створенні «довідника» відповідності аргументу «Шукане значення» певному результату, окремо від основного масиву, в якому прописуються умови та відповідні їм значення:

Потім робочої частини таблиці вже прописується формула з посиланням на довідник, заповнений раніше. Тобто. у довіднику в стовпці «D» відбувається пошук значення зі стовпця «А» і при знаходженні відповідності виводиться значення зі стовпця «Е» в стовпець «В».
Синтаксис формули:
Виведення результату в осередок B2:

Результат:

Тепер уявімо ситуацію, коли необхідно підтягнути дані в одну таблицю з іншого, у своїй таблиці не ідентичні. Див. приклад нижче

Видно, що рядки в стовпцях "Продукт" обох таблиць не співпадають, однак це не є перешкодою для використання функції "ВПР".
Виведення результату в осередок B2:

Але при вирішенні стикаємося з новою проблемою - при "протягування" написаної нами формули праворуч від стовпця "В" до стовпця "Е", нам доведеться вручну замінювати аргумент "номер_стовпця". Справа ця трудомістка і невдячна, тому, на допомогу нам приходить інша функція - «Стовпець» (COLUMN).
Синтаксис функції:
СТРІЛКА([посилання])

• Посилання – осередок або діапазон осередків, для яких потрібно повернути номер стовпця.

Якщо використовувати запис типу:

то функція виведе номер поточного стовпця (у комірці якого написано формулу).
В результаті виходить число, яке можна використовувати у функції «ВПР», чим ми скористаємося і отримуємо наступний запис формули:
Виведення результату в осередок B2:

=ВПР($A3;$H$3:$M$6; СТІЛБЕЦЬ();0)

Функція "Стовпчик" визначить номер поточного стовпця, який буде використовуватися аргументом "Номер_стовпця" для визначення номера стовпця пошуку в довіднику.
Крім того, можна використовувати конструкцію:

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

Продовжуємо розвивати тему та ускладнюємо умову: уявімо, що у нас є два довідники з різними даними по продуктах і необхідно вивести в таблицю з результатом значення залежно від того, який тип довідника вказано у колонці «Довідник»
Умова:

• Якщо в стовпці «Довідник» зазначено число 1, дані повинні тягнутися з таблиці «Довідник_1», якщо число 2, то з таблиці «Довідник_2» відповідно до зазначеного місяця

Варіант рішення, який відразу спадає на думку, наступний:

=ЯКЩО($B3=1; ВПР($A3;$G$3:$I$6; СТІЛБЕЦЬ)-1;0); ))

Плюси: найменування довідника може бути будь-яким (текст, цифри та їх поєднання), мінуси – погано підходить, якщо варіантів більше трьох.
Якщо ж номери довідників завжди є числами, має сенс використовувати наступне рішення:
Виведення результату в осередок C3:

=ВПР($A3; ВИБІР($B3;$G$3:$I$6;$K$3:$M$6); СТІЛЕЦЬ()-1;0)

Плюси: формула може включати до 254 найменувань довідників, мінуси – їх найменування має бути строго числовим.
Результат для формули з використанням функції «ВИБІР»:

Бонус: ВПР за двома і більше ознаками в аргументі «шукане значення».
Умова:

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

Обидві таблиці наведено нижче:

Як видно з табличних форм, кожна позиція має не тільки найменування (яке не є унікальним), але також відноситься до певного класу і має свій варіант фасування.
Використовуючи поєднання імені та класу та фасування, ми можемо створити нову ознаку, для цього в таблиці з даними створюємо додатковий стовпець «Додатковий знак», який заповнюємо за допомогою наступної формули:

Використовуючи символ «&», об'єднуємо три ознаки на один (розділювач між словами може бути будь-яким, як і не бути зовсім, головне використовувати аналогічне правило і для пошуку)
Аналогом формули може бути функція «ЗЧЕПИТИ» (CONCATENATE), у цьому випадку вона виглядатиме таким чином:

=СЦЕПИТИ(H3;"_";I3;"_";J3)

Після того, як додаткова ознака створена для кожного запису в таблиці з даними, приступаємо до написання функції пошуку за цією ознакою, яка матиме вигляд:
Виведення результату в осередок D3:

=ЯСЛИПОМИЛКА(ВПР(A2&"_"&B2&"_"$G$2:$K$6;5;0);0)

У функції «ВПР» як аргумент «шукане_значення» використовуємо все ту ж зв'язку трьох ознак (найменування_клас_фасування), але беремо її вже в таблиці для заповнення і заносимо безпосередньо в аргумент (як варіант, можна було б виділити значення для аргументу в додатковий стовпець таблиці для заповнення, але це буде зайвим).
Нагадую, що використання функції «ЄСПОМИЛКА» (IFERROR) необхідно, якщо шукане значення так і не буде знайдено, і функція «ВПР» виведе нам значення «#Н/Д» (про це нижче).
Результат на малюнку нижче:

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

Кейс_3 Пошук значення в масиві, або коли ВПР не може нам допомогти

Розглянемо ситуацію, коли потрібно зрозуміти, чи є в масиві осередків необхідні нам значення.
Завдання:

• у стовпці «Умова пошуку» вказано значення і необхідно визначити, чи є воно у стовпці «Масив для пошуку»

Візуально все виглядає у такому вигляді:

Як бачимо, функція «ВПР» тут безсила, т.к. ми шукаємо не точне збіг, саме наявність у осередку потрібного нам значення.
Для вирішення завдання необхідно використовувати комбінацію кількох функцій, а саме:
«ЯКЩО»
«ЯСЛИПОМИЛКА»
«СТРОК»
«ЗНАЙТИ»

По порядку про всіх, «ЯКЩО» ми вже розібрали раніше, тому перейдемо до функції «ЯСЛИПОМИЛКА» (IFERROR)

ЕСЛИПОМИЛКА (значення, значення_помилки)

• Значення - аргумент, що перевіряється виникнення помилок.
• Значення_помилки - значення, що повертається при помилці при обчисленні за формулою. Можливі такі типи помилок: #Н/Д, #ЗНАЧ!, #ПОСИЛКА!, #СПРАВ/0!, #ЧИСЛО!, #ІМ'Я? і #ПУСТО!

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

«СТРОЧН» (LOWER)

• Текст - текст, що перетворюється на нижній регістр.

Важливо: функція «СТРОЧН» не замінює символи, що не є літерами.
Роль у формулі: оскільки функція «ЗНАЙТИ» (FIND) здійснює пошук та обліком регістру тексту, то необхідно привести весь текст до одного регістру, інакше «чАй» буде не одно «чай» і т.д. Це актуально, якщо значення регістру не є умовою пошуку та відбору значень, інакше формулу «СТРОК» можна не використовувати, так пошук буде більш точним.

Тепер докладніше про синтаксис функції «ЗНАЙТИ» (FIND).

ЗНАЙТИ(шуканий_текст, переглядається_текст, [поч_позиція])

• Шуканий текст - текст, який необхідно знайти.
• Текст, що переглядається - текст, в якому потрібно знайти шуканий текст.
• Нач_позиция - знак, з якого потрібно розпочати пошук. Перший знак у тексті «текст, що переглядається» має номер 1. Якщо номер не вказаний, він за замовчуванням вважається рівним 1.

Синтаксис формули-рішення матиме вигляд:
Виведення результату в осередок B2:

=ЯКЛИ(ЯКІ ПОМИЛКА(ЗНАЙТИ(СТРОЧН(A2); СТРОЧН(E2);1);0)=0;«fail»;«bingo!»)

Розберемо логіку формули за діями:

1. РЯДКОВ(A2) – перетворює аргумент «Шуканий_текст» в осередку в А2 в текст з нижнім регістром
2. Функція «ЗНАЙТИ» починає пошук перетвореного аргументу «Шуканий_текст» в масиві «Терст, що переглядається», який перетворюється функцією «СТРОЧН(E2)», також в текст з нижнім регістром.
3. Якщо, функція знаходить збіг, тобто. повертає порядковий номер першого символу слова/значення, що збігається, спрацьовує умова ІСТИНА у формулі «ЯКЩО», т.к. отримане значення не дорівнює нулю. Як результат, у стовпці "Результат" буде виведено значення "Bingo!"
4. Якщо ж, функція знаходить збіг тобто. порядковий номер першого символу слова/значення, що збігається, не вказується і замість значення повертається помилка, спрацьовує умова, закладена в формулу «ЯСЛИПОМИЛКА» і повертається значення рівне «0», що відповідає умові брехня у формулі «ЯКЩО», т.к. отримане значення дорівнює "0". Як результат, у стовпці "Результат" буде виведено значення "fail".

Як видно з малюнка вище, завдяки функціям «СТРОЧН» і «ЗНАЙТИ» ми знаходимо значення, що знаходять незалежно від регістру символів, і місця знаходження в осередку, але необхідно звернути увагу на рядок 5.
Умова пошуку задана як «111», але в масиві пошуку вказано значення «1111111 печенюшки», проте формула видає результат «Bingo!». Це тому, що значення «111» входить у ряд значень «1111111», як наслідок перебуває збіг. У протилежному випадку ця умова не спрацює.

Кейс_4 Пошук значення в масиві за кількома умовами, або коли ВПР тим більше не може нам допомогти

Уявімо ситуацію, коли необхідно знайти значення з «Таблиця з результатом» у двовимірному масиві «Довідник» за кількома умовами, а саме за значенням «Найменування» та «Місяць».
Таблична форма завдання матиме такий вигляд:

Умова:

• У таблицю з результатом необхідно підтягнути дані відповідно до збігу умов «Найменування» та «Місяць».

Для вирішення подібного завдання підійде комбінація функцій «ІНДЕКС» та «ПОШУКПОЗ»

Синтаксис функції "ІНДЕКС" (INDEX)

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

• Масив - діапазон осередків, з якого показуватимуться значення у разі збігу умов їх пошуку.
• Якщо масив містить лише один рядок або один стовпець, аргумент «рядок» або «номер_стовпця» відповідно не є обов'язковим.
• Якщо масив займає більше одного рядка та одного стовпця, а з аргументів «номер_рядка» та «номер_стовпця» заданий лише один, то функція «ІНДЕКС» повертає масив, що складається з цілого рядка або цілого стовпця аргументу «масив».
• Номер_рядка - номер рядка в масиві, з якого потрібно повернути значення.
• Номер_стовпця - номер стовпця в масиві, з якого потрібно повернути значення.

Іншими словами функція повертає із зазначеного масиву в аргументі "Масив" значення, яке знаходиться на перетині координат, вказаних у аргументах "Номер_рядка" та "Номер_стовпця".

Синтаксис функції «ПОШУКПОЗ» (MATCH)

ПОШУКПОЗ(шукане_значення, що переглядається_масив, [тип_порівняння])

• Шукане_значення - значення, яке зіставляється зі значеннями в аргументі проглядається_масив. Аргумент шукане значення може бути значенням (числом, текстом або логічним значенням) або посиланням на комірку, що містить таке значення.
• Масив, що переглядається - діапазон осередків, в яких проводиться пошук.
• Тип_порівнювання - необов'язковий аргумент. Число -1, 0 чи 1.

Функція ПОШУКПОЗ шукає вказаний елемент у діапазоні осередків і повертає відносну позицію цього елемента в діапазоні.
Суть використання комбінації функцій «ІНДЕКС» і «ПОШУКПОЗ» в тому, що ми проводимо пошук координат значень щодо їх найменування по «осях координат».
Осю Y буде стовпець «Найменування», а віссю X – рядок «Місяць».

Частина формули:

ПОШУКПОЗ($A4;$I$4:$I$7;0)

повертає число по осі Y, у разі воно дорівнюватиме 1, т.к. значення «А» є у шуканому діапазоні і має відносну позицію «1» у цьому діапазоні.
частина формули:

ПОШУКПОЗ(B$3;$J$3:$L$3;0)

повертає значення #Н/Д, т.к. значення «1» відсутнє в діапазоні, що переглядається.

Таким чином, ми отримали координати точки (1; #Н/Д), які функція «ІНДЕКС» використовує для пошуку в аргументі «Масив».
Повністю написана функція для осередку B4 матиме такий вигляд:

=ІНДЕКС($J$4:$L$7; ПОШУКПОЗ($A4;$I$4:$I$7;0); ПОШУКПОЗ(B$3;$J$3:$L$3;0))

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

=ІНДЕКС($J$4:$L$7;1;#Н/Д))

Оскільки аргумент «Номер_стовпця» має значення «#Н/Д», результат для осередку «B4» буде відповідний.
Як очевидно з результату в повному обсязі значення у таблиці з результатом знаходять збіг з довідником й у результаті бачимо, частина значень у таблиці виводиться як «#Н/Д», що утрудняє використання даних для подальших розрахунків.
Результат:

Щоб нейтралізувати цей негативний ефект використовуємо функцію «ЕЛИПОМИЛКА», про яку ми читали раніше, і замінюємо значення, що повертається при помилці на «0», тоді формула матиме вигляд:

=ЕСЛИПОМИЛКА(ІНДЕКС($J$4:$L$7; ПОШУКПОЗ($A4;$I$4:$I$7;0); ПОШУКПОЗ(B$3;$J$3:$L$3;0));0)

Демонстрація результату:

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

Кейс_5 Пошук значення в діапазоні чисел

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

• Від 0 до 1000 = А
• Від 1001 до 1500 = Б
• Від 1501 до 2000 = В
• Від 2001 до 2500 = Г
• Більше 2501 = Д

Функція ПЕРЕГЛЯД (LOOKUP) повертає значення з рядка, стовпця або масиву. Функція має дві синтаксичні форми: векторну та форму масиву.

ПРОГЛЯД(шукане_значення; переглядається_вектор; [вектор_результатів])

• Шукане значення - значення, яке функція ПЕРЕГЛЯД шукає в першому векторі. Значення, що шукається, може бути числом, текстом, логічним значенням, ім'ям або посиланням на значення.
• Вектор - діапазон, що складається з одного рядка або одного стовпця. Значення в аргументі Вектор, що переглядається, можуть бути текстом, числами або логічними значеннями.
• Значення в аргументі проглядається_вектор повинні бути розташовані в порядку зростання: ..., -2, -1, 0, 1, 2, ..., A-Z, брехня, ІСТИНА; інакше функція ПЕРЕГЛЯД може повернути неправильний результат. Текст у нижньому та верхньому регістрах вважається еквівалентним.
• Вектор_результатів - діапазон, що складається з одного рядка чи стовпця. Вектор_результатів повинен мати той же розмір, що і переглядається_вектор.

=ПРОГЛЯД(E3;$A$3:$A$7;$B$3:$B$7)

Аргументи «Вектор, що переглядається» і «Вектор_результату» можна записати у формі масиву – у цьому випадку не доведеться виводити їх в окрему таблицю на аркуші Excel.
У цьому випадку функція буде виглядати так:
Виведення результату в осередок B3:
=ПРОГЛЯД(E3;(0;1001;1501;2001;2501);(«А»;«Б»;«В»;«Г»;«Д»))

Кейс_6 Підсумовування чисел за ознаками

Для підсумовування чисел за певними ознаками можна використовувати три різні функції:
СУМІСЛІ (SUMIF) – підсумовує лише за однією ознакою
СУМІСЛІМН (SUMIFS) – підсумовує за безліччю ознак
СУМПРОВИЗВ (SUMPRODUCT) – підсумовує за безліччю ознак
Існує також варіант з використанням «СУМ» (SUM) та функції формули масивів, коли формула «СУМ» зводиться в масив:
((=СУМ(()*()))
але такий підхід досить незручний і повністю перекривається за функціоналом формулою «СУММПРОІЗВ»
Тепер докладніше щодо синтаксису «СУММПРОІЗВ»:

СУММПРОИЗВ(масив1, [масив2], [масив3],...)

• Масив1 – перший масив, компоненти якого потрібно перемножити, а потім скласти результати.
• Масив2, масив3… - від 2 до 255 масивів, компоненти яких потрібно перемножити, та був скласти результати.

Умова:

• Знайти загальну суму вартості відвантажень по кожному з продуктів за певний період:

Як видно з таблиці з даними, щоб порахувати вартість необхідно ціну помножити на кількість, а отримане значення, застосувавши умови відбору переносити в таблицю з результатом.
Проте формула «СУММПРОІЗ» дозволяє проводити такі розрахунки всередині формули.
Виведення результату в осередок B4:

=СУММПРОИЗВ(($A4=$H$3:$H$11)*($K$3:$K$11>=B$3)*($K$3:$K$11
Розберемо формулу частинами:
– задаємо умову щодо відбору у стовпці «Найменування» таблиці з даними по стовпцю «Найменування» у таблиці з результатом

($K$3:$K$11>=B$3)*($K$3:$K$11 – задаємо умову за тимчасовими рамками, дата більша чи дорівнює першого числа поточного місяця, але менше першого числа місяця наступного. Аналогічно – умова у таблиці з результатом, масив – у таблиці з даними.
– перемножуємо стовпці «Кількість» та «Ціна» у таблиці з даними.
Безсумнівним плюсом цієї функції є вільний порядок запису умов, їх можна записувати у порядку, результат це не вплине.
Результат:

Тепер ускладнимо умову і додамо вимогу, щоб відбір за найменуванням «печінки» відбувався тільки за класами «малі» та «великі», а за найменуванням «булки» всі, крім за класом «з джемом»:

Виведення результату в осередок B4:

=СУММПРОИЗВ(($A4=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11
У формулі для відбору печінки додалася нова умова:

(($I$3:$I$11=«малі»)+($I$3:$I$11=«великі»))

– як видно, дві або більше умов по одному стовпцю виділяються в окрему групу за допомогою символу «+» та укладання умов у додаткові дужки.
У формулі для відбору по булках також додалася нова умова:

=СУММПРОИЗВ(($A5=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11 «з джемом»);($L$3:$L$11)*($K$3:$K$11))

це:

($I$3:$I$11<>«з джемом»)

- насправді, у цій формулі можна було написати умову відбору так само як і при відборі по печінках, але тоді, довелося б перераховувати три умови у формулі, в даному випадку, простіше написати виняток - не одно «з джемом» для цього використовуємо значення «<>».
Взагалі, якщо групи ознак/класів заздалегідь відомі, краще об'єднувати їх у ці групи, створюючи довідники, ніж записувати всі умови у функцію, роздмухуючи її.
Результат:

Що ж, ось ми і підійшли до кінця нашого короткого мануалу, який насправді міг би бути набагато більшим, але метою було все-таки дати вирішення ситуацій, що зустрічаються, а не описувати рішення приватних (але набагато цікавіших випадків).
Сподіваюся, що мануал допоможе кому-небудь у вирішенні завдань за допомогою Excel, адже це буде означати, що моя праця не зникла!

Дякую за приділений час!

Привіт друзі. Як часто вам доводиться для якогось значення шукати відповідність у таблиці Ексель? Наприклад, потрібно в довіднику знайти адресу людини, або в прайсі ціну товару. Якщо такі завдання зустрічаються – ця посада саме для вас!

Я виконую подібні процедури щодня і без описаних нижче функцій мені справді довелося б важко. Беріть на замітку та застосовуйте їх у роботі!

Пошук у таблиці Ексель, функції ВПР та ГПР

Роль цих функцій у житті звичайного користувача важко переоцінити. Тепер можна легко знайти в таблиці даних відповідний запис та повернути відповідне їй значення.

Синтаксис функції ВПР такий: = ВПР ( Шукане значення; таблиця_для_пошуку; номер_виводимого_стовпця; [тип_порівняння]). Розглянемо аргументи:

• Шукане значення– значення, яке шукатимемо. Це обов'язковий аргумент;
• Таблиця для пошуку- Той масив осередків, в якому буде пошук. Стовпець з потрібними значеннями повинен бути першим у цьому масиві. Це також обов'язковий аргумент;
• Номер виведеного стовпця- Порядковий номер стовпця (починаючи з першого в масиві), з якого функція виведе дані при збігу значень, що шукаються. Обов'язковий аргумент;
• Тип зіставлення– виберіть «1» (або «ІСТИНА») для суворого збігу, «0» («БРЕХНЯ») – для повного збігу. Аргумент необов'язковий, якщо його упустити – буде виконано пошук несуворого збігу.

Пошук точного збігу за допомогою ВПР

Подивимося з прикладу, як працює функція ВПР, коли обраний тип зіставлення «БРЕХНЯ», пошук точного збігу. У масиві В5:Е10 вказані основні засоби компанії, їх балансова вартість, інвентарний номер і місце розташування. У осередку В2 зазначено найменування, котрій потрібно у таблиці знайти інвентарний номер і помістити їх у осередок С2 .

Функція ВПР в Excel

Запишемо формулу: = ВПР (B2; B5: E10; 3; Брехня) .

Тут перший аргумент показує, що у таблиці необхідно шукати значення з осередку В2 , тобто. слово "Факс". Другий аргумент каже, що таблиця для пошуку - в діапазоні В5: Е10, а шукати слово "Факс" потрібно в першому стовпці, тобто. в масиві В5: В10. Третій аргумент повідомляє програмі, що результат розрахунку міститься у третьому стовпці масиву, тобто. D5: D10. Четвертий аргумент дорівнює «брехня», тобто. потрібен повний збіг.

Так, функція отримає рядок «Факс» з осередку В2 і шукатиме його в масиві В5: В10 зверху вниз. Щойно збіг буде знайдено (рядок 8), функція поверне відповідне значення зі шпальти D , тобто. вміст D8. Саме це нам і потрібно, завдання вирішено.

Якщо значення не буде знайдено, функція поверне .

Пошук неточного збігу за допомогою ВВР

Завдяки цій опції в роботі ВПР ми можемо уникнути складних формул, щоб знайти потрібний результат.

У масиві В5:С12 вказано процентні ставки за кредитами залежно від суми позики. В осередку В2 вказуємо суму кредиту і хочемо отримати в С2 ставку для такої угоди. Завдання складне тим, що сума може бути будь-якою і навряд чи співпадатиме із зазначеними в масиві, пошук за точним збігом не підходить:

Тоді запишемо формулу не суворого пошуку: =ВПР(B2;B5:C12;2;ІСТИНА). Тепер з усіх представлених у стовпці В даних програма шукатиме найближче менше. Тобто для суми 8 000 буде відібрано значення 5000 і виведено відповідний відсоток.

Несуворий пошук ВПР в Excel

Для коректної роботи функції необхідно відсортувати перший стовпець таблиці за зростанням. Інакше вона може дати помилковий результат.

Функція ГПР має такий самий синтаксис, як і ВПР, але шукає результату не в стовпцях, а рядках. Тобто, переглядає таблиці не зверху вниз, а ліворуч і виводить заданий номер рядка, а не стовпця.

Пошук даних за допомогою функції ПЕРЕГЛЯД

Функція ПРОГЛЯД працює аналогічно ВПР, але має інший синтаксис. Я використовую її, коли таблиця даних містить кілька десятків стовпців і для використання ВПР потрібно додатково прораховувати номер колонки, що виводиться. У таких випадках функція ПРОГЛЯД полегшує завдання. І так, синтаксис: = ПЕРЕГЛЯД( Шукане значення; Масив_для_пошуку; Масив для відображення) :

• Шукане значення- Дані або посилання на дані, які потрібно шукати;
• Масив для пошуку– один рядок чи стовпець, у якому шукаємо аналогічне значення. Цей масив обов'язково сортуємо за зростанням;
• Масив для відображення- Діапазон, що містить дані для виведення результатів. Звичайно, він повинен одного розміру з масивом для пошуку.

За такого запису ви даєте не відносне посилання масиву результатів. А на нього вказуєте, тобто. не потрібно попередньо прораховувати номер стовпця, що виводиться. Використовуємо функцію ПЕРЕГЛЯД у першому прикладі для функції ВПР (основні засоби, інвентарні номери): =ПРОГЛЯД(B2;B5:B10;D5:D10). Завдання успішно вирішено!

Функція «ПРОГЛЯД» у Microsoft Excel

Пошук за відносними координатами. Функції ПОШУКПОЗ та ІНДЕКС

Ще один спосіб пошуку даних - комбінування функцій ПОШУКПОЗ та ІНДЕКС.

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

• Шукане значення- Обов'язковий аргумент
• Масив, що переглядається– один рядок чи стовпець, у якому шукаємо збіг. Обов'язковий аргумент
• Тип зіставлення– вкажіть "0" для пошуку точного збігу, "1" - найближче менше, "-1" - найближче більше. Оскільки функція проводить пошук з початку списку в кінець, при пошуку найближчого меншого - відсортуйте стовпець пошуку за спаданням. А при пошуку більшого – сортуйте його за зростанням.

Позиція необхідного значення знайдена, тепер можна вивести його на екран за допомогою функції ІНДЕКС( Масив; Номер рядка; [Номер_стовпця]) :

• Масив– аргумент вказує з якого масиву осередків потрібно вибрати значення
• Номер рядка– вказуєте порядковий номер рядка (починаючи з першого осередку масиву), який потрібно вивести. Тут можна записати значення вручну або використовувати результат обчислення іншої функції. Наприклад, ПОШУКПОЗ.
• Номер стовпця– необов'язковий аргумент, вказується, якщо масив складається із кількох стовпців. Якщо аргумент упущено, формула використовує перший стовпець таблиці.

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

Функції ПОШУКПОЗ та ІНДЕКС в Екселі

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

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

Серед тисяч рядків та десятків стовпців даних вручну у таблиці Ексель знайти щось практично неможливо. Єдиний варіант, це скористатися якоюсь функцією пошуку і далі ми розглянемо, як здійснюється пошук у таблиці Excel.

Для пошуку даних у таблиці Excel необхідно використовувати пункт меню «Знайти та виділити»на вкладці «Головна», в якому потрібно вибирати варіант «Знайти»або скористатися для виклику комбінацією клавіш "Ctrl + F".

Наприклад спробуємо знайти необхідне число серед даних нашої таблиці, оскільки саме під час пошуку чисел необхідно враховувати деякі тонкощі пошуку. Шукатимемо в таблиці Excel число "10".

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

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

Для правильного пошуку даних у таблиці Ексель слід натиснути кнопку "Параметри"і налаштувати область пошуку. Зараз шукане значення шукається навіть у формулах, що використовуються в осередках для розрахунків. Нам необхідно вказати пошук тільки в значеннях і при бажанні можна ще вказати формат шуканих даних.

При пошуку слів у таблиці Excel слід також враховувати всі ці тонкощі і, наприклад, можна враховувати навіть регістр літер.

Ну і наостанок розглянемо, як зробити пошук даних в Екселі тільки в потрібній області аркуша. Як видно з нашого прикладу, потрібне значення "10"зустрічається одночасно у всіх стовпцях даних. Якщо необхідно це значення знайти, припустимо, тільки в першому стовпці, необхідно виділити даний стовпець або будь-яку область значень, в якій необхідно зробити пошук, а потім вже починати пошук.

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

Як знайти значення в іншій таблиці або сила ВПР

Насправді я в цій статті хочу розповісти про можливості не лише функції ВВР, але так само хочу торкнутися і ПОШУКПОЗяк дуже споріднену з ВПР функцію. Кожна з цих функцій має свої плюси, так і мінуси. Якщо двома словами, то ВПР шукає якесь вказане нами значення серед безлічі значень, розташованих у одному стовпці. Мабуть найчастіше необхідність у ВВР виникає коли треба порівняти дані, знайти дані в іншій таблиці, з однієї таблиці додати дані в іншу, спираючись на будь-який критерій і т.д.
Щоб краще зрозуміти принцип роботи ВПР краще почати з якогось практичного прикладу. Є таблиця такого виду:
рис.1

і з першої таблиці необхідно підставити на другу дату для кожного прізвища. Для трьох записів це не проблема і руками зробити – все очевидно. Але в житті це таблиці на тисячі записів та пошук із підстановкою даних вручну може зайняти не одну годину. Плюс ще пара ложок дьогтю: мало того, що ПІБ розташовані в різному порядку в обох таблицях і кількість записів в таблицях різне, так ще таблиці розташовані на різних аркушах/книгах. Я вважаю, що переконав вас, що підстановка даних руками взагалі не варіант. Зате ВПР (VLOOKUP)тут буде незамінна. При цьому практично нічого не треба буде робити - тільки записати в перший осередок стовпця З другої таблиці (туди, куди слід підставити дати з першої таблиці)таку формулу:
=ВПР($A2 ;Лист1!$A$2:$C$4 ;3;0)
Записати формулу можна або безпосередньо в осередок, або скориставшись диспетчером функцій, вибравши в категорії Посилання та масивиВПР та окремо вказавши потрібні критерії. Тепер копіюємо( Ctrl+C) осередок з формулою, виділяємо всі осередки стовпця С до кінця даних і вставляємо ( Ctrl+V).

Спочатку основний принцип роботи: ВПР шукає у першому стовпці аргументу Таблиця значення, вказане аргументом Шукане_значення . При знаходженні потрібного значення функція повертає значення напроти знайденого значення, але зі стовпця, вказаного аргументом Номер_стовпця . З інтервальним переглядом розберемося трохи згодом. ВПР може відновити лише одне значень - перше, відповідне критерій. Якщо шукане значення не знайдено (відсутня у таблиці), то результатом функції буде #Н/Д . Не треба цього боятися – це навіть корисно. Ви точно знатимете, яких записів немає і таким чином можете порівнювати дві таблиці один з одним. Іноді виходить так, що Ви бачите, що дані є в обох таблицях, але ВПР видає #Н/Д. Значить, дані у Ваших таблицях не ідентичні. В одній із них є зайві непримітні прогалини (зазвичай перед значенням або після), або знаки кирилиці перемішані зі знаками латиниці. Так само #Н/Д буде, якщо критерії числа та в таблиці вони записані як текст (Як правило в лівому верхньому кутку такого осередку з'являється зелений трикутничок), а підсумкової - як числа. Чи навпаки.

Опис аргументів ВПР
$A2 - аргумент Шукане_значення(назвемо його Критерійдля стислості). Це те, що ми шукаємо. Тобто. для першого запису другої таблиці це буде Петров С.А. Тут можна вказати або безпосередньо текст критерію(в цьому випадку він повинен бути в лапках - =ВПР("Петров С.А" ;Лист1!$A$2:$C$4;3;0) , або посилання на комірку, з цим текстом (як у прикладі функції). Є невеликий нюанс: також можна застосовувати символи підстановки: "*" і "?". Це дуже зручно, якщо Вам потрібно знайти значення лише в частині рядка. Наприклад, Ви можете не вводити повністю "Петров С.А", а ввести лише прізвище та знак зірочки - "Петров*". Тоді буде виведено будь-який запис, який починається на "Петрів". Якщо ж Вам треба знайти запис, в якому в будь-якому місці рядка зустрічається прізвище "Петрів", то можна вказати так: "*петров*". Якщо хочете знайти прізвище Петров і будь-які ініціали будуть у імені-по батькові (якщо ПІБ записані у вигляді Іванов І.І.), то тут саме такий вигляд: "Іванов?.?." . Часто необхідно для кожного рядка вказати своє значення (у стовпці А Прізвища і треба їх знайти). У разі завжди вказуються посилання осередки стовпця А. Наприклад, в осередку A1 записано: Іванов. Так само відомо, що Іванов є в іншій таблиці, але після прізвища можуть бути записані і ім'я та по батькові (або ще щось). Але нам потрібно знайти лише рядок, який починається на прізвище. Тоді необхідно записати так: A1 &"*" . Цей запис буде рівнозначний "Іванов*" . В A1 записано Іванов, амперсанд(&) використовується для об'єднання в один рядок двох текстових значень. Зірочка в лапках (як і належить бути тексту всередині формули). Таким чином і отримуємо:
A1&"*" =>
"Іванів"&"*" =>
"Іванів*"
Дуже зручно, якщо значень для пошуку є багато.
Якщо треба визначити, чи є хоч десь слово в рядку, то зірочки ставимо з обох боків: "*"& A1 &"*"

Аркуш1!$A$2:$C$4 - аргумент Таблиця. Вказується діапазон осередків. Тільки діапазон повинен містити дані від першого осередку з даними до останньої. Це не обов'язково має бути вказаний у прикладі діапазон. Якщо рядків 100, то Аркуш1!$A$2:$C$100. Важливо пам'ятати три речі: перше, це Таблиця завжди має починатися з того стовпця, в якому шукаємо Критерій . І ніяк інакше. Інакше нічого не буде знайдено або результат буде зовсім не той, якого очікуєте. Друге: аргумент Таблиця має бути "закріплений" . Що це означає. Бачите знаки долара – $? Це і є закріплення (якщо точніше, то це називається абсолютним посиланням на діапазон). Як це робиться. Виділяєте текст посилання (тільки один діапазон – один критерій)і тиснете F4 до тих пір, поки не побачите, що перед позначенням імені стовпця і перед номером рядка не з'явилися долари. Якщо цього не зробити, то при копіюванні формули аргумент Таблиця "з'їжджатиме" і результат знову-таки буде невірним. І останнє - таблиця повинна містити стовпці від першого (у якому шукаємо) до останнього (з якого необхідно повертати значення). У прикладі Аркуш1!$A$2:$C$4- отже вдасться повернути значення зі стовпця D(4), т.к. у таблиці лише три стовпці.

3 - Номер_стовпця. Тут просто вказуємо номер стовпця в аргументі Таблиця, значення з якого нам необхідно підставити як результат. У прикладі це Дата прийняття – тобто. стовпець №3. Якби потрібен був відділ, то вказали б 2, а якби нам знадобилося просто порівняти, чи є прізвища однієї таблиці в іншій, то можна було б вказати і 1. Важливо: аргумент Номер_стовпця не повинен перевищувати кількість стовпців в аргументі Таблиця . Інакше результатом формули буде помилка #ПОСИЛАННЯ!. Наприклад, якщо в якості вказаний діапазон $B$2:$C$4 і необхідно повернути дані зі стовпця, то правильно вказати 2. Т.к. аргумент Таблиця($B$2:$C$4) містить лише два стовпці - В і С. Якщо ж спробувати вказати номер стовпця 3(яким за рахунком він є на аркуші), то отримаємо помилку #ПОСИЛАННЯ!, т.к. третього стовпця у вказаному діапазоні просто немає.

Практична порада: якщо аргумент Таблиця має занадто велику кількість стовпців і Вам необхідно повернути результат з останнього стовпця, то зовсім необов'язково вираховувати їх кількість. Можна вказати так: =ВПР($A2 ;Лист1! $A$2:$C$4 ;ЧИСЛСТОЛБ(Лист1! $A$2:$C$4);0) . До речі в цьому випадку Лист1! теж можна прибрати, як зайве: =ВПР($A2 ;Лист1! $A$2:$C$4 ;ЧИСЛСТОЛБ($A$2:$C$4);0) .

0 - Інтервальний_перегляд- Дуже цікавий аргумент. Може дорівнювати або ІСТИНА або брехня. Відразу постає питання: а чому в моїй формулі там 0? Все дуже просто - Excel у формулах може сприймати 0 як брехня, а 1 як ІСТИНА. Якщо в ВПР вказати даний параметр рівний 0 або брехня, то буде відбуватися пошук точної відповідності заданому критерію. Це не має жодного відношення до знаків підстановки ("*" та "?"). Якщо ж використовувати 1 або ІСТИНА (або взагалі не вказувати останній аргумент, т.к. за замовчуванням він дорівнює ІСТИНА), то ... Дуже довга історія. Коротко - ВПР шукатиме найбільш схоже значення, що підходить під Критерій . Іноді дуже корисно. Щоправда, якщо використовувати цей параметр, необхідно, щоб список у аргументі Таблиця був відсортований за зростанням. Звертаю увагу на те, що сортування необхідне тільки в тому випадку, якщо аргумент Інтервальний_перегляд у Вас дорівнює ІСТИНА або 1. Якщо ж 0 або брехня - сортування не потрібне.

Багато хто напевно помітив, що на картинці у мене поплутані відділи для ПІБ. Це не помилка запису. У прикладі, що додається до прикладу, показано, як можна однією формулою підставити і їх і дати, не змінюючи вручну аргумент Номер_стовпця. Мені здалося, що подібний приклад може стати в нагоді.

Як уникнути помилки #Н/Д(#N/A) у ВПР?
Ще часто проблема - багато хто не хоче бачити #Н/Д результатом, якщо збіг не знайдено. Це легко оминути:
=ЯКЩО(ЕНД(ВПР($A2 ;Лист1! $A$2:$C$4 ;3;0));"";ВПР($A2 ;Лист1! $A$2:$C$4 ;3;0))))
Тепер якщо ВПР не знайде збігу, то осередок буде порожнім.
А користувачам версій Excel 2007 і вище можна використовувати ЄЛИПОМИЛКА:
=ЕСЛИПОМИЛКА(ВПР($A2 ;Лист1! $A$2:$C$4 ;3;0);"")

Обіцяна ПОШУКПОЗ

Ця функція шукає значення, вказане параметром Шукане_значенняв аргументі Масив, що переглядається. А результатом функції є номер позиції знайденого значення Проглядається_масиві. Саме номер позиції, а чи не саме значення. У принципі її я не розписуватиму так само докладно, тому що основні моменти рівно такі ж. Якби ми хотіли застосувати її для таблиці вище, вона була б такою:
=ПОШУКПОЗ($A2 ;Лист1! $A$2:$A$4 ;0)
$A2 - Шукане значення. Тут все так само, як і з ВПР. Так само допустимі символи підстановки і в такому ж виконанні.

Лист1! $A$2:$A$4 - Массив, що переглядається. Основна відмінність від ВПР – допускається вказати масив лише з одним стовпцем. Це має бути той стовпець, у якому ми збираємося шукати Шукане_значення . Якщо вказати більше одного стовпця, то функція поверне помилку.

Тип_порівняння(0) - те саме, що й у ВПР Інтервальний_перегляд . З тими самими особливостями. Відрізняється хіба що можливістю пошуку найменшого від шуканого чи найбільшого. Але на цьому я не зупинятимуся в цій статті.

З головним розібралися. Але нам треба повернути не номер позиції, а саме значення. Значить ПОШУКПОЗ у чистому вигляді нам не підходить. Принаймні одна сама по собі. Але якщо її використовувати разом з функцією ІНДЕКС - це те, що нам потрібно і навіть більше.
=ІНДЕКС(Лист1! $A$2:$C$4 ;ПОШУКПОЗ($A2 ;Лист1! $A$2:$A$4 ;0);2)
Така формула результатом поверне те саме, що й ВПР.

Аргументи функції ІНДЕКС
Лист1! $A$2:$C$4 - Масив. Як цей аргумент ми вказуємо діапазон, з якого хочемо отримати значення. Можливо як один стовпець, так і кілька. Якщо стовпець один, то останній аргумент функції вказувати не треба. До речі - даний аргумент може зовсім не співпадати з тим, який ми вказуємо в аргументі функцій ПОШУКПОЗ.

Далі йдуть Номер_рядки та Номер_стовпця. Саме як Номера_рядка ми і підставляємо ПОШУКПОЗ, яка повертає нам номер позиції в масиві. На цьому все й ґрунтується. ІНДЕКС повертає значення з Масиву, яке знаходиться у вказаному рядку(Номер_рядка) Масиву та вказаному стовпці(Номер_стовпця), якщо стовпців більше одного. Важливо знати, що в даній зв'язці кількість рядків в аргументі Масив функції ІНДЕКС і кількість рядків в аргументі Масив, що переглядається, функції ПОШУКПОЗ має збігатися. І починатися з одного і того ж рядка. Це у звичайних випадках, якщо Ви не переслідуєте інші цілі.
Як і у випадку з ВПР, ІНДЕКС у разі не знаходження шуканого значення повертає #Н/Д. І оминути подібні помилки можна так само:
Для всіх версій Excel (включаючи 2003 і раніше):
=ЯКЩО(ЕНД(ПОШУКПОЗ($A2 ;Лист1! $A$2:$A$4 ;0));"";ІНДЕКС(Лист1! $A$2:$C$4 ;ПОШУКПОЗ($A2 ;Лист1! $A$2: $A$4 ;0);2))
Для версій 2007 та вище:
=ЕСЛИПОМИЛКА(ІНДЕКС(Лист1! $A$2:$C$4 ;ПОШУКПОЗ($A2 ;Лист1! $A$2:$A$4 ;0);2);"")

Робота з критеріями довше 255 символів
Є у ІНДЕКС-ПОШУКПОЗ і ще одна перевага перед ВПР. Справа в тому, що ВПР не може шукати значення, довжина рядка яких містить більше 255 символів. Це трапляється рідко, але трапляється. Можна, звичайно, обдурити ВПР і урізати критерій:
=ВПР(ПСТР($A2 ;1;255);ПСТР(Лист1!$A$2:$C$4 ;1;255);3;0)
але це формула масиву. Та й, до того ж, далеко не завжди така формула поверне потрібний результат. Якщо перші 255 символів ідентичні першим 255 символам у таблиці, а далі знаки різняться – формула цього вже не побачить. Та й формула повертає виключно текстові значення, що у випадках, коли повертатися повинні числа, не дуже зручно.

Тому краще використовувати таку хитру формулу:
=ІНДЕКС(Лист1!$A$2:$C$4 ;СУМПРОИЗВ(ПОШУКПОЗ(ІСТИНА;Лист1!$A$2:$A$4 =$A2 ;0));2)
Тут я у формулах використовував однакові діапазони для легкочитаності, але в прикладі для скачування вони відрізняються від зазначених тут.
Сама формула побудована на можливості функції СУММПРОІЗВ перетворювати на масивні обчислення деяких функцій усередині неї. В даному випадку ПОШУКПОЗ шукає позицію рядка, в якій критерій дорівнює значенню в рядку. Підстановочні символи тут застосувати не вдасться.

У прикладі, що додається до статті, Ви знайдете приклади використання всіх описаних випадків і приклад того, чому ІНДЕКС і ПОШУКПОЗ часом краще ВПР.

Завантажити приклад

(26,0 KiB, 14 615 завантажень)

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

("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"," :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","text ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"2 textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; background-color:#333333;opacity:0.6;filter:a lpha(opacity=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;","buttoncssress "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))