Використання функцій

Функції – це спеціальні розроблені формули, які дозволяють швидко і легко виконувати складні обчислення Вони дозволяють робити математичні, логічні, статистичні розрахунки, а також цілий ряд інших і подібних вбудованих функцій в Microsoft Excel кілька сотень Деякі функції, такі як синус (SIN) і факторіал (ФАКТ), є еквівалентом досить довгих математичних виразів і можуть створюватися вручну Інші ж, такі як ЕСЛИ (IF) або ВПР (VLOOKUP), у вигляді формул реалізувати неможливо Якщо жодна з вбудованих функцій не підходить для вирішення якої-небудь специфічного завдання, завжди існує можливість створити користувача функцію, а про те, як це зробити, розповідається в главі 32, «Користувальницькі функції» Нижче ми дамо вступну інформацію про можливості функцій і способи їх застосування, а в наступних главах вже не зупинятимемося на цих питаннях

Довідкова інформація про функції

При підготовці цієї книги її автори опинилися перед важким вибором Повний опис кожної з декількох сотень вбудованих функцій Excel вимагало б окремої книги, а можливо, й двох Тому функції, що представляють, на нашу думку, найбільший інтерес, розглядаються тут досить детально, про інші лише коротко згадуються Основна увага приділена фінансовим та інформаційним функціям, а також функціям перегляду, інженерним і тригонометричним функціям, тобто тим, якими в основному і користується більшість читачів У тих випадках, коли потрібна додаткова або більше повна інформація про синтаксис і аргументах функцій, детально не розглядаються в даній книзі, слід звернутися до одного з наступних вбудованих ресурсів програми:

Довідкова система Microsoft ExcelТут ви знайдете будь-яку інформацію про цікавить вас функції Для цього натисніть клавішу F1, а потім введіть у полі Шукати (Search) панелі завдань ключове слово для пошуку всіх задовольняють запиту розділів довідкової системи У розділі Довідка за функціями (Function Reference) всі функції розподілені за категоріями, що дозволяє швидко знайти необхідні відомості (рис 131)

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

Діалогове вікно Аргументи функції (Function Arguments) Якщо ви хочете отримати конкретну інформацію про аргументи функції, клацнітьпіслявведення в рядок формулкорректного імені функції на кнопці Вставка функції Зверніть увагу, що в нижній частині вікна Аргументи функції (рис 132) є гіперпосилання на відповідну сторінку довідки Microsoft Excel

Рис 131 Довідкова система Microsoft Excel включає в себе вичерпний посібник з вбудованим функціям

Рис 132 Для отримання інформації про аргументи використовуйте кнопку Вставка функції

Спливаючі підказкиЦі підказки у вигляді прямокутників жовтого кольору особливо корисні, якщо при введенні функції ви не впевнені точно в її синтаксисі Ви можете отримати допомогу прямо в процесі введення функції в комірку, як показано на рис 133, якщо вкажете коректне імя функції

Якщо у віконці підказки клацнути на імені функції, Excel виведе на екран відповідну сторінку довідкової системи з докладним описом цієї функції Якщо клацнути на одному з аргументів, відповідна частина формули, що відноситься до цього аргументу, буде виділена Щоб відключити спливаючі підказки, перейдіть на вкладку Загальні (General) діалогового вікна Параметри і в розділі Параметри (Settings) скиньте прапорець Підказки для функцій (Function ScreenTips)

Кнопка Вставка функції

Рис 133 Щоб отримати інформацію про аргументи функції, клацніть на назві аргументу в поле підказки

ДИВИСЬ ТАКОЖ

Про довідковій системі Excel ми розповідали в розділі «Допомога в інтерактивному режимі» глави 2

Установка пакета аналізу

Більшість широко востребуемих функцій вбудовані безпосередньо в програму, але деякі функції, призначені в основному для аналізу даних, включені в інструментальну надбудову Пакет аналізу (Analysis ToolPak) Тут зібрані кошти (макроси) першої необхідності, призначені для статистичної обробки даних, а також функції, доступні за допомогою Майстра функцій

Рис 134 Зліва – діалогове вікно Надбудови, праворуч – вікно Аналіз даних

Щоб переконатися, встановлений чи ні пакет аналізу, відкрийте меню Сервіс Якщо команда Аналіз даних (Data Analysis) присутня, то пакет також приєднаний (можливо, вам доведеться розкрити цей меню до повного розміру) Діалогове вікно Аналіз даних (Data Analysis) показано на рис 134, справа Якщо дана надбудова не встановлена, виберіть у меню Сервіс команду Надбудови (Add-Ins), поставте відповідний прапорець в однойменному діалоговому вікні і клацніть на кнопці ОК Але за відсутності її у списку надбудов вам знову доведеться запустити програму установки Office

ДИВИСЬ ТАКОЖ

Детальніше пакет аналізу розглядається в розділі 17, «Статистичний аналіз» Питання установки Microsoft Excel висвітлені в додатку А

Синтаксис функцій

Функції складаються з двох логічних частин: імені функції і одного або декількох аргументів Імя функції – Як, наприклад, СУММ (SUM) або СРЗНАЧ (AVERAGE) – описує операцію, нею виконувану Аргументи задають значення або клітинки, використовувані функцією Наприклад, функція ROUND (ROUND) має наступний синтаксис:

= ROUND (число колічество_ціфр) Тут аргументчисломоже бути числом або посиланням на клітинку, в якій міститься значення, а другий аргумент – колічество_ціфр

– визначає, до якої цифри округляється задане значення Так, у виразі

ROUND (М30, 2) перший аргумент є посиланням на клітинку

Аргументи функції завжди полягають в круглі дужки Відкриває дужка вказується відразу після імені функції, без пробілу Якщо ж буде введений пробіл або будь-який інший символ, в комірці зявиться значення # ІМЯ (# NAME), Яке говорить про помилку вводу

ЗАУВАЖЕННЯ

Деякі функції, такі як ПІ (PI), ІСТИНА (TRUE) і ТДАТУ (NOW), не мають аргументів – вони використовуються в складі формул або інших функцій Проте після кожного такого імені повинні стояти порожні круглі дужки, наприклад = ТДАТУ ()

Коли функція передбачає використання декількох аргументів, кожен з них відокремлюється від іншого символом крапки з запятой1 Так, формула = ПР0ІЗВЕД (С1 С2 С5) перемножує значення, що містяться в осередках С1, С2 і С5 Окремі функції, такі як ПРОИЗВЕД (PRODUCT) або СУММ (SUM), не вимагають вказівки точної кількості аргументів Ви можете задати для них до 30 аргументів, при цьому загальна довжина всієї

формули не повинна перевищувати 1024 символу Як аргумент допускається використовувати як посилання на окрему клітинку, так і посилання на діапазон робочого аркуша Наприклад, формула = CУMM (Al: A5 C2: C10 D3: D17) має всього три аргументи, але при цьому вона діє для 29 осередків (Зокрема,

1 Для англомовної версії це кома – Прямуючи ред

перший аргумент, А1: А5,  є посиланням на діапазон з пяти осередків від Al no A5) Аргументом функції вправі бути й інша формула, що посилається на інші клітинки або діапазони

Вирази як аргументи

Як аргументи функції можна використовувати вираження(Expressions), які

Excel перетворює в значення і розглядає як аргументи Так, у формулі

= CУMM (SIN (Al * ПІ ()) 2 * C0S (A2 * ПІ ())) крапка з комою відокремлює два обчислюваних аргументу функції СУММ

Типи аргументів

У наведених вище прикладах аргументи в більшості випадків були посиланнями на клітинки або діапазони Але це не обовязкова умова, аргументами бувають числові, текстові та логічні значення, імена діапазонів, масиви і спеціальні значення помилок Розглянемо ці типи аргументів по порядку:

Числові значення Аргументом функції вправі бути будь-яке число Наприклад, функція СУММ у формулі = СУММ (327 209 176) складає числа 327, 209 і 176 Однак частіше числові величини вводяться не в саму формулу, а в осередку листа, на які потім і посилаються аргументи функцій

Текстові значення У формулі = ТЕКСТ (ТДАТУ () Д МММ РРРР) (TEXT (N0W (), MMMM D YYYY)) другий аргументом функції ТЕКСТ є текстове значення, яке задає шаблон виводу дат при перетворенні повертається функцією ТДАТУ значення в рядок символів Текстовий аргумент – це або рядок символів, укладена в лапки, або посилання на осередок, що містить текст Детальніше див розділ «Текстові функції »глави 14

Логічні значення Аргументи низки функцій приймають тільки логічні значення ІСТИНА (TRUE) і БРЕХНЯ (FALSE) Логічний вираз повертає на робочий лист або в клітинку, в якій ця формула знаходиться, одне з цих двох значень Наприклад, формула

= ЕСЛИ (А1 = ІСТИНА Нова, Стара) & ціна – Це логічне вираження Якщо значення в комірці А1 одно ІСТИНА, функція ЯКЩО повертає рядок Нова, а вся формула в цілому поверне значення Нова ціна Детальніше див розділ «Логічні функції» глави 14

Іменовані посилання Аргументом функції може бути імя діапазону Так, якщо в підміню Імя (Name) меню Вставка ви вибрали команду Присвоїти (Define) і призначили діапазону СЗ: Сб імя Отримано, то для обчислення суми значень в осередках цього діапазону підходить формула = СУММ (Отримано) Детальніше про імена діапазонів див розділ «Присвоєння імен осередкам і діапазонам» глави 12

Масиви Масиви також вправі виступати аргументами деяких функцій, а такі функції, як ТЕНДЕНЦІЯ (TREND) і ТРАНСП (TRANSPOSE), як аргумент приймають і зовсім тільки масиви Більшість функцій не так вимогливі вони цілком успішно працюють в числі іншого і з масивами Останні можуть складатися з числових, текстових або логічних значень Детальніше див розділ «Масиви» глави 12

► Аргументи змішаних типівВ одній функції припустимо застосовувати аргументи різних типів Наприклад, у формулі = СРЗНАЧ (Группа1 АЗ 5 * 3) аргументами є: у першому випадку імя діапазону (Группа1), у другому – посилання на клітинку (A3), а в третьому – числове вираження (5 * 3)

Додавання функцій на робочий лист

Найпростіший спосіб додати в комірку робочого аркуша вбудовану функцію Excel – просто клацнути на кнопці Вставка функції (Insert Function) При цьому на екрані зявляється діалогове вікно Майстра функцій, показане на рис 135

Кнопка Вставка функції

Рис 135 Для додавання функції на робочий лист використовуйте вікно Майстра функцій

Якщо ви не цілком упевнені в тому, яка саме функція вам потрібна, введіть опис операції, яку ви збираєтеся виконувати, у текстове полі Пошук функції (Search) Наприклад, так: «Я хочу знати, скільки осередків містять значення » Потім натисніть кнопку Знайти (Go), та майстер поверне список рекомендованих функцій, аналогічний тому, що наведено у прикладі на рис 136 Якщо серед них немає підходящої для вирішення вашого завдання, спробуйте описати потрібну дію іншими словами і повторити запит

Ви можете здійснювати пошук функцій за категоріями Для цього виберіть в списку Категорія (Category) один з розділів: Фінансові (Financial), Дати і час (Date & Time), Текстові (Text) і т д У категорії Рекомендовані (Recommended) зібрані всі функції, знайдені за запитом, тобто з використанням поля Пошук функції

Рис 136 Щоб знайти потрібну функцію, використовуйте текстове полі Пошук функції

Коли ви клацнете на потрібної функції в списку і на кнопці OK, Excel виведе в активній клітинці знак рівності, імя функції і пару круглих дужок Потім на екрані зявиться діалогове вікно Аргументи функції з синтаксисом функції та її коротким описом (див рис 132) Щоб отримати більш повне і докладний опис, клацніть на гіперпосиланні Довідка по цій функції (Help On This Function), розташованої в нижній частині вікна

РАДА

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

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

Деякі функції, наприклад ІНДЕКС (INDEX), мають кілька форм (варіантів завдання аргументів) Для подібної функції відкриється додаткове діалогове вікно майстра, що дозволяє вибрати потрібний варіант, як показано на рис 137

РАДА

Якщо ви не памятаєте всі аргументи, але працюєте в рядку формул, то після введення знаку рівності та імені функції натисніть клавіші Ctrl + A Ви побачите одну з двох розглянутих вище діалогових вікон, в залежності від кількості форм завдання аргументів

Рис 137 Якщо функція має кілька синтаксичних форм, на екрані зявляється додаткове діалогове вікно

Через що виникає помилка # ІМЯ

Помилка # ІМЯ (# NAME) Обумовлюється кількома причинами, але найчастіше це неправильно введене імя функції Однак тут дуже легко проконтролювати себе: використовуйте тільки малі літери Якщо після натискання клавіші Enter букви в імені функції зміняться на прописні, значить, воно було набрано правильно Інакше, швидше за все, має місце помилка У цьому випадку краще звернутися до довідкової системі Excel або до Майстра функцій

Вставка посилань і імен

Як і в ситуації з будь-якої іншої формулою, ви можете легко вставити у функцію посилання на комірки та імя за допомогою миші Наприклад, щоб ввести в комірку С11 функцію, усереднювати значення в діапазоні С2: С10, наберіть в цьому осередку текст = СРЗНАЧ (, а потім просто виділіть мишею діапазон С2: С10 Навколо вибраних осередків зявиться рамка, що біжить, а в рядку формул – посилання на цей діапазон Поставте закриває дужку та натисніть клавішу Enter Іменовані діапазони, константи або формули також вставляються у формули за допомогою Excel Для цього в меню Вставка виберіть команду Імя, а потім – Вставити, і виділіть в списку діалогового вікна Вставка імені (Paste Name) потрібне імя Після клацання на кнопці ОК це імя зявиться у формулі в точці вставки

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*