Создание сводных таблиц и диаграмм

4.1. Используя таблицу на листе ПродИрис, создайте сводную таблицу суммарной стоимости для конкретных наименований проданных товаров каждым из продавцов с размещением ее на новом листе. Для этого в макете сводной таблицы перетащите мышью: поле Имя – в область столбцов, поле Наименование проданного товара – в область строк, поле Стоимость – в область данных.

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

4.3. Переименуйте лист с этой сводной таблицей на СВ1 и переместите этот лист в конец после листа Итоги2. Сохраните изменения в файле.

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

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

4.6. Переименуйте лист с этой сводной таблицей на СВ2 и переместите его в конец после листа СВ1. Сохраните изменения в файле.

4.7. По данным сводной таблице на листе СВ2 создайте сводную диаграмму.

Выберите из выпадающего списка для критерия Состояние в верхнем левом углу диаграммы вместо значения Продано сначала значение Возвращено, а затем значение Все и проследите, как при этом будет изменяться вид диаграммы.

4.8. По данным сводной таблице на листе СВ2 создайте еще одну сводную диаграмму с типом Линейчатая ® Объемный вариант нормированной линейчатой диаграммы (лист Диаграмма2).

4.9. Скопируйте лист СВ2 с его содержимым и переименуйте копию на СВ3.

На листе СВ3 замените поле Количество в области данных на поле Стоимость, а в области страницы для поля Состояние выберите из списка значение Все.

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

Поменяйте местами поля Дата и Имя. Сохраните изменения в файле.

4.10. Скопируйте лист СВ3 со всем его содержимым и переименуйте копию на СВ4. На листе СВ4 перетащите поле Имя из области столбцов в область страницы правее поля Состояние. Кроме этого, в область страницы перетащите поле Дата, а в область столбцов вынесите поле Количество. Такая реализация сводной таблицы представлена на рис. 6.3, а.

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

03.05.2015 (рис. 6.3, б).

а)

б)

Рис. 6.3. Сводные таблицы по нескольким критериям:

а) по обощенным данным; б) после уточнения категорий

Таким образом, работая с большими сводными таблицами можно их упрощать, фильтруя часть информации.

4.12. Сохраните изменения в файле Фамилия_6_n.xls.

5. Функции категории "Работа с базой данных"

5.1. Создайте новый лист с именем Функции. Скопируйте на этот лист исходную таблицу с листа ПродИрис.

5.2. Поскольку для функций категории Работа с базой данных надо подготовить диапазон условий, скопируйте в ячейку А17 название столбца Состояние, а в ячейку В17 – название столбца Цена. В ячейку А18 введите или скопируйте значение "Возвращено", а в ячейку В18 введите значение ">=5000" (без кавычек).

5.3. Подпишите ячейку А20, вписав в нее текст "Стоимость возвращенных", а ниже в ячейке А21 с помощью функции БДСУММ вычислите суммарную стоимость всех возвращенных товаров.

Напомним, что подобную задачу Вы уже решали в лабораторной работе № 2 в п. 2.8. – Можете сравнить используемые средства и полученные результаты.

5.4. В ячейке А24 создайте функцию БДСУММ для вычисления суммарного количества только возвращенных товаров с ценой свыше 5000 тг. и подпишите соответствующим текстом ячейку А23.

5.5. Вычислите количество сделок по товарам с наименованиями Программное обеспечение и Клавиатура. Для этого сформируйте соответствующее условие: в ячейку Н17 скопируйте название столбца Наименование проданного товара, а в ячейки Н18 и Н19 – Программное обеспечение и Клавиатура. Подпишите соответствующим образом ячейку Н20 и вставьте в ячейку Н21 функцию БСЧЕТ, которая подсчитывает количество числовых ячеек в указанном столбце по заданному критерию.

5.6. Вычислите количество сделок по товарам с ценой в диапазоне значений от 5000 до 10000 тг. Для этого скопируйте в ячейку С17 название столбца Цена, а в ячейку С18 впишите условие "<=10000" (без кавычек). Таким образом, критерий отбора будет состоять из двух условий проверки границ ценового диапазона, и оба эти условия в данном случае должны выполняться одновременно. Подпишите соответствующим образом ячейку А26 и создайте в ячейке А27 функцию БСЧЕТ для вычисления количества сделок по товарам с ценой от 5000 до 10000 тг.

5.7. Для определения максимальной стоимости сделки, состоявшейся в конкретный день, например 03.04.2015, скопируйте в ячейку D17 название столбца Дата, а в ячейку D18 – 03.04.2015. Подпишите соответствующим образом ячейку D20 и вставьте ячейку D21 функцию ДМАКС для определения максимальной стоимости сделки.

5.8. Определите имя продавца, совершившего сделку с максимальной стоимостью, воспользовавшись функцией БИЗВЛЕЧЬ. Для критерия отбора скопируйте в ячейку Е17 название столбца Стоимость, а в ячейку Е18 вставьте функцию =МАКС(F2:F15). Подпишите соответствующим образом ячейку Н23 и создайте в ячейке Н24 необходимую формулу с функцией БИЗВЛЕЧЬ.

5.9. Выполните разметку страницы таким образом, чтобы вся информация на ней занимала только одну страницу.

5.10. Сохраните изменения в файле Фамилия_6_n.xls и закройте все файлы.

5.11. Устно проработайте ответы на все контрольные вопросы и письменно ответьте на контрольный вопрос, соответствующий номеру Вашего варианта.__

Контрольные вопросы

1) Какие операции обработки таблиц БД имеются в Excel?

2) В чем отличие применения команды Данные  Сортировка от применения кнопок сортировки на панели инструментов?

3) Какие условия отбора записей можно задавать в пункте Условие и в диалоговом окне Пользовательский автофильтр?

4) Какое предназначение имеет автофильтр?

5) Для каких целей используют расширенный фильтр?

6) Какие возможные значения имеет поле Обработка в диалоговом окне Расширенный фильтр? В чем особенность их применения?

7) Как пользоваться командой Автофильтр?

8) Что такое фильтр? Какие виды фильтров Вы знаете?

9) Каков порядок применения расширенного фильтра?

10) Как отменить результаты фильтрации?

11) В чем отличие применения расширенного фильтра от автофильтра?

12) Для чего используются промежуточные итоги?

13) Какие итоговые операции существуют для организации промежуточных итогов?

14) Какие требования выдвигаются к столбцу выбираемому из списка При каждом изменении в диалоговом окне Промежуточные итоги?

15) Каков порядок применения промежуточных итогов?

16) Как сменить итоговую операцию, которая используется "по умолчанию" при подведении промежуточных итогов?

17) Что будет вычисляться, если задать функцию суммирования по столбцу, содержащему текстовые данные, например, по столбцу "Фамилия"?

18) Как свернуть/развернуть уровни группировки?

19) Как отменить промежуточные итоги?

20) Что такое сводные таблицы? Для чего они применяются?

21) Перечислите существующие итоговые операции в сводных таблицах.

22) Опишите порядок создания сводной диаграммы.

23) Чем сводная диаграмма отличается от обычной диаграммы?

24) Как изменить тип сводной диаграммы?

25) С помощью какого пункта меню формируется сводная таблица?

26) Какие надо соблюдать правила при создании сводной таблицы?

27) Каким образом можно изменять состав сводной таблицы?

28) Из каких элементов состоят сводные таблицы?

29) На основе каких источников возможно создание сводных таблиц?

30) Как создать сводную таблицу в MS Excel при наличии готовой таблицы? И как можно изменить структуру полученной сводной таблицы (имеется в виду положение полей)?

Рекомендуемая литература

Обязательная:

1. Симонович С.В. Информатика базовый курс: Учебник для вузов. 3-е изд. Стандарт 3-го поколения.-Спб.: Питер, 2012-640с.

2. Ульрих Л.А. Электронные таблицы Microsoft Excel. Проблемы и решения: Практич. пособие / Пер. с англ. – Издательство ЭКОМ, 2002, 400 с.

3. Попов А.М. Информатика и математика для юристов: учебник для вузов /А.М. Попов, В.Н. Сотников, Е.И. Нагаева. - М.: ЮНИТИ-ДАНА, 2011. - 391 с.

4. Могилев А.В. Информатика: учебное пособие для вузов/А.В. Могилев, Н.И. Пак, Е.К. Хеннер. - 8-е изд., стер. - М. : Академия, 2012. - 841 с.

5. Острейковский В.А. Информатика: учебник для вузов/Острейковский В.А. - М.: Высш. школа, 2001. - 511 с.

6. Гринберг А.С. Информационные технологии моделирования процессов управления экономикой: учебное пособие для вузов/Гринберг А.С., Шестаков В.М. - М.: ЮНИТИ-ДАНА, 2003. - 399с.

7. Каймин В.А. Информатика: учебник для вузов/Каймин В.А. - 3-е изд. - М.: ИНФРА-М, 2003. - 272с.

8. Беленький П.П. Информатика: учебное пособие для сред. спец. учеб. заведений/под ред. Беленького П.П. - Р н/Д: Феникс, 2003. - 448 с.

9. Горячев А.В. Практикум по информационным технологиям/Горячев А.В., Шафрин Ю.А. - М.: БИНОМ, 2003. - 272с.

10. Степанов А.Н. Информатика: учебное пособие для вузов/Степанов А.Н. - СПб.: Питер, 2005. - 684с.

Дополнительная:

1. Копыл В.И. Курсовая работа на компьютере / В.И. Копыл. – Мн.: Харвест, 2003. – 48 с. (Какие кнопки нажимать).

2. Шафрин Ю. Информационные технологии: в 2-х ч. Учебное пособие для 7-11 кл./Ю. Шафрин - М.: Лаборатория базовых знаний, 1999.

3. Богатов, Д. Ф. Информатика и математика для юристов: Краткий курс в таблицах и схемах: учебное пособие/Под ред. В.А. Минаева. - М.: ПРИОР, 1998. - 224с.

4. Рыжиков Ю.И. Информатика: лекции и практикум: Учебное пособие/ Рыжиков Ю.И. - СПб.: КОРОНА принт, 2000. - 256с.