Как автоматически обновить формулу при добавлении нового столбца

У меня есть набор данных, а на другом листе я провожу расчёты для минимальных и максимальных значений целых строк.

Данные начинаются с D4, и каждая строка представляет собой другой набор данных. Проблема в том, что когда я добавляю новый столбец для новых данных, Excel не обновляет формулы, чтобы автоматически расширить диапазон. Мне приходится вручную проходить через каждое сообщение об ошибке и “Обновить, чтобы включить ячейки”.

Пример формулы:

=MIN(Historian!$D4:$AR4)

Я предполагаю, что вы имеете в виду, что начинаете использовать столбец справа от ранее использованных столбцов, “AS” в случае вашей формулы, потому что вставка в ваш диапазон достигла бы желаемого результата.

Существуют несколько традиционных подходов:

1. Вы можете использовать то, что один интернет-сайт называет “подходом серой полосы”. Это, возможно, самая старая идея (из идей для систематического решения проблемы… смотрите #5 для, вероятно, самой старой идеи). У вас есть пустой столбец (в вашем случае, но можно сказать “строка”, если вверх и вниз) слева от ваших ячеек формул и один справа от них. Так что, если вы хотите суммировать, скажем, диапазон, как D4:AR4, вам следует использовать диапазон C4:AS4 и всегда ВСТАВЛЯТЬ столбец справа, а не просто начинать использовать столбец AS. Это заставит Excel автоматически корректировать вашу формулу. Единственный реальный недостаток обычно заключается в том, что это ощущается как что-то устаревшее и неуклюжее. Тем не менее, это работает как часы.

2. Используйте формулу, которая генерирует динамический диапазон. Это диапазон, который автоматически расширяется, когда вы начинаете использовать строку или столбец больше. Как? Ну, вот в чём загвоздка, да? Вам нужно найти какой-то хитрый способ сказать Excel, как это реализовать. На практике это обычно не требует супер-умного решения, но может привести к трудно заметным ошибкам. Я также могу упомянуть, что когда у вас мало опыта с функциями Excel, вы можете даже не представить, каким образом некоторые функции могут быть использованы для достижения этой цели. Но существует множество сайтов, которые дают вам много идей здесь, и это действительно очень легко, предлагая отличный набор уроков, которые помогут вам в Excel.

3. Используйте вышеупомянутую идею, но примените формулу к именованному диапазону. Как предлагает Раджеш С. Конечно, возникают некоторые трудности. Как только вы найдете хороший способ сделать это для каждой строки, вы можете начать создавать каждый именованный диапазон, который вам нужен… у вас 42,000 строк? Ну, начните создавать эти 42,000 именованных диапазонов, потому что они сами себя не создадут… Но обучение, а также многие сайты, которые это предлагают, на вашей стороне, и вы можете изменить вашу формулу динамического диапазона, основывая её на адресах ячеек, которые она использует, либо на строке (или столбце), в которой она находится, либо на относительном расположении ячейки по сравнению с исходными данными, или и том, и другом, а также на других идеях. У вас есть второй уровень “динамичности”, который вы применяете к формуле, а затем вам просто нужен один именованный диапазон для всех этих ячеек, если вы можете увидеть способ надежно связать его с местом, где находится формула для суммирования или другого. Это также доступно на множестве сайтов и довольно легко, как только вы получите правильный настрой.

4. Используйте VBA. VBA является золотым стандартом для многих вещей и представляет собой золотую жилу для легко характеризуемых, повторяющихся задач. Простые вещи не действительно сложны для изучения, потому что вы обычно можете сделать их с помощью записывающего макроса, а затем посмотреть на сгенерированный VBA и сказать “А-га!” и просто расширить его по мере необходимости. Хотя VBA обычно не преподавать БЕСПЛАТНО в интернете, вы все равно можете найти много простых вещей. VBA занимает время для “выполнения своей задачи”, но ему просто все равно, если вам нужно, чтобы он проверял 1,000 ячеек в строке, отбрасывал все пустые ячейки и находил минимум для ячеек с фактическими записями. И есть и другие простые способы игнорировать те неиспользуемые ячейки справа в каждой строке. В этом плане время выполнения не сопоставимо с тем, что было на старых компьютерах. Больше нет правил “не сортируйте базу данных до ухода в 17:00” (потому что это займёт 4-10 часов), потому что компьютеры СТЕРЕЖНЫЕ. (Кроме того, использование VBA может помочь вам преодолеть трудности, так сказать, и позволить вам быть более готовым узнать о нём больше, а не оставлять этот ужасный момент на “когда-нибудь”… просто мысль.)

5. Самый простой вариант, если это не приведет к другим проблемам: просто напишите формулу, чтобы включить, скажем, 50 дополнительных столбцов. Или 52: D4:CR4, и раз в год или когда угодно, вернитесь к этому и расширьте их, если вы добавляете много столбцов, или раз в десять лет, если вы не добавляете их так быстро.