Вкладені функції. Редагування функцій

Лекція 4

Тема: Табличні процесори та їх використання

Мета:Узагальнити знання з теми «Табличні процесори»; розвивати міжпредметні зв’язки, власний кругозір щодо Microsoft Excel; виховувати інтерес до майбутньої професії.

План

Математичні, логічні та статичні функції.

2. Функції дати і часу.

3. Вкладені функції. Редагування функцій.

4. Призначення, основні поняття та типи діаграм.

Технологія створення діаграм.

Редагування та форматування діаграм.

Література

1. Ярмуш О. В. Інформатика і комп’ютерна техніка: навч. посібник / О.В. Ярмуш, М. М. Редько – К.: Вища освіта, 2006. – 359с. – С. 224.

2. Інформатика: Комп’ютерна техніка. Комп’ютерні технології: підручник / під ред. В. А. Баженова. – К.: Каравела, 2003. – 464с.

3. Інформатика: Комп’ютерна техніка. Комп’ютерні технології: посіб. / за редакцією О. І. Пушкаря. – К.: ВЦ «Академія», 2001.– 696с.

4. Баженов В.А. Інформатика. Комп’ютерна техніка. Комп’ютерні технології: підручник / В. А. Баженов. – 2-ге видання. – К.: Каравела, 2007. – 640с.

5. Тхір І. Л. Посібник користувача ПК / І. Л. Тхір, В. П. Галушка, А.В. Юзків. – 2-ге видання. – Тернопіль: СМП «Астон», 2002. – 718с.

6. Бондаренко С. Excel 2003. Популярный самоучитель / С. Бондаренко, М. Бондаренко – СПб.:Питер, 2005. – 320с.

7. Кузьмин В. Microsoft Office Excel 2003. Учебный курс / В. Кузьмин – СПб.:Питер, 2004. – 493с.

8. Глушков С. В. Персональний комп’ютер / С. В. Глушков, О. С. Сурядний. – Харків: Фоліо, 2007. – 509с.

9. Дибкова Л. М. Інформатика і комп’ютерна техніка: навч. посіб / Л.М. Дибкова – 2-ге вид., перероб., доп. – К.: Академвидав, 2007. – 416с.

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

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

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

Розглянемо деякі із них.

1. СУММ – додає аргументи.

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

3. COS, SIN, TAN – тригонометричні функції cos, sin і tg.

4. ACOS, ATAN – зворотні тригонометричні функції arсcos, arсtg.

5. ГРАДУСЫ – перетворює радіани в градуси.

6. LN – натуральний логарифм числа.

7. ABS – модуль числа.

8. ПИ – повертає число Пі (π=3.14).

9. ЗНАК – повертає знак числа.

10. ПРОИЗВЕД – повертає добуток аргументів.

11. СТЕПЕНЬ – повертає результат піднесення до степеня.

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

13. ОСТАТ – повертає остачу від ділення.

14. СЛЧИС – повертає випадкове число в інтервалі від 0 до 1.

15. РИМСКОЕ – перетворює число в арабському записі до числа в римському як текст.

16. СУММЕСЛИ – повертає суму вмістимого комірок, яке задовольняє заданому критерію;

17. СУММКВ – повертає суму квадратів аргументів.

18. МОБР, МУММНОЖ, МОПРЕД – зворотна матриця, добуток та визначник матриці.

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

Окрему групу складають функції, призначені для роботи з матрицями. В їх застосуванні є особливості: аргументами таких функцій є діапазон комірок. При введенні функцій, аргументами яких виступають масиви (матриці) і які повертають як результат матрицю, необхідно перед введенням функції виділяти не одну комірку, куди буде розміщений результат, а діапазон. Завершити введення аргументів матричних функцій потрібно обов’язково натисканням комбінації клавіш Ctrl+Shift+Enter, а не просто кнопки OK. Часто на аргументи цих функцій накладаються обмеження, викликані математичним обґрунтуванням цих операцій (наприклад, фіксована кількість рядків та стовпців).

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

1. Виділити всі комірки діапазону, які будуть додаватися та натиснути кнопку ∑ (автосума) на панелі інструментів. У наступній комірці нижче або правіше від виділеного діапазону з’явиться результат підсумовування.

2. Зробити активною комірку, в яку буде розміщено суму та натиснути ∑. Excel автоматично запропонує діапазон підсумовування, який буде відображатися у формулі. За необхідності його можна замінити.

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

Наприклад, сума діапазону комірок рядка від А1 до А20

=СУММ(А1:А20).

У цьому прикладі знак «:» означає діапазон з вказівкою першого і останнього членів діапазону.

У цьому випадку функція СУММ використовується з одним аргументом – діапазоном комірок. Однак вона може мати більше одного аргументу (до 30), розділених «;». При цьому кожний аргумент може бути як числом, адресою комірки, так і діапазоном комірок. Також можна додавати і діапазони комірок, не розміщені поруч, тобто окремі. Для виділення таких діапазонів необхідно утримувати клавішу Ctrl і мишею виділяти діапазони.

Якщо, наприклад, потрібно перемножити два числа, то треба записати

=ПРОИЗВЕД(А1;В1).

За допомогою функції СУММЕСЛИ можна просумувати значення з діапазону комірок, але при цьому здійснюється додавання лише тих значень, які відповідають певному критерію.

Приклад 1. Необхідно визначити сумарну заробітну пла-ту працівників, які виконали план більше ніж на 100. Інформація про виконання плану знаходиться в стовпці F (з F2 по F10), інформація про заробітну плату – у стовпці E (E2 по E10).

Розв’язання

Для розв’язання даної задачі необхідно скористатися функцією СУМЕСЛИ:

Серед аргументів функції СУММЕСЛИ задаємо такі:

1. Діапазон: F2:F10 (діапазон, що буде порівнюватися з критерієм).

2. Критерій: «>100».

3. Діапазон додавання: E2:E10.

Функція СУМЕСЛИ буде мати такий запис:

=СУММЕСЛИ(F2:F10;">100";E2:E10).

Результат розрахунків:

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

Статистичні функції призначені для проведення статистичного аналізу. Крім того, їх можна використовувати для факторного та регресійного аналізу.

Спочатку розглянемо найуживаніші:

1. СРЗНАЧ – визначає середнє значення.

2. МИН, МАКС – визначає мінімальне та максимальне значення.

3. СЧЕТ – визначає кількість числових аргументів.

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

Приклад 2. Необхідно знайти максимальне значення в діапазоні від А2 до А7.

Розв’язання

Для розв’язання цієї задачі використовують функцію МАКС(аргументи), що повертає максимальне значення зі списку аргументів

=МАКС(А2:А7).

Розглянемо деякі інші функції.

1. КОРРЕЛ – визначає коефіцієнт кореляції між двома множинами даних.

2. СРГЕОМ – визначає середнє геометричне.

3. СРОТКЛ – повертає середнє абсолютних значень відхилень даних від середнього.

4. СРЗНАЧА – визначає середнє арифметичне аргументів, якими можуть бути як числа, так і текст, логічні значення.

5. СЧЕТЕСЛИ – підраховує кількість значення у переліку аргументів, які задовольняють деяку умову.

6. ДИСП – оцінює дисперсію з виборки.

Приклад 3. У таблиці наведені дані про працівників фірми (в стовпці D знаходиться інформація про посаду). Необхідно визначити кількість працівників на посаді бухгалтера.

Розв’язання

Для цього використовуємо функцію СЧЕТЕСЛИ.

У полі Диапазон необхідно заповнити діапазон «D2: D16», а у полі Критерийвказати «бухгалтер».

У результаті функція СЧЕТЕСЛИ матиме вигляд

=СЧЁТЕСЛИ(D2:D10;"бухгалтер").

Результат розрахунків буде мати вигляд:

Критерієм можуть бути довільні логічні обмеження, наприклад: >=100, <0. Звернімо увагу на те, що якщо в критерії повинно стояти обмеження, яке використовує адресу деякої комірки (наприклад, хочемо задати обмеження >А5), критерій повинен задаватися у такому вигляді: «>»&A5.

У критерії можна використовувати маски введення «*» замість довільної послідовності символів та знак «?» замість одного довільного символу. Наприклад, щоб визначити кількість працівників, прізвище яких починається з букви А, необхідно задати критерій «А*».

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

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

Ці функції набувають логічних значень «Істина» або «Хибно». Ця категорія містить всього шість функцій, але вона є дуже важливою і часто використовуваною.

До логічних функцій відносять такі функції: ЕСЛИ, И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ.

Логічна функція ЕСЛИ

Найбільш важливою є функція ЕСЛИ.

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

Ця функція записується таким чином:

ЕСЛИ(Лог_выражение; Значение_если_истина; Значение_если_ложь).

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

Приклад 4. Необхідно перевірити, чи виконано план робітниками фірми «Сатурн», та вивести значення виконання і не виконання плану. План записаний у стовпці D, а виконання роботи у стовпці С.

Розв’язання

В даній задачі застосуємо функцію ЕСЛИ. Ми будемо порівнювати значення стовпців D та С. Порівняємо значення стовпця С «виконано» зі стовпцем D «план». Якщо значення у стовпчику «виконано» більше, ніж значення у стовпчику «план», то план виконано.

Запис функції:

=ЕСЛИ(С3>D3;«так»;«ні»).

Якщо функція виконується, то одержуємо значення Так, а якщо не виконується – Ні. Результат виконання плану працівниками фірми «Сатурн»:

Приклад 5.Необхідно розрахувати функцію

Розв’язання

Для розв’язання даної задачі необхідно скористатися функцією ЕСЛИ. Серед аргументів функції ЕСЛИ є логічний вираз, де необхідно перевірити виконання умови. Тут необхідно перевірити одну з умов, що задані в прикладі. Умову вибираємо довільно ( або ). Перевіримо умову . Якщо ця умова виконується, ми потрапляємо в проміжок , де , а якщо ця умова не виконується, то ми потрапляємо у проміжок , де . Розрахунок даного виразу за допомогою електронної таблиці Excel:

Приклад 6. Необхідно розрахувати значення виразу

Розв’язання

Для розв’язання необхідно використати функцію ЕСЛИ. Тут, як і в попередньому прикладі, необхідно перевірити одну умову в логічному виразі. Наприклад, перевіримо умову . Якщо ця умова виконується, то вираз набуває значення . Якщо ж умова не виконується, то ми потрапляємо до проміжку , де вираз набуває значення .

Для розв’язання задачі в Excel необхідно помістити покажчик миші в комірку D2, набрати знак «=» (дорівнює) та за допомогою Майстра функцій викликати функцію ЕСЛИ.

Замість аргументу Лог_выражение необхідно записати умову . Оскільки значення Х в нас записано в комірку С2, то умова в Excel запишеться таким чином: C2<5.

Аргумент Значение_если_истина містить значення, коли умова виконується. У нашому випадку це значення . В Excel це буде мати такий вираз: ABS(C2)+1, де ABS(C2) – це функція, що розраховує модуль від числа, яке знаходиться в комірці С2. Щоб помістити дану функцію у функцію ЕСЛИ, необхідно активізувати список функцій, який розміщений у лівій частині панелі формул, та вибрати потрібну функцію. Якщо такої функції немає серед перелічених, то необхідно вибрати команду Другие функции, після чого з’явиться вікно Мастера функций. У цьому вікні необхідно обрати категорію Математические та в цій категорії обрати функцію ABS(аргумент).

Щоб продовжити створення складної функції та забезпечити її коригування, потрібно активізувати кнопку f_X на панелі формул.

Аргумент Значение_если_ложь містить розрахунок другого рівняння, якщо умова логічного виразу функції ЕСЛИ не виконується. Порядок розрахунку аналогічний.

Після створення функції її копіюють для всіх значень Y за допомогою знака «+» у нижньому правому куті комірки. Після цього функція ЕСЛИ набуває вигляду:

Логічна функція И

Функція И повертає значення Істина, якщо всі аргументи мають значення Істина.

Синтаксис функції буде таким:

=И(логічне_значення_1;логічне_значення_2;..).

Її використовують для об’єднання двох і більше умов. Наприклад, для перевірки, чи належить Х з комірки А2 до діапазону від 0 до 5, використовують функцію И з такими аргументами: И(А2>0; А2<5). Результат розрахунків в Excel:

Логічна функція ЛОЖЬ

Функція ЛОЖповертає логічне значення ЛОЖ

Синтаксис функції буде таким: =ЛОЖ().

Логічна функція НЕ

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

Якщо є значення «-1», при використанні даної функції значення зміниться на протилежне.

Логічна функція ИЛИ

Функція ИЛИ – повертає логічне значення істина, якщо коли хоч один з аргументів має значення істина.

Щоб перевірити, чи належить Х з комірки А2 до діапазону менше нуля або дорівнює 10, функція має вигляд =ИЛИ(А2>1;А2<0) і повертає значення ИСТИНА

Функції дати і часу

Для роботи зі значенням типу дата та час в Excel використовують функції категорії ДАТА і ВРЕМЯ.

Розглянемо деякі із них.

Функція ДАТА

Функція ДАТА повертає значення дати. Загальний вигляд функції:

ДАТА(рік;місяць;день).

Функція ДАТА(2000;2;1) залежно від установленого формату дати повертає значення 01.02.00.

Функція ДЕНЬ

Функція ДЕНЬ повертає день дати в числовому форматі. Наприклад, у комірці F2 вміщена дата 28.10.2003, тоді значення функції ДЕНЬ(F2) дорівнює 28.

Функція ДЕНЬНЕД

Функція ДЕНЬНЕД визначає день тижня, на який припадає дата, визначена як аргумент. Синтаксис функції:

ДЕНЬНЕД(дата;тип). При цьому аргумент тип визначає порядок розрахунку і може мати значення:

1 (за замовчуванням) – число від 1 (неділя) до 7;

2 – число від 1 (понеділок) до 7;

3 – число від 0 (неділя) до 6.

Функція =ДЕНЬНЕД(28.10.2003) повертає значення 3, а функція =ДЕНЬНЕД(«23.10.2003»;2) – значення 2.

Функція СЕГОДНЯ

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

Функція ЧАС

Функція ЧАС повертає значення часу в налаштованому часовому форматі.

Синтаксис запису: ЧАС(години;хвилини;секунди).

Функція ТДАТ

Функція ТДАТ повертає поточну дату та час. Синтаксис функції: ТДАТ().

Функція МЕСЯЦ

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

Функція ДНЕЙ360

Функція ДНЕЙ360 визначає кількість днів між двома датами, яку вона вираховує на основі 360-денного року.

Для більш детального ознайомлення з цими та іншими функціями необхідно скористатися програмою Excel.

Вкладені функції. Редагування функцій

Вкладені функції – це функції, аргументами яких можуть бути інші функції.

Розглянемо деякі приклади.

Приклад 7. Необхідно визначити поточний рік.

Розв’язання

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

Приклад 8.Необхідно визначити, до якої цінової групи належить товар, за такими правилом:

1) якщо ціна товару < 100 грн. – дешеві товари;

2) якщо 100 <1000 грн. – середній товар;

3) якщо ціна товару > 1000 грн. – дорогі товари.

Розв’язання

Нехай у комірці С записана ціна товару. Для розв’язку скористаємося функцією ЕСЛИ()

Для розв’язування даної задачі у Excel необхідно вибрати функцію ЕСЛИ(). У вікні Аргументы функциизадати такі значення:

1) Лог_выражение: С2<100;

2) Значение_если_истина: дешеві;

3) Значение_если_ложь.

Обрана функція перевіряє значення комірки С2 та видає попередній результат. Для продовження розміщуємо курсор миші Значение_если_ложь та знову викликаємо функцію ЕСЛИ(). Для цього у вікні Excel в адресному вікні знаходимо спадний список і знову вибираємо ЕСЛИ(), де вводимо:

1) Лог_выражение: С2<=100;

2) Значение_если_истина: середні;

3) Значение_если_ложь: дорогі.

У результаті одержимо вкладену функцію вигляду

=ЕСЛИ(С2<100; «дешеві»; ЕСЛИ(С2<=1000; «середні»; «дорогі»)).

Результат розв’язання даного прикладу у Excel:

Приклад 9. Обчислити значення виразу

Розв’язання

Для розв’язання даного прикладу необхідно скористатися функцією ЕСЛИ. Спочатку перевіряємо умову . Якщо умова виконується, то значення виразу буде таким: . Далі необхідно перевірити одну з двох умов, наприклад . Якщо ця умова виконується, то . А якщо умова не виконується, то ми потрапляємо у проміжок . Тепер знову необхідно перевірити умову . Якщо ця умова виконується, то . Якщо ця умова не виконується, то ми потрапляємо у проміжок , . На цьому проміжку функція не визначена.

Розрахунок значення в Excel:

Повідомлення про помилки

Якщо при обчисленні формули сталася помилка, то в комірку виводиться повідомлення про помилку, яке починається із символу #. Ехсеl виводить такі повідомлення про помилки, що подані у таблиці:

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

Ім’я_функції(аргумент).