3.3. Особливості роботи з масивами даних

Програма Microsoft Excel може використовуватись також як примітивна база даних. У файлах Excel зручно тримати деякі види табличних даних і програма надає кілька ефективних інструментів для роботи з ними. Розглянемо деякі із них.

ВЫБОР. Синтаксис функції: =ВЫБОР(індекс; перший елемент; другий елемент;…; двісті п’ятдесят четвертий елемент). В результаті роботи функція повертає з переліку елементів (від першого до двісті п’ятдесят четвертого) той, номер якого збігається з індексом.

Приклад: Після введення в довільну комірку формули =ВЫБОР(3; 12; 11; А1; В9) та натискання клавіші Enter буде виведено вміст комірки А1, оскільки посилання на неї має третій за порядком номер у аргументах функції ВЫБОР.

ВПР. Синтаксис функції: =ВПР(значення, що шукається; діапазон пошуку; номер стовпця, значення з якого повертається; точний чи приблизний збіг). Застосовується при необхідності знайти значення у крайньому лівому стовпчику обраного масиву та повернути значення з комірки, що знаходиться у тому ж рядку, що і знайдена комірка, та стовпчику, вказаному у формулі. Слід звернути увагу, що при використанні останнього параметру ИСТИНА (приблизний збіг), перший стовпчик діапазону, в якому проводиться пошук, повинен бути відсортований за зростанням. При параметрі ЛОЖЬ у сортуванні необхідності немає.

Приклад: При введенні в комірку Е2 порядкового номеру студента, у комірці Е3 повинне з’являтися відповідне номеру прізвище. Таблиця з порядковими номерами та прізвищами наведена на рис. 46.

Рис. 46.

Вводимо у комірку Е3 формулу: =ВПР(E2; A2:B10; 2; ЛОЖЬ) та натискаємо клавішу Enter. У комірці з’явиться попередження про помилку #Н/Д. Тобто збігів не знайдено. У формулі перший параметр Е2 – посилання на комірку, у ній немає жодного значення, через це і виникає помилка. Другий параметр А2:В10 – діапазон даних, 2 – значення, яке потрібно вивести, знаходиться у другому стовпчику діапазону. Якщо ввести в комірку Е2 будь-яке число від 1 до 9, у комірці Е3 з’явиться відповідне прізвище рис. 47.

Рис. 47.

ГПР. Синтаксис функції: =ГПР(значення, що шукається; діапазон пошуку; номер рядка, значення з якого повертається; точний чи приблизний збіг). Використання аналогічне до функції ВПР з відмінністю у тому, що пошук виконується по верхньому рядку.

ИНДЕКС. Синтаксис функції: =ИНДЕКС( посилання; номер рядка; номер рядка; номер стовпця; номер області). Посилання – діапазон, з якого виводяться дані; номер рядка, в якому знаходиться комірка; номер стовпця, в якому знаходиться комірка; номер області, з якої необхідно вивести дані.

Приклад: Дано набір даних (рис. 48). Необхідно вивести довжину l у комірку С1 при зміні значення коду у комірці В1.

Рис. 48

Введемо у комірку С1 формулу: =ИНДЕКС( В3:В11; В2). Таким чином буде переглядатися діапазон В3:В11 при зміні коду, який вводиться у комірку В1. Результат вказано на рис. 49. Як бачимо, видається результат, що відповідає значенню коду 4.

Рис. 49.

Цю задачу можна вирішити також за допомогою функції ВПР.

ПОИСКПОЗ. Синтаксис функції: =ПОИСКПОЗ( значення, що шукається; масив; тип співставлення). Функція повертає відносне положення значення, що шукається у масиві, введеного у формулу.

Приклад: Необхідно знайти, під яким номером знаходиться студент Вовчок у масиві, рис. 46.

Введемо у довільну комірку формулу: =ПОИСКПОЗ(«Вовчок»; В2:В10; 0 ) та натиснемо Enter. У комірці буде виведено число 7. Як бачимо, Вовчок і справді знаходиться на сьомому місці у масиві.

СМЕЩ. Синтаксис функції: =СМЕЩ( посилання; зміщення за рядками; зміщення за стовпцями; висота; ширина). Функція повертає масив заданої висоти та ширини, зміщений від початку вихідного масиву на задану кількість рядків та стовпчиків.

Приклад: З масиву, показаного на рис. 50 показати, починаючи з 1 рядка стовпчика N, масив, розміром 5 рядків і 6 стовпчиків, що починається на 3 рядки нижче і на 2 рядки правіше початку початкового масиву.

Рис. 50.

Виділимо, починаючи з комірки N1 область в шість стовпців шириною та 5 рядків висотою та введемо формулу: =СМЕЩ(A1:L21;3;2;5;6). Натиснемо сполучення клавіш Ctrl+Shift+Enter. Як видно з рис. 50, новий масив повинен починатися з числа 1,6 (рис. 51).

Рис. 51.