Как сделать перекрестные ссылки в excel

Обновлено: 04.07.2024

Часто в тексте документа необходимо отметить, где находится объект связанный с конкретным текстом. Например, «Схему подсоединения водопроводного крана смотри на рисунке 10». То есть нам необходимо сделать ссылку на рисунок. Если в документе 5 рисунков, то можно и вручную подсчитать. А если рисунков 25? Ещё хуже, если собираем один большой документ из нескольких тоже немаленьких документов. Вот тут на помощь придёт Перекрестная ссылка.

Понять и запомнить!

Перекрестная ссылка — это ссылка на элемент, который находится в другой части документа, например, «См. Рис 1.» или «См. пункт 3.1».

Можно создать перекрестные ссылки на следующие элементы:

Абзац
Заголовок
Закладка (познакомимся с понятием Закладка позже)
Сноска (познакомимся с понятием Сноска позже)
Концевая сноска (познакомимся с понятием Концевая сноска позже)
Названия объектов

По окончании урока вы сможете:

Задать перекрёстную ссылку на Название таблицы
Задать перекрёстную ссылку на Название рисунка
Обновить поле «Перекрёстная ссылка»

Если вы правильно сделали документ по итогам Урока 30, то откройте этот документ. Но можете воспользоваться этой ссылкой: Скачайте документ тут. Откройте документ. Поскольку работы со стилями на этом уроке не будет, то я закрыла область задач Стили.

1. Перекрестная ссылка на Название таблицы

Вы обратили внимание, что по тексту документа встречаются выражения «(см. )»? Я подготовила место для перекрестной ссылки.

Шаг 1. Поставим курсор в месте предполагаемой перекрестной ссылки:

Шаг 2. Открываем диалоговое окно «Перекрёстные ссылки» (Лента Ссылки →группа команд Называния → кнопка Добавить перекрестную ссылку):

На всякий случай я покажу, как выглядит эта кнопка в полноэкранном режиме:

Появляется диалоговое окно:

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

На уроке 30 я рассказала вам, что такое Поле. Перекрестная ссылка – это тоже Поле. Например, текст «см. рис. 15» является перекрёстной ссылкой на название рисунка 15 , т.е. щелчок ЛМ по перекрестной ссылке отправляет нас непосредственно к названию рисунка. Если название рисунка 15 меняет номер по каким-то причинам, то перекрестная ссылка на этот рисунок тоже меняет номер. Причем, не важно в каком месте документа находится данный рисунок.

Понять и запомнить!

Перекрестные ссылки используются для навигации по документу.

Шаг 3. Устанавливаем в диалоговом окне Перекрёстная ссылка следующие значения:

Тип ссылки – таблица
Вставить ссылку на – «Постоянная часть и номер» (зачем нам всё название целиком?), а что такое Постоянная часть и что такое номер см. Урок 29
Вставить как гиперссылку – оставляем
Для какого названия – выбираем Таблица 1. Виды нормативных актов (список всех названий таблиц программа составляет автоматически на основе имеющихся в документе названий таблиц, если мы, разумеется вставляли название при помощи диалогового окна «Вставить название», см. Урок 29)

Шаг 4. После установок нужных нам значений нажимаем кнопку «Вставить»:

Кнопка Отмена превратилась в копку Закрыть.

Шаг 5. Нажимаем кнопку Закрыть

Итак, в выбранном месте появилась постоянная часть и номер выбранного названия таблицы:

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

2. Перекрёстная ссылка на Название рисунка

Шаг 1. Поставим курсор в месте предполагаемой перекрёстной ссылки:

Шаг 2. Вызываем диалоговое окно «Перекрестные ссылки» (Лента Ссылки →группа команд Называния → кнопка Добавить перекрестную ссылку:

Появляется диалоговое окно «Перекрестные ссылки».

Шаг 3. Устанавливаем в диалоговом окне «Перекрестная ссылка» следующие значения:

Шаг 4. После установок нужных нам значений нажимаем кнопку «Вставить»

Шаг 5. Нажимаем кнопку Закрыть

И вот в выбранном месте появилась постоянная часть и номер выбранного названия рисунка:

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

Понять и запомнить!

Перекрёстная ссылка делается на название объекта, а не на сам объект

Я старалась по всему тексту неустанно повторять: Название таблицы, Название рисунка. Если вы удалите рисунок, а название объекта оставите, то ссылка будет работать как ни в чём не бывало.

А теперь самое время посмотреть, как будет работать перекрёстная ссылка.

3. Обновление поля «Перекрестная ссылка»

Поставим сразу сложную задачу. При работе с документом мы выяснили, то глава 5 на самом деле должна быть главой 2. Вспоминаем Урок 28, где мы познакомились с навигацией по заголовкам. Перейдите в начало документа (нажмите одновременно клавиши на клавиатуре Ctrl+Home)

Шаг 1. Отрываем рабочую область Навигация. Лента Вид → группа команд Показ → флажковая кнопка Область навигации:

Получаем такую картину:

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

Шаг 2. В любом месте области задач Навигация щёлкаем ПМ и из контекстного меню выбираем команду Свернуть все:

Получаем такую картину:

Сразу работать стало удобнее.

Шаг 3. Перемещаем в области Навигация Главу 5 на второе место в списке (нажимаем ЛМ на название Главы 5 и, не отпуская левую клавишу мышки, перетаскиваем Главу 5 на второе место после главы 1):

Вы обратили внимание, что, как только мы нажали ЛМ по главе 5 в области Навигация, то курсор сразу перескочил на главу 5?

Но вместе с главой 5 мы перетащили рисунок, который сохранил свой номер.

Шаг 4. Выделяем весь документ одновременным нажатием клавиш с клавиатуры Ctrl+A и нажимаем функциональную кнопку F9, в результате чего появляется уже знакомое окно:

Отмечаем радиоточку «обновить целиком»
Нажимаем ОК

Не пугайтесь, если окно «Обновление списка иллюстраций» выскочит во второй раз. Ведь у нас два списка иллюстраций: перечень рисунков и перечень таблиц.

И название рисунка поменяло свою нумерацию, и перекрёстная ссылка «отработала» это изменение. Можете пройти по всему документу и убедиться, что нумерация рисунков и таблиц обновилась, также обновились перечни рисунков и таблиц.

Теперь вы сможете:

Задать перекрёстную ссылку на Название таблицы
Задать перекрёстную ссылку на Название рисунка
Обновить поле «Перекрёстная ссылка»

Добавляйте названия объектов, меняйте главы местами, объединяйте документы в единый документ – вам теперь не страшно! Функциональная кнопка F9 вас никогда не подведёт!

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

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

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

Перекрестная ссылка на отдельные ячейки в Excel

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

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

Если вы хотите сослаться на ячейку из другого листа, вам нужно поместить имя листа перед ячейкой, а затем восклицательный знак. Правильный формат для этого = Лист2! C7,

Формула = Лист2! C7 * Лист2! C8 умножит данные из ячейки С7 и С8 в листе с именем Лист2.

Вы также можете ссылаться на ячейки из другого файла электронной таблицы Excel. Вам нужно будет использовать квадратные скобки для идентификации файла, назовите лист, из которого он сделан, а затем используйте ссылку на ячейку. Например, знак равно[Spreadsheet.xlsx]Лист2! C7,

С помощью знак равно[Spreadsheet.xlsx]Лист2! C7*[Spreadsheet.xlsx]Лист2! C8вы сможете умножить данные из ячейки С7 и С8 в листе Sheet2 из файла с именем Spreadsheet.xlsx,

Ссылки на диапазоны ячеек в Excel

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

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

Чтобы сослаться на диапазон ячеек из другого листа в том же файле, назовите лист перед диапазоном ячеек. Например, = SUM (! Sheet2 C7: C10),

Чтобы сослаться на диапазон ячеек из другого файла, используйте квадратные скобки для имени файла, затем ссылку на рабочий лист и, наконец, диапазон ячеек. Например, = СУММ ([Spreadsheet.xlsx]! Sheet2 A7: A10),

Ссылки на именованные ячейки в Microsoft Excel

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

Затем вы можете использовать это определенное имя ячейки в качестве ссылки в других ячейках. Например, именованная ячейка с именем namedcell может упоминаться в другой ячейке как = Namedcell.

Эту именованную ссылку на ячейку можно использовать как есть на всем листе. Например, если это было на листе с именем Sheet2, вы все равно используете = namedcell ссылка, чтобы сослаться на это.

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

Typing = Spreadsheet.xlsx! Namedcell будет относиться к ячейке с именем namedcell сохраняется во внешнем файле электронной таблицы Excel с именем Spreadsheet.xlsx вместо.

Организация данных в Microsoft Excel

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

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

Как сослаться на другой лист в том же файле Excel

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

Таким образом, ссылка на ячейку B3 относится к ячейке на пересечении столбца B и строки 3.

При ссылке на ячейки на других листах этой ссылке на ячейку предшествует имя другого листа. Например, ниже приведена ссылка на ячейку B3 в названии листа «Январь».

Восклицательный знак (!) Отделяет имя листа от адреса ячейки.

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

Чтобы создать эти ссылки, вы можете ввести их непосредственно в ячейку. Однако проще и надежнее позволить Excel написать для вас ссылку.

Введите знак равенства (=) в ячейку, щелкните вкладку «Лист», а затем щелкните ячейку, на которую вы хотите сделать перекрестную ссылку.

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

Нажмите Enter, чтобы завершить формулу.

Как сослаться на другой файл Excel

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

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

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

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

В этом примере вы можете увидеть знаки доллара ($) среди адресов ячейки. Это абсолютная ссылка на ячейку ( Узнайте больше об абсолютных ссылках на ячейки ).

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

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

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

Хранение данных в одной книге, если это возможно, более надежно.

Как сделать перекрестную ссылку на диапазон ячеек в функции

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

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

В следующем примере функция SUM суммирует значения из диапазона B2: B6 на листе с именем Sales.

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

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

Более того, это имя уникально для всех таблиц в этом файле Excel.

Например, мы можем назвать ячейку «ChicagoTotal», и тогда перекрестная ссылка будет выглядеть так:

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

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

Щелкните в поле «Имя» в верхнем левом углу, введите имя, которое хотите назначить, и нажмите клавишу «Ввод».

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

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

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

Как отформатировать данные в виде таблицы

При работе с обширным списком связанных данных использование функции «Формат как таблицы» в Excel может упростить обращение к данным в ней.

Возьмите следующую простую таблицу.

Это может быть отформатировано как таблица.

Нажмите на ячейку в списке, перейдите на вкладку «Главная», нажмите кнопку «Формат таблицы» и выберите стиль.

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

Затем вы можете присвоить значимое имя вашей таблице на вкладке «Дизайн».

Затем, если бы нам нужно было суммировать продажи в Чикаго, мы могли бы обратиться к таблице по ее имени (с любого листа), за которой следовала бы квадратная скобка ([), чтобы увидеть список столбцов таблицы.

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

Вы можете увидеть, как таблицы могут упростить ссылки на данные для функций агрегирования, таких как SUM и AVERAGE, по сравнению со стандартными ссылками на листы.

Эта таблица мала для демонстрации. Чем больше таблица и чем больше листов в книге, тем больше преимуществ вы увидите.

Как использовать функцию VLOOKUP для динамических ссылок

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

Однако что, если ячейка, на которую вы ссылаетесь, может измениться при вставке новых строк или кто-то отсортирует список?

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

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

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

Ниже приведен пример списка сотрудников.

Функция VLOOKUP просматривает первый столбец таблицы и затем возвращает информацию из указанного столбца справа.

Следующая функция VLOOKUP ищет идентификатор сотрудника, введенный в ячейку A2 в приведенном выше списке, и возвращает дату, объединенную из столбца 4 (четвертый столбец таблицы).

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

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

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

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

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

Содержание

Работа в MS Excel многим кажется скучной и сложной, но ведь это далеко не так. Excel может быть удобным, если знать некоторые секреты, которые мы Вам расскажем. Скорее всего, многие из Вас найдут что-то знакомое, но, без сомнений, материал пригодится многим. Итак:

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

В верхнем меню выбираем: Вид → Панель инструментов → Настройка. Откроется диалоговое окно Настройка, перейдите на вкладку Команды. Теперь, в списке Категории выберите Сервис. Прокрутите вниз список Команды, Вы увидите значок калькулятора (рядом будет текстовая надпись "Другая"). Удерживая левой кнопкой мыши, перетащите этот значок из списка на панель инструментов. Теперь можно просто кликать на этот значок, чтобы запустить калькулятор.

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

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

Как часто нам нужно задать определенные требования к количеству символов в тексте или строке, например, при подаче объявления или составлении текста. Для этого делаем следующее: в ячейке, в которой Вам нужно выводить количество символов ставим знак "=" и задаем формулу "ДЛСТР", потом в скобочках пишем саму ячейку, в которой считаем символы, выглядит это примерно так: =ДЛСТР(ячейка) жмем Enter.

Если у Вас документ Excel довольно длинный, и для его просмотра нужно пользоваться прокруткой, то очень удобно будет закрепить верхние строчки ("шапку таблицы"), и про прокрутке документа вниз, они будут в зоне видимости – не нужно будет возвращаться к верху документа и смотреть, что же обозначает данная ячейка. Делается прикрепление просто: помещаем курсор в под ту строку, которую хотим закрепить, в панели инструментов выбираем Окно → Закрепить области. Чтобы отменить закрепление, нужно нажать Окно → Снять закрепление областей.

Если в Вашем файле 2 и более листов, и Вам нужно постоянное переключение между ними, можно переключать их не мышкой, а сочетанием клавиш Ctrl + PageDown для перехода на следующий лист, и Ctrl + PageUp для возврата на предыдущий.

Иногда нам нужно в файле не удаляя, скрыть какие-то данные – вычисления, текст и т.п. нужно скрыть данные в ячейке. Для этого выделим то, что хотим скрыть – это может быть как одна ячейка, так и несколько строк и столбцов. Кликаем правой кнопкой мыши – в открывшемся меню выбираем Формат ячеек, вкладка Число, выбираем Все форматы, справа а поле Тип ставим три знака точки с запятой ";;;". Жмем ОК. Данные будут видны только в строке ввода значений, и то, если курсов будет стоять на скрытой ячейке. Чтобы сделать данные видимыми, нужно проделать заново всю операцию от с начала выделения до ввода точек с запятыми, только теперь нужно их удалить из поля Тип.

Стандартные функции Excel не позволяют ставить абзацы в одной ячейке. Как только мы жмем Enter, курсор переходит на другую строку. Выход есть – каждый раз, когда нужно будет начать предложение в ячейке с новой строчки – жмите Alt + Enter.

Чтобы не регулировать вручную высоту и ширину ячеек, можно воспользоваться нехитрым способом: выделяем нужную область, на панели меню последовательно нажимаем: Формат → Строка → Автоподбор высоты. Так мы выровняли высоту. Аналогично делаем для выравнивания по ширине – выделяем рабочую область, на панели меню Формат → Столбец → Автоподбор ширины. По-моему, достаточно просто.

В больших документах иногда приходится работать с данными из нескольких листов сразу. Предположим, что у нас 2 листа: Лист А и лист Б. На листе Б должна присутствовать часть данных из листа А. Для этого ставим курсор в ячейку на листе Б, куда поместим данные листа А, ставим знак равно "=", переходим на лист А, ставим курсор в ту ячейку, данные которой будут использованы на листе Б, жмем Enter. Данные должны появиться в нужной ячейке на листе Б.

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

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

Для того, чтобы удалить гиперссылку, нужно навести курсор на ячейку, в которой находится гиперссылка, кликнуть по ней правой кнопкой мыши, и в открывшемся меню выбрать пункт Удалить гиперссылку

Читайте также: