У Алана есть рабочая тетрадь с двумя листами: «Данные за сентябрь» и «Обзорный отчет». На втором листе у него есть ссылки в формулах на ячейки на первом листе. Когда наступает новый месяц, Алану нужно изменить имя рабочего листа «Данные за сентябрь» на «Данные за октябрь», что нарушает все формулы на другом листе.

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

Прежде всего, следует сказать, что такое поведение (как описано) не является нормальным для Excel. Если вы находитесь в рабочем листе обзорного отчета и создаете формулу, которая ссылается на ячейку на листе «Данные за сентябрь», то любые изменения имени рабочего листа «Данные за сентябрь» должны автоматически отражаться в формулах на любых других листах книги. Единственный раз, когда это не так, если у вас есть формула, которая использует функцию ДВССЫЛ для ссылки на что-то на листе, как показано здесь:

=INDIRECT("'September Data'!A3")

Причина, по которой он не меняется, заключается в том, что имя рабочего листа заключено в одинарные кавычки (апострофы), что означает, что оно рассматривается как строковая константа, которую не следует изменять. Если вам нужно использовать КОСВЕННЫЙ, поместите имя рабочего листа в другую ячейку и укажите ссылку на эту ячейку в формуле КОСВЕННО, как описано в других вопросах ExcelTips. Затем вы можете изменить содержимое указанной ячейки, чтобы отразить имя рабочего листа, который вы хотите использовать.

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

  1. Отобразите второй рабочий лист (обзорный отчет).

  2. Нажмите Ctrl + H, чтобы открыть вкладку «Заменить» в диалоговом окне «Найти и заменить».

  3. Нажмите кнопку «Параметры», если она видна. (См. Рис. 1.)

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

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

  6. Убедитесь, что в раскрывающемся списке «Искать в» установлено значение «Формулы».

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

Другой вариант, который хорошо работает, — просто определить именованные диапазоны в таблице данных за сентябрь. Каждой ячейке (или диапазону ячеек) должно быть присвоено имя, которое затем можно будет использовать в формулах на других листах.

Любые изменения имени рабочего листа вообще не повлияют на использование именованных диапазонов.

Наконец, вы можете рассмотреть возможность просмотра своего макета данных. Например, вам может быть лучше не иметь лист под названием «Данные за сентябрь».

Вместо этого создайте рабочий лист с именем «Текущий месяц» и укажите его в формулах. Когда наступит начало нового месяца, скопируйте данные из рабочего листа «Текущий месяц» и заархивируйте их на новом рабочем листе с названием месяца, который он представляет (например, «Данные за сентябрь»).

Таким образом, вы всегда работаете с текущими данными, и данные за предыдущие месяцы доступны для просмотра в любое время.

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

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

link: / excelribbon-Unbreakable_Formula_References_to_Worksheets [Нерушимые ссылки формул на рабочие листы].