У Алана есть рабочая тетрадь с двумя листами: «Данные за сентябрь» и «Обзорный отчет». На втором листе у него есть ссылки в формулах на ячейки на первом листе. Когда наступает новый месяц, Алану нужно изменить имя рабочего листа «Данные за сентябрь» на «Данные за октябрь», что нарушает все формулы на другом листе. Он задается вопросом, как он может изменить формулы, которые ссылаются на первый рабочий лист, чтобы они не ломались, когда он меняет имя первого рабочего листа.
Прежде всего, следует сказать, что это поведение (как описанный) не является нормальным для Excel. Если вы находитесь на листе обзорного отчета и создаете формулу, которая ссылается на ячейку на листе данных за сентябрь, то любые изменения имени листа данных за сентябрь должны автоматически отражаться в формулах на любых других листах книги. Единственный раз, когда это не так, если у вас есть формула, которая использует функцию ДВССЫЛ для ссылки на что-то на листе, как показано здесь, например:
= ДВССЫЛ («‘Данные за сентябрь’! A3 «)
Причина, по которой он не меняется, заключается в том, что имя рабочего листа заключено в одинарные кавычки (апострофы), что означает, что он рассматривается как строковая константа, которую нельзя изменять. Если вам нужно использовать INDIRECT, поместите имя рабочего листа в другую ячейку и укажите ссылку на эту ячейку в формуле INDIRECT, как описано в других вопросах . Затем вы можете изменить содержимое указанной ячейки, чтобы отразить имя рабочего листа, который вы хотите использовать.
Если вы не хотите изменять формулы таким образом, вы можете использовать Найти и «Заменить», чтобы изменить ссылки после переименования рабочего листа «Данные за сентябрь». Выполните следующие действия:
- Отобразите второй рабочий лист (обзорный отчет).
- Нажмите Ctrl + H , чтобы отобразить вкладку “Заменить” диалогового окна “Найти и заменить”.
- Нажмите кнопку “Параметры”, если она видна. (См. Рис. 1.)
- В поле «Найти» введите «Данные за сентябрь» (без кавычек).
- В поле “Заменить на” введите “Данные за октябрь” (без кавычек).
- Убедитесь, что в раскрывающемся списке Искать в установлено значение Формулы.
- Щелкните “Заменить все”.
Рис. 1. Вкладка «Заменить» диалогового окна «Найти и заменить».
Другой вариант, который хорошо работает, – это просто определить именованные диапазоны в таблице данных за сентябрь. Каждой ячейке (или диапазону ячеек) должно быть присвоено имя, которое затем можно будет использовать в формулах на других листах. Любые изменения имени рабочего листа вообще не повлияют на использование именованных диапазонов.
Наконец, вы можете рассмотреть возможность просмотра макета данных. Например, вам может быть лучше не иметь лист под названием «Данные за сентябрь. «Вместо этого создайте рабочий лист с именем« Текущий месяц »и укажите его в своих формулах. Когда наступит начало нового месяца, скопируйте данные из рабочего листа« Текущий месяц »и заархивируйте его на новом листе с названием месяца. он представляет (например, “Данные за сентябрь”). Таким образом, вы всегда работаете с текущими данными и имеете данные за предыдущие месяцы, доступные для просмотра в любое время.
Источник: