Как легко ранжировать данные по абсолютному значению в 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 и СОРТИРОВКА (Excel 365 и Excel 2021+)
Если вы используете Excel 365 или Excel 2021, вы можете воспользоваться мощными динамическими функциями массива, такими как СОРТИРОВКА и 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 > Сортировка > Расширенная сортировка на ленте Excel.
< - В окне Расширенная сортировка диалогового окна настройте параметры сортировки:
- Выберите заголовок столбца, который вы хотите отсортировать, из раздела «Столбец»;
- В выпадающем меню «Сортировать по» выберите Абсолютное значение;
- В разделе «Порядок» выберите От наименьшего к наибольшему. Вы также можете выбрать От наибольшего к наименьшему, если предпочитаете порядок убывания.
- После того, как все настроено, нажмите кнопку OK кнопку.
<
Теперь значения в списке отсортированы на основе их абсолютных значений, сохраняя при этом свои исходные знаки. Это позволяет вам легко определить наименьшие или наибольшие значения по величине, независимо от того, являются ли они положительными или отрицательными.
- Мои данные содержат заголовки: Убедитесь, что этот флажок установлен, если ваши данные включают строку заголовков;
- Учет регистра: Применяется только к тексту; может быть не отмечен для числовой сортировки.
- Пустые ячейки в начале: Отметьте эту опцию, если хотите, чтобы пустые ячейки отображались вверху отсортированного списка.
🔚 Заключение
Ранжирование или сортировка данных по абсолютным значениям в Excel может предоставить более глубокие представления, особенно при анализе величины значений независимо от их знака. Это руководство представило несколько подходов для удовлетворения различных потребностей пользователей:
- Используйте функцию СУММПРОИЗВ для надежной формулы ранжирования, которая работает во всех версиях Excel.
- Воспользуйтесь функциями XMATCH и СОРТИРОВКА в 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек