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

1. Выделите любую ячейку фильтруемого списка.

2. На вкладке Данные нажмите кнопку .

3. В диалоговом окне Расширенный фильтр исходный диапазон будет указан автоматически (если выполнен пункт1).

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

4. Можно временно скрыть строки, которые не удовлетворяют условию отбора и показать только результат фильтрации в самом первоначальном списке. Для этого устано­ви­те переключатель Обработка в положение Фильтровать список на месте.

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

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

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

Если на листе существует диапазон с именем Критерии, то в поле Диапазон условий автоматически появится ссылка на этот диапазон.

Задание №6

1. С помощью расширенного фильтра вывести всех Агаповых.

2. Вывести фамилии и имена членов второй группы на 2 строчки ниже основного списка.

3. Добавить поле Возраст. Как вычислять – см выше. Поле необходимо для контроля правильности выборки.

4. Вывести на месте всех, кто моложе 19 лет. Использовать условие с формулой! Поместить отфильтрованные записи в на другой лист.

Сложные условий отбора для расширенного фильтра

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

На ячейки одного поля накладываются несколько условий отбора

Если несколько условий должны выполняться вместе (операция И), то надо в диапазоне условий расположить рядом (в одной строке) столько заголовков поля, сколько условий. Если надо отобрать людей, у которых рейтинг находится в интервале (70;80), то диапазон условий должен иметь следующий вид

Рейтинг	Рейтинг
<=80	>70

Если надо отобрать записи, удовлетворяющие одному из условий, то условия звписываются в несколько строчек. Чтобы отобрать тех, у кого возраст либо больше 20, либо меньше 10, надо создать следующий диапазон условий.

=(ГОД(СЕГОДНЯ())-ГОД(Дата рождения))<10

=(ГОД(СЕГОДНЯ())-ГОД(Дата рождения))>20

 

Условие отбора накладывается на ячейки двух или более столбцов

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

Группа	Рейтинг
первая	>70

 

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

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

Фамилия	Отчество	Группа
Агапов		первая
	Борисов*	вторая

Задание №7

1. Выбрать членов первых двух групп.

2. Выбрать тех, укоторых рейтинг находится в пределах от 60 до 70.

3. Выбрать Агаповых, которые родились после 1 января 1987 года.

4. Выбрать всех членов первой группы и Бориса из второй.

Таблицы

В Excel 2007 появилась новая структура данных – таблицы. Они представляют разновидность списков, для работы с которыми есть дополнительные инструменты. Для таблиц сразу устанавливается режим автофильтра.

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

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

На вкладке Вставка в группе Таблицы выберите команду Таблица.

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

При выделении любой ячейки таблицы появится окно Работа с таблицами с вкладкой Конструктор.

Задание №8

Скопируйте список предыдущего задания на новый лист и преобразуйте его в таблицу.

Подведение итогов в таблице Excel

1. Щелкните в любом месте таблицы.

2. На вкладке Конструктор в группе Параметры стилей таблиц установите флажок Строка итогов.

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

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

Задание №9

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

2. Вывести всех, родившихся в апреле.

3. Вывести всех, имеющих рейтинг выше среднего.

Добавление и удаление строк и столбцов в таблицах Excel