Posobie_1_chast2_Excel

Зміна вихідних даних зведеної таблиці

Якщо в список, на основі якого була створена зведена таблиця, уставлені додаткові рядки або стовпці, необхідно обновити і зведену таблицю. Для цього треба:

•натиснути будь-яку комірку зведеної таблиці;

•на панелі інструментів Сводные таблицы зі списку кнопки Сводная таблица вибрати позицію Мастер – відкриється діалогове вікно Мастер сводных таблиц и диаграмм – шаг 3 из 3;

•натиснути Назад;

•у полі Діапазон діалогового вікна Мастер сводных таблиц – шаг 2 з 3

задати новий діапазон вихідних даних;

•натиснути Готово.

10.Знайдіть у зведеній таблиці дані про кількість облігацій, закуплених у січні, і переконайтеся, що закупівель акцій у січні не було.

11.На листі Список за допомогою форми даних додайте в список новий запис з наступними даними:

f№ – 1142;

fМісяць – січень;

fНайменування товару – Облігація;

fЕмітент – Казначейство;

fГарантія –12;

fЦіна за ед. – 1065;

fЗакупівля –10;

70

fПовернення – 0;

fОбсяг браку – 0;

fОдержувач – Підприємство1.

12.На листі Зведена таблиця задайте новий діапазон даних (А2:К91).

13.Перевірте, чи змінилися в зведеній таблиці дані про кількість облігацій, закуплених у січні.

Якщо дані списку були змінені, але рядки і стовпці не додавалися,

можна обновити зведену таблицю інакше. Для цього треба:

•натиснути будь-яку ячейку зведеної таблиці;

•вибрати команду Данные/Обновить або натиснути кнопку Обновить данные на панелі інструментів Сводная таблица.

14.На листі Список знайдіть за допомогою форми дані зведення про закупівлю Векселів у червні.

15.Зміните значення полючи Закупівля (шт.), нехай воно буде дорівнює 0.

16.Обновіть зведену таблицю. Перевірте, чи змінилися в зведеній таблиці дані про кількість векселів, закуплених у червні.

Щоб видалити зведену таблицю, потрібно виділити її (разом з назвами полів) і дати команду Исправления/Очистить/Все.

71

Зміна структури зведеної таблиці

Структуру зведеної таблиці можна трансформувати за допомогою макета, додаючи або видаляючи поля на ньому, а також змінюючи розташування полів в областях таблиці. Щоб вставити або перемістити поле зведеної таблиці, треба:

•натиснути ячейку зведеної таблиці;

•вибрати позицію Мастер... зі списку Сводная таблица на панелі інструментів Сводные таблицы;

•у діалоговому вікні Мастер сводныхтаблиц – шаг 3 з 3 натиснути кнопку

Макет;

•у діалоговому вікні Мастер сводных таблиц и диаграмм – шаг 3 з 3

перетягнути назви потрібних полів у відповідні області, натиснути ОК;

•натиснути Готово у вікні Мастер сводных таблиц.

17.Використовуючи макет, змініть структуру таблиці: розмістіть дані поля

Місяць в області Строка, дані поля Найменування цінного папера – в

області Столбец. Додайте в область Столбец поле Емітент. Зверніть увагу на зміни в таблиці.

Щоб перемістити поле зведеної таблиці, не звертаючись до макета, треба перетягнути назву полів в необхідну область. При цьому покажчик миші прийме вид стрілки з «» макетом зведеної таблиці, що приклеївся. Стежіть за зміною індикації цього макета, щоб проконтролювати, у яку саме область поміститься поле, що перетягується.

18.Поміняйте місцями поля Найменування цінного папера й Емітент в

області Столбец.

19.Помістіть поле Емітент в області Строка.

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

20. Видаліть поле Емітент.

72

Підведення підсумків у зведеній таблиці за допомогою обчислювальних функцій

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

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

•натиснути будь-яку ячейку даних зведеної таблиці;

•вибрати позицію Мастер... зі списку Сводная таблица на панелі інструментів Сводная таблица;

•у діалоговому вікні Мастер сводных таблиц и диаграмм – шаг 3 з 3

натиснути кнопку Макет ;

73

•двічі натиснути назва полів в області Данные;

•у діалоговому вікні Вычисление поля сводной таблицы вибрати в групі Операция потрібну підсумкову функцію;

•натиснути кнопку Формат для форматування даних зведеної таблиці;

•натиснути кнопку Дополнительно для одержання додаткових результатів, наприклад, частки від загальної суми і т.п.;

•якщо потрібно, внести зміни в поле Имя і натиснути ОК у вікні Вычисление поля сводной таблицы;

•натиснути OK у діалоговому вікні Мастер сводных таблиц и диаграмм - Макет;

•Натиснути кнопку Готово.

21.За допомогою макета змініть підсумкові обчислення в зведеній таблиці – використовуйте функцію Количество.

Щоб використовувати дві або більш підсумкові функції, треба:

• вибрати позицію Мастер... зі списку Сводная таблиця	на панелі
інструментів Сводная таблиця;

•у діалоговому вікні Мастер сводных таблиц и диаграмм – шаг 3 з 3

натиснути кнопку Макет;

•на макеті, що з'явився, перетягнути назву полів, за даними яких, будуть проводитися обчислення, в область Данные;

•змінити для цього поля підсумкову функцію Сумма на необхідну;

•натиснути Готово).

22.Додайте в зведену таблицю сумарний розрахунок Валового обсягу закупівель (функція Сумма) і відформатируйте результат форматом грошовий з числом десяткових знаків, рівним 0, і грошовою одиницею - грн.

23.Скасуєте обчислення кількості значень по полю Валовий обсяг закупівель.

74

Приховання елементів полів зведеної таблиці

Щоб сховати один або кілька елементів полів зведеної таблиці, треба натиснути в зведеній таблиці по кнопці списку, що розкривається, у назві поля, елементи якого необхідно сховати, у списку, що відкрився, зняти прапорець біля того елемента, що не потрібно відображати, натиснути ОК.

24. Сховайте в поле Найменування цінного папера елементи Облігація, Казначейське зобов'язання, Ф'ючерс і Форвард.

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

25.Знову виведіть на екран елемент Облігація.

26.Перейдіть до листа Виробництво.

27.Створіть зведену таблицю, помістивши її на новому листі:

f в область Страница помістите назву поля Рік,

fв область Строка – Цех,

fв область Страница – Квартал,

fв область Данные – Загальна сума.

75

28.Назвіть створений аркуш Вправа.

29.Подивіться, скільки деталей було випущено за 1998 рік.

30.Подивится кількість випущених деталей за перше півріччя (1 і 2 вартал) 1998 роки. Снову відобразіть дані за всі квартали.

31.Змініть структуру зведеної таблиці:

fв область Страница помістите назву поля Деталь,

fв область Страница додайте Рік, помістивши його ліве поля Квартал.

32.Подивіться кількість випущених деталей Гвинт М10 за 1 і 2 квартали 1997 і 1998 років.

33.Переключіться на аркуш Виробництво, використовуючи форму даних:

fзнайдіть запис про випуск деталей за наступними критеріями: Рік – 1998, Цех – №2, Квартал – 1, Деталь – Гвинт М10 і замініть дані в поле Якісні

на 605000;

fзнайдіть інший запис: Рік – 1997, Цех – №1, Квартал – 1, Деталь – Гвинт М10 і заміните дані в поле Якісні на 510000.

34.Обновіть зведену таблицю на листі Вправа, і подивіться, чи відображені в ній внесені Вами зміни.

35.Задайте обчислення Максимального (Мах) значення поля Загальна кількість.

36.Проаналізуйте результати обчислень.

37.Поверніть обчислення Сумы по полю Загальне сума.

38.Видаліть зі зведеної таблиці поле Квартал.

39.Збережіть зроблені зміни.

76

Угруповання і разгруппировка даних у зведеній таблиці

При роботі зі зведеною таблицею іноді потрібно об'єднати дані таблиці в групу, тобто згрупувати них. Можна групувати дати, числа і значення, розташовані в області Столбец або Строка. Щоб згрупувати дані, треба:

•виділити елементи, що групуються;

•дати команду Данные/Группа и структура/Группировать – з'явиться новий елемент, що поєднує виділені;

•двічі натиснути назву нового поля;

•у діалоговому вікні Вычисление полей сводной таблицы у поле Имя увести відповідну назву поля;

•натиснути ОК;

•дати нову назву групі елементів, якщо необхідно.

77

40.Перейдіть до листка Зведена таблиця.

41.Зробіть груповання місяців по кварталах: січень, лютий, березень – Група1; квітень, травень, червень – Група2; липень, серпень, вересень – Група3; жовтень, листопад, грудень – Група4.

42.Дайте новому полю Місяць2 назву Квартал.

43.Дайте назви групам: Квартал1, Квартал2, Квартал3, Квартал4.

Щоб розгрупувати дані, треба:

•натиснути назва групи;

•натиснути кнопку Разгруппировать на панелі інструментів Сводные

таблицы.

44. Розгрупуйте дані групи Квартал1.

Приховання і відображення детальних даних у зведеній таблиці

Microsoft Excel дозволяє відображати детальні дані в зведеній таблиці і знову сховати їх. Наприклад, можна відобразити дані за II квартал, сховавши дані за квітень, травень, червень.

Щоб сховати детальні дані, треба:

•натиснути назва поля або групи, детальні дані яких Ви хочете сховати;

•натиснути кнопку Спрятать детали на панелі інструментів Сводные

таблицы.

45. Сховайте детальні дані за обсягом закупівель у 3 і 4 кварталах. Щоб відобразити на екрані деталі в поле або в групі даних, треба:

• натиснути назва групи або поля;

78

• натиснути кнопку Показать детали на панелі інструментів Сводные таблицы.

46. Знову відобразіть детальні дані за обсягом закупівель у Кварталі3.

Представлення даних зведеної таблиці у виді діаграми

Дані зведеної таблиці можна представити у вигляді діаграми. Ця процедура принципово нічим не відрізняється від складання діаграми до звичайної таблиці Microsoft Excel.

Щоб скласти діаграму до зведеної таблиці, треба:

•виділити одну з комірок зведеної таблиці;

•дати команду Вставка/Диаграмма або натиснути відповідну кнопку на панелі інструментів Сводные таблицы. Діаграма буде побудована за наявними у зведеній таблиці даним і розміщена на окремий аркуш.

47.Перейдіть до листка Вправа.

48.Перевірте структуру зведеної таблиці. Вона повинна виглядати так: в області Страница – назва поля Деталь, в області Строка – Рік, в області

Столбец – Цех, в області Данные) – сума по полю Загальний підсумок.

49.Створіть діаграму.

50.Змініть діаграму, вибравши тип гистограма, вид 1.

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

51. Змініть структуру зведеної діаграми: поміняйте місцями назви полів Цех і

79