Завдання

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

 

1.2.1 Створення таблиці

Створимо заготовки 2-x таблиць рис. 1.2, виконуючи наступні операції:

 

 

Рисунок 1.2 – Створена таблиця

а) форматування рядків назви таблиць, використовуючи команду об’єднання клітин А1:С1, та A7:H7 і розміщення у центрі відповідного тексту;

б) заголовки таблиці „Відомість нарахувань” розміщені в двох рядках (8 та 9) таблиці, які об’єднанні для стовбців A , B , C , D , H ; для тексту використано Полужирный стиль шрифту, а текст „Податки” – по центру об’єднаного діапазону E8:G8 ;

в) для заголовків таблиць встановити формат згідно рис. 1.1;

г) зміна ширини стовпця (залежно від об’єму інформації, що вводиться);

д) встановлення формату „денежный” – у грн., для клітин з інформацією, що містить грошові одиниці. Краще це зробити до введення даних (виділити відповідні клітини та встановити для них формат „денежный”);

е) заповнення клітинок стовпця „№” послідовністю чисел 1, 2, …;

ж) введення формул у верхню клітину стовпців E , F , G , H таблиці „Відомість нарахувань”;

з) копіювання формул донизу по стовпчиках на всю таблицю.

Задамо пенсійний і профспілковий податки на рівні 2% та 1% від окладу відповідно. Для підрахунку розміру цих податків зручно ввести формулу в одну клітину (E10, F10) , а потім розповсюдити її на весь стовпчик. Головне не забути про абсолютні посилання, тому що значення відсотків від окладу відповідних податків задані в таблиці „Нормативно-довідкових даних”. Тобто посилання буде на клітини за межами таблиці, що створюємо.

Враховуючи це, маємо формули:

Е10=С10*$В$4 , F10=C10*$B$3 .

Після введення формул в клітини Е10 та F10 їх треба скопіювати донизу по стовпчику(протягуючи курсор заповнення).

Податок на прибуток залежить від наявності дітей у працівника і підраховуємо за формулою:

=С10*$B$5 ,

тобто, заданий відсоток від окладу, якщо працівник не має дітей,

і за формулою:

=(C10-$C$3-F10)*$B$5 ,

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

Тому в клітину G10 необхідно вести формулу за допомогою якої робиться перевірка умови на наявність дітей у працівника:

G10: =ЕСЛИ(D10=0 ; С10*$B$5 ; (C10-$C$3-F10)*$B$5) .

Формулу треба скопіювати донизу по стовпчику(протягуючи маркер заповнення).

Для визначення „Суми до видачі” залучимо формулу, яка підраховує різницю окладу і податків:

H10: = С1 -E10-F10-G10 .

Після введення формул в клітину H10 її треба скопіювати донизу по стовпчику;

и) заповнення таблиці відповідною текстовою та фіксованою числовою інформацією (стовпчики „ПІБ”, „Оклад”, „Кількість дітей”).

Вносимо інформацію в стовпчики „ПІБ”, „Оклад”, „Кількість дітей” після того, як уведені усі формули. Результат буде підраховуватися відразу після введення даних про оклад.

У завершальному вигляді таблиця буде мати вигляд, як на рис. 1.3 :

 

Рисунок 1.3 – Таблиця з даними

Після заповнення таблиці „Відомість нарахувань” можна додати рядок для підрахунку загальної суми нарахувань податків та суми до видачі .

 

1.2.2 Приклад створення діаграми по заданій таблиці

Побудуємо діаграму, що відображає нарахування кожному співробітнику. Для цього треба:

а) виділити два стовпця таблиці „Прізвище, ім’я по батькові” та „Сума до видачі” . Виділення стовпців, які не розміщені поряд, необхідно робити при натиснутій клавіші CTRL ;

б) запустити майстер діаграм одним зі способів: вибрати кнопку „Мастер диаграмм” – панелі інструментів, або команду меню Вставка→Диаграмма ;

в) рухаючись по крокам Мастера диаграмм, оберіть тип діаграми – круговая, объемная разрезанная, підписи данних (встановити прапорець долі).

Приблизний вигляд діаграми наведений на рис. 1.4.

 

 

Рисунок 1.4 – Діаграма „Сума до видачі”

г) для того, щоб перевірити, який існує зв’язок між таблицею та діаграмою додайте у середину таблиці новий рядок (виділити рядок таблиці та виконати Вставка→Строки ). Заповнити цей рядок даними на нового співробітника. Дані на нового співробітника автоматично з’являються в діаграмі.

 

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

 

1.3.1 Призначення всіх елементів вікна Excel.

1.3.2 Головне меню ЕТ Excel. Призначення яких пунктів меню Ви вивчили, виконуючи роботу?

1.3.3 Які види даних можна заносити до ЕТ? Що уявляє собою стандартний формат розміщення даних в клітинах ЕТ ?

1.3.4 Як надати імена клітинам та як їх потім використовувати?

1.3.5 В чому полягає процес модифікації формул у клітинах ЕТ?

1.3.6 Як виконати редагування даних в ЕТ ?

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

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

1.3.9 Які групи функцій існують в ЕТ? Назвіть декілька функцій з кожної групи.

 

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

Тарифи, норми, відсотки, планові дані у завданнях визначати самостійно та задавати їх в окремих, так званих нормативно-довідкових таблицях.

Даних у таблиці, яку потрібнос створити, повинно бути не менше 20 рядків .

Побудувати діаграму згідно наведеного прикладу.

 

1.4.1Житлове управління нараховує квартплату мешканцям за встановленими тарифами. Відомо, що 1м² площини у межах норми коштує Х коп., норма площини на одну людину встановлює S м², сім’ї, де норма проживання не витримана, сплачують на К% менше від нарахованої суми.

Скласти таблицю „Квартира” нарахування сплати за квартиру, яка містить інформацію: № квартири , її площина , кількість мешканців , площина на одного мешканця , сума до сплати .

 

1.4.2Відомо, що існує дві категорії споживачів електроенергії . Для першої категорії нарахування за спожиту електроенергію здійснюється за звичайним тарифом, тобто, по Х коп. за 1квт/г, для другої категорії є пільговий тариф – на У коп. менше тарифного.

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

 

1.4.3Компанія з водопостачання населенню встановила норми споживання на одну людину холодної води – N₁ м³, гарячої – N₂ м³. Відомо, що в межах норми 1м³ холодної води коштує М₁ коп., 1м³ гарячої води коштує М₂ коп. Поза нормою сплата за кожний м³ збільшується на К% , як для холодної так і для гарячої води.

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

 

1.4.4Житлове управління нараховує сплату за опалення мешканцям за встановленими тарифами. Існує дві категорії споживачів. Відомо, що для споживачів 1 категорії пільг сплата встановлена у розмірі N₁ коп. за 1м² , для споживачів 2 категорії – N₂ коп. за 1м² .

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

 

1.4.5Відомо, що норма споживання газу на одну людину становить Х м³ і сплата за 1м³ – У коп. Якщо спожитий газ перевищує норму, то сплата збільшується на У₁ коп. за кожний м³ газу, спожитого поза нормою.

Скласти таблицю „Газ” нарахування сплати за спожитий газ, яка містить інформацію: № квартири , кількість мешканців , попереднє показання лічильника газу , теперішнє показання лічильника газу , кількість спожитого газу .Підрахувати сплату за опалення кожним споживачем.

 

1.4.6На підприємстві у кінці кожного місяця працівникам нараховується додатково премія. Розмір премії становить М% від окладу, якщо робітник відпрацював місячну норму днів N , а в інших випадках розмір премії нараховується 2% за кожний робочий день.

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

 

1.4.7 Телефонна компанія надає послуги населенню за різними тарифами, враховуючи категорію абонента. Так для абонентів звичайної категорії тариф встановлено в розмірі N коп. за хвилину, пільговій категорії абонентів тариф встановлено на M коп. менше від звичайного тарифу.

Скласти таблицю „Розрахунки за телефон” , яка містить інформацію: прізвище абонента , категорія пільг , номер телефону , тривалість розмов(у хвилинах) , плата за розмови .

 

1.4.8 Житлове управління нараховує сплату за користування ліфтом мешканцями за встановленими тарифами. Існує дві категорії споживачів. Відомо, що для споживачів 1 категорії пільг сплата встановлена у розмірі N₁ коп. на одного мешканця, для споживачів 2 категорії – на N₂ коп. менше. Крім того, мешканці першого поверху за ліфт не сплачують.

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

 

1.4.9 Підприємство виготовляє деталі двох найменувань, на виготовлення яких існують різні норми та розцінки . Для працівників, які виготовляють ці деталі у разі перевищення норми встановлена додатково премія у розмірі N% від заробітку.

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

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

 

1.4.10 Для населення встановлена норма споживання електроенергії у розмірі – М кВт/г за місяць, за тарифом – N коп. за одиницю спожитої електроенергії. Відомо, що існує дві категорії споживачів електроенергії. Споживачам першої категорії сплата встановлена за звичайним тарифом, для другої категорії: за нормою споживання сплата становить 50% тарифу, поза нормою – за тарифом.

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

 

1.4.11 Цех виготовляє деталі одного типу. Відомо, що робітники 1 розряду за кожну виготовлену деталь отримують N₁ коп., 2 розряду – N₂ коп., 3 розряду – N₃ коп.

Скласти таблицю „Заробітна плата” нарахування заробітної платні робітникам, яка містить інформацію: прізвище робітника , розряд , кількість виготовлених деталей , сума до сплати .

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

 

1.4.12 Відомо, що мінімальний прожитковий мінімум становить Х крб. Якщо на одного члена сім’ї робітника приходиться менше прожиткового мінімуму, то матеріальна допомога на кожну дитину становить У крб.

Скласти таблицю „Допомога” нарахувань матеріальної допомоги робітникам, яка містить такі відомості: прізвище робітника , заробітну платню , кількість дітей , додатковий прибуток (наприклад, заробіток жінки, або чоловіка) , сума на одного члена сім’ї, розмір допомоги кожному робітнику.

1.4.13 Відомо, що податківець, у якого немає дітей, сплачує податки за бездітність в розмірі N₁ % від прибутку, у якого не більше 2-х дітей – N₂ % від доходу, сім’я, в якій 3 і більше дітей, звільняється від цього податку.

Скласти таблицю „Податок” нарахування податків за бездітність, яка містить відомості: прізвище податкоплатника , кількість дітей , прибуток , розмір податку за бездітність.

Визначити загальну суму податку , найбільший прибуток та його податок .

 

1.4.14 Туристична фірма на період шкільних канікул встановила додаткові пільги. Для дітей до п’ятирічного віку встановлені знижки у розмірі 70 % від вартості путівки, до десятирічного віку 50 % , старші діти пільг не мають.

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

 

1.4.15 Продовольчий склад під час поточної інвентаризації для продуктів першої категорії встановив слідуючи нормативи: якщо термін зберігання не закінчився, ціна зменшується на 30% , якщо товар прострочений – його списують . Для продуктів другої категорії: якщо термін зберігання не закінчився , ціна зменшується на 50 % , якщо товар прострочений – його списують.

Скласти таблицю „Нова ціна”, яка містить відомості: назва продукту , категорія , ціна за одиницю , дата зберігання , нова ціна (в цій графі вказувати ціну , або інформацію про списання).

 

1.4.16 Відомо, що абонентська плата за телефон складає N крб. за місяць, до якої входить плата за М хвилин міських розмов. Кожна хвилина розмов поза норму встановлює N₁ коп. Воїни-ветерани сплачують по тарифу тільки розмови поза нормою.

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

1.4.17 Аптекоуправління встановило дві категорії населення і два типи ліків. Хворі 1 категорії сплачують N₁₁ % за ліки 1 типу , N₁₂ % за ліки 2 типу ; хворі 2 категорії сплачують N₂₁ % за ліки 1 типу , N₂₂ % за ліки 2 типу .

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