Как в excel сделать ссылку на ячейку из другого листа

Ссылка на ячейку в другом листе Excel

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

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

Ссылка на лист в формуле Excel

Доходы за январь, февраль и март введите на трех отдельных листах. Потом на четвертом листе в ячейке B2 просуммируйте их.

Возникает вопрос: как сделать ссылку на другой лист в Excel? Для реализации данной задачи делаем следующее:

1. Заполните Лист1, Лист2 и Лист3 так как показано выше на рисунке.
2. Перейдите на Лист4, ячейка B2.
3. Поставьте знак «=» и перейдите на Лист1 чтобы там щелкнуть левой клавишей мышки по ячейке B2.
4. Поставьте знак «+» и повторите те же действия предыдущего пункта, но только на Лист2, а потом и Лист3.
5. Когда формула будет иметь следующий вид: =Лист1!B2+Лист2!B2+Лист3!B2, нажмите Enter. Результат должен получиться такой же, как на рисунке.



Как сделать ссылку на лист в Excel?

Ссылка на лист немного отличается от традиционной ссылки. Она состоит из 3-х элементов:

1. Имя листа.
2. Знак восклицания (служит как разделитель и помогает визуально определить, к какому листу принадлежит адрес ячейки).
3. Адрес на ячейку в этом же листе.

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

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

Ссылка на лист в другой книге имеет уже 5 элементов. Выглядит она следующим образом: =’C:\Docs\[Отчет.xlsx]Лист1′!B2.

Описание элементов ссылки на другую книгу Excel:

1. Путь к файлу книги (после знака = открывается апостроф).
2. Имя файла книги (имя файла взято в квадратные скобки).
3. Имя листа этой книги (после имени закрывается апостроф).
4. Знак восклицания.
5. Ссылка на ячейку или диапазон ячеек.

Данную ссылку следует читать так:

• книга расположена на диске C:\ в папке Docs;
• имя файла книги «Отчет» с расширением «.xlsx»;
• на «Лист1» в ячейке B2 находится значение на которое ссылается формула или функция.

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

Без функций и формул Excel был бы одной большой таблицей предназначенной для ручного заполнения данными. Благодаря функциям и формулам он является мощным вычислительным инструментом. А полученные результаты, динамически представляет в желаемом виде (если нужно даже в графическом).

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Как в excel сделать ссылку на ячейку из другого листа

Всем доброго времени суток. Столкнулся с такой проблемой что не могу сообразить как сделать ссылку, которая ссылается на другой лист, но при этом условие выполнялось текущего листа из которого ссылается сама формула. Пример прикладываю. Исходя из него объясню попонятнее. В ячейке «В2» листов 2-5, стоит условие «да, «нет». Так же в листах 2-5 в ячейке «А3» стоит ссылка на лист1 ячейка «А3». Так вот, на листах 2-3 в ячейке должна стоять формула «Лист1!А3», но при этом само условие, должно выполнятся из значения «В1» текущего листа. Понятно что макросом это не проблема, а вот самой формулой не получается.

Прикрепленные файлы

• Книга1.xlsx (11.83 КБ)

Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
02.06.2021 20:30:29
Объясняли, объясняли. Ни по тесту, ни из файла понятнее не получилось.
Пользователь
Сообщений: 23 Регистрация: 02.06.2021
03.06.2021 07:50:08

На листах 2-5 в ячейке «А3» должна стоять формула «=Лист1!A3», а в Лист1!A3 нужна такая формула которая завесила бы от условия листа. Исходя из примера в ячейке «В1» листов 2-5, стоят значения, «да,нет,нет,да», соответственно. Исходя из этого условия в ячейке «А3» листов 2-5, должно быть значение «получилось, не получилось, не получилось, получилось». Повторяю, при этом в ячейке «А3» должна стоять формула «=Лист1!A3», а в самом листе 1 в ячейке А3 нужна эта самая формула.

Пользователь
Сообщений: 11251 Регистрация: 01.01.1970
03.06.2021 08:00:47
бред какой то
без формул руками нарисуйте что вы ожидаете увидеть какой результат должен отразится

Цитата
Максим Бугриев написал: а в самом листе 1 в ячейке А3 нужна

Лень двигатель прогресса, доказано.
Пользователь
Сообщений: 23 Регистрация: 02.06.2021
03.06.2021 08:18:10

Еще подробнее,
На листе 2 в ячейке В1 стоит условие «да», значит в ячейке А3 должен получиться текст в виде «получилось», сама формула там должна стоять «=Лист1!A3».
На листе 3 в ячейке В1 стоит условие «нет», значит в ячейке А3 должен получиться текст в виде «не получилось», сама формула там должна стоять «=Лист1!A3».
На листе 4 в ячейке В1 стоит условие «нет», значит в ячейке А3 должен получиться текст в виде «не получилось», сама формула там должна стоять «=Лист1!A3».
На листе 5 в ячейке В1 стоит условие «да», значит в ячейке А3 должен получиться текст в виде «получилось», сама формула там должна стоять «=Лист1!A3».

Так вот на листе 1 в ячейке А3 нужно придумать формулу, которая смогла бы это сделать.
Как еще понятнее объяснить, не знаю.

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

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

Сейчас мы пошагово рассмотрим как это можно сделать. Допустим у нас есть ячейка с текстом “Смотрите на листе 2” и мы хотим, чтобы данный текст стал ссылкой, нажав на которую мы должны переместиться на Лист 2.

Для решения данной задачи необходимо выделить ячейку с этим текстом (B4) и далее либо нажав правой кнопкой мыши выбрать пункт меню “Гиперссылка”

или выбрать данный пункт, перейдя в раздел “Вставка”.

После этого откроется окно вставки гиперссылки. Необходимо перейти в раздел “Место в новом документе” и выбрать необходимый лист, на который требуется сделать ссылку. В нашем пример это Лист 2.

Нажимаем “Ок” и текст в нашем файле Excel превратится в ссылку.

Спасибо за внимание. Будем рады помочь.

Поделиться:
Одноклассники

• Предыдущая запись Цвет ячейки в Excel
• Следующая запись Как произвести математические действия с числом из буфера памяти в Excel

27 Комментариев
Любава says:
19.06.2019 в 1:24 пп

Подскажите пожалуйста , как сделать гиперсылку на все ячейки. Дано 2 листа в книге ЕХСЕЛ. В одном описание (содержание), на другом листе данные (лист1). С листа содержание, нужно сделать так чтобы переходить в лист1, это я понимаю как сделать. Но в листе 1, очень много ячеек. мне нужно сделать ггиперссылку ,допустим с ячейки A1 по ячейку 100. как это сделать? (ручками слишком долго) Может есть какая-то формула? Заранее спасибо за ответ

07.06.2021 в 6:03 дп

=ПРОСМОТР(Лист3!C3;<0:2,5:3,5:4,5>😉
т.е. на листе 1 внутри формулы указываете Лист3 (или где прописана формула). Далее копируете. Вставляете

Родион says:
09.11.2018 в 10:00 дп

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

Дмитрий says:
08.07.2018 в 8:03 дп

Добрый день есть файл эксель где допустим 1 лист с табличкой с ФИО работников, а остальные листы фамилии работников в которых их данные. Как сделать так чтобы гиперссылками сама находила нужный лист (с фамилией) и ссылалась на ячейку допустим А1. Напротив каждого работника.

02.10.2017 в 6:33 пп

Здравствуйте, я немного не по теме, но всё же, помогите, мне нужно высчитать данные из одной ячейки на листе 1 и из второй ячейки на листе 2. Итог должне высчитаться на листе #2

excel says:
02.10.2017 в 6:38 пп
Не понятно что нужно сделать.
Александр says:
24.09.2017 в 4:29 дп

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

excel says:
24.09.2017 в 12:20 пп

Думаю для начала нужно создать таблицу с адресами всех файлов.
Имя — адрес.
Потом уже с помощью поиска находите все подходящие ячейки и рядом подтягиваете ссылку с помощью ГИПЕРССЫЛКА()
=ГИПЕРССЫЛКА(«D:\FINANCE\otchet.xlsx»;A1)

Лилия says:
04.05.2017 в 1:35 пп

Здравствуйте. Подскажите, пожалуйста, как можно сделать в файле Excel ссылку на документ Word. Например, есть определенный список блюд в Excel, нажимаем на ячейку с номером 1 должен открыится рецепт по этому блюду.
Спасибо за помощь.

Dedicated servers says:
11.03.2017 в 8:55 пп

Создает ярлык или ссылку, которая позволяет открыть документ, расположенный на сетевом сервере, в интрасеть или в Интернете. Если щелкнуть ячейку, содержащую функцию ГИПЕРССЫЛКА, в приложении Microsoft Excel будет открыт файл, расположение которого задано аргументом «адрес».

Livado says:
10.03.2017 в 1:48 пп

Добрый день! Подскажите ПОЖАЛУЙСТА, почему в EXCEL 2007 не работает ссылка на другую книгу.
В OpenOffice это работает
[G:\Old-D\LAG\Standard\MASSY\MASSY.xls]Изоляция!$a$1

Dedicated servers says:
04.03.2017 в 1:20 дп

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

excel says:
04.03.2017 в 4:11 пп
Айжан says:
17.01.2017 в 6:13 дп

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

excel says:
17.01.2017 в 7:31 дп
Василий says:
29.06.2016 в 8:17 дп

Подскажите пожалуйста. В 1 листе у меня формула на данные из листа 2.
Как настроить Excel, чтобы можно было перейти из листа 1 (двойным кликом мыши) в лист 2?

excel says:
29.06.2016 в 2:39 пп
В самой статье про это и написано как это сделать
Сания says:
15.06.2016 в 4:58 дп

Добрый день! спасибо за доступное объяснение.
Но хотела поинтересоваться вот в чем. Я постоянно пользуюсь гиперссылками, т.к. документов куча, а нужно держать их в порядке. Первый год пользовалась нормально, а потом почему то все гиперссылки перестали открываться. И приходилось каждый адрес обновлять по мере надобности. У меня эти реестры собрались за 3 года, каждый раз обновлять утомляет. На каком то сайте было описано что можно использовать свойство гиперссылок с помощью формул, но я так и не поняла описание. Пожалуйста опишите все также доступно как и в этой статье. Спасибо

excel says:
17.06.2016 в 10:23 дп

Спасибо за предложение, как появится свободное время обязательно напишу. Но в целом ничего сложного нет. используйте функцию «Гиперссылка».
Вот вам пример, на основании которого вы сможете сделать другие ссылки Вот так будет выглядеть ссылка на документ myfile.xlsx, который находится в папке C:\Docs\Excel\Primer, при открытии файла курсор перейдет на Лист1 в ячейку A1
=ГИПЕРССЫЛКА(«[C:\Docs\Excel\Primer\myfile.xlsx]Лист1!$A$1»)
то же самое но вместо текста будет Myfile
=ГИПЕРССЫЛКА(«[C:\Docs\Excel\Primer\myfile.xlsx]Лист1!$A$1″;»Myfile»)

Татьяна says:
18.01.2016 в 2:41 пп

Добрый день! Подскажите, как создать гиперссылку на определенный диапазон ячеек или лист в другой книге? Если делаю функцией или просто вставляю гиперссылку, то она отсылает просто на эту книгу Excel, а нужно, чтобы на определенный лист этой другой (не текущей) книги, т.к. листов в ней ОЧЕНЬ много.. Помогите, пожалуйста!

Создание и изменение ссылки на ячейку

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

Ссылки на ячейки можно использовать в одной или нескольких формулах для указания на следующие элементы:

• данные из одной или нескольких смежных ячеек на листе;
• данные из разных областей листа;
• данные на других листах той же книги.

Объект ссылки

Возвращаемое значение

Значение в ячейке C2

Значения во всех ячейках, но после ввода формулы необходимо нажать сочетание клавиш Ctrl+Shift+Enter.

Примечание. Эта функция не работает в Excel в Интернете.

Ячейки с именами «Актив» и «Пассив»

Разность значений в ячейках «Актив» и «Пассив»

Диапазоны ячеек «Неделя1» и «Неделя2»

Сумма значений в диапазонах ячеек «Неделя1» и «Неделя2» как формула массива

Ячейка B2 на листе Лист2

Значение в ячейке B2 на листе Лист2

Создание ссылки на ячейку на том же листе

1. Щелкните ячейку, в которую нужно ввести формулу.
2. В строка формул введите = (знак равенства).
3. Выполните одно из следующих действий.
4. Создайте ссылку на одну или несколько ячеек. Чтобы создать ссылку, выделите ячейку или диапазон ячеек на том же листе. Можно переместить границу выделения, перетащив границу ячейки, или перетащить угол границы, чтобы расширить выделение.
5. Создайте ссылку на определенное имя. Чтобы создать ссылку на определенное имя, выполните одно из указанных ниже действий.
6. Введите имя.
7. Нажмите клавишу F3, выберите имя в поле Вставить имя и нажмите кнопку ОК.

Примечание: Если в углу цветной границы нет квадратного маркера, значит это ссылка на именованный диапазон.

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

Создание ссылки на ячейку на другом листе

На ячейки, расположенные на других листах в той же книге, можно сослаться, вставив перед ссылкой на ячейку имя листа с восклицательным знаком (!). В приведенном ниже примере функция СРЗНАЧ используется для расчета среднего значения в диапазоне B1:B10 на листе «Маркетинг» в той же книге.

1. Ссылка на лист «Маркетинг».

2. Ссылка на диапазон ячеек с B1 по B10 включительно.

3. Ссылка на лист, отделенная от ссылки на диапазон значений.

1. Щелкните ячейку, в которую нужно ввести формулу.
2. В строка формул введите = (знак равенства) и формулу, которую нужно использовать.
3. Щелкните ярлычок листа, на который нужно сослаться.
4. Выделите ячейку или диапазон ячеек, на которые нужно сослаться.

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

Создание ссылки на ячейку с помощью команды «Ссылки на ячейки»

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

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

1. Выделите ячейку с данными, ссылку на которую необходимо создать.
2. Нажмите клавиши CTRL+C или перейдите на вкладку Главная и в группе Буфер обмена щелкните Копировать .
3. Нажмите клавиши CTRL+V или перейдите на вкладку Главная и в группе Буфер обмена щелкните Вставить . По умолчанию при вставке скопированных данных отображается кнопка Параметры вставки.
4. Нажмите кнопку Параметры вставки, а затем выберите пункт Вставить связь.

Изменение ссылки на ячейку на другую ссылку на ячейку

1. Дважды щелкните ячейку, содержащую формулу, которую нужно изменить. Каждая ячейка или диапазон ячеек в Excel, на которые ссылается формула, выделяются своим цветом.
2. Выполните одно из указанных ниже действий.
   • Чтобы переместить ссылку на ячейку или диапазон, перетащите цветную границу к новой ячейке или диапазону.
   • Чтобы изменить количество ячеек в диапазоне, перетащите угол границы.
   • В строка формул выделите ссылку в формуле и введите новую ссылку .
   • Нажмите клавишу F3, выберите имя в поле Вставить имя и нажмите кнопку ОК.
3. Нажмите клавишу ВВОД или, в случае формула массива, клавиши CTRL+SHIFT+ВВОД.

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

Изменение ссылки на ячейку на именованный диапазон

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

1. Выполните одно из указанных ниже действий.
   • Выделите диапазон ячеек, содержащих формулы, в которых необходимо заменить ссылки на ячейки определенными именами.
   • Чтобы заменить ссылки именами во всех формулах листа, выделите одну пустую ячейку.
2. На вкладке Формулы в группе Определенные имена щелкните стрелку рядом с кнопкой Присвоить имя и выберите команду Применить имена.
3. Выберите имена в поле Применить имена, а затем нажмите кнопку ОК.

Изменение типа ссылки: относительная, абсолютная, смешанная

1. Выделите ячейку с формулой.
2. В строке формул строка формул выделите ссылку, которую нужно изменить.
3. Для переключения между типами ссылок нажмите клавишу F4.

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