Исправление распространённых ошибок копирования

В основе большинства ошибок при копировании лежит одна причина: ссылка сместилась, хотя должна была остаться на месте, или осталась на месте, хотя должна была сместиться.

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

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

Предположим, в E1 хранится ставка налога 0.08, а в D2 Вы вводите =C2*E1, после чего заполняете столбец вниз. Посмотрите, что происходит:

• D2 = =C2*E1 (правильно)
• D3 = =C3*E2 (E2 пуста!)
• D4 = =C4*E3 (тоже пуста)

Ссылка на E сместилась и больше не указывает на ставку. В строках ниже второй умножение выполняется на ноль, поэтому везде получается 0.

Зафиксируйте общую ячейку, чтобы она не смещалась. Перед заполнением измените формулу на =C2*$E$1.

Знаки доллара фиксируют и столбец E, и строку 1. Теперь при заполнении вниз:

• D3 = =C3*$E$1
• D4 = =C4*$E$1

В каждой строке выполняется умножение на правильную ставку. C по-прежнему смещается, а $E$1 остаётся на месте.

Если скопировать формулу туда, где её ссылки выйдут за край листа, появится #REF!.

Например, формула =A2-B2, размещённая в столбце A и перетащенная влево, больше не может указывать на допустимую ячейку, поэтому возвращает #REF!. Эта ошибка означает: «ссылка больше не существует».

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

Скопированная формула деления может обратиться к пустым ячейкам и выполнить деление на ноль, отобразив #DIV/0!.

Предположим, формула =B2/C2 заполнена вниз по столбцу, в котором некоторые ячейки C пусты. Деление на пустую ячейку вызывает ошибку.

Оберните формулу в IFERROR, чтобы вместо ошибки отображалась пустая ячейка или ноль: =IFERROR(B2/C2,0). Деление по-прежнему выполняется там, где это возможно, а ошибки скрываются там, где оно невозможно.

Классическая оплошность — перетащить маркер заполнения слишком далеко и перезаписать нужные ячейки или вставить формулу поверх введённых вручную значений.

Мгновенное исправление — нажать Ctrl+Z (Cmd+Z), чтобы отменить действие. В электронных таблицах хранится длинная история отмен, поэтому Вы можете отменить несколько случайных операций заполнения.

После отмены повторите заполнение внимательнее и остановите перетаскивание на правильной последней строке.

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

Чтобы скопировать только формулу, после перетаскивания нажмите кнопку Параметры автозаполнения и выберите Заполнить без форматирования. При вставке выберите Специальная вставка, а затем только пункт Формулы, чтобы сохранить внешний вид области назначения.

Если что-то выглядит неправильно, всегда начинайте одинаково: щёлкните ошибочную ячейку и прочитайте строку формул.

Проверьте, указывает ли каждая ссылка туда, куда Вы задумали. Если константа, которая должна быть $E$1, отображается как E5, это означает, что ссылка не была зафиксирована. Ссылка со значением #REF! указывает на удалённую целевую ячейку. Строка формул превращает загадочное неправильное число в очевидное исправление.

Чтобы проверить весь лист сразу, включите режим Показать формулы. В Excel нажмите Ctrl+` (клавиша с грависом), а в Google Таблицах выберите «Вид», затем «Показать формулы».

В каждой ячейке отображается формула, а не её результат, поэтому Вы можете просмотреть столбец и сразу заметить строку, в которой ссылки сместились. Нажмите сочетание клавиш ещё раз, чтобы вернуться к обычному режиму.

Лучшее исправление — вообще не допустить ошибку. Перед перетаскиванием проверьте каждую ссылку и решите: должна ли она перемещаться вместе со строкой или оставаться фиксированной?

Добавьте знаки доллара ко всему, что должно оставаться на месте: например, к одной ставке, общей сумме в заголовке или таблице поиска. Быстро добавить их можно, щёлкнув внутри ссылки и нажав F4: это сочетание циклически переключает варианты фиксации. Сначала зафиксируйте ссылки, затем выполняйте заполнение.

Предположим, что в столбце комиссий во всех строках, кроме первой, отображается 0. Вы щёлкаете строку 5 и видите в строке формул =C5*E4, хотя ставка находится в E1.

Диагноз очевиден: E1 не была зафиксирована, поэтому ссылка сместилась к E4. Исправьте исходную формулу на =C2*$E$1, затем заново заполните столбец. Теперь в каждой строке отображается правильное значение. Один знак доллара исправил весь столбец.

Теперь Вы умеете исправлять формулы, которые перестают работать при копировании:

• Смещающаяся константа исправляется фиксацией знаками доллара, например $E$1.
• #REF! означает, что ссылка вышла за пределы таблицы; #DIV/0! означает деление на пустую ячейку, которое можно обработать с помощью IFERROR.
• Ctrl+Z отменяет ошибочное заполнение, а вариант Заполнить без форматирования сохраняет стили в чистоте.
• Проводите диагностику с помощью строки формул или режима Показать формулы и фиксируйте ссылки перед заполнением.

Вы завершили урок «Правильное копирование формул».