Когда вы копируете формулу из одной ячейки в другую, Excel автоматически обновляет все относительные ссылки в формуле в зависимости от цели, которая получает формулу. Например, предположим, что ячейка B7 содержит следующую формулу:

=B6+A7

Если вы скопируете эту формулу в ячейку D22, Excel автоматически обновит ссылки, чтобы они относились к ячейке D22, как показано здесь:

=D21+C22

Когда вы копируете формулы из одного рабочего листа в другой, а формула содержит ссылку на предыдущий рабочий лист, Excel не выполняет этот тип обновления формул — по крайней мере, не в именах рабочих листов. Например, предположим, что у вас есть три рабочих листа с именами январь, февраль и март — в таком порядке. На листе за февраль у вас есть следующая формула:

=January!B7*1.075

Если вы скопируете эту ячейку на мартовский рабочий лист, Excel автоматически изменит ссылку B7 (при необходимости), но не изменит имя листа (январь, который был «на один меньше», чем лист, на котором формула впервые возникла) к скорректированному относительному имени листа (февраль, который «на единицу меньше», чем лист, на который копируется формула).

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

Решение состоит в том, чтобы выполнить простую операцию поиска и замены в Excel, как описано здесь:

  1. Скопируйте формулы из рабочего листа за февраль в нужное место на листе за март.

  2. Открыв рабочий лист March, нажмите Ctrl + A. Это выбирает все ячейки на целевом листе.

  3. Выберите «Заменить» в меню «Правка» или нажмите Ctrl + H. Excel отображает вкладку «Заменить» диалогового окна «Найти и заменить». (См. Рис. 1.)

  4. В поле «Найти» введите «Январь!» (без кавычек).

  5. В поле «Заменить на» введите «Февраль!» (без кавычек).

  6. Нажмите «Заменить все».

Формулы на рабочем листе теперь обновлены, поэтому они ссылаются на соответствующий рабочий лист.

Обратите внимание на шаги 4 и 5, что то, что вы ищете и заменяете, не является прямым названием месяца. Это сделано потому, что сами по себе названия месяцев (январь, февраль и т. Д.) Могут легко встретиться в других местах на листе, не являясь частью формулы. Вы не хотите изменять эти экземпляры, поэтому включены дополнительные символы, чтобы сузить поиск.

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

Этот совет (2946) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь:

link: / excelribbon-Relative_Worksheet_References_when_Copying [Относительные ссылки рабочего листа при копировании].