Пошук даних в таблицях Ексель впр і інші функції
Я виконую подібні процедури кожен день і без описаних нижче функцій мені дійсно довелося б туго. Беріть на замітку і застосовуйте їх в роботі!
Роль цих функцій в житті звичайного користувача складно переоцінити. Тепер можна легко знайти в таблиці даних відповідних записів і повернути відповідне їй значення.
Синтаксис функції ВПР такий: = ВПР (шукане_значення; табліца_для_поіска; номер_виводімого_столбца; [тіп_сопоставленія]). Розглянемо аргументи:
- Шукане значення - значення, яке будемо шукати. Це обов'язковий аргумент;
- Таблиця для пошуку - той масив осередків, в якому буде пошук. Стовпець з шуканими значеннями повинен бути першим у цьому масиві. Це теж обов'язковий аргумент;
- Номер виводиться шпальти - порядковий номер стовпчика (починаючи з першого в масиві), з якого функція виведе дані при збігу шуканих значень. Обов'язковий аргумент;
- Тип зіставлення - виберіть «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
Для коректної роботи функції потрібно впорядкувати перший стовпець таблиці по зростанню. Інакше, вона може дати помилковий результат.
Функція ГПР має такий же синтаксис, як і ВПР, але шукає результат не в шпальтах, а в рядках. Тобто, переглядає таблиці не зверху вниз, а зліва направо і виводить заданий номер рядка, а не стовпчика.
Ще один спосіб пошуку даних - комбінування функцій ПОИСКПОЗ і ІНДЕКС.
Перша з них, служить для пошуку значення в масиві і отримання його порядкового номера: ПОИСКПОЗ (шукане_значення; Просматріваемий_массів; [Тип зіставлення]). Аргументи функції:
- Шукане значення - обов'язковий аргумент
- Проглядається масив - один рядок або стовпець, в якому шукаємо збіг. обов'язковий аргумент
- Тип зіставлення - вкажіть «0» для пошуку точного збігу, «1» - найближче менше, «-1» - найближче більше. Оскільки функція проводить пошук з початку списку в кінець, при пошуку найближчого меншого - відсортуйте стовпець пошуку по спадаючій. А при пошуку більшого - сортуйте його по зростанню.
Позиція необхідного значення знайдена, тепер можна вивести його на екран за допомогою функції ІНДЕКС (Масив; номер_рядка; [номер_стовпчика]):
- Масив - аргумент вказує з якого масиву осередків потрібно вибрати значення
- Номер рядка - вказуєте порядковий номер рядка (починаючи з першого осередку масиву), яку потрібно вивести. Тут можна записати значення вручну, або використовувати результат обчислення іншої функції. Наприклад, ПОИСКПОЗ.
- Номер стовпця - необов'язковий аргумент, вказується, якщо масив складається з декількох стовпців. Якщо аргумент упущений, формула використовує перший стовпець таблиці.
Тепер скомбініруем ці функції, щоб отримати результат:
Функції ПОИСКПОЗ і ІНДЕКС в Ексель
Ось такі способи пошуку і виведення на екран даних існують в Excel. Далі, Ви можете використовувати їх в розрахунках, використовувати в презентації, виконувати з ними операції, вказувати в якості аргументів інших функцій і ін.