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

Например, если у него есть формула = СУММ (B: B), а затем он вставляет столбец слева от столбца B, формула автоматически обновляется Excel до = СУММ (C: C). Он не хочет, чтобы формула обновлялась; он по-прежнему хочет, чтобы он ссылался на столбец B после того, как он вставит новый столбец.

Один из способов получить желаемый результат — использовать функцию OFFSET для ссылки на столбец B. Например, рассмотрим следующую формулу:

=SUM(OFFSET(A:A,0,1))

Если эта формула находится в ячейке, а вы вставляете столбец перед столбцом B, то формула не обновляется; он по-прежнему относится к столбцу B. Почему?

Поскольку формула относится к столбцу A, и вы ничего не сделали для перемещения столбца A. Если вы вставили столбец перед столбцом A, формула обновится до ссылки на столбец B.

Это означает, что лучший способ обработать формулу — это использовать функцию ДВССЫЛ следующим образом:

=SUM(INDIRECT("B:B"))

Функция ДВССЫЛ использует текст для параметра, и, поскольку это текст, он не считается ссылкой для обновления в Excel. Независимо от вставки или удаления столбцов формула всегда будет ссылаться на столбец B.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (10786) применим к Microsoft Excel 2007, 2010 и 2013. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Stopping_a_Formula_from_Updating_References [Остановка формулы из обновления ссылок].