Формула Excel меняет себя - ошибка или предполагаемое поведение?

Question

У меня странная ошибка, которая недавно поймала меня. Я хочу понять, ожидается ли поведение Excel и, если да, есть ли способ предотвратить или отключить его?

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

Если я начну со столбца B и перейду, вставив 1 вдоль строки, то формула для общего количества столбцов 1-3 изменится (в столбце N).

В строке 9 я указывал по 1 в каждой ячейке, и формула в N10 изменилась с SUM(B10:D10) на SUM(B10:M10) .

Однако формулы в O10 и P10 не изменились. Зачем?

В строке 5 и строке 1 я вставил числа в случайном порядке, и общие формулы остались прежними.

Answer 1

Поведение, которое вы видите, такое, как задумано. Это связано с опциями редактирования. Excel видит, что вы вводите данные в ячейки, которые соседствуют, но не входят в диапазон формулы, и расширяет диапазон в формуле в качестве помощи для вас.

Вы можете отключить эту функцию, сняв флажок Extend data formats and formulas в Excel options , Advanced options , Параметры Editing options .

Кроме того, если вы напишите формулу с заблокированными столбцами ( =SUM($B10:$D10) ), они останутся без изменений.