☰

Как вернуть предпоследнее текстовое значение из строки?

tag-icon • tag-icon microsoft-excel worksheet-function
Как вернуть предпоследнее текстовое значение из строки?

Может ли кто-нибудь подсказать мне формулу Excel, возвращающую предпоследнее текстовое значение в строке?

Мне удалось найти эту формулу для последнего текстового значения: =INDEX(U2:Y2,MATCH(REPT("z",255),U2:Y2)), но не уверен, как получить предпоследнее значение?

решение1

ФОРМУЛА

Вот альтернативный метод возвратан-ое текстовое значение с конца.

=IFERROR(INDEX(U2:Y2,LARGE(ISTEXT(U2:Y2)*COLUMN(U2:Y2),2)-COLUMN(U2)+1),"")

Эту формулу необходимо ввести как формулу массива, используя Ctrl+ Shift+ Enterвместо просто Enter. Вы поймете, что сделали все правильно, если на обоих концах появятся фигурные скобки { }.

КАК ЭТО РАБОТАЕТ

=IFERROR(...,"")вернет пустое значение, если остальная часть формулы является ошибкой. Вы получите ошибку `#ЗНАЧЕНИЕ!#, если нет хотя бынтекстовые значения в диапазоне.

INDEX(U2:Y2,...)принимает массив и возвращает некоторое значение внутри in. INDEX(U2:Y2,4)вернет 4-й элемент массива, который в данном случае является тем, что находится в X2.

LARGE(..,2)принимает некоторый массив и возвращает второе по величине значение из этого массива. Измените 2на любое другое число, чтобы получитьнth наибольший элемент. SMALLФункция похожа, но возвращаетнйнаименьшийвместо этого ценность.

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"

решение2

Я подозреваю, что ваша методология может быть не самой эффективной... но используя то, чего вы уже достигли, вы могли бы использовать свои существующие MATCH,вычесть одинчтобы дать вам новую границу для поиска и вернуть последний текст из этой новой области. Метод OFFSETпозволит вам переопределить область поиска с вашим новым пределом, например так:

=INDEX(U2:Y2,MATCH(REPT("z",255),OFFSET(U2,0,0,1,MATCH(REPT("z",255),U2:Y2)-1)))

решение3

=INDEX(D7:P7,MATCH(REPT("z",255),D7:P7)-1)

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