Excel при копировании вставляет ссылку
В Microsoft Excel вы можете связать ячейку книги с другой книгой, используя формулу, которая ссылается на внешнюю книгу. При создании этой ссылки может использоваться относительный путь. С помощью относительных ссылок можно перемещать книги, не нарушая связь. В этой статье рассказывается о том, как с помощью Excel хранить ссылки на связанные книги в разных обстоятельствах.
Дополнительная информация
Как обрабатываются пути ссылок при открытии файла
При открытии в Excel файла, содержащего ссылки (связанная книга), она объединяет части ссылок, хранящиеся в файле, с необходимыми фрагментами текущего пути связанной книги.
Как сохраняются пути ссылок
Если путь к связанному файлу сохраняется в Excel, для определения того, что нужно сохранить, используются следующие правила:Примечание. Перемещение вверх по пути указывает на то, что вы ссылаетесь на папки, расположенные вне корневого диска или общего доступа. Если переместиться вниз по пути, это означает, что вы переходите ближе к корневому диску или к общему доступу.
Если связанный файл и исходный файл данных находятся на разных дисках, буква диска сохраняется с путем к файлу и имени файла.
Если связанный файл и исходный файл данных находятся в одной папке, сохраняется только имя файла.
Если файл исходных данных находится в папке, вложенной в ту же корневую папку, что и связанный файл, свойство хранится для указания корневой папки. Все части пути, к которым предоставлен общий доступ, не сохраняются. Например, если связанный файл C:\Mydir\Linked.xls зависит от C:\Mydir\Files\Source.xls, то единственной сохраненной частью пути является \Files\Source.xls.
Если исходный файл данных находится в папке, расположенной ниже связанного файла, свойство сохраняется, чтобы указать это. Например, связанный файл — C:\Mydir\Files\Myfile\Linked.xls, а исходный файл данных — C:\Mydir\Files\Source.xls. Excel хранит только \MyDir\Files\.. \Source.xls.Примечание. Это позволяет поддерживать ссылки при копировании связанного файла в дополнительную вложенную папку в папке, в которой находится исходный файл. Например, связанный файл — C:\Mydir\Files\Myfiles1\Linked.xls, а файл исходных данных — C:\Mydir\Files\Source.xls, связанный файл с именем Link. xls копируется из папки C:\Mydir\Files\Myfiles1 в папку C:\Mydir\Files\Myfiles2, а ссылка на C:\Mydir\Files\Source.xls сохраняется.
Если исходный файл данных находится в папке XLStart, в разделе " Каталог автозагрузки" или " Библиотека ", свойству записывается свойство, которое указывает на одну из этих папок, и сохраняется только имя файла.Примечание. В Excel распознаются две стандартные папки XLStart, из которых автоматически открываются файлы при запуске. Ниже указаны две папки.
Папка XLStart в папке установки Office, например C:\Program Files\Microsoft Office \Office\XLStart
Папка XLStart в профиле пользователя (например, C:\Documents and Settings \имя_пользователя\Application Data\Microsoft\Excel\XLStart
Папка XLStart в профиле пользователя — это Папка XLStart, которая будет храниться в качестве свойства ссылки. Если вы используете папку XLStart, которая находится в папке установки Office, эта папка XLStart будет обрабатываться так же, как и любая другая папка на жестком диске. Имя папки Office меняется между версиями Office. Например, имя папки Office — Office, Office10, Office11 или Office12 в зависимости от используемой версии Office. Изменение имени папки приводит к разрыву связей при переходе на компьютер, на котором установлена другая версия Excel, чем версия, в которой была установлена ссылка.
Важно отметить, что содержимое строки формул не обязательно должно быть сохранено. Например, если файл исходных данных закрыт, вы увидите полный путь к файлу, но может быть сохранено только имя файла.
Относительные и абсолютные ссылки
Ссылки на внешние книги создаются в определенном порядке, если это возможно. Это означает, что полный путь к исходному файлу данных не записывается, а только часть пути, связанная с связанной книгой. С помощью этого метода вы можете перемещать книги, не нарушая связи между ними. Однако ссылки остаются неизменными, только если книги остаются в одном месте относительно друг друга. Например, если связанный файл — C:\Mydir\Linked.xls, а файл источника данных — C:\Mydir\Files\Source.xls, вы можете переместить файлы на диск D, пока исходный файл по-прежнему находится во вложенной папке "файлы". Относительные ссылки могут приводить к возникновению проблем при перемещении связанного файла на другой компьютер, когда источник находится в одном месте.
Сопоставленные диски и UNC-имя
При связывании книги с исходными данными связь устанавливается на основе способа открытия книги. Если книга была открыта на подключенном диске, она будет создана с помощью подключенного диска. Ссылка останется в том случае, если книга с исходными данными будет открыта в будущем. Если файл исходного файла данных открыт с помощью пути в формате UNC, ссылка не будет возвращена на подключенный диск, даже если соответствующий диск доступен. Если в одном и том же файле есть ссылки UNC и сопоставленные диски, а исходные файлы открыты одновременно с конечным файлом, только те ссылки, которые соответствуют тому, как был открыт этот файл, будут реагировать на гиперссылку. В частности, если вы откроете файл на подключенном диске и измените значения в исходном файле, только ссылки, созданные для подключенного диска, будут немедленно обновлены. Ссылка, отображаемая в Excel, может выглядеть по-разному в зависимости от того, как была открыта книга. Возможно, эта ссылка соответствует корневому общему ресурсу UNC или букве корневого диска, которая использовалась для открытия файла.
Сценарии, которые могут привести к неправильной работе ссылок
Существует несколько причин, по которым связь между файлами может быть непреднамеренно облагаться, чтобы они указывали на ошибочные места. Ниже приведены два наиболее распространенных сценария. Сценарий 1:
Вы можете сопоставить диск с корневым каталогом общего доступа. Например, вы можете подключить диск Z к \ \сервер\поделиться\Folder1.
Вы создаете ссылки на книгу, которая хранится в сопоставленном расположении, после того как вы откроете файл через этот подключенный диск.
Вы открываете файл по UNC-пути.
Вследствие этого ссылка будет разорвана.
Если вы закрыли файл, не сохраняя его, ссылки не будут изменены. Тем не менее, если сохранить файл перед его закрытием, вы сохранит ссылки с текущим разорванным расположением. Папки между корнем общего доступа и сопоставленной папкой будут оставлены вне пути. В приведенном выше примере ссылка изменится на \ \сервер\Folder1. Другими словами, имя общего доступа исключается из пути. Сценарий 2.
Вы можете сопоставить диск с корневым каталогом общего доступа. Например, вы можете подключить диск Z к \ \сервер\поделиться\Folder1.
Вы открываете файл по UNC-пути или сопоставленному диску, подключенному к другой папке в общем доступе, например \ \сервер\поделиться\Folder2.
Как следствие, ссылка будет разорвана.
Если вы закрыли файл, не сохраняя его, ссылки не будут изменены. Тем не менее, если сохранить файл перед его закрытием, вы сохранит ссылки с текущим разорванным расположением. Папки между корнем общего доступа и сопоставленной папкой будут оставлены вне пути. В приведенном выше примере ссылка изменится на \ \сервер\folder1.
Копирую столбец из одной книги (Книга1) в другую(Книга2). При этом формулы в столбце в Книге2 имеют вид, например,
[Книга2]А1+В2
Как убрать ссылку [книга2], чтобы формула выглядела как и в источнике, т.е. А1+В2
Копирую столбец из одной книги (Книга1) в другую(Книга2). При этом формулы в столбце в Книге2 имеют вид, например,
[Книга2]А1+В2
Как убрать ссылку [книга2], чтобы формула выглядела как и в источнике, т.е. А1+В2 kissi80
light26,
вид в источнике (Книга1), например, в ячейке Y20, на листе с названием Итоги:
=коммунальщик!Z20
где коммунальщик - название листа в этой книге.
вид в другой книге, куда копируется столбец(!)
='[Книга1.xlsx]коммунальщик'!Z20
Так вот надо , чтобы названия книги, т.е. что бы квадратных скобок и внутренностей их НЕ БЫЛО!
light26,
вид в источнике (Книга1), например, в ячейке Y20, на листе с названием Итоги:
=коммунальщик!Z20
где коммунальщик - название листа в этой книге.
вид в другой книге, куда копируется столбец(!)
='[Книга1.xlsx]коммунальщик'!Z20
Так вот надо , чтобы названия книги, т.е. что бы квадратных скобок и внутренностей их НЕ БЫЛО! kissi80
Так вот надо , чтобы названия книги, т.е. что бы квадратных скобок и внутренностей их НЕ БЫЛО! Автор - kissi80
Дата добавления - 18.11.2011 в 11:29
Как вариант, правда с неким дополнительным действом:
после вставки жмем Ctrl+H, в "Найти" - "[Книга1]", в "Заменить на" - ничего, в "Область поиска" - "формулы". И жмем "Заменить все".
Как вариант, правда с неким дополнительным действом:
после вставки жмем Ctrl+H, в "Найти" - "[Книга1]", в "Заменить на" - ничего, в "Область поиска" - "формулы". И жмем "Заменить все". _Boroda_
_Boroda_,
это да, это я уже нарыл, спасибо.
однако хотелось бы что-нить попроще в смысле макрос какой-нибудь, т.е. кнопку нажал и все сделалось, без дополнительных действов, поскольку это не я буду делать, а пользователи, со всеми вытекающими. (((
_Boroda_,
это да, это я уже нарыл, спасибо.
однако хотелось бы что-нить попроще в смысле макрос какой-нибудь, т.е. кнопку нажал и все сделалось, без дополнительных действов, поскольку это не я буду делать, а пользователи, со всеми вытекающими. ((( kissi80
[/vba]
Такой макрос. Можно положить в книгу макросов (Персонал), можно использовать только в этой книге. Можно повесить на кнопку. Второй файл должен быть открыт. Судя по первому посту, так оно и есть.
[/vba]
Такой макрос. Можно положить в книгу макросов (Персонал), можно использовать только в этой книге. Можно повесить на кнопку. Второй файл должен быть открыт. Судя по первому посту, так оно и есть. _Boroda_
[/vba]
Такой макрос. Можно положить в книгу макросов (Персонал), можно использовать только в этой книге. Можно повесить на кнопку. Второй файл должен быть открыт. Судя по первому посту, так оно и есть. Автор - _Boroda_
Дата добавления - 18.11.2011 в 16:07
Если подобных переносов много, замены становятся очень трудоемкими.
Тогда лучше не заниматься заменами, а в конце открыть окно связей (в XL2003 Правка >> Связи) и пересоединить связи на новую книгу
Для "свежих" переносов срабатывает на 100%, но при одном условии - новая книга должна быть сохранена до пересоединения
Если подобных переносов много, замены становятся очень трудоемкими.
Тогда лучше не заниматься заменами, а в конце открыть окно связей (в XL2003 Правка >> Связи) и пересоединить связи на новую книгу
Для "свежих" переносов срабатывает на 100%, но при одном условии - новая книга должна быть сохранена до пересоединения v__step
С уважением, Владимир
v__step, в том-то все и дело, что не 100 %.
Поэтому и надо было механизм, который одинаково работает.
Со временем проблем пока нет, всегда можно выкроить, да и сейчас доделывается система, которая будет на SQL, т.е. тут от Excel отойдем
v__step, в том-то все и дело, что не 100 %.
Поэтому и надо было механизм, который одинаково работает.
Со временем проблем пока нет, всегда можно выкроить, да и сейчас доделывается система, которая будет на SQL, т.е. тут от Excel отойдем kissi80
Был бы очень благодарен, если бы Вы привели живой пример такого сбоя
Для меня это важно, т.к. я пользуюсь пересоединением связей не только вручную (очень часто), но и программно в Чистилке (выложил эту утилиту в разделе "Готовые решения" в сыром виде), и пока что не замечал сбоев
Уже засомневался - только что проверил для разрушенной связи - сработало
Был бы очень благодарен, если бы Вы привели живой пример такого сбоя
Для меня это важно, т.к. я пользуюсь пересоединением связей не только вручную (очень часто), но и программно в Чистилке (выложил эту утилиту в разделе "Готовые решения" в сыром виде), и пока что не замечал сбоев
Уже засомневался - только что проверил для разрушенной связи - сработало
С уважением, Владимир
Был бы очень благодарен, если бы Вы привели живой пример такого сбоя
Для меня это важно, т.к. я пользуюсь пересоединением связей не только вручную (очень часто), но и программно в Чистилке (выложил эту утилиту в разделе "Готовые решения" в сыром виде), и пока что не замечал сбоев
Уже засомневался - только что проверил для разрушенной связи - сработало
v__step, Не, не приведу. )))
Я написал, что не 100%, поскольку при нескольких попытках (в разное время) слетали другие связи. Дело в том, что эти конкретные книги сейчас очень запутаны и тяжеловесны (напримр, 150 листов с перекрестными ссылками. ). Поэтому мы сейчас все переводим на SQL, но пока не перешли, дорабатывать-то надо.
Наверное все дело действительно в некорректных связях, которые накопились и мешают, но разбираться нет ни времени ни желания.
v__step, Не, не приведу. )))
Я написал, что не 100%, поскольку при нескольких попытках (в разное время) слетали другие связи. Дело в том, что эти конкретные книги сейчас очень запутаны и тяжеловесны (напримр, 150 листов с перекрестными ссылками. ). Поэтому мы сейчас все переводим на SQL, но пока не перешли, дорабатывать-то надо.
Наверное все дело действительно в некорректных связях, которые накопились и мешают, но разбираться нет ни времени ни желания. kissi80
С уважением, Владимир
Предположим, что у нас есть вот такая несложная таблица, в которой подсчитываются суммы по каждому месяцу в двух городах, а затем итог переводится в евро по курсу из желтой ячейки J2.
Проблема в том, что если скопировать диапазон D2:D8 с формулами куда-нибудь в другое место на лист, то Microsoft Excel автоматически скорректирует ссылки в этих формулах, сдвинув их на новое место и перестав считать:
Задача: скопировать диапазон с формулами так, чтобы формулы не изменились и остались теми же самыми, сохранив результаты расчета.
Способ 1. Абсолютные ссылки
Как можно заметить по предыдущей картинке, Excel сдвигает только относительные ссылки. Абсолютная (со знаками $) ссылка на желтую ячейку $J$2 не сместилась. Поэтому для точного копирования формул можно временно перевести все ссылки во всех формулах в абсолютные. Нужно будет выделить каждую формулу в строке формул и нажать клавишу F4:
Способ 2. Временная деактивация формул
- Выделяем диапазон с формулами (в нашем примере D2:D8)
- Жмем Ctrl+H на клавиатуре или на вкладке Главная - Найти и выделить - Заменить (Home - Find&Select - Replace)
Способ 3. Копирование через Блокнот
Этот способ существенно быстрее и проще.
Нажмите сочетание клавиш Ctrl+Ё или кнопку Показать формулы на вкладке Формулы (Formulas - Show formulas) , чтобы включить режим проверки формул - в ячейках вместо результатов начнут отображаться формулы, по которым они посчитаны:
Скопируйте наш диапазон D2:D8 и вставьте его в стандартный Блокнот:
Теперь выделите все вставленное (Ctrl+A), скопируйте в буфер еще раз (Ctrl+C) и вставьте на лист в нужное вам место:
Осталось только отжать кнопку Показать формулы (Show Formulas) , чтобы вернуть Excel в обычный режим.
Примечание: этот способ иногда дает сбой на сложных таблицах с объединенными ячейками, но в подавляющем большинстве случаев - работает отлично.
Способ 4. Макрос
Если подобное копирование формул без сдвига ссылок вам приходится делать часто, то имеет смысл использовать для этого макрос. Нажмите сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , вставьте новый модуль через меню Insert - Module и скопируйте туда текст вот такого макроса:
Для запуска макроса можно воспользоваться кнопкой Макросы на вкладке Разработчик (Developer - Macros) или сочетанием клавиш Alt+F8. После запуска макрос попросит вас выделить диапазон с исходными формулами и диапазон вставки и произведет точное копирование формул автоматически:
По умолчанию при копировании (или вырезании) и копировании в Excel в ячейки-источнике или диапазоне в нее вются все данные, форматирование, формулы, проверка и прикомменты. Это происходит при нажатии CTRL+V. Так как это может быть не то, что вам нужно, в зависимости от того, что вы копируете, у вас есть множество других параметров в области "Ветвь".
Например, вам может потребоваться вировать содержимое ячейки, но не ее форматирование. Или, возможно, вам нужно транспонировали данные из строк в столбцы. Или вам может потребоваться в качестве висячих результатов вместо самой формулы.
Совет: Скопировать формулы в смежные ячейки листа также можно с помощью маркера заполнения.
Параметры меню в области "Ветвь" (на ленте)
Название параметра
Что в нее в pasted (что в нее в последний
Вставка всего содержимого скопированных ячеек.
Сохранить ширину столбцов оригинала
Вставка содержимого скопированных ячеек с сохранением ширины столбца.
Транспонировать
Вставка содержимого скопированных ячеек с изменением ориентации. Данные строк будут вставлены в столбцы, и наоборот.
Формулы без форматирования и при комментариев.
Результаты формулы без форматирования и при комментариев.
Форматирование
Только форматирование скопированные ячейки.
Значения и исходное форматирование
Вставка значений и формата скопированных ячеек.
Вставить связь
Вставка ссылки на исходные ячейки вместо содержимого скопированных ячеек.
Вставка скопированного изображения.
Связанный рисунок
Вставка скопированного изображения со ссылкой на исходные ячейки (изменения, внесенные в исходных ячейках, отражаются и в ячейках, куда вставлено изображение).
Команда ''Специальная вставка''
Чтобы использовать параметры в окне Специальная ветвь, на ленте главная выберитезначок буфера обмена (в области Вpaste)и выберите специальная ветвь.
Сочетание клавиш: CTRL+ALT+V.
В диалоговом окне Специальная вставка выберите атрибут, который нужно вставить.
Примечание: В зависимости от типа скопированных данных и параметра, выбранного в разделе Вставить, некоторые параметры могут быть неактивны.
Параметр вставки
В этом формате вся ячейка в формате скопирована.
В этом шаблоне в этом шаблоне в этом шаблоне вся формула содержит только формулы скопированные данные, которые введены в формулу.
В этом способе в ячейки в этом оке можно вкопировать только значения скопированные данные.
В этом формате можно вкопировать только скопированные данные.
Примечания и заметки
В нее вложены только примечания и заметки, вложенные в скопированную ячейку.
В этой области в этой области вся информация о правилах проверки данных для скопирований ячеек.
с исходной темой
В этом формате вся ячейка в формате темы документа, примененном к скопированные данные.
В этом формате в скопированную ячейку в нее вложено все содержимое и форматирование, кроме границ.
ширины столбцов
В этом примере в этом примере в этом столбце или диапазоне столбцов в этом столбце или диапазоне столбцов.
формулы и форматы чисел
В этом формате в скопированные ячейки в этом формате в этом формате будут доступны только формулы и все параметры форматирования.
значения и форматы чисел
В этом формате в скопированные ячейки в этом формате можно вкопировать только значения и все параметры форматирования.
Все объединение условных форматов
В этом формате в скопированные ячейки в формате содержимого и условного форматирования.
Вы также можете указать математическую операцию, которая будет применяться к скопированные данные.
Указывает, что скопированные данные не будут применены к математическим операциям.
Добавляет скопированные данные к данным в конечной ячейке или диапазоне ячеек.
Вычитает скопированные данные из данных в конечной ячейке или диапазоне ячеек.
Перемножая скопированные данные с данными в конечной ячейке или диапазоне ячеек.
Делит скопированные данные на данные в конечной ячейке или диапазоне ячеек.
Другие параметры
Пропуск пустых страниц
Не следует заменять значения в области в области в виде вилки, если пустые ячейки возникают в области копирования при выборе этого квадрата.
Транспонировать
При этом столбцы скопированные данные будут меняться на строки и наоборот.
Вставить связь
Щелкните, чтобы создать ссылку на скопированные ячейки.
Проверка и исправление ссылок на ячейки в формуле
Примечание: Ссылки на ячейки автоматически корректируются при вырезании (а не копировании) и копировании формул.
После вложения скопированной формулы проверьте правильность всех ссылок на ячейки в новом расположении. Ссылки на ячейки могут быть изменены в зависимости от типа ссылки (абсолютной, относительной или смешанной), используемой в формуле.
Например, если скопировать формулу в ячейку A1 и вкопировать ее на две ячейки вниз и вправо (C3), ссылки на ячейки в вкопиной формуле изменятся следующим образом:
Новая ссылка
$A$1 (абсолютный столбец и абсолютная строка)
A$1 (относительный столбец и абсолютная строка)
$A1 (абсолютный столбец и относительная строка)
A1 (относительный столбец и относительная строка)
Если ссылки на ячейки в формуле не дают нужного результата, попробуйте переключиться на другие типы ссылок:
Вы выберите ячейку, содержащую формулу.
В выберите ссылку, которая вы хотите изменить.
Нажмите F4, чтобы переключиться между сочетаниями ссылок, и выберите нужный вариант.
Дополнительные сведения о ссылках на ячейки см. в обзоре формул.
При копировании в Excel в Интернете, вы можете выбрать параметры в области вклеек в ячейки назначения.
Название параметра
Что в нее в pasted (что в нее в последний
Вставка всего содержимого скопированных ячеек.
Вставка формул
Формулы без форматирования.
Paste Values
Результаты формулы без форматирования.
Форматирование в виде ветвей
Только форматирование скопированные ячейки.
Вставить и транспонировать
Все содержимое ячейки, но при его вклеии будет меняться содержимого. Данные строк будут вставлены в столбцы, и наоборот.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Краткое описание проблемы: я бы хотел, чтобы Excel НЕ ссылался на ссылки на электронные таблицы, которые я копирую и вставляю, пока у меня не будет полной настройки электронной таблицы.
Обзор: у меня есть лист, который служит для агрегирования набора данных из ряда дополнительных книг, и когда я добавляю новый столбец данных, я обычно копирую ранее связанный столбец, а затем выполняю поиск и замену, чтобы получить другой набор данных из тех же столбцов (т. е. в первом столбце я извлекаю именованную ячейку Data1 из каждой связанной рабочей книги и хочу извлечь именованную ячейку Data2 чтобы скопировать / вставить столбец Data1 а затем найти и заменить новый столбец получить именованную ячейку Data2 из каждой книги)
Проблема в том, что у меня нет только одной книги, которая делает это, у меня есть много, и мне приходится регулярно создавать новые, и когда я копирую и вставляю связанный файл, Excel отстает, когда он вставляется в новый столбец, и я предполагаю, что начинает связываться к рабочим тетрадям. Я хотел бы, чтобы он этого не делал и позволил мне настроить все мои столбцы (он делает это для каждого), найти и заменить, а затем я могу обновить ссылки. Вроде как, если вы установите свои вычисления вручную, вы можете настроить кучу различных формул, а затем нажать F9, чтобы обновить все сразу. К сожалению, установка Excel для ручного вычисления не мешает ему пройти через процесс связывания с именованными ячейками для каждой рабочей книги, когда весь столбец копируется и вставляется, и при настройке новой рабочей книги происходит значительное отставание.
Если кто-нибудь знает хороший способ решения этой проблемы, это сэкономит мне много времени, когда я создаю новые рабочие книги по агрегации.
Лучший пример . Может быть, просто для ясности, когда я говорю «связанная ячейка», я имею в виду ячейку с формулой C:\Some folder\someworkbook.xlsm'NamedCellNumberOne и у меня будет столбец с несколькими из них для NamedCellNumberOne в ряде различных книг и Я хочу скопировать и вставить это и ввести NamedCellNumberTwo в другой столбец. Это когда он отстает и замедляет создание этих вещей.
Читайте также: