Excel сортировка по возрастанию чисел с буквами
Если вам нужно отсортировать список, то к вашим услугам куча способов, самый простой из которых - кнопки сортировки на вкладке или в меню Данные (Data - Sort) . Бывают, однако, ситуации, когда сортировку списка нужно делать автоматически, т.е. формулами. Такое может потребоваться, например, при формировании данных для выпадающего списка, при вычислении данных для диаграмм и т.д. Как же "на лету" сортировать список формулой?
Способ 1. Числовые данные
Если список содержит только числовую информацию, то его сортировку можно легко сделать с помощью функций НАИМЕНЬШИЙ (SMALL) и СТРОКА (ROW) :
Функция НАИМЕНЬШИЙ (SMALL) выдергивает из массива (столбец А) n-й по счету наименьший элемент. Т.е. НАИМЕНЬШИЙ(A:A;1) - это самое маленькое число из столбца, НАИМЕНЬШИЙ(А:А;2) - второе по счету наименьшее и т.д.
Функция СТРОКА (ROW) выдает порядковый номер строки для указанной ячейки, т.е. СТРОКА(А1)=1, СТРОКА(A2)=2 и т.д. В данном случае она используется просто как генератор последовательности чисел n=1,2,3… для нашего отсортированного списка. С тем же успехом можно было сделать дополнительный столбец, заполнить его вручную числовой последовательностью 1,2,3… и ссылаться на него вместо функции СТРОКА.
Способ 2. Текстовый список и обычные формулы
Если в списке не числа, а текст, то функция НАИМЕНЬШИЙ (SMALL) уже не сработает, поэтому придется пойти другим, чуть более длинным, путем.
Сначала добавим служебный столбец с формулой, где будет вычисляться порядковый номер каждого имени в будущем отсортированном списке с помощью функции СЧЁТЕСЛИ (COUNTIF) :
В английской версии это будет:
Ну, и наконец, осталось просто вытащить из списка имена по их номерам. Для этого можно использовать такую формулу:
Функция ПОИСКПОЗ (MATCH) ищет в столбце В нужный порядковый номер (1, 2, 3 и т.д.) и выдает, по сути, номер строки, где находится это число. Функция ИНДЕКС (INDEX) вытаскивает из столбца А имя по этому номеру строки.
Способ 3. Формула массива
Этот способ представляет собой, по сути, тот же алгоритм расстановки, что и в Cпособе-2, но реализованный формулой массива. Для упрощения формулы диапазону ячеек С1:С10 было дано имя List (выделить ячейки, нажать Ctrl+F3 и кнопку Создать):
В ячейку Е1 копируем нашу формулу:
Или в англоязычной версии:
и нажимаем Ctrl+Shift+Enter, чтобы ввести ее как формулу массива. Потом полученную формулу можно скопировать вниз на всю длину списка.
Если нужно, чтобы формула учитывала не фиксированный диапазон, а могла подстраиваться при дописывании новых элементов к списку, то нужно будет слегка изменить стратегию.
Во-первых, диапазон List нужно будет задать динамически. Для этого при создании нужно указать не фиксированный диапазон C3:C10, а специальную формулу, которая будет ссылаться на все имеющиеся значения независимо от их количества. Нажмите Alt+F3 или откройте вкладку Формулы - Диспетчер имен (Formulas - Name Manager) , создайте новое имя и в поле Ссылка (Reference) впишите вот такую формулу (я предполагаю, что диапазон сортируемых данных начинается с ячейки C1):
Этот метод представит определяемую пользователем функцию (VBA) для извлечения чисел только из чисел с префиксом или суффиксом, а затем вы сможете легко сортировать.
1, нажмите другой + F11 одновременно клавиши, чтобы открыть окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модули, а затем вставьте ниже код VBA в новое открывающееся окно модуля.
Функция, определяемая пользователем: извлекать числа только из текстовых строк
3. Сохраните код и закройте окно Microsoft Visual Basic для приложений.
4. Выберите пустую ячейку рядом с исходными числами с префиксом или суффиксом, говорит Ячейка B2, введите формулу = число (A2) (A2 - это число с префиксом или суффиксом) в него, а затем перетащите дескриптор автозаполнения этой ячейки в нужный диапазон.
5. Поскольку извлеченные числа хранятся в виде текста, нам необходимо преобразовать их в числа. Скопируйте все извлеченные числа, щелкните правой кнопкой мыши скопированный диапазон и выберите Вставить значения из контекстного меню. Смотрите скриншот:
Внимание: В Excel 2007 вы можете скопировать извлеченные числа, сохраненные в виде текста, а затем щелкнуть Главная > макаронные изделия > Вставить значения.
6. Продолжайте выбирать эти числа, а затем нажмите рядом с выбранными числами и выберите Преобразовать в число из раскрывающегося списка. Смотрите скриншот:
7. Не снимая выделения с новых чисел, нажмите Данные > Сортировать от наименьшего к наибольшему or Сортировать от большего к меньшему; а затем проверьте Расширить выбор во всплывающем диалоговом окне Предупреждение о сортировке и щелкните значок Сортировать кнопка. Смотрите скриншоты:
8. Теперь числа с буквенным префиксом или суффиксом были отсортированы как обычная сортировка чисел (см. Снимок экрана ниже). При необходимости удалите извлеченные числа.
Сортировка чисел с текстовым / буквенным префиксом или суффиксом путем извлечения чисел
Собственно, Kutools for Excel также предоставляет аналогичные ВЫДЕРЖКИ функция для извлечения чисел из текстовых строк. Следовательно, мы можем сортировать числа с буквенным префиксом или суффиксом, сначала извлекая числа.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1. Выберите пустую ячейку рядом с числами с буквенным префиксом или суффиксом, говорит Ячейка B2, введите формулу = EXTRACTNUMBERS (A2; ИСТИНА) (A2 - это ячейка числа с буквенным префиксом или суффиксом) в нее, а затем перетащите дескриптор автозаполнения этой ячейки в нужный диапазон. Смотрите скриншот:
2. Не снимая выделения с выделенных чисел, щелкните Данные > Сортировать от наименьшего к наибольшему or Сортировать от большего к меньшему; проверить Расширить выбор во всплывающем диалоговом окне Sort Warning (Предупреждение о сортировке), а затем щелкните Сортировать кнопка. См. Скриншоты ниже:
3. Теперь числа с буквенным префиксом или суффиксом были отсортированы как обычная сортировка чисел. И вы можете удалить все извлеченные числа по мере необходимости.
Сортировка чисел с текстовым / буквенным префиксом или суффиксом путем разделения текста и чисел
Kutools for Excel также поддерживает разделение текста и чисел из ячеек по его Разделить клетки утилита. Таким образом, мы можем разделить текст и числа и поместить их в разные столбцы для упрощения сортировки. Пожалуйста, сделайте следующее:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1. Выберите числа с буквенным префиксом или суффиксом и нажмите Кутулс > Текст > Разделить клетки. См. Снимок экрана ниже:
2. В открывшемся диалоговом окне "Разделить ячейки" установите флажок Разделить на столбцы вариант в Тип раздел, проверьте Текст и номер в Разделить на и нажмите Ok кнопка. Смотрите скриншот выше:
3. Теперь появляется второе диалоговое окно Split Cells. Выберите ячейку рядом с исходными числами с буквенным префиксом или суффиксом, говорит «Ячейка B2», и щелкните значок OK кнопку.
Теперь тексты и числа разделены и помещены рядом с исходными числами с префиксом или суффиксом. См. Снимок экрана ниже.
4. Выберите разделенные числа и нажмите Данные > Сортировать от наименьшего к наибольшему or Сортировать от большего к меньшему; а затем проверьте Расширить выбор во всплывающем диалоговом окне Предупреждение о сортировке и щелкните значок Сортировать кнопка. См. Скриншоты ниже:
5. Теперь числа с буквенным префиксом или суффиксом были отсортированы как обычная сортировка чисел. Вы можете удалить разделенный текст и цифры по своему усмотрению.
Сортировка чисел с текстовым / буквенным префиксом или суффиксом, удаляя буквы
Этот метод представит Kutools for Excel's Удалить символы утилита, чтобы удалить все нечисловые символы (включая буквы) и оставить числа только в ячейках. И затем вы можете легко сортировать числа с текстовым / буквенным префиксом или суффиксом.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1. Скопируйте все числа с буквенным префиксом или суффиксом, а затем вставьте рядом с исходными числами с буквенным префиксом или суффиксом.
2. Продолжайте выбирать вставленные числа, нажмите Кутулс > Текст > Удалить символы, а затем только проверить Нечисловой во всплывающем диалоговом окне «Удалить символы» и нажмите кнопку Ok кнопка. См. Снимок экрана ниже:
3. Теперь в новом столбце остались только цифры. Продолжайте выбирать эти числа и нажмите Данные > Сортировать от наименьшего к наибольшему or Сортировать от большего к меньшему; а затем проверьте Расширить выбор во всплывающем диалоговом окне Предупреждение о сортировке и щелкните значок Сортировать кнопка. См. Скриншоты ниже:
4. Удалите числа по мере необходимости.
Демо: сортировка чисел с текстовым / буквенным префиксом или суффиксом в Excel
Как отсортировать числа в ячейке в Excel?
Для нас легко и часто сортировать числа в списке столбцов, но пробовали ли вы когда-нибудь сортировать числа в одной ячейке? Возможно, для вас нет хорошего способа, кроме как расположить их по одному, здесь я расскажу о том, как сортировать числа в ячейках в Excel.
Сортировка чисел в ячейках с помощью формулы
Чтобы отсортировать числа в ячейках на листе, вы можете применить следующую длинную формулу, сделайте следующее:
1. Рядом со своими данными введите следующую формулу, в этом примере я введу ее в ячейку C1, см. Снимок экрана:
=TEXT(SUM(SMALL(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1))))*10^(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))))),REPT("0",LEN(A1)))
2. Затем нажмите Shift + Ctrl + Enter вместе, затем перетащите маркер заполнения в диапазон, в котором вы хотите применить эту формулу, и вы увидите, что числа отсортированы от меньшего к большему. Смотрите скриншот:
Ноты:
1. Если цифра числа в ячейке больше 15, эта формула не даст правильного результата.
2. Если вы хотите отсортировать числа в порядке убывания, вы можете использовать эту формулу: =TEXT(SUM(LARGE(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1))))*10^(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))))),REPT("0",LEN(A1))).
3. В приведенных выше формулах A1 указывает ячейку, содержащую числа, которые вы хотите отсортировать, вы можете изменить ее по своему усмотрению.
Сортировка чисел в ячейках с помощью функции, определяемой пользователем
Поскольку у формулы есть некоторые ограничения, вы можете использовать следующие Функция, определяемая пользователем для сортировки чисел в ячейках длиной более 15 цифр.
1. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.
Код VBA: сортировка чисел в ячейках
3. Затем сохраните и закройте этот код, вернитесь на свой рабочий лист и введите эту формулу = sortnumsincell (A1) в пустую ячейку рядом с вашими данными, см. снимок экрана:
4. Затем перетащите маркер заполнения в ячейки, которые вы хотите содержать эту формулу, и все числа в ячейках были отсортированы в порядке возрастания, как показано на следующем снимке экрана:
Внимание: Если вы хотите отсортировать числа в порядке убывания, введите эту формулу = sortnumsincell (A1,1).
Сортировка чисел, разделенных запятыми, в ячейках с кодом VBA
Если ваши числа разделены определенными символами, такими как запятая, точка с запятой, точка и т. Д., Как показано на следующем снимке экрана, как вы можете отсортировать их по ячейкам? Теперь я представляю вам код VBA, чтобы вы могли их отсортировать.
1. Удерживайте ALT + F11 , чтобы открыть Окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.
Код VBA: номера сортировки разделяются запятыми внутри ячеек
3, Затем нажмите F5 ключ для запуска этого кода, а затем выберите ячейки, которые содержат числа в всплывающем окне подсказки, см. снимок экрана:
4, Затем нажмите OK, все числа в ячейках отсортированы по возрастанию в исходном диапазоне.
Внимание: Вы можете заменить запятую «,» на любые другие символы в приведенном выше коде. И этот код может только сортировать данные по возрастанию.
Сортировка данных на листе позволяет изменять их порядок и быстрее находить нужные значения. Можно отсортировать диапазон или таблицу данных по одному или нескольким столбцам данных. Например, вы можете отсортировать список сотрудников сначала по отделам, а затем по фамилиям.
Как сортировать данные в Excel?
Выделение данных для сортировки
Выделите диапазон табличных данных, например A1:L5 (несколько строк и столбцов) или C1:C80 (один столбец). Этот диапазон может включать и заголовки столбцов.
Быстрая сортировка
Выделите одну ячейку в столбце, в котором требуется выполнить сортировку.
Чтобы выполнить сортировку по возрастанию (от А до Я или от наименьшего числа к наибольшему), на вкладке Данные в группе Сортировка и фильтрация нажмите кнопку .
Чтобы выполнить сортировку по убыванию (от Я до А или от наибольшего числа к наименьшему), нажмите кнопку .
Сортировка по указанным условиям
Эта возможность позволяет выбрать столбец для сортировки, а также другие критерии, например цвет шрифта или ячейки.
Выделите одну любую ячейку в диапазоне, для которого требуется выполнить сортировку.
Нажмите на вкладке Данные в группе Сортировка и фильтрация кнопку Сортировка. Откроется диалоговое окно "Сортировка".
Выберите в раскрывающемся списке Сортировать по первый столбец, для которого вы хотите выполнить сортировку.
Выберите в списке Сортировка пункт Значения, Цвет ячейки, Цвет шрифта или Значок ячейки.
В списке Порядок выберите порядок сортировки, который вы хотите применить. Можно сортировать текст и числа по возрастанию и по убыванию: от А до Я или от Я до А для текста и от наименьшего к наибольшему или от наибольшего к наименьшему для чисел.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel для iPad Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Еще. Меньше
Функция СОРТ сортирует содержимое диапазона или массива.
В этом примере используется отдельная сортировка по регионам, продавцам и продуктам с помощью формулы =СОРТ(A2:А17), скопированной в ячейки F2, H2 и J2.
Примечание: Эта функция в настоящее время доступна обладателям подписки на Microsoft 365 в Актуальном канале. Она будет доступна обладателям подписки на Microsoft 365 в Полугодовом канале (корпоративном) начиная с июля 2020 г. Дополнительные сведения о том, как функции будут выпускаться для обладателей подписки на Microsoft 365, см. в статье Когда мне станут доступны самые новые возможности Microsoft 365.
Функция СОРТ возвращает отсортированный массив элементов в массиве. Возвращаемый массив имеет ту же форму, что и предоставленный аргумент массива.
=СОРТ(массив;[индекс_сортировки];[порядок_сортировки];[по_столбцу])
Диапазон или массив для сортировки
[индекс_сортировки]
Число, указывающее строку или столбец, по которым нужно выполнить сортировку.
[порядок_сортировки]
Число, указывающее нужный порядок сортировки: 1 для сортировки по возрастанию (по умолчанию), -1 для сортировки по убыванию
Логическое значение, указывающее нужное направление сортировки: ЛОЖЬ для сортировки по строкам (по умолчанию), ИСТИНА для сортировки по столбцам
Если аргумент "индекс_сортировки" не указан, предполагается строка 1 или столбец 1. Если не указан порядок сортировки, применяется сортировка по возрастанию. По умолчанию Excel выполняет сортировку по строкам, а сортировка по столбцам применяется, если аргументу "по_столбцу" присвоено значение ИСТИНА. Если аргументу "по_столбцу" присвоено значение ЛОЖЬ или он отсутствует, Excel выполняет сортировку по строкам.
Функция СОРТ предназначена для сортировки данных в массиве. Если вы хотите отсортировать данные в сетке, лучше использовать функцию СОРТПО, поскольку она удобнее. Функция СОРТПО учитывает добавления или удаления столбцов, так как она ссылается на диапазон, а функция СОРТ ссылается на номер индекса столбца.
Массив может рассматриваться как строка со значениями, столбец со значениями или комбинация строк и столбцов со значениями. В приведенном выше примере исходным массивом для формулы СОРТ является диапазон A5:D20.
Функция СОРТ возвращает массив, который будет рассеиваться, если это будет конечным результатом формулы. Это означает, что Excel будет динамически создавать соответствующий по размеру диапазон массива при нажатии клавиши Enter. Если ваши вспомогательные данные хранятся в таблице Excel, тогда массив будет автоматически изменять размер при добавлении и удалении данных из диапазона массива, если вы используете Структурированные ссылки. Дополнительные сведения см. в статье Поведение рассеянного массива.
Примеры
Сортировка диапазона значений по убыванию.
Совместное использование функций СОРТ и ФИЛЬТР, чтобы отсортировать диапазон по возрастанию и ограничить его значениями, превышающими 5000.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Читайте также: