Формулы MS Excel, отображающие #REF, когда данные вводятся в ячейку определенным образом?

Question

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

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

Я не понимаю этого поведения и есть ли способ остановить его?

Спасибо

Answer 1

Пример:
Введите число 1 в ячейку A1
Введите =IF(B2<>"";A1+1;"") в A2, затем скопируйте его в десять ячеек ниже A2.
Введите a, b, c, d, ef в B2 и ниже

Теперь проверьте, как формулы в столбце A ссылаются на ячейки. (Выберите одну ячейку, нажмите F2)
Возьмите одну из ячеек в столбце B и переместите ее, достаточно одного шага.

Для LibreOffice 4.2.7.2 ссылка в формуле (в столбце A) следует за перемещением.
Для Excel я полагаю, что ссылка заменена на #REF - поскольку Excel обнаруживает, что это "перекрывает" формулу в ячейке выше.
Не удается проверить - нет доступа к Excel.

Что из этого является более правильным? Это может быть предметом для обсуждения, не рассматривается здесь.

Замените формулы в A2 на =IF(OFFSET(A2;0;1)<>"";A1+1;"") (и скопируйте вниз)
и вы сможете перемещать содержимое в столбце B, не оказывая отрицательного воздействия.
Это связано с тем, что на ячейки в столбце B ссылаются НЕ ПРЯМО, а через смещение от столбца A.

- Для перемещения по данным, на которые есть ссылки, вы можете сделать это с помощью следующих шагов:

1. Вырежьте данные
2. Вставьте его с помощью «Специальных вставок> Значения» в неиспользуемом месте (на него не ссылаются никакие формулы)
3. При необходимости освободите место для данных (выберите и удалите значения)
4. Повторите шаги 1 и 2, чтобы правильно разместить данные