Вопрос или проблема
У меня есть таблица, как ниже. Я хочу скопировать все формулы из книги B в A, но без ссылки на книгу B.
Когда я это делаю сейчас, у меня везде в формулах появляется ссылка на книгу B. Но я хочу просто формулы, связанные с моей текущей книгой (книга A).
Если я копирую простую сумму, это работает, но когда формула включает таблицу, ссылка указывает на книгу B.
Вот пример формулы:
=IF(Start!$D$8="Weekly",0.000001*SUMIFS(tbl_database[Value],tbl_database[Node],H$2,tbl_database[Type],$A14,tbl_database[Accounting],$C$1,tbl_database[Filter],"No"),0.000001*SUMIFS(tbl_database[Value],tbl_database[Node],H$2,tbl_database[Type],$A14,tbl_database[Accounting],$C$1,tbl_database[Source],"<>stack",tbl_database[Source],"<>overflow"))
Книга A Лист A:
Книга B Лист B:
Если я перехожу в строку формул и копирую фактическую формулу из книги B в A, я получаю ожидаемый результат, но, как вы можете видеть, со всеми этими формулами это может быть утомительно.
</div><div class="s-prose js-post-body" itemprop="text">Проверено в Excel 2010
Копирование любой части таблицы из A в B будет ссылаться обратно на A. Копирование всей таблицы (необходимо включить заголовки!) не ссылается обратно на A – если только формула не имеет ссылки на другую таблицу или другой лист. Формулы в таблице, которые ссылаются на диапазон за пределами таблицы, но на том же листе, правильно ссылаются на B.
Решение (хоть и не очень хорошее) состоит в том, чтобы убедиться, что в таблице нет формул, которые ссылаются на другую таблицу или лист, прежде чем копировать в B. Вы можете сделать это, переместив свои данные в A на тот же лист.
Как упоминалось в комментариях, использование Ctrl H для поиска всех ссылок на лист и их замены на пустую строку – это быстрый способ решить проблему после вставки.
Не упоминалось в комментариях о возможности наименованных диапазонов, указывающих обратно на A, вероятно, с поврежденными ссылками. Вы можете попытаться смягчить это, используя область рабочего листа (в отличие от книги), но, вероятно, вам все равно придется редактировать/удалять их в менеджере имен после вставки в B.
Мне интересен VBA, и я считаю, что лучшее решение – это написать код, который генерирует оригинальный отчет в A. Это уберет необходимость копирования из A в B, просто запустите программу для генерации нового отчета по мере необходимости.
</div><div class="s-prose js-post-body" itemprop="text">Не очень элегантно, но… почему бы не позволить ссылкам скопироваться, а затем использовать редактирование/замену, чтобы удалить их все?
</div>Ответ или решение
Чтобы решить проблему копирования формул из одной книги Excel в другую без ссылок на исходную книгу, нужно учитывать несколько ключевых аспектов. Это поможет избежать создания нежелательных ссылок на «Workbook B» в «Workbook A». Рассмотрим несколько эффективных подходов к данной задаче.
1. Копирование формул без ссылок
Прежде всего, стоит понимать, что Excel по умолчанию создает ссылки при копировании формул между рабочими книгами. Однако есть способы обойти эту поведенческую особенность:
1.1. Копирование текста формулы
Это наиболее простой и эффективный способ:
- Откройте Workbook B и перейдите к ячейкам с необходимыми формулами.
- Копируйте формулы из строки формул (Formula Bar). Это гарантирует, что вы получите только текст формулы без ссылок на Workbook B.
- Перейдите в Workbook A и вставьте скопированные формулы в нужные ячейки. Excel воспринимает это как текст, что позволяет избежать создания ссылок.
1.2. Использование функции "Заменить"
Если вам нужно перенести несколько формул, можно воспользоваться и другим подходом:
- Скопируйте формулы из Workbook B в Workbook A, как обычно.
- После вставки, используйте сочетание клавиш Ctrl + H для открыть инструмент "Найти и заменить".
- В поле "Найти" введите название листа из Workbook B (например,
Start!). - Оставьте поле "Заменить на" пустым и нажмите "Заменить все".
- В поле "Найти" введите название листа из Workbook B (например,
Этот метод заменит все ссылки на исходный лист, оставляя только сами формулы, привязанные к Workbook A.
2. Учет таблиц
Как вы уже заметили, формулы, которые ссылаются на таблицы, могут вести себя иначе. Чтобы исправить это, важно обратить внимание на следующее:
2.1. Повторное использование структур таблиц
Если возможно, создайте аналогичные таблицы в Workbook A. Это, возможно, потребует некоторой дополнительной работы, но избавит от проблем со ссылками после копирования.
2.2. Формулы, ссылающиеся на внешние диапазоны
Если есть необходимость ссылаться на исходные диапазоны в Workbook A, рекомендуется использовать именованные диапазоны. Это можно сделать следующим образом:
- В Workbook A, перейдите в "Управление именами" (Name Manager) и создайте именованные диапазоны, которые будут ссылаться на нужные данные.
- После этого скопируйте формулы из Workbook B и измените ссылки, чтобы они использовали вновь созданные именованные диапазоны.
3. Автоматизация с помощью VBA
Если описанные методы не подходят из-за объема задач или частоты их выполнения, можно автоматизировать процесс с помощью VBA:
Sub CopyFormulasWithoutLinks()
Dim wbA As Workbook
Dim wbB As Workbook
Dim wsB As Worksheet
Dim cell As Range
Set wbA = ThisWorkbook
Set wbB = Workbooks("BookB.xlsx") ' Замените на имя вашей книги
Set wsB = wbB.Sheets("SheetName") ' Замените на имя вашего листа
For Each cell In wsB.UsedRange
If cell.HasFormula Then
wbA.Sheets("TargetSheetName").Range(cell.Address).Formula = cell.Formula
End If
Next cell
End SubЭтот код проходит через все ячейки в рабочем листе Workbook B, проверяет наличие формул и копирует их в Workbook A без ссылок на Workbook B.
Заключение
Копирование формул без нежелательных ссылок — это задача, требующая внимания к деталям, но с использованием предложенных методов и подходов можно успешно справиться с предъявляемыми требованиями. Выбор подхода будет зависеть от специфики ваших данных и частоты выполнения задач. Например, для периодической почасовой работы с отчетами может подойти и VBA, что значительно сэкономит время и усилия.