Фильтрация записей с помощью функции расширенного фильтра
1. Создайте книгу с именем Оценки. На Листе1 создайте таблицу, согласно образца. Переименовать Лист1 в Исходная таблица.
. 
2. Проведите подготовительную работу — переименуйте новый Лист2 на Расширенный фильтр1 и скопируйте на него исходную таблицу.
3. Используя расширенный фильтр, найти студентов, имеющих тройки по математике и четверки по физике. Для этого необходимо:
§ Скопируйте все имена столбцов в другую область на том же листе, например установив курсор в ячейку Н3. Это область, где будут формироваться условие отбора записей.
§ Сформируйте в области условий отбора Критерии сравнения (диапазон Н4:I4) — об оценках по физике и математике. Для этого в первую строку после имен полей введите:
- в столбец Физика - 4;
- в столбец Математика - 3;
§ Произведите фильтрацию записей на том же листе:
- установите курсор в область исходных данных;
- выполните команду Дополнительно на вкладке Данные;
- в диалоговом окне «Расширенный фильтр» с помощью мыши задайте следующие параметры, например:
§ нажмите кнопку <ОК>,
5. Дополнительное задание. Определив область для извлечения данных под таблицей, найдите студентов, имеющих:
§ тройки по математике;
§ двойки по математике, но пять по физике и средний балл больше 3,5 (предварительно добавив в таблицу столбец "Ср. балл");
§ тройки по математике или тройки по физике;
§ двойку по любому предмету (хотя бы одну).
§ средний балл меньше 4;
§ средний балл больше, чем 3,5 и оценку по математике больше 3;
§ средний балл не меньше, чем 4,5 или меньше 4, но по биологии - 5;
§ средний балл больше, чем 3,5, но меньше 4.
§ не имеющих двоек;
§ имеющих хотя бы одну двойку.
§ не имеющих двоек и имеющих средний балл не меньше 4;
Примечание. Каждое задание выполнять на новом листе.
Консолидация данных.
Цель:Знакомство с механизмом консолидации данных.
КРАТКАЯ СПРАВКА
Одним из способов получения итоговой информации является консолидация данных, которая выполняется в соответствии с выбранной функцией обработки.
Консолидация — агрегирование (объединение) данных, представленных в исходных областях-источниках.
Результат консолидации находится в области-назначения. Таблица консолидации создаётся путём применения функции обработки к исходным значениям. Области-источники могут находиться на различных листах или рабочих книгах. В консолидации может участвовать 255 областей-источников.
| ОБЛАСТЬ-НАЗНАЧЕНИЯ |
| КОНСОЛИДИРОВАННАЯ ТАБЛИЦА |
| КНИГА 1,ЛИСТ 1 |
| ТАБЛИЦА1 1 |
| КНИГА 1, ЛИСТ 10 |
| КНИГА 2 , ЛИСТ5 |
| ФАЙЛ |
| ТАБЛИЦА 8 |
| ТАБЛИЦА 3 33 |
| ТАБЛИЦА 5 |
Существуют следующие варианты консолидации данных:
§ с помощью формул, где используются ссылки;
§ по расположению данных для одинаково организованных областей-источников (фиксированное расположение);
§ по категориям для различающихся по своей структуре области данных;
§ с помощью сводной таблицы;
§ консолидация внешних данных.
При консолидации данных с помощью формулиспользуемые в них ссылки могут иметь разное представление в зависимости от взаимного расположения областей-источников и области-назначения:
§ все области на одном листе - в ссылках указывается адрес блока ячеек, например D1: C8;
§ области на разных листах - в ссылках указывается название листа, диапазон, например
o лист 1 !D1: лист2! C8
§ области в разных книгах, на разных листах - в ссылках указывается название книги, название листа, диапазон, например [книга1] лист1!D1: [книга2] лист2!С8.
При консолидации поположениюданных все источники имеют одинаковое расположение данных источников (имена категорий данных в выделяемые области-источники не включаются). Данные имеют одинаковую структуру, фиксированное расположение ячеек и могут быть консолидированы с определенной функцией обработки (среднее значение; максимальное, минимальное и т.п.) по их расположению. Дня консолидации данных курсор устанавливается в область места назначения. Выполняется команда Данные, Консолидация выбирается вариант и задаются условия консолидации.
При консолидации по категориям области-источники содержат однотипные данные, но организованные в различных областях-источниках неодинаково. Для консолидации данных по категориям используются имена строк и/или столбцов (имена включаются в выделенные области-источники). Выполняется команда Данные, Консолидация, выбирается вариант и задаются условия консолидации.
Условия консолидации задаются в диалоговом окне «Консолидация». В окне «Функция» выбирается функция консолидации данных. Для каждой области-источника строится ссылка, для чего курсор устанавливается в поле ссылки, затем переходят в область- источника для выделения блока ячеек и нажимается кнопка<Добавить>.
При консолидации по категориям область имен входит в выделение, флажки подписи верхней строки или значения левого столбца. Excel автоматически переносит эти имена в область назначения.
Переключатель Создавать связи с исходными данными создает при консолидации связи области назначения к областям-источникам. При изменениях в области назначения автоматически обновляются результаты консолидации.
Внимание!Нельзя корректировать ссылки на области-источники (добавлять или удалять новые области-источники) при наличии флажка переключатели Создавать связи с исходными данными.
В окне «Список диапазонов» для текущего рабочего листа итогов консолидации перечислены ссылки на все области-источники. Ссылки можно модифицировать: добавить новые области-источника, удалить существующие области-источника либо изменить его конфигурацию, если только до этого не был выбран переключатель Создавать связь с исходными данными.
Для одного листа итогов консолидации набор ссылок на области-источники постоянен, на нем можно построить несколько видов консолидации с помощью различных функций. Курсор переставляется в новое место, выполняется команда Данные, Консолидация, выбирается другая функция для получения сводной информации
ТЕХНОЛОГИЯ РАБОТЫ
1. В новой книге с именем Консолидация создайте три таблицы, содержащие сведения о поставляемых товарах, по образцу, приведенному на рис. 1. Для каждого месяца первого квартала на отдельном листе новой книги создается собственная таблица с названием "Поставки товаров в месяце", где месяц - январь, февраль, март. При создании таблиц пользуйтесь режимом "группового заполнения листов" или копирования данных.
§ Переменная часть таблиц (столбцы "Объем" и "Дата") должна соответствовать данным, приведенным на рис. 1. Переименуйте листы, дав им соответствующие имена (Янв, Фев, Мар).
Рис. 1
2. Вставьте новый лист, присвоив ему имя Консолидация. Скопируйте в него заголовок таблицы и откорректируйте его соответствующим образом (рис. 3). Установите курсор в первую свободную ячейку (А3).
Рис. 2
§ Активизируйте диалоговое окно Консолидация с помощью команд Данные – Работа с данными - Консолидация, и, последовательно указывая в поле Ссылка необходимые адреса консолидируемых областей, сформируйте их полный список, состоящий из трех записей, как представлено на рис. 2.
§ В поле Функция оставьте функцию вычисления суммы и укажите, что в качестве имен (названий строк) будут выбираться данные из левого столбца (А) консолидируемой области. Укажите на необходимость создания динамической связи с исходными данными.
§ Выполните консолидацию. Сравните полученные результаты с приведенными на рис. 3. 
Рис. 3
§ Просмотрите созданную структуру, последовательно показывая или скрывая уровни этой структуры. Откройте второй (внутренний) уровень для поставщиков из С.Петербурга и Череповца. Сравните полученный результат с представленным на рис. 4.
Рис. 4
§ Пользуясь командами Формулы – Зависимости формул – Влияющие ячейки, проследите влияющие ячейки для ячеек С7, С10, С38. Убедитесь в правильности полученных результатов.
§ Раскройте структуру для первых трех консолидированных данных и просмотрите формулы в столбце С (Формулы – Зависимости формул – Влияющие ячейки – Показать формулы).Верните отображение результатов вычислений.
§ Меняя данные в ячейках листов "Янв", "Фев", "Мар", проследите за автоматическим пересчетом общей итоговой суммы (ячейка С38) и частичных сумм в ячейках С10, С26 и т.д.
2. Откройте новую книгу и создайте в ней одну таблицу, имеющую аналогичную предыдущим структуру и содержащую данные за второй квартал. Образец такой таблицы на рис. 5. Назовите лист с таблицей "2кварт". Сохраните созданную книгу под именем Имя_11_2.
Рис. 5
§ Сверните окно рабочей книги.
§ На новом листе книги Имя_11_1 выполните консолидацию четырех диапазонов ячеек - трех из листов "Янв", "Фев", "Мар" книги Имя_11_1, а четвертого из соответствующего диапазона книги Имя_11_2 листа "2кварт". Пользуйтесь кнопкой Обзор диалогового окна Консолидация. Обратите внимание на структуру ссылки при задании области консолидации из неактивной книги. Проверьте результат и сравните его с тем, что представлен на рис. 6.
Рис. 6
§ Закройте книгу Имя_11_2. Обратите внимание на структуру ссылки при задании области консолидации из закрытой книги.
§ Выполните консолидацию данных из четырех таблиц, но задав в качестве обработки данных вычисление среднего значения консолидируемых данных. Сравните полученный результат с представленным на рис. 7.
3. Добавьте в книгу еще один лист, именовав его как "Конс_данные2", и выполните на этом листе консолидацию данных, расположенных в таблицах листов "Янв", "Фев", "Мар" и "Апр" (структура таблицы листа "Апр" приведена на рис. 8), обратив внимание на задание консолидируемой области для листа "Апр".
Рис. 7
§ Проверьте правильность структуры таблицы на листе "Конс_данные2", сравнив ее с представленной на рис. 9.
Рис. 8 Рис .9
Сводные таблицы.
Цель:Научиться создавать сводные таблицы.
КРАТКАЯ СПРАВКА
Команда Вставка – Таблицы – Сводная таблица вызывает Мастера сводных таблиц для построения сводов — итогов определенных видов на основании данных списков, других сводных таблиц, внешних баз данных, нескольких разрозненных областей данных электронной таблицы Excel. Сводная таблица обеспечивает различные способы агрегирования информации.
Используя окно Создание сводной таблицы, необходимо:
1. Указать вид источника сводной таблицы:
§ использование таблицы или диапазона;
§ использование внешнего источника данных;
2. Указать диапазон ячеек, содержащего исходные донные.Полное имядиапазона ячеек записываете в виде