Как вставить динамический адрес ячейки в формулу. Например, у меня есть данные в прерывистых ячейках подряд. Мне нужно использовать формулу countblank для подсчета количества пустых ячеек после последней ячейки, где есть данные. Я использовал формулу "lookup(2,1/B:B <>" "), row(B:B))", чтобы найти последнюю ячейку, в которой есть данные. Например, эта формула возвращает ответ 18 в столбце "B". Я могу использовать формулу объединения, чтобы получить этот адрес, который является B18. Что мне делать, чтобы получить этот адрес (B18) в формуле счета?
1 ответ
2
Исходя из того, что я понимаю, вы можете использовать комбинацию CELL, OFFSET и INDIRECT и 2 вспомогательных ячеек для достижения того, что вы хотите.
В приведенном ниже примере образец данных находится в ячейках B:B. Последний ряд в B - ряд 14.
Формула в ячейке помощника Е4
="B"&LOOKUP(2,1/(B:B<>""),ROW(B:B))
Это возвращает B14, который является последней заполненной ссылкой на ячейку в Col B.
В Helper Cell E5 поместите следующую формулу, чтобы получить ссылку на последний ряд в столбце B
="B"&ROWS(B:B)
В моем Excel 2013 это возвращает B1048576
Теперь формула в G4
=COUNTBLANK(INDIRECT(CELL("address",OFFSET(INDIRECT(CELL("Address",INDIRECT(E4))),1,0))):INDIRECT(E5))
Часть формулы CELL("Address",INDIRECT(E4)) возвращает ссылку $ B $ 14, а OFFSET добавляет к ней 1 строку, делая ее $ B $ 15. COUNTBLANK использует эти НЕПРАВИЛЬНЫЕ ссылки на ячейки для получения ожидаемых ссылок на ячейки и возвращает ожидаемый результат.
На картинке ниже ячейки E6 и E8 только для справки. Они нигде не используются.
