Лабораторна робота № 3.5. З допомогою функції Линейн, у клітинці на кілька строчок нижче, визначаємо тісноту зв’язку отриманої моделі, що враховує мультиколінеарність. 6. Виділити кілька клітинок на ширину нової таблиці та на 3–5 клітинок униз. У рядку формул вставити курсор у кінці формули та з допомогою одночасного натискання кнопок Ctrl + Shift + Enter отримати значення показників оцінки тісноти зв’язку. 7. Зробити загальні висновки. На основі отриманих коефіцієнтів побудувати умовну економіко-математичну модель. Лабораторна робота № 3.
Електронні таблиці Excel. Аналіз даних. Обчислення підсумків. Консолідація даних. Зведена таблиця.
Мета роботи: познайомиться з функціями, що дозволяють здійснювати аналіз даних у таблиці.
Хід роботи 1. Підготовка до роботи. 1.1. Ввімкніть живлення комп'ютера і дочекайтеся завантаження операційної системи. 1.2. Завантажите Microsoft Excel. 1.3. Відкрийте файл, що містить готовий список (базу даних), використовуючи команди Файл / Открыть Файл знаходиться в тій же папці, що і лабораторна робота №3, його ім'я - Список.xls.
2. Обчислення підсумків. Excel може автоматично розраховувати підсумкові значення при кожній зміні значень у стовпці. Припустимо, потрібно оцінити Суму оплати кожної фірми за зазначений період часу. 2.1. Відсортуйте дані в таблиці за Назвою фірми. Як другий критерій сортування можна задати № рахунку. 2.2. Активізуйте один із чарунків списку. 2.3. Виконайте команди Данные / Итоги. На екрані з'явиться діалогове вікно Промежуточные итоги 2.4. У поле списку При каждом изменении в виберіть заголовок стовпця Назва фірми, для якого необхідно обчислити проміжні підсумки після кожної зміни даних на робочому листі. 2.5. У поле списку Операция установіть функцію Сум., для обчислення підсумкової суми. 2.6. В області Сложить итоги по активізуйте опцію Сума оплати. 2.7. Клацніть на кнопці ОК. У результаті таблиця буде доповнена рядками, що містять підсумкові значення для кожної фірми. В останній з вставлених у таблицю рядків включається інформація про загальний підсумок для усіх фірм. Зверніть увагу, що нижче того поля, де вказується адреса поточного чарунку, з'являться числа 1,2,3. Вони відповідають різним рівням структуризації. Рівень 1 відповідає загальному підсумкові по всіх рядках. Рівень 2 відповідає підсумкам по кожній окремій фірмі. Рівень 3 відповідає всьому спискові. 2.8. Клацніть по цифрі 2. Excel сховає всі рядки з даними, залишивши тільки підсумки по кожній з фірм, а також загальний підсумок. 2.9. Клацніть на знаку плюс ліворуч від рядка 28, ЗАТ Краса. Excel покаже всі дані тільки по даній фірмі. 2.10. Клацніть на знаку мінус, щоб сховати деталі. 2.11. Клацніть на цифрі 3, що позначає найнижчий рівень структуризації таблиці. Excel відобразить усі рядки. Припустимо, що ви хочете побачити суму оплати для кожної фірми по датах рахунків. 2.12. Установіть покажчик чарунку в будь-який чарунок таблиці. 2.13. Виконайте команди Данные / Итоги. На екрані з'явиться діалогове вікно Промежуточные итоги 2.14. У поле списку При каждом изменении в виберіть заголовок стовпця Дата рахунка. 2.15. Скиньте прапорець Заменить поточные итоги. 2.16. Клацніть на кнопці ОК. Excel буде перераховувати значення в підсумкових рядках щоразу при зміні дати рахунка. При додаванні другого рівня підсумків, Excel введе ще одну цифру в рівень структуризації. 2.17. Проаналізуйте отримані результати. Excel має можливість комбінувати кілька підсумків (застосування вкладених підсумків). 2.18. Відкрийте повторно діалогове вікно Промежуточные итоги, виконавши команди Данные / Итоги. 2.19. У поле При каждом изменении в виберіть елемент Назва фірми. 2.20. Щоб визначити, по скількох рахунках вироблялася оплата, установіть у поле Операция функцію Кількість значень. 2.21. В області Сложить итоги активізуйте опцію № рахунка. 2.22. Виключіть опцію Заменить поточные итоги, якщо вона включена, для того, щоб у таблиці відображалися всі підсумки. 2.23. Натиснути кнопку ОК. Проаналізуйте отримані результати. 2.24. Для видалення рядків із підсумковими значеннями клацніть на кнопці Убрать все, розташовану в діалоговому вікні Промежуточные итоги 2.25. Закрийте файл список.xls, не зберігаючи зміни у файлі.
3. Консолідація даних. Консолідація виконується в тому випадку, якщо необхідно підсумувати дані, розташовані в різних областях таблиці. За допомогою функції консолідації значень із несуміжних областей можна виконати ті ж операції, що і при автоматичному визначенні проміжних підсумків. Підлягаючи консолідації, діапазони чарунків можуть знаходитися на одному робочому листі, на різних аркушах і в різних книгах. 3.1. Відкрийте файл з ім'ям Приклад до лабораторної роботи № 3. Він знаходиться в тій же папці, що і текст лабораторної роботи №3. 3.2. Перегляньте вміст 1-го, 2-го і третього робочих аркушів таблиці. 3.3. Відсортуйте дані в кожній з таблиць за назвою фірми. 3.4. Перейдіть на робочий лист № 4 і в першому рядку цього листа введіть заголовок «Підсумки роботи за I квартал 2009 року». 3.5. Активізуйте чарунок A2. 3.6. Виконайте команди Данные / Консолидация. 3.7. У поле Функция діалогового вікна, що з'явилося, установити Сумма. 3.8. У поле Ссылка по черзі введіть адреси всіх діапазонів, що консолідуються. Адреси можна ввести з клавіатури, однак набагато зручніше вказати їх у полі Ссылка шляхом виділення діапазонів. 3.8.1. Перейдіть на 1-й робочий лист із консолідуючими даними і виділіть перший діапазон. Під час виділення чарунків вікно Консолидация звертається до розміру поля введення, звільняючи робочу область листа. У діапазон чарунків варто включати і заголовок рядків, інакше можна швидко втратити орієнтацію в таблиці. 3.8.2. Клацніть на кнопці Добавить, внаслідок чого посилання на зазначений діапазон з'явиться в полі Список диапазонов. 3.8.3. Перейдіть на 2-й робочий лист із даними, що консолідуються, і виділіть другий діапазон. 3.8.4. Клацніть на кнопці Добавить. 3.8.5 Перейдіть на 3-й робочий лист із даними, що консолідуються, і виділіть третій діапазон. 3.8.6. Клацніть на кнопці Добавить Пам’ятайте! Якщо діапазони, що консолідуються, розташовані на різних аркушах книги однаково, то після введення адреси першого діапазону виділяти інші діапазони не потрібно, досить перейти на новий лист і натиснути кнопку Добавить . 3.9. Установіть опцію Значение левого столбца в області Использовать как имена. У такий спосіб задається консолідація по іменах. При цьому значення в рядках з однаковими мітками будуть просумовані. 3.10. У цій же області установіть прапорці Подписи верхней строки і Создать связи с исходными данными (активізація останньої опції дозволяє встановити динамічний зв'язок між вихідними даними й результатами консолідації, що дозволяє автоматично обновляти дані). 3.11. Натиснути кнопку ОК. 3.12. Активізуйте робочий лист, у якому повинні знаходитися результати консолідації. Дана таблиця правильно відбиває підсумки по кожній фірмі по стовпцях: Сума на рахунку, Сума оплати і Борг. 3.13. Проаналізуйте отриману таблицю. Вона має ряд недоліків: 3.13.1. Видаліть колонки № рахунка, тому що номери рахунків також просумувалися. 3.13.2. Розкрийте другий рівень структури (для цього натисніть на кнопці 2 ліворуч угорі). 3.13.3. Проаналізуйте стовпець В (інформація, що знаходиться в ньому може бути вилучена). 3.13.4. Видаліть стовпець В з таблиці. Проаналізувавши отриману таблицю, можна зробити висновок, що консолідація даних краща для швидкого одержання чорнових таблиць із метою перегляду й аналізу загальної картини. 3.14. Відформатуйте отриману підсумкову таблицю. 3.15. Збережіть робочу книгу на дискеті.
4. Зведена таблиця. Зведеними називаються допоміжні таблиці, що містять частину даних аналізованої таблиці, відібраних так, щоб залежності між ними відображалися найкраще. Зведені таблиці є могутнім інструментом для динамічного підведення підсумків і перегляду списку.
4.1. Створення зведеної таблиці. Здійснюється на основі області таблиці, цілої таблиці або декількох таблиць. 4.1.1. Відкрийте файл з ім'ям Робота з клієнтами.xls. Він знаходиться в тій же папці, що і лабораторна робота № 3. 4.1.2. Виконайте команди Данные / Сводная таблиця. На екрані з'явиться вікно першого кроку Мастер Сводных таблиц. 4.1.3. В області Создать таблицу на основании данных, которые находятся: вибрати перемикач в списке или базе данных Microsoft Excel (він звичайно активізований за замовчуванням). 4.1.3. Натисніть кнопку Далее. На екрані з'явиться діалогове вікно другого кроку майстра зведених таблиць. 4.1.4. У поле Диапазон: уведіть діапазон чарунків, дані з якого будуть включені у зведену таблицю. Пам’ятайте! Якщо перед запуском майстра покажчик чарунку знаходився усередині таблиці, то програма автоматично вставить у поле Діапазон адресу всієї таблиці. 4.1.5. Натисніть кнопку Далее. На екрані з'явиться діалогове вікно третього кроку майстра зведених таблиць. Тут варто вказати, які дані і як виводити. Excel автоматично виводить у правій частині діалогового вікна поля з іменами стовпців діапазону даних. 4.1.6. Перетягніть мишкою поле Спеціалізація в область Сторінка. 4.1.7. Перетягніть мишкою поле Назва й Дата оплати в область Рядок (при цьому полі Назва повинна бути зверху – так визначається порядок угруповання: спочатку по назвах фірм, а потім по датах оплати). 4.1.8. Перетягніть мишкою поле Дата рахунка – в область Столбец. 4.1.9. Перетягніть мишкою поле Сума на рахунку в область Данные. В області Данные з'явиться поле з назвою Сумма по полю Сума на рахунку. 4.1.20. Двічі клацніть на поле Сумма по полю Сума на рахунку. На екрані з'явиться діалогове вікно Вычисление полей сводной таблицы. 4.1.21. У поле Имя введіть нове ім'я для поля – Сума по рахунках. 4.1.22. У поле Операция можна змінити групову операцію (за замовчуванням - сумма), виконувану над полями даних. Зупиніться на операції сумма. 4.1.23. Натисніть кнопку ОК. 4.1.24. Перетягніть мишкою поле Сума оплати в область Данные. В області Данные з'явиться поле з назвою Сумма по полю Сума оплати. 4.1.25. Двічі клацніть на поле Сумма по полю Сума оплати. На екрані з'явиться діалогове вікно Вычисление полей сводной таблицы. 4.1.26. У поле Имя введіть нове ім'я для поля – Сума оплат. 4.1.27. У поле Операция виберітьоперацію сумма. 4.1.28. Натисніть кнопку ОК. 4.1.29. Натисніть кнопку Далее. На екрані з'явиться діалогове вікно четвертого кроку майстра зведених таблиць. 4.1.30. У поле Разместить таблицу в установити прапорець новый лист. 4.1.31. Натиснути кнопку Параметры. На екрані з'явиться діалогове вікно Параметры сводной таблицы. 4.1.32. У поле Имя введіть назву таблиці - Зведена таблиця. 4.1.33. Установіть прапорець Для пустых ячеек отображать, а текстове поле праворуч уведіть цифру 0. 4.1.34. Установіть прапорець общая сума по столбцам, якщо він не встановлений. 4.1.35. Скиньте прапорець общая сума по строкам. 4.1.36. Скиньте прапорець сохранить данные вместе с таблицей (для оптимального використання пам'яті). 4.1.37. Установити прапорець обновить при открытии. 4.1.38. Натиснути кнопку ОК. 4.1.39. Натиснути кнопку Готово. На екрані з'явиться зведена таблиця, яку необхідно трохи підкоректувати.
4.2. Редагування зведеної таблиці. Додамо в таблицю поле Заборгованість,щообчислюється, воно являє собою різницю між сумою на рахунку й сумою оплати. 4.2.1. Відкрийте панель Сводная таблица, використовуючи команди Вид / Панель инструментов / Сводная таблица (ця панель використовується при редагуванні даних). 4.2.2. Виконайте команди Сводная таблица / Формулы / Вычисляемое поле панелі інструментів Сводной таблицы. На екрані з'явиться діалогове вікно Вставка поля,щообчислюється. 4.2.3. У списку Поля виберіть елемент Сума на рахунку. 4.2.4. Натиснути кнопку Добавить поле. 4.2.5. Уведіть знак мінус у поле Формула. 4.2.6. Додайте аналогічним образом у формулу поле Сума оплати. 4.2.7. У поле Имя введіть назву поля, що додається, - Борг. 4.2.8. Натисніть кнопку ОК. 4.2.9. Установіть курсор у будь-який чарунок, що відповідає полю Сумма по полю Борг. 4.2.10. Виконайте команди Сводная таблица /Поле панелі інструментів Сводная таблица для зміни імені поля Сумма по полю Борг на Заборгованість (при виконанні команд активним повинен бути чарунок відповідний рядкові поля Сума по полю Борг). 4.2.11. У поле Имя введіть нове ім'я поля - Заборгованість. 4.2.12. Натисніть кнопку ОК. У результаті у зведеній таблиці в області даних будуть знаходитися три поля: Сума по рахунках, Сума оплат і Заборгованість.
4.3. Групування по тимчасових діапазонах. Згрупуємо всі дані по датах. 4.3.1. Виділіть поле Дата рахунка. 4.3.2. Натисніть кнопку Þ (Группировать)панелі інструментів Сводная таблица. На екрані з'явиться діалогове вікно Группировать 4.3.3. Скиньте прапорець начиная с (він визначає автоматичний вибір початку діапазону угруповання). 4.3.4. У розділі с шагом виберіть крок Дни. 4.3.5. Заберіть виділення з кроку Месяцы. 4.3.6. У поле Количество дней укажіть 7. 4.3.7. Натисніть кнопку ОК. 4.3.8. Виконайте команди Сводная таблица / Поле панелі інструментів Сводная таблица. На екрані з'явиться діалогове вікно Вичисление полей сводной таблицы 4.3.9. Установіть прапорець Отображать пустые элементы У результаті у зведеній таблиці дані будуть згруповані по тижнях по даті оплати, причому в таблиці будуть присутні стовпці і для тижнів, під час яких операції не вироблялися, але тільки до тижня, під час якої була зроблена остання операція (включно). 4.2.10. Натисніть кнопку ОК. 4.3.11. Для розгрупування даних натисніть кнопку Ü (Разгруппировать) панелі інструментів Сводная таблица. 4.3.12. Самостійно зробіть угруповання таблиці по місяцях.
4.4. Зміна структури зведеної таблиці. Надамо отриманій зведеній таблиці інший вид. Перетворимо таблицю у звіт за спеціалізаціями і розмістимо поля даних по сумах рахунків, оплат і заборгованостей не в стовпець, а в рядок. Для цього: 4.4.1. Викличте Мастер Сводных таблиц щигликом миші на його піктограмі на панелі інструментів Сводная таблица. 4.4.2. Перетягніть поле Спеціалізація в область Строк у крайню верхню позицію. 4.4.3. Перетягніть поле Дата рахунку в область Страниц. 4.4.4. Перетягніть поле Дата оплати в область Столбцов. 4.4.5. Натисніть кнопку Готово. На екрані з'явиться зведена таблиця, що має іншу структуру. Проаналізуйте отриманий результат. 4.4.6. Перетягніть поле Данные у зведеній таблиці вправо й опустіть відразу ж під полем Дата оплати. Структура зведеної таблиці трохи змінитися. Проаналізуйте отриманий результат.
4.5. Керування загальними й проміжними підсумками. Керування проміжними підсумками здійснюється за допомогою діалогового вікна Вычисление поля сводной таблицы. Щоб сховати проміжні підсумки необхідно: 4.5.1. Виділіть поле Спеціалізація. 4.5.2. Виконайте команди Сводная таблица / Поле панелі інструментів Сводные таблицы. На екрані з'явиться діалогове вікно Вичисление поля сводной таблицы 4.5.3. Установіть перемикач нет в групі Промежуточные итоги. 4.5.4. Натисніть на кнопку ОК. Проаналізуйте зведену таблицю. У ній зникнуть проміжні підсумки. Щоб сховати загальні підсумки у зведеній таблиці: 4.5.5. Клацніть по будь-якому чарунку зведеної таблиці правою кнопкою миші, тобто викличте контекстне меню. 4.5.6 Виконайте команду Параметры контекстного меню. 4.5.7. Скиньте прапорець Общая сума по столбцах. 4.5.8. Натисніть кнопку ОК. У Зведеній таблиці тепер не буде утримуватися підсумкових значень. 4.5.9. Відобразіть проміжні й загальні підсумки у зведеній таблиці самостійно, установивши перемикач Автоматически в групі Промежуточные итоги діалогового вікна Вичисление полей сводной таблицы. 4.5.10. Відобразіть загальні підсумки у зведеній таблиці самостійно, установивши прапорець Общая сума по столбцам, контекстного меню Параметры.
4.6. Форматування зведеної таблиці. Форматування зведеної таблиці можна проводити звичайним способом, установлюючи необхідні параметри в діалоговому вікні Формат ячеек. Але при реорганізації зведеної таблиці деякі настроювання можуть пропасти. Тому рекомендується застосовувати Автоформат. 4.6.1. Установіть курсор на будь-який чарунок таблиці. 4.6.2. Виконайте команди Формат / Автоформат. 4.6.3. У списку форматів діалогового вікна Автоформат виберіть классический2. 4.6.4. Клацніть на кнопці ОК.
4.7. Форматування полів зведеної таблиці. 4.7.1. Виділіть будь-як чарунок із поля Сума по рахунках. 4.7.2. Натисніть кнопку Поле сводной таблицы на панелі інструментів Сводная таблица. З'явиться діалогове вікно Вычисление поля сводной таблицы. 4.7.2. Натисніть кнопку Формат. З'явиться діалогове вікно Формат ячеек. 4.7.3. Виберіть формат Денежный у списку Числовые форматы. 4.7.4. Установіть 2 у поле Число десятичных знаков. 4.7.5. У списку Значения виберіть позначення грн. Український. 4.7.6. Натисніть кнопку ОК. 4.7.7. Натисніть кнопку ОК. 4.7.8. Установіть такий самий формат для всіх полів зведеної таблиці.
|
