Створення електронної таблиці "Відомість нарахування премії за 1 квартал 2010 року"

Курсова робота
з дисципліни "Інформатика та КТ"
на тему
«СТВОРЕННЯ ЕЛЕКРОННОЇ ТАБЛИЦІ»
«Відомість нарахування премії за 1 квартал 2010року»

Зміст
Вступ
1. Формалізація
2. Структура електронної таблиці
3. Опис організації елекронних таблиць
3.1 Заповнення таблиці
3.2 Використання в таблиці стандартних функцій
3.3 Форматування таблиці
3.4 Сортування даних
3.5 Вибірка даних за заданими умовами
3.6 Підведення проміжних підсумків
3.7 Процес консолідації робочих листів
3.8 Технологія побудови графіків
4. Інструкція з експлуатації таблиці
Вступ
 
Електроннатаблиця (ЕТ) — це програма для обробки даних, поданиху вигляді таблиці.
Так само як ізвичайна таблиця, вона складається з комірок. Кожна комірка має свою адресу, щоскладається з номера стовпця номера рядка, на місці перетину яких вонаміститься. Сучасні ЕТ створюють можливість працювати з величезною кількістю — мільйонами — комірок і обробляти дуже великі обсяги інформації. Через це їхтакож називають табличними процесорами.
Частина коміроктаблиці знаходиться перед користувачем на екрані дисплея. В ці коміркикористувач заносить текст, дані, формули для розрахунків. Під управліннямпрограми ЕТ комп'ютер вмиті виконує ці обчислення і заносить результати увідповідні комірки При зміні даних у яких-небудь комірках автоматичноперераховуються результати в усіх формулах, що містять адреси цих комірок Запотреби, користувач редагує таблицю: видаляє або вставляє рядки і стовпці,переносить блоки комірок, змінює їх вміст. Програма ЕТ забезпечує автоматичнийконтроль за правильною зміною адрес комірок у формулах при редагуванні таблиці.
ЕТ дає змогусортувати рядки у визначеному користувачем порядку, будувати графіки і діаграмиза даними таблиці, друкувати на папері всю таблицю або її частину, зберігатитаблицю з графіками та діаграмами на диску для подальшого використання.
Електроннимитаблицями користуватися просто, вони потребують стандартного набору пристроївкомп'ютера і забезпечують швидкість та надійність обчислень. Найчастіше вонизастосовуються у фінансовій діяльності для автоматизації банківських ібухгалтерських обчислень.
Перша електроннатаблиця з'явилася в 1979 р. її створили випускники Массачусетськоготехнологічного інституту Ден Бриклін і Боб Фрепстон. Вона називалася VisiCalc(від англ. — видимий калькулятор).
Сучасні технологіїобробки інформації часто приводять до того, що виникає потреба подання даних увигляді таблиць.
Різноманітніфінансові документи (відомості на заробітну плату, табель робочого часу,інвентаризаційні описи, квартальні звіти, складський облік матеріалів тощо) тежмають вигляд таблиць. Для табличних обчислень характерні відносно простіформули, по яким проводяться обчислення, і великі об’єми вхідних даних. Длятаких розрахунків слід використовувати комп’ютер. З цією метою були створеніелектронні таблиці (табличні процесори) — це прикладне програмне забезпеченняспільного призначення, яке використовується для обробки даних, представлених утабличній формі.
Електронна таблиця(ЕТ) дозволяє зберігати в табличній формі велику кількість вхідних даних,результатів, а також зв’язків (алгебраїчних і логічних співвідношень) між ними.При зміні вхідних даних всі результати автоматично перераховуються і заносятьсяв таблицю. Такі таблиці можна переглядати, змінювати, записувати на носіїзовнішньої пам’яті для зберігання, роздруковувати на принтері.
Головна перевагаЕТ — можливість миттєвого перерахунку усіх даних, пов’язаних формульнимизалежностями, при зміні значення будь-якого компоненту таблиці.
Табличний процесорMS Excel дозволяє:
Розв'язуватиматематичні задачі: виконувати різноманітні табличні обчислення, обчислюватизначення функцій, будувати графіки і діаграми та ін.;
Виконуватичисельне дослідження;
Проводитистатистичний аналіз;
Реалізовуватифункції бази даних — введення, пошук, сортування, фільтрацію і аналіз даних;
Встановлюватизахист на окремі фрагменти таблиць;
Нагляднопредставляти дані у вигляді діаграм та графіків;
Вводити іредагувати тексти;
Виконувати обмінданих з іншими програмами, наприклад, вставляти текст, малюнки, таблиці,зроблені в інших додатках.
Документелектронної таблиці називається робочою книгою. У кожному документі можерозміщуватися від 1 до 255 таблиць — робочих аркушів. Документ Excelзберігається у файлі з будь-яким ім’ям і розширенням. xls.
В даній курсовійроботі за допомогою табличного процесора Excel проведені розрахунки длявідомості нарахування заробітної плати.
1. Формалізація
Використаємо такіумовні позначення:Опис Позначення Математична формула  Категорія  К К=1, 2, 3, 4, 5 Посада (Керівники)  ПК Посада (Службовці)  ПСЛ Посада (Спеціалісти)  ПСП Посада (Робітники зі шкідливими умовами праці)  ПРШУМ Посада (Робітники з нормальними умовами праці)  ПРНУП % премії (Керівники)  ВПК ВПК = 70% % премії (Службовці)  ВПСЛ ВПСЛ = 60% % премії (Спеціалісти)  ВПСП ВПСП = 65% % премії (Робітники зі шкідливими умовами праці)  ВПРШУП ВПРШУП = 55% % премії (Робітники з нормальними умовами праці)  ВПРНУП ВПРНУП = 40% Посада  П Нарахована з/плата за окладом (1)  НЗПО (1) Нарахована з/плата за окладом (2)  НЗПО (2) Нарахована з/плата за окладом (3)  НЗПО (3) Всього нараховано з/п за квартал  ВНЗПК
/> КТУ  КТУ Умовна з/плата  УЗП УЗП = ВНЗПК * КТУ % нарахування премії  ВНП Нараховано премії  НП НП = УЗП * ВНП Підсумкові функції Опис Позначення Математична формула
Усього за графою
Нарахована з/плата за окладом (1) УНЗПО (1)
/>
Усього за графою
Нарахована з/плата за окладом (2) УНЗПО (2)
/>
Усього за графою
Нарахована з/плата за окладом (3) УНЗПО (3)
/>
Усього за графою
Всього нараховано з/п за квартал УВНЗПК
/>
Усього за графою
Умовна з/плата УУЗП
/>
Усього за графою
Нараховано премії УНП
 />
Максимальне за графою
Нарахована з/плата за окладом (1) Max НЗПО (1)
/>, i = 1÷ n
Максимальне за графою
Нарахована з/плата за окладом (2) Max НЗПО (2)
/>, i = 1÷ n
Максимальне за графою
Нарахована з/плата за окладом (3) Max НЗПО (3)
/>, i = 1÷ n
Максимальне за графою
Нараховано премії Max НП
/>,
i = 1÷ n
Середнє за графою
Нарахована з/плата за окладом (1) СрНЗПО (1)
/>
Середнє за графою
Нарахована з/плата за окладом (2) СрНЗПО (2)
/>
Середнє за графою
Нарахована з/плата за окладом (3) СрНЗПО (3)
/>
Середнє за графою
Нараховано премії СрНП
/>
2. />Структура електронної таблиціПоряд-ковий номер Назва графи Тип даних Адреса клітин
Позначення і
математична формула Табличний процесор 1 Відомість нарахування премії за 1 квартал 2010 року текст D1 " Відомість нарахування премії за 1 квартал 2010 року " 2 Минулий квартал поточного року формула E2 =«за 1 квартал „&ГОД (СЕГОДНЯ ()) &“ року» 3 Категорія текст H4 ‘Категорія число H5: H9 K Вихідні дані 4 Посада текст J4 ‘Посада текст J5: J9 П Вихідні дані

5 % премії текст K4 ‘% премії число K5: K9 ВП Вихідні дані 6 Нумерація граф число A11: L11 1, 2, …, 12 7 Порядковий номер текст А10 Порядковий номер число A12: A26 1, 2, …, 15 8 П.І.П. текст В10 ‘П.І.П. текст B12: B26 Вихідні дані 9 Посада текст С10 ‘Посада число C12: C26 П
=ЕСЛИ (D12=$H$5; $I$5; ЕСЛИ (D12=$H$6; $I$6; ЕСЛИ (D12=$H$7; $I$7; $I$8))
…
=ЕСЛИ (D26=$H$5; $I$5; ЕСЛИ (D26=$H$6; $I$6; ЕСЛИ (D26=$H$7; $I$7; $I$8))) 10 Категорія текст D10 ‘Категорія число D12: D26 К Вихідні дані 11 Нарахована з/плата за окладом (1) текст E9: Е10 ‘Нарахована з/плата за окладом (1), грн число E12: E26 НЗПО (1) Вихідні дані 12 Нарахована з/плата за окладом (2) текст F9: F10 ‘Нарахована з/плата за окладом (2), грн число F7: F22 НЗПО (2) Вихідні дані 13 Нарахована з/плата за окладом (3) текст G9: G10 ‘Нарахована з/плата за окладом (3), грн формула G7: G22 НЗПО (3) Вихідні дані 14 Всього нараховано з/п за квартал текст H10 ‘Всього нараховано з/п за квартал, грн формула H12: H26
 />
=СУММ (E12: G12)
…
=СУММ (E26: G26) 15 КТУ текст I10 ‘КТУ формула I12: I26 КТУ Вихідні дані 16 Умовна з/плата текст J10 ‘Умовна з/плата, грн формула J12: J26  УЗП = ВНЗПК * КТУ
=ПРОИЗВЕД (H12; I12)
…
=ПРОИЗВЕД (H26; I26) 17 % нарахування премії текст K: 10 ‘% нарахування премії формула K12: K26 ВНП
=ЕСЛИ (D12=$H$5; $K$5; ЕСЛИ (D12=$H$6; $K$6; ЕСЛИ (D12=$H$7; $K$7; ЕСЛИ (D12=$H$8; $K$8; ЕСЛИ (D12=$H$9; $K$9)))))
…
=ЕСЛИ (D26=$H$5; $K$5; ЕСЛИ (D26=$H$6; $K$6; ЕСЛИ (D26=$H$7; $K$7; ЕСЛИ (D26=$H$8; $K$8; ЕСЛИ (D26=$H$9; $K$9))))) 18 Нараховано премії текст L: 10 ‘Нараховано премії, грн формула L12: L26 НП = УЗП * ВНП
=ПРОИЗВЕД (J12; K12)
…
=ПРОИЗВЕД (J26; K26) 19 Всього текст A27: D27 ‘Всього формула
E27
F27
G27
H27
J27
L27
/>
/>
/>
/>
/>
/>
=СУММ (E12: E26)
=СУММ (F12: F26)
=СУММ (G12: G26)
=СУММ (E27: G27)
=СУММ (J12: J26)
=СУММ (L12: L26) 20 Максимальне текст A28: D28 ‘Максимальне формула
E28
F28
G28
L28
/>, i = 1÷ n
/>, i = 1÷ n
/>, i = 1÷ n
/>,
i = 1÷ n
=МАКС (E12: E26)
=МАКС (F12: F26)
=МАКС (G12: G26)
=МАКС (L12: L26) 21 Середнє текст A29: D29 ‘Середнє формула
E29
F29
G29
L29
/>
/>
/>
/>
=СРЗНАЧ (E12: E26)
=СРЗНАЧ (F12: F26)
=СРЗНАЧ (G12: G26)
=СРЗНАЧ (L12: L26)
3. Опис організації елекроннихтаблиць
Таблицю сформуватитаким чином:
1) Установити глобальні режими параметрів: стандарти мови, числа, дати, роздільникцілої і дробової частин числа тощо.
Пуск→Настройка→Панельуправления→Язык и региональные стандарты →… (числа,денежная единица, дата).
2) Передзаповненням створити структуру електронної таблиці, в якій зазначити, де та щорозміщено.
4) Завантажититабличний процесор MS Excel одним із способів:
Пуск → Всепрограммы → Microsoft Office → Microsoft Office Excel 2003;
за допомогоюярлика /> наробочому столі — подвійне клацання лівою кнопкою миші;
за допомогоюярлика /> напанелі швидкого запуску — одне клацання лівою кнопкою миші;
тощо.
5) Заповнити івідформатувати клітини таблиці відповідно до поставленого завдання.
Створити новуробочу книгу:
Файл →Створити… → Нова книга
або на стандартнійпанелі інструментів натиснути />.
Аркуші можнадодати за допомогою операції Вставка®Аркуш. Змінити назву аркуша можна за допомогою:
контекстного менюярлика аркуша;
Формат →Аркуш → Перейменувати;
подвійного натискання на ярлику листка, задати потрібну назву відповідно допоставленого завдання: «Відомість нарахування заробітної плати за 1квартал 2010 року»
Оформлення роботи
Налагодити глобальнірежими параметрів: стандарти мови, числа, дати, роздільник цілої і дробовоїчастин числа тощо.
Пуск → Настройка→ Панель управления → Язык и стандарты →… (Числа,Денежная единица, Дата)
Установити наробочому аркуші параметри сторінки:
Меню Файл ® Параметри сторінки® закладка“Сторінка": Розмір паперу А4 Орієнтація Альбомна Масштаб ¤ Гранична кількість сторінок 1 у ширину й 1 у висоту
Закладка “Поля”Верхнє, нижнє 2,5 Ліве, праве 2 Верхнього колонтитула 1,3 Нижнього колонтитула 1,3 Центрувати на сторінці
R горизонтально
R вертикально
Нижній колонтитул:
Вигляд®Колонтитули ®“Створити нижній колонтитул” з інформацією (визначається автоматично): Зліва Системна дата
/> У центрі Файл
/> Справа Лист
/>
Установити режимвідображення таблиці із заголовками рядків та стовпців.
Файл ® Параметри сторінки® закладка Аркуш®
/>RЗаголовки рядків і стовпців
Створити структурутаблиці, у якій зазначено, де та що розташовано.
Вихідні дані розрахункової таблиці повинні відображати реальні співвідношенняі підібрані таким чином, щоб можна було виконати всі операції, передбаченізавданням (вибірку даних, проміжні підсумки, консолідацію).
Перед заповненнямзаголовків таблиці установити формат: меню Формат ® Клітинки ® Вирівнювання.Назначити вирівнювання по горизонталі та по вертикалі по центрута зафіксувати відображення R переносити по словах.
Заповненнірозрахункову таблицю в формулах, де треба ввести значення з фіксованої клітини,застосувати посилання на абсолютні адреси.
Заповнення формулбажано виконувати в такому порядку.
Спочатку заповнититільки перший рядок інформаційної частини таблиці.
Перевіритирезультати виконання формул на контрольному прикладі.
Для заповненняформулами наступних рядків скористатися операцією копіювання діапазону клітин зформулами.
Ввести вихіднідані.
Переглянутикінцеві результати копіювання та уважно їх проаналізувати (звернути увагу на відмінністькопіювання абсолютних адрес та відносних).
Для організаціїнаступних аркушів скопіювати Розр_Горобець з розрахунковою таблицею необхіднукількість разів, використовуючи один із методів: протягування ярлика листка іззатиснутою клавішею Ctrl; контекстне меню; меню Правка.
Перейменуватикожен аркуш відповідно до вимог.
Для відображеннятаблиці у формульному (розрахунковому) вигляді необхідно вибрати пунктменю
Сервіс ® Параметри ® Вигляд ® R активізувати пункт Формули(зняти прапорець з пункту Формули).
Грошові данібажано відобразити з точністю до двох знаків після коми (до копійок) уфінансовому форматі.
Результати розширеногофільтра розмістити в іншій області таблиці на тому самому листку.
Текст курсовоїроботи оформляти в текстовому редакторі Microsoft Word.
Для стилюосновного тексту (однойменний стиль) рекомендуються такі параметри: Формат шрифту Шрифт Times New Roman Накреслення Звичайний Розмір 14  Формат абзацу Перший рядок Відступ на 1,27 см Вирівнювання За шириною Інтервал перед і після Міжрядковий інтервал 1 (або 1,5)  Параметри сторінки: Поля Верхнє, нижнє 2,5 см Ліве 2 см Праве 2 см
Налагодити автоматичнийперенос слів:
Сервіс ® Мова ® Розставленняпереносів ® Автоматичне розставлення переносів.
Використовуватидля виділення окремих фрагментів тексту напівжирний шрифт і курсив.
Курсова роботароздруковується на чорно-білому (можна кольоровому) принтері (не можна здаватироботи з нечітким або розмазаним текстом) на білому папері формату А4(210х297мм).
Текст, таблиці йінші матеріали курсової роботи розміщувати тільки на лицьовому боцікожного аркуша.
Всі сторінкиповинні мати наскрізну нумерацію внизу сторінки справа. Першоюсторінкою є титульний аркуш, але номер на ньому не повинен відображатися. Розміршрифту номера сторінки — 12.
На сторінці підзаголовком “Зміст” розмістити зміст роботи з проставленими номерамисторінок. При цьому зміст повинен відповідати зазначеним по тексту заголовкамскладових частин роботи й номерам сторінок, на яких вони починаються. Змістповинен бути сформований автоматично з використанням можливостей текстовогопроцесора.
Меню Вставка ® Посилання ® Зміст і покажчики®
Закладка Зміст® Рівні(можна 2, 3, …)
(щоб заголовок“Зміст” не потрапив у список змісту, до нього не можна застосовувати стильзаголовка).
В курсовій роботібажано розмістити у верхніх колонтитулах сторінок прізвище, ім’я та побатькові студента, групу, номер варіанта або назву поточного розділу.
У текстідопускаються тільки загальноприйняті скорочення слів.
При посиланні утексті на літературні джерела в дужках зазначити порядковий номер джерела, щовідповідає списку використаної літератури із зазначенням сторінок.
Заголовкирозділів, підрозділів, таблиць та ілюстрацій повинні бути обов'язково оформленіз використанням стилів. Наприкінці назв крапки ставити не треба, за виняткомумовних скорочень і позначень.
Кожний розділпочинається з нової сторінки, підрозділи починати з нової сторінки не потрібно.
Параметри стилю заголовківрозділів (Заголовок 1):  Формат шрифту Шрифт Times New Roman Накреслення Напівжирний Розмір 16 Видозміна Усі великі Інтервал Розріджений на 0,1 пт  Формат абзацу Вирівнювання По центру Інтервал перед Інтервал після 6 пт Розташування на сторінці
— з нової сторінки;
заборонити автоматичний перенос слів  Формат нумерації
 Багаторівневий список заголовків
(1 Заголовок 1; 1.1 Заголовок 2; 1.1.1 Заголовок 3)
Примітка. Длярозділів “Вступ”, “Висновки”, “Використана література” і “Додатки”після застосування до них стилю заголовок 1 необхідно виключити форматнумерації.
Параметри стилю заголовківпідрозділів (Заголовок 2):  Формат шрифту Шрифт Times New Roman Накреслення Напівжирний Розмір 14 Інтервал Розріджений на 0,1 пт  Формат абзацу Вирівнювання по центру Інтервал перед і після 6 пт Розташування на сторінці
— не відривати від наступного
заборонити автоматичний перенос слів  Формат нумерації
 Багаторівневий список заголовків
(1 Заголовок 1; 1.1 Заголовок 2; 1.1.1 Заголовок 3)
Таблиці йілюстрації (схеми, графіки, рисунки тощо) розміщують після першого згадуванняпро них в тексті. В разі необхідності допускається їхній перенос на наступнусторінку після згадування по тексту з обов’язковим посиланням. Посиланняоформляються за допомогою вставки перехресних посилань на постійнучастину й номер назви. Постійна частина назви рисунків та таблицьвставляється за допомогою засобів текстового процесора, знак номера (№) невикористовується.
Під рисункамиповинні розміщуватися їхні назви з наскрізною нумерацією.
Приклад назвирисунка:
Рисунок 1 — Вікнотабличного процесора Excel
Таблиці повиннібути наочними й обрамленими з усіх боків і усередині. Розмір шрифту в таблицяхповинен бути не менше 10 і не більше 14пт. Над таблицями необхіднорозташовувати назви, які повинні мати наскрізну нумерацію.
 
Прикладтабличного заголовка:
Таблиця 3 — Формульний вигляд
Таблиці, якізаймають більше однієї сторінки, повинні переноситися на наступну з додаваннямпід голівкою таблиці й на початку кожної наступної сторінки таблиці, щорозривається, рядка з порядковою нумерацією стовпців. Голівка в таблиціробиться один раз, але на кожній наступній сторінці перед продовженням таблицінеобхідно писати заголовок у такий спосіб:
Продовження табл.3
Наприкінці роботинаводиться список літератури, в якому джерела розміщені у порядку їхвикористання. Список літератури варто оформляти відповідно до Держстандарту(додаток Г) із застосуванням формату нумерованого списку.
В кінці роботидодати два чистих аркуші для рецензій викладача. Після висновків роботапідписується автором, ставиться дата.
Після отриманняперевіреної роботи в неї в жодному разі не можна вносити ніяких змін (вилучатиаркуші із зауваженнями керівника і т.п.).
Виправленіфрагменти з посиланнями на сторінки, де були зроблені зауваження керівником,варто оформити й роздрукувати на окремих аркушах і підшити до початковоговаріанта роботи.3.1 Заповнення таблиці
 
Вводять дані (текст або числа) безпосередньо в поточну клітину або в рядокформул, що розміщується у верхній частині вікна програми під панелямиінструментів.
Місце введеннявідмічається текстовим курсором. Якщо почати введення з алфавітно-цифровихклавіш, дані з поточної клітини заміняються текстом, що вводиться.
Дані, яківводяться, відображаються як в клітині, так і в рядку формул.
Для завершеннявведення даних використовують клавішу ENTER на клавіатурі або кнопку /> Enter врядку формул.
/>
Число, на відмінувід довільного тексту, у своєму записі використовує обмежений набір символів
0 1 2 3 4 5 6 7 89 + — ,. р.% Е е.
Основна різницячисла від тексту полягає в тому, що з числом можна виконувати в подальшомурізні дії (наприклад, арифметичні операції), а з текстом ні.
Число післявведення автоматично вирівнюється по правому краю клітини.
Якщо число невміщується в ширину стовпця, то на екрані з’являється ######.
При введенні довгоготексту на екрані буде видно тільки ту частину тексту, якапоміщається до першої занятої справа клітини, навіть якщо там стоїтьзнак пробілу (пропуску).
В рядку формулвидно повний текст.
Щоб відмінитивнесені зміни і відновити колишнє значення клітини, використовують клавішу Escабо кнопку /> Скасування в рядку формул.
Для очищенняпоточної клітини або виділеного діапазону найпростіше використовувати клавішу Delete.
Наприклад, длявведення заголовка таблиці в роботі виділили клітини D1: I1, об’єднали і ввелитекст “Відомість нарахування премії ”.
Редагування даних
Для редагуванняданих (внесення змін, додавання і т.д.), введених раніше в клітину, її потрібнопопередньо виділити (активізувати).
В рядку формулз’явиться вміст клітини. Потім необхідно текстовий курсор перевести в рядокформул і почати редагування
або
натиснути F2
або
подвійнеклацання лівою кнопкою миші по клітині, що підлягаєредагуванню. В клітині з’явиться її вміст, і потім його можна буде редагувати.
Закінчуєтьсяредагування, як і введення.
Enter — закінчення редагування і у залежності від настройок або залишаємося увихідній клітині, або переходимо до однієї із сусідніх клітин.
Кінець редагуваннятакож відбувається, якщо натиснути лівою кнопкою на іншій клітині (одночасновона активізується).
Якщо застарі даніпотрібно повністю замінити, то можна відразу вводити нові, не переходячи дорядка формул (старі дані автоматично будуть знищені). Фактично це нередагування, а нове введення.
В розрахунковійтаблиці
Формуємо заголовоктаблиці;
автоматичнозаповнюємо порядковий номер (А11: А26) і рядок нумерації (А11: L11);
заповнюємопочатковими даними діапазони клітин B12: B26, D12: D26, Е12: Е26, F12: F26,G12: G26, I12: I26 (див. Структура таблиці).
Обрамуваннятаблиці
Спочатку виділимоблоком необхідний діапазон, потім активуємо піктограм
“Границя” і увікні границь виберемо необхідний вигляд контуру виділеного блоку клітин.
/>
В даній задачібули використані такі типи границь: Порядковий номер Діапазон Формат 1 H4: K8
Зовнішня границя — потовщена (/>) 2 E9: K9
Зовнішня границя — потовщена (/>) 3 А10: L10
Зовнішня границя — потовщена (/>) 4 A11: L26
Зовнішня границя — потовщена (/>) 5 A27: L27, A28: L28, A29: L29
Зовнішня границя — потовщена (/>) 6 A12: L26
Внутрішня границя — тонка (/>). 7 H5: K8
Внутрішня границя — тонка (/>).
Внесення формул утаблицю та їх копіювання.
Заповнюємоформулами клітини C12, H12, J12, K12, L12.
Наприклад, щобобчислити в клітині H12 Всього нараховано з/п за квартал, потрібно їїактивізувати і ввести =СУММ (E12: G12).
Аналогічновводяться інші формули для обчислення:Адреса клітини  Формула C12 =ЕСЛИ (D12=$H$5; $I$5; ЕСЛИ (D12=$H$6; $I$6; ЕСЛИ (D12=$H$7; $I$7; $I$8))) H12 =СУММ (E12: G12) J12 =ПРОИЗВЕД (H12; I12) K12 =ЕСЛИ (D12=$H$5; $K$5; ЕСЛИ (D12=$H$6; $K$6; ЕСЛИ (D12=$H$7; $K$7; ЕСЛИ (D12=$H$8; $K$8; ЕСЛИ (D12=$H$9; $K$9))))) L12 =ПРОИЗВЕД (J12; K12)
Копіювання формул:
Копіюємо формулирядка 12 в рядки 13-26.
Примітка.
Для копіюванняформул скористаємося маркером заповнення (він являє собою маленький чорнийквадрат в нижньому правому куту виділеної клітини або діапазону). Потрібновиділити клітину, формулу якої необхідно скопіювати, клацнути мишею на цьомуквадраті і перемістити покажчик в кінець діапазону, який заповнюється. Прицьому вміст першої клітини буде скопійовано у весь виділений діапазон.3.2 Використання в таблицістандартних функцій
У роботі буливикористані такі функції: календарні функції ГОД, СЕГОДНЯ, логічна ЕСЛИ тапідсумкові СУММ, МАКС, СРЗНАЧ, ПРОИЗВЕД.
Наприклад, щобзнайти суму діапазону H12: H26, активізуємо клітину H27, натискаємо настандартній панелі інструментів значок автосуми />, в клітині з’являється =SUM (H12:H26). Оскільки запропонований діапазон нас не задовольняє, виправляємопротягуванням миші на (H12: H26) і натискаємо Enter.
Введення в клітинуD24 функції для обчислення середнього значення діапазону.
Активізувати E29.
Натиснути накнопці Вставка функції /> на стандартній панеліінструментів.
У списку Категоріявибрати пункт Статистичні.
У списку функційвибрати функцію СРЗНАЧ і натиснути на кнопці ОК.
Виділити потрібнийдіапазон (Е13: Е26) методом протягування і натиснути клавішу ENTER.
Аналогічно вклітину E28 вводимо функцію = MAX (E12: E26) для обчислення максимальногозначення діапазону (E12: E26).3.3 Форматування таблиці
1) Зміна ширинистовпця:
Переміщуємо правуграницю поточного (вибраного) заголовка стовпця до того часу, поки його ширинане досягне необхідного розміру.
2) Форматуваннятаблиці:
Введений текствирівнюється автоматично в клітині по лівому краю, числа — по правому краю. Задопомогою форматування встановлюються необхідні параметри. Формат числа,призначений для клітині, визначає спосіб перегляду числа на аркуші.
Наприклад, длявирівнювання голівки таблиці по центру необхідно її виділити (A10: L10) іскористатися на панелі інструментів Формат / Клітинки… / вибрати вкладкуВирівнювання і у вікні команди встановити: положення “По горизонталі — поцентру", “По вертикалі — по центру” і поставити прапорець на “Відображення- переносити по словах”.
/>
3) Збереженняелектронної таблиці.
Аркуш зпобудованою розрахунковою таблицею перейменувати “Розр_Горобець”(додаток А). Зберегти файл
а) під новимім’ям:
Файл →Зберегти як… Диск А: / №3_Горобець. xls
б) під тим самиміменем:
Файл ®Зберегти,
CTRL/S
на стандартнійпанелі інструментів />.
4) Перетвореннятаблиці у формульний вигляд.
Для організаціїнаступних аркушів робочої книги треба скопіювати аркуш “Розр_Garmash"необхідну кількість разів.
Для перетвореннятаблиці у формульний вигляд необхідно вибрати пункт меню Сервіс ® Параметри… ® вибрати вкладкуВигляд і встановити прапорець у команді Параметри вікна напроти Формули.
/>
Таблицю,представлену у формульному вигляді, зберегти на аркуші “Форм_Горобець”(додаток Б) у файлі під тим самим іменем.3.4 Сортування даних
Завдання:
У аркуші “Сорт_Горобець”виконати сортування таблиці за 3 ключами:
«Категорія»у порядку зростання.
«П.І.П.»за абеткою.
«Нарахованопремії» у порядку зростання.
Етапи сортування:
Скопіювати аркуш “Розр_Горобець”методом протягування ярлика аркуша та перейменувати новий аркуш “Розр_Горобець(2) ” на “Сорт_Горобець”.
Визначити, що єзаголовком таблиці. У даному випадку — це рядок (10).
Приховати рядокнумерації (11): виділити заголовок рядка ® контекстне меню®Приховати.
Виділити діапазонВ10: L26.
Виконати дії: Дані®Сортування.
В діалоговомувікні “Сортування" встановити відповідні ключі та параметри:
/>/>
Натиснути ОК.
Відсортованутаблицю зберегти на аркуші “Сорт_Горобець” (додаток В) у файлі під тимсамим іменем.3.5 Вибірка даних за заданими умовами
Авто фільтр.
Завдання
На аркуші “Фільтр-А_Прізвище”відібрати записи, для яких Посада починається з літери «К» таНараховано з/п за квартал білише за 7200 грн.
Етапи виконання:
Скопіювати аркуш “Розр_Горобець”та перейменувати на “Фільтр-А_Горобець".
Вибрати якзаголовок таблиці рядок нумерації (11).
Виділити діапазонА11: L26.
Виконати дії: Дані®Фільтр® Автофільтр.
У стовпці Посаданазначити умову:
/>
Натиснути ОК.
У стовпці Всьогонараховано з/ за квартал назначити умову:
/>
Натиснути ОК.
Відфільтрованутаблицю зберегти на аркуші “Фільтр-А_Горобець” (додаток Г) у файлі підтим самим іменем.
Розширений фільтр.
Команда табличногопроцесора розширений фільтр на відміну від команди автофільтр потребуєвизначення умови (критерію) відбору даних в окремому діапазоні даноголистка поза списком. Для цього необхідно визначити, в якому місці даноголистка будуть розміщені умова критеріїв відбору та результати.
Завдання:
На аркуші “Фільтр-Р_Прізвище”відібрати записи, для яких П.І.П. починається з літери “Л” і Категорія менша за3.
Етапи виконання:
Скопіювати аркуш “Розр_Горобець”та перейменувати на “Фільтр-Р_Горобець".
Вибрати якзаголовок таблиці рядок нумерації (11). Рядок (10) слугуватиме для наочності.
Скопіюватизаголовок таблиці (10 — 11-й рядки) в рядки 33, 34, щоб сформувати діапазонумов.
Сформуємо умовивідбору.  П.І.П.  Посада  Категорія 2 3 4 +К* >3
Примітка.
Логічне “і” вExcel реалізується в одному рядку, “або” — в різних.
Виділити діапазонА11: L26.
Виконати командуДані®Фільтр®Розширений фільтр.
В діалоговомувікні розширеного фільтра задати:
В полі “Обробка”активізувати пункт
“Скопіюватирезультати до іншого розташування”;
Вихіднийдіапазон$A$11: $L$26;
Діапазонумов$A$33: $L$35;
Діапазон для результату$A$38:$L$40.
Натиснути ОК.
Зберегти таблицю(додаток Д) у книзі під тим самим іменем.
Фільтрація закритерієм, що обчислюється
Завдання:
На аркуші “Фільтр-Форм_Прізвище"вивести запис з найбільшою Нарахованою премією
Етапи виконання:
Скопіювати аркуш “Розр_Горобець” та перейменувати на “Фільтр-Форм_Горобець".
Вибрати якзаголовок таблиці рядок нумерації (11). Рядок (10) слугуватиме для наочності.
Скопіюватизаголовок таблиці (10 — 11-й рядки) в рядки 3, 34, щоб сформувати діапазонумов.
Сформувати таблицюкритеріїв (умови фільтрації).
Примітка.
В діапазоніумов заголовок стовпця (11), за яким проводиться відбір, не повинен збігатисяіз заголовком початкового списку.
У рядку 34 вклітині L34 замість числа 12 введемо довільний текст, наприклад, “ Найбільшанарахована премія". (Можна взагалі залишити клітину пустою).
Сформувати умовивідбору. В клітину L35 ввести =L12=МАКС ($L$12: $L$26).
Примітка.
Результатомвиконання даної формули є TRUE (істина) або FALSE (хибність) залежно відзначення клітини L12.
Виділити діапазонА11: L26.
Виконати командуДані ® Фільтр®Розширений фільтр.
У діалоговомувікні розширеного фільтра задати:
/>
Натиснути ОК.
Зберегти таблицю(додаток Е) у файлі під тим самим іменем.3.6 Підведення проміжних підсумків
Завдання:
На аркуші “Пр_Прізвище”підвести проміжні підсумки: за посадою знайти середню Умовну зарплату.
Етапи виконання:
Скопіювати аркуш “Розр_Горобець”та перейменувати на “Фільтр-Пр_Горобець".
Рядок нумерації(11) вилучити, заголовок таблиці — рядок (10).
Відсортуватитаблицю за полем Посада, при зміні якого необхідно підвести проміжні підсумки(стовпчик Порядковий номер не підлягає сортуванню).
Виділити діапазонА: J21.
Виконати командуДані®Підсумки.
В діалоговомувікні проміжних підсумків задати:
/>
Натиснути ОК.
Примітка.
Результатом єструктурована таблиця з підсумками по кожному відділу та у цілому.
Зберегти таблицю(додаток Ж) у файлі під тим самим іменем.3.7 Процес консолідації робочихлистів
Завдання:
Виконати два видиконсолідації за розташуванням: динамічну (аркуш “КД_Прізвище”) тастатичну (аркуш “КС_Прізвище”) на прикладі знаходження середньогоза стовпцем 12 за три місяці.
Етапи виконання:
Скопіювати аркуш“Розр_Garmash" п’ять разів та перейменувати на “Січень_Garmash",“Лютий_Garmash, “Березень_Garmash", “КС_Garmash”,
“КД_Garmash".
Консолідація даних“За розташуванням" (Статистична).
На аркуші “КС_Garmash"очистити діапазон клітин L12: L26.
Виділити діапазонL12: L26.
Командою Дані®Консолідація активізувати однойменне діалогове вікно.
 
/>
У списку “Функція”вибрати функцію, що використовується приоб’єднанні даних, — середнє.
У полі “Посилання”за допомогою кнопки /> перейти на лист “Січень_Горобець”,виділити той самий діапазон L12: L26 та натиснути Додати.
Аналогічнопередати у список діапазонів дані за Лютий таБерезень.
Примітка.
Результатомвиконання статичної консолідації на відміну від динамічної є неструктурованатаблиця з підсумками за три місяці, в якій не можна їх переглянути детально. Нааркуші із статичною консолідацією не відбувається змін у результаті проведенняякихось змін в даних на аркушах, які беруть участь в консолідаціїї.
Аркуш зі статичноюконсолідацію наведено у додатку И.
Консолідація даних“За розташуванням" (Динамічна).
На аркуші “КД_Горобець”виконати описану послідовність дій. Щоб консолідація була динамічною,потрібно у вікні консолідації активізувати поле “Створювати зв’язки звихідними даними".
Примітка.
Результатомвиконання динамічної консолідації на відміну від статичної є структурованатаблиця з підсумками за три місяці, в якій можна детально їх переглянути шляхомактивізації кнопки /> або згорнути (приховати) кнопкою />.
Зберегтитаблицю (додаток И) у файлі під тим самим іменем.3.8 Технологія побудови графіків
Для наочноговідображення даних, які входять до електронної таблиці, служать діаграма іграфіки. Вони розміщуються на робочому аркуші і дають можливість порівнювати,аналізувати дані.
Завдання:
Побудувати:
лінійний графік співвідношення даних стовпчиків 10 та 12 (аркуш "Д_Прізвище"),кругову діаграму% співвідношення «Всього нараховано з/п заквартал» за категорією (аркуш "Круг_Прізвище").
Етапи виконання:
Скопіювати аркуш “Розр_Горобець”та перейменувати на “_Garmash".
Приховати рядок11.
На робочому аркушівиділимо діапазони комірок (J10: J26, L10: L26) з даними Умовна з/плата таНараховано премії (несуміжні діапазони виділяються за допомогою Ctrl).
АктивізуватиМайстер діаграм за допомогою однойменної кнопки на стандартній панеліінструментів.
У діалоговомувікні Майстра виконати чотири кроки.
Крок 1 Вибрати типта вигляд діаграми.
Крок 2 Змінити абовибрати діапазон даних, на основі яких буде побудовано діаграму.
Крок 3 Ввестизаголовки діаграми та її осей. Встановити параметри діаграми, пов’язані з їїзовнішнім оформленням.
Крок 4 Вибративаріант розміщення діаграми (на поточному або окремому аркуші)
/> 
/>
/>
/>
Натиснемо кнопкуГотово.
Аналогічним чиномбудуємо кругову діаграму відповідно до поставленого завдання.
Зберегти таблицю зграфіком (додаток К) у файлі під тим самим іменем.
4. Інструкція з експлуатації таблиці
Завантажити Excel.
Відкрити файл“№варіанта_Горобець. xls”
Даний файл маєтакі аркуші:
Розр_Горобець" — розрахунковий вигляд таблиці;
“Форм_Горобець”- формульний вигляд таблиці;
“Сорт_Горобець”- відсортована таблиця;
“Фільтр-А_Горобець”- таблиця з проведеним автофільтром;
“Фільтр-Р_Garmash" — таблиця з проведеним розширеним фільтром;
“Фільтр-Форм_Горобець" — таблиця з проведеним розширеним фільтром за критерієм, що обчислюється;
“Пр_Горобець”- таблиця з проміжними підсумками;
“Січень_Горобець",“Лютий_Горобець”,
“Березень_Горобець”- вихідні таблиці для проведення консолідації;
“KС_Горобець”,“КД_Горобець” — таблиці з результатами динамічної та статичноїконсолідації відповідно;
“Д_Горобець",“Круг_Горобець” — таблиці з лінійним графіком та діаграмою.
В побудованійтаблиці, виходячи з фактичних даних, заповнюються за власним розсудом тількиклітини стовпців: A, B, D, E, F, G, I.
Для того щобввести числові дані, необхідно активізувати потрібну клітину, ввести дані,натиснути Enter. Пересуватися по клітинах таблиці можна за допомогоюклавіш управління курсором та за допомогою миші.
Для того щобперейти на інший аркуш, потрібно покажчик миші навести на ярлик потрібногоаркуша, розміщеного в нижній частині документа і натиснути один раз лівоюкнопкою миші.
Редагувати даніможна в рядку формул або в самій клітині, натиснувши двічі на ній лівоюкнопкою миші, або натиснути F2, попередньо виділивши клітину. Для видаленняданих з клітини потрібно її виділити і натиснути Delete. Для виділеннядіапазону клітин слід протягти покажчик миші від одного кута діапазону допротилежного.
Іноді виникаєситуація, коли потрібно додати чи видалити рядок, наприклад, призміні кількості найменувань продукції у списку. Щоб вставити рядок,потрібно виділити клітину, над якою вставляємо рядок Щоб вставити декількарядків, потрібно виділити таку саму кількість рядків знизу від місця вставки.Щоб одночасно виділити декілька рядків, натиснути ліву кнопкумиші і протягнути її по заголовках. Тоді відкрити меню Вставка→Рядки.Вставлені рядки будуть такого самого формату, що й виділені. Щоб видалитирядки, необхідно їх виділити, відкрити меню Правка→Видалити. Всіпосилання у формулах і іменах відповідно поновлюються.
Щоб змінитиширину стовпця чи рядка, потрібно помістити покажчик миші на межу,яка розділяє заголовки двох стовпців чи рядків, натиснути кнопку миші іперетягнути в потрібному напрямку границю або виконати команду меню Формат→Стовпець(Рядок) →Автодобір ширини (висоти).
Зручний і швидкийспосіб переглянути деякі дані надають діаграми. Наприклад, на графіку “XY_Garmash"можна визначити собівартість продукції та ціну реалізації за тонну. Для цьогослід навести курсор на ряд даних, і автоматично висвітиться значення.
Захист формул.
Виділити діапазонклітин, в яких потрібно приховати формули. За необхідністю можна виділитидіапазон клітин чи весь аркуш.
Команда головногоменю Формат→Клітинки→Захист. Установити прапорець “Сховатиформули". Натиснути ОК.
Команда головногоменю Сервіс→Захист→Захистити аркуш.
Якщо виникаєнеобхідність у зміні вмісту комірок, слід зняти захист комірок, виконавшизворотні дії „Сервіс/Захист аркуша”. Для більш надійного захисту даних утаблицях можна встановлювати пароль.
Вихід з Excel. Уменю Файл вибрати команду Вихід або натиснути мишею кнопкумінімізації />.