Как добавить/удалить строку, не затрагивая формулу в столбце в Excel

Question

У меня есть столбец B с формулой, полной IF, где каждая ячейка ссылается на ячейку над ней. Время от времени мне нужно удалить некоторые строки. После удаления строки формула в столбце B выдает #REF для оставшихся ячеек под удаленной строкой. Как удалить строку, чтобы формула автоматически обновилась, ссылаясь на новую ячейку над ней? Кроме того, при добавлении строки может ли формула автоматически размещаться в ячейке B без того, чтобы мне приходилось добавлять ее каждый раз?

Answer 1

Зависит от вашей ситуации и планировки. В зависимости от этого есть 2 варианта.

Закомментировано Вы можете использовать абсолютные ссылки, что, я думаю, вам и нужно, например:

=IF($A$1)

$ означает, что он прикрепляется к этой ячейке, независимо от значения!

Однако, в зависимости от того, что именно вы удаляете, вы можете найти использование Indirect полезным.

Возвращает ссылку, указанную текстовой строкой. Ссылки немедленно оцениваются для отображения их содержимого. Используйте INDIRECT, когда вы хотите изменить ссылку на ячейку в формуле, не изменяя саму формулу.

=INDIRECT($A$2)     

Болееинформация, цитата и источник изображения

Answer 2

Я столкнулся с той же проблемой ранее. Вот как я ссылался на ячейку выше текущей (в данном случае A2):

=OFFSET(A2,-1,0)

Однако при добавлении строк формулу необходимо копировать и вставлять. Если только не создан скрипт, который делает это автоматически.

Более подробную информацию о формуле смещения можно получить:https://support.office.com/en-us/article/OFFSET-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66

Answer 3

У меня есть рабочая книга с рабочими листами, которые черпают данные с помощью формул из другого рабочего листа. Характер данных означает, что может быть множество строк с дублирующимися или ошибочными данными, которые необходимо удалить. Удаление строк на исходном рабочем листе нарушает импорт и вычисления. Я использую обходной путь редактирования, когда очищаю данные. Процесс прост. Я создаю рабочий лист редактирования данных. Копирую в него весь рабочий лист исходных данных. Редактирую данные, удаляя строки по мере необходимости, а затем вставляя данные обратно на исходный рабочий лист. Просто и очень эффективно!