Не разрывается связь в excel

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

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

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

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

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

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

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

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

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

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

Код на VBA

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

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

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

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

Programming and other

Это не баг, а фича.
Популярная поговорка

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

Заходим в редактор связей и лицезреем причину ошибки:

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

В Диспетчере имен пусто:

Не удается отыскать источник неверной ссылки…
Распаковываю книжку и копаюсь в xml файлах:

Наводит на размышления… Изучаю атрибуты класса Names во интегрированном языке программирования, пишу такую микро функцию:

Опосля выполнения процедуры вижу солидный перечень имен в Диспетчере:

Удивляюсь, но не весьма очень =)
Удаляю имена и разрываю связь. Ошибка исчезает.

Может быть ли не пользуясь процедурой на VBA поменять статус укрытых имен? Я не отыскал другого метода (Excel 2007).

Сокрытые имена в Excel : 3 комментария

>> Может быть ли не пользуясь процедурой на VBA поменять статус укрытых имен?

Спасибо за макрос! Брал на вооружение.

Роскошно! Спасибо. Но не посодействовало ( Видимо, просто необходимо удалить все именованные спектры.

Добавить комментарий Отменить ответ

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

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

Читатели Лайфхакера уже знакомы с Денисом Батьяновым, который делился с нами секретами Excel. Сейчас Денис скажет о том, как избежать самых распространённых заморочек с Excel, которые мы часто создаём для себя без помощи других.

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

Вы не даёте заглавия столбцам таблиц

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

Пустые столбцы и строчки снутри ваших таблиц

Это сбивает с толку Excel. Встретив пустую строчку либо столбец снутри вашей таблицы, он начинает мыслить, что у вас 2 таблицы, а не одна. Для вас придётся повсевременно его поправлять. Также не стоит скрывать ненадобные для вас строчки/столбцы снутри таблицы, лучше удалите их.

На одном листе размещается несколько таблиц

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

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

Интересно почитать: Как в эксель преобразовать дату в текст

Данные 1-го типа искусственно размещаются в различных столбцах

Весьма нередко юзеры, которые знают Excel довольно поверхностно, отдают предпочтение такому формату таблицы:

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

Дело в том, что данный формат содержит 2 измерения: чтоб отыскать что-то в таблице, вы должны обусловиться со строчкой, перебирая филиал, группу и агента. Когда вы найдёте подходящую стоку, то позже придётся находить уже подходящий столбец, потому что их здесь много. И эта «двухмерность» очень усложняет работу с таковой таблицей и для обычных инструментов Excel — формул и сводных таблиц.

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

Если вы возжелаете применить обычные формулы суммирования типа СУММЕСЛИ (SUMIF), СУММЕСЛИМН (SUMIFS), СУММПРОИЗВ (SUMPRODUCT), то также обнаружите, что они не сумеют отлично работать с таковой компоновкой таблицы.

Рекомендуемый формат таблицы смотрится так:

Разнесение инфы по различным листам книжки «для удобства»

Ещё одна всераспространенная ошибка — это, имея некий обычный формат таблицы и нуждаясь в аналитике на базе этих данных, разносить её по отдельным листам книжки Excel. К примеру, нередко делают отдельные листы на любой месяц либо год. В итоге объём работы по анализу данных практически множится на число сделанных листов. Не нужно так созодать. Копите информацию на ОДНОМ листе.

Информация в комментах

Нередко юзеры добавляют важную информацию, которая может им пригодиться, в комментарий к ячейке. Имейте в виду, то, что находится в комментах, вы сможете лишь поглядеть (если найдёте). Вынуть это в ячейку проблемно. Рекомендую лучше выделить отдельный столбец для объяснений.

Бардак с форматированием

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

Любая таблица обязана иметь однородное форматирование. Пользуйтесь форматированием умных таблиц. Для сброса старенького форматирования используйте стиль ячеек «Обыденный».
Не выделяйте цветом строчку либо столбец полностью. Выделите стилем определенную ячейку либо спектр. Предусмотрите «легенду» вашего выделения. Если вы выделяете ячейки, чтоб в предстоящем произвести с ними какие-то операции, то цвет не наилучшее решение. Хоть сортировка по цвету и возникла в Excel 2007, а в 2010-м — фильтрация по цвету, но наличие отдельного столбца с чётким значением для следующей фильтрации/сортировки всё равно лучше. Цвет — вещь небезусловная. В сводную таблицу, к примеру, вы его не затащите.
Заведите привычку добавлять в ваши таблицы автоматические фильтры (Ctrl+Shift+L), закрепление областей. Таблицу лучше сортировать. Лично меня постоянно приводило в бешенство, когда я получал каждую недельку от человека, ответственного за проект, таблицу, где не было фильтров и закрепления областей. Помните, что подобные «мелочи» запоминаются весьма навечно.

Объединение ячеек

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

Объединение текста и чисел в одной ячейке

Тягостное воспоминание производит ячейка, содержащая число, дополненное сзаду текстовой константой « РУБ.» либо » USD», введенной вручную. В особенности, если это не печатная форма, а рядовая таблица. Арифметические операции с таковыми ячейками естественно невозможны.

Числа в виде текста в ячейке

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

Если ваша таблица будет презентоваться через LCD проектор

Выбирайте очень контрастные композиции цвета и фона. Отлично смотрится на проекторе тёмный фон и светлые буковкы. Самое ужасное воспоминание производит красноватый на чёрном и напротив. Это сочетание очень неконтрастно смотрится на проекторе — опасайтесь его.

Страничный режим листа в Excel

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

Интересно почитать: Анализ чувствительности в excel пример таблица данных

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

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

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

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

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

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

Код на VBA

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

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

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

Exceltip

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

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

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

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

связи Excel

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

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

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

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

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

До этого чем создавать связи меж таблицами

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

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

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

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

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

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

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

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

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

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

Интересно почитать: Excel модуль числа

Для вас также могут быть увлекательны последующие статьи

Как сопоставить два столбца в Excel — способы сопоставления данных Excel
Формулы таблиц Excel
Функция СЖПРОБЕЛЫ в Excel с примерами использования
Четыре метода использования ВПР с несколькими критериями
Что если показать сокрытые строчки в Excel не работает
Седьмой урок обучающего курса — Базы Excel — Управление несколькими рабочими листами
5-ый урок курса по основам Excel — Печать в программке
6-ой урок онлайн курса по основам Excel — Управление рабочим листом
4-ый урок курса по основам Excel — Изменение ячеек
3-ий урок курса по основам Excel — Форматирование рабочих листов

7 объяснений

Спасибо! весьма нужный материал!

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

Как вставлять, перемещать либо удалять разрывы страничек в листе Excel 2021

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

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

Ниже приведены шаги, которые нужно сделать, когда вы желаете вставлять, перемещать либо удалять разрывы страничек в Лист Microsoft Excel.

Вставьте разрыв странички в Excel

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

Изберите столбец либо строчку, которые вы желаете вставить разрыв странички.

Перейдите на вкладку Макет странички , а потом нажмите Разрывы , отысканные в разделе Настройка странички . В конце концов, нажмите Вставить разрыв странички .

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

Ниже приведены шаги, которые следует выполнить, если вы желаете сделать вертикальный разрыв странички:

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

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

Если вы желаете создать горизонтальный разрыв странички, вот что вы делаете:

1] Расположите указатель ячейки в столбце A либо строке справа от строчки, в которой вы желаете вставить разрыв странички.

2] Откройте меню Excel и изберите «Вставить разрыв странички». Вы увидите горизонтальную линию на листе, которая показывает, где будет разбита страничка.

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

Прочтите : Как настроить панель резвого доступа в Excel, чтоб она работала вам.

Переместить разрыв странички в Excel

1] Перейдите на вкладку Файл , а потом Характеристики .

2]. Посреди вкладок с левой стороны нажмите «Добавочно» и установите флаг « » «Включить наполнение». дескриптор и перетаскивание ячейки.

3] Откройте рабочий лист, который вы желаете поменять.

4] Нажмите Просмотр , а потом Подготовительный просмотр странички .

5] Чтоб переместить разрыв странички, просто перетащите его в новое пространство.

Удаление разрыва странички в Excel

1] На вкладке Вид нажмите Page Break Подготовительный просмотр .

2] Изберите строчку либо столбец разрыва странички, который вы собираетесь удалить.

3] Перейдите на вкладку Макет странички и нажмите «Перерывы». Изберите Удалить разрыв странички . Он удалит предшествующий разрыв странички.

Надеюсь, что это поможет!

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

Microsoft дозволяет юзерам создавать, соединять воединыжды , удалите, добавьте псевдоним электрической почты Outlook и используйте те же опции Inbox & account для различных псевдонимов.

Как поправить недостающие полосы сетки в листе Excel

Ах так поправить недостающие полосы сетки на листе Excel. Попытайтесь один из этих 5 способов.

Как удалить разрывы страничек в Гугл Docs

Разрывы страничек могут быть раздражающими. Используйте надстройку Page Sizer для удаления разрывов страничек и сотворения непрерывных страничек в Документах Гугл.