Как перенумеровать отсортированный столбец, чтобы начать с нуля в Excel

Question

У меня есть 2 столбца чисел в Excel. После их сортировки я хочу преобразовать числа в отсортированные числа от нуля, который минимальное число конвертирует в 0, второе число конвертирует в 1 и .... Например, у меня есть эти две колонки:

и я хочу такой результат:

Кто-нибудь может помочь мне ?!

Спасибо

Answer 1

Предполагая, что ваши данные (как показано) в A1:B3

1. В D1 введите =SUMPRODUCT((A1>$A$1:$B$3)/COUNTIF($A$1:$B$3,$A$1:$B$3))

2. Заполните формулу до D3, затем до E3.

Если вы хотите деструктивно заменить исходные отсортированные данные, перейдите к необязательному шагу 3:

3. Выберите + Копировать D1:E3 -> Вставить как значения в A1.

---

Пояснение формулы:

Есть две ключевые части для понимания этой формулы.

1. (A1>$A$1:$B$3) генерирует массив булевых значений: TRUE для значений в A1: B3, которые меньше, чем A1, FALSE для значений, превышающих A1. Поскольку этот массив делится в формуле, Excel преобразует эти логические значения в единицы и нули.
2. SUMPRODUCT(1/COUNTIF($A$1:$B$3,$A$1:$B$3)) - это конструкция, используемая для подсчета уникальных значений в массиве. Это работает путем взвешивания каждого значения на 1/(the number of times the number appears in the array) . Это гарантирует, что сумма всех значений, связанных с этим значением, будет равна 1 . Подумай об этом так. Если X появляется n раз и каждый раз считается 1/n , сумма этих подсчетов будет n*(1/n)=1 . То есть каждое уникальное значение учитывается только один раз.

Итак, имея это в виду, рассмотрим пример формулы =SUMPRODUCT((B1>$A$1:$B$3)/COUNTIF($A$1:$B$3,$A$1:$B$3)) , который присваивает уникальную id 3 к значению 2000 в примере. Шаг за шагом, вот как оценивается формула:

1. =SUMPRODUCT((2000>{1001,1001,1002,2000,1002,1003})/COUNTIF($A$1:$B$3,$A$1:$B$3))
2. =SUMPRODUCT({TRUE,TRUE,TRUE,FALSE,TRUE,TRUE}/COUNTIF($A$1:$B$3,$A$1:$B$3))
3. =SUMPRODUCT({TRUE,TRUE,TRUE,FALSE,TRUE,TRUE}/COUNTIF({1001,1001,1002,2000,1002,1003},$A$1:$B$3))
4. =SUMPRODUCT({TRUE,TRUE,TRUE,FALSE,TRUE,TRUE}/{2,2,2,1,2,1})
5. =SUMPRODUCT({1/2,1/2,1/2,0/1,1/2,1/1})
6. =3

Answer 2

1. дублировать столбец A в столбец D
2. дублируйте все значения столбца B в столбец D, причем их назначение начинается с первой пустой ячейки ниже столбца A (теперь у вас есть все значения в столбце D)
3. выберите содержимое столбца D и удалите дубликаты
4. введите значение 0 в ячейку E1
5. введите значение 1 в ячейку E2
6. выберите ячейки E1:E2
7. дважды щелкните на маленьком квадрате, который появляется в правом нижнем углу границы выделения (последовательность чисел теперь расширена до последнего числа)
8. в ячейку типа F1 =VLOOKUP(A1;$D:$E;2;FALSE)
9. сфокусируйтесь на ячейке G1 и нажмите Ctrl+R
10. выделите ячейки F1:G1
11. дважды щелкните маленький квадрат, который появляется в нижнем правом углу границы выделения

Теперь у вас есть результаты в столбцах F и G. (Игнорировать оставшиеся значения #N/A внизу этих столбцов.)

Не стесняйтесь спрашивать, если что-то неясно.