Есть много способов сослаться на диапазон переменного размера. Есть также несколько способов найти последний непустой столбец.
Во-первых, как найти строку с последним непустым в столбце A:A
:
Я собираюсь обернуть эти формулы в оператор IFERROR
, который вернет 6
если произойдет ошибка. Это потому, что вы заявляете, что ваши данные начинаются со строки 6, поэтому, если что-то пойдет не так, это позволит вам вернуть только A6
вместо некоторого сообщения об ошибке. Если вы хотите показать ошибку, просто удалите обертку IFERROR
.
Если ваши данные все числа, используйте это: IFERROR(MATCH(10^308,A:A),6)
Если ваши данные полностью текстовые, используйте это: IFERROR(MATCH(REPT(CHAR(255),255),A:A),6)
Если ваши данные представляют собой числа и текст, найдите максимальное значение из двух: MAX(IFERROR(MATCH(10^308,A:A),6),IFERROR(MATCH(REPT(CHAR(255),255),A:A),6)
Вы также можете использовать формулу массива, но я стараюсь держаться подальше от них, потому что они обычно медленнее. Это будет так, как показано ниже, и будет введено с помощью Ctrl+Shift+Enter. (Вы поймете, что это правильно, если фигурные скобки {} появятся с обеих сторон формулы.)
MAX(ROW(A:A)*NOT(ISBLANK(A:A)))
Во-вторых, как ссылаться на диапазон A6:A{last non blank row}
:
Поскольку вы хотите одну и ту же ссылку три раза, было бы неплохо добавить вспомогательную ячейку. Есть несколько способов получить то, что мы хотим, но я собираюсь показать несколько. Допустим, вспомогательная ячейка - B1
а формула, которую вы хотите, - B2
. Я просто собираюсь использовать пример формулы "последней строки" для текста, потому что я считаю, что это то, что вы данные. Вы должны изменить это на то, что вам нужно.
Метод INDEX
: (вероятно, лучший вариант)
B1 = IFERROR(MATCH(REPT(CHAR(255),255),A:A),6)
B2 = INDEX(A6:INDEX(A:A,B1),MODE(MATCH(A6:INDEX(A:A,B1),A6:INDEX(A:A,B1),0)))
INDIRECT
метод:
B1 = "A6:A"&IFERROR(MATCH(REPT(CHAR(255),255),A:A),6)
B2 = INDEX(INDIRECT(B1),MODE(MATCH(INDIRECT(B1),INDIRECT(B1),0)))
Метод OFFSET
:
B1 = IFERROR(MATCH(REPT(CHAR(255),255),A:A),6)
B2 =INDEX(OFFSET(A6,0,0,B1-5),MODE(MATCH(OFFSET(A6,0,0,B1-5),OFFSET(A6,0,0,B1-5),0)))