Как сортировать в excel

Как отменить сортировку в Excel

В Excel предусмотрено три типа фильтров:

1. Автофильтр — для отбора записей по значению ячейки, по формату или в соответствии с простым критерием отбора.

Автофильтр

1. Выделить одну ячейку из диапазона данных.
2. На вкладке Данные найдите группу Сортировка и фильтр .
3. Щелкнуть по кнопке Фильтр .

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

1. Выбрать условие фильтрации.

Варианты фильтрации данных

• Фильтр по значению — отметить флажком нужные значения из столбца данных, которые высвечиваются внизу диалогового окна.
• Фильтр по цвету — выбор по отформатированной ячейке: по цвету ячейки, по цвету шрифта или по значку ячейки (если установлено условное форматирование).
• Можно воспользоваться строкой быстрого поиска
• Для выбора числового фильтра, текстового фильтра или фильтра по дате (в зависимости от типа данных) выбрать соответствующую строку. Появится контекстное меню с более детальными возможностями фильтрации:

1. При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… и др.
2. При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит…, начинается с… и др.
3. При выборе опции Фильтры по дате варианты фильтрации — завтра, на следующей неделе, в прошлом месяце и др.
4. Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… , используя который можно задать одновременно два условия отбора, связанные отношением И — одновременное выполнение 2 условий, ИЛИ — выполнение хотя бы одного условия.

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

Отмена фильтрации

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

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

Чтобы быстро снять фильтрацию со всех столбцов необходимо выполнить команду Очистить на вкладке Данные

Срезы

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

Создание срезов

В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.

Для этого нужно выполнить следующие шаги:

1. Выделить в таблице одну ячейку и выбрать вкладку Конструктор .
2. В группе Сервис (или на вкладке Вставка в группе Фильтры ) выбрать кнопку Вставить срез .

1. В диалоговом окне отметить поля, которые хотите включить в срез и нажать OK.

Форматирование срезов

1. Выделить срез.
2. На ленте вкладки Параметры выбрать группу Стили срезов , содержащую 14 стандартных стилей и опцию создания собственного стиля пользователя.

1. Выбрать кнопку с подходящим стилем форматирования.

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.

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

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

Задание условий фильтрации

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

Способ 3: Формула сортировки

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

Шаг 1: Создание вспомогательной формулы

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

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

Теперь диапазон выделенных ячеек имеет собственное имя, отвечающее за его содержимое, — в нашем случае это фрукты. Если вводите несколько слов в названии, не ставьте пробел, а используйте вместо него нижнее подчеркивание: «(пример_текста)».

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

Сейчас результатом этой формулы будет «1», поскольку ее запись не совсем верна для будущих расчетов, поэтому добавьте выражение

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

Переименуйте диапазон с числами в группу — это понадобится при составлении следующей формулы.

Шаг 2: Создание формулы сортировки

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

1. В новой ячейке начните вводить . Эта формула отвечает за поиск позиции строки, из-за чего и следует указать аргумент «A1».

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

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

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

Проверьте результат и затем растяните формулу так, как это уже было показано выше.

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

Для упрощения понимания отдельно предоставляем полную формулу:

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

Шаг 3: Модернизации формулы для повторных названий

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

1. Откройте вспомогательную формулу и уберите знак «, чтобы работал только при условии равенства.

Добавьте вторую часть — , позволяющую нормально записывать одинаковые слова в последовательном порядке.

Снова растяните формулу, чтобы она изменилась на всех ячейках.

Добавьте в список повторяющиеся названия, чтобы проверить их нормальное отображение.

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

По дате

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

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

Как настроить группировку строк и столбцов в Excel

Важно! Проблемы с выбором данного фильтра могут быть обусловлены некорректным форматом ячеек. Например, если дата будет введена простым числами в формате 01.01.2011, без указания формата ячейки “Дата”

Как сортировать по алфавиту в excel

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

Затем, на вкладке под названием «Главная», в разделе «Редактирование» кликаем по кнопке «Сортировка и фильтр»

А в всплывшем списке выбираем пункт с названием «Сортировка от А до Я».
Стоит обратить внимание, что выпадает окошко, в котором необходимо выбрать: по всей таблице либо в границах выделения.
Если необходимо осуществить лишь в одном из столбиков, следует проставить галочку на пункт «Сортировать в рамках выделения».

В зависимости от возрастания и убывания

Данный метод сортировки осуществляется так же, как по алфавиту. Различие будет лишь в наименовании функций: «По возрастанию», а также, «По убыванию».

По нескольким полям файла

Если сортировка необходима одновременно в нескольких столбиках и по нескольким параметрам, пригодится опция под названием «Настраиваемая сортировка». Рассмотрим ее.

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

Шапка окна содержит следующие кнопки:

1. Добавление уровня;
2. Удаление уровня;
3. Копирование уровня;
4. Клавиши вверх и вниз.

Говоря о том, каким именно образом будет осуществлена подобная сортировка, стоит заметить, что Эксель пойдет по списку сверху. Первый уровень является наиболее приоритетным. Например, нами выбрано «сортировать людей в зависимости от имени от А до Я – выполнено.

Затем, следующее условие состоит в сортировке списка в зависимости от возраста (по увеличению). Что же получается? Имена так и останутся рассортированные по алфавиту, однако люди-тёзки будут располагаться в определенном порядке – от самых младших к самым старшим.

Итак, прежде всего, были отсортированы строчки в зависимости от имен

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

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

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

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

Как отсортировать листы в алфавитном / буквенно-цифровом порядке в Excel?

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

Сортировка листов в алфавитном / буквенно-цифровом порядке с кодом VBA

В центре поддержки Майкрософт есть макрос для сортировки листов по альфа-каналу. Мы можем применить его, выполнив следующие шаги:

1.  Удерживая нажатой ALT + F11 ключи, и он открывает Microsoft Visual Basic для приложений окно.

2.  Нажмите Вставить > Модуль, а затем вставьте следующий макрос в Окно модуля.

VBA: сортировка листов в алфавитном / буквенно-цифровом порядке

Sub SortWorkBook()
'Updateby20140624
Dim xResult As VbMsgBoxResult
xTitleId = "KutoolsforExcel"
xResult = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) & "Clicking No will sort in Descending Order", vbYesNoCancel + vbQuestion + vbDefaultButton1, xTitleId)
For i = 1 To Application.Sheets.Count
    For j = 1 To Application.Sheets.Count - 1
        If xResult = vbYes Then
            If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
                Sheets(j).Move after:=Sheets(j + 1)
            End If
            ElseIf xResult = vbNo Then
                If UCase$(Application.Sheets(j).Name) < UCase$(Application.Sheets(j + 1).Name) Then
                    Application.Sheets(j).Move after:=Application.Sheets(j + 1)
            End If
        End If
    Next
Next
End Sub

3. нажмите F5 ключ для запуска этого макроса. В следующем окне подсказки щелкните Да, все листы будут отсортированы по возрастанию в алфавитном порядке; и нажмите Нет, все листы будут отсортированы по убыванию в алфавитном порядке.

Сортировка листов в алфавитном / буквенно-цифровом порядке с помощью Kutools for Excel

Если вы не знакомы с макросами или предпочитаете другие способы, вы можете попробовать Kutools for Excel. Kutools for Excel’s Сортировать листы инструмент может легко сортировать все листы.

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Нажмите Kutools Plus > Рабочий лист > Сортировать листы. Смотрите скриншот:

2. В Сортировать листы В диалоговом окне выберите один тип сортировки, который вам нужен на правой панели, например Альфа-сортировка, Буквенно-цифровая сортировка, а затем нажмите OK кнопка. Смотрите скриншот:

Затем все листы сортируются на основе указанного типа сортировки. Смотрите скриншот:

Kutools for Excel’s Сортировать листы инструмент может быстро переупорядочить все листы в активной книге. Он поддерживает несколько типов сортировки, в том числе Альфа-сортировка, Буквенно-цифровая сортировка, Цвет Сортировка и Обратный. Кроме того, вы также можете перемещать листы вверх / вниз и сбрасывать сортировку.

  Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

Способ 2: Настраиваемая сортировка

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

1. В том же разделе «Редактирование» нажмите кнопку «Настраиваемая сортировка».

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

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

Для параметра «Порядок» задайте значение «По возрастанию».

Если желаете отсортировать другие столбцы, вручную добавьте новый уровень и выполните те же действия.

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

Динамическая сортировка чисел

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

Допустим, необходимо отсортировать столбец простых чисел. Необходимо:

1. Поставить курсор в соседнюю ячейку, в столбце которой планируется выводиться отсортированные данные.
2. Ввести в нее формулу:

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

Таблицы

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

Давайте отсортируем класс в порядке возрастания, и таким же образом средний бал.

В окне сортировки ставим галочку в поле «Мои данные содержат заголовки». В разделе «Столбец» выбираем из списка «Класс», сортировка по «Значению», а порядок «По возрастанию».

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

Данные в таблице отсортированы.

Теперь в столбце «Имя» закрасим ячейки с мальчиками в синий цвет, ячейки с девочками в розовый. Чтобы не делать это для каждой ячейки в отдельности, прочтите статью, как выделить ячейки в Excel – в ней написано, как выделить несмежные ячейки.

Выполним сортировку этого столбца по цвету ячейки: сначала будут девочки, потом мальчики. Снова выделяем всю таблицу, жмем «Сортировка» – «Настраиваемая …».

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

Нажимаем на кнопку «Добавить уровень». В разделе «Столбец» выбираем «Имя», сортировка – «Цвет ячейки», порядок – «розовый», «Сверху».

Теперь с помощью стрелочек перемещаем данную строку наверх списка. Нажмите «ОК».

Таблица с отсортированными данными выглядит следующим образом.

Если Вы работаете с большими таблицами, можете также прочесть статьи по темам: как закрепить шапку в таблице Excel – чтобы при прокрутке она всегда отображалась вверху, и как сделать фильтрацию данных в Эксель – это позволит просматривать данные в таблице с конкретными параметрами.

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

Оценить статью:

(1 оценок, среднее: 5,00 из 5)

Об авторе: Олег Каминский

Вебмастер. Высшее образование по специальности «Защита информации». Создатель портала comp-profi.com. Автор большинства статей и уроков компьютерной грамотности

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

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

• Первыми идут цифры от меньшего к большему;
• Затем идут специальные символы ($, %, * и т.д.);
• За ними следуют знаки препинания;
• Далее начинаются алфавиты, сначала английский, затем русский. Также не учитывается регистр, т.е. заглавные и прописные буквы равны друг другу (А=а).

Для того чтобы выполнить упорядочивание, пройдите на вкладку «Данные» и выберите раздел «Сортировка и фильтр». Для сортировки предназначено 3 значка:

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

Рассмотрим все пункты подробнее.

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

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

Установка порядка первого столбца таблицы при выборе первого варианта:

Как видно строки сохранили свою целостность, и имена по-прежнему соответствуют своим фамилиям.

Те же самые действия, но при выборе второго варианта:

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

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

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

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

Для примера будем использовать следующую таблицу:

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

Выделите диапазон нашей таблицы и кликните по значку «Сорировка». Появится следующее окно:

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

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

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

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

Для нашего примера используем трехуровневую сортировку. Для первого уровня зададим сортировку по «Столбец1», порядок «От Я до А». Для второго уровня зададим сортировку по «Столбец2», порядок «По возрастанию». Для третьего уровня по «Столбец3», порядок «По убыванию». Параметры для сортировки везде «значения».

Рассмотрим получившийся результат:

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

Таким образом, можно задавать сортировку в Excel по значительному количеству столбцов.

Также в окне сортировки можно задавать различные параметры:

• Поставив флажок на поле «Мои данные содержать заголовки», программа учтет, что верхние ячейки диапазонов упорядочивать не нужно;
• Нажав кнопку «Параметры» Вы получите окно, в котором можно задать:

1. Возможность учета регистра (заглавные символы имеют больший порядковый номер по сравнению с прописными);
2. Выбрать сортировку по строкам или по столбцам.

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