Excel: автозаполнение формул в списках

Question

У меня довольно большой лист Excel, где первые 20 столбцов содержат входные данные, еще 15 столбцов содержат формулы, которые используют входные данные для расчетов.

Я часто удаляю весь набор входных данных и заменяю его новым, объем входных данных варьируется и варьируется от 4 000 до 10 000 строк.

Есть ли поддержка в Excel для автоматической настройки части формулы в соответствии с количеством строк входных данных? Теперь я делаю это вручную, либо удаляя строки, если входные данные меньше предыдущего набора, либо копируя ячейки формулы, если входные данные стали больше. Это довольно утомительно, поэтому я ищу способ автоматизировать это.

Некоторые из формул выдают # N/A, если входные данные отсутствуют, и я уплотняю вычисленные данные с помощью сводных таблиц, поэтому мне нужны формулы, точно соответствующие строкам входных данных.

Я нахожусь на Excel 2003.

Answer 1

Чтобы добавить строки, вы можете просто выбрать последнюю ячейку в каждом столбце формулы и дважды щелкнуть в нижнем правом углу, как вы собирались перетащить, но вместо этого дважды щелкнуть. Это заполнит формулы вниз, чтобы соответствовать значениям слева.

До (дважды щелкните, где находится маленький черный ящик):

После:

Answer 2

Если вы часто этим занимаетесь, вы можете создать на листе элемент управления VBA, чтобы очистить все данные в первых 20 столбцах и оставить только формулы в первой строке для следующих 15 столбцов. Таким образом, вы можете просто нажать кнопку, затем вставить новые данные и заполнить строку 1 для столбца U до AJ двойным щелчком мыши.

Answer 3

Некоторые возможные решения ....

1) Можете ли вы использовать вычисляемые поля - это формулы сводной таблицы? Вычисляемые поля отлично работают, если ваша формула ссылается только на столбцы в каждой строке (сама по себе). Поскольку они являются частью Pivot, они не занимают много места и не замедляют работу вашей таблицы. Если ваши формулы вертикальные или сложные, скорее всего, это не сработает.

2) Используйте автоматическое форматирование таблицы (Вставка таблицы), а не просто данные. Формулы, введенные в режиме автоматической таблицы, будут удалены или добавлены при удалении или добавлении строк.

3) Используйте IF(ISERROR(формула, затем пустое или ноль, иначе формула) в ваших формулах, чтобы скрыть недействительные результаты. Это не будет хорошо работать, если ваши данные сводной таблицы сводятся - так как они могут подсчитывать эти пустые строки, но должны работать хорошо, если вы в основном полагаетесь на суммы значений.