#excel #excel-formula
#excel #excel-формула
Вопрос:
Я хочу определить динамический диапазон, который будет расширять строки и столбцы моего диапазона всякий раз, когда вставляется новая строка или столбец. Моя текущая формула не хочет расширяться до ячейки $T$13 . Мои заголовки начинаются в строке $M$7 . Как я могу настроить свою формулу?
Используемая формула
=OFFSET(Sheet1!$M$8,0,0,COUNTA(Sheet1!$M:$M),COUNTA(Sheet1!$1:$1))
Мне нужно, чтобы мой диапазон расширялся до ячейки $T$13
Ответ №1:
Прямо сейчас ваша формула подсчитывает количество текстовых значений в столбце M.
Это ненадежный подход, поскольку столбец M содержит только пять текстовых значений, но столбцы S и T имеют гораздо больше значений.
Если вы не знаете, в каком столбце может быть наибольшее количество записей, вы можете ввести вспомогательную ячейку в каждом столбце, которая подсчитывает количество записей ниже. Я предлагаю вам вставить новую строку 2. Например, в столбце M введите формулу в M2
=counta($M$3:M$99999)
Скопируйте эту формулу в столбец T.
Затем вы можете оценить, какой из столбцов имеет наибольшее число
=max(M2:T2)
Это можно подключить к вашей исходной формуле следующим образом:
=OFFSET(Sheet1!$M$8,0,0,max(M2:T2),COUNTA(Sheet1!$1:$1))
Итак, теперь вместо того, чтобы просто смотреть, сколько строк в столбце M, формула использует максимальное количество строк в столбцах от M до S.
Теперь вы можете скрыть строку 2, если это нарушает дизайн вашего рабочего листа.
Редактировать: простое количество текстовых значений с помощью CountA будет игнорировать пустые ячейки и вернет неверные результаты. Вам действительно нужна формула, чтобы найти номер строки последней заполненной ячейки в каждом столбце.
Это действительно должен быть новый вопрос, но здесь идет
Если в столбце есть числовые значения, вы можете использовать
=MATCH(99^99,B5:B999,1)
Если в столбце есть текстовые значения, вы можете использовать
=MATCH("zzz",C5:C999,1)
Соответствующим образом отрегулируйте свои диапазоны.
Комментарии:
1. @teyln данные в моем примере расширены до
T13, но что, если я добавлю еще один столбец для дат, мне нужно будет расширить=max(M2:T2)? Есть какой-нибудь способ решить эту проблему, не зная последнего столбца или строки?2. Нет. Если вы используете правильную формулу в строке 2, она вернет нужное вам число в СМЕЩЕНИИ. Прочтите мой пост еще раз. Я внес правку, чтобы перехватить пустые строки.
Ответ №2:
В итоге я использовал решение, упомянутое @tevlyn.
В пределах досягаемости $M2:$T2
У меня есть формула =IFERROR(MATCH(99^99,M$8:M$999,1),0) фололвинга .
Я добавил IFERROR , потому что в моих данных не всегда есть растянутые данные $T2 .
Затем я определил свой диапазон в диспетчере имен, используя:
=OFFSET(Sheet3!$M$8,0,0,MAX(Sheet3!$M$2:$T$2),COUNTA(Sheet3!$1:$1))
Это все еще работает, даже если между диапазонами есть пробелы.