Так что если
A1:A5 = {3, 3, 4, 4, 5}
какую функцию я мог бы вставить в B1, чтобы получить ссылку на A4 (ячейка, содержащая последнее вхождение 4)?
Так что если
A1:A5 = {3, 3, 4, 4, 5}
какую функцию я мог бы вставить в B1, чтобы получить ссылку на A4 (ячейка, содержащая последнее вхождение 4)?
Я предполагаю, что A1:A5 отсортированы в порядке возрастания. Если это так, вы можете использовать следующую формулу:
=ADDRESS(ROW(A1)-1+MATCH(4,A1:A5,1),COLUMN(A1))
Это даст выход $ A $ 4.
Вот как это работает:
MATCH(4,A1:A5,1)
находит индекс наибольшего значения <= 4, предполагая, что A1:A5 отсортировано в порядке возрастания. На самом деле это означает, что он находит первое значение больше 4 и просто возвращает индекс перед этим индексом.
ADDRESS(row,col)
столбец ) преобразует номер строки и номер столбца в ссылку на ячейку. Для номера столбца я просто использовал столбец списка: COLUMN(A1)
. Для номера строки я использовал индекс, возвращаемый функцией MATCH
в качестве смещения от начала списка (ROW(A1)-1
). Вы можете опустить ROW(A1)-1
и в этом случае все равно будет работать, но произойдет сбой, как только ваш список начнется где-то, кроме строки 1.
Обратите внимание, что для использования этого ссылочного значения в другом месте вам нужно использовать: INDIRECT(B1)
.
Вы можете использовать формулу массива, чтобы найти максимум строк
{=INDEX(A1:A5,MAX((A1:A5=4)*(ROW(A1:A5))),1)}
Войти с помощью control+shift+enter. Часть MAX вернет наибольший номер строки из всех ячеек, которые содержат «4». Это может быть все, что вам нужно, но я обернул это в функцию INDEX, которая указывает на ячейку на случай, если вам понадобится этот дополнительный шаг.
предполагая, что в вашем массиве не совпадают значения столбцов, я бы посоветовал вам просто изменить значения столбцов, чтобы значения в столбце 5 массива теперь находились в столбце 1, а затем использовать min(). Получить первое вхождение всегда легче, чем последнее.
Там есть объяснение здесь (поиск "F94"), но имейте в виду, это не красиво!