Поменять ссылки на другие листы в формулах excel

Работа с гиперссылками в Excel

Синтаксис

ГИПЕРССЫЛКА(адрес;имя)

Адрес     — путь и имя файла для документа, который нужно открыть как текст. Адрес может ссылаться на определенное место в документе, например на ячейку или именованный диапазон листа или книги Excel либо на закладку в документе Microsoft Word. Путь может указывать на файл, хранящийся на жестком диске, или путь может представлять собой путь на сервере (в Microsoft Excel для Windows) или путь URL-адреса в Интернете или интрасети.

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

• Если переход, указанный в аргументе «адрес», не существует или переход невозможен, при щелчке по ячейке появляется сообщение об ошибке.

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

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

• Если аргумент «имя» возвращает значение ошибки (например, #ЗНАЧ!), вместо текста ссылки в ячейке отображается значение ошибки.

Примеры

В следующем примере открывается лист с именем Budget Report. xls, хранящийся в Интернете по адресу example.microsoft.com/report , и отображается текст «щелкнуть для отчета».

=HYPERLINK(«http://example.microsoft.com/report/budget report.xls», «Click for report»)

В следующем примере показано, как создать гиперссылку на ячейку F10 на листе Budget Report. xls, который хранится в Интернете, в расположении с именем example.microsoft.com/report. В ячейке листа, содержащей гиперссылку, в качестве адреса перехода отображается содержимое ячейки D1:

=HYPERLINK(«[http://example.microsoft.com/report/budget report.xls]Annual!F10», D1)

В следующем примере создается гиперссылка на диапазон с именем Итогиотдел на листе Budget Report. xls, который хранится в Интернете, в расположении с именем example.microsoft.com/report. В ячейке листа, содержащей гиперссылку, отображается текст «Щелкните, чтобы вывести итоги по отделу за первый квартал»:

=HYPERLINK(«[http://example.microsoft.com/report/budget report.xls]First Quarter!DeptTotal», «Click to see First Quarter Department Total»)

Чтобы создать гиперссылку на определенное место в документе Microsoft Word, необходимо использовать закладку для определения места, на которое нужно перейти в документе. В следующем примере создается гиперссылка на закладку с именем КвартПриб в документе с именем годовой отчет. doc, расположенном по адресу example.microsoft.com:

=HYPERLINK(«[http://example.microsoft.com/Annual Report.doc]QrtlyProfits», «Quarterly Profit Report»)

В Excel для Windows в приведенном ниже примере содержимое ячейки D5 в качестве текста ссылки для перехода в ячейку и открывается файл с именем 1stqtr. xls, хранящийся на сервере с именем FINANCE в инструкциях общего доступа. В данном примере используется путь в формате UNC.

=HYPERLINK(«\\FINANCE\Statements\1stqtr.xls», D5)

В следующем примере открывается файл 1stqtr. xls в Excel для Windows, хранящийся в каталоге Finance на диске D, и отображается числовое значение, хранящееся в ячейке h20.

=HYPERLINK(«D:\FINANCE\1stqtr.xls», h20)

В Excel для Windows в приведенном ниже примере создается гиперссылка на область «итоги» в другой (внешней) книге, Мибук. xls.

=HYPERLINK(«Totals»)

В Microsoft Excel для Macintosh в следующем примере отображается текст «щелкните здесь» в ячейке, а затем открывается файл с именем «первый квартал», хранящийся в папке «отчеты бюджета» на жестком диске с именем «Macintosh HD».

=HYPERLINK(«Macintosh HD:Budget Reports:First Quarter», «Click here»)

Вы можете создавать гиперссылки на листе для перехода от одной ячейки к другой. Например, если в книге «Бюджет» активным является лист «Июнь», приведенная ниже формула создаст гиперссылку на ячейку E56. В качестве текста гиперссылки используется значение, содержащееся в ячейке E56.

=HYPERLINK(«June!E56», E56)

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

Как добавить гиперссылку в excel – несколько способов

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

Как добавить

Существует несколько методов добавления гиперссылки:

1. При помощи одноименной кнопки, расположенной во вкладке Вставка на Панели инструментов.

1. Используя горячие клавиши Ctrl+K

В обоих случаях откроется окно настроек гиперссылки.

1. Через строку формул с использованием функции ГИПЕРССЫЛКА, которая содержит в себе два аргумента: адрес и имя. Первый служит для ссылки на объект или часть рабочего листа, а второй – для подписи связи.

Примеры гиперссылок

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

Ссылка на другой лист делается следующим образом:

1. Выбираете ячейку на листе, нажимаете горячие клавиши Ctrl+K и в появившемся окне выбираете Связать с Местом в документе.
2. Затем в поле, где перечисляются листы рабочей книги, выбираете нужный.
3. Вводите адрес конкретной ячейки с учетом нумерации по строке и столбцу.
4. В строку текста вписываете подпись гиперссылки.
5. Подтверждаете действие кнопкой ОК.

1. Получаете следующее

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

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

1. Переходите в меню создания гиперссылки.
2. Выбираете Связать с файлом и нажимаете на кнопку открывающейся папки.

1. Указываете, например, на файл pdf внутри файловой системы и нажимаете ОК. Обязательно включите отображение всех файлов.

1. Строка адрес заполнилась путем к документу и дублировалась в поле текста. Подпись можно изменить при желании на более информативную.

1. Результат

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

В pdf-редакторе открывается нужный документ.

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

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

Рассмотрим отдельно работу со специальной функцией excel – ГИПЕРССЫЛКА. Сделаем ссылку на ячейку другого листа. Для этого в строке формул записываете следующее:

где первая часть – адрес ячейки, а вторая – имя связи с другим листом.

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

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

Форматирование и удаление

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

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

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

1. Просто нажимаете Del или Backspace на клавиатуре в ячейке со ссылкой.
2. Щелкаете правой клавишей мыши и из выпадающего списка выбираете необходимую строку

После этого надпись становится черной и без нижней черты.

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

Ссылка на ячейку в другом листе 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\Лист1′!B2.

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

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

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

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

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

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

Гиперссылка в Excel — создание, изменение и удаление

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

Существует четыре способа добавить гиперссылку в рабочую книгу Excel:

1) Напрямую в ячейку

2) C помощью объектов рабочего листа (фигур, диаграмм, WordArt…)

3) C помощью функции ГИПЕРССЫЛКА

4) Используя макросы

Добавление гиперссылки напрямую в ячейку

Чтобы добавить гиперссылку напрямую в ячейку, щелкните правой кнопкой мыши по ячейке, в которую вы хотите поместить гиперссылку, из раскрывающегося меню выберите Гиперссылка

Либо, аналогичную команду можно найти на ленте рабочей книги Вставка -> Ссылки -> Гиперссылка.

Привязка гиперссылок к объектам рабочего листа

Вы также можете добавить гиперссылку к некоторым объектам рабочей книги: картинкам, фигурам, надписям, объектам WordArt и диаграммам. Чтобы создать гиперссылку, щелкните правой кнопкой мыши по объекту, из выпадающего меню выберите Гиперссылка.

Либо, аналогичным способом, как добавлялась гиперссылка в ячейку, выделить объект и выбрать команду на ленте. Другой способ создания – сочетание клавиш Ctrl + K – открывает то же диалоговое окно.

Обратите внимание, щелчок правой кнопкой мыши на диаграмме не даст возможность выбора команды гиперссылки, поэтому выделите диаграмму и нажмите Ctrl + K

Добавление гиперссылок с помощью формулы ГИПЕРССЫЛКА

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

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

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

Например, если я введу в ячейку формулу =ГИПЕРССЫЛКА(Лист2!A1; «Продажи»). На листе выглядеть она будет следующим образом и отправит меня на ячейку A1 листа 2.

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

=ГИПЕРССЫЛКА(«https://exceltip.ru/»;»Перейти на Exceltip»)

Чтобы отправить письмо на указанный адрес, в функцию необходимо добавить ключевое слово mailto:

Добавление гиперссылок с помощью макросов

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

где,

SheetName: Имя листа, где будет размещена гиперссылка

Range: Ячейка, где будет размещена гиперссылка

Address!Range Адрес ячейки, куда будет отправлять гиперссылка

Name Текст, отображаемый в ячейке.

Виды гиперссылок

При добавлении гиперссылки напрямую в ячейку (первый способ), вы будете работать с диалоговым окном Вставка гиперссылки, где будет предложено 4 способа связи:

1) Файл, веб-страница – в навигационном поле справа указываем файл, который необходимо открыть при щелчке на гиперссылку

2) Место в документе – в данном случае, гиперссылка отправит нас на указанное место в текущей рабочей книге

3) Новый документ – в этом случае Excel создаст новый документ указанного расширения в указанном месте

4) Электронная почта – откроет окно пустого письма, с указанным в гиперссылке адресом получателя.

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

Изменить гиперссылку

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

Удалить гиперссылку

Аналогичным способом можно удалить гиперссылку. Щелкнув правой кнопкой мыши и выбрав из всплывающего меню Удалить гиперссылку.

Как преобразовать кучу текстовых URL-адресов в активные гиперссылки в Excel?

Если у вас есть список URL-адресов, которые представляют собой обычный текст, как вы могли бы активировать эти текстовые URL-адреса для интерактивных гиперссылок, как показано на следующем снимке экрана?

Преобразование набора текстовых URL-адресов в активные гиперссылки с формулами

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

Введите эту формулу: = ГИПЕРССЫЛКА (A2; A2) в пустую ячейку, в которую вы хотите вывести результат, а затем перетащите дескриптор заполнения вниз, чтобы применить эту формулу к ячейкам, которые вы хотите, и все текстовые URL-адреса были преобразованы в активные гиперссылки, см. снимок экрана:

Преобразуйте кучу текстовых URL-адресов в активные гиперссылки с кодом VBA

Приведенный ниже код VBA также может помочь вам решить эту задачу, пожалуйста, сделайте следующее:

1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модульи вставьте следующий код в окно модуля.

Код VBA: преобразование кучи текстовых URL-адресов в активные гиперссылки:

Sub activateHyperlinks()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Application.ActiveSheet.Hyperlinks.Add Rng, Rng.Value
Next
End Sub

3, Затем нажмите F5 нажмите клавишу для запуска этого кода, и появится окно подсказки, напоминающее вам о выборе ячеек, которые вы хотите преобразовать в интерактивные гиперссылки, см. снимок экрана:

4, Затем нажмите OK , URL-адреса в виде обычного текста были преобразованы в активные гиперссылки, см. снимок экрана:

Преобразование кучи текстовых URL-адресов в активные гиперссылки Kutools for Excel

Вот удобный инструмент -Kutools for Excel, С его Конвертировать гиперссылки функция, вы можете быстро и преобразовать кучу текстовых URL-адресов в интерактивные гиперссылки и извлекать реальные адреса гиперссылок из текстовой строки гиперссылки.

Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 30 дней.

Перейти к загрузкеБесплатная пробная версия 30 днейпокупкаPayPal / MyCommerce

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выделите ячейки, содержащие текстовые URL-адреса, которые вы хотите активировать.

2. Затем нажмите Kutools > Ссылка > Конвертировать гиперссылки, см. снимок экрана:

3. В Конвертировать гиперссылки диалоговое окно, выберите Содержимое ячейки заменяет адреса гиперссылок вариант под Тип преобразования раздел, а затем проверьте Преобразовать исходный код range, если вы хотите поместить фактические адреса в исходный диапазон, см. снимок экрана:

4. Затем нажмите Ok Кнопка, и текстовые URL-адреса были активированы сразу, см. снимок экрана:

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

Создание гиперссылок

Гиперссылки позволяют не только «вытащить» информацию из ячеек, но и осуществить переход на ссылаемый элемент. Пошаговое руководство по созданию гиперссылки:

1. Первоначально необходимо попасть в специальное окошко, позволяющее создать гиперссылку. Существует множество вариантов реализации этого действия. Первый – жмем ПКМ по необходимой ячейке и в контекстном меню выбираем элемент «Ссылка…». Второй – выбираем нужную ячейку, перемещаемся в раздел «Вставка» и выбираем элемент «Ссылка». Третий – используем комбинацию клавиш «CTRL+K».

3435

2. На экране отобразилось окошко, позволяющее настроить гиперссылку. Здесь существует выбор из нескольких объектов. Более детально рассмотрим каждый вариант.

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

Пошаговое руководство:

1. Производим открытие окошка для создания гиперссылки.
2. В строчке «Связать» выбираем элемент «Файлом, веб-страницей».
3. В строчке «Искать в» осуществляем выбор папки, в которой располагается файл, на который мы планируем сделать линк.
4. В строчке «Текст» осуществляем ввод текстовой информации, которая будет показываться вместо ссылки.
5. После проведения всех манипуляций щелкаем на «ОК».

36

Как создать гиперссылку в Excel на веб-страницу

Пошаговое руководство:

1. Производим открытие окошка для создания гиперссылки.
2. В строке «Связать» выбираем элемент «Файлом, веб-страницей».
3. Щёлкаем на кнопку «Интернет».
4. В строчку «Адрес» вбиваем адрес интернет-странички.
5. В строчке «Текст» осуществляем ввод текстовой информации, которая будет показываться вместо ссылки.
6. После проведения всех манипуляций щелкаем на «ОК».

37

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

Пошаговое руководство:

1. Производим открытие окошка для создания гиперссылки.
2. В строчке «Связать» выбираем элемент «Файлом, веб-страницей».
3. Нажимаем на «Закладка…» и осуществляем выбор рабочего листа для создания ссылки.
4. После проведения всех манипуляций щелкаем на «ОК».

38

Как создать гиперссылку в Excel на новую рабочую книгу

Пошаговое руководство:

1. Производим открытие окошка для создания гиперссылки.
2. В строчке «Связать» выбираем элемент «Новый документ».
3. В строчке «Текст» осуществляем ввод текстовой информации, которая будет показываться вместо ссылки.
4. В строку «Имя нового документа» вводим наименование нового табличного документа.
5. В строчке «Путь» указываем локацию для осуществления сохранения нового документа.
6. В строчке «Когда вносить правку в новый документ» выбираем наиболее удобный для себя параметр.
7. После проведения всех манипуляций щелкаем на «ОК».

39

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

Пошаговое руководство:

1. Производим открытие окошка для создания гиперссылки.
2. В строке «Связать» выбираем элемент «Электронная почта».
3. В строчке «Текст» осуществляем ввод текстовой информации, которая будет показываться вместо ссылки.
4. В строчке «Адрес эл. почты» указываем электронную почту получателя.
5. В строку «Тема» вводим наименование письма
6. После проведения всех манипуляций щелкаем на «ОК».

40

Обновление данных в файле

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

Если данные архива исходного файла изменяются, когда документ со ссылкой закрывается, то при последующей открытии этого файла программа выдает сообщение с предложением обновить связь. Следует согласиться и выбрать пункт «Обновить». Таким образом, при открытии документа ссылка использует обновленные данные исходного файла, а пересчет формулы с ее использованием автоматически.

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

Динамическая гиперссылка в Excel

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

Исходная таблица:

Для создания ссылки на ресурс http://valuta.pw/ в ячейке D7 введем следующую формулу:

Описание параметров:

• http://valuta.pw/ – URL адрес требуемого сайта;
• “Курсы валют” – текст, отображаемый в гиперссылке.

В результате получим:

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

Как изменить относительные формулы на абсолютные формулы в Excel 2013 — манекены 2019

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

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

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

Например, на рабочем листе «Матушка гусиных предприятий — 2013» вы сталкиваетесь с этой ситуацией при создании и копировании формулы, которая вычисляет, какой процент каждого ежемесячного итога (в диапазоне ячеек B14: D14) равен ежеквартальный итог в ячейке E12.

Предположим, что вы хотите ввести эти формулы в строке 14 листа рабочих матов Goose Enterprises — 2013 Sales, начиная с ячейки B14. Формула в ячейке B14 для расчета процента от января-продажи к первому кварталу является очень простой:

= B12 / E12

Эта формула делит январскую сумму продаж в ячейке B12 на квартальную сумму в E12 (что может быть проще?). Посмотрите, однако, на то, что произойдет, если вы перетащили дескриптор заполнения в одну ячейку вправо, чтобы скопировать эту формулу в ячейку C14:

= C12 / F12

Настройка первой ссылки на ячейку от B12 до C12 — это просто что доктор заказал. Однако корректировка ссылки на вторую ячейку от E12 до F12 является катастрофой. Вы не только не подсчитаете, какой процент февральских продаж в ячейке C12 приходится на продажи в первом квартале в E12, но вы также получаете один из этих ужасных # DIV / 0! ошибки в ячейке C14.

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

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

Например, ячейка B14 содержит правильную формулу для копирования в диапазон ячеек C14: D14:

= B12 / $ E $ 12

Посмотрите на рабочий лист после того, как эта формула будет скопирована в диапазон C14: D14 с дескриптором заполнения и выбирается ячейка C14

Обратите внимание, что панель формул показывает, что эта ячейка содержит следующую формулу:. = C12 / $ E $ 12

= C12 / $ E $ 12

Поскольку E12 был изменен на $ E $ 12 в исходной формуле, все копии имеют такой же абсолютный (не изменяющийся ) Справка.

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

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

2. Расположите точку вставки где-нибудь на ссылке, которую вы хотите преобразовать в абсолютную.

3. Нажмите F4.

4. Когда вы закончите редактирование, нажмите кнопку «Ввод» на панели формул, а затем скопируйте формулу в диапазон перепутанных ячеек с помощью дескриптора заполнения.

Обязательно нажмите F4 только один раз, чтобы полностью изменить абсолютную абсолютную величину ссылки на ячейку. Если вы снова нажмете функциональную клавишу F4, вы получите так называемую ссылку , , где только часть строки является абсолютной, а часть столбца является относительной (как в E $ 12).

Если вы снова нажмете F4, в Excel появится другой тип смешанной ссылки, где часть столбца будет абсолютной, а часть строки будет относительной (как в $ E12).

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

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

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

Выберите свой курс (вас может заинтересует больше от Excel 2013 ), заполните краткую регистрацию, а затем дайте eLearning спину с помощью Try It! кнопка.

Вы будете правы на курсе для более надежных ноу-хау: полная версия также доступна в Excel 2013 .

Трюк №64. Как сделать, чтобы формула ссылалась на следующие строки при копировании по столбцам

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

Если создать ссылку на одну ячейку, например, А1, а затем скопировать ее вправо по столбцам, то в результате эта ссылка будет меняться: =В1, =С1, =D1 и т. д.

, но на самом деле вы хотите получить другой результат. Нужно, чтобы приращение формулы шло по строкам, а не по столбцам, то есть =А1, =А2, =А3 и т. д. К сожалению, в Excel такая возможность не предусмотрена. Но можно обойти это ограничение с помощью функции ДВССЫЛ (INDIRECT), в которую вложена функция АДРЕС (ADDRESS).

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

Выделите ячейку D1 и введите в ней следующую формулу: =INDIRECT(ADDRESS(COLUMN()-3;1), в русской версии Excel =ДВССЫЛ(АДРЕС(СТОЛБЕЦ()-3;1)). Когда вы сделаете это, в ячейке D1 должно появиться число 1. Это происходит потому, что формула ссылается на ячейку А1.

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

Чтобы продемонстрировать, о чем идет речь, будем использовать необходимую всем функцию СУММ (SUM). Предположим, вы получили длинный список чисел, и необходимо просуммировать столбцы чисел в режиме промежуточной суммы: =SUM($A$1:$A$2). =SUM($A$1:$A$3). =SUM($A$1:$A$4), в русской версии Excel =СУММС$А$1:$А$2). =СУММ($А$1:$А$3). =СУММ($А$1:$А$4).

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

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

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

Заполните диапазон А1:А100 числами от 1 до 100 по порядку. В ячейку А1 введите 1, выделите ячейку А1 и, удерживая клавишу Ctrl, щелкните маркер заполнения и протащите «го вниз на 100 строк.

Выделите ячейку D1 и введите следующую формулу: =SUM(INDIRECT(ADDRESS(1;1)&»:»&ADDRESS(COLUMN()-2;1))), в русской версии Excel =СУММ(ДВССЫЛ(АДРЕС(1;1)&»:»&АДРЕС(СТОЛБЕЦ()-2;1))). В результате вы получите 3 как сумму ячеек А1:А2.

Переменная функция СТОЛБЕЦ (COLUMN) заставляет последнюю ссылку на ячейку увеличиваться на 1 каждый раз, когда вы копируете формулу в новый столбец.

Функция СТОЛБЕЦ (COLUMN) всегда возвращает номер столбца (а не букву) в ту ячейку, в которой находится, если только вы не ссылаетесь на другую ячейку. Иначе, можно было бы воспользоваться средством Excel Специальная вставка → Транспонировать (Paste Special → Transpose).

Введите формулу =SUM($A$1:$A2) в ячейку В1 (обратите внимание на относительную ссылку на строку и абсолютную ссылку на столбец в ссылке $А2) и скопируйте эту формулу вниз до ячейки В100. Выделив диапазон В2:В100, скопируйте его, выделите ячейку D1 (или любую другую, справа от которой есть 100 или более столбцов) и выберите команду Специальная вставка → Транспонировать (Paste Special → Transpose)

Если необходимо, можно удалить формулы в диапазоне В2:В100

Выделив диапазон В2:В100, скопируйте его, выделите ячейку D1 (или любую другую, справа от которой есть 100 или более столбцов) и выберите команду Специальная вставка → Транспонировать (Paste Special → Transpose). Если необходимо, можно удалить формулы в диапазоне В2:В100.

Запретить Excel автоматически создавать гиперссылки

Пока что мы лечим симптомы.

Теперь давайте посмотрим, как определить первопричину проблемы — URL-адреса / электронные письма автоматически преобразуются в гиперссылки.

Причина этого в том, что в Excel есть настройка, которая автоматически преобразует «Интернет и сетевые пути» в гиперссылки.

Вот шаги, чтобы отключить этот параметр в Excel:

1. Перейти к файлу.
2. Щелкните Параметры.
3. В диалоговом окне «Параметры Excel» нажмите «Проверка» на левой панели.
4. Нажмите кнопку «Параметры автозамены».
5. В диалоговом окне «Автозамена» выберите вкладку «Автоформат при вводе».
6. Снимите флажок с опции «Интернет и сетевые пути с гиперссылками».
7. Щелкните ОК.
8. Закройте диалоговое окно параметров Excel.

Если вы выполнили следующие шаги, Excel не будет автоматически преобразовывать URL-адреса, адреса электронной почты и сетевые пути в гиперссылки.

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

Подтяжка данных из закрытых файлов

Помогает при обновлении того или иного отчета одного формата, но за разные периоды времени. 

Как использовать: 

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

Проставляем ссылку на ячейки из ранее скачанных данных. 

Если отчет не меняется — ставим ссылку на конкретную ячейку. 

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

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