На аркушi 1 захистiть усi жовтi стовпцi (Номер, Назва, Цiна, Сума) та аркуш.

Переконайтеся, що змiни можна вносити лише у стовпець Кiлькiсть.

25. Для розв’язування задачi 1а на аркушi 4 виконайте консолiдацiю трьох таблиць „Товарний чек”.

Задача 1а. Скiльки всього одиниць i на яку суму куплено товару з номером 1? Скiльки всього затрачено грошей?

Алгоритм розв’язування задачi такий:

1. Скопiюйте стовпцi А, В, С (якi мiстятъ незмiннi частини таблиць) з аркуша 1 на аркуш 4.

2. Виокремте поряд з ними стовпці D i Е для занесення консолiдованих даних.

3. Виконайте команди з головного меню Данi – Консолідація...

4. Переконайтеся, що функцiя консолідації — Сума.

5. Перейдiть на аркуш 1.

6. Виокремте стовпцi D i Е з вхiдними даними.

7. Виконайте команду Додати у вікні консолідації.

8. Повторiть пункти 6 і7 для аркушiв 2 і 3.

9. Вiзуально переконайтеся, що список дiапазонiв містить посилання на три потрiбнi аркушi.

10. Натиснiть на кнопку ОК — отримаєте результат.

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

27. Для розв’язування задачi 2 перейдіть на п’ятий аркуш і назвiть йото „Рахунок”

Вставте у книгу аркуш, якщо потрiбно. двiчi клацнiть на бiрцi аркуша, введiть нову назву і натиснiть на клавiшу вводу.

2В. Увiмкнiть режим вiдображення формул у таблицi.

29. Уведiть заголовки, числа i формули розв’язування задачi 2:

АдресаДані і формули

А1 Нарахування % протягом 10 рокiв

А2 Рiк

В2 Сума

С2 Прирiст

D2 Кiнець року

АЗ 1999

B3 2500

СЗ =ВЗ*0, 12

D3 =ВЗ+С3

В4 =D3

30. Заповнiть роки командою Заповнити.

Виокремте клiтинку АЗ - Редагувати - Заповнити – Прогресія – По стовпцях – Арифметична – Крок 1- Граничне значення: 2015- ОК

Скопiюйте формулу з клiтинки В4 вниз.

Виокремте клiтинку В4 i перетягнiть маркер копiювання вниз.

Скопiюйте формулу а клiтинки С3 вниз.

Скопiюйте формулу з клiтинки D3 вниз (рис. 40).

34. Скасуйте режим вiдображення формул —. отримаєте результати.

Який буде внесок через 10 рокiв? Розв’язок задачi є в клітинці D12.

35. Розв’яжіть задачу 2а.

Задача 2а. Який буде внесок через 10 рокiв, якщо початкова сума виражається чотирьохзначним числом, утвореним з номера вашого варiанта дописуванням двох чи трьох нулiв?

Результат перепишіть у звiт.

36. Скопiюйте таблицю на шостий аркуш i розв’яжіть задачу 2б.

Задача 26. Якою буде сума внеску через 10 рокiв, якщо внесок зроблено під 15% рiчних?

Внесiть змiни у формулу, що є в клiтинцi С3, i не забудьте скопіювати її в iнші клiтинки.

Вiдповiдь з клiтинки D12 занестьi у звiт.

Збережiть книжку на диску.

Продемонструйте шiсть аркушiв викладачевi. Закiнчiть роботу.

Практичнi роботи № 2-3

Тема

МS Ехсеl. Статистичнi функції. Електронна таблищя як база даних. Підведення підсумків. Географiчна карта. Задача „Дiяльнiсть фiрми в Українi”. Задача „Дiяльнiсть фiрми в Європi”. Задача „Табулювання функції та обчислення площi кривої”.

Мета

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

Задача З „Дiяльнiсть фiрми в Україні” (робота № 3)

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

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

Задача 4 „Дiяльнiсть фiрми в Європі” (робота № 4)

Нехай ваша фiрма має фiлiали не пише в Києвi, Харковi, Львові, Одесi, Донецьку, але ще й у трьох країнах Європи: Польщi, Німеччини, Францiї (розглянути по три-чотири мiста в кожнiй країні). Данi про діяльність фiрми протягом трьох мiсяцiв, наприклад, сiчня, лютого, березня, занести в таблицю i визначити обсяги продажiв у Європi для кожної країни методом пiдведення пiдсумкiв. Нанести результати у виглядi стовпчикових дiаграм на географiчну карту Європи. Таблиця має мiстити близько двадцати рядкiв такої структури: Країна, Мiсто, Сiчень, Лютий, Березень, Всього. Назви мiст пiд час введення по країнах не треба групувати.

Задача 5 „ Табулювання функцiї та обчислення площi (робота № 4)

Протабулювати функцiю у=n(sin nx²+1), де n — номер варiанта, та обчислити площу пiд кривою методом лiвих прямокутникiв. Вiдрiзок, на якому розглядати функцiю, i крок табулювання h задати самостiйно (у таблицi повинно бути 10—12 рядкiв) (рис. 341, а).

Рис. 3, а. Зразок таблицi для задачi З.

Рис. 3, б. Зразок таблицi для задачi 5.

План

1. Дiапазони клiтинок.

2. Вирази i функції.

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

4. Електронна таблиця як база даних.

5. Пiдсумки в таблицях.

6. Географiчна карта.

Теоретичні відомості

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

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

• А1: С3 — прямокутний дiапазон;

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

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

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

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

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

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

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

• математичні — siп, соs, ехр, ln, аЬs, аtan, sgrt тощо, а також, функцiї для роботи з матрицями;

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

• логічні;

• фiнансові;

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

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

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

Оскiльки суми обчислюють найчастіше, на панелi керування

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

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

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

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

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

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

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

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

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

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

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

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

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

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

4. Уточнити тип карти в отриманому дiалоговому вiкнi:

Європа, Росiя, Україна тощо. Одержимо карту, панель iнструментiв i панель оформления карти, яка має назву Елемент Мiсrosoft Мар.

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

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