Как перенести формулу с одного листа на другой в excel
Формулы могут работать с ячейками из других листов. Вам просто нужно указать перед ссылкой на ячейку имя листа и восклицательный знак. Например, следующая формула добавляет 12 к значению в ячейке С1, которая находится на листе Лист2: =Лист2!С1+12.
Что делать, если вам нужно вычислить сумму всех значений в С1, начиная с листа Лист2 и заканчивая листом Лист6? Следующая формула решает эту задачу: =СУММ(Лист2:Лист6!C1). В данном случае двоеточие разделяет название первого и последнего листов.
Для создания такой формулы выполните приведенные ниже действия.
Активизируйте ячейку, которая будет содержать формулу, и наберите =СУММ(.
Щелкните на вкладке первого листа (в нашем случае это Лист2) и выберите ячейку С1.
Нажмите Shift и щелкните на вкладке последнего листа (в нашем случае это Лист6).
Нажмите Enter, и формула будет введена в ячейку.
В шаге 2 вы можете выбрать диапазон, а не одну-единствениую ячейку. Например, эта формула возвращает сумму C1:F12 для всех листов от Лист2 до Лист6: =СУММ(Лист2:Лист6!С1:Р12).
Теперь я покажу вам интересный прием, который узнал при прочтении новостных конференций Excel и на обучение в Минске. Если вы хотите просуммировать одну и ту же ячейку со всех листов, кроме текущего, просто введите формулу наподобие этой: =СУММ(‘*’!C1). Звездочка служит подстановочным символом, который интерпретируется как «все листы, кроме этого одного». Когда вы нажмете клавишу Enter после ввода этой формулы, Excel преобразует формулу, чтобы она использовала фактические имена листов. Это работает, даже если активный лист находится где-нибудь в середине, между другими листами. Например, если книга состоит из шести листов и вы введете приведенную выше формулу в ячейку листа Лист3, Excel создаст следующую формулу: =СУММ(Лист1:Лист2!С1;Лист4:Лист6!C1).
Но это еще не все. Введите следующую формулу для нахождения суммы ячеек С1 во всех листах, которые начинаются со слова Регион: =СУММ(‘Регион*»!C1). Excel может преобразовать данную формулу во что-то наподобие этого: =СУММ(Регион1:Регион4!C1).
Вы также можете использовать подстановочный знак ? — он указывает на любой отдельный символ. Например, при вводе следующей формулы Excel создаст формулу, которая просуммирует значения, начиная с листа Лист1 и заканчивая листом Лист9 (названия, которые содержат одну цифру): =СУММ(‘Лист?’!C1). Этот прием не ограничивается функцией СУММ. Он работает и с другими функциями, такими как СРЗНАЧ, МИН, МАКС.
Функция ЛИСТ предназначена для возвращения номера конкретного листа с промежутком, который открывает доступ ко всей рабочей книге в MS Excel. Функция ЛИСТЫ предоставляет пользователю информацию о количестве листов, содержащихся в рабочей книге.
Формулы с использованием ссылок на другие листы Excel
Предположим у нас имеется фирма DecArt в которой работают сотрудники и им ежемесячно начисляется зарплата. У данной фирмы имеются сведения о среднемесячной зарплате в Excel, а данные по ней размещены на разных листах: на листе 1 размещены данные о зарплате, на листе 2 премия в процентах. Нам необходимо вычислить размер премии в рублях, при том чтобы данные эти были размещены на втором листе.
Для начала рассмотрим пример работы с листами в формулах Excel. Пример 1:
- Создадим на листе 1 рабочей книги табличного процессора Excel таблицу, как показано на рисунке. Сведения о среднемесячной заработной плате:
- Далее на листе 2 рабочей книги подготовим область для размещения нашего результата — размера нашей премии в рублях, как показано на рисунке:
- Далее нам необходимо будет ввести в ячейку В2 формулу, изображенную на рисунке ниже:
Ввод данной формулы происходил следующим образом: для начала в ячейке В2 мы установили знак «=», затем кликнули на «Лист1» в нижнем левом углу рабочей книги и перешли на ячейку C3 на листе 1, далее ввели операцию умножения и перешли вновь на «Лист2» для того чтобы добавить процент.
Таким образом мы получили при расчете премии каждого сотрудника, причем исходные данные находились на одном листе, а расчет был произведен на другом листе. Данная формула окажется весьма полезной при работе с более длинными массивами данных в крупных организациях.
Функция ЛИСТЫ для подсчета количества листов в рабочей книге
Рассмотрим теперь пример работы функции ЛИСТЫ. Довольно часто бывает так, что в рабочей книге Excel расположено слишком много листов. Выяснить их точное количество визуально не представляется возможным, именно с этой целью и создана функция ЛИСТЫ.
В данной функции всего 1 аргумент – «Ссылка» да и то необязательный для заполнения. Если его не заполнять тогда функция возвращает общее количество листов, созданных в текущей рабочей книге файла Excel. При необходимости можно заполнить аргумент. Для этого в нем необходимо указать ссылку на рабочую книгу, в которой необходимо подсчитать общее количество листов, созданных в ней.
Пример2. Предположим у нас имеется фирма по производству мягкой мебели, и у нее есть множество документов, которые содержатся в рабочей книге Excel. Нам необходимо вычислить точное число этих документов, так как каждый из них имеет свое название, то для того чтобы визуально вычислить их количество потребуется время.
На рисунке ниже показано примерное количество листов:
Чтобы организовать подсчет всех листов, необходимо воспользоваться функцией ЛИСТЫ. Просто ставим знак равенства «=» и вписываем функцию, не заполняя ее аргументов в скобках. Вызов данной функции показан ниже на рисунке:
В результате получим следующее значение: 12 листов.
Таким образом мы узнали, что в нашей фирме имеется 12 документов, содержащихся в рабочей книге Excel. Этот простой пример наглядно иллюстрирует работу функции ЛИСТЫ. Данная функция может стать полезной для руководителей, офисных сотрудников, менеджеров по продажам.
Ссылки на другие листы в шаблонах документов
Пример 3. Имеются данные о расходах на банкет компании занимающейся выездным обслуживанием. Необходимо произвести расчет общей стоимости банкета, а также общий выход порций блюд, и вычислить общее количество листов в документе.
- Создадим таблицу «Банкетное меню», общий вид которой представлен на рисунке ниже:
- Аналогичным образом создадим таблицы на разных листах «Общая стоимость» и «Общий выход»:
- При помощи формулы со ссылками на другие листы произведем расчет общей стоимости банкетного меню:
- Перейдем на лист «Общий выход» и путем умножения ячеек веса одной порции, находящегося на листе 2 и общего количества, находящегося на листе 1 произведем расчет общего выхода:
В результате у нас получился простейший шаблон для подсчета расходов на 1 банкет.
Один из читателей нашего сайта SirExcel задал вопрос, который может встречаться очень часто. Вопрос заключался в следующем «Как сделать так, чтобы на одном листе показывалась информация из определенной ячейки к примеру $A$1 но с каждого листа из книги? Листов очень много надо сделать сводный список содержимого определенной ячейки со всех листов.»
Я как раз собирался написать о том, как работает функция ДВССЫЛ, почему бы не рассмотреть ее именно на этом примере.
Итак, мы имеем файл Excel, в котором много листов. Давайте рассмотрим простой пример, где названия листов у нас не менялись. То есть у нас есть Лист1, Лист2, Лист3 и так далее.
Допустим, что на каждом листе (Лист1-Лист5) у нас есть данные по выручке 5 различных магазинов в виде одинаковых таблиц данных.
На листе 6 есть результирующая таблица, где нам необходимо заполнить ее данными, взяв их со всех листов.
Таким образом, чтобы заполнить данные за январь нам необходимо взять данные по выручке магазинов, находящихся в ячейке B2, но на 5 различных листов.
Конечно, в нашем примере это можно было бы сделать в ручную, указав просто ссылки на соответствующие значения, но если листов очень много, или данных очень много, то вручную это сделать будет очень затруднительно.
Существует различные способы решить данную задачу, например, с помощью простенького макроса, который можно записать макрорекодером. Ну а мы с вами для решения задачи будет использовать функцию ДВССЫЛ.
ДВССЫЛ – функция, которая возвращает ссылку заданную текстовой строкой.
Чтобы понять принцип действия данной функцию, давайте рассмотрим такой пример.
Посмотрите на рисунок ниже. В ячейке А1 написан текст D4, а в самой ячейке D4 указано число 9999. Если мы напишем формулу ДВССЫЛ(A1), то в результате получим число 9999. То же самое мы получим если напишем формулу так:
=ДВССЫЛ("D4")
То есть мы написали текстом адрес ячейки D4 и функция ДВССЫЛ вернула нам то значение, которое находится по данному адресу (D4).
Надеюсь, все стало более понятным. Теперь давайте рассмотрим на нашем примере. Чтобы заполнить данные за январь с разных листов нам необходимо прописать следующие ссылки на ячейки.
=Лист1!B2 (Выручка за январь магазина 1)
=Лист2! В2 (Выручка за январь магазина 2) и т.д.
Но сложность заключается в том, что если ячейку просто протянуть вниз, то Лист1 не будет меняться на Лист2 и так далее.
Если мы пропишем формулу
ДВCСЫЛ("Лист1!B2"), ДВCСЫЛ("Лист2!B2") и так далее, то функция будет возвращать нам то же самое, но это так же не решает нашу проблему, ведь «Лист1!B2» прописан обычным текстом и так же не будет изменяться при протягивании.
Для решения нашей задачи мы разделим текст «Лист1!B2» на две части (отдельно «Лист1» и отдельно «!B2» — обратите внимание на восклицательный знак во второй части текста) и потом их склеим. Текст Лист1, Лист2 и так далее мы пропишем напротив соответствующих магазинов, при этом написав Лист1, мы сможем просто протянуть ячейку вниз и автоматически получить список Лист1, Лист2 и так далее.
Теперь сцепим этот текст внутри функции ДВССЫЛ с помощью знака & и получим формулу, которая будет автоматически протягиваться и при этом будут подтягиваться данные из одной и той же ячейки, но разных листов.
Итоговая формула выглядит именно так, но я специально расписал с самого начала по шагам, чтобы было понятно из чего состоит данная формула. В принципе, этого уже достаточно, чтобы за короткий срок решить задачу, указанную в начале статьи. Чтобы заполнить данные за февраль, нам необходимо в ячейке D2 написать такую же формулу, но поменять текст «!B2» на «!B3»
=ДВССЫЛ(A2&"!B3") и протянуть вниз, аналогично за март.
Но никто нам не мешает немного усложнить формулу и заполнить ее автоматически, протянув формулу вниз, а затем вправо. Тут я не буду подробно описывать, но если вы разобрались с предыдущим примером, то легко поймете и данный.
Итак, мы решили задачу, как быстро заполнить данные за определенный месяц, но нам необходимо заполнить данные за остальные месяца. Мы знаем, что данные за Январь соответствуют ячейке B2, февраль — B3, Март — B4. То есть буква «B» остается неизменной, меняется лишь цифра. Зная это мы просто разобьем текст внутри ссылки ДВССЫЛ не на 2 части, а на три. Добавим дополнительно строку над таблицей и напишем сверху цифры соответствующие ячейки месяца. Пропишем формулу.
=ДВССЫЛ(A3& «!B»&C1), где A3 — это текст «Лист1», «!B» — это неизменный текст и С1 — это цифра 2. Все это объединено с помощью знака & в общий текст «Лист1!B2». Нам также потребуется закрепить столбец A (с помощью знака $), чтобы он не менялся при протягивании формулы вправо и строку 1, чтобы она не менялась при протягивании формулы вниз. Мы получаем следующую итоговую формулу.
=ДВССЫЛ($A3&"!B"&C$1)
которую можно протянуть вправо и вниз.
Заметки по теме:
- Если бы листы назывались у нас Магазин 1, Магазин 2 и так далее, то дополнительный столбец со словами Лист1, Лист2 и так далее нам бы не потребовался.
- Функцию ДВССЫЛ используют часто тогда, когда требуется изменить ссылку на ячейку в формуле, не изменяя саму формулу.
Если вам понравилась статья, пожалуйста, нажмите +1 и «Мне нравится». Так же подписывайтесь на нашу рассылку или вступайте в нашу группу ВКонтакте, чтобы не пропустить наши следующие уроки по Excel
При копировании формул в другое место можно выбрать параметры вложения, специфичные для формулы, в ячейках назначения.
Ниже объясняется, как скопировать и вставить формулу.
Выделите ячейку с формулой, которую хотите скопировать.
Щелкните ячейку, в которую нужно вставить формулу.
Если ячейка находится на другом листе, перейдите на него и выберите эту ячейку.
Чтобы быстро вировать формулу с ее форматированием, нажмите +V. Можно также щелкнуть стрелку рядом с кнопкой Вировать:
При щелчке по стрелке появляется список параметров. Ниже перечислены те из них, что используются чаще всего.
Формулы — для вклеения только формулы, а не форматирования исходной ячейки.
Формулы & числовых форматов— для вклеения только формулы и форматирования числовых данных (например, формат процента, формата валюты и так далее).
Сохранить исходное форматирование— для вклейки формулы, форматирования номеров, шрифта, размера шрифта, границы и затенения исходной ячейки.
В этом шаблоне можно вировать значения, чтобы исключить формулу и в нее просто получить результат.
Другие доступные параметры:
Без границ— для вклеения формулы, форматирования номеров, шрифта, размера шрифта, затенения, но не границы исходного текста.
Сохранить ширину столбцов исходного текста— для вклейки формулы, форматирования номеров, шрифта, размера шрифта, затенения, границы и ширины исходного текста.
Транспон— Используйте этот параметр при копировании более одной ячейки. При копировании ячеек, которые находятся рядом по строке, эти ячейки будут вкопироваться в столбец. Если ячейки находятся в столбце, они будут вклеиться в строку рядом. Этот параметр позволяет в виде вклеить формулу, форматирование номеров, шрифт, размер шрифта, затенение, границу.
Совет: Скопировать формулы в смежные ячейки листа также можно с помощью маркера заполнения.
Проверка и исправление ссылок на ячейки в новом месте
Скопировав формулу в новое место, важно убедиться в том, что все ссылки в ней работают правильно. Ссылки на ячейки могут быть изменены в зависимости от используемого типа абсолютных или относительных ссылок.
Например, если скопировать формулу на две ячейки вниз и справа от ячейки A1, использованные ссылки на ячейки будут обновлены так:
Новая ссылка
$A$1 (абсолютный столбец и абсолютная строка)
A$1 (относительный столбец и абсолютная строка)
$A1 (абсолютный столбец и относительная строка)
A1 (относительный столбец и относительная строка)
Если ссылки на ячейки в формуле не дают нужного результата, попробуйте переключиться на другие типы ссылок.
Выделите ячейку с формулой.
В области формул выберите ссылку, которая вы хотите изменить:
Чтобы переключиться с абсолютного на относительный тип ссылки или обратно, нажмите клавишу F4 и выберите нужный вариант.
Перенос формулы в другое место
В отличие от копировании формулы, при ее перемещении в другое место на том же или другом листе содержащиеся в ней ссылки на ячейки не изменяются независимо от их типа.
Щелкните ячейку с формулой, которую хотите перенести.
Щелкните ячейку, в которую нужно вставить формулу.
Если ячейка находится на другом листе, перейдите на него и выберите эту ячейку.
Чтобы вировать формулу с форматированием, на вкладке Главная нажмите кнопку В виде вкладки или нажмите +V.
Чтобы воспользоваться другими параметрами вставки, щелкните стрелку под кнопкой Вставить. Доступно несколько вариантов, но чаще всего используются перечисленные ниже.
Сохранить исходное форматирование: вставка только формул, форматов чисел, атрибутов шрифта и его размера, границ и заливки исходной ячейки.
Значения & с исходным форматированием, чтобы в нее вклеились только значения и форматирование. Формула не вставляется.
Важно помнить о возможностях изменения относительной ссылки на ячейку при переходе или копировании формулы.
Перемещение формулы При этом ссылки на ячейки в формуле не изменяются независимо от типа ссылки на ячейку.
Копирование формулы: При копировании формулы изменяются относительные ссылки на ячейки.
Перемещение формулы
Выделите ячейку с формулой, которую необходимо переместить.
В группе Буфер обмена на вкладке Главная нажмите кнопку Вырезать.
Формулы можно скопировать и путем перетаскивания границы выделенной ячейки в левую верхнюю ячейку области вставки. При этом будут заменяться все существующие данные.
Выполните одно из указанных ниже действий.
Чтобы вировать формулу иформатирование: в группе Буфер обмена на вкладке Главная нажмите кнопку В виде вкладки.
Чтобы вировать только формулу:в группе Буфер обмена на вкладке Главная нажмите кнопку В paste(Главная), выберите специальная ветвь ищелкните Формулы.
Копирование формулы
Вы выберите ячейку, содержащую формулу, которую нужно скопировать.
В группе Буфер обмена на вкладке Главная нажмите кнопку Копировать.
Выполните одно из указанных ниже действий.
Чтобы вировать формулу и любое форматирование, в группе Буфер обмена на вкладке Главная нажмите кнопку В виде вкладки.
Чтобы вировать только формулу, в группе Буфер обмена на вкладке Главная нажмите кнопку Вировать ,выберите специальная ветвь ,а затем щелкните Формулы.
Примечание: В нее можно вклеить только результаты формулы. В группе Буфер обмена на вкладке Главная нажмите кнопку Вировать, выберите специальная ветвь ,а затем щелкните Значения.
Убедитесь, что ссылки на ячейки в формуле дают нужный результат. При необходимости переключить тип ссылки можно следующим образом:
Выделите ячейку с формулой.
В строке формул строка формул выделите ссылку, которую нужно изменить.
Для переключения между сочетаниями нажмите F4.
В таблице подводится информация об обновлении типа ссылки при копировании формулы, содержащей ссылку, на две ячейки вниз и на две ячейки вправо.
Копируемая формула
Первоначальная ссылка
Новая ссылка
$A$1 (абсолютный столбец и абсолютная строка)
A$1 (относительный столбец и абсолютная строка)
$A1 (абсолютный столбец и относительная строка)
A1 (относительный столбец и относительная строка)
Примечание: Формулы также можно копировать в смежные ячейки с помощью маркер заполнения . После проверки на то, что ссылки на ячейки в формуле дают нужный результат в шаге 4, вы выберите ячейку со скопированной формулой и перетащите его по диапазону, который вы хотите заполнить.
Перемещение формул очень похоже на перемещение данных в ячейках. Единственное, что нужно знать, — ссылки на ячейки, используемые в формуле, остаются нужными после перемещения.
Вы выберите ячейку с формулой, которую вы хотите переместить.
Щелкните Главная > вырезать (или нажмите CTRL+X).
Выйдите из ячейки, в которая должна вться формула, и нажмите кнопку Вировать (или нажмите CTRL+V).
Убедитесь, что ссылки на ячейки остаются нужными.
Совет: Вы также можете щелкнуть ячейки правой кнопкой мыши, чтобы вырезать и врезать формулу.
Совет: Формулу также можно скопировать и вкопировать в другую ячейку или на другой таблицу.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Ссылка на ячейку в другом файле Excel, как сложить ячейки из разных файлов в excel, как сделать ссылку на ячейку в другом файле excel, как вставить ссылку на ячейку в другом файле excel, как добавить ссылку на ячейку в другом файле excel
Когда имеется много файлов с различными данными в Экселе, для удобства анализа все данные могут сводится в одну таблицу в совершенно другом документе Эксель. Для этого всего лишь потребуется в формуле ссылка на ячейку в другом файле Excel.
Сделать ссылку на ячейку в другом файле Excel довольно просто. Если предполагается, что книга, из которой будут браться значения, будет в этот момент всегда открыта, то после знака равно в формуле в квадратных скобках указывается полное название файла с расширением, затем указывается название листа, на котором размещается ячейка, и после восклицательного знака указывается адрес конкретной ячейки.
Если в названии файла присутствуют пробелы, то название файла с названием листа дополнительно берутся в одинарные кавычки.
Чтобы Эксель всегда мог взять данные из файла, даже если он будет закрыт, в адресе необходимо указывать полный путь к файлу.
В приложении Microsoft Office Excel возможно использовать данные не только открытой рабочей таблицы, но и задействовать данные других файлов Excel. Кроме того, если имеется несколько файлов Excel с разными сведениями, для удобства систематизации и анализа информации можно создать отдельный документ, который будет обобщать и производить необходимые расчеты с информацией этих документов.
С данной целью применяются Excel ссылки на ячейки в другом файле, которые представляют собой адрес ячейки или диапазона ячеек другой рабочей таблицы.
Типы ссылок
От типа ссылки зависит, на какие значения будет указывать ссылка при ее перемещении:
- Относительная ссылка показывает значение ячейки, удаленной от самой себя на одинаковое расстояние. При перемещении такой ссылки на один столбец вправо, ее значение будет соответствовать значению ячейки, расположенной на один столбец правее изначальной. Приложение Microsoft Office Excel обычно по умолчанию использует этот вид ссылок.
- Абсолютная ссылка показывает значение одной и той же ячейки исходных данных. Создается она путем некоторой корректировки готовой формулы. Для этого в ссылке перед номером столбца и строки устанавливается символ $. Если символ доллара устанавливается только перед номером столбца/строки, то с перемещением ссылки ее значения будут меняться в пределах указанного столбца/строки исходных данных.
- Смешанная ссылка включает в себя оба способа использования исходных данных.
При создании ссылки «Эксель» на ячейку другого файла следует использовать имя для ссылки на используемые ячейки. С применением имени использование значений необходимых ячеек упрощается. Такие ссылки не изменяются при перемещении исходных данных либо самих ссылок, так как имя ссылается на конкретный диапазон ячеек.
Процесс создания ссылки на ячейку в другом файле
- Открываем исходный файл Excel, в котором проводится работа, и второй, данные которого необходимо использовать в текущем файле.
- В исходном выделяем ячейку, в которой требуется создать ссылку, то есть использовать значения другого документа.
- Вводим в ячейку знак равенства. Если с данными ссылки необходимо выполнить вычисления, то далее вносится функция либо другие значения, которые должны предшествовать значению ссылки.
- Переходим ко второму файлу с необходимыми нам данными, выбираем лист документа, содержащий ячейки, на которые нужно сослаться.
- Выделяем ячейку или группу ячеек, на данные которых требуется создать ссылку.
- Если формула предполагает внесение дополнительных функций или данных, то переходим к исходному документу и дополняем формулу, после чего завершаем операцию нажатием клавиш Ctrl+Shift+Enter.
- В исходном файле просматриваем итоговую формулу, при необходимости корректируем ее, после чего снова нажимаем Ctrl+Shift+Enter.
Внешний вид Excel ссылки на ячейку в другом файле
- Ссылка на значение одной ячейки: =Лист1!А1
- Ссылка на значение диапазона ячеек: =Лист1!А1:В5
Такой вид будут они иметь, если файл, данные которого используются для ссылки, будет открыт во время работы.
Для того чтобы использовать данные файла, который в данный момент закрыт, в ссылке необходимо указывать полный путь к файлу:
Обновление данных в файле
Если оба используемых документа открыты, а в исходном изменяются значения ячеек, которые используются в ссылке Excel на ячейку в другом файле, то происходит автоматическая замена данных и, соответственно, пересчет формул в текущем файле, содержащем ссылку.
Кроме создания ссылок на ячейки в другом файле, в Excel реализовано еще множество различных возможностей, позволяющих сделать процесс работы намного удобнее и быстрее.
Гиперссылки широко используются в Интернете для навигации по сайтам и документам. Работая с файлами Excel вы также можете создавать гиперссылки, как на интернет-ресурсы, так и на ячейки, файлы или форму отправку Email.
Гиперссылка в Excel это ссылка, нажав на которую, пользователь может быть перемещен на конкретную ячейку, документ или интернет-страницу.
Excel позволяет создавать гиперссылки для:
Гиперссылку в Excel легко заметить, она выглядит как подчеркнутый текст, выделенный синим цветом:
В Excel существует два типа гиперссылок: абсолютные и относительные.
Абсолютные гиперссылки содержат в себе полный интернет-адрес или полный путь на компьютере. Например:
Относительные ссылки содержат в себе частичный путь, например:
Я рекомендую всегда использовать абсолютные ссылки, так как при переходе по относительным ссылкам в Excel-файле, открытом на другом компьютере возможны ошибки.
Чтобы создать гиперссылку проделайте следующие шаги:
- Выделите ячейку, в которой вы хотите создать гиперссылку;
- Нажмите правую клавишу мыши;
- В выпадающем меню выберите пункт “Ссылка”:
- В диалоговом окне выберите файл или введите веб-адрес ссылки в поле “Адрес”:
- Нажмите “ОК”
Ниже, мы подробней разберем как создать гиперссылку:
- На другой документ;
- На веб-страницу;
- На конкретную область в текущем документе;
- На новую рабочую книгу Excel;
- На окно отправки Email.
Чтобы указать гиперссылку на другой документ, например Excel, Word или Powerpoint файлы:
- Откройте диалоговое окно для создания гиперссылки;
- В разделе “Связать с” выберите “Файлом, веб-страницей”;
- В поле “Искать в” выберите папку, в которой лежит файл, на который вы хотите создать ссылку;
- В поле “Текст” введите текст, который будет отображаться в качестве ссылки;
- Нажмите “ОК”.
Созданная вами гиперссылка будет выглядить ровно так, как вы настроили ее отображение.
Чтобы указать гиперссылку веб-страницу:
- Откройте диалоговое окно для создания гиперссылки;
- В разделе “Связать с” выберите пункт “Файлом, веб-страницей”;
- Нажмите на кнопку “Интернет”;
- Введите адрес веб-страницы в поле “Адрес”;
- В поле “Текст” укажите текст, отображаемый в виде ссылки.
Для создания гиперссылки на конкретный лист текущего файла Excel или ячейки:
- Откройте диалоговое окно для создания гиперссылки;
- В левой колонке диалогового окна под надписью “Связать с” выберите “Файлом, веб-страницей”;
- В диалоговом окне нажмите кнопку “Закладка…” и выберите лист создания ссылки. В поле “Введите адрес ячейки” укажите ячейку.
Для вставки гиперссылки, после нажатия на которую будет создан новый Excel-файл:
Для вставки гиперссылки, после нажатия на которую будет создан e-mail:
Как редактировать гиперсcылку в Excel
Для редактирования уже созданной гиперссылки, кликните по ячейке с ссылкой правой клавишей мыши и в выпадающем меню выберите “Edit Hyperlink”.
В диалоговом окне внесите корректировки в ссылку.
По-умолчанию, все гиперссылки в 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 был бы одной большой таблицей предназначенной для ручного заполнения данными. Благодаря функциям и формулам он является мощным вычислительным инструментом. А полученные результаты, динамически представляет в желаемом виде (если нужно даже в графическом).
Предположим, что у нас есть вот такая несложная таблица, в которой подсчитываются суммы по каждому месяцу в двух городах, а затем итог переводится в евро по курсу из желтой ячейки J2.
Проблема в том, что если скопировать диапазон D2:D8 с формулами куда-нибудь в другое место на лист, то Microsoft Excel автоматически скорректирует ссылки в этих формулах, сдвинув их на новое место и перестав считать:
Задача: скопировать диапазон с формулами так, чтобы формулы не изменились и остались теми же самыми, сохранив результаты расчета.
Способ 1. Абсолютные ссылки
Как можно заметить по предыдущей картинке, Excel сдвигает только относительные ссылки. Абсолютная (со знаками $) ссылка на желтую ячейку $J$2 не сместилась. Поэтому для точного копирования формул можно временно перевести все ссылки во всех формулах в абсолютные. Нужно будет выделить каждую формулу в строке формул и нажать клавишу F4:
Способ 2. Временная деактивация формул
- Выделяем диапазон с формулами (в нашем примере D2:D8)
- Жмем Ctrl+H на клавиатуре или на вкладке Главная - Найти и выделить - Заменить (Home - Find&Select - Replace)
Способ 3. Копирование через Блокнот
Этот способ существенно быстрее и проще.
Нажмите сочетание клавиш Ctrl+Ё или кнопку Показать формулы на вкладке Формулы (Formulas - Show formulas) , чтобы включить режим проверки формул - в ячейках вместо результатов начнут отображаться формулы, по которым они посчитаны:
Скопируйте наш диапазон D2:D8 и вставьте его в стандартный Блокнот:
Теперь выделите все вставленное (Ctrl+A), скопируйте в буфер еще раз (Ctrl+C) и вставьте на лист в нужное вам место:
Осталось только отжать кнопку Показать формулы (Show Formulas) , чтобы вернуть Excel в обычный режим.
Примечание: этот способ иногда дает сбой на сложных таблицах с объединенными ячейками, но в подавляющем большинстве случаев - работает отлично.
Способ 4. Макрос
Если подобное копирование формул без сдвига ссылок вам приходится делать часто, то имеет смысл использовать для этого макрос. Нажмите сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , вставьте новый модуль через меню Insert - Module и скопируйте туда текст вот такого макроса:
Для запуска макроса можно воспользоваться кнопкой Макросы на вкладке Разработчик (Developer - Macros) или сочетанием клавиш Alt+F8. После запуска макрос попросит вас выделить диапазон с исходными формулами и диапазон вставки и произведет точное копирование формул автоматически:
Читайте также: