Вишня В.Б., Ілюхін С.М., Кравець В.П., Шумейко О.О. Основи інформатики та обчислювальної техніки (навчально-методичний посібник для курсантів, студентів та слухачів)
Подождите немного. Документ загружается.
191
сторінки, і дати команду Вставка > Разрыв страницы. Програма Excel
вставить примусові розриви сторінки перед рядком і стовпцем, у яких
розташовується даний осередок. Якщо обраний осередок знаходиться в
першому рядку чи в стовпці А, то розрив сторінки задається тільки по
одному напрямку.
5.14. Побудова діаграм і графіків.
У програмі Excel термін діаграма
використовується для позначення
усіх видів графічного представлення числових даних. Побудова графічного
зображення проводиться на основі ряду даних. Так називають групу
осередків з даними в межах окремого рядка чи стовпця. На одній діаграмі
можна відображати кілька рядків даних. Діаграма являє собою вставний
об’єкт, впроваджений на один з листів робочої книги.
Вона може
розташовуватися на тому же листі, на якому знаходяться дані, чи на будь-
якому іншому листі (часто для відображення діаграми відводять окремий
лист). Діаграма зберігає зв’язок з даними, на основі яких вона побудована, і
при відновленні цих даних автоматично змінює свій вигляд.
Для побудови діаграми звичайно використовують Майстер діаграм
,
що запускається щигликом на кнопці Мастер диаграмм на стандартній
панелі інструментів. Часто зручно заздалегідь виділити область, що містить
дані, що будуть відображатися на діаграмі, але задати цю інформацію можна
й у ході роботи майстра.
Вибір типу діаграми. На першому етапі роботи майстра вибирають
форму діаграми. Доступні форми перераховані в списку
Тип на вкладці
Стандартные. Для обраного типу діаграми праворуч міститься кілька
варіантів представлення даних (палітра Вид), з яких варто вибрати найбільш
придатний. На вкладці Нестандартные відображається набір цілком
сформованих типів діаграм з готовим форматуванням. Після завдання форми
діаграми варто клацнути на кнопці Далее.
Вибір даних. Другий етап роботи майстра служить для
вибору даних,
192
по яких буде будуватися діаграма. Якщо діапазон даних був обраний
заздалегідь, то в області попереднього перегляду у верхній частині вікна
майстра з’явиться приблизне відображення майбутньої діаграми. Якщо дані
утворять єдиний прямокутний діапазон, то їх зручно вибирати за допомогою
вкладки Диапазон данных. Якщо дані не утворять єдиної групи, то
інформацію для
малювання окремих рядів даних задають на вкладці Ряд.
Попереднє представлення діаграми автоматично обновляється при зміні
набору відображуваних даних.
Оформлення діаграми. Третій етап роботи майстра (після щиглика
на кнопці Далее) складається у виборі оформлення діаграми. На вкладках
вікна майстра задаються:
• назва діаграми, підписи осей (вкладка Заголовки);
• відображення і маркірування
осей координат (вкладка Оси);
• відображення сітки ліній, рівнобіжних осям координат
(вкладка Линии сетки);
• опис побудованих графіків (вкладка Легенда);
• відображення написів, що відповідають окремим елементам
даних на графіку (вкладка Подписи данных);
• представлення даних, використаних при побудові графіка, у
виді таблиці (вкладка Таблица данных).
В залежності від типу
діаграми деяких з перерахованих вкладок може
не бути.
Розміщення діаграми.
На останньому етапі роботи майстра (після
щиглика на кнопці Далее) указується, чи варто використовувати для
розміщення діаграми новий робочий лист, чи один з наявних. Звичайно цей
вибір важливий тільки для наступного друку документа, що містить
діаграму. Після щиглика на кнопці Готово діаграма будується автоматично і
вставляється на зазначений робочий лист.
Редагування
діаграми Готову діаграму можна змінити. Вона
193
складається з набору окремих елементів, таких, як самі графіки (ряди даних),
осі координат, заголовок діаграми, область побудови та інше. При щиглику
на елементі діаграми він виділяється маркерами, а при наведенні на нього
покажчика миші — описується спливаючою підказкою. Відкрити діалогове
вікно для форматування елемента діаграми можна через меню Формат (для
виділеного елемента
), чи через контекстне меню (команда Формат). Різні
вкладки діалогового вікна, що відкрилося, дозволяють змінювати параметри
відображення обраного елемента даних.
Якщо потрібно внести в діаграму істотні зміни, варто знову
скористатися майстром діаграм. Для цього варто відкрити робочий лист із
діаграмою, чи вибрати діаграму, впроваджену в робочий лист із даними.
Запустивши майстер
діаграм, можна змінити поточні параметри, що
розглядаються у вікнах майстра, як задані за замовчуванням. Щоб видалити
діаграму, можна видалити робочий лист, на якому вона розташована
(Правка > Удалить лист), чи вибрати діаграму, впроваджену в робочий
лист із даними, і натиснути клавішу Delete.
5.15. Використання електронних таблиць як баз даних.
Звичайно бази
даних являють собою набір взаємозалежних таблиць.
Найпростіші бази даних складаються з однієї таблиці. Як таку базу даних,
цілком можна використовувати електронну таблицю Excel. Програма Excel
включає набір функцій, що дозволяють виконувати всі основні операції,
властиві базам даних.
Інформація в базі даних складається з набору записів, кожен з яких
містить той
самий набір полів. Записи характеризуються порядковими
номерами, а кожне поле має заголовок, що описує його призначення.
Щоб вміст робочого листа розглядався як база даних Excel, необхідно
дотримувати строгих правил. Кожному полю запису відповідає один
стовпець робочого листа. Стовпці бази даних повинні йти підряд, без
проміжків між ними. У першому рядку
кожного стовпця (точніше, у тім
194
рядку, з якого починається база даних) повинний бути зазначений заголовок
відповідного поля. Заголовок поля повинний займати не більш одного
осередка. Вміст осередка заголовка повинний бути унікальним в межах
робочого листа.
Записи бази даних повинні йти безпосередньо нижче рядка заголовків.
Порожні рядки не допускаються. Взагалі, порожній рядок розглядається як
ознака закінчення бази
даних, тобто, записи повинні йти підряд, без
проміжків між ними. У базі даних, оформленої таким чином, можливе
виконання більшості операцій, характерних для баз даних. Всі операції з
базами даних виконуються приблизно однаково. Спочатку необхідно вибрати
будь-який осередок у базі даних, а потім почати потрібну операцію. При
цьому весь діапазон
записів бази даних вибирається автоматично.
Сортування бази даних.
Для
сортування бази даних використовується
команда Данные > Сортировка.
Діалогове вікно Сортировка диапазона
служить для вибору поля, по якому
проводиться сортировка. Якщо задані
заголовки полів, то, як позначення,
використовуються саме вони, якщо
заголовки відсутні, як заголовки
використовуються позначення стовпців.
Перше з обраних полів визначає режим
первинного сортування. Якщо при збігу
значень у цьому полі існують
додаткові критерії, що визначають порядок записів, то можна також задати
поля для вторинного і третинного сортування. По кожному з полів
сортування може виконуватися в порядку зростання чи убування.
Фільтрація бази даних. Тому що база даних може включати
величезне число записів (у програмі Excel природною межею
служить
195
максимальне число рядків робочого листа — 65536), не завжди потрібно
відображати всі ці записи. Виділення підмножини загального набору записів
називається фільтрацією. Найбільш простим способом фільтрації в програмі
Excel є використання автофильтра.
Застосування автофільтру. Включення режиму фільтрації
здійснюється командою Данные > Фильтр > Автофильтр. При цьому для
кожного поля бази даних автоматично створюється набір
стандартних
фільтрів, доступних через списки, що розкриваються. Кнопки цих списків,
відображаються біля поля заголовка кожного стовпця.
За замовчуванням використовується варіант Все, що вказує, що
записи бази даних повинні відображатися без фільтрації. Варіант Первые 10
дозволяє відібрати визначене число (чи відсоток) записів за яким-небудь
критерієм. Варіант Условие дозволяє задати спеціальну умова
фільтрації.
Крім того, мається можливість добору записів, що мають у потрібному полі
конкретне значення.
При застосуванні фільтра, записи, що не попадають у відібрану
підмножину, не відображаються. Загальне число записів бази даних і число
відібраних записів відображається в рядку стану програми. Вихідні номери
відібраних записів зберігаються, але відображаються блакитним кольором.
Точно таким
же кольором зображується заголовок поля (чи декількох полів),
по якому була виконана фільтрація.
Відфільтрована база даних може використовуватися при печаті
(друкуються тільки записи, що відносяться до обраної підмножини) і при
побудові діаграм (графік будується на базі обраних записів). В останньому
випадку зміна критеріїв фільтрації автоматично змінює вид діаграми.
Розширена фільтрація
. Якщо потрібно, щоб діаграма не залежала від
зміни критеріїв фільтрації, необхідно створити копію відповідних записів.
Створити таку копію можна тільки за допомогою розширеного фільтра.
Розширений фільтр оперує з двома базами даних однакової структури, тобто
196
з однаковим набором полів. Звичайно, для запису умов фільтрації створюють
окремий робочий лист.
Запис у базі даних умов може містити порожні поля. Кожне
непорожнє поле задає визначена умова фільтрації. Для текстових полів
потрібно збіг поля в базі даних із заданим тут значенням. Для числових полів
можна також указувати знаки порівняння. Умови
, задані в межах одного
запису, повинні виконуватися одночасно. Для умов, заданих у рамках різних
записів, досить виконання хоча б одного.
При виборі розширеної фільтрації командою Данные > Фильтр >
Розширенный фильтр, можна виконати фільтрацію на місці, чи витягти
відфільтровані записи і помістити їх окремо, на будь-який робочий лист
будь
-якої відкритої робочої книги.
Аналіз даних. Докладний аналіз даних з таблиці бази даних вимагає
можливості одержання зведеної інформації з записів бази даних. Фактично,
мова йде про сполучення засобів фільтрації і підсумкових обчислень.
Результати такого аналізу можуть бути представлені у виді зведеної таблиці
чи зведеної діаграми. При створенні зведеної діаграми автоматично
створюється і зведена таблиця, що містить дані, на основі яких будується
діаграма.
5.16. Побудова зведеної таблиці.
Побудова зведеної таблиці проводиться за допомогою Мастера
сводной таблицы. Для цього служить команда Данные > Сводная таблица.
Спочатку, як звичайно, потрібно виділити осередок, що відноситься до бази
даних.
На першому етапі роботи майстра вибирається
тип даних (для
використання бази даних Excel — це пункт В списке чи базі даних Microsoft
Excel) і тип оформлення зведених даних — Сводная таблица. Потім, після
щиглика на кнопці Далее, треба уточнити, чи правильно був обраний
діапазон, що охоплює базу даних. Якщо дотримуватися правил обмеження
197
бази даних порожніми осередками, то автоматичний вибір здійснюється
безпомилково. Після чергового щиглика на кнопці Далее вибирається місце
розташування зведеної таблиці. У більшості випадків варто розміщати її на
новому робочому листі (перемикач Новый лист).
Зміст зведеної таблиці. Але одночасно з цим треба сформувати зміст
і оформлення зведеної таблиці. Для вибору змісту
треба клацнути на кнопці
Макет. Зведена таблиця складається з чотирьох областей: Сторінка, Рядок,
Стовпець і Дані. Кожній області зведеної таблиці повинні відповідати одне
чи кілька полів бази даних. Виключення робиться тільки для поля Сторінка,
яке можна залишити порожнім. При заповненні зведеної таблиці дані з
відповідних полів беруться автоматично. Для заповнення цих
полів служать
кнопки, назви яких збігаються з назвами полів бази даних. Помістити кнопку
в потрібне поле можна методом перетаскування. У зведеній таблиці можна
використовувати тільки ті поля, що необхідні. Незадіяні поля в доборі й
обчисленнях не приймають участі.
Область Сторінка відіграє роль зовнішнього фільтра. Дані в іншій
частині таблиці відносяться
до значення, обраного в цьому полі. Область
Рядок містить значення, що визначають зміст рядка таблиці, і задає другий
рівень сортування. Аналогічним образом, область Стовпець визначає вміст
стовпців. На відміну від області Сторінка, у зведеній таблиці містяться дані
для всіх можливих значень полів, які лежать у цих областях.
Область Дані визначає власне
вміст таблиці. На відміну від всіх інших
областей, до даних, що попадають в осередок таблиці, застосовується
функція для підсумкових обчислень (за замовчуванням — підсумовування).
Якщо необхідно змінити цю функцію, треба двічі клацнути на відповідній
кнопці і вибрати потрібну операцію зі списку, що розкривається.
Крім стандартного набору підсумкових функцій, можна
використовувати і
додаткові обчислення. Для цього треба клацнути на кнопці
Дополнительно, вибрати потрібне значення зі списку, що розкривається,
198
Дополнительные вычисления, і, якщо потрібно, вказати необхідні
параметри. Після вибору і настроювання даних варто клацнути на кнопці
ОК.
Оформлення зведеної таблиці. Для настроювання зовнішнього
вигляду зведеної таблиці треба клацнути на кнопці Параметри. Тут можна
задати ім’я листа зведеної таблиці, включити режим підсумовування даних
по рядках і стовпцях, дозволити доступ
до інформації, використаної при
обчисленні, виконати інші настроювання. По їхньому завершенні потрібно
клацнути на кнопці ОК, а потім на кнопці Готово, і зведена таблиця
будується автоматично.
Робота зі зведеною таблицею. При створенні зведеної таблиці
автоматично відкривається і панель інструментів Сводные таблицы. Надалі
відкривати і закривати цю панель можна, клацаючи правою кнопкою
миші на
кожній з відкритих панелей інструментів, і, вибираючи пункт Сводные
таблицы з контекстного меню.
Якщо потрібно змінити вид зведеної таблиці, це можна зробити за
допомогою кнопок, які знаходяться у таблиці у відповідних областях. При
щиглику на відповідній кнопці, з’являється невелике вікно, у якому можна
відключити деякі з відображуваних
показників. Для області Сторінка
праворуч відображається список, що розкривається, що дозволяє вибрати
різні умови фільтрації. Крім того, клацнувши в межах зведеної таблиці
правою кнопкою миші, і, вибравши в контекстному меню пункт Отобразить
страниці, можна розділити єдину зведену таблицю на набір таблиць, що
відповідають різним значенням, які можна вибрати в області Сторінка
.
Зведена таблиця залишається динамічно зв’язаною з даними, на
підставі яких вона була побудована. Це, зокрема, означає, що для будь-якого
значення, приведеного в зведеній таблиці, можна з’ясувати, як воно було
отримано. Для цього треба двічі клацнути на відповідному осередку таблиці.
199
При цьому автоматично створюється новий робочий лист, що містить
вибірку записів, використаних при обчисленні даного значення.
Динамічний зв’язок з вихідними даними виявляється й у тім, що при
зміні даних не потрібно заново формувати зведену таблицю. Досить
клацнути в межах таблиці правою кнопкою миші і вибрати в контекстному
меню пункт Обновить
Данные.
Утім, це не відноситься до ситуації, коли записи бази даних
додаються чи видаляються. У цьому випадку необхідно знову запустити
майстер зведених таблиць (простіше всього для цього скористатися кнопкою
Мастер сводных таблиц на панелі інструментів Сводные таблицы),
клацнути на кнопці Назад і уточнити діапазон осередків, що включають
записи бази даних.
Після щиглика на кнопці Готово дані зведеної таблиці
будуть оновлені з урахуванням зміни числа записів.
5.17. Побудова зведених діаграм.
Зведена діаграма являє собою діаграму, побудовану на основі зведеної
таблиці. При створенні зведеної діаграми відповідна їй зведена таблиця
створюється автоматично, навіть якщо явного запиту на її створення не було.
Для створення зведеної
діаграми по вже існуючій зведеній таблиці
найпростіше клацнути на кнопці Мастер диаграмм на панелі інструментів
Сводные таблицы. Щоб створити зведену діаграму безпосередньо на основі
бази даних, треба запустити майстер зведених таблиць командою Данные >
Сводная таблица і на першому етапі роботи майстра установити перемикач
Сводная диаграмма. Далі найпростіше відразу клацнути
на кнопці Готово.
Програма Excel автоматично створить нові робочі листі, що
відповідають створюваній зведеній таблиці і зведеній діаграмі, і відкриє лист
із діаграмою. Реальної інформації цей лист поки що не містить. Формування
вмісту діаграми здійснюється точно так само, як формування зведеної
таблиці: перетаскуванням кнопок, що відповідають полям бази даних, у
потрібні області діаграми. Таких областей три.
200
• Область сторінки розташовується у верхній частині діаграми.
• Область даних збігається з областю побудови.
• Область категорій ( рядка і стовпців проміжної зведеної таблиці)
розташовується в нижній частині діаграми чи ліворуч від неї.
Кнопки полів, які можна перетаскувати, у даному випадку
розташовуються безпосередньо на панелі інструментів Зведені таблиці. Щоб
скасувати використання
поля, його треба перемістити з області діаграми
назад на панель.
Інформація про поля бази даних відображається на діаграмі, так само,
як і в зведеній таблиці, — із кнопками, що розкриваються. Використовуючи
їх, можна змінити правила фільтрації чи відключити відображення деяких
значень. Подальше форматування і настроювання діаграми здійснюється
через контекстні меню. Так,
щоб змінити вид діаграми, треба клацнути
правою кнопкою миші на області побудови і вибрати в контекстному меню
пункт Тип диаграммы. Для зміни формату клацніть на відповідному
елементі діаграми правою кнопкою миші і виберіть у контекстному меню
відповідний пункт Формат... . У цілому, можливості керування
відображенням зведених діаграм трохи нижче, ніж для звичайних
діаграм, що
пов’язано з більш складним внутрішнім механізмом їхньої побудови.
5.18. Невірні формули.
Якщо одержати значення в результаті обчислення формули з якихось
причин неможливо, програма Excel видає замість значення осередку код
помилки. Можливі коди помилок і причини їхньої появи приведені в таблиці.
Таблиця 5.2. Стандартні повідомлення програми про помилки
Код помилки Причина виникнення