Файл: Практическая работа 17. Использование инструментов электронных таблиц для анализа данных. Работа сор списками.docx

Скачать файл
Заказать решение

ВУЗ: Не указан

Категория: Не указан

Дисциплина: Не указана

Добавлен: 09.11.2023

Просмотров: 21

Скачиваний: 1

ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.

ПРАКТИЧЕСКАЯ РАБОТА №17.

Использование инструментов электронных таблиц для анализа данных. Работа сор списками.



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

Реализуемые компетенции:

  • ПК 6.1. Участвовать в планировании основных показателей производства.

  • ПК 6.5. Вести утвержденную учетно-отчетную документацию.

  • ОК 1. Понимать сущность и социальную значимость своей будущей профессии, проявлять к ней устойчивый интерес.

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

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

  • ОК 5. Использовать информационно-коммуникационные технологии в профессиональной деятельности.

  • ОК 9. Ориентироваться в условиях частой смены технологий в профессиональной деятельности.

В результате освоения практических заданий обучающийся должен:

  • уметь: оформлять документы с помощью MS Excel и научиться работать со списками, работать с операциями сортировки и фильтрации данных;

  • знать: понятия «электронная таблица», «сортировка», «фильтрация»; правила и приёмы работы с сортировкой и фильтрацией.

Материально-техническое обеспечение занятия: персональный компьютер, табличный процессор MicrosoftExcel 2007.

Практические задания и методические указания

Задание 1. Использование инструментовMicrosoftExcel 2007 для выполненияопераций Фильтрации,Сортировки, Подведения итогов для работы со списком меню

  1. Список – это таблица, содержащая упорядоченный набор данных, база данных на рабочем листе. Столбцы списка называются полями, строки – записями.

Чтобы достичь максимальной эффективности при работе со списками, надо следовать следующим правилам:каждый столбец должен содержать информацию одного типа;одна или две верхние строки списка должны содержать мнемонические названия столбцов, эти строки называют «строками заголовка списка»;список не должен содержать пустые строки и столбцы без названия, такие строки и столбцы делят список на два;для списка обычно отводится отдельный лист.


Над списками можно выполнять такие операции как фильтрация и сортировка. Ведение списков – это добавление, редактирование и удаление строк.

  1. Откройте таблицу Меню.xlsx.Переименуйте Лист1 в Объект.

  2. Отсортируйте записи таблицы по возрастанию цены продукта. Для этогоустановите курсор в ячейку столбца Цена ($);щелкните правой кнопкой мыши и в контекстном меню выберите команду Фильтр – Сортировка от минимального к максимальному. Посмотрите как изменилась таблица.

  3. Выполните сортировку записей таблицы по Поставщикам по возрастанию, а затем по Цене по убыванию. Для этого установите курсор в любую ячейку таблицы, затем на вкладке Данные в группе Сортировка и фильтр выберите команду Сортировка;в диалоговом окне Сортировка в строкеСортировать по выберите из списка полей Поставщик, в строке Порядок установите От А до Я (По возрастанию); затем нажмите кнопку Добавить уровень и в строке Затем по установите Цена ($), а в строке Порядок установите По убыванию;нажмите ОК и просмотрите таблицу после сортировки. Записи в ней будут отсортированы по поставщикам, а внутри поставщиков по цене по убыванию.

  4. Самостоятельно отсортируйте записи по столбцуКалорийность,затем по Весу (г) и затем поЦене ($).Убедитесь, правильно ли выполнена сортировка.

  5. Выберите из исходной таблицы на листе Объект записи о продуктах, стоимость которых более 1 $, используя пользовательский автофильтр. Для этого установите курсор в любую ячейку строки с названиями столбцов (заголовков таблицы); на вкладкеДанныев разделе Сортировка и фильтр выполните командуФильтр;щелкните по кнопке в ячейке Цена ($), выберите Числовые фильтры - больше… и в диалоговом окне Пользовательский автофильтр введите больше 1;нажмите OК.

  6. Отмените Автофильтр, для этого на вкладкеДанные выполните команду Фильтр.

  7. Определитецены продуктовблюда Лобио, в которое включите продукты, произведенные в Риге, и помесите результат в другую таблицу – таблицу результатов, используя поиск расширенным фильтром. Для этого скопируйте лист Объект и переименуйте его в Расш_Ф_ Лобио. Сформируйте на листе Расш_Ф_ Лобио таблицу с критерием отбора. Для этогов ячейку D18 введите текст: Критерий: Вывести цены на продукты блюда Лобио, которые произведены в Риге;в ячейки D20:E20 скопируйте из основной таблицы заголовки Блюдо и Город;в ячейки D21:E21 введите условия отбора: в D21 скопируйте из основной таблицы – Лобио, а в E21 – Рига.Сформируйте таблицу результатов поиска, скопировав в ячейки, начиная с A23, следующие названия столбцов основной (исходной) таблицы: Блюдо, Продукт, Поставщик, Город, Цена ($).Поместите курсор в пределы исходной таблицы; перейдите на вкладку Данные в разделе Сортировка и фильтр выполните команду Дополнительно. Введите в диалоговом окне Расширенный фильтр исходный диапазон – это диапазон основной таблицы;установите признакСкопировать результат в другое место;введите Диапазон условий: D20:E21, используя цветную кнопку около поля; введите в строкеПомесить результат вдиапазон ячейки для результирующей таблицы A23:E23, используя цветную кнопку около поля; нажмите ОК.



  1. Создайте результирующую таблицу с записями, в которых цена больше или равна 1 $ или калорийность больше 3000. Для этого скопируйте лист Объект и переименуйте его в Расш_Ф_ИЛИ. В ячейку D18 введите текст Критерий;создайте таблицу критериев, скопировав имена столбцов (полей) Цена ($)и Калорийность в ячейки D20 и E20 соответственно и для каждого поля введите логическое условие: в ячейку D21 введите >=1, в ячейку E21 введите >3000, образуя логическую операцию ИЛИ. Создайте заголовки столбцов для результирующей таблицы, скопировав имена всех столбцов основной таблицы в диапазон ячеек A23:М23. Поместите курсор в пределы основной таблицы и перейдите на вкладку Данные в разделе Сортировка и фильтр выполните команду Дополнительно. Введите в диалоговом окне Расширенный фильтр исходный диапазон – это диапазон основной таблицы; установите признакСкопировать результат в другое место; введите Диапазон условий: D20:E21, используя цветную кнопку около поля; введите в строкеПомесить результат вдиапазон ячейки для результирующей таблицы A23:М23, используя цветную кнопку около поля; нажмите ОК.

  2. Подсчитайте суммарную стоимость блюда Шашлык, используя функцию базы данных БДСУММ. Для этого скопируйте лист Объект и переименуйте его в Расш_Ф_БД. В ячейку D18 введите текст Критерий отбора записей для вычислений. Создайте таблицу с критерием отбора для блюда Шашлык, скопируйте в ячейку F20 название поля Блюдо;скопируйте в ячейку F21название блюда Шашлык;введите в ячейку D22 текст: Результат суммирования отобранных записей;установите курсор в ячейку F23, где надо получить результат и выполните командуВставить функцию вкладки Формулы, в диалогом окне выберите категорию Работа с базой данных, функцию БДСУММ. Введите в диалоговое окно аргументы функции: в строку База данных – диапазон ячеек основной таблицы вместе с названиями столбцов;в строку Поле – адрес ячейки с названием столбца, по значениям которого будет выводиться результат (L1); в строку Критерий – ячейки таблицы с условиями отбора (F20:F21).Нажмите ОК. В ячейке F23 будет выведена суммарная стоимость блюда Шашлык.

  3. Рассчитайте суммарную калорийность по каждому блюду и их стоимость, используя технологию итоговых таблиц. Перед созданием итоговой таблицы необходимо отсортировать строки таблицы в нужном порядке для подведения итогов. Перейдите на лист Объект;установите курсор в любую ячейку поля Блюдои выполните команду Сортировка от А до Я на вкладке Данные. Выполните команду Промежу­точные ито­ги раздела Структура вкладки Данные. В диалоговом окне Промежуточные итоги в области При каждом изменении в выберите Блюдо;в области Операция выберите Сумма; в области Добавить итоги по выберите Калорийность и Цена ($);установите флажкиЗаменить текущие итоги и Итоги под данными; чтобы за каждым итогом следовал автоматический разрыв страницы, установите флажок Конец страницы между группами;нажмите OK.

  4. Для отображения только промежуточных и общих итогов используйте обозначения уровней структуры 1, 2, 3 рядом с номерами строк. Кнопки + и - позволяют отобразить и скрыть строки подробных данных для отдельных итогов.При необходимости команду Промежуточные итоги можно использовать снова, чтобы добавить строки итогов с использованием других функций. Во избежание перезаписи имеющихся итогов снимите флажокЗаменить текущие итоги.




  1. Аннулируйте промежуточные итоги таблицы. Для этого установите курсор в любую ячейку таблицы; выполните команду Промежуточные итоги раздела Структура вкладки Данные; в диалоговом окне Промежуточные итоги щелкните по кнопкеУбрать все.

  2. Самостоятельно создайте итоговую таблицу для определения вес блюда в зависимости от значения его наименования.

  3. Постройте сводную таблицу, информирующую о сумме блюда и его весе по каждому блюду. Для этого активизируйте рабочий лист Объект; выполните команду Вставка - Сводная таблица. В диалоговом окне Создание сводных таблиц в строке Таблицаилидиапазон установите диапазон исходной таблицы, укажите, куда следует поместить отчет сводной таблицы – На новый лист;нажмите ОК (появится шаблон для создания сводной таблицы и окно со списком полей и областями, куда можно переместить поля исходной таблицы). Переместите поле Блюдо из окна Список полей сводной таблицы в область Названия строк, поле Вид в область Фильтр отчета, а поля Вес (г) и Цена ($) – в область Значения.



  1. После создания сводной таблицы на ленте появится контекстная вкладка Параметры (Работа со сводными таблицами) с кнопками для работы со сводными таблицами.Измените исходные данные на листе Объект, перейдите на лист сводной таблицы и выполните командуОбновить вкладки Параметры. Отмените обновление.

  2. Измените вид сводной таблицы, перетащив поле Блюдо в область Фильтр отчета, а поле Вид – в область Названия строк.

  3. Добавьте новые поля в сводную таблицу. Например, добавьте поле Торговая наценка. Для этого выполните команду Вычис­ляемое поле выпадающего списка Формулы вкладки ленты Параметры. В диалоговом окне Вставка вычисляемого поля введите в строку Имя – новое имя поля Торговая наценка; в строку Формула введите формулу для расчета = 'Цена ($)'*150%+ 'Цена ($)'. Нажмите ОК.

  4. Создайте сводную диаграмму на основании созданной сводной таблицы. Для этого выполните команду Сводная диаграмма вкладки Параметры. В диалоговом окне Вставка диаграммы нажмите ОК, после чего будет вставлена сводная гистограмма. При этом на ленте добавится контекстная вкладка Конструктор для сводных диаграмм, а рядом с диаграммой окно Область фильтра сводной диаграммы. Изменять диаграмму можно перемещая поля в разные области в окне Список полей сводной таблицы.На вкладке Конструктор для сводных диаграмм можно выбрать стили диаграмм и макеты диаграмм, при необходимости можно изменить тип диаграммы.



  1. Скопируйте лист Объект три раза. Переименуйте полученные листы Объект (2), Объект (3) и Объект (4) в Объект1, Объект2, Объект3. Удалите одновременно столбцы Вид, Дата Р, Продукт, Калорийность, Вес (г), Поставщик, Город, Страна, Вес (кг), Дата П на листах Объект1, Объект2, Объект3. Для этого сгруппируйте листы Объект1, Объект2, Объект3 для одновременного редактирования;выделите на текущем листе столбцы щелчком по соответствующей букве (С, D и т.д.) и удалите их; разгруппируйте листы, щелкнув по выделенным листам при нажатой клавише Shift.

  2. Введите индивидуальную информацию для каждой таблицы. Для этого увеличьте цену на 5% на листах Объект2 и Объект3 относительно стоимости на листе Объект1, предварительно сгруппировав листы Объект2 и Объект3, а затем разгруппировав их. Измените значения поля Порция на листе Объект3, увеличив их на 2.

  3. Создайте итоговую таблицу о стоимости блюд и количестве порций, расположенных в таблицах на листах Объект1, Объект2, Объект3, используя консолидацию рабочих листов. Для этого добавьте новый лист, переименовав его вКонсолидация; выделите ячейку на новом листе Консолидация, начиная с которой будут размещены итоговые данные (например, A1); выполните команду Консолида­ция раздела Работа с данными вкладки Данные. Выберите в диалоговом окне Консолидация из списка функций функцию Сумма;в строку Ссылка введите абсолютную ссылку на столбцы Блюдо, Порций и Цена ($) на листе Объект1 путем выделения этой части таблицы и нажмите кнопкуДобавить;добавьте абсолютную ссылку на те же столбцы на листе Объект2 и на листе Объект3;включите флажки Значения левого столбца и Подписи верхней строки;нажмите OK. На листе Консолидация появится таблица с консолидированными данными.



  1. Измените данные на листах Объект1, Объект2, Объект3. Проанализируйте, изменятся ли данные в итоговой таблице? Отмените изменения.

  2. Установите связь консолидированной таблицы с исходными таблицами, чтобы консолидация обновлялась автоматически при изменении исходных данных. Для этого создайте на листе Консолидация итоговую таблицу, начиная с ячейки А8, и дополнительно в диалоговом окне Консолидация установите флажокСоздавать связи с исходными данными. Нажмите ОК.

  3. Измените данные на листах Объект1, Объект2, Объект3 Проанализируйте, изменятся ли данные в итоговой таблице?

  4. Сохраните полученный файл в папку своей группы под именем Список.xlsx. Завершите работу с программой.

Смотрите также файлы