Практична робота №6. Аналіз даних в таблицях Excel

Аналіз даних в таблицях Excel. Підготовка
електронних таблиць до друку

Мета роботи:

- ознайомитись з процесом керування даними;

- навчитись використовувати консолідацію даних, зведені таблиці та метод „Подбор параметра”;

- освоїти перевірку орфографії та підготовку робочих листів до друку.

Консолідація даних

Консолідація – це об’єднання даних з різних діапазонів.

1. Додайте Лист 8 з назвою Консолідація.

2. В клітинках A1:D13 розмістіть таблицю з початковими даними для консолідації (рис. 7 ). Зверніть увагу: для правильного проведення консолідації необхідно дотримуватись форматування таблиці (зображення, шрифти, межі).

Рис. 7. Дані для консолідації

3. За допомогою клавіші Ctrl виділіть діапазони клітинок: B3:D5, B7:D9, B11:D13. В пункті меню Формат виберіть команду Ячейки. На вкладці Число виберіть формат Денежный, позначення – грн. Натисніть ОК.

4. Залийте рядки 1, 6, 10 блідо-зеленим кольором. Об'єднайте клітинки A15:D15. Залийте жовтим кольором. Введіть текст: Результати консолідації. Мінімальне значення. Змініть зображення на полужирний.

5. Клацніть клітинку А16. В пункті меню Данные виберіть Консолидация. В діалоговому вікні Консолидация, що з'явилося, в полі Функция виберіть Минимум. Клацніть в полі Ссылка. Виділіть діапазон клітинок A3:D5. Натисніть кнопку Добавить.

6. Поверніться до поля Ссылка, очистіть його, виділіть діапазон клітинок A7:D9. Натисніть кнопку Добавить. Аналогічно додайте значення по постачальнику, що залишився – діапазон A11:D13.

7. В полі Использовать в качестве имени поставте прапорець Значения левого столбца. Натисніть кнопку ОК.

8. Виділіть отриману таблицю (клітинки A16:D18). В пункті меню Формат виберіть команду Ячейки. На вкладці Граница виберіть тип ліній – жирна, виберіть лінії – зовнішні, колір – червоний. Натисніть кнопку ОК.

9. Клацніть клітинку А20. Аналогічно отримайте таблицю: Результати консолідації. Середнє значення. Для цього в діалоговому вікні Консолідация змініть тільки поле Функция. В ньому виберіть Среднее. Отриману таблицю відформатуйте так само, як і попередню.

Метод „Подбор параметра”

Метод «подбор параметра» використовується у тому випадку, коли потрібно отримати певне значення формули. Для того, щоб вирішити цю задачу, вказують одну з клітинок, що впливає на значення формули. Excel підбирає для вмісту клітинки таке значення, при якому у формулі буде отриманий необхідний результат.

Завдання 1

Додайте лист 9 з назвою Подбор параметра. В клітинку А1 введіть назву завдання:

Вивчення можливостей методу ” Подбор параметра ”.

Знайдіть значення невідомого для формули Х + 3 = 5 за допомогою Сервис ® Подбор параметра. Для цього встановіть в клітинці В5 значення 5, змінюючи клітинку В3 (рис.8). Обчислений результат з’явиться в клітинці В3.

Рис. 8. Завдання 1

Завдання 2

Знайдіть корінь рівняння Х² + 2Х – 5 = 0. Встановіть точність знаходження коренів. За методом Подбор параметра корінь знаходиться засобом послідовних наближень. Для цього за допомогою команди Сервис ® Параметры, на вкладці Вычисления задайте относительную погрешность (точність обчислень) рівною 0,001 та число итераций (кроків підбору) рівним 100. Після отримання результату змініть относительную погрешность та число итераций на 0,0001 і 1000 відповідно. Порівняйте результати (рис.9).

Рис. 9. Завдання 2

Завдання 3

Знайдіть об’єм продажу керамічної плитки в жовтні, якщо відомо, що в серпні було продано 43 тис. штук, у вересні об’єм продажу становив 75% від продажу в серпні, а за три місяці було продано 104 тис. штук керамічної плитки.

Завдання 4

Складіть задачу з використанням методу „Подбор параметра”.

Зведені таблиці

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

1. Додайте лист 11 з назвою Зведена таблиця. Створіть початкову таблицю для створення зведеної таблиці (рис. 10)

2. Необхідно створити зведену таблицю з підведенням підсумків з продажу товарів магазинами за видами продукції і видами обсягів продажу. Встановіть курсор в клітинку А18 з даними початкової таблиці. Виберіть пункт меню Данные ® Сводная таблица. В групі Создать таблицу на основе данных, находящихся встановіть перемикач в Списке или базе данных Microsoft Excel. В групі Вид створюваного звіту встановіть перемикач Сводная таблица і натисніть кнопку Далее.

3. Перевірте, що у вікні, що з'явилося, в опції Диапазон вказаний весь діапазон елементів початкової таблиці A1:D11, і натисніть Далее (при виділенні діапазону спочатку виділіть перший стовпчик, а потім протягніть вправо).

4. В опції Поместить таблицу в встановіть перемикач в новый лист і натисніть кнопку Макет.

Рис. 10. Звіт для зведеної таблиці

5. За допомогою миші перетягніть поле «Найменування магазину» в область Страница, поле «Вид продукції» – в область Строка, поле «Щоденний обсяг продажу, тис. грн.» – в область Столбец, поле «Місячний обсяг продажу, тис. грн» – в область Данные і натисніть ОК. Натисніть кнопку Готово.

Підготовка електронних таблиць до друку

1. Підготуйте до друку таблиці з практичної роботи №5. Встановіть масштаб 100%.

2. Встановіть параметри сторінки при розташуванні на одному листі: книжна орієнтація; горизонтальне і вертикальне центрування, задайте поля: верхнє – 2,5, нижнє – 2,5, ліве – 2, праве – 2, поля верхнього та нижнього колонтитулів – 1,3.

3. Виберіть розмір аркуша А4.

4. Створіть для даної таблиці колонтитули: у верхньому колонтитулі введіть прізвище та ім’я студента, номер групи, дату виконання роботи, в нижньому колонтитулі – номер сторінки.

5. Перевірте орфографію за допомогою команди Автозамена або Сервис → Орфография.

6. Прогляньте таблицю в режимі «Предварительный просмотр».

7. Встановіть такі параметри друку:

– встановіть сітку;

– виберіть чорно-білий друк документа;

– виведіть на друк заголовки рядків та стовпців;

– виберіть режим виводу на друк – вниз, потім вправо.

Підготовка до друку діаграм та графіків

1. Підготуйте до друку діаграми та графіки з практичної роботи №4.

2. Встановіть масштаб 25%, для того, щоб всі графіки та діаграми розмістились на одному аркуші, задайте поля: верхнє – 2, нижнє – 2, ліве – 2, праве – 2, поля верхнього та нижнього колонтитулів -1,3.

3. Орієнтація – альбомна, горизонтальне і вертикальне центрування.

4. Виберіть розмір аркуша А4.

5. Створіть для цього аркуша колонтитули: в нижньому колонтитулі введіть прізвище та ім’я студента, номер групи, дату виконання роботи, у верхньому колонтитулі – „Діаграми та графіки”.

6. Перегляньте практичну роботу в режимі «Предварительный просмотр».

7. Встановіть такі параметри друку:

· виведіть на друк заголовки рядків та стовпців;

· виберіть режим виводу на друк – вниз, потім вправо;

· виберіть чорновий друк документу;

· встановіть якість друку – 300 точок на дюйм.

Список літератури

1. Інформатика. Комп'ютерна техніка /під ред. Баженова В.А. – К.: Каравела, 2003. – 464 с.

2. Баженов В.А. Інформатика. Комп'ютерна техніка. Комп'ютерні технології: підручник / В.А. Баженов, П.С. Венгерський, В.М. Горлач та ін. – К.: Каравела, 2004. – 464 с.

3. Информатика. Базовый курс / под ред. С.В. Симоновича. – СПб., 1998.

4. Джим Бойс. Использование Мiсгоsoft Оffсе 97. Профессиональный выпуск. – К.; М.; СПб., 1998. – 1119 с.

5. Інформатика: Комп'ютерна техніка. Комп'ютерні технології /за ред. О.І. Пушкаря. – К.: Видавничий центр «Академія», 2001. – 696 с.

6. Інформатика: Комп'ютерна техніка Комп'ютерні технології: підручник для студентів вищих навч. закладів / за ред. О.І. Пушкаря. – К.: Видавничий центр «Академія», 2003. – 704 с.

7. Глушаков С.В. MICROSOFT EXCEL XP: навчальний посібник /
С.В. Глушаков., О.С. Сурядний. – Харків: Фоліо, 2006. – 508 с.

8. Зелінський С.Е. MICROSOFT OFFICE 2007: навчальний посібник / С.Е. Зелінський. – Харків: Фоліо, 2008. – 506 с.

Навчально-методичне видання

Електронні таблиці MS EXCEL

Методичні вказівки
до виконання практичних робіт для студентів,
які навчаються за напрямами 6.060101 «Будівництво»,
6.030601 «Менеджмент», 6.030510 «Товарознавство і торговельне підприємництво», 6.080101 «Геодезія, картографія та землеустрій», 6.060103 «Гідротехніка (водні ресурси)»

Укладачі: Яковенко Наталія Миколаївна

Тихонова Ольга Олексіївна

Комп’ютерне верстання Т.І. Кукарєвої

Підписано до друку 2009. Формат 60 ´ 84 _{1/ 16}

Ум. друк. арк. 1,63. Обл.-вид. арк. 1,75.

Тираж 100 прим. Вид. № 88/ІІІ-09. Зам. №

КНУБА, Повітрофлотський проспект, 31, Київ, Україна, 03680

E-mail: red-isdat@knuba.edu.ua

Віддруковано в редакційно-видавничому відділі

Київського національного університету будівництва і архітектури

Свідоцтво про внесення до Державного реєстру суб¢єктів

Видавничої справи ДК № 808 від 13.02.2002 р.

Не нашли, что искали? Воспользуйтесь поиском: