IV.5.1. Формат введення даних типу Дата

ПИ()

ABS(число)

КОРЕНЬ(число)

ФАКТР(число)

Число - це ненегативне число, факторіал якого обчислюється. Якщо число не ціле, то проводиться відкидання дробової частини.

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

МОПРЕД(масив)

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

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

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

Число - це число, для якого обчислюється квадратний корінь. Якщо число негативно, то функція КОРІНЬ повертає значення помилки #ЧИСЛО!.

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

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

ПИ - повертає число 3,14159265358979, математичну константу p з точністю до 15 цифр.

Приклад: ПИ()/2 дорівнює 1,57079...

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

Функція	Опис
COS, SIN, TAN	Тригонометричні функції
ACOS, ASIN, ATAN, ATAN2	Зворотні тригонометричні функції
COSH, SINH, TANH	Гіперболічні функції
ACOSH, ASINH, ATANH	Зворотні гіперболічні функції
LN, LOG, LOG10	Натуральний логарифм, логарифми по основі 2 і 10
EXP	Експонента
НЕЧЕТ(EVEN), OKPУГЛ(ROUND), ОКРУГЛВВЕРХ (ROUNDUP), ОКРУГЛВНИЗ (ROUNDDOWN), ЧETH(ODD)	Функції округлення
ABS	Модуль (абсолютне значення)'числа
ГРАДУСЫ (DEGREES)	Перетворення радіан в градуси
РАДИАНЫ (RADIANS)	Перетворення градусів в радіани
ЦЕЛОЕ (INT)	Ціла частина числа
ОСТАТ (MOD)	Залишок від розподілу
ПИ(Р1)	Число пі
СТЕПЕНЬ (POWER)	Зведення в степінь
ПРОИЗВ (PRODUCT)	Добуток ряду чисел
СЛЧИС (RAND)	Повертає випадкове число
РИМСКОЕ (ROMAN)	Перетворення арабського числа в римське
КОРЕНЬ (SQRT)	Квадратний корінь
СУММ (SUM)	Сума ряду чисел
СУММ КВ (SUMSQ)	Сума квадратів ряду чисел
ОТБР (TRUNC)	Відкидає дробову частину

Логічні функції

- Логічні функції призначені для перевірки виконання умови або для перевірки декількох умов.

Функція	Опис
И (AND)	Логічне множення
ЛОЖЬ (FALSE)	Повертає хибне значення
ЕСЛИ (IF)	Повертає одне значення, якщо умова істинна, і друге, якщо умова хибна
НЕ (NOT)	Логічне заперечення
ИЛИ (OR)	Логічне складання
ИСТИНА (TRUE)	Повертає істинне значення

Функція ухвалення рішень (ЕСЛИ)

Функція ЕСЛИ використовується при перевірці умов для значень і формул.

Синтаксис функції:

ЕСЛИ ( лог_вираз; значення_якщо_істина; значення_якщо_хибність )

Аргументи функції:

1. лог_вираз– це будь-який вираз, що набуває значення ІСТИНА або ХИБНІСТЬ. Наприклад, А10 = 100. Якщо значення в осередку А10 дорівнює 100, то вираз прийме значення ІСТИНА. Інакше – ХИБНІСТЬ.

2. значення_якщо_істина - це значення, яке повертається, якщо лог_выражение рівне ІСТИНА.

3. значення_якщо_хибність — це значення, яке повертається, якщо лог_выраз є ХИБНИМ.

Тобто функція ЕСЛИ виконує перевірку умови, представленої у вигляді аргументу лог_вираз. Якщо ця умова виконується (значення лог_вираз набуває значення ІСТИНА), тоді функція повертає значення аргументу значення_якщо_істина. Інакше (якщо значення лог_вираз набуває значення ХИБНІСТЬ) функція повертає значення аргументу значення_якщо_хибність.

Приклад:

ЕСЛИ (А5 > 35; А5+1; А5-3)

Якщо осередок А5 містить число 60, тоді функція ЕСЛИ поверне число 61, тобто значення виразу А5+1.

А якщо в осередок А5 ввести число 10, тоді функція ЕСЛИ поверне 7, обчислене за формулою А5-3.

Функції ЕСЛИ можуть бути вкладеними один в одну як значення аргументів значение_если_истина і значение_если_ложь для конструювання складніших перевірок.

Приклади:

1. На листі бюджетного звіту осередок A10 містить формулу для розрахунку поточного бюджеті. Якщо результат обчислень за формулою в осередку A10 менше або рівний 100, то приведена нижче функція відображає "В межах бюджету". Інакше функція відображає "Перевищення бюджету".

ЕСЛИ (A10<=100;"В межах бюджету";"Перевищення бюджету")

2. У наступному прикладі, якщо значення осередку A10 — 100, то лог_вираз має значення ІСТИНА і обчислюється сума для осередків B5:B15. Інакше лог_вираз має значення ХИБНІСТЬ і повертається порожній текст (""), що очищає осередок, який містить функцію ЕСЛИ.

ЕСЛИ(A10=100;СУММ(B5:B15);"")

Застосування операторів у формулах

Операторами позначаються операції, які слід виконати над операндами формули. У Microsoft Excel включено чотири види операторів: арифметичні, текстові, оператори порівняння і оператори посилань.

Арифметичні оператори. Служать для виконання арифметичних операцій, таких як складання, віднімання, множення. Операції виконуються над числами. Використовуються наступні арифметичні оператори.

Аріфметічеський оператор	Значення	Приклад
+ (знак плюс)	Складання	3+3
– (знак мінус)	Вичитаніє унарний мінус	3–1 –1
* (зірочка)	Множення	3*3
/ (коса межа)	Ділення	3/3
% (знак відсотка)	Відсоток	20%
^ (кришка)	Піднесення до ступеня	3^2 (аналогічне 3*3)

Оператори порівняння. Використовуються для порівняння двох значень. Результатом порівняння є логічне значення: або ІСТИНА, або ХИБНІСТЬ.

Оператор порівняння	Значення	Приклад
= (знак рівності)	Рівно	A1=B1
> (знак більший)	Більше	A1>B1
< (знак менший)	Менше	A1<B1
>= (знак більше і знак рівності)	Більше або рівно	A1>=B1
<= (знак менше і знак рівності)	Менше або рівно	A1<=B1
<> (знак «не рівно»	Не рівно	A1<>B1

Текстовий оператор конкатенації. Амперсанд (&) використовується для об'єднання декількох текстових рядків в один рядок.

Текстовий оператор	Значення	Приклад
& (амперсанд)	Об'єднання послідовностей символів в одну послідовність.	Вираз "Північний " & " вітер" еквівалентно рядку "Північний вітер".

Оператор посилання. Для опису посилань на діапазони осередків використовуються наступні оператори.

Оператор посилання	Значення	Приклад
: (двокрапка)	Оператор діапазону використовується для посилання на всі осередки між крайніми осередками діапазону, включаючи ці осередки.	B5:B15
, (кома)	Оператора об'єднання об'єднує декілька посилань в одне посилання.	СУМ(B5:B15,D5:D15)

Логічні функції И, ИЛИ, НЕ

1. Синтаксис функції И

И( логічне_значення1; логічне_значення2;...)

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

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

· Аргументи мають бути логічними значеннями, масивами або посиланнями, які містять логічні значення.

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

· Якщо вказаний інтервал не містить логічних значень, то И повертає значення помилки #ЗНАЧ!.

Приклади:

1. И(ІСТИНА; ІСТИНА) дорівнює ІСТИНА

2. И(ІСТИНА; ХИБНІСТЬ) дорівнює ХИБНІСТЬ

3. И(2+2=4; 2+3=5) дорівнює ІСТИНА

4. Якщо інтервал B1:B3 містить значення ІСТИНА, ХИБНІСТЬ, і ІСТИНА, то:

5. И(B1:B3) дорівнює ХИБНІСТЬ

6. Якщо осередок B4 містить число між 1 і 100, то:

И(1<B4; B4<100) дорівнює ІСТИНА

2. Синтаксис функції ИЛИ

ИЛИ( логічне_значення1;логічне_значення2;...)

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

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

Приклади:

1. ИЛИ(ІСТИНА) дорівнює ІСТИНА

2. ИЛИ(1+1=1;2+2=5) дорівнює ХИБНІСТЬ

3. Синтаксис логічної функції НЕ

НЕ( логічне_значення )

Логічне_значення - це значення або вираз, який при обчисленні дає ІСТИНА або ХИБНІСТЬ. Якщо логічне_значення має значення ХИБНЕ, то функція НЕ повертає значення ІСТИНА; якщо логическое_значение має значення ІСТИНА, то функція НЕ повертає значення ХИБНЕ.

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

Приклади:

1. НЕ(ХИБНЕ) дорівнює ІСТИНА

2. НЕ(1+1=2) дорівнює ХИБНЕ

Повертає логічне значення ІСТИНА.

Приклад на використання функції ЕСЛИ у поєднанні з логічною функцією И.

Хай фірма вирішила виплатити премію співробітникам згідно наступній формулі:

Задачу розподілу премії можна вирішити в Excel у вигляді розрахункової таблиці, фрагмент якої показаний на Рис. 5. Перший стовпець містить стаж співробітників. У осередок В2 вноситься формулу (див. Рис. 5 рядок формул) з використанням функцій ЯКЩО (вкладених одна в одну). Складена формула повністю відповідає формулі розрахунку премії.

Рис. 5.

Потім розмножимо формулу в нижче лежачі осередки (В3:в7), використовуючи маркер заповнення. Результатом буде таблиця на Рис. 6.

Рис. 6.

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

1. Який синтаксис запису функції?

2. За допомогою чого можна проглянути і вибрати необхідну вбудовану функції?

3. З допомогою, якій функції можна найшвидше обчислити суму даних в осередках?

4. Коли використовується функція ухвалення рішення ЕСЛИ?

5. Синтаксис написання функції ЕСЛИ?

6. Як працює функція ЕСЛИ?

7. Які логічні функції ви знаєте? Результат їх роботи?

Лекція № 10.

IV.5 Зберігання дат і часу доби в Microsoft Excel.

План лекції:

1. Дані типу Дата/Время

2. Формат введення даних типу Дата

3. Формат відображення даних типу Дата

4. Арифметичних операцій над даними типу Дата.

5. Функції для роботи з даними типу Дата.

Дані типу Дата/Час. Microsoft Excel зберігає дату у вигляді послідовних чисел, а час у вигляді десяткової частини цього значення (час є частиною дати). Дати і значення часу представляються числами; тому їх можна складати і віднімати, а також використовувати в інших обчисленнях. Наприклад, щоб визначити число днів між двома датами, можна відняти одну дату з іншої. При використанні основного формату для осередків, дату, що містять, і час, можна відобразити дату у вигляді числа або час у вигляді дробової частини числа з десятковою крапкою.

Excel підтримує дві системи дат: 1900 і 1904. За умовчанням, Microsoft Excel для Windows використовує систему дат 1900. Щоб скористатися системою дат 1904, виберіть команду Параметри в меню Сервіс, а потім на вкладці Обчислення встановите прапорець Система дат 1904.

У наступній таблиці представлені перша і остання дати для кожної системи, а також відповідні ним значення.

Система дат	Перша дата	Остання дата
	1 січня 1900 г. (значення 1)	31 грудня 9999 г. (значення 2958465)
	2 січня 1904 г. (значення 1)	31 грудня 9999 г. (значення 2957003)

Дати — це числа В Microsoft Excel дати інтерпретуються як числа. Зовнішнє представлення дати на листі залежить від числового формату, призначеного осередку. При введенні значень дати відбувається їх автоматичне розпізнавання, і загальний формат осередку замінюється на вбудований формат дати. За умовчанням значення дати вирівнюються в осередку по правому краю. Якщо не відбувається автоматичного розпізнавання формату дати, то введені значення інтерпретуються як текст, який вирівнюється в осередку по лівому краю.

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

Синтаксис формату введення даних типу Дата з роздільником крапка (.):

ДД.ММ.РР або ДД.ММ.РРРР

Примітки:

Дві цифри року. Якщо при введенні дати вказано тільки дві останні цифри року, Excel додасть перші дві по наступних правилах:

· Якщо число лежить в інтервалі від 00 до 29, то воно інтерпретується як рік з 2000 по 2029. Наприклад, якщо в осередок введена дата 28.5.19, Microsoft Excel розпізнає її як 28 травня 2019 р.

· Якщо число лежить в інтервалі від 30 до 99, то воно інтерпретується як рік з 1930 по 1999. Наприклад, якщо в осередок введена дата 28.5.98, Excel розпізнає її як 28 травня 1998 р.

Чотири цифри року Для забезпечення правильної інтерпретації значень року вводите чотири цифри року (2001 замість 01). При введенні чотирьох цифр року Excel не визначатиме сторіччя по описаних вище правилах.

Приклад:

Потрібно ввести дату 12 вересня 2005 року. Формат введення: 12/09/05 або 12.09.05

IV.5.2. Формат відображення даних типу Дата

Змінити відображення дати в осередку можна за допомогою команди Формат ® Ячейки, вибравши потім в діалоговому вікні числовий формат Дата. У вікні Тип будуть надані різні формати відображення, серед яких можна вибрати найбільш відповідний (Рис. 7.).

Рис. 7.

IV.5.3. Функції для роботи з даними типу Дата

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

Функції роботи з датою і часом

Функція	Опис
ДАТА(DATE)	Повертає дату у форматі Excel
ДАТАЗНАЧ(DATEVALUE)	Перетворить дату з текстового формату в код Excel
ДЕНЬ(DAY)	День місяця заданої дати
ДНЕЙ360 (DAYS360)	Обчислює кількість днів між двома датами на основі З60-денного року
ЧАС (HOUR)	Годину часу, заданого аргументом
МИНУТЫ (MINUTE)	Хвилина часу, заданого аргументом
МЕСЯЦ (MONTH)	Номер місяця заданої дати
ДАТА (NOW)	Поточна дата і час в числовому форматі
СЕКУНДЫ (SECOND)	Секунда часу, заданого аргументом
ВРЕМЯ (TIME)	Перетворить час дня в дробове число від 0 до 1
ВРЕМЗНАЧ (TIMEVALUE)	Перетворить час з текстового формату в дату в числовому форматі
ДЕНЬ (TODAY)	Поточна дата в числовому форматі
ДЕНЬНЕД (WEEKDAY)	Номер дня тижня заданої дати
ГОД(YEAR)	Рік заданої дати

Функція ГОД

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

Синтаксис

ГОД( дата_в_числовому_форматі )

Дата_в_числовому_форматі — це дата, рік якої необхідно знайти. Дати можуть вводитися як текстові рядки в подвійних лапках (наприклад "30.1.1998"), як числа (наприклад 35825 представляє 30 січня 1998 р. при використанні системи дат 1900) або як результат інших формул або функцій (наприклад ДАТАЗНАЧ("30.1.2001")).