Как легко ранжировать данные по абсолютному значению в Excel?
Ранжирование данных по абсолютному значению в Excel часто требуется при статистическом анализе, финансовом моделировании и сравнении данных. Хотя Excel предлагает встроенные функции ранжирования, они не поддерживают ранжирование по абсолютному значению напрямую. В этом подробном руководстве представлены несколько оптимизированных методов для эффективного решения этой задачи.
Ранжирование данных по Абсолютное значение с помощью формул
Если вам нужно ранжировать числа по их абсолютному значению (игнорируя знак), следующие формулы могут оказаться полезными.
✅Формула 1: с использованием функции СУММПРОИЗВ (все версии Excel)
Этот метод использует функцию СУММПРОИЗВ, доступную во всех версиях Excel.
Введите приведённую ниже формулу в пустую ячейку, где должен отображаться результат, а затем протяните её вниз, чтобы заполнить остальные ячейки и отобразить все ранги по абсолютному значению в порядке убывания, как показано на скриншоте.
=SUMPRODUCT(--(ABS(A2)<,ABS($A$2:$A$13)))+1
- ABS(A2): возвращает абсолютное значение для текущей ячейки A2.
- ABS($A$2:$A$13): возвращает абсолютные значения всех чисел в диапазоне A2:A13.
- ABS(A2)<ABS($A$2:$A$13): сравнивает, меньше ли абсолютное значение текущей ячейки каждого значения в диапазоне, и возвращает последовательность значений ИСТИНА/ЛОЖЬ.
- --(…): преобразует значения ИСТИНА/ЛОЖЬ в 1 и 0 (ИСТИНА = 1, ЛОЖЬ = 0).
- SUMPRODUCT(…): суммирует все единицы, чтобы определить количество значений, абсолютная величина которых превышает текущее.
- +1: ранжирование начинается с 1, то есть наибольшее абсолютное значение получает ранг 1.
- Чтобы присвоить ранги значениям по возрастанию их Абсолютное значение, используйте следующую формулу:
=SUMPRODUCT(--(ABS(A2)>,ABS($A$2:$A$13)))+1 - В более ранних версиях Excel, чтобы ввести формулы как формулы массива, необходимо нажать Ctrl + Shift + Enter. В Excel 365 или Excel 2021 достаточно просто нажать клавишу Enter.
✅Формула 2: с использованием функций XMATCH и SORT (Excel 365 и Excel 2021+)
Если вы используете Excel 365 или Excel 2021, вы можете применять мощные функции динамических массивов, такие как SORT и XMATCH, чтобы ранжировать числа по их абсолютному значению.
Введите приведённую ниже формулу в пустую ячейку, где должен отображаться результат, а затем протяните её вниз, чтобы заполнить остальные ячейки и отобразить все ранги по абсолютному значению в порядке убывания, как показано на скриншоте.
=XMATCH(ABS(A2), SORT(ABS($A$2:$A$13),,-1))
- ABS(A2): возвращает Абсолютное значение для Текущая ячейка A2.
- ABS($A$2:$A$13): преобразует все значения в диапазоне A2:A13 в их абсолютные величины.
- SORT(…,, -1): сортирует эти абсолютные значения по убыванию (знак «-1» указывает на порядок по убыванию).
- XMATCH(…): определяет позицию абсолютного значения текущего значения в отсортированном списке — именно эта позиция и представляет его ранг.
Чтобы присвоить ранги значениям по возрастанию их Абсолютное значение, используйте следующую формулу:
=XMATCH(ABS(A2), SORT(ABS($A$2:$A$13),,1))Сортировка данных по Абсолютное значение с помощью Kutools для Excel
Сортировка данных по абсолютному значению в Excel напрямую не поддерживается стандартными инструментами сортировки. Хотя эту задачу можно решить с помощью формул, они требуют дополнительных столбцов и подвержены ошибкам. С Kutools для Excel вы легко отсортируете данные по абсолютному значению всего за несколько щелчков — без формул и лишних усилий.
После установки Kutools для Excel выполните следующие действия:
- Выделите столбец со значениями, которые нужно отсортировать. Затем на ленте Excel выберите KUTOOLS PLUS > Сортировка > Расширенная сортировка.
< - В диалоговом окне Расширенная сортировканастройте параметры сортировки:
- Выберите заголовок столбца, по которому нужно выполнить сортировку, в разделе Столбец;
- В раскрывающемся меню Сортировать повыберите Абсолютное значение;
- В разделе Порядок выберите От наименьшего к наибольшему. При необходимости можно выбрать От наибольшего к наименьшему, чтобы задать порядок по убыванию.
- Когда все настройки заданы, нажмите кнопку ОК.
<
Теперь значения в списке отсортированы по их абсолютной величине, при этом исходные знаки сохраняются. Это позволяет легко находить наименьшие или наибольшие значения по модулю — независимо от того, положительные они или отрицательные.
- Включить заголовки: установите этот флажок, если ваши данные содержат строку заголовков;
- С учетом регистра: применяется только к тексту — при сортировке чисел этот параметр можно не изменять.
- Пустые ячейки в начале: установите этот флажок, чтобы пустые ячейки отображались вверху отсортированного списка.
🔚 Заключение
Ранжирование или сортировка данных по абсолютному значению в Excel позволяет глубже понять суть данных, особенно при анализе их величины независимо от знака. В этом руководстве представлены различные подходы, отвечающие разнообразным потребностям пользователей:
- Воспользуйтесь функцией СУММПРОИЗВ, чтобы создать надёжную формулу ранжирования, совместимую со всеми версиями Excel.
- Используйте функции XMATCH и SORT в Excel 365 и 2021+, чтобы создавать динамические и современные формулы.
- Для самого быстрого и удобного решения Kutools для Excel предлагает сортировку данных по абсолютному значению всего одним щелчком мыши — без необходимости писать формулы.
Выберите метод, который лучше всего подходит вашей версии Excel и рабочему процессу — будь то точный контроль с помощью формул или эффективность автоматизированных инструментов, таких как Kutools. Хотите освоить ещё больше полезных приёмов в Excel? На нашем сайте вас ждут тысячи обучающих материалов, которые помогут вам стать настоящим мастером Excel!
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек