Тема: Сортування даних. Підведення підсумків. Зведені таблиці

Скачати 95.41 Kb.

Назва	Тема: Сортування даних. Підведення підсумків. Зведені таблиці
Дата	25.03.2013
Розмір	95.41 Kb.
Тип	Документи

bibl.com.ua > Інформатика > Документи

Лабораторна робота 5
Тема: Сортування даних. Підведення підсумків. Зведені таблиці
Мета роботи: навчитися виконувати сортування даних, їх фільтрацію, створювати проміжні підсумки, зведені таблиці.
Порядок виконання роботи

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

Обладнання і програмне забезпечення

Персональний комп’ютер.
Операційна система Windows XP,
Електронні таблиці Microsoft Excel 2003.
Роздаткові матеріали.

Література

Берлинер Э.М., Глазырина И.Б., Глазырин Б.Э. Microsoft Office 2003. – М.: ООО «Бином-Пресс», 2004. – 576 с.
Блаттнер П. Использование Microsoft Office Excel 2003. – М.: Вільямс, 2004. – 864 с.
Глинський Я.М. Практикум з інформатики: Навчальний посібник. – Львів: Деол, 1998. – 167 с.
Глушаков С.В., Мачула О.В., Сурядный А.С. Редактор электронных таблиц Microsoft Excel XP / Худож.-оформитель А.С. Ютман. – Харьков: Фолио, 2003. – 95 с.
Гончаров А. Excel 7.0 в примерах. – СПб.: Питер, 1996. – 256 с.
Гусева О.Л., Миронова Н.Н. Практикум по Excel. – М.: Финансы и статистика, 1997. – 160 с.
Дибкова Л.М. Інформатика та комп’ютерна техніка: Посібник для студентів вищих навчальних закладів. – К.: Видавничий центр “Академія”, 2002. – 320 с.
Додж М., Кината К., Стинсон К. Эффективная работа с Microsoft Excel 97 / Пер. с англ. – СПб.: Питер, 1998. – 1072 с.
Долголаптев В.Г. Работа в Excel 7.0 для Windows 95 на примерах. – М.: БИНОМ, 1995. – 384 с.
Должков В.А., Колесников Ю.В. Microsoft Excel 2000. – СПб.: БХВ-Санкт-Петербург, 1999. – 1088 с.
Информатика. Базовый курс / Симонович С.В. и др. – СПб: Питер, 2000. – 640 с.
Інформатика та обчислювальна техніка: Корот. тлумач. слов. / В.П. Гондол, А.Г. Дерев’янко, В.В. Матвєєв, Ю.З. Прохур; За ред.. В.П. Гондола. – К.: Либідь, 2000. – 318 с.
Інформатика: Комп’ютерна техніка. Комп’ютерні технології. Посібник. / За ред.. О.І. Пушкаря. – К.: Видавничий центр “Академія”, 2001. – 696 с.
Карберг К. Бизнес-анализ при помощи Excel. – К.: Диалектика, 1999. – 500 с.
Комягин В.Б., Коцюбинський А.О. EXCEL 7.0 в примерах. – М.: Нолидж, 1996.
Коржинский С. Робота на компьютере: Популярный самоучитель (+CD). – СПб.: Питер, 2005. – 368 с.
Коцюбинский А.О., Гошев С.В. Excel для бухгалтера в примерах. – М.: ЗАО «Издательский Дом «Главбух»». – 2003. – 240 с.
Культин Н.Б. Microsoft Excel: Быстрый старт. – СПб.: БХВ-Петербург, 2002. – 208 с.
Лавренов С.М. Excel: Сборник примеров и задач. – М.: Финансы и статистика, 2003. – 336 с.
Microsoft Excel 97. Шаг за шагом: Пер. с англ. – М.: Эком, 1997. – 448 с.
Морзе Н.В. Основи інформаційно-комунікаційних технологій. – К.: Видавнича група BHV, 2006. – 352 с.
Мэнсфилд Р. Еxcel 7.0 для занятых / Перев. з англ. – СПБ: Питер, 1997. – 304 с.
Пасько В., Колесников А. Самоучитель работы на персональном компьютере: 2-е изд. доп. – К.: Издательская группа BHV, 2000. – 656 с.
Персон С. MS Excel 97 в оригинале. В 2-х томах. – СПБ: BHV-Петербург, 1998.
Фултон Дженифер. Освой самостоятельно Microsoft Excel 2000. 10 минут на урок.: Пер. с англ. – М.: Издательский дом «Вильямс», 2002. – 224 с.

Короткі теоретичні відомості
Сортуванням називають перевпорядковування даних, за якого значення обраних вами полів (їх називають ключовими полями або ключами) розташовуються:

числа – в порядку зростання або зменшення їх величин,
тексти – в алфавітному порядку (прямому чи зворотному),
дати і час – у хронологічному порядку (прямому або зворотному).

Перед сортуванням даних важливо правильно виділити область, у якій відбуватиметься сортування. Якщо перед вибором меню Данные / Сортировка помістити курсор всередину таблиці, то Excel автоматично виділить усі її рядки і стовпці. Якщо ж виділити тільки частину таблиці, то і сортування відбудеться тільки у межах виділеної області. Наприклад, якщо ви виділите тільки стовпець із прізвищами співробітників, але не виділите стовпець із сумами належних їм виплат, то прізвища будуть перевпорядковані, але суми виплат залишаться нерухомими в колишніх комірках. Так можна зіпсувати дані, на введення яких був витрачений значний час. Виправити помилку можна, якщо відразу після неправильного сортування натиснути кнопку скасування, або закрити файл, не зберігаючи на диску внесені зміни.

Якщо сортування відбувається за одним ключем, то достатньо просто встановити курсор у ключове поле і скористатися однією з кнопок ^ži ^gi І. У процесі цього Excel сам виділить всю таблицю і виконає сортування за обраним ключем. Щоб Excel зміг правильно визначити межі таблиці, у ній не повинно бути пустих рядків чи стовпців.

Для сортування за декількома полями слід помістити курсор всередину таблиці або виділити діапазон комірок, що підлягає сортуванню, та вибрати в меню Данные опцію Сортировка. В результаті не екрані з’явиться вікно, показане на рис. 1.

Якщо ви бажаєте сортувати стовпці (а не рядки, як це робиться за замовчуванням), то сповістіть про це програмі, клацнувши на кнопці Параметры. Там же можна задати особливі режими сортування деяких даних, наприклад назви місяців чи днів тижня – не в алфавітному, а в хронологічному порядку тощо.

Зазвичай Excel вважає перший рядок шапкою таблиці, що не бере участі в сортуванні. Якщо ж таблиця не має шапки, і її перший рядок підлягає сортуванню нарівні з іншими, то у вікні сортування (рис. 1) слід вибрати: Идентифицировать поля по значениям столбцов листа.

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

Записи, що містять однакові значення ключового поля, у свою чергу, можуть бути відсортовані по іншому ключовому полю, якщо ви заповните графу Затем по. Наприклад, у процесі сортування персоналу записи з однаковими прізвищами сортуються за іменами. Аналогічно, записи, що містять однакові значення в двох перших ключових полях, можна піддати сортуванню, указавши третє ключове поле в графі В последнюю очередь по. Наприклад, якщо збігаються і прізвище, і ім’я, то сортування здійснюється за полем по батькові.

Щоб виконати сортування з обраними параметрами, клацніть мишкою на кнопці ОК.

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

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

Закінчивши сортування, і обравши в меню Данные пункт Итоги, на екран виводиться вікно (рис. 2), у якому треба відповісти на наступні питання:

Куди треба вставляти рядки з підсумками? Якщо, наприклад, ми виберемо тут Дата або Товар, то рядок підсумків буде з’являтися там, де змінюється значення дати чи найменування товару.
Як треба підбивати підсумок? Найчастіше підсумок – це просто сума значень у стовпці. Але замість суми можна вибрати, наприклад, середнє арифметичне, максимальне або мінімальне значення тощо.
У яких стовпцях треба підбивати підсумок? Тут треба позначити галочкою найменування стовпців, у яких обчислюються підсумки.
Чи потрібно вилучати всі старі підсумки перед виводом нових? Якщо відмовитись від вилучення, то нові підсумки додаватимуться до сформованих раніше. Це дозволяє виводити декілька різних підсумків одночасно, наприклад, до раніше обчислених сум додати ще й середні значення.
Чи потрібно після виводу кожного рядка з підсумками переходити на нову сторінку?
Де потрібно розміщати рядки з підсумками? (під відповідними групами рядків даних чи над ними).

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

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

Якщо таблиця містить проміжні підсумки різних рівнів деталізації, то зручно користуватися також розташованими в лівому верхньому куті кнопками з зображеннями цифр. Кнопка з цифрою 1 забезпечує виведення тільки загального підсумку, приховуючи всі дані таблиці і всі проміжні підсумки. Кнопка 2 додає проміжні підсумки наступного рівня деталізації і т.д. Кнопка з найбільшою цифрою забезпечує виведення усіх наявних у таблиці даних з усіма проміжними підсумками.

Зведені таблиці є ще одним засобом підведення підсумків. Щоб побудувати зведену таблицю, помістіть курсор всередині бази даних⁴^¹, виберіть у меню Даные опцію Сводные таблицы і дайте відповіді на питання програми:

Звідки взяти вхідні дані для побудови зведеної таблиці? Тут виберіть опцію В списке или базе данных Microsoft Excel.
Який діапазон комірок містить вхідні дані для побудови зведеної таблиці? Тут ви або погоджуєтеся з діапазоном, запропонованим програмою, або уточнюєте його.
Якою повинна бути структура зведеної таблиці? Тут треба за допомогою мишки перетягнути кнопки з зображенням заголовків стовпців вхідної бази даних на відповідні позиції зведеної таблиці. Наприклад, для виконання завдання 6, кнопку Товар треба перемістити в позицію Строка, кнопку Продавец – у позицію Столбец, кнопку Стоимость – у позицію Данные, а кнопку Дата – у позицію Страница (див. рис. 3). Звичайно в позиції Данные кнопка, що відповідає числовим даним вхідної таблиці, набуває вигляду Сумма по полю... Але крім суми, зведена таблиця дозволяє обчислювати і багато інших підсумків: середнє, мінімум, максимум тощо. Щоб змінити спосіб обчислення підсумків, треба двічі клацнути на кнопці в позиції Данные і вибрати потрібний спосіб підведення підсумку. 4. Де треба розмісити побудовану зведену таблицю? Тут є можливість вибору між новим робочим аркушем і аркушем, що містить вхідну базу даних.

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

Хід виконання роботи
1. Заповніть робочий аркуш даними про реалізацію товарів відповідно до зразка, приведеного на рис. 4, де Д1 – дата продажу, Т1 – прізвище продавця, Т2 –найменування товару, Ч1, Ч2 – його ціна і кількість, формула Ф1=Ч1*Ч2.

Таблиця повинна містити 30-40 рядків, але в процесі цього в них повинні зустрічатися не більше 3-4 дат, 3-4 прізвищ продавців і 4-5 найменувань товарів. Отже дати, прізвища продавців і найменування товарів мають повторюватися, але обов’язково в різних сполученнях.

Скопіюйте вміст даної таблиці ще на два робочих аркуші.
На першому аркуші виконайте сортування записів у порядку зменшення вартості продажів.
На другому робочому аркуші виконаєте необхідне сортування і підведення підсумків продажів по датах із проміжними підсумками по товарах.
На третьому робочому аркуші виконайте необхідне сортування і підведення підсумків продажів по товарах із проміжними підсумками по продавцях.
Побудуйте зведену таблицю, у якій у рядках зазначені найменування товарів, у стовпцях – прізвища продавців, а на перетинанні рядка і стовпця – сумарна вартість даного товару, проданого даним продавцем. Таблиця повинна забезпечувати виведення інформації за весь період і за кожну дату окремо.
Побудуйте зведену таблицю, де рядкам відповідають найменування товарів, стовпцям – дати продажу, а на перетинанні рядка і стовпця – сумарна вартість даного товару, проданого за цей день. Таблиця повинна забезпечувати виведення інформації про всіх продавців і про кожного продавця окремо.
Запишіть у макрос процес створення зведеної таблиці або процес сортування і підведення підсумків, а потім виконайте цей макрос.
Збережіть створений файл на диску.
Відкрийте файл, створений у лабораторній роботі 4, і побудуйте по його даних зведені таблиці, що характеризували б середню вартість i-кімнатної квартири (i= 1,2,3,...) у кожному районі, а також середню вартість квадратного метра в такій квартирі.

Теми для самостійного вивчення:
Створення і використання макросів.
Контрольні питання

Як здійснюється сортування даних у Excel?
Які параметри можна задати перед початком сортування?
Як повинні бути відсортовані дані перед підведенням підсумків і чому?
Як здійснюється підведення підсумків?
Що таке символи структури і як ними користуватися?
Що таке макроси? Як записати і виконати макрос?
Що в Excel називають базою даних?
Як здійснюється побудова зведеної таблиці?
Як змінити структуру зведеної таблиці після її створення?

1 Базою даних у Excel називають таблицю регулярної структури, в якій перший рядок містить заголовки полів (стовпців), а решта рядків заповнені однотипними даними. В таблиці не повинно бути пустих стовпців і об’єднаних комірок.

Схожі:

УРОКУ Тема: Організувавши табличні дані у формі списку, користувач одержує можливість виконання в Excel дій, аналогічних процедурам обробки баз...	УРОКУ Тема: Зведені таблиці Створюючи зведену таблицю, користувач задає імена полів, що розміщаються в її рядках і стовпцях. Допускається також завдання поля...
Уроку Тема уроку Використання простого та розширеного фільтрів при впорядкуванні даних. Консолідація даних. Зведені таблиці	Тема. Створення звітів за допомогою майстра Систематизація та застосування знань стосовно джерела даних, рівнів групування, порядку сортування даних, формування зовнішнього...
Тема. Створення звітів за допомогою майстра Систематизація та застосування знань стосовно джерела даних, рівнів групування, порядку сортування даних, формування зовнішнього...	План-конспект Тема: Зведені таблиці У цьому прикладі ми маємо 799 рядків даних збуту, перелічених за країнами, продавцями та обсягами збуту
ЗАТВЕРДЖУЮ Аналіз, структура програми. Швидке (двоїчне) сортування. Аналіз, застосування. Способи сортування за лінійний час. Реалізація на...	ЗАТВЕРДЖУЮ Аналіз, структура програми. Швидке (двоїчне) сортування. Аналіз, застосування. Способи сортування за лінійний час. Реалізація на...
ЗАТВЕРДЖУЮ Аналіз, структура програми. Швидке (двоїчне) сортування. Аналіз, застосування. Способи сортування за лінійний час. Реалізація на...	Тема: Фільтрація даних Мета роботи: навчитися виконувати сортування даних та їх фільтрацію в Microsoft Excel

Додайте кнопку на своєму сайті:
Портал навчання