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

Ход работы.

Задание 1:

Сохраните созданный  Вами ранее Классный журнал 2 (Ваша фамилия) под именем Классный журнал 3 (Ваша фамилия). Откройте его в Excel (двойным щелчком)

Организуем подведение итогов.

Microsoft Excel может автоматически вычислять промежуточные и общие итоги в списке. При вставке автоматических промежуточных итогов Microsoft Excel изменяет разметку списка (группируя элементы списка и подводя при этом итоги по каждой группе строк), что позволяет отображать и скрывать строки каждого промежуточного итога.

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

Задание 2. Подведите промежуточные итоги по полю Год рождения в списке учеников, вычислив количество учеников определенного возраста.

Для этого:

Создайте новый лист и назовите его Итоги по ученикам.

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

Выполните сортировку скопированного записей списка на листе Итоги по ученикам по полю Год рождения в порядке возрастания

Вставьте промежуточные итоги:

·Выделите список вместе с заголовком

·Из меню Данные выберите команду Итоги ...

·В появившемся диалоговом окне:

·Выберите поле, от которого будет зависеть подведение промежуточных итогов: в списке под заголовком При каждом изменении в выбрать необходимое поле - Год рождения

·Выбрерите операцию, которая будет применяться при этом: в списке под заголовком Операция выбрать операцию - Количество

·Указажите поле, к которому будет применяться операция: в списке под заголовком Добавить итоги по поставить галочку напротив поля - Год рождения

Использование структурированных данных

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

Для этого:

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

Используя «-» для скрытия элементов группы (строки объединяемые линией под этим знаком) данного уровня, сверните все строки с исходными данными

Используя «+» для раскрытия элементов группы данного уровня, раскройте все строки с исходными данными, касающиеся только одного года (год выберите самостоятельно)

То же самое сделайте с другими

Задание 4. Удалите текущие промежуточные итоги

Для этого:

Выделите все строки данного списка вместе со строками промежуточных и общих итогов

Из меню Данные выберите команду Итоги ...

3) В появившемся диалоговом окне нажмите на кнопку  Убрать всеЗадание 5.

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

Консолидация данных

При консолидации данных объединяются значения из нескольких диапазонов данных. Данные должны быть настроены для консолидации:

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

·Разместите каждый диапазон на отдельном листе. Не помещайте диапазоны на лист, на котором должна выполняться консолидация.

·Если консолидация выполняется по положению, убедитесь, что макеты всех диапазонов совпадают.

·Если консолидация выполняется по категории, убедитесь, что подписи столбцов или строк, которые требуется объединить, совпадают с учетом регистра букв.

Задание 6. Создание консолидации данных.

На отдельном листе электронного журнала класса оформить таблицу, содержащую консолидированные (в виде среднего арифметического) оценки учеников класса по всем предметов.

Для этого:

Создайте новый лист и назовите его Консолидация

Скопируйте столбцы номеров и фамилий и имен учеников (с их заголовками!) с листа Сведения об учениках.

В последнем столбце введите название Итоговая оценка 

В меню Данные выберите команду Консолидация.

Выберите из раскрывающегося списка Функция весовую функцию, которую требуется использовать для консолидации данных - Среднее

Если Список диапазонов непуст, то очистите его:

щелкните по любому диапазону в Списке диапазонов,

щелкните по кнопке Удалить.

Создайте свой список диапазонов.

Задайте первый диапазон:

переведите курсор в поле Ссылка,

перейдите на математика, выделите на нем годовые оценки, щелкните по кнопке Добавить, аналогично добавьте данные по другим дисциплинам.

9)Установите флажок  Создавать связи с исходными данными, для того чтобы таблицу консолидации можно было обновлять автоматически при каждом изменении данных в каком-либо исходном диапазоне

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

Нажмите на кнопку Ok

Чтобы понять каким образом происходит подсчет средних значений в итоговых ячейках необходимо раскрыть структуру соответствующей группы (слева, используя В) - Вы увидите ссылки на значения указанных диапазонов, скрытые и используемые таким образом при нахождении итогового значения группы на основе выбранной Вами функции

Задание 7. Самостоятельно дополните таблицу консолидированными средними оценкам и по четвертям.

Сводные таблицы

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

Задание 8. Создание сводной таблицы

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

Для этого

1.Выделите диапазон для создания сводной таблицы: таблицу с данными на листе Сведения об учениках (вместе с заголовком!)

2.Выберите из меню Вставка команду Сводная таблица и выполните шаги для настройки построения сводной таблицы:

а) выберите исходные данные - в списке, и вид отчета - сводная таблица. После этого нажмите кнопку Далее

б) указанный диапазон будет соответствовать диапазону, выделенному Вами ранее (диапазону таблицы Сведения об учениках) 

с) Укажите место создания таблицы: установите переключатель на пункт Новый лист

Откроется макет сводной таблицы

Перетащите в область СТРОКА «кнопку» поля Фамилия

Перетащите в область СТОЛБЕЦ «кнопку» поля Год рождения

Перетащите в область ФИЛЬТР ОТЧЕТА «кнопку» поля Успеваемость

Перетащите в область ЗНАЧЕНИЯ «кнопку» поля №; выберите  в параметрах значений функцию Количество значений; и нажмите кнопку Ok

Замечание.Таким образом, в этой сводной таблице будут сводиться значения по двум полям (Фамилия и Год рождения) и при этом, если для данной Фамилии будет найден соответствующий год рождения, то в ячейке на пересечении этих значений будет вычисляться количество значений по полю № (т.е. с данным порядковым номером - он уникален, как и само сочетание данной фамилии и года рождения в нашей таблице, поэтому нет разницы, что считать: Фамилии или №). При этом можно ограничить вывод фамилий, путем выбора конкретной успеваемости в поле Страница 

Задание 9. Использование сводной таблицы

На основе сводной таблицы отобразить хорошистов выбранного Вами года

Для этого:

В области поля Успеваемость раскройте список и выберите Хорошисты и нажмите Ok

В области поля Год рождения раскройте список и выберите один Год рождения (на ваше усмотрение) и нажмите Ок; результат покажите преподавателю

Макросы в Excel

Если требуется периодическое выполнение задачи в Microsoft Excel, можно автоматизировать задачу с помощью макроса. Макрос - это последовательность команд и функций, хранящаяся в модуле Visual Basic. Ее можно выполнять всякий раз, когда необходимо выполнить данную задачу. Работа с макросами в Microsoft Excel осуществляется через меню Сервис/ Макрос.

Задание 10.Запись макроса. Создайте макрос для автоматической защиты текущего листа.

Для этого

Выберите лист Математика и снимите с него защиту

Включите запись макроса: выберите из меню Сервис/ Макрос команду Начать запись...

a)Укажите имя макроса: Защита_текущего_ листа

Замечание: Не используйте пробелы в имени макроса! (для разделения слов вместо символа пробела используйте символ подчеркивания - «_»)

b)Укажите сочетание клавиш для его вызова: в свободный квадрат напротив сочетания … установите курсор и нажмите клавишу с изображением символа Я (назначив таким образом сочетание CTRL+Я)

c)Нажмите кнопку Ok, для начала записи макроса

Выполните ряд действий по защите выбранного листа

Остановите запись макроса: выберите из меню Сервис/ Макрос команду Остановить запись...

Задание 11. Самостоятельно создайте макрос для автоматической снятия с защиты текущего листа 

Задание 12. Использование созданного макроса.

Создайте на листе Математика кнопки в виде двух овалов с тенью и надписями: Защита листа и Снятие защиты с листа. Назначьте им созданные ранее Вами макросы.

Для этого.

Используя Панель рисования разместите на листе Математика два овала и назначьте им тень

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

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

Проверьте данные кнопки в действии. Обратите внимание на один из недостатков: пароль у Вас более не запрашивается, т.е. данный способ может применяться только в случае, если нужно защитить данные листа от непреднамеренного изменения (без пароля!)

Скопируйте созданные кнопки на другие листы с предметами

Задание 13. Самостоятельно создайте четыре макроса и назначьте им кнопкам на Титульном листе:

Действие 1: Смена фона заливки

Действие 2: Изменение вида объекта WordArt

Действие 3: Выборка хорошистов на листе Сведения об учениках и копирование этих сведений на титульный лист

4)Действие 4: Очистка области на Титульном листе от сведений, скопированных в предыдущем здании.