Приклад використання Мастера функций

Припустимо, що користувачеві необхідно обчислити середнє значення діапазону клітинок з допомогою функції СРЗНАЧ (AVERAGE). Для цього виконайте такі дії:

Введіть числа (можна вводити будь-які значення) в діапазон клітинок А1: А6.

Встановіть табличний курсор у клітинку А7 (ця клітинка буде вміщати функцію).

Клацніть мишкою на кнопці виклику Мастера функций (Function Wizard), розміщеній на стандартній панелі інструментів. На екрані з'явиться перше з двох діалогових вікон (рис. 2.39). Користувач побачить, що в рядку функцій з'явиться якась формула.

Оскільки функція СРЗНАЧ (AVERAGE) належить до категорії Статистические (Statistical), виберіть цю категорію зі списку Категория (Function Category). У вікні Функция (FunctionName) буде подано перелік статистичних функцій.

З цього переліку виберіть функцію СРЗНАЧ (AVERAGE). Внизу діалогового вікна буде подано перелік її аргументів з коротким описом.

Клацніть мишкою на кнопці Далее (Next). Excel відтворить на екрані друге діалогове вікно Мастера функций (Function Wizard) (рис. 2.40).

Рис. 2.40. Друге з двох діалогових вікон Мастера Функций

Активізуйте вікно редагування, позначене як Число1 (Number1).

У робочій таблиці виберіть діапазон клітинок А1: А6. Цей діапазон з'явиться у вікні редагування. В діалоговому вікні у полі Значение (Value) буде подано результат обчислення за цією функцією.

Оскільки користувач збирався обчислювати середнє значення тільки одного діапазону значень, немає потреби вводити будь-які додаткові аргументи. Клацніть мишкою на кнопці Готово (Finish).

Після цього в клітинці А7 з'явиться формула, за якою обчислюється середнє значення чисел, розміщених у клітинках з А1 до А6:

=СРЗНАЧ(А1: А6) або =AVERAGE(А1: А6)

2.4.5.4.2.3. Додаткова інформація про діалогове вікно Мастер функций

Наведемо додаткові рекомендації, про які необхідно пам'ятати, працюючи з діалоговим вікном Мастер функций:

У будь-який момент роботи з діалоговим вікном Мастер функций, якщо виникне потреба в допомозі, її можна отримати, клацнувши мишкою на кнопці Справка (Help).

Якщо користувач вводить нову формулу, Мастер функций автоматично починає її зі знака =.

Якщо під час виклику Мастера функций активна клітинка не була пустою, то її вміст буде видалено.

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

Перебуваючи в другому діалоговому вікні Мастера функций, можна клацнути мишкою на кнопці Назад (Back), щоб повернутися до першого діалогового вікна Мастера функций, де можна вибрати іншу функцію.

Якщо користувач передумав вводити функцію то, перебуваючи в будь-якому вікні Мастера функций, необхідно клацнути мишкою на кнопці Отмена (Cancel).

Кількість полів редагування, які розміщені в другому вікні Мастера функций, визначається числом аргументів, які використовує функція. Якщо функція не використовує аргументи, поля редагування не виводяться на екран. Якщо функція використовує змінну кількість аргументів, наприклад, функція СРЗНАЧ (AVERAGE), Excel додає нові поля редагування щоразу, коли вводиться черговий аргумент.

Поле, розміщене праворуч від області редагування кожного аргумента, показує його поточне значення.

Деякі з функцій мають кілька форм, наприклад, функція ИНДЕКС (INDEX). Якщо користувач вибирає одну з таких функцій, Excel виводить на екран додаткове діалогове вікно, яке дає змогу вибрати потрібну форму функції. Таке діалогове вікно позначається як Шаг1а (Step1a).

Якщо користувач бажає тільки ввести список аргументів конкретної функції, потрібно набрати знак рівності (=) і назву цієї функції, а потім натиснути комбінацію клавіш < Ctrl+A>. В результаті на екрані з'явиться друге діалогове вікно Мастера функций.

Для швидкого пошуку функції в списку Функция (Function Name) активізуйте цю секцію першого діалогового вікна Мастера функций і введіть першу букву назви функції. Потім прокручуйте список до знаходження потрібної функції. Наприклад, якщо вибрана категорія Полный алфавитный перечень (All) і є потреба використати функцію SIN, клацніть мишкою на вікні списку Функция (Function Name) і натисніть на клавіатурі букву < S>. Таким чином буде знайдена перша з функцій, імена яких починаються з цієї букви. Це значно звузить коло пошуку.

Якщо користувач бажає використати функцію як аргумент (тобто як вкладену функцію), необхідно клацнути мишкою на кнопці, розміщеній ліворуч від поля введення аргументів у другому діалоговому вікні Мастера функций. Excel виведе на екран перше діалогове вікно Мастера функций і дасть змогу вибрати другу функцію. В рядку заголовка цього вікна з'явиться слово [ Вложеный ] [(Nested)], що буде нагадувати про те, що користувач створює вкладену функцію. З допомогою Мастера функ-ций користувач може створити до семи рівнів вкладених функцій.

2.4.5.5. Приклади функцій

В цьому пункті будуть наведені приклади формул, в яких використовуються функції. Через досить обмежений обсяг посібника будуть наведені всі категорії, подані в Мастере функций, але не кожна функція окремо. Додаткову інформацію з конкретних функцій можна отримати з допомогою довідкової системи (Help), яка інсталюється при інсталяції Excel.

Примітка. У деяких випадках за назвою функції важко визначити категорію, до якої належить та чи інша функція. Наприклад, функція СЧЁТ (COUNT) належить до категорії Статистические (Statistical), але функція СЧЁТЕСЛИ (COUNTIF) - до категорії Математические (Math& Trig). Функції, які повертають логічні значення, наприклад, ЕОШИБКА (ISERROR) або ЕЧИСЛО (ISNUMBER), віднесені до категорії Проверка свойств и значений (Information), а не до категорії Логические (Logical), що було б логічніше.

2.4.5.5.1. Математичні і тригонометричні функції

В Excel входять 52 функції цієї категорії. В неї включені як звичайні функції типу СУММ (SUM) або ЦЕЛОЕ (INT), так і багато інших, одна з яких може виявитися потрібною користувачеві. Розглянемо деякі з них.

СЧЁТЕСЛИ (COUNTIF)

Ця функція дає змогу підрахувати, скільки разів у діапазоні клітинок трапляється конкретна величина. Вона має два аргументи: посилання на діапазон клітинок, у якому розміщені значення для підрахунку, і критерій, який визначає, що конкретно потрібно підрахувати. На рис. 2.41 відтворена робоча таблиця, в якій підрахована кількість жителів різного віку. Для цього у формулах, які розміщені в клітинках графи Е, використана функція СЧЁТЕСЛИ (COUNTIF). Наприклад, формула в клітинці Е2 має вигляд

=СЧЁТЕСЛИ(В: В; D2) або =COUNTIF(В: В; D2)

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

Рис. 2.41. Використання функції СЧЁТЕСЛИ

ЦЕЛОЕ (INT)

Функція ЦЕЛОЕ (INT) повертає цілу частину числа, при цьому вона відкидає всі цифри, які становлять дробову частину числа. Наприклад, якщо в клітинці А1 розміщене число 412, 345, а в клітинку В1 записати формулу

=ЦЕЛОЕ(А1) або =INT(А1)

то в клітинку В1 ця формула поверне значення 412.

ОКРУГЛ (ROUND)

Функція ОКРУГЛ (ROUND) округлює число до вказаної кількості десяткових розрядів. Ця функція звичайно використовується для контролю точності розрахунків. Функція ОКРУГЛ (ROUND) має два аргументи: перший - значення для округлення, другий - цифра, яка вказує на точність округлення. Якщо другий аргумент від'ємний, то округлення зміщується ліворуч від коми на задану кількість цифр. У табл. 2.4 подані деякі приклади використання цієї функції.

Таблиця 2.4

Приклади використання функції ОКРУГЛ

Зауваження. Бажано не плутати округлене значення з відформатованим. Якщо у формулі є посилання на клітинку, в якій розміщене число, округлене з допомогою функції ОКРУГЛ (ROUND), то в розрахунках буде брати участь округлене значення. Якщо ж число в цій клітинці було відформатоване так, що воно лише здається округленим, то в розрахунках буде брати участь реальне (не округлене) значення.

КОРЕНЬ (SQRT)

Функція КОРЕНЬ (SQRT) повертає значення квадратного кореня з аргументу. Якщо аргумент від'ємний, функція сприймає його як помилку. В результаті виконання наступного прикладу буде повернуто число 23:

=КОРЕНЬ(1024) або =SQRT(1024)

Примітка. Для розрахунку кубічного кореня використовується піднесення до степеня (1/3). Наприклад, кубічний корінь з 32768 дорівнюватиме 32 (аналогічно можна обчислювати корені будь-яких степенів).

=32768^(1/3)

СУММ (SUM)

Якщо взяти навмання кілька робочих книг, то можна переконатися, що найчастіше в них використовується функція СУММ (SUM). Ця функція - одна з найпростіших. Вона може мати від 1 до 30 аргументів. Для підрахування суми значень клітинок трьох діапазонів (А1: А10 С1: С10 Е1: Е10) можна використати цю функцію з трьома аргументами:

=СУММ(А1: А10; С1: С10; Е1: Е10) або =SUM(А1: А10; С1: С10; Е1: Е10)

Аргументи не обов'язково мають задаватися в такому вигляді. Наприклад, можна використовувати адреси окремих клітинок, посилання на діапазони тощо:

=СУММ(А1; С1: С10; 125) або =SUM(А1; С1: С10; 125)

СУММЕСЛИ (SUMIF)

Функція СУММЕСЛИ (SUMIF) схожа з функцією СЧЁТЕСЛИ (COUNTIF), яка була описана раніше, але СУММЕСЛИ (SUMIF) підсумовує значення, які розміщені в клітинках, а не підраховує їх кількість. Функція СУММЕСЛИ (SUMIF) має три аргументи. Перший аргумент - діапазон клітинок, вміст яких має бути відібраним за якимось критерієм, другий - критерій відбору, третій - діапазон клітинок, значення яких потрібно підсумувати, якщо виконується цей критерій. На рис. 2.42 відтворена робоча таблиця, в який підрахована сума продажів за різними регіонами.

Рис. 2.42. Використання функції СУММЕСЛИ

Примітка. Зверніть увагу на те, що формули завжди починаються зі знака " =" (дорівнює). За цією ознакою Excel відрізняє формули від рядків тексту.

2.4.5.5.2. Текстові функції

В основному Excel призначений для роботи з числами, але він має ще 23 вбудовані функції для роботи з текстом. Розглянемо деякі з них.

СИМВОЛ (CHAR)

Функція СИМВОЛ (CHAR) повертає окремий символ, який відповідає заданому як аргумент ANSI-коду. Значення аргумента має міститися в інтервалі від 1 до 255. (Функція КОДСИМВ (CODE) виконує обернену дію.)

Ця функція може знадобитися для введення символів, які важко або неможливо ввести з клавіатури. Наприклад, наступна формула повертає символ авторського права " O ":

=СИМВОЛ(169) або =CHAR(169)

ЛЕВСИМВ (LEFT)

Функція ЛЕВСИМВ (LEFT) віділяє рядок символів заданої довжини з іншого рядка, починаючи з крайньої лівої позиції. Ця функція використовує два аргументи: перший аргумент - рядок, а другий (необов'язковий) - число символів. Якщо другий аргумент відсутній, Excel за замовчуванням виділяє перший символ тексту.

=ЛЕВСИМВ(" Т.Г.Шевченко") або =LEFT(" Т.Г.Шевченко")

У цьому прикладі формула повертає букву " Т".

=ЛЕВСИМВ(" Володимир Мономах"; 9)

або

=LEFT(" Володимир Мономах; 9")

У цьому прикладі формула повертає " Володимир".

ДЛСТР (LEN)

Функція ДЛСТР (LEN) повертає кількість символів у текстовому рядку. Наприклад, наступна формула повертає число 9:

=ДЛСТР(" Концепція") або =LEN(" Концепція")

Якщо необхідно визначити довжину рядка без урахування пропусків, використовують функцію ДЛСТР (LEN), аргументом якої є функція СЖПРОБЕЛЫ (TRIM). Наприклад, якщо бажано знати кількість символів тексту в клітинці А1 (без будь-яких пропусків), то необхідно використати формулу

=ДЛСТР(СЖПРОБЕЛЫ(А1)) або =LEN(TRIM(А1))

ПСТР (MID)

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

=ПСТР(" Правове забезпечення системи"; 9; 12)

або

=MID(" Правове забезпечення системи"; 9; 12)

Ця формула поверне " забезпечення".

ЗАМЕНИТЬ (REPLACE)

Функція ЗАМЕНИТЬ (REPLACE) дає змогу замінювати символи в тексті. Вона має чотири аргументи: перший - рядок тексту, в якому потрібно зробити заміну; другий - позиція в текстовому рядку, з якої необхідно починати заміну; третій - кількість символів, які необхідно замінити; четвертий - новий текст, який має замінити той, що був у рядку. В наступному прикладі формула повертає " Тарас Шевчено".

=ЗАМЕНИТЬ(" Тарас Петриненко"; 7; 10" Шевченко")

або

=REPLACE(" Тарас Петриненко"; 7; 10" Шевченко")