Check-moscow.ru

Финансы и учет
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как выделить ячейки с внешними ссылками в Excel?

Внешние ссылки в Excel

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

Если наша формула выглядит как приведенная ниже, это внешняя ссылка.

Внешние ссылки 1

‘C: Users Admin_2.Dell-PC Рабочий стол : Это путь к этому листу на компьютере.

X LSX: Это название книги в пути.

Лист Vlookup: Это название рабочего листа в книге.

$ C $ 1: $ D $ 25: Вот диапазон на этом листе.

Типы внешних ссылок в Excel

  • Ссылки на одном листе.
  • Ссылки с разных листов, но из одной книги.
  • Ссылки из другой книги.

# 1- Ссылки на одном листе

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

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

Внешние ссылки - Типы 1

Хорошо, это простая ссылка на один лист.

# 2 — Ссылки с разных листов, но в одной книге

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

Например, в книге есть два листа, и в данный момент я нахожусь на листе 1, и даю ссылку на лист 2.

Внешние ссылки - Типы 2

# 3 — Ссылки из другой книги

Этот тип ссылок называется внешней ссылкой. Она взята совсем из другой книги.

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

Читайте так же:
Как заключить все ячейки в кавычки или вставить кавычки вокруг текста в Excel?

Внешние ссылки - Типы 3

Как найти, отредактировать и удалить внешние ссылки в Excel?

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

Внешние ссылки 2

Позвольте мне объяснить методы поиска внешних ссылок в Excel.

Метод №1: Использование метода поиска и замены с символом оператора

Внешние ссылки должны быть задокументированы с указанием пути или URL-адреса ссылающейся книги. Каждая ссылка включает символ оператора «[«[»

Шаг 1: Выберите лист, затем нажмите Ctrl+F (комбинация клавиш для поиска внешних ссылок).

Внешние ссылки 3 (Найти и заменить)

На втором шаге введите символ [и нажмите кнопку Найти все].

Внешние ссылки 3 (Найти и заменить) 1

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

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

Метод № 2: Использование метода поиска и замены с расширением файла

Среди внешних ссылочных ячеек можно найти название книги и тип книги.

Обычно файлы сохраняются с расширением .xlsx, .xls, .xlsm и .xlb.

Нажмите Ctrl + F (сочетание клавиш для поиска внешних ссылок) после выбора листа.

Найти и заменить метод 1

Введите .xlsx и нажмите кнопку «Найти все».

Найти и заменить метод 2

Ячейки с внешними ссылками будут показаны здесь.

Метод № 3: Использование опции редактирования ссылки в Excel

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

Читайте так же:
Как выделить строки на основе раскрывающегося списка в Excel?

На вкладке «Данные» в Excel вы найдете опцию «Редактировать ссылку».

метод

Шаг 1: Выделите ячейки, которые вы хотите отредактировать, приостановить или удалить ссылки на ячейки.

Затем нажмите кнопку Редактировать ссылки в Excel. Здесь доступно несколько вариантов.

способ 2

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

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

Разрыв связи

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

Изменить ссылки

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

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

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

Нажмите CTRL+A, чтобы выбрать все звонки.

Нажмите на кнопку Прервать.

Разбор функции ДВССЫЛ (INDIRECT) на примерах

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

Простой пример на ДВССЫЛ

Аргумент функции, который является ссылкой на A1, заключен в кавычки, так что по сути это текст.

» Ну ОК», — скажете вы. «И что тут полезного? «.

Читайте так же:
Как выделить активную ячейку или выделение в Excel?

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

Пример 1. Транспонирование

К горизонтальному (транспонирование). Использование DVSSYL так же просто, как использование специальной вставки или функции TRANSPOSE в формуле массива :

Транспонирование столбца в строку

Логика проста: чтобы получить адрес следующей ячейки, сложите вместе букву «А» и номер столбца текущей ячейки, который вы получаете из функции COLUMN.

Лучше выполнить обратную процедуру несколько иначе. Удобнее использовать режим ссылок R1C1 вместо классического «морского боя», потому что нам придется создавать ссылки на ячейки B2, C2, D2 и т.д. Наши ячейки будут отличаться только номером столбца, например, B2=R1C 2, C2=R1C 3, D2=R1C 4 и т.д.

В результате необходим второй необязательный аргумент функции DVSSYL. Если он равен FALSE, мы можем установить опорный адрес в режиме R1C1. Таким образом, мы можем легко транспонировать горизонтальный диапазон обратно в вертикальный:

ДВССЫЛ в режиме R1C1

Пример 2. Суммирование по интервалу

Одним из способов суммирования окна (диапазона) заданного размера на листе является использование функции OFFSET. В дополнение к DVSSYL, DVSSYL может решить аналогичную задачу. Если нам нужно суммировать данные только за определенный диапазон-период, мы можем склеить их из отдельных кусочков, превратить в ссылку, а затем использовать ее внутри функции SUM:

Сумма по интервалу

Пример 3. Выпадающий список по умной таблице

Иногда Microsoft Excel не воспринимает причудливые названия таблиц и столбцов как допустимые ссылки. Например, когда вы пытаетесь создать выпадающий список (вкладка Data — Validate data) на основе столбца Employees из таблицы People smart, вы получаете ошибку:

Ошибка при создании вып.списка

Если ссылка обернута в нашу функцию DVSSYL, Excel принимает ее, и наш выпадающий список динамически обновляется при добавлении новых сотрудников в конец умной таблицы:

Читайте так же:
Как выровнять знак доллара слева в Excel?

ДВССЫЛ для создания динамического выпадающего списка

Пример 4. Несбиваемые ссылки

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

Несбиваемые ссылки

Если скопировать и ввести обычные ссылки (=B2 в первой зеленой ячейке), то при удалении Даши мы получим ошибку #REF! (#REF!). Функция DVSSYL не создает таких проблем при создании ссылок.

Пример 5. Сбор данных с нескольких листов

Предположим, что у нас есть 5 листов с одним и тем же типом отчета от разных сотрудников (Михаил, Елена, Иван, Сергей, Дмитрий):

Отчеты для сборки

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

Вы можете собрать данные со всех листов (не сложенных вместе, а уложенных один под другим) с помощью одной единственной формулы:

Сборка данных функцией ДВССЫЛ

Как видите, идея та же: вы вставляете ссылку в нужную ячейку данной электронной таблицы, и DVSSYL преобразует ее в «живую». Для удобства я добавил буквы столбцов (B,C,D) над таблицей и номера строк, которые нужно взять с каждого листа, справа.

Подводные камни

При использовании DWSSI (INDIRECT) следует учитывать его слабые стороны:

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

Добавлять новые ссылки несложно, но когда количество ссылок становится слишком большим, их удаление вручную отнимает много времени. Чтобы решить эту проблему одним махом, можно воспользоваться специальным макросом. Его можно найти в аддоне VBA-Excel. Затем нужно активировать его и перейти на вкладку. Там будет раздел «Связи», где нужно нажать на кнопку «Разорвать все связи».

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

Код на VBA

Если нет возможности активировать это дополнение, вы можете создать макрос самостоятельно. Для этого откройте редактор Visual Basic, нажав Alt + F11, и введите следующие строки в поле ввода кода.

Читайте так же:
Как выделить строку, если ячейка содержит текст / значение / пробел в Excel?

Select Case MsgBox(«Все ссылки на другие книги будут удалены из этого файла, а формулы, ссылающиеся на другие книги будут заменены на значения.» & vbCrLf & «Вы уверены, что хотите продолжить?», 36, «Разорвать связь?»)

Если он не пустой (WbLinks), то

For i = 1 To UBound(WbLinks)

ActiveWorkbook.BreakLink Name:=WbLinks(i), Type:=xlLinkTypeExcelLinks

MsgBox «В данном файле отсутствуют ссылки на другие книги.», 64, «Связи с другими книгами»

Создание связи с книгой

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

Введите значение = (знак равенства).

Чтобы использовать функцию, например SUMM, введите ее имя со скобками. Например, =SUM(.

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

Обычные расширения файлов: .xlsx, .xls, .xlsm, .xlb.

Excel вернуться к оригиналу и отобразить значения из оригинала.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector