Сортировка по сложному ключу в Excel

Обновлено: 23.11.2024

В. Можно ли сортировать столбец в Excel с помощью формул, а не с помощью инструмента сортировки на вкладке «Данные», чтобы процесс сортировки выполнялся автоматически при обновлении данных?

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

СОРТИРОВКА — это одна из нескольких новых функций, использующих преимущества новой функции динамических массивов Excel, которая недавно была выпущена в качестве бета-версии для некоторых участников программы предварительной оценки Office (см. раздел технических вопросов и ответов за февраль 2019 г. «Microsoft Excel: обновление до новейших функций Excel»). " для получения информации о том, как стать участником программы предварительной оценки Office). В качестве усовершенствования механизма вычислений Excel функция динамических массивов позволяет одной формуле создавать результаты, которые расширяются в другие ячейки, как показано в примере СОРТИРОВКИ ниже. Microsoft называет этот процесс "проливом" и называет все формулы Excel, дающие множественные результаты, формулами "разлитого массива".

Предыдущие выпуски Excel предоставляют аналогичную функциональность массива, но требуют, чтобы пользователь выделил весь выходной диапазон, а затем ввел формулу массива, нажав Ctrl+Shift+Enter. Новая функциональность динамических массивов стала проще, поскольку пользователю достаточно нажать клавишу Enter, диапазон вывода не нужно выбирать, а изменения в исходных данных автоматически отражаются без необходимости каждый раз повторно нажимать комбинацию клавиш Ctrl+Alt+Enter. исходные данные меняются.

Вот простой пример, объясняющий, как работает новая функция массива SORT. Столбцы A и B на снимке экрана ниже (ячейки A3:B17) содержат несортированные данные о продажах по штатам. Чтобы отсортировать эти данные с помощью формулы, а не инструмента «Сортировка» на вкладке «Данные», я помещаю курсор в ячейку D3 и ввожу формулу = СОРТИРОВКА (A3: B17), чтобы получить результаты отсортированного массива, показанные ниже в ячейках D3: E17. Преимущество такого подхода заключается в том, что по мере изменения данных в столбцах A и B в будущем результаты в столбцах D и E будут обновляться автоматически.


Формула =СОРТИРОВКА(A3:B17) использует настройки по умолчанию "сортировать по" и "порядок сортировки"; таким образом, список отсортирован в алфавитном порядке. Синтаксис новой функции SORT: =SORT(массив, [индекс_сортировки], [порядок_сортировки], [по_столбцу]) . Первый аргумент идентифицирует массив для сортировки. Все остальные аргументы являются необязательными. Второй аргумент определяет, по какому столбцу будет отсортирован массив. Значение по умолчанию для [sort_index] равно 1, поэтому формула SORT в приведенном выше примере приводит к сортировке массива по первому столбцу. Третий аргумент определяет, будет ли сортировка по возрастанию (введите 1) или по убыванию (введите -1). По умолчанию — по возрастанию. Поэтому, если вы хотите отсортировать по «Продажам», чтобы получить список продаж по штатам, ранжированным от самого высокого к самому низкому, вы должны отсортировать по второму столбцу в порядке возрастания, в результате чего получится формула = СОРТИРОВКА (A3: B17, 2, - 1 ).

Четвертый аргумент, [by_column] , определяет, сортирует ли формула строки данных или столбцы данных. Поскольку сортировать строки данных гораздо чаще, как в примере слева, по умолчанию для четвертого аргумента используется значение FALSE .

Вот еще несколько комментариев о функциях динамических массивов:

<р>1. Скоро в Office 365: ожидается, что функции динамических массивов будут выпущены для всех участников программы предварительной оценки Office и подписчиков Office 365 в этом году, возможно, даже до того, как этот выпуск JofA попадет в почтовые ящики. Эта функция не была включена в приобретаемую версию Excel 2019 и не ожидается.

<р>3. Включение таблиц: при использовании формулы массива SORT может оказаться полезным сначала преобразовать исходные данные в таблицу Excel (используя параметр меню «Таблица» на вкладке «Вставка»), пример которого показан ниже. Обратите внимание, что формула массива в ячейке D26 ссылается на Table2, которая изображена слева в столбцах A и B. Преимущество этого подхода заключается в том, что он позволяет автоматически обновлять результаты СОРТИРОВКИ при добавлении строк или столбцов в конец Table2.

(Примечание. Формулы массива не будут работать в ячейках таблицы Excel; все формулы на основе массива должны располагаться в ячейке листа Excel подальше от любых таблиц Excel.)

<р>
4.Поведение массива: после того, как вы создадите формулу массива, а затем выберете ячейку (или ячейки) в области разнесенного массива, Excel выделит диапазон разбросанного массива рамкой, как показано вокруг ячеек D26: E31 на снимке экрана выше. Граница исчезает, когда вы выбираете ячейку за пределами области разлива. Кроме того, редактировать можно только первую ячейку в области разнесенного массива. Когда вы выбираете другую ячейку в области массива с разбросом, эта формула становится видимой в строке формул в виде серого текста (называемого фантомным текстом), но ее нельзя изменить. Чтобы обновить формулу, необходимо отредактировать самую верхнюю левую ячейку в диапазоне массива (ячейка D26). Затем Excel автоматически обновит все результаты перенесенного массива, когда вы нажмете клавишу Enter, и размер области перенесенного массива изменится по мере необходимости.

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

Изучение основ сортировки в Excel дает прочную основу для реализации более сложных задач сортировки.

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

Я буду использовать Excel 2013 в системе Windows 7. На рисунке A показан пример набора данных. Вы можете загрузить версию .xlsx или .xls или работать с любым собственным простым набором данных. Я буду предлагать инструкции для других версий, если они существенно отличаются, но в большинстве случаев вы сможете работать с этой статьей независимо от вашей версии.

Рисунок А

Это наш пример данных.

Перед сортировкой

Что вы делаете перед сортировкой, важно; Excel опирается на три вещи:

  • Диапазон сортировки
  • Ключ сортировки
  • Порядок сортировки

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

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

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

Сортировать

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

  1. Нажмите внутри столбца, содержащего ключ сортировки (в диапазоне данных).
  2. Перейдите на вкладку "Главная".
  3. Выберите параметр «Сортировка и фильтр» в группе «Редактирование».
  4. Выберите порядок сортировки.
  5. Если вы используете Excel 2003, на стандартной панели инструментов есть две кнопки сортировки. Для более сложных параметров выберите "Сортировка" в меню "Данные".

Сортировка по одному ключу

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

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


Изображение: Microsoft

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

Я использую Excel в Office 365 в 64-разрядной системе Windows 10, но вы можете применить эти методы сортировки к более ранним версиям. Пользовательские сортировки не поддерживаются в браузерной версии. Вы можете использовать свои собственные данные или загрузить демонстрационные файлы .xlsx и .xls.

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

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

Чтобы найти эти параметры, перейдите на вкладку «Данные», а затем выберите параметр «Сортировка» в разделе «Сортировка». Группа фильтров. Затем нажмите кнопку «Параметры», чтобы открыть диалоговое окно, показанное на рисунке A. (Для доступа к этим настройкам необходимо выбрать диапазон значений.)

Рисунок А

Показать расширенные параметры сортировки Excel.

  • С учетом регистра
  • Сортировать сверху вниз
  • Сортировать слева направо

Как видите, по умолчанию установлен только расширенный параметр Сортировать сверху вниз, что имеет смысл. Как правило, это направление сортировки — сверху вниз (а не слева направо).

Начнем с параметра "С учетом регистра". На рис. B показаны результаты двух сортировок: сортировки по умолчанию, не зависящей от регистра, и сортировки с учетом регистра. Как видите, строчные символы сортируются перед прописными. (Я выбрал диапазоны сортировки перед сортировкой, и если есть соседние столбцы, вам будет предложено развернуть или сохранить текущий выбор.)

Рисунок Б

С учетом регистра символы нижнего регистра сортируются перед прописными.

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

Рисунок C

Вы можете сортировать слева направо.

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

Как использовать функцию пользовательской сортировки Excel

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

  1. Перейдите на вкладку "Файл" и выберите "Параметры".
  2. Выберите «Дополнительно» на левой панели.
  3. В разделе «Общие» нажмите «Редактировать настраиваемые списки». Вы увидите встроенные списки Excel — вы, вероятно, уже пользуетесь некоторыми и, возможно, никогда не знали, что используете пользовательскую сортировку.
  4. В элементе управления "Записи списка" введите список в порядке сортировки (рис. D): "Маленький", "Маленький", "Средний", "Большой", "Очень большой". (Если список длинный или уже существует как обычные данные, вы можете указать ссылку на ячейку и нажать «Импорт».)
  5. Нажмите «Добавить», и Excel перенесет новую пользовательскую сортировку в пользовательские списки слева.
  6. Дважды нажмите "ОК".

Рисунок D

Введите размеры в том порядке, в котором вы хотите их отсортировать.

Чтобы применить пользовательское программное обеспечение, выберите список, который нужно отсортировать. Затем перейдите на вкладку «Главная», нажмите «Сортировать и усилить». Отфильтруйте в группе «Редактирование» и выберите «Пользовательская сортировка» в раскрывающемся списке. Если данные существуют в соседних столбцах, вам будет предложено расширить выбор, но не выбирайте «Продолжить с текущим выбором» и нажмите «Сортировать». В раскрывающемся списке «Порядок» выберите «Пользовательский список» (рис. E). Выберите список размеров, показанный на рисунке F, и дважды нажмите OK. На рисунке G показан отсортированный список.

Рисунок E

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

Рисунок F

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

Рисунок G

Excel сортирует список в соответствии с пользовательской сортировкой списка.

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

Отправьте мне свой вопрос об Office

Еженедельный информационный бюллетень Microsoft

Будьте инсайдером Microsoft в своей компании, прочитав эти советы, рекомендации и памятки по Windows и Office.

Как отсортировать ячейки по абсолютным значениям в Excel?

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

  • Повторное использование всего: добавляйте наиболее часто используемые или сложные формулы, диаграммы и другие объекты в избранное и быстро используйте их повторно в будущем.
  • Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
  • Инструменты объединения: несколько книг и листов в одну; Объединение нескольких ячеек/строк/столбцов без потери данных; Объединить повторяющиеся строки и суммировать.
  • Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Одна рабочая книга для нескольких файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
  • Вставить, пропуская скрытые/отфильтрованные строки; Подсчет и сумма по цвету фона; Массовая рассылка персонализированных электронных писем нескольким получателям.
  • Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировка по неделям, дням, частоте и т. д.; Фильтр по полужирному шрифту, формулам, комментариям.
  • Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!

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

<р>1. В соседней пустой ячейке, например, B2, введите эту формулу =ABS(A2), см. снимок экрана:

<р>2. Затем нажмите клавишу Enter и выберите ячейку B2, а затем перетащите маркер заполнения в диапазон, который вы хотите содержать в этой формуле, и все числа были преобразованы в абсолютные значения. Смотрите скриншот:

<р>3. Затем нажмите «Данные» > «Сортировка от А до Я» или «Сортировка от Я до А», как вам нужно, см. снимок экрана:

<р>4. Появится диалоговое окно «Предупреждение о сортировке», установите флажок «Расширить выбор». Смотрите скриншот:

<р>5. Затем нажмите кнопку «Сортировать», и все числа будут отсортированы по абсолютным значениям. Смотрите скриншот:

<р>6. После получения отсортированного результата вы можете удалить вспомогательный столбец B по своему усмотрению.

Если вы установили Kutools for Excel, функция расширенной сортировки может помочь вам более прямо и просто сортировать ячейки по абсолютным значениям.

Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатно попробовать без ограничений в течение 30 дней. Получить сейчас

Шаг 1. Выберите ячейки, которые необходимо отсортировать по их абсолютным значениям, и нажмите Предприятие > Расширенная сортировка. Смотрите скриншот:

Шаг 2. Укажите параметр в диалоговом окне «Расширенная сортировка». Смотрите скриншот:

  • Выберите столбец, который вы хотите отсортировать, и, если столбец имеет заголовок, установите флажок Мои данные имеют заголовки;
  • Выберите Абсолютное значение в раскрывающемся списке Сортировать по;
  • Выберите порядок по возрастанию или убыванию в разделе «Порядок».


Шаг 3. Нажмите «ОК», ячейки будут отсортированы по абсолютным значениям. Смотрите скриншот:

Читайте также: