Когда Стивен создает формулу и ссылается на ячейку на другом листе в той же книге, ссылка всегда является относительной (A1).

Однако, когда он ссылается на ячейку в другой книге, ссылка всегда абсолютна ($ A $ 1). Стивен интересуется, есть ли способ сделать это автоматически относительным (A1) без необходимости заходить в ячейку и трижды нажимать F4.

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

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

  1. Выделите ячейки, в которых есть формулы.

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

  3. В поле «Найти» введите один знак доллара.

  4. Убедитесь, что поле «Заменить на» пусто.

  5. Щелкните «Заменить все».

Это оно; все знаки доллара в выбранных ячейках удаляются, оставляя относительные ссылки по желанию. Если у вас есть некоторые абсолютные ссылки в тех ячейках, которые вы хотите сохранить, вам следует использовать элементы управления в диалоговом окне «Найти и заменить», чтобы пройти по каждому экземпляру и выполнить замену по желанию.

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

  1. Переместите целевой лист в книгу, которая будет содержать формулы. (На самом деле переместите его, а не просто скопируйте.)

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

  3. Сохраните свою книгу.

  4. Переместите целевой рабочий лист (тот, который вы переместили на шаге 1) обратно в исходную книгу.

Что происходит с этими шагами, так это то, что когда вы перемещаете рабочий лист обратно в исходную книгу, Excel обновляет первую часть ссылок на формулы, чтобы включить информацию о внешней книге. Однако при этом ссылки на ячейки в этих формулах не меняются с относительных на абсолютные.

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

Этот совет (11225) применим к Microsoft Excel 97, 2000, 2002 и 2003.

Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-Relative_References_to_Cells_in_Other_Workbooks [Относительные ссылки на ячейки в других книгах].