Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИ
ПОЛТАВСЬКИЙ НАЦІОНАЛЬНИЙ ТЕХНІЧНИЙ УНІВЕРСИТЕТ
ІМЕНІ ЮРІЯ КОНДРАТЮКА
КАФЕДРА КОМП’ЮТЕРНИХ ТА ІНФОРМАЦІЙНИХ ТЕХНОЛОГІЙ І СИСТЕМ
Методичні вказівки
до проведення лабораторних робіт із ТП MS-Еxcel
для студентів Центру післядипломної освіти
всіх форм навчання зі спеціальностей:
7.050106 – “Облік і аудит”
7.050107- “Економіка підприємства”
(спеціалізація – “Правове регулювання економіки підприємства”)
7.050201- “Менеджмент організацій”
Полтава 2003
Методичні вказівки до проведення лабораторних робіт із ТП MS- Еxcel для студентів всіх форм навчання зі спеціальностей:7.050106 – “Облік і аудит”,7.050107- “Економіка підприємства” (спеціалізація – “Правове регулювання економіки підприємства”),7.050201- “Менеджмент організацій”. – Полтава: ПолтНТУ, 2003 .– 33 с.
Укладачі:О.І.Корх,канд.техн.наук,доцент, Т.М.Деркач, канд.техн.наук, доцент.
Рецензент: Харченко О.А, канд.техн.наук, доцент.
Відповідальний за випуск: завідувач кафедри комп’ютерних та інформаційних технологій і систем Корх О.І., канд. техн. наук, доцент.
Затверджено радою університету
Протокол № 3 від 14.11.2003 р.
Редактор Н.В. Білан
Коректор Н.О.Янкевич
Вступ
В економічних, бухгалтерських, фінансових, статистичних, інженер-них та інших розрахунках інформація записується у вигляді таблиць (табличні задачі). Таблиці складаються з рядків та стовпців, на перетині яких знаходяться клітинки. В одну частину клітинок заносяться вхідні дані, в решту–результати обчислення за певними формулами.
Сьогодні найбільшого поширення набули табличні процесори Excel фірми Microsoft (США). Версії цієї програми відрізняються часом створення та функціональними можливостями. Пакет програм Excel містить у собі систему обробки електронних таблиць, пакет ділової графіки, систему керування табличними базами даних і систему макропрограмування.
Виконання розрахунків за допомогою Excel не вимагає знання мов програмування, що дає можливість працювати з цією програмою користувачам, які не є професіональними програмістами.
Загальні відомості. Структура електронної таблиці
Усі робочі файли Excel мають тип *.xls і зображуються у вигляді книг-Excel, кожна із яких складається із визначеної кількості аркушів Excel (але не більше ніж 255 ) . На кожному аркуші розміщується електронна таблиця, котра може мати 65536 рядків та 256 стовпців. Одночасно може бути відкрито декілька книг-Excel, кожна з яких знаходиться в окремому вікні. Вікна системи та книг є вікнами системи Windows із всіма їх елементами управління й параметрами (властивостями). Кожному файлу та аркушу-Excel присвоюються деякі стандартні імена. Ім’я файла можна змінити при його записі на диск через меню Файл та команду Сохранить_как. Стандартне ім’я аркуша-Excel змінюється при використанні правої клавіші „миші”, коли курсор знаходиться на імені аркуша. Переміщення від одного робочого аркуша до іншого виконується вибором відповідного ярлика аркуша лівою клавішею „миші”.
Аналогічно до звичайної таблиці, електронна таблиця (ЕТ) містить рядки і стовпці, на перетині яких знаходяться клітинки. Рядки позначаються числами від 1 до 65536, а стовпці – однією або двома літерами латинського алфавіту від А, В, С …до IU (256 стовпців) (рис.1).
Клітинка характеризується адресою (ім’ям), вмістом, значенням, форматом. В одній клітинці можна записати до 32000 символів. Адреса клітинки визначається рядком і стовпцем, у яких вона знаходиться. Для запису адреси спочатку записується назва стовпця, а потім – рядка. Наприклад: A1 , E13 , AF1099 .
Рис. 1. Вікно Excel із відкритою книгою
Для швидкого доступу до найпоширеніших команд передбачені так звані панелі інструментів. У випадку, коли панель відсутня на екрані, її можна викликати, використовуючи меню Вид/Панели_инструментов. Для виведення панелі на екран необхідно ввімкнути індикатор, що знаходиться зліва від назви відповідної панелі. Серед найбільш часто вживаних є панель інструментів Стандартная та Форматирование. Панель інструментів Стандартная має вигляд :
Головними операціями у роботі з кнопками цієї панелі є: створення нового файла, відкриття вже створеного, збереження активної книги, операції простого копіювання та ін. При переміщенні покажчиком „миші”по кнопках панелі інструментів система автоматично виводить підказку про функцію кожної кнопки.
Панель інструментів Форматирование призначена для виконання основних операцій зміни формату виведення інформації на екран. Під час практичних занять необхідно добре ознайомитися та вивчити можливості кожної панелі.
Вміст клітинки – це записані в неї дані. Для введення інформації необхідно виділити потрібну клітинку табличним курсором, після чого почати вводити дані. Для завершення запису даних у клітинку необхідно натиснути клавішу <Enter> або клавішу переміщення курсора.
Коригування неправильно введених даних в активній клітинці виконується після натиснення клавіші <F2> або подвійним клацанням лівої клавіші мишки.
Дані в клітинці можуть бути одного із типів: текст, число, дата або час доби, формула.
Текст можна ввести як латинськими літерами, так і кирилицею. На початку тексту вводиться знак “ (лапки). Але якщо текст не починається з
прогалини і не може бути сприйнятий як дата, час, число або формула, то лапки вводити не обов’язково. Введений текст вирівнюється в клітинці по лівому краю. Щоб почати в клітинці новий рядок тексту, слід натиснути клавіші <Alt>+<Enter>.
Числа можна вводити цілими або дійсними. Дійсні числа записують із фіксованою або плаваючою крапкою й можуть містити не більше 15 значущих цифр. Для правильного введення цифр числа та розділового знаку цілої і дробової частин (крапки або коми) слід набирати на малій цифровій клавіатурі в режимі <NumLock>. При записі чисел із фіксованою крапкою нульову цілу і дробову частину можна не записувати.
Наприклад: 3,1415 0,25 ,125 .
В Excel дата і час доби інтерпретуються як числа. Зовнішнє зображення часу або дати в клітинці залежить від визначеного формату зображення. При введенні значення дати або часу доби виконується їх автоматичне розпізнання і заміна загального формату клітинки на вбудований формат дати або часу. Значення дати і часу вирівнюється в клітинці по правому краю. Параметри, встановлені значком “Язык и стандарты” Панели управления , визначають формат запису для дати і часу та розділові символи, які використовуються в стандарті , наприклад, двокрапка (:) і крапка (.) . Щоб увести дату і час доби в одну й ту ж клітинку, необхідно їх розділити символом пробіл. Час доби та дати можна додавати, віднімати і виконувати над ними інші дії. У формули вони можуть бути введені у вигляді тексту, взятого в лапки. Наприклад, =”05.12.94”-“03.05.94” результат дорівнює 68.
Формула починається із символу = (дорівнює) і складається з операндів, з’єднаних знаками арифметичних операцій: + (додавання), - (віднімання), * (множення), / (ділення), ^ (піднесення до степеня), %(обчислення відсотків). Для визначення порядку дій використовують круглі дужки. Операндами можуть бути числа, адреси клітинок, функції.
Адреси клітинок у формулах використовуються як числові змінні. При обчисленні за формулою замість адреси підставляється поточне значення даної клітинки. Наприклад: нехай у клітинці B5 записана формула =A1+7 . Якщо в клітинці A1 буде значення 9, то значення клітинки B5 буде 16. При зміні значення в A1 переобчислюється і значення B5.
Адреси клітинок можуть бути відносними й абсолютними. Відносна адреса вказує розташування клітинки, на яку є посилання у формулі, відносно клітинки, в якій ця формула записана. Наприклад, якщо в клітинці B5 записана формула =A1+7 , це означає, що число 7 додається до значення клітинки, котра знаходиться на один стовпчик лівіше і на 4 рядки вище від даної. Якщо адреса клітинки, в якій записана ця формула, змінюється (це може бути при копіюванні чи перенесенні формули в іншу клітинку або при додаванні чи видаленні рядків або стовпців таблиці), то формула змінить свій вигляд. Так, якщо формулу =A1+7 перенести з клітинки B5 в D7, то вона матиме вигляд =C3+7.
Абсолютні адреси клітинок завжди вказують на одну і ту ж клітинку і не змінюються при перетворенні таблиці. Для позначення абсолютної адреси перед позначенням рядка і стовпця ставиться знак $ (це легко зробити, натиснувши після запису адреси клавішу <F4>). Можливі випадки, коли абсолютною адресою є тільки вказівник рядка або стовпця. Наприклад : $A$3 , $B5 , D$7 .
Табличний процесор MS-Excel97/2000 містить понад 400 вбудованих функцій, які поділяються на математичні й тригонометричні, календарні, статистичні, логічні, фінансові, текстові, для керування в базі даних та інформаційні. Щоб використати функцію як операнд у формулі, необхідно записати її ім’я й аргумент (у круглих дужках). Аргументами деяких функцій є блоки клітинок. Блок – це частина ЕТ, що має форму прямокутника і складається з клітинок, що прилягають одна до одної. Блок визначається адресами лівої верхньої і правої нижньої клітинок, розділеними двокрапкою. Наприклад : A1:E15 , B4:B100 , A1:F1 .
У логічних функціях вирази логічного типу записуються з використанням операторів порівняння: = (дорівнює), > (більше), < (менше), >= (більше або дорівнює), <= (менше або дорівнює), <> (не дорівнює).
Наприклад: =ЕСЛИ(A5*B5>C5 ; СУММ(D1:D5) ; D5*C5).
Для отримання детальної інформації про будь-яку вбудовану функцію слід звернутися в меню Справка/Вызов_справки/Создание формул/Использование_функций або використати для цього Мастера функций.
Можна використовувати два варіанти конструювання і введення формул у клітинки ЕТ :
Перший спосіб безпосереднього введення функції в клітинку ЕТ під-порядкований загальному принципу роботи з формулами. Для цього необхідно:
Рис. 2. Майстер функцій. Крок 1. Категорії функцій
Другий спосіб використовує Мастер функций, і він є значно ефективнішим, якщо таблиця початкових даних уже підготовлена і потрібно виконати деякі розрахунки.
Для виклику Мастера функций необхідно:
Рис. 3. Майстер функцій. Крок 2
Наведемо декілька прикладів написання формул та їх пояснення:
1) =2,58*(E3+D3)/1,5-C3
Вміст клітинки E3 додається із вмістом клітинки D3, результат множиться на 2,58 і ділиться на 1,5 , від отриманого результату віднімається вміст клітинки C3 .
Обчислюється сума значень, розміщених у клітинках A1, A2, A3, D2, D3, D4, D5 .
3) =ЕСЛИ(C2*E2>D10;”Так”;”Ні”)
Визначається значення логічного виразу. Якщо його значення буде «Істина», то результатом розрахунку за цією формулою буде текстове значення «Так». В іншому випадку – «Ні».
У процесі розроблення та налагодження ЕТ можуть бути допущені деякі помилки в клітинках із формулами, які зображуються наступним чином:
|
|
Причини появи помилок та їх виправлення |
|
##### |
Помилка є, якщо числове значення не поміщається в клітинці. В цьому випадку треба збільшити ширину стовпця таблиці або змінити формат зображення числового значення. |
|
#ДЕЛ/0! |
У формулі виконується дія ділення на нуль або в якості дільника використовується посилання на клітинку з нульовим чи пустим значенням. У цьому випадку треба змінити посилання або ввести ненульове значення у відповідну клітинку. |
|
#ИМЯ? |
Помилка є, якщо Excel не може розпізнати ім’я функції або текст уведений без символів лапок, або в посиланні на діапазон клітинок пропущений знак двокрапка. |
|
#ЗНАЧ? |
Помилка є, якщо використовується недопустимий тип аргументу функції, наприклад, замість числового чи логічного значення у формулу введений текст. |
|
#ЧИСЛО! |
Помилка наявна при використанні недопустимих числових значень у якості аргументу функції. |
Копіювання інформації
При розробленні ЕТ формули, як правило, вводяться один раз, а в інші клітинки аналогічні формули заносяться за допомогою копіювання.
Для копіювання інформації необхідно:
Аналогічно виконується копіювання інформації на інший аркуш або книгу. Для копіювання даних у клітинки в середині стовпчика чи рядка необхідно:
Для заповнення рядів чисел або дат у клітинках середини стовпчика чи рядка необхідно:
Для введення однакових даних у декілька клітинок одночасно необхідно:
Видалення інформації
Для видалення інформації з клітинок ЕТ необхідно:
Для видалення непотрібних рядків чи стовпців ЕТ необхідно:
Форматування
Форматування даних – це відображення вмісту чи значення клітинки в зручній для користувача формі. Форматування виконується над даними, розміщеними в активній клітинці або у виділеному блоці, рядку, стовпці, аркуші. Для виконання форматування необхідно вибрати пункт меню Формат та відповідну команду. Ячейки – форматування виділених клітинок. Строка – форматування рядків. Столбец – форматування
стовпців . Лист – форматування аркушів. Автоформат…- використання вбудованих параметрів форматування. Условное форматирование – форматування виділеного діапазону клітинок на основі логічних умов, які задаються користувачем при розробленні таблиці. Стиль…- використання для форматування ЕТ, що розробляється, різних стандартних комбінацій параметрів форматування.
Основою форматування є форматування клітинки або блоку клітинок. Для цього необхідно після виділення відповідних клітинок вибрати пункт меню Формат та команду Ячейки або в контекстному меню команду Формат ячеек . Після вибору команди з’явиться діалогове вікно з шістьма вкладками :
Число – вибір та створення форматів зображення даних ; Выравнивание – вибір варіанта вирівнювання даних у клітинці, об’єднання клітинок, перенос тексту по словах; Шрифт – вибір шрифту та його параметрів; Граница – вибір виду обмежувальних ліній для зображення меж виділеного діапазону; Вид – вибір варіанта кольору (заливка) та рисунка (узор) для заповнення фону клітинок; Защита – дозволяє встановити або відмінити захист на коригування введених даних в окремих клітинках ЕТ та закрити від перегляду формули.
Рис. 4
Вирівнювання вмісту клітинки виконується відносно меж клітинки. Дані в
Рис. 5
клітинці, наприклад текст, можуть розміщуватися в кілька рядків, якщо включити опцію переносить_по_словам. Ширина клітинки (стовпця) може автоматично змінюватися для відображення всього тексту, якщо включити опцію автоподбор_ширины. Клітинки виділеного блоку можуть бути об’єднані в одну клітинку, якшо включити опцію объединение_ячеек . Орієнтування зображення тексту в клітинці може змінюватися в інтервалі від 0 до 180 градусів.
Проведення обмежувальних ліній в ЕТ призначено для поліпшення сприйняття числових значень. Найкраще з цією метою використовувати вкладку Граница діалогового вікна Формат_ячеек. За будь-якого форматування необхідно твердо знати, що форматування проводиться з виділеним фрагментом, тобто перед проведенням ліній потрібно виділити інтервал клітинок, відносно якого і буде проведено лінію. Після чого вибрати місце розташування лінії відносно даного інтервалу. Вибір стилю та кольору ліній меж отримуємо з відповідних елементів – тип, цвет .
Розглянемо деякі найбільш часто вживані під час розроблення ЕТ прикладних задач питання на форматування.
Для копіювання встановленого формату з однієї клітинки або блоку клітинок в інший необхідно :
Щоб змінити тип шрифту та його розміри, необхідно :
Для відображення (розміщення) кількох рядків тексту в середині клітинки необхідно :
Для об’єднання інтервалу клітинок (або блоку) в одну клітинку і розміщення даних верхньої лівої клітинки інтервалу необхідно:
В об’єднаній клітинці для вирівнювання тексту використовуються кнопки По левому краю, По правому краю, По центру панелі інструментів Форматирование.
Сортування даних
Для використання деяких операцій автоматичної обробки даних часто буває необхідно заздалегідь розташувати дані в таблиці (списку) в строго визначеній послідовності - виконати сортування вихідних даних.
Сортування здійснюється «на місці» - безпосередньо в таблиці.
В електронній таблиці можна сортувати як рядки, так і стовпці. Рядки можна відсортувати за значенням клітинок одного стовпця або декількох.
Рядки, стовпці або окремі клітинки в процесі сортування переупорядковуються відповідно до заданого користувачем порядку сортування. Списки можна сортувати в зростаючому або в спадаючому порядку.
За замовчуванням Excel сортує дані за абеткою. Для сортування в іншому порядку (наприклад, щоб розташувати місяці і дні тижня відповідно до їх логічного, а не алфавітного порядку) варто використовувати користувальницький порядок сортування.
Сортування даних в електронній таблиці виконується за допомогою команд меню Данные/Сортировка або за допомогою кнопок на стандартній панелі інструментів.
Фільтрація даних зі списку
Фільтрація (вибірка) даних у таблиці дозволяє відображати тільки ті рядки, вміст клітинок яких відповідає заданій умові або декільком умовам. За допомогою фільтрів користувач може в зручній для себе формі виводити або видаляти (ховати) запис у списку.
На відміну від сортування, дані при фільтрації не переупорядковуються, а лише ховаються ті записи, що не відповідають заданим критеріям вибірки.
Відібрані записи можна форматувати або видаляти, копіювати в окрему область таблиці, роздруковувати, а також використовувати для наступних обчислень або побудови діаграм.
Фільтрація даних в електронних таблицях може виконуватися двома способами: за допомогою автофільтра або розширеного фільтра.
Фільтрація даних із використанням автофільтра.
Для виконання цієї операції потрібно:
Умови для добору записів у визначеному стовпці можуть складатися з двох самостійних частин, з'єднаних логічним зв'язком і/або.
Кожна частина умови може включати:
|
= Дорівнює > Більше < Менше |
<=Менше або дорівнює <>Не дорівнює >= Більше або дорівнює |
Для відновлення всіх рядків вихідної таблиці потрібно клацнути „мишею” на кнопці зі стрілкою (синього кольору) і в списку, що розкрився, вибрати рядок «все» або виконати команду Данные/Фильтр/Отобразить все.
Для скасування режиму фільтрації потрібно встановити курсор у середині таблиці і знову ввести команду меню Данные/Фильтр/Автофильтр (забрати перемикач).
Фільтрація даних з використанням розширеного фільтра
Розширений фільтр дозволяє формувати множинні критерії вибірки і здійснювати більш складну фільтрацію даних електронної таблиці з заданням набору умов добору за декількома стовпцями.
Для фільтрації записів списку розширений фільтр забезпечує використання двох типів критеріїв:
Фільтрація записів із використанням розширеного фільтра виконується за допомогою команди меню Данные/Фильтр/Расширенный фильтр.
Важливою особливістю цього режиму є те , що до виконання самої команди фільтрації необхідно сформувати спеціальну область для задання умов фільтрації даних – діапазон умов добору (інтервал критеріїв).
Діапазон умов повинен містити рядок із заголовками стовпців і кілька рядків для задання діапазону умов. Спочатку копіюють в окреме місце ( на іншому або на тому самому робочому аркуші – як правило, вище від вихідної таблиці) рядок із заголовками стовпців, потім у розташовані нижче рядки вводять критерії вибірки за окремими стовпцями.
Між значеннями умов добору і таблицею повинен знаходитися, як мінімум, один порожній рядок.
Якщо критерії добору ( умови фільтрації) вводяться в одному рядку для різних стовпців, то вони вважаються зв'язаними умовою «И». Якщо критерії добору записуються в різних рядках, то вони вважаються зв'язаними умовою «АБО».
Після форматування діапазону умов із критеріями вибірки записів установлюють курсор усередині таблиці, уводять команду Данные/Фильтр/Расширенный фильтр і в діалоговому вікні Расширенный фильтр указують діапазон клітинок таблиці й адресу або ім'я сформованого заздалегідь діапазону умов.
Записи можна фільтрувати на місці або одночасно з виконанням фільтрації копіювати в зазначену область на поточному робочому аркуші.
Щоб скопіювати відфільтровані рядки в іншу область аркуша, варто встановити перемикач Скопировать результат в другое место, перейти в поле Поместить результат в диапазон і вказати верхню ліву клітинку області вставки відібраних даних.
Посилання на діапазон умов добору повинні включати тільки заголовки стовпців і рядки з критеріями, тобто не містити порожніх рядків.
Автоматичне підведення підсумків - це зручний спосіб швидкого узагальнення й аналізу даних в електронній таблиці.
Для того, щоб мати можливість автоматично підводити загальні і проміжні підсумки, дані в таблиці повинні бути організовані у вигляді списку або бази даних.
Перед обчисленням проміжних підсумків варто виконати сортування за тими стовпцями, за якими будуть підводитися підсумки, для того, щоб усі записи з однаковими полями цих стовпців потрапили в одну групу.
Якщо дані в таблиці організовані неправильно (не у вигляді списку), то Excel може не зрозуміти структуру таблиці і не створити проміжних підсумків.
При підведенні підсумків Excel автоматично створює формулу, додає рядок або рядки для запису проміжних підсумків і підставляє адреси клітинок даних.
Для однієї і тієї ж групи даних можна одночасно обчислювати проміжні підсумки за допомогою декількох функцій, а також обчислювати «вкладені» або багаторівневі підсумки.
Значення загальних і проміжних підсумків перераховуються автоматично при кожній зміні детальних даних.
При підведенні проміжних підсумків автоматично можуть бути обчислені:
Сума
Кількість значень
Середнє
Максимум
Мінімум
Добуток
Кількість чисел
Зміщене відхилення
Незміщене відхилення
Зміщена дисперсія
Незміщена дисперсія
Для автоматичного підведення підсумків варто виконати наступну послідовність дій:
Підсумки можуть виводитися або нижче вихідних даних або вище, якщо в діалоговому вікні Промежуточные итоги знятий перемикач Итоги под данными.
Команда Данные/Итоги для однієї і тієї ж таблиці може виконуватися багаторазово. При цьому раніше створені підсумки можуть як замінятися новими, так і залишатися незмінними, якщо в діалоговому вікні Промежуточные итоги знятий перемикач Заменить текущие итоги.
Команда Данные/Итоги використовується для узагальнення даних, що знаходяться на одному робочому аркуші, і в тому випадку, якщо ці дані розташовані в суміжних клітинках. Підсумки виводяться на тому ж робочому аркуші в структурованій таблиці і вихідних даних.
Якщо вихідні дані розташовані в несуміжних клітинках або потрібно підвести підсумки за даними, розмішеними на декількох робочих аркушах або в різних робочих книгах (файлах), то виконують консолідацію даних, що дозволяє об'єднати дані з декількох джерел і виводити підсумки в будь-якій зазначеній користувачем області.
Розроблення ЕТ для автоматизації розв’язку прикладної задачі виконується в такій послідовності:
Створення ЕТ розглянемо на прикладі нарахування заробітної плати (спрощений варіант).
Вихідні дані
Зарплата нараховується залежно від кількості відпрацьованих годин і погодинної ставки. Із нарахованої суми утримується прибутковий податок, у пенсійний фонд - 2%. Прибутковий податок обчислюється залежно від розміру нарахованої суми:
|
НС< 17грн. |
ПП= 0 грн |
|
17 грн.<НС<= 85 грн |
ПП= 10%(НС-17) |
|
85 грн.<НС<= 170грн |
ПП=15%(НС-85)+6,80грн |
|
170 грн.<НС |
ПП=20%(НС-170)+19,55грн |
Рис. 7
Розв’язання задачі
Із системного меню запустити Excel (стартовий файл excel.exe) та налагодити параметри роботи.
Розроблення таблиці слід почати із зміни ширини стовпців А і В аркуша 1. Увести заголовок таблиці в клітинку А1 та заголовки стовпців у відповідні клітинки шапки таблиці :
|
Адреса клітинки |
Вміст клітинки |
|
А1 |
Відомість на виплату зарплати |
|
А2 |
№ з/п |
|
В2 |
Прізвище, ім'я та по батькові |
|
С2 |
Підрозділ |
|
D2 |
Погодинна ставка |
|
E2 |
Відпрацьовано годин |
|
F2 |
Нараховано (грн.) |
|
G2 |
Прибутковий податок |
|
H2 |
Відрахування у пенс. фонд |
|
I2 |
Утримано |
|
J2 |
До видачі |
При цьому не слід звертати увагу на те, що текст заголовків стовпців шапки таблиці виходить за межі клітинок. Після введення всіх заголовків виконаємо форматування цих клітинок.
Для об’єднання інтервалу клітинок А1:J1 та розміщення заголовка таблиці в середині необхідно :
Виділимо інтервал клітинок А2:J2 та виберемо меню Формат, команду Ячейки, а потім вкладку Выравнивание. Встановлюємо необхідні параметри вирівнювання і для завершення діалогу натискуємо командну кнопку <OK>.
Рис. 8
У клітинки блоку A3:E12 уводимо дані контрольного прикладу. В інші клітинки рядка 3 таблиці вводимо наступні формули:
|
Адреса клітинки |
Формула |
|
F3 |
=C3*D3 |
|
G3 |
=ЕСЛИ(E3<=17;0;ЕСЛИ(E3<=85;(E3-17)*0.1;ЕСЛИ(E3<=170; (E3-85)*0.15+6.8;(E3-170)*0.2+19.55))) |
|
H3 |
=E3*0.02 |
|
I3 |
=F3+G3 |
|
J3 |
=E3-H3 |
Для заповнення клітинок блоку Е4:J12 аналогічними формулами виділяємо інтервал клітинок E3:J3.
У правому нижньому куті виділеного інтервалу знаходиться маркер заповнення – маленький квадрат. Установити покажчик “миші” на маркер заповнення (при цьому він змінить свою форму на маленький хрестик) і, натиснувши (не відпускаючи) ліву клавішу “миші”, перетягнути його на клітинку J3. У результаті цих дій усі формули інтервалу F3:J3 копіюються та вирівнюються за місцем у виділеному інтервалі клітинок.
Рис. 9
Як тільки буде відпущена клавіша “миші”, в клітинках можна бачити результати обчислення (які звіряють із вихідними даними контрольного розрахунку). У випадку невідповідності результатів контрольним розрахункам треба переглянути введені формули та відредагувати помилки.
Рис. 10
Для підведення підсумків у стовпцях F:J запишемо формулу суми. Встановимо табличний курсор у клітинку F13. На панелі інструментів Стандартная натиснемо кнопку (автосума).
Рис. 11
У клітинці F13 відображується формула суми, а після натиснення клавіші <Enter> - результат. Установимо покажчик “миші” на маркер заповнення (табличний курсор у клітинці F13) і перетягнемо його в клітинку J13. У результаті цих дій у клітинках G13, H13, I13, J13 з’являться формули суми відповідних значень.
На завершальному етапі розроблення ЕТ для поліпшення сприйняття числових значень установити відповідний формат зображення числової інформації, шрифт та стиль текстової інформації, тип вирівнювання, тип та товщину ліній для обмежувальних ліній по границях клітинок. Найкраще для цього використовувати меню Формат, команду Ячейки. Спочатку необхідно виділити блок клітинок A2:J13, а потім використати вкладку Граница діалогового вікна Формат_ячеек .
Рис. 12
У результаті будемо мати електронну таблицю у вигляді, наведеному на рисунку 14 .
Збереження таблиці у файлі на диску виконується через меню Файл та команду Сохранить_как.
Для виконання сортування відомості за прізвищем необхідно виконати наступні кроки:
1 крок . Виділити таблицю списку.
2 крок. Данные/Сортировка або натиснути кнопку сортування на панелі інструментів.
3 крок. Встановити параметри сортування:
У полі Сортировать по встановити ім’я поля, за яким необхідно відсортувати список за збільшенням або зменшенням.
Рис. 13
У полі Идентифицировать поля по вказати перший рядок заголовка, який не бере участі у сортуванні.
Після натискання на кнопці <OK> таблиця матиме вигляд:
Рис. 14
Зробити вибірку співробітників, які отримали зарплату менше ніж 200 грн., можна двома способами: автофільтром або розширеним фільтром.
Для виклику фільтрації даних за допомогою Автофильтра необхідно виконати наступні кроки:
1 крок. Виділити заголовок списку.
2 крок. Данные/Фильтр/Автофильтр.
Програма Excel додасть до клітинок заголовка кнопки розкриття списку.
Рис. 15
3 крок. Установити параметри фільтрації:
Рис. 16
Після натискання кнопки <OK> на екрані відобразяться ті записи, які задовольнятимуть встановлений критерій.
Рис. 17
Колір номерів відфільтрованих записів та колір кнопок розкриття списку зміниться на блакитний.
Вибірку співробітників, які відпрацювали понад 100 годин і отримали зарплату більше ніж 200 грн., краще виконати за допомогою розширеного фільтра. Для цього необхідно виконати наступні кроки:
1 крок. На початку таблиці залишити декілька порожніх рядків, у перший з яких скопіювати заголовки стовпців списку.
2 крок. У комірки під певними заголовками записати відповідні умови.
Рис. 18
Між значенням умов та списком повинен бути як мінімум один рядок.
3 крок. Розмістити табличний курсор у списку.
4 крок. Виконати команду меню Данные/Фильтр /Расширенный фильтр.
5 крок. У полі Исходный диапазон установити: A5:J15.
У полі Диапазон условий установити: A1:J2.
У полі Обработка встановити опцію Фильтровать на месте.
Рис. 19
6 крок. Натиснути <OK>.
Після виконаних дій таблиця матиме вигляд:
Рис. 20
Підрахувати суму, яку отримав кожний відділ, можна за допомогою вбудованої функції Excel - Итоги. Для цього необхідно виконати наступні кроки:
1 крок. Розмістити табличний курсор у списку.
2 крок. Виконати сортування списку даних за Підрозділом.
Рис. 21
3 крок. Виконати команду меню Данные /Итоги.
4 крок. Установити параметри створення підсумків:
5 крок. Натиснути <OK>.
Після виконання всіх дій таблиця матиме вигляд:
Рис. 22
Щоб переглянути тільки загальні підсумки, необхідно натиснути на кнопку рівня 1. Щоб вивести проміжні і загальні підсумки, потрібно натиснути на кнопку рівня 2.
Коли розроблення таблиці закінчено і її роботу протестовано на вхідних даних різних варіантів контрольних розрахунків, переходять до побудови графіків та діаграм.
Графічне представлення числових залежностей відіграє важливу роль в аналізі даних. Табличний процесор Excel має велику кількість графічних можливостей. Для їх використання нам лише потрібно зазначити, яку інформацію необхідно використати у побудові графіків, тип графіка та місце його розташування в книзі-Excel. Усе це виконується за допомогою Мастера диаграмм, натиснувши кнопку панелі інструментів Стандартная.
Розглянемо на прикладі побудову діаграми за даними стовпця “До видачі”, створеної ЕТ. Виділимо інтервал клітинок В3:В12 та інтервал J3:I12 (з натисненою клавішею <Ctrl> ) . Після вибору кнопки виклику Мастера диаграмм з’явиться на екрані перше діалогове вікно для вибору типу діаграми. Виберемо об’ємну кругову діаграму (див.рис.23). Перехід до наступного кроку Мастера диаграмм відбувається через командну кнопку <Далее> . На кожному кроці є можливість повернутися на крок назад, відмінити побудову діаграми або закінчити побудову з тими параметрами, які є на цей час.
Рис. 23
На другому кроці можна змінити діапазон даних та підписати категорії.
Рис. 24
На третьому кроці в правій частині вікна з’являється зображення зовнішнього вигляду діаграми. Можна задати додаткові параметри: заголовок діаграми, місце розміщення легенди, підписи даних.
Рис. 25.
На останньому, четвертому, кроці вибираємо місце розташування діаграми – на окремому аркуші або на аркуші, де розміщена таблиця з даними. Якщо задаємо розташування на окремому листі, то також уводимо назву цього аркуша. В іншому випадку програма виконує вставку діаграми у зазначене місце таблиці.
Рис. 26
У результаті отримаємо кінцевий вигляд діаграми (рис.27).
Будь-які зміни, що будуть проводитися з відповідними даними ЕТ , тепер негайно відображатимуться в області графіка. У разі необхідності можна внести зміни у положення, розміри та саму діаграму.
Для зміни положення діаграми потрібно її виділити, помістити курсор над графіком та при натисненій лівій клавіші “миші” перенести діаграму на нове місце.
Для зміни розмірів діаграми необхідно її виділити і встановити курсор на один із маркерів, що оточують діаграму (невеликі квадратики по кутах та по середині меж діаграми), і при натисненій лівій клавіші “миші” перемістити маркер у потрібному напрямку.
Рис. 27
Після виділення за допомогою “миші” окремого елемента діаграми (надписів, легенди тощо) можна провести його редагування. В основному для виконання редагування використовується меню Формат або команди контекстного меню, викликаного за допомогою правої клавіші “миші” для виділеної області діаграми. Наприклад, якщо необхідно відредагувати всю область побудови діаграми, то після її виділення отримуємо таке контекстне меню:
Рис. 28
Використовуючи відповідні команди даного меню, можемо вставити або вилучити на діаграмі назви, позначки даних тощо. Форматування області побудови дозволяє змінити зовнішній вигляд діаграми доповненням кольорової палітри, рамок, фонових візерунків. Виділення іншої області діаграми приводить до появи відповідного контекстного меню.
Рекомендації до підготовки і проведення лабораторно-практичних робіт
1.Вивчити теоретичну частину теми.
2.Одержати у викладача пакет індивідуальних завдань. Ті, хто вивчає тему самостійно, мають скористатися наведеним вище прикладом.
3.Стосовно кожного завдання:
4.Оформити і захистити звіт (постановка задачі, макет таблиці із записаними в ній формулами та даними, контрольний приклад, віддруковані на принтері таблиця і діаграми).
Список літератури
|
Вступ............................................................................................................. |
3 |
|
Загальні відомості. Структура електронної таблиці ............................... |
3 |
|
Панелі інструментів ................................................................................... |
4 |
|
Типи даних. Уведення даних у клітинки ................................................. |
5 |
|
Копіювання інформації ............................................................................. |
9 |
|
Видалення інформації ............................................................................... |
10 |
|
Форматування ............................................................................................ |
10 |
|
Сортування даних ...................................................................................... |
13 |
|
Фільтрація даних зі списку......................................................................... |
13 |
|
Автоматичне обчислення загальних і проміжних підсумків.................. |
15 |
|
Розроблення електронної таблиці ............................................................ |
17 |
|
Рекомендації до підготовки і проведення лабораторно- практичних робіт …………………............................................................ |
30 |
|
Контрольні питання .................................................................................. |
30 |
|
Список літератури .................................................................................... |
31 |
маркер заповнення
Табличний курсор (активна клітинка)
Адреса актив-ної клітинки
Рядок формул (вміст активної клітинки)
Бордюр стовпців
Бордюр рядків
Заголовок вікна (робочої книги-Excel)
Рядок меню
Стандартна панель інструментів
Панель інструментів форматування
Кнопка редагування формули
Ярлик активного аркуша
Ярлики аркушів робочої книги
Кнопки прокрутки ярликів аркушів
Рядок статусу
Лінійка горизонтальної прокрутки
Лінійка вертикальної прокрутки
Поля для введення аргументів
Встановити менше Встановити 200
Кнопка рівня 1
Кнопка рівня 2
ім’я поля, за яким необхідно відсортувати список