Курсова робота
з дисципліни "Інформатика та КТ"
на тему
«СТВОРЕННЯ ЕЛЕКРОННОЇ ТАБЛИЦІ»
«Відомість нарахування премії за 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)
Всього нараховано з/п за квартал
ВНЗПК
/>
КТУ
КТУ
Умовна з/плата
УЗП
УЗП = ВНЗПК * КТУ--PAGE_BREAK----PAGE_BREAK----PAGE_BREAK----PAGE_BREAK--
=МАКС (L12: L26) --PAGE_BREAK--
Шрифт
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
Наприкінці роботи наводиться список літератури, в якому джерела розміщені у порядку їх використання. Список літератури варто оформляти відповідно до Держстандарту (додаток Г) із застосуванням формату нумерованого списку. продолжение
--PAGE_BREAK--
В кінці роботи додати два чистих аркуші для рецензій викладача. Після висновків робота підписується автором, ставиться дата.
Після отримання перевіреної роботи в неї в жодному разі не можна вносити ніяких змін (вилучати аркуші із зауваженнями керівника і т.п.).
Виправлені фрагменти з посиланнями на сторінки, де були зроблені зауваження керівником, варто оформити й роздрукувати на окремих аркушах і підшити до початкового варіанта роботи.
3.1 Заповнення таблиці
Вводятьдані (текст або числа) безпосередньо в поточну клітинуабо в рядок формул, що розміщується у верхній частині вікна програми під панелями інструментів.
Місце введення відмічається текстовим курсором. Якщо почати введення з алфавітно-цифрових клавіш, дані з поточної клітини заміняються текстом, що вводиться.
Дані, які вводяться, відображаються як в клітині, так і в рядку формул.
Для завершення введення даних використовують клавішу ENTERна клавіатурі або кнопку />Enterв рядку формул.
/>
Число, на відміну від довільного тексту, у своєму записі використовує обмежений набір символів
0 1 2 3 4 5 6 7 8 9 + — ,. р.% Е е.
Основна різниця числа від тексту полягає в тому, що з числом можна виконувати в подальшому різні дії (наприклад, арифметичні операції), а з текстом ні.
Число після введення автоматично вирівнюється по правомукраю клітини.
Якщо числоне вміщуєтьсяв ширину стовпця, то на екрані з’являється ######.
При введенні довгоготекстуна екрані буде виднотільки ту частину тексту, яка поміщається до першої занятої справа клітини, навіть якщо там стоїть знак пробілу (пропуску).
В рядку формул видно повнийтекст.
Щоб відмінитивнесені зміни і відновити колишнє значення клітини, використовують клавішу 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 функції для обчислення середнього значення діапазону. продолжение
--PAGE_BREAK--
Активізувати 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.
Виконати команду Дані®Підсумки.
В діалоговому вікні проміжних підсумків задати:
/> продолжение
--PAGE_BREAK--
Натиснути ОК.
Примітка.
Результатом є структурована таблиця з підсумками по кожному відділу та у цілому.
Зберегти таблицю (додаток Ж) у файлі під тим самим іменем.
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. У меню Файлвибрати команду Вихід або натиснути мишею кнопку мінімізації />.