Сопоставить абсолютное значение и вернуть весь столбец EXCEL

У нас есть ряд с ориентировочными числами, из которых извлекаются два наибольших абсолютных значения. Под этими ориентировочными числами у нас есть данные, охватывающие n строк. Теперь мы хотели бы получить строки для каждого из этих самых больших ориентировочных значений.

Например:

-1.6 2.5 0.5 1.2
gh  bh  dh  jh
12  45  45  89

Следующие формулы находят первые два самых больших значения (скажем, в P1 и Q1):

=LARGE(ABS($A$1:$D$1),1)
=LARGE(ABS($A$1:$D$1),2)

Теперь приведенная ниже формула работает, если самые большие индикаторы ПОЗИТИВНЫ:

=INDEX(A:D,0,MATCH($Q$1,$A$1:$D$1,0))

Мы не можем заставить его работать для индикативов, когда они ОТРИЦАТЕЛЬНЫ. Любые советы будут оценены. Спасибо.

Ожидаемый результат в этом конкретном случае должен выглядеть так:

-1.6 2.5
gh  bh
12  45

person AK88    schedule 18.08.2017    source источник
comment
Вы пробовали =ИНДЕКС(A:D,0,ABS(MATCH($Q$1,$A$1:$D$1,0))) ?   -  person Tony M    schedule 18.08.2017
comment
Ага. #N/A вернулся   -  person AK88    schedule 18.08.2017


Ответы (1)


используйте эту формулу массива:

=INDEX(A:D,0,MATCH($Q$1,ABS($A$1:$D$1),0))

Поскольку это формула массива, ее необходимо подтвердить с помощью Ctrl-Shift-Enter вместо ввода при выходе из режима редактирования. Если все сделано правильно, Excel поставит {} вокруг формулы.


Это вернет полный столбец в виде массива. Если вы хотите отобразить полный столбец. Выделите достаточно строк, чтобы отобразить данные с верхней строкой как активной. Поместите приведенную выше формулу в строку формул и нажмите Ctrl-Shift-Enter.

Excel заполнит выбранные ячейки формулой в массиве и поместит {} вокруг каждой формулы.

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

person Scott Craner    schedule 18.08.2017
comment
Это не будет работать, когда вы скопируете его. Должно быть =ИНДЕКС(A1:D1,0,ПОИСКПОЗ($Q$1,ABS($A$1:$D$1),0)) - person Marco Vos; 18.08.2017
comment
@MarcoVos да, будет. Его просто нужно правильно ввести, см. редактирование для объяснения того, как это сделать. - person Scott Craner; 18.08.2017
comment
@ScottCraner, спасибо за ответ. Но проблема, с которой мы сталкиваемся, заключается в том, что мы должны взять САМЫЕ БОЛЬШИЕ два значения в абсолютном выражении. АКА, если у меня есть строка со следующими номерами 1.65, 0.5, 0.3, -2.65, мне придется извлечь 1.65 и -2.65. На данный момент эти два значения извлекаются 1.65 и 2.65, и ваше решение работает, только если второе значение равно -2.65. Есть ли обходной путь? Может быть, нам следует изменить способ извлечения самых больших значений в строке? - person AK88; 21.08.2017