No Image

Что такое внешняя ссылка в excel

СОДЕРЖАНИЕ
0 просмотров
10 марта 2020

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

Ссылка на другой лист

Допустим, на одном листе вы считаете свой доход. Получаете финальное число. А на другом листе — считаете расходы, и вам нужно, чтобы у вас перед глазами было число, которое покажет, сколько денег у вас осталось. Что нужно сделать? Правильно, из доходов вычесть расходы. Доходы — на листе 1, расходы — на листе 2. Что делаем?

В формулу на листе 2 вставляем не просто ссылку В2 (тогда Excel возьмет число из ячейки В2 листа 2), а ‘Лист1’!В2, то есть В2 листа 1. Обратите внимание: лист 1 отделен штрихами, а после него стоит разделитесь — !. Штрих вводится на английской раскладке с помощью клавиши Э. Диапазон ячеек указывается так: ‘Лист1’!А1:С4. Подробно мы рассмотрим этот пример в примерах.

Ссылка на другие книги

Вы можете сослаться не только на другой лист данной книги, но и вообще на другой файл. Вот так выглядит ссылка на ячейку А1 листа 3 файла Книга2: =[Книга2.xlsx]’Лист3’A1. Ссылка на книгу содержит имя книги и должна быть заключена в квадратные скобки []. В ссылке должно быть указано имя листа в книге. Если файл, на который вы ссылаетесь, не открыт в программе Excel, то вы должны указать его полный адрес, например: =СУММ(‘C:My Documents[Книга2.xlsx]Лист1’!A1;А1). Эта формула суммирует ячейку А1 открытого листа и ячейку А1 листа 1 файла Книга 2, который находится в папке My Documents на диске С:.

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

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

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

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

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

Выделите ячейки или ячейки, в которых вы хотите создать внешнюю ссылку.

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

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

Выделите ячейки, на которые должна указывать ссылка.

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

Читайте также:  Oklick gaming mouse 765g

Кроме того, на конечном листе измените формулу или измените ее.

Нажмите сочетание клавиш CTRL+SHIFT+ВВОД.

Примечание: Если у вас установлена текущая версия Office 365, вы можете просто ввести формулу в выходную ячейку, а затем нажать клавишу Ввод , чтобы подтвердить формулу как формулу динамического массива. В противном случае необходимо ввести формулу в качестве устаревшей формулы массива: введите формулу в выходную ячейку или верхнюю левую ячейку выходного диапазона, а затем нажмите клавиши CTRL + SHIFT + ВВОД , чтобы подтвердить его. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

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

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

Выделите ячейки или ячейки, в которых вы хотите создать внешнюю ссылку.

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

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

Нажмите клавишу F3, а затем выберите имя, на которое должна указывать ссылка.

Откройте конечную книгу и исходную книгу.

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

В диалоговом окне новое имя в поле имя введите имя диапазона.

В поле Формула удалите содержимое, а затем оставьте курсор в поле.

Если имя содержат формулу, введите формулу, а затем поместите курсор в то место, где должна находиться внешняя ссылка. Например, введите = сумм (), а затем наведите курсор на круглые скобки.

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

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

В диалоговом окне новое имя нажмите кнопку ОК.

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

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

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

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

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

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

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

Читайте также:  Как отвязать аккаунт гугл от телефона xiaomi

Формулы с внешними ссылками на другие книги отображаются двумя способами в зависимости от того, открыта или закрыта исходная книга (книга, которая предоставляет данные для формулы).

Когда источник открыт, внешняя ссылка включает имя книги в квадратных скобках ( []), за которым следует имя листа, восклицательный знак ( !) и ячейки, от которых зависит формула. Например, следующая формула добавляет ячейки C10: C25 из книги с именем Budget. xls.

= СУММ ([бюджетный. xlsx] годовой! C10: C25)

Если источник не открыт, внешняя ссылка включает полный путь.

= СУММ (‘ К:репортс [бюджетный. xlsx] годов ‘! C10: C25)

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

Формулы, которые связываются с определенными имя в другой книге, используют имя книги, а затем восклицательный знак (!) и имя. Например, в приведенной ниже формуле ячейки добавляются в диапазоне Sales из книги с именем Budget. xlsx.

= SUM (бюджетный. xlsx! Продаже

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

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

Get expert help now

Don’t have time to figure this out? Our expert partners at Excelchat can do it for you, 24/7.

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

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

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

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

  1. Откройте все файлы – свод и исходники
  2. Кликните по ячейке, в которую нужно вставить ссылку, и введите знак равно (как вы это делаете, когда создаете формулы).
  3. Выберите файл с исходным значением и кликните на этом значении. В строке формул появится ссылка на это значение.
  4. Если к этому значению нужно прибавить другие из остальных источников – допишите «+» и повторяйте п.3-4 до полного заполнения.
  5. Когда все ссылки на ячейки сделаны, нажмите Enter , будет просчитан сводный результат.

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

Когда Вы закроете исходные файлы, к ссылке слева добавится еще и адрес файла-исходника:

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

Читайте также:  Сетевая модель это модель

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

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

Управление внешними ссылками

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

  • Обновить – принудительное обновление всех ссылок
  • Изменить – указать новую книгу Excel, в которой располагаются подключенные данные
  • Открыть – открывает документ-источник
  • Разорвать связь – удаляет внешние ссылки из этой связи, заменяет их на значение
  • Состояние – обновляет статус доступности файлов с данными
  • Запрос на обновление связей – укажите программе, нужно ли обновлять данные при открытии файла

Если у Вас большой файл с несколькими листами, может понадобиться много времени на поиски внешних ссылок. Так как быстро найти внешние ссылки в документе Excel? Предложу рецепт. Все подобные ссылки имеют общий элемент – открывающая и закрывающая квадратные скобки, в которые записано имя файла-исходника. В других случаях такие скобки используются редко. Нажмите Ctrl+F для открытия окна поиска, в поле «Найти» введите квадратную скобку и нажмите «Найти все». В результатах поиска точно будут все внешние ссылки.

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

Этот инструмент упрощает сведение информации в одну таблицу данных, это удачный способ автоматизации процесса.

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

Давайте выполнять. Открываем все файлы-исходники и свод. Активируем «Свод». Ищем на ленте Данные – Работа с данными – Консолидация . Откроется окно подключения внешних данных.

Теперь пошагово выполняем:

  1. В поле «Функция» выбираем «Сумма» (или что-то другое, если нужно)
  2. В поле «Ссылка» указываем ссылку на данные в первом файле-источнике (вместе с шапкой). Лучше всего, указать столбцы полностью, например A:D . Тогда не нужно будет переопределять диапазон при добавлении новых данных.
  3. Нажимаем «Добавить», ссылка появляется в поле «Список диапазонов»
  4. Повторяйте п. 2-3, пока не добавите ссылки на все файлы-исходники

  1. Установите галочки «Подписи верхней строки» и «Значения левого столбца», чтобы Эксель сам подобрал заголовки шапки и категории в строках
  2. Установите галку «Создавать связи с исходными данными», тогда консолидация будет сделана с помощью ссылок на ячейки. Иначе – будут просто вставлены итоговые значения.
  3. Нажмите Ок для завершения настройки консолидации.

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

Если исходные таблицы изменились, можно переформировать свод. Для этого повторно откройте окно «Консолидация» и нажмите Ок.

Такой вот простой инструмент, решающий одну из самых противных задач в Эксель. Я предпочитаю применять именно его, ручная работа по внесению ссылок – не для меня. Рекомендую и Вам пользоваться. Хотя, более удачным решением будет применение сводной таблицы (когда это возможно), или создание собственной VBA-программы с детальными настройками сведения.

На этом оставлю Вас экспериментировать с Консолидацией, и приступаю к написанию поста о защите данных в Эксель. Читайте, эта информация сейчас, как никогда актуальна. До встречи!

Комментировать
0 просмотров
Комментариев нет, будьте первым кто его оставит

Это интересно
Adblock detector