Приклади роботи функції бази даних БІЗВЛЕЧЬ в Excel
Припустимо ми маємо в своєму розпорядженні базою даних, яка експортована в Excel так як показано нижче на малюнку:
Функція БІЗВЛЕЧЬ приклади в Excel
Всі функції Excel, які призначені для роботи з базами даних володіють однією загальною властивістю. Всі вони вимагають заздалегідь сформатувати діапазон запитів до бази, який необхідно заповнити для пошуку і подальшої роботи. Тому в першу чергу ми повинні попередньо сформатувати всі критерії наших запитів до бази. Для цього:
- Вище бази даних додамо 4 порожніх рядки. Для цього досить виділити 4 заголовка рядків листа Excel і натиснути правою кнопкою мишки. З контекстного меню вибрати вставити. Або після виділення рядків по заголовкам натиснути комбінацію гарячих клавіш CTRL + SHIFT + =.
- Далі скопіюйте всі заголовки стовпців бази даних і вставте їх в перший рядок листа для допоміжної таблиці критеріїв.
Простір для заповнення критеріїв запитів вище даних бази.
Спочатку спробуємо отримати номер фактури на прізвище клієнта:
- В осередку D2 введіть прізвище Антонова.
- В осередку A3 введіть наступну формулу:
Відразу ж отримуємо готовий результат як показано нижче на малюнку:
Формула знайшла відповідний номер фактури для клієнта з прізвищем Антонова.
Розбір принципу дії функції БІЗВЛЕЧЬ для роботи з базами даних в Excel:
У нашому випадки функція БІЗВЕЧЬ повернула одне значення - без помилок. Цю функцію можна так само використовувати для виведення цілого рядка за одну операцію без копіювання функції в інші осередки з іншими аргументами. Щоб уникнути необхідності вказувати новий критерій для кожної її копії складемо просту формулу, в яку додамо функцію стовпець. Для цього:
- В осередку A3 введіть наступну формулу:
- Скопіюйте її в усі осередки діапазону A3: E3.
Обрана цілий рядок інформації по конкретного прізвища певного клієнта.
Принцип дії формули для виведення цілого рядка з бази даних:
У конструкції функції БІЗВЕЧЬ змінили ми тільки другий аргумент, значення якого обчислюється функцією стовпець в місце числа 1. Ця функція повертає номер поточного стовпця для поточної комірки.
Безперечна перевага використання функції БІЗВЛЕЧЬ полягає в автоматизації. Досить лише змінити критерій і в результаті ми отримуємо вже новий рядок інформації з бази даних клієнтів фірми. Наприклад, знайдемо дані тепер по номеру клієнта 58499. Видаляємо старий критерій вводимо новий і відразу ж отримуємо результат.
Обробка баз даних в Excel за кількома критеріями
Припустимо нашу базу поповнив новий прибутковий клієнт з таким же ім'ям «Василь». Нам відомо про нього лише ім'я та прізвище «Василій Великий». На іменини в день Василя нам потрібно вислати тільки 1 подарунок для більш прибуткового клієнта фірми. Ми повинні вибрати кому віддати перевагу: Василю Веселому або Василю Великому. Для цього порівнюємо їх суми транзакцій:
- Розширте діапазон для переглядається таблиці $ A $ 5: $ E $ 18 в параметрах формул, так як у нас додався новий клієнт і на одну запис стало більше: Тепер функція повертає помилку # ЧИСЛО! так як в базі більш ніж 1 запис за даним критерієм.
- В поле критеріїв «Ім'я» вводимо значення «Василь», а потім в поле «Прізвище» вводимо значення «Великий».
В результаті ми бачимо, що подарунок отримає більш активний клієнт Василь Великий.