Excel: Ссылки на ячейки и книжки

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

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

I. Чтоб сделать ссылку на ячейку из другого листа той же книжки нужно:

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

3. Перейти, при помощи вкладок листов, на лист, из которого будем брать данные.

4. Выделить ячейку с нужным значением.

Таковым образом, мы получим ссылку на ячейку из Листа 2, и в начальной ячейке отобразится необходимое значение.
То же самое можно было получить, если вручную ввести в начальную ячейку формулу «=Лист2!В2».

Для наполнения других ячеек таблицы, можно протянуть формулу при помощи маркера наполнения,

Сейчас при изменении цен на Листе 2 , автоматом будут изменяться и значения цен в таблице «Размер продаж».

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

= Имя листа ! Адресок ячейки

II. Аналогично можно сделать ссылку на ячейку из иной книжки.

1-ый метод — в ячейку первой книжки вносим символ «=», перебегаем ко 2-ой книжке, где избираем подходящую ячейку. Дальше жмем Enter .

2-ой метод — вручную прописать ссылку в ячейку.

Пример ссылки, если книжки находятся в одном каталоге: =[Книга1.xlsx]Лист1!A1

=[ Имя книги ] Имя листа! Адресок ячейки

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

Пример ссылки на ячейку в книжке, находящейся в каталоге C:Мои документы:

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

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

Создание связанного перечня всех книжек в папке при помощи Kutools for Excel

Ссылка на определенную ячейку листа в иной книжке с функцией гиперссылки

Чтоб сделать ссылку на лист в иной книжке, вы сможете применить Ссылка функции.

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

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

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

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

5. Нажмите OK, и если для вас необходимо показать подсказку на экране, вы сможете надавить SreenTip чтоб указать подсказку на экране.

6. Нажмите OK > OK чтоб закрыть диалоги. Тогда ссылка была сотворена. Когда вы щелкаете ссылку, раскроется книжка и найдется определенная ячейка листа, на который вы ссылаетесь.

Ссылка на определенную ячейку листа в иной книжке с формулой

Также существует формула, при помощи которой можно стремительно сделать ссылку на другую книжку.

Изберите ячейку, в которой вы собираетесь расположить ссылку, и введите эту формулу = ГИПЕРССЫЛКА («[C: Users DT168 Desktop 20161122.xlsx] сверхурочно! B4», «НАЖМИТЕ ЗДЕСЬ») в строчку формул, а потом нажмите Enter. Сейчас ссылка сотворена.

Примечание: в формуле вы сможете поменять путь и отображаемый текст по собственному усмотрению.

Создание связанного перечня всех книжек в папке при помощи Kutools for Excel

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

Опосля установки Kutools for Excel, пожалуйста, сделайте, как показано ниже 🙁 Скачать безвозмездно Kutools for Excel на данный момент!)

1. Нажмите Kutools Plus > Импорт Экспорт > Перечень названий файлов. Смотрите снимок экрана:

2. в Перечень названий файлов диалог, сделайте как показано ниже:

A: Изберите папку, в которой вы желаете показать все файлы

B: укажите тип файла, который желаете показать

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

D: Проверьте Создание гиперссылок опцию.

3. Нажмите Ok, названия файлов с возможностью связывания были перечислены на новеньком листе.

Создание внутренних и наружных ссылок функцией ГИПЕРССЫЛКА

Это обычная, но очень нужная функция, позволяющая сделать живую ссылку на:

• всякую ячейку на любом листе в книжке
• именованный спектр
• фрагмент умной таблицы
• наружный файл Excel на диске либо на сервере в сети
• интернет-страницу

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

Синтаксис функции прост:

=ГИПЕРССЫЛКА( Адресок ; Текст )

• Текст — это видимый текст гиперссылки в ячейке (обычно выделенный голубым цветом и подчеркиванием, как все гиперссылки), по которому щелкает юзер. Быть может хоть каким, это роли не играет.
• Адресок — пространство, куда мы желаем сослаться (и это самое увлекательное).

Давайте разглядим все варианты подробнее.

Ссылка на интернет-страницу

Это самый обычный и тривиальный вариант внедрения нашей функции ГИПЕРССЫЛКА (HYPERLINK) . Первым аргументом тут будет полная ссылка на интернет-страницу (URL):

Направьте внимание, что адресок должен быть полным, т.е. непременно начинаться с протокола (обычно «http»). При щелчке по сделанной ссылке данный веб-сайт раскроется в браузере по дефлоту.

Ссылка на ячейку снутри книжки

Если нужна ссылка на ячейку либо спектр, находящийся снутри текущей книжки, то адресок необходимо будет указать последующим образом:

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

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

Ссылка на ячейку во наружной книжке

Можно просто сделать ссылку на ячейку во наружной книжке. Тогда в качестве первого аргумента нужен будет полный путь к файлу (с расширением!), имя листа и адресок ячейки:

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

Ссылка на файл на сервере

Также поддерживаются сетевые ссылки, если файл сохранен, к примеру, на корпоративном сервере в общей папке — в этом случае употребляется путь в формате UNC, начинающийся с 2-ух оборотных дробей:

Ссылка на именованный спектр

Если вы сделали именованный спектр (к примеру, через Формулы — Диспетчер имен — Сделать), то можно расслабленно употреблять это имя в ссылке:

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

Ссылка на умную таблицу либо ее элементы

Если вы знакомы с умными таблицами, то понимаете какая это нужная штука. При желании, можно просто сделать при помощи функции ГИПЕРССЫЛКА (HYPERLINK) ссылку на хоть какой подходящий нам фрагмент умной таблицы либо на нее полностью. Для этого в первом аргументе необходимо указать имя таблицы (к примеру Таблица1) и кодовое обозначение ее элемента:

Поддерживаются последующие обозначения:

• Таблица1[#Все] — ссылка на всю таблицу, включая заглавия столбцов, данные и строчку итогов
• Таблица1[#Данные] либо просто Таблица1 — ссылка лишь на данные (без строчки заголовка и итогов)
• Таблица1[#Заголовки] — ссылка лишь на первую строчку таблицы с заголовками столбцов
• Таблица1[#Итоги] — ссылка на строчку итогов (если она включена)

Формирование заполненной формы письма email

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

При щелчке по таковой ссылке мы увидим вот такую красоту:

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

Внедрение необычных знаков

Время от времени можно соединять функцию ГИПЕРССЫЛКА с функцией вывода необычных символов СИМВОЛ (CHAR), которую мы уже разбирали. Это дозволяет поменять обычный текст ссылки на что-то наиболее привлекательное:

Абсолютные и относительные ссылки в Excel

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

Относительные ссылки в Excel

По дефлоту, все ссылки в Excel относительные. Когда вы копируете ссылку из одной ячейки в другую, она автоматом меняется относительно позиции столбца и строчки новейшей ячейки к ячейке, из которой вы скопировали ссылку. К примеру, если вы скопируете формулу =A1+B1 из строчки 1 в строчку 2, то формула поменяется на =A2+B2 . Относительные ссылки полезны в том случае, когда нам необходимо повторить один и этот же расчет на несколько столбцов и строк.

Как сделать и скопировать формулу с относительными ссылками

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

• Выделим первую ячейку, в столбце “Результат” в которой будет сотворена наша формула:

• Вставим в ячейку D2 формулу, которая перемножает стоимость за блюдо и количество: =B2*C2 .
• Нажмите кнопку “Enter” на клавиатуре. Формула произведет расчет и его итог вы увидите в ячейке D2 .

• Зажав левую кнопку мыши, протяните ячейку D2 за правый нижний угол по всему спектру ячеек D3:D12 . Таковым образом, вы скопируете формулу из ячейки D2 и перенесете ее на каждую ячейку спектра.

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

Абсолютные ссылки в Excel

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

Для сотворения абсолютной ссылки употребляется символ бакса “$”. С его помощью вы сможете зафиксировать от конфигураций столбец, строчку либо всех вкупе:

$A$2 – столбец и строчка не меняются при копировании формулы;

A$2 – при копировании формулы не изменяется лишь строчка;

$A2 – столбец не меняется при копировании формулы .

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

Как сделать и скопировать формулу с абсолютными ссылками

В нашем примере мы будем употреблять в ячейке E1 – 18% как значение НДС для расчета налога на продукты в колонке D . Для правильного расчета нам будет нужно употреблять абсолютную ссылку $E$1 в нашей формуле, потому что нам принципиально, чтоб стоимость всякого продукта перемножалась на ставку НДС, обозначенную в ячейке E1 . Ниже разглядим как мы, будем это созодать:

• Выделим ячейку, в которую мы желаем вставить формулу для расчета налога. В нашем примере это ячейка D3 .

• Напишем формулу, рассчитывающую сумму налога для всякого продукта, с учетом его цены и количества =(B3*C3)*$E$1 .

• Протянем полученную формулу на все ячейки в спектре D4:D13 .

• Два раза кликните на хоть какой ячейке из спектра D4:D13 и удостоверьтесь, что формула сработала корректно. Принципиально убедиться, что вы верно указали ссылку на ячейку $E$1 в абсолютном формате.

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

Часто, нам в расчетах требуется использовать данные с различных листов файла Excel. Для этого, при разработке ссылки на ячейку из другого листа необходимо употреблять заглавие листа и восклицательного знака на конце ( ! ). К примеру, если вы желаете сделать ссылку на ячейку A1 на листе Sheet1, то ссылка на эту ячейку будет смотреться так:

ВАЖНО! Если в заглавие листа, на ячейку с которого вы ссылаетесь есть пробелы, то заглавие этого листа в ссылке обязано быть заключено в кавычки (‘ ‘). К примеру, если заглавие вашего листа Бюджет Конец, то ссылка на ячейку A1 будет смотреться так:

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

• Выберем ячейку, на которую мы желаем сослаться и обратим внимание на заглавие листа. В нашем случае это ячейка E14 на вкладке “Меню”:

• Перейдем на лист и выберем ячейку, в которой мы желаем поставить ссылку. В нашем примере это ячейка B2 .

• В ячейке B2 введем формулу, ссылающуюся на ячейку E14 с листа “Меню”: =Меню!E14
• Нажмем кнопку “Enter” на клавиатуре и увидим в ячейке B2 значение ячейки E14 с листа “Меню”.

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

Еще более нужных приемов в работе со перечнями данных и функциями в Excel вы узнаете в практическом курсе “От новенького до мастера Excel“. Успей зарегистрироваться по ссылке!