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

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

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

Запустите программу MS Excel. Создайте файл "Типовой вариант - списки.xls"). Переименуйте лист1 рабочей книги на Исходный список. Создайте список согласно условию задания и с учетом правил ведения списков Excel, как показано ниже.

1. Простую сортировку осуществите через команды меню Данные / Сортировка / ОК - п. 1.1 данного пособия (рис. 8.1):

Рис. 8. 1. Сортировка таблицы для задания 1

Установите пользовательский порядок сортировки: март, февраль, январь – п. 1.2. данного пособия. Сервис / Параметры / вкладка Списки. Затем установите параметры как на рис. 8.2.

Рис. 8. 2. Установка пользовательских параметров сортировки для задания 1

Для выполнения сортировки выделите команду Данные / Сортировка / Параметры / Сортировка по первому ключу / выберите последовательность в соответствии с рис. 8.3.

Рис. 8. 3. Задание пользовательских параметров сортировки для задания 1

1. Создайте копию листа "Типовой вариант" и назовите его "Автофильтр". Копирование данных осуществляется в соответствии с п. 2.1.1 данного пособия.

Для добавления нового листа выделите команду Вставка / Лист. Выделите лист целиком, щелчком на нулевую ячейку. Затем в листе "Типовой вариант" выделите команду Копировать. Вставьте

выделенную таблицу в ячейку А1 листа Автофильтр командой Вставить.

Щелкните правой кнопкой мыши на ярлыке нового листа, выберите команду Переименовать и присвойте листу название "Автофильтр".

Для создания автофильтра (п. 2.1.1 данного пособия) выделите команды Данные / Фильтр / Автофильтр. Ограничение на цену выбираете в строке условие, находясь в автофильтре (рис. 8.4).

Рис. 8. 4. Задание пользовательского автофильтра для задания 2

2. Создайте копию листа "Типовой вариант" и назовите его "Пользовательский фильтр".

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

Рис. 8. 5. Диапазон условий (критерий выборки) пользовательского фильтра для задания 3

Названия столбцов должны быть идентичны названиям столбцов в исходной таблице.

Выделите команды Данные / Фильтр / Расширенный фильтр и заполняете параметры в соответствии с рис. 8.6.

Рис. 8. 6. Выполнение пользовательского фильтра для задания 3

3. Создайте копию листа "Типовой вариант" и назовите его "Итоги - продавцы".

Отсортируйте таблицу по полю "Продавец" (п.1.1 данного пособия). Далее выделите команды меню Данные / Итоги (п. 3.1 данного пособия). Заполните параметры в соответствии с рис. 8.7.

Рис. 8. 7. Диалоговое окно Промежуточные итоги для задания 4

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

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

4. Аналогично четвертого задания подведите промежуточные итоги (п. 3.1 данного пособия) в новом рабочем листе "Итоги - продукты". Результат выполнения операции показан на рис. 8.9.

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

5. Для выполнения шестого задания в листе "Типовой вариант" вызовите меню Данные / Сводная таблица. Следуйте предлагаемым шагам (п. 4.1 данного задания). Макет сводной таблицы оформите следующим образом (рис. 8.10):

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

В результате вы должны получить сводную таблицу следующего вида (рис.8.11):

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

Полученный лист назовите "Свод-1".

6. Для изменения сводной таблицы, поместите указатель на нее и вызовите мастер сводных таблиц (правая кнопка мыши) и сформируйте новый макет в соответствии с п. 7 типового задания: Поле "Продукт" переместите в строки, поле "Цена" добавьте в столбец (рис. 8.12). Результат приведен на рис. 8.13.

Рис. 8. 12. Макет сводной таблицы для задания 7

Рис. 8. 13. Фрагмент сводной таблицы для задания 7

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

7. В этом пункте требуется создать группы по "Продуктам", а именно: группа молочных продуктов (молоко, кефир) и группа хлебобулочных изделий (хлеб, булка). Для этого выделите в сводной таблице "Свод -2", удерживая нажатой клавишу Ctrl, те продукты, которые должны входить в одну группу. Затем нажмите правую кнопку мыши и выберите Группа и структура / Группировать (рис. 8.14).

Рис. 8. 14. Создание группы в сводной таблице для задания 8

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

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

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

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

8. Цены по интервалам группируются по аналогии с заданием 8. Можно сформировать две и более группы. Вначале создайте новую сводную таблицу "Свод 3" по макету (рис. 8.17).

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

Выполните группировку по полю Цена как показано на рис. 8.18. Результат группировки приведен на рис. 8.19.

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

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

9. Для того чтобы скрыть в сводной таблице (п.4.5) "Свод-1" одного из продавцов, необходимо навести указатель мыши на имя поля, и убрать флажок у нужной фамилии (рис. 8.20) или, нажмите правой кнопкой мыши на фамилии Иванов выберите пункт меню "Скрыть". Чтобы восстановить скрытые данные по этому продавцу нужно встать указателем на поле "Продавец" и выберите отображение скрытой фамилии продавца.

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

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

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

скопируйте лист "Свод-2" на лист "Свод-4". Поместите указатель мыши в ячейку с названием этой группы, нажмите правую кнопку мыши, выберите меню Группа и структура / Скрыть детали. После выполнения задания примерный вид макета и сводной таблицы может быть таким как на рис. 8.21 и 8.22.

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

11. Для выполнения текущего задания сформируйте макет сводной таблицы "Свод-5" следующим образом (рис. 8.23).

Рис. 8. 23. Макет сводной таблицы для задания 12

Измените операцию Сумма по полю "Продано" на операцию Максимум по этому же полю дважды щелкнув левой кнопкой мыши по кнопке поля "Сумма по полю Продано" (рис. 8.24).

Рис. 8. 24. Изменение операции с полем сводной таблицы для задания 12

12. Для выполнения данного пункта задания выделите "Сумма" во всей сводной таблице "Свод-5", затем выделите команды меню Формат / "Ячейки" / закладка Число / Денежный. В результате имеем сводную таблицу следующего вида (рис.8.25).

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

13. Создайте новый рабочий лист "Функции баз данных" и туда скопируйте лист "Типовой вариант".

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

а

б

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

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

Рис. 8. 27. Область вывода результатов расчетов для задания 14

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

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

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

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

Рис. 8. 28. Диалоговое окно функции ДМАКС для задания 14

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

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

Рис. 8. 30. Результат выполнения задания14

Видите, какой у нас молодец Сидоров. А кто отличился в феврале?