Servisneva.ru

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

Как сортировать и фильтровать данные в Excel

Функция сортировки будет полезна в случае необходимости упорядочивания значений ячеек по алфавиту либо по возрастанию/убыванию

Функция сортировки будет полезна в случае необходимости упорядочивания значений ячеек по алфавиту либо по возрастанию/убыванию

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

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

Правила совместной «работы» нескольких условий отбора

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

Но главных правил — всего два! Все остальные случаи — различные сочетания этих двух правил.

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

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

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

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

Задача №6:

Отфильтровать информацию о пластинах массой менее 0,1 тонны по всей базе.

База данных-расширенный фильтр в Excel-03-13s

В результате работы расширенного фильтра показаны все пластины базы данных, имеющие массу менее 0,1 тонны (иллюстрация правила №1).

Задача №7:

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

База данных-расширенный фильтр в Excel-04-13s

Задача решена – показаны записи обо всех пластинах базы данных и обо всех изделиях тяжелее 1 тонны (иллюстрация правила №2).

Для отмены действия расширенного фильтра необходимо выполнить команду главного меню программы Excel «Данные» — «Фильтр» — «Отобразить все».

Обращаю внимание на необходимость внимательного контроля корректности указания исходного диапазона базы данных и диапазона таблицы условий в выпадающем диалоговом окне «Расширенный фильтр»!

В частности, в последнем примере необходимо указать: «Диапазон условий: $A$1:$F$3»!

Если в диапазоне условий или в вашей базе окажутся полностью пустые строки, то расширенный фильтр работать не будет!

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

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

Читать еще:  Ищем аналоги Скайпа

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

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

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

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

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

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

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

Другие виды

Сортировка в excel обладает широким набором дополнительных возможностей. Рассмотрим подробнее.

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

Сортировка в excel7

Вот так выглядит список фруктов с двумя видами представления данных:

Сортировка в excel8

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

Сортировка в excel9

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

Сортировка в excel10

Получаете следующий результат сортировки по годам:

Сортировка в excel11

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

  1. По цвету ячейки или шрифта. Поскольку Microsoft Excel обладает большим набором инструментов для настройки формата ячеек, в том числе и цвет, то и сортировка также возможна по этому параметру.

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

Сортировка в excel12

Нажимаете ОК и получаете отсортированные оценки по цветам:

Сортировка в excel13

Такой же принцип упорядочивания данных по цвету шрифта.

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

Сортировка в excel14

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

Сортировка в excel15

  1. Динамическая сортировка. Этот способ подходит для более опытных пользователей редактора Excel, поскольку для работы используются сложные формулы с использованием различных функций. Такой вид обладает несомненным преимуществом – при изменении исходного массива данных, отсортированный также изменяется под новые условия. То есть процесс автоматизирован и не требует постоянно корректировки.
Читать еще:  Как вставить текст в презентацию в PowerPoint: инструкция

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

Поиск выбросов с помощью функций НАИБОЛЬШИЙ / МАЛЕНЬКИЙ

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

Если есть какие-либо выбросы, вы сможете их идентифицировать, не просматривая все данные в обоих направлениях.

Предположим, у нас есть приведенный ниже набор данных, и мы хотим знать, есть ли какие-либо выбросы.

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

Точно так же второе по величине значение будет равно

Если вы не используете Microsoft 365, в котором есть динамические массивы, вы можете использовать приведенную ниже формулу, и она даст вам пять наибольших значений из набора данных с помощью одной единственной формулы:

Точно так же, если вам нужны 5 наименьших значений, используйте следующую формулу:

или следующее, если у вас нет динамических массивов:

Когда у вас есть эти значения, очень легко обнаружить любые выбросы в наборе данных. Хотя я решил извлечь 5 наибольших и наименьших значений, вы можете выбрать 7 или 10 в зависимости от размера вашего набора данных.

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

1. Expression.Sort – метод Sort объекта Worksheet возвращает объект Sort.

2. .SortFields.Clear – метод SortFields объекта Sort возвращает коллекцию объектов SortFields. Метод Clear объекта SortFields удаляет все существующие объекты SortField.

3. .SortFields.Add Key, SortOn, Order, DataOption – метод Add объекта SortFields создает и возвращает новый экземпляр объекта SortField с заданными параметрами.

Параметры метода Add объекта SortFields:

Key – обязательный параметр, который задает значение ключа для сортировки. Тип данных – Range. Обычно указывается первая ячейка столбца при сортировке по строкам или первая ячейка строки при сортировке по столбцам. Сортировка диапазона будет осуществлена по данным столбца (строки), первая ячейка которого указана в качестве ключа.

SortOn – необязательный параметр, который задает критерий сортировки (по какому свойству ячеек производится сортировка).

Значения, которые может принимать SortOn:

КонстантаЗначениеОписание
SortOnValuesсортировка по значению (значение по умолчанию)
SortOnCellColor1сортировка по цвету ячейки
SortOnFontColor2сортировка по цвету шрифта
SortOnIcon3сортировка по иконке*

* Иконки (значки) могут быть заданы ячейкам при условном форматировании диапазона.

Order – необязательный параметр, задающий порядок сортировки (по возрастанию или по убыванию).

Значения, которые может принимать Order:

КонстантаЗначениеОписание
xlAscending1сортировка по возрастанию (значение по умолчанию)
xlDescending2сортировка по убыванию

DataOption – необязательный параметр, который задает способ сортировки текста.

Значения, которые может принимать DataOption:

КонстантаЗначениеОписание
xlSortNormalчисловые и текстовые данные сортируются отдельно (значение по умолчанию)
xlSortTextAsNumbers1текстовые данные рассматриваются для сортировки как числовые

4. .SetRange [Range] – метод SetRange объекта Sort задает диапазон (таблицу), в котором выполняется сортировка.

5. .Header = [xlGuess, xlYes, xlNo] – свойство Header объекта Sort указывает, является ли первая строка таблицы строкой заголовков (шапкой).

Значения, которые может принимать свойство Header:

КонстантаЗначениеОписание
xlGuessExcel сам определяет, есть ли строка заголовков
xlYes1строка заголовков есть, сортировка ее не затрагивает
xlNo2строки заголовков нет (значение по умолчанию)

6. .MatchCase = [True, False] – свойство MatchCase объекта Sort указывает, как учитывать регистр при сортировке.

Значения, которые может принимать свойство MatchCase:

КонстантаЗначениеОписание
Falseрегистр не учитывается (значение по умолчанию)
True1сортировка с учетом регистра

7. .Orientation = [xlTopToBottom, xlLeftToRight] – свойство Orientation объекта Sort задает ориентацию для сортировки.

Значения, которые может принимать свойство Orientation:

КонстантаЗначениеОписание
xlTopToBottom1сортировка по стокам (значение по умолчанию)
xlLeftToRight2сортировка по столбцам

8. .Apply – метод Apply объекта Sort выполняет сортировку диапазона в соответствии с примененными параметрами.

Устранение неполадок с форматами дат в Excel

Иногда, когда вы пытаетесь отсортировать в Excel по дате, вы можете обнаружить, что не можете заставить Excel распознавать данные как дату. Представьте, например, что кто-то дает вам электронную таблицу с датами. Вы поместили данные в Excel, но они не сортируются правильно, когда вы пытаетесь использовать описанные выше методы. Почему не работает?

Рассмотрим этот список дат:

Выглядит достаточно просто. Однако, когда вы выбираете данные, нажимаете кнопку «Сортировка и фильтр» и выбираете сортировку от самых старых до самых новых, это не работает! Вы можете получить что-то вроде этого:

Обратите внимание, что последняя дата в столбце отсортирована неправильно. Что, черт возьми, происходит?

Скорее всего, даже если данные в ячейке A9 выглядит как и дата, она не форматируется как дата, поэтому Excel не может ее правильно отсортировать. В зависимости от того, какую версию Excel вы используете, даже если вы отформатируете ошибочную ячейку как дату, выбрав ячейку, щелкнув правой кнопкой мыши и выбрав Формат ячеек и выбрав параметр Дата, Все еще не решит проблему.

Как исправить даты, которые не будут правильно отсортированы в Excel

К счастью, эту проблему легко исправить.

  1. Вырежьте столбец дат, выбрав ячейки и нажав Ctrl + x, выбрав «Вырезать» в главном меню или щелкнув правой кнопкой мыши по ячейке (ям) и выбрав «Вырезать».

  1. Откройте Блокнот или другой текстовый редактор.
  2. Вставьте даты в текстовый редактор.

  1. Вернитесь в Excel, выберите столбец, в котором раньше были даты, щелкните правой кнопкой мыши и выберите Формат ячеек.
  2. Выберите «Дата» и нажмите «ОК».

  1. Теперь вернитесь в текстовый редактор, выберите и скопируйте список дат.

  1. Вернитесь в Excel и вставьте даты в столбец, который вы отформатировали на шаге 5.
  2. Теперь у вас должна быть возможность сортировать. Выберите столбцы с данными, нажмите кнопку «Сортировка и фильтр» и выберите «Сортировать от старых к новым». Альт! Оно работает!

голоса
Рейтинг статьи
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector