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

Тема: Аналіз даних за допомогою функцій табличного процесора.

Мета: Навчитися виконувати автоматизацію обчислень в таблицях Excel на основі використання функцій та систем посилань різних типів.

Обладнання: персональний комп'ютер IBM PC, ОС Windows, текстовий процесор MS Word.

Порядок виконання практичної роботи:

  1. На диску D: у папці з назвою Група_Ваше прізвище, створити папку ПР № 4.
  2. Завантажити програму MS Excel.
  3. Кожне нове завдання виконувати на окремому аркуші, імена аркушам задавати згідно заголовку таблиці.
  4. Створити таблицю розрахунку вартості бланку.
  A B C D E
Вартість бланків
Товар Сума, грн Кількість Ціна одиниці, грн Ціна одиниці округлена, грн
Форма №1 345,00 {Формула1} {Формула2}
Форма №36 500,00 {Формула1} {Формула2}
Форма №51 256,00 {Формула1} {Формула2}
Форма №24 444,00 {Формула1} {Формула2}
Форма №78 769,00 {Формула1} {Формула2}
Всього {сума стовп. B} {сума стовп. C} {сума стовп. D} {сума стовп. Е}
         
    Найбільша ціна бланка: {Формула3}
    Найменша ціна бланка: {Формула4}
    Середня ціна бланка: =СРЗ
{Формула1} розраховується як «Сума, грн. ділити на «Кількість»
{Формула2} розраховується як «Сума, грн.» ділити на «Кількість», але з використанням функції округлення «ОКРУГЛ»
{Формула3} Розрахувати найбільшу вартість бланку за допомогою функції «МАКС»
{Формула4} Розрахувати найменшу вартість бланку за допомогою функції «МИН»
{Формула5} Розрахувати середню вартість бланку за допомогою функції «СРЗНАЧ»
               

5. Створити дві таблиці на різних аркушах

  A B C D E F G H
1. Витрати пального машинами різних марок.
2. Курс долара 8,20
3. Машина Марка пального Витрати на 100 км у літрах при швидкості Середні витрати палива Середня вартість на 100 км
4. 50 км/г 90 км/г 120 км/г 180 км/г
5. AUDI   {Формула2}
6. BMV {Формула1} {Формула2}
7. MERSEDES {Формула1} {Формула2}
8. ЗАПОРОЖЕЦ {Формула1} {Формула2}
9. FIAT {Формула1} {Формула2}
10. FERRARI {Формула1} {Формула2}
11. Всього {сума ст. H}
12.
13. Найменьша з середніх витрат палива {Формула3}
14. Найбільша з середніх витрат палива {Формула4}


  A B
1. Вартість пального
2.    
3. Марка пального Ціна одного літру
4. 10,50
5. 11,85
6.    
7.    
{Формула1} Розраховується як середне значення між всіма витратами палива
{Формула2} = Ціна одного літру* Середні витрати палива (Ціна одного літру вибирається з таблиці Вартість пального за допомогою функції ЕСЛИ)
         
  1. Створити таблицю
  A B C D E F
  Розрахунок витрат по електроенергії за січень
           
1. Вартість за 1 кВт/годин, коп. 0,28    
2. Середньомісячна тривалість робочого часу, год 168,9    
3. Коефіцієнт споживання   0,80    
4. Розрахунковий час споживання на місяць {Формула1}    
           
№ Споживачі Кількість Потужність, кВт Оплата за місяць За одиницю
Побутові потреби 0,080 {Формула2} {Формула3}
Виробничі потреби        
  - ПЕОМ Pentium 0,280 {Формула2} {Формула3}
  - Монітор SVGA 0,080 {Формула2} {Формула3}
  - Телевізор Sony 0,250 {Формула2} {Формула3}
  - Принтер Сanon 0,080 {Формула2} {Формула3}
  - Стендове обладнання 1,100 {Формула2} {Формула3}
  - Паяльне обладнання 0,070 {Формула2} {Формула3}
  Всього на вироб. потреби {Формула4} {Формула5} {Формула7}  
  Всього   {Формула6} {Формула8}  
           

Формули створити самостійно на основі пояснення:

{Формула1} множити значення пунктів 2 та 3 розташованих попереду таблиці
{Формула2} «Вартість за 1 кВт/годин, коп.» * «Розрахунковий час споживання на місяць»* «Кількість* «Потужність» При створенні формули назви комірок, де розташований результат «Вартості за 1 кВт/годин» та «Розрахункового часу споживання на місяць» повинні встановлюватись з застосуванням банківського знаку, наприклад $D$3.
{Формула3} ділити «Оплату за місяць на «Кількість»
{Формула4} Сума по пункту 2 стовпчика «Кількість»
{Формула5} Сума по пункту 2 стовпчика «Потужність»
{Формула6} Скласти значення по пункту1 та «Всього на вироб. потреби» стовпчика по стовпчику «Потужність»
{Формула7} Сума по пункту 2 стовпчика «Оплата за місяць»
{Формула8} Скласти значення по пункту1 та «Всього на вироб. потреби» стовпчика по стовпчику «Оплата за місяць»
  1. Торговий склад виробляє уцінку продукції, що зберігається. Якщо продукція зберігається на складі довше 10 місяців, то вона втрачає вартість у 2 рази, а якщо термін зберігання перевищив 6 місяців, але не досяг 10 місяців, то - в 1,5 рази. Отримати відомість уцінки товару, яка повинна включати наступну інформацію: найменування товару, термін зберігання, ціна товару до уцінки, ціна товару після уцінки.

Режим значень

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

8. Створити таблицю розрахунку заробітної плати

A B C D E F G H I J K
Розрахунково-платіжна відомість видачі заробітної плати за листопад 2012 року
Прізвище Роз-ряд Тариф (коп.) Відпрац. час (год.) Нарах. (грн.) Утрим. прибуд. подат. (грн.) Утрим. пенс. : (до 150-1%<150-2) Утрим. профс. 1%: Ф-д соц. зах безр. 0,5%: Всього утрим. До видачі
Бондарев Б. В. 74,20 {Форм.1} {Форм.2} {Форм.3} {Форм.4} {Форм.5} {Форм.6} {Форм.7}
Волков В. В. 59,60              
Горелов Г. В. 59,60              
Белкін Б. М. 56,60              
Крилов К. В. 56,60              
Новіков Н. В. 59,60              
Сухов С. В. 59,60              
Михайлов А.А. 56,60              
Соткін С. Н. 56,60              
Тихонов Т. В. 56,60              
Фемін Ф. Н. 59,60              
Носов О. К. 59,60              
Орлов О. В. 59,60              
Прохоров П. В. 74,20              
Білоусов Б. В. 59,60              
Єгоров Е. В. 59,60              
Жуков М. Д. 56,60              
Оніщенко О. О. 56,60              
Правдін П. С. 74,20              
                     
Всього:                    
                     
Директор               І. Іванов
{Форм. 1} = «Тариф» * «Відпрацьований час» (врахувати, що тариф вказаний копійками, а «Нараховано» виводиться у гривнях)
{Форм. 2} «Утримано прибуткового податку». Розраховується так: Якщо «Нараховано» менше ніж 85 грн. «Утримано прибуткового податку» дорівнює – нуль. Якщо «Нараховано» більше 85 грн. тоді «Утримано прибуткового податку» як 13% від різниці між «Нараховано» та мінімумом 85 грн.
{Форм. 3} «Утримано пенсійний податок». Якщо «Нараховано» більше 150 грн. тоді нараховується як 2% від «Нараховано», якщо менше 150 грн. тоді нараховується як 1% від «Нараховано»
{Форм. 4} «Утримано профспілковий податок» = 1% від «Нараховано»
{Форм. 5} «Фонд соц. захисту безробітних» = 0,5% від «Нараховано»
{Форм. 6} = «Утримано прибутковий податок» + «Утримано пенсійний податок» + «Утримано профспілковий податок» + «Фонд соц. захисту безробітних»
{Форм. 7} = «Нараховано» – «Всього утримано»
                                           

9. Таблиця містить наступні дані про учнів школи: прізвище, вік і зростання учня. Скільки учнів можуть займатися в баскетбольній секції, якщо туди приймають дітей з зростом не менше 160 см? Вік не повинен перевищувати 13 років.

Режим значень

    Максимальний вік
    Мінімальний зріст
Прізвище Вік Зріст Результат
Іванов  
Петров  
Сидоров  
Смирнов  
Воронін  
Воробйов  
Снигирів  
Соколов  
Макаров  
Павлов  
Підсумок

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