Ім'я файлу: ВН-21. Створення формул. Робота з майстром функцій. Робота з вкл
Розширення: docx
Розмір: 187кб.
Дата: 24.11.2022
скачати
Пов'язані файли:
УРОК №1 Класифікація програмного забезпечення..docx
конспект_аналітика.DOCX
Урок №2. Ковальство..docx
Урок №1. Поняття енергії, енергозбереження..docx

Урок №20. Створення формул. Робота з майстром функцій. Робота з вкладеними функціями.
Стандартні функції використовують у програмі Excel тільки у формулах. Це значить, що якщо в комірку уводиться одна лише функція, то вона буде починатися зі знака дорівнює. У формулі функція вказується за допомогою імені функції, після якої в дужках вказується список параметрів (аргументів). Окремі параметри розділяють у списку крапкою з коми. Як параметр може використовуватися число, адреса комірки або довільний вираз, для обчислення якого також можуть використовуватися функції. Якщо почати введення формули зі знака дорівнює, то ліворуч від рядка формул з'являться три значки, перший значок у вигляді букви Х – скасувати, другий у вигляді «галочки» - введення, третій fx – вставка функції рис. 1.



Рисунок 1. Введення формул в комірку
У лівій частині рядка формул, де раніше розташовувалася адреса поточної комірки, тепер з'являється список функцій. Якщо його розкрити, то можна вибрати із цього списку одну з десяти функцій, які використовувалися останніми, а також пункт інші функції для вибору потрібної функції. При виборі функції палітра функції змінить свій вид рис. 2. У цій палітрі можна задати аргумент функції й побачити результат обчислення функції. Параметри можна вводити безпосередньо в рядок формул або в поля палітри формул, а якщо вони є посиланнями - вибирати на робочому листі за допомогою мишки. Після натискання клавіші ОК функція буде введена в поточну комірку. Правила обчислення формул, що містять функції, не відрізняються від правил обчислення простих формул.


Рисунок 2. Палітра формул, при виборі конкретної функції
Якщо назва параметра в палітрі формул зазначено напівжирним шрифтом, параметр є обов'язковим і відповідне полем повинне бути заповнене. Параметри, назви яких приводяться звичайним шрифтом, можна опускати. Якщо в списку, що розкрився, функцій з десяти функцій ви не змогли вибрати ні однієї, тоді необхідно скористатися пунктом інші функції. При виборі пункту інші функції запускається Майстер функцій, що полегшує вибір потрібної функції рис 3.



Рисунок 3. Вікно майстра функцій
У списку Категория вибирається категорія, до якої відноситься функція (якщо визначити категорію важко - найбільш загальне логічне визначення, використовують пункт Полный алфавитный перечень, а в списку Функция - конкретна функція даної категорії. Після клацання на кнопці ОК ім'я функції заноситься в рядок формул разом з дужками, що обмежують список параметрів. Текстовий курсор встановлюється між цими дужками.
Функціональна залежність компонентів у формулах

Формули складають основу обробки даних у табличному процесорі Mі-crosoft Excel. Компонентами формул є:

  • знаки математичних операцій;

  • вбудовані функції Microsoft Excel;

  • константи (числової, текстові, дати, логічні);

  • посилання на осередки;

  • посилання на імена блоків осередків.

У формулах компоненти зв'язані функціональною залежністю, цей зв'язок можна графічно представляти за допомогою панелі інструментів Зависимости, що виводиться на екран командою Сервис ►Зависимости формул ► Панель зависимостей (рис. 4).



Рисунок 4. Панель інструментів Зависимости
Комірки електронної таблиці поділяються на залежні і ті що впливають на результат. Одна й та ж сама комірка може бути залежною від одних і така що впливає на інші комірки. Для аналізу залежностей комірок у формулах в панелі інструментів Зависимости використовуються такі кнопки:

Влияющие ячейки - для відображення стрілок від комірок (аргументів), від яких залежить значення поточної комірки з формулою. Для перегляду усіх зв’язків варто натиснути цю кнопку. Курсор при цьому повинний бути на комірці з формулою.

Зависимые ячейки - для відображення стрілок на комірки (функції), які явно залежать від значення поточної комірки (аргументу). Для перегляду зв'язку варто натиснути цю кнопку. Курсор повинний знаходитися на комірці з аргументом функції або на комірці з елементом формули.

Источник ошибки - для відображення всіх комірок, що впливають на поточну комірку, у якій міститься помилкове значення. Якщо помилки немає, стрілки не відображаються.

Обвести неверные данные - для відображення комірок, що не задовольняють умовам перевірки, що були встановлені командою Данные ►Проверка.

Создать примечание - для комірок з формулами вводяться необхідні коме­нтарі. Щоб вилучити примітку необхідно виконати команду Вид ►Примечание. Для видалення знака примітки необхідно виконати команду Удалить примечание на панелі інструментів Рецензирование.

Кнопки на панелі Зависимости: Убрать все стрелки, Убрать стрелки к влияющим ячейкам або Убрать стрелки к зависимым ячейкам - для вида­лення непотрібних ліній на листі. При побудові виразів у формулах вико­ристо­ву­ються такі знаки:

  • арифметичні оператори: + (плюс), - (мінус), * (множення), / (розподіл), ^ (зведення в ступінь), % (відсотки);

  • текстовий оператор & (амперсент) для об'єднання текстових послідовностей, кожна з який задається в подвійних лапках;

  • реляційні оператори порівняння: = (знак рівності), > (знак більше), < (знак менше), > = (більше чи дорівнює) < = (менше чи дорівнює), <> (менше чи більше).

У формулах діапазон комірок вказуються за допомогою символу : (двокрапка). Якщо у формулі міститься кілька діапазонів комірок, тоді кожний діапазон відокремлюється один від одного через ; (крапка з коми). Наприклад, СУМ(У1: У5;D1:D5). Для завершення уведення формул варто натиснути клавішу Enter або кнопку (галочка) у рядку формул - ознака підтвердження уведення формули. Для відмови від уведення формули варто натиснути клавішу Esc у рядку формули.
Майстер функцій

Для включення у формулу вбудованої функції використовується майстер функцій, який викликається різним чином:

  • за допомогою меню Вставка ►Функция;

  • при натисненні кнопки fx на панелі інструментів Стандартная.

  • при натисненні кнопки = (дорівнює) на клавіатурі і тоді можна вивести список вбудованих функцій зліва від рядка формул для вибору функції.

У списку вбудованих функцій є 10 останніх функцій які раніш використовувались, а також вказівка на використання інших функцій.

Введення пара­метрів вбудованих функцій виконується в спеціальному діалоговому вікні, наприклад як на рисунку 5 яке є палітрою функції.

У діалоговому вікні містяться поля для введення параметрів (аргументів) функції, короткий коментар про призначення функції і кожного окремого параметра.

У цьому вікні міститься посилання на довідкову систему функції.

Для завершення введення параметрів функції натискається кнопка ОК, для відмови від введення параметрів - кнопка Отмена.

Параметрами вбудованої функції можуть бути: константи, посилання на комірки або діапазон комірок; імена блоків, обчислювані вирази, зокрема з використанням інших вкладених функцій.



Рисунок 5. Палітра фінансової функції БС для введення параметрів
Максимально допустима глибина вкладень вбудованих функцій у формулі-7. Порядок введення констант:

  • числові константи вводяться з урахуванням знаку числа і роздільника десяткової крапки;

  • рядок тексту вводиться без подвійних лапок;

  • дати вводяться з прийнятим роздільником (крапка, коса межа або дефіс), або використовується вбудована функція ДАТА (рік, місяць, день) для перетворення дати в числовий формат;

  • логічні константи водяться як числа 1- истина, 0 - ложь або рядки тексту ИСТИНА, ЛОЖЬ (без лапок), або як вбудовані функції ИСТИНА() і ЛОЖЬ().

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

При виведенні адреси комірки або діапазону можна:


  • вільно переміщати курсор на листі, переходити на інші листи або в іншу відкриту робочу книгу;

  • формувати посилання на комірку клацанням лівою кнопкою миші;

  • формувати посилання на діапазон комірок шляхом виділення діапазону при натиснутій лівій кнопці миші;

  • виводити список імен блоків для вибору за допомогою команди Вставка ► Имя ► Вставить або натисненням клавіші F3.

Якщо іменований блок комірок існує, при виділенні відповідного діапазону комірок у формулу автоматично вводиться блок. Якщо іменований блок комірок створюється після введення формули, команда меню Вставка ►Имя ► Применить замінює посилання на діапазон комірок ім'ям блоку.
Вбудовані функції в Excel

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

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

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

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

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

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

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

Де номер рядка; номер стовпця комірки вказуються цифрами, тип посилання може бути 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)}.

Вбудовані функції категорії «Текстовые функции»

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

Текстова функція ТЕКСТ() перетворює значення в текст в заданому числовому форматі.

Синтаксис: ТЕКСТ(значення;формат).

Значення - числове значення, або формула, обчислення якої дає числове значення, або посилання на комірку, що містить числове значення. Формат -  числовий формат в текстовій формі із списку Числовые форматы з вкладки Число діалогового вікна Формат ячеек.

  • Формат не може містити зірочку (*).

  • Форматування комірки за допомогою вкладки Число (команда Ячейки меню Формат) міняє тільки формат, але не значення. Використання функції ТЕКСТ перетворить значення у форматований текст, і результат більше не бере участь в обчисленнях як число.

Розглянемо приклад використання функції ТЕКСТ(). Нехай в комірці А1 буде прізвище Міщенко І.А. В комірці В1 буде вказане любе число, нехай 30000. Треба в комірці С1 вивести такий коментар: Міщенко І.А. продав товару на 30000 грн одиниць товару.

Для цього в комірку С1 введемо функцію =ТЕКСТ(=A1&" продав на"&ТЕКСТ(B1;"# ##0[$ грн.]")&" одиниць товару."

В даному прикладі використаний той формат який є у вікні Формат ячеек вкладки Число. Знак & використовується для з’єднання слів.

Текстова функція ЗНАЧЕН перетворить рядок тексту, що відображає число, в число.

Синтаксис: ЗНАЧЕН(текст)

текст - текст в лапках або посилання на комірку, що містить текст, який потрібно перетворити.

  • Текст може бути в будь-якому форматі, Microsoft Excel, що допускається, для числа, дати і часу. Якщо текст не задовольняє жодному з цих форматів, то функція ЗНАЧЕН повертає значення помилки #ЗНАЧ!.

  • Зазвичай немає необхідності використовувати функцію ЗНАЧЕН у формулах, оскільки Microsoft Excel автоматичний перетворить текст в число при необхідності. Ця функція призначена для сумісності з іншими програмами електронних таблиць.

Функція СЦЕПИТЬ - об'єднує декілька текстових рядків в одну.

Синтаксис: СЦЕПИТЬ(текст1;текст2;...),

текст1, текст2 ...- це від 1 до 30 елементів тексту, що об'єднуються в один елемент тексту. Елементами тексту можуть бути текстові рядки, числа або посилання, які посилаються на одну комірку.

Замість функції СЦЕПИТЬ для об'єднання текстів можна використовувати оператора «&».

Приклад на використання функції СЦЕПИТЬ. Нехай комірка А1 буде мати такий зміст: Іванов А.Н. одержав, в комірці В1 буде введене число 5000., в комірці С1 грн зарплати. Треба в комірці D1 вивести таке повідомлення: Іванов А.Н. одержав 5000 грн зарплати. Тоді в комірці D1 треба записати функцію: =СЦЕПИТЬ(A1;B1;C1).

Функція: ДЛСТР повертає кількість знаків в текстовому рядку

Синтаксис: ДЛСТР(текст)

текст - текст, довжину якого потрібно визначити. Пропуски також враховуються.

Наприклад: функція =ДЛСТР(“Фінанси”) введена в будь-яку комірку дає значення 7. В аргументі також можна вказувати посилання на комірку.

Функція ПОИСК використовується для пошуку входження знаку або рядка тексту в інший рядок тексту, з тим щоб змінити текст.

Синтаксис: ПОИСК (шуканий текст;текст для пошуку ;поч. позиція).

Нехай нам потрібно знайти позицію букви к у слові Податок, слова знаходиться у комірці А1. Тоді функція запишеться так: =ПОИСК("т";A1;1), результат буде 5.
Вбудовані функції категорії «Дата время»

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

Функція =СЕГОДНЯ() визначає поточну дату і не має аргументів.

Функція =ТДАТА() визначає поточну дату і час.

Функція =ДАТА(рік;місяць;день) визначає дату в числовому форматі, наприклад функція =ДАТА(2009;6;17) дозволяє отримати результат: 17.06.09.

Функція ДАТАЗНАЧ(“17.06.2009 ”) дозволяє перевести дату з текстового формату в числовий.

Функція Год повертає рік, відповідний аргументу дата у числовому форматі. Рік визначається як ціле в інтервалі 1900-9999.

Синтаксис: Год(дата у числовому форматі).

Дати повинні вводитися з використанням функції ДАТА або як результат обчислення інших формул і функцій. Наприклад для функції ДАТА(2009;8;23), функція =ГОД(ДАТА(2009;8;23) дає нам рік 2009.

Функція МЕСЯЦ повертає місяць в даті, заданій в числовому форматі. Місяць повертається як ціле число діапазоні від 1 (січень) до 12 (грудень).

Синтаксис: МЕСЯЦ(дата в числовому форматі).

Дата в числовому форматі - це дата, місяць якої необхідно знайти. Дати повинні вводитися з використанням функції ДАТА або як результат інших формул і функцій. Наприклад, для функції ДАТА(2009,8,23) функція =МЕСЯЦ(ДАТА(2009;8;23) дає нам місяць 8.

Функція ДЕНЬ повертає день в даті, заданій в числовому форматі. День повертається як ціле число діапазоні від 1 до 31.

Синтаксис: ДЕНЬ(дата в числовому форматі)

Дата в числовому форматі - це дата, день якої необхідно знайти. Дати повинні вводитися з використанням функції ДАТА або як результат інших формул і функцій.

Наприклад, для функції ДАТА(2009,8,23) функція =ДЕНЬ(ДАТА(2009;8;23) дає нам 23 серпня.

Функція ЧАС повертає годину, відповідну заданому часу в числовому форматі. Година визначається як ціле в інтервалі від 0 до 23.

Синтаксис: ЧАС(час в числовому форматі)

Час в числовому форматі - час, для якого потрібно виділити години. Час може бути заданий текстовим рядком в лапках (наприклад "18:45"), десятковим числом (наприклад 0,78125 - відповідає 18:45) або результатом інших формул або функцій (наприклад ВРЕМЗНАЧ("18:45")).

Якщо потрібно визначити годину відповідного часу який введений в комірку тоді функція запишеться так: =ЧАС(A2).

Наприклад, нехай в комірці А2 буде введений час 3:30:30 АМ (АМ потрібно вводити латинськими символами) тоді функція дає нам 3 години ранку, якщо в комірці буде введений час 3:30:30 РМ або 15:30 тоді функція дає нам 15 годин.

Microsoft Excel для Windows і Excel для «Макінтоша» використовують різні системи дат за умовчанням. Час є дробовою частиною значення дати і задається десятковим числом (наприклад, 12:00 представляється як 0,5).


Вбудовані функції категорії «Проверка свойств и значений»

Функція ИНФОРМ повертає інформацію про поточне операційне середовище.

Синтаксис: ИНФОРМ(тип інформації)

Таблиця1. Тип інформації та значення яке повертається функцією ИНФОРМ

Тип інформації

Значення яке повертається

"версия"
Версія Microsoft Excel у вигляді текстового рядка.

"версия ос"
Поточна версія операційної системи у вигляді текстового рядка.

"доступная память"
Кількість вільної пам'яті в байтах.

"используемая память"
Кількість пам'яті, використовуваної для даних.

"источник"
Повертається посилання на найлівішу верхню видиму у вікні комірку з урахуванням поточного стану прокрутки, наприклад: $A:$A$1.

"каталог"
Шлях поточного каталогу.

"память всего"
Загальна кількість доступної пам'яті в байтах, включаючи вже використовувану.

"перевычислить"
Поточний режим повторного обчислення; повертається «Автоматично» або «Вручну».

"система"
Назва операційного середовища:
Macintosh="mac" Windows = "pcdos"

"число файлов"
Кількість активних робочих листів у відкритих книгах.


Функції перевірки типу значень - це дев'ять функцій категорії «Проверка свойств и значений» які використовуються для перевірки типу значення або посилання.

Кожна з цих функцій перевіряє тип значення і повертає значення ИСТИНА або ЛОЖЬ залежно від типу.

Наприклад, функція ЕПУСТО повертає логічне значення ИСТИНА, якщо значення, що перевіряється, є посиланням на порожню осередок; інакше повертається логічне значення ЛОЖЬ.

Синтаксис дев'яти функцій:

  1. ЕПУСТО(значення).

  2. ЕОШ(значення).

  3. ЕОШИБКА(значення).

  4. ЕЛОГИЧ(значення).

  5. ЕНД(значення).

  6. ЕНЕТЕКСТ(значення).

  7. ЕЧИСЛО(значення).

  8. ЕССИЛКА(значення).

  9. ЕТЕКСТ(значення).

Значення - це значення, що перевіряється. Значення може бути порожньою коміркою, значенням помилки, логічним значенням, текстом, числом, посиланням або ім'ям об'єкту будь-якого з перерахованих типів.

  • Аргумент цих функцій не перетворюється. Для більшості інших функцій, наприклад, якщо як аргумент потрібне число, то текстове значення «19» перетвориться в число 19. Проте у формулі ЕЧИСЛО("19") «19» не перетвориться з тексту в число, і функція ЕЧИСЛО повертає значення ЛОЖЬ.

  • Ці функції корисні у формулах і макросах для перевірки результатів обчислень. Комбінуючи ці функції з функцією ЕСЛИ, можна локалізувати помилки у формулах.


Вбудовані функції категорії «Математические»

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

Функція SIN повертає синус заданого кута.

Синтаксис: SIN(число)

Число - кут в радіанах, для якого обчислюється синус.

Якщо аргумент заданий в градусах, помножте його на ПИ()/180 або використовуйте функцію РАДИАНЫ, щоб перетворити в радіани.

Приклади:

  • =SIN(ПИ()) - Синус ПИ радіан (0, приблизно).

  • =SIN(ПИ()/2) - Синус ПИ/2 радіан (1).

  • =SIN(30*ПИ()/180) - Синус 30 градусов (0,5).

  • =SIN(РАДИАНЫ(30)) - Синус 30 градусів (0,5).

Функція COS повертає косинус заданого кута.

Синтаксис: COS(число).

Число - це кут в радіанах, для якого визначається косинус.

Замітка: Якщо кут заданий в градусах, помножте його на ПИ()/180 або використовуйте функцію РАДИАНЫ, щоб перетворити його в радіани.

Приклади:

  • =COS(1,047) - Косинус угла 1,047 радиан (0,500171).

  • =COS(60*ПИ()/180) -Косинус угла 60 градусов (0,5).

  • =COS(РАДИАНЫ(60)) - Косинус угла 60 градусов (0,5).

Функція ASIN повертає арксинус числа. Арксинус числа  це кут, синус якого дорівнює числу. Кут визначається в радіанах в діапазоні від «-ПИ»/2 до «ПИ»/2.

Синтаксис: ASIN(число)

Число  це синус шуканого кута, значення повинне бути в діапазоні від -1 до 1.

Щоб виразити арксинус в градусах, помножте результат на 180/ПИ( ) або використовуйте функцію ГРАДУСЫ.

Приклади:

  • =ASIN(-0,5) - Арксинус числа -0,5 у радіанах -ПИ/6 (-0,5236).

  • =ASIN(-0,5)*180/ПИ() - Арксинус числа -0,5 у градусах (-30).

  • =ГРАДУСЫ(ASIN(-0,5)) - Арксинус числа -0,5 у градусах (-30).


Функція ACOS повертає арккосинус числа. Арккосинус числа  це кут, косинус якого рівний числу. Кут визначається в радіанах в інтервалі від 0 до «ПИ».

Синтаксис: ACOS(число).

Число  це косинус шуканого кута, значення повинне знаходитися в діапазоні від -1 до 1.

Якщо потрібно перетворити результат радіан в градуси, то помножте його на 180/ПИ() або використовуйте функцію ГРАДУСЫ.

Приклади:

  • =ACOS(-0,5) - Арккосинус числа -0,5 у радіанах, 2*ПИ/3 (2,094395).

  • =ACOS(-0,5)*180/ПИ() - Арккосинус числа -0,5 у градусах (120).

  • =ГРАДУСЫ(ACOS(-0,5)) - Арккосинус числа -0,5 у градусах (120).

Функція EXP повертає число «e», зведене у вказаний ступінь. Число «e» рівне 2,71828182845904 і є підставою натурального логарифма.

Синтаксис: EXP(число).

Число - це число, для якого обчислюється експоненціальна функція з підставою «e».

Приклади:

  • =EXP(1) - наближене значення е (2,718282).

  • =EXP(2) - підстава натурального логарифма «e», зведене в квадрат (7,389056).

Функція LN повертає натуральний логарифм числа. Натуральний логарифм це логарифм по підставі e (2,71828182845904).

Синтаксис: LN(число)

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

LN є зворотною функцією до EXP.

Приклади:

  • =LN(86) - натуральний логарифм числа 86 (4,454347).

  • =LN(2,7182818) - натуральний логарифм числа e (1).

  • =LN(EXP(3)) - натуральний логарифм числа e, зведеного в третій ступінь (3).

Функція LOG повертає логарифм числа по заданій підставі.

Синтаксис: LOG(число;підстава).

Число - позитивне дійсне число, для якого обчислюється логарифм.

Підстава - підстава логарифма. Якщо підстава опущена, то воно вважається рівною 10.

Приклади:

  • =LOG(10) - логарифм числа 10 (1).

  • =LOG(8; 2) - логарифм числа 8 по підставі 2 (3).

  • =LOG(86; 2,7182818) - логарифм числа 86 по підставі e (4,454347).

Функція LOG10 повертає десятковий логарифм числа.

Синтаксис: LOG10(число).

Число - позитивне дійсне число, для якого обчислюється десятковий логарифм.

Приклади:

  • =LOG10(86) - десятковий логарифм числа 86 (1,934498451).

  • =LOG10(10) - десятковий логарифм числа 10 (1).

  • =LOG10(1E5) - десятковий логарифм числа 1E5 (5).

  • =LOG10(10^5) - десятковий логарифм числа 10^5 (5).

Функція КОРЕНЬ повертає позитивне значення квадратного кореня.

Синтаксис: КОРЕНЬ(число).

Число - число, для якого обчислюється квадратний корінь.

Замітки: Якщо число негативне, то функція КОРЕНЬ повертає значення помилки #ЧИСЛО!

Приклад: =КОРЕНЬ(16)- квадратний корінь числа 16 (4)

Функція СТЕПЕНЬ повертає результат зведення числа в ступінь.

Синтаксис: СТЕПЕНЬ(число;ступінь)

Число - підстава. Воно може бути будь-яким дійсним числом.

Ступінь - показник ступеня, в який зводиться підстава.

Замітка: Замість функції СТЕПЕНЬ для піднесення до ступеня можна використовувати оператора ^, наприклад 5^2

Функція МОПРЕД повертає визначника матриці (матриця зберігається в масиві).

Синтаксис: МОПРЕД(масив)

Масив - числовий масив з рівною кількістю рядків і стовпців.

  • Масив може бути заданий як інтервал комірок, наприклад A1:C3, або як масив констант, наприклад {1;2;3:4;5;6:7;8;9}, або як ім'я, що іменує інтервал або масив.

  • Якщо яка-небудь комірка в масиві порожня або містить текст, то функція МОПРЕД повертає значення помилки #ЗНАЧ!.

  • МОПРЕД також повертає значення помилки #ЗНАЧ!, якщо масив має нерівну кількість рядків і стовпців.

  • Визначник матриці - це число, що обчислюється на основі значень елементів масиву. Для масиву A1:C3, що складається з трьох рядків і трьох стовпців, визначник обчислюється таким чином:

  • МОПРЕД(A1:C3) визначає як:
    A1*(B2*C3-B3*C2)+ A2*(B3*C1-B1*C3)+ A3*(B1*C2-B2*C1)

  • Визначники матриць зазвичай використовуються при вирішенні систем рівнянь з декількома невідомими.

  • МОПРЕД проводить обчислення з точністю приблизно 16 значущих цифр, що може в деяких випадках приводити до невеликих чисельних помилок.

Приклад: Нехай дана матриця як на рис.6.



Рисунок 6. Матриця для приклада
Функцію МОПРЕД запишемо так: =МОПРЕД(A2:D5) - визначник приведеної вище матриці (88).

Функція ABS повертає модуль (абсолютну величину) числа. Абсолютна величина числа - це число без знаку.

Синтаксис: ABS(число).

Число - це дійсне число, модуль якого потрібно знайти.

Приклад: =ABS(A2) - абсолютне значення -4 (4)

Функція ОКРУГЛ округляє число до вказаної кількості десяткових розрядів.

Синтаксис: ОКРУГЛ(число;число розрядів), де число - число, що округляється. Число розрядів - кількість десяткових розрядів, до якої потрібно округляти число.

  • Якщо число розрядів більше 0, то число округляється до вказаної кількості десяткових розрядів праворуч від десяткової коми.

  • Якщо число розрядів рівне 0, то число округляється до найближчого цілого.

  • Якщо число – розрядів менше 0, то число округляється зліва від десяткової коми.

Приклад: =ОКРУГЛ(2,15; 1) Округляє число 2,15 до одного десяткового розряду (2,2).
Логічні функції

Розглянемо основні логічні функції:ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ.

ФункціяЕСЛИ використовується при перевірці умов для значень і формул. Умова - це логічний вираз в якому використовуються знаки порівняння =, <> (не дорівнює), <, >, <=, >= між виразами, змінними, числами.

Синтаксис: ЕСЛИ(Логічний вираз;Вираз 1 ;Вираз 2).

Логічний_вираз приймає значення ИСТИНА або ЛОЖЬ.

Якщо логічний вираз приймає значення ИСТИНА то виконується Вираз 1 інакше Вираз 2.

Логічний вираз це і є умова яка перевіряється наприклад, A10 =100 - це логічний вираз, якщо значення в комірці A10 дорівнює 100, то вираз приймає значення ИСТИНА. У противному випадку - ЛОЖЬ. Наприклад, записана функція ЕСЛИ в якійсь комірці і має вид: =ЕСЛИ(A2<=100;"В межах бюджету";"Поза бюджетом"). Це означає, якщо в комірці А2 число менше або дорівнює числу 100 (тобто ИСТИНА), функція ЕСЛИ приймає значення «В межах бюджету». У противному випадку - «Поза бюджетом».

Функція И - це логічне множення.

Синтаксис: И(логічне_значення 1; логічне_значення 2; ...)

Логічне_значення 1, логічне_значення 2, ... це від 1 до 30 умов, що перевіряються, які можуть мати значення або ЛОЖЬ, або ИСТИНА.

Функція И повертає значення ИСТИНА, якщо всі аргументи мають значення ИСТИНА; повертає значення ЛОЖЬ, якщо хоча б один аргумент має значення ЛОЖЬ.

Наприклад, =И(2+2=4;2+3=5), Результат - ИСТИНА.

Функція ИЛИ – це логічне додавання.

Функція ИЛИ повертає значення ИСТИНА, якщо хоча б один з аргументів має значення ИСТИНА; повертає ЛОЖЬ, якщо всі аргументи мають значення ЛОЖЬ.

Синтаксис: ИЛИ(логічне_значення 1;логічне_значення 2; ...)

Логічне_значення 1, логічне_значення 2,...- від 1 до 30 умов, що перевіряються, які можуть мати значення або ИСТИНА, або ЛОЖЬ.

Наприклад, =ИЛИ(2+1=4; 2+3=5), Результат - ИСТИНА.

Функція НЕ - це логічна функція яка міняє на протилежне логічне значення свого аргументу.

Функція НЕ використовується в тих випадках, коли необхідно бути впевненим у тім, що значення не дорівнює деякій конкретній величині.

Синтаксис: НЕ(логічне_значення).

Логічне_значення - величина або вираз, які можуть приймати два значення: ИСТИНА або ЛОЖЬ. Приклад, =НЕ(1+1=2), Результат - ЛОЖЬ.

Функції ИСТИНА, ЛОЖЬ аргументів не мають і вони безпосередньо вводяться в комірки. Функція ИСТИНА це значення 1 а функція ЛОЖЬ – 0.
Статистичні функції

У цьому розділі розглянемо такі функції: СРЗНАЧ СРОТКЛ, ДИСП, МАКС, КВАДРОТКЛ.

Функція СРЗНАЧ повертає середнє (арифметичне) своїх аргументів.

Синтаксис: СРЗНАЧ(число1; число2; ...).

Число1, число2 ... - це від 1 до 30 аргументів, для яких обчислюється середнє.

  • Аргументи повинні бути або числами, або іменами, масивами або посиланнями, що містять числа.

  • Якщо аргумент, який є масивом або посиланням, містить тексти, логічні значення або порожні комірки, то такі значення ігноруються; проте комірки, які містять нульові значення, враховуються.

Наприклад:

=СРЗНАЧ(A2:A6; 5) – повертається середнє арифметичне приведених чисел в діапазоні комірок A2:A6 і числа 5.

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

Синтаксис: СРОТКЛ(число1; число2; ...).

Число1, число2... - це від 1 до 30 аргументів, для яких визначається середнє абсолютних відхилень. Можна використовувати масив або посилання на масив замість аргументів, що розділяються крапкою з комою.

  • Аргументи повинні бути числами або іменами, масивами або посиланнями, що містять числа.

  • Якщо аргумент, який є масивом або посиланням, містить тексти, логічні значення або порожні комірки, то такі значення ігноруються; проте, комірки, які містять нульові значення враховуються.

  • Рівняння для середнього відхилення наступне:

СРОТКЛ =

На результат СРОТКЛ впливають одиниці вимірювання вхідних даних.

Приклад: СРОТКЛ(A2:A8) – повертається середнє абсолютних значень відхилень чисел від середнього, які знаходяться в діапазоні A2:A8 .

Функція ДИСП оцінює дисперсію по вибірці.

Синтаксис: ДИСП(число1;число2; ...).

Число1, число2...- від 1 до 30 числових аргументів, відповідних вибірці з генеральної сукупності.

  • ДИСП припускає, що аргументи є тільки вибіркою з генеральної сукупності. Якщо дані представляють всю генеральну сукупність, обчислюйте дисперсію, використовуючи функцію ДИСПР.

  • Логічні значення, такі як ИСТИНА або ЛОЖЬ, а також текст ігноруються. Якщо вони не повинні ігноруватися, користуйтеся функцією робочого листа ДИСПА.

  • ДИСП використовує наступну формулу:

ДИСП =

де  - вибіркове середнє СРЗНАЧ(число1,число2.), а n  розмір вибірки.

Приклад: =ДИСП(A2:A11) - повертається дисперсія числових даних діапазону A2:A11.

Функція МАКС повертає найбільше значення з набору значень.

Синтаксис: МАКС(число1;число2; ...)

Число1, число2...-  від 1 до 30 чисел, серед яких потрібно знайти найбільше.

  • Можна задавати аргументи, які є числами, порожніми комірками, логічними значеннями або текстовими представленнями чисел. Аргументи, які є значеннями помилки або текстами, що не перетворюються в числа, викликають значення помилок.

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

  • Якщо аргументи не містять чисел, то функція МАКС повертає 0 (нуль).

Приклад: =МАКС(A2:A6; 30) – повертається найбільше число з діапазону A2:A6 та числа 30.

Функція КВАДРОТКЛ повертає суму квадратів відхилень точок даних від їх середнього.

Синтаксис: КВАДРОТКЛ(число1;число2;...).

Число1, число2 ...- це від 1 до 30 аргументів, квадрати відхилень яких підсумовуються.

Можна використовувати окремий масив або посилання на масив замість аргументів, що розділяються крапкою з комою.

  • Аргументи повинні бути числами або іменами, масивами або посиланнями, що містять числа.

  • Якщо аргумент, який є масивом або посиланням, містить текст, логічні значення або порожні комірки, то такі значення ігноруються; проте комірки, які містять нульові значення, враховуються.

  • Рівняння для суми квадратів відхилень має наступний вигляд:

КВАДРОТКЛ =

Приклад:

=КВАДРОТКЛ(A2:A8) повертає суму квадратів відхилень чисел діапазону A2:A8 від їх середнього значення.


Контрольні запитання



  1. Що таке формула, як вона водиться та які вона може мати компоненти?

  2. Які операції з формулами можна виконувати у табличному процесорі Excel?

  3. Що таке майстер функцій у табличному процесорі та як з ним працювати?

  4. Яким чином можна включити у формулу вбудовану функцію?

  5. У якому вікні вводяться параметри вбудованих функцій?

  6. Що таке константа та змінна у табличному процесорі Excel, які можуть використовуватися в якості параметрів функції?

  7. Проаналізуйте призначення та використання основних вбудованих функцій категорії «Ссылки и массивы».

  8. Проаналізуйте призначення та використання основних вбудованих функцій категорії «Текстовые функции».

  9. Проаналізуйте призначення та використання основних вбудованих функцій категорії «Дата время».

  10. Проаналізуйте призначення та використання основних вбудованих функцій категорії «Проверка свойств и значений».

  11. Проаналізуйте призначення та використання основних вбудованих функцій перевірки типу значень.

  12. Проаналізуйте призначення та використання основних вбудованих функцій категорії «Математические».

  13. Проаналізуйте призначення та використання основних логічних вбудованих функцій :ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ.

скачати

© Усі права захищені
написати до нас