Лабораторна робота № 4
Excel. Статистичні функції. Електронна таблиця як база даних. Організація розгалужень
Мета: вміти користуватися діапазонами комірок і стандартними статистичними функціями, виключати, вставляти стовпці і рядки в таблицю, шукати і впорядковувати дані, підводити підсумки, будувати математичні вирази.
Задача 1. В«Діяльність фірми в УкраїніВ»
Нехай ваша фірма має філіали в Києві, Харкові, Львові, Одесі, Донецьку чи інших містах і є дані про обсяги продажу у філіях. За даними про діяльність фірми протягом трьох місяців, наприклад, січня, лютого, березня, створити таблицю для визначення обсягів продажів: максимальних, мінімальних і в цілому на Україні. Крім цього, створити нову таблицю - проект бізнес-плану на наступні два місяці: квітень, травень - з розширенням географії діяльності фірми (назви двох-трьох міст додати самостійно).
Завдання 2. В«Табулювання функції та обчислення площі В»
протабулювати функцію y = n (sinx2 +1), де n - номер варіанта, і обчислити площу під кривою методом лівих прямокутників. Відрізок, на якому розглядати функцію, і крок табулювання h задати самостійно (у таблиці повинно бути 10-12 рядків).
Теоретичні відомості
Декілька клітинок робочої таблиці, які мають суміжні сторони, утворюють діапазон комірок.
Діапазони мають прямокутну форму і описуються адресами двох діагонально-протилежних осередків. Наприклад: А1: С3 - прямокутний діапазон; А1: А9 - діапазон-стовпець; А1: Е1 - діапазон-рядок.
Щоб виділити діапазон, потрібно клацнути в лівому верхньому кутку і, не відпускаючи кнопки, перемістити білий хрестоподібний курсор у правий нижній кут, відпустити кнопку. Щоб відмовитися від вибору, досить клацнути за межами діапазону.
Щоб виділити несуміжні діапазони, потрібно користуватися клавішею Ctrl. Наприклад, щоб виділити два несуміжних стовпця-діапазону, потрібно клацнути на їх назвах в режимі натиснутої клавіші Ctrl.
діапазоні можна давати назви і використовувати ці назви замість виразів типу А1: А9. Програма сама дає назви діапазонам, якщо вона може їх однозначно розпізнати. Наприклад, в таблиці на рис. 1 назви стовпців розпізнаються автоматично, тому в клітинку Е4 замість формули = B4 + C4 + D4 можна ввести формулу = січня + лютий + Березень.
Як відомо, для виконання обчислень використовують формули. Формула має вигляд = вираз. Розглянемо правила утворення виразів. Пріоритети виконання операцій у виразах такі як в елементарної математики. Наведемо їх у зворотньому порядку:
Пріоритет Операції Пояснення
1 () операції в дужках, аргументи функцій;
2 sin, cos тощо математичні та інші функції;
3% відсотки;
4 ^ зведення в ступінь (-5 ^ 2 = 25);
5 * або/множення або розподіл;
6 + або - сума або різниця;
7 & об'єднання текстів;
8 =, <,>,> = операції порівняння.
Стандартних функцій є декілька категорій: математичні - sin, cos, exp, ln, abs, atan, sqrt і ін, а також функції для роботи з матрицями; статистичні - СРЗНАЧ, МИН, МАКС, СУММ та ін; логічні; фінансові; для роботи з датами, текстами та ін
Функції можуть бути визначені над числами, адресами осередків, адресами (назвами) діапазонів і їх списками. Елементи списку записують через роздільник, який визначається операційною системою: кому, якщо в числах використовується десяткова крапка, або крапку з комою, наприклад, так, = СУММ (А1; В6: С8; 20).
Оскільки суми обчислюють найчастіше, на панелі інструментів є кнопка Автосума. Нею користуються так: виокремлюють комірку під стовпцем або праворуч від рядка з даними і клацають на кнопці Автосума - отримують потрібну суму (числових даних з відповідного стовпця або рядка).
Дії над елементами ЕТ (виділеними осередками, стовпцями, рядками, діапазонами, усією таблицею) виконують командами контекстного або основного меню або за допомогою кнопок панелі інструментів.
Наприклад, при необхідності в таблицю вставляють порожні рядки (стовпчики) або виключають їх командами: Редагувати> Вставити або Виключити.
У виділену клітинку можна вставити примітку, яка пояснює її призначення, командою Вставити> Примітка, а виключити командою Редагувати> Очистити (або засобами контекстного меню).
Розглянемо ще один спосіб швидкого введення текстових даних в таблицю. Він полягає у використанні списків користувача. Списки можуть містити назви товарів, міст, фірм, прізвища та т.д. Список користувач спочатку створює командами Сервис> Установки> Вкладка Списки> Новий список> Вводити елементи списку через кому або натискаючи клавішу введення> Додати> ОК. Список використовують так: перший елемент списку вводять в яку-небудь комірку, перетягують її маркер копіювання - Здійснюється автозаповнення таблиці елементами списку.
ЕТ можна використовувати як базу даних. Розглянемо стандартні дії, які можна виконувати з даними: 1) впорядкувати рядки за зростанням або спаданням значень у деякому стовпці; 2) шукати дані по деяких критеріях. Стовпець з даними тут називають полем.
Впорядкування. Спочатку вибирають частину таблиці з даними і назвами полів або всю таблицю (без заголовка таблиці і рядків з підсумками). Сортування виконують командою Дані> Сортувати, отримують список назв полів, де вибирають потрібну назву, наприклад Місто, і задають порядок сортування: за зростанням або спаданням - отримують таблицю, де рядки будуть впорядковані в алфавітному або зворотному порядку назв міст.
Пошук даних називають інакше фільтром даних. Спочатку вибирають рядок, яка містить назви стовпців, і виконують команду Дані> Фільтр> Автофільтр. Осередки з назвою стовпців стають списками з кнопками розгортання. Розгортають потрібний список, наприклад Січень, вибирають в списку значення Умова - відкривається вікно конструктора умов. У ньому є зручні засоби для формулювання критерію пошуку по стовпцю Січень, наприклад такого: більше 500000 і менше 2000000. Після цього натискають на ОК і на екрані отримують результати пошуку - рядки таблиці з містами, де показник діяльності фірми в січні задовольняє даним критерієм. Щоб відновити на екрані всю таблицю, виконують команду Дані> Фільтр> Показати всі.
Якщо потрібно отримати складний критерій на базі назв кількох шпальт, то використовують команду Дані> Фільтр> Розширений фільтр.
Підсумки в таблицях. Підсумки підводять з метою визначення кращих, гірших, сумарних, середніх показників діяльності фірми в декількох країнах, містах, підрозділах і т.д. Для цього спочатку рядки в таблиці сортують з метою групування (розміщення поруч) даних, які стосуються кожної країни, міста або підрозділу для отримання підсумків до впорядкованої таблиці застосовують команду Дані> Підсумки, де задають: 1) назву поля, що містить об'єкти, для яких створюють підсумки, наприклад Країна; 2) операцію підсумовування і 3) назву поля, що містить дані, які підлягають підсумовування (наприклад, Всього або/і Березень). Операції підсумовування бувають різні: сума, максимум, мінімум, середнє значення, відхилення від норми і т.д.
Завдання
1. Запустіть програму ЕТ, відкрийте нову книгу і створіть список користувача з назвами міст.
1.2. Введіть дані для вирішення завдання 1, як показано на рис. 1. Далі введіть дані самостійно ще для трьох міст
Числа в стовпець Е і рядки 10-13 НЕ вводити!
3. Введіть формули для розв'язування задачі 1. В осередку Е4 обчисліть суму чисел рядка 4.
Виберіть клітинку Е4 і натисніть на кнопку Автосума, а потім на кнопку введення - отримаєте формулу = СУММ (B4: D4).
4. В осередку В10 обчисліть суму чисел у стовпці В.
5. Скопіюйте формулу з комірки Е4 вниз в діапазон Е5: Е10.
6. Скопіюйте формулу з комірки В10 правіше в діапазон C10: D10.
7. В осередках В12: Е12 визначте ...
