6.8. Вбудовані функції в Excel

Табличний процесор Excel має широкий набір вбудованих функцій, які підрозділяються на категорії: математичні, статистичні, логічні, фінансові, текстові і т.д. Функції мають відповідний синтаксис правопису, тому рекоме­ндує­мо при застосуванні функцій використовувати майстра функцій. Аргументами функцій, як і в математиці, можуть бути константи (числові, текстові), змінні або інші функції.

Константа - це конкретне число, текст або дата.

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

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

6.8.1. Вбудовані функції категорії «Ссылки и массивы»

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

Синтаксис функції: АДРЕС(номер рядка;номер стовпця;тип посилання;формат посилання; ім’я листа).

Де номер рядка; номер стовпця комірки вказуються цифрами, тип посилання може бути 1, тоді адреса комірки буде вказана абсолютною, наприклад $E$4, якщо 2-E$4, 3- $E4, 4- E4. Формат посилання вказує, який стиль матиме адреса комірки: 0 (адреса комірки буде складатися з номера рядка та стовпця); 1 (адреса комірки буде складатися з ім'я стовпця та номера рядка). Ім’я листа – ім’я листа де знаходиться комірка.

Наприклад нам необхідно знайти адресу комірки яка знаходиться на перетині 4-го рядка та 5–го стовпця на листі з ім’ям «Приклад1» тоді у поточній комірці робочого листа треба записати формулу: =АДРЕС(4;5;1;1;”Приклад1”) та натиснути клавішу Enter і ми отримаємо: Приклад1!$E$4

Функція ДВССЫЛ(). Ця функція повертає значення з комірки адреса якої вказується в аргументі. Адреса комірки повинна задаватися у вигляді текстового рядка.

Наприклад, в комірці А2 міститься формула вигляду:

=ДВССЫЛ(АДРЕС(4;5;1;1);1).

Одиниця в другому аргументі функції ДВССЫЛ вказує стиль, який використовується у функції АДРЕС. Ця функція в даному прикладі повертає значення з комірки E4. Якщо ім'я листа не вказане використовується поточний лист.

Функція ЧСТРОК(). Дана функція визначає число рядків в заданому діапазоні комірок. Наприклад, функція =ЧСТРОК (C2:E5) дає значення 4.

Функція ЧИСЛСТОЛБ() визначає число стовпців в заданому діапазоні. Наприклад, функція =ЧИСЛСТОЛБ(С2:E5) дає значення 3.

Функція СТОЛБЕЦ() визначає початковий номер стовпця заданого діапазону або іменованого блоку комірок.

Наприклад, функція =СТОЛБЕЦ(С2:E5) дає значення 3.

Функція СТРОКА() визначає початковий номер рядка посилання, діапазону комірок або іменованого блоку комірок.

Наприклад, функція =СТРОКА(С2:Е5) дає значення 2.

Функція ВЫБОР() дозволяє вибрати по заданому індексу діапазон комірок, блок або значення з вказаного списку констант.

Синтаксис: ВЫБОР(номер індексу ;значення1;значення2;.)

Наприклад, функція =ВЫБОР(2;A2;A3;A4;A5) вибирає значення з комірки A3.

Функція ИНДЕКС() використовується для вибору значень з посилання або масиву. Функція ИНДЕКС() має дві синтаксичні форми: масив і посилання. Посилальна форма завжди повертає посилання; форма масиву завжди повертає значення або масив значень.

Синтаксис 1: ИНДЕКС(масив;номер рядка;номер стовпця) повертає значення вказаної комірки або масив значень в аргументі «масив».

Приклад 1: =ИНДЕКС(A2:B3;2;2) - повертає значення на перетину другого рядка і другого стовпця з діапазону A2:B3. Якщо задати номер рядка або номер стовпця рівним 0 (нулю), то функція ИНДЕКС() поверне масив значень для цілого стовпця або цілого рядка відповідно.

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

Приклад 2: =ИНДЕКС(A2:C6;2;3) - перетин другого рядка і третього стовпця в діапазоні A2:C6, яким є вміст комірки C3.

Приклад 3: =ИНДЕКС((A1:C6;A8:C11);2;2;2) - перетин другого рядка і другого стовпця в другій області діапазону A8:C11, яким є вміст комірки B9.

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

Наприклад, в комірці В5 введена формула =ПОИСКПОЗ(1000;Е2:Е5;0). Якщо в кінці аргументу 0 то буде пошук першого рівного значення. Якщо 1 то буде пошук найбільшого значення, яке не перевершує шукане, при цьому масив повинен бути впорядкований за збільшенням. Якщо масив впорядкований по убуванню буде пошук найменшого значення, яке перевершує шукане.

Функція ПРОСМОТР дозволяє проглянути дані в масиві шуканих значень і порівняти з вектором перегляду і у разі їх збігу вивести шукане значення з масиву результатів.

Синтаксис функції ПРОСМОТР (шукане значення; вектор перегляду;вектор результату).

Наприклад: Нехай у будь якій комірці введена функція: = ПРОСМОТР(В1;С2:С5;D2:D5) це означає, що значення з комірки В1 буде порівнюватись зі значенням в діапазоні С2:С5, якщо воно рівне то буде виводитись значення комірки з рядка масиву вектора результату.

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

Синтаксис функції: ВПР(шукане значення;масив;номер стовпця;діапазон перегляду). Шукане значення – це значення яке шукається в масиві. Номер стовпця – це стовпець з якого виводиться значення у випадку коли шукане значення співпадає зі значенням у масиві в першому рядку. Значення виводиться в тому ж рядку в якому шукане значення співпадає зі значен­ням в масиві. Якщо шукається точне значення, тоді діапазон перегляду рівний 1, якщо приблизно 0.

Наприклад, функція =ВПР(125;Блок;2;1) повертає значення із стовпця 2, якщо число 125 співпаде з числом в першому рядку блоку. Шукане значення може бути значенням, посиланням або текстовим рядком.

Функція ГПР (горизонтальний перегляд) проглядає верхній рядок таблиці і повертає значення з вказаного рядка.

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

Функція СМЕЩ() повертає посилання, зміщене щодо заданого.

Синтаксис: СМЕЩ(задане посилання;рядок зсуву;стовпець зсуву;висота результуючого діапазону;ширина результуючого діапазону). Приклад: =СМЕЩ(C3;2;3;1;1). Це означає що комірка з якої буде виведене значення зміщена відносно комірки C3 на два рядка вниз і на три стовпця вправо при цьому висота і ширина діапазону буде дорівнювати одиниці.

У другому прикладі: =СУММ(СМЕЩ(C3:E5;-1;0;3;3)) буде виводитись сума з діапазону C2:E4 висотою в три рядки та шириною в три стовпці.

Функція ТРАНСП (транспонування діапазону комірок) для “ розвороту ” на 90° блоку комірок.

Наприклад, потрібно транспонувати початковий блок комірок С2:Е5 у комірки А10:D12. Для цього потрібно виділити комірки А10:D12, потім увести функцію у рядок формул чи першу комірку діапазону А10:D12 та натиснути комбінацію клавіш: Ctrl + Shift + Enter.

У комірки діапазону: А10:D12 вводиться формула масиву {=ТРАНСП(С2:Е5)}.