Электроника для всех своими руками


Электроника для всех своими руками
Электроника для всех своими руками





Электроника для всех своими руками

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

расширенный фильтр в Excel

Обычный и расширенный фильтр

В Excel представлен простейший фильтр, который запускается с вкладки «Данные» — «Фильтр» (Data — Filter в англоязычной версии программы) или при помощи ярлыка на панели инструментов, похожего на конусообразную воронку для переливания жидкости в ёмкости с узким горлышком.

Для большинства случаев данный фильтр является вполне оптимальным вариантом. Но, если необходимо осуществить отбор по большому количеству условий (да ещё и по нескольким столбцам, строкам и ячейкам), многие задаются вопросом, как сделать расширенный фильтр в Excel. В англоязычной версии называется Advanced filter.

использование расширенного фильтра в Excel

Первое использование расширенного фильтра

В Excel большая часть работы проходит с таблицами. Во-первых, это удобно, во-вторых, в одном файле можно сохранить сведения на нескольких страницах (вкладках). Над основной таблицей желательно создать несколько строк, самую верхнюю из которых оставить для шапки, именно в данные строки будут вписываться условия расширенного фильтра Excel. В дальнейшем фильтр наверняка будет изменён: если потребуется больше условий, всегда можно вставить в нужном месте ещё одну строку. Но желательно, чтобы между ячейками диапазона условий и ячейками исходных данных была одна незадействованная строка.

Как использовать расширенный фильтр в Excel, примеры, рассмотрим ниже.

A B C D E F 1 Продукция Наименование Месяц День недели Город Заказчик 2 овощи Краснодар "Ашан" 3 4 Продукция Наименование Месяц День недели Город Заказчик 5 фрукты персик январь понедельник Москва "Пятёрочка" 6 овощи помидор февраль понедельник Краснодар "Ашан" 7 овощи огурец март понедельник Ростов-на-Дону "Магнит" 8 овощи баклажан апрель понедельник Казань "Магнит" 9 овощи свёкла май среда Новороссийск "Магнит" 10 фрукты яблоко июнь четверг Краснодар "Бакаль" 11 зелень укроп июль четверг Краснодар "Пятёрочка" 12 зелень петрушка август пятница Краснодар "Ашан"

Применение фильтра

В приведённой таблице строки 1 и 2 предназначены для диапазона условий, строки с 4 по 7 - для диапазона исходных данных.

Для начала следует ввести в строку 2 соответствующие значения, от которых будет отталкиваться расширенный фильтр в Excel.

Запуск фильтра осуществляется с помощью выделения ячеек исходных данных, после чего необходимо выбрать вкладку «Данные» и нажать кнопку «Дополнительно» (Data — Advanced соответственно).

В открывшемся окне отобразится диапазон выделенных ячеек в поле «Исходный диапазон». Согласно приведённому примеру, строка принимает значение «$A:$F».

Поле «Диапазон условий» должно заполниться значениями «$A:$F».

Окошко также содержит два условия:

  • фильтровать список на месте;
  • скопировать результат в другое место.

Первое условие позволяет формировать результат на месте, отведённом под ячейки исходного диапазона. Второе условие позволяет сформировать список результатов в отдельном диапазоне, который следует указать в поле «Поместить результат в диапазон». Пользователь выбирает удобный вариант, например, первый, окно «Расширенный фильтр» в Excel закрывается.

Основываясь на введённых данных, фильтр сформирует следующую таблицу.

A B C D E F 1 Продукция Наименование Месяц День недели Город Заказчик 2 овощи Краснодар "Ашан" 3 4 Продукция Наименование Месяц День недели Город Заказчик 5 овощи помидор февраль понедельник Краснодар "Ашан"

При использовании условия «Скопировать результат в другое место» значения из 4 и 5 строк отобразятся в заданном пользователем диапазоне. Исходный диапазон же останется без изменений.

excel расширенный фильтр диапазон условий

Удобство использования

Описанный способ не совсем удобен, потому для усовершенствования обычно используют язык программирования VBA, с помощью которого составляют макросы, позволяющие автоматизировать расширенный фильтр в Excel.

Если пользователь обладает знаниями VBA, рекомендуется изучить ряд статей данной тематики и успешно реализовывать задуманное. При изменении значений ячеек строки 2, отведённой под Excel расширенный фильтр, диапазон условий будет меняться, настройки сбрасываться, сразу запускаться заново и в необходимом диапазоне будут формироваться нужные сведения.

условия расширенного фильтра Excel

Сложные запросы

Помимо работы с точно заданными значениями, расширенный фильтр в Excel способен обрабатывать и сложные запросы. Таковыми являются введённые данные, где часть знаков заменена подстановочными символами.

Таблица символов для сложных запросов приведена ниже.

Пример запроса Результат 1 п

возвращает все слова, начинающиеся с буквы П:

  • персик, помидор, петрушка (если ввести в ячейку B2);
  • Пятёрочка (если ввести в ячейку F2).
2 = результатом будет выведение всех пустых ячеек, если таковые имеются в рамках заданного диапазона. Бывает весьма полезно прибегать к данной команде с целью редактирования исходных данных, ведь таблицы могут с течением времени меняться, содержимое некоторых ячеек удаляться за ненадобностью или неактуальностью. Применение данной команды позволит выявить пустые ячейки для их последующего заполнения, либо реструктуризации таблицы. 3 <> выведутся все непустые ячейки. 4 ию все значения, где имеется буквосочетание «ию»: июнь, июль. 5 =????? все ячейки столбца, имеющие четыре символа. За символы принято считать буквы, цифры и знак пробела.

Стоит знать, что значок может означать любое количество символов. То есть при введённом значении «п» будут возвращены все значения, вне зависимости от количества символов после буквы «п».

Знак «?» подразумевает только один символ.

как сделать расширенный фильтр в Excel

Связки OR и AND

Следует знать, что сведения, заданные одной строкой в «Диапазоне условий», расцениваются записанными в связку логическим оператором (AND). Это означает, что несколько условий выполняются одновременно.

Если же данные записаны в один столбец, расширенный фильтр в Excel распознаёт их связанными логическим оператором (OR).

расширенный фильтр в Excel примеры

Таблица значений примет следующий вид:

A B C D E F 1 Продукция Наименование Месяц День недели Город Заказчик 2 фрукты 3 овощи 4 5 Продукция Наименование Месяц День недели Город Заказчик 6 фрукты персик январь понедельник Москва "Пятёрочка" 7 овощи помидор февраль понедельник Краснодар "Ашан" 8 овощи огурец март понедельник Ростов-на-Дону "Магнит" 9 овощи баклажан апрель понедельник Казань "Магнит" 10 овощи свёкла май среда Новороссийск "Магнит" 11 фрукты яблоко июнь четверг Краснодар "Бакаль"

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

Ещё один способ фильтрования данных осуществляется с помощью команды «Вставка — Таблица — Сводная таблица» (Insert — Table — PivotTable в англоязычной версии).

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

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

как пользоваться расширенным фильтром в Excel

Заключение

В заключение следует отметить, что область применения фильтров в Microsoft Excel весьма широка и разнообразна. Достаточно применить фантазию и развивать собственные знания, умения и навыки.

Сам по себе фильтр прост в применении и освоении, несложно разобраться, как пользоваться расширенным фильтром в Excel, но он предназначен для случаев, когда необходимо малое количество раз произвести отсеивание сведений для дальнейшей обработки. Как правило, не предусматривает работу с большими массивами сведений ввиду обычного человеческого фактора. Здесь уже на помощь приходят более продуманные и продвинутые технологии обработки сведений в Microsoft Excel.

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

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

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


Источник: http://fb.ru/article/251312/rasshirennyiy-filtr-v-excel-primeryi-kak-sdelat-rasshirennyiy-filtr-v-excel-i-kak-im-polzovatsya






Электроника для всех своими руками

Электроника для всех своими руками

Электроника для всех своими руками

Электроника для всех своими руками

Электроника для всех своими руками

Электроника для всех своими руками

Электроника для всех своими руками

Электроника для всех своими руками

Похожие новости: