Фильтрация записей с помощью функции расширенного фильтра

1. Создайте книгу с именем Оценки. На Листе1 создайте таблицу, согласно образца. Переименовать Лист1 в Исходная таблица.

. Фильтрация записей с помощью функции расширенного фильтра - student2.ru

2. Проведите подготовительную работу — переименуйте новый Лист2 на Расширенный фильтр1 и скопируйте на него исходную таблицу.

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

§ Скопируйте все имена столбцов в другую область на том же листе, например установив курсор в ячейку Н3. Это область, где будут формироваться усло­вие отбора записей.

§ Сформируйте в области условий отбора Критерии сравнения (диапазон Н4:I4) — об оценках по физике и математике. Для этого в первую строку после имен полей введите:

- в столбец Физика - 4;

- в столбец Математика - 3;

§ Произведите фильтрацию записей на том же листе:

- установите курсор в область исходных данных;

- выполните команду Дополнительно на вкладке Данные;

- в диалоговом окне «Расширенный фильтр» с помощью мыши задайте следующие пара­метры, например:

Фильтрация записей с помощью функции расширенного фильтра - student2.ru

§ нажмите кнопку <ОК>,

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. Переименуйте листы, дав им соответствующие имена (Янв, Фев, Мар).

Фильтрация записей с помощью функции расширенного фильтра - student2.ru

Рис. 1

2. Вставьте новый лист, присвоив ему имя Консолидация. Скопируйте в него заголовок таблицы и откорректируйте его соответствующим образом (рис. 3). Установите курсор в первую свободную ячейку (А3).

Фильтрация записей с помощью функции расширенного фильтра - student2.ru

Рис. 2

§ Активизируйте диалоговое окно Консолидация с помощью команд Данные – Работа с данными - Консолидация, и, последовательно указывая в поле Ссылка необходимые адреса консолидируемых областей, сформируйте их полный список, состоящий из трех записей, как представлено на рис. 2.

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

§ Выполните консолидацию. Сравните полученные результаты с приведенными на рис. 3. Фильтрация записей с помощью функции расширенного фильтра - student2.ru Фильтрация записей с помощью функции расширенного фильтра - student2.ru Фильтрация записей с помощью функции расширенного фильтра - student2.ru Фильтрация записей с помощью функции расширенного фильтра - student2.ru

Рис. 3

§ Просмотрите созданную структуру, последовательно показывая или скрывая уровни этой структуры. Откройте второй (внутренний) уровень для поставщиков из С.Петербурга и Череповца. Сравните полученный результат с представленным на рис. 4.

Фильтрация записей с помощью функции расширенного фильтра - student2.ru

Рис. 4

§ Пользуясь командами Формулы – Зависимости формул – Влияющие ячейки, проследите влияющие ячейки для ячеек С7, С10, С38. Убедитесь в правильности полученных результатов.

§ Раскройте структуру для первых трех консолидированных данных и просмотрите формулы в столбце С (Формулы – Зависимости формул – Влияющие ячейки – Показать формулы).Верните отображение результатов вычислений.

§ Меняя данные в ячейках листов "Янв", "Фев", "Мар", проследите за автоматическим пересчетом общей итоговой суммы (ячейка С38) и частичных сумм в ячейках С10, С26 и т.д.

2. Откройте новую книгу и создайте в ней одну таблицу, имеющую аналогичную предыдущим структуру и содержащую данные за второй квартал. Образец такой таблицы на рис. 5. Назовите лист с таблицей "2кварт". Сохраните созданную книгу под именем Имя_11_2.

Фильтрация записей с помощью функции расширенного фильтра - student2.ru

Рис. 5

§ Сверните окно рабочей книги.

§ На новом листе книги Имя_11_1 выполните консолидацию четырех диапазонов ячеек - трех из листов "Янв", "Фев", "Мар" книги Имя_11_1, а четвертого из соответствующего диапазона книги Имя_11_2 листа "2кварт". Пользуйтесь кнопкой Обзор диалогового окна Консолидация. Обратите внимание на структуру ссылки при задании области консолидации из неактивной книги. Проверьте результат и сравните его с тем, что представлен на рис. 6.

Фильтрация записей с помощью функции расширенного фильтра - student2.ru

Рис. 6

§ Закройте книгу Имя_11_2. Обратите внимание на структуру ссылки при задании области консолидации из закрытой книги.

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

3. Добавьте в книгу еще один лист, именовав его как "Конс_данные2", и выполните на этом листе консолидацию данных, расположенных в таблицах листов "Янв", "Фев", "Мар" и "Апр" (структура таблицы листа "Апр" приведена на рис. 8), обратив внимание на задание консолидируемой области для листа "Апр".

Фильтрация записей с помощью функции расширенного фильтра - student2.ru

Рис. 7

§ Проверьте правильность структуры таблицы на листе "Конс_данные2", сравнив ее с представленной на рис. 9.

Фильтрация записей с помощью функции расширенного фильтра - student2.ru

Рис. 8 Рис .9

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

Цель:Научиться создавать сводные таблицы.

КРАТКАЯ СПРАВКА

Команда Вставка – Таблицы – Сводная таблица вызывает Мастера сводных таблиц для построения сводов — итогов определенных видов на основании данных списков, других сводных таблиц, внешних баз данных, нескольких разрозненных областей данных электронной таб­лицы Excel. Сводная таблица обеспечивает различные способы агрегирования инфор­мации.

Фильтрация записей с помощью функции расширенного фильтра - student2.ru

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

1. Указать вид источника сводной таблицы:

§ использование таблицы или диапазона;

§ использование внешнего источника данных;

2. Указать диапазон ячеек, содержащего исходные донные.Полное имядиапазона ячеек записываете в виде

Наши рекомендации