Youtubezilla.ru

Мастер бытовой техники
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Сортировка и фильтрация данных в Excel

Фильтрация — это выбор необходимых данных из списка для последующей работы с ними. Результатом этой процедуры будут определенные строки, которые подходят под критерии отбора. Остальные записи временно скрываются и не используются до тех пор, пока пользователь не отключит фильтр. С отобранными данными можно выполнять стандартные действия: редактирование, форматирование, печать, построение графиков, диаграмм и т.д.

Всего в Экселе есть 2 способа фильтрации: автофильтр и расширенный фильтр. Запустить их можно через панель меню, щелкнув «Данные — Фильтр». С помощью первого варианта осуществляется быстрый отбор необходимой информации с простыми критериями поиска. В режиме автофильтра заглавная строка таблицы в каждой колонке будет содержать кнопку со стрелочкой, кликнув на которую можно указать критерии для отбора. Для каждого столбца можно установить свои настройки. В данном режиме можно установить следующие параметры:

  1. Сортировка по возрастанию или по убыванию.
  2. «Все» — Excel покажет (восстановит) все строки.
  3. «Первые 10» — Excel отобразит первые 10 записей. При выборе этого пункта откроется новое окно, в котором можно указать число записей, выбрать, какие из них показывать (наибольшие или наименьшие), а также установить ограничение на количество отображаемых записей.
  4. «Условие» — здесь пользователь может самостоятельно создать 2 критерия отбора данных, объединяя их простыми операторами И, ИЛИ.
  5. Любой из элементов. Здесь можно выбрать любое значение, которое находится в колонке. Например, если столбец состоит из наименований товаров, то в перечне элементов будут указаны абсолютно все товары. Пользователь может указать любой из них.
  6. «Пустые» и «Непустые» — Excel отобразит пустые (или непустые) ячейки. Данная опция появляется только в том случае, если в столбце есть незаполненные поля.

Пример создания автофильтра данных Excel

Пример создания автофильтра данных Excel

Чтобы удалить фильтр из одной колонки, нужно кликнуть в перечне элементов на пункт «Все». Если нужно отменить его для всей таблицы, необходимо выбрать в панели меню пункты «Данные — Фильтр — Отобразить все». Удаление автофильтра осуществляется так же, как и его запуск.

Сортировка и фильтрация данных в Excel

Всегда казалось странным, что Excel не поддерживает регулярные выражения (кои в среде программистов зовут регулярками или регэкспами). С их помощью делают сложную сортировку и фильтрацию. Инструмент SeoTools plugin for Excel поддерживает регулярки, но, как и большинство дополнительных ресурсов для Excel, работает только на платформе ПК. Эта статья предназначена, в основном, для пользователей Mac (мы не рекламируем SeoTools).

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

Немного теории

Всегда казалось странным, что Excel не поддерживает регулярные выражения (кои в среде программистов зовут регулярками или регэкспами). С их помощью делают сложную сортировку и фильтрацию. Инструмент SeoTools plugin for Excel поддерживает регулярки, но, как и большинство дополнительных ресурсов для Excel, работает только на платформе ПК. Эта статья предназначена, в основном, для пользователей Mac (мы не рекламируем SeoTools).

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

Подготовка к работе с расширенным фильтром

Для дальнейшего нам нужен «подопытный» — база данных, над которой будем ставить эксперименты. Мы её оставили отформатированной под таблицу. Но это лишь для «красоты», поскольку применение расширенного фильтра удалит стандартные табличные фильтры. Последние можно включить повторно по окончании получения наборов данных.

Расширенный фильтр находится:

Для ПК: Данные > Сортировка и фильтр >Дополнительно.

Для Мак: Данные > Сортировка и фильтр > Фильтр > Расширенный фильтр

Пользователи Mac могут щёлкнуть ПКМ по таблице и выбрать нужный пункт из контекстного меню. На ПК такой возможности нет.

Небольшой совет

Базовые операторы

В дополнение к вышеупомянутым подстановочным знакам приведём некоторые операторы, необходимые при работе с фильтром:

  • = равенство, т.е. левая и правая часть должна совпасть: =билет [включит все ячейки, содержащие билет]
  • <> не равно, левая и правая часть не должны совпасть: <> https://www.aviasales.ru/mag* [исключаем данный адрес (блог)]
  • ‘ апостроф преобразует формулу в текст, если поставить его первым в ячейке: ‘=купить авиабилет
  • > больше чем: >500
  • >= больше или равно: B4-C4>=3
  • < меньше чем: C6<D6
  • <= меньше или равно: <=3

Заголовки

Набор условий

Вот три базовых конструкции для множественных условий:

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

Фильтр отберёт строки, для которых выполняется хотя бы одно из условий, т.е. либо Позиция меньше 11, либо Количество больше 1000. Оператор ИЛИ можно использовать и для одного столбца. В таком случае, все критерии располагаются друг под другом в целевом столбце.

И: Если необходимо одновременное соблюдение всех критериев, применяется логический оператор И. Для этого критерии располагают на одной строке.

Данный пример отбирает строки, где в столбце Ключевые слова встречается слово «купить» и нет слова «самолёт», также значение в Позиция больше 7, а значение в Среднемесячное количество больше 1000. Помним, что звёздочка обозначает любое количество знаков. В нашем примере будут отобраны строки, в которых встречается «купить» на любой позиции внутри ячейки. Обратите внимание, что отсев идёт по слову «самолёт», «самолет» вполне проходит.

ОБА ОПЕРАТОРА: Можно блеснуть своим умением, применив оба оператора для установки критериев.

Такая таблица условий слегка обескураживает. Но этот фильтр всего лишь отбирает строки, в которых присутствует слово «купить» или «аэрофлот», при этом нет слов «самолет» или «самолёт», а значения последнего столбца больше 1000.

Примечание: Чтобы получить текст вместо формул, достаточно поставить апостроф перед операторами сравнения: ‘=купить или ‘<>самолёт. Майкрософт на своём сайте советует излишне сложный способ, предлагая заключить в двойные кавычки всё выражение, а перед ним ставить знак равенства: .

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

Промежуток: Если необходимо получить данные, лежащие в некотором промежутке, можно применить следующий приём:

Эти условия отберут ключевые слова, для которых Среднемесячное количество находится в промежутке от 3700 до 10000.

Формулы: Это действительно мощный инструмент. Вместо таблицы критериев можно использовать формулы.

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

Пара моментов, на которые необходимо обращать внимание при использовании формул:

  • Формула должна возвращать логическое значение ЛОЖЬ или ИСТИНА;
  • Заголовки над формулами должны отличаться от заголовков базы данных, можно вообще использовать пустую строку;
  • Формула должна ссылаться на ячейки первой строки под заголовками базы данных;
  • Ссылки на проверяемые ячейки базы должны быть относительными: вида С4, при абсолютных, таких как $C$4 — значение формул будет статичным, т.е. всегда ЛОЖЬ или всегда ИСТИНА.

Общий алгоритм

Пошаговый алгоритм работы с расширенным фильтром (полагаем, что данные и табличка условий уже подготовлена):

Шаг 1: Как упоминалось ранее, щёлкните любую ячейку внутри подготовленного набора данных и перейдите:

Данные > Сортировка и фильтр >Дополнительно для ПК. Для Мак: Данные > Сортировка и фильтр > Фильтр > Расширенный фильтр.

Откроется меню. Здесь приведён скриншот Excel 2013, но все пункты совпадают с Mac.

Шаг 2: Выберите, где будут размещаться отобранные данные. В большинстве случаев разумнее определить «другое место», это оставит неизменным исходный массив данных. Но выбор за вами.

Шаг 3: Для исходного диапазона Excel по умолчанию отбирает всю «таблицу». Это можно изменить, исключив ненужные столбцы. Но Excel позволит выбрать только прямоугольный диапазон. Чтобы отобрать столбцы с пропусками, введите названия целевых столбцов в свободных ячейках и укажите их адрес для диапазона вывода. В Дополнительных примерах приводится подробное пояснение.

Шаг 4: Для установки диапазона условий щёлкните внутри поля и выделите соответствующий диапазон на листе. Если нужно свернуть меню, нажмите на значок выбора диапазона справа от поля.

Шаг 5: Если выбран пункт Cкопировать результат в другое место, укажите адрес в поле Поместить результат в диапазон. Можно указать единичную ячейку, тогда она станет верхней левой выводимого диапазона, или выбрать ячейки с введёнными названиями нужных столбцов.

Шаг 6: Если нужны только уникальные записи поставьте флажок. К сожалению, большинство людей используют исключительно эту функцию.

Шаг 7: Жмём OK, смотрим на результат.

Шаг 8: Если список фильтровался на месте, можно сбросить фильтр, нажав Очистить, но это верно для ПК. Данный пункт расположен выше пункта Дополнительно. На Маке присутствует аналог — Очистить фильтр, но он недоступен. Ещё один довод в пользу фильтрации на новое место. Чтобы сбросить фильтр нужно отменить действие — нажать Command-Z, или подключить стандартный фильтр.

По цвету ячейки или текста

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

Выделяем весь диапазон, кликаем на кнопочку «Сортировка и фильтр» и выбираем из меню «Настраиваемая…» .

В следующем окне, уберите галочку с поля «Мои данные содержат заголовки» , если Вы выделили их без верхней строки, которая является шапкой таблицы. Затем выбираем столбец, по которому будем сортировать, в примере это «I» . В разделе «Сортировка» из выпадающего списка выбираем «Цвет шрифта» . В разделе порядок выбираем «красный цвет» – «Сверху» . Это мы отсортировали числа красного цвета.

Теперь нужно, чтобы в столбце шли числа зеленого цвета. Нажмите на кнопочку «Добавить уровень» . Все настройки те же, только выберите «зеленый цвет» . Нажмите «ОК» .

Наш столбец отсортирован следующим образом.

Как видите, числа идут не по порядку. Давайте отсортируем числа в порядке возрастания. Выделяем столбец, нажимаем «Сортировка и фильтр» – «Настраиваемая …» . В открывшемся окне нажмите на кнопку «Добавить уровень» . Столбец остается «I» , в следующем поле выбираем по «Значению» , порядок «По возрастанию» . Нажмите «ОК» .

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

Аналогичным образом сортируются данные и по цвету ячейки, только в разделе «Сортировка» выбирайте из списка «Цвет ячейки» .

Глава 6. Сортировка, фильтрация, срезы Таблиц Excel

Это продолжение перевода книги Зак Барресс и Кевин Джонс. Таблицы Excel: Полное руководство для создания, использования и автоматизации списков и таблиц (Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables by Zack Barresse and Kevin Jones. Published by: Holy Macro! Books. First printing: July 2014. – 161 p.).

Таблицы сортируются с помощью элементов управления Автофильтра. Когда Таблица сортируется, Excel автоматически ограничивает область сортировки диапазоном тела данных Таблицы, и не включает в сортировку строки, лежащие ниже Таблицы.

Ris. 6.1. Menyu sortirovki Tablits

Рис. 6.1. Меню сортировки Таблиц

Скачать заметку в формате Word или pdf, примеры в формате Excel

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

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

Вы можете просмотреть параметры сортировки, щелкнув раскрывающийся элемент Автофильтр в правой части каждой ячейки заголовка. На рис. 6.1 показано меню сортировки и фильтрации, которое появляется при нажатии раскрывающегося списка столбца Item.

Существует два вида раскрывающихся элементов управления, связанных с ячейками: список проверки данных и Автофильтр. Они выглядят одинаково, но расположены по разные стороны правой границы ячейки:

Ris. 6.2. Avtofiltr i spisok proverki dannyh

Рис. 6.2. Автофильтр и список проверки данных

Кроме того, раскрывающиеся элементы управления Автофильтром отображаются всегда, независимо от того, выбрана ячейка или нет. Элементы управления списком проверки отображаются только тогда, когда ячейка выбрана и активна.

Вы также можете получить доступ к командам сортировки, пройдя по меню Главная –> Редактирование –> Сортировка и фильтр.

Пользовательская сортировка

В нижней части меню Сортировать по цвету находится команда Пользовательская сортировка (рис. 6.3). К ней также можно получить доступ, пройдя по меню Данные –> Сортировка.

Ris. 6.3. Vyzov polzovatelskoj sortirovki

Рис. 6.3. Вызов пользовательской сортировки

При выборе этой команды Excel открывает диалоговое окно Сортировка, которое позволяет выполнять сортировку по нескольким столбцам и условиям:

Рис. 6.4. Диалоговое окно Сортировка для пользовательской сортировки; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Если столбец отсортирован значок Автофильтра изменяется, чтобы показать стрелку вверх (по возрастанию / по алфавиту) или вниз (по убыванию):

Ris. 6.5. Znachok Avtofiltra posle sortirovki

Рис. 6.5. Значок Автофильтра после сортировки

Фильтры

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

Excel позволяет выполнять фильтрацию несколькими способами: по цвету, по тексту/числу/дате.

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

Если столбец содержит в основном текстовые значения, Excel отображает меню Текстовые фильтры с разнообразными параметрами фильтрации:

Ris. 6.6. Tekstovye filtry

Рис. 6.6. Текстовые фильтры

Если столбец содержит в основном числовые значения, Excel отображает меню Числовые фильтры:

Ris. 6.7. CHislovye filtry

Рис. 6.7. Числовые фильтры

Если столбец содержит в основном значения дат, Excel отображает меню Фильтры по дате:

Ris. 6.8. Filtry dat

Рис. 6.8. Фильтры по дате

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

Когда Таблица отфильтрована, номера строк в левой части окна рабочего листа изменяются с черного на синий:

Ris. 6.9. Nomera strok otfiltrovannoj Tablitsy priobretayut sinij tsvet

Рис. 6.9. Номера строк отфильтрованной Таблицы приобретают синий цвет

Еще один способ узнать, применен ли фильтр (и какие столбцы фильтруются) – посмотреть на значки Автофильтра. Когда столбец фильтруется, значок изменяется на знак воронки (см. рис. 6.9).

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

Чтобы удалить фильтр из столбца, кликните стрелку Автофильтра и выберите пункт Удалить фильтр из столбца… Если столбец не фильтруется, эта команда отключена.

Срезы

Срезы были впервые представлены в Excel 2010 только для сводных таблиц. Начиная с Excel 2013 они доступны и для Таблиц. Срез – это визуальный интерактивный элемент управления фильтрацией. Срезы выводят уникальные значения одного столбца в виде кнопок. По умолчанию при нажатии кнопки Excel включает это значение и исключает все остальные. Если нажать клавишу Ctrl и одну из кнопок, а затем отпустить Ctrl, выберутся все кнопки, кроме нажатой. Если нажать и удерживать клавишу Shift (или Ctrl), и нажать несколько кнопок, то все, относящиеся к ним значения, будут показаны.

Чтобы добавить срез на лист Excel, кликните любую ячейку Таблицы и пройдите по меню Конструктор –> Вставить срез. Откроется диалоговое окно вставка срезов:

Рис. 6.10. Диалоговое окно Вставка срезов

Вы можете выбрать столбец(ы), для которого вы хотите вывести срез, а затем нажать ОК. Excel создает по одному срезу для каждого выбранного столбца. После создания среза Excel помещает его на рабочий лист. Если кликнуть по срезу, появится вкладка Параметры:

Ris. 6.11. Vkladka Parametry dlya nastrojki sreza

Рис. 6.11. Вкладка Параметры для настройки среза

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

Ris. 6.12. Nastrojka sreza

Рис. 6.12. Настройка среза

Вкладка ленты Инструменты для среза –> Параметры позволяет настроить визуальный стиль, количество столбцов, размеры среза, кнопок и др.

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

Ris. 6.13. Kontekstnoe menyu sreza Tablitsy

Рис. 6.13. Контекстное меню среза Таблицы

Если строка заголовка Таблицы отключена, вы не можете использовать срезы. Все срезы, связанные с Таблицей отображаются как устаревшие (рис. 6.14). Вы можете нажать кнопку Обновить, чтобы включить строку заголовка таблицы и сделать срезы активными.

Ris. 6.14. Neaktivnye srezy posle otklyucheniya zagolovka Tablitsy

Рис. 6.14. Неактивные срезы после отключения заголовка Таблицы

Временная шкала – это вариант среза, который специально предназначен для дат. Временные шкалы доступны только для сводных таблиц, но не для Таблиц Excel.

Фильтрация дат

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

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

фильтрация дат

В группе команд « Фильтр по дате » содержится большой перечень популярных запросов:

фильтр дат

Чтобы настроить произвольный интервал, выберите пункт МЕЖДУ .

Сортировка по указанным числовым значениям

Анализ проделанной работы, объёмов продаж, роста прибыли, успеваемости учащихся, закупки дополнительных материалов сопровождаются выделением параметров, имеющих максимальный и минимальный показатели. Конечно, если таблица небольшая, то пользователь сумеет просто так отыскать лучший показатель. Но в тех случаях, когда Excel имеет чрезмерно большое количество строк и столбцов, без применения встроенных функций, позволяющих отсортировать таблицу, найти желаемый показатель можно, но придётся потратить большое количество рабочего времени.

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

Фильтр по возрастанию и убыванию

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

Сортировка по возрастанию и убыванию

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

Итак, первоначально необходимо выделить таблицу, подлежащую проведению анализа. Далее следует перейти на новый лист, кликнуть правой кнопкой мыши, а затем кликнуть по строке «Специальная вставка». Перед пользователем на экране появится окно с параметрами, среди которых нужно выбрать параметр «Значение», после чего нажать «Ок».

Специальная вставка

Теперь дублирующий вариант создан, поэтому можно приступать к дальнейшим действиям. Чтобы полностью понять, как отсортировать таблицу в Excel по возрастанию, необходимо выделить вновь всю таблицу, затем перейти во вкладку «Данные», там среди нескольких инструментов будет находиться и желаемый «Сортировка», по которому нужно кликнуть.

Настройка критериев сортировки

В открывшемся окне параметров имеется строка «Мои данные содержат заголовки». Возле нее расположено маленькое окошко, в котором следует поставить галочку. Остаётся выставить в ниже открывшихся окнах столбец, который подлежит анализу, а также вариант желаемой сортировки: по возрастанию или убыванию. Далее соглашаемся с выставленными параметрами, после чего таблица продемонстрирует мгновенно желаемый результат, избавляя от многочасовой изнурительной работы.

Результаты сортировки

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

Как установить фильтр?

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

как сделать фильтр в excel от меньшего к большему

Во вкладке данные панели быстрого доступа выбрать иконку установить фильтр.

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

Нажимаем на треугольник фильтра, выбираем критерии фильтрации и нажимаем кнопку «ОК».

Подробное видео о фильтрах в Excel ниже:

голоса
Рейтинг статьи
Читайте так же:
Как удалить CPU Miner — инструкция от Averina.com
Ссылка на основную публикацию
Adblock
detector