Главная страница

Практическая работа служит для получения практических навыков по изучению следующих тем


Скачать 111.5 Kb.
НазваниеПрактическая работа служит для получения практических навыков по изучению следующих тем
Дата07.09.2022
Размер111.5 Kb.
Формат файлаdoc
Имя файлаlab_3_avto.doc
ТипПрактическая работа
#665689

Практическая работа

СТРУКТУРИРОВАНИЕ, КОНСОЛИДАЦИЯ ДАННЫХ
И ПОСТРОЕНИЕ СВОДНЫХ ТАБЛИЦ

Практическая работа служит для получения практических навыков по изучению следующих тем:

  • манипулирование данными, расположенными на разных листах рабочей книги;

  • списки и операции со списками (фильтрация, сортировка);

  • использование диалоговых окон для изменения информации в списках;

  • структура таблицы (создание и удаление);

  • консолидация данных, расположенных на разных листах рабочей книги методом использования команды Данные/Консолидация;

  • построение сводных таблиц.


Цель работы: научиться объединять данные расположенные на разных листах,
формировать структурированные отчеты в табличной форме,
осуществлять поиск и фильтрацию данных в таблицах.

Основные сведения о списках, структуре рабочего
листа, консолидации и сводных таблицах

Список - это упорядоченный набор данных, база данных на рабочем листе. Столбцы списка называются полями, строки – записями. Ведение списка можно осуществлять в диалоговом окне (форме). Над списками можно выполнять такие операции, как фильтрация и сортировка.

В процессе сортировки списка переупорядочиваются строки в соответствии с содержимым одного, двух или трех столбцов.

Фильтрация – это быстрый способ выделения подмножества данных списка для последующей работы с ним. В результате фильтрации списка на экран выводятся только те строки, которые содержат определенные значения, либо те, которые удовлетворяют некоторому набору условий поиска (критерию).

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

Консолидация это комбинирование или накопление информации из двух или более рабочих листов в нескольких рабочих книгах. Основным фактором, влияющим на успешность консолидации, является способ размещения информации в рабочих листах. Если эти способы во всех рабочих листах одни и те же, в этом случае задача консолидации становится достаточно простой.

Сводная таблица - это динамический итог данных, содержащихся в базе данных. Создается сводная таблица только при помощи специального средства "Мастер сводных таблиц".

Выполнение практической работы

Перед вами стоит задача совместной обработки нескольких таблиц – списков, расположенных на разных листах рабочей книги.

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. Создать структуру построенной таблицы для скрытия детальных числовых данных. Для этого установите курсор внутри таблицы и выполните команду Данные/Группа и Структура/Создание структуры. На экране структуры таблицы щелкните кнопку "-", чтобы скрыть столбцы с числами, а затем кнопку "+" для показа скрытой информации.

  1. Удалить структуру, выполнив команду Данные/Группа и Структура/Удалить структуру.

  2. Добавить к существующим листам рабочей книги еще один. Для этого установить указатель на один из ярлыков, нажать правую кнопку мыши и выбрать команду Добавить. В диалоговом окне Вставка выделить значок с названием "Лист" и нажать кнопку "Ok".

  3. Переименовать "Лист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. Сгруппировать листы "Январь", "Февраль", "Март" для ввода общей для них информации. Группа листов создается щелчком мышью на ярлыке листа при нажатой клавише <CTRL>. Для ввода индивидуальной для каждой таблицы информации листы разгруппировать путем выбора в контекстном меню команды Разгруппировать листы.

11. Ввести и размножить формулы для подсчета стоимости и итоговых сумм во введенные таблицы.

12. Использовать консолидацию рабочих листов для получения итоговой информации о продаже литературы в целом за квартал (Январь, Февраль, Март). Для этого:

  • добавить новый лист, переименовать его в "Квартал";

  • выделить ячейку на новом листе "Квартал", начиная с которой будут размещены итоговые данные (например, А1);

  • выполнить команду Данные/ Консолидация;

  • в диалоге "Консолидация" выбрать в списке функций функцию Сумма;

  • в строку "Ссылка" ввести абсолютную ссылку на консолидируемые данные (например, Январь!$А$2:$С$12) и нажать кнопку "Добавить";

  • повторить ввод и добавление данных для ввода всей консолидируемой информации;

  • включить флажок "значения левого столбца";

  • нажать кнопку "Ok".

13. Изменить некоторые данные в одном из консолидируемых листов, например количество проданных Адсорберов в Январе. Изменятся ли данные в итоговой таблице?

14. Установить связанную консолидацию данных. Для этого
вставить новый рабочий лист, переименовать его в "Квартал1", активизировать ячейку начала формирования итоговой таблицы (например, А1) , выполнить все положения пункта 12, добавив флажок "Создавать связи с исходными данными".

  1. В полученной структурированной таблице просмотреть скрытые данные, нажав кнопки "2" или "+".

  2. Изменить некоторые данные в одном из консолидируемых листов, например, количество проданных Адсорберов в Январе. Изменятся ли данные в итоговой таблице?

  3. Построить сводную таблицу, информирующую о сумме проданных деталей по каждому наименованию отдельно. Для этого:

  • активизировать рабочий лист "Заказ";

  • вызвать мастер сводных таблиц, выполнив команду Данные/ Сводная таблица;

  • в окне "Мастер сводных таблиц – шаг 1 из 4" выбрать источник, откуда будут поступать данные для построения таблицы (первую из предложенных опций: "В списке или в базе данных Microsoft Excel") и щелкнуть по кнопке "Далее>";

  • в окне шага 2 ввести область исходных данных для построения сводной таблицы, например, Заказ!$А$1:$F$11, и щелкнуть по кнопке "Далее>";

  • в окне шага 3 для определения внешнего вида сводной таблицы требуется в макете сводной таблицы перетащить поле таблицы "Название" в поле макета "столбец", поле таблицы "Квартал" в поле макета "строка", "Сумма" должна быть расположена в поле "данные", а затем щелкнуть по кнопке "Далее>";

  • в окне шага 4 установить переключатель "Новый лист" и нажать кнопку "Готово".

  1. Изменить исходные данные (сначала убрать, а затем добавить одну строку в исходную таблицу) и в контекстном меню (щелчком правой клавиши мыши на поле сводной таблицы) выбрать команду Обновить данные.

  2. Поменять местами строки и столбцы сводной таблицы. Для этого снова запустить Мастер сводных таблиц и в диалоге шага 3 повернуть макет таблицы на 90 градусов: в строках вывести названия изданий, а в столбцах – квартал, после чего завершить диалог.

  3. Открыть макет сводной таблицы (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. Результаты по каждому вопросу представьте на отдельном листе.

  1. Проанализируйте суммы ремонта по видам ремонта

Для этого существует несколько способов:

Первый:

  1. Отсортируйте всю таблицу по столбцу вид ремонта.

  2. Выполните команду Данные/ Итоги просуммируйте Сумму ремонта при каждом изменении вида ремонта. Отобразите только итоги по видам ремонта

Второй:

Постройте сводную таблицу, где в строках будут виды ремонта, а в области данных сумма ремонта, можно в область столбцов перенести фамилию мастера.

Постройте диаграмму.

  1. Проанализируйте суммы ремонтов по видам ремонта и маркам автомобилей.

  2. Проанализируйте количество ремонтов по маркам автомобилей.
    Получите фамилии владельцев а/м форд, для этого дважды кликните по соответствующей ячейке сводной таблицы или примените фильтр.


  3. Проанализируйте количество и суммы ремонтов по мастерам-исполнителям.

  4. Найдите информацию о ремонтах на сумму более15000 руб.
    Найдите информацию о ремонтах на сумму более15000 руб. а/м
    Audi мастера Зайцева (примените фильтр).

  5. Подготовьте информацию о клиентах проходивших ТО1 отдельно по каждой марке.

  6. Подготовьте информацию о времени работы мастеров отдельно за каждый месяц.
    Для этого постройте сводную таблицу в область строк поместите дату ремонта, в области столбцов ФИО мастера, в область данных сумму по времени работ.
    Сгруппируйте данные по месяцам: установите курсор на любую ячейку с датой и выполните команду Данные/ Группировка


  7. Сгруппируйте ремонты на мелкие, средние и крупные (до 3000р.; от3000 до10000р.; свыше 10000р.) и подсчитайте их количество и среднюю сумму по группе.

  8. Проанализируйте количество ремонтов по видам ремонтов отдельно по маркам автомобилей Audi, Nissan и Skoda.
    Постройте сводную таблицу, где в области строк будут виды ремонта, в области столбцов – модели а/м, марки а/м поместите в область страниц и выберите только марки Audi, Nissan и Skoda, а в область данных поместите сумму ремонта.



написать администратору сайта