Может ли кто-нибудь сказать мне формулу Excel, чтобы вернуть второе последнее текстовое значение в строке?
Мне удалось найти эту формулу для последнего текстового значения: =INDEX(U2:Y2,MATCH(REPT("z",255),U2:Y2)) , но не уверен, как получить второе последнее значение?
ФОРМУЛА
Вот альтернативный метод для возврата 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"
Я подозреваю, что ваша методология может быть не самой эффективной ... но используя то, что вы уже достигли, вы можете использовать существующий MATCH , вычесть один, чтобы дать вам новую границу для поиска, и вернуть последний текст из этой новой области. Метод OFFSET позволит вам переопределить область поиска с вашим новым лимитом, например так:
=INDEX(U2:Y2,MATCH(REPT("z",255),OFFSET(U2,0,0,1,MATCH(REPT("z",255),U2:Y2)-1)))
=INDEX(D7:P7,MATCH(REPT("z",255),D7:P7)-1)