Запретить Excel изменение формулы последней строки таблицы при добавлении новой строки

Question

У меня есть таблица со столбцами с именами "дата", "стоимость" и "ежедневно". "Дата" - это дата, "стоимость" - это число, а "ежедневно" - это формула, рассчитывающая общую стоимость каждого дня. Формула выглядит так:

= ЕСЛИ (A2 = A3 "", SUMIF (А $ 2: А2, А2, В $ 2: B $ 2)

Это проверяет, что дата текущей строки отличается от следующей строки, и, если так, возвращает сумму затрат строк с текущей датой.

Проблема возникает, когда я добавляю новые данные в таблицу. Предположим, что есть 10 строк. Тогда ячейка С10 будет иметь формулу:

= ЕСЛИ (А10 = А11 "", SUMIF (А $ 2: А10, А10, В $ 2: B $ 2)

Но если я добавлю новую строку (например, нажав клавишу табуляции, когда выделена ячейка C10), тогда формула C10 автоматически изменится на:

= IF(A10 = A12, "", SUMIF(A $ 2:A11, A10, B $ 2:B $ 2)

Кроме того, стиль ячейки сбрасывается, так что это больше не жирный текст, и рядом с ячейкой появляется предупреждение о том, что "эта ячейка не соответствует формуле столбца".

Как я могу предотвратить это поведение?

Answer 1

Вместо этого вы можете использовать offset(). (:

Таким образом, ваша формула становится:

=IF(A2=offset(A2,1,0),"",SUMIF(A$2:offset(A2,0,0),A2,B$2:B$2)

В моем тестовом наборе данных это работает, когда добавление строк не меняет формулу, удаляя зависимости. Пожалуйста, поделитесь, если это не так.

p/s: все (изменение ссылки на ячейку) произошло именно так, потому что определенная вами формула «зависит» от значений A11. А действие «вставить строки» «сохранит» исходную ячейку, указанную в формуле С10. Который был «перемещен» в A12 из-за вставки строки.