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

Задача 1.1.

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

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

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

Задача 1.2.

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

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

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

Задача 1.3.

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

Відомо, що 1м³ холодної води коштує М₁ коп., 1м³ гарячої води коштує М₂ коп.

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

Задача 1.4.

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

Відомо, що для споживачів 1 категорії пільг сплата встановлена у розмірі N₁ коп. за 1м², для споживачів 2 категорії — N₂ коп. за 1м².

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

Задача 1.5.

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

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

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

Задача 1.6.

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

Підрахувати розмір премії, яка становить М% від окладу, якщо робітник відпрацював місячну кількість днів N, а в інших випадках розмір премії нараховується за формулою: [оклад/N]*(кількість відпрацьованих днів)*М.

Підрахувати загальну суму нарахувань, найбільшу та середню суму нарахувань.

Задача 1.7.

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

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

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

Задача 1.8.

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

Відомо, що робітники 1 розряду за кожну виготовлену деталь отримують N1 коп., 2 розряду — N2 коп., 3 розряду — N3 коп.

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

Задача 1.9.

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

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

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

Задача 1.10.

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

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

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

Задача 1.11.

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

Відомо, що 1 категорія сплачує за 1м² – N коп., 2 категорія – на К% нижче, а 3 категорія має 100% пільги.

Підрахувати сплату кожної сім’ї, визначити загальну плату.

Задача 1.12.

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

Відомо, що кожна хвилина міської розмови становить N₁ коп., міжміської — N₂ коп., міжнародної — N₃ коп.

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

Задача 1.13.

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

Відомо, що продовольчі товари мають три категорії. За один день зберігання продукти 1 категорії мають знижку на N₁% від початкової ціни, 2 категорії – N₂%, а третьої – N₃%.

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

Задача 1.14.

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

Відомо, що абонентська плата складає N крб., до якої входить плата за М хвилин міських розмов. Кожна розмова поза М хвилин міських розмов встановлює N₁ крб., міжміських — N₂ крб., міжнародних — N₃ крб.

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

Задача 1.15.

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

Відомо, що 2 категорії населення сплачують за ліки відповідно до типу (категорії) ліків різний відсоток від ціни. Так, хворі 1 категорії сплачують N₁₁% за ліки 1 типу, N₁₂% за ліки 2 типу; хворі 2 категорії сплачують N₂₁% за ліки 1 типу, N₂₂% за ліки 2 типу.

Підрахувати суму сплати за ліки кожним хворим та загальну суму знижок.

Завдання № 2.

Задача 2.1.

Створити й заповнити бланк товарного рахунку, наведений нижче.

Вантажовідправник та його адреси

Вантажоодержувач та його адреси

До Реєстру № Дата отримування «___»___________200__р.

РАХУНОК № 123 від 15.11.2005р.

Постачальник Торговий Дім „Роги та Копита”

Адреса: 243100, м.Кривий Ріг, вул. Пушкіна, 23

Р/рахунок № 45638078 у банку НАДРА, МФО 985435

Доповнення:

№	Найменування	Од. виміру	Кількість	Ціна	Сума
1
2
3
4
5
6
РАЗОМ

Керівник підприємства Сидоркин А.Ю.

Головний бухгалтер Іванченко П.Н.

Порядок виконання:

Виконання завдання найкраще розбити на три етапи:

1-ий етап. Створення таблиці бланка рахунку.

2-ий етап. Заповнення таблиці.

3-ий етап. Оформлення бланка.

1-ий етап.

1. Полягає в створенні таблиці. Основне завдання вмістити таблицю по ширині аркуша. Для цього треба попередньо встановити поля, розмір і орієнтацію паперу (Файл  Параметри сторінки…), виконавши команду Сервіс  Параметри..., у групі перемикачів Параметри вікна активізуйте перемикач Авторозбиття на сторінки (рис. 2.2).

Рис. 2.2. Параметри вікна.

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

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

№	Найменування	Од. виміру	Кількість	Ціна	Сума
1
2
3
4
5
6
РАЗОМ

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

• Підберіть ширину стовпців, змінюючи її за допомогою миші.

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

• Розграфіть таблицю, використовуючи лінії різної товщини. Зверніть увагу на те, що в останньому рядку п'ять сусідніх клітинок не мають внутрішнього обрамлення.

3. Найпростіше досягнути цього наступним шляхом:

• Виділити всю таблицю й установити рамку — "Контур" жирною лінією.

• Потім виділити всі рядки, крім останнього й установити рамку тонкою лінією "Праворуч", "Ліворуч", "Зверху", "Знизу".

• після цього виділити окремо праву клітинку нижнього рядка й установити для неї рамку "Ліворуч" тонкою лінією;

• залишиться виділити перший рядок таблиці й установити для неї рамку "Знизу" жирною лінією.

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

• На цьому етапі бажано виконати команду Файл  Попередній Перегляд, щоб переконатися, що таблиця цілком вміщається на аркуші по ширині й всі лінії обрамлення на потрібнім місці.

2-й етап.

1. Полягає в заповненні таблиці, використанні різних форматів числа.

• Заповніть стовпці "Найменування", "В" і "Ціна" на свій розсуд.

• Установіть грошовий формат числа в тих клітинках, у яких будуть розміщені суми й установіть необхідне число десяткових знаків, якщо вони взагалі потрібні.

2. У нашому випадку це порожні клітинки стовпців "Ціна" і "Сума". Їх потрібно виділити й виконати команду ФорматКомірок..., вибрати вкладку Число й вибрати категорію Грошовий (рис. 2.3). Це дасть вам поділ на тисячі, щоб зручніше було орієнтуватися у великих сумах.

• В

  Рис. 2.2.

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

• В

  Рис. 2.3. Формат комірок.

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

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

3-й етап.

1. Для оформлення рахунку вставте додаткові рядки перед таблицею.

Для цього виділіть декілька перших рядків таблиці й виконайте команду Вставка  Рядка. Вставиться стільки ж рядків, скільки ви виділили.

2. Наберіть необхідний текст до й після таблиці. Стежте за вирівнюванням.

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

Текст "РАХУНОК №" внесений в клітинку лівого стовпця, і застосоване вирівнювання по центру виділення (попередньо виділені клітинки одного рядка по всій ширині таблиці рахунку). Застосована рамка для цих клітинок зверху й знизу.

Вся інша текстова інформація до й після таблиці внесена в лівий стовпець, вирівнювання вліво.

3. Виконайте перегляд.

Задача 2.2.

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

Порядок виконання:

1. Нове поняття "абсолютне посилання" можна розглянути на конкретному прикладі. Підготуємо традиційну таблицю квадратів двозначних чисел (рис. 2.5), добре знайому кожному з курсу алгебри.

ТАБЛИЦЯ КВАДРАТІВ
	0	1	2	3	4	5	6	7	8	9
1	100	121	144	169	196	225	256	289	324	361
2	400	441	484	529	576	625	676	729	784	841
3	900	961	1024	1089	1156	1225	1296	1369	1444	1521
4	1600	1681	1764	1849	1936	2025	2116	2209	2304	2401
5	2500	2601	2704	2809	2916	3025	3136	3249	3364	3481
6	3600	3721	3844	3969	4096	4225	4356	4489	4624	4761
7	4900	5041	5184	5329	5476	5625	5776	5929	6084	6241
8	6400	6561	6724	6889	7056	7225	7396	7569	7744	7921
9	8100	8281	8464	8649	8836	9025	9216	9409	9604	9801

Рис. 2.5. Таблиця квадратів.

2. В клітинку A3 введіть число 1, в клітинку А4 — число 2, виділіть обидві клітинки й протягніть маркер виділення вниз, щоб заповнити стовпець числами від 1 до 9.

3. Аналогічно заповніть клітинки В2 — К2 числами від 0 до 9.

4. Коли ви заповнили рядок числами від 0 до 9, то всі необхідні вам для роботи клітинки одночасно не видно на екрані. Звузьте їх, але так, щоб всі стовпці мали однакову ширину (чого не можна домогтися, змінюючи ширину стовпців мишкою).

Для цього виділіть стовпці від А до К и виконайте команду Формат  Стовпець  Ширина..., у поле введення Ширина стовпця введіть значення, наприклад, 5.

5. Зрозуміло, що в клітинку В3 потрібно помістити формулу, що підносить до квадрата число, складене з десятків, зазначених у стовпці А і одиниць, що відповідають значенню, розміщеному в рядку 2. Таким чином, саме число, що повинне зводитися у квадрат в клітинці В3 можна задати формулою =А3*10+В2 (число десятків, помножене на десять плюсів число одиниць). Залишається звести це число у квадрат.

6. Спробуємо скористатисяМайстром функцій.

Для цього виділіть клітинку, в якій повинен розміститися результат обчислень (В3), і виконайте команду Вставка  Функція... (рис. 2.6).

7. Серед запропонованих категорій функцій виберіть Математичні  Ступінь  ОК.

8

Рис. 2.6. Майстер функцій.

. В наступному діалоговому вікні введіть число (основа ступеня) —А3*10+В2 і показник ступеня — 2. Так само, як і при наборі формули безпосередньо в клітинці електронної таблиці, немає необхідності вводити адреси кожної клітинки, на яку посилається формула, з клавіатури. Працюючи з Майстром функцій, досить указати мишею на відповідну клітинку електронної таблиці, і її адреса з'явиться в полі введення Число діалогового вікна. Вам залишиться ввести тільки арифметичні знаки (*, +) і число 10.

9. Якщо діалогове вікно загороджує потрібні клітинки електронної таблиці, перемістіть його вбік, схопивши мишею за заголовок. У цьому ж діалоговому вікні можна побачити значення самого числа (10) і результат обчислення ступеня (100).

Залишається тільки нажати кнопку Закінчити.

В клітинці В3 з'явився результат обчислень.

10. Хотілося б поширити цю формулу й на інші клітинки таблиці. Виділіть клітинку В3 і заповніть, простягнувши маркер виділення вправо, сусідні клітинки. Що відбулося? Чому результат не виправдав наших очікувань? В клітинці С3 не видно числа, тому що воно не міститься цілком в ній. Розширте мишею стовпець С. Число з'явилося на екрані, але воно явно не відповідає квадрату числа 11. Чому?

Справа в тому, що коли ми поширили формулу вправо, Excel автоматично змінив з урахуванням нашого зсуву адреси клітинок, на які посилається формула, і в клітинці С3 зводиться у квадрат не число 11, а число, обчислене по формулі = В3*10+32.

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

12. Для фіксування будь-якої позиції адреси клітинки перед нею ставлять знак $. Таким чином, поверніть ширину стовпця С у вихідне положення й виконайте наступні дії:

• Виділіть клітинку В3 і, встановивши текстовий курсор у рядок формул, виправте наявну формулу =СТУПІНЬ(А3*10+В2;2) на правильну =СТУПІНЬ($АЗ*10+В$2,2).

• Тепер, скориставшись послугами маркера заповнення, можна заповнити цією формулою всі вільні клітинки таблиці (спочатку простягнути маркер заповнення вправо, потім, не знімаючи виділення з отриманого блоку клітинок, вниз).

• Залишилося оформити таблицю: ввести в клітинку А1 заголовок, сформатувати його й відцентрувати по виділенню, виконати обрамлення таблиці й заповнення фоном окремих клітинок.

Задача 2.3.

Представте, що ви маєте власну фірму з продажу якої-небудь продукції й вам щодня доводиться роздруковувати прайс-лист з цінами на товари залежно від курсу долара. Підготуйте таблицю, що складається зі стовпців: "Найменування товару", "Еквівалент $ US", "Ціна в грн.". Заповніть всі стовпці, крім "Ціна в грн.". Стовпець "Найменування товару” заповніть текстовими даними (перелік товарів на ваш розсуд), а стовпець "Еквівалент $ US" числами (ціни в дол.).

Порядок виконання:

1. Зрозуміло, що в стовпці "Ціна в грн." повинна розміститися формула: "Еквівалент $ US"*Kypc долара".

Чому незручно в цій формулі множити на конкретне значення курсу? Тому, що при кожній зміні курсу, вам доведеться міняти свою формулу в кожній клітинці.

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

2. Як задавати абсолютні посилання, розглянуто вище, однак існує ще один зручний спосіб: посилатися не на адресу клітинки, а на ім'я, яке можна привласнити клітинці.

Виділіть клітинку, в яку буде вводитися курс долара (вище таблиці), введіть у неї значення курсу долара на сьогоднішній день і виконайте команду Вставка  Ім'я  Привласнити... (рис.2.7).

П

Рис. 2.7. Привласнення імені клітинки.

римітка: Ім'я може мати в довжину до 255 символів і містити букви, цифри, підкреслення (_), символи — зворотна коса риса (\), крапки й знаки питання. Однак перший символ повинен бути буквою, підкресленням (_) або символом зворотна коса риса (\). Не допускаються імена, які сприймаються як числа або посилання на клітинки.

3. У діалоговому вікні, що з'явилося, вам залишається тільки ввести ім'я клітинки (її точна адреса вже наведена в полі введення "Посилається на") і нажати кнопку ОК. Зверніть увагу на те, що в Полі імені, замість адреси клітинки, тепер розміщене її ім'я.

В клітинку, розташовану лівіше клітинки "Курс_долара", можна ввести текст "Курс долара".

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

Для цього виділіть верхню порожню клітинку стовпця "Ціна в гривнях" і введіть формулу в такий спосіб: введіть знак "=", потім клацніть мишею по клітинці, що розташована лівіше (в якій розміщена ціна в дол.), після цього введіть знак "*" і в списку, що розкривається, Поля імені виберіть мишею ім'я клітинки "Курс долара". Формула повинна виглядати приблизно так: =В7*Курс_долара.

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

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

7. Змініть ширину стовпців. Виділіть таблицю й задайте для неї обрамлення.

8. Закінчіть роботу. Оформіть звіти.

Задача 3.1.

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

Порядок виконання:

Розіб'ємо дану вправу на кілька завдань у логічній послідовності:

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

2. Заповнення таблиці даними традиційним способом та із застосуванням форми.

3. підбір даних за певною ознакою.