Отбор строк списка с помощью расширенного фильтра

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

Функция расширенного фильтра отбирает строки списка в соответствии с заданными условиями отбора. Интервал ячеек рабочего листа, где задается логико-арифметическое выражение условия отбора, называется диапазоном критериев. При построении диапазона критериев нужно учитывать следующие рекомендации:

- диапазон критериев располагается в любой свободной части рабочего листа,

- он включает только те метки полей списка, которые входят в критерий отбора строк,

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

- под ячейкой с именем столбца в диапазоне критериев задается условие отбора по примеру: конкретное значение ячейки списка или значние с оператором сравнения. Для текстовых значений допускается задание образцов с символами: “*” (маскирует любую последовательность символов в поисковом поле) и “?” (маскирует один символ поискового поля). Например, образец по примеру: “АО*” задает отбор всех значений поля, которые начинаются с символов “АО”. Операторы сравнения: “>“ – больше, “>=“ – больше или равно, “<“ – меньше, “<=“ – меньше или равно, ”<>“ – не равно, ”=“ – равно. Пустая клетка в диапазоне критериев означает отсутствие условия, такому условию соответствует любое значение соответствующего поля списка,

- по диапазону критериев создается условие отбора - логико‑арифметическое выражение в дизъюнктивно - нормальной форме. Схема построения условия отбора: критерии отбора по примеру, заданные в одной строке диапазона критериев, объединяются операцией конъюнкции (логическое умножение - “и”), а результаты этого объединения в разных строках диапазона критериев объединяются операцией дизъюнкции (логическая сложение - “или”).

Задание 1. С помощью функции расширенного фильтра отобрать строки списка, соответствующие операциям, в которых покупателем ценных бумаг была инвестиционная компания “Новинка”, а продавцом - инвестиционная компания “Крылья”, кроме того, такие операции списка, в которых принимали участие ценные бумаги любого товарищества с ограниченной ответственностью (ТОО), выпущенные в декабре 2007 года.

Указания.

1. Скопировать исходные данные на новый лист, листу присвоить имя – РасширФильтр1.

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

3. На свободном месте рабочего листа сформировать диапазон условий. Скопировать метки столбцов списка: в ячейку P1 - Инвестор - продавец, в ячейку Q1 - Инвестор-покупатель, в R1 - Дата эмиссии, в S1 - Дата эмиссии, в T1 - Эмитент. 4. Для записи условия отбора строк списка в ДНФ в MS Excel применяет способ записи выражения «по образцу». Определить значения ячеек диапазона условий (Рис. 10. 2): - условие: «Новинка» – покупатель, а «Крылья» - продавец записывается в диапазоне условий так: в ячейку P2 записать значение «Крылья», в ячейку Q2 записать значение «Новинка», - для отбора операций списка, в которых принимали участие ценные бумаги любого товарищества с ограниченной ответственностью (ТОО), выпущенные в декабре 2007 года, записать: в ячейку R3 - операцию сравнения (больше или равно) и дату: «>=01.12.2007», в ячейку S3: «<01.01.2008», в ячейку T3: «ТОО*».   Рис. 10. 2. Диапазон условий расширенного фильтра

 

5. Вызвать функцию расширенного фильтра. Установить курсор - в области списка. На линейке управления в группе Сортировка и фильтр выбрать команду Дополнительно (Рис. 10. 3). Рис. 10. 3. Вызов функции расширенного фильтра

 

6. В диалоговом окне расширенного фильтра определить значения параметров (Рис. 10. 4): – параметр Исходный диапазон определяет диапазон со списком: «$A$7:$M$60», – перенос релевантных данных на новое место определяет параметр: «¤-скопировать результат на другое место», – параметр Поместить результат в диапазон содержит адреслевой верхней ячейки диапазона, в который следует разместить результат, – поле параметра Диапазон условий указать интервал условиями отбора: «$P$1:$T$3», – значение параметра  Только уникальные записи очистить, т.е. установить в положение - , – нажать кнопку ОК!. Рис. 10. 4. Определение значений параметров функции Расширенный фильтр

7. Фрагмент результата представлен на рисунке (Рис. 10. 5):   Рис. 10. 5. Результат выполнения функции расширенного фильтра

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

Указания.

1. Скопировать лист Данные на новый лист, листу присвоить имя – РасширФильтр2. Вызвать функцию расширенного фильтра.

2. В диалоговом окне расширенного фильтра определить значения параметров (Рис. 10. 6): – параметр Исходный диапазон определяет столбец, в котором проводится поиск уникальных значений:«$B$7:$B$283», – установить значение параметра: «¤-скопировать результат на другое место», – в поле параметра Поместить результат в диапазон указать левую верхнюю ячейку результата в свободной части рабочего листа: «$P$3», – условий отбора задавать не требуется, поэтому поле параметра Диапазон условий оставить пустым, – установить значение параметра: þ Только уникальные записи в положение, – нажать кнопку ОК! Рис. 10. 6. Панель параметров функции расширенного фильтра   Результат работы функции расширенного фильтра – список уникальных значений поля Дата сделки будет размещен в диапазоне ячеек, адрес левой верхней ячейки диапазна – Р3.

Рис. 10. 7. Панель параметров функции Промежуточные итоги 3. Определить количество элементов в полученном списке с помощью функции Промежуточные итоги, для этого (Рис. 10. 7): – выделить диапазон – полученный список дат проведения сделок, – вызвать функцию Промежуточные итоги, – определить параметры функции Промежуточные итоги в диалоговом окне : При каждом изменении в: « Дата сделки», Операция: « Количество», Добавить итоги по: «Дата сделки», – нажать кнопку ОК! Рис. 10. 8. Результат выполнения функции Промежуточные итоги

4. На рабочем листе отобразится структура списка дат сделок (Рис. 10. 8).

Рис. 10. 9. Общее количество торговых дней по всему списку

 

5. Используя кнопки в верхней левой части экрана, отобразить полученный список в заданном виде (Рис. 10. 9).

11. Итоговые функции в сводных таблицах

 

Для расчета статистических характеристик по данным списка используются сводные таблицы. Источником данных для сводных таблиц могут служить данные рабочего листа MS Excel или данные внешних источников, например, информация базы данных MS Access. Результат расчета – таблица, у которой в области строк и столбцов находятся поля списка, которые служат классификаторами, а в области данных – поля, по которым рассчитываются значения статистических характеристик: сумма, среднее, максимум, минимум, счет количества значений и другие статистические показатели. Структура сводной таблицы предусматривает задание условий отбора строк списка. Допускается группировка строк и/или столбцов сводной таблицы, при этом статистические характеристики паресчитывается автоматически.

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

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

Задание 1. По данным списка для каждого эмитента определить:

1) общую сумму сделок с его ценными бумагами,

2) число сделок, в которых принимали участие его ценные бумаги,

3) дату первой сделки и

4) дату последней сделки с его ценными бумагами.

Используя постраничный просмотр сводной таблицы, получить итоговые значения для эмитента АО «КрАЗ», а также общие итоговые значение для нескольких эмитентов: АО «КрАЗ» АО ОТ «Доверие», Товарищество на вере «Янтарь»

Указания.

1. Скопировать с созданием копии лист Данные. Новому листу присвоить имя – Сводные. Курсор установить в области списка и выделить любую ячейку внутри списка. Вставить сводную таблицу, для этого на линейке управления в группе Таблицы выбрать команду Сводная таблица (Рис. 11. 1).	Рис. 11. 1. Вызов функции Сводная таблица
2. В диалоговом окне Создание сводной таблицы автоматически выделяется диапазон списка. Определить местоположение отчета сводной таблицы – указать курсором левую верхнюю ячейку диапазона, где будет расположен отчет (Рис. 11. 2): ¤ На существующий лист Диапазон:Сводные!$А$285. Нажать кнопку ОК!	Рис. 11. 2. Определение исходных данных и размещение сводной таблицы
3. В диалоговом окне параметров Список полей сводной таблицы определить структуру сводной таблицы: 3.1. Метку столбца Эмитент «отбуксировать» в область Названия строк. Такой способ перестановки полей получил название drag and drop, его обозначение – «d&d». 3.2. Метку столбца СуммаСделки «отбуксировать» в область Значения. «Кликом», нажав на левую кнопку мыши, вызвать мини-меню поля «Количество по поля Дата Сделки». Вызвать опцию Параметры полей значений… (Рис. 11.3).	Рис. 11. 3. Определение структуры сводной таблицы
3.3. В появившемся диалоговом окне Параметры поля значений определить значения параметров: Пользовательское имя: «Сумма по полю Сумма сделки», Операция: «С умма» ( Рис. 11. 4 ). Нажать кнопку Числовой формат, в открывшемся диалоговомокне ФоматЯчеек в поле Числовые форматы выбрать значение«Денежный».Закрыть окно Числовой формат – нажать кнопку ОК! Закрыть диалоговое окно Параметры поля значений – нажать кнопку ОК!	Рис. 11. 4. Панель определения значений параметров поля Сумма
3.4. Метку столбца Дата Сделки – в область значений, для появившегося поля Количество по полю Дата сделки определить параметры: Пользовательское имя – «Число сделок», Операция – «Количество». Нажать на кнопку Числовой формат (Рис. 11. 5). В открывшемся диалоговом окне ФоматЯчеек в поле Числовые форматы выбрать значение «числовой», число десятичных знаков – «0».Нажать кнопку ОК! Закрыть диалоговое окно Параметры поля значений – нажать кнопку ОК!	Рис. 11. 5. Панель определения значений параметров поля Число сделок
3.5. Метку столбца ДатаСделки перенести в область Значения еще раз. Для нового поля определить параметры: Пользовательское имя – «Последняя сделка», Операция – «Максимум». Нажать на кнопку Числовой формат. В открывшемся диалоговом окне ФоматЯчеек в поле Числовые форматы выбрать значение «Дата», Тип – «14.03.01» (Рис. 11. 6). Закрыть диалоговое окно Формат ячеек – нажать кнопку ОК! Закрыть диалоговое окно Параметры поля значений – нажать кнопку ОК! Рис. 11. 6. Панель определения формата поля сводной таблицы Последняя сделка
3.6. Метку столбца ДатаСделки поставить в область данных еще раз. Для нового поля определить параметры (Рис. 11. 7): Пользовательское имя – «Первая сделка», Операция – «М инимум». Нажать на кнопку Числовой формат. В открывшемся диалоговом окне ФоматЯчеек в поле Числовые форматы выбрать значение «Дата», Тип – «14.03.01». Закрыть диалоговое окно Формат ячеек – нажать кнопку ОК!. Закрыть диалоговое окно Параметры поля значений – нажать кнопку ОК!.	Рис. 11. 7. Панель определения значений параметров поля Первая сделка
4. Задание выполнено. На рабочем пространстве Ms Excel отражается построенная сводная таблица и ее структура (Рис. 11. 8). Рис. 11. 8. Сводная таблица и ее структура на рабочем листе
5. Раскрывающийся список в поле Эмитент служит фильтром отбора строк списка по заданным значениям наименований эмитентов (Рис. 11. 9).	Рис. 11. 9. Раскрывающийся список в поле Эмитент
6. С помощью фильтра сводной таблицы определить значение поля Эмитент – «АО “КрАЗ”». 7. Затем открыть мини-меню сводной таблицы, вызвать опцию Параметры сводной таблицы… ( Рис. 11. 10). Рис. 11. 10. Мини-меню сводной таблицы   8. Определить имя сводной таблицы, параметр Имя: «Итоговые функции в сводных таблицах» (Рис. 11. 11). Рис. 11. 11. Панель определения параметров сводной таблицы
8. Область страницы обеспечивает более компактный просмотр сводной таблицы. Просмотреть сводную таблицу по страницам можно в нескольких вариантах: 8.1. «Отбуксировать» метку столбца сводной таблицы Эмитент в область полей страниц, метку столбцов Данные – в левый столбец сводной таблицы (Рис. 11. 12). Такую таблицу можно «пролистать» постранично, для каждого эмитента отводится одна страница ( Рис. 11. 13).
Рис. 11. 12. Перенос поля Эмитент в область страницы	Рис. 11. 13.Постраничный просмотр сводной таблицы

 

8.2. Выделить в окне фильтра несколько значений поля Эмитент, например,АО «КрАЗ», АО ОТ «Доверие», Товарищество на вере «Янтарь» (Рис. 11. 14). Пересчет итоговых функций сводной таблицы происходит автоматически (Рис. 11. 1).
Рис. 11. 14. Определение набора значений поля Эмитент в области страниц	Рис. 11. 15. Итоговые функции сводной таблицы для набора значений поля страницы

Задание 2. По данным списка определить число сделок между инвестиционными компаниями.

Указания.

1. Курсор установить в области списка операций и выделить любую ячейку внутри списка. Вставить сводную таблицу, для этого на линейке управления в группе Таблицы выбрать команду Сводная таблица. Разместить сводную таблицу на существующем листе, левая верхняя ячейка таблицы имеет адрес: Е285.

2.В диалоговом окне параметров Список полей сводной таблицы определить структуру сводной таблицы (Рис. 11. 16): 2.1. Разместить поля: Инвестор-покупатель – в области Названия строк, а Инвестор-продавец– в область Названия столбцов, 2.2. Для определения итоговых значений: меткустолбца СуммаСделок перенести в область Значения, выбрать операцию – Количество, формат поля – числовой, число десятичных знаков – 0, пользовательское имя поля – Число сделок. Рис. 11. 16. Сводная таблица «Количество сделок» и ее структура

3.Определить имя сводной таблицы – «Количество сделок».