Практическая работа № 7 «Создание документов профессиональной направленности в табличных редакторах»

Практическая работа № 7 «Создание документов профессиональной направленности в табличных редакторах»

Цель работы: Изучение информационной технологии  выполнения операций «Промежуточные итоги», «консолидация», создания сводных таблиц  в табличном процессоре MS Excel.

Оборудование и материалы: персональный компьютер с установленным пакетом  программ MS Office.

Краткие  теоретические сведения

1. Подведение промежуточных итогов в таблице.

Подвести промежуточные итоги в таблице Excel можно с помощью встроенных формул и команды «Промежуточные итоги» в группе «Структура» на вкладке «Данные».

Чтобы функция выдала правильный результат, проверьте диапазон на соответствие следующим условиям:

·        Таблица оформлена в виде простого списка или базы данных.

·        Первая строка – названия столбцов.

·        В столбцах содержатся однотипные значения.

·        В таблице нет пустых строк или столбцов.

Порядок выполнения операции:

1.      Выполнить сортировку по тому столбцу, по которому нужно получить промежуточные итоги.

2.      Данные – Структура – Промежуточные итоги.

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

2. Операция консолидации позволяет объединять данные,  расположенные в разных таблицах, на разных листах и даже в разных файлах. 

·        Участки таблиц, из которых извлекаются данные, должны иметь единую структуру:

·        имена консолидируемых данных  должны во всех таблицах располагаться либо в левом столбце, либо в верхней строке;

·        характеристики данных  должны располагаться в одном и том же порядке.

 Для получения консолидированной  таблицы:

1.      Выделите левую верхнюю ячейку будущей таблицы.

2.      Выполните Данные – Консолидация. Появится панель Консолидация .

3.      В списке Функция выставите ту функцию, по которой будете подводить итог. В поле Ссылка введите диапазон ячеек, по которым будет строиться итоговая таблица. Чтобы ввести диапазон, достаточно провести по нему указателем мыши с нажатой левой кнопкой и щелкнуть кнопку Добавить.

3. Сводные таблицы.

Сводные таблицы строятся на основе  простых базовых таблиц. Для создания сводной таблицы перейдите на вкладку Вставка, где в группе Таблицы выберите команду Сводная таблица.

Откроется следующее диалоговое окно:

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

И последняя опция, которую нужно установить в этом окне - выбрать место расположения сводной таблицы: в новом окне или на этом же листе. В последнем случае нужно указать диапазон адресов, где должна располагаться сводная таблица.

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

Если данные в исходной таблице изменились, то для обновления сводной таблицы в контекстном меню выберите команду Обновить.

Порядок выполнения работы

1.      В личную папку скопируйте документ Microsoft  Excel «Отчет о практической работе №7».

2.      На листе  1 в ячейке А1 запишите  дату.

3.      В ячейке А2  запишите  тему работы, в ячейке А3 запишите цель работы, в ячейке А4 оборудование.

4.      Откройте документ в режиме «Разметка страницы».  В верхнем колонтитуле запишите номер практической работы, в нижнем колонтитуле свою фамилию. Откройте документ в обычном режиме.

5.      Выделите диапазон ячеек A2:G4, задайте команды: Объединить по строкам, Перенос текста  (вкладка Главная – группа команд Выравнивание). Высота строк 45.

Задание 1. Подведение промежуточных итогов. 

В заданной  таблице:

                                                                                                                                                    Таблица 1

1.       Задайте количество запчастей;

2.       Вычислите общую стоимость запчастей (= цена*количество);

3.       Для цен и общей стоимости задайте денежный формат в рублях.

4.       Используя команду Промежуточные Итоги, вычислите  для каждого Склада сумму  количества запчастей.  Для этого:

a.       выделите таблицу;

b.       Выполните сортировку содержимого таблицы 1 по Складам;

c.       на  вкладке Данные  в группе Структура  выберите команду  Промежуточные Итоги. Задайте команды как на рисунке.

d.       Скопируйте полученную таблицу.

e.       Выделите таблицу 1, откройте окно Промежуточные итоги и нажмите Убрать все.

5.       В таблице 1 выполните сортировку данных по Виду запчасти и получите промежуточные итоги:  сумма  количество и общая стоимость. Скопируйте полученную таблицу.

Задание 2. Консолидация данных таблиц.

Создайте таблицы по образцу. Недостающие данные задайте самостоятельно.

Магазин "Автомир"                                  Магазин "Автолавка"                                 Магазин "Кардан"

По данным таблиц составьте  итоговую  таблицу о наличие товара по всем магазинам, используя команду Консолидация.

Итоговая  таблица наличия товаров по всем магазинам

Для этого:

Поставьте курсор в свободную ячейку.

Выполните команду вкладка Данные – группа Работа с данными – команда Консолидация.

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

Задание 3. Создание сводной таблицы.

1.      На листе 2 выделите ячейку  А3.

2.      Создайте макет сводной таблицы.  Для этого на вкладке Вставка выберите команду Сводная таблица – Сводная таблица.

3.      В диалоговом окне Создание сводной таблицы задайте команды, как на рис. 1.

Рисунок 1

4.  В «Списке полей сводной таблицы» переместите названия полей, как на рис.2. Название столбцов – Товар, название строк – Клиент и Год, Значение – Количество.

5. Сводная таблица будет иметь вид, как на рис. 3

6.      Для полученной таблицы задайте границы ячеек.

7.      Скопируйте эту таблицу в диапазоны A29:G47 и A50:G68.

8.      Назовите таблицы Таблица 1, Таблица 2, Таблица 3.

9.      В Таблице 1 выделите диапазоны B11:F13; B15:F17; B19:F21; B23:F25. Проанализируйте, каких товаров продали меньше 500 шт. Для этого задайте команду вкладка Главная - условное форматирование – правило выделения ячеек  -  числа меньше 500 –темно-красный текст и светло-красная заливка. Аналогично определите, продажа каких товаров составила больше 4000 шт.

10.  В Таблице 2, используя фильтр, оставьте данные только о клиенте 1 и клиенте 3, товаре 2 и товаре 5. Определите,  сколько всего указанных товаров продали эти клиенты.  Ячейку с результатом выделите цветом.

11.  В Таблице 3, используя маркеры     , скройте строки с данными по годам. К диапазону B52:F55 примените команду условное форматирования:  выделите те ячейки, значения в которых выше среднего.

Задание 4. Создание сводной таблицы и диаграммы.

6.      На листе 3 создайте сводную таблицу и сводную диаграмму.

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

2.      Оставьте в таблице только данные о продажах 1-го, 3-го, 4-го товаров клиентом 2 и клиентом 4.

3.      Выполните редактирование  и форматирование сводной диаграммы:

4.      На листе «Исходная таблица» измените сумму продаж 1-го товара у клиента 2.

5.      Выделите сводную таблицу. На вкладке Работа со сводными диаграммами – Анализировать  нажмите команду Обновить. Убедитесь, данные в сводной таблице и сводной диаграмме изменились.

 Выведите на печать  лист 1, лист 2, лист 3.

Контрольные вопросы

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

3.       Опишите порядок создания сводных таблиц и диаграмм.

Список литературы

1.  Михеева Е.В. Информатика: учебник для студентов учреждений среднего профессионального образования, М.: издательский центр  «Академия», 2018

2.  Михеева Е.В. Информатика. Практикум:  учебное пособие для студентов учреждений среднего профессионального образования, М.: издательский центр  «Академия», 2019

Скачано с www.znanio.ru