В MS Excel міститься велика кількість стандартних формул, які називаються функціями. Вбудовані функції Excel діляться на наступні категорії: математичні, логічні, тригонометричні, статистичні, фінансові, інформаційні, текстові, функції дати і часу, інженерні, функції для роботи з базою даних, функції перегляду і посилань.
вбудовані функціїExcel - це спеціальні, заздалегідь створені формули, які дозволяють легко і швидко виконувати складні обчислення. Вони подібні до спеціальним клавішам на деяких калькуляторах, призначеним для обчислення квадратних коренів, логарифмів і статистичних характеристик.
Деякі функції, такі як СУММ (SUM), SIN (SIN) і фактром (FACT), є еквівалентами довгих математичних формул, які можна створити самим. Інші функції, такі як ЯКЩО (IF) і ВПР (VLOOKUP), у вигляді формул реалізувати неможливо.
У тих випадках, коли потрібна інформація про функції, слід звертатися до довідкової системи Excel, де може бути знайдено повний опис кожної вбудованої функції.
Швидко отримати інформацію про функції можна також за допомогою кнопки вставка функції.
Функції складаються з двох частин: імені функції і одного або декількох аргументів. Ім'я функції, наприклад, СУММ (SUM) або СРЗНАЧ (AVERAGE) описує операцію, яку ця функція виконує. Аргументи функції Excel задають значення або осередки, які використовуються функцією. Наприклад, в такій формулі СУМ - це ім'я функції, а С3: С5 - її єдиний аргумент. Ця формула підсумовує числа в осередках С3, С4 і С5:
СУММ (С3: С5).
Аргумент функції укладено в круглі дужки. Відкриває дужка відзначає початок аргументу і ставиться відразу після імені функції. У разі введення пробілу або іншого символу між ім'ям і відкриває дужкою в осередку буде відображено помилкове значення # ИМЯ? (#NAME?).
Деякі функції, такі як ПІ (PI) і ІСТИНА (TRUE), не мають аргументів. Навіть якщо функція не має аргументів, вона все одно повинна містити круглі дужки:
При використанні в функції декількох аргументів вони відокремлюються один від одного крапкою з комою. Наприклад, наступна формула вказує Excel, що необхідно перемножити числа в осередках С1, С2 і С5:
Зробимо (С1; С2; С5).
У функції можна використовувати до 30 аргументів, якщо при цьому загальна довжина формули не перевищує 1024 символів. Однак будь-який аргумент може бути діапазоном, що містить довільне число осередків листа. Наприклад, наступна функція має три аргументи, але підсумовує числа в 29 осередках (перший аргумент, А1: А5, посилається на діапазон п'яти осередків від А1 до А5 і т.д.):
CУMM (A1: A5; C2: C10; D3: D17).
Комбінацію функцій можна використовувати для створення вираження, яке Excel зводить до єдиного значенням і інтерпретує його як аргумент. Наприклад, в такій формулі: SIN (A1 * ПІ ()) і 2 * COS (A2 * ПІ ()) - це вираження, які обчислюються і використовуються як аргументи функції СУММ:
CУMM (SIN (A1 * ПІ ()); 2 * COS (A2 * ПІ ())).
типи аргументів
аргумент- вираз, що задає значення при зверненні до процедури або функції, від якого залежить результат її виконання.
В якості аргументів використовуються числові, текстові та логічні значення, імена діапазонів, масиви і помилкові значення. Деякі функції повертають значення цих типів і їх надалі можна використовувати в якості аргументів в інших функціях.
Аргументи функції можуть бути числовими. Наприклад, функція СУММ в такій формулі підсумовує числа 327, 209 і 176:
СУМ (327; 209; 176).
Зазвичай числа вводяться в комірки аркуша, які будуть використовуватися, а потім застосовуються посилання на ці осередки в якості аргументів у функціях.
Як аргумент функції можуть використовуватися текстові значення. наприклад:
ТЕКСТ (ТДАТУ (); «Д МММ РРРР»).
У цій формулі другий аргумент функції ТЕКСТ «Д МММ РРРР», є текстовим і задає шаблон для перетворення десяткового значення дати, що повертається функцією ТДАТУ (), в рядок символів. Текстовий аргумент може бути рядком символів, укладеної в подвійні лапки, або посиланням на осередок, яка містить текст.
Аргументи ряду функцій можуть приймати тільки логічні значення ІСТИНА (TRUE) або БРЕХНЯ (FALSE). Логічний вираз повертає значення ІСТИНА або БРЕХНЯ в клітинку або формулу, яка містить цей вислів. Наприклад, перший аргумент функції ЯКЩО (IF) в такій формулі є логічним виразом, яке використовує значення:
ЯКЩО (А1 \u003d ІСТИНА, «Нова», «Стара») & «ціна».
Якщо значення в комірці А1 одно ІСТИНА, то вираз А1 \u003d ІСТИНА повертає значення ІСТИНА, і функція ЯКЩО повертає рядок Нова, а формула в цілому повертає текстове значення Нова ціна.
Як аргумент функції можна вказати ім'я діапазону. Наприклад, якщо вибрати команду присвоїти підміню ім'яменю вставка і призначити діапазону С3: С6 ім'я Отримано, то для обчислення суми чисел в осередках С3, С4, С5 і С6 можна використовувати формулу:
СУМ (Отримано).
Аргументом функції може бути масив. Деякі функції, такі як ТЕНДЕНЦІЯ (TREND) і ТРАНСП (TRANSPOSE) вимагають завдання масиву аргументів. Інші функції не вимагають завдання масиву, але можуть використовувати такі аргументи. Масиви можуть містити числові, текстові або логічні значення.
В одній функції можна використовувати аргументи різних типів. Наприклад, в такій формулі аргументами є ім'я діапазону (Група 1), посилання на осередок (A3) і числовий вираз (5 * 3), а сама формула повертає єдине числове значення:
СРЗНАЧ (Группа1; А3; 5 * 3).
Введення функцій в робочому аркуші
Вводити функції в робочому аркуші можна прямо з клавіатури або за допомогою команди функція меню вставка. При введенні функції з клавіатури краще використовувати малі літери. Коли закінчиться введення функції, необхідно натиснути клавішу Enter або виділити іншу клітинку. Excel змінить букви в імені функції на прописні, якщо воно було введено правильно. Якщо букви не змінюються, це означає, що ім'я функції введено невірно.
Якщо виділити комірку і вибрати в меню вставка команду функція, Excel виведе вікно діалогу Майстер функцій- крок 1 з 2, Показане на рис. 2.2. Відкрити це вікно можна також за допомогою кнопки вставка функції на стандартній панелі інструментів.
У цьому вікні спочатку вибирають категорію (або Повний алфавітний перелік) в списку Категорія і потім в алфавітному списку функція вказують потрібну функцію. В якості альтернативи після вибору категорії можна клацнути на імені будь-якої функції в списку функція і натиснути клавішу, які відповідають першій літері потрібного імені. Щоб ввести функцію, необхідно натиснути кнопку ОК або клавішу Enter.
Excel введе знак рівності, ім'я функції і пару круглих дужок. Потім Excel відкриє друге вікно діалогу майстри функцій (Без рядка заголовка).
Друге вікно діалогу майстри функцій містить по одному полю для кожного аргументу обраної функції. Якщо функція має змінне число аргументів, це вікно діалогу при введенні додаткових аргументів розширюється. Опис аргументу, поле якого містить точку вставки (курсор), виводиться в нижній частині вікна діалогу.
Рис. 2.2. вікно діалогу Майстер функцій- крок 1 з 2
Праворуч від кожного поля аргументу відображається його поточне значення. Це дуже зручно, коли використовуються посилання або імена. Поточне значення функції відображається внизу вікна діалогу.
Після натискання кнопки ОК або клавіші Enter створена функція з'явиться в рядку формул.
Деякі функції, такі як ІНДЕКС (INDEX) мають кілька форм (варіантів завдання аргументів). Якщо вибрати таку функцію в списку функція, Excel відкриє додаткове вікно діалогу майстри функцій, Як на рис. 2.2, в якому можна вибрати потрібну форму функції.
Перелік основних функцій, розташованих за категоріями з прикладами виконання наведено в табл. 2.1.
Таблиця 2.1
Основні функції та їх призначення
| результат | призначення |
||||
| Дата і час (всього 14) |
|||||
| СЬОГОДНІ () | Читає поточну дату з системного годинника ПК |
||||
| ДАТА (2006; 5; 12) | Повертає дату в числовому форматі |
||||
| ЧАС (18; 32; 15) | Повертає час в числовому форматі |
||||
| Математичні (всього 50) |
|||||
| Модуль числа |
|||||
| Синус числа (в радіанах) |
|||||
| РАДІАНИ (170) | Перетворює радіани в градуси |
||||
| ГРАДУСИ (30) | Перетворює градуси в радіани |
||||
| Експонента (е \u003d 2,71828182845904) |
|||||
| натуральний логарифм |
|||||
| Логарифм числа по заданому підставі |
|||||
| Корінь (256) | Квадратний корінь |
||||
| Факторіал |
|||||
| ОКРУГЛ (45,827; 2) | Округлює до заданого числа десяткових розрядів |
||||
| 3,141592 65358979 | Число пі, округлене до 15 розрядів |
||||
| Римське (454) | перетворює число в римський текстовий формат |
||||
| МОПРЕД (А1: СЗ) | Визначник матриці (тут матриця \u003d -) |
||||
| Статистичні (80 функцій) | для статистичного аналізу діапазонів даних |
||||
| Фінансові (53 функції) | Для типових фінансових розрахунків |
||||
| інженерні | Встановлюються додатково через пункт меню «Сервіс-\u003e Надбудови -\u003e Пакет аналізу» |
||||
| Обробка індексів і масивів |
|||||
| Робота з базою даних (всього 12) | Витяг і обробка записів в базах даних |
||||
| Текстові (всього 23) |
|||||
| ДЛСТР ( "Метелик") | довжина тексту |
||||
| ЗАМІНИТИ ( "Лампочка"; 3; 2; "ст") | Ластівка | Заміна символів у тексті |
|||
| ПРАВСИМВ ( "Лампочка"; 5) | Праві символи слова |
||||
| ШУКАТИ ( "ана"; "Банан і ананас") | Шукає текст і повертає знайдену позицію |
||||
| ПІДСТАВИТИ ( "ананас"; "ан"; "з") | Замінює один текст іншим |
||||
| СЦЕПИТЬ ( "Само"; "років") | зв'язує слова |
||||
| Логічні (всього 6) |
|||||
| АБО (ІСТИНА; БРЕХНЯ; ЛОЖЬ) | логічне АБО |
||||
| І (ІСТИНА; ЛОЖЬ) | логічне І |
||||
| НЕ (ІСТИНА) | логічне НЕ |
||||
| Eсли (F1\u003e 5; 10; 5) | 10 (тут F1 \u003d 7); | Перевіряє умова і повертає одне з двох значень |
|||
| ЕНЕТЕКСТ (155) | Якщо не текст, повертає логічне значення ІСТИНА |
||||
| ЕЧІСЛО ( "Текст") | Якщо число, повертає логічне значення ІСТИНА |
||||
Функції можна набирати з клавіатури, але легше створювати за допомогою майстри функцій. Він запускається кнопкою fx на Панелі інструментів. використовуючи Майстер функцій, Можна уникнути помилок в набираемой формулою і отримати підказку за призначенням і формату функції і її аргументів.
приклад. У послідовних осередках Al, A2, A3 записані прикметники в однині. У стовпці В потрібно отримати по два символи закінчення слів з шпальти А, а в стовпці С отримати прикметники у множині, використовуючи текстові функції Excel (рис. 2.3).
Рис. 2.3. Текстові функції Excel
Заповнення стовпця В.
1. Встаньте на осередок В1 і натисніть кнопку fx.
2. На першому етапі в вікні майстри функцій виберіть групу Текстові, функцію ПРАВСИМВ і натисніть ОК (рис. 2.4).
Рис. 2.4. вікно Майстер функцій
3. На другому етапі в вікні майстри функцій введіть адресу оброблюваної осередки (поле текст) і число видобутих символів (рис. 2.5).
Рис. 2.5. Введення адреси оброблюваної осередки
Після натискання ОК в клітинку В1 автоматично буде записана функція \u003d ПРАВСИМВ (А1; 2), яка витягує (обчислює) з осередку А1 два правих символу. Зверніть увагу - в правій частині кожного поля показаний результат введення.
4. розмножте формулу з комірки В1 в осередку В2 і ВЗ, потягнувши за кут комірки В1.
Заповнення стовпця З:
1. Встаньте на осередок С1 і натисніть кнопку fx.
2. На першому етапі в вікні майстри функцій виберіть Текстові, функцію ПІДСТАВИТИ і натисніть ОК.
3. У наступному вікні майстра функційвведіть адресу осередки з оброблюваних текстом (А1), що замінюється текст (В1) і новий текст, як показано на малюнку на наступній сторінці. Після натискання ОК в осередку С1 автоматично сформується формула \u003d ПІДСТАВИТИ (А1; В1; «Перші»), яка підставляє закінчення «перші» в слово «Червоний» замість букв «Перший».
4. розмножте формулу з комірки С1 в осередку С2 і СЗ, потягнувши за кут комірки С1.
Запис прикметників із закінченням «Перший» в однині в стовпці А, в стовпці С вони будуть перетворюватися на множину (рис. 2.6).
Рис. 2.6. Вікно перетворення у множину
Сьогодні ми розглянемо функцію ЯКЩО.
Функція ЯКЩО часто використовується в Excel для вирішення багатьох завдань. Знати її дуже корисно. У даній статті ми спробуємо розповісти про її роботу на простих прикладах, досить один раз розібратися з конструкцією функцією ЯКЩО і ви зможете застосовувати її і в найскладніших варіантах.
функція ЯКЩО перевіряє, чи виконується умова, і повертає одне значення, якщо воно виконується, і інше значення, якщо немає.
Синтаксис функції ЯКЩО дуже простий:
ЯКЩО (лог_вираз; [ значеніе_еслі_істіна]; [значеніе_еслі_ложь])
лог_вираз - це будь-яке значення або вираз, яке при обчисленні дає значення ІСТИНА або БРЕХНЯ.
Що це означає? Вираз при обчисленні дає значення ІСТИНА якщо цей вислів вірно.
У цій частині необхідно перевірити на відповідність вираження.
наприклад:
ЯКЩО (А1 \u003d 10; [значеніе_еслі_істіна]; [значеніе_еслі_ложь]) - якщо А1 дорівнює 10, то вираз А1 \u003d 10 дасть значення ІСТИНА, а якщо не дорівнює 10, то БРЕХНЯ
Інший приклад
ЯКЩО (А1\u003e 30; [значеніе_еслі_істіна]; [значеніе_еслі_ложь]) - якщо в осередку А1 число більше 30, то А1\u003e 30 поверне ІСТИНА, а якщо менше, то БРЕХНЯ
ще приклад
ЯКЩО (С1 \u003d "Так"; [значеніе_еслі_істіна]; [значеніе_еслі_ложь]) - якщо в осередку C1 міститься слово "Так" то вираз поверне значення ІСТИНА, а якщо немає, то С1 \u003d "Так" поверне БРЕХНЯ
ЯКЩО (лог_вираз; [ значеніе_еслі_істіна]; [значеніе_еслі_ложь])
значеніе_еслі_істіна, значеніе_еслі_ложь - як видно з їх назви, це те що необхідно зробити в залежності від того, що повернув лог вираження: ІСТИНА і НЕПРАВДА
Приклад використання функції ЯКЩО в Excel
Розглянемо використання функції ЯКЩО на практичному прикладі. У нас є таблиця замовлень, яку ми використовували при розгляді роботи. Нам необхідно заповнити стовпець на замовлення Ведер (помилково на зображенні вказано «Замовлення Стіл»), тобто необхідно вибрати тільки замовлення з Відрами. Це можна зробити різними способами, але ми з вами буде використовувати функцію ЯКЩО, щоб показати її роботу на прикладі. (См.рисунок)
Для вирішення поставленого завдання напишемо формулу з використанням функції ЯКЩО
ЯКЩО (A3 \u003d "Відро"; D3; "-")
Як ви змогли помітити аргументи функції ЯКЩО розділені крапкою з комою.
Отже, перший аргумент (лог вираження) A3 \u003d "Відро" перевіряє чи міститься в осередку А3 слово «Відро», якщо міститься, то виконується другий аргумент функції ЯКЩО ( значеніе_еслі_істіна), В нашому випадку це D3 (тобто вартість замовлення), якщо в осередок А3 не дорівнює слову «Відро», то виконується третій аргумент функції ЯКЩО ( значеніе_еслі_ложь), В нашому випадку це «-» (тобто буде написано тире).
Таким чином, в осередку E3 з'явиться значення D3, тобто число 240.
Робота з великими формулами буває проблематична навіть для досвідчених користувачів. Найважче - це розбиратися в чужій конструкції і зрозуміти як вона працює. Нещодавно в рамках навчання, мене попросили розібрати кілька складних формул, причому з'ясувалося, що формули дійсно награможденние - я нарахував 7-8 ЯКЩО і ще приблизно 5-6 інших функцій в одній комірці. У таких ситуаціях дуже важливо визначити, що є аргументом кожної функції. Тому я вирішив написати невелику статтю про важливу штуку - аргумент Excel і його роль в обчисленнях. А найголовніше, опишу в статті - як зручно знайти і виділити кожен з аргументів, при написанні величезних формул.
Вважаю потрібно спочатку сказати пару слів банальної теорії.
Аргумент Excel. теорія
Аргументами називаються величини, що використовуються при обчисленнях функцій / формул. Аргументи можуть бути числом, текстом і навіть формулою з іншими функціями.
Аргументи можуть бути як обов'язковими (без яких формула не буде працювати), так і не обов'язковими (без яких функція буде працювати за замовчуванням), такі виділяються квадратними дужками.
ДЕНЬНЕД (дата_в_чісловом_формате;)
Де «дата_в_чісловом_формате» повинна бути обов'язково заповнена, а - можна не вносити, причому навіть крапку з комою ставити необов'язково.
Важливо відзначити, що функції можуть і не містити аргументу
СЬОГОДНІ ()
А можуть і бути з непостійним кількістю аргументів, як:
СУММЕСЛІМН ()
Як зручно знайти і виділити аргументи функції?
Для розуміння великих, важких формул, дуже важливо вміти виділяти аргументи вкладених функцій. Навіть якщо ви написали цю формулу, то я вам гарантую, через півроку ви далеко не відразу в ній розберетеся. Щоб зручно побачити аргумент функції клікніть на підказку знизу, яка з'явиться при заході в клітинку і аргумент буде виділено:
На прикладі клікніть «шукане значення» і в самій формулі воно подстветітся виділенням.
Для зручності так само прикладаю гифку
Але бувають записи й важчі. такі:
або такі
Формули не мої, сподіваюся творці не образяться. Упевнений багато (я в тому числі) зустрічали гори символів і серйозніше. У таких конструкціях розібратися без можливості виділення буває вкрай складно.
Що ще важливо сказати
При подібних конструкціях дуже важливо розібратися формулу по частинах, виділити її аргумент. Наявний в Excel метод виділення аргументу дуже зручний. Так само я можу порадити рознести кожну функцію на новий рядок за допомогою Alt + Enter - теж дуже допомагає - детальніше тут.
Так само важливо відзначити, що виділення аргументу функції можна робити після виділення будь-якого аргументу формули.
Наприклад, вам незрозуміло як працює функція в прикладі (спеціально не заповнили 4й аргумент ВВР).
Ви можете тицьнути мишкою на будь-яке місце формули, після чого для вас з'явиться підказка, який саме це аргумент.
З цієї підказки ви зможете перейти на будь-який з аргументів, клікнувши на нього
А також можете перейти в довідку, клікнувши на назву самої формули, в даному випадку ВПР.
Вдалих розборів своїх і чужих формул! Пишіть ваші зауваження.
(Visited 523 times, 1 visits today)
Привіт, друзі! Це вступна стаття про функції Excel, де я розповім, що таке функції, аргументи функції, як вставити функцію у формулу. А в наступних постах ми будемо розбиратися, які є функції Ексель і як їх правильно використовувати.
Функції в Excel - це інструкції, які виконують більш складні розрахунки, ніж математичні оператори. Деякі розрахунки неможливо виконати без функцій, тому їх ідеальне знання - запорука вашого успіху.
Ви можете «вкладати» одну функцію в іншу, копіювати формули з функціями (не забуваємо про різних типах посилань). Головне - чітко розуміти, як працює функція, інакше вона може дати невірний результат або помилку. Ви можете і не помітити цього. А щоб розібратися в роботі функцій - читайте мої пости про різні функції і довідку Microsoft.
З появою кожної нової версії Ексель, перелік функцій оновлюється. Розробники додають нові, затребувані, прибирають функції, які вже не актуальні. У цьому блозі я буду описувати функції Microsoft Excel 2013, але відповім на всі питання про функції в інших версіях програми. Як завжди, задавайте їх у коментарях.
Аргументи функції Excel
аргументи функцій - це ісхоние дані для розрахунку функції. Наприклад, для функції СУММ (підсумовування) - це перелік чисел, осередків або діапазонів осередків для підсумовування. Аргументи вказуються в дужках після імені функції і розділяються крапкою з комою (в англомовній версії - коми). За кількістю аргументів, функції можуть бути:
- без аргументів - не потребують аргументах для розрахунку. Наприклад, \u003d ПІ () - повертає число 3,1428.
- З одним аргументом - потрібно ввести всього один аргумент. Наприклад \u003d СТРОЧН (А1) - переведе в рядковий вид все символи в осередку А1.
- З кількома аргументами - потрібно ввести певне число аргументів, більше одного. Наприклад, функція \u003d ПСТР (А1; 1; 10) поверне перші 10 символів з рядка в комірці А1.
- З необов'язковими аргументами - функція має аргументи, які вказувати необов'язково. Наприклад, \u003d ВПР ( «Іванов»; А1: В30; 2; 0) буде шукати прізвище «Іванов» в діапазоні А1: В30 і поверне інформацію про нього. Останній аргумент тут - «Інтервальний перегляд» - позначає спосіб пошуку, його вказувати необов'язково.
- Зі змінним кількістю аргументів - кількість аргументів може змінюватися. Наприклад, \u003d СРЗНАЧ (А1; В3: В15; С2: F2) - вважатиме середнє значення цифр в зазначених діапазонах. Перераховуючи осередку через крапку з комою, ви можете задати різну кількість аргументів.
Як вставити функцію в Excel
Щоб вставити функцію у формулу, можна скористатися одним з перерахованих способів:
Якщо ім'я функції вам невідомо, скористайтеся вікном Вставка функції. Щоб його викликати - спробуйте один із способів:
Після виконання будь-якої з цих операцій, відкриється вікно Вставка функції. В поле Пошук функції коротко опишіть, що потрібно зробити і натисніть Знайти. В поле Виберіть функцію з'являться результати пошуку. Клікайте на функції в списку, читайте їх опису. Якщо відповідна функція не знайдено - перефразируйте запит і повторіть пошук.
Коли ви знайшли функцію, клікніть на ній 2 рази, відкриється вікно аргументів. Після їх заповнення, натисніть ОК, програма прорахує результат.
Вікно »Аргументи функції»
І знову я констатую, що ми вивчили важливу і просту тему. Поки що просту! Попрактикуйтесь самостійно вставляти функції на робочий лист і переконайтеся, що в цьому немає нічого складного. Тим не менш, це дуже важливі навички. Їх успішне застосування, доведене до автоматизму - це основа основ. Далі вже буду описувати роботи, засновані на практиці з цієї статті.
Ось і все про вставці функцій на лист, а в наступній статті ми почнемо розглядати текстові функції. До зустрічі на сторінках блогу OfficeЛЕГКО.com!
В Excel міститься безліч вбудованих функцій, які можуть бути використані для інженерних, статистичних, фінансових, аналітичних та інших розрахунків. Іноді умови поставлених завдань вимагають більш гнучкого інструменту для пошуку рішення, тоді на допомогу приходять макроси і призначені для користувача функції.
Приклад створення своєї користувальницької функції в Excel
Подібно макросам, призначені для користувача функції можуть бути створені з використанням мови VBA. Для реалізації даного завдання необхідно виконати наступні дії:
- Відкрити редактор мови VBA за допомогою комбінації клавіш ALT + F11.
- У вікні, вибрати пункт Insert і підпункт Module, як показано на малюнку:
- Новий модуль буде створено автоматично, при цьому в основній частині вікна редактора з'явиться вікно для введення коду:
- При необхідності можна змінити назву модуля.
- На відміну від макросів, код яких повинен знаходитися між операторами Sub і End Sub, призначені для користувача функції позначають операторами Function і End Function відповідно. До складу користувальницької функції входять назва (довільне ім'я, що відбиває її суть), список параметрів (аргументів) з оголошенням їх типів, якщо вони потрібні (деякі можуть не брати аргументів), тип значення, тіло функції (код, що відображає логіку її роботи) , а також оператор End Function. Приклад простий користувальницької функції, що повертає назви дня тижня в залежності від зазначеного номера, представлений на малюнку нижче:
- Після введення представленого вище коду необхідно натиснути комбінацію клавіш Ctrl + S або спеціальний значок в лівому верхньому кутку редактора коду для збереження.
- Щоб скористатися створеною функцією, необхідно повернутися до табличного редактору Excel, встановити курсор в будь-яку клітинку та ввести назву для користувача функції після символу «\u003d»:
Вбудовані функції Excel містять пояснення як повертається результату, так і аргументів, які вони приймають. Це можна побачити на прикладі будь-якої функції натиснувши комбінацію гарячих клавіш SHIFT + F3. Але наша функція поки що не має форми.
Щоб задокументувати для користувача функцію, необхідно виконати наступні дії:
- Створіть новий макрос (натисніть комбінацію клавіш Alt + F8), у вікні введіть довільну назву нового макросу, натисніть кнопку Створити:
- В результаті буде створений новий модуль з заготівлею, обмеженою операторами Sub і End Sub.
- Введіть код, як показано на малюнку нижче, вказавши необхідну кількість змінних (в залежності від числа аргументів для користувача функції):
- Як «Macro» повинна бути передана текстовий рядок з назвою користувальницької функції, як «Description» - змінна типу String з текстом опису повертається, як «ArgumentDescriptions» - масив змінних типу String з текстами описів аргументів для користувача функції.
- Для створення опису користувальницької функції досить один раз виконати створений вище модуль. Тепер при виклику користувача функції (або SHIFT + F3) відображається опис повертається результату і змінної:
Описи функцій створювати не обов'язково. Вони необхідні у випадках, якщо призначені для користувача функції будуть часто використовуватися іншими користувачами.
Приклади використання призначених для користувача функцій, яких немає в Excel
Вид вихідної таблиці даних:
Кожному працівникові покладається 24 вихідних дня з виплатою S \u003d N * 24 / (365-n), де:
- N - сумарна зарплата за рік;
- n - число святкових днів у році.
Створимо для користувача функцію для розрахунку на основі цієї формули:
Код прикладу:
Public Function Otpusknye (summZp As Long, holidays As Long) As Long
If IsNumeric (holidays) \u003d False Or IsNumeric (summZp) \u003d False Then
Otpusknye \u003d "Введено нечислові дані"
Exit Function
ElseIf holidays
Функції є залежність одного елемента (результату) від інших елементів (аргументів, .. тих, що всередині :-)). Це як би зрозуміло. Для того щоб використовувати будь-яку функцію в Excel, слід ввести її як формулу (нюанси описані тут) Або як частина формули в комірку робочого аркуша. Послідовність, в якій повинні розташовуватися застосовуються у формулі символи і аргументи, називається синтаксисом функції. Всі функції використовують однакові правила синтаксису. Якщо порушити ці правила, то Excel видасть повідомлення про те, що у формулі є помилка і не буде з вами дружити. Але повірте, у функціях Excel все досить однотипно і розібравшись один раз, на одній-двох функціях, в інших випадках все буде досить просто. Правила синтаксису при записі функцій Далі розглянуті правила, яких необхідно дотримуватися для грамотного і оптимального побудови формули з використанням однієї або декількох функцій. Якщо функція з'являється на самому початку формули, їй повинен передувати знак рівності, як це має місце на початку будь-якої формули. Я про це вже говорив в попередніх статтях, Але не гріх ще повторити. Після цього вводиться ім'я функції і відразу за ним - список аргументів у круглих дужках. Аргументи відокремлюються один від одного крапкою з комою «;». Дужки дозволяють Excel визначити, де починається і де закінчується список аргументів.Функція Excel Зауважте, в запису функції обов'язково повинні бути присутніми відкриває і закриває дужки, при цьому не можна вставляти пробіли між назвою функції і дужками. В іншому випадку Excel видасть повідомлення про помилку. В якості аргументів можна використовувати числа, текст, логічні значення, масиви, значення помилок або посилання. При цьому параметри, що задаються користувачем, повинні мати допустимі для даного аргументу значення. Наприклад, у наведеній нижче формулі здійснюється підсумовування значень в осередках В2, В3, В4, В5 і Е7, причому частина осередків - від В2 до В5, представлені як безперервний діапазон.
Аргументи функції Excel Розглянемо роботу функції ОКРУГЛ (арг1; арг2), яка повертає число, округлене до заданої кількості знаків після коми, і має два аргументи: арг1 - комірки має в своєму числом (або саме число), яке потрібно округлити; арг2 - кількість цифр після коми у числа після округлення.
Щоб округлити число 2,71828, що знаходиться в осередку A1, з точністю до одного, двох або трьох знаків після коми і записати результати обчислень відповідно в осередку B1, C1 і D1, необхідно діяти наступним чином. Ввести число 2,71828 в клітинку A1. Ввести в комірки B1, C1 і D1 формули наступного виду: \u003d ОКРУГЛ (A1; 1) \u003d ОКРУГЛ (A1; 2) \u003d ОКРУГЛ (A1; 3) Аргументи можуть бути як константами, так і функціями. Функції, які є аргументами іншої функції, називаються вкладеними. Наприклад, підсумуємо значення осередків А1 і А2, попередньо округливши ці значення до двох десяткових знаків: \u003d СУММ (ОКРУГЛ (A1; 2); ОКРУГЛ (A2; 2)) Тут функція ОКРУГЛ є вкладеною аж два рази, але це не страшно, в формулах Excel можна використовувати до семи рівнів вкладеності функцій. Варто відзначити, що в Excel існують функції, які не мають аргументів. До таких засобів належать ПІ (повертає значення числа π, округлене до 15 знаків) або СЬОГОДНІ (повертає поточну дату). При використанні подібних функцій слід в рядку формул відразу після назви функції ставити порожні круглі дужки без аргументів. Іншими словами, щоб отримати в осередках значення числа p або поточну дату, слід ввести формули такого виду: \u003d ПІ () \u003d СЕГОДНЯ () Типи функцій Excel Для зручності роботи користувача при побудові формул функції в Excel розбиті по категоріях: функції управління базами даних і списками, функції дати і часу, фінансові, статистичні, текстові, математичні, логічні. Текстові функції використовуються для обробки тексту, а саме: пошуку потрібних символів, записи символів в строго певне місце тексту і т.д. За допомогою функцій Дати і часу можна вирішити практично будь-які завдання, пов'язані з урахуванням календарних дат або часу (наприклад, розрахувати кількість робочих днів для будь-якого проміжку часу). Логічні функції використовуються при створенні складних формул, які в залежності від виконання тих чи інших умов реалізовуватимуть різні види обробки даних. Вони особливо цікаві, і про них поговоримо в окремій статті. В Excel широко представлені Математичні функції і деякі я вже привів в прикладах. У розпорядженні користувача також знаходиться бібліотека Статистичних функцій, за допомогою якої можна здійснювати пошук за середнє значення, максимального і мінімального елементів і ін.
функції Excel - це спеціальні, заздалегідь створені формули, які дозволяють легко і швидко виконувати складні обчислення. Їх можна порівняти зі спеціальними клавішами на калькуляторах, призначених для обчислення квадратних коренів, логарифмів і ін. Excel має кілька сотень вбудованих функцій, які виконують широкий спектр різних обчислень. Деякі функції є еквівалентами довгих математичних формул, які можна зробити самому. А деякі функції у вигляді формул реалізувати неможливо. Функції складаються з двох частин: імені функції і одного або декількох аргументів. ім'я функції , наприклад СУМ, - описує операцію, яку ця функція виконує. аргументи задають значення або осередки, які використовуються функцією. У формулі, наведеній нижче: СУМ - ім'я функції; В1: В5 - аргумент. Дана формула підсумовує числа в осередках В1, В2, В3, В4, В5.
\u003d СУММ (В1: В5)
Знак рівностіна початку формули означає, що введена саме формула, а не текст. Якщо знак рівності буде відсутній, то Excel сприйме введення просто як текст. аргумент функції укладений в круглі дужки . Відкриває дужка відзначає початок аргументу і ставиться відразу після імені функції. У разі введення пробілу або іншого символу між ім'ям і відкриває дужкою в осередку буде відображено помилкове значення # ИМЯ? Деякі функції не мають аргументів. Навіть в цьому випадку функція повинна містити круглі дужки:
\u003d С5 * ПІ ()
При використанні в функції декількох аргументів вони відокремлюються один від іншого крапкою з комою . Наприклад, наступна формула вказує, що необхідно перемножити числа в осередках А1, А3, А6:
\u003d Вироблено (А1; А3; А6)
У функції можна використовувати до 30 аргументів, якщо при цьому загальна довжина формули не перевищує 1024 символів. Однак будь-який аргумент може бути діапазоном , Що містить довільне число осередків листа. наприклад:
\u003d СУММ (А2: А5; В4: В8)
У наведених раніше прикладах всі аргументи були посиланнями на комірки або діапазони . Але в якості аргументів можна також використовувати числові, текстові та логічні значення, імена діапазонів, масиви і помилкові значення. Деякі функції повертають значення цих типів, і їх надалі можна використовувати в якості аргументів в інших функціях. Аргументи функції можуть бути числовими. Наприклад, функція СУММ в такій формулі підсумовує числа 24, 987, 49:
\u003d СУММ (24; 987; 49)
Як аргумент функції можуть використовуватися текстові значення. наприклад:
\u003d ТЕКСТ (ТДАТУ (); "Д МММ РРРР")
У цій формулі другий аргумент функції ТЕКСТ є текстовим і задає шаблон для перетворення десяткового значення дати, що повертається функцією ТДАТУ (NOW), в рядок символів. Текстовий аргумент може бути рядком символів, укладеної в подвійні лапки, або посиланням на осередок, яка містить текст.
Аргументи ряду функцій можуть приймати тільки логічні значення ІСТИНАабо БРЕХНЯ. Логічний вираз повертає значення ІСТИНАабо БРЕХНЯв клітинку або формулу, яка містить цей вислів. наприклад:
\u003d ЕСЛИ (А1 \u003d ІСТИНА; "Підвищення"; "Зниження") & "ціни"
Як аргумент функції можна вказати ім'я діапазону. Наприклад, якщо діапазону комірок А1: А5 присвоєно ім'я "Дебет" (Вставка - Ім'я - Присвоїти), то для обчислення суми чисел в осередках з А1 по А5 можна використовувати формулу:
\u003d СУММ (Дебет)
В одній функції можна використовувати аргументи різних типів:
\u003d СРЗНАЧ (Дебет; С5; 2 * 8)
Excel. Використання функцій робочої таблиці. Аргументи. Майстер функцій. Логічні, інформаційні функції і функції роботи з посиланнями і масивами
Використання функцій робочої таблиці Функція Excel - це спеціальна, заздалегідь визначена формула, яка працює з одним або декількома значеннями і повертає результат.
Всі функції можна поділити на такі групи: l l l-математичні, статистичний, -логічні, -Дата і час, -Фінансові, -текстовий, -Посилання і масиви, -роботи з базою даних, перевірка властивостей і значень -інженерні, -інформаційні
Майстер функцій. Крок 1 і 2 l Вставка функції шляхом натискання значка функції на панелі інструментів l Вибір функції
Майстер функцій. Крок 3 і 4 l l Діалогове вікно «Аргументи функції» Завершальний етап роботи майстра функцій. висновок формули
Логічні функції l логічні функції є невід'ємними компонентами багатьох формул. Вони використовуються всякий раз, коли необхідно виконати ті чи інші дії в залежності від виконання будь-яких умов
Src \u003d "http://present5.com/presentation/3/88838539_293880995.pdf-img/88838539_293880995.pdf-8.jpg" alt \u003d "(! LANG: Функція ЯКЩО Синтаксис: \u003d ЕСЛИ (логічний_вираз; значеніе_еслі_істіна; значеніе_еслі_ложь) l приклад: \u003d ЕСЛИ (А 1\u003e 3; 10; 20) \u003d ЕСЛИ (В 5\u003e 100;"> Функция ЕСЛИ Синтаксис: =ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь) l Пример: =ЕСЛИ(А 1>3; 10; 20) =ЕСЛИ(В 5>100; “Принять”; ”Отказать”) l!}
Функції І, АБО, НЕ l l l функції І (AND), АБО (OR), НЕ (NOT) - дозволяють створювати складні логічні вирази. Ці функції працюють в поєднанні з простими операторами порівняння. Функції І і АБО можуть мати до 30 логічних аргументів. Синтаксис: \u003d І (логіческое_значеніе 1; логіческое_значеніе 2...) \u003d АБО (логіческое_значеніе 1; логіческое_значеніе 2...) \u003d НЕ (логіческое_значеніе)
Src \u003d "http://present5.com/presentation/3/88838539_293880995.pdf-img/88838539_293880995.pdf-10.jpg" alt \u003d "(! LANG: Функції І, АБО, НЕ l Приклади: l \u003d ЕСЛИ (И (А 3\u003e 0; D 3\u003e 0); "Рішення є"; "Рішення"> Функции И, ИЛИ, НЕ l Примеры: l =ЕСЛИ(И(А 3>0; D 3>0); “Решение есть”; “Решения нет”)) l =ЕСЛИ(ИЛИ(А 3!}
Вкладені функції ЯКЩО При вирішенні складних логічних задач можна використовувати вкладені функції ЯКЩО l Приклад: \u003d ЕСЛИ (В 10 \u003d 25; "Відмінно"; ЕСЛИ (И (В 10 22); "Добре"; ЕСЛИ (И (В 1019); " задовільно ";" Незадовільно "))) l
Функції ІСТИНА і НЕПРАВДА функції ІСТИНА (TRUE) і БРЕХНЯ (FALSE) надають альтернативний спосіб запису логічних значень ІСТИНА і НЕПРАВДА. Ці функції не мають аргументів і виглядають наступним чином: l \u003d ІСТИНА () l \u003d БРЕХНЯ () l Приклад: \u003d ЕСЛИ (А 1 \u003d ІСТИНА (); "Проходьте"; "Стоп") l