☰

Поиск наибольшего числового значения из диапазона гибридных данных в Excel

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-365
Поиск наибольшего числового значения из диапазона гибридных данных в Excel

У меня есть строка из 6 буквенно-цифровых значений, как на картинке. Мне нужна формула, которая определит три самых высоких числовых значения в каждой строке, а затем отобразит буквы, связанные с этими значениями, в правильном порядке (по убыванию). Например, строка 1 дает ответ RES, так как R является самым высоким в строке, за ним следует E, а затем S. Если есть совпадение (как выше), предпочтение отдается первому появившемуся значению. Я базовый пользователь Excel, и это поставило меня в тупик. Я могу делать элементы решения, но ему это не нравится, когда я пытаюсь объединить. Благодарен за вашу помощь.

введите описание изображения здесь

решение1

введите описание изображения здесь

Как это работает:

Мои исходные данные находятся в диапазоне A2:F3.

  • Напишите эту формулу в ячейке B6, чтобы разделить алфавиты из исходных данных и заполнитьВерно.

      =LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)

  • Чтобы разделить числа из исходных данных, введите эту формулу в ячейку B7, заполнитеВправо, затем вниз.

     =VALUE(RIGHT(A2,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1))

  • В ячейке B10напишите эту Формулу массива, заполнитеВернозатем нажмите F2и закончите с Ctrl+Shift+Enterи заполнитеВниз.

    {=LARGE(B7:G7,{1,2,3})}

  • Запишите эту формулу в ячейку B13.ВернозатемВниз.

       =IFERROR(INDEX(B$6:$G$6,MATCH(B10,B7:$G7,0)),"")

  • Наконец, в ячейке B16напишите эту формулу и заполнитевниз.

=CONCATENATE(B13,C13,D13)

При необходимости отрегулируйте ячейки в формуле.

решение2

Предположим, что ваши данные «R35» находятся по адресу A1.

делать

H1  --->  =VALUE(RIGHT(A1,LEN(A1)-1))

и перетащите до тех пор L1, пока , затем

N1  --->  =IF(COUNTIF($H1:$L1,H1)=1,H1,H1+0.5)
O1  --->  =IF(COUNTIF($H1:$L1,I1)=1,I1,I1+0.4)
P1  --->  =IF(COUNTIF($H1:$L1,J1)=1,J1,J1+0.3)
Q1  --->  =IF(COUNTIF($H1:$L1,K1)=1,K1,K1+0.2)
R1  --->  =IF(COUNTIF($H1:$L1,L1)=1,L1,L1+0.1)

затем

T1  --->  =RANK(N1,$N1:$R1,0)

и перетащите до тех пор X1, пока , затем

Z1  --->  =INDEX($A1:$F1,MATCH(1,$T1:$X1,0))
AA1  --->  =INDEX($A1:$F1,MATCH(2,$T1:$X1,0))
AB1  --->  =INDEX($A1:$F1,MATCH(3,$T1:$X1,0))

затем

AD1  --->  =LEFT(Z1)&LEFT(AA1)&LEFT(AB1)

наконец.. выберите H1:AD1и перетащите до AD6.

Столбец AD должен быть тем, что вы ищете. Вы можете скрыть столбцы или сделать это на другом листе, чтобы это выглядело проще.

Пожалуйста, поделитесь, если вы застряли (в понимании формулы или ее выполнении). ( :

Надеюсь, поможет.

p/s: +0,5, +0,4 .. +0,1 используется для удовлетворения этого требования

первый появившийся получает преимущество

решение3

Мне нужно было 6 вспомогательных ячеек без использования VBS. Так что если ваши данные находятся в A1 через F1:

установите G1 на

=INT(RIGHT(A1,2)&"006")

установите H1 на

=INT(RIGHT(B1,2)&"005")

установите I1 на

=INT(RIGHT(C1,2)&"004")

установите J1 на

=INT(RIGHT(D1,2)&"003")

установите K1 на

=INT(RIGHT(E1,2)&"002")

установите L1 на

=INT(RIGHT(F1,2)&"001")

и М1 к

=LEFT(INDIRECT(ADDRESS(ROW(),MATCH(LARGE(G1:L1,1),G1:L1,0))),1)&LEFT(INDIRECT(ADDRESS(ROW(),MATCH(LARGE(G1:L1,2),G1:L1,0))),1)&LEFT(INDIRECT(ADDRESS(ROW(),MATCH(LARGE(G1:L1,3),G1:L1,0))),1)

Вы должны иметь возможность скопировать и вставить эти 7 формул вниз по строкам. Обратите внимание, что повторяющиеся значения обрабатываются слева направо.

Связанный контент