Предположим, у вас есть книга с тремя листами: Sheet1, Sheet2 и Sheet3. В столбце A1 рабочего листа Sheet2 у вас есть формула = Sheet1! A1. Когда вы копируете эту формулу из Sheet2 в ячейку A1 на Sheet3, формула по-прежнему ссылается на Sheet1. Но как такое может быть? Почему Excel не корректирует ссылку на лист, как ссылки на ячейки?

Как и именованные диапазоны, Excel рассматривает имена листов как абсолютные. Каждый объект рабочего листа не зависит от всех других листов в книге.

Когда вы вставляете формулу, которая включает ссылку на лист, эта ссылка на лист остается неизменной в том, что вставлено.

Вы можете сделать несколько вещей. Один из них — просто изменить ссылку на формулу после ее вставки, чтобы она ссылалась на правильный лист. Если вам нужно изменить многие из них, вы можете выбрать все формулы на целевом листе (F5 | Special | Formulas), а затем использовать «Найти и заменить», чтобы заменить исходное имя рабочего листа (Sheet1)

с правильным именем рабочего листа (Sheet2).

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

Function PrevSheet(rCell As Range)

Application.Volatile     Dim i As Integer     i = rCell.Cells(1).Parent.Index     PrevSheet = Sheets(i - 1).Range(rCell.Address)

End Function

Макрос просматривает текущий лист, а затем определяет, какой лист находится перед ним. Затем делается ссылка на этот рабочий лист.

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

=PrevSheet(A1)

Это возвращает значение ячейки A1 из предыдущего листа. Если у вас есть Sheet1, Sheet2 и Sheet3, и вы используете эту формулу на Sheet3, то она возвращает значение Sheet2! A1. Если предыдущий лист является первым листом книги или это не рабочий лист, функция возвращает ошибку #Value.

Если позже вы скопируете эту формулу на другой лист (скажем, на Лист 5), то она вытянет значение относительно своего нового местоположения, что означает, что оно вытянет значение из Sheet4! A1.

Вы также можете указать имя листа, и функция будет работать нормально:

=PrevSheet(Sheet3!A5)

Эта версия всегда будет возвращать Sheet2! A5, поскольку sheet2 является предыдущим листом Sheet3.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

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

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

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