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

В цій статті

Пошук значень у списку по вертикалі за точним збігом

Для виконання цього завдання можна використовувати функцію ВПР або поєднання функцій індекс та ПОШУКПОЗ.

Приклади використання функції ВПР

функція ВПР.

Приклади ІНДЕКСІВ І Збігів

Що означає:

=ІНДЕКС(потрібно повернути значення з C2:C10, яке буде відповідати ПОШУКПОЗ(перше значення "Капуста" в масиві B2:B10))

Формула шукає перше значення у комірці C2: C10, відповідне капусти(B7), і повертає значення в C7 ( 100 ) - перше значення, що відповідає капусти.

Щоб отримати додаткові відомості, див. Функція індекс та функція ПОШУКПОЗ.

Пошук значень у списку по вертикалі з приблизним збігом

Для цього скористайтеся функцією ВПР.

Важливо:

У наведеному вище прикладі функція ВПР шукає ім'я учня, який має 6 тардієс в діапазоні A2: B7. У таблиці немає запису для 6 тардієс, тому функція ВПР шукає наступну найвищу відповідність нижче 6 і знаходить значення 5, пов'язане з першим ім'ям Дейв, і, отже, повертає Дейв.

Для отримання додаткових відомостей див. функцію ВВР.

Пошук значень по вертикалі у списку невідомого розміру з точною відповідністю

Для виконання цього завдання використовуйте функції ЗМІЩ та ПОШУКПОЗ.

Примітка:Цей підхід використовується, якщо дані знаходяться в діапазоні зовнішніх даних, які ви оновлюєте щодня. Ви знаєте, що в стовпці B є Ціна, але ви не знаєте скільки рядків даних повертає сервер, а перший стовпець не відсортований за абеткою.

C1- це верхній лівий осередок діапазону (також званий початковим осередком).

Match ("апельсини"; C2: C7; 0)шукає помаранчевий колір у діапазоні C2: C7. Не слід вмикати початкову комірку в діапазон.

1 - кількість стовпців праворуч від початкового осередку, для яких має бути повернене значення, що повертається. У нашому прикладі значення, що повертається знаходиться в стовпці D, Sales.

Пошук значень у списку по горизонталі за точним збігом

Для цього завдання використовується функція ГПР. Нижче наведено приклад.

Функція ДПР виконує пошук по стовпцю Salesі повертає значення рядка 5 у вказаному діапазоні.

Додаткові відомості можна знайти у розділі функції ГПР.

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

Для цього завдання використовується функція ГПР.

Важливо:Переконайтеся, що значення у першому рядку відсортовані у порядку зростання.

У наведеному вище прикладі функція ДПР шукає значення 11000 у рядку 3 у вказаному діапазоні. Він не знаходить 11000 і, отже, шукає таке найбільше значення, яке менше 1100 і повертає число 10543.

Додаткові відомості можна знайти у розділі функції ГПР.

Створення формули підстановки за допомогою майстра підстановок (тільки Excel 2007)

Примітка:Надбудова "Майстер підстановок" припинена в Excel 2010. Ці функціональні можливості замінені майстром функцій та доступними функціями пошуку та роботи з посиланнями (посиланнями) .

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

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

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

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

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

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

• Шукане значення– значення, яке шукатимемо. Це обов'язковий аргумент;
• Таблиця для пошуку- Той масив осередків, в якому буде пошук. Стовпець з значеннями повинен бути першим у цьому масиві. Це також обов'язковий аргумент;
• Номер стовпця, що виводиться- Порядковий номер стовпця (починаючи з першого в масиві), з якого функція виведе дані при збігу значень, що шукаються. Обов'язковий аргумент;
• Тип зіставлення– виберіть «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. Далі, Ви можете використовувати їх у розрахунках, використовувати у презентації, виконувати з ними операції, вказувати як аргументи інших функцій та ін.

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

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

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

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

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

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

Функція ЗНАЙТИ враховуєі не підтримує використання символів підстановки(Про них читайте далі).

Функція ЗНАЙТИ в Ексель

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

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

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

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

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

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

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

Підстановочні символи в Excel

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

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

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

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

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

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

Пошук значень у таблиці 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 рядків.

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

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

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

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

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

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

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

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

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

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