Excel - сортировка по формуле при наличии повторяющихся значений

Question

Мои данные изменятся, поэтому я использую динамические списки. Сортировка должна быть динамичной. Я не хочу нажимать на встроенную функцию сортировки.

Я сортирую динамические данные по формуле, но происходит сбой, когда сортировка данных одинакова. Например:

A B Marham 240 Sigonella 400 Toulon 400 Ghedi 150 Lajes 2000 Nordholz 400

Сортируя по столбцу B и возвращая значения столбца A, результат должен быть:

Lajes 2000 Sigonella 400 Toulon 400 Nordholz 400 Marham 240 Ghedi 150

Примечание: меня не волнует порядок дублирования значений между собой ("400").

С этой формулой:
{=INDEX($A$1:$A$6, MATCH(SMALL(COUNTIF($B$1:$B$6, "<"&$B$1:$B$6), ROW(1:1)), COUNTIF($B$1:$B$6, "<"&$B$1:$B$6), 0))}

Результаты:

Lajes 2000 Sigonella 400 Sigonella 400 Sigonella 400 Marham 240 Ghedi 150

MATCH сбивает меня с толку. Как мне обойти это?

Answer 1

Вы можете обойти дубликаты, добавив скрытый столбец C, в котором вы добавляете к своим значениям очень маленькие цифры, достаточно малые, чтобы их нельзя было получить другими средствами и не мешали исходным значениям, что позволяет различать дубликаты, например

  A          B          C
Marham      240      240,0001
Sigonella   400      400,0002
Toulon      400      400,0003
Ghedi       150      150,0004
Lajes      2000     2000,0005
Nordholz    400      400,0006

Затем вы просто сортировать по значениям в столбце C

В качестве альтернативы, если вам не нужны скрытые столбцы, вы можете поместить значения следующим образом:

  A            B
Marham      240,0001
Sigonella   400,0002
Toulon      400,0003
Ghedi       150,0004
Lajes      2000,0005
Nordholz    400,0006

но отформатируйте поля для отображения числовых значений с точностью до 0 цифр, поэтому Excel сохраняет ваши подробные значения, но отображает их как:

  A          B
Marham      240
Sigonella   400
Toulon      400
Ghedi       150
Lajes      2000
Nordholz    400

Answer 2

Вот что я придумал ... Это не совсем то, что я искал ... но это сработало.

Используя те же данные выше, я изменил INDEX чтобы он возвращал счет вместо баз:

{=INDEX($B$1:$B$6, MATCH(SMALL(COUNTIF($B$1:$B$6, "<"&$B$1:$B$6), ROW(1:1)), COUNTIF($B$1:$B$6, "<"&$B$1:$B$6), 0))}

Затем, чтобы вернуть базы, я использовал эту формулу, которая возвращает несколько совпадающих значений:

{=index(A$1:A$6,small(if(b$1:b$6=b1,row(b$1:b$6)),countif(b$1:b$6,b1)))}

Я все еще хотел бы заставить оригинальную формулу работать самостоятельно, если у кого-то есть предложение.

Answer 3

Я знаю, что это было всегда, но формулы в других ответах не работали для меня. Тем не менее, я заставил его работать с несколькими незначительными изменениями:

{=INDEX($A$1:$A$7,SMALL(IF(B$1:B$6=B1,ROW(B$1:B$6)),COUNTIF(B1:B$6,B1)))}

Answer 4

Как насчет =sort(a2:b7,2,true) в ячейке C2?