Неверная ссылка excel

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

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

Гиперссылки на лист в Excel Online
Здравствуйте! Сталкивался ли кто-нибудь с гиперссылками на лист или ячейку Excel Online.

Скопировать гиперссылки в новый столбец
Привет! Есть порядка 700 ячеек в столбце. Каждая ячейка содержит гиперссылку. Необходимо каким-то.

Гиперссылки на другой документ. не получается
Добрый день! Уважаемые, подскажите как мне быть? дело вот в чем: Есть документ эксель «Журнал.

Невозможно разорвать связи с другой книгой

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

Что такое связи в Excel и как их создать
Иногда при работе с различными отчетами приходится создавать связи с другими книгами(отчетами). Чаще всего это используется в функциях вроде ВПР (VLOOKUP) для получения данных по критерию из таблицы, расположенной в другой книге. Так же это может быть и простая ссылка на ячейки другой книги. В итоге ссылки в таких ячейках выглядят следующим образом:
=ВПР( A2 ;'[Продажи 2018.xlsx]Отчет’!$A:$F;4;0)
или
='[Продажи 2018.xlsx]Отчет’!$A1

• [Продажи 2018.xlsx] — обозначает книгу, в которой итоговое значение. Такие книги так же называют источниками
• Отчет — имя листа в этой книге
• $A:$F и $A1 — непосредственно ячейка или диапазон со значениями

Если закрыть книгу, на которую была создана такая ссылка, то ссылка сразу изменяется и принимает более «длинный» вид:
=ВПР( A2 ;’C:UsersДмитрийDesktop[Продажи 2018.xlsx]Отчет’!$A:$F;4;0)
=’C:UsersДмитрийDesktop[Продажи 2018.xlsx]Отчет’!$A1
Предположу, что большинство такими ссылками не удивишь. Такие ссылки так же принято называть связыванием книг. Поэтому как только создается такая ссылка на вкладке Данные в группе Запросы и подключения активируется кнопка Изменить связи. Там же, как несложно догадаться, их можно изменить. В большинстве случаев ни использование связей, ни их изменение не доставляет особых проблем. Даже если в книге источники были изменены значения ячеек, то при открытии книги со связью эти изменения будут так же автоматом обновлены. Но если книгу-источник переместили или переименовали — при следующем открытии книги со ссылками на неё Excel покажет сообщение о недоступных связях в книге и запрос на обновление этих ссылок:

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

Так же изменение связей доступно непосредственно из вкладки Данные

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

Выделить нужные связи и нажать Разорвать связь. При этом все ячейки с формулами, содержащими связи, будут преобразованы в значения вычисленные этой формулой при последнем обновлении. Данное действие нельзя будет отменить — только закрытием книги без сохранения.
Так же связи внутри формул разрываются, если формулы просто заменить значениями -Копируем нужные ячейки -Правая кнопка мыши -Специальная вставка -Значения. Формулы в ячейках будут заменены результатами их вычислений, а все связи будут удалены.
Более подробно про замену формул значениями можно узнать из статьи: Как удалить в ячейке формулу, оставив значения?

Что делать, если связи не разрываются

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

• проверьте нет ли каких-либо связей в именованных диапазонах:
  нажмите сочетание клавиш Ctrl + F3 или перейдите на вкладку Формулы (Formulas) —Диспетчер имен (Name Manager)
  Читать подробнее про именованные диапазоны
  Если в каком-либо имени есть ссылка с полным путем к какой-то книге(вроде такого ‘[Продажи 2018.xlsx]Отчет’!$A1 ), то такое имя надо либо изменить, либо удалить. Кстати, некоторые имена в итоге могут выдавать ошибку #ССЫЛКА! (#REF!) . К ним тоже стоит присмотреться.
  Настоятельно рекомендую перед удалением имен создать резервную копию файла, т.к. неверное удаление таких имен может повлечь неправильную работу файла даже в случае, если сами ссылки возвращали в итоге ошибочное значение.
• если удаление лишних имен не дает эффекта — проверьте условное форматирование:
  вкладка Главная (Home) —Условное форматирование (Conditional formatting) —Управление правилами (Manage Rules) . В выпадающем списке проверить каждый лист и условия в нем:
  
  Может случиться так, что условие было создано с использованием ссылки на другие книги. Как правило Excel запрещает это делать, но если ссылка будет внутри какого-то именованного диапазона — то диапазон такой можно будет применить в УФ, но после его удаления в самом УФ это имя все равно остается и генерирует ссылку на файл-источник. Такие условия можно удалять без сомнений — они все равно уже не выполняются как положено и лишь создают «пустую» связь.
• Так же не помешает проверить наличие лишних ссылок и среди проверки данных(Что такое проверка данных). Как правило связи могут быть в проверке данных с типом Список. Но как их отыскать, если проверка данных распространена на множество ячеек? Проверять каждую? Это очень долго. Поэтому я предлагаю коротенький код, который отыщет все такие ссылки быстрее и сэкономит время):

Option Explicit ‘————————————————————————————— ‘ Author : The_Prist(Щербаков Дмитрий) ‘ Профессиональная разработка приложений для MS Office любой сложности ‘ Проведение тренингов по MS Excel ‘ https://www.excel-vba.ru ‘ info@excel-vba.ru ‘ WebMoney — R298726502453; Яндекс.Деньги — 41001332272872 ‘ Purpose: ‘————————————————————————————— Sub FindErrLink() ‘надо посмотреть в Данные -Изменить связи ссылку на файл-иточник ‘и записать сюда ключевые слова в нижнем регистре(часть имени файла) ‘звездочка просто заменяет любое кол-во символов, чтобы не париться с точным названием Const sToFndLink$ = «*продажи 2018*» Dim rr As Range, rc As Range, rres As Range, s$ ‘определяем все ячейки с проверкой данных On Error Resume Next Set rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllVal On Error Resume Next s = rc.Val если надо выделить еще и цветом End If End Sub

Чтобы правильно использовать приведенный код, необходимо скопировать текст кода выше, перейти в редактор VBA( Alt + F11 ) -создать стандартный модуль(Insert —Module) и в него вставить скопированный текст. После чего вызвать макросы( Alt + F8 ), выбрать FindErrLink и нажать выполнить.
Есть пара нюансов:
1. Прежде чем искать ненужную связь необходимо определить её ссылку: Данные -Изменить связи. Запомнить имя файла и записать в этой строке внутри кавычек:
Const sToFndLink$ = «*продажи 2018*»
Имя файла можно записать не полностью, все пробелы и другие символы можно заменить звездочкой дабы не ошибиться. Текст внутри кавычек должен быть в нижнем регистре. Например, на картинках выше есть связь с файлом «Продажи 2018.xlsx», но я внутри кода записал «*продажи 2018*» — будет найдена любая связь, в имени которой есть «продажи 2018».
2. Код ищет проверки данных только на активном листе
3. Код только выделяет все найденные ячейки(обычное выделение), он ничего сам не удаляет.
4. Если надо подсветить ячейки цветом — достаточно убрать апостроф(‘) перед строкой
rres.Interior.Color = vbRed ‘если надо выделить еще и цветом

Как правило после описанных выше действий лишних связей остаться не должно. Но если вдруг связи остались и найти Вы их никак не можете или по каким-то причинам разорвать связи не получается(например, лист со связью защищен)- можно пойти совершенно иным путем. Действует этот рецепт только для файлов новых форматов Excel 2007 и выше:
1. Обязательно делаем резервную копию файла, связи в котором никак не хотят разрываться
2. Открываем файл при помощи любого архиватора(WinRAR отлично справляется, но это может быть и другой, работающий с форматом ZIP)
3. В архиве перейти в папку xl -> externalLinks
4. Сколько связей содержится в файле, столько файлов вида externalLink1.xml и будет внутри. Файлы просто пронумерованы и никаких сведений о том, к какому конкретному файлу относится эта связь на поверхности нет. Чтобы узнать какой файл .xml к какой связи относится надо зайти в папку «_rels» и открыть там каждый из имеющихся файлов вида externalLink1.xml.rels. Там и будет содержаться имя файла-источника.
5. Если надо удалить только связь на конкретный файл — удаляем только те externalLink1.xml.rels и externalLink1.xml, которые относятся к нему. Если удалить надо все связи — удаляем все содержимое папки externalLinks
6. Закрываем архив
7. Открываем файл в Excel. Появится сообщение об ошибке вроде «Ошибка в части содержимого в Книге . «. Соглашаемся. Появится еще одно окно с перечислением ошибочного содержимого. Нажимаем закрыть.

После этого связи должны быть удалены.

Статья помогла? Поделись ссылкой с друзьями!

12 наиболее распространённых проблем с Excel и способы их решения

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

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

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

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

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

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

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

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

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

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

Данные одного типа искусственно располагаются в разных столбцах

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ещё больше полезной информации про Excel можно узнать на сайте Дениса.

Оценка статьи:

Загрузка...