Есть ли в Excel формула, которую я могу использовать для заполнения столбца B (в моем примере ниже), которая вернет номер последней строки, содержащей определенную строку, перед текущей строкой?
Данные не являются смежными.
Например, данные могут быть:
| A (value) B (prev recent instance)
----------------------------------------------
1 | Mike
2 | John
3 | Mike 1
4 | Tony
5 | Mike 3
6 | John 2
Затем я захочу расширить его, чтобы в столбце C отображался самый ранний случай.
Кажется, это очень похоже на этот вопрос: Какая формула Excel вернет мне номер последней строки ячейки в диапазоне, содержащем определенные данные?, однако этот вопрос и ответ требуют, чтобы данные были смежными.
Редактировать: Я бы хотел, чтобы ответ был представлен в виде формулы рабочего листа, однако, если вы считаете, что более целесообразно реализовать ее с помощью VBA, пожалуйста, включите это в свой ответ, указав плюсы и минусы каждого подхода.
решение1
Чтобы найти самый ранний случай, ниже приведена формула:
=LOOKUP(2,1/(A:A=A2),ROW(A:A))
Для лучшего понимания ниже представлен скриншот
решение2
Предполагая, что ваши данные начинаются в A1, попробуйте эту «формулу массива» в B2
=IF(COUNTIF(A$1:A1,A2),MATCH(2,1/(A$1:A1=A2)),"")
подтвердил нажатием CTRL+SHIFT+ENTER и скопировал вниз
или вы можете использовать эту версию без массива
=IF(COUNTIF(A$1:A1,A2),MATCH(2,INDEX(1/(A$1:A1=A2),0)),"")
В Excel 2007 или более поздней версии вы можете упростить его с помощью ЕСЛИОШИБКА, т.е. версия массива становится
=IFERROR(MATCH(2,1/(A$1:A1=A2)),"")
Для строки «1-й экземпляр» в столбце C попробуйте эту формулу в C1, скопированную вниз
=MATCH(A1,A$1:A1,0)
Примечание: формулы возвращают не строго «номера строк», а относительные номера строк для ваших данных, т. е. строка 1 ваших данных возвращает 1 и т. д.