Задание 4. Группирование и анализ данных в электронной таблице
Консолидация
При консолидации данных объединяются значения из нескольких диапазонов данных или из одной таблицы. Консолидация позволяет сгруппировать данные по уровням, например, посмотреть все продажи конкретного человека. Таблица консолидации размещается на свободном листе.
Задание 4.1. Найти суммарные продажи всех менеджеров за указанный период.
Порядок выполнения
1. Вставить новый лист, переименовать его Консолидация.
2. Выполнить пункт меню Данные/Консолидация.
3. В открывшемся ДО ввести:
· в строке функция – Сумма;
· щелкнуть мышкой в строке Ссылка, переключиться на лист Данные по продажам I квартал, выделить мышкой всю таблицу без заголовков;
· в группе Использовать в качестве имен установить флажок значения левого столбца;
· установить флажок создавать связи с исходными данными;
· нажать кнопку ОК.
4. Появится новая таблица, где будут сгруппированы продажи по менеджерам и найдена итоговая сумма продаж. Развернув структуру по конкретному менеджеру можно посмотреть, величину продаж.
Задание 4.2.Определить величину продаж по заказчикам.
Примечание: при вызове ДО Консолидация удалить из списка диапазонов старые диапазоны. В качестве исходной таблицы указать таблицу без первого столбца (без менеджеров).
Задание 4.3. Определить количество продаж у каждого менеджера.
Примечание: при вызове ДО Консолидация указать функцию Количество.
|
|
|
Консолидацию можно применять и для данных, хранящихся на разных листах.
Задание 4.4. Определить среднюю величину продаж за два первых квартала года.
Порядок выполнения
1. Создать копию листа Данные по продажам I квартал:
· при нажатой клавише <Ctrl> отвести мышкой ярлычок листа в правую сторону;
2. Переименовать лист как Данные по продажам II квартал:
· щелкнуть правой кнопкой мышки по ярлычку листа 2 раза;
· стереть старое название листа и ввести новое.
3. Внести изменения в список продаж, например, изменить сумму некоторых продаж, а некоторые совсем убрать и внести новые, изменить название месяцев на апрель – июнь.
4. Вставить новый лист, переименовать его как Консолидация по кварталам.
5. Сделать активной ячейку А1.
6. Выполнить пункт меню Данные/Консолидация.
7. В открывшемся ДО ввести:
· в строке функция – Среднее;
· щелкнуть мышкой в строке Ссылка, переключиться на лист Данные по продажам I квартал, выделить мышкой всю таблицу без заголовков;
· нажать кнопку Добавить;
· щелкнуть мышкой в строке Ссылка, переключиться на лист Данные по продажам II квартал, выделить мышкой всю таблицу без заголовков;
|
|
|
· в группе Использовать в качестве имен установить флажок значения левого столбца;
· установить флажок создавать связи с исходными данными;
· нажать кнопку ОК.
В созданной структуре в первых строках будут содержаться данные с первого листа, затем со второго.
Задание 5. Сводные таблицы (отчет сводных таблиц)
Отчет сводной таблицы представляет собой интерактивную таблицу, с помощью которой можно быстро объединять и сравнивать большие объемы данных. Можно менять местами строки и столбцы для получения различных итогов по исходным данным, а также показывать подробные данные по нужным областям.
Создавать сводные таблицы проще всего с использованием соответствующего Мастера сводных таблиц, который проведет по шагам и уточнит вид таблицы и ее место расположения.
Задание 5.1. Построить сводную таблицу по исходным данным.
Порядок выполнения задания
1. Сделать активной любую ячейку в исходной таблице.
2. Выполнить пункт меню Данные/Сводная таблица.
3. На первом шаге определяется вид источника данных и вид создаваемой таблицы, в нашем случае просто нажать кнопку Далее.
4. На втором шаге определяется диапазон исходных данных, так как у нас была выделена ячейка в исходной таблице, то Excel автоматически определит его, поэтому опять нажать кнопку Далее.
|
|
|
5. На третьем шаге нажать кнопку Макет, в макете:
· поместить в область Страница данные по менеджерам,
· в область Строка – данные по заказчикам,
· в область Столбец – данные по месяцам,
· в область Данные – данные по сумме продаж.
6. Нажать кнопку ОК, затем кнопку Готово.
7. Создается новый лист, где размещается сводная таблица.
Построенная сводная таблица отражает данные по всем заказчикам: в какой месяц они осуществляли закупки.
Можно изменять выводимые в сводной таблице данные прямо на листе.
Задание 5.2. Определить все продажи менеджера Иванова.
Порядок выполнения задания
1. Щелкнуть мышкой по выпадающему списку возле области Менеджер и выбрать фамилию Иванов.
2. Нажать кнопку ОК.
В таблице отразятся данные только по Иванову.
Задание 5.3.Вывести все продажи только за январь.
Порядок выполнения задания
1. Убрать флажки с названий всех месяцев кроме Январь.
2. Нажать кнопку ОК.
В сводной таблице выведутся данные за один месяц по всем совершенным продажам.
Также легко можно менять и структуру сводной таблицы.
|
|
|
Задание 5.4.Вывести сводные данные по менеджерам.
Порядок выполнения задания
1. Скопируйте построенную таблицу ниже
2. Измените ее структуру:
· перетащите мышкой область Заказчики под область Менеджеры,
· область Менеджеры – в область Столбцов. Данные перегруппируются.
Теперь таблица отражает месячные продажи, осуществленные менеджерами. Естественно, итоговые данные по месяцам совпадают.
В процессе работы можно изменитьитоговую функцию.
Задание 5.5. Определить количество продаж у каждого менеджера.
Порядок выполнения задания
1. Выделить область Сумма по полю Сумма продаж, щелкнуть правой кнопкой мышки.
2. Из выпадающего меню выбрать пункт Параметры поля
3. В открывшемся ДО выбрать функцию Количество.
4. Нажать кнопку ОК.
В данном случае в сводной таблице отразятся количество продаж, совершенных менеджерами.
По сводной таблице легко создаетсядиаграмма. Следует иметь в виду, что диаграмма жестко связана с таблицей и любое изменение таблицы вызывает изменение ее диаграммы и наоборот.
Задание 5.6. По каждой таблице построить диаграмму.
На проверку присылать два файла:
1. Задание 1, выполненное в программе MS Word
2. Задание 2, выполненное в программе MS Excel
Задания присылайте на почту kaverinairina@yandex.ru
Дата добавления: 2018-04-15; просмотров: 259; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!