Вернуть крайнее правое значение в строке с условиями

У меня есть список значений переменной длины (столбцы выходят, возможно, выходят на ValueN, этот лист со временем будет расширяться). Например:

Subject  Value1  Value2  Value3  Value4
Sub1     Pass    Fail    Fail
Sub2     Pass    NA
Sub3     Fail    Fail    Fail    Fail
Sub4     NA      NA      NA      NA
Sub5     NA      PASS    NA

Мне нужно создать новый столбец, который сообщает о крайнем правом прохождении или неудаче и сообщает NA только в том случае, если все столбцы являются NA (ни в одной строке не будут все столбцы пустыми), например:

Subject  Value1  Value2  Value3  Value4  New Column
Sub1     Pass    Fail    Fail            Fail
Sub2     Pass    NA                      Pass
Sub3     Fail    Fail    Fail    Fail    Fail
Sub4     NA      NA      NA      NA      NA
Sub5     NA      Pass    NA              Pass

Некоторое время назад я нашел еще одно сообщение, которое помогает мне получить самое правильное значение (формула Excel для получения последней записи в строке), но я не знаю, как изменить логику, чтобы возвращать NA, только если все значения в строке - NA.

Это формула, которую я использую для получения самого правого значения:

=LOOKUP(2,1/(B2:E2<>""),B2:E2)

Я пробовал несколько вариантов, но не могу заставить логику возвращать NA, только если все значения являются NA правильно.

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

РЕДАКТИРОВАТЬ: Я выполнил задачу с потерей вложенных операторов IF, но это не решает проблему необходимости изменять весь лист при добавлении дополнительных столбцов. Ищу надежное решение, если такое решение существует.


excel excel-formula excel-2010 worksheet-function
person JRodge01    schedule 08.01.2016    source источник
comment
Это NA тестовые значения или фактические #N/A ошибки?   -  person    schedule 08.01.2016
comment
Это реальные значения.   -  person JRodge01    schedule 08.01.2016

Ответы (2)


arrow_upward
3
arrow_downward

Используйте AGGREGATE¹ функцию чтобы выдавать ошибку в пустых ячейках и значениях NA при игнорировании ошибок. ЕСЛИОШИБКА может покрывают значения NA, если не может быть возвращено недопустимое значение.

In G2 as,

=IFERROR(INDEX(B2:E2, AGGREGATE(14, 6, COLUMN(A:D)/(SIGN(LEN(B2:E2))*(B2:E2<>"NA")), 1)), "NA")

При необходимости долейте.

sign_len

¹ Функция AGGREGATE был представлен в Excel 2010. Он недоступен в более ранних версиях.

person Community    schedule 08.01.2016
comment
Спасибо за умный ответ! Я не уверен, что у всех, кто просматривает документ, будет доступ к Excel 2010, но, поскольку это более приятный ответ, это становится их проблемой. :) - person JRodge01; 08.01.2016
comment
Однако у меня есть два вопроса по поводу вашего ответа, поэтому я понимаю, что здесь происходит. Почему вы используете COLUMN (A: D) в агрегатной функции и что делает оператор ‹›? - person JRodge01; 08.01.2016
comment
КОЛОНКА (A: D) - это как сказать 'от 1 до 4'. <> равно "не равно" - person ; 08.01.2016

arrow_upward
1
arrow_downward

Эта модификация должна работать:

=IF(COUNTIF(B2:E2,"NA")=COUNTA(B2:E2),"NA",LOOKUP(2,1/((B2:E2<>"")*(B2:E2<>"NA")),B2:E2))
person Doug Glancy    schedule 08.01.2016
comment
У вас такой надоедливый NA в G5. - person ; 08.01.2016
comment
@Jeeped, у меня есть NA для строки 5, но я не уверен в ее качестве. Я думал, что это было требованием: ... сообщает NA, только если все столбцы - NA. - person Doug Glancy; 08.01.2016
comment
Извините за неясность. Да, это было необходимое требование; ваша первая формула не охватывала это, и я ждал, чтобы увидеть, готовятся ли изменения (которые были). Лично я пытался протезировать более новый функция AGGREGATE в таких ситуациях. - person ; 08.01.2016
comment
@Jeeped, спасибо за деобфускацию! Ваш, конечно, тоже хорошо работает. Я все еще хочу начать использовать Aggregate. - person Doug Glancy; 08.01.2016