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

1. 1.      ЗапуститеMS Excel.

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

3. 3.      Вставьте еще один лист рабочей книги.

4. 4.      Скопируйте таблицу с данными, содержащую поля:Код заказа, Наименование товара, Код фирмы, Цена заказа, Дата продажи с листаРезизия.

5. 5.      Переименуйте этот лист в лист с именемСортировка.

6. 6.      Вставьте столбец после столбцаНаименование товара.

7. 7.      Введите в поле заголовка столбцаТип продукции.

8. 8.      Добавьте записи в таблицу, чтобы их число стало 10, для этого выполните следующие операции:

9. 9.      Щёлкните на ячейке в конце таблицы и начните набирать слово Конфеты. Как только вы введёте букву «К», функция Автоввод автоматически наберёт оставшиеся буквы слова Конфеты.

10. 10.  Введите остальные данные. Исходная таблица представлена на Рис 130.

 

Код заказа	Наименование товара	Тип продукции	Код фирмы	Цена заказа	Дата продажи
1	Конфеты "Василек"	шоколад.	1	10 550,00р.	02-04-10
2	Конфеты "Ромашка"	шоколад.	2	5 800,00р.	02-04-25
3	Конфеты "Загадка"	карамель	2	3 200,00р.	02-04-25
4	Конфеты "Москвичка"	карамель	3	7 000,00р.	05-04-28
5	Конфеты "Школьная"	шоколад.	4	5 200,00р.	02-05-07
6	Конфеты "Шалунья"	карамель	2	2 700,00р.	02-05-25
7	Конфеты "Южная ночь"	шоколад.	1	4 500,00р.	02-05-27
8	Конфеты "Слами"	карамель	4	7 800,00р.	02-05-17
9	Конфеты "Чароднейка"	шоколад.	3	2 600,00р.	02-05-14
10	Конфеты "Орион"	шоколад.	4	4 100,00р.	02-05-18

 

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

 

Сортировка данных

1. 1.      Отсортируйте данные по наименованию товара, для этого выполните следующие действия:

• -          Установите курсор в поле таблицы;

• -          В менюДанные выберите команду Сортировка. В открывшемся диалоговом окне в группе Сортировать по щёлкните на стрелке «вниз» и в списке заголовков столбцов выделите строкуНаименование товара (Рис 131).

Данные в группе будут отсортированы по наименованию товара и расположены в алфавитном порядке.

• -          Убедитесь , что текстовые поля в группахЗатемпоиВ последнюю очередьпусты, и щёлкните на кнопкеОК.

5. 5.      Отменить сортировку можно выбрав командуОтменить сортировкув меню Правка. Данные будут находиться в прежнем порядке.

 

Рисунок 131 - Диалоговое окно Сортировка диапазона

5. 6.      Отсортируйте по двум критериям: по Наименованию товара и по Дате продажи, для этого:

• -          В менюДанные выберите командуСортировка.

• -          Убедитесь, что в текстовом поле группыСортировать понаходится заголовокНаименование товараи включите опциюпо убыванию.

• -          В группеЗатем по щелкните на кнопке «вниз» и выделите ключДата продажи. Щёлкните на кнопке ОК.

Фильтрация списков с помощью автофильтра

1. 1.      Для фильтрации списков выполните следующие операции:

• -          Установите курсор текущей ячейки в таблицу.

• -          В менюДанныевыберите командуФильтр ~ Автофильтр.В заголовках каждого столбца появятся кнопки фильтра со стрелочками вниз.(Рис. 132)

 

 

Стрелки фильтра

 

 

 

Рисунок 132 - Таблица со стрелками фильтра

2. 2.      Щёлкните на стрелке фильтра в ячейкеДата продажи. В появившемся окне списка критериев необходимо выбрать нужный критерий (ключ) фильтрации.

Критерии фильтрации

 

Рисунок 133 -Список критериев фильтрации

3. 3.      В открывшемся списке критериев выберитеДату продаж 02-04-25, т.е. какие конфеты были проданы в этот день. Результат выполнения операции представлен на Рис. 134.

Рисунок134 - Список конфет, проданных 02-04-25

Остальные записи таблицы не удалены, а временно скрыты.

Порядковые номера строк в списке не являются последовательными. Они выделены на листе синим цветом. В списке, где происходила фильтрация, стрелка фильтрации стала синего цвета.

4. 4.      Восстановить список можно с помощью менюДанные командаФильтр ~ Показать всеили в списке критериев фильтрации выделитьВсе.

5. 5.      Для задания своих критериев поиска используютПользовательский автофильтр.

 

 

Рисунок 135 - Диалоговое окно Пользовательский автофильтр

11. 11.  Найдите товары с названием «Конфеты «Ромашка» и «Конфеты «Южная ночь».

12. 12.  Щёлкните на стрелке фильтра поляНаименование товара. В списке ключей выберитеУсловие. Откроется диалоговое окноПользовательский автофильтр (Рис135 ).

13. 13.  Убедитесь, что в группеНаименование товарав верхнем поле операторов находится «равно».

14. 14.  Щёлкните на стрелке соседнего поля и выделите строку Конфеты «Ромашка».

15. 15.  Включите опцию ИЛИ.

16. 16.  Щёлкните на стрелке нижнего поля критериев и выделите строку Конфеты «Южная ночь».

17. 17.  Щёлкните на кнопке ОК. Список выглядит как на рис.136.

18. 18.  Отмените Автофильтр. КомандаФильтр ~ Отобразить всеменюДанные.

.

Рисунок 136 - Результат выполнения операций с помощью Пользовательского автофильтра

Расширенный фильтр

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

Для применения расширенного фильтра:

1. 1.      Перед списком данных вставьте 3-4 пустые строки

2. 2.      Сформируйте диапазон условий.

• -          В первую пустую строку скопируйте заголовки фильтруемых столбцов.

• -          Во вторую – под соответствующими заголовками введите условия отбора (Рис. 137)..

• -          В ячейкуС2 – шоколад., а вD2 – 1.

3. 3.      Выберите командуДанные~Фильтр ~Расширенный фильтр( Рис.138), установив курсор внутри списка

Код заказа	Наименование товара	Тип продукции	Код фирмы	Цена заказа	Дата продажи
1	Конфеты "Василек"	шоколад.	1	10 550,00р.	02-04-10
2	Конфеты "Ромашка"	шоколад.	2	5 800,00р.	02-04-25
3	Конфеты "Загадка"	карамель	2	3 200,00р.	02-04-25
4	Конфеты "Москвичка"	карамель	3	7 000,00р.	05-04-28
5	Конфеты "Школьная"	шоколад.	4	5 200,00р.	02-05-07
6	Конфеты "Шалунья"	карамель	2	2 700,00р.	02-05-25
7	Конфеты "Южная ночь"	шоколад.	1	4 500,00р.	02-05-27
8	Конфеты "Слами"	карамель	4	7 800,00р.	02-05-17
9	Конфеты "Чародейка"	шоколад.	3	2 600,00р.	02-05-14
10	Конфеты "Орион"	шоколад.	4	4 100,00р.	02-05-18

 

Рисунок 137 - Таблица для применения Расширенного фильтра

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

5. 5.      Установите переключательОбработка в положениеФильторвать список на местедиалогового окна Расширенный фильтр (Рис.138). при необходимости установите параметрТолько уникальные записи, т.е. неповторяющиеся записи и нажмите кнопкуOK

 

Рисунок138 - Диалоговое окно Расширенный фильтр

6. 6.      В результате будут найдены шоколадные конфеты, которые поставляет фирма. Результат применение расширенного фильтра представлен на Рис.139.

Код заказа	Наименование товара	Тип продукции	Код фирмы	Цена заказа	Дата продажи
		шоколад.	1
Код заказа	Наименование товара	Тип продукции	Код фирмы	Цена заказа	Дата продажи
1	Конфеты "Василек"	шоколад.	1	10 550,00р.	02-04-10
7	Конфеты "Южная ночь"	шоколад.	1	4 500,00р.	02-05-27

Рисунок 139 - Результат применения Расширенного фильтра

1. Отмените Расширенный фильтр командой меню Данные ~ Фильтр ~ Отобразить все.

2. Произведите фильтрацию данных из таблицы по тому же критерию, но результат поместите в диапазон A17:F27. Для этого

• -          скопируйте заголовок таблицы в диапазонA17:F17;

• -          выберите командуДанные~Фильтр ~Расширенный фильтр (рис.140);

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

• -          установите переключательОбработка в положениескопировать результат в другое место и установите диапазонA17:F27 в полеПоместить результат в диапазон;

• -          нажмите кнопку OK.

 

Рисунок 140 – Окно для задания диапазонов

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

1. 1.      Найти суммарную стоимость отдельных видов конфет, например шоколадных.

2. 2.      Установите курсор в таблицу.

3. 3.      В менюДанныевыберите командуИтоги. Откроется диалоговое окноПромежуточные итоги.

 

Рисунок 141 - Диалоговое окно Промежуточные итоги

4. 4.      Щёлкните на стрелке текстового поляПри каждом изменении в:и в появившемся окне списка выделите строкуТип продукции.

5. 5.      Убедитесь, что в полеОперациянаходится словоСумма.

6. 6.      Убедитесь, что в полеДобавить итоги по:флажок установлен только напротив строкиЦена заказа.

7. 7.      Установите флажки напротив строкЗаменить текущие итоги иИтоги под данными., и щелкните на кнопкеОК.

8. 8.      Вы получите итоговые значения суммарной стоимости каждого вида конфет. Рабочий лист выглядит как на рис.142:

Рисунок 142 - Рабочий лист с промежуточными итогами

 

1. 1.      Для управления структурой таблицыИтоги, вы можете использовать кнопки:

•         Развернуть группу;

•         Свернуть группу;

•         Режим 1, Режим 2 и т.д.

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.     Скопируйте результат на свою дискету