Сортировка дробных чисел в 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):
В Excel есть две универсальные кнопки, позволяющие выполнять сортировку по алфавиту, возрастанию или убыванию, если речь идет о числах. Ими пользоваться проще всего, если есть уже готовая таблица, для которой и производится сортировка. Учитывайте, что при добавлении новых значений сортировка сбивается, а для предотвращения возникновения подобной ситуации нужно запускать ее повторно или применять Способ 3 этой статьи.
-
Сначала зажмите левую кнопку мыши и выделите все значения, которые хотите отсортировать в порядке возрастания.
Способ 2: Настраиваемая сортировка
Настраиваемая сортировка подойдет при работе с несколькими значениями в таблице, когда необходимо не только отсортировать один ряд по возрастанию, но и задействовать алфавитную сортировку или другие ее типы, присутствующие в Excel. Основной процесс настройки при использовании этого инструмента выглядит нехитрым образом.
- В том же разделе «Редактирование» нажмите кнопку «Настраиваемая сортировка».
Способ 3: Формула для динамической сортировки
В завершение разберем более сложный, но гибкий способ, подразумевающий создание вспомогательной формулы, которая будет сравнивать значения в таблице и выводить на новых ячейках числа по возрастанию. Преимущество этого метода перед остальными заключается в том, что формула автоматически расширяется при добавлении новых значений в таблицу, а значит, происходит их динамическая сортировка.
-
Активируйте первую ячейку для формулы и введите =НАИМЕНЬШИЙ . Это основная функция, которая автоматически и рассчитает необходимые значения.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Как отсортировать десятичные числа в столбце?
Если у вас есть список десятичных чисел, со встроенной функцией сортировки в Excel данные будут сортироваться по значению обычным образом. Но иногда вам нужно, чтобы данные были отсортированы по числу справа от десятичной точки, как показано на следующем снимке экрана. У вас есть хороший способ решить эту задачу в Excel?
Сортировка десятичных чисел в списке с помощью вспомогательного столбца
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Чтобы отсортировать десятичные числа на основе числа справа от десятичной точки, вы должны создать вспомогательный столбец, а затем отсортировать данные по новому вспомогательному столбцу, сделайте следующее:
1. Введите эту формулу: =INT(A2)&"."&TEXT(RIGHT(A2,LEN(A2)-(FIND(".",A2))),"000") в пустую ячейку рядом с вашими данными, B2, например, см. снимок экрана:
2. Затем перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите содержать эту формулу, и отобразятся данные нового столбца, а затем щелкните Данные > Сортировать чтобы отсортировать базу данных по этому новому вспомогательному столбцу, см. снимок экрана:
3. В выскочившем Предупреждение о сортировке коробка, пожалуйста, выберите Расширить выбор вариант, см. снимок экрана:
4. Затем нажмите Сортировать кнопку, а в Сортировать диалоговом окне выберите вспомогательный столбец, который вы создали из Сортировать по: раскрывающийся список, а затем выберите Ценности из Сортировать по раздел, наконец, выберите порядок сортировки под заказ раздел, см. снимок экрана:
5, Затем нажмите OK кнопка, другая Предупреждение о сортировке всплывает окно подсказки, выберите Сортировка чисел и чисел, сохраненных в виде текста отдельно вариант, см. снимок экрана:
6. Затем нажмите OK кнопку, и данные были отсортированы по номеру справа от десятичной точки по мере необходимости. Смотрите скриншот:
7. Наконец, вы можете удалить содержимое вспомогательного столбца по мере необходимости.
Как отсортировать числа в ячейке в 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, данные будут сортироваться по значению обычным образом. Но иногда вам нужно, чтобы данные были отсортированы по числу справа от десятичной точки, как показано на следующем снимке экрана. Есть ли у вас хороший способ решить эту задачу в Excel?
Сортировка десятичных чисел в списке с помощью вспомогательного столбца
Сортировка десятичных чисел в списке с помощью вспомогательного столбца
Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Сэкономьте 50% своего времени и уменьшите тысячи щелчков мышью каждый день!
Чтобы отсортировать десятичные числа на основе числа справа от десятичной точки, вы должны создать вспомогательный столбец, а затем отсортируйте данные по новому вспомогательному столбцу, сделайте следующее:
1 . Введите эту формулу: = INT (A2) & «.» & TEXT (RIGHT (A2, LEN (A2) — (FIND («.», A2))), «000») в пустая ячейка рядом с вашими данными, например B2 , см. снимок экрана:
2. Затем перетащите маркер заполнения вниз к ячейкам, которые вы хотите содержать эту формулу, и отобразятся данные нового столбца, и затем нажмите Data > Sort , чтобы отсортировать базу данных по этому новому вспомогательному столбцу, см. снимок экрана:
3. В всплывающем окне Предупреждение о сортировке выберите вариант Развернуть выделение , см. снимок экрана:
4 . Затем нажмите кнопку Сортировка и в диалоговом окне Сортировка выберите вспомогательный столбец, созданный вами с помощью Сортировать по раскрывающийся список, а затем выберите Значения в разделе Сортировка , наконец, выберите порядок сортировки в разделе Порядок раздел, см. снимок экрана:
5 . Затем нажмите кнопку OK , появится еще одно окно Предупреждение о сортировке , выберите Сортировать числа и числа, сохраненные в виде текста по отдельности , см. снимок экрана:
6 . Затем нажмите кнопку OK , и данные будут отсортированы по номеру справа от десятичной точки, как вам нужно.. См. Снимок экрана:
7 . Наконец, вы можете удалить содержимое вспомогательного столбца по своему усмотрению.
Читайте также: