Как сделать ссылку на название листа excel
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функции ГИПЕРССЫЛКА в Microsoft Excel.
Описание
Функция ГИПЕРССЫЛКА создает ярлык для перехода в другое место в текущей книге или открытия документа, сохраненного на сетевом сервере, в интрасеть или в Интернете. Если щелкнуть ячейку с функцией ГИПЕРССЫЛКА, Excel к указанному расположению или откроется указанный документ.
Синтаксис
ГИПЕРССЫЛКА(адрес;[имя])
Аргументы функции ГИПЕРССЫЛКА описаны ниже.
адрес — обязательный аргумент. Путь к документу и имя файла, который нужно открыть. Адрес может ссылаться на определенное место в документе, например на ячейку или именованный диапазон листа или книги Excel либо на закладку в документе Microsoft Word. Путь может быть к файлу, который хранится на жестком диске. Путь также может быть универсальным соглашением об именовке (UNC) на сервере (в Microsoft Excel для Windows) или URL-адресом в Интернете или интрасети.
Обратите Excel в Интернете что функция ГИПЕРССЫЛКА действительна только для веб-адресов (URL-адресов). Link_location может быть текстовая строка, заключенная в кавычка, или ссылка на ячейку, содержаную ссылку в виде текстовой строки.
Понятное_имя — необязательный аргумент. Текст ссылки или числовое значение, отображаемое в ячейке. Имя отображается синим цветом с подчеркиванием. Если этот аргумент опущен, в ячейке в качестве текста ссылки отображается аргумент "адрес".
Аргумент "имя" может быть представлен значением, текстовой строкой, именем или ячейкой, содержащей текст или значение для перехода.
Замечания
Чтобы в Excel приложении выбрать ячейку с гиперссылкой, не переходя по ней, щелкните ячейку и удерживайте кнопку мыши, пока указатель не , а затем отпустите кнопку мыши. В Excel в Интернете выберите ячейку, щелкнув ее, когда указатель является стрелкой; перейти к пункту назначения гиперссылки, щелкнув, когда указатель является указателем руки.
Примеры
=ГИПЕРССЫЛКА("http://example.microsoft.com/report/budget report.xlsx", "Щелкните, чтобы просмотреть отчет")
Открывает книгу, сохраненную по адресу http://example.microsoft.com/report. В ячейке отображается текст "Щелкните, чтобы просмотреть отчет" в качестве текста ссылки.
=ГИПЕРССЫЛКА("[http://example.microsoft.com/report/budget report.xlsx]Годовой!F10"; D1)
=ГИПЕРССЫЛКА("[http://example.microsoft.com/report/budget report.xlsx]'Первый квартал'!ИтогиОтдел", "Щелкните, чтобы просмотреть итоги по отделу за первый квартал")
Создает гиперссылку на диапазон ИтогиОтдел на листе Первый квартал книги, сохраненной по адресу http://example.microsoft.com/report. В ячейке листа, содержащей гиперссылку, в качестве текста ссылки отобразится "Щелкните, чтобы вывести итоги по отделу за первый квартал".
=ГИПЕРССЫЛКА("http://example.microsoft.com/Annual Report.docx]КвартПриб", "Квартальный отчет о прибыли")
Отображает содержимое ячейки D5 в качестве текста ссылки и открывает книгу, сохраненную на сервере FINANCE в общей папке Statements. В данном примере используется путь в формате UNC.
Открывает книгу 1stqtr.xlsx, которая находится в каталоге Finance на жестком диске D. После этого выводится число, которое содержится в ячейке H10.
Создает гиперссылку на область Totals в другой (внешней) книге Mybook.xlsx.
=ГИПЕРССЫЛКА("[Книга1.xlsx]Лист1!A10";"Перейти на Лист1 > A10")
Чтобы перейти в другое место на текущем листе, укажите имя книги и имя листа, как в этом примере, где "Лист1" — текущий лист.
=ГИПЕРССЫЛКА("[Книга1.xlsx]Январь!A10";"Перейти на лист Январь > A10")
Чтобы перейти в место на другом листе, укажите имя книги и имя листа, как в этом примере, где "Январь" — другой лист в книге.
=ГИПЕРССЫЛКА(ЯЧЕЙКА("адрес",Январь!A1";"Перейти на лист Январь > A1")
Чтобы перейти в другое место на текущем листе без использования полной ссылки на лист ([Книга1.xlsx]), вы можете воспользоваться этой формулой, где ЯЧЕЙКА("адрес") возвращает текущее имя книги.
Чтобы быстро обновить все формулы на листе, использующие функцию ГИПЕРССЫЛКА с теми же аргументами, можно поместить целевой объект ссылки на тот же или другой лист, а затем использовать абсолютную ссылку на эту ячейку в качестве аргумента "адрес" в формулах с функцией ГИПЕРССЫЛКА. Изменения целевого объекта ссылки при этом будут немедленно отражаться в формулах.
В этой статье мы рассмотрим с вами такую задачу, как проставление ссылок в Excel. Ссылки на другие листы позволяют сделать более удобной навигацию.
Сейчас мы пошагово рассмотрим как это можно сделать. Допустим у нас есть ячейка с текстом “Смотрите на листе 2” и мы хотим, чтобы данный текст стал ссылкой, нажав на которую мы должны переместиться на Лист 2.
Для решения данной задачи необходимо выделить ячейку с этим текстом (B4) и далее либо нажав правой кнопкой мыши выбрать пункт меню “Гиперссылка”
или выбрать данный пункт, перейдя в раздел “Вставка”.
После этого откроется окно вставки гиперссылки. Необходимо перейти в раздел “Место в новом документе” и выбрать необходимый лист, на который требуется сделать ссылку. В нашем пример это Лист 2.
Нажимаем “Ок” и текст в нашем файле Excel превратится в ссылку.
Спасибо за внимание. Будем рады помочь.
На всех предыдущих уроках формулы и функции ссылались в пределах одного листа. Сейчас немного расширим возможности их ссылок.
Excel позволяет делать ссылки в формулах и функциях на другие листы и даже книги. Можно сделать ссылку на данные отдельного файла. Кстати в такой способ можно восстановить данные из поврежденного файла xls.
Ссылка на лист в формуле Excel
Доходы за январь, февраль и март введите на трех отдельных листах. Потом на четвертом листе в ячейке B2 просуммируйте их.
Возникает вопрос: как сделать ссылку на другой лист в Excel? Для реализации данной задачи делаем следующее:
- Заполните Лист1, Лист2 и Лист3 так как показано выше на рисунке.
- Перейдите на Лист4, ячейка B2.
- Поставьте знак «=» и перейдите на Лист1 чтобы там щелкнуть левой клавишей мышки по ячейке B2.
- Поставьте знак «+» и повторите те же действия предыдущего пункта, но только на Лист2, а потом и Лист3.
- Когда формула будет иметь следующий вид: =Лист1!B2+Лист2!B2+Лист3!B2, нажмите Enter. Результат должен получиться такой же, как на рисунке.
Как сделать ссылку на лист в Excel?
Ссылка на лист немного отличается от традиционной ссылки. Она состоит из 3-х элементов:
- Имя листа.
- Знак восклицания (служит как разделитель и помогает визуально определить, к какому листу принадлежит адрес ячейки).
- Адрес на ячейку в этом же листе.
Примечание. Ссылки на листы можно вводить и вручную они будут работать одинаково. Просто у выше описанном примере меньше вероятность допустить синтактическую ошибку, из-за которой формула не будет работать.
Ссылка на лист в другой книге Excel
Ссылка на лист в другой книге имеет уже 5 элементов. Выглядит она следующим образом: =’C:DocsЛист1′!B2.
Описание элементов ссылки на другую книгу Excel:
- Путь к файлу книги (после знака = открывается апостроф).
- Имя файла книги (имя файла взято в квадратные скобки).
- Имя листа этой книги (после имени закрывается апостроф).
- Знак восклицания.
- Ссылка на ячейку или диапазон ячеек.
Данную ссылку следует читать так:
- книга расположена на диске C: в папке Docs;
- имя файла книги «Отчет» с расширением «.xlsx»;
- на «Лист1» в ячейке B2 находится значение на которое ссылается формула или функция.
Полезный совет. Если файл книги поврежден, а нужно достать из него данные, можно вручную прописать путь к ячейкам относительными ссылками и скопировать их на весь лист новой книги. В 90% случаях это работает.
Без функций и формул Excel был бы одной большой таблицей предназначенной для ручного заполнения данными. Благодаря функциям и формулам он является мощным вычислительным инструментом. А полученные результаты, динамически представляет в желаемом виде (если нужно даже в графическом).
Разберем несколько вариантов добавления имени листа в ячейку в Excel с помощью формул.
Формула имени листа в Excel
Чтобы присвоить ячейке имя листа в Excel можно воспользоваться следующей формулой:
Давайте по шагам разберем принцип действия формулы имени листа.
Шаг 1. Функция ЯЧЕЙКА
Функция ЯЧЕЙКА позволяет получить данные о содержимом ссылки, в том числе и имя файла.
В данном случае формула ЯЧЕЙКА(«ИМЯФАЙЛА»;A1) позволяет получить полный путь файла Excel на локальном диске:
Как мы видим название листа идет сразу после названия файла, обрамленного в квадратные скобки.
Шаг 2. Функция ПОИСК
Таким образом для извлечения имени листа необходимо найти символ закрывающейся квадратной скобки (]) с помощью функции ПОИСК, которая возвращает позицию первого вхождения искомого элемента:
Прибавляя к результату 1, мы получаем позицию с которой начинается имя листа.
Шаг 3. Функция ПСТР
После нахождения квадратной скобки нам достаточно извлечь из полного названия файла правую часть, применив функцию ПСТР.
Данная функция возвращает заданное количество знаков, начиная с указанной позиции.
Так как точное количество символов в имени листа неизвестно, то в качестве последнего аргумента функции ПСТР указываем заведомо большее число, чем длина имени листа (подойдет любое число больше 31 — максимальная длина названия листа).
Альтернативная формула
Чтобы вставить название листа в ячейку можно воспользоваться альтернативной формулой:
Отличие от предыдущего варианта заключается в использовании функции ПРАВСИМВ, которая возвращает указанное количество знаков с конца текста.
Описание используемых функций
Функция ЯЧЕЙКА:
ЯЧЕЙКА(тип_сведений; )
Возвращает сведения о форматировании, адресе или содержимом первой ячейки ссылки.
- Тип сведений (обязательный аргумент) — текстовое значение, задающее тип сведений о ячейке (например, адрес, столбец, цвет, имяфайла, формат, скобки и т.д.);
- Ссылка (необязательный аргумент) — ячейка, по которой возвращаются данные.
Функция ПОИСК:
ПОИСК(искомый_текст; просматриваемый_текст; )
Возвращает позицию первого вхождения знака или строки текста (при чтении слева направо, прописные и строчные буквы не различаются).
- Искомый текст (обязательный аргумент) — искомый текст
- Просматриваемый текст (обязательный аргумент) — текст, по которому производится поиск;
- Начальная позиция (необязательный аргумент) — номер знака в просматриваемом тексте, с которого следует начать поиск.
Функция ПСТР:
ПСТР(текст; начальная_позиция; число_знаков)
Возвращает заданное число знаков из строки текста, начиная с указанной позиции.
- Текст (обязательный аргумент) — текст, из которого извлекают символы;
- Начальная позиция (обязательный аргумент) — позиция первого знака, извлекаемого из текста;
- Число знаков (обязательный аргумент) — количество знаков, возвращаемых функцией.
У каждого листа есть название. В одной книге Excel может быть 256 листов. Как использовать имя листа в Excel, чтобы упростить расчеты для множества листов? Например, вам нужно собрать сумму определенного столбца формулой со всех листов, как использовать имя листа в Excel для сбора данных? Ниже предлагаю описание этой возможности табличного редактора.
Имя листа Excel в расчетах
Конкретный пример. Есть два листа с данными по годам Сумма1 и Сумма2, необходимо посчитать сумму по этим листам. При этом необходимо использовать имя листа в формуле. Т.е. если добавить любое количество листов, была бы возможность быстро рассчитать эту сумму.
Для таких расчетов существует замечательная функция =ДВССЫЛ()
По факту в ней необходим один аргумент — ссылка на ячейку. Есть еще необязательный аргумент для определения типа ссылок (R1C1 или A1 — по умолчанию A1).
Чтобы указать имя листа в аргументе (ссылку) нужно воспользоваться возможностью сцепить. Разберем на примере
C$2 — ячейка в которой записано имя листа на который ссылаемся (в нашем случае Сумма1 и Сумма2 )
& — символ сцепки
«» — значит, что внутри ячеек воспринимается Excel как текст
!b2 — текстовое значение той ячейке на которую ссылаемся
Решение примера выше можно посмотреть в примере.
Вернуть имя листа в Excel в ячейку
Проще всего наверное макросом, создаем макрос и вносим в него такой текст:
Т.е. ячейке A1 мы этим макросом присваиваем имя листа (не забудьте запустить макрос)
Как я говорил, я больше работаю с формулами и функциями, т.к. они доступнее для простого пользователя, коих большинство. Наверное лучше разделить создание формулы на несколько этапов, т.к. она получится громоздкой
Мы используем функцию ПРАВСИМВ для того, чтобы отделить нужное количество символов в возвращенном полном имени файла при помощи ЯЧЕЙКА(«имяфайла») — например, C:UsersЛист1
Далее мы считаем количество ячеек которых нужно оделить справа — т.е. находим полную длину пути файла (ДЛСТР(ЯЧЕЙКА(«имяфайла»))) и вычитаем из нее количество символов до знака ]
В итоге получаем, что из текста C:UsersЛист1 нам с правой стороны нужно отделить 5 символов — Лист1
Формулу можно ввести в любую ячейку, так что не должно возникнуть проблем.
Имя файла в Excel
Так же можно вернуть в ячейке имя файла Excel
Формула будет иметь вид:
«имяфайла» — это именно такой аргумент, так и надо записывать.
Поделитесь нашей статьей в ваших соцсетях:
(Visited 5 456 times, 16 visits today)
Использование ссылок на другие рабочие листы в Excel дает возможность связывать листы между собой. Это позволяет создавать сложные проекты в рамках одной книги, где множество листов заимствуют друг у друга данные. В этом уроке Вы узнаете, как создать ссылку на рабочий лист Excel, а также между книгами.
Excel позволяет ссылаться на ячейки любого рабочего листа текущей книги, что особенно полезно, когда необходимо использовать конкретное значение с другого листа. Чтобы сделать это, в начале ссылки должно стоять имя листа с восклицательным знаком (!). Например, если необходимо сослаться на ячейку A1 на листе Лист1, ссылка будет выглядеть так: Лист1!A1.
Обратите внимание, если в названии листа содержатся пробелы, то его необходимо заключить в одинарные кавычки (‘ ‘). Например, если вы хотите создать ссылку на ячейку A1, которая находится на листе с названием Бюджет июля. Ссылка будет выглядеть следующим образом: ‘Бюджет июля’!А1.
Создаем ссылку в Excel на другой лист
В следующем примере мы будем ссылаться с одного листа Excel на значение, которое относится к другому рабочему листу. Это позволит нам использовать одно и тоже значение на двух разных листах.
Если Вы в дальнейшем переименуете лист, то ссылка автоматически обновится и появится новое название листа.
Как создать ссылку на другую книгу Excel
Помимо создания ссылок на другие листы, Excel также позволяет создавать ссылки на другие книги. Для этого перед ссылкой на рабочий лист необходимо подставить имя книги в квадратных скобках. Например, если имя книги – Книга1, листа – Лист1, то ссылка на ячейку А1 будет выглядеть следующим образом: =Лист1!А1
Чтобы использовать приведенную выше конструкцию, необходимо, чтобы рабочая книга Excel, на которую мы ссылаемся, была открыта.
Чтобы присвоить ячейке имя листа в Excel можно воспользоваться следующей формулой:
=ПСТР(ЯЧЕЙКА(«ИМЯФАЙЛА»; A1 );ПОИСК(«]»;ЯЧЕЙКА(«ИМЯФАЙЛА»; A1 ))+1;255)
Давайте по шагам разберем принцип действия формулы имени листа.
Шаг 1. Функция ЯЧЕЙКА
Функция ЯЧЕЙКА позволяет получить данные о содержимом ссылки, в том числе и имя файла.
В данном случае формула ЯЧЕЙКА(«ИМЯФАЙЛА»; A1 ) позволяет получить полный путь файла Excel на локальном диске:
Как мы видим название листа идет сразу после названия файла, обрамленного в квадратные скобки.
Шаг 2. Функция ПОИСК
Таким образом для извлечения имени листа необходимо найти символ закрывающейся квадратной скобки (]) с помощью функции ПОИСК, которая возвращает позицию первого вхождения искомого элемента:
Прибавляя к результату 1, мы получаем позицию с которой начинается имя листа.
Шаг 3. Функция ПСТР
После нахождения квадратной скобки нам достаточно извлечь из полного названия файла правую часть, применив функцию ПСТР.
Данная функция возвращает заданное количество знаков, начиная с указанной позиции.
Так как точное количество символов в имени листа неизвестно, то в качестве последнего аргумента функции ПСТР указываем заведомо большее число, чем длина имени листа (подойдет любое число больше 31 — максимальная длина названия листа).
Альтернативная формула
Чтобы вставить название листа в ячейку можно воспользоваться альтернативной формулой:
=ПРАВСИМВ(ЯЧЕЙКА(«ИМЯФАЙЛА»; A1 );ДЛСТР(ЯЧЕЙКА(«ИМЯФАЙЛА»; A1 ))-ПОИСК(«]»;ЯЧЕЙКА(«ИМЯФАЙЛА»; A1 )))
Отличие от предыдущего варианта заключается в использовании функции ПРАВСИМВ, которая возвращает указанное количество знаков с конца текста.
Описание используемых функций
Функция ЯЧЕЙКА:
ЯЧЕЙКА(тип_сведений; [ссылка])
Возвращает сведения о форматировании, адресе или содержимом первой ячейки ссылки.
- Тип сведений(обязательный аргумент) — текстовое значение, задающее тип сведений о ячейке (например, адрес, столбец, цвет, имяфайла, формат, скобки и т.д.);
- Ссылка(необязательный аргумент) — ячейка, по которой возвращаются данные.
Функция ПОИСК:
ПОИСК(искомый_текст; просматриваемый_текст; [начальная_позиция])
Возвращает позицию первого вхождения знака или строки текста (при чтении слева направо, прописные и строчные буквы не различаются).
- Искомый текст(обязательный аргумент) — искомый текст
- Просматриваемый текст(обязательный аргумент) — текст, по которому производится поиск;
- Начальная позиция(необязательный аргумент) — номер знака в просматриваемом тексте, с которого следует начать поиск.
Функция ПСТР:
ПСТР(текст; начальная_позиция; число_знаков)
Возвращает заданное число знаков из строки текста, начиная с указанной позиции.
В этой статье мы рассмотрим с вами такую задачу, как проставление ссылок в Excel. Ссылки на другие листы позволяют сделать более удобной навигацию.
Сейчас мы пошагово рассмотрим как это можно сделать. Допустим у нас есть ячейка с текстом “Смотрите на листе 2” и мы хотим, чтобы данный текст стал ссылкой, нажав на которую мы должны переместиться на Лист 2.
Для решения данной задачи необходимо выделить ячейку с этим текстом (B4) и далее либо нажав правой кнопкой мыши выбрать пункт меню “Гиперссылка”
или выбрать данный пункт, перейдя в раздел “Вставка”.
После этого откроется окно вставки гиперссылки. Необходимо перейти в раздел “Место в новом документе” и выбрать необходимый лист, на который требуется сделать ссылку. В нашем пример это Лист 2.
Нажимаем “Ок” и текст в нашем файле Excel превратится в ссылку.
Спасибо за внимание. Будем рады помочь.
На всех предыдущих уроках формулы и функции ссылались в пределах одного листа. Сейчас немного расширим возможности их ссылок.
Excel позволяет делать ссылки в формулах и функциях на другие листы и даже книги. Можно сделать ссылку на данные отдельного файла. Кстати в такой способ можно восстановить данные из поврежденного файла xls.
Ссылка на лист в формуле Excel
Доходы за январь, февраль и март введите на трех отдельных листах. Потом на четвертом листе в ячейке B2 просуммируйте их.
Возникает вопрос: как сделать ссылку на другой лист в Excel? Для реализации данной задачи делаем следующее:
- Заполните Лист1, Лист2 и Лист3 так как показано выше на рисунке.
- Перейдите на Лист4, ячейка B2.
- Поставьте знак «=» и перейдите на Лист1 чтобы там щелкнуть левой клавишей мышки по ячейке B2.
- Поставьте знак «+» и повторите те же действия предыдущего пункта, но только на Лист2, а потом и Лист3.
- Когда формула будет иметь следующий вид: =Лист1!B2+Лист2!B2+Лист3!B2, нажмите Enter. Результат должен получиться такой же, как на рисунке.
Как сделать ссылку на лист в Excel?
Ссылка на лист немного отличается от традиционной ссылки. Она состоит из 3-х элементов:
- Имя листа.
- Знак восклицания (служит как разделитель и помогает визуально определить, к какому листу принадлежит адрес ячейки).
- Адрес на ячейку в этом же листе.
Примечание. Ссылки на листы можно вводить и вручную они будут работать одинаково. Просто у выше описанном примере меньше вероятность допустить синтактическую ошибку, из-за которой формула не будет работать.
Ссылка на лист в другой книге Excel
Ссылка на лист в другой книге имеет уже 5 элементов. Выглядит она следующим образом: =’C:DocsЛист1′!B2.
Описание элементов ссылки на другую книгу Excel:
- Путь к файлу книги (после знака = открывается апостроф).
- Имя файла книги (имя файла взято в квадратные скобки).
- Имя листа этой книги (после имени закрывается апостроф).
- Знак восклицания.
- Ссылка на ячейку или диапазон ячеек.
Данную ссылку следует читать так:
- книга расположена на диске C: в папке Docs;
- имя файла книги «Отчет» с расширением «.xlsx»;
- на «Лист1» в ячейке B2 находится значение на которое ссылается формула или функция.
Полезный совет. Если файл книги поврежден, а нужно достать из него данные, можно вручную прописать путь к ячейкам относительными ссылками и скопировать их на весь лист новой книги. В 90% случаях это работает.
Без функций и формул Excel был бы одной большой таблицей предназначенной для ручного заполнения данными. Благодаря функциям и формулам он является мощным вычислительным инструментом. А полученные результаты, динамически представляет в желаемом виде (если нужно даже в графическом).
Использование ссылок на другие рабочие листы в Excel дает возможность связывать листы между собой. Это позволяет создавать сложные проекты в рамках одной книги, где множество листов заимствуют друг у друга данные. В этом уроке Вы узнаете, как создать ссылку на рабочий лист Excel, а также между книгами.
Excel позволяет ссылаться на ячейки любого рабочего листа текущей книги, что особенно полезно, когда необходимо использовать конкретное значение с другого листа. Чтобы сделать это, в начале ссылки должно стоять имя листа с восклицательным знаком (!). Например, если необходимо сослаться на ячейку A1 на листе Лист1, ссылка будет выглядеть так: Лист1!A1.
Обратите внимание, если в названии листа содержатся пробелы, то его необходимо заключить в одинарные кавычки (‘ ‘). Например, если вы хотите создать ссылку на ячейку A1, которая находится на листе с названием Бюджет июля. Ссылка будет выглядеть следующим образом: ‘Бюджет июля’!А1.
Создаем ссылку в Excel на другой лист
В следующем примере мы будем ссылаться с одного листа Excel на значение, которое относится к другому рабочему листу. Это позволит нам использовать одно и тоже значение на двух разных листах.
Если Вы в дальнейшем переименуете лист, то ссылка автоматически обновится и появится новое название листа.
Как создать ссылку на другую книгу Excel
Помимо создания ссылок на другие листы, Excel также позволяет создавать ссылки на другие книги. Для этого перед ссылкой на рабочий лист необходимо подставить имя книги в квадратных скобках. Например, если имя книги – Книга1, листа – Лист1, то ссылка на ячейку А1 будет выглядеть следующим образом: =Лист1!А1
Чтобы использовать приведенную выше конструкцию, необходимо, чтобы рабочая книга Excel, на которую мы ссылаемся, была открыта.
Имеем две таблицы на разных листах одной книги: таблицу с заказами от клиентов (на листе Заказы) и таблицу с клиентской базой (лист Клиенты). Работая с таблицей заказов, хочется иметь возможность быстро переходить на таблицу с клиентами, чтобы просмотреть подробные данные по клиенту (адрес, ФИО директора и т.д.). То есть, другими словами, хочется в таблице заказов иметь гиперссылку в каждой строке, при щелчке мышью по которой будет происходить переход на лист Клиенты, причем именно на ту строчку где упоминается данный клиент:
Что-то типа типа функции ВПР (VLOOKUP), но не ради подстановки данных, а для быстрой ссылки из одной таблицы в другую.
Шаг 1. Создаем переменную с именем листа
Для создания гиперссылок в будущем нам понадобится каждый раз прописывать имя текущего файла и имя листа Клиенты, на который пойдут ссылки. Поэтому проще один раз создать переменную в памяти Excel с нужным значением и обращаться к ней потом по мере надобности.
В Excel 2007/2010 для этого можно воспользоваться вкладкой Формулы (Formulas) и кнопкой Диспетчер имен (Name Manager) . В более старых версиях выбрать в меню Вставка - Имя - Присвоить (Insert - Name - Define) . В открывшемся окне нажмите кнопку Создать (New) и введите туда имя переменной (я назвал ее для примера Мой_Лист) и формулу в строку Диапазон (Reference) :
=ПСТР(ЯЧЕЙКА("имяфайла";Клиенты!$A$1); ПОИСК("[";ЯЧЕЙКА("имяфайла";Клиенты!$A$1)) ;256) &"!"
Разберем эту конструкцию на составляющие для понятности:
- ЯЧЕЙКА("имяфайла";Клиенты!$A$1) - функция, которая по адресу заданной ячейки (А1 с листа Клиенты) выдает любые нужные данные по листу и файлу. В данном случае - полный путь к текущему файлу до листа в виде D:\Рабочие документы\Договоры[Бюджет.xls]Клиенты
- Из этой строки нам нужна только часть с именем файла и листа (без диска и папок), поэтому мы ищем первое вхождение квадратной открывающей скобки в строку с помощью функции ПОИСК (FIND) и затем вырезаем из строки все, начиная с этого символа и до конца (256 символов) с помощью функции ПСТР (MID) .
- В конце, к вырезанному фрагменту с именем файла и листа приклеиваем восклицательный знак - стандартный разделитель имен листов и адресов ячеек в формулах, т.к. дальше должны будут идти адреса ячеек.
Таким образом эта формула выдает на выходе имя текущего файла в квадратных скобках с расширением с приклееным к нему именем листа и восклицательным знаком. Работу формулы легко проверить - просто введите в любую пустую ячейку =Мой_Лист и нажмите клавишу Enter.
Шаг 2. Создаем гиперссылки
Выделите пустую ячейку в строке напротив первого заказа и введите туда вот такую формулу:
Разберем ее на составляющие аналогичным образом:
- Функция ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0) - ищет порядковый номер ячейки в диапазоне А1:А7 на листе Клиенты, где встречается название текущего клиента из B2 (последний аргумент =0 означает поиск точного совпадения, аналогично функции ВПР)
- Функция АДРЕС формирует адрес ячейки (в виде текстовой строки) по номеру строки и столбца, т.е. адрес ячейки с нужным клиентом, куда должна потом ссылаться гиперссылка
- Затем мы приклеиваем к адресу ссылку на файл и лист (переменную Мой_Лист) и используем это в качестве аргумента для функции ГИПЕРССЫЛКА (HYPERLINK) , которая, собственно, и создает нужную нам ссылку.
При желании, можно заменить внешнее представление гиперссылки с банальных символов ">>" на что-нибудь поинтереснее с помощью функции СИМВОЛ (CHAR) , которая умеет выводить нестандартные символы по их кодам:
=ГИПЕРССЫЛКА(Мой_Лист&АДРЕС(ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0);1);СИМВОЛ(117))
Так, например, если использовать шрифт Wingdings 3 и символ с кодом 117, то можно получить вот такие симпатичные значки гиперссылок:
Читайте также: