Практическая работа СТРУКТУРИРОВАНИЕ, КОНСОЛИДАЦИЯ ДАННЫХ И ПОСТРОЕНИЕ СВОДНЫХ ТАБЛИЦ
учебно-методическое пособие на тему
В методическом пособии даются пошаговые рекомендации для выполнения практической работы. Пособие может быть использовано на уроках дисциплины "Информационные технологии в профессиональной деятельности" для специальности "Техническое обслуживание и ремонт автомобильного транспорта" и других специальностей.К методическим рекомендациям прилагается файл с данными для выполнения самостоятельной части работы.
Работа служит для получения практических навыков по изучению следующих тем:
- манипулирование данными, расположенными на разных листах рабочей книги;
- списки и операции со списками (фильтрация, сортировка);
- использование диалоговых окон для изменения информации в списках;
- структура таблицы (создание и удаление);
- консолидация данных, расположенных на разных листах рабочей книги методом использования команды Данные/Консолидация;
- построение сводных таблиц.
Скачать:
Вложение | Размер |
---|---|
![]() | 111.5 КБ |
![]() | 68 КБ |
Предварительный просмотр:
Практическая работа
СТРУКТУРИРОВАНИЕ, КОНСОЛИДАЦИЯ ДАННЫХ
И ПОСТРОЕНИЕ СВОДНЫХ ТАБЛИЦ
Практическая работа служит для получения практических навыков по изучению следующих тем:
- манипулирование данными, расположенными на разных листах рабочей книги;
- списки и операции со списками (фильтрация, сортировка);
- использование диалоговых окон для изменения информации в списках;
- структура таблицы (создание и удаление);
- консолидация данных, расположенных на разных листах рабочей книги методом использования команды Данные/Консолидация;
- построение сводных таблиц.
Цель работы: научиться объединять данные расположенные на разных листах,
формировать структурированные отчеты в табличной форме,
осуществлять поиск и фильтрацию данных в таблицах.
Основные сведения о списках, структуре рабочего
листа, консолидации и сводных таблицах
Список - это упорядоченный набор данных, база данных на рабочем листе. Столбцы списка называются полями, строки – записями. Ведение списка можно осуществлять в диалоговом окне (форме). Над списками можно выполнять такие операции, как фильтрация и сортировка.
В процессе сортировки списка переупорядочиваются строки в соответствии с содержимым одного, двух или трех столбцов.
Фильтрация – это быстрый способ выделения подмножества данных списка для последующей работы с ним. В результате фильтрации списка на экран выводятся только те строки, которые содержат определенные значения, либо те, которые удовлетворяют некоторому набору условий поиска (критерию).
Структура таблицы наиболее полезна для создания итоговых отчетов, в которых не нужно приводить все детали. Один рабочий лист может иметь только одну структуру (горизонтальную, вертикальную или обе). Структура может иметь до восьми уровней вложения.
Консолидация – это комбинирование или накопление информации из двух или более рабочих листов в нескольких рабочих книгах. Основным фактором, влияющим на успешность консолидации, является способ размещения информации в рабочих листах. Если эти способы во всех рабочих листах одни и те же, в этом случае задача консолидации становится достаточно простой.
Сводная таблица - это динамический итог данных, содержащихся в базе данных. Создается сводная таблица только при помощи специального средства "Мастер сводных таблиц".
Выполнение практической работы
Перед вами стоит задача совместной обработки нескольких таблиц – списков, расположенных на разных листах рабочей книги.
1. Загрузить программу Excel.
2. На листе рабочей книги (Лист1) создать табл. 1 с исходными данными о заказе партий запчастей у некоторой фирмы на год:
Таблица 1
Номер | Название | Цена | Количество шт. в партии | Сумма | Квартал |
1 | Адсорбер | 10000 | 17000000 | Кв1 | |
2 | Батарея аккумуляторная | 20000 | 20000000 | Кв2 | |
3 | Воздухозаборник | 10000 | 35000000 | Кв1 | |
4 | Генератор | 10000 | 40000000 | Кв3 | |
б | Датчик давления | 100000 | 270000000 | Кв3 | |
6 | Адсорбер1 | 20000 | 80000000 | Кв1 | |
7 | Батарея аккомуляторная1 | 10000 | 60000000 | Кв1 | |
8 | Воздухозаборник1 | 10000 | 45000000 | Кв3 | |
9 | Генератор1 | 10000 | 120000000 | Кв3 | |
10 | Датчик температуры | 15000 | 150000000 | Кв4 |
3. Рассчитать цену одного экземпляра по каждому наименованию заказанной продукции путем ввода и последующего копирования формулы.
4. Переименовать "Лист1" в "Заказ". Для этого установить указатель на ярлык "Лист1", нажать правую кнопку мыши, в контекстном меню выбрать команду Переименовать и вместо прежнего имени листа Лист1 ввести новое имя Заказ.
5. Получить итоговую сумму по столбцу "Сумма". Для этого установить курсор в ячейку Е12 и нажать кнопку автосуммирования. Ввести сформированную формулу.
6. Создать структуру построенной таблицы для скрытия детальных числовых данных. Для этого установите курсор внутри таблицы и выполните команду Данные/Группа и Структура/Создание структуры. На экране структуры таблицы щелкните кнопку "-", чтобы скрыть столбцы с числами, а затем кнопку "+" для показа скрытой информации.
- Удалить структуру, выполнив команду Данные/Группа и Структура/Удалить структуру.
- Добавить к существующим листам рабочей книги еще один. Для этого установить указатель на один из ярлыков, нажать правую кнопку мыши и выбрать команду Добавить. В диалоговом окне Вставка выделить значок с названием "Лист" и нажать кнопку "Ok".
- Переименовать "Лист2", "Лист3", "Лист4" в "Январь", "Февраль", "Март" (табл. 2, 3, 4), так как они будут содержать информацию о реализации запчастей за первые три месяца года. Ввод данных осуществлять в соответствии с указаниями следующего пункта.
Январь Таблица 2
Наименование | Количество | Стоимость |
Адсорбер | 400 | |
Батарея аккумуляторная | 399 | |
Воздухозаборник | 100 | |
Генератор | 600 | |
Датчик давления | 45 | |
Адсорбер1 | 356 | |
Батарея аккомуляторная1 | 700 | |
Воздухозаборник1 | 873 | |
Генератор1 | 287 | |
Датчик температуры | 2087 |
Февраль Таблица 3
Наименование | Количество | Стоимость |
Адсорбер | 260 | |
Батарея аккумуляторная | 160 | |
Воздухозаборник | 400 | |
Генератор | 32 | |
Датчик давления | 146 | |
Адсорбер1 | 176 | |
Батарея аккомуляторная1 | 879 | |
Воздухозаборник1 | 911 | |
Генератор1 | 1000 | |
Датчик температуры | 3000 |
Март Таблица 4
Наименование | Количество | Стоимость |
Адсорбер | 274 | |
Батарея аккумуляторная | 406 | |
Воздухозаборник | 167 | |
Генератор | 548 | |
Датчик давления | 98 | |
Адсорбер1 | 200 | |
Батарея аккомуляторная1 | 654 | |
Воздухозаборник1 | 1007 | |
Генератор1 | 809 | |
Датчик температуры | 3086 |
10. Сгруппировать листы "Январь", "Февраль", "Март" для ввода общей для них информации. Группа листов создается щелчком мышью на ярлыке листа при нажатой клавише
11. Ввести и размножить формулы для подсчета стоимости и итоговых сумм во введенные таблицы.
12. Использовать консолидацию рабочих листов для получения итоговой информации о продаже литературы в целом за квартал (Январь, Февраль, Март). Для этого:
- добавить новый лист, переименовать его в "Квартал";
- выделить ячейку на новом листе "Квартал", начиная с которой будут размещены итоговые данные (например, А1);
- выполнить команду Данные/ Консолидация;
- в диалоге "Консолидация" выбрать в списке функций функцию Сумма;
- в строку "Ссылка" ввести абсолютную ссылку на консолидируемые данные (например, Январь!$А$2:$С$12) и нажать кнопку "Добавить";
- повторить ввод и добавление данных для ввода всей консолидируемой информации;
- включить флажок "значения левого столбца";
- нажать кнопку "Ok".
13. Изменить некоторые данные в одном из консолидируемых листов, например количество проданных Адсорберов в Январе. Изменятся ли данные в итоговой таблице?
14. Установить связанную консолидацию данных. Для этого
вставить новый рабочий лист, переименовать его в "Квартал1", активизировать ячейку начала формирования итоговой таблицы (например, А1) , выполнить все положения пункта 12, добавив флажок "Создавать связи с исходными данными".
- В полученной структурированной таблице просмотреть скрытые данные, нажав кнопки "2" или "+".
- Изменить некоторые данные в одном из консолидируемых листов, например, количество проданных Адсорберов в Январе. Изменятся ли данные в итоговой таблице?
- Построить сводную таблицу, информирующую о сумме проданных деталей по каждому наименованию отдельно. Для этого:
- активизировать рабочий лист "Заказ";
- вызвать мастер сводных таблиц, выполнив команду Данные/ Сводная таблица;
- в окне "Мастер сводных таблиц – шаг 1 из 4" выбрать источник, откуда будут поступать данные для построения таблицы (первую из предложенных опций: "В списке или в базе данных Microsoft Excel") и щелкнуть по кнопке "Далее>";
- в окне шага 2 ввести область исходных данных для построения сводной таблицы, например, Заказ!$А$1:$F$11, и щелкнуть по кнопке "Далее>";
- в окне шага 3 для определения внешнего вида сводной таблицы требуется в макете сводной таблицы перетащить поле таблицы "Название" в поле макета "столбец", поле таблицы "Квартал" в поле макета "строка", "Сумма" должна быть расположена в поле "данные", а затем щелкнуть по кнопке "Далее>";
- в окне шага 4 установить переключатель "Новый лист" и нажать кнопку "Готово".
- Изменить исходные данные (сначала убрать, а затем добавить одну строку в исходную таблицу) и в контекстном меню (щелчком правой клавиши мыши на поле сводной таблицы) выбрать команду Обновить данные.
- Поменять местами строки и столбцы сводной таблицы. Для этого снова запустить Мастер сводных таблиц и в диалоге шага 3 повернуть макет таблицы на 90 градусов: в строках вывести названия изданий, а в столбцах – квартал, после чего завершить диалог.
- Открыть макет сводной таблицы (3 шаг) и перетащить поле "Название" в область макета "страница", закончить диалог и обратить внимание на изменения в сводной таблице, затем нажать кнопку "Отобразить страницы" панели инструментов "Сводная таблица".
21. Отсортировать данные таблицы "Заказ" по возрастанию цены. Для этого:
- сделать текущей ячейку поля "Цена";
- нажать кнопку инструментального меню "Сортировать по возрастанию".
22. Выполнить многоуровневую сортировку по двум ключам: сначала по цене, потом по названиям в порядке возрастания значений этих ключей. Для этого:
- установить курсор в область данных таблицы "Заказ";
- вызвать команду Данные/Сортировка;
- в диалоговом окне в область "Сортировать по" ввести первый ключ сортировки "Цена"; в область "Затем по" ввести второй ключ сортировки "Название";
- нажать кнопку "Параметры..." и ознакомиться с возможными вариантами задания параметров сортировки;
- щелкнуть кнопку "Ok".
23. Выполнить подсчет промежуточных итогов по Количеству шт. в партии отдельно по кварталам, предварительно отсортировав данные таблицы "Заказ" по возрастанию номера квартала. Для этого:
- сделать текущей ячейку поля "Квартал";
- нажать кнопку инструментального меню "Сортировать по возрастанию";
- выполнить команду Данные/Итоги;
- в диалоговом окне команды "Промежуточные итога" в области "При каждом изменении в" выбрать "Квартал", в области "Операция" выбрать "Сумма", в области "Добавить итоги по" выбрать " Количество шт. в партии ";
- щелкнуть кнопку "Ok".
24. Аннулировать промежуточные итоги таблицы "Заказ". Для этого:
- установить указатель мыши на таблицу "Заказ";
- выполнить команду Данные/Итоги;
- в диалоговом окне команды "Промежуточные итоги" щелкнуть по кнопке "Убрать все".
25. Использовать форму базы данных для просмотра и корректировки записей таблицы "Заказ". Для этого:
- выделить область рабочего листа, где находятся данные вместе с названием столбцов;
- выполнить команду Данные/Форма;
- переместиться вперед и назад по таблице, используя кнопки "Далее" и "Назад";
- удалить последнюю строку таблицы нажатием кнопки "Удалить";
- добавить новую строку, используя кнопку "Добавить";
- закрыть диалог нажатием кнопки "Закрыть".
26. Использовать форму базы данных для поиска в таблице "Заказ" информации о запчастях, Количество шт. в партии которых превышает 10000. Для этого:
- выделить область рабочего листа, где находятся данные вместе с названием столбцов;
- выполнить команду Данные/Форма;
- в диалоговом окне "Заказ" щелкнуть по кнопке "Критерии";
- в поле Количество шт. в партии ввести критерий поиска (>10000);
- переместиться вперед и назад по найденным записям, используя кнопки "Далее" и "Назад";
- закрыть диалог нажатием кнопки "Закрыть".
27. Самостоятельно выполнить:
- сортировку данных таблицы "Заказ" по возрастанию значений поля "Номер";
- поиск в таблице "Заказ" информации о запчастях, заказанных в первом квартале с Количеством шт. в партии менее 20000.
28. Использовать автофильтр для вывода в таблице "Заказ" информации только о запчастях, цена которых более 5000 руб. Для выполнения этого задания необходимо:
- отметить область рабочего листа с данными и с заголовками;
- выполнить команду Данные/Фильтр/Автофильтр;
- раскрыть список на поле "Цена", выбрать пункт "Условие" и ввести выражение "больше 5000";
- щелкнуть кнопку "Ok".
29. Отменить автофильтр, для этого выполнить команду Данные/Фильтр и снять пометку с позиции Автофильтр.
30. Использовать усиленный фильтр для получения данных о запчастях, цена которых менее 5000, заказанных в третьем квартале Количеством шт. в партии более 10000. Для этого:
- скопировать имена столбцов "Цена", "Квартал" и "Количество шт. в партии" в ту часть рабочего листа, которая не содержит данных для поиска: H1,I1,J1;
- в клетки H2,I2,J2 ввести критерии поиска (<5000, >10000, Кв3);
- выполните команду меню Данные/Фильтр/Расширенный фильтр;
- в диалоге "Расширенный фильтр" задать область, где находятся данные (интервал списка) и область, в которой заданы критерии поиска;
- в группе "Обработка" укажите, что фильтрация будет выполняться на месте;
- щелкните кнопку "Ok".
31. Сохранить рабочую книгу в файле с именем lab3.xls.
32. Для выхода из Excel выберите из меню команду Файл/Выход.
Задания для самостоятельной работы
Проанализируйте данные о произведённых ремонтных работах некоторого автосервиса, в качестве исходных данных используйте данные в файле copir/ 311-315_ИТ_в_ПД /ТО.xls. Результаты по каждому вопросу представьте на отдельном листе.
- Проанализируйте суммы ремонта по видам ремонта
Для этого существует несколько способов:
Первый:
- Отсортируйте всю таблицу по столбцу вид ремонта.
- Выполните команду Данные/ Итоги просуммируйте Сумму ремонта при каждом изменении вида ремонта. Отобразите только итоги по видам ремонта
Второй:
Постройте сводную таблицу, где в строках будут виды ремонта, а в области данных сумма ремонта, можно в область столбцов перенести фамилию мастера.
Постройте диаграмму.
- Проанализируйте суммы ремонтов по видам ремонта и маркам автомобилей.
- Проанализируйте количество ремонтов по маркам автомобилей.
Получите фамилии владельцев а/м форд, для этого дважды кликните по соответствующей ячейке сводной таблицы или примените фильтр. - Проанализируйте количество и суммы ремонтов по мастерам-исполнителям.
- Найдите информацию о ремонтах на сумму более15000 руб.
Найдите информацию о ремонтах на сумму более15000 руб. а/м Audi мастера Зайцева (примените фильтр). - Подготовьте информацию о клиентах проходивших ТО1 отдельно по каждой марке.
- Подготовьте информацию о времени работы мастеров отдельно за каждый месяц.
Для этого постройте сводную таблицу в область строк поместите дату ремонта, в области столбцов ФИО мастера, в область данных сумму по времени работ.
Сгруппируйте данные по месяцам: установите курсор на любую ячейку с датой и выполните команду Данные/ Группировка - Сгруппируйте ремонты на мелкие, средние и крупные (до 3000р.; от3000 до10000р.; свыше 10000р.) и подсчитайте их количество и среднюю сумму по группе.
- Проанализируйте количество ремонтов по видам ремонтов отдельно по маркам автомобилей Audi, Nissan и Skoda.
Постройте сводную таблицу, где в области строк будут виды ремонта, в области столбцов – модели а/м, марки а/м поместите в область страниц и выберите только марки Audi, Nissan и Skoda, а в область данных поместите сумму ремонта.
По теме: методические разработки, презентации и конспекты
Сводная таблица соответствия направлений коррекционно - развивающей работы учителя-логопеда.
Планирование учителя-логопеда...
![](/sites/default/files/pictures/2024/04/30/picture-940917-1714424821.jpg)
Практическая работа №1. Инструкция для педагогов. Перенос таблиц из MS WORD в MS EXCEL. Редактирование таблицы.
В работе педагога очень много текстовых документов, среди них иногда встречаются большие таблицы, которые удобнее обрабатывать в табличном редакторе, но возникает проблема, как безболезненно перенести...
![](/sites/default/files/pictures/2014/03/12/picture-417391-1394643947.jpg)
Приёмы технологии критического мышления обучающихся на уроках физики посредством чтения и письма. Прием «Сводная таблица» по теме «Кинематика»
Приёмы технологии критического мышления обучающихся на уроках физики посредством чтения и письмаПрием «Сводная таблица» по теме «Кинематика»...
![](/sites/default/files/pictures/2020/04/08/picture-1237133-1586354794.png)
Инструкция для выполнения заданий по расчету загрузки (судна, вагона, контейнера) при помощи сводных таблиц в MS Excel
Инструкция для выполнения заданий по расчету загрузки (судна, вагона, контейнера) при помощи сводных таблиц в MS Excel...
![](/sites/default/files/pictures/2020/05/26/picture-271826-1590488303.jpg)
Практическое занятие 3. Консолидация данных, сводные таблицы в Excel.
Консолидация данных, сводные таблицы в Excel....
![](/sites/default/files/pictures/2023/03/12/picture-1463189-1678638926.jpg)
Сводная таблица произношения
Сводная таблицарезультатов обследования звукопроизношения и состояния артикуляционного аппарата...
План урока по теме: Исходные данные для построения основы рукава. Предварительные расчеты построения чертежа основы.
Разработка плана урока по теме Исходные данные для построения основы рукава. Предварительные расчеты построения чертежа основы...