Пример выполнения контрольной работы

Контрольная работа выполняется каждым студентом в компьютерном классе в программе MS Excel 2007 в зависимости от варианта задания - последнего номера зачетки.

Рассмотрим выполнение типового варианта задания.

- Запустите программу MS Excel. Откройте файл (если это требуется) «БД списки 07». Откройте лист «Типовой вариант». Для тренировки создайте список согласно условию задания и с учетом правил ведения списков Excel, как показано на рис. 11.1.

- Скопируйте его (см. зад. 2.2 данного пособия) и копию переименуйте в лист с номером «(1)». Это позволит Excel, в дальнейшем автоматически перенумеровать листы для следующих заданий.

Примечания. Просим вас располагать листы слева направо от листа «Типовое задание», а не наоборот.

Рис. 11. 1. Примерный вид исходной таблицы для выполнения типового задания

1. Настраиваемую сортировку осуществите сначала по полю «Месяц», затем по «Продукт», затем по «Цена, руб.». (см. зад. 4.1 данного пособия) - рис. 11.2.

Рис. 11. 2. Задание параметров настраиваемой сортировки

В пользовательском порядке сортировки (см. зад. 4.2 данного пособия) расположите строки списка в следующем порядке: март, февраль, январь.

- Начните сортировку обычным образом. В окне Сортировка выделите столбец «Месяц».

- В раскрывающемся списке Порядок диалогового окна Сортировка выделите опцию Настраиваемый список.

- В появившемся окне Списки в области Элементы списка введите требуемую вам последовательность сортировки по полю «Месяц» (рис. 11.3).

Рис. 11. 3. Задание элементов списка для пользовательской сортировки

- Щелкните мышью по кнопке Добавить, а затем закройте окно кнопкой ОК. Результат создания пользовательского порядка сортировки приведен на рис. 11.4.

Рис. 11. 4. Окно Сортировка с заданным пользовательским порядком

- В окне Сортировка щелкните на кнопке ОК, и ваш список будет отсортирован в том порядке, который вам нужен (рис. 11.5).

Рис. 11. 5. Результат сортировки с заданным пользовательским порядком (фрагмент)

Данный вариант таблицы будет основным для большинства последующих операций.

Рис. 11. 6. Таблица Excel 2007 после выполнения второго задания

- Создайте копию листа «(1)» с именем «(2)».

2. Преобразуйте список листа (2) в таблицу Excel как это было сделано ранее (см. зад. 2.2 данного пособия). На вкладке ленты Вставка в группе Таблица выберите команду Таблица.

- Рассчитайте значения столбца «Сумма, руб.». Обратите внимание, какие имена будут иметь адреса ячеек в созданной вами формуле.

- Для добавления итоговой строки на вкладке Конструктор группы Параметры стилей таблицы установите флажок Строка итогов (см. зад. 2.5 данного пособия).

Результат выполнения второго задания показан на рис. 11.6.

3. На новом рабочем листе «3», cкопированного с листа «2», выполните фильтр по списку значений: для продавцов «Сидоров» и «Иванов» найдите значения столбца «Сумма, руб.» в интервале от 500 и до 2000 руб.

- Для начала рассчитайте значения столбца «Сумма, руб.», используя имена столбцов «Цена, руб.» и «Сумма, руб.».

- На вкладка Формулы группы Определение имен выберите команду Создать из выделенного фрагмента.

- Для написания формулы с использованием именованных столбцов после написания знака «=» в этой же группе команд выберите команду Использовать в формуле и выберите соответствующие имена столбцов. Формула будет выглядеть следующим образом:

- На вкладке Главная в группе Редактирование выберите команду Сортировка и фильтр

- Далее в столбце «Продавец» установите Пользовательский автофильтр, в соответствии с рис. 11.7.

Рис. 11. 7. Пользовательский автофильтр

- Для столбца «Сумма, руб.» задайте выборку «больше или равно 500» И «меньше или равно» 2000.

Результат выполнения третьего задания показан на рис 11.8.

Рис. 11. 8. Результат выполнения третьего задания

4. Перед тем, как создать копию листа «1» выполните в нем любым способом вычисление столбца «Сумма, руб.».

- Для запуска расширенного фильтра сначала необходимо сформировать диапазон условий (рис. 11.10) и диапазон вывода (рис. 10.9 и рис. 10.11).

Рис. 11. 9. Выполнение расширенного фильтра «И» для задания 4

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

- На вкладке Данные в группе Сортировка и фильтр выберите команду Дополнительно (см. зад. 5.3.1 данного пособия). Параметры заполните в соответствии с рис. 11.9.

Рис. 11. 10. Диапазоны условий (критерии выборки) расширенного фильтра для задания 4

- Для расширенного фильтра «ИЛИ» установите параметры, показанные на рис. 11.11.

Рис. 11. 11. Выполнение расширенного фильтра «ИЛИ» для задания 4

5. Скопируйте лист «1». Выполните предварительную сортировку списка по возрастанию поля «Продавец» (см. зад. 4.1 данного пособия)..

- На вкладке Главная группы Структура выберите команду Промежуточные итоги (см. зад. 6.1 данного пособия).

- Заполните параметры в соответствии с рис. 11.12.

Рис. 11. 12. Диалоговое окно Промежуточные итоги для поля «Продавец» задания 5

Результат выполнения операции может выглядеть таким, как он показан на рис. 11.13.

Рис. 11. 13. Результат вычисления промежуточных итогов для задания 5

6. Аналогично пятого задания (см. предыдущий пункт данного пособия) в новом рабочем листе подведите итоги по каждому продукту. Результат выполнения операции показан на рис. 11.14.

Рис. 11. 14. Результат вычисления промежуточных итогов для задания 6

7. Используя данные первого рабочего листа, постройте сводную таблицу (см. зад. 7.2. данного пособия). Поле «Месяц» поместите в область фильтров, «Продавец» в область названия строк, «Продукт» в область названия столбцов, «Сумма, руб.» в область значений

Примечание. Новый лист со сводной таблицей будет создан автоматически, вам только понадобится затем переименовать его в лист «(7)».

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

- Подтвердите выбранный диапазон и укажите, что отчет сводной таблицы будет помещен на новый лист.

- Поместите поля исходного списка в отчет сводной таблицы в соответствии с заданием.

Рис. 11. 15. Список полей сводной таблицы для задания 7

Рис. 11. 16. Представление сводной таблицы для задания 7

Список полей сводной таблицы показан на рис. 11.15, а сама сводная таблица на рис. 11.16.

8. Сводную таблицу задания «7» скопируйте в новый лист – «8».

Примечание. Если вы делаете новую сводную таблицу на основе предыдущей, то старая таблица может быть удалена. В этом случае, вам придется создавать макет новой сводной таблицы из листа «1» или создавать новый лист и копировать в него предыдущую сводную таблицу.

- Для изменения сводной таблицы, поместите указатель на нее и в окне Списки полей сводной таблицы сформируйте новый макет в соответствии с п. 8 типового задания: Поле «Продукт» переместите в строки, поле «Цена» добавьте в столбец (рис. 11.17). Результат приведен на рис. 11.18.

Рис. 11. 17. Список полей сводной таблицы для задания 8

Рис. 11. 18. Сводная таблица для задания 8

9. В этом пункте требуется создать группы по "Продуктам", а именно: группа молочных продуктов (молоко, кефир) и группа хлебобулочных изделий (хлеб, булка).

- Скопируйте лист «(8)». Из названия строк удалите поле «Продавец». В новой сводной таблице выделите, удерживая нажатой клавишу Ctrl, те продукты, которые должны входить в одну группу. Затем нажмите правую кнопку мыши и выберите Группировать (см. зад. 7.2.3 данного пособия).

- Появившуюся структуру "Группа 1" нужно переименовать. Для этого щелкните мышью в ячейку "Группа 1" а затем введите новый текст в строке формул рабочего листа электронной таблицы (рис. 11.19).

Рис. 11. 19. Переименование группы в сводной таблице для задания 9 (фрагмент)

- Попробуйте сформировать «Группа 2». В результате выполнения операции группировки будет сформирована таблица, показанная на рис. 11.20.

Рис. 11. 20. Группировка товаров после переименования

10. Цены по интервалам группируются по аналогии с заданием 8. Можно сформировать две и более группы.

- Вначале создайте список полей новой сводной таблицы «(10)» по макету (рис. 11.21).

Рис. 11. 21. Макет сводной таблицы для задания 10

- Выполните группировку по полю Цена как показано на рис. 11.22.

Рис. 11. 22. Группировка поля Цена сводной таблицы для задания 10

Результат группировки приведен на рис. 11.23.

Рис. 11. 23. Сводная таблица для задания 10

11. Для того чтобы скрыть одного из продавцов в новой таблице «(11)», добавьте в список полей «Продавец» и уберите флажок у нужной фамилии (рис. 11.24). Чтобы восстановить скрытые данные по этому продавцу нужно встать указателем на поле "Продавец" и выберите отображение скрытой фамилии продавца.

Рис. 11. 24. Сокрытие/открытие значения поля сводной таблицы для задания 11

12. Чтобы выполнить скрытие детализирующих данных по одной из групп продуктов (например, Хлебобулочным) скопируйте лист «(9)».

- В новом, двенадцатом листе п оместите указатель мыши в ячейку с названием этой группы, нажмите кнопку , расположенную слева от текста. Ее вид изменится на , а заодно детализация группы исчезнет. После выполнения задания сводная таблица может быть такой, как на рис. 11.25.

Рис. 11. 25. Сводная таблица для задания 12

Примечание. Ту же задачу можно решить двойным щелчком на заголовке группы.

13. Для выполнения текущего задания сформируйте макет сводной таблицы «(13)» следующим образом (рис. 11.26).

Рис. 11. 26. Макет сводной таблицы для задания 13

- Измените операцию Сумма по полю «Продано» на операцию Количество. Для этого щелкните на стрелке справа от наименования поля «Продано, шт.» и в окне Параметры поля значений выберите операцию Количество (рис. 11.27).

Рис. 11. 27. Изменение операции с полем сводной таблицы для задания 13

Результат выполнения данной операции приведен на рис. 11.28.

Рис. 11. 28. Сводная таблица для задания 13

14. Скопируйте предыдущую сводную таблицу в новый лист «(14)».

- Для выполнения данного пункта задания щелкните на любой ячейке с числом, которое надо представить в денежном формате, например, В6.

- В окне Списки полей сводной таблицы щелкните на стрелке справа от наименования поля «Сумма, руб.» и в окне Параметры поля значений щелкните на кнопке Числовой формат. Далее установите денежный формат.

В результате имеем сводную таблицу следующего вида (рис.11.29).

Рис. 11. 29. Сводная таблица для задания 14

15. Используя данные первого рабочего листа, постройте сводную диаграмму (см. зад. 7.4. данного пособия) со следующими параметрами: поле «Месяц» поместите в область фильтров, «Продавец» в область названия строк, «Продукт» в область названия столбцов, «Сумма, руб.» в область значений.

- На вкладке Вставить группы Таблицы выберите команду Сводная диаграмма. Согласитесь с диапазоном и предложением размещения диаграммы на новом листе.

Рис. 11. 30. Сводная диаграмма для задания 15 (1-й вариант)

- Организуйте список полей сводной таблицы, аналогичный п. 7 типового задания.

Будет создана сводная таблица и соответствующая ей сводная диаграмма (рис. 11.30). Ее можно редактировать как обычную диаграмму.

- Измените, список полей сводной таблицы в соответствии с заданием 8.

Сводная таблица и соответствующая ей сводная диаграмма изменятся следующим образом (рис. 11.31).

Рис. 11. 31. Сводная диаграмма для задания 15 (2-й вариант)

16. Скопируйте лист «(1)» в новый лист «(16)».

- В ячейках A23:D25 организуйте область критериев (рис. 11.32). В ячейки А24, С24, А25 и С25 скопируйте содержимое соответствующих ячеек списка, а в ячейки В25 и D25 впишите формулы поиска максимального и минимального значения по столбцу «Сумма, руб.».

а

б

Рис. 11. 32. Область критериев для задания 16 в режиме представления данных (а) и в режиме задания формул (б)

- В ячейках A29:Е31 организуйте область вывода данных. В ячейках, не выделенных цветом, скопируйте соответствующие ячейки списка (рис. 11.33).

Рис. 11. 33. Область вывода результатов расчетов для задания 16

- В ячейку В31 напишите формулу определения наибольшего числа в столбце списка «Сумма» для января (рис. 11.34).

Рис. 11. 34. Диалоговое окно функции ДМАКС для задания 16

- В ячейку С31 напишите формулу извлечения фамилии продавца, который совершил максимальную по стоимости сделку в январе месяце (рис. 11.35).

- Для марта месяца, вычисления выполняются аналогично.

Результат извлечения данных из списка приведен на рис. 11.33.

Рис. 11. 35. Диалоговое окно функции БИЗВЛЕЧЬ для задания 16