Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Лабораторна робота № 1
"Знайомство з електронною таблицею Excel, формування таблиці"
Мета роботи: опанування технології створення файла презентації з використанням системи підготовки презентацій MS PowerPoint.
2. Завдання роботи
Освоїти прийоми переміщення по ЕТ і введення в її даних.
3.1. Завантажити Excel.
3.2.Ознайомитися з областями екрана; освоїти переміщення по екрану за допомогою миші і кнопок прокрутки; ознайомитися з меню інструментів. По черзі включити декілька пунктів головного меню, ознайомитися з принципом розташування і виклику їх опцій.
3.3. Сформувати таблиці згідно додатку ЛР1.
3.4.Заповнити стовпці таблиці даними; використовуючи автосумування обчислити підсумкові значення стовпців; оформити таблицю з допомогою команди Автоформат.
3.5. Зберегти таблиці в файлі і оформити звіт.
Звіт повинен містити:
- назву роботи, постановку задачі і відомості про послідовність її виконання;
- відповіді на контрольні питання п.7 вказівок;
- таблиці з розрахунками;
- висновки.
5. Загальні положення
5.1. Поняття про електронні таблиці
Інформаційні технології обробки даних часто вимагають представлення відомостей поданих у табличній формі. Для табличних розрахунків характерні відносно прості формули і великі об'єми вихідних даних.
Універсальним засобом для автоматизації розрахунків над табличними даними стали пакети прикладних програм (ППП), які називаються ТАБЛИЧНИМИ ПРОЦЕСОРАМИ або ЕЛЕКТРОННИМИ ТАБЛИЦЯМИ (ЕТ).
Електронна таблиця (ЕТ) – основа будь-якого табличного процесора. За термінологією, прийнятою в середовищі Exсel, вона називається робочим аркушем, або просто аркушем. Сукупність аркушів, розміщених в одному файлі, прийнято називати робочою книгою.
ЕТ створюються в оперативній пам'яті комп'ютера і зберігаються на магнітних дисках у папках і підпапках, що є за своєю суттю каталогами та підкаталогами. Послідовність папок, яка вказує, де знаходиться книга, називається шляхом до книги. Їх зміст можна редагувати, упорядковувати, виводити на друк. ЕТ можуть бути використані як для побудови діаграм так і для нескладної бази даних. Усі книги-файли Excel мають розширення .xls.
5.2. Екран Excel
Стандартний вигляд ЕТ – екран з сіткою, яка поділяє його на стовпці і рядки. Стовпці таблиці названі латинськими буквами і комбінаціями латинських букв від A до IV (256 стовпців). Рядки пронумеровані від 1 до 65536. Комірки, отримані на перетинанні стовпців і рядків, мають свою адресу, яка складається з букви, що позначає стовпець і цифри, що позначає рядок. Наприклад, комірка що знаходиться на перетинанні стовпця B и рядка 5, має адресу B5. Адресу комірки також називають посиланням. На екрані монітора, як правило, видно не більш18 повних рядків робочої таблиці і 9 повних стовпців із стандартною шириною 9 позицій, назву відкритої книги, а також основні елементи вікон Windows: КНОПКА СИСТЕМНОГО МЕНЮ, ПАНЕЛЬ ГОЛОВНОГО МЕНЮ, ПАНЕЛІ ІНСТРУМЕНТІВ, ПАНЕЛЬ ФОРМУЛ т. ін.
Рис. 1.1. Екран Excel з виділеним блоком комірок B4: D6
Назва головного вікна.
Для переміщення по робочому аркушу на значні відстані використовуються смуги прокрутки. Часто доводиться виконувати дії з декількома комірками одночасно, такими як блок або діапазоном комірок.
Блок – це множина комірок, що утворюють область прямокутної форми. Блок позначається адресою верхньої лівої і правої нижньої комірок прямокутника, розділених двокрапкою, наприклад: B4:D6, A5:A10, C3:E3.
5.3. Головне меню Excel
На верхній панелі знаходиться головне меню Excel. Воно має у своєму складі дев’ять спадних меню: Файл, Правка, Вид, Вставка, Формат, Сервис, Данные, Окно і Справка. Кожне з них виконує певні функції, має декілька команд керування процесором і активізується за допомогою миші або натисненням на клавішу Alt разом із клавішею літери, підкресленої в його імені, наприклад Alt+Ф і т. ін.
Вибір будь-якого пункту меню розкриває список його команд. Вибір команди, після якої вказані багато крапок, наприклад команда СОХРАНИТЬ КАК... в меню ФАЙЛ, приводить до появи діалогового вікна. Діалогові вікна можуть містити багато опцій, назви яких розташовані один за одним у вигляді карток. Наприклад, при виборі команди ПАРАМЕТРЫ СТРАНИЦЫ... пункту меню Файл відкривається діалогове вікно, яке містить чотири опції: Страница, Поля, Колонтитул, Лист, одна з яких активна в даний момент (рис. 1.2).
Рис. 1.2. Діалогове вікно „Параметри сторінки”
Вона дозволяє маніпулювати при друці представленими у вікні параметрами сторінки, такими як орієнтація, масштаб, розмір аркушу т. ін. Для вибору іншої опції вікна потрібно клацнути мишею по її заголовку.
Команди в головному меню не завжди доступні. Недоступна в даний момент команда пофарбована тьмяним сірим кольором. Вона залишається тьмяною до того часу, поки не з'являться умови для її роботи.
Виконання деяких команд також може здійснюватись через клавіші швидкого доступу, які позначені справа від назви команди.
5.4. Панелі інструментів
Багатьом командам в рядку меню відповідають кнопки на панелі інструментів, за допомогою яких здійснюються швидкий і наочний вибір та виконання команд. Наприклад, кнопка з зображенням ножиців, виконує команду ВЫРЕЗАТЬ, а кнопка з зображенням машинки для друкування, виконує команду ПЕЧАТЬ. При запуску програми на екрані автоматично з'являються дві панелі інструментів: стандартна і форматування, тому що вони містять інструменти які найчастіше використовуються. Однак, Excel має в своєму арсеналі багато інших панелей інструментів, які також можна висвітити в вікні Excel за допомогою команди ВИДПАНЕЛИ ИНСТРУМЕНТОВ.
Рис. 1.3. Вікно меню "Панелі інструментів"
При пересуванні покажчика миші на будь-яку кнопку панелі інструментів, під нею висвітлюється невелика підказка про призначенню кнопки.
5.5. Контекстно-залежне меню
Для зручності виконання задач, пов'язаних з окремими об'єктами екрана (такими як панель інструментів, вікно документа чи комірка робочої таблиці), Excel в доповнення до головного меню також пропонує вторинну систему КОНТЕКСТНЫХ МЕНЮ. Вони містять лише команди, які стосуються даного об'єкту.
Рис. 1.4. Контекстно-залежне меню комірки
Для виклику контекстно-залежного меню використовується права кнопка миші, котра натискається в момент наведення покажчика миші на потрібний об'єкт. Наприклад, наведення покажчика на комірку і натиснення на праву кнопку миші викликає контекстне меню комірки, яке містить пункти ВЫРЕЗАТЬ, КОПИРОВАТЬ, ВСТАВИТЬ т. ін.
5.6. Типи даних
В Excel розрізняються три основні типи даних: числові значення, текстові значення і формули.
Числові значення – це числа, що представляють кількісні величини, і числа, які розглядуються як дати і час. Будь-які числові значення можуть бути використані як аргументи для формул. Цей тип даних вирівнюється по правому краю комірки.
Щоб програма сприймала введені час і дату як числа, потрібно скористатися стандартним форматом введення. При цьому Excel не робить різниці між рядковими і прописними літерами і при введенні дат дозволяє використовувати крапку (.), похилу рису (/), або дефіс (-).
Якщо при введенні часу після цифр введені літери АМ чи А (позначає до полудня), або РМ чи Р (після полудня), то час на екрані відображається в 12-часовом форматі.
Приклади завдання часу і дати в Excel приведені в дужках після указівки відповідного формату: Д.М.ГГ (10/25/94 або 10-25-94), Д.МММ.ГГ (25-Окт-94), Д.МММ (10-Окт), МММ.ГГ (Окт-94), Ч.ММ М/РМ (3:21 РМ), ЧЧ.ММ (15:21), ЧЧ.ММ.СС (15:21:04) і іншими. Можна ввести дату і час в одну комірку, розділивши їх пробілами (25-Окт-94 15:21).
Текст – узагальнена категорія даних, яка має на увазі все, що не має справи з числами і обчисленнями. Текстові дані можуть складатися з букв, цифр, знаків пунктуації, пробілів. Текстові величини автоматично вирівнюються по лівому краю комірки. Також можна ввести число у вигляді тексту, поставив перед ним знак апострофа (‘).
Формули в ЕТ починаються із знака дорівнює (=), який відрізняє введення тексту від введення формули. Наприклад: =b5*c5*d5. Після впровадження формули в комірку, вона видна в рядку формул, а в самій комірці висвітлюється результат обчислень. Формули можуть складатися з чисел, посилань на комірки і вбудованих функцій, таких як СУММ, СРЗНАЧ і ін., розділених операторами додавання (+), віднімання (-), множення (*), ділення (/), зведення у ступінь(^).
5.7. Одержання допомоги
Одержати довідку Excel можна у будь-який час, використовуючи:
- команду “?” головного меню;
- інструмент (на стандартній панелі інструментів);
При виборі команди “?” з'являється список термінів і вибирається потрібний.
5.8. Відносні і абсолютні посилання
При виконанні операцій копіювання і дублювання формульних даних, які містять посилання на комірки, Excel автоматично корегує їх, змінюючи номер рядка або найменування стовпця в залежності від напрямку руху. Наприклад, формула A1+B1, що знаходиться в комірці С1 і яка містить посилання на A1 і B1, при копіюванні в комірку С2 (вниз на рядок) перетвориться в A2+B2. Змінювані посилання називаються відносними. Таке корегування в багатьох випадках дуже зручне, однак іноді зовсім не потрібне по суті виконуваної задачі або структури таблиці. Щоб посилання в процесі копіювання не змінювались, їх роблять абсолютними, встановивши знак долара перед літерою стовпця і перед номером рядка, наприклад $A$1. Допускається конструювання змішаних посилань, наприклад $A1. Адрес стовпця даної комірки є постійним, а адреса рядка – відносною.
Рис. 1.5. Приклади копіювання формули з використанням абсолютних і змішаних посилань
На рис. 1.5 зображені два фрагмента таблиць з прикладом копіювання формули з комірки A5 в комірки A6, B5 і B6 таблиці. У верхньому фрагменті зміст комірок представлено у вигляді формул, а в нижньому – результат обчислення по цим формулам. У формулах використані абсолютне посилання на всю комірку і змішані посилання.
5.9 Введення і редагування вмісту комірки
Зазвичай, при запуску Excel на екрані товстою рамкою виділена комірка з номером A1, яка готова до прийому інформації. Щоб ввести інформацію в іншу комірку, її виділяють за допомогою миші або клавіш керування курсором (клавіш із стрілками). Введення здійснюється по правилам, зазначеним в п.6.6 для різних типів даних. По мірі набору даних з клавіатури Excel показує всі введені символи як на панелі формул, що знаходиться зверху, так і в активній комірці на робочому аркуші. На панелі формул активізуються кнопки: ОТМЕНЫ (позначена хрестом Х); ВВОДА, (позначена галочкою) і Мастер функций (позначений fx).
Щоб набрана інформація була внесена в поточну комірку робочого аркуша, вона повинна бути підтверджена одним з наступних дій з використанням клавіш або миші:
- натисканням клавіші введення, або виходом з поточної комірки за допомогою клавіші управління курсором.
- клацанням миші по інший комірці, або по кнопці введення позначеній галочкою;
Для виправлення помилок в час заповнення комірки, до того як введення інформації підтверджено, можна скористатися клавішею Backspace або Delete. Для видалення всього набраного тексту потрібно за допомогою миші натиснути кнопку з хрестом (х).
Виправити вже підтверджений зміст комірки можна як в рядку формул, так і безпосередньо в комірці. Для цього потрібно:
1) вибрати потрібну комірку;
2) двічі клацнути кнопкою миші або натиснути клавішу F2;
3) відредагувати зміст;
За допомогою клавіші Insert можна змінити режим вставки символів на режим заміни.
Щоб здійснити очищення комірки, при якому знищується вміст комірки без її видалення з робочої таблиці, потрібно: або натиснути <Del>, а потім <Enter>, або вибрати з контекстного меню комірки команду ОЧИСТИТЬ СОДЕРЖИМОЕ, або виконати команду головного меню ПРАВКАОЧИСТИТЬ СОДЕРЖИМОЕ .
Для видалення комірки з її змістом і атрибутами форматування, потрібно вибрати команду УДАЛИТЬ з контекстного меню комірок або ПРАВКАУДАЛИТЬ з головного меню. Excel відобразить діалогове вікно, в якому потрібно вказати, як зсунути сусідні комірки для заповнення місця що звільнилося, після видалення на робочому аркуші.
5.10. Маніпуляції з інтервалом комірок
5.10.1. Виділення інтервалу комірок.
Для деяких видів перетворень в таблиці потрібно попередньо виділити декілька комірок, які складають інтервал рядків, стовпців і т. ін. Можна виділити:
- рядок або стовпець, клацнувши мишею по номеру рядка чи стовпця;
- декілька рядків чи стовпців, клацнувши мишею на першому номері рядка і протягти покажчик миші по рядкам (стовпцям) що виділяються;
- весь робочий аркуш, клацнувши по кнопці, що знаходиться перед заголовком стовпців і над заголовком рядків в лівому куті робочої книги;
- блок комірок, встановивши курсор у верхній лівий кут блоку і протягти покажчик миші до правого нижнього кута блоку, або, встановивши курсор в лівий верхній кут, натиснув клавішу Shift, і не відпускаючи її, рухатися за допомогою клавіш керування курсором до правої нижньої комірки.
5.10.2. Переміщення і копіювання змісту комірок.
Excel підтримує стандартні прийоми використання буфера обміну для копіювання і переміщення інформації в середині відкритої книги, в іншу книгу чи навіть в інші програми, запущені в Windows.
Для копіювання (переміщення) змісту комірок потрібно:
1) виділити комірки що копіюються або переміщуються (див. п.5.10.1);
2) виконати команду ПРАВКАКОПИРОВАТЬ (ПРАВКАВЫРЕЗАТЬ);
3) пересунути покажчик миші на нову позицію і виділити першу комірку майбутнього блока;
4) виконати команду ПРАВКАВСТАВИТЬ.
Копіювання дозволяє розмножити вміст однієї комірки в інші комірки електронної таблиці. Для цього, при указівці позиції вставки, виділяють не одну, а декілька комірок, де повинна з'явитися копія.
Для переміщення блока комірок в середині відкритої книги можна застосувати також мишу, для цього потрібно:
1) виділити блок комірок;
2) встановити покажчик миші на одній з границь відзначеного блоку (покажчик прийме вигляд стрілки);
3) протягти виділену область комірок до потрібного місця робочої таблиці.
Потрібно пам'ятати, що якщо в комірці що копіюється або переміщується знаходиться формула, то в ній відбувається корегування відносних посилань.
5.10.3. Вставка і видалення інтервалу комірок.
Вставка нової комірки, стовпця чи рядка здійснюється за допомогою пункту ВСТАВКА головного меню. Попередньо комірка, рядок чи стовпець перед якими повинна поміститися вставка, повинні бути виділені, як зазначено в п.5.10.1. Для вставки декількох строк чи стовпців потрібно виділити скільки рядків чи стовпців, скільки ви хочете вставити. Тоді перед першим виділеним стовпцем чи першим рядком уставиться стільки нових стовпців або рядків, скільки ви виділили. Пункт меню ПРАВКАУДАЛИТЬ видаляє виділений діапазон.
5.11. Зміна розмірів стовпця і рядка
Якщо текст, введений в комірку, довше ширини стовпця, то він буде показаний в наступних стовпцях, при умові, що вони порожні. В протилежному випадку, текст на екрані буде обрізаний границею наступного стовпця.
У випадках, коли стандартна ширина стовпця не влаштовує вас, її змінюють одним з наступних способів, попередньо виділивши стовпець:
- командою ФОРМАТСТОЛБЕЦШИРИНА головного меню;
- командою ШИРИНА СТОЛБЦА контекстного меню стовпця;
- за допомогою миші, пересуваючи границю стовпця в полі заголовків стовпців;
- автоматично установлюючи ширину стовпця по самому довгому запису в комірці виділеного стовпця командою ФОРМАТСТОЛБЕЦАВТОПОДБОР ШИРИНЫ головного меню.
Для зміни висоти виділеного рядка використовують два прийоми:
- командою ФОРМАТСТРОКА-ВЫСОТА головного меню викликати поле ВЫСОТА, де задати необхідну висоту рядка;
- за допомогою миші встановити курсор на нижню лінію сітки потрібного рядка в поле заголовків рядків. При цьому курсор перетвориться в перехрестя. Протягти цю лінію вгору чи вниз, відповідно зменшуючи або збільшуючи висоту рядка.
5.12. Автоматичні операції підсумовування, дублювання і заповнення суміжних комірок
Для обчислення підсумкової суми по числовим значенням в суміжному інтервалі комірок використовується інструмент АВТОСУММИРОВАНИЕ, позначений математичним символом суми, що знаходиться на панелі стандартних інструментів. Техніка автопідсумовування наступна:
1) встановити курсор в комірку, де повинна з'явитися підсумкова сума;
2) клацнути по кнопці å, при цьому в самій комірці і в панелі формул з'являється функція СУММ, з обраним комп'ютером діапазоном комірок, що знаходиться над зазначеною коміркою, або зліва від неї;
3) натиснути клавішу <Enter>, якщо діапазон, зазначений комп'ютером нас влаштовує, в протилежному випадку його попередньо редагують (див. п.5.9.2).
ДУБЛЮВАННЯ даних використовується для введення формул і провадиться в наступній послідовності:
1) виділити блок комірок, куди потрібно помістити формулу (див. п.5.10.1);
2) набрати з клавіатури формулу і натиснути <Ctrl+Enter>, щоб внести запис у всі позначені комірки.
Багато робочих таблиць потребують введення послідовних дат, часу і інших типів рядів (наприклад, стовпці, що позначають послідовність місяців з січня по грудень або рядки, пронумеровані від 1 до 100). Для створення таких послідовностей використовують АВТОМАТИЧНЕ ЗАПОВНЕННЯ. Воно може бути використано і для введення формул в суміжні комірки таблиці, при цьому посилання в формулах автоматично змінюються.
Автозаповнення виконується за допомогою маркера заповнення, що знаходиться в правому нижньому куті виділеної комірки або блока комірок.
Спочатку в комірки електронної таблиці вводять початкові значення послідовності. Для послідовності з кроком зміни 1(один місяць, один день, один квартал і ін.) достатньо одного першого значення в одній комірці.
Увага! Просто будь-яка одна цифра не породжує послідовність.
Для одержання ряду з довільним кроком зміни або послідовного числового ряду вводять два значення – зразка, в сусідні комірки, які показують зміни між сусідніми елементами послідовності (див. рис.1.6., комірки G1:G2; H1:H2 і т.д.).
Комірки (одну чи дві, в залежності від виду послідовності) з початковими значеннями виділяють, встановлюють курсор на маркері заповнення (курсор перетворюється в тонкий чорний хрестик) і протягують його по всім заповнюваним коміркам.
При цьому Excel або створює послідовність (якщо є впевненість, що початкове значення породжує послідовність) у всіх відзначених вами комірках, або заповнює інтервал початковими значеннями.
Рис. 1.6. Фрагмент електронної таблиці з різними значеннями в комірках
Рис. 1.7. Фрагменти рядів, отриманих шляхом автозаповнення
На рис. 1.7 в стовпцях А:Е показані приклади рядів з кроком зміни 1. Стовпець А містить послідовні позначення місяців з січня по червень, в стовпці В – дати від 04.01.91 до 09.01.91, в стовпці С – нумерація кварталів (причому після 4 кварталу в комірці С5 знову з'являється квартал 1) т.ін. В стовпцях G:K показані фрагменти рядів, отриманих по двом першим значенням. Стовпець G містить позначення днів тижня через один (Пн, Ср, Пт, Нд, Вт т.ін.), в стовпці H – час змінюється не тільки по годинам, але й по хвилинам (після 24 годин або 60 хвилин нумерація знову починається з початку) т. ін.
5.13. Збереження таблиці на диску
Для збереження створеної таблиці на диску Excel пропонує інструмент ЗБЕРЕГТИ, представлений кнопкою з зображенням дискети, або команду ФАЙЛ СОХРАНИТЬ головного меню. При першому натисканні на інструмент СОХРАНИТЬ програма видає діалогове вікно СОХРАНИТЬ КАК..., яке використовують для вказівки більш змістовного імені, нового диска або вибору нового каталогу перед збереженням.
6.1. Завантаження програми Excel
Після вмикання ПК і введення відповідного пароля автоматично завантажується ОС Windows. Є декілька способів завантаження Excel:
клацанням ми шею на кнопці «Пуск» викликати головне меню систе ми, в якому вибирати каскадне меню «Программы», а по тім - «Місrosoft Excel».
за допомогою піктограми-ярлика . Таблич ний процесор завантажують подвійним клацанням на ньо му мишею - і на екрані монітора з'являється основне вікно Excel.
Примітка Ярлик створюють, якщо Excel використовують повсяк день. Для цього необхідно послідовно відкрити вікно про грами «Проводник» і його папки — Windows, Главное меню та Программы. В останній є ярлик Excel, який «буксирується» на вільне місце робочого стола Windows при натисканні кла віші <Сtrl>.
Очищують робочий стіл командою «Свернуть все» спеці ального контекстного меню, що викликається на екран кла цанням правою клавішею миші.
6.2. Формування таблиці
Розглянемо основні прийоми створення таблиць на прикладі таблиці, представленої на рисунку 1.8.
Рис. 1.8. Приклад сформованої таблиці
6.2.1. Оформлення заголовка таблиці і назв стовпців.
Для оформлення заголовка таблиці встановимо курсор в комірку A1 і введемо текст, наприклад Визначення витрати вакцини для профілактики. Введення тексту закінчуємо натисненням клавіші <Enter>, або іншим способом, зазначеним в п.5.9.1
В комірку A2 введемо текст: ...ящуру в господарстві на одну тварину у ваговій групі. Набраний текст поширюється в сусідні комірки, тому що в них відсутня будь-яка інформація.
Для введення в 3 рядок таблиці триповерхових заголовків потрібно дозволити переносити текст по словам. Для цього:
1) виділимо 3 рядок (див. п.5.10.1);
2) виконаємо команду ФОРМАТЯЧЕЙКИ;
3) на вкладці ВЫРАВНИВАНИЕ включимо прапорець ПЕРЕНОСИТЬ ПО СЛОВАМ.
6.2.2. Форматування заголовків стовпців.
Встановимо курсор по черзі в комірки A3, В3, С3 і введемо заголовки стовпців. Щоб текст заголовків розташовувався рівномірно, як показано на рис. 1.8, збільшимо ширину стовпців В і С, за допомогою миші. Для зміни висоти рядка скористаємося прийомом автоматичної встановлення висоти.
6.3. Введення даних і редагування таблиці
6.3.1. Використання прийому автозаповнення для введення номерів вагової групи і середньої ваги тварин.
1) введемо в комірку A4 цифру 1;
2) введемо в комірку A5 цифру 2;
3) виділимо інтервал комірок A4: A5 (см. п.5.10.1);
4) встановимо курсор на маркері заповнення комірки A5;
5) протягнемо маркер до комірки A13.
Щоб заповнити стовпець B значеннями середньої ваги тварин у ваговій групі від 50 до 200 і від 200 до 350, використовуємо прийом автозаповнення, вказавши у кожному випадку новий крок зміни.
6.3.2. Заповнення таблиці формулами для обчислення.
Витрата вакцини на одну тварину розраховується по формулі У=0,05+0,001*Х, де Х- вага тварини. Для автоматичного розрахунку значень третього стовпця таблиці по даній формулі, введемо її в комірку С4 у вигляді = 0,05+0,001*B4 і потім виконаємо автозаповнення (або копіювання, як вказано в п.5.10.2). В результаті в комірках С4:С13 з'являться числа 0,1; 0,125; 0,15; 0,175; 0,2; 0,225; 0,25; 0,275; 0,35; 0,4.
6.3.3. Підрахунок підсумкових значень.
Заповнимо четвертий стовпець таблиці даними про кількість тварин по кожній групі. Обчислимо загальну кількість тварин в господарстві, використовуючи інструмент Автопідсумовування (см.п.5.12.1).
6.3.4. Оформлення рамок таблиці.
Для більш наглядного уявлення даних у створеній таблиці, її розділяють на область заголовка, підніжжя т. ін., використовуючи прийом автоформатування.
1) виділимо діапазон A1: D14;
2) виконаємо команду ФОРМАТАВТОФОРМАТПРОСТОЙ.
6.4. Збереження створеної таблиці на диску
1) виконаємо команду ФАЙЛ СОХРАНИТЬ КАК;
2) в діалоговому вікні що з'явився введемо підходяще по змісту таблиці ім'я файла, наприклад ВAKЦИНА, залишивши розширення файлу .XLS без зміни;
3) клацнемо по кнопці Ok.
Лабораторна робота № 2
"Зв'язування електронних таблиць Excel"
1. Мета роботи
Вивчити принцип роботи з декількома зв'язаними таблицями.
2. Задачі роботи
Освоїти прийоми пов'язування електронних таблиць в Excel.
3. Зміст роботи
3.1 Завантажити оболонку Windows, запустити додаток Excel, завантажити файл з таблицею, створеною в лабораторній роботі №1.
3.2 Відкрити друге вікно для перегляду наступного аркуша, впорядкувати розташування вікон на екрані.
3.3 На другому аркуші сформувати таблицю, зазначену викладачем (див. Додатки ЛР2), об'єднану по змісту з таблицею, створеною раніше. Пов'язати дані таблиць за допомогою команди СПЕЦИАЛЬНАЯ ВСТАВКА і прямим пов'язуванням.
3.4 Перейменувати обидва аркуші, задавши їм змістовні назви, видалити аркуші робочої книги, які не використовуються.
3.5 Зберегти свій файл на дискеті та оформити звіт.
4. Вимоги до звіту
Звіт повинний містити:
- назву роботи, задачі і послідовність її виконання;
- відповіді на контрольні питання п.7 вказівок;
До звіту додається дискета з результатом роботи.
5. Загальні положення
5.1. Аркуші робочої книги
В Excel можна працювати одночасно з декількома таблицями, розташованих на РОБОЧИХ ЛИСТАХ (АРКУШАХ), які поєднуються в РОБОЧІ КНИГИ. Використання робочих аркушів, розташованих в одній книзі, тобто в одному файлі, полегшує роботу з декількома таблицями або діаграмами, зв'язаними по змісту або загальними даними, і допомагає проводити наступний аналіз даних.
Робочі аркуші можуть мати власні імена. В новій робочій книзі аркуші нумеруються від Лист 1 до Лист 16. При запуску Excel на екрані з'являється робоча книга з ім'ям Книга 1, в якій відкритий перший робочий лист.
В нижній частині екрана Excel розташовані ЯРЛИЧКИ з іменами робочих аркушів і КНОПКИ ПРОКРУЧУВАННЯ ярличків.
Для переходу в інший робочий аркуш книги потрібно клацнути лівою кнопкою миші на його ярличку. Якщо ярличок аркушу не видний на екрані, можна правою кнопкою миші клацнути на кнопках прокручування ярличків і у списку аркушів що відкрився, вибрати потрібний.
Клацання правою кнопкою миші на ярличку аркушу відкриває його контекстно-залежне меню, що складається з п'яти команд: ВСТАВКА..., УДАЛИТЬ, ПЕРЕИМЕНОВАТЬ..., ПЕРЕМЕСТИТЬ/СКОПИРОВАТЬ, ВЫДЕЛИТЬ ВСЕ ЛИСТЫ, за допомогою яких можна керувати робочими аркушами книги.
5.2. Вставка, видалення і перейменування робочого аркуша
Після вибору команди ВСТАВКА з контекстно-залежного меню аркушів на екрані з'являється діалогове вікно, в якому можна вибрати тип аркушу що вставляється: робоча таблиця, діаграма або ін. Непотрібний або порожній аркуш можна видалити командою УДАЛИТЬ з контекстно-залежного меню аркушів.
Можна видалити відразу декілька робочих аркушів, попередньо виділивши їх. Виділення провадиться за допомогою лівої кнопки миші при натиснутій клавіші Ctrl. Після вибору команди Excel запитає вас, чи дійсно ви бажаєте видалити робочі аркуші.
Для щоденної роботи перейменування робочих аркушів дуже важливе, тому що значно зручніше звертатися до аркушів, що мають змістовні назви. Перейменування виконується командою ПЕРЕИМЕНОВАТЬ з контекстно-залежного меню аркушів, в однойменному діалозі, в якому потрібно ввести нове ім'я для робочого аркуша.
5.3. Переміщення і копіювання робочих аркушів
Можна легко переміщати робочі аркуші в середині робочої книги, змінюючи порядок їх чергування. Також є можливість копіювання робочих аркушів, причому переміщати і копіювати робочі аркуші можна і в інші книги. Для цього використовується команда ПЕРЕМЕСТИТЬСКОПИРОВАТЬ з контекстно-залежного меню аркушів. В діалоговому вікні що з'явилося на екрані, за допомогою кнопки-перемикача СОЗДАВАТЬ КОПИЮ можна вибрати як переміщення так і копіювання робочого аркуша. В полі В КНИГУ даного діалогу можна вказати книгу, в яку необхідно перемістити або скопіювати робочий аркуш. По умовчанню в цьому полі знаходиться назва поточної книги. В полі ПЕРЕД ЛИСТОМ вказується позиція, куди потрібно перемістити або скопіювати аркуш.
Переміщати або копіювати робочі аркуші можна і за допомогою миші, використовуючи прийом перетягування ярличка на потрібну позицію. Під час перетягування покажчик миші приймає форму стрілки з листом паперу. Якщо ви хочете скопіювати робочий аркуш, то під час перетягування натисніть клавішу CTRL.
5.4. Поєднання робочих аркушів
Якщо на одному робочому аркуші використовуються дані з іншого аркушу, то ці аркуші вважаються поєднаними (зв’язаними). За допомогою поєднання можна звести в одну таблицю (комірку) значення комірок з декількох різних таблиць.
Зміна змісту комірки на одному аркуші (аркуші-джерелу) робочої книги приводить до змін поєднаних з нею комірок в аркушах-приймачах. Цей принцип відрізняє поєднання аркушів від простого копіювання змісту комірок з одного аркушу в інший. В залежності від техніки виконання, поєднання (зв’язування) буває “прямим“ і через команду СПЕЦИАЛЬНАЯ ВСТАВКА.
Пряме поєднання аркушів використовується при введенні формули в комірку одного аркушу, в котрій в якості одного з операндів, використовується посилання на комірку другого аркушу. Якщо в комірці таблиці (наприклад, в робочому Листе2) міститься формула, в якій використовується посилання на комірку іншого робочого аркушу (наприклад, на комірку А1 робочого Листа1) і обидва аркуші завантажені, то таке поєднання вказаних аркушів називається “прямим”. Термін “пряме” поєднання позначає, що користувач сам безпосередньо при введенні формули вказує ім’я аркушу і абсолютну адресу комірки, розділених знаком "!".
Приклади формул:
= C5*Лист1! A1
= Лист3! В2*100
= Лист1! A1- Лист2! A1
Для вказівки посилання на аркуші, що знаходяться в незавантажених робочих книгах, в формулі потрібно задати без прогалин повний шлях місцезнаходження файлу. Шлях задається в одинарних лапках, де вказується назва диска, каталогу, ім’я робочої книги в квадратних дужках та ім’я аркушу, на який йде посилання.
Наприклад = 'C:\ EXCEL\[ КНИГА1.XLS] Лист1'!A1*С5
Поєднання через команду СПЕЦИАЛЬНАЯ ВСТАВКА проводиться, якщо яка-небудь комірка таблиці на одному робочому аркуші повинна містити значення комірки з іншого робочого аркушу.
Щоб внести в Лист2 значення комірки A1 з вихідного Листа1, потрібно виділити цю комірку і вибрати команду ПРАВКАКОПИРОВАТЬ. На другому аркуші поставити курсор на ту комірку, куди потрібно копіювати, і виконати команду ПРАВКАСПЕЦИАЛЬНАЯ ВСТАВКАВСТАВИТЬ СВЯЗЬ. На другому аркуші з’явиться вказівка на комірку вихідного аркуша, наприклад: = Лист1!$A$1 .
При такому поєднанні EXCEL автоматично переводить відносну адресу в абсолютну, тому що, якщо адреса звертання не абсолютна, це може привести до помилок, особливо якщо звертатись до незавантажених файлів.
5.6. Робота с декількома вікнами
Через команди пункту меню ОКНО Excel забезпечує усе різноманіття роботи з віками в середовищі Windows.
Командою РАЗБИТЬ екран Excel можна “розбити” на чотири вікна (два горизонтальних і два вертикальних). Тоді в чотирьох вікнах відображуються різні частини одного робочого аркушу, причому точка перетину вікон пройде поруч з активною в момент розподілу коміркою таблиці. Переходячи з одного вікна в інше і переміщуючись в робочому просторі, можна встановити зручне для роботи розташування таблиці. Встановивши покажчик миші на перетин вертикальної і горизонтальної ліній, що поділяють аркуш (курсор перетворюється в хрестик) і переміщуючи його, можна маніпулювати розмірами і взаєморозміщенням вікон на екрані.
Щоб видалити розподіл на підвікна потрібно двічі клацнути на лінії розподілу вікон. Можна також залишити тільки вертикальні або горизонтальні вікна.
Командою НОВОЕ ОКНО на екрані можна “відкрити” декілька додаткових вікон. В цьому випадку в них можуть відображуватись різні частини одного аркушу, різні аркуші однієї або різних книг.
Для закриття додаткового вікна в його системному меню потрібно викликати команду ЗАКРЫТЬ або скористатися комбінацією клавіш CTRL+F4.
Максимальна кількість додаткових вікон обмежена тільки можливостями оперативної пам’яті вашого комп’ютера. Знову відкриті вікна EXCEL автоматично нумерує. Номер проставляється через двокрапку після імені файлу, що виводиться у вікні. При збереженні файлу, інформація про додаткові вікна не знищується, і при наступних викликах програми вони завжди у вашому розпорядженні.
Використовуючи сполучення додаткових відкритих і поділених на частини вікон, можна добитись максимальної зручності в роботі з таблицями.
Командою УПОРЯДОЧИТЬ організують велику кількість вікон на екрані. В діалоговому вікні УПОРЯДОЧИТЬ ОКНА є чотири варіанти групування вікон: МОЗАИКА, ПО ГОРИЗОНТАЛИ, ПО ВЕРТИКАЛИ, ОКНА АКТИВНОЙ РАБОЧЕЙ КНИГИ. Перша опція рівномірно розподіляє відкриті вікна на робочій сторінці EXCEL. Друга розмістить всі вікна одне над одним в повному обсязі. Третя – розподілить вікна одне поруч з іншими. Четверта перенесе всі вікна робочого аркушу на передній план. Вікна інших документів EXCEL розмістить на задньому плані.
5.7. Зміна, приховування і згортання вікон
Як правило, є можливість перейти в інше вікно, активізувавши необхідне вікно клацанням миші. Для виклику вікна закритого іншими вікнами, потрібно звернутись до меню ОКНО, де знаходиться список вікон, котрі в даний момент доступні. Вводячи з клавіатури цифру що стоїть перед ім’ям файлу або клацнувши лівою кнопкою миші на потрібному рядку меню, ви переведете вікно на перший план.
Якщо в даний час робота з відкритим вікном не ведеться, то його можна "приховати". Це робиться за допомогою команди меню ОКНО-СКРЫТЬ. Робочий аркуш, при цьому не видаляється, а робиться невидимим.
Вікно можна зменшити до розмірів значка одним з трьох способів:
- за допомогою кнопки мінімізації вікна, що знаходиться в правому верхньому куті вікна;
- командою СВЕРНУТЬ з системного меню вікна;
- комбінацією клавіш CTRL+F9;
За допомогою команди меню ОКНО УПОРЯДОЧИТЬ ЗНАЧКИ, котра з’являється, коли ви зменшили вікна до значків. Це дає можливість оптимально розмістити значки усередині вікна EXCEL.
5.8. Збільшення та зменшення розмірів зображення у вікнах
Для кожного вікна можна визначити необхідний для зручної роботи з ним розмір зображення. Для цього у програмі передбачена команда ВИДМАСШТАБ що відкриває однойменний діалог, де можна збільшити або зменшити зображення в межах від 10 до 400%. Цей діалог містить дві опції: ПО ВЫДЕЛЕНИЮ і ПОЛЬЗОВАТЕЛЬСКИЙ.
Перша з них автоматично вибирає масштаб збільшення, щоб на екрані були видні тільки виділені комірки, за допомогою другої можна самим задати будь-який процент збільшення або зменшення зображення.
5.9. Зміна відносних посилань в абсолютні і навпаки
Іноді виникає необхідність зміни абсолютних посилань у змішані або відносні. Такі перетворення можна виконати автоматично за допомогою клавіші F4. Для цього виділяють потрібну комірку, в панелі формул, курсором вказують на посилання, яке потрібно змінити і декілька разів натискують клавішу F4.
При кожному натисканні, посилання модифікується з відносного в абсолютне, змішане і знову у відносне. В процесі модифікації посилання можна зупинити на потрібному варіанті і підтвердити його натисканням на клавішу введення.
6. Методичні рекомендації
6.1. Завантаження файлу з таблицею
Щоб продовжити роботу з книгою, що містить створену і збережену раніш таблицю, виконаємо команду головного меню: ФАЙЛОТКРЫТЬ. У вікні що з’явиться, в полі Имя файла потрібно задати ім’я необхідного файлу (наприклад, Вакцина.xls) і натиснути ОК, або вибрати його за допомогою подвійного клацання лівою кнопкою миші на запропонованому списку.
6.2. Відкриття і впорядкування вікон
Для одночасного перегляду двох таблиць на різних аркушах робочої книги потрібно розмістити обидва аркуші в двох вікнах. Це можна зробити шляхом виконання:
1) команди головного меню ОКНОНОВОЕ ОКНО і потім ОКНОУПОРЯДОЧИТЬМОЗАИКА;
2) у другому вікні клацанням на ярличку іншого аркушу.
6.3. Формування поєднаної таблиці
Перейдемо на Лист 2 у другому вікні і сформуємо там таблицю, яка представлена на рис. 2.2. Заголовок таблиці введемо звичайним способом.
Заголовки двох перших стовпців можна повністю скопіювати з таблиці на аркуші 1:
1) виділимо комірки А3:В3 у таблиці на першому аркуші (див. рис. 2.1) ;
2) виконаємо команду головного меню ПРАВКА-КОПИРОВАТЬ;
3) виділимо комірку A3 на другому аркуші ;
4) виконаємо команду ПРАВКАВСТАВИТЬ.
Заголовки стовпців при копіюванні зберегли формат оформлення, тому в них використовується встановлене раніш перенесення слів. Заголовки третього і четвертого стовпців введемо з клавіатури, попередньо задавши для комірок С3 і D3 дозвіл переносити текст по словам. Використаємо зміни ширини стовпця і висоти рядка для рівномірного розміщення тексту у всіх стовпцях.
Для заповнення першого стовпця номерами вагових груп тварин можна використати прийом копіювання даних.
Стовпець 2 заповнимо відповідними значеннями з таблиці на першому аркуші. Використаємо для цього прийом поєднання через команду СПЕЦИАЛЬНАЯ ВСТАВКА:
1) виділимо комірки В4:В13 на першому аркуші;
2) виконаємо команду ПРАВКА КОПИРОВАТЬ;
3) виділимо комірку В4 на другому аркуші;
4) виконаємо команду ПРАВКАСПЕЦИАЛЬНАЯ ВСТАВКА ВСТАВИТЬ СВЯЗЬ;
В подальшому при зміні цих значень на першому аркуші комп’ютер автоматично змінить їх і на другому. Змінювати ці дані у другій таблиці не рекомендується, тому що це можна зробити, тільки встановивши між ними зв’язок. При спробі зміни залежних даних, з’являється попереджуюче діалогове вікно, змінити яке можна клавішею Esc.
Рис .2.1 Фрагмент вікна Excel, що містить вихідну таблицю на Аркуші 1
Кількість тварин в групі в третій стовпець таблиці введемо з клавіатури.
Для находження Витрат вакцини на групу потрібно Витрати вакцини на одну тварину з таблиці 1 помножити на кількість тварин у групі з другої таблиці, тобто використати формулу: = С4*Лист1!C4.
При заповненні четвертого стовпця застосуємо одночасно пряме поєднання таблиць і прийом дублювання формули. Для цього:
1) виділимо діапазон комірок D4:D13 у другій таблиці;
2) введемо з клавіатури формулу для визначення витрат вакцини на групу тварин, і натиснемо < Сtrl + Enter>.
Рис. 2.2. Сформована таблиця на аркуші 2
Рис. 2.3. Попереджуюче вікно при зміні залежних даних
Для визначення загальної кількості тварин у господарстві і загальних витрат вакцини використаємо інструмент Автосумування.
6.4. Перейменування та видалення робочих аркушів
1) клацніть правою кнопкою миші на ярличку першого аркушу;
2) виконайте команду ПЕРЕИМЕНОВАТЬ у контекстному меню аркуша;
3) введіть нове ім’я, наприклад: Вакцина на одну тварину.
Для перейменування другого аркушу виконайте ті ж пункти, задавши йому друге ім’я.
Всі невикористані аркуші робочої книги видаліть командою УДАЛИТЬ контекстного меню аркуша.
7. Контрольні питання
Лабораторна робота № 3
"Вбудовані функції MS EXCEL
та оформлення робочих аркушів"
1. Мета та завдання роботи
Знайомство з функціями Excel і можливостями оформлення робочих аркушів.
2. Зміст роботи
2.1 Завантажити оболонку Windows та завантажити додаток Excel.
2.2 Обрахувати ряд стандартних функцій згідно завдання п.5.1.
2.3 Відформатувати таблицю рамками і кольором згідно завданню п.5.2.
3. Вимоги до звіту
Звіт повинний містити:
4. Загальні положення
4.1. Обчислювальні можливості і робота з функціями
Основні правила запису формул:
- запис формул в Excel починається із знаку дорівнює (=);
- формули можуть включати звернення до однієї або декількох функцій;
- після імені кожної функції в дужках задаються аргументи, що використовуються функцією. Функції Microsoft Excel є обчислювальними інструментами, котрі можуть бути використані в робочому аркуші для автоматичного прийняття обчислень, виконання дій і обчислення значень. Excel надає широкий набір вбудованих функцій, котрі виконують різноманітні типи обчислень.
Будь-яка функція в Excel включає в себе дві частини: ім’я функції, наприклад, СРЗНАЧ (середнє значення) і необхідні аргументи.
Аргументи – це інформація, яку функція використовує, щоб обчислити нове значення або провести дію. Аргументи завжди знаходяться справа від імені функції і поміщені в дужки.
Аргументом може бути число, ім’я комірки, посилання на комірку або інша функція. Рівень вкладеності функцій в формулах може достигати семи.
Якщо функція не використовує аргументів, то за її ім’ям ідуть пусті дужки () - без пробілів між ними, наприклад, поточна дата: СЕГОДНЯ () ;
- аргументи в функціях відокремлюються один від одного крапками з комами (наприклад, =СРЗНАЧ(1;2;3;4;5) дорівнює 3, але =СРЗНАЧ(;;1;2;3;4;5) дорівнює 2,14);
На прикладі функції СУММ, яка обчислює суму своїх аргументів і може сприймати їх від 1 до 30, розглянемо можливі варіанти завдання аргументів.
Приклад використання аргументів:
Аргументи функції СУММ можуть бути одним з чотирьох нижченаведених видів.
Число, наприклад:
=СУММ(1;10;100)
Формула, котра своїм результатом має число, наприклад:
=СУММ(0,5+0,5;СРЗНАЧ(5;5);10^2)
Функції, які використовуються як аргументи інших функцій, як в попередньому прикладі, називаються вкладеними функціями. В цьому прикладі вкладена функція СРЗНАЧ є аргументом функції СУММ.
Посилання на комірку або на інтервал комірок, який містить числа або формули, які поставляють числа, наприклад:
=СУММ(A1; A2)
=СУММ(A1: A5)
У другому прикладі в якості аргументів наведених функцій, задається інтервал комірок. Він задається адресою комірки, в котрій знаходиться перша цифра стовпця, потім оператор зв’язку - двокрапка ( : ) і адреса комірки, в якій знаходиться остання цифра. Інший приклад еквівалентний формулі СУММ(A1;A2;A3;A4;A5). Перевага використання інтервалу складається в тому, що аргумент A1:A5 приймається за один аргумент, в той час як А1, А2, А3, А4, А5 - за п’ять аргументів. Якщо потрібно скласти більш 30 чисел, то прийдеться використовувати інтервали, тому що функція не може мати більше 30 аргументів.
Ім’я, яке посилається на константу, формулу, комірку або інтервал комірок, що містять числа або формули, що поставляють числа, наприклад:
=СУММ(Основа; Приріст)
В даному прикладі використовуються власні імена «Приріст» та «Основа». Імена призначаються константам, інтервалам, формулам і роблять формули більш зручними для читання, розуміння і роботи.
4.2. Майстер функцій
Майстер функцій дозволяє в діалоговому режимі переглядати списки всіх функцій, розподілених по категоріям, вибрати будь-яку з них і задати її аргументи.
Звернутися до Майстра функцій можна:
1) за допомогою пункту меню Вставка Функция;
2) комбінацією клавіш [Shift]+[F3];
3) кнопкою на панелі інструментів: .
Після виклику Майстра функцій з’являється перше вікно діалогу (рис. 3.1), в якому відображаються категорії та імена функцій, рядок формул та кнопки. Функції перераховані по категоріям, такими як: "Фінансові", "Математичні", "Статистичні" та ін. При переміщенні по списку категорій, список імен функцій справа змінюється. Вибір категорії підтверджується клацанням лівою кнопкою миші на відповідному рядку. При виборі функції із списку в правій частині діалогового вікна, в рядку формул автоматично з’являється визначення функції, а також правильно розташовані дужки та крапки з комою, що розділяють аргументи функції.
Рис. 3.1. Майстер функцій: крок 1 з 2
Після вибору потрібної функції і натисненні на кнопку Шаг відбувається перехід до другого вікна діалогу. На цьому кроці (рис. 3.2) здійснюється введення аргументів вибраної функції з клавіатури в спеціальне вікно введення, де знаходиться курсор у вигляді вертикальної риски. Інтервал комірок можна також виділити безпосередньо на робочому аркуші, тоді він автоматично заноситься у вікно введення. Після натиснення кнопки Закончить, Майстер поміщає ці дані у рядок формул робочого аркушу таблиці, а в комірці з’являється результат обчислень по даній функції. Кнопка Отмена дозволяє відмовитись від введення. В цьому випадку, якщо допущена некоректність при введенні аргументів або самої функції, з’являється повідомлення про помилку.
Рис. 3.2. Майстер функцій: крок 2 з 2
Багато імен аргументів вказують на те, яку інформацію слід задавати в якості фактичного значення аргументу. Якщо в імені аргументу використовуються скорочення чис, ссыл або лог, то це означає що аргумент повинен бути числом, посиланням або логічним значенням.
Наприклад, у функції ОКРУГЛ(число; количество_цифр), обидва аргументи повинні бути числами, де перший аргумент - число, яке округляється, і друге - кількість знаків після коми.
4.3. Оформлення робочих аркушів
Перетворення, які Excel виконує з оформленням символів в комірці, називаються форматуванням. Воно включає:
- шрифтове і кольорове оформлення;
- вирівнювання даних у комірках;
- встановлення формату виведення чисел т. ін.
Форматування можна виконувати за допомогою кнопок панелі інструментів "Форматирование" (рис.3.3). Текст з призначенням будь-якої кнопки панелі з’являється під кнопкою, якщо вказати на її нижній край курсором.
Рис. 3.3. Панель інструментів "Форматування”
Але ж найбільший арсенал засобів форматування Excel надає, якщо скористатися командою Формат Формат ячеек (рис. 3.4).
Рис. 3.4. Діалогове вікно "Формат комірок - ярличок Шрифт "
Діалогове вікно "Формат ячеек" надає шість ярличків для виклику підменю:
Число – задає кількість десяткових знаків для будь-якого числа даної комірки; з’ясовує чи слід давати знак долара або процента; чи є число датою т.ін.
Выравнивание – визначає, яким чином розміщується зміст в середині комірки. За звичай, числа друкують з правого краю комірки, текст - з лівого, а заголовки центрують.
Шрифты – визначають розмір, форму і товщину кожного символу в середині комірки. Одиницею виміру шрифту служить пункт; чим більше пунктів, тим більший символ.
Рамка – дозволяє обвести частини таблиці в рамки, добавити лінії під стовпцями.
Вид – дозволяє задати зафарбовування комірки (колір та візерунок).
Защита – керує приховуванням даних і дозволяє встановити блокування комірок, що робить неможливим будь-яку зміну даних.
За допомогою команди Формат Автоформат можна швидко відформатувати виділену область таблиці або весь робочий аркуш, застосувавши один з запропонованих зразків оформлення таблиці.
5. Методичні рекомендації
5.1. Використання вбудованих функцій
Створити і заповнити нижче приведену таблицю, використовуючи для обчислень можливості Майстра функцій.
Таблиця 2
|
Аргумент Х |
LN |
EXP |
X*LN |
ABS |
КОРЕНЬ |
SIN |
COS |
ОКРУГЛ |
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
1) Заповніть стовпець 1 значеннями від –100 до 100 з кроком 10, виконавши копіювання або автозаповнення.
2) Значення в стовпцях 2 і 3 підрахуйте тільки для позитивних х (визначив їх за допомогою функції ЕСЛИ), для негативних – введіть 0.
Приклад: = ЕСЛИ (А3>0; LN(A3); 0);
Увага! У даному прикладі функція LN є вкладеною функцією, тому що використовується як аргумент функції ЕСЛИ. Щоб вставити «функцію Б» в якості аргументу «функції А» потрібно при введенні аргументу для «функції А» у другому діалоговому вікні повторно викликати Майстер функцій, клацнувши на його значку поруч із рядком введення.
Увага! Якщо функція в середині формули після її введення в комірку взята в подвійні лапки (наприклад, =ЕСЛИ(A1>0;"LN(A1)";0)), вона сприймається, як текст і обрахування по ній виконуватись не будуть. Для видалення лапок увійдіть в режим редагування комірки за допомогою F2 або подвійного клацання.
3) Для обрахування значень в стовпці 4 використовуйте функцію ПРОИЗВЕД.
Приклад: =ПРОИЗВЕД (А3:В3)
4) При обрахуванні значень 6 стовпця використовуйте абсолютні величини значень аргументу.
5) При обрахуванні тригонометричних функцій в стовпцях 7 і 8 аргумент повинен бути заданий в радіанах. Для переводу градусів в радіани можна використовувати множення на і ділення на 180, але значно простіше використовувати функцію РАДИАНЫ. Враховуючи, що значення аргументу задані в градусах, зробіть відповідні обчислення.
Приклади:
= SIN(А3*ПИ/180)
= SIN(РАДИАНЫ(А3))
6) В 9 стовпець внесіть декілька округлених значень функції COS.
Приклади: = ОКРУГЛ(H3, 0) - до цілого;
= ОКРУГЛ(H4, 1) - до десяткових значень;
7) Продовжте таблицю, додавши за останнім значенням аргументу наступні графи: середнє значення, максимум, мінімум.
8) Здійсніть відповідні обчислення по всім стовпцям таблиці.
5.2. Оформлення робочого аркуша
1) Переформатуйте усі комірки з числами і переконайтесь, що всі стовпці вирівняні правильно.
2) Оформіть заголовки стовпців крупним напівжирним шрифтом, щоб вони ясно ідентифікували кожний стовпець. Використовуйте темний фон і напівжирний білий курсив, щоб ваші заголовки виділялись найбільш виразно.
3) Щоб виділити перший стовпець, окресліть його для більшої виразності подвійною лінією. Інші стовпці окресліть одинарною рамкою.
4) Використовуйте тонування світло-жовтим кольором для полів, зайнятих даними: це більш сприятливо для сприйняття, ніж звичайний різкий білий тон.
5) Приберіть звичайні лінії сітки Excel за допомогою команди СервисПараметрыВидСетка: відразу стануть ясно видні всі дані в таблиці.
6) Захистіть робочий аркуш.
Увага! Для виділення всього робочого аркушу необхідно клацнути лівою кнопкою миші на сірій кнопці в лівому верхньому куті вікна робочого аркушу (вона знаходиться прямо над першим рядком таблиці, зліва від стовпця А). У виділеному повністю робочому аркуші все призначені опції форматування будуть застосовані для всього аркушу. Майте на увазі, що можна відформатувати комірку (або групу комірок), навіть якщо вона порожня.
6. Контрольні питання
Лабораторна робота № 4
„Графічні можливості EXCEL ”
1. Мета роботи
Метою даної роботи є отримання навичок при побудові, редагуванні і оформленні діаграм в табличному процесорі EXCEL.
2. Завдання роботи
Навчитись:
2.1. Будувати діаграми будь-яких типів.
2.2. Редагувати їх.
2.3. Встановлювати на діаграмах елементи оформлення.
2.4. Вносити в діаграму додаткові об’єкти.
2.5. Обновляти діаграми.
3. Зміст роботи
3.1. На аркуші 1 робочої книги розмістіть дані для діаграм у вигляді однієї з таблиць представлених в Додатку ЛР4 або вказаних викладачем.
3.2. Побудуйте впроваджену плоску гістограму, використовуючи наступні параметри:
- рядки даних брати в стовпцях;
- вибрати підписи даних в процентах;
- добавити легенду;
- добавити назву діаграми.
3.3. По цім же даним побудувати об’ємну кругову діаграму, взявши рядки даних в стовпцях. Вона повинна містити відповідні змісту мітки даних та заголовок. Розмістити її на окремому аркуші, назвавши “Кругова діаграма”.
3.4. На аркуші Кругова діаграма змініть шрифт і колір заголовка, добавивши легенду, змінить колір фону. Перенести легенду в правий верхній кут.
3.5. На аркуші 2 помістіть дані для діаграм у вигляді таблиць. В першому стовпці таблиця повинна містити значення Х від 0 до 100 з кроком 10. У другому стовпці помістіть значення будь-якої тригонометричної функції від Х (наприклад, sin).
3.6. По цим даним побудуйте точкову діаграму, розмістивши її на окремому аркуші, назвавши його “Точкова”. Встановіть білий фон для області побудови діаграми.
3.7. Додайте до таблиці з даними новий стовпець із значеннями ще однієї тригонометричної функції. Додайте нові дані до діаграми.
3.8. Оформити точкову діаграму, а також доповнити її елементами за допомогою панелі інструментів РИСОВАНИЕ.
3.9. Побудувати будь-яку (можна нестандартну) діаграму по трьом несуміжним стовпцям по будь-якої таблиці.
3.10. Зберегти свій файл, назвавши ім’ям факультету.
3.11. Оформити звіт.
4. Зміст звіту
4.1. Назва роботи та її задачі.
4.2. Основні етапи роботи і результати їх виконання у вигляді файлу у папці “Мої документи”.
4.3. Відповіді на контрольні питання пункту 7.
5. Загальні положення
5.1 Загальні відомості про діаграми.
Табличний процесор Excel дає змогу подавати табличні дані в наочній та зручній для сприйняття графічній формі. Ілюстрації використовують для показу функціональної залежності однієї величини від іншої або для порівняння двох і більше величин, із метою виявлення тенденцій зміни якого-небудь параметра у часі, відображення відсоткового вмісту кількох компонентів у певному об’єкті тощо.
Діаграма – це графічне представлення даних робочого аркушу на площині креслення. Вони можуть допомогти при аналізі та порівнянні даних.
При створенні діаграми на основі виділених на робочому аркуші комірок, Excel використовує значення величин з робочого аркушу і представляє їх на діаграмі у вигляді елементів, які можуть бути зображені смугами, лініями, стовпцями, секторами, точками та в іншій формі. Групи даних, що відображують зміст комірок одного рядка або стовпця на робочому аркуші, складають ряд даних. Кожний ряд даних виділяється на діаграмі унікальним кольором або візерунком.
Після створення діаграми можна її удосконалити, доповнюючи представлену інформацію різноманітними компонентами діаграми: мітками даних, легендою, назвами т. ін. Компоненти діаграм можна переміщувати а їх розміри можна змінювати. Можна також відформатувати ці компоненти, використовуючи різноманітні візерунки, кольори, засоби вирівнювання тексту, шрифти та інші атрибути форматування.
За допомогою Excel можна будувати 14 стандартних типів діаграм, кожен із яких має ще кілька різновидів. Для цього використовують спеціальну програму Майстер діаграм, яка повністю автоматизує цей процес, залишаючи за користувачем тільки введення даних і прийняття низки елементарних рішень.
5.2. Створення діаграм
Створення будь-яких діаграм розпочинається з виділення діапазону даних, що підлягають відображенню, та активізації програми Майстер діаграм.
5.2.1. Виділення даних
Перед побудовою діаграми можна виділити дані, по яким вона буде будуватись. Для цього маркується мишею потрібний інтервал даних таблиці, включаючи заголовки рядків і стовпців.
Іноді виникає необхідність створення діаграми, яка використовує несуміжні (непримикаючі одна до одної) комірки або інтервалів комірок.
Це буває при створенні діаграми:
- що використовує несуміжні рядки або стовпці робочого аркушу;
- що використовує окремі фрагменти рядка або стовпця;
- по таблиці, в якій зустрічаються порожні, незаповнені рядки або стовпці, що знаходяться між рядками або стовпцями, по яким потрібно побудувати діаграму.
У всіх цих випадках потрібно виділити інтервали комірок, натиснувши та утримуючи клавішу CTRL. Несуміжні виділення повинні мати прямокутну форму.
5.2.2. Побудова діаграми.
Якщо діаграма призначена як ілюстрація до даних на робочому аркуші, то її краще відобразити разом з ними. У цьому випадку створюється впроваджена діаграма на цьому ж робочому аркуші. Діаграму також можна розмістити на окремому аркуші діаграм, який створюється автоматично зліва від робочого аркушу.
Як і вбудовані діаграми, так і аркуші діаграм пов’язані з тими даними на робочому аркуші, по яким вони були створені, і в обох випадках ці діаграми будуть автоматично обновлятись при обновленні даних.
Діаграми створюються за допомогою МАЙСТРА ДІАГРАМ, котрий може бути викликаний за допомогою піктограми на панелі інструментів або пункту головного меню ВСТАВКА ДИАГРАММА. Процес створення діаграм складається з чотирьох характерних кроків. Це:
При правильному призначенні початкового діапазону даних Майстер здатний сформувати її практично за перший крок його роботи. Багато параметрів призначаються ним за умовчання.
На першому кроці МАЙСТРА ДІАГРАМ потрібно вибрати тип діаграми і вигляд: плоский або об’ємний. Тип діаграми вибирається таким чином, щоб представити дані найбільш наочним і вражаючим способом. Перехід до наступного кроку здійснюється натисканням кнопки Далее.
При активізації команди Вставка Диаграмма або кнопки-піктограми
(Мастер диаграмм) на екран викликається діалогове вікно Мастер диаграмм (шаг 1 из 4): тип диаграммы з двома опціями-вкладками: Стандартные та Нестандартные (рис. 4.1)
До стандартних діаграм належать гістограма та графік, а також діаграми від лінійчатої до пірамідальної. Нестандартні діаграми є різновидами стандартних і відрізняються від них передусім колірною гаммою.
Рис. 4.1. Вікно Майстер діаграм (крок 1 з 4):
тип діаграми із вкладкою «Стандартні»
Вибравши тип та вигляд діаграми, Ви можете переглянути її зразок та оцінити його. Для цього потрібно активізувати параметр Просмотр результата. Зразок нестандартної діаграми відображається у вікні відразу ж після вибору її типу.
За умовчання Excel призначає стовпцеві гістограму. За допомогою кнопки Сделать стандартной тип діаграми, що призначається системою за умовчання, можна змінити. Цю процедуру виконують тільки після завершення формування вибраної діаграми.
5.2.3. Найпоширеніші типи стандартних діаграм та їх різновиди
Гістограма (рис. 4.1) належить до стовпцевих діа грам, має сім різновидів і є одним із популярних засобів ділової графіки. Гістограму зручно використовувати для зображення показників-змінних, які порівнюються в ме жах певного часового інтервалу, тощо. Гістограми дають змогу, наприклад, наочно відобразити планові та фінансові показники виробництва за квартал звітного року, щомі сячні платежі за кредитами т. ін. При цьому стовпці, що стосуються різних змінних, розміщуються рядами, які мож на суміщати.
Гістограма має вигляд стовпцевої діаграми, що містить загальний заголовок, вісь X (або вісь категорій), вісь У (або вісь значень), заголовки і масштабні мітки осей X та У, а також легенду з мітками. За бажанням користувача ле генда може розміщуватися праворуч, ліворуч, зверху, у пра вому куті або знизу діаграми, її мітки — це графічне зо браження показників-змінних, які відображаються діагра мою; вони пояснюють, як підсвічуються, штрихуються або позначаються певні змінні.
У гістограмі кількість стовпців у групах визначається кількістю змінних. Стовпці груп, що відповідають одній змінній, штрихуються (підсвічуються) однаково; їх висота пропорційна значенню змінних. Кількість груп стовпців дорівнює числу значень змінних.
Категоріями гістограм, тобто їх незалежними змінни ми, як правило, є різні часові періоди (роки, квартали, міся ці тощо). Тому Майстер діаграм за умовчання викорис товує заголовки стовпців як мітки осі X, а заголовки ряд ків — для ідентифікації змінних, що відображаються. Однак можливим є також протилежне призначення.
Різновидом гістограм є циліндричні, конічні та піра мідальні діаграми; в них стовбці замінюються відповідними геометричними об'єктами. Варті уваги суміщені та нормовані до 100% діаграми, що відображають частку кож ної категорії в загальній сумі значень.
Лінійчата діаграма — це та сама гістограма, але з іншою орієнтацією осей. Тут вісь критеріїв X розміщується вертикально, а вісь значень У — горизонтально. В Ехсеl їй часто віддають перевагу, тому що її стовпці роз міщуються горизонтально по рядках і добре вписуються в робочий аркуш.
Графік відзначається своєю простотою та наочністю і відображає одну або кілька залежностей вигляду у=f(х). Використовується для ілюстрації коливань у часі курсів валют або акцій. У графіку маркери даних — це графічні символи, що ідентифікують змінні та їх значення. Марке ри, які стосуються однієї змінної, мають однакові форму і розмір. Опис усіх маркерів графіка називається його ле гендою.
Кругова діаграма відображає співвідношення між ці лим і його частинами (наприклад, між усім бюджетом та окремими його статтями, всією інвестиційною програмою та її складовими тощо). Для побудови кругової діаграми необхідно обчислити суму всіх значень певного поля таб лиці, а потім визначити, яку частину цієї суми становить кожне значення. Окремі або всі сегменти кругової діагра ми можуть висуватися.
Кільцева діаграма — один із різновидів кругової діа грами. Вона також відображає співвідношення між цілим і його частинами, але містить кілька змінних (цілих).
Діаграма з ділянками (або діаграма площ) подібна до гістограми із суміщеними стовпцями, її графік — це лінії, що з'єднують верхні точки стовпців гістограми з відпо відним штрихуванням. При цьому верхній графік відо бражає суму значень усіх показників-змінних, а нижній — значення першої змінної.
Особливість діаграми з ділянками полягає в тому, що вона, з одного боку, відображає характер змін окремих по казників-змінних, а з іншого — демонструє їх співвідно шення. Отже, цій діаграмі притаманні властивості графі ка та кругової діаграми.
Точкова діаграма широко використовується у статис тиці. Вона відображає не тільки зміну показників, що від булася за певний проміжок часу, а й міру відхилення їх від середнього значення. Інтервали часу тут можуть бути різними.
Інші діаграми зі списку вікна Майстра діаграм (рис. 4.1) використовуються рідко і тому в даній роботі не розглядаються.
На другому кроці здійснюється вибір даних для побудови діаграми (крок 2 із 4). Пе рехід до другого кроку роботи Майстра здійснюється че рез команду Далее або після натиснення на клавішу Еnter (див. рис. 4.1). При цьому відкривається діало гове вікно Мастер диаграмм (шаг 2 из 4): источник данных диаграмм із двома вкладками Диапазон данных і Ряд (рис. 4.2).
Вкладка Діапазон даних забезпечує:
введення або зміну діапазону даних, необхідних для побудови діаграми;
вибір способу формування рядів діаграм: за рядка ми або за стовпцями виділеного діапазону;
відображення сформованого зразка, перехід до чер гового кроку або повернення до першого.
Рис. 4.2. Вікно Майстер діаграм (крок 2 з 4):
джерело даних діаграми із вкладкою «Діапазон даних»
Діапазон даних, до якого можуть належати заголовки стовпців і рядків, вводять до поля Диапазон вручну або вибирають його початкову та кінцеву комірки у самій таб лиці. Якщо діалогове вікно заслоняє таблицю, то його по трібно скоротити до розмірів поля Диапазон, клацнув ши мишею на кнопці мінімізації з червоною стрілкою. Адреси комірок діапазону — абсолютні (з ознакою $), що означає неможливість їх змінити при будь-якому перемі щенні діаграми в межах робочого аркуша.
Вкладка «Ряд» (рис. 4.3) реалізує режим формуван ня рядів діаграми, в полях цієї вкладки задають:
• ім'я кожного ряду (змінної) та адреси його розмі щення;
• діапазон значень кожної змінної (ряду);
• діапазон розміщення категорій по осі X.
За умовчання кожному ряду спочатку присвоюється системне ім'я Ряд1, Ряд2 і т. п. Адреси всіх комірок і діапазонів — абсолютні. Розміри вікна (рис. 4.3) можна звести до розмірів будь-якого з діапазонів за допомогою кнопок мінімізації. Імена всіх змінних діаграми відобра жаються в її легенді.
Рис. 4.3. Вікно „Майстер діаграм (крок 2 із 4):
джерело даних діаграми” із вкладкою «Ряд»
Після завершення всіх процедур кроку 2 треба клац нути мишею на кнопці «Далее» та перейти до кроку З роботи Майстра.
На третьому кроці відкривається діалогове вікно встановлення параметрів діаграми, яке складається з шістьох вкладок: Заголовки, Оси, Линии сетки, Легенда, Подписи данных, Таблица дан ных (рис. 4.4). На відповідних вкладках можна встановити параметри діаграми, у тому числі підписи даних, заголовки, осі, лінії сітки, легенду т ін.
Вкладка Заголовки забезпечує ручне введення за головків діаграми, заголовків осі X (категорій) та осі У (значень).
Рис. 4.4. Вікно „Майстер діаграм (крок 3 з 4}:
параметри діаграми” із вкладкою «Заголовки»
Вкладка Оси визначає вмикання (вимикання) міток осей X, У і заміну осі категорій віссю часу, тобто заміну міток вигляду «січень», «лютий», «березень» мітками ви гляду «01.01.02», «01.02.02» і «01.03.02» відповідно.
За допомогою вкладки Линии сетки вмикають (ви микають) основні та проміжні лінії по осях X, У і таким чином формують сітку діаграми.
Вкладка Легенда дає змогу вмикати (вимикати) ле генду та змінювати її положення щодо діаграми.
Вкладка Подписи данных збільшує наочність діагра ми: її параметри дозволяють (забороняють) відображати ключі (мітки) легенди і значення змінних або категорій діаграми над її стовпцями.
Вкладка Таблица данных містить два перемикачі: «Таблица данных» та «Ключ легенды». Перший дозволяє (забороняє) відображати таблицю виділеного діапазону даних знизу діаграми, а другий — відображати ключі леген ди, тобто маркери змінних у цій таблиці.
На четвертому кроці можна вибрати місце для розміщення діаграми: на окремому або на наявному аркуші. Вибір варіанта розміщення діаграми здійснюється в діалоговому вікні Майстра за допомогою двох перемикачів (рис. 4.4).
Рис. 4.4. Вікно «Майстер діаграм (крок 4 з 4}: розміщення діаграми»
Щоб розмістити діаграму на окремому робочому аркуші, досить увімкнути відповідний перемикач, а потім (за бажанням) замінити системне ім'я «Диаграмма1» ім'ям користувача, наприклад «Залежність Кредит = f(місяць)».
Для розміщення діаграм як вбудова ного графічного об'єкта на одному з аркушів робочої кни ги потрібно ввімкнути однойменний перемикач, а потім ви брати ім'я цього аркуша зі списку, що розкривається.
Розміри будь-якої діаграми змінюють «буксируванням» її кадрових маркерів у відповідних напрямках. Розташу вання діаграм на екрані можна змінювати за допомогою кнопок-піктограм вирівнювання тексту, а також «букси руванням» її кадру.
Останнє діалогове вікно Майстра діаграм закрива ється натисненням на клавішу <Еnter> або клацанням мишею на кнопці «Готово».
5.3. Редагування діаграми
Готову діаграму можна редагувати, доповнюючи її мітками даних, назвою діаграми і осей, легендою і лініями сітки; змінюючи оформлення і розміщення елементів, змінюючи текст заголовків т. ін. Це дозволяє поліпшити зовнішній вигляд діаграми, зробити її більш наочною та ефективнішою.
У кожній діаграмі розрізняють ділянку діаграми й ділянку побудови діаграми. Перша – це простір, обмежений зовнішньою рамкою діаграми, друга – простір між осями координат Х та У. Модифікація об’єктів указаних ділянок виконується окремо. Будь-яку з ділянок активізують подвійним клацанням мишею у якій-небудь точці.
Для внесення змін на діаграмі використовують один з наступних методів:
- за допомогою контекстного меню (клацання правою кнопкою миші);
- за допомогою команди головного меню ФОРМАТ;
- за допомогою подвійного клацання лівою кнопкою миші;
- піктограма МАЙСТЕР ДИАГРАМ на панелі інструментів.
Щоб внести зміни в діаграму необхідно:
1) Підвести покажчик миші до діаграми і клацнути лівою кнопкою миші. З’явиться бордюр з ліній навколо діаграми.
2) Виділити об’єкт, який потрібно редагувати. Навколо нього з’явиться контур. Якщо ж об’єкт поділяється на більш дрібні, то клацанням лівою кнопкою миші можна вибрати один з них і за допомогою маркерів змінити розмір. Якщо курсор має вигляд стрілки і знаходиться всередині контуру, то його можна переміщувати по екрану.
Діалогове вікно форматування частіше всього містить наступні вкладки:
1) ВИД (рис.4.5.) – дає змогу встановити колір, товщину та тип лінії рамки діаграми, а також колір і спосіб її заливання. Останній вибирає користувач у спеціальному вікні Заливка, яке активізується параметром Способы заливки. Вікно Заливка надає користувачу можливість вибрати варіант градієнтності та тип штрихування цієї ділянки, її текстуру і візерунок, а також розмістити певний рисунок (малюнок) із певного файлу магнітного диска.
Заливання ділянки діаграми додає їй привабливого вигляду. У разі використання об’ємних діаграм можна заливати й стінки та основи стовпців. Останні виділяють так само, як і ділянку діаграми – подвійним клацанням мишею.
Рис.4.5. Вкладка „Вид” Рис. 4.6. Вкладка „Шрифт”
2) ШРИФТ (рис.4.6.) – дозволяє змінити шрифт, стиль, розмір, колір, підкреслювання та ефекти виділеного тексту діаграми.
3) ВИРІВНЮВАННЯ (рис.4.7.) – контролює вирівнювання тексту та орієнтацію заголовків діаграми та осей, міток даних, вікон тексту. Доступні параметри залежать від типу виділеного тексту.
Рис. 4.7. Вкладка „Выравнивание”
5.4. Додавання нових даних до діаграми.
Після того, як діаграма вже побудована, може виникнути необхідність обновити її, добавити нові ряди або елементи даних з робочого аркуша. Метод, за допомогою якого це можна зробити, залежить від того, яку саме діаграму - впроваджену чи на окремому аркуші – потрібно обновити.
Для того, щоб добавити дані на впроваджену діаграму, можна, виділити їх на робочому аркуші, перетягти за допомогою миші на діаграму. Для додавання даних на окремий аркуш діаграми можна використати команду ВСТАВКА НОВЫЕ ДАННЫЕ або копіювання через буфер обміну. Додавання даних автоматично приводить до обновлення існуючої легенди.
Щоб краще контролювати те, як будуть розташовані на діаграмі дані з робочого аркушу, можна використати наступний метод:
- виділити потрібні дані;
- скопіювати їх в буфер обміну;
- перейти на аркуш з діаграмою;
- виконати команду ПРАВКА СПЕЦИАЛЬНАЯ ВСТАВКА ДОБАВИТЬ ЗНАЧЕНИЯ НОВЫЕ ЗНАЧЕНИЯ.
5.5. Панель інструментів МАЛЮВАННЯ.
- за допомогою піктограми МАЛЮВАННЯ викликається панель інструментів МАЛЮВАННЯ. Ця панель містить кнопки, які дозволяють створювати і форматувати графічні об’єкти. Вона має наступний вигляд (рис.4.8.):
Рис. 4.8.
За допомогою кнопок цієї панелі можна виконати наступні дії:
Кнопка |
Функція |
|
Лінія |
Малює відрізок прямої лінії. |
|
Прямокутник |
Малює незафарбовані прямокутники або квадрати |
|
Овал |
Малює незафарбовані овали або кола. |
|
Текстове поле |
Малює текстове поле, в якому можна вводити текст на робочому аркуші |
|
Стрілка |
Створює стрілку на активному робочому аркуші або на діаграмі |
|
Додавання об’єкту WordArt |
Вибирає стиль напису. |
|
Колір заливання |
Змінює колір заливання |
|
Колір ліній |
Змінює колір ліній |
|
Колір шрифту |
Змінює колір шрифту |
|
Тип лінії |
Дозволяє вибрати різноманітні типи ліній |
|
Вид стрілки |
Дозволяє встановити різноманітні види стрілок |
|
Об’єм |
Встановлює об’ємні фігури |
|
Дії |
Змінює автофігури |
|
Автофігури |
Встановлює лінії, основні фігури і т. ін. |
|
Вибір об’єктів |
Дозволяє вибрати різноманітні об’єкти |
|
Вільне обертання |
Дозволяє повертати об’єкти |
|
Тінь |
Додає темну рамку до правої та нижньої сторонам виділеного інтервалу або об’єкту |
6. Методичні рекомендації
6.1. На аркуші робочої книги помістіть дані у вигляді таблиці для побудови діаграми. Замаркіруйте потрібний діапазон за допомогою миші.
6.2. Викличте серію діалогових вікон МАЙСТЕР ДІАГРАМ за допомогою піктограми на панелі інструментів - МАЙСТЕР ДІАГРАМ.
На першому кроці виберіть тип діаграми – гістограма, на наступному – виправте або залишіть незмінним діапазон виділених комірок, розташуйте ряди даних по стовпцям. На третьому кроці – введіть назву, додайте легенду, підписи даних у процентах. На останньому – розмістіть діаграму на наявному аркуші.
6.3. По цим же даним на робочому аркуші побудуйте кругову діаграму, що містить тільки назву і мітки даних. Розмістіть її на окремому аркуші, назвавши її “Кругова діаграма”.
6.4. Для редагування заголовку слід двічі клацнути по ньому мишею. З’явиться вікно для його форматування. В цьому вікні на вкладці ШРИФТ встановіть стиль і розмір для вибраного шрифту, а також поміняйте його колір. Для роботи з назвами по осям, можна скористатися вкладкою ВЫРАВНИВАНИЕ ОРИЕНТАЦИЯ. В ній виберіть підходящий зразок. Для зміни стилю написання міток по осям існує вкладка ШРИФТ СТИЛЬ. Щоб змінити місцеположення легенди, клацніть на ній мишею і перетягніть в потрібне місце.
6.5. На вільному аркуші помістіть дані для побудови точкової діаграми. Для заповнення першого стовпця – значень Х від 0 до 100 з кроком 10 – використовуйте метод автозаповнення. У другому – обчисліть значення тригонометричної функції, скориставшись піктограмою МАЙСТЕР ФУНКЦІЙ на панелі інструментів або командою головного меню ВСТАВКА ФУНКЦИЯ.
Примітка: аргумент повинен бути в радіанах.
6.6. По цій таблиці побудуйте точкову діаграму.
6.7. Для додатку нових даних до діаграми слід виконати команду ВСТАВКА НОВЫЕ ДАННЫЕ. Після цього виділіть потрібні дані на робочому аркуші, включаючи заголовок, і натисніть кнопку ОК. Мітки рядків і стовпців повинні додаватись автоматично.
6.8. Для оформлення діаграми виконайте наступне:
В належних по змісту точках вставте наступні текстові поля:
- максимальне значення функції;
- мінімальне значення функції;
Для цього викличте піктограму ТЕКСТОВОЕ ПОЛЕ, вкажіть за допомогою миші його розмір і напишіть в ньому відповідний текст.
За допомогою піктограми СТРЕЛКА проведіть стрілки від текстових полів до відповідних значень. Для цього використайте кнопку Стрелка.
При натисненні цієї кнопки курсор миші змінює свою форму на тонкий хрестик. Протягніть його, щоб намалювати стрілку.
Щоб намалювати декілька стрілок підряд, зробіть подвійне клацання по цій кнопці. Вона залишиться натиснутою до тих пір, доки знову не натиснути її, іншу кнопку або клавішу ESC або не клацнути по будь-якій частині робочого аркушу листа без протягування. Щоб намалювати строго горизонтальні, вертикальні або стрілки що розташовуються точно під 45 градусів, утримуйте натиснутою клавішу SHIFT під час протягування курсору миші. Якщо потрібно щоб стрілка була прив’язана до ліній сітки робочого аркушу, потрібно утримувати натиснутою клавішу ALT під час протягування. При відпусканні кнопки миші, нарисована стрілка буде автоматично виділена.
6.9. Побудуйте довільну діаграму (наприклад, пелюсткову). Дані для побудови можуть бути будь-якими, елементи оформлення виберіть довільно.
6.10. Збережіть файл, назвавши ім’ям факультету, використовуючи команду головного меню ФАЙЛ СОХРАНИТЬ КАК.
7. Контрольні питання
Лабораторна робота № 5
"Управління даними в EXCEL"
1. Мета роботи
Метою даної роботи є отримання навичок при роботі з таблицями як з базами даних в EXCEL.
2. Завдання роботи
Навчитись:
2.1. Створювати бази даних.
2.2. Створювати форми даних і працювати в них із записами.
2.3. Сортувати записи в базах даних.
2.4. Вибирати записи по різноманітним критеріям.
3. Зміст роботи
3.1. Створити новий аркуш робочої книги “База даних” і розмістити на ньому дані, вказані викладачем, у вигляді бази даних. В першому рядку таблиця повинна містити імена полів. З другого рядка повинні розміщуватись записи.
3.2. Викликати форму даних для вашої таблиці і відредагувати записи в ній по завданню п. 7.
3.3. Вибрати записи по наступним умовам :
- по єдиному в своєму роді критерію в текстовому полі;
- більше одного із значень в числовому полі;
- по першій літері текстового поля;
- по двом критеріям в числових полях.
3.4. Відсортувати базу даних по убуванню третього стовпця.
Однакові значення третього стовпця відсортувати по зростанню другого.
3.5. Включити автофільтр і відфільтрувати записи по наступнім параметрам:
- рівність в текстовому полі
- нерівність в числовому полі
- И інтервал
- ИЛИ інтервал.
3.6. Оформити звіт.
4. Зміст звіту
4.1. Назва роботи і її задачі.
4.2. Основні етапи роботи і результати їх виконання у вигляді файлу на дискеті.
4.3. Відповіді на контрольні питання по пункту 7.
5. Загальні положення
5.1. Загальні поняття про бази даних
База даних – це будь-яка сукупність пов’язаної інформації, поєднаної по певному признаку. Наприклад, базою даних є розклад польотів літаків, або розклад руху автобусів. Більшість баз даних для зберігання своєї інформації використовують таблиці. Кожна таблиця складається з рядків і стовпців, які в комп’ютерних базах даних називаються записами і полями.
Основним призначенням бази даних є швидкий пошук інформації, що міститься в ній і вибірка інформації по заданому критерію.
Microsoft Excel має численний набір команд, які дозволяють легко працювати з базами даних. Щоб скористатися можливостями Microsoft Excel для обробки даних, потрібно створити базу даних на робочому аркуші. Базу даних, як і будь-яку книгу Excel, створюють командою Файл Создать.
5.2. Команда Форма
За допомогою команди ДАННЫЕ ФОРМА можна вивести форму даних, яка представляє собою вікно діалогу, що використовується для перегляду, зміни, додавання і видалення записів бази даних, або для пошуку конкретних записів на базі обумовлених критеріїв пошуку.
У цьому вікні діалогу назви стовпців таблиці становляться іменами полів у формі даних. Excel автоматично присвоює "гарячі клавіші" кожному імені поля. Для того, щоб переміститись в певне поле, необхідно натиснути ALT разом з літерою, яка підкреслена в імені поля. Поля відповідають кожному стовпцю у списку. Все поля, які доступні для редагування, з’являються у вікні редагування.
Смуга прокручування дозволяє прокручувати записи у списку і показує приблизну позицію запису, що виводиться базою даних. За допомогою бігунка на смузі прокручування і за допомогою стрілок на її кінцях можна переміщатись усередині бази. Індикатор номера запису повідомляє номер виведеного запису і загальну кількість записів у базі. Номер запису замінюється на вікно Создать, якщо Ви вже знаходитесь в конці бази.
В діалоговому вікні форми даних можна користуватись наступними кнопками:
|
Кнопка |
Призначення |
|
Создать |
Дозволяє ввести новий запис в базу даних. Дані що вводяться, будуть додані як новий запис в кінець бази |
|
Удалить |
Видаляє виведений запис; інші записи бази зсуваються. Видалені записи не можуть бути відновлені |
|
Восстановить |
Поновлює відредаговані поля у виведеному запису, видаляє зроблені зміни. Якщо потрібно відновити запис, то це необхідно зробити перед натисненням клавіші ENTER або перед переходом до іншого запису. |
|
Предыдущая |
Виводить попередній запис в базі |
|
Следующая |
Виводить наступний запис в базі |
|
Критерии |
Виводить вікно діалогу, в якому можна ввести критерії або умови для відшукання потрібної підмножини записів |
|
Закрыть |
Закриває форму даних |
|
Очистить |
Видаляє існуючий критерій з вікна діалогу. Доступна тільки тоді, коли натиснута кнопка Критерии. |
|
Форма |
Повертає до типу форми даних по умовчанню. Доступна тільки тоді, коли натиснута кнопка Критерии |
Якщо натиснута кнопка Критерии, то кнопки Предыдущая і Следующая пересуваються тільки по відібраним записам.
5.3. Сортування записів
Реляційні бази даних (списки) сортують так само, як і дані таблиць-документів: за зростанням або спаданням значень вибраних полів; за алфавітом і за послідовністю, зворотною йому; за одним, двома або трьома ключами; за рядками або стовпцями; за днями, місяцями та іншими ключами.
За допомогою команди ДАННЫЕ СОРТИРОВКА можна впорядкувати рядки в базі даних у відповідності зі змістом певних стовпців. Припустимо, що наприклад база даних містить дані про продаж, і має стовпці для імені продавця, кількості товару, дати замовлення т. ін. Можна скористатися командою СОРТИРОВКА для того, щоб впорядкувати список по іменам продавців, розмістити дані по убуванню обсягів продаж або в порядку зростання дати замовлення. Для цього потрібно виділити дані що будуть сортуватись і визначити необхідний порядок сортування, або створити і застосувати користувальницький порядок сортування.
Щоб відсортувати базу даних цілком, потрібно лише виділити одну комірку зі списку і вибрати команду ДАННЫЕ СОРТИРОВКА. Microsoft Excel автоматично вибере весь список для сортування. Microsoft Excel визначає розташування міток стовпців, навіть якщо вони займають два рядки, і виключає їх із сортування. Можна використати мітки стовпців для того, щоб вказати стовпці, по яким потрібно відсортувати список. Microsoft Excel дозволяє також сортувати тільки виділені рядки або стовпці, або дані тільки в одному рядку або стовпці.
Після завдання даних для сортування і вибору команди на екрані з’явиться вікно діалогу, в якому слід задати наступні поля:
Сортировать – дозволяє виділити потрібний стовпець для сортування і вибрати порядок сортування по зростанню або по убуванню. Слід натиснути кнопку По Возрастанию для сортування від меншого до більшого, по алфавіту або в хронологічному порядку дат. Або кнопку По Убыванию для сортуванню від більшого до меншого, в зворотному алфавітному або в зворотному хронологічному порядку. Вибраний стовпець називається ключовим.
Затем – два додаткових вікна дають можливість визначити порядок вторинного сортування для рядків, в яких є співпадаючі елементи. Потрібно встановити прапорець По Возрастанию або По Убыванию в кожному вікні для визначення впорядкування даних.
Строка Меток Столбцов – визначає, чи є в базі що сортується, рядок заголовків, котрий потрібно виключити із сортування. Якщо база містить мітки стовпців, то виберіть перемикач Есть, а якщо міток нема, то виберіть перемикач Нет.
Параметры – виводить вікно діалогу Параметры Сортировки, в якому Ви можете:
- визначити користувальницький порядок сортування для стовпців, зазначеного у вікні Сортировать.
- визначити сортування з урахуванням регістру.
- змінити орієнтацію сортування, тобто, замість сортування зверху вниз встановити сортування зліва направо. Після встановлення усіх необхідних полів і параметрів слід натиснути на кнопку ОК.
Microsoft Excel використовує наступні основні принципи при сортуванні:
- якщо сортування ведеться по одному стовпцю, то рядки з однаковими значеннями в цьому стовпці зберігають попереднє впорядкування;
- рядки з пустими комірками в стовпці, по яким ведеться сортування, розміщуються у кінці списку що сортується;
- параметри сортування - вибраний стовпець, порядок (зростаючий чи убутний) и напрямок сортування (зверху вниз чи зліва направо);
- зберігаються від попереднього застосування сортування до тих пір, доки їх не змінити, не вибрати іншу базу даних або не змінити мітки стовпців;
- якщо сортування ведеться по декільком стовпцям, рядки з однаковими значеннями в першому ключовому стовпці сортуються в порядку, який визначається другим ключовим стовпцем, рядки з однаковими значеннями в перших двох ключових стовпцях сортуються по третьому стовпцю.
Якщо результат сортування не той, якого очікували, зверніть увагу на тип даних що сортуються. Переконайтесь, що всі числа введені як цифрові дані або як текст. Слід ввести всі дані в одному стовпці або як числа або як текст.
5.4. Фільтрація або вибірка даних
За допомогою команди ДАННЫЕ ФИЛЬТР можна відшукати і використати потрібну підмножину даних в базі. У відфільтрованій базі виводяться на екран тільки ті рядки, які містять певні значення або відповідають певним критеріям вибірки. При цьому інші рядки будуть тимчасово сховані. Підменю ФИЛЬТР в Microsoft Excel надає можливість використовувати як команду АВТОФИЛЬТР, так і команду РАСШИРЕННЫЙ ФИЛЬТР якщо потрібно використовувати складний критерій для вибірки даних.
5.4.1. Застосування АВТОФІЛЬТРУ.
Команда АВТОФИЛЬТР поміщає кнопки із списків що розкриваються (кнопки із стрілкою) безпосередньо в рядок з мітками стовпців, за їх допомогою можна вибрати елементи бази, які слід вивести на екран. Наприклад, можна вивести на екран всі рядки, що містять певні значення в стовпці, скажемо, "все рядки, котрі містять дату замовлення 11.12.97 в стовпці „Дата Заказа".
Можна також застосовувати користувальницькі критерії порівняння для фільтрації даних в базі. Для цього у списку слід вибрати пункт Настройка. На екран виводиться вікно діалогу Пользовательский Автофильтр.
В цьому вікні можна визначити до двох критеріїв порівняння для одного і того ж стовпця при фільтрації даних. Microsoft Excel порівняє елементи списку із введеними раніше значеннями і виведе лише рядки, які задовольняють критерію. Можна також використовувати умовні оператори И / ИЛИ для об’єднання або порівняння критеріїв в одному і тому ж стовпці.
Для визначення двох критеріїв виберіть один із наступних варіантів:
- натисніть перемикач "И" для поєднання двох критеріїв у Вашому фільтрі. В цьому випадку виберуться дані, для яких виконуються відразу обидві умови.
- натисніть перемикач "ИЛИ" для використання двох різних критеріїв у Вашому фільтрі. В цьому випадку виберуться дані, для яких виконуються хоча б одна з умов.
5.4.2. Застосування РОЗШИРЕНОГО ФІЛЬТРУ.
Команда РАСШИРЕННЫЙ ФИЛЬТР дозволяє відшукувати рядки за допомогою більш складних критеріїв, наприклад, "продаж 10.02.97 на суму більш, ніж 500000 грн., або продаж 11.02.97 на суму більш, ніж 300000 грн."
При виборі цієї команди у вікні діалогу що з’явилось, слід встановити наступні параметри:
Действие – перемикач Фильтровать Список На Месте ховає рядки, які не задовольняють вказаному критерію. Перемикач Копировать На Другое Место копіює відфільтровані дані на інший робочий аркуш або на інше місце на тому ж робочому аркуші.
Интервал Списка – визначає інтервал, що містить дані, які повинні фільтруватись.
Интервал Критериев – визначає інтервал комірок на робочому аркуші, що містить потрібні критерії. Microsoft Excel виводить посилання на цей інтервал.
Копировать На – визначає інтервал комірок, в який копіюються рядки, задовольняючі певним критеріям. Це поле активно тільки в тому випадку, якщо обраний перемикач Копировать На Другое Место.
Без Повторений – виводить тільки рядки, що задовольняють критерію і не містять елементів що повторюються. Якщо інтервал критеріїв не визначений, то в цьому випадку всі рядки списку, що містять дублікати, будуть сховані.
5.4.3. Робота з відфільтрованими даними
Коли рядки приховуються за допомогою команд АВТОФИЛЬТР і РАСШИРЕННЫЙ ФИЛЬТР, робочий лист переходить в режим фільтрації.
В цьому режимі багато команд Microsoft Excel впливають тільки на видимі комірки. Після того, як буде застосований фільтр для виводу тільки потрібних рядків, можна скопіювати отриману підмножину даних в інше місце для подальшого аналізу. Для того щоб повернутись до первісного виду бази даних, слід скористатися командою ДАННЫЕ ФИЛЬТР ПОКАЗАТЬ ВСЕ.
6. Методичні рекомендації
6.1. Редагування і вибір записів у формі даних
Виділити будь-яку комірку таблиці, викликавши форму даних і виконати наступні вправи:
- перейти на третій запис;
- перейти на початок та кінець бази даних;
- за допомогою кнопки Создать ввести 2 нові записи; для переходу до наступного поля скористайтесь мишкою, після введення значення останнього поля натисніть клавішу ENTER;
- видалити четвертий запис;
- змінити значення одного з полів другого запису;
- проробіть те ж саме з останнім записом, але потім змінене значення відновити.
Для того щоб вибрати запис по якій-небудь умові необхідно натиснути кнопку Критерии і ввести наступні параметри:
- для вибору по єдиному критерію в текстовому полі слід ввести одне конкретне значення у відповідному полі;
- для вибору більше якого-небудь значення в числовому полі поставити оператор порівняння “>“ у відповідному полі і написати потрібне значення;
- для вибору по першій літері текстового поля ввести першу літеру у відповідне поле;
- для вибору по двом критеріям в числових полях в двох будь-яких числових полях ввести оператори порівняння і потрібні значення.
Після введення параметрів натиснути клавішу ENTER. Для перегляду вибраних записів скористайтесь кнопками Предыдущая та Следующая. Щоб видалити попередній критерій скористайтесь кнопкою Очистить. Закрийте форму даних.
6.2. Сортування даних
Для того щоб відсортувати базу даних по убуванню третього стовпця необхідно вибрати будь-яку комірку третього поля бази даних і виконати команду ДАННЫЕ СОРТИРОВКА. З’явиться вікно діалогу, в якому в полі Сортировать повинно автоматично вибратись ім’я третього стовпця. Вказати порядок сортування по убуванню.
В першому полі Затем потрібно визначити порядок вторинного сортування для рядків третього поля, в яких є співпадаючі елементи. Потрібно вибрати ім’я другого поля і встановити прапорець По Возрастанию. Після встановлення всіх параметрів натисніть на клавішу ОК.
6.3. Фільтрація даних
Виділити будь-яку комірку бази даних і виконати команду ДАННЫЕ ФИЛЬТР АВТОФИЛЬТР. В рядку з іменами стовпців з’являться кнопки списків що розкриваються (кнопки із стрілками). За їх допомогою можна вибрати елементи бази, які потрібно вивести на екран. Для того щоб відфільтрувати записи по рівності в текстовому полі слід вибрати із списків що розкриваються відповідне значення цього поля. Для виконання інших завдань потрібно у відповідних полях вибрати із списку Настройка.
З’явиться вікно з назвою Пользовательский автофильтр. Клацніть по стрілці поруч з самим лівим вікном і потім виберіть потрібний оператор порівняння. В самому правому вікні що розкриється введіть значення, яке потрібно використати разом з оператором порівняння, або клацніть по стрілці поруч з вікном що розкривається та виберіть пункт списку. Можна також використовувати символи шаблона для пошуку входжень тексту.
7. Контрольні питання
Лабораторна робота № 6
„Створення зведених таблиць в MS EXCEL”
1. Мета роботи
Ознайомитись з можливостями створення і використання зведених таблиць в Excel.
2. Задачі роботи
Виконання прийомів формування, редагування, зміни та аналізу зведених таблиць.
3. Зміст роботи
При підготовці до даної роботи необхідно створити, на базі раніше виконаних таблиць, початкову таблицю об’ємом 30 рядків і 5-10 стовпців, що містить повторення значень в полях по рядкам і стовпцям (див. приклади в Додатках ЛР6)
3.1 Завантажити Excel і відкрити файл із створеною таблицею.
3.2 На окремих аркушах робочої книги створити зведені таблиці по завданням п.п.6.1 - 6.8.
3.3 Показати викладачу результати роботи, створивши файл і скласти звіт.
4. Зміст звіту
- назва роботи, її мета і послідовність виконання;
- структури зведених таблиць на листах 2 і 3;
- відповіді на питання п.7 по указівці викладача.
5. Загальні положення
5.1. Поняття про зведені таблиці
Для всебічного і ефективного аналізу даних великих таблиць в Excel використовуються таблиці, які називають зведеними (ЗТ). Функції роботи із ЗТ відносяться до одного з самих потужних інструментів Excel.
Головні переваги ЗТ - це представлення великих обсягів інформації в концентрованому і зручному для аналізу вигляді, широкі можливості для групування даних, а також можливість отримання проміжних і загальних підсумків, які поміщаються в таблицю автоматично.
Застосовувати інструмент ЗТ рекомендується для великих таблиць, де є різноманітні повторення значень в стовпцях та (або) рядках.
ЗТ є “трьохмірними”, тому що до звичних атрибутів таблиць: рядок та стовпець, додається атрибут - сторінка (на основі початкової таблиці з даними по 12 місяцям, можна створити ЗТ, що містить, наприклад, 13 сторінок; і “відкриваючи”, спеціальним способом, будь-яку з 12 сторінок, можна переглянути дані будь-якого одного місяця, а на 13 сторінці - підсумкові дані за всі місяці відразу).
5.2. Майстер зведених таблиць
Для побудови і модифікації ЗТ використовується Майстер зведених таблиць, що викликається за допомогою пункту меню Данные Сводная таблица. Майстер надає ряд діалогових вікон, в яких користувач, вибирає ті чи інші запропоновані варіанти, крок за кроком створює ЗТ. Побудова ЗТ виконується в чотири етапи (кроки). Перехід від етапу до етапу виконується кнопкою Шаг в діалогових вікнах Майстра зведених таблиць.
На першому кроці пропонується вибрати джерело даних для побудови таблиці. Створити ЗТ можна на основі даних, що знаходяться в одному з нижченаведених джерел.
У списку або базі даних будь-якого аркушу Microsoft Excel.
Під списком розуміють таблицю, що має заголовки стовпців тільки в одному першому рядку. Якщо список містить загальні і проміжні підсумки, їх потрібно видалити, щоб не включати у ЗТ.
У зовнішньому джерелі даних, котрим може бути база даних, текстовий файл або будь-яке інше джерело, крім книги Microsoft Excel.
У декількох діапазонах консолідації, тобто в декількох областях одного чи більше аркушів Excel. При цьому списки і аркуші повинні мати однакові заголовки рядків та стовпців.
У іншій зведеній таблиці, яку можна використовувати для створення на її основі нової ЗТ. Нова ЗТ буде пов’язана з тими ж вихідними даними. При обновленні даних ЗТ - джерела, інша ЗТ також обновляється.
Перед кожним видом джерела в діалоговому вікні встановлений перемикач. Первісно активізований найбільш розповсюджений варіант використання списків при побудові ЗТ. Вибрати інше джерело можна, зробивши активним відповідний перемикач. В залежності від категорії джерела змінюються наступні етапи роботи по створенню ЗТ.
На другому кроці в діалоговому вікні вказується інтервал комірок списку або бази даних, які повинні зводитись. В загальному випадку повне ім’я інтервалу що задається у вигляді: [ім’я_ книги]ім’я_аркушу!інтервал;
Якщо ЗТ будується в тій же книзі, де знаходиться вихідна таблиця, то ім’я книги вказувати не обов’язково. Для вказівки інтервалу з іншої закритої робочої книги натискується кнопка ПРОЛИСТАТЬ, далі, у діалоговому вікні що відкрилось, вибирається диск, каталог і файл закритої книги, вводяться ім’я робочого аркушу і інтервал комірок.
На третьому кроці в макеті таблиці в так званому режимі створення (рис. 6.1) створюється структура ЗТ і визначаються її функції. Макет представлений у центрі вікна і складається з областей: рядок, стовпець, сторінка і дані. Справа від макету відображаються всі імена полів (заголовки стовпців) в заданому інтервалі вихідної таблиці.
Розміщення полів в певній області макету виконується шляхом їх “перетягування” при натиснутій лівій кнопці миші. Щоб видалити поле з області макету, його перетягують за границі макету. Видалення поля приведе до приховування у ЗТ усіх залежних від нього величин, однак не вплине на вихідні дані.
Кожне поле в областях стовпець, рядок, сторінка може розміщатись тільки один раз, а в області дані воно може повторюватись з різними підсумковими функціями.
Кожна область макета, де розміщуються поля вихідної таблиці, має своє призначення, визначаюче зовнішній вигляд ЗТ та її функції:
Рядок - поля цієї області формують заголовки рядків ЗТ; якщо таких полів декілька, то вони розміщуються в макеті зверху вниз, забезпечуючи групування даних ЗТ по ієрархії полів, де для кожного елементу зовнішнього поля, елементи внутрішнього поля повторюються (див. приклад 1);
Стовпець - поля в цій області формують заголовки стовпців ЗТ; якщо таких полів декілька, то вони в макеті розміщуються зліва направо, забезпечуючи групування даних ЗТ по ієрархії полів;
Сторінка - поля в цій області виступають в якості фільтрів і дозволяють переглядати “на окремих сторінках” дані ЗТ, що відповідають різним значенням поля, поміщеного в цю область; поле сторінки в режимі перегляду розміщується на два рядки вище основної частини ЗТ в крайньому лівому стовпці (рис. 6.3). У сусідній справа комірці виводиться Все та поруч з’являється стрілка що розкривається, для вибору іншого елементу поля; в області сторінка може бути розміщено декілька полів, між якими встановлюється ієрархічний зв’язок - зверху вниз;
Дані – обов’язково обумовлена область для розміщення полів, по яким підводяться підсумки, відповідно обраній підсумковій функції; розміщені тут поля можуть бути довільних типів.
Приклад 1.
Завдання: По вихідній таблиці “Облік кількості дерев” (Додаток ЛР6, табл.1) побудувати ЗТ, згрупував дані по породі, № ділянки та віку. Підрахувати кількість дерев у вказаних групах.
Виконання: В макеті (рис.6.1.) в область рядок розміщені два поля: порода дерева і Nп.п. ділянки, в область стовпець - поле Вік. В області дані: Сума по полю Кількість. Готова ЗТ (рис.6.2.) виводиться на аркуші робочої книги Excel в режимі перегляду, де комірки, що містять імена полів, виділені темним кольором.
Рис. 6.1. Можливий вид макету зведеної таблиці
На перехресті рядків з номерами ділянок (котрі повторюються для кожної породи дерев) і стовпців з віком виводяться величини, які означають загальну кількість дерев даного віку і породи дерев, що ростуть на даній ділянці. По зовнішньому полю Порода виводяться проміжні підсумки (Дуб всього, Клен всього т. ін.) і загальні підсумки по рядкам і стовпцям.
Примітка: Excel автоматично обчислює проміжні підсумки під кожним елементом зовнішнього поля рядка або стовпця за допомогою тієї ж функції, котра вибрана для поля даних.
Дана ЗТ стане більш компактною і це дозволить окремо переглядати дані по кожній породі, якщо перемістити поле Порода в область сторінка.
Рис. 6.2. Зведена таблиця до прикладу 1
В отриманій ЗТ (рис.6.3) дані згруповані по віку, по номеру ділянки і прораховані для всіх порід дерев. Для виведення даних по будь-якій окремій породі, потрібно клацнути на стрілці що розкривається і вибрати інший елемент (наприклад, ялина).
Рис. 6.3. Фрагмент зведеної таблиці з полем в області сторінка
У зведених таблицях можна обчислити і представити 11 різних підсумкових функцій: Сума, Рахунок та інші. Крім того для кожного поля області дані можна задати одне з 9 додаткових обчислень, котрі задають спосіб представлення чисел (наприклад, замість абсолютних значень виведення процентної величини цих значень по відношенню до певного підсумку).
Для настроювання параметрів полів використовуються діалогові вікна двох типів із загальною назвою “Поле сводной таблицы”, елементи яких дозволяють:
- змінити вихідне ім’я поля, формат числа т. ін.;
- вказати тип підсумків що формуються по значенню поля;
- видалити поле з макету т. ін.
На рис. 6.4 представлений приклад вікна для настроювання параметрів поля дані, в яких кнопка ПАРАМЕТРЫ>> відкриває додаткове поле списку “Показать данные в виде” (рис. 6.5.) для завдання додаткових обчислень.
Приклад 2.
Завдання: По даним таблиці “Баланс ресурсів і використання м’яса і м’ясопродуктів” (Додаток, табл.2) визначити: як в процентному співвідношенні змінився загальний об’єм ресурсів за 1994-1996 рр.?
Виконання: Тому що, загальний об’єм ресурсів по кожній області знаходиться в стовпці ПІДСУМОК, потрібно створити ЗТ з обчисленням суми по полю ПІДСУМОК для кожного окремого року і визначити процентну різницю отриманої суми років 1995 і 1996 від 1994. В макеті в область сторінка помістити поле Області, в область рядок: Роки, в область дані - Сума по полю Підсумок; задати додаткове обрахування: " % різниці від" по полю Роки, базовий елемент: 1994 (рис. 6.5.).
Рис. 6.4. Фрагмент вікна “Поле зведеної таблиці”, в котрому задається додаткове обчислення “ % різниці від”
Рис. 6.5. Вікно “Поле зведеної таблиці”
для настроювання поля області дані
На рис. 6.6а) представлена ЗТ, що відображає сумарні значення по полю Підсумок за окремо взяті роки. В таблиці на рис. 6.6б) використане додаткове обчислення % різниці отриманої суми років 1995 і 1996 від 1994 для поля Підсумок області дані. По даним цієї ЗТ видно, що загальний об’єм ресурсів по всім областям зменшився в 1995 році на 6,2%, а в 1996 році на 14,37% в порівнянні з 1994 р. За допомогою стрілки що розкривається поля Області можна переглянути аналогічні дані по будь-якій області, що входить в вихідну таблицю.
На четвертому кроці вказується початкова комірка для вставки ЗТ і деякі додаткові параметри ЗТ. В поле ПОЧАТКОВА КОМІРКА вказується координата лівого верхнього кута ЗТ або на поточному аркуші, або на іншому аркуші поточної робочої книги або в іншій раніше відкритій робочій книзі. Якщо адреса початкової комірки не визначена, ЗТ створюється на новому робочому аркуші поточної робочої книги починаючи з комірки А1. В цьому ж вікні можна визначити потрібні чи ні в ЗТ Загальні підсумки по стовпцям і рядкам, а також інші параметри, які по умовчанню відзначені хрестиком, тобто включені.
|
а) б) |
|
Рис. 6.6. Зведені таблиці для прикладу 2 |
5.4. Редагування зведеної таблиці
Редагувати ЗТ можна, як в режимі перегляду, так і повернувшись, в режим створення таблиці. Змінам в таблиці можуть піддаватись структура (добавлення нових полів, видалення існуючих, зміна місцезнаходження поля), тип використовуваної функції) і додаткові обчислення.
Сукупність усіх способів редагування в різних режимах зводиться до наступних дій:
- перетягування назви полів у відповідну область;
- виклик команди Дані головного меню;
- звернення до контекстно-залежного меню поля;
- подвійне клацання на імені поля;
- клацання на потрібній кнопці панелі інструментів "Запрос и сведение".
Панель інструментів "Запрос и сведение" з’являється на екрані монітора автоматично після побудови ЗТ або викликається командою головного меню Вид Панели инструментов. За допомогою кнопок панелі інструментів "Запрос и сведение" можна швидко переміститись в діалогове вікно 3 із 4 Майстра зведених таблиць (тобто для зміни структури таблиці), відкрити діалогове вікно “Поле сводной таблицы”; відобразити всі сторінки поточної ЗТ на окремі аркуші поточної книги т. ін..
6. Методичні рекомендації
6.1 Для створення ЗТ виконайте команду ДанныеСводная таблица. На першому кроці в діалоговому вікні Майстра зведених таблиць виберіть опцію "В списке или базе данных Microsoft Excel". На другому кроці, щоб вказати інтервал комірок, перейдіть на Лист1, виділіть в ньому, за допомогою миші, всю таблицю разом із рядком заголовків стовпців (в цьому випадку Excel автоматично введе у вікно "діапазон" назву аркуша і виділені комірки) і клацніть на кнопці Шаг>. Розмістіть в макеті по одному полю в областях рядок, стовпець і дані. Для поля області дані задайте підсумкову функцію Среднее, настанови діалогового вікна на четвертому кроці оставте без змін.
Примітка: При побудові ЗТ використовувати поля вихідної таблиці на власний розсуд або запропоновані викладачем, щоб ЗТ робила аналіз даних вихідної таблиці більш зручним і ефективним.
6.2 Повернувшись в режим створення за допомогою кнопки панелі інструментів (курсор повинен знаходиться у будь-якій комірці ЗТ), добавити до створеної зведеної таблиці поле в область сторінка і клацнути на кнопці ЗАКІНЧИТИ.
6.3 В режимі перегляду проаналізуйте дані зведеної таблиці з різними значеннями поля області сторінка, поміняйте місцями поля в областях стовпець і рядок, “перетягнувши” назву поля в потрібну область безпосередньо в робочому аркуші. Змінити вид підсумкової функції для області дані на Рахунок, перейшовши в діалогове вікно “Поле зведеної таблиці”, для виклику якого перевести курсор в будь-яку комірку поля дані і клацнути на кнопці панелі інструментів, або в контекстно-залежному меню поля виконати команду Поле зведеної таблиці.
6.4 На наступному аркуші книги створити нову зведену таблицю, в якій розмістити в області рядок два поля вихідної таблиці.
6.5 В режимі перегляду змінити тип підсумку для зовнішнього поля області рядок. Змінити тип підсумку можна у вікні “Поле зведеної таблиці”.
6.6 Виключити загальні підсумки по стовпцям і рядкам зведеної таблиці, перейшовши за допомогою команди ДаніЗведена таблиця і Крок> на четвертий крок Майстра зведених таблиць.
6.7 На новому аркуші книги створити зведену таблицю з двома полями в області сторінка. Для поля області дані задати підсумкову функцію і додаткові обчислення на свій розсуд. Для установки додаткових обчислень, потрібно клацнути на кнопці ПАРАМЕТРИ >> вікна “Поле зведеної таблиці”.
6.8 Відобразити “усі сторінки” одного з полів області сторінка на окремих аркушах за допомогою кнопки панелі інструментів і переглянути отриманні аркуші робочої книги.
7. Контрольні питання