Многие пользователи ПК хорошо знакомы с пакетом продуктов для работы с различного рода документами под названием Microsoft Office. Среди программ этой компании есть MS Excel. Данная утилита предназначена для работы с электронными таблицами.
В программе присутствует так называемый расширенный фильтр в Excel. Он предназначен для удобства работы и создания таблиц. О нем и пойдет речь в нашей статье. Мы расскажем далее, как его делать и как пользоваться.
Что это за функция? Описание
Что значит расширенный фильтр в Excel? Это функция, которая позволяет разграничивать выбранные данные (по столбцам в "Экселе") относительно введенных требований.
К примеру, если у нас есть электронная таблица со сведениями обо всех учениках школы (рост, вес, класс, пол и т. п.), то мы с легкостью сможем выделить среди них, скажем, всех мальчиков с ростом 160 из 8-го класса. Сделать это можно, используя функцию "Расширенный фильтр" в Excel. О ней мы и будем детально рассказывать далее.
Что значит автофильтр?
Какие особенности имеет обычный и расширенный фильтр? Сначала расскажем про первый. Автофильтр (обычный) предназначен для разграничения данных по заранее известным критериям, количество которых определено. Его используют, когда имеется электронная таблица незначительных размеров, по столбцам и заданным параметрам. Если нам необходимо задать неограниченное количество критериев для фильтрации, необходимо выбирать расширенный.
Как делать правильно?
Как сделать расширенный фильтр в Excel? Чтобы было понятно, каким образом происходит процедура и как она делается, рассмотрим пример.
Инструкция по расширенной фильтрации электронной таблицы:
- Необходимо создать место выше основной таблицы. Там и будут располагаться результаты фильтрации. Должно быть достаточное количества места для готовой таблицы. Также требуется еще одна строка. Она будет разделять отфильтрованную таблицу от основной.
- В самую первую строку освобожденного места скопировать всю шапку (названия колонок) основной таблицы.
- Ввести необходимые данные для фильтрации в нужный столбец. Отметим, что запись должна выглядеть следующим образом: = "= фильтруемое значение".
- Теперь необходимо пройти в раздел "Данные". В области фильтрации (значок в виде воронки) выбрать "Дополнительно" (находится в конце правого списка от соответствующего знака).
- Далее во всплывшем окошке нужно ввести параметры расширенного фильтра в Excel. "Диапазон условий" и "Исходный диапазон" заполняются автоматически, если была выделена ячейка начала рабочей таблицы. Иначе их придется вводить самостоятельно.
- Нажать на Ок. Произойдет выход из настроек параметров расширенной фильтрации.
После проделанных шагов в основной таблице останутся только записи по заданному разграничивающему значению. Чтобы отменить последнее действие (фильтрацию), нужно нажать на кнопку "Очистить", которая находится в разделе "Данные".
Работа с расширенным фильтром в "Экселе"
Как пользоваться расширенным фильтром в Excel? Для того чтобы понять, обратимся к данной функции повторно. Предположим у нас в электронной таблице есть колонка с городами. До этого мы уже делали расширенную фильтрацию на город Ростов. Теперь же хотим в этом же документе добавить к полученным по городу Ростову еще и те записи, которые соответствуют городу Самаре.
Для этого необходимо:
- Разместить условия разграничения (="-Самара") под предыдущим запросом (="=Ростов").
- Вызвать меню расширенного фильтра (раздел "Данные", вкладка "Фильтрация и сортировка", выбрать в ней "Дополнительно").
- Нажать Ок. После этого расширенная фильтрация закроется в Excel. А на экране появится готовая таблица, состоящая из записей, в которых указан город Самара или Ростов.
При работе следует учитывать следующий очень важный момент. Все введенные критерии должны находиться только в тех столбцах, по которым идет фильтрование. Выше основной таблицы.
После того как был изучен основной метод расширенной фильтрации, можно приступать к изучению всех возможностей данной функции. Имеется в виду именно использование формул.
Расширенная фильтрация. Основные правила использования при работе "Экселе"
Правила использования:
- Критериями отбора называются результаты исходной формулы.
- Результатом могут быть только два значения: "ИСТИНА" или "ЛОЖЬ".
- При помощи абсолютных ссылок указывается исходный диапазон фильтруемой таблицы.
- В результатах формулы будут показаны только те строки, которые получают по итогу значение "ИСТИНА". Значения строк, которые получили по итогу формулы "ЛОЖЬ", не будут высвечиваться.
При помощи этих правил можно создавать любые формулы с допустимыми операндами и значениями.
Пример в "Экселе 2010"
Рассмотрим пример расширенного фильтра в Excel 2010 и использования в нем формул. К примеру, разграничим значения какого-нибудь столбца с числовыми данными по результату среднего значения (больше или меньше).
Инструкция для работы с расширенным фильтром в Excel по среднему значению колонки:
- Для начала необходимо выбрать формулу среднего значения данных столбца. Для этого нужно выбрать диапазон результатов от начальной записи до конечной. Просуммировать их и разделить на количество строк (количество записей).
- После этого выбрать в разделе "Фильтрование и сортировка" пункт "Дополнительно". Вписать туда нужные данные (по диапазонам).
- После этого нажать Ок. В результате получится таблица, в которой есть записи, не превышающие среднего значения по заданному столбцу.
Таким образом можно использовать расширенную фильтрацию довольно разнообразными способами. Сделать это можно, прибегая к составлению различного рода формул.
Главное - не забывать вписывать критерии фильтрации над основной таблицей в столбце, по которому будет проводиться разграничение.
Внимание! При работе записи, которые не прошли критерий фильтрации, не удаляются. Они просто не показываются (не отображаются). Расширенная фильтрация предназначена для того, чтобы можно было скопировать результат и вставить его куда-нибудь в другое место. После этого необходимо нажать на "Очистить" в разделе "Фильтрация и сортировка". Тогда ваша исходная таблица вернется на экран.
Автофильтр. Пример использования
Автофильтр - это обычный инструмент. Его можно применить, исключительно задав точные параметры. Например, вывести все значения таблицы, которые превышают значения 1000 (< 1000), или показать точные данные, как было рассмотрено в примере с городами.
Им пользуются намного чаще, чем расширенным. Однако параметров для фильтрации, предлагаемых автофильтром, часто бывает недостаточно.
Плюсы и минусы расширенного фильтра в программе "Эксель"
Так как расширенная фильтрация - это дополнение к обычной, то в ее использовании есть свои преимущества и недостатки. Их мы сейчас и рассмотрим.
Плюсы расширенной фильтрации:
- можно использовать формулы.
Минусы расширенной фильтрации:
- чтобы проделать данную операцию, необходимо потратить больше времени на разбирательство с введением диапазона и исходных данных;
- для использования расширенного фильтрования с применением формул необходимо знать и уметь пользоваться правилами логики и составления уравнений MS Excel.
Несмотря на наличие минусов, эта функция имеет все же большее количество возможностей, нежели просто автофильтрация. Хотя при последней нет необходимости вводить что-то свое, кроме критерия, по которому будет проводиться выявление значений.
Фильтрация по двум отдельным критериям. Как правильно ее сделать?
Расширенная фильтрация позволяет использовать различные формулы имеющейся электронной таблицы. Если вам необходимо разграничить ее сначала по одному критерию, потом по другому (отдельно друг для друга), необходимо:
- Создать место для ввода параметра фильтрования. Удобнее всего оставлять это место над основной таблицей и не забывать копировать шапку (названия столбцов), чтобы не запутаться, в какую колонку вводить этот критерий.
- Ввести нужный показатель для фильтрации. Например, все записи, чьи значения столбца больше 1000 (> 1000).
- Пройти во вкладку "Данные". В разделе "Фильтрация и сортировка" выбрать пункт "Дополнительно".
- В открывшемся окошке указать диапазоны рассматриваемых значений и ячейку со значением рассматриваемого критерия.
- Нажать на Ок. После этого будет выведена отфильтрованная по заданному критерию таблица.
- Скопировать результат разграничения. Вставить отфильтрованную таблицу куда-нибудь в сторону на том же листе Excel. Можно воспользоваться другой страницей.
- Выбрать "Очистить". Данная кнопка находится во вкладке "Данные" в разделе "Фильтрация и сортировка". После ее нажатия отфильтрованная таблица вернутся в первоначальный вид. И можно будет работать с ней.
- Далее необходимо снова выделить свободное место для таблицы, которая будет отфильтрована.
- Потом нужно скопировать шапку (названия столбцов) основного поля и перенести их в первую строчку освобожденного под отфильтрованную структуру места.
- Пройти во вкладку "Данные". В разделе "Фильтрация и сортировка" выбрать "Дополнительно".
- В открывшемся окошке выбрать диапазон записей (столбцов), по которому будет проводиться фильтрация.
- Добавить адрес ячейки, в которой записан критерий разграничения, например, "город Одесса".
- Нажать на Ок. После этого произойдет фильтрация по значению "Одесса".
- Скопировать отфильтрованную таблицу и вставить ее либо на другой лист документа, либо на той же странице, но в стороне от основной.
- Снова нажать на "Очистить". Все, готово. Теперь у вас имеются три таблицы. Основная, отфильтрованная по одному значению (>1000), а также та, что отфильтрована по другому значению (Одесса).
Небольшое заключение
Несмотря на то что на первый взгляд данная функция кажется сложной, на самом деле все довольно просто. Все, что необходимо знать, это диапазон условий расширенного фильтра в Excel. Однако нельзя забывать следующее. Не следует путать обычный и расширенный фильтр. Также рекомендуется выучить правила логики и составления формул Excel, тогда работа с фильтром становится простой.
Наверное, все пользователи, которые постоянно работают с Microsoft Excel, знают о такой полезной функции этой программы как фильтрация данных . Но не каждый в курсе, что существуют также и расширенные возможности у этого инструмента. Давайте рассмотрим, что умеет делать расширенный фильтр Microsoft Excel и как им пользоваться.
Недостаточно сразу запустить расширенный фильтр — для этого необходимо выполнить еще одно условие. Далее мы расскажем о последовательности действий, которые следует предпринять.
Шаг 1: Создание таблицы с условиями отбора
Чтобы установить расширенный фильтр, прежде всего требуется создать дополнительную таблицу с условиями отбора. Ее шапка в точности такая, как у основной, которую мы, собственно, и будем фильтровать. Для примера мы разместили дополнительную таблицу над основной и окрасили ее ячейки в оранжевый цвет. Хотя размещать ее можно в любом свободном месте и даже на другом листе.
Теперь вписываем в дополнительную таблицу сведения, которые нужно будет отфильтровать из основной таблицы. В нашем конкретном случае из списка выданной сотрудникам заработной платы мы решили выбрать данные по основному персоналу мужского пола за 25.07.2016.
Шаг 2: Запуск расширенного фильтра
Только после того как дополнительная таблица создана, можно переходить к запуску расширенного фильтра.
Таким образом, можно сделать вывод, что расширенный фильтр предоставляет больше возможностей, чем обычная фильтрация данных. Но нельзя не отметить, что работа с этим инструментом все-таки менее удобна, чем со стандартным фильтром.
Новосибирский Государственный Университет экономики и управления
Кафедра Экономической информатики
БОРИДЬКО О.Н.
Методические указания по выполнению лабораторной работы
« Фильтрация в Microsoft Excel 2007,функции базы данных »
по дисциплине « Информатика »
для студентов 1 курса дневного отделения экономических специальностей
Новосибирск
Табличный процессор Microsoft Excel 2007
Методические указания к выполнению лабораторной работы № 3
”Фильтрация в Excel,функции базы данных”
1СПИСКИ данных в EXCEL 3
2Фильтрация данных в EXCEL 3
2.1Типы критериев 3
2.1.1Критерии на основе сравнения 4
2.1.2Критерии в виде образца-шаблона 5
2.1.3Множественные критерии на основе логических операций 5
2.1.4Вычисляемые критерии на основе логических формул 5
3Средства фильтрации 6
3.1Автофильтр 6
3.2Расширенный фильтр 9
4ВСТРОЕННЫЕ ФУНКЦИИ базы данных 15
5Вопросы к защите лабораторной работы 18
СПИСКИ данных в EXCEL
При работе с таблицами большую помощь могут оказать содержащиеся в Excel средства работы с базой данных.
Таблица в Excel представляет собой однотабличную базу данных .
В Excel базы данных называются списками .
Список – определенным образом сформированный на рабочем листе Excel массив данных со столбцами и строками.
Список может использоваться как база данных, в которой строки выступают в качестве записей , а столбцы являются полями . Первая строка списка при этом содержит названия столбцов. Список должен быть организован так, чтобы в каждом столбце содержалась однотипная информация. Пустые ячейки недопустимы.
Фильтрация данных в excel
Фильтрация данных – это наиболее частые действия, производимые со списком или базой данных. Фильтрация производится на основе задаваемых пользователем критериев – требований, налагаемых на информацию. Результатом фильтрации является временное скрытие записей, не удовлетворяющих заданным критериям.
Для фильтрации списков в Excel существует две команды:
Автофильтр
Расширенный фильтр
Автофильтр обеспечивает простой и быстрый способ скрытия лишних записей, оставляя на экране только те, что удовлетворяют критериям.
Расширенный фильтр позволяет накладывать более сложные условия отбора, которые могут включать вычисляемые критерии.
Типы критериев
Чтобы отфильтровать данные, необходимо прежде всего описать то, что необходимо найти, другими словами, задать диапазон критериев . Принципы составления диапазона одинаковы и для автофильтра, и для расширенного фильтра, хотя записываются они в различных местах. Excel поддерживает несколько типов критериев, приведем основные из них:
Критерии на основе сравнения – позволяют находить точные соответствия с помощью гибкого набора операций сравнения;
Критерии в виде образца-шаблона – позволяют находить данные по соответствию некоторому шаблону (применяется только к тексту, либо к числам, отформатированным как текст);
Множественные критерии на основе логических операций – позволяют объединить несколько критериев с помощью логических операций;
Вычисляемые критерии на основе логических формул – позволяют создавать условия отбора, зависящие от значений логических формул.
Мы познакомились и научились применять стандартную фильтрацию в Excel. Но очень часто возникают ситуации, когда базовые инструменты фильтрации бессильны и не могут обеспечить необходимый результат выборки. В этом уроке Вы узнаете, как решить эту проблему в Excel с помощью расширенных фильтров.
Если вдруг возникает необходимость выделить какие-то специфичные данные, то, как правило, базовые инструменты фильтрации с такой задачей уже не справляются. К счастью, Excel содержит множество расширенных фильтров, включая поиск текста, даты и фильтрацию по числовым значениям, что позволяет сузить результаты и помочь найти именно то, что Вам нужно.
Фильтрация и поиск в Excel
Excel позволяет искать информацию, которая содержит точную фразу, число, дату и многое другое. В следующем примере мы воспользуемся этим инструментом, чтобы оставить в журнале эксплуатации оборудования только продукцию марки Saris .
Использование расширенных текстовых фильтров в Excel
Расширенные текстовые фильтры используются для отображения более конкретной информации, например, ячеек, которые не содержат заданный набор символов. Допустим, наша таблица уже отфильтрована таким образом, что в столбце Тип отображены только Другие изделия. В дополнение мы исключим все позиции, содержащие слово “case” в столбце Описание оборудования .
Использование в Excel расширенных фильтров по дате
Расширенные фильтры по дате позволяют выделить информацию за определенный промежуток времени, к примеру, за прошлый год, за этот месяц или между двумя датами. В следующем примере мы воспользуемся расширенным фильтром по дате, чтобы посмотреть оборудование, которое было отдано на проверку сегодня.
Использование расширенных числовых фильтров в Excel
Расширенные числовые фильтры позволяют оперировать данными самыми различными способами. В следующем примере, мы выберем только то оборудование, которое входит в заданный промежуток идентификационных номеров.
Иногда при работе с большой базой данных возникает необходимость отобрать информацию по определенному критерию (например, показать только тех людей, которые родились в 1980 году). Для выполнения задач подобного рода используется фильтр в Excel.
Видео по фильтрации и сортировке данных в Excel
Виды фильтров в Excel
Фильтрация — это выбор необходимых данных из списка для последующей работы с ними. Результатом этой процедуры будут определенные строки, которые подходят под критерии отбора. Остальные записи временно скрываются и не используются до тех пор, пока пользователь не отключит фильтр. С отобранными данными можно выполнять стандартные действия: редактирование, форматирование, печать, построение графиков, диаграмм и т.д.
Всего в Экселе есть 2 способа фильтрации: автофильтр и расширенный фильтр. Запустить их можно через панель меню, щелкнув «Данные — Фильтр». С помощью первого варианта осуществляется быстрый отбор необходимой информации с простыми критериями поиска. В режиме автофильтра заглавная строка таблицы в каждой колонке будет содержать кнопку со стрелочкой, кликнув на которую можно указать критерии для отбора. Для каждого столбца можно установить свои настройки. В данном режиме можно установить следующие параметры:
Чтобы удалить фильтр из одной колонки, нужно кликнуть в перечне элементов на пункт «Все». Если нужно отменить его для всей таблицы, необходимо выбрать в панели меню пункты «Данные — Фильтр — Отобразить все». Удаление автофильтра осуществляется так же, как и его запуск.
Расширенный фильтр
Пример создания расширенного фильтра — необходимо задать диапазон исходных данных, фильтров и диапазон, куда помещаются отфильтрованные данные
Второй вариант фильтрации предоставляет пользователю больше возможностей для отбора необходимых данных.Чтобы запустить расширенную опцию, необходимо создать копию заголовков таблицы (т.е. просто скопировать шапку). Это будет диапазон условий. Затем надо заполнить этот диапазон критериями отбора. Но тут важно придерживаться правил: если нужно, чтобы значения отбирались по двум параметрам (к примеру, фамилия студента и оценка), то условия записываются в одну строку; если же критерии будут отбираться в режиме «ИЛИ» (марка машины или объем двигателя), тогда они записываются в разные строки.
Допустим, есть таблица с 2 колонками — наименование товара и количество. Всего товаров 3 — бананы, апельсины, мандарины, а количество — 10, 20 и 15 штук соответственно. После того как будет скопирована шапка, можно создать условие, например, показать товары, количество которых меньше или равно 15. То есть под скопированной шапкой в колонке «Кол-во» надо написать <=15. Затем надо запустить расширенный фильтр, указать исходный диапазон (исходная таблица), диапазон условий (таблица, где указано «кол-во <=15») и нажать «ОК». Исходная таблица изменится: теперь тут будут отображены только бананы (10 штук) и мандарины (15 штук).
Таким образом, принцип работы фильтрации должен быть понятен. Этим способом можно отбирать любые элементы из базы данных, насколько большой она бы ни была. Такая процедура поможет упростить работу пользователя с большими объемами данных.