Как в эксель сделать ссылку на ячейку на другом листе: Функция ДВССЫЛ и динамическая ссылка на лис Excel

Работа со ссылками в Excel

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

Синтаксис

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

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

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

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

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

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

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

Примеры

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

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

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

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

В следующем примере создается ссылка на диапазон «ОтделПоиск» книги «Бюджетный 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, необходимо с помощью закладки определить место в документе. В следующем примере создается ссылка на закладку «КвартПриб» в документе «Годовые Report.doc, example.microsoft.com:

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

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

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

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

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

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

=HYPERLINK(«[C:\My Documents\Mybook.xls]Totals»)

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

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

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

=HYPERLINK(«[Budget]June!E56», E56)

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

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

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

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

на листе Лист1, ссылка будет выглядеть так: Лист1!A1.

Обратите внимание, если в названии листа содержатся пробелы, то его необходимо заключить в одинарные кавычки (‘ ‘). Например, если вы хотите создать ссылку на ячейку A1, которая находится на листе с названием Бюджет июля. Ссылка будет выглядеть следующим образом: ‘Бюджет июля’!А1.

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

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

1. Найдите ячейку, на которую хотите сослаться, и запомните, где она находится. В нашем примере это ячейка E14 на листе Заказ меню.
2. Перейдите к нужному листу. В нашем примере, мы выберем лист Счет за услуги по питанию
   .
3. Выбранный лист откроется.
4. Найдите и выделите ячейку, в которой должно появиться значение. В нашем примере мы выделим ячейку B2.
5. Введите знак равенства (=), название листа с восклицательным знаком(!) и адрес ячейки. В нашем примере мы введем =’Заказ меню’!E14.
6. Нажмите Enter на клавиатуре. Появится значение, на которое идет ссылка. Если значение ячейки E14 на листе Заказ меню изменить, то и значение на листе Счет за услуги по питанию автоматически обновится.

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

Если Вы введете название листа неправильно, в ячейке появится ошибка #ССЫЛКА! В следующем примере мы допустили опечатку в названии. Рядом с ячейкой, которая содержит ошибку, появился смарт-тег с восклицательным знаком. Нажмите на него и выберите из раскрывающегося списка нужный вам вариант: редактировать или игнорировать ошибку.

Как создать ссылку на другую книгу Excel

Помимо создания ссылок на другие листы, Excel также позволяет создавать ссылки на другие книги. Для этого перед ссылкой на рабочий лист необходимо подставить имя книги в квадратных скобках. Например, если имя книги – Книга1, листа – Лист1, то ссылка на ячейку А1 будет выглядеть следующим образом: =[Книга1.xlsx]Лист1!А1

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

Оцените качество статьи. Нам важно ваше мнение:

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

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

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

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

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

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

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

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

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

Это самый простой и очевидный вариант применения нашей функции

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

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

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

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

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

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

Ссылка на ячейку во внешней книге

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

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

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

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

Ссылка на именованный диапазон

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

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

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

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

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

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

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

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

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

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

Использование нестандартных символов

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

Ссылки по теме

Ссылки в Excel

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

Абсолютная и относительная ссылка на ячейку в Excel

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

Относительная ссылка позволяет изменять адрес ячеек по строкам и столбцам при копировании формулы в другое место документа. То есть, если скопировать формулу из ячейки А3 в ячейку С3, то для расчета суммы возьмутся новые адреса ячеек: С1 и С2.

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

Рассмотрим следующий пример. Есть таблица, в которую внесены: наименование товара, его цена и количество проданных единиц. Посчитаем итоговую сумму для каждой единицы. В ячейку D6 пишем формулу: =В6*С6. Как видите, ссылки на ячейки в формуле относительные.

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

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

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

Чтобы сделать абсолютную ссылку на ячейку в Excel, нужно добавить знак «$» в адрес ячейки перед названием столбца и строки. Или же поставить курсор в строке формул после адреса нужной ячейки и нажать «F4». В примере, для расчета суммы в ячейке А3,   используется теперь абсолютная ссылка на ячейку А1.

Давайте посчитаем сумму для ячеек D1 и D2. В ячейку D3 скопируем формулу из А3. Как видите, результат вместо 24 – 25. Все из-за того, что в формуле была использована абсолютная ссылка на ячейку $A$1. Поэтому в расчете использовались не ячейки D1 и D2, а ячейки $A$1 и D2.

Рассмотрим для примера такую таблицу: есть наименование товара и его себестоимость. Чтобы определить цену товара для продажи, нужно посчитать НДС. НДС – 20%, и значение написано в ячейке В9. Вписываем формулу для расчета в ячейку С6.

Если мы скопируем формулу в остальные ячейки, то не получим результат. Так как в расчете будут использоваться ячейки В10 и В11, которые не заполнены значениями.

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

Если в строке формул поставить курсор после адреса ячейки и нажать «F4» второй и третий раз, то получится смешанная ссылка в Excel. В этом случае, при копировании может не изменяться или строка – А$1, или столбец – $А1.

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

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

Ссылка на ячейку с другого листа в формуле будет выглядеть следующим образом: Лист1!А1 – название листа, знак восклицания, адрес ячейки. Если в названии листа используются пробелы, то его нужно взять в одинарные кавычки: ‘Итоговые суммы’ – ‘Итоговые суммы’!А1.

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

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

Возвращаемся на Лист1. В ячейку С6 пишем формулу для расчета НДС: ставим «=», затем выделяем ячейку В6 и делаем ссылку на ячейку В1 с другого листа.

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

Если изменить название листа Все константы на Все константы1111, то оно автоматически поменяется и в формуле. Точно также, если на листе Все константы изменить значение в ячейке В1 с 20% на 22%, то формула будет пересчитана.

Для того чтобы сделать ссылку на другую книгу Excel в формуле, возьмите ее название в квадратные скобки. Например, сделаем ссылку в ячейке А1 в книге с названием Книга1 на ячейку А3 из книги с названием Ссылки. Для этого ставим в ячейку А1 «=», в квадратных скобках пишем название книги с расширением, затем название листа из этой книги, ставим «!» и адрес ячейки.

Книга, на которую мы ссылаемся, должна быть открыта.

Ссылка на файл или гиперссылка в Excel

В документах Excel иногда появляется необходимость ссылаться на внешние файлы или на другие книги Excel. Реализовать нам это поможет использование гиперссылки.

Выделите ячейку, в которую необходимо вставить гиперссылку. Это может быть как число, так и текст или рассчитанная формула, или пустая ячейка. Перейдите на вкладку «Вставка» и кликните по кнопочке «Гиперссылка».

Сделаем ссылку на другую книгу Эксель. В поле «Связать с» выбираем «файлом, веб-страницей». Найдите нужную папку на компьютере и выделите файл. В поле «Текст» можно изменить надпись, которая будет отображаться в ячейке – это только в том случае, если ячейка изначально была пустая. Нажмите «ОК».

Теперь при нажатии на созданную гиперссылку будет открываться книга Excel с названием Список.

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

---

Оценить статью: Загрузка… Поделиться новостью в соцсетях

 

Об авторе: Олег Каминский

Вебмастер. Высшее образование по специальности «Защита информации». Создатель портала comp-profi.com. Автор большинства статей и уроков компьютерной грамотности

какие типы ссылок бывыют, как создать абсолютную ссылку в экселе

Чаще всего гиперссылки в excel используются при создании прайс-листов

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

Ссылки — это указатели на некоторый объект. Создать такой указатель очень просто: нужно лишь ввести в любое поле знак «=» (равно), а затем выбрать, например, ячейку, на которую необходимо ссылаться. Ссылки всегда будут копировать то значение, которое находится в исходной ячейке. Если исходные данные изменятся, то и результат мгновенно будет обновлен. Например, в поле A1 указано число 5. Необходимо создать ссылку на это число в другом поле — B1. Для этого нужно щелкнуть левой кнопкой мыши на ячейке B1, написать знак равно, выбрать поле A1 и нажать «Enter». В поле B1 тут же появится результат — число 5. Если теперь исходное число в поле A1 изменить, например, написать 12, то результат в ячейке B1 тут же обновится — там тоже будет стоять число 12. То есть ссылки копируют не конкретное число (или слово), которое было написано, а данные, указанные в ячейке. Это очень помогает пользователю при работе с табличным процессором Excel.

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

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

Итак, ссылки — это формулы, которые копируют данные с исходной ячейки (группы ячеек, строки, столбца и т.д.). Они могут быть 2 видов: относительные и абсолютные.

Чаще всего при работе в Excel используются относительные ссылки. Это обычная формула, которая выглядит примерно таким образом: «=B1» или «=Лист1!A1». В первом случае дублируется значение поля B1, а во втором — поля A1 с первого листа рабочей книги Excel. Если такую формулу скопировать, к примеру, потянуть вниз, то она тоже распространится вниз по ячейкам. Если в первом примере скопировать ссылку еще на 2 строки вниз, то результат будет следующим: «=B2» и «=B3».

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

Абсолютные ссылки содержат в себе формулу, которая копирует только одно и то же поле. Абсолютные ссылки содержат в себе фиксированное значение, и если пользователь скопирует эту формулу куда-то еще — значение останется неизменным (оно не распространится вниз или в сторону). Но зачем нужны такие ссылки?

Например, в Excel создана таблица для расчета зарплаты сотрудников. И пользователю необходимо рассчитать каждому сотруднику зарплату, опираясь на исходные данные: количество отработанных часов, почасовая оплата и пр. Формула простая: количество отработанных часов умножается на почасовую оплату. Затем пользователю необходимо скопировать эту формулу для всех остальных сотрудников, к примеру, просто потянув ее вниз. Но в данном случае «почасовая оплата» — это фиксированное значение, которое находится всего в одной ячейке. И если потянуть формулу вниз, то это значение просто сместится вниз на пустое поле (по умолчанию там стоит 0). А если любое число умножить на 0, то получится 0. Посчитать зарплату таким способом не получится. Для этого надо знать, как сделать абсолютную ссылку и зафиксировать значение в поле «Почасовая оплата», чтобы при копировании оно никуда не смещалось.

Делается это несложно: нужно лишь написать обычную ссылку (например, «=A1»), а затем нажать кнопку F4. Теперь формула будет выглядеть так: «=$A$1». Знак доллара означает, что значение зафиксировано, и если пользователь потянет формулу вниз или в сторону — это число останется неизменным.

Можно также указать, чтобы значение поля «Почасовая оплата» сохранялось только при копировании по столбцам, а при копировании по строкам — не сохранялось (или наоборот). Для этого нужно просто еще раз нажать кнопку F4. Если нужно оставить фиксированное значение при копировании формулы по столбцам, то формула будет выглядеть так: «=A$1», а если по строкам — тогда «=$A1». Такие ссылки называются смешанными.

Как сделать в Экселе гиперссылку

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

Существует 3 способа, как сделать в Экселе гиперссылку:

• напрямую в ячейку;
• с помощью объектов;
• с помощью функции.

Достаточно щелкнуть правой кнопкой мыши по любой ячейке и в появившемся списке выбрать пункт «Гиперссылка». Откроется новое окно, в котором необходимо указать адрес сайта или файл на компьютере. Также можно создать ее и через панель меню: «Вставка — Гиперссылка».

Теперь, когда вы знаете, как сделать простую гиперссылку, можно легко создать такую же ссылку с помощью объектов рабочего листа. К ним относятся любые изображения, надписи, фигуры, объекты WordArt, диаграммы и пр. Для этого нужно вызвать контекстное меню на одном из таких объектов и выбрать пункт «Гиперссылка». Кроме того, можно это сделать и с помощью комбинации клавиш — Ctrl+K.

Еще один вариант, как сделать гиперссылку, с помощью встроенной функции. Данная функция имеет следующий синтаксис: =Гиперссылка(адрес; [имя]). Вызвать ее можно 2 способами:

• через панель меню «Вставка — Функция»;
• вручную.

Если воспользоваться первым способом, то сначала в появившемся окне необходимо будет найти функцию «Гиперссылка» (проще всего написать название функции в поле «Поиск функции»). После этого откроется еще одно окно, в котором надо будет заполнить 2 параметра — адрес и имя. Адрес — это местоположение ячейки, диапазона ячеек или объекта в документе. Здесь можно указать и страницу в интернете или путь к файлу на компьютере. А в поле «Имя» указывается наименование, которым будет подписано выбранное поле. Хотя заполнять его необязательно.

Можно сделать все это вручную, выделив необходимое поле и прописав: =гиперссылка(. После открытой скобки нужно будет указать адрес, поставить точку с запятой, указать имя гиперссылки, закрыть скобку и нажать «Enter». Например, можно написать в поле E1 такую формулу: =Гиперссылка(«yandex. ru»; «Открыть Яндекс»). В поле E1 будет написана фраза «Открыть Яндекс», щелкнув по которой вы откроете сайт в браузере.

Кроме того, существует еще один способ, как сделать гиперссылку, с помощью макросов. Но этот вариант слишком сложный: лучше использовать более простые варианты.

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

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

1. Файл, веб-страница (здесь указывается путь к файлу или адрес сайта).
2. Место в документе (лист или ячейка).
3. Новый документ (путь к новому документу).
4. Электронная почта (здесь указывается адрес получателя, который будет отображен при открытии Microsoft Outlook).

Существующие типы гиперссылок

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

Такими довольно простыми способами можно создать гиперссылку в Excel. Наиболее простым и практичным способом является первый — через ячейку. Дело в том, что именно здесь собраны все варианты: можно создать ссылку на любое поле в документе, можно указать путь к файлу, адрес сайта и т.д. Хотя использовать можно любой из этих способов — как удобнее.

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

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

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

Типы ссылок

От типа ссылки зависит, на какие значения будет указывать ссылка при ее перемещении:

1. Относительная ссылка показывает значение ячейки, удаленной от самой себя на одинаковое расстояние. При перемещении такой ссылки на один столбец вправо, ее значение будет соответствовать значению ячейки, расположенной на один столбец правее изначальной. Приложение Microsoft Office Excel обычно по умолчанию использует этот вид ссылок.
2. Абсолютная ссылка показывает значение одной и той же ячейки исходных данных. Создается она путем некоторой корректировки готовой формулы. Для этого в ссылке перед номером столбца и строки устанавливается символ $. Если символ доллара устанавливается только перед номером столбца/строки, то с перемещением ссылки ее значения будут меняться в пределах указанного столбца/строки исходных данных.
3. Смешанная ссылка включает в себя оба способа использования исходных данных.

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

Процесс создания ссылки на ячейку в другом файле

Как происходит?

1. Открываем исходный файл Excel, в котором проводится работа, и второй, данные которого необходимо использовать в текущем файле.
2. В исходном выделяем ячейку, в которой требуется создать ссылку, то есть использовать значения другого документа.
3. Вводим в ячейку знак равенства. Если с данными ссылки необходимо выполнить вычисления, то далее вносится функция либо другие значения, которые должны предшествовать значению ссылки.
4. Переходим ко второму файлу с необходимыми нам данными, выбираем лист документа, содержащий ячейки, на которые нужно сослаться.
5. Выделяем ячейку или группу ячеек, на данные которых требуется создать ссылку.
6. Если формула предполагает внесение дополнительных функций или данных, то переходим к исходному документу и дополняем формулу, после чего завершаем операцию нажатием клавиш Ctrl+Shift+Enter.
7. В исходном файле просматриваем итоговую формулу, при необходимости корректируем ее, после чего снова нажимаем Ctrl+Shift+Enter.

Внешний вид Excel ссылки на ячейку в другом файле

Как выглядит?

1. Ссылка на значение одной ячейки: =[Источник.xls]Лист1!А1
2. Ссылка на значение диапазона ячеек: =[Источник.xls]Лист1!А1:В5

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

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

=D:\Папка\[Источник.xls]Лист1!A1

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

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

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

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

Как заблокировать ячейки в Excel и разблокировать определенные ячейки на защищенном листе

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

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

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

Как заблокировать ячейки в Excel

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

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

Подробные инструкции по блокировке ячеек в Excel 2010, Excel 2013 и Excel 2016 приведены ниже.

1. Разблокируйте все ячейки на листе.

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

• Нажмите Ctrl + A или нажмите кнопку Select All , чтобы выделить весь лист.
• Нажмите Ctrl + 1, чтобы открыть диалоговое окно Формат ячеек (или щелкните правой кнопкой мыши любую из выбранных ячеек и выберите Форматировать ячейки из контекстного меню).
• В диалоговом окне Формат ячеек перейдите на вкладку Защита , снимите флажок Заблокировано и нажмите ОК .

2. Выберите ячейки, диапазоны, столбцы или строки, которые нужно защитить.

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

Чтобы защитить столбцы в Excel, выполните одно из следующих действий:

• Чтобы защитить один столбец , щелкните букву столбца, чтобы выбрать его. Или выберите любую ячейку в столбце, который вы хотите заблокировать, и нажмите Ctrl + Пробел.
• Чтобы выбрать соседних столбцов , щелкните правой кнопкой мыши заголовок первого столбца и перетащите выделение по буквам столбца вправо или влево. Или выберите первый столбец, удерживайте нажатой клавишу Shift и выберите последний столбец.
• Чтобы выбрать несмежных столбцов , щелкните букву первого столбца, удерживайте нажатой клавишу Ctrl и щелкните заголовки других столбцов, которые вы хотите защитить.

Чтобы защитить строки в Excel, выберите их аналогичным образом.

Чтобы заблокировать все ячейки с формулами , перейдите на вкладку Home > Редактирование группы > Найти и выбрать > Перейти к специальному .В диалоговом окне Перейти к специальному установите переключатель Формулы и нажмите кнопку ОК. Подробные инструкции со снимками экрана см. В разделе Как заблокировать и скрыть формулы в Excel.

3. Заблокировать выделенные ячейки.

Выбрав необходимые ячейки, нажмите Ctrl + 1, чтобы открыть диалоговое окно Формат ячеек (или щелкните правой кнопкой мыши выбранные ячейки и выберите Форматировать ячейки ), перейдите на вкладку Защита и установите флажок Заблокировано .

4. Защитите лист.

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

На вкладке Review в группе Changes нажмите кнопку Protect Sheet . Или щелкните правой кнопкой мыши вкладку листа и выберите Защитить лист… в контекстном меню.

Вам будет предложено ввести пароль (необязательно) и выбрать действия, которые вы хотите разрешить пользователям выполнять.Сделайте это и нажмите ОК. Вы можете найти подробные инструкции со скриншотами в этом руководстве: Как защитить лист в Excel.

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

Как разблокировать ячейки в Excel (снять защиту с листа)

Чтобы разблокировать все ячейки на листе, достаточно снять защиту листа. Для этого щелкните правой кнопкой мыши вкладку листа и выберите Снять защиту с листа… из контекстного меню.Или нажмите кнопку Снять защиту листа на вкладке Review в группе Changes :

Для получения дополнительной информации см. Как снять защиту с листа Excel.

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

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

Как разблокировать определенные ячейки на защищенном листе Excel

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

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

1. Выберите ячейки или диапазоны, которые нужно разблокировать паролем, когда лист защищен.
2. Перейдите на вкладку Review > Changes group и щелкните Allow Users to Edit Ranges .
   

   Примечание. Эта функция доступна только для незащищенного листа. Если кнопка Разрешить пользователям редактировать диапазоны неактивна, нажмите кнопку Снять защиту листа на вкладке Проверить .

3. В диалоговом окне Разрешить пользователям редактировать диапазоны нажмите кнопку Новый… , чтобы добавить новый диапазон:
   
4. В диалоговом окне New Range выполните следующие действия:
   • В поле Название введите значащее имя диапазона вместо значения по умолчанию Диапазон1 (необязательно).
   • В поле Относится к ячейкам введите ссылку на ячейку или диапазон. По умолчанию включены текущие выбранные ячейки или диапазон (ы).
   • В поле Пароль диапазона введите пароль. Или вы можете оставить это поле пустым, чтобы позволить всем редактировать диапазон без пароля.
   • Щелкните кнопку ОК.
   Наконечник. В дополнение к разблокировке указанного диапазона паролем или вместо него вы можете предоставить определенным пользователям разрешения на редактирование диапазона без пароля . Для этого нажмите кнопку Permissions… в нижнем левом углу диалогового окна New Range и следуйте этим инструкциям (шаги 3–5).
5. Появится окно Подтвердите пароль и предложит вам повторно ввести пароль. Сделайте это и нажмите ОК .
6. Новый диапазон будет указан в диалоговом окне Разрешить пользователям редактировать диапазоны . Если вы хотите добавить еще несколько диапазонов, повторите шаги 2–5.
7. Нажмите кнопку Защитить лист на кнопке окна, чтобы активировать защиту листа.
   
8. В окне Protect Sheet введите пароль для снятия защиты листа, установите флажки рядом с действиями, которые вы хотите разрешить, и нажмите OK . Наконечник. Рекомендуется защитить лист паролем, отличным от пароля, который вы использовали для разблокировки диапазона (ов).
   
9. В окне подтверждения пароля еще раз введите пароль и нажмите OK. Это оно!

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

Разрешить определенным пользователям редактировать выбранные ячейки без пароля

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

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

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

1. Перейдите на вкладку Review > Changes group и щелкните Allow Users to Edit Ranges .

   Примечание. Если Разрешить пользователям редактировать диапазоны выделен серым цветом, нажмите кнопку Снять защиту листа , чтобы снять защиту листа.

2. В окне Разрешить пользователям редактировать диапазоны выберите диапазон, для которого вы хотите изменить разрешения, и нажмите кнопку Разрешения… .
   

   Наконечник. Кнопка Permissions… также доступна при создании нового диапазона, разблокированного паролем.

3. Откроется окно Permissions , и вы нажмете кнопку Добавить… .
   
4. В поле Введите имена объектов для выбора введите имена пользователей, которым вы хотите разрешить редактировать диапазон.

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

   Например, чтобы позволить себе редактировать диапазон, я ввел свое короткое имя:
   

   Excel проверил мое имя и применил требуемый формат:
   

5. Когда вы ввели и подтвердили имена всех пользователей, которым вы хотите предоставить разрешения на редактирование выбранного диапазона, нажмите кнопку ОК.
6. В разделе Группа или имена пользователей укажите тип разрешения для каждого пользователя ( Разрешить или Запретить ) и нажмите кнопку ОК, чтобы сохранить изменения и закрыть диалоговое окно.
   

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

Как заблокировать ячейки в Excel, кроме ячеек ввода

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

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

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

Ожидается, что пользователи будут вводить свои данные в ячейки B2: B9, а формула в B11 вычисляет баланс на основе ввода пользователя. Итак, наша цель — заблокировать все ячейки на этом листе Excel, включая ячейку формулы и описания полей, и оставить разблокированными только ячейки ввода (B3: B9). Для этого выполните следующие действия.

1. На вкладке Home в группе Styles найдите стиль Input , щелкните его правой кнопкой мыши и выберите Modify… .
   
2. По умолчанию стиль Excel Input включает информацию о шрифте, цветах границы и заливки, но не о состоянии защиты ячеек. Чтобы добавить его, просто установите флажок Protection :
   

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

3. Как видно на скриншоте выше, защита теперь включена в стиль Input , но для него установлено значение Locked , тогда как нам нужно разблокировать входные ячейки .Чтобы изменить это, нажмите кнопку Format … в правом верхнем углу окна Style .
4. Откроется диалоговое окно Формат ячеек , вы переключитесь на вкладку Защита , снимите флажок Заблокировано и нажмите OK:
   
5. Диалоговое окно Style обновится, чтобы указать статус No Protection , как показано ниже, и вы щелкните OK :
   
6. А теперь выберите ячейки ввода на листе и нажмите кнопку стиля Вход на ленте. Выбранные ячейки будут отформатированы и разблокированы одновременно:
   
7. Как вы помните, блокировка ячеек в Excel не действует, пока не будет включена защита листа. Итак, последнее, что вам осталось сделать, это перейти на вкладку Review > Changes group и нажать кнопку Protect Sheet .

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

Как найти и выделить заблокированные / разблокированные ячейки на листе

Если вы блокировали и разблокировали ячейки в данной электронной таблице несколько раз, возможно, вы забыли, какие ячейки заблокированы, а какие разблокированы. Чтобы быстро найти заблокированные и разблокированные ячейки, вы можете использовать функцию ЯЧЕЙКА, которая возвращает информацию о форматировании, местоположении и других свойствах указанной ячейки.

Чтобы определить статус защиты ячейки, введите слово «защита» в первый аргумент формулы ЯЧЕЙКИ и адрес ячейки во второй аргумент.Например:

= ЯЧЕЙКА ("защита", A1)

Если A1 заблокирован, приведенная выше формула возвращает 1 (ИСТИНА), а если она разблокирована, формула возвращает 0 (ЛОЖЬ), как показано на снимке экрана ниже (формулы находятся в ячейках B1 и B2):

Нет ничего проще, правда? Однако, если у вас более одного столбца данных, описанный выше подход — не лучший вариант. Было бы гораздо удобнее увидеть сразу все заблокированные или разблокированные ячейки, чем разбирать многочисленные единицы и нули.

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

• Чтобы выделить заблокированные ячейки: = ЯЧЕЙКА ("защита", A1) = 1
• Чтобы выделить разблокированные ячейки: = CELL ("защита", A1) = 0

Где A1 — крайняя левая ячейка диапазона, на который распространяется ваше правило условного форматирования.

В качестве примера я создал небольшую таблицу и заблокировал ячейки B2: D2, содержащие формулы СУММ.На следующем снимке экрана показано правило, выделяющее эти заблокированные ячейки:

Примечание. Функция условного форматирования отключена на защищенном листе. Поэтому обязательно отключите защиту рабочего листа перед созданием правила ( Вкладка «Просмотр »> Изменения, группа > Снять защиту листа ).

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

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

Вас также может заинтересовать

Работа со ссылками в Excel

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

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

1. На листе щелкните ячейку, в которой вы хотите создать ссылку.

   Вы также можете выбрать объект, например изображение или элемент диаграммы, который вы хотите использовать для представления ссылки.

   Вы также можете щелкнуть правой кнопкой мыши ячейку или изображение, а затем нажать Ссылка в контекстном меню или нажать Ctrl + K.

2. В разделе Ссылка на щелкните Создать новый документ .

3. В поле Имя нового документа введите имя нового файла.

   Совет: Чтобы указать расположение, отличное от указанного в поле Полный путь , вы можете ввести новое расположение перед именем в поле Имя нового документа или нажать Изменить , чтобы выбрать расположение, которое вы хотите, а затем нажмите ОК .

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

5. В поле Текст для отображения введите текст, который вы хотите использовать для представления ссылки.

6. Для отображения полезной информации при наведении указателя мыши на ссылку щелкните ScreenTip , введите нужный текст в поле ScreenTip text , а затем нажмите OK .

1. На листе щелкните ячейку, в которой вы хотите создать ссылку.

   Вы также можете выбрать объект, например изображение или элемент диаграммы, который вы хотите использовать для представления ссылки.

   Вы также можете щелкнуть правой кнопкой мыши ячейку или объект и затем щелкнуть Ссылка в контекстном меню или нажать Ctrl + K.

2. В разделе Ссылка на щелкните Существующий файл или веб-страницу .

3. Выполните одно из следующих действий:

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

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

   • Чтобы выбрать веб-страницу, щелкните Просмотренные страницы , а затем щелкните веб-страницу, на которую хотите перейти.

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

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

   • Чтобы найти веб-страницу, нажмите Просмотр в Интернете

Отображение содержимого ячейки в другой ячейке в Excel

Ссылка на диапазон (несколько ячеек)

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

Получение значений из нескольких ячеек в Excel

Часто мы относим несколько ячеек к единой ячейке.Мы можем ссылаться на несколько ячеек и диапазонов в Excel, чтобы объединить текст или выполнить вычисления.

Получение текста из нескольких ячеек

Следующая формула будет ссылаться на текст из нескольких ячеек и объединять их для отображения в одной ячейке. Это получит содержимое из ячеек E2 и F2 и отобразит объединенный текст в другой ячейке.

= E2 и F2

Объединить текст из нескольких ячеек:

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

= E2 & ”” & F2

Получение значений из нескольких ячеек:

Иногда нам нужно извлечь значения из другой ячейки и выполнить некоторые вычисления в ячейке. Например, у нас есть значения стоимости и количества в двух разных ячейках (B2, C2), и мы можем найти общую стоимость в другой ячейке (A2).

= B2 * C2

Объединение значений нескольких ячеек:

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

= «Общая стоимость» & B2 * C2

Обращение к данным из диапазона ячеек

В следующем примере показано обращение к диапазону и выполнение математических вычислений.Он будет ссылаться на диапазон от D1 до E5 и поместит сумму данных в диапазон A1

.

= СУММ (D1: E5)

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

Ссылка на ячейки с одного листа очень проста в Excel. Нам нужно передать имя листа в формуле, за которым следует символ «!». Восклицательный знак используется для обозначения рабочего листа в формуле Excel.

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

= Данные! A1

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

= ’Технические данные’! A1

Ссылки и объединение ячеек

Мы можем объединить ячейки, используя оператор Concatenate или функцию CONCATENATE в Excel. Давайте посмотрим на оба метода объединения ячеек в Excel.

Использование оператора объединения : Вы можете ссылаться на ячейки с помощью адреса ячейки и использовать оператор & (амперсанд) для объединения ячеек.Например, следующая формула объединит ячейки (D1 и E1).

= D1 и E1

‘OR

= D1 & ”“ & E1

Использование функции CONCATENATE : мы можем использовать функцию Excel для ссылки и объединения нескольких ячеек и диапазонов в Excel. Вы используете, (запятую) t0, чтобы указать ссылку на каждую ячейку в формуле.

= СЦЕПИТЬ (D1, E1)

Ссылка на именованный диапазон

Мы можем ссылаться на именованные диапазоны в Excel с помощью формулы.Имя диапазона должно следовать за оператором =, чтобы ссылаться на именованный диапазон в Excel. Следующая формула относится к именованному диапазону, называемому «TotalSales».

= Всего продаж

Будет отображено значение ячейки, названное «TotalSales».

В следующих примерах показано, как ссылаться на диапазон ячеек в Excel. Диапазон («Столбец продаж») используется в функции СУММ для возврата общего значения именованного диапазона «Столбец продаж».

= СУММ («Столбец продаж»)

Отображение значений ячеек на фигуре

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

• Выберите форму
• Нажмите на панель формул
• Введите оператор = (присвоение)
• И введите требуемый адрес ячейки (например: = $ A $ 3)

Отображение значения ячейки в заголовке диаграммы

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

• Выберите диаграмму, на которой вы хотите отобразить значение ячейки
• Перейдите на вкладку «Дизайн диаграммы» на ленте.
• И показать заголовок диаграммы из элементов диаграммы
• Щелкните заголовок выбранной диаграммы
• Нажмите на панель формул
• Введите оператор = (присвоение)
• И введите требуемый адрес ячейки (например: = Sheet2! $ K $ 8)

Редактирование одной и той же ячейки на нескольких листах (Microsoft Excel)

Обратите внимание: Эта статья написана для пользователей следующих версий Microsoft Excel: 97, 2000, 2002 и 2003.Если вы используете более позднюю версию (Excel 2007 или новее), этот совет может не подойти вам . Чтобы посмотреть версию этого совета, написанного специально для более поздних версий Excel, щелкните здесь: Редактирование одной и той же ячейки на нескольких листах.

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

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

1. Выберите первую книгу в серии, которую вы хотите отредактировать.
2. Удерживая нажатой клавишу Shift , вы щелкаете вкладку последнего рабочего листа в серии, которую хотите отредактировать. Теперь должен быть выбран диапазон рабочих листов. Excel также добавляет слово [Группа] в строку заголовка, чтобы указать, что у вас выбрана группа листов.
3. Внесите изменения в рабочий лист, показанный на экране. Ваши изменения автоматически вносятся и на все остальные листы диапазона.
4. Когда закончите, выберите один рабочий лист, щелкнув его вкладку. (Щелкните вкладку листа, кроме первой в диапазоне.)

Шаг 3 может показаться немного запутанным, но на самом деле это не так. Если у вас есть выбранный диапазон рабочих листов и вы вводите формулу в ячейку D4, то та же формула вводится в ячейку D4 на каждом из выбранных листов. Это очень мощный инструмент, и Excel не уведомит вас, если вы собираетесь перезаписать существующую формулу на одном из рабочих листов.Вот почему шаг 4 — отмена выбора рабочих листов — так важен. Если вы забудете это сделать, вы можете легко испортить все свои рабочие листы, даже не собираясь этого делать.

ExcelTips — ваш источник экономичного обучения Microsoft Excel. Этот совет (2605) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь: Редактирование одной и той же ячейки в нескольких листах .

Автор Биография

Аллен Вятт

Аллен Вятт — всемирно признанный автор, автор более чем 50 научно-популярных книг и многочисленных журнальных статей. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнать больше о Allen …

Подтверждение преобразования файлов

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

Узнайте больше

Ссылка Word для создания новой книги Excel

Легко создавать и включать в документы ссылки на другие источники как в Word, так и за его пределами. Есть некоторые ограничения …

Узнайте больше

Создание и сохранение электронной таблицы

Прежде чем вы сможете работать с числами, датами и формулами, вам необходимо создать электронную таблицу Sheets. Вы можете сделать это с помощью…

Узнайте больше

Изменение размера шрифта при копировании

Вы когда-нибудь копировали информацию с одного листа на другой только для того, чтобы информация, которую вы вставляете, не выглядела так …

Узнайте больше

Копирование ячейки без форматирования

Когда вы копируете ячейку из одного места в другое (возможно, даже на другой рабочий лист), вы можете не захотеть копировать…

Узнайте больше

Быстрый ввод данных

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

Узнайте больше

Простой способ поиска значений на нескольких листах в Excel

Этот подход включает преобразование всех данных на вкладках «Раздел» в таблицы данных Excel.

Щелкните любую ячейку данных на вкладке «Подразделение».

Нажмите CTRL + T, чтобы открыть окно Создать таблицу .

Вам будет предложено указать область таблицы данных.

Преобразует данные в таблицу данных Excel.

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

Укажите имя таблицы на вкладке Design .

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

Сделайте то же самое для двух других подразделений, чтобы у вас были следующие имена таблиц:

Вернитесь на вкладку «Сводка» и постройте формулу, используя подход ИНДЕКС-ПОИСКПОЗ. (Вот ссылка на руководство по использованию функций ИНДЕКС () и ПОИСКПОЗ ().)

Синтаксис функции ИНДЕКС ():

 = ИНДЕКС (массив; номер_строки; [номер_столбца]) 

• массив : Это область, где находится ответ.
• row_num : на сколько строк нужно спуститься, чтобы найти ответ.
• column_num : Сколько столбцов справа нужно пройти, чтобы найти ответ.

Как и раньше, давайте сначала начнем с основной формулы.

Предположим, наша единственная вкладка — Game Div.

Как только формула работает для Game Div., Мы можем расширить ее, чтобы искать значения на разных вкладках.

Запустите формулу, введя:

 Ячейка D6 = ИНДЕКС (

Перейти к Game Div. и выберите столбец Сумма счета.

Вместо отображения ссылки на ячейку теперь отображается как Game_Div. [Invoiced Amount].

Второй параметр функции ИНДЕКС () — это row_num .

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

Вместо жесткого кодирования используется функция ПОИСКПОЗ (), чтобы найти, какая строка соответствует Дате, выбранной на вкладке Сводка, и вернуть позицию в функцию ИНДЕКС ().

Синтаксис функции ПОИСКПОЗ ():

 = ПОИСКПОЗ (искомое_значение, искомое_массив, [тип_сопоставления]) 

Формула Match становится:

 MATCH (Итог! $ B $ 4!, Game_Div.[Дата], 0).
 

Окончательная формула становится:

 Ячейка D6 = ИНДЕКС (Game_Div. [Выставленная сумма], MATCH (Summary! $ B $ 4, Game_Div. [Date], 0)) 

Вытягивание этой формулы до Utility Div. row будет возвращать те же значения, поскольку они жестко запрограммированы для просмотра внутри Game Div. таб.

Чтобы исправить это, мы будем использовать функцию ДВССЫЛ (), которая поможет нам получить имена динамических вкладок.

Однако соглашение об именах в ячейках B6: B8 отличается от имен таблиц, поскольку в последних используется подчеркивание вместо пробела.

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

Функция ПОДСТАВИТЬ () поможет нам сделать эту замену.

В качестве первого шага давайте представим функцию КОСВЕННО.

1. Добавьте функцию ДВССЫЛ (), заменив Game_Div. [Сумма счета] с КОСВЕННЫМ («Game_Div. [Сумма счета]»)

2.Заменить Game_Div. со ссылкой на ячейку B6 и объедините ее с заголовком таблицы [Сумма счета] с помощью символа &.

3. Чтобы заменить пробел на подчеркивание, используйте функцию ЗАМЕНА ().

Синтаксис: ПОДСТАВИТЬ (текст, старый_текст, новый_текст, [номер_экземпляра]).

• текст : ячейка, в которой должна выполняться замена.
• old_text : Какой конкретный символ вы хотите заменить. В данном случае это «».
• new_text : Чем заменить old_text. В данном случае это «_».
• Instance_num : сколько раз мы хотим, чтобы выполнялась замена. Это необязательный аргумент. Мы можем не указывать его, что означает, что мы хотим, чтобы все вхождения «» были заменены на «_».

= ЗАМЕНА (B6, «», «_») & «[Сумма счета]»…

4. Такая же замена должна применяться к ссылке в части MATCH (). Окончательная формула теперь выглядит так:

Ячейка D6 = ИНДЕКС (КОСВЕННО (ПОДСТАВИТЬ (B6, «», «_») & «[Сумма счета]»), ПОИСКПОЗ (Сводка! $ B $ 4, КОСВЕННО (ЗАМЕНА (B6, «», «_» & « [Дата] ”), 0))

5.Перетащите формулу в ячейку D8.

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

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

 Учебное пособие по  
 Excel Worksheets: VBA Activesheet и Worksheets 
 
 Сегодня мы узнаем о таблицах VBA. Мы рассмотрим все неприятности, например,  VBA Activesheet и его сравнение с обычными рабочими листами , как  Select Worksheets , как  Activate Worksheets ,  Selecting vs Activating Worksheets … и все остальное, что вам нужно знать о рабочем листе VBA в целом.
  
 ThisWorkbook против ActiveWorkbook 
 
 
 Некоторые рабочие листы Excel Начнем с основ. Прежде чем мы начнем, я хочу подчеркнуть   и напомнить разницу   между ActiveWorkbooks и ThisWorksbooks. Вкратце: 
 
 
  ThisWorkbook  — относится к книге, в которой выполняется макрос VBA 
 
  ActiveWorkbook  — относится к книге, которая находится в самом верхнем окне Excel 
 
 
 Очень важно понимать эту разницу, и я рекомендую вам сначала прочитать мой пост по этой теме.
  
 Иерархия объектов Excel VBA 
 
 
 Во-вторых, имеет смысл напомнить иерархию объектов Excel. 
 
 Наверху, по адресу , корень , у нас есть , наше приложение Excel . Приложение Excel представляет собой весь процесс Excel. Далее по дереву у нас есть  наших рабочих тетрадей . Каждое приложение содержит коллекцию книг. Заглянув в одну рабочую книгу, мы заметим, что она содержит коллекцию  рабочих листов . С другой стороны, рабочие листы, как вы знаете,  могут определять диапазоны  (не то же самое, что отдельные ячейки).Используя диапазон, мы можем получить доступ к его значениям ячеек  или формулам . 
  
 Доступ к рабочим листам VBA 
 
 
 Теперь, когда это у нас есть, давайте рассмотрим различные способы доступа к рабочим листам в VBA: 
  
 Рабочие листы ActiveWorkbook VBA 
 
  
 Сборники листов и листов 
 
 
 листов в  ActiveWorkbook : 
 
Dim ws как рабочий лист, wsCollection как листы
Set wsCollection = Sheets 'Получить всю коллекцию рабочих листов
Set ws = Sheets (1) 'Получить первый рабочий лист в ActiveWorkbook
Set ws = Sheets ("Sheet1") 'Получить рабочий лист с именем "Sheet1" в ActiveWorkbook
 
 Точно так же мы можем использовать  рабочих листов вместо листов.
Dim ws как рабочий лист, wsCollection как листы
Set wsCollection = Worksheets 'Получить всю коллекцию рабочих листов
Set ws = Worksheets (1) 'Получить первый рабочий лист в ActiveWorkbook
Set ws = Worksheets ("Sheet1") 'Получить рабочий лист с именем "Sheet1" в ActiveWorkbook
 
 Рабочие листы ThisWorkbook, VBA 
 Сборники листов и листов 
 листов в  ThisWorkbook :
Dim ws как рабочий лист, wsCollection как листы
Set wsCollection = Sheets 'Получить всю коллекцию рабочих листов
Установите ws = ThisWorkbook.Таблицы (1) 'Получите первый рабочий лист в ThisWorkbook
Set ws = ThisWorkbook.Sheets ("Sheet1") 'Получить рабочий лист с именем "Sheet1" в ThisWorkbook
 
 Точно так же мы можем использовать  рабочих листов вместо листов. 
Dim ws как рабочий лист, wsCollection как листы
Set wsCollection = Worksheets 'Получить всю коллекцию рабочих листов
Set ws = ThisWorkbook.Worksheets (1) 'Получить первый рабочий лист в ActiveWorkbook
Set ws = ThisWorkbook.Worksheets ("Sheet1") 'Получить рабочий лист с именем "Sheet1" в ThisWorkbook
 
 Имя рабочего листа VBA и имя Excel 
 caption id = ”attachment_11373 ″ align =” alignright ”width =” 213 ″] Имя рабочего листа VBA и имя Excel [/ caption] Говоря о рабочих листах  в ThisWorkbook , имя  VBA рабочего листа не совпадает с Имя Excel  рабочего листа.Давайте разбираться в этом по-другому. Справа у нас есть экран из примера Workbook. Строка   VBAName  — это имя VBA нашего рабочего листа , с другой стороны, строка   Excel Name  — это наше имя Excel. Вы можете использовать оба имени для обозначения одного и того же рабочего листа, но по-разному. 
 
 Использование имени VBA для рабочего листа 
 Рабочий лист VBA Name Мы можем ссылаться на рабочий лист VBA напрямую по его имени VBA — просто набрав его. Это очень удобно и полезно . Это связано с тем, что имя VBA не может быть изменено пользователем по ошибке с уровня Excel (не VBE). Следовательно, какое бы имя пользователь ни давал на уровне рабочей книги вашему рабочему листу, его имя VBA остается прежним.
 Использование имени листа в Excel 
  Использование таблицы Excel из VBA не рекомендуется. . Ниже я имею в виду рабочий лист, который я назвал   Excel Name   в моем примере выше.
Dim ws как рабочий лист

Set ws = Worksheets ("Имя Excel") 'Получить рабочий лист с именем "Sheet1" в ActiveWorkbook
 
 Вы не замечаете, что по сравнению с получением рабочего листа по его имени VBA, при использовании объекта Worksheets или Sheets по умолчанию вы попадаете с указанным рабочим листом, но из ActiveWorkbook. Обычно это не имеет большого значения. Но я подчеркиваю, что это просто еще одно место, где можно совершить распространенную ошибку.  См. Обе версии ниже :
Dim ws как рабочий лист

'--- Лист по имени Excel в ActiveWorkbook ---
Set ws = Worksheets ("Excel Name") 'Получить рабочий лист с именем "Sheet1" в ActiveWorkbook
Установите ws = ActiveWorkbook.Рабочие листы («Имя Excel») 'Получить рабочий лист с именем «Лист1» в ActiveWorkbook

'--- Лист по имени Excel в ThisWorkbook ---
Set ws = ThisWorkbook.Worksheets ("Имя Excel") 'Получить рабочий лист с именем "Sheet1" в ActiveWorkbook
 
 Активный лист VBA 
 Как и в случае с Active и ThisWorkbook,  сначала нужно понять разницу между выбором рабочего листа и его активацией .
  Выбранный и активированный рабочий лист , различия:
  Выбранный рабочий лист  — один или несколько рабочих листов, выбранных в окне Excel.Каждая рабочая тетрадь имеет свой собственный набор избранных заданий
  ActiveWorksheet  — текущий рабочий лист, который вы просматриваете и с которым работаете.