Как выполнить ранжирование по двум столбцам в Excel?
При работе с наборами данных Excel, содержащими имена и два разных набора оценок — например, результаты тестов, баллы за проекты или данные о продажах за несколько периодов, — нередко возникает необходимость ранжировать имена на основе комбинации этих двух показателей. Такая задача особенно актуальна, когда требуется оценить общий уровень успеваемости или совокупные достижения: при присуждении стипендий, определении лучших торговых представителей или объединении результатов двух этапов соревнования. Однако Excel не предлагает встроенной функции для ранжирования по нескольким столбцам, поэтому для получения нужного результата приходится применять креативные подходы.
Ранжирование по двум столбцам
Один из эффективных способов ранжирования имён по двум столбцам с оценками — применение пользовательской формулы, учитывающей оба набора данных. Обычно первая оценка выступает в качестве основного критерия, а вторая помогает разрешать ничьи. Такой подход особенно полезен, когда важно сохранить исходный порядок среди записей с одинаковыми основными оценками за счёт дополнительного показателя эффективности.
Чтобы применить этот метод, выполните следующие действия:
1. Выберите пустую ячейку, в которую нужно поместить результаты ранжирования. Например, выберите ячейку D2.
2. Введите следующую формулу:
=RANK(B2,$B$2:$B$7)+SUMPRODUCT(--($B$2:$B$7=$B2),--(C2<$C$2:$C$7))Эта формула сначала определяет ранг по первому столбцу оценок. Если встречаются одинаковые значения (ничья), она корректирует ранг с учётом второй оценки, упорядочивая связанные записи по вторичному критерию. Часть формулы с SUMPRODUCTотвечает за разрешение таких ситуаций: она подсчитывает случаи, когда первые оценки совпадают, но вторая оценка ниже.
3. Нажмите Enter, чтобы подтвердить формулу.
4. Перетащите маркер заполнения вниз, чтобы применить формулу ко всем остальным ячейкам столбца D.
В этой формуле B2 и C2 ссылаются на первые ячейки данных в первом и втором столбцах оценок соответственно, а диапазоны $B$2:$B$7 и $C$2:$C$7 охватывают все имеющиеся оценки. Если ваш диапазон данных отличается, обязательно скорректируйте эти ссылки.
Этот метод отлично подходит для небольших и средних наборов данных, где разрешение ничьих осуществляется просто на основе следующего столбца с оценками. Однако если вам предстоит обрабатывать большие объёмы данных или использовать более сложные правила ранжирования, рассмотрите следующие альтернативные подходы.
Код VBA — автоматизация ранжирования на основе двух столбцов для крупных наборов данных или настраиваемых правил ранжирования
Если вы работаете с большими объёмами данных или вам необходимо автоматизировать ранжирование на основе собственной логики — например, с учётом дополнительных условий или применения к разным диапазонам, — VBA станет практичным решением. Этот подход идеален, когда ручное использование формул становится слишком громоздким или когда требуется пакетная обработка нескольких листов.
1. Щёлкните Инструменты разработчика > Visual Basic. Когда откроется окно Microsoft Visual Basic for Applications, нажмите Вставка > Модуль и скопируйте приведённый ниже код в модуль:
Sub Rank_Two_Columns()
Dim lastRow As Long
Dim ws As Worksheet
Dim rngScore1 As Range, rngScore2 As Range, rngRank As Range
Dim i As Long, j As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row 'Assume scores are in columns B and C
Set rngScore1 = ws.Range("B2:B" & lastRow)
Set rngScore2 = ws.Range("C2:C" & lastRow)
Set rngRank = ws.Range("D2:D" & lastRow)
For i = 1 To rngScore1.Rows.Count
Dim rankCount As Long
rankCount = 1
For j = 1 To rngScore1.Rows.Count
If rngScore1.Cells(j, 1).Value > rngScore1.Cells(i, 1).Value Then
rankCount = rankCount + 1
ElseIf rngScore1.Cells(j, 1).Value = rngScore1.Cells(i, 1).Value Then
If rngScore2.Cells(j, 1).Value > rngScore2.Cells(i, 1).Value Then
rankCount = rankCount + 1
End If
End If
Next j
rngRank.Cells(i, 1).Value = rankCount
Next i
End SubЭтот код ранжирует строки по первой оценке, а в случае ничьей использует вторую оценку для разрешения спора. Предполагается, что первые оценки находятся в столбце B, вторые — в столбце C, а результаты ранжирования выводятся в столбец D (начиная с ячейки D2). При необходимости скорректируйте буквы столбцов или диапазоны в соответствии с расположением ваших данных.
2. Чтобы выполнить код, закройте редактор VBA, вернитесь в Excel и нажмите Alt + F8, чтобы открыть диалоговое окно макросов, выберите Rank_Two_Columns и нажмите Выполнить. Ранги появятся в столбце D.
Перед запуском макросов убедитесь, что они включены в Excel, и всегда заранее сохраняйте свою работу — действия макросов отменить невозможно. Для особенно крупных наборов данных это решение на VBA может выполнить ранжирование значительно быстрее, чем ручное копирование формул.
Если возникают ошибки вроде «Индекс за пределами диапазона» или ранги не отображаются, убедитесь, что в диапазоне нет пустых строк и что столбцы с оценками корректно указаны в коде.
Другие встроенные методы Excel — использование вспомогательных столбцов для объединения двух оценок и последующего ранжирования
Для пользователей, предпочитающих обходиться без формул с массивной логикой или VBA, ранжирование можно легко реализовать с помощью встроенных инструментов сортировки Excel и вспомогательного столбца. Этот метод особенно удобен, когда оба столбца оценок содержат числовые значения и вы хотите применить взвешенную или конкатенированную логику сортировки.
Вот как это сделать:
1 в качестве вспомогательного.
2. В первой строке вспомогательного столбца (например, D2) введите формулу, которая уникально ранжирует каждую строку. Вы можете объединить две оценки или применить весовые коэффициенты, если одна оценка должна иметь больший вес, чем другая. Например, если первая оценка учитывается на 60 %, а вторая — на 40 %, введите:
=B2*0.6+C2*0.43. Нажмите Enter и скопируйте эту формулу во все строки ниже.
4. Выделите все данные (включая имена и оба столбца с оценками).
5. Перейдите на вкладку Данные и выберите Сортировка. В диалоговом окне сортировки укажите вспомогательный столбец в качестве ключа и выберите порядок От наибольшего к наименьшему.
6. Теперь ваши данные упорядочены согласно комбинированной логике ранжирования.
Этот метод не требует сложных формул или сценариев и удобен, когда нужно применить разные веса к критериям ранжирования. Однако имейте в виду: при объединении чисел в текст (например, преобразовании оценок 90 и 88 в 9088) он будет работать некорректно, если оценки содержат разное количество цифр. Для создания уникальных и логичных рангов взвешенные суммы или масштабированные вычисления обычно надёжнее.
Если вам нужен столбец с рангами, вы можете дополнительно применить функцию RANK к значениям вспомогательного столбца. Например, в ячейке E2 введите:
=RANK(D2,$D$2:$D$7)Затем перетащите формулу ранжирования, чтобы заполнить все строки.
Примечание: обязательно убедитесь, что метод вычисления во вспомогательном столбце точно соответствует заданному правилу ранжирования. Этот подход идеально подходит для простого ранжирования по «общему баллу» или когда нужен быстрый и наглядный способ сортировки данных.
В заключение, ранжирование данных по двум столбцам в Excel можно выполнить с помощью формул — для динамических и простых сценариев, макросов VBA — для масштабных или настраиваемых задач, а также вспомогательных столбцов в сочетании с сортировкой — для простого взвешенного или конкатенированного ранжирования. Всегда проверяйте результаты выборочной сверкой рангов, особенно после сортировки или запуска сценариев. Если результаты оказываются несогласованными, убедитесь, что в ваших данных нет скрытых символов, объединённых ячеек, пустых строк или чисел, отформатированных как текст.
Раскройте магию Excel с помощью KUTOOLS AI
- Интеллектуальное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это доступно через простые команды.
- Пользовательские формулы: создавайте индивидуальные формулы для оптимизации рабочих процессов.
- Программирование на VBA: Пишите и внедряйте код VBA легко и без усилий.
- Анализ формул: Легко разбирайтесь даже в самых сложных формулах.
- Перевод текста: Ломайте языковые барьеры прямо в ваших таблицах.
Связанные статьи
- Как быстро отсортировать список по определённому значению в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек