Файл: Задания.doc

2. 2.      Отменить итоги можно с помощью командыИтогименюДанные. В диалоговом окнеПромежуточные итогинажать кнопкуУбрать все(Рис.141).

Дополнительные задания

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

2. Введите данные в таблицу, число записей должно быть не менее 10.

3. Добавьте в таблицу две записи, используя функцию Автоввод.

4. Скопируйте полученную таблицу еще на четыре отдельных листа, предварительно вставив их. Назовите листы: Сортировка2, Автофильтр1, Автофильтр2, Расширенный фильтр.

5. Отсортируйте список Код заказапо возрастанию.

6. Перейдите на лист Сортировка 2

7. Отсортируйте список по полю Тип продукциипо возрастанию и по полюНаименование товарапо убыванию.

8. Перейдите на лист Автофильтр.

9. Создайте Автофильтр.

10. Выберите записи находящиеся в столбце с именем Код заказа.

11. Перейдите на лист Автофильтр 2.

12. Задайте свои критерии поиска данных: Наименование товара начинается на определенную букву, используяПользовательский автофильтр.

13. Перейдите на лист Расширенный фильтр

14. Выполните поиск записей с помощью Расширенного фильтра, поместив полученный результат на отдельную область электронной таблицы, задав диапазон условий:Цена заказа лежит в определенном диапазоне.

15. Скопируйте таблицу на пустой лист и назовите его Промежуточные итоги.

16. Добавьте промежуточные итоги, определив Стоимость по определенному виду товара.

17. Сохраните рабочую книгу

Формулировка задания: MS Excel. Создание сводных и консолидированных таблиц

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

Контрольный пример

11. Запустите Microsoft Excel.

12. Откройте книгу с контрольным примером.

13. Добавьте в книгу новый рабочий лист и переименуйте его в лист с именем «Список».

14. Скопируйте ранее созданную таблицу с листа «Сортировка»и отмените все условия сортировки, возвращая таблице первоначальный вид.

---

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

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

Рисунок 143 - Таблица исходных данных с листа «Список»

16. Выберите в пункте меню ДанныекомандуСводная таблица.

17. Microsoft Excel запуститМастер сводных таблиц и диаграмм – шаг 1 из 3. На данном шаге построения сводной таблицы указывается тип источника данных и вид создаваемого отчета. Установите переключатели согласно приведенному ниже рисунку и нажмите кнопкуДалее.

Рисунок 144 - Мастер сводных таблиц и диаграмм – шаг 1 из 3

18. Далее во втором окне Мастер сводных таблиц и диаграмм, вам требуется указать диапазон, в котором находятся исходные данные. Если вы правильно выполнили пункт 5 контрольного задания, выделили одну ячейку в списке, то мастер правильно заполнит это окно диалога, нажмите кнопкуДалее.

Рисунок 145 - Мастер сводных таблиц и диаграмм – шаг 2 из 3

19. В последнем окне диалога, укажите место, где вы хотите расположить сводную таблицу, в нашем примере это новый лист, смотрите ниже приведенный пример, после нажмите кнопку Готово.

Рисунок 146 - Мастер сводных таблиц и диаграмм – шаг 3 из 3

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

• -          в поле страницы поля Код заказа и Код фирмы;

• -          в поле столбцов поле Вид продукции;

• -          в поле строк поле Наименование товара;

• -          в область данных поле Цена заказа.

Рисунок 147 – Построение сводной таблицы

21. После построения сводной таблицы с помощью мастера она должна иметь следующий вид:

---

Рисунок 148 – Сводная таблица

22. Переименуйте лист, на котором была создана сводная таблица в лист с соответствующим именем.

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

24. В появившемся диалоговом окне Параметры сводной таблицы, уберите флажок в разделеФорматнапротив поляОбщая сумма по строками нажмите кнопкуОК.

25. В поле Код фирмыиз списка выберите фирму под номером 4 и нажмите кнопку ОК.

26. После всех преобразований вы должны получить таблицу, приведенную ниже, по которой можно сделать следующие выводы: фирма под номеров 4 приобрела товар по следующим наименованиям, шоколадные конфеты «Орион» и «Школьная» на сумму 9300 тыс. руб. и карамель «Слами» на сумму 7800 тыс. руб.

Рисунок 149 – Итоговая сводная таблица

Создание консолидированной таблицы

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

Рисунок 150 – Таблица с исходными данными

28. Переименуйте лист с созданной таблицей в лист с именем «Январь».

29. Создайте три копии листа «Январь».

30. На второй, третьей и четвертой копии листа измените значения в столбцах КоличествоиЦена за единицу товара, таким образом, чтобы они отличались друг от друга по разным позициям товаров.

31. Переименуйте данные листы соответственно в листы с названиями «Февраль», «Март», «Апрель».

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

Рисунок 151 – Подготовка листа Консолидация

33. Переименуйте данный лист в лист с названием «Консолидация».

34. В пункте меню Данныевыберите командуКонсолидацияи заполните окно диалогаКонсолидация. Выберите функциюМаксимумв раскрывающемся спискеФункция. В данном примере в областиИспользовать в качестве именустановите флажок в полеЗначение левого столбца, для консолидации по строкам.

---

Рисунок 152 – Окно диалога Консолидация

35. В поле Ссылкавведите или укажите с помощью мыши последовательно следующие исходные ссылки:

•         Январь!$A$2:$B$11

•         Февраль!$A$2:$B$11

•         Март!$A$2:$B$11

•         Апрель!$A$2:$B$11

Ввод в это поле производится в следующем порядке :

•         вводим данные за январь в поле Ссылка;

•         нажимаем кнопку Добавить. Данные будут перенесены с областьСписок данных;

•         повторить выполнение выше описанных действий для остальных данных.

Рисунок 153 – Команда Консолидация использует ссылки из Списка диапазонов для вычисления консолидированных максимальных значений.

36. Нажмите кнопку ОК. Microsoft Excel вычислит максимальные исходные значения и поместит их в итоговый лист, представленные на рисунке 12.

Рисунок 154 – Диапазон В2:В11 в листе Консолидациясодержит максимальные значения соответствующих ячеек в четырех исходных листах.

37. Сохраните файл рабочей книги.

Дополнительные зедения

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

2. 2.         Переименуйте лист в лист с именемСводная таблица.

3. 3.         Введите данные в таблицу, число записей должно быть не менее 10.

4. 4.         Создайте на существующем листе своей рабочей книги сводную таблицу, с помощью которой можно проанализировать закупки фирмы по датам продажи.

5. 5.         Отформатируйте параметры сводной таблицы по своему усмотрению.

6. 6.         На новом листе рабочей книги создайте таблицу, которая будет отображать информацию о продажах продукции за определенный месяц. Таблица должна содержать следующие столбцы:Наименование товара, Цена за единицу товара, Количество, Цена заказа.

7. 7.         Переименуйте лист по названию текущего месяца.

8. 8.         Создайте три копии листа текущего месяца.

9. 9.         Переименуйте листы по названиям других месяцев года.

10. 10.     На данных листах внесите изменения значений в столбцеЦена товара за единицу.

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

12. 12.     Переименуйте данный лист в лист с названиемКонсолидация.

13. 13.     Сохраните рабочую книгу.

14. 14.     Закройте все открытые файлы электронной таблицы.

15. 15.     Скопируйте результат на свою дискету

---

 

---