Функції програми ОО Саlс

Стандартних функцій є декілька категорій:

· математичні – sin, cos, exp, ln, abs, atan, sqrt тощо, а також функції для роботи з матрицями;

· статистичні —- середнє значення (AVERAGE), МІН (MIN), МАКС (МАХ) тощо;

· логічні;

· фінансові;

· для роботи з датами, текстами та ін.

Функції можуть бути визначені над числами, адресами клітинок, адресами (назвами) діапазонів та їхніми списками. Елементи списку записують через роздільник, який визначається операційною системою: кому або крапку в комою, наприклад: =СУМА(А1; В6.С8; 20).

Рис. 32. Зразок таблиці для задачі 5

 

Оскільки суми обчислюють найчастіше, у рядку формул е кнопка Сума Нею користуються так: виокремлюють клітинку під стовпцем чи праворуч від рядка з даними і клацають на кнопці Сума – отримують потрібну суму (числових даних з відповідного стовпця чи рядка).

Дії над елементами ЕТ (виокремленими клітинками, стовпцями, рядками, діапазонами, усією таблицею) виконують командами контекстного чи головного меню або за допомогою кнопок пеналів.

Наприклад, у разі потреби в таблицю вставляють порожні рядки чи стовпці або вилучають їх командами: Редагувати => Вставити або Вилучити.

У виокремлену клітинку можна вставити примітку, яка пояснює її призначення, командою Вставити => Примітка, а вилучити командою Редагувати => Вилучити примітку.

Списки користувачів.

Розглянемо ще один спосіб швидкого введення текстових даних у таблицю. Він полягає у використанні списків користувача. Списки можуть містити назви товарів, міст, фірм, прізвища тощо. Список користувач спочатку створює командами Сервіс => Параметри s> Електронні таблиці => Сортування списків. Далі треба клацнути на кнопці Новий та ввести елементи списку через кому або натискаючи на клавішу вводу => Додати ОК. Список використовують так: перший елемент списку вводять у деяку клітинку, перетягують и маркер копіювання – відбувається автозаповнення таблиці елементами списку.

Впорядкування.

ЕТ можна використовувати як базу даних. Розглянемо типові дії, які можна виконувати з даними:

· впорядковувати рядки за зростанням чи спаданням значень у деякому стовпці;

· шукати дані за деяким критерієм.

Стовпець з даними тут називають полем.

Спочатку вибирають частину таблиці з даними і назвами полів або всю таблицю (без назви таблиці і рядків з підсумками). Сортування виконують командою Дані => Сортувати, отримують список назв полів, де вибирають потрібну назву, наприклад Місто, і задають порядок сортування: за зростанням чи спаданням – отримують таблицю, де рядки будуть впорядковані в алфавітному чи зворотному порядку назв міст.

Пошук даних.

Пошук даних називають інакше фільтруванням даних. Спочатку вибирають таблицю разом з рядком, що містить назви стовпців, і виконують команду Дані => Фільтр => Автофільтр. Клітинки з назвами стовпців стають списками з кнопками розгортання. Розгортають потрібний список, наприклад Січень, вибирають у списку потрібне значення або пункт Стандарт. Якщо вибирають пункт Стандарт, то відкриється вікно конструктора умов, у якому є зручні засоби для формулювання критеріїв. Наприклад стовпець Січень: > 500000 і < 2000000, натискають на Гаразд і на екрані отримують результати пошуку – рядки таблиці з містами, де показник діяльності фірми у січні задовольняв певному критерію. Щоб відновити на екрані всю таблицю, виконують команду Дані Фільтр => Зняти фільтр. Викликати конструктор побудови критеріїв можна і за допомогою команд Дані => Фільтр => Стандартний фільтр.

Якщо треба побудувати складний критерій на базі назв декількох стовпців, то використовують команду Дані => Фільтр => Розширений фільтр. Тут спочатку в окремі клітинки (поряд із таблицею) потрібно ввести назви полів, по яких здійснюється пошук, а під ними – критерії пошуку. Наприклад, у клітинку F1 записуємо Січень, а у клітинку F2 – >2 000 000.

Підсумки.

Підсумки підводять з метою визначення ліпших, гірших, сумарних, середніх показників діяльності фірми в деяких країнах, містах, підрозділах тощо. Для цього спочатку рядки в таблиці сортують з метою групування (розташування поряд) даних, що стосуються кожної країни, міста чи підрозділу. Для отримання підсумків до впорядкованої таблиці застосовують команду Дані => Підсумки, де задають: 1) назву поля, що містить об'єкти, для яких створюють підсумки, наприклад Країна; 2) операцію підсумовування і 3) назву поля, що містить дані, які підлягають підсумовуванню (наприклад, Всього або/і Березень). Операції підсумовування є різні: сума, максимум, мінімум, середнє значення, відхилення від норми тощо.

Хід роботи № 1

1. Запустіть програму ЕТ, відкрийте нову книжку і створіть список користувача з назвами міст.

2. Уведіть дані для розв'язування задачі 3. Уведіть свої дані для п'яти міст аналогічно до таких (рис. 31):

Адреси      Дані

А1    DIGITAL в Україні (Введіть назву своєї фірми)

А2    Обсяги продажу у грн.

A3    Місто

В3    Січень

С3    Лютий

D3    Березень

Е3     Всього

А4    Київ (Створіть список користувача для назв міст)

В4    2250000 (Введіть такі або подібні числові дані)

С4    2720000

D4    3200000

А5    Львів

В5    1150500

С5    1890000

D5    1600000

... введіть дані самостійно ще для трьох міст

А10 Всього

А12 Максимум

А13 Мінімум

Числа у стовпець Е і рядки 10 – 13 не вводити!

3. Уведіть формули розв'язування задачі 3. Обчисліть значення клітинки Всього для філії з першого міста (Києва). У клітинці Е4 обчисліть суму чисел рядка 4. Виберіть клітинку Е4 і натисніть на кнопки Сума і вводу – отримаєте формулу = CYMA(B4:D4).

4. Обчисліть в В10 значення продажу Всього за січень. Виберіть клітинку В10 і натисніть на кнопки Сума і вводу.

5. Обчисліть в значення продажу Всього для решта філіалів. Скопіюйте формулу з клітинки Е4 вниз у діапазон Е5:Е10. Клацніть мишею в Е4 і перетягніть маркер вниз.

6. Обчисліть значення усіма філіалами за решту місяців. Скопіюйте формулу з В10 праворуч у діапазон C10:D10.

7. У клітинках В12:Е12 визначте максимальні значення у стовпцях даних. Уведіть формулу = МАКС(В4:В8) у клітинку В12 і скопіюйте її праворуч у діапазон С12:Е12.

8. Визначте мінімальні значення продажу за січень. Виберіть клітинку В13 і натисніть на кнопку Автопілот: функції (поряд із кнопкою Сума), у діалоговому вікні у розділі Статистичні двічі клацніть на назві функції МІН та у полі число 1: уведіть діапазон В4:В8 і натисніть на Гаразд.

9. Скопіюйте формулу з клітинки ВІЗ в діапазон С13:Е13. Який загальний обсяг продажу за три місяці?

10. Задайте формат чисел Числовий без десяткових знаків після крапки і з роздільником груп трьох розрядів. Виберіть усі числові дані в таблиці: Формат => Клітинки => Число => Числовий => увімкніть режим розмежовувати групи розрядів і задайте кількість десяткових цифр після коми (крапки): 0 => Гаразд.

11. Відцентруйте заголовки у перших двох рядках відносно стовпців А – Е.

12. Скопіюйте таблицю в буфер обміну і вставте її на сторінку 2.

13. На сторінці 2, використовуючи стару таблицю, побудуйте нову таблицю "Прогноз обсягів продажу на два місяці, грн". Доповніть таблицю стовпцями з назвами Квітень, Травень і Всього2. Дані для квітня і травня придумайте і введіть довільні. Який обсяг продажу планує фірма у квітні і травні (окремо і разом)?

14. Заховайте і покажіть стовпець Е. Виберіть стовпець Е і застосуйте команду Формат Стовпець Заховати. Як показати захований стовпець? Щоб застосувати команду показати, треба спочатку виокремити два стовпці, між якими є захований.

15. Очистіть рядки 12 і 13.

16. Доповніть таблицю трьома рядками з новими містами і відповідними даними. Перевірте правильність формул у стовпці і рядку Всього. Відредагуйте формули, якщо треба. Який сумарний прогноз продажу в гривнях тепер (введіть потрібні формули)?

17. Посортуйте рядки таблиці в алфавітному порядку назв міст.

18. Посортуйте філіали (рядки) за спаданням обсягу продажу в першому кварталі. Занотуйте у звіт, який філіал є на першому місці.

19. Застосуйте до таблиці автофільтр, щоб вивести рядки з назвами філіалів, які в лютому мали обсяги продажу понад 1 500 000.

20. Виведіть рядки з назвами філіалів, які в березні мали обсяги продажу більші, ніж 1 200 000, і менші, ніж 1 600 000.

21. Які філіали мали обсяги продажу в січні більші, ніж 2 000 000, а в лютому – більші, ніж 1 700 000? Команду Автофільтр застосуйте двічі. Перенесіть утворену таблицю під основну.

22. Використовуючи розширений фільтр, виведіть назви філіалів, які в січні мали обсяги продажу більші, ніж 1 500 000, а в березні – менші, ніж 2 000 000. Введіть у клітинку F1 – Січень, в F2 – > 1500000, в G1 – Березень, а в G2 – < 2000000. Далі виокремтеділіть таблицю і виконайте команди Дані => Фільтр => Розширений фільтр. У діалоговому вікні введіть діапазон F1:G2. Якщо шукані дані потрібно розмістити під основною таблицею, то клацніть на кнопці Більше (More) та введіть адресу першої клітинки області, куди необхідно скопіювати таблицю.

23. Скопіюйте основну таблицю на наступну сторінку і відформатуйте її якнайкраще. Числа відобразіть у грошовому форматі. Для цього виберіть усі числові дані та задайте потрібний формат. Доповніть таблицю стовпцем з номерами телефонів фірм. Номери телефонів введіть як семизначні числа і задайте їм формат користувача ##0-00-00. Доповніть таблицю стовпцем з датами подання філіалами даних і задайте датам формат зі списку форматів дат або формат користувача ДД.ММ.ПТГ (DD.MM.YYYY).

24. Збережіть книжку на диску з назвою Прізвище2.

25. Закінчіть роботу.

Хід роботи № /2

1. Запустіть програму ЕТ і відкрийте книжку з розв'язком задачі про діяльність фірми в Україні або створіть нову книжку.

2. Розв'яжіть задачу 4 "Діяльність фірми в Європі". Рекомендується використати фрагменти таблиці з попередньої роботи, але можна ввести всі дані спочатку. Потрібно скласти фінансовий звіт фірми за три місяці, яка має філіали у 16-20 містах у чотирьох країнах Європи. Дані про філіали введіть у довільному порядку. Поля (стовпці) таблиці такі: Країна, Місто, Січень, Лютий, Березень, Всього. Введіть формулу в поле Всього для обчислення суми обсягів продажу у філіалах за три місяці.

3. Упорядкуйте дані по полю Країна.

4. Підведіть підсумки діяльності філіалів у кожній країні з таким критерієм підсумовування; сума обсягів продажу за три місяці. Виокремте таблицю з назвами полів і застосуйте до неї команду Дані => Підсумки. У вікні, що відкриється, заповніть три поля: 1) при кожній зміні – Країна; 2) додати підсумки по полю – Всього; 3) операція —- сума. Натисніть на кнопку Гаразд.

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

6. Заховайте всі проміжні результати, тобто залишіть на екрані лише рядки з підсумками. Клацніть на всіх кнопках зі знаком мінус або один раз на кнопці з номером рівня підсумків, яка є вверху. Поекспериментуйте з кнопками Показати деталі (+) і Заховати деталі (-). Заховайте деталі.

7. Уведіть вхідні дані розв'язування задачі 5 так (див. рис. 32): Адреси        Дані

А1    Табулювання функції та обчислення площі під кривою

А2    <тут зазначте свою функцію, межі, крок>

А5    х

В5    у

С5    Площа

8. Задайте режим "Автоматично шукати назви стовців і рядків"

Сервіс => Параметри => Електронна таблиця => Обчислення задайте потрібний режим (Automatically find column and row labels). Якщо задано цей режим, то програма автоматично надаватиме діапазонам-стовпцям назви полів-стовпців. Ці назви можна використовувати у формулах. Якщо діапазону треба дати іншу назву, то див. пункт 9.

9. Введіть у клітинку D4 значення кроку і дайте клітинці назву h. Введіть число і виберіть цю клітинку. Застосуйте команду Вставити => Назва => Присвоїти => введіть h => Додати => Гаразд.

10. Уведіть формули розв'язування задачі 6 так (див. рис. 32):

Адреси	Формули	Примітки
А6	0	Значення лівої межі задайте своє
В6	= SIN(x^2) + 1	Формула обчислення значення функції sinx2+l, а ви введіть свою формулу
С6	= h * у	Обчислено площу одного лівого прямокутника
А7	= А6 + h

11. Скопіюйте формули з клітинок А7, В6, С7 вниз до кінця робочої таблиці – отримаєте площі всіх лівих прямокутників у стовпці С. У стовпці С вилучіть останнє значення.

12. Щоб отримати площу під всією кривою, обчисліть суму площ усіх лівих прямокутників. Клацніть під стовпцем С і застосуйте команду-кнопку Сума. Яке значення площі отримано?

13. У клітинку зі значенням площі вставте таку примітку: Цей результат отримав <Ваше прізвище>.

14. Обчисліть середнє арифметичне всіх значень функції.

15. Назвіть робочу сторінку Площа.

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

17. Сформатуйте таблицю, щоб вона мала якнайкращий вигляд.

18. Розв'яжіть таку задачу на новій сторінці.

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

19. Розв'яжіть наступну задачу на новій сторінці.

Задача. Побудуйте таблицю мір для перших десяти значень унцій, якщо 1 унція = 28.353495 г – 142 карати.

20. Розв'яжіть таку задачу на новій сторінці.

Задача. Обчисліть перших двадцять значень числової послідовності a_i=sin2i.

21. Збережіть книжку на диску в особистій папці.

22. Закінчіть роботу.

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

1. Яке призначення формул в ЕТ?

2. Яке значення функції МІН(5; 2; 13)?

3. Що таке копіювання формул?

4. Яке значення функції СУМА(5; 2; 13)?

5. Які є категорії стандартних функцій?

6. Як клітинці дати назву?

7. Що таке діапазон клітинок? Наведіть приклади діапазонів.

8. Як скопіювати формулу з деякої клітинки в діапазон-стовпець?

9. Як виокремити діапазон клітинок?

10. Яке значення функції М»Н(15; 20; 5)?

11. Опишіть пріоритети виконання операцій у виразах.

12. Як вставити рядок у таблицю?

13. Яке значення функції СУМА(12; 20)?

14. Як працює команда Підсумки даних?

15. Як скопіювати формулу з деякої клітинки в рядок?

16. Як вилучити стовпець чи рядок з таблиці?

17. Яке призначення приміток і як їх Вставляти?

18. Як виконати пошук потрібних даних у таблиці?

19. Як очистити клітинку?

20. Яке значення функції МАКС(15; 40; 25)?

21. Як створити список користувача?

22. Як вилучити примітку?

23. Як користуватися командою Автофільтр?

24. Яке значення виразу СУМА(5; 10; 15) – 2 * МІН(20;40)?

25. Як заховати стовпець?

26. Яке значення функції СРЗНАЧ(15; 20; 25)?

27. Наведіть приклади статистичних функцій.

28. Яке призначення кнопки Сума?

29. Як забрати чи показати деталі у підсумковій таблиці?

30. Як скопіювати таблицю на іншу сторінку?

31. Які математичні функції є в ЕТ?

32. Яке значення виразу SIN(0) + 2 * SQRT(9)?

33. Як заповнити стовпець числами, що утворюють арифметичну прогресію?

34. Як відцентрувати заголовок таблиці відносно стовпців?

35. Як задати режим відображення формул?

36. Яке значення виразу МАКС(15; 20; 25) + МІН(8;12)?

37. Як зберегти книжку на диску?

38. Яке значення функції МАКС(7; 12)?

39. Як заповнити стовпець чи рядок елементами списку користувача?