Excel. Перемещение формул без изменения относительных ссылок

Рубрика: 7. Полезняшки Excel

На днях дочь обратилась с проблемой. Она построила сложную таблицу в Excel с большим числом формул, основанных на относительных ссылках, и возникла потребность скопировать эти формулы в новую область листа с сохранением ссылок на те же ячейки, что и исходные формулы (подробнее о типе ссылок см. Относительные, абсолютные и смешанные ссылки на ячейки в Excel). «Зайти» во все ячейки с формулами и изменить ссылки на абсолютные было затруднительно, так как таких ячеек было больше ста…

К сожалению, стандартные средства Excel не позволяют выполнить подобное копирование. Что вообще-то говоря, удивительно! Попробуйте, например, перенести формулу =В1+С1, хранящуюся в ячейке D1, в ячейку D4 (рис. 1). Если выполнить копирование с помощью специальной вставки и опции вставить формулы, в ячейке D4 обнаружите формулу =В4+С4.

Рис. 1. Специальная вставка; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf, примеры в формате Excel

Решение пришло из моего прошлого опыта: когда я был верстальщиком, я очень широко использовал контекстные замены, и был мастером в этом искусстве. 🙂

Выделите диапазон ячеек, который хотите скопировать. В нашем примере это С4:С13 (область 1 на рис. 2), и выберите команду Главная → Найти и выделить → Заменить (область 2 на рис. 2), или нажмите Ctrl + H (английская H).

Рис. 2. Найти и заменить

В открывшемся диалоговом окне «Найти и заменить» (рис. 3) в поле «Найти» введите знак = (с него начинаются все формулы). В поле «Заменить на» введите знаки && или любой иной символ который, как вы уверены, не используется ни в одной из формул. Нажмите «Заменить все».

Рис. 3. Заменить знак = на знаки &&

Во всех формулах на рабочем листе вместо знака равенства теперь стоит && (рис. 4).

Рис. 4. После замены

Скопируйте ячейки С4:С13 в требуемое место, и выполните обратную замену всех && на =. И первоначальные, и новые формулы ссылаются на одни и те же ячейки (рис. 5), причем формулы используют относительные ссылки, то есть их можно «протягивать».

Рис. 5. Формулы удалось перенести

Дополнение от 1 октября 2016

Еще один вариант решения проблемы можно найти у Джона Уокенбаха. [1] Переключите Excel в режим просмотра формул, пройдя по меню Формулы –> Зависимости формул –> Показывать формулы (рис. 6). Выделите диапазон для копирования. В данном примере – С4:С13. Скопируйте его в буфер. Откройте текстовый редактор, например, Word или Блокнот. Вставьте скопированные данные. Выделите весь текст, и снова скопируйте его в буфер. Вернитесь в Excel и активизируйте верхнюю левую ячейку диапазона, в который хотите вставить ваши формулы. Убедитесь, что лист, на который копируются данные, находится в режиме просмотра формул. Вставьте формулы. Выйдете из режима показа формул, повторно пройдя по меню пройдя по меню Формулы –> Зависимости формул –> Показывать формулы. Формулы в целевом диапазоне будут ссылаться на те же ячейки, что и в исходном.

6-%d1%80%d0%b5%d0%b6%d0%b8%d0%bc-%d0%bf%d0%be%d0%ba%d0%b0%d0%b7%d1%8b%d0%b2%d0%b0%d1%82%d1%8c-%d1%84%d0%be%d1%80%d0%bc%d1%83%d0%bb%d1%8b

Рис. 6. Режим Показывать формулы

Примечание. В некоторых случаях операция вставки в Excel выполняется с ошибкой и программа разбивает формулу на две и более ячейки. Если так происходит, то, возможно, недавно вы пользовались функцией Excel Текст по столбцам и приложение напоминает вам, как данные разбирались при последнем сеансе. Откройте Мастер распределения текста по столбцам и измените параметры. Выполните команду Данные –> Работа с данными –> Текст по столбцам. В диалоговом окне Мастера распределения текста по столбцам выберите С разделителями и нажмите Далее. Снимите флажки со всех вариантов разделителей, кроме варианта знак табуляции, и нажмите Отмена. После этих изменений формулы будут вставляться правильно.

[1] Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 144, 145.

Комментарии: 29 комментариев

Можно сделать сразу формулы, которые при копировании не меняются. Для этого использовать значет $ перед нужной переменной…

Станислав, если формул немного, то, действительно, проще воспользоваться стандартной возможностью Excel — войти в каждую ячейку и изменить в формулах относительные ссылки на абсолютные (или смешанные). Так же, если с самого начала работы вы планируете в дальнейшем копировать формулы, то сразу можете создавать их с правильными типами ссылок. Описанный прием нужен, когда большой массив формул уже создан, и вы понимаете, что его нужно скопировать в другое место… 🙂

Большое спасибо! Такая простая идея, тысячу раз ей пользовался при форматировании книг из тхт в doc, но здесь применить не догадался!
с $ не подходит, так как функция изменения формул при копировании нужна при создании самого листа, а вот потом, когда лист нужно переместить в другую книгу проставлять их нереально. проще копировать содержимое ячеек поочередно, но тоже не выход.

Большое спасибо! Очень пригодилось.

Спасибо, очень помогло! Блестящее решение!

Вот жесть!!Сам до такого точно бы не додумался, класс, но будьте внимательны!!! если есть формулы которые оперируют числами уже в самой таблице их нужно будет менять!

Просто гениально! 🙂

Очень здорово!
Удивительно просто и удивительно полезно, впрочем, как все гениальное!

ГЕНИАЛЬНО! Снимаю шляпу.

Nimogla tebya tvoya doch po ranshe sprosit) Bolshoe spasibo!!!

я вот стесняюсь спросить. а почему не сделать «Скопировать» — «Специальная вставка» — формулы ?
не ?

Попробуйте открыть Excel-файл, приложенный к заметке, и выполните то, что предлагаете. Увидите результат. Проанализируйте, как Excel вставляет формулы. К сожалению, относительные ссылки он обрабатывает не так, как нужно в нашем случае…

Просто респект и уважуха…

Спасибо вам огромное за совет! Причем такой простой!
Вы не представляете насколько вы мне облегчили задачу!
У меня больше 3-х тысяч формул с ссылками на ячейки. И в течении года на конец каждого квартала мне необходимо было их переносить.

Громаднейшее спасибо!
Сама бы не догадалась!

просто не работает

Спасибо! Очень помогло!

Глупости пишете. Воспользуйтесь встроенным способом: выделите нужный диапазон, далее правой кнопкой вызовите контекстное меню, выберете «Присвоить имя», в верхней строчке появившейся таблички введите любое имя, далее «ОК» и потом вставляя в любую ячейку пишите =имя_вашего_диапазона и протягивайте на сколько нужно.

Сашок, классная идея! Вот только, зачем писать в таком неуважительном тоне. 🙁 К тому же твоя идея годится только для переноса диапазона в горизонтальном направлении. При попытке сместить диапазон в вертикальном направлении выдает неверные результаты…

Спасибо! Гениально и просто!!!

Спасибо, за подсказку! Очень выручил этот момент. Просто, и незамысловато. А главное надёжно, никакие данные не потеряются…

Так ведь при выделении ячеек и перетаскивании в новое место (именно перетаскивании за сторону, а не протягивании за угол) ссылки сохраняются.

Спасибо Вам!

Добрый день, мне сначала тоже понравилась идея…
Но есть некоторые но.

Например, некоторые ячейки исходной таблицы (пусть А1, А2) ссылались на «внешние» ячейки (вне копируемой таблицы) А некоторые — ссылались внутри копируемой таблицы (например, сумма А1 и А2 в ячейке А3).

Копируем таблицу в столбик B.

Ячейки А1 и А2 продолжают ссылаться на те же внешние источники что и в родительской таблице, но сумма будет ссылаться не на сумму B1 и B2, а на родительскую таблицу (сумму А1 и А2). Теперь меняем B1 и B2, но сумма B3 не меняется.

Хотя в родительской таблице иная ситуация.

Идеальная схема — это когда внешние продолжают ссылаться на внешние, а внутренние ссылки внутри новой таблицы меняются…

Но наверное идеал недостижим))

Преклоняюсь, перед гибкостью ума , очень выручил меня этот метод! Просто, как и всё гениальное!

Большое Вам спасибо!:)

Как всегда Вы на высоте.

Просто и гениально!)

помогите, пожалуйста! существует 20 таблиц, из которых собираются сводные данные еще в 5 таблиц. Таблицы огромны!!!! Все работает, считает. Но если переносить таблицу, в другое место(в данном случае необходимо передать данные таблицы в другой комп по сети) но при перемещении, перестают работать формулы и не считают. что делать в таком случае, в ручную обработать заново все формулы, требуется очень много времени. Заранее спасибо!

Напишите на мой e-mail theurban.com@yandex.ru
Возможно я смогу вам помочь


Прокомментировать