Лабораторна робота № 7

Робота зі зведеними таблицями та діаграмами у Microsoft Excel

Теоретичні відомості:

Додавання зведених таблиць

Команда Сводная таблица в вкладці Вставка є спеціальним засобом обробки даних, який створює таблицю, що набудовується, для організації полів на листі. Створюється зведена таблиця за допомогою Майстра зведених таблиць. Після створення можна продовжити її налагоджування за допомогою перестановки полів і накладення нових форматів даних.

Для створення зведеної таблиці варто виділити будь-яку комірку в списку, що буде проглядатися за допомогою зведеної таблиці (якщо можна, краще виділити весь список) і виконати команду Сводная таблица в вкладці Вставка. Після чого відкриється діалогове вікно Создание сводной таблицы,

Рис. 1 – Створення зведенної таблиці

який просить указати джерело даних для таблиці. Необхідно обрати таблицю або діапазон данних для аналізу та вказати де розташувати звіт по зведеній таблиці. Аналогічно можна створювати таблиці по зовнішнім даним, отриманим за допомогою Microsoft Query, по декількох діапазонах консолідації або по іншій зведеній таблиці. Натискання кнопки Далее виводить вікно (рис. 1), за допомогою якого необхідно виконати формування макета звіту. На екрані з'явиться таблиця з областями для сторінок, рядків, стовпців і даних. На рухливих кнопках у правій частині вікна приведені назви полів списку. Визначите вихідну структуру зведеної таблиці перетаскуванням кнопок з полями в області Фильтр отчета, Названия строк, Название столбцов і Значения. Поля в області Названия строк, утворять рядки зведеної таблиці, в області Названия столбцов – стовпці, в області Фильтр отчета – використовується для фільтрації всього звіту на основі обраного єлемента в фільтрі звіту, Значения – для виводу сумарних числових значень.

Рис. 2 – Список полей зведеної таблиці

В будь-який момент можна змінити розташування полів, просто перетгнувши їх у потрібну область. Після створення звіту зведеної таблиці або діаграми в нього можна додати поля, що містять дані, які необхідно відобразити у звіті. Як правило, для кожної з областей в розділі макета вибирається одне поле, проте для перегляду різних значень певного поля можна додати в область Значения кілька його копій.

Для фільтрації даних вкажіть необхідні параметри в меню фільтр.

Щоб змінити положення полів у звіті зведеної таблиці, клацніть ім'я поля в одній з областей і виберіть одну з команд, які запропоновані.

Щоб видалити поле, виконайте у списку полів зведеної таблиці одну з таких дій:

- У полі Выберите поля для добавления в отчет зніміть прапорець поля, яке потрібно видалити.

- В області макета клацніть поле, яке потрібно видалити, і натисніть кнопку Удалить.

- В області макета клацніть поле, яке потрібно видалити, і перетягніть його за межі списку полів зведеної таблиці.

Робота з діаграмами

За даними електронної таблиці можна побудувати кругову, крапкову, кільцеву, пузиркову, пелюсткову або поверхневу діаграму на поточному або на окремому листі. Об'ємні діаграми можуть мати вигляд тривимірних стовпчиків, пірамід, конусів або циліндрів. За замовчуванням у Microsoft Excel установлена діаграма, при цьому можна змінити тип прийнятої за замовчуванням.

Діаграма, поміщена на лист Excel і збережена в одному файлі з ним, називається впровадженою. При відновленні даних у таблицях обновляються і впроваджені діаграми. Якщо виділити об'єкт-діаграму, то цей об'єкт можна переміщати, у нього можна додавати дані, можна виділяти, форматувати, переміщати і змінювати розміри більшості вхідних у нього елементів.

Щоб створити діаграму в Excel, спочатку введіть числові дані на лист. Потім можна побудувати на їх основі діаграму, вибравши потрібний тип діаграми на вкладці Вставка в групі Диаграммы. Виберіть тип і підтип діаграми.

Щоб переглянути всі доступні типи діаграм, натисніть стрілочку в нижньому правому кутку, щоб запустити діалогове вікно Вставка диаграммы, а потім використовуйте стрілки для прокрутки типів діаграм.

Рис.3 – Типи діаграм

За замовчуванням діаграма додається на лист як впроваджена діаграма . Щоб помістити діаграму на окремий лист діаграми , змініть її розташування , виконавши такі дії:

1. Клацніть якому місці впровадженої діаграми , щоб активувати її. Відкриється панель Робота с диаграммами з додатковими вкладками Конструктор , Макет і Формат.

2. На вкладці Конструктор у групі Расположение натисніть кнопку Переместить диаграмму.

3. У розділі Разместить диаграмму виконайте одну з таких дій .

• Щоб додати діаграму на окремий лист , встановіть перемикач на окремому аркуші .

• Якщо потрібно впроваджена діаграма на аркуші, встановіть перемикач на наявному аркуші, а потім виберіть лист в поле на наявному аркуші.

Excel автоматично призначає діаграмі ім'я, наприклад Діаграмма1 для першої діаграми, створеної на аркуші. Щоб змінити ім'я діаграми, виконайте такі дії:

1. Клацніть діаграму.

2. На вкладці Макет у групі Свойства клацніть текстове поле Имя диаграммы.

3. Введіть нове ім'я.

4. Натисніть клавішу ENTER.

Для того что змінити макет елементу клацніть елемент діаграми, макет якого потрібно змінити, або виконайте такі дії, щоб вибрати його із списку елементів діаграми:

1. Клацніть якому місці діаграми, щоб відобразити кошти Работа с диаграммами.

2. На вкладці Формат у групі Текущий фрагмент клацніть стрілку в полі Элементы диаграммы, а потім виберіть потрібний елемент діаграми.

На вкладці Макет у групі Подписи, Оси або Фон натисніть кнопку елемента діаграми, що відповідає обраному елементу, а потім виберіть потрібний макет.

Рис. 4 – Групи Підписи, Осі, Фон

Для зміни формату елемента клацніть елемент діаграми, формат якого потрібно змінити, або виконайте такі дії, щоб вибрати його із списку елементів діаграми:

1.Клацніть якому місці діаграми, щоб відобразити кошти Работа з диаграмамми.

2.На вкладці Формат у групі Текущий фрагмент (рис.5)клацніть стрілку в полі Елементы диаграми, а потім виберіть потрібний елемент діаграми.

Рис. 5 – Текущий фрагмент

• Щоб форматувати виділений елемент діаграми , у групі Текущий фрагмент натисніть кнопку Формат выделенного фрагмента, а потім виберіть потрібні параметри форматування.

Рис. 6 - Формат области диаграммы

• Щоб форматувати фігуру виділеного елемента діаграми , у групі Стили фигур (рис. 6) виберіть потрібний стиль або натисніть кнопку Заливка фигуры, Контур фигуры або Эффекты для фигур , а потім виберіть потрібні параметри форматування.

Рис. 6 – Стили фигур

• Щоб за допомогою WordArt відформатувати текст у виділеному елементі діаграми , у групі Стили WordArt (рис. 7) виберіть потрібний стиль або натисніть кнопку Заливка текста, Контур текста або Анимация, а потім виберіть потрібні параметри форматування.

Рис. 7 - Стили WordArt

Для додавання назви диаграми клацніть якому місці діаграми , до якої потрібно додати назву. Відкриється панель Работа с диаграммами з додатковими вкладками Конструктор, Макет та Формат. На вкладці Макет у групі Подписи натисніть кнопку Название диаграммы(рис.8). Виберіть пункт Название по центру с перекрытием або Над диаграммой.

Рис.8 – Названые диаграммы

У текстовому полі Название диаграммы, що з'явилося в діаграмі , введіть потрібний текст.

Яккщо необхідно зробити розрив рядка встановіть покажчик у місце передбачуваного розриву і натисніть клавішу ENTER.

Для форматування тексту виділіть його , а потім виберіть необхідні параметри форматування на міні- панелі інструментів. Можна також скористатися кнопками форматування на стрічці ( вкладка Главная , група Шрифт) . Щоб відформатувати всі назви , клацніть правою кнопкою миші , в контекстному меню виберіть команду Формат названия диаграммы і задайте потрібні параметри форматування. Для додавання назв осей потрібно клацнути у будь-якому місці диаграми, до осей якої необхідно додати назву. Відкриється панель Работа с диаграммами с додатковими вже відомими вкладками Конструктор, Макет та Формат. На вкладці Макет у групі Подписи натисніть кнопку Названия осей . Далі виконайте одну або декілька з таких дій:

• Щоб додати назву до основної горизонтальній осі ( осі категорій) , виберіть пункт Название основной горизонтальной оси (рис.9), а потім виберіть потрібний параметр .

Рис. 9 – Название основной горизонтальной оси

• Щоб додати назву до основної вертикальної осі ( осі значень) , виберіть пункт Название основной вертикальной оси (рис.10), а потім виберіть потрібний параметр .

Рис.10 – Название основной вертикальной оси

• Щоб додати назву до осі глибини ( осі рядів даних) , виберіть пункт Название оси Z(рис.11), а потім виберіть потрібний параметр .

Рис. 11 – Название оси Z

Цей параметр доступний тільки в тому випадку , якщо обрана діаграма дійсно є об'ємною діаграмою , наприклад об'ємною гистограмою .

У текстовому полі Название осей , що з'явилося в діаграмі , введіть потрібний текст. Для вставки розриву рядка встановіть покажчик у місце передбачуваного розриву і натисніть клавішу ENTER.

Для форматування тексту виділіть його , а потім виберіть необхідні параметри форматування на міні- панелі інструментів. Можна також скористатися кнопками форматування на стрічці ( вкладка Главная , група Шрифт) . Щоб відформатувати всю назву , клацніть на його правою кнопкою миші , в контекстному меню виберіть команду Формат название оси і задайте потрібні параметри форматування.

При перемиканні на інший тип діаграми , який не підтримує назви осей (наприклад , кругову діаграму) , назви осей перестають відображатися. Назви знову будуть відображатися , якщо переключитися назад на тип діаграми , що підтримує назви осей .

Назви , які відображаються для допоміжних осей , будуть втрачені при перемиканні на тип діаграми , який не відображує допоміжні осі.

Для зв'явування назви з коміркою листа потрібно клацнути на назву діаграми або осі, яку потрібно зв'язати зв'язати з коміркою листа.На аркуші в рядку формул введіть знак рівності (=). Виберіть клітинку аркуша, що містить дані або текст, які потрібно відобразити на діаграмі. (У рядок формул можна також ввести посилання на клітинку аркуша. Введіть знак рівності, ім'я листа і знак оклику, наприклад = Лист1! F2.) Далі натисніть клавішу ENTER. При створенні діаграми відображається легенда , але можна її приховати або змінити її розташування . Клацніть діаграму , в якій необхідно відобразити або приховати легенду. Відкриється панель Робота с диаграмамми з додатковими вкладками Конструктор , Макет і Формат. На вкладці Макет у групі Подписи натисніть кнопку Легенда(рис.12) . Виконайте одну з таких дій :

• Щоб приховати легенду , виберіть пункт Ні.

Щоб швидко видалити з діаграми легенду або елемент легенди , виберіть її та натисніть клавішу DELETE. Можна також клацнути легенду або елемент легенди правою кнопкою миші і вибрати команду Удалить .

• Щоб відобразити легенду , виберіть потрібний параметр відображення .

При виборі одного з параметрів відображення легенда переміщається , а область побудови автоматично змінюється відповідно з нею. Якщо перемістити легенду і змінити її розмір за допомогою миші , область побудови автоматично не зміниться.

Рис. 12 - Легенда

• Щоб задати додаткові параметри , виберіть пункт Дополнительные параметры легенды і вкажіть потрібний параметр відображення .

За замовчуванням легенда і діаграма не перекриваються . Якщо місце обмежене , можливо , вдасться зменшити діаграму , знявши прапорець Показывать легенду без перекрытия диаграммы .

Для відобрження або прихову лінії сітки клацніть на необхідну діаграму.

На вкладці Макет у групі Оси натисніть кнопку Линии сетки. Щоб додати горизонтальні лінії сітки на діаграму , виберіть пункт Горизонтальные линии сетки по основной оси і потрібний параметр . Якщо діаграма містить допоміжну горизонтальну вісь , можна також вибрати пункт Линии сетки на вспомогательной горизонтальной оси.

Щоб додати вертикальні лінії сітки на діаграму , виберіть пункт Вертикальные линии сетки по основной оси і потрібний параметр . Якщо діаграма містить допоміжну вертикальну вісь , можна також вибрати пункт Линии сетки на вспомогательной вертикальной оси.

Щоб додати лінії сітки осі глибини на об'ємну діаграму , виберіть пункт Линии сетки по оси Z і потрібний параметр . Цей пункт доступний тільки в тому випадку , якщо обрана діаграма дійсно є об'ємною діаграмою , наприклад об'ємної гистограммой .

Щоб приховати лінії сітки , виберіть пункт Горизонтальные линии сетки по основной оси, Вертикальные линии сетки по основной оси або Линии сетки по оси Z ( на об'ємній діаграмі ) і на Ні.

Щоб швидко видалити лінії сітки з діаграми , виділіть їх , а потім натисніть клавішу DELETE.