ФОРМУЛА
Вот альтернативный метод для возврата n- го текстового значения с конца.
=IFERROR(INDEX(U2:Y2,LARGE(ISTEXT(U2:Y2)*COLUMN(U2:Y2),2)-COLUMN(U2)+1),"")
Эта формула должна быть введена как формула массива, используя Ctrl+Shift+Enter вместо просто Enter. Вы узнаете, что все сделали правильно, если на обоих концах появятся фигурные скобки {}.
КАК ЭТО УСТРОЕНО
=IFERROR(...,"")
вернет пустое значение, если остальная часть формулы является ошибкой. Вы получите `# ЗНАЧЕНИЕ!# ошибка, если в диапазоне нет хотя бы n текстовых значений.
INDEX(U2:Y2,...)
принимает массив и возвращает некоторое значение в. INDEX(U2:Y2,4)
вернул бы четвертый элемент в массиве, который, в данном случае, является тем, что находится в X2
.
LARGE(..,2)
принимает некоторый массив и возвращает второе по величине значение из этого массива. Замените 2
на любое другое число, чтобы получить n- й по величине элемент. Функция SMALL
аналогична, но вместо этого возвращает n- е наименьшее значение.
ISTEXT(U2:Y2)*COLUMN(U2:Y2)
- это часть, в которой вы вводите ее как формулу массива. Функция ISTEXT
будет возвращать массив значений TRUE
/FALSE
для того, содержит ли каждая ячейка текстовое значение. Например, это может быть {TRUE,TRUE,FALSE,FALSE,TRUE}
. Функции COLUMN
возвращают номер столбца. В этом случае это будет {21,22,23,24,25}
. Эти два умножаются (TRUE=1
, FALSE=0
). Для моего примера конечный массив будет {21,22,0,0,25}
. Подстановка этого в формулу LARGE
из ранее вернула бы 22
потому что это второе по величине значение.
-COLUMN(U2)+1
просто настройте значение, включаемое в формулу INDEX
чтобы учесть тот факт, что мы начинаем со столбца 22, а в массиве U2:Y2
всего 5 столбцов. Мы хотим вернуть значение от 1
до 5
, а не от 21
до 25
.
ПРИМЕР
Допустим, у вас были следующие данные в диапазоне U2:Y2
:
Hello | World | meep | 5 | boop
Второе-последнее текстовое значение - meep
поэтому давайте посмотрим, как работает формула. Я собираюсь заполнить расчеты по одному шагу за раз. Подобный прогон можно увидеть, используя "Оценить формулу" на ленте "Формулы", хотя он может быть не в том же порядке.
=IFERROR(INDEX(U2:Y2,LARGE(ISTEXT(U2:Y2)*COLUMN(U2:Y2),2)-COLUMN(U2)+1),"")
=IFERROR(INDEX(U2:Y2,LARGE(ISTEXT(U2:Y2)*COLUMN(U2:Y2),2)-21+1),"")
=IFERROR(INDEX(U2:Y2,LARGE(ISTEXT(U2:Y2)*COLUMN(U2:Y2),2)-20),"")
=IFERROR(INDEX(U2:Y2,LARGE({TRUE,TRUE,TRUE,FALSE,TRUE}*COLUMN(U2:Y2),2)-21),"")
=IFERROR(INDEX(U2:Y2,LARGE({TRUE,TRUE,TRUE,FALSE,TRUE}*{21,22,23,24,25},2)-21),"")
=IFERROR(INDEX(U2:Y2,LARGE({21,22,23,0,25},2)-21),"")
=IFERROR(INDEX(U2:Y2,23-21),"")
=IFERROR(INDEX(U2:Y2,2),"")
=IFERROR(INDEX({"Hello","World","meep",5,"boop"},2),"")
=IFERROR("meep","")
="meep"