Как разорвать связи в excel

6

Разорвать связи в excel

​Смотрите также​​ как умудриться оставить​ If FormulaNew =​ CLL.row) If r1​ чтобы можно было​ Else sNum =​ Error Resume Next​ одна формула, но​ именно этой ссылки.​ по новым менять​ пересмотрите столбцы в​В поле «Связанная таблица»​Из службы данных​ В связи «один​.​ может также использовать​ всех связей без​Примечание:​ в этой ячейке​ FormulaNew & LinkNumber​ = 0 Then​ выбрать конкретный внешний​

​ sNum & s​​ For Each Sh​

​ опять же не​Сложности в том​ и так далее.​ ней, чтобы определить​ выберите​ >​ ко многим» эта​Выберите нужный режим.​ данные из другой​ вывода запроса. Кроме​Мы стараемся как​ ссылку только на​ sLink = «»​ r1 = Val(sNum)​ источник, на который​ If Not UslOpen2​ In ActiveWorkbook.Worksheets For​​ на константу. А​​ что не все​ Но в Excel​ содержит ли другой​

​On_Time_Performance​Из Microsoft Azure Marketplace​ таблица должна быть​Примечание:​ книги. Может потребоваться​ того, если книга​ можно оперативнее обеспечивать​ одну из книг?​

Разрыв связи

​ End If Sboy​​ + CLL.row Else​​ надо удалить ссылки.​​ Then LinkPostFix2 =​​ Each c In​​ на =C2*B2, других​​ функции поддерживают формула​

​ 2000 я не​​ столбец в другом​​, а в поле​​. В мастере импорта​ частью с несколькими​ При наличии разорванных связей​

​ проверить и удалить​​ содержит несколько связей,​​ вас актуальными справочными​ желательно чтобы это​

​ = True ElseIf​ r2 = Val(sNum)​С уважением, Гульмира​ LinkPostFix2 & s​ Sh.UsedRange.Cells If c.HasFormula​

​ измененении не заметила.​ массивов. Т.е. если​

​ нашел функции разрыва​​ месте модели соответствующие​​ «Связанный столбец (первичный​

Удаление имени определенной ссылки

​ таблиц откроется домашняя​ элементами. В примере​ будет появляться оповещение​ эти типы связей.​ можно обновлять только​ материалами на вашем​

​ была ссылка на​​ Status = 0​​ + CLL.row End​​KoGG​​ End If End​​ Then Call Links_To_Constants(c)​​То, что получилось​

​ формула не поддерживает​​ связи :(. Может​​ значения.​ ключ)» —​

​ страница Microsoft Azure​ с клиентами и​

​ об этом.​​В Excel формула удаляется​​ некоторые из них.​

​ языке. Эта страница​​ книгу, а не​​ Then FormulaNew =​

Управление обновлением внешних ссылок (связей)

​ If UslR =​: Sub Заменить_ссылки_на_книгу_константами_в_активной_книге() Dim​ Select If sNum​ End If Next​ в результате выполнения​ формула массива -​ подскажет кто в​Предположим, у вас есть​FlightDate​ Marketplace.​ логикой операций со​

Дополнительные сведения о внешних ссылках

​Нажмите кнопку​ при замене на​Закройте все книги. Если​ переведена автоматически, поэтому​ значение.​ FormulaNew & sTmp​ False ElseIf UslC​ Sh As Worksheet,​ <> «» Then​ c Next Sh​ макроса приложила.​ то она должна​ чем тут загвозка?​ модель, которая содержит​.​

​В разделе​ временем необходимо сначала​

​Обновить​ возвращаемое значение. Чтобы​ одна книга-источник будет​ ее текст может​

​kristina S​ ElseIf Status <​ Then LinkSuffix2 =​ c As Range,​

Управление связями с другими книгами

​ Usl2 = False​ End Sub Sub​KoGG​ заменятся полностью значением,​ Может другие варианты​ продажи продукции по​В поле «Таблица» выберите​Price​ выбрать таблицу продаж​. Это позволит гарантировать​ отменить случайную замену​ открыта, а остальные​ содержать неточности и​: Добрый день! Не​ 4 Then sLink​ LinkSuffix2 & Format(Val(sNum)​ MyFileName$, Arr With​ If j =​ Links_To_Constants(CLL As Range)​

Ручное обновление всех (или никаких) связей в книге

​: Макрос был рассчитан​ а если поддерживает​ какие есть?​ территории, и вы​BasicCalendarUS​

​ клиентов, потому что​ наличие в книге​​ формулы ее значением,​​ закрыты, обновления не​ грамматические ошибки. Для​ понятно, что Вы​​ = sLink &​​ + CLL.Column) If​

Частичное обновление связей с другими книгами вручную

​ Len(LinkPostFix) Then Usl2​

​ Dim FormulaOld$, FormulaNew$,​​ на минимальное понимание​​ — то массивом.​​Другой Excel ставить​​ впоследствии импортируете демографические​​, а в поле​​Free​

​ каждый день, скорее​​ самых последних данных.​​ немедленно после ввода​ будут одинаковыми.​

​ нас важно, чтобы​ спрашиваете, может Вам​ sTmp ElseIf Status​ c1 = 0​

​ «Укажите файл, связи​ = True Else​

​ s$, sNum$, sTmp$,​​ ситуации, что например​​При этом необходимо​

Управление выдачей запроса на обновление всех связей

​ в офисе нельзя,​ данные, чтобы узнать,​ «Столбец (чужой)» —​

Отключение выдачи запроса на обновление связей данной книги и обновление связей вручную

​(Бесплатно).​​ всего, происходит множество​Связь с параметрическим запросом​ или вставки значения​Откройте книгу, содержащую связи.​ эта статья была​ поможет настройка для​ = 4 Then​ Then c1 =​

​ на который будут​​ Select Case s​​ sLink$, LinkPostFix$, LinkPostFix2$​​ нельзя использовать в​​ чтобы ссылки на​​ как мне объяснили​​ есть ли корреляция​

​DateKey​​В разделе​​ продаж.​

​ нельзя обновить без​

​ нажмите кнопку​​Чтобы обновить связи, нажмите​ вам полезна. Просим​ панели быстрого доступа​ If InStr(1, sLink,​ Val(sNum) + CLL.Column​ заменены константами в​​ Case «R», «C»,​

Настройка обновления связей по запросу при открытии файла

​ Dim LinkNumber, i%,​ именах файлов, папок​​ другие книги -​​ лицензия есть только​

Если никакие связи не создавались

​ между продажами и​​. Нажмите​​Category​​Для элемента​​ открытия книги-источника.​​Отменить​​ кнопку​

​ вас уделить пару​​ «разорвать связи» Прикрепленные​​ MyFileName) > 0​

​ Else c2 =​​ формулах:» .InitialFileName =​ «:» Usl2 =​ j%, k%, n%,​​ и листов пробелы​

​ так и оставались​ на этот. ​ демографическими тенденциями на​ОК​(Категория) нажмите​Столбец (чужой)​Нажмите кнопку​.​Параметры​ секунд и сообщить,​ файлы Разорвать связь.jpg​

Разрыв связей с источником

​ Then sLink =​​ Val(sNum) + CLL.Column​ ActiveWorkbook.Path & «\»​ True End Select​ r1&, r2&, c1&,​ и символы математических​ ссылками.​Лузер​ каждой территории. Так​для создания связи.​Science & Statistics​​выберите столбец, который​

​Не обновлять​​Выделите ячейку с формулой.​​в области сведений​​ помогла ли она​​ (27.53 КБ)​​ sLink & sTmp​​ End If UslC​

​ .AllowMultiSelect = False​​ End If If​​ c2&, rOffset&, cOffset&,​ операций. Сейчас адаптировал​

​Возможно нужно записать​: Она там же,​ как демографические данные​Обратите внимание, что время​

​(Наука и статистика).​ содержит данные, относящиеся​

​.​​Если формула является​​ о документе, а​

​ вам, с помощью​bedvit​ Else Sboy =​

​ .ButtonName = «OK»​​ UslArray And Usl2​​ Status%, LenFormula% Dim​​ под данную ситуацию.​​ текущие значения ссылок​​ где и в​​ поступают из различных​

​ задержки в настоящее​​Найдите​​ к элементу​Закройте конечную книгу.​ формула массива, выделите​​ затем выберите команду​​ кнопок внизу страницы.​

​: Разорвать связи с​ True End If​ If sNum =​ .Filters.Add Description: data:image/gif;base64,R0lGODdhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=» data-src=»//img.my-excel.ru/excel-v-tekst-vstavit-formulu-v_19_1.gif» alt=»Кнопка «>​ Для удобства также​

​ нужными файлами -​
​ Else LinkSuffix =​ «» Case «R»​ Excel», _ Extensions: If .Show​​ = 0 Then​​ Sboy As Boolean,​

​В случае использования​​ ссылки их текущими​​ разорвать связь​​ от остальной части​​В таблице​​подписаться​ ​ столбца даты в​

​Нажмите кнопку​​Выбор диапазона, содержащего массив​ ​Закройте все книги.​

​ оригинал (на английском​ в значениях, другие​​ End If End​ ​ LinkSuffix2 = LinkSuffix2​ = 0 Then​​ r1 = Val(sNum)​ ​ UslArray As Boolean,​

Если известно, что подключение к исходному файлу отсутствует

​ российского регионального разделителя​​ значениями.​​Если имелся ввиду​ модели. Для интеграции​BasicCalendarUS​.​ обеих таблицах необходимо​Обновить​ ​

Если требуется работать с имеющимися данными, а не заменять их новыми

​Откройте книгу, содержащую связи.​​ языке) .​​ (не разорванные) останутся​

Если обновление в последний раз заняло слишком много времени

​ If If Sboy​​ & s Case​​ Exit Sub End​ Else r2 =​ _ UslOpen As​ дробной части [,]​К сожалению не​ VBA, то​ демографических данных с​​перетащите​​Введите свои учетные данные​​ выбрать этот столбец​​.​​Щелкните любую ячейку в​​На вкладке​Важно:​ на ссылках.​

Если эта книга создана другим пользователем и вы не знаете, почему появился этот запрос

​ Then FormulaNew =​​ «C» UslC =​​ If MyFileName =​ Val(sNum) End If​ Boolean, UslOpen2 As​ неправильно обрабатывалась дробная​ смыслю в программировании.​​ActiveWorkbook.BreakLink Name:=»связанная_книга.xls», Type:=xlExcelLinks​​ остальной частью своей​​YearKey​​ Майкрософт и нажмите​​ именно сейчас.​​Примечание:​

Если вы каждый раз одинаково отвечаете на этот запрос и хотите, чтобы он больше не отображался

​ диапазоне массив.​Данные​ ​KoDeX​

​ FormulaNew & sLink​ True LinkSuffix2 =​​ .SelectedItems(1) End With​

​ UslR = False​ Boolean, UslR As​ часть — теперь​ И к тому​Алексей​ модели вам нужно​в область строк​

​Sign in​​В поле​​Мы стараемся как​​На вкладке​​в группе​​Когда разорвать связь в​​: При разрыве одной​

​ & sTmp Usl​ LinkSuffix2 & s​​ Arr = Split(MyFileName,​​ ElseIf UslC Then​​ Boolean, UslC As​​ правильно.​​ же наверное это​

​: В том то​​ будет найти столбец​​ над пунктом​​(Вход). Откроется окно​Связанная таблица​​ можно оперативнее обеспечивать​Главная​Подключения​ исходную книгу внешних​

​ связи остается общее​ = False UslArray​​ Case «:» LinkSuffix2​

​ «\») MyFileName =​​ If c1 =​ Boolean FormulaOld =​Со ссылками на​ очень сложная задача​ и дело, что​ в одной из​MonthInCalendar​ предварительного просмотра данных.​

​выберите таблицу, содержащую​​ вас актуальными справочными​​в группе​​нажмите кнопку​​ ссылок, все формулы,​​ значение в ячейке,​​ = False Sboy​

​ = LinkSuffix2 &​​ «[» & Arr(UBound(Arr,​​ 0 Then c1​

​ диапазоны ячеек все​​ раз разработчики Excel​ в Excel 2000​ демографических таблиц, соответствующий​

Если известно, что исходный файл доступен для подключения к нему

​.​​Прокрутите вниз и нажмите​​ хотя бы один​ материалами на вашем​Редактирование​

Если используется параметрический запрос

​Изменить связи​ используйте значение из​ а я спрашиваю​

​ = False Status​​ s Case Else​​ 1)) & «]»​

​ формул R1C1 FormulaNew​

​ еще намного сложнее​

​ не воплотили ее​​ нет такого пункта​​ тому, который вы​

Создание связи между двумя таблицами в Excel

​Теперь вы можете разделить​​Select Query​ столбец данных, которые​ языке. Эта страница​нажмите кнопку​.​ поля в исходной​ как разорвать одну​ = 0 sLink​ sNum = sNum​ For Each Sh​ c2 = Val(sNum)​ = «» If​ — это не​ в реальность, ведь​ как «разорвать связь»​ уже используете. Например,​ задержки прибытия по​(Запрос на выборку).​ связаны с таблицей,​ переведена автоматически, поэтому​Найти и выделить​

​Из списка​ книге преобразуются в​ (две. три. ) связи,​ = «» LinkSuffix​ & s If​ In ActiveWorkbook.Worksheets For​ End If UslC​ InStr(1, FormulaOld, «.xl»)​ обрабатывается.​ все пользователи Excel​А как с​ если демографические данные​ годам и месяцам,​Нажмите кнопку​ выбранной в поле​ ее текст может​и выберите команду​Источник​ свои текущие значения.​ но при этом​ = «» End​ Not UslOpen2 Then​ Each c In​ = False End​ > 0 Then​Sub Заменить_ссылки_константами_в_активной_книге() Dim​ были бы рады​ VBA работать я​ организованы по регионам​ а также другим​

​Далее​Таблица​ содержать неточности и​Выделить группу ячеек​

​выберите связанный объект,​ Например, если разорвать​ чтобы другая, допустим​ If Next i​ LinkSuffix2 = LinkSuffix2​ Sh.UsedRange.Cells If c.HasFormula​ If sNum =​ LenFormula = Len(FormulaOld)​ Sh As Worksheet,​

​ такой дополнительной опции​ не в курсе. ​ и ваши данные​ значениям в календаре.​.​.​ грамматические ошибки. Для​.​

​ который необходимо изменить.​ связь с внешней​

​ четвертая связь осталась​ Else FormulaNew =​ & s End​

​ Then Call Links_To_Constants2(c,​ «» End If​ For i =​​ c As Range,​​ программы.​​slan​​ о продажах определяют​​Советы:​​Чтобы импортировать данные, выберите​

​В поле​ нас важно, чтобы​В диалоговом окне​Для выделения нескольких связей​ ссылкой = сумм​ ссылкой, а остальные​ FormulaOld End If​ If End Select​ MyFileName) End If​

​ End If Next​ 1 To LenFormula​ Formula On Local​Нигде в интернете​: а у меня​ область продажи, то​ По умолчанию месяцы перечислены​BasicCalendarUS​Связанный столбец (первичный ключ)​ эта статья была​Выделить группу ячеек​ щелкните каждый связанный​ ([бюджет.xls] Годовой! C10:​ были значениями либо​

​ If Trim(FormulaNew) =​​ If sNum <>​​ Next c Next​​ j LinkPostFix =​​ sTmp = Mid(FormulaOld,​

​ Error Resume Next​​ не смогла найти​​ нет 2000го​ вы могли бы​ в алфавитном порядке.​

​и нажмите​​выберите столбец, содержащий​​ вам полезна. Просим​​установите флажок​​ объект, удерживая нажатой​

​ C25), формула СУММ​​ вообще их небыло.​​ «=» Then FormulaNew​ «» Then Usl2​​ Sh End Sub​​ LinkPostFix2 If LinkPostFix​ i, 1) Select​ For Each Sh​ реализацию такой задачи.​а выделить ячейки​ связать два набора​ С помощью надстройки​Готово​ уникальные значения, которые​ вас уделить пару​Текущий массив​ клавишу CTRL.​ заменяется вычисленное значение​kristina S​ = «» CLL.FormulaR1C1​

​ = False If​​ Sub Links_To_Constants2(CLL As​​ Like «RC:R*» Then​ Case sTmp Case​ In ActiveWorkbook.Worksheets For​​ На данном форуме​​ со связими можете?​ данных, найдя общие​ Power Pivot вы​. При быстром подключении​ соответствуют значениям в​

​ секунд и сообщить,​​.​​Чтобы выделить все связи,​ — объекты, которые​: непонятно, выборочно связи​ = FormulaNew End​ j = Len(LinkSuffix)​​ Range, MyFileName$) Dim​​ If r2 =​

​ «‘» If Status​​ Each c In​​ есть ссылки на​Алексей​ столбцы, такие как​ можете изменить порядок​ к Интернету импорт​​ столбце, выбранном в​​ помогла ли она​

​На вкладке​​ нажмите клавиши CTRL+A.​​ могут быть. Кроме​

Дополнительные сведения о связях между таблицами в Excel

​ Sub​ Then Usl2 =​ FormulaOld$, FormulaNew$, s$,​ 0 Then r2​

​ = 0 Then​ Sh.UsedRange.Cells If c.HasFormula​

​ поиск подобных ячеек​: ячейки выделяются, копируются,​

​ государство, почтовый индекс​ сортировки так, чтобы​ займет около минуты.​ поле​ вам, с помощью​

Примечания о связях

​Главная​Нажмите кнопку​ того поскольку этой​KoDeX​Гульмира​ True Else Select​ sNum$, sTmp$, sLink$,​ = r1 If​ Usl = True​ Then c.Formula =​ со ссылками на​

​ я даже уже​ или регион, чтобы​ они отображались в​ После выполнения вы​Столбец​ кнопок внизу страницы.​в группе​Обновить значения​ действие​: выборочный разрыв связи,​: Выходит ошибка выполнения​ Case s Case​ LinkSuffix$, LinkSuffix2$ Dim​ c2 = 0​ Status = 1​ Links_To_Constants(c.Formula) End If​

​ внешние источники данных.​ все формулы убрал,​ обеспечить подстановку.​ хронологическом порядке.​ увидите отчет о​.​ Для удобства также​Буфер обмена​.​нельзя отменить​ пример во вложении.​ «1004»​ «R», «C», «:»​ LinkNumber, i%, j%,​ Then c2 =​ End If Case​ Next c Next​Очень надеюсь на Вашу​ вставив только значения,​Кроме совпадающих значений есть​Таблица​ состоянии перемещения 73 414​Нажмите кнопку​ приводим ссылку на​нажмите кнопку​Можно настроить автоматическое обновление​, рекомендуется сохранить версию​KoDeX​Application-defined or object-defined​ Usl2 = True​ k%, n%, r1&,​ c1 r1 =​ «[» If Not​ Sh End Sub​

​ помощь.​ удалял книгу, на​ несколько дополнительных требований​BasicCalendarUS​ строк. Нажмите​ОК​

​ оригинал (на английском​Копировать​ связей при запуске​ конечной книги в​: неужели ни у​ error​ End Select End​ r2&, c1&, c2&,​ CLL.row: c1 =​

Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании

​ Usl Then If​ Function Links_To_Constants(FormulaOld) As​Пример в приложениях.​ которую этот лист​ для создания связей.​должна быть открыта​Закрыть​.​ языке) .​.​ или после запуска.​ качестве резервной копии.​ кого нет никаких​

​Выполнение макроса останавливается​ If If UslArray​ rOffset&, cOffset&, Status%,​ CLL.Column ElseIf LinkPostFix​

​ Status = 0​​ String Dim FormulaNew$,​​Заранее спасибо,​​ ссылается. Проблема в​​Значения данных в столбце​​ в окне Power​​.​Примечания о связях​Вы применяли функцию ВПР,​Нажмите кнопку​

​Предупреждение:​​Если вы используете диапазона​​ идей. ​​ на строке​​ And Usl2 Then​

​ LenFormula% Dim Usl​​ Like «R*C:*» Then​​ Then Status =​​ sTmp$, sLink$ Dim​​С уважением,Гульмира​

​ том, что он​​ подстановки должны быть​​ Pivot.​​Чтобы импортировать второй набор​​Пример. Связывание данных логики​

​ чтобы переместить данные​Вставить​​ Этот режим влияет на​​ внешних данных, параметра​vikttur​

​CLL.FormulaR1C1 = FormulaNew​​ If UslR Then​​ As Boolean, Usl2​

​ If c2 =​​ 1 End If​​ LinkNumber, i%, Usl​

​KoGG​​ все равно постоянно​​ уникальными. Другими словами,​​В главной таблице нажмите​​ данных, нажмите​ операций со временем​ столбца из одной​.​ всех пользователей книги.​ в запросе могут​: Есть. Но не​​На формуле =СЧЁТЕСЛИ(‘D:\Мои​​ If r1 =​

​ As Boolean, Sboy​ 0 Then c2​​ If Status >​​ As Boolean, Sboy​​: Sub Заменить_ссылки_константами_в_активной_книге() Dim​​ спрашивает обновление, можно​​ столбец не может​​Сортировка по столбцу​

​Получение внешних данных​​ с данными по​​ таблицы в другой?​​Нажмите стрелку рядом с​​ Если отключить обновление​

​ использовать данные из​​ по теме.​​ документы\[Книга на которую​​ 0 Then r1​​ As Boolean, UslArray​

​ = c1 c1​​ 3 Then UslOpen​ As Boolean, Status%,​​ Sh As Worksheet,​​ от него отказаться,​​ содержать дубликаты. В​

​.​​ >​​ рейсам авиакомпании​

​ Так как в​​ кнопкой​​ связей и выдачу​

​ другой книги. Вы​​Из правил:​​ ссылки.xls]Лист1′!$B$3:$B$8;»>1″)​ = Val(sNum) Else​ As Boolean, _​ = CLL.Column ElseIf​ = True End​ LenFormula% FormulaNew =​ c As Range,​ но все равно​​ модели данных нули​​В поле «Сортировать» выберите​Из службы данных​»Могут потребоваться связи между​ Excel теперь есть​Параметры вставки​ запроса, пользователи этой​

​ можете обнаружить и​kodex​​Наверное нужно чтобы​​ r2 = Val(sNum)​​ UslOpen As Boolean,​​ LinkPostFix Like «RC*:*»​ If Case «.»​ «» If InStr(1,​​ Formula On Local​​ при следующем входе​ и пустые строки​​MonthInCalendar​​ >​ таблицами»​ встроенная модель данных,​, а затем​ книги не будут​ удалить любой из​: Да не вопрос. ​ выполнение макроса продолжалось​​ End If UslR​​ UslOpen2 As Boolean,​ Then If r2​ If Status =​ FormulaOld, «[«) >​ Error Resume Next​

​ он опять его​ эквивалентны пустому полю,​​.​​Из Microsoft Azure Marketplace​Шаг 1. Определите, какие таблицы​ функция ВПР устарела.​

​ выберите команду​ знать, что данные​​ этих типов связей.​​и все же,​​ пропуская подобные формулы​​ = False ElseIf​ UslR As Boolean,​ = 0 Then​ 1 Then Status​ 0 Then Usl​ For Each Sh​

​ запрашивает. ​​ которое является самостоятельным​​В поле «По» выберите​​.​​ указать в связи​ Вы можете создать​

​Значения​ устарели.​На вкладке​ какое-нить решение есть​ (оставляя их как​ UslC Then If​ UslC As Boolean​

​ r2 = r1​ = 2 End​ = False Status​ In ActiveWorkbook.Worksheets For​​Guest​​ значением данных. Это​

​MonthOfYear​В разделе​​Шаг 2. Найдите столбцы, которые​​ связь между двумя​.​На вкладке​​Данные​​ частичного (выборочного) разрыва​

​ есть).​​ c1 = 0​​ FormulaOld = CLL.FormulaR1C1​ r1 = CLL.row​​ If Case «]»​​ = 0 Sboy​​ Each c In​​: Принеси файл к​

​ означает, что не​.​Type​ могут быть использованы​

​ таблицами на основе​​Нажмите кнопку​​Данные​​в группе​​ связей в ЭКСЕЛЬ. ​Подобные пропущенные формулы​​ Then c1 =​​ ‘ Стиль формул​

​ End If If​ If Status =​ = False LenFormula​ Sh.UsedRange.Cells If c.HasFormula​ тому у кого​

​ может быть несколько​​Сводная таблица теперь сортирует​(Тип) нажмите​ для создания пути​ совпадающих данных в​Не обновлять​в группе​Подключения​XXXXXL​

​ можно будет потом​​ Val(sNum) Else c2​​ R1C1 FormulaNew =​ LinkPostFix Like «*:RC»​ 2 Then Status​

​ = Len(FormulaOld) For​​ Then c.FormulaR1C1 =​​ есть Excel 2003-2007​

​ нулей в столбце​​ каждую комбинацию «месяц​​Data​

​ от одной таблицы​​ них. Затем можно​​. Обновление из источника​

​Подключения​нажмите кнопку​: Всем доброго дня.​ заменить вручную.​ = Val(sNum) End​ «» If InStr(1,​ Then r2 =​ = 3 Else​ i = 1​​ Links_To_Constants(c.FormulaR1C1) End If​​ удали связи сохрани​ подстановок.​ и год» (октябрь​Данные).​ к другой​ создать листы Power​ при отсутствии подключения​

«Могут потребоваться связи между таблицами»

​нажмите кнопку​Изменить связи​ Админ, точно такую​С уважением, Гульмира​ If UslC =​ FormulaOld, MyFileName) >​ CLL.row: c2 =​ Sboy = True​

​ To LenFormula sTmp​ Next c Next​ и бегом обратно​Типы данных столбца подстановок​ 2011, ноябрь 2011)​В разделе​Вы узнаете, существуют ли​ View или сводные​ к нему невозможно.​Изменить связи​.​

Шаг 1. Определите, какие таблицы указать в связи

​ же тему разместил​AlenaZ​ False End If​ 0 Then LenFormula​ CLL.Column ElseIf LinkPostFix​ End If UslOpen​ = Mid(FormulaOld, i,​ Sh End Sub​ в свой офис.​ и исходного столбца​ по номеру месяца​Price​ связи, при перетаскивании​ таблицы и другие​ Например, если книга-источник​.​Примечание:​ у формулистов, предполагаю​: Добрый день форумчане!​ sNum = «»​

​ = Len(FormulaOld) For​​ Like «*:R*C» Then​ = False Case​ 1) Select Case​ Function Links_To_Constants(FormulaOld) As​ Думаю должно получиться!​ должны быть совместимы.​ в году (10,​(Цена) нажмите​ полей из разных​ отчеты с полями​ расположена в сети,​Нажмите кнопку​ Отсутствие команды​ ее нужно снести.​Нужна ваша помощь.​ End If End​ i = 1​ c2 = CLL.Column​ «!» If Status​ sTmp Case «‘»​ String Dim FormulaNew$,​Алексей​ Подробнее о типах​ 11). Изменить порядок​Free​ таблиц в список​ из каждой таблицы,​ а подключение к​Запрос на обновление связей​Изменить связи​у кого какие​ Есть много файлов.​ If Next j​ To LenFormula sTmp​ ElseIf LinkPostFix Like​ = 3 Then​ If Status =​ sTmp$, sLink$ Dim​

Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблице к другой

​: Уже проверял -​ данных см. в​ сортировки несложно, потому​(Бесплатно).​ полей сводной таблицы.​ даже если они​ сети отсутствует.​.​означает, что файл​ есть соображения по​

​ В них есть​ LinkSuffix = LinkSuffix2​ = Mid(FormulaOld, i,​ «*:RC*» Then r2​ Status = 4​ 0 Then Usl​ LinkNumber, i%, Usl​ этот вариант работает,​ статье Типы данных​ что канал​Найдите​ Если вам не​ получены из различных​Нажмите кнопку​Выберите нужный режим.​ не содержит связанных​ следующему вопросу:​ ссылки-привязки связей на​ If LinkSuffix Like​ 1) Select Case​ = CLL.row End​ Else Sboy =​ = True Status​ As Boolean, Sboy​ вот только я​ в моделях данных.​DateStream​US Air Carrier Flight​ будет предложено создать​ источников. Например, если​Не обновлять​Примечание:​ данных.​Условие: В одну​ другие книги. Раньше​ «RC:R*» Then If​

​ sTmp Case «‘»​ If If UslArray​ True End If​

​ = 1 End​ As Boolean, Status%,​ создал этот файл​Подробнее о связях таблиц​предоставляет все необходимые​ Delays​ связь, то в​ у вас есть​.​ Даже если запретить Excel​В списке​ ячейку, допустим B3​ эти связи были​ r2 = 0​

​ If Status =​ Then FormulaNew =​ Case «R» If​ If Case «[«​ LenFormula% FormulaNew =​ для дальнейших работ​ см. в статье​

​ столбцы для работы​и нажмите​ Excel уже есть​ данные о продажах​

Excel не разрывает связь с внешней книгой (Формулы/Formulas)

​ клиентам, вам может​​Не обновлять​ обновление связей, пользователь​выберите связь, которую​

​ трех книг, при​​ часть книг были​ r1 If c2​

​ = True Status​​ If r2 <​ Then Status =​

​ Then Status =​​ FormulaOld, «[«) >​

​ раз выбегать из​​ в модели данных.​ вы используете другую​(Выбрать).​ связи данных.​ потребоваться импортировать и​. Если не требуется​ будет получать сообщения​

​ требуется разорвать.​​ разрыве связи хотя​
​ удалены(т.к. перестали быть​
​ = 0 Then​
​ = 1 End​ r1 Then k​ 5 End If​
​ 1 End If​ 0 Then Usl​ офиса для обработки​
​К началу страницы​
​ таблицу логики операций​Прокрутите вниз и нажмите​
​Создание связей подобно использованию​ связать данные логики​

​ получение текущих данных,​
​ о наличии разорванных​Для выделения нескольких связанных​
​ бы одной из​ нужными данные из​ c2 = c1​
​ If Case «[«​ = r2: r1​
​ Case «C» If​ Case «]» If​ = False Status​ ведет к потере​rogert​ со временем, ваши​Select Query​ команд vlookup. Необходимы​ операций со временем,​

​ можно сэкономить время,​​ связей​ объектов, удерживайте нажатой​

Как в Excel 2000 можно разорвать связь с листом?

​ этих книг, в​​ них). И теперь​ r1 = CLL.row:​ If Not Usl​ = r2: r2​ Status = 5​ Status = 1​ = 0 Sboy​ смысла в этой​: Через стандартную команду​ действия будут другими.​(Запрос на выборку).​ столбцы, содержащие совпадающие​ чтобы проанализировать тенденции​ не обновляя все​.​ клавишу CTRL и​ ячейке В3 остается​ при открытии файла​ c1 = CLL.Column​ Then If Status​ = k If​ Then Status =​ Then Status =​
​ = False LenFormula​ программке :(.​ «Разорвать связь» в​По мере добавления полей​Нажмите кнопку​

​ данные, так что​​ продаж по годам​ связи. После открытия​Существует несколько вариантов выбора​ щелкните каждый связанный​ значение.​
​ выдаёт ошибку при​ ElseIf LinkSuffix Like​
​ = 0 Then​

​ c2 < c1​​ 6 End If​ 2 End If​ = Len(FormulaOld) For​Может кто знает​ меню данные, ничего​
​ в сводную таблицу​Далее​ Excel может создавать​

​ и месяцам.​​ книги нажмите на​ в диалоговом окне​

​ объект.​Вопрос: Как разорвать​

​ обновлении связей. И​​ «R*C:*» Then If​ Status = 1​ Then k =​ Case «:» If​ Case «!» If​ i = 1​ вариант попроще?​ не происходит, связь​ вы получите уведомление​.​ перекрестные ссылки для​Все таблицы в книге​ вкладке​Запрос на обновление связей​Чтобы выделить все связи,​

​ одну (две, . )​​ приходится в ручную​ c2 = 0​ End If If​ c2: c1 =​ Status = 6​ Status = 2​ To LenFormula sTmp​

​slan​​ с внешней книгой​ о необходимости связи​Нажмите​ строк из одной​ указываются в списках​Данные​.​ нажмите клавиши CTRL+A.​ связи, при этом​ разрывать связи с​ Then c2 =​
​ Status > 3​ c2: c2 =​

​ Then UslArray =​​ Then Status =​ = Mid(FormulaOld, i,​

​: выбегать — это​​ как висела так​ между таблицами, чтобы​

​Готово​ таблицы на строки​
​ полей сводной таблицы​в группе​На вкладке​

​Щелкните элемент​​ в ячейке В3​

​ теми книгами ,​​ c1 c1 =​ Then UslOpen =​

​ k For k​​ True End If​ 3 Else Sboy​ 1) Select Case​
​ надеюсь образно?​ и висит. И​ разобраться с полями,​для импорта данных.​ другой таблицы. В​

​ и Power View.​​Подключения​Данные​Разорвать​

​ 1)_осталась ссылка на​​ которые были удалены,​ CLL.Column ElseIf LinkSuffix​ True End If​
​ = r1 To​ Case «-» If​ = True End​ sTmp Case «‘»​Алексей​

​ из списка связей​ выбранными в сводной​ При быстром подключении​

​ примере с логикой​​При импорте связанных таблиц​кнопку​

​в группе​.​
​ книгу не разорванной​
​ и теперь не​
​ Like «RC*:*» Then​ Case «.» If​
​ r2 For n​ Not UslOpen Then​
​ If Case «:»​
​ If Status =​

​: выбегать -​​ в том же​ таблице.​ к Интернету импорт​ операций со временем​

​ из реляционной базы​​Изменить связи​Подключения​Если ссылка используется определенный​ связи, разорванные связи​

Разрыв внешних связей без исчезновения формул

​ существуют. Файлов много,​​ If r2 =​ Status = 1​ = c1 To​ If Status =​ If Status =​ 0 Then Usl​ это надеюсь образно?​ займет около 15 минут.​ таблица клиента должна​ данных Excel часто​, а затем обновите​нажмите кнопку​ имя, имя автоматически​
​ удалились?;​ поэтому долго это​ 0 Then r2​ Then Status =​ c2 s =​ 6 Then Status​ 3 Then Sboy​ = True Status​Ну а как​ не уходит, т.е.​
​ вам, когда необходима​ После выполнения вы​ содержать значения даты,​ может создавать эти​ связи только с​
​Изменить связи​ не удаляется. Может​2)_осталась ссылка на​ в ручную удалять.​ = r1 r1​ 2 End If​
​ sLink & «R»​ = 7 ElseIf​ = True End​ = 1 End​ поступать?​ вообще ничего не​ связь, он не​ увидите отчет о​ которые есть и​ связи в модели​ нужными исходными файлами.​.​
​ потребоваться удалить имя​ книгу не разорванной​Можно ли как​ = CLL.row End​ Case «]» If​ & Format(k) &​ Status > 4​ If Case «​
​ If Case «[«​Постом выше к​
​ происходит когда щелкаешь​
​ может подсказать, какие​
​ состоянии перемещения 2 427 284​

​ в таблице логики​​ данных, формируемой в​Нажмите кнопку​Нажмите кнопку​ также, выполнив следующие​ связи, разорванные связи​ то сделать макрос​ If If LinkSuffix​ Status = 2​ «C» & Format(n)​ Then Sboy =​ «, «+», «-«,​ If Not Usl​ стати то же​ «разорвать связь».​ таблицы и столбцы​ строк. Нажмите​ операций со временем.​ фоновом режиме. В​Не обновлять​Запрос на обновление связей​ действия:​ остались значениями?​ и с помощью​ Like «*:RC» Then​ Then Status =​ LinkNumber = ExecuteExcel4Macro(s)​ True End If​ «/», «*», «^»,​ Then Status =​ моя тема. ​igrtsk​ использовать, а также​Закрыть​В модели данных связи​ других случаях необходимо​. Обратитесь к владельцу​.​На вкладке​XXXXXL​ него удалять испорченные​ r2 = CLL.row:​ 3 Else Sboy​ If IsNumeric(LinkNumber) Then​ End If Case​ «(«, «)», «;»​ 1 End If​slan​: Это происходит на​ возможна ли связь​. Теперь у вас​ таблиц могут быть​ создавать связи вручную.​ книги. Вы также​Щелкните​Формулы​: кто с макросом​ связи с теми​ c2 = CLL.Column​ = True End​ LinkNumber = Replace(LinkNumber,​ » «, «+»,​ If Status =​ Case «]» If​: електронные виды связи..​ разных машинах или​ между таблицами. Чтобы​ есть две таблицы​ типа «один к​Убедитесь, что книга содержит​ можете выяснить, какие​Не отображать оповещение и​в группе​ на «ты»?, сможете​ удалёнными книгами. А​ ElseIf LinkSuffix Like​ If UslOpen =​ «,», «.») Else​ «/», «*», «^»,​ 3 Then Status​ Status = 1​Алексей​ на одном ПК?​ получить ответы на​ в модели данных.​ одному» (у каждого​ хотя бы две​ связи находятся в​ не обновлять автоматические​Определенные имена​ доработать его под​ что бы работающие​ «*:R*C» Then c2​ False Case «!»​ LinkNumber = «»»»​ «(«, «)», «,»,​ = 4 ElseIf​ Then Status =​: Пытаюсь испробовать все​rogert​ свои вопросы, попробуйте​ Чтобы связать их,​ пассажира есть один​ таблицы и в​

​ книге. На вкладке​​ связи​нажмите кнопку​ мои нужды, для​ связи оставались на​ = CLL.Column ElseIf​ If Status =​ & LinkNumber &​ «;» If Status​
​ Status = 2​ 2 End If​ методы :)))​

​: На разных машинах​​ сделать следующее.​ нужны совместимые столбцы​ посадочный талон) или​ каждой из них​Данные​.​Диспетчер имен​ частичного разрыва связей?​ месте!​
​ LinkSuffix Like «*:RC*»​ 3 Then Status​
​ «»»» End If​ = 6 Then​ Then Sboy =​ Case «!» If​Guest​ одно и тоже​
​Если ваша модель содержит​ в каждой таблице.​ «один ко многим»​ есть столбец, который​в группе​
​Возможно, связь была создана​.​XXXXXL​Заранее большущее спасибо!​ Then r2 =​ = 4 Else​ FormulaNew = FormulaNew​ Status = 7​ True End If​ Status = 2​: Эта прога для​igrtsk​ всего лишь несколько​Убедитесь, что значения в​ (в каждом рейсе​ можно сопоставить со​Подключения​ случайно путем перемещения​В диалоговом окне​: вот еще один​pashulka​ CLL.row End If​ Sboy = True​ & LinkNumber If​ ElseIf Status >​ End Select If​ Then Status =​ всего офиса -​: Тогда без вашего​ таблиц, понятно, какие​ столбце​ много пассажиров), но​ столбцом из другой​нажмите кнопку​ или копирования диапазона​Диспетчер имен​ макрос нашел, но​: Пример разрыва связи​ If UslArray Then​ End If Case​ Not (k =​ 4 Then Sboy​ Sboy Then Status​ 3 Else Sboy​ замучаешься переводить. ​ файла не разобраться​ из них нужно​DateKey​ не «многие ко​ таблицы.​Изменить связи​ ячеек, листа или​щелкните имя, которое​ он разрывает связи​ с сохранением значений.​ FormulaNew = FormulaNew​ «R» If Status​ r2 And n​ = True End​ = 0 FormulaNew​ = True End​Можно ли как​rogert​ использовать. Но для​в таблице​ многим». Связи «многие​Отформатируйте данные в виде​.​ диаграммы из одной​ нужно изменить.​ только в выбранных​ Тестировать желательно​ & «<" If​ = 4 Then​ = c2) Then​ If End Select​ = FormulaNew &​ If Case "​ нибудь установить 2007-й​: держите!​ больших моделей вам​BasicCalendarUS​ ко многим" приводят​ таблицы или​Можно ответить на запрос​ книги в другую.​Щелкните имя, чтобы выделить​ ячейках, при этом​на копии​ r2 < r1​ Status = 5​ FormulaNew = FormulaNew​ If Not Sboy​ sLink Sboy =​ ", "+", "-",​ офис, так что​gling​ может понадобиться помощь.​указаны в формате​ к ошибкам циклической​импортируйте внешние данные как​ и запретить его​ Откройте книгу, а​ его.​ заменяет все на​книги.​ Then k =​ End If Case​ & "," End​ Then If i​ False sLink =​ "/", "*", "^",​
​ б и 2000-й​: У меня такое​ Один из способов​ 01.01.2012 00:00:00. В​
​ зависимости, таким как​ таблицу на новый​ вывод для этой​ затем найдите ненужные​
​Нажмите кнопку​ значения, кто сможет​Private Sub Test()​ r2: r1 =​ «C» If Status​ If Next n​ = LenFormula Then​ «» ElseIf i​ «(«, «)» If​ то же работал?​ было из за​ заключается в том,​ таблице​ «Обнаружена циклическая зависимость».​ лист.​ книги в будущем.​ связи и разорвите​Удалить​ доработать его с​ ‘Microsoft Excel XP​ r2: r2 =​ = 5 Then​ Next k FormulaNew​ If Status =​ = LenFormula Then​ Status = 3​Алексей​ именованных диапазонов. Попробуйте​ чтобы использовать представление​On_Time_Performance​ Эта ошибка может​Присвойте каждой из таблиц​Не отображать запрос и​ их или замените​.​ частичным (выборочным) разрывом​ iArr = ActiveWorkbook.LinkSources(xlExcelLinks)​ k If c2​ Status = 6​ = FormulaNew &​ 6 Then LinkPostFix​ If Status =​ Then Status =​: Может в коде​ убрать именованные диапазоны​ диаграммы в надстройке​также есть столбец​ произойти, если вы​ понятное имя: На​ обновлять связи автоматически​ их значениями.​Нажмите кнопку​ связи? помогите, очень​ If Not IsArray(iArr)​ < c1 Then​ End If Case​ «>» sLink =​ = LinkPostFix &​ 3 Then sLink​ 4 Else Sboy​ листа какие функции​ и разорвать связи.​ Power Pivot. Представление​ даты и времени​ создаете прямое подключение​ вкладке​ ​Важно:​ОК​ надо. ​ Then Exit Sub​ k = c2:​ «:» If Status​ «» Else If​ sTmp sTmp =​ = sLink &​ = True End​ прописать. :(​ Сам попробовал не​ диаграммы обеспечивает визуализацию​FlightDate​ между двумя таблицами​Работа с таблицами​Этот параметр влияет на​ При разрыве связей с​.​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub ВставитьЗначения2()​ For Each tmp​ c1 = c2:​ = 6 Then​ LinkPostFix = «RC»​ «» Status =​ sTmp LinkNumber =​ If End Select​slan​ получилось.​ всех таблиц в​, значения которого указаны​ со связью «многие​щелкните​ все книги, которые​ источником все формулы,​При создании внешних ссылок​Dim ArrLinks As​ In iArr If​ c2 = k​ UslArray = True​ Then LinkPostFix =​ 7 End If​ ExecuteExcel4Macro(sLink) If IsNumeric(LinkNumber)​ If i =​: Эта прога для​Саня​ модели данных. С​ в том же​ ко многим» или​Конструктор​ открываются на компьютере.​ использующие источник, заменяются​ (также называемых связями)​ Variant​ ActiveWorkbook.LinkInfo(tmp, xlLinkInfoStatus) =​ For k =​ End If Case​ «R» & Format(CLL.row)​ End If If​ Then LinkNumber =​ LenFormula Then If​ всего офиса -​: на листе Catalog​ помощью него вы​ формате: 01.01.2012 00:00:00.​ непрямые подключения (цепочку​>​ Пользователи, которые откроют​ на их текущее​ можно задать для​Dim i As​ xlLinkStatusMissingFile Then ActiveWorkbook.BreakLink​ r1 To r2​ «-» If Not​ & «C» &​ Status = 7​ Replace(LinkNumber, «,», «.»)​ Status = 3​ замучаешься переводить. ​в ячейках:​ можете быстро определить,​ Два столбца содержат​ связей таблиц, в​Имя таблицы​ книгу на другом​ значение. Например, связь​ них поддержку и​ Integer​ tmp, xlExcelLinks End​ For n =​ UslOpen Then If​ Format(CLL.Column) ElseIf LinkPostFix​ Then sNum =​ Else LinkNumber =​ Then sLink =​Можно ли как​C48:C49,E49,E52,C52,C54:C55,E54:E55,E58,C58,E74,C74,C66,E66​ какие таблицы отделены​ совпадающие данные одинакового​ которой каждая таблица​и введите имя.​ компьютере, не будут​ =СУММ([Бюджет.xls]Годовой!C10:C25) будет заменена​ расписание обновления. В​Dim cell As​ If Next End​ c1 To c2​ Status = 6​ Like «R*C» Then​ «» LinkPostFix2 =​ «»»» & LinkNumber​ sLink & sTmp​ нибудь установить 2007-й​выставлена проверка данных,​ от остальной части​ типа и по​ связана со следующей​Убедитесь, что столбец в​ затронуты.​ на =45. Эту​ случае изменения значения​ Range​ Sub​ s = sLink​ Then Status =​ LinkPostFix = Replace(LinkPostFix,​ «» r1 =​ & «»»» End​ LinkNumber = ExecuteExcel4Macro(sLink)​ офис, так что​ которая и держит​ модели.​ крайней мере один​ отношением «один ко​ одной из таблиц​На вкладке​ операцию нельзя отменить,​ в ячейке приложение​Dim WorkRng As​AlenaZ​ & «R» &​ 7 ElseIf Status​ «C», «C» &​ 0: r2 =​ If FormulaNew =​ If Not IsNumeric(LinkNumber)​ б и 2000-й​ связь.​Примечание:​ из столбцов (​ многим», но между​ имеет уникальные значения​Файл​ поэтому стоит сохранить​ Microsoft Excel не​ Range​:​ Format(k) & «C»​ > 4 Then​ Format(CLL.Column)) ElseIf LinkPostFix​ 0: c1 =​ FormulaNew & LinkNumber​ Then LinkNumber =​ то же работал?​очищаем условие проверки,​ Можно создавать неоднозначные связи,​DateKey​ первой и последней​ без дубликатов. Excel​нажмите кнопку​ версию файла​ выполняет поиск нового​Dim FileName As​pashulka​ & Format(n) LinkNumber​ Sboy = True​ Like «RC*» Then​ 0: c2 =​ Exit For End​ «»»» & LinkNumber​у меня дома​ сохраняем, перезагружаем книгу,​ которые являются недопустимыми​) содержит только уникальные​ образуется отношение «многие​ может создавать связи​Параметры​.​ значения, если это​ String​, огромные спасибки. мур​ = ExecuteExcel4Macro(s) If​ End If End​ LinkPostFix = Replace(LinkPostFix,​ 0 For j​ If End If​ & «»»» FormulaNew​ 2003 и 2007​ связь пропала.​ при использовании в​ значения. В следующих​ ко многим»). Дополнительные​ только в том​и выберите категорию​На вкладке​ не задано пользователем.​On Error Resume​ мур!​ IsNumeric(LinkNumber) Then LinkNumber​ If Case «​ «R», «R» &​ = 1 To​ If Status =​ = FormulaNew &​ работают​как быстро найдено:​ сводной таблице или​ действиях вы будете​ сведения см. в​ случае, если один​Дополнительно​Данные​Книга, содержащая связи, называется​ Next​XXXXXL​ = Replace(LinkNumber, «,»,​ «, «+», «/»,​

​ Format(CLL.row)) End If​​ Len(LinkPostFix) s =​ 4 Then LinkNumber​ LinkNumber Exit For​
​Николай Павлов​1. открываем копию​ отчете Power View.​​ использовать эти столбцы,​​ статье Связи между​ столбец содержит уникальные​.​в группе​
​ книгой назначения, а​If TypeName(Selection) <>​: Всем доброго дня.​
​ «.») Else LinkNumber​ «*», «^», «(«,​ sLink = sLink​
​ Mid(LinkPostFix, j, 1)​ = ExecuteExcel4Macro(sLink) If​ End If End​: Попробуйте вот этот​ книгу винраром​ Пусть все ваши​
​ чтобы связать таблицы.​

​ таблицами в модели​​ значения.​В Excel 2007 нажмите​Подключения​ книга, с которой​ «Range» Then Exit​Условие: В одну​ = «»»» &​ «)», «,», «;»​ & LinkPostFix LinkNumber​ Select Case s​ IsNumeric(LinkNumber) Then LinkNumber​ If If Sboy​ код:​2. находим и​ таблицы связаны каким-то​В окне Power Pivot​ данных.​Например, чтобы связать продажи​ кнопку Microsoft Office​нажмите кнопку​ установлена связь, называется​ Sub​ ячейку, допустим B3​ LinkNumber & «»»»​ If Status =​ = ExecuteExcel4Macro(sLink) If​ Case «[» UslOpen2​ = Replace(LinkNumber, «,»,​ Then Status =​Set Wb =​ удаляем папку «externalLinks»​ образом с другими​ нажмите​Типы данных в двух​ клиента с логикой​, щелкните​Изменить связи​ исходной книгой. Если​ArrLinks = ThisWorkbook.LinkSources(xlExcelLinks)​ идут связи с​ End If FormulaNew​ 6 Then Status​ IsNumeric(LinkNumber) Then LinkNumber​ = True Case​ «.») Else LinkNumber​ 0 FormulaNew =​ ActiveWorkbook​3. закрываем архиватор​ таблицами в модели,​Сводная таблица​ столбцах должны быть​ операций со временем,​Параметры Excel​.​ открытая книга (конечный​If IsEmpty(ArrLinks) Then​ трех книг, при​ = FormulaNew &​ = 7 ElseIf​ = Replace(LinkNumber, «,»,​ «]» UslOpen2 =​ = «»»» &​ FormulaNew & sLink​WorkbookLinks = Wb.LinkSources(Type:=xlLinkTypeExcelLinks)​4. открываем файл​ но при попытке​, чтобы создать сводную​ совместимы. Подробные сведения​ обе таблицы должны​и выберите категорию​В списке​ файл) содержит связи​ Exit Sub​ разрыве связи хотя​ LinkNumber If Not​ Status > 4​ «.») Else LinkNumber​ False If UslR​ LinkNumber & «»»»​ Sboy = False​If IsArray(WorkbookLinks) Then​ в Excel​ объединения полей из​ таблицу на новом​ см. в статье​ включать дату в​Дополнительно​Источник​ с другими книгами​Set WorkRng =​ бы одной из​ (k = r2​ Then Sboy =​ = «»»» &​ Then LinkPostFix2 =​ End If FormulaNew​ sLink = «»​For I =​5. Excel ругается,​ разных таблиц вы​ или существующем листе.​ Типы данных в​ одинаковом формате (например,​.​выберите связь, которую​ или файлами (которые​ Selection.SpecialCells(xlCellTypeFormulas)​ этих книг, в​ And n =​ True End If​ LinkNumber & «»»»​ LinkPostFix2 & Format(Val(sNum)​ = FormulaNew &​ End If If​ LBound(WorkbookLinks) To UBound(WorkbookLinks)​ но потом восстанавливает​ получите сообщение «Могут​В списке полей разверните​ моделях данных.​ 01.01.2012) и по​В разделе​ требуется разорвать.​ называются исходными файлами),​For i =​ ячейке В3 остается​ c2) Then FormulaNew​ End Select If​ End If FormulaNew​ + CLL.row) If​ LinkNumber & sTmp​ Status = 4​Wb.BreakLink Name:=WorkbookLinks(I), _​ данные, выдавая следующее:​ потребоваться связи между​ таблицу​Другие способы создания связей​ крайней мере в​Общие​Для выделения нескольких связанных​ а исходные файлы​ 1 To UBound(ArrLinks)​ значение.​ = FormulaNew &​ Not Sboy Then​ = FormulaNew &​ r1 = 0​ Usl = False​ Then LinkNumber =​ Type:=xlLinkTypeExcelLinks​Удаленное свойство: Проверка данных​ таблицами». Наиболее вероятной​On_Time_Performance​ могут оказаться более​ одной таблице (логика​снимите флажок​ объектов щелкните каждый​ изменятся, то связи​FileName = FileNameOnly((CStr(ArrLinks(i))))​Вопрос: Как разорвать​ «,» End If​ If i =​ LinkNumber sLink =​ Then r1 =​ Status = 0​ ExecuteExcel4Macro(sLink) If Not​Next I​ из части /xl/worksheets/sheet7.xml​ причиной является то,​и нажмите​ понятными, особенно если​ операций со временем)​Запрашивать об обновлении автоматических​ из них, удерживая​ в открываемой книге​For Each cell​ одну (две, . )​ Next n Next​ LenFormula Then If​ «» End If​ Val(sNum) + CLL.row​ sLink = «»​ IsNumeric(LinkNumber) Then LinkNumber​end if​6. открываем изначальный​ что вы столкнулись​ArrDelayMinutes​ неизвестно, какие столбцы​ должны быть перечислены​ связей​ нажатой клавишу CTRL.​ могут указывать на​ In WorkRng​ связи, при этом​ k FormulaNew =​ Status = 6​ Sboy = True​ Else r2 =​ ElseIf Status =​ = «»»» &​Igor67​ файл, переходим на​ со связью «многие​, чтобы добавить их​ использовать. Дополнительные сведения​ все даты только​. Если этот флажок​Чтобы выделить все связи,​ устаревшие данные.​If InStr(1, cell.Formula,​ в ячейке В3​ FormulaNew & «>»​ Then LinkSuffix =​ ElseIf Status =​ Val(sNum) + CLL.row​ 0 Then FormulaNew​ LinkNumber & «»»»​: А разве в​ 7-й лист и​ ко многим». Если​ в область значений.​ см. в статье​ один раз в​ снят, связи будут​ нажмите клавиши CTRL+A.​1. Конечная книга содержит​ FileName) Then cell.Formula​ 1)_осталась ссылка на​ sLink = «»​ LinkSuffix & sTmp​ 0 Then FormulaNew​ End If UslR​ = FormulaNew &​ FormulaNew = FormulaNew​ новой книге после​ ищем там проверки​ вы будете следовать​ В сводной таблице​ Создание связи в​ столбце.​ обновляться автоматически (без​Щелкните элемент​ внешние ссылки.​ = cell.Value​ книгу не разорванной​ Else If LinkSuffix​ sTmp = «»​ = FormulaNew &​ = False ElseIf​ sTmp Else sLink​ & LinkNumber &​ СПЕЦИАЛЬНОЙ ВСТАВКИ СВЯЗИ​ данных (Ctrl+G, выделить,​ цепочке связей между​ вы увидите общее​ представлении диаграммы в​Щелкните​ выдачи запроса).​Разорвать​2. Внешняя ссылка (или​Next​ связи, разорванные связи​ = «RC» Then​ Status = 7​ sTmp ElseIf Status​ UslC Then LinkPostFix2​ = sLink &​ sTmp Usl =​ СОХРАНЯЮТСЯ?​ проверки данных -​ таблицами, которые подключаются​ время задержанных рейсов​ Power Pivot.​Данные​Одинаковый запрос для всех​.​ связь) представляет собой​Next​ удалились?;​ LinkSuffix = «R»​ End If End​Гульмира​ = LinkPostFix2 &​ sTmp End If​ False Status =​Лузер​ сначала всех, потом,​ к необходимым для​ в минутах.​Вы можете узнать о​>​ пользователей этой книги​имя связи автоматически не​ ссылку на диапазон​End Sub​2)_осталась ссылка на​ & Format(CLL.row) &​ If If Status​: 2-я версия макроса​ Format(Val(sNum) + CLL.Column)​ Next i Else​ 0 sLink =​: Смотря чего выбрать​ если их будет​ вас таблицам, то​Разверните таблицу​ связях обеих таблиц​Отношения​ ​ удаляется, поэтому его​ ячеек в книге-источнике.​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>Private Function FileNameOnly(fname​ книгу не разорванной​ «C» & Format(CLL.Column)​ = 7 Then​ у меня опять​ If c1 =​ FormulaNew = FormulaOld​ «» ElseIf Status​ в спец вставке,​ несколько, по одному)​ вы, вероятно, обнаружите​BasicCalendarUS​ и логики операций​.​Предупреждение:​ может потребоваться удалить.​3. Книга-источник содержит ячейку,​ As String) As​ связи, разорванные связи​ ElseIf LinkSuffix Like​ sNum = «»​ почему-то не работала.​ 0 Then c1​ End If If​ = 0 Then​ если значения, то​rogert​ наличие двух или​и нажмите​ со временем с​Если команда​ Этот режим влияет на​Удаление имени​ значение из которой​

​ String​​ остались значениями?​ «R*C» Then LinkSuffix​
​ LinkSuffix2 = «»​А вот третья​
​ = Val(sNum) +​ FormulaNew = » один​
​MonthInCalendar​ помощью свободных данных​Отношения​ всех пользователей книги.​На вкладке​
​ загружается и помещается​’ Возвращает имя​Закрыто. Дубль.​
​ = Replace(LinkSuffix, «C»,​

Разрыв ТОЛЬКО ошибочных связей в excel

​ r1 = 0:​​ версия работает, но​
​ CLL.Column Else c2​ Then FormulaNew =​ & sTmp Else​Гульмира​ метод! Сработало)​ ко многим» между​, чтобы добавить его​ на Microsoft Azure​недоступна, значит книга​ Если отключить обновление​Формулы​ в конечную книгу.​ файла fname без​KoDeX​ «C» & Format(CLL.Column))​ r2 = 0:​ разрывает связи со​ = Val(sNum) +​ «» Links_To_Constants =​ sLink = sLink​: Есть две книги.​Алексей​
​ таблицами. Не существует​ в область строк.​ Marketplace. Некоторые из​ содержит только одну​ связей и выдачу​в группе​Когда обе связанные книги​ указания его директории​: Доброго дня. Вот​
​ ElseIf LinkSuffix Like​

​ c1 = 0:​​всеми​ CLL.Column End If​ FormulaNew End Function​​ & sTmp End​​ В одной есть​
​: Сделал небольшую програмку,​ простого обходного пути,​Обратите внимание, что теперь​ этих наборов данных​ таблицу.​ запроса, пользователи этой​Определенные имена​ открыты на одном​Dim temp As​ не смог пока​ «RC*» Then LinkSuffix​ c2 = 0​

​внешними ссылками, а​​ UslC = False​​Доработанная версия для​​ If Next i​ ссылка на другие​

выборочный (частичный) разрыв связи (Формулы/Formulas)

​ в которой на​​ который бы работал​
​ в сводной таблице​ очень велики, и​В окне​ книги не будут​нажмите кнопку​ компьютере, связи обновляются​ Variant​ найти на «Планете. «​ = Replace(LinkSuffix, «R»,​
​ For j =​ нужно чтобы разрывалась​ End If sNum​ ссылок на диапазоны,​ Else FormulaNew =​ книги. В Excel​ нескольких листах идут​ в любой ситуации,​
​ перечислены месяцы, но​ для их загрузки​Управление связями​ знать, что данные​
​Диспетчер имен​

как разорвать связи в ячейке, но чтобы одна ссылка осталась?

​ автоматически. Когда конечная​​If fname =​ этого, казалось бы​ «R» & Format(CLL.row))​ 1 To Len(LinkSuffix)​ связь только с​ = «» Case​ кроме того правильно​ FormulaOld End If​ при разрыве связей​ расчеты, а на​ но вы можете​ количество минут одинаковое​ за разумное время​нажмите кнопку​ устарели.​.​ книга открыта, а​ «» Then FileNameOnly​ легкого, и неужели​ End If sLink​ s = Mid(LinkSuffix,​

​ указанными.​​ «R» UslR =​ обрабатываются относительные ссылки.​ If FormulaNew =​ происходит замена формул​ одном итоговая таблица.​ попробоватьсоздать вычисляемые столбцы,​ для каждого месяца.​ необходимо быстрое подключение​

​Создать​​На вкладке​В столбце​ книга-источник не открыта,​ = «»: Exit​ никому не нужного​ = sLink &​

​ j, 1) Select​​В моем случае​ True LinkPostFix2 =​Тем не менее,​ » книгой на​

​ LinkPostFix2 & s​​ функция СЧЕТЕСЛИ не​ = «» Links_To_Constants​ внешний источник текущим​

​ разные и итоговая​​ которые вы хотите​ указывающие на связь.​
​Запустите надстройку Power Pivot​

​В окне​​в группе​
​выберите имя, которое​ о документе возможно​temp = Split(fname,​ — в сводный​

выборочный (частичный) разрыв связи (Макросы/Sub)

​ ExecuteExcel4Macro(sLink) If IsNumeric(LinkNumber)​​ «[» UslOpen2 =​ которую» ссылки».​ Case «C» UslC​ срабатывает при замене​ = FormulaNew End​
​ значением.​ таблица соответственно должна​ использовать в одной​
​В списке полей, в​ в Microsoft Excel​Создание связи​Подключения​ следует удалить, и​ появление запроса на​ Application.PathSeparator)​ отчет, а точнее​ Then LinkNumber =​
​ True Case «]»​А ссылка на​ = True LinkPostFix2​ диапазона массивом констант<1,2,0,1>.​ Function​Мне же необходим​ меняться, поэтому решил​ таблице.​
​ разделе «Могут потребоваться​ и откройте окно​щелкните стрелку рядом​нажмите кнопку​

​ нажмите кнопку​​ обновление связей. Можно​FileNameOnly = temp(UBound(temp))​ предположим в одну​ Replace(LinkNumber, «,», «.»)​ UslOpen2 = False​

​ «книга4» должна остаться​​ = LinkPostFix2 &​Sub Заменить_ссылки_константами_в_активной_книге() Dim​Гульмира​ макрос, с помощью​ копировать лист с​После того как вы​ связи между таблицами»​ Power Pivot.​ с полем​Изменить связи​Удалить​
​ задать режим вывода​
​End Function​ ячейку идут данные​
​ Else LinkNumber =​ If UslR Then​
​ ссылкой.​ s Case «:»​
​ Sh As Worksheet,​: Скопировала Ваш макрос​
​ которого формула не​ таблицой из книги​
​ определили, какая таблица​ нажмите​
​Нажмите​Таблица​.​
​.​
​ этого запроса, а​Коды надо оформлять​
​ из разных источников​ «»»» & LinkNumber​
​ LinkSuffix2 = LinkSuffix2​Буду очень признательная​
​ LinkPostFix2 = LinkPostFix2​
​ c As Range,​ в книгу, при​
​ терялась, а заменялось​ для сохранения прошлых​ не связана с​
​Создать​
​Получение внешних данных​
​и выберите таблицу​
​Нажмите кнопку​Если используется диапазон внешних​ также режим обновления​
​ тегами (кнопка #)​ (книга1, книга2, книга3),​ & «»»» End​
​ & Format(Val(sNum) +​ если доработаете макрос​
​ & s Case​ Formula On Local​ выполнении заменяется только​ только текущее значение​
​ расчетов, а оставшийся​ остальной частью модели,​
​.​
​ >​
​ из раскрывающегося списка.​Запрос на обновление связей​

Как в excel разорвать связи

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

К сожалению, если книга-источник была удалена/перемещена или переименована, то связь нарушится. Также связь будет потеряна если вы переместите конечный файл (содержащий ссылку). Если вы передадите только конечный файл по почте, то получатель тоже не сможет обновить связи.

При нарушении связи, ячейки со ссылками на другие книги будут содержать ошибки #ССЫЛКА.

Как разорвать связь

Один из способов решения данной проблемы – разрыв связи. Если в файле только одна связь, то сделать это довольно просто:

1. Перейдите на вкладку Данные.
2. Выберите команду Изменить связи в разделе Подключения.
3. Нажмите Разорвать связь.

ВАЖНО! При разрыве связи все формулы ссылающиеся на книгу-источник будут преобразованы в значения! Отмена данной операции невозможна!

Как разорвать связь со всеми книгами

Для удобства, можно воспользоваться макросом, который разорвет связи со всеми книгами. Макрос входит в состав надстройки VBA-Excel. Чтобы им воспользоваться необходимо:

1. Перейти на вкладку VBA-Excel.
2. В меню Связи выбрать команду Разорвать все связи.
   

Код на VBA

Код макроса удаляющего все связи с книгой представлен ниже. Можете скопировать его в свой проект.

Как разорваться связи только в выделенном диапазоне

Иногда в книге имеется много связей и есть опасения, что при удалении связи можно удалить лишнюю. Чтобы этого избежать с помощью надстройки можно удалить связи только в выделенном диапазоне. Для этого:

1. Выделите диапазон данных.
2. Перейдите на вкладку VBA-Excel (доступна после установки).
3. В меню Связи выберите команду Разорвать связи в выделенных ячейках.
   

Разрыв связи с внешним ресурсом в Excel

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .

Когда разорвать связь в исходную книгу внешних ссылок, все формулы, используйте значение из поля в исходной книге преобразуются в свои текущие значения. Например, если разорвать связь с внешней ссылкой = сумм ([бюджет.xls] Годовой! C10: C25), формула СУММ заменяется вычисленное значение — объекты, которые могут быть. Кроме того поскольку этой действие нельзя отменить, рекомендуется сохранить версию конечной книги в качестве резервной копии.

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

Разрыв связи

На вкладке Данные в группе Подключения нажмите кнопку Изменить связи.

Примечание: Отсутствие команды Изменить связи означает, что файл не содержит связанных данных.

В списке Источник выберите связь, которую требуется разорвать.

Для выделения нескольких связанных объектов, удерживайте нажатой клавишу CTRL и щелкните каждый связанный объект.

Чтобы выделить все связи, нажмите клавиши CTRL+A.

Щелкните элемент Разорвать.

Удаление имени определенной ссылки

Если ссылка используется определенный имя, имя автоматически не удаляется. Может потребоваться удалить имя также, выполнив следующие действия:

На вкладке Формулы в группе Определенные имена нажмите кнопку Диспетчер имен.

В диалоговом окне Диспетчер имен щелкните имя, которое нужно изменить.

Щелкните имя, чтобы выделить его.

Нажмите кнопку Удалить.

Нажмите кнопку ОК.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Как в excel разорвать связи

На разных машинах одно и тоже

На разных машинах одно и тоже rogert

На разных машинах одно и тоже Автор – rogert
Дата добавления – 10.09.2014 в 12:14

на листе Catalog
в ячейках:
C48:C49,E49,E52,C52,C54:C55,E54:E55,E58,C58,E74,C74,C66,E66

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

как быстро найдено:
1. открываем копию книгу винраром
2. находим и удаляем папку “externalLinks”

3. закрываем архиватор
4. открываем файл в Excel
5. Excel ругается, но потом восстанавливает данные, выдавая следующее:
Удаленное свойство: Проверка данных из части /xl/worksheets/sheet7.xml
6. открываем изначальный файл, переходим на 7-й лист и ищем там проверки данных (Ctrl+G, выделить, проверки данных – сначала всех, потом, если их будет несколько, по одному)

на листе Catalog
в ячейках:
C48:C49,E49,E52,C52,C54:C55,E54:E55,E58,C58,E74,C74,C66,E66

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

как быстро найдено:
1. открываем копию книгу винраром
2. находим и удаляем папку “externalLinks”

3. закрываем архиватор
4. открываем файл в Excel
5. Excel ругается, но потом восстанавливает данные, выдавая следующее:
Удаленное свойство: Проверка данных из части /xl/worksheets/sheet7.xml
6. открываем изначальный файл, переходим на 7-й лист и ищем там проверки данных (Ctrl+G, выделить, проверки данных – сначала всех, потом, если их будет несколько, по одному) Саня

Сообщение на листе Catalog
в ячейках:
C48:C49,E49,E52,C52,C54:C55,E54:E55,E58,C58,E74,C74,C66,E66

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

как быстро найдено:
1. открываем копию книгу винраром
2. находим и удаляем папку “externalLinks”

3. закрываем архиватор
4. открываем файл в Excel
5. Excel ругается, но потом восстанавливает данные, выдавая следующее:
Удаленное свойство: Проверка данных из части /xl/worksheets/sheet7.xml
6. открываем изначальный файл, переходим на 7-й лист и ищем там проверки данных (Ctrl+G, выделить, проверки данных – сначала всех, потом, если их будет несколько, по одному) Автор – Саня
Дата добавления – 10.09.2014 в 14:13

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Создание связи между таблицами Excel

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

Межтабличные связи в Excel используются для получения данных как с других листов рабочей книги, так и с других рабочих книг Excel. К примеру, у вас имеется таблица с расчетом итоговой суммы продаж. В расчете используются цены на продукт и объем продаж. В таком случае имеет смысл создать отдельную таблицу с данными по ценам, которые будут подтягиваться с помощью связей первой таблицы.

Когда вы создаете связь между таблицами, Excel создает формулу, которая включает в себя имя исходной книги, заключенную в скобки [], имя листа с восклицательным знаком на конце и ссылку на ячейку.

Создание связей между рабочими книгами

1. Открываем обе рабочие книги в Excel
2. В исходной книге выбираем ячейку, которую необходимо связать, и копируем ее (сочетание клавиш Ctrl+С)
3. Переходим в конечную книгу, щелкаем правой кнопкой мыши по ячейке, куда мы хотим поместить связь. Из выпадающего меню выбираем Специальная вставка
4. В появившемся диалоговом окне Специальная вставка выбираем Вставить связь.

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

Вы можете использовать инструменты копирования и автозаполнения для формул связи так же, как и для обычных формул.

Прежде чем создавать связи между таблицами

Прежде чем вы начнете распространять знания на свои грандиозные идеи, прочитайте несколько советов по работе со связями в Excel:

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

Автоматические вычисления. Исходная книга должна работать в режиме автоматического вычисления (установлено по умолчанию). Для переключения параметра вычисления перейдите по вкладке Формулы в группу Вычисление. Выберите Параметры вычислений –> Автоматически.

Избегайте циклические ссылки. Циклические связи – когда две рабочие книги содержат ссылки друг на друга – могут быть причиной медленного открытия и работы файла.

Обновление связей

Для ручного обновления связи между таблицами, перейдите по вкладке Данные в группу Подключения. Щелкните по кнопке Изменить связи.

В появившемся диалоговом окне Изменение связей, выберите интересующую вас связь и щелкните по кнопке Обновить.

Разорвать связи в книгах Excel

Разрыв связи с источником приведет к замене существующих формул связи на значения, которые они возвращают. Например, связь =[Источник.xlsx]Цены!$B$4 будет заменена на 16. Разрыв связи нельзя отменить, поэтому прежде чем совершить операцию, рекомендую сохранить книгу.

Перейдите по вкладке Данные в группу Подключения. Щелкните по кнопке Изменить связи. В появившемся диалоговом окне Изменение связей, выберите интересующую вас связь и щелкните по кнопке Разорвать связь.

Вам также могут быть интересны следующие статьи

5 комментариев

Спасибо! очень полезный материал!

Пожалуйста, исправьте опечатку:
«В исходной книге выбираем ячейку, которую необходимо связать, и копируем ее (сочетание клавиш Ctrl+V)»
Думаю должно быть «Ctrl+С»

Трюк №13. Удаление несуществующих ссылок на книги Excel

О, несуществующие ссылки! Вы открываете рабочую книгу и видите сообщение с просьбой обновить ссылки — но у вас нет никаких ссылок! Как же можно обновить то, что не существует?

Внешние ссылки — это ссылки, указывающие на другие книги. Непредвиденные внешние ссылки могут появиться по различным причинам, начиная от перемещения или копирования диаграмм, листов диаграмм или рабочих листов в другую рабочую книгу. Но знание того, что внешние ссылки существуют, не всегда помогает найти их. Вот несколько способов расправиться с проблемой несуществующих ссылок.

Сначала необходимо узнать, присутствуют ли в книге реальные внешние ссылки (существующие), о которых вы просто забыли. Если вы не уверены, есть ли такие, начните с поиска в самом очевидном месте — в формулах. Для этого удостоверьтесь, что никакие другие книги не открыты и выполните поиск по ключевой фразе [*] в формулах на всех листах. Закройте все остальные книги, чтобы гарантировать, что все ссылки в формулах будут содержать [*], где звездочка представляет собой символ шаблона.

Когда вы удостоверитесь, что в формулах ссылок нет, нужно будет убедиться, что допустимые ссылки не прячутся где-либо еще. Для этого нужно начать из глубины книги Excel, содержащей несуществующие ссылки. Выберите команду Вставка → Имя → Присвоить (Insert → Name → Define). Пролистайте список имен, щелкая и выделяя их по очереди и просматривая формулу в поле Формула (Refers to) внизу. Удостоверьтесь, что ни одно из имен не ссылается на другую рабочую книгу.

Другой возможный источник ссылок — это ваши диаграммы. У них может проявиться та же проблема, которую мы только что рассматривали. Необходимо проверить, что все диапазоны данных и метки оси X (X-axis) диаграммы не ссылаются на внешние книги. И снова вам решать, являются ли найденные ссылки правильными.

Ссылки скрываются и в объектах, например, текстовых полях, автофигурах и т. д. Объекты могут ссылаться на внешние рабочие книги. Самый простой способ найти объекты — выбрать любую ячейку на листе и затем выбрать команду Правка → Перейти (Edit → Go To). В диалоговом окне Переход (Go To) щелкните на кнопке Выделить (Special), установите флажок Объекты (Objects) и щелкните на кнопке ОК. Будут выбраны все объекты на листе. Это необходимо выполнять на копии вашей рабочей книги. Когда все объекты будут выделены, вы сможете удалить, сохранить, закрыть и снова открыть вашу копию, чтобы проверить, решена ли проблема.

И, наконец, последнее неочевидное место, где можно найти реальные ссылки — это скрытые листы, которые вы могли создать и забыть о них. Отобразите такие листы, выбрав команду Формат → Лист → Отобразить (Format → Sheet → Unhide). Если пункт Отобразить (Unhide) во вложенном меню Лист (Sheet) неактивен, это означает, что у вас нет скрытых листов. (Если вы уверены, что в книге есть листы, не появляющиеся в этом меню, обратитесь за подробностями к разделу «Трюк № 5. Как скрыть лист, чтобы его невозможно было отобразить».)

Теперь, когда вы устранили возможность появления реальных ссылок, настало время удалить несуществующие. Откройте рабочую книгу с несуществующими ссылками и выберите команду Правка → Связи (Edit → Links). Иногда можно просто выделить ненужную ссылку, щелкнуть кнопку Изменить (Change Source) и связать ссылку с самой собой. Но чаще всего появляется сообщение, что одна из формул содержит ошибку, и это сделать невозможно.

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

Создайте настоящую связь между двумя книгами, открыв обе. Перейдите к проблемной книге и в любой ячейке любого листа введите =. Теперь щелкните любую ячейку в хорошей книге и нажмите клавишу Enter, чтобы создать настоящую внешнюю ссылку на другую книгу. Сохраните обе книги, но не закрывайте их. В книге с несуществующими ссылками выберите команду Правка → Связи (Edit → Links) и щелкните кнопку Изменить (Change Source), чтобы увидеть все ссылки на новую рабочую книгу, с которой вы только что намеренно создали новую связь. Снова сохраните вашу рабочую книгу и удалите ячейку, в которой создали настоящую внешнюю ссылку. И еще раз сохраните файл.

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

Мы продолжаем, предполагая, что эта техника у вас сработала. Вот что следует сделать далее. Откройте копию вашей рабочей книги (ту, которая содержит все данные) и сделайте еще одну копию. Вы будете работать с проблемным листом (или листами) и выполнять процесс удаления, чтобы обнаружить, где на листе прячется проблема.

Открыв проблемный лист, выделите некоторый диапазон ячеек (область приблизительно 10 х 10 ячеек) и затем выберите команду Правка → Очистить → Все (Edit → Clear → All). (Вы уверены, что сохранили копию книги?) Сохраните, закройте и заново откройте лист. Если приглашение обновить ссылки не появилось, вы нашли проблему, и теперь необходимо переделать этот блок ячеек. Если приглашение открылось, продолжайте очищать ячейки, пока при очередном открытии приглашение не будет выведено. После этого измените проблемные ячейки.

Мы надеемся, что эти приемы сэкономят ваше рабочее время и нервы, которые тратятся на эти несчастные несуществующие ссылки в рабочих книгах. Это не самые простые и приятные решения, но они позволят забыть о проблемах.

Разорвать связи листа с помощью VBA

Каким VBA скриптом можно разорвать все ссылки активного листа на соседние листы той же книги и превратить их в значения? Тоисть если в формуле ячейки задействована ссылка на другой лист превращать эту формулу в значения

2 ответа 2

Можно проверять наличие имени листа в формуле. Вариант не очень хороший.

Пример. В книге есть лист кекс

В формуле нет сылки на другой лист кекс, но она по ошибке будет преобразована в значение.

Проверять имя листа с восклицательным знаком (кекс!)? Да. Но этого мало: если имя листа имеет пробелы, в формуле оно окружено апострофами (‘вкусные кексы’!) и само имя отделено от восклицательного знака.

Вывод: или дополнительно обрабатывать тест формулы, или для каждого листа проверять по два значения. Второе проще и быстрее.

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

If c.HasFormula Then – проверяем только ячейки с формулами.

Три фрагмента кода, обведенные “рамками” – формирование списка адресов ячеек, в которых удалены формулы. Эти пять строк могут быть безболезненно исключены из макроса.

Cells(10, 10) – указать ссылку за пределами используемого диапазона листа или вывести список на отдельный лист.

Но и тут возможен сюрприэ

Маловероятно, но возможно. Для полной безопасности перед удалением формулы ставим еще одну проверку:

Т.е. если в формуле обнаружен фрагмент с именем листа, заменяем его на что-то неестественное для имени, пробуем вычислить формулу. И если ошибки нет, значит, ложная тревога – фрагмент, похожий на ссылку, присутствует только в текстовой строке. Важно именно менять, а не удалять имя (замена на “”): при удалении останется ссылка на ячейки активного листа и IsError=False – формула со ссылкой на другой лист не удалится.

И это еще не все

После дополнения IsError возможна еще одна ошибка в определении нужной формулы: в формуле в текстовой строке присутствует фрагмент с именем другого листа и сама формула по каким-либо причинам выдает ошибку.

Вернемся к первому примеру с листом кекс:

Наша созданная конструкция честно проверит все три условия: увидит формулу в ячейке; найдет в ней имя листа с восклицательным знаком; проверит, что ошибка есть – и преобразует формулу в результат ее работы!

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

Но это костыль. А правильно: не допускать в работе формул появления ошибок.

Работа со связанными таблицами в Microsoft Excel

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

Связанные таблицы очень удобно использовать для обработки большого объема информации. Располагать всю информацию в одной таблице, к тому же, если она не однородная, не очень удобно. С подобными объектами трудно работать и производить по ним поиск. Указанную проблему как раз призваны устранить связанные таблицы, информация между которыми распределена, но в то же время является взаимосвязанной. Связанные табличные диапазоны могут находиться не только в пределах одного листа или одной книги, но и располагаться в отдельных книгах (файлах). Последние два варианта на практике используют чаще всего, так как целью указанной технологии является как раз уйти от скопления данных, а нагромождение их на одной странице принципиально проблему не решает. Давайте узнаем, как создавать и как работать с таким видом управления данными.

Создание связанных таблиц

Прежде всего, давайте остановимся на вопросе, какими способами существует возможность создать связь между различными табличными диапазонами.

Способ 1: прямое связывание таблиц формулой

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

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

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

Нужно сделать так, чтобы данные о ставках из второго листа подтягивались в соответствующие ячейки первого.

На первом листе выделяем первую ячейку столбца «Ставка». Ставим в ней знак «=». Далее кликаем по ярлычку «Лист 2», который размещается в левой части интерфейса Excel над строкой состояния.

Способ 2: использование связки операторов ИНДЕКС — ПОИСКПОЗ

Но что делать, если перечень сотрудников в табличных массивах расположен не в одинаковом порядке? В этом случае, как говорилось ранее, одним из вариантов является установка связи между каждой из тех ячеек, которые следует связать, вручную. Но это подойдет разве что для небольших таблиц. Для массивных диапазонов подобный вариант в лучшем случае отнимет очень много времени на реализацию, а в худшем – на практике вообще будет неосуществим. Но решить данную проблему можно при помощи связки операторов ИНДЕКС – ПОИСКПОЗ. Посмотрим, как это можно осуществить, связав данные в табличных диапазонах, о которых шел разговор в предыдущем способе.

1. Выделяем первый элемент столбца «Ставка». Переходим в Мастер функций, кликнув по пиктограмме «Вставить функцию».
2. В Мастере функций в группе «Ссылки и массивы» находим и выделяем наименование «ИНДЕКС».
3. Данный оператор имеет две формы: форму для работы с массивами и ссылочную. В нашем случае требуется первый вариант, поэтому в следующем окошке выбора формы, которое откроется, выбираем именно его и жмем на кнопку «OK».
4. Выполнен запуск окошка аргументов оператора ИНДЕКС. Задача указанной функции — вывод значения, находящегося в выбранном диапазоне в строке с указанным номером. Общая формула оператора ИНДЕКС такова:

«Массив» — аргумент, содержащий адрес диапазона, из которого мы будем извлекать информацию по номеру указанной строки.

«Номер строки» — аргумент, являющийся номером этой самой строчки. При этом важно знать, что номер строки следует указывать не относительно всего документа, а только относительно выделенного массива.

«Номер столбца» — аргумент, носящий необязательный характер. Для решения конкретно нашей задачи мы его использовать не будем, а поэтому описывать его суть отдельно не нужно.

«Искомое значение» — аргумент, содержащий наименование или адрес ячейки стороннего диапазона, в которой оно находится. Именно позицию данного наименования в целевом диапазоне и следует вычислить. В нашем случае в роли первого аргумента будут выступать ссылки на ячейки на Листе 1, в которых расположены имена сотрудников.

«Просматриваемый массив» — аргумент, представляющий собой ссылку на массив, в котором выполняется поиск указанного значения для определения его позиции. У нас эту роль будет исполнять адрес столбца «Имя» на Листе 2.

«Тип сопоставления» — аргумент, являющийся необязательным, но, в отличие от предыдущего оператора, этот необязательный аргумент нам будет нужен. Он указывает на то, как будет сопоставлять оператор искомое значение с массивом. Этот аргумент может иметь одно из трех значений: -1; 0; 1. Для неупорядоченных массивов следует выбрать вариант «0». Именно данный вариант подойдет для нашего случая.

Способ 3: выполнение математических операций со связанными данными

Прямое связывание данных хорошо ещё тем, что позволяет не только выводить в одну из таблиц значения, которые отображаются в других табличных диапазонах, но и производить с ними различные математические операции (сложение, деление, вычитание, умножение и т.д.).

Посмотрим, как это осуществляется на практике. Сделаем так, что на Листе 3 будут выводиться общие данные заработной платы по предприятию без разбивки по сотрудникам. Для этого ставки сотрудников будут подтягиваться из Листа 2, суммироваться (при помощи функции СУММ) и умножаться на коэффициент с помощью формулы.

1. Выделяем ячейку, где будет выводиться итог расчета заработной платы на Листе 3. Производим клик по кнопке «Вставить функцию».
2. Следует запуск окна Мастера функций. Переходим в группу «Математические» и выбираем там наименование «СУММ». Далее жмем по кнопке «OK».
3. Производится перемещение в окно аргументов функции СУММ, которая предназначена для расчета суммы выбранных чисел. Она имеет нижеуказанный синтаксис:

Способ 4: специальная вставка

Связать табличные массивы в Excel можно также при помощи специальной вставки.

1. Выделяем значения, которые нужно будет «затянуть» в другую таблицу. В нашем случае это диапазон столбца «Ставка» на Листе 2. Кликаем по выделенному фрагменту правой кнопкой мыши. В открывшемся списке выбираем пункт «Копировать». Альтернативной комбинацией является сочетание клавиш Ctrl+C. После этого перемещаемся на Лист 1.
2. Переместившись в нужную нам область книги, выделяем ячейки, в которые нужно будет подтягивать значения. В нашем случае это столбец «Ставка». Щелкаем по выделенному фрагменту правой кнопкой мыши. В контекстном меню в блоке инструментов «Параметры вставки» щелкаем по пиктограмме «Вставить связь».

Способ 5: связь между таблицами в нескольких книгах

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

1. Выделяем диапазон данных, который нужно перенести в другую книгу. Щелкаем по нему правой кнопкой мыши и выбираем в открывшемся меню позицию «Копировать».
2. Затем перемещаемся к той книге, в которую эти данные нужно будет вставить. Выделяем нужный диапазон. Кликаем правой кнопкой мыши. В контекстном меню в группе «Параметры вставки» выбираем пункт «Вставить связь».
3. После этого значения будут вставлены. При изменении данных в исходной книге табличный массив из рабочей книги будет их подтягивать автоматически. Причем совсем не обязательно, чтобы для этого были открыты обе книги. Достаточно открыть одну только рабочую книгу, и она автоматически подтянет данные из закрытого связанного документа, если в нем ранее были проведены изменения.

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

Изменения в таком массиве, связанном с другой книгой, можно произвести только разорвав связь.

Разрыв связи между таблицами

Иногда требуется разорвать связь между табличными диапазонами. Причиной этого может быть, как вышеописанный случай, когда требуется изменить массив, вставленный из другой книги, так и просто нежелание пользователя, чтобы данные в одной таблице автоматически обновлялись из другой.

Способ 1: разрыв связи между книгами

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

1. В книге, в которой подтягиваются значения из других файлов, переходим во вкладку «Данные». Щелкаем по значку «Изменить связи», который расположен на ленте в блоке инструментов «Подключения». Нужно отметить, что если текущая книга не содержит связей с другими файлами, то эта кнопка является неактивной.
2. Запускается окно изменения связей. Выбираем из списка связанных книг (если их несколько) тот файл, с которым хотим разорвать связь. Щелкаем по кнопке «Разорвать связь».
3. Открывается информационное окошко, в котором находится предупреждение о последствиях дальнейших действий. Если вы уверены в том, что собираетесь делать, то жмите на кнопку «Разорвать связи».
4. После этого все ссылки на указанный файл в текущем документе будут заменены на статические значения.

Способ 2: вставка значений

Но вышеперечисленный способ подходит только в том случае, если нужно полностью разорвать все связи между двумя книгами. Что же делать, если требуется разъединить связанные таблицы, находящиеся в пределах одного файла? Сделать это можно, скопировав данные, а затем вставив на то же место, как значения. Кстати, этим же способом можно проводить разрыв связи между отдельными диапазонами данных различных книг без разрыва общей связи между файлами. Посмотрим, как этот метод работает на практике.

1. Выделяем диапазон, в котором желаем удалить связь с другой таблицей. Щелкаем по нему правой кнопкой мыши. В раскрывшемся меню выбираем пункт «Копировать». Вместо указанных действий можно набрать альтернативную комбинацию горячих клавиш Ctrl+C.
2. Далее, не снимая выделения с того же фрагмента, опять кликаем по нему правой кнопкой мыши. На этот раз в списке действий щелкаем по иконке «Значения», которая размещена в группе инструментов «Параметры вставки».
3. После этого все ссылки в выделенном диапазоне будут заменены на статические значения.

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

Как удалить (разорвать) связи в документе Word, Excel

При открытии документа MS Word появляется предупреждение о наличии связных документов (связей) в исходном документе:

Документ содержит связи с другими файлами. Обновить в документе данные, связанные с другими файлами?

Такое предупреждение появляется, когда в документе есть ссылки на другие документы (например, на таблицу Excel). Удалить (разорвать) связи в документе MS Word возможно с помощью следующих несложных действий:

(Инструкция для версии MS Word 2016)

1. Открыть исходный документ для редактирования (меню «Вид» — «Изменить документ«):

2. В меню «Файл» выбрать пункт «Сведения«:

3. В разделе «Связные документы» нажимаем пункт «Изменить связи с файлами«:

4. В окне связи возможно удалить связь с другими (внешними) документами с помощью кнопки «Разорвать связь«: