предыдущая | содержание | следующая

Практическое занятие № 15

1.      Откройте MS Excel.

1.     Построить Таблицу 1. В столбцы Регион, Месяц и Наименование продукции данные вводить, используя возможность выбора из списка. Сохраните книгу, в которой вы работаете под именем Таблицы.xls на диске H:.

2.     Отсортируйте по алфавиту столбец Регион; столбец Месяц в порядке следования в 1-ом квартале месяцев без потери соответствия данных! (Для этого выделите всю таблицу, выберите Пункт меню Данные - Сортировка - Сортировать по: Регион - по возрастанию: Затем по: Месяц - по убыванию).

3.     Используя функцию Автосумма рассчитайте Итого.

4.     Оформите наглядно Таблицу 1: задайте обрамление, заливку цветом, размер и тип шрифта (по своему усмотрению).

5.     Присвоить ячейке, в которой содержится общая сумма Выручки имя Итого (Вставка - Имя - Присвоить или используя поле имя ячейки).

6.     Закрепите шапку Таблицы 1.

7.     Установите на Таблицу 1 автофильтр.

8.     Используя средства автофильтра, отобразите, выручку за проданную продукцию по Южному региону за Месяц Март по молочным продуктам. Выделите полученные строки красным цветом.

9.     Установите режим - отобразить все.

10. Переименуйте лист 1 в Исходные данные.

11. Добавьте после листа Исходные данные новый лист, назвав его Промежуточная таблица.

12. Постройте Таблицу 2.

13. Используя функцию СуммЕсли, рассчитайте Объем выручки по молочным продуктам по всем регионам. Для ячейки, отображающей рассчитанную сумму, задайте дробный формат (простые дроби).

14. Используя возможности автофильтра Таблицы 1 (условие или пользовательский автофильтр), и написав формулу автосуммирования в Таблице 2, рассчитайте Объем выручки по всем продуктам  западного региона в общей сумме выручки. Для ячейки, отображающей рассчитанную сумму, задайте процентный формат.

15. Используя возможности автофильтра (условие или пользовательский автофильтр) Таблицы 1, и написав формулу автосуммирования в Таблице 2, рассчитайте Объем выручки по тем продуктам, выручка за которые превышает 150 тыс. руб.

16. Добавьте ниже Объем выручки по продуктам, сумма которых превышает 150 тыс. руб строку, и напишите показатель Курс доллара, руб/долл и в столбце значение введите курс доллара на дату расчета.

Таблица 1. Динамика продаж по регионам

Таблица 2. Сводные показатели продаж

17. Рассчитайте на основании полученной суммы, выручку в долларах США. Для ячейки, отображающей рассчитанную сумму, задайте денежный формат ($).

18. Определите по всем регионам: во сколько раз выручка продавца Казанцева А. оказалась больше, чем у продавца Ворфоломеева А. Для ячейки, отображающей рассчитанную сумму, задайте числовой формат (число знаков после запятой равно 2).

19. Скройте все формулы для расчетных ячеек Таблицы 2.

20. Защитите лист Таблица 2 от внесение изменений, установив пароль 111. Проверьте работоспособность защиты, попробовав внести изменения.

21. Снимите защиту.

22. Скройте лист Таблица 2.

23. Вынесите на панель инструментов значок мастера сводных.

24. На основе данных Таблицы 1 построить на отдельном листе сводную таблицу 3: Вынести в Поле страницы Регион и Месяц продаж; в Поле столбца - Наименование товара; Поле данных - Выручка за проданную продукцию; Поля строки - Фамилии продавцов.

25. Установите Масштаб отображения листа 75%.

26. Отключите в параметрах сводной таблицы автоформат и включите сохранять форматирование.

27. Придайте сводной Таблице 3 наглядный вид:

Ø      используя Формат ячеек выровняйте содержимое ячеек по Горизонтали: по значению, по Вертикали: по центру; установите переносить текст по словам

Ø      установите автоподбор ширины столбцов.

Ø      установите на свой вкус обрамление, заливку цветом, размер и цвет шрифта;

Ø      все денежные значения должны быть отображены без десятых.

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

29. Отобразите скрытый лист таблица 2.

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

31. Вырежьте в соседнюю ячейку из Таблицы 1 всю выручку по Западному региону.

32. Произведите обновление сводной таблицы (проверьте правильность изменений в Таблице 2).

33. Верните Таблицу 1 в исходный вид.

34. Постройте Сводную таблицу 4 на отдельном листе: Вынести в Поле страницы Регион и Наименование товара, в Поле столбца - Месяц, Поле данных - Выручка за проданную продукцию. В качестве Поля строки принять Фамилии продавцов.

35. Примените к полученной таблице автоформат Классический.

36. Установите имя продавца и месяц, который имеет наименьший объем продаж по Западному региону по всем видам продуктов.

37. Скопируйте полученное значение в ячейку наименьшая выручка Таблицы 2. (Правильный ответ Восковой С., Февраль 30 тыс.)

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

39. Используя мастер сводных таблиц, поменяйте формулу для расчета по Полю данные. Вместо общей суммы рассчитайте средний объем продаж.

40. Постройте на отдельном листе Сводную таблицу 3: Вынести в Поле страницы Регион и Наименование товара. Поле данных - Выручка за проданную продукцию. В качестве Поля строки принять Фамилии продавцов и Месяц продаж

41. Переименуйте все листы, на которых находятся сводные таблицы, дав им имена соответствующих таблиц (например, Сводная таблица 1).

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

43. Отобразите Сводную таблицу: Регион - Западный, Товар- мясопродукты.

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

45. Измените вид диаграммы:

Ø      Установите формат заголовка диаграммы - Вид - Заливка - обычная;

Ø      Добавьте легенду,

Ø      Установите подписи значений - Доля.

46. Откройте документ отчет.doc.

47. Создайте гиперссылку c текстом «Лабораторная работа по сводным таблицам» на файл Таблицы.xls.

48. Создайте гиперссылку c текстом «Сайт кафедры ИОМАС» на сайт кафедры ИОМАС (www.iomas.vsau.ru).

49. Проверьте работоспособность гиперссылок.

50. Сохраните изменения в файлах программ Word и Excel.

51. Покажите преподавателю результаты выполнения лабораторных работ по Word и Excel.

52. Закройте файл Таблицы.xls.

53. Закройте документ отчет.doc.

предыдущая | содержание | следующая