Фильтр по списку значений

При применении фильтра по списку значений имена полей будут дополнены раскрывающимися списками (рис. 4.46), из которых можно выбрать нужное значение поля (рис. 4.46) или задать пользовательский критерий (рис. 4.48).

Чтобы создать фильтр по списку значений:

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

- На вкладке Главная в группе Редактирование выберите команду Сортировка и фильтр (рис. 3.97).

Рис. 3. 97. Выбор команды Сортировка и фильтр

Рядом с именами полей будут отображены кнопки со стрелками, нажав на которые, можно открыть список команд (рис. 3.98), аналогичный тому, что вы уже видели при фильтрации в сводной таблице (рис 3.95).

Рис. 3. 98. Меню Фильтр для списка

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

Откройте список для поля, значение которого хотите использовать в качестве фильтра (критерия отбора). В списке будут приведены значении ячеек выбранного поля.

Выберите из списка нужный элемент. На экране будут отображены только те записи, которые соответствуют заданному фильтру. Кнопка со стрелкой в имени поля изменится на кнопку Фильтр .

Вы можете применить фильтр только для одного списка в рабочем листе, но для любого количества полей.

Чтобы снова отобразить на экране все записи списка:

- Щелкните по кнопке Фильтр. Установите флажок Выделить все (рис. 3.99.а). Все объекты вновь будут выделены (рис. 3.99.б).

а б

Рис. 3. 99. Отображение всех записей

- Можно поступить по-другому. Выберите команду Сортировка и фильтр (рис. 3.97). В списке опций команды выделите Очистить (рис. 3.100). Все объекты также будут выделены.

Рис. 3. 100. Очистка условий фильтрации

Чтобы убрать автофильтр:

- Проделайте предыдущее задание, но выберите опцию Фильтр (рис. 3.100).

F Выполнение задания

Выберите из списка все записи для первого магазина. Результат представлен на рис. 4.47.

Рис. 3. 101. Результат выполнения автофильтра для первого магазина

Вновь отобразите на экране все записи списка.

ФИЛЬТР ПО УСЛОВИЮ

Применение фильтра по условию:

F Выполнение задания

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

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

- На вкладке Главная в группе Редактирование выберите команду Сортировка и фильтр (рис. 3.97), затем Числовые форматы и Настраиваемый фильтр для поля "Магазины".

- В открывшемся окне диалогаПользовательский автофильтр (рис. 3.102) укажите отдельные критерии для одного поля и соедините их с помощью логического оператора (И или ИЛИ).

- Щелкнитепо верхнему правому язычку и выберите "1"

- Щелкните по нижнему язычку и выберите “2”.

- Установите в левом и верхнем и нижнем поле знак равенства (рис. 3.102).

Рис. 3. 102. Результат выполнения автофильтра для первого магазина

- Переключатель “И” означает, что оба условия должны выполняться одновременно. Но в нашем случае это невозможно: не может быть организация одновременно "1" и "2". Значит, надо щелкнуть по “ИЛИ”.

- Нажмите на кнопку ОК. Будут выбраны четыре записи для указанных магазинов (рис. 3.103).

- Сделайте снова, чтобы показывались все строки (рис. 3.99 или 3.100).

Для закрепления попробуйте сделать фильтр по выборке сведений для магазина 2 или 3, для которых премия превышает 10 тыс. руб.

Рис. 3. 103. Результат выполнения автофильтра для первого и третьего магазина

- В данном случае вам необходимо применить фильтр сразу для двух полей "Магазины" и "Премия". Результаты показаны на рис. 4.50.

Рис. 3. 104. Результат выполнения автофильтра для второго и третьего магазина и премии больше 10 тыс. руб.

Перейдем к изучению еще одной процедуры Excel – консолидации.

КОНСОЛИДАЦИЯ ДАННЫХ

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

Существует несколько способов консолидации:

Консолидация по расположению. Данные предварительно упорядочиваются во всех листах в одинаковом порядке и расположении.

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

Выполним консолидацию по расположению.

Если количество листов рабочей книги меньше четырех, то вам надо их добавить.

F Выполнение задания

Чтобы вставить новый рабочий лист:

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

- На вкладке Главная в группе Ячейки выберите пункт Вставить (рис. 3.105.а), а затем — команду Вставить лист (рис. 3.105.б).

Рис. 3. 105.Добавление листа через командную вкладку

- Щелкните правой кнопкой мыши на ярлычке листа. В появившемся контекстном меню (рис 3.106) выберите Вставить.

Рис. 3. 106. Контекстное меню ярлыка рабочего листа

- В диалоговом окне Вставка (рис. 3.107) укажите тип вставляемого объекта - Лист.

- Наиболее просто вставить лист можно щелкнув по значку Вставить лист в области ярлыков.

- Для любителей работы с клавишами вставить лист можно одновременным нажатием клавиш Chift + F11.

Рис. 3. 107. Диалоговое окно Вставка

- С помощью левой кнопки мыши расположите ярлыки листов в заданной последовательности (рис. 3.108).

Рис. 3. 108. Изменение последовательности расположения рабочих листов

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

- Переименуйте "Лист2" в "Промежуточный", а "Лист3" в "Консолидация" как в п. 3.13.1 (рис. 3.109).

Рис. 3. 109. Последовательность расположения и имена рабочих листов

Выполните копирование фрагмента таблицы A1:F9, из листа "Основной" в лист "Промежуточный". Для этого воспользуйтесь буфером обмена.

- Выделите диапазон ячеек A1:F7 листа "Основной". Щелкните на кнопку Копировать вкладки Главная группы команд Буфер обмена.

- Откройте рабочий лист " Промежуточный ". Курсор установите в ячейку А1. Щелкните на кнопку Вставить вкладки Главная группы Буфер обмена.

Копия выделенного фрагмента появится в области A1:F7 листа "Промежуточный".

- Очистите ячейки B2:F7. Оставьте заполненным только столбец " Магазины ".

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

Затем выполните копирование всего рабочего листа "Основной" в лист Консолидация, для того, чтобы все таблицы и диаграммы сделать полностью одинаковыми. Это позволит вам сэкономить в дальнейшем массу времени. Данную операцию можно выполнить двумя способами.