Вставка ячеек в Excel без обновления смежных формул

Question

Иногда я визуально сравниваю два набора данных, вставляя их в Excel рядом с формулой посередине, например =A1=C1 . Это вернет true или false в зависимости от того, точно ли соответствуют ячейки. Когда в наборах данных отсутствует запись, я могу заставить их снова выстроиться в линию, просто вставив новую ячейку и сдвинув существующие вниз. Однако, когда я делаю это, он обновляет формулы для смещения ссылочной ячейки на количество вставленных ячеек, например так:

Это вызывает проблемы, потому что мне приходится переделывать все формулы в середине каждый раз, когда это происходит. На длинном наборе данных это становится очень утомительным. Можно ли как-то "заблокировать" формулы в столбцах сравнения, чтобы мне было безразлично, вставлять или удалять ячейки, они всегда будут указывать на исходные ячейки, которые я установил?

Кстати, я знаю, что $ A $ 1 заблокирует ссылку, но это работает, только если вы перемещаете эту ячейку, а не перемещаете ссылочную ячейку. Если вы поместите это сверху и перетащите их вниз, все они останутся $ A $ 1, независимо от того, какую строку вы перетаскиваете (плюс, он все равно обновляется после вставки ячейки в столбец ссылок).

Answer 1

Если я понимаю ваш вопрос, я думаю, что это может сработать:

=OFFSET($B$1,ROW()-1,-1)=OFFSET($B$1,ROW()-1,1)

Где B1 - произвольный якорь для ваших формул, и вы всегда вычисляете смещение ячейки по обе стороны от вашего сравнения.

Answer 2

Вместо того, чтобы вставлять ячейки, вы можете скопировать ячейки, которые вы хотите сдвинуть вниз, и использовать paste-special-values, чтобы разместить их там, где вы хотите. Другими словами, перемещение значений вместо ячеек будет препятствовать корректировке формул.

Answer 3

Я поместил один набор данных в столбец A, а другой - в столбец B. Затем я вставил приведенную ниже формулу в каждую ячейку в столбце С. Функция ROW() возвращает текущий номер строки, поэтому вы можете вставлять / удалять данные в столбцах A или B без необходимости вставлять и повторно вставлять формулы. Вы можете вставить формулу в любой столбец, и она все равно будет работать - сравнивая столбцы A и B в строке, в которой она находится.

=IF(INDIRECT(ADDRESS(ROW(),1)) = INDIRECT(ADDRESS(ROW(),2)), "ok", "***diff***")

Я согласен - мне бы хотелось, чтобы в Excel был какой-то способ не изменять формулы при вставке и удалении данных в столбце.