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

• Название: Лабораторная работа использование статистических, математических и текстовых функций
• Дата: 13.05.2023
• Размер: 1.26 Mb.
• Имя файла: Лаб_раб_EXCEL.doc
• Тип: Лабораторная работа; #1126371
• страница: 7 из 7

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

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

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


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

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

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

• 
  выделить исходную таблицу с названиями строк и столбцов;
  
• 
  выполнить команду Данные — Сводная таблица (или вывести на экран панель инструментов Сводная таблица (Вид — Панели инструментов) — Сводная таблица — Мастер сводных таблиц;
  
• 
  указать «в списке или в базе данных МS Ехсе1»; будет выведен диапазон выделенных ячеек;
  
• 
  создать макет сводной таблицы, перетащив с помощью мыши поле Год в область строки, поле Продукция — в область столбца, поле Сбыт — в область данных;
  
• 
  выбрать новый лист для помещения на него таблицы;
  
• 
  переименуйте созданный рабочий лист в Год-Продукция.
  

5. 
   Измените ориентацию строк и столбцов в сводной таблице: установите курсор на любую ячейку сводной таблицы; вызовите Мастера сводных таблиц; протаскиванием мыши поменяйте местами поле строк Год и поле столбца Продукция.
   
6. 
   Измените макет сводной таблицы, заменив поле столбца Год на Район. Для этого удалите поле столбца Год, протащив за пределы сводной таблицы, а поле Район в область столбца.
   
7. 
   Создайте самостоятельно еще две сводных таблицы на основе данных следующей таблицы (рис. 14)
   

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

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

• 
  выведите данные за 2006 год;
  
• 
  выделите таблицу;
  
• 
  Правка — Копировать;
  
• 
  Правка — Специальная вставка — включите опцию Вставить Значения.
  

	Поле строки	Поле столбца	Поле страницы	Поле данных	Функция
Таблица 2		Продукция	Год Район продавец	Сбыт Объем	Сумм Сумм
Таблица 3		Продукция	Год Район	Сбыт Сбыт	Макс Мин

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

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

Для этого:

• 
  откройте лист Исходные данные;
  
• 
  создайте сводную таблицу, в которой полем строк будет Месяц, полем столбца — Продукция, полем данных — Сбыт;
  
• 
  выделите месяцы Январь, Февраль, Март;
  
• 
  щелкните на кнопке Группировка на панели Сводная таблица;
  
• 
  переименуйте появившееся имя поля группы Месяц2 в Кварталы (вводите непосредственно вместо слова Месяц2), аналогично переименуйте элемент Группа1 в Квартал1;
  
• 
  объедините в кварталы остальные месяцы года.
  

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

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

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

• 
  в поле Сортировать выберите Продавец, в поле Затем — Продукция;
  
• 
  сначала обеспечить получение промежуточных итогов по продукции, затем по Продавцам. При этом следует снять флажок Заменить текущие итоги;
  
• 
  скрыть (Показать) детализирующие данные в таблице, щелкая на кнопках структурирования таблицы (+, -, 1..4).
  

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

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

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

• 
  откройте рабочий лист Исходные данные;
  
• 
  выделите две верхних строки таблицы и выполните команды Вставка — Строки, снимите выделение строк;
  
• 
  введите в эти строки в столбцы А, B, С следующее:
  

Продавец	Сбыт	Район
Петров	>300	Восточный

• 
  выделите таблицу или одну из ее ячеек;
  
• 
  Данные — Фильтр — Расширенный фильтр;
  
• 
  включите опцию Копировать результат в другое место, задайте Исходный диапазон и Диапазон условий (А1:С2), а также — куда выводить найденные данные.