Приклад виконання завдання

Завдання. Створити таблицю, у якій розраховується заробіток робітників сільського кооперативу при збиранні яблук. У сільському кооперативі працюють дві бригади, робітники яких збирають яблука трьох сортів. За 1 кг зібраних яблук сорту “Семеренко” працівник одержує 0, 17 грн.; “Джонатан” - 0, 12 грн.; “Шафран” - 0, 10 грн. Скласти таблицю, що містить наступні стовпчики: номер бригади, прізвище робітника, сорт яблук, кількість зібраних кілограм, оплата за зібрані яблука. Для введення номера бригади, прізвища робітника й сорту яблук організувати списки, що розкриваються. У стовпці оплата записати формулу, що обчислює заробіток робітника. Обчислити загальну кількість зібраних яблук і загальну суму заробітку робітників.

На рисунку 1.1 наведена створена таблиця. Діапазон G3: G8 містить список всіх робітників. У діапазоні В3: В9 кожна клітина містить список, що розкривається, значень із діапазону G3: G8, що створювався через пункт меню Данные/Проверка.

До клітини Е3 введена формула:

=ЕСЛИ(C3 = $I$3; $J$3; ЕСЛИ(C3 = $I$4; $J$4; $J$5))*D3

Ця формула обчислює заробіток робітника залежно від зібраної кількості яблук (клітина D3) і розцінок для кожного сорту (клітини I3: J5). Адреси клітин з діапазону I3: J5 є абсолютними, тому що вони використовуються для кожного робітника, тому не повинні змінюватися при копіюванні. Щоб обчислити загальну кількість зібраних кілограм яблук, до клітини D11 введена формула =СУММ(D3: D9).

Рисунок 1.1 - Таблиця для завдання

1.3 Завдання для самостійної роботи

Створити таблицю для даних свого варіанту в Excel:

1. заповнити таблицю даними (не менше 20 рядків);

2. при заповненні таблиці даними, які повторюються, викори­стати команду Проверка в меню Данные для створення списків, щоб прискорити введення;

3. тарифи та інші фіксовані дані розмістити на окремому Листі або вище головної таблиці; посилання на них здійснювати за допомогою абсолютної адреси або імені;

4. виконати необхідні розрахунки за допомогою формул та Майстра функцій;

5. виконати форматування таблиці;

6. виконати копіювання, переміщення та коригування даних.

1.4 Варіанти індивідуальних завданнь

Варіант 1

На малому підприємстві працюють три бригади, робітники яких виготовляють деталі двох найменувань на станках з програмним управлінням. Кожен робітник за зміну виготовляє деталі тільки одного з найменувань. Існують різні розцінки для кожного виду деталей та норма для кількості деталей, виготовлених за зміну. Якщо кількість деталей, зроблених робітником, перевищує норму, то робітник отри­мує премію у розмірі 10% від заробітку.

Норми визначити самостійно та записати їх на Лист Excel. Скла­сти таблицю, що містить таку інформацію: номер бригади; прізвище робітника; найменування виготовлених деталей; їх кількість; вартість їх виготовлення; премія; загальна нарахована сума.

Найменування виготовлених деталей вибирати зі списку, створе­ного в меню Данные – Проверка. Встановити контроль введе­них даних для номеру бригади.

Підрахувати загальну суму, сплачену робітникам. Визначити се­редній та максимальний розмір премії.

Варіант 2

Компанія по постачанню електроенергії отримує плату з клієн­тів за тарифом:

ü 16 коп. за 1кВт/г, якщо спожито менше 200 кВт за місяць;

ü 20 коп. за 1 кВт/г, якщо споживання за місяць від 200 кВт до 500 кВт включно;

ü 30 коп. за 1 кВт/г, якщо споживання за місяць більше 500 кВт.

Послугами компанії користуються 20 клієнтів, які мешкають у трьох будинках. Кожен платник може сплачувати нараховану суму готівкою або кредитною карткою.

Діючі тарифи повинні бути записані в окремих клітинках. Вид оплати вибирати зі списку, створеного в меню Данные – Проверка. Встановити контроль введених даних для номеру будинку.

Скласти таблицю, що містить таку інформацію: номер будинку; прізвище клієнта; попередній показник лічильника; останній показник лічильника; спожита кількість електроенергії; її вартість; вид оплати (готівка або кредитка).

Розрахувати вартість електроенергії для кожного клієнта і за­гальну нараховану суму. Визначити мінімальну та середню кількість спожитої електроенергії.

Варіант 3

Три магазини замовляють на складі книжки. За замовлення більше ніж 30 примірників склад зменшує вартість на них на 10%; а за 50 і більше примірників – на 15%. Скласти таблицю, що містить таку інформацію: магазин, видавництво, автор, назва книги, ціна за одну книгу, замовлена кількість, загальна вартість зі знижкою.

Відсотки знижок записати до клітинок та посилання на них здійснювати за допомогою абсолютної адреси або імені. Назву видав­ництва вибирати зі списку, створеного в меню Данные – Проверка. Встановити контроль введених даних для номеру магазину.

Обчислити загальну вартість та кількість замовлених книг. Визначити найбільший та середній обсяг замовлення.

Варіант 4

У місті розташовано три автопарки для таксі з автомобілями економ – класу та престиж – класу. Сплата за таксі здійснюється за такими правилами. Поїздки на 5 км або менше коштують однаково для всіх типів таксі. Кожний наступний кілометр сплачується за ін­шим тарифом. Цей тариф для таксі престиж – класу більше ніж тариф для економ – класу на певний відсоток, наприклад на 10%. Водій отримує свій відсоток за кожну поїздку. Розміри тарифів і відсотків встановити самостійно та записати їх на Лист Excel.

Скласти таблицю обліку поїздок, яка містить таку інформацію: номер автопарку; клас таксі; прізвище водія; відстань; вартість по­їздки; заробітна плата водія.

Клас таксі вибирати зі списку, створеного за допомогою меню Данные – Проверка. Для номеру автопарку встановити контроль вве­дених даних або вибирати його зі списку.

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

Варіант 5

Три туристичні фірми організують подорожі до п’яти країн. Якщо клієнт замовив більше трьох путівок, він отримує знижку у роз­мірі 5% від вартості путівок; більше десяти путівок – 10%. Відсотки знижок записати до окремих клітинок.

Скласти таблицю, яка містить таку інформацію: фірма; прізвище покупця; країна; вартість однієї путівки; кількість замовлених путівок; кількість днів відпочинку; вартість путівок з урахуванням знижок.

Назву фірми та країну для подорожі вибирати зі списків, створе­них в меню Данные – Проверка.

Обчислити загальну вартість путівок. Визначити середню кіль­кість днів відпочинку. Яка найбільша кількість путівок в одному замовленні?

Варіант 6

На оптовому складі зберігається продукція чотирьох категорій: парфумерія, побутова хімія, господарчі товари, посуд. На складі діє така система знижок:

ü якщо продукція зберігається на складі довше ніж 3 місяці, то ціну на неї зменшують на 10%;

ü якщо термін зберігання перевищує 6 місяців, то ціну зменшу­ють на 30%.

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

Категорію товару вибирати зі списку, створеного за допомогою меню Данные – Проверка. Встановити контроль введених даних для терміну зберігання.

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

Варіант 7

На аптечномускладі зберігаються ліки. Необхідно скласти відо­мість з обліку ліків. Таблиця повинна мати таку інформацію: назва препарату; країна – виробник; дата виготовлення; термін зберігання в місяцях; ціна за одиницю упаковки; кількість; дата закінчення терміну зберігання; загальна вартість; уцінка. Для обчислення дати закінчення терміну зберігання вважатимемо, що в місяці завжди 30 днів.

Замість системної дати комп’ютера краще записати до окремої клітинки конкретну дату та використовувати її в розрахунках, не змі­нюючи. Країну – виробника треба вибирати зі списку, створеного в меню Данные – Проверка. Встановити контроль введених даних для терміну зберігання.

Якщо термін зберігання закінчується менш ніж за 30 днів до вказаної дати, то ціна товару зменшується на 10%. Запишіть її до стовпчику " Уцінка ". Якщо термін зберігання вже скінчився, то нав­проти назви препарату в тому ж стовпчику зробіть позначку “ Підлягає знищенню ”.

Обчислити загальну вартість усіх ліків. Визначити середній та максимальний терміни зберігання ліків.

Варіант 8

У двох філіях фірми працює 20 працівників. З їхньої заробітної плати утримується прибутковий податок згідно таких ставок:

ü із заробітку від 0 до 85 гривень – не утримується;

ü вище 85 гривень – 13% від суми, що перевищує 85 грн.

Відсотки податків та розмір неоподаткованого мінімуму зар­плати (85 грн.) треба записати до окремих клітинок.

Всі працівники розподілені на три категорії: робітники, служ­бовці, адміністрація. Робітники та службовці отримують премію у розмірі 10% від заробітку, а адміністрація – 15%. Скласти таблицю, яка містить такі дані: назва філії; прізвище працівника; категорія; заробітна плата; премія податок; одержати.

Назву філії та категорію працівника вибирати зі списків, створе­них в меню Данные – Проверка.

Визначити максимальну та мінімальну премію. Обчислити се­редній розмір податку та загальну суму заробітку.

Варіант 9

Фірма – постачальник продукції виконує замовлення чотирьох магазинів по таких групах товарів: побутова техніка, аудіоапаратура та відеоапаратура. Якщо замовлений товар не було поставлено у мага­зин протягом певної кількості днів після оплати (наприклад, за 3 дні), фірма – постачальник сплачує штрафи у розмірі 0, 1% від вартості товару за кожний день затримки.

Відсоток штрафу та договірну кількість днів поставки після оплати записати до окремих клітинок.

Назви груп товарів вибирати зі списку, створеного за допомо­гою меню Данные – Проверка. Встановити контроль введених даних для номеру магазину.

Скласти таблицю виконання заявок магазинів, що містить таку інформацію: номер магазину; група товару; назва товару; ціна за оди­ницю; замовлена кількість одиниць товару; дата оплати; дата постав­ки; сплачена сума; фактична кількість днів між поставкою та оплатою товару; штрафи.

Обчислити середню вартість одного замовлення, Підрахувати загальну вартість замовлених товарів та суму штрафів.

Варіант 10

У двох бригадах працюють 20 робітників. Всі вони мають одна­ковий щомісячний оклад. Кожного місяця відома нормативна кіль­кість робочих днів. Оплата здійснюється пропорційно кількості від­працьованих днів. Робітникам сплачуються такі надбавки за розряд:

ü за перший або другий розряд – не сплачується;

ü за третій розряд – 5% заробітку;

ü за четвертий розряд – 10% заробітку.

Нормативну кількість робочих днів у місяці, розмір місячного окладу та відсотки надбавок треба записати до окремих клітинок.

Розряд робітника вибирати зі списку, створеного за допомогою меню Данные – Проверка. Встановити контроль введених даних для номеру бригади або кількості відпрацьованих днів.

Скласти таблицю розрахунку заробітної плати, яка містить такі дані: номер бригади; прізвище робітника; розряд; кількість відпрацьо­ваних днів; зарплата без надбавок; надбавка; всього нараховано. Ви­значити середню кількість днів у місяці, відпрацьованих одним робіт­ником, мінімальну та загальну нараховану суму.

Варіант 11

Телефонна компанія об’єднує три АТС. Вартість міських та міжміських розмов підраховується окремо за різними тарифами. Крім того, для міжміських розмов існує пільговий тариф за розмови у свята та в нічний час. Розміри тарифів треба записати до окремих клітинок.

Скласти таблицю розрахунків за телефон, яка містить таку ін­формацію: номер АТС; номер телефону; прізвище абонента; тип роз­мов (міська, міжміська або з пільгами); тривалість розмов; нарахована плата за розмови.

Тип розмов вибирати зі списку, створеного в меню Данные – Проверка. Встановити контроль введених даних для номеру АТС або вибирати його зі списку.

Визначити максимальну та середню тривалість розмов. Обчис­лити загальну суму, сплачену за телефонні розмови.

Варіант 12

Оптова торговельна фірма реалізує автомобілі марок “Таврія”, “Нубіра” та “Леганза”. Якщо покупець оплачує вартість замовлених автомобілів протягом трьох днів із дня оформлення замовлення, він отримує знижку у розмірі 5% від вартості покупки; протягом семи днів – 3%. Скласти таблицю, яка містить таку інформацію: прізвище продавця; марка автомобіля; вартість однієї машини; кількість замов­лених автомобілів; дата замовлення; дата оплати; сума до сплати.

Вартість кожної марки автомобілів треба записати на Листі Excel в окремих клітинках та вставляти її до таблиці за допомогою функції ЕСЛИ. Марку автомобіля та прізвище продавця вибирати зі списку, створеного в меню Данные – Проверка. Встановити контроль введених даних для кількості замовлених автомобілів

Обчислити загальну кількість проданих машин та їх вартість з урахуванням знижок. Вказати середню та найбільшу кількість автомо­білів в одному замовленні.

Варіант 13

Житлове управління нараховує квартплату мешканцям трьох будинків. Відома вартість оплати за один квадратний метр та норма житлової площі для однієї людини. Якщо площа квартири перевищує норму для всієї родини більше, ніж, на 10 кв. м, то сплата за надмірну площу збільшується вдвічі. За інших умов квартплата нараховується пропорційно площі квартири.

Скласти таблицю, яка містить таку інформацію: номер будинку; номер квартири; прізвище мешканця; площа квартири; кількість меш­канців; нарахована квартплата.

Норми визначити самостійно, записати їх до клітинок і вико­ристовувати в розрахунках посилання на них. Встановити контроль введених даних для номеру будинку та кількості мешканців за допо­могою меню Данные – Проверка.

Підрахувати загальну та середню квартплату по всьому управ­лінню. Визначити максимальну кількість мешканців в одній квартирі.

Варіант 14

В торговельній фірмі працюють продавці, консультанти та ме­неджери. Щотижня фірма виплачує робітникам зарплату у розмірі 5% від виручки. Якщо товару продано більше, ніж на планову суму, то зарплата становить 7% від виручки. Робітники, які працюють у фірмі більше 5 років, отримують на 1% більше.

Посаду робітника вибирати зі списку, створеного в меню Данные – Проверка. Встановити контроль введених даних для поля стаж роботи.

Планову суму та відсотки для розрахунку зарплати визначити самостійно, записати їх до клітинок Excel.

Скласти таблицю, яка містить таку інформацію: прізвище робіт­ника; посада; стаж роботи; сума виручки; зарплата. Визначити най­більший та середній стаж роботи робітників і загальну суму зарплати.

Варіант 15

Компанія по постачанню води підприємствам нараховує плату за користування водою згідно тарифам:

ü 20 коп. за 1л за перші 100 літрів води та

ü 25 коп. за кожний наступний літр після 100 літрів.

Деякі клієнти мають пільги. Вони сплачують не 100% від варто­сті води, а менше, наприклад 30% або 50%. Створити таблицю, яка містить таку інформацію: район міста; підприємство; розмір пільг; по­переднє показання лічильника; останнє показання лічильника; спожи­та кількість води; розмір оплати.

Діючі тарифи та відсотки пільг записати до клітинок Excel. Район міста вибирати зі списку, створеного в меню Данные – Проверка. Встановити контроль введених даних для розміру пільг.

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

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

1. Які види даних можна заносити в клітини Excel? Що таке формат розміщення даних у клітинах?

2. Як дати імена клітинам і як ці імена потім використовують?

3. Розкажіть докладно про різні способи копіювання даних. Що відбувається з адресами при цьому? Що таке абсолютна адреса й що таке відносна адреса?

4. Робота з декількома листами робочої книги. Як змінити ім'я листа? Як використати це ім'я у формулах?

5. Правила запису формул.

6. Перелічите категорії функцій, приведіть приклади функцій.

7. Коли використовують функцію ЕСЛИ?

8. Як створити список, що розкривається, для введення даних?