Как легко ранжировать данные по абсолютному значению в 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): Сравнивает, является ли абсолютное значение текущей ячейки меньше каждого значения в диапазоне, возвращая серию значений TRUE/FALSE.
- --(...): Преобразует значения TRUE/FALSE в 1 и 0 (TRUE = 1, FALSE = 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 сделайте следующее:
- Выберите столбец, содержащий значения, которые вы хотите отсортировать. Затем щелкните Kutools Plus > Sort > Advanced Sort на ленте Excel.
< - В Advanced Sort диалоговом окне настройте параметры сортировки:
- Выберите заголовок столбца, который вы хотите отсортировать, из раздела Column;
- В выпадающем меню Sort On выберите Absolute value;
- В разделе Order выберите Smallest to Largest. Вы также можете выбрать Largest to Smallest, если предпочитаете порядок убывания.
- Когда всё настроено, нажмите кнопку OK .
<
Теперь значения в списке отсортированы по их абсолютным значениям, сохраняя при этом свои исходные знаки. Это позволяет вам легко определить наименьшие или наибольшие значения по величине независимо от того, являются ли они положительными или отрицательными.
- My data has headers: Убедитесь, что этот флажок установлен, если ваши данные включают строку заголовков;
- Case sensitive: Применяется только к тексту; может быть оставлено неотмеченным для числового сортировки.
- Blank cells in front: Поставьте этот флажок, если хотите, чтобы пустые ячейки отображались в начале отсортированного списка.
🔚 Заключение
Ранжирование или сортировка данных по абсолютным значениям в 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% и сократите сотни кликов мышью ежедневно!