Когда вы работаете с листом, копируя и перемещая информацию из одного места в другое, вы можете задаться вопросом, есть ли способ скопировать или переместить выделенный фрагмент без изменения в Excel всех ссылок в выделенном фрагменте. Ответ, конечно, в том, что это зависит от обстоятельств. (Разве вам это не нравится в Excel?) Давайте посмотрим, как можно копировать и перемещать выделенные области в Excel.

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

Sub ConvertToAbsolute()

Dim c As Variant     Application.ScreenUpdating = False     For Each c In Selection         c.Value = Application.ConvertFormula(c.Formula, _           xlA1, , xlAbsolute)

Next c     Application.ScreenUpdating = True End Sub

После запуска этого макроса вы можете скопировать и вставить выделенный фрагмент, не выполняя никаких обновлений ссылок в Excel. После завершения вставки вы можете изменить ссылки в выделенном фрагменте (и в исходном диапазоне, если он все еще существует), выбрав диапазон и применив этот макрос:

Sub ConvertToRelative()

Dim c As Variant     Application.ScreenUpdating = False     For Each c In Selection         c.Value = Application.ConvertFormula(c.Formula, _           xlA1, , xlRelative, c)

Next c     Application.ScreenUpdating = True End Sub

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

Если вы перемещаете выделение, Excel не обновляет ссылки на ячейки при перемещении. Вы можете перемещаться либо, выбрав диапазон и используя клавиатуру (нажав Ctrl + X, чтобы вырезать, а затем Ctrl + V, чтобы вставить выделение), либо с помощью мыши (перетащив выделение в новое место).

В любом случае Excel оставляет ссылки в выделенном фрагменте одинаковыми — относительными или нет — без обновления.

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

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

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

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

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

Этот совет (11804) применим к Microsoft Excel 2007, 2010, 2013 и 2016.

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

link: / excel-Pasting_without_Updating_References [Вставка без обновления ссылок].