Я хочу использовать следующую формулу: INDEX(A6:A94,MODE(MATCH(A6:A94,A6:A94,0)))
За исключением того, что точное количество строк неоднозначно (импортируется с использованием VBA). Как можно сослаться на все непустые ячейки, начиная с A6, для использования в приведенной выше формуле?
Это должно быть что-то вроде этого: INDEX(A6:A{last non blank},MODE(MATCH(A6:A{last non blank},A6:A{last non blank},0)))
Спасибо!
Есть много способов сослаться на диапазон переменного размера. Есть также несколько способов найти последний непустой столбец.
Во-первых, как найти строку с последним непустым в столбце 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)))