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

Лаб_раб_EXCEL. Лабораторная работа использование статистических, математических и текстовых функций


Скачать 1.26 Mb.
НазваниеЛабораторная работа использование статистических, математических и текстовых функций
Дата13.05.2023
Размер1.26 Mb.
Формат файлаdoc
Имя файлаЛаб_раб_EXCEL.doc
ТипЛабораторная работа
#1126371
страница7 из 7
1   2   3   4   5   6   7

ЛАБОРАТОРНАЯ РАБОТА 9.

Анализ сбыта продукции на основе сводных таблиц.



ЗАДАЧА. Используя таблицу с исходными данными о сбыте и объеме продукции (рис. 13), создать сводные и выборочные таблицы, позволяющие быстро анализировать итоговую информацию о сбыте по видам продукции, регионам, продавцам, годам.


Рисунок 13 – Исходная таблица


  1. Наберите заданную таблицу. Переименуйте рабочий лист в Исходные данные.

  2. Продолжите таблицу до апреля 2007 года, предусмотрев в ней 4 вида продукции (молоко, мясо, бакалея, напитки), 4 продавцов (Петров, Никитин, Марченко, Сидоров) и 4 региона (Северный, Южный, Восточный, Западный). Используйте автозаполнение ячеек (Сервис — Параметры — Правка — Автозаполнение ячеек). Сохраните таблицу.

  3. Отсортируйте по месяцам, использовав пользовательский порядок сортировки.

  4. Создайте сводную таблицу 1, задав в ней в качестве поля строки — Год, поля столбца Продукция, поля данных — Сбыт. Используйте для вычисления сбыта функцию СУММ, задаваемую по умолчанию. Для создания сводной таблицы используется Мастер.

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

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

  • выполнить команду Данные — Сводная таблица (или вывести на экран панель инструментов Сводная таблица (Вид Панели инструментов) Сводная таблица — Мастер сводных таблиц;

  • указать «в списке или в базе данных МS Ехсе1»; будет выведен диапазон выделенных ячеек;

  • создать макет сводной таблицы, перетащив с помощью мыши поле Год в область строки, поле Продукция в область столбца, поле Сбыт — в область данных;

  • выбрать новый лист для помещения на него таблицы;

  • переименуйте созданный рабочий лист в Год-Продукция.




  1. Измените ориентацию строк и столбцов в сводной таблице: установите курсор на любую ячейку сводной таблицы; вызовите Мастера сводных таблиц; протаскиванием мыши поменяйте местами поле строк Год и поле столбца Продукция.

  2. Измените макет сводной таблицы, заменив поле столбца Год на Район. Для этого удалите поле столбца Год, протащив за пределы сводной таблицы, а поле Район в область столбца.

  3. Создайте самостоятельно еще две сводных таблицы на основе данных следующей таблицы (рис. 14)

Каждую сводную таблицу начинать делать, открыв рабочий лист Исходные данные с выделенной таблицей. Каждую таблицу создавать на отдельном листе, переименовав их в соответствии с содержанием. Для выбора функций Макс и Мин — двойной щелчок на поле Сумма по сбыту в области данных.

  1. Преобразуйте сводную таблицу 2 в отчет, выведя данные за 2006 год

  • выведите данные за 2006 год;

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

  • Правка — Копировать;

  • Правка Специальная вставка включите опцию Вставить Значения.







Поле строки

Поле столбца

Поле страницы

Поле данных

Функция

Таблица 2




Продукция

Год

Район

продавец

Сбыт

Объем

Сумм

Сумм

Таблица 3




Продукция

Год

Район

Сбыт

Сбыт

Макс

Мин

Рисунок 14 – Исходная таблица


  1. На основании исходных данных о сбыте и объеме продукции по месяцам создайте сводную ведомость, сгруппировав данные в кварталы.

Для этого:

  • откройте лист Исходные данные;

  • создайте сводную таблицу, в которой полем строк будет Месяц, полем столбца — Продукция, полем данных — Сбыт;

  • выделите месяцы Январь, Февраль, Март;

  • щелкните на кнопке Группировка на панели Сводная таблица;

  • переименуйте появившееся имя поля группы Месяц2 в Кварталы (вводите непосредственно вместо слова Месяц2), аналогично переименуйте элемент Группа1 в Квартал1;

  • объедините в кварталы остальные месяцы года.


10. Сверните данные в таблице, обеспечив вывод итогов по кварталам. Для этого выполните двойной щелчок на имени квартала или установите курсор на имени квартала и щелкните на кнопке Скрыть детали на панели Сводная таблица. Для показа деталей — вывода данных по месяцам — щелкните на Показать детали.
11. Составьте выборочный отчет о сбыте продукции каждым продавцом, используя команду Сортировка:

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

  • выделите любую ячейку в таблице;

  • выполните команды Данные Сортировка, в поле Сортировать по Продавец — по возрастанию;

  • Данные Итоги, в поле «При каждом изменении выберите Продавец, в поле Использовать функцию — Сумм, Добавить итоги по — Сбыт.


12. Составьте отчет о сбыте и объеме каждого вида продукции для каждого продавца:

  • в поле Сортировать выберите Продавец, в поле Затем — Продукция;

  • сначала обеспечить получение промежуточных итогов по продукции, затем по Продавцам. При этом следует снять флажок Заменить текущие итоги;

  • скрыть (Показать) детализирующие данные в таблице, щелкая на кнопках структурирования таблицы (+, -, 1..4).


13. Составьте выборочный отчет о сбыте одного продавца в одном районе, используя Фильтр:

  • откройте рабочий лист Исходные данные;

  • выделите одну из ячеек таблицы;

  • Фильтр Автофильтр. На именах столбцов появятся стрелки, открывающие списки, из списка Продавец выберите Марченко;

  • из списка Район Западный.

Для восстановления всех записей — Данные Показать все или еще раз Данные Фильтр — Автофильтр.
14. Составьте выборочный отчет при сложном критерии отбора, например, для продавца Петрова, района — Восточный сбыте более 3000:

  • откройте рабочий лист Исходные данные;

  • выделите две верхних строки таблицы и выполните команды Вставка — Строки, снимите выделение строк;

  • введите в эти строки в столбцы А, B, С следующее:

Продавец

Сбыт

Район

Петров

>300

Восточный

  • выделите таблицу или одну из ее ячеек;

  • Данные — Фильтр — Расширенный фильтр;

  • включите опцию Копировать результат в другое место, задайте Исходный диапазон и Диапазон условий (А1:С2), а также куда выводить найденные данные.
1   2   3   4   5   6   7


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