17169

Ms Excel. Статистичні функції. Електронна таблиця, як база даних. Підведення підсумків. Географічна карта

Практическая работа

Информатика, кибернетика и программирование

Практична робота №14 Тема: Ms Excel. Статистичні функції. Електронна таблиця як база даних. Підведення підсумків. Географічна карта. Мета: Уміти користуватися діапазонами клітинок та стандартними статистичними функціями наносити дані на географічну карту вилучати вста...

Украинкский

2013-06-29

207.5 KB

57 чел.

Практична робота №14

Тема: Ms Excel. Статистичні функції. Електронна таблиця, як база даних. Підведення підсумків. Географічна карта.

Мета: Уміти користуватися діапазонами клітинок та стандартними статистичними функціями, наносити дані на географічну карту, вилучати, вставляти стовпці і рядки в таблицю, впорядковувати і шукати дані, підводити підсумки, будувати математичні вирази.

Обладнання: ПЕОМ. Табличний процесор MS Excel.

  1.  Правила ТБ
  2.  Теоретичні відомості

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

Діапазони мають прямокутну форму й описуються адресами двох діагонально-протилежних клітинок. Наприклад:

♦   А1:СЗ — прямокутний діапазон;

♦   А1:А9 — діапазон-стовпець;

♦   А1:Е1 — діапазон-рядок.

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

Щоб виокремити несуміжні діапазони, треба користуватися клавішею Сtrl. Наприклад, щоб виокремити два несуміжні стовпці-діапазони, потрібно клацнути на їхніх назвах в режимі натиснутої клавіші Сtrl.

Діапазонам можна надавати назви і використовувати ці назви замість виразів на зразок А1:А9. Програма сама дає назви діапазонам, якщо вона може їх однозначно розпізнати. Наприклад, в таблиці на рис. 36 назви стовпців розпізнаються автоматично, тому в клітинку Е4 замість формули =В4 + С4 + D4 можна ввести формулу =Січень + Лютий + Березень.

2. Вирази і функції. Як відомо, для виконання обчислень використовують формули. Формула має вигляд = вираз. Розглянемо правила утворення виразів. Пріоритети виконання операцій у виразах такі, як в елементарній математиці. Наведемо їх у спадному порядку:

Пріоритет  Операції               Пояснення

   1                    ()                        операції в дужках, аргументи функцій;

   2                 sin, cos тощо        математичні та інші функції;

   3                   -                          унарний мінус;

   4                   %                        відсотки;

   5                   ^                          піднесення до степеня (-5^2=25);

   6                 *  або /                  множення або ділення;

   7                 + або -                  додавання або віднімання;

   8                    &                       об'єднання текстів;

   9                =,<, >, >=              операції порівняння.

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

математичні sin, соs, ехр, in, аbs, аtan, sqrt тощо, а також функції для роботи з матрицями;

статистичні — СРЗНАЧ, МИН, МАКС, СУММ тощо (розглядаємо російськомовну версію програми);

логічні;

фінансові, бухгалтерські;

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

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

=СУММ(А1;В6:С8;20).

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

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

3.  Списки користувача. Розглянемо ще один спосіб швидкого введення текстових даних у таблицю. Він полягає у використанні списків користувача. Списки можуть містити назви товарів, міст, фірм, прізвища тощо. Список користувач спочатку створює командами Сервіс → Параметри → Закладка Списки → Новий СПИСОК → Вводить елементи списку через кому або натискаючи на клавішу вводу → Додати → ОК. Список використовують так: перший елемент списку вводять у деяку клітинку, перетягують її маркер копіювання — відбувається авто заповнення таблиці елементами списку.

4.  Електронна таблиця як база даних. ЕТ можна використовувати як базу даних. Розглянемо типові дії, які можна виконувати з даними: 1) впорядковувати рядки за зростанням чи спаданням значень у деякому стовпці; 2) шукати дані за деяким критерієм; 3) переглядати, створювати і шукати дані в ЕТ з великою кількістю стовпців і рядків за допомогою форм. Стовпець з даними тут називають полем.

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

Пошук даних називають інакше фільтруванням даних. Спочатку вибирають рядок, що містить назви стовпців, і виконують команду Дані → Фільтр → Автофільтр. Клітинки з назвами стовпців стають списками з кнопками розгортання. Розгортають потрібний список, наприклад Січень, вибирають у списку значення Умова — відкриється вікно конструктора умов. Тут є зручні засоби для формулювання критерію пошуку в стовпці Січень. Приклад числової умови: більше 500000 і менше 2000000. Натискають на ОК і на екрані отримують результати пошуку — рядки таблиці з містами, де показник діяльності фірми у січні задовольняв цьому критерію. Щоб відновити на екрані всю таблицю, виконують команду Дані → Фільтр → Показати все.

Якщо треба побудувати складний критерій на базі назв декількох стовпців, то застосовують команду Дані → Фільтр → Розширений фільтр, яку в цій роботі не використовуватимемо, оскільки її можна замінити виконанням команди Автофільтр декілька разів.

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

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

6.  Географічна карта. Для візуального подання числових даних економічного чи іншого характеру використовують інструмент — географічну карту. Є такі формати (способи) відображення даних: кольорові або сірі (різної інтенсивності) заливки територій, покриття територій країн крапками, пропорційними символами, круговими чи стовпчиковими діаграмами. За допомогою кнопок панелі інструментів та команд меню на карту можна вивести підписи до об'єктів (назви країн, міст тощо), різні тексти, карту автомобільних доріг, географічну сітку, різні по-значки-прапорці тощо (рис. 42).

Розглянемо алгоритм нанесення даних на географічну карту.

1.  Виокремити дані разом з назвами полів. Назви країн мають бути виконані російською мовою або англійською залежно від версії програми.

2.  Запустити додаток Місrоsоft Мар, клацнувши на кнопці стандартної панелі із зображенням глобуса.

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

4.  Уточнити тип карти в отриманому діалоговому вікні: Європа, Росія, Україна тощо. Одержимо карту, панель інструментів і панель оформлення карти, яка має назву Елемент Microsoft Map.

                               Рис. 1.Нанесення даних на географічну карту.

  1.  Перетягнути кнопку формату, що є ліворуч у панелі оформлення карти, в робоче поле конструктора, а кнопку з назвою поля даних, наприклад Січень, перетягнути сюди зверху.
  2.  Повторити п. 5 для інших форматів та полів даних.

Зауважимо, що, починаючи з МS Ехсеl 2002, засоби для роботи з географічною картою вилучені. Мало того, таблиці, що містять географічні карти, створені в попередніх версіях, не відкриваються у нових.

  1.  Індивідуальне завдання

         Задача «Діяльність фірми в Україні»

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

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

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

Хід роботи

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

2.     Створіть список користувача з назвами міст для розв'язування задачі 3.

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

Адреси          Дані

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

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

АЗ            Місто

B3            Січень

СЗ            Лютий

DЗ            Березень

ЕЗ            Всього

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

B4            2250000 (Уведіть такі або подібні семизначні числові дані)

С4            2340000

D4          3200000

А5           Львів

B5           1150000

С5           1550000

D5           1640000

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

А10        Всього

А12        Максимум

А13        Мінімум

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

4.     Уведіть формули розв'язування задачі. У клітинці Е4 обчисліть суму чисел рядка 4.

Виберіть клітинку Е4 і натисніть на кнопки Автосума — отримаєте формулу = СУММ(В4:D4). Натисніть Enter.

5.     У клітинці В10 обчисліть суму чисел у стовпці В. Виберіть клітинку В10 і натисніть на кнопки Автосума і вводу.

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

7.     Скопіюйте формулу з клітинки В10 праворуч у діапазон С10:D10.

8.     У клітинках В12:Е12 визначте максимальні значення у стовпцях даних.

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

9.     Визначте мінімальні значення у стовпцях.

Виберіть клітинку В1З і натисніть на кнопку Вставляння функцій fх, виберіть у діалоговому вікні функцію МИН ОК.

Уведіть у наступному вікні діапазон В4:В8 і натисніть на ОК.

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

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

 Виберіть усі числові дані в таблиці → Формат → Клітинки → Число → Числовий → Увімкніть режим розмежовувати групи розрядів і задайте кількість десяткових цифр після коми (крапки): → ОК.

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

Виберіть діапазон А1:Е1 і натисніть на кнопку З'єднати і помістити в центрі (буква а зі стрілками) на панелі інструментів.

13*. Зобразіть дані на географічній карті України, використовуючи формат — стовпчикові діаграми.

Це завдання виконувати за вказівкою викладача лише за наявності карти України в комплекті комп'ютерних карт. Назви міст введіть мовою програми (російською чи англійською).

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

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

16.  Заховайте і покажіть стовпець Е.

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

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

18.  Доповніть таблицю трьома рядками з новими містами і відповідними даними.

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

19.   Скопіюйте таблицю з аркуша 2 на аркуші 3 і 4.

20.   Упорядкуйте рядки таблиці в алфавітному порядку назв міст (аркуш 2).

Спочатку виокремте лише рядки з даними. Дані → Сортувати.

21.  Упорядкуйте філіали (рядки) за спаданням обсягу продажу в першому кварталі (аркуш2).

Занотуйте у звіт, який філіал є на першому місці.

22.   Перейдіть на аркуш 3 і перейменуйте його на Фільтри. Скористайтесь контекстним меню закладки аркуша 3.

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

24.  Скопіюйте результат пошуку під основну таблицю.

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

26.   Які філіали мали обсяги продажу в січні більші, ніж 700 000, а в лютому більші, ніж     800 000?

Команду Автофільтр застосуйте двічі. Перенесіть утворену таблицю під основну.

27.   Відформатуйте дані і доповніть таблицю на аркуші 4 так. Числа відобразіть у грошовому форматі з наявністю назви грошової одиниці. Для цього виберіть усі числові дані і задайте потрібний формат. Доповніть таблицю стовпцем з номерами телефонів фірм. Номери телефонів введіть як семизначні числа і задайте їм формат користувача ##0-00-00. Доповніть таблицю стовпцем з датами подання філіалами даних і задайте датам формат зі списку форматів дат або формат користувача ДД.ММ.ГГГГ (dd.mm.уууу). Застосуйте автопідбір ширини стовпців.

28.  Ознайомтесь з функціонуванням ЕТ в режимі форми. Переконайтесь, що на закладці Обчислення задано параметр книги: Допускати назви діапазонів. Виберіть дані разом із заголовками стовпців і виконайте команду Дані Форма... Полистайте сторінки форми вперед і назад. Створіть ще один запис з інформацією про філію в Броварах (скористайтесь кнопкою Створити і натискайте на клавішу Таb для переходу на наступне поле введення даних). Виконайте пошук номера телефону одеської філії, перейшовши в режим введення умов (кнопка Умови) у поля форми. Полистайте сторінки форми для міст, назви яких починаються буквою К (умова на місто: К*).

29.  Збережіть книжку на диску з назвою Задача.

30.   Продемонструйте чотири аркуші книги і закінчіть роботу.

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

1. Яке призначення формул в ЕТ? Яке значення функції МИН(5; 2; 13)?

2. Що таке копіювання формул? Яке значення функції СУММ(5; 2; 13)?

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

4.  Як клітинці надати ім'я?

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

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

7.  Як виокремити діапазон? Яке значення функції МИН(15; 20; МАКС(1; 3; 5))?

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

9.  Як вставити рядок у таблицю? Яке значення функції СУММ(12; МАКС(8; 12; 20))?

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

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

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

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

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

15.  Як нанести дані на географічну карту?

16. Як очистити клітинку? Яке значення функції МАКС(15; 40; 25)?

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

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

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

20.  Які є формати нанесення даних на географічні карти?

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

22.  Яке призначення кнопки Автосума?

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

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

25. Які математичні функції є в ЕТ? Яке значення виразу SIN(0) + 2*SQRT(9)?

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

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

28.  Як задати режим відображення формул? Яке значення функції СРЗНАЧ(15; 20; МИН(25; 45))?

29.   Як зберегти книжку на диску? Яке значення виразу СРЗНАЧ(МАКС(8; 12); МИН(4; 16))?

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

31.  Як заховати стовпець? Яке значення функції СРЗНАЧ (15; 20; 25; 5; 10)?

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

33.  Яке значення виразу МАКС(15; 20; МИН(45; 25))?

34.  Яким символом відокремлюють аргументи у функціях?

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

  1.  Оформлення звіту.
  2.  Захист роботи.


 

А также другие работы, которые могут Вас заинтересовать

81199. Вероучение и культ синтоизма 22.07 KB
  Священными книгами синтоизма считаются Кодзики и Нихон секи. Кодзики содержит мифы космогонического и героического характера. Нихон секи представляет собой в основном японские исторические хроники есть там и мифы о происхождении мира и Японии частично повторяющие сюжеты Кодзики.
81200. Специфика зороастризма 22.95 KB
  Выделяют два самых важных божества: Ахура Мазда светлое божество олицетворявшее мудрость правду и Ангра Манью тёмный бог. прежде всего почитался АхураМазда осознававшийся как Богтворец воплощение и носитель блага. Демонов противостоящих ахурам возглавляет главный носитель зла АнхраМанью. Борьба Ахура Мазды и Анхра Манью изначальна бескомпромиссна и каждый человек должен занять свою личную позицию в ней.
81201. История формирования иудаизма 26.17 KB
  Выделяют различные периоды формирования и развития иудаизма. Возникновение иудаизма как религии принято связывать с именем Моисея получивший на горе Синай через Откровение десять заповедей образовавших основу монотеизма и религиозной этики. формируются основные черты иудаизма: строгий монотеизм централизация культа канонизация священных книг появлению веры в сверхъестественную помощь для освобождения от угнетателей и веры в избавителямессию.
81202. Догматы и культ иудаизма 26.33 KB
  Центральная доктрина иудаизма вера в единого Бога который бессмертен вечен всемогущ вездесущ и безграничен. В соответствии с нормами иудаизма верующий поддерживает связь с Богом через молитву а божья воля открывается человеку через Танах.
81203. Структура Ветхого завета в иудаизме 22.12 KB
  Книги Ветхого Завета были написаны в период с XIII по I в. Ветхий Завет состоит из следующих книг: 1 Книги закона Тора Учение или Пятикнижие Моисеево составление книг приписывается Моисею: Бытие сотворение мира и человека рай первые люди грехопадение размножение человечества всемирный потоп Ной патриархи родоначальники еврейского народа Авраам Исаак Иаков Иосиф с братьями поселение евреев в Египте; Исход Моисей 10 заповедей освобождение из плена; Левит религиозное законодательство; Числа законодательство и...
81204. Формирование ислама. Жизнь и деятельность Мухаммеда 25.03 KB
  Жизнь и деятельность Мухаммеда. Политическое и религиозное движение возглавил пророк Мухаммед. Мухаммед родился в 570 г. Мать Амина по обычаю мекканцев отдала Мухаммеда кормилицебедуинке у которой он рос до 5 лет.
81205. Вероучение ислама 24.39 KB
  Иман или вера включает: Веру в Единого Бога Аллаха. Веру в Ангелов и демонов. 3Веру в Святые Писания и в святость Корана который считается словом божьим божественным откровением которое передавал Аллах в виде видений Мухаммеду в течение 22 лет т. 4Веру в Пророков и в посланничество Мухаммеда.
81206. Коран – священная книга ислама 24.8 KB
  Главным источником веры является Коран священная книга мусульман состоящая из притч молитв и проповедей Мухаммеда. спустя почти два десятилетия после смерти пророка был составлен свод Коран чтение другие названия: китаб книга зикр предостережение . Святость Корана обусловлена тем что изречения пророку диктовал архангел Джебраил на протяжении 22 лет доносивший слова самого БогаАллаха. эти откровения составили канонический текст Корана который дошел до наших дней в неизменном виде.
81207. Основные направления в исламе 24.66 KB
  В результате внутренних противоречий в VII веке возникли два направления: сунниты и шииты. Последователи суннизма признавали законность власти первых четырех халифов а шииты считали единственным законным главой мусульман четвертого халифа Али ум. Шииты поклоняются этому имаму и верят что перед Страшным Судом явится махди для установления на земле равенства и справедливости. Шииты как и сунниты признают святость Корана а в Сунне признают лишь те хадисы авторами которых являются четвертый халиф Али и его последователи.