В эксель ссылка на другой лист: Создание и изменение ссылки на ячейку

excel как сделать ссылку на другой лист

На чтение 4 мин. Просмотров 50 Опубликовано 15.12.2019

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

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[Отчет.xlsx]Лист1′!B2.

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

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

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

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

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

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

Допустим у вас в Excel есть два листа – Лист1 и Лист2

Необходимо вставить данные ячейки из Лист1 в Лист2

В ячейки C2 Лист1 находится число – 888. Чтобы вставить ссылку на это число в ячейку B2 Лист2, надо воспользоваться записью

=Лист1!B2

При изменении данных в ячейки C2 Лист1, меняются данные в ячейке B2 Лист2

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

Сейчас мы пошагово рассмотрим как это можно сделать. Допустим у нас есть ячейка с текстом “Смотрите на листе 2” и мы хотим, чтобы данный текст стал ссылкой, нажав на которую мы должны переместиться на Лист 2.

Для решения данной задачи необходимо выделить ячейку с этим текстом (B4) и далее либо нажав правой кнопкой мыши выбрать пункт меню “Гиперссылка”

или выбрать данный пункт, перейдя в раздел “Вставка”.

После этого откроется окно вставки гиперссылки. Необходимо перейти в раздел “Место в новом документе” и выбрать необходимый лист, на который требуется сделать ссылку. В нашем пример это Лист 2.

Нажимаем “Ок” и текст в нашем файле Excel превратится в ссылку.

Как сделать перекрестную ссылку на ячейки между электронными таблицами Microsoft Excel

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

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

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

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

Таким образом, ссылка на ячейку B3 относится к ячейке на пересечении столбца B и строки 3.

При ссылке на ячейки на других листах этой ссылке на ячейку предшествует имя другого листа. Например, ниже приведена ссылка на ячейку B3 в названии листа «Январь».

  = Январь! B3 

Восклицательный знак (!) Отделяет имя листа от адреса ячейки.

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

  = «Январские продажи»! B3 

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

Введите знак равенства (=) в ячейку, щелкните вкладку «Лист», а затем щелкните ячейку, на которую вы хотите сделать перекрестную ссылку.

Когда вы это сделаете, Excel запишет для вас ссылку на панели формул.

Нажмите Enter, чтобы завершить формулу.

Как сослаться на другой файл Excel

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

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

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

  = [Chicago.xlsx] января! B3 

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

  = '[New York.xlsx] январь'! B3 

В этом примере вы можете увидеть знаки доллара ($) среди адресов ячейки. Это абсолютная ссылка на ячейку ( Узнайте больше об абсолютных ссылках на ячейки ).

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

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

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

Хранение данных в одной книге, если это возможно, более надежно.

Как сделать перекрестную ссылку на диапазон ячеек в функции

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

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

В следующем примере функция SUM суммирует значения из диапазона B2: B6 на листе с именем Sales.

  = SUM (Sales B2: B6) 

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

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

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

Более того, это имя уникально для всех таблиц в этом файле Excel.

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

  = ChicagoTotal 

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

  = Продажи! B2 

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

Щелкните в поле «Имя» в верхнем левом углу, введите имя, которое хотите назначить, и нажмите клавишу «Ввод».

При создании определенных имен вы не можете использовать пробелы. Поэтому в этом примере слова были объединены в имени и разделены заглавной буквой. Вы также можете разделить слова с помощью символов, таких как дефис (-) или подчеркивание (_).

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

Нажмите Формулы> Диспетчер имен. В окне диспетчера имен вы можете увидеть список всех определенных имен в книге, где они находятся и какие значения они хранят в данный момент.

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

Как отформатировать данные в виде таблицы

При работе с обширным списком связанных данных использование функции «Формат как таблицы» в Excel может упростить обращение к данным в ней.

Возьмите следующую простую таблицу.

Это может быть отформатировано как таблица.

Нажмите на ячейку в списке, перейдите на вкладку «Главная», нажмите кнопку «Формат таблицы» и выберите стиль.

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

Затем вы можете присвоить значимое имя вашей таблице на вкладке «Дизайн».

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

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

  = СУММ (в продаже [Чикаго]) 

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

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

Как использовать функцию VLOOKUP для динамических ссылок

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

Однако что, если ячейка, на которую вы ссылаетесь, может измениться при вставке новых строк или кто-то отсортирует список?

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

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

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

Ниже приведен пример списка сотрудников.

Функция VLOOKUP просматривает первый столбец таблицы и затем возвращает информацию из указанного столбца справа.

Следующая функция VLOOKUP ищет идентификатор сотрудника, введенный в ячейку A2 в приведенном выше списке, и возвращает дату, объединенную из столбца 4 (четвертый столбец таблицы).

  = ВПР (A2, сотрудники A: E, 4, FALSE) 

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

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

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

---

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

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

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

Также можно ссылаться на данные из других таблиц. Подробную информацию о ссылках на данные из других таблиц можно найти в статье Формулы: ссылки на данные из других таблиц.

Краткая информация о типах ссылок в формулах

Ниже приведена памятка по созданию собственных формул.

Элемент, на который нужно сослаться	Требуемый формат	Пример
Отдельная ячейка	Название столбца, номер строки	=Бюджет1
Название столбца содержит пробел или заканчивается на число	Заключите название столбца в квадратные скобки.	=[Столбец A]1 =DAY([Q1]1)
Абсолютная ссылка (всегда ссылается на указанную ячейку, строку или столбец)	Введите символ «$» перед названием столбца, номером строки или обоими.	=$[Столбец A]$1 =[Столбец B]$1 =$[Столбец C]1
Несколько несвязанных ячеек	Ссылки на ячейки должны быть разделены запятыми.	=SUM(Бюджет1, Затраты4, [Прогноз прибыли]20)
Диапазон ячеек в одном столбце	Введите первую и последнюю ячейки диапазона через двоеточие «:».	=SUM(Бюджет1:Бюджет12)
Столбец целиком (вместе со всеми добавляемыми ячейками)	Название столбца через двоеточие «:».	=SUM(Бюджет:Бюджет)
Диапазон ячеек в нескольких столбцах	Введите ссылку на правую верхнюю и левую нижнюю ячейки через двоеточие «:».	=SUM(Январь1:Март5)
Отдельная ячейка, диапазон ячеек или столбцы целиком из другой таблицы	Введите название созданной ссылки в другой таблице в фигурных скобках.	=COUNT({my_sheet1 Диапазон1})

Более подробное описание типов ссылок

Ссылка на отдельную ячейку

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

Например, формула в столбце «Балансовая стоимость» в следующей таблице управления запасами умножит значение из строки 1 столбца «Цена» на значение строки 1 столбца «Запасы»:

Формула вернет значение 2 994,00 р. , т. е. общую стоимость:

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

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

=[Годовой бюджет]1 + [Годовой бюджет]2

=[Q1]1 + [Q2]1

=[Риск/Проблема]5 + [Вероятность/Смягчение]5

Создание абсолютной ссылки

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

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

=$[Столбец A]$1 * $[Столбец B]$1

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

=[Столбец A]$1 * [Столбец B]$1

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

=$[Столбец A]1 * $[Столбец B]1

Ссылка на диапазон ячеек в одном столбце

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

Например, формула в нижней части столбца «Балансовая стоимость» в следующей таблице управления запасами сложит значения строк с 1-й по 6-ю в данном столбце:

Эта формула вернет значение «40763,75 р.», то есть итоговую балансовую стоимость:

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

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

=SUM([Годовой бюджет]:[Годовой бюджет])

ПРИМЕЧАНИЕ. Если формула введена в ячейку столбца, на который она ссылается, она не будет учитывать ту ячейку, в которую она содержится. Например, если вы введете формулу из примера выше в таблицу под названием «Годовой бюджет», формула SUM просуммирует значения всех ячеек за исключением той ячейки, в которую введена эта формула.

Ссылка на диапазон ячеек из нескольких столбцов

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

Например, формула в столбце «Общий запас» в следующей таблице управления запасами просуммирует значения строки 1 из столбцов «Запасы A», «Запасы B» и «Запасы C»:

 

Формула вернет значение «998», т. е. сумму запасов из трёх ячеек:

 

Как сделать перекрестную ссылку на ячейки между таблицами Excel

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

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

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

Перекрестная ссылка на отдельные ячейки в Excel

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

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

Чтобы сослаться на эту ячейку в вашем активном листе, введите = C7 в другой ячейке или используйте формулу, содержащую ссылку на эту ячейку, которая объединяет несколько ссылок на ячейки. = C7-C8-* например, умножает данные из C7 на данные из ячейки C8.

Если вы хотите сослаться на ячейку из другого листа, вам нужно поместить имя листа перед ячейкой, а затем восклицательный знак. Правильный формат для этого = Лист2! C7,

Формула = Лист2! C7 * Лист2! C8 умножит данные из ячейки С7 и С8 в листе с именем Лист2.

Вы также можете ссылаться на ячейки из другого файла электронной таблицы Excel. Вам нужно будет использовать квадратные скобки для идентификации файла, назовите лист, из которого он сделан, а затем используйте ссылку на ячейку. Например, знак равно[Spreadsheet.xlsx]Лист2! C7,

С помощью знак равно[Spreadsheet. xlsx]Лист2! C7*[Spreadsheet.xlsx]Лист2! C8вы сможете умножить данные из ячейки С7 и С8 в листе Sheet2 из файла с именем Spreadsheet.xlsx,

Ссылки на диапазоны ячеек в Excel

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

Например, найти сумму ячеек от C7 до C10 легко – просто введите = СУММ (С7: С10), используя двоеточие для подключения начала (С7) и конец (С10) клетки в ассортименте.

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

Чтобы сослаться на диапазон ячеек из другого листа в том же файле, назовите лист перед диапазоном ячеек. Например, = SUM (! Sheet2 C7: C10),

Чтобы сослаться на диапазон ячеек из другого файла, используйте квадратные скобки для имени файла, затем ссылку на рабочий лист и, наконец, диапазон ячеек. Например, = СУММ ([Spreadsheet.xlsx]! Sheet2 A7: A10),

Ссылки на именованные ячейки в Microsoft Excel

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

Затем вы можете использовать это определенное имя ячейки в качестве ссылки в других ячейках. Например, именованная ячейка с именем namedcell может упоминаться в другой ячейке как = Namedcell.

Эту именованную ссылку на ячейку можно использовать как есть на всем листе. Например, если это было на листе с именем Sheet2, вы все равно используете = namedcell ссылка, чтобы сослаться на это.

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

Typing = Spreadsheet.xlsx! Namedcell будет относиться к ячейке с именем namedcell сохраняется во внешнем файле электронной таблицы Excel с именем Spreadsheet. xlsx вместо.

Организация данных в Microsoft Excel

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

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

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

Рассмотрим ситуацию копирования листа с формулами в другую книгу (Другой документ) .

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

 

Копирование листа в другой документ.

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

Далее в документе с таблицей кликнуть правой кнопкой мыши по ярлыку с названием листа и в появившемся контекстном меню выбрать позицию «Переместить или скопировать…».

В появившемся меню указать документ, в который будет скопирован лист. Нажать кнопку «ОК».

При копировании листа с расчетами в другую книгу (другой документ), формулы ссылаются на старый документ.

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

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

Чтобы избавиться от ссылок на старый документ в формулах на листе «Эксель», можно воспользоваться опцией поиска.

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

Вызвать поиск можно нажав сочетание клавиш «ctrl+F»  (в русской раскладке «ctrl+А» ), либо воспользоваться пиктограммой «Найти и выделить…» на главной вкладке.

В появившейся форме для поиска нужно выбрать вкладку «Заменить». На вкладке «Заменить» есть 2 поля. Первое поле  — то значение, которое  ищем, второе поле – то, на что меняем.

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

Относительные и абсолютные ссылки – как создать и изменить

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

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

Что такое ссылка на ячейку?

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

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

Например, A1 относится к адресу на пересечении столбца A и строки 1; B2 относится ко второй ячейке в столбце B и так далее.

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

Например, если вы введете простейшее выражение =A1 в клетку C1, Эксель продублирует данные из A1 в C1:

Чтобы сложить числа в ячейках A1 и A2, используйте:  =A1 + A2

Что такое ссылка на диапазон?

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

Например, диапазон A1:C2 включает 6 ячеек от A1 до C2.

Как создать ссылку?

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

1. Выберите, где вы хотите ввести формулу.
2. Введите знак равенства (=).
3. Выполните одно из следующих действий:
   • Запишите координаты прямо в ячейку или в строку формул, или же
   • Кликните ячейку, к которой хотите обратиться.
4. Введите оставшуюся часть формулы и нажмите Enter для завершения.

Например, чтобы сложить значения в A1 и A2, введите знак равенства, щелкните A1, введите знак плюса, щелкните A2 и нажмите Enter:

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

Например, чтобы сложить значения в A1, A2 и A3, введите знак равенства, затем имя функции СУММ и открывающую скобку, выберите ячейки от A1 до A3, введите закрывающую скобку и нажмите Enter:

Чтобы обратиться ко всей строке или целому столбцу, щелкните номер строки или букву столбца соответственно.

Например, чтобы сложить все ячейки в строке 1, начните вводить функцию СУММ, а затем кликните заголовок первой строки, чтобы включить ссылку на строку в ваш расчёт:

Как изменить ссылку?

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

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

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

3. Нажмите Enter.

Как сделать перекрестную ссылку?

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

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

Например, вот как вы можете создать ссылку на адрес A1 на листе Лист2 в той же книге Excel:

=Лист2!A1

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

=’Новый лист’!A1

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

1. Начните ввод в ячейку. Запишите знак «=».
2. Щелкните вкладку листа, на которую хотите сослаться, затем выберите ячейку или диапазон ячеек на этом листе.
3. Завершите ввод и нажмите Enter.

Как сослаться на другую книгу?

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

Например:

=[Книга1.xlsx]Лист1!A1

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

='[Новый файл.xlsx]Лист1′!A1

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

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

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

Относительная ссылка на ячейку.

Относительная ссылка является самой простой и включает координаты строки и столбца, например А1 или А1:D10. По умолчанию все адреса ячеек в Экселе являются относительными.

Пример:

=A2

Это простейшее выражение сообщает программе, что нужно показать значение, которое записано в первой колонке (A) и второй строке (2). Используя скриншот чуть ниже, если бы эта формула была помещена в ячейку D1, она отобразила бы число «8», поскольку это значение находится по адресу A2.

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

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

Например, чтобы сложить числа в A2 и B2, вы вводите это в C2: =A2+B2. При копировании из строки 2 в строку 3 выражение изменится на = A3+B3.

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

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

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

Вместо того, чтобы вводить формулу для всех ячеек одну за другой, вы можете просто скопировать ячейку D2 и вставить ее во все остальные ячейки (D3: D8). Когда вы это сделаете, вы заметите, что адрес автоматически настраивается, чтобы ссылаться на соответствующую строку. Например, формула в ячейке D3 становится B3*C3, а в D4 теперь записано:  B4*C4.

Абсолютная ссылка на ячейку.

Абсолютная ссылка в Excel имеет знак доллара ($) в координатах строк или столбцов, например $A$1 или $A$1:$B$20.

Символ доллара, добавленный перед любой из координат, делает адрес абсолютным (т. е. предотвращает изменение номера строки и столбца).

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

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

Например, чтобы умножить числа в столбце B на величину скидки из F2, вы вводите следующую формулу в строке 2, а затем копируете её вниз, перетаскивая маркер заполнения:

=B2*$F$2

Относительная ссылка (B2) будет изменяться в зависимости от относительного положения строки, в которую она копируется, в то время как абсолютная ($F$2) всегда будет зафиксирована на одном и том же адресе:

Конечно, можно в ваше выражение жёстко вбить 10% скидки, и этим решить проблему при копировании. Но если впоследствии вам понадобится изменить процент скидки, то придется искать и корректировать все формулы. И обязательно какую-то случайно пропустите. Поэтому принято подобные константы записывать отдельно и использовать абсолютные ссылки на них.

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

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

Смешанная ссылка.

Смешанные ссылки немного сложнее, чем абсолютные и относительные.

Может быть два типа смешанных ссылок:

• Строка блокируется, а столбец изменяется при копировании.
• Столбец фиксируется, а строка изменяется.

Смешанная ссылкасодержит одну относительную и одну абсолютную координату, например $A1 или A$1.

Как вы помните, абсолютная ссылка содержит 2 знака доллара ($), которые фиксируют как столбец, так и строку. В смешанной только одна координата является фиксированной (абсолютной), а другая (относительная) будет изменяться в зависимости от нового расположения:

• Абсолютный столбец и относительная строка, например $A1. Когда выражение с этим типом ссылки копируется в другие ячейки, знак $ перед буквой столбца фиксирует обращение строго на указанный столбец, чтобы оно никогда не изменялось.  Относительная ссылка на строку без знака доллара будет меняться в зависимости от строки, в которую копируется формула.
• Относительный столбец и абсолютная строка, например A$1. Здесь адресация на строку будет зафиксирована, а на столбец — поменяется.

Может быть много ситуаций, когда нужно фиксировать только одну координату: либо столбец, либо строку.

Например, чтобы умножить колонку с ценами (столбец В) на 3 разных значения наценки (C2, D2 и E2), вы поместите следующую формулу в C3, а затем скопируете ее вправо и затем вниз:

=$B3*(1+C$2)

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

В первом множителе мы зафиксировали столбец. Поэтому при копировании вправо по строке адрес $B3 не изменится: ведь строка по-прежнему третья.

А вот во втором множителе знак доллара мы поставили перед номером строки. Поэтому при копировании формулы в D3 координаты столбца изменятся и вместо C$2 мы получим D$2. В результате в D3 получим:

=$B3*(1+D$2)

А когда будем копировать вниз, всё будет наоборот: $B3 изменится на $B4, $B5 и т.д. А вот D$2 не изменится, так как «заморожена» строка. В результате в С4 получим:

=$B4*(1+C$2)

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

И если ваши наценки вдруг изменятся, просто поменяйте числа в C2:E2, и проблема будет решена почти мгновенно.

Как изменить ссылку с относительной на абсолютную (или смешанную)?

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

1. Дважды щелкните ячейку, содержащую формулу.
2. Выберите ссылку, которую хотите изменить.
3. Нажмите F4 для переключения между четырьмя ссылочными типами.

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

Если вы выбрали относительную ссылку без знака $, например A1, последовательно нажимая F4, вы будете переключаться между абсолютной ссылкой с двумя знаками доллара $A$1, абсолютной строкой A$1, абсолютным столбцом $A1, а затем вновь вернёмся к A1.

Имя как разновидность абсолютной ссылки.

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

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

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

=B2*скидка

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

Формула же при этом становится более понятной и читаемой.

Ссылка на столбец.

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

• Абсолютная ссылка на столбец – $A:$A
• Относительная – A:A

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

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

А теперь давайте посмотрим это на примере.

Предположим, у вас есть некоторые числа в колонке B, и вы хотите узнать их общее и среднее значение. Проблема в том, что новые данные добавляются в таблицу каждую неделю, поэтому писать обычную формулу СУММ() или СРЗНАЧ() для фиксированного диапазона ячеек – не лучший вариант. Вместо этого вы можете ссылаться на весь столбец B:

=СУММ($D:$D)— используйте знак доллара ($), чтобы создать абсолютную ссылку на весь столбец, которая привязывает формулу к столбцу B.

=СУММ(D:D)— напишите формулу без $, чтобы сделать относительную ссылку на весь столбец, которая будет изменяться при копировании.

Совет. При написании формулы щелкните мышкой на букве заголовка (D, например), чтобы добавить ссылку сразу на весь столбец. Как и в случае ячейками, программа по умолчанию вставляет относительную ссылку (без знака $):

Ссылка на строку.

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

• Абсолютная ссылка на строку – $1:$1
• Относительная – 1:1

Пример 2. Ссылка на всю строку (абсолютная и относительная)

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

=СРЗНАЧ($3:$3) – абсолютная ссылка на всю строку зафиксирована с помощью знака доллара ($).

=СРЗНАЧ(3:3) – относительная ссылка на строку изменится при копировании вниз.

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

Для бананов (B13) расчет уже будет такой:   СРЗНАЧ(4:4). Как видите, номер строки автоматически изменился. 

Ссылка на столбец, исключая первые несколько строк.

Это очень актуальная проблема, потому что довольно часто первые несколько строк на листе содержат некоторые вводные предложения, шапку даблицы или пояснительную информацию, и вы не хотите включать их в свои вычисления. К сожалению, Excel не допускает ссылок типа D3:D, которые включали бы все данные в столбце D, только начиная со строки 3. Если вы попытаетесь добавить такую ​​конструкцию, ваша формула, скорее всего, вернет ошибку #ИМЯ?.

Вместо этого вы можете указать максимальную строку, чтобы ваша ссылка включала все возможные адреса в данном столбце. В Excel с 2019 по 2007 максимум составляет 1 048 576 строк и 16 384 столбца. Более ранние версии программы имеют максимум 65 536 строк и 256 столбцов.

Итак, чтобы найти сумму продаж в приведенной ниже таблице (колонка «Стоимость»), можно использовать выражение:

=СУММ(D3:D1048576)

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

=СУММ(D:D)-СУММ(D1:D2)

Но первый вариант предпочтительнее, так как СУММ(D:D) выполняется дольше и требует больше вычислительных ресурсов, чем СУММ(D3:D1048576).

Смешанная ссылка на весь столбец.

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

• Смешанная на столбец, например $A:A
• Смешанная на строку, например $1:1

Теперь посмотрим, что произойдет, если вы скопируете формулу с такими адресами в другие ячейки. Предположим, вы вводите формулу =СУММ($B:B) в какую-то клетку, в этом примере F3. Когда вы копируете формулу вправо (в G3), она меняется на, =СУММ($B:C), потому что первая B имеет знак $ и остается неподвижной, а вторая B – обычная и поэтому меняется. 

В результате Эксель сложит все числа в столбцах B и C. Ну и, двигаясь далее вправо, далее можно найти сумму уже трёх колонок.

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

Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге!

5 способов быстро транспонировать таблицу — В этой статье показано, как столбец можно превратить в строку в Excel с помощью функции ТРАНСП, специальной вставки, кода VBA или же специального инструмента. Иначе говоря, мы научимся транспонировать таблицу.… Как быстро заполнить пустые ячейки в Excel? — В этой статье вы узнаете, как выбрать сразу все пустые ячейки в электронной таблице Excel и заполнить их значением, находящимся выше или ниже, нулями или же любым другим шаблоном. Заполнять… Как поменять столбцы местами в Excel? — В этой статье вы узнаете несколько методов перестановки столбцов в Excel. Вы увидите, как можно перетаскивать один или сразу несколько столбцов мышью либо с помощью «горячих» клавиш. Если вы постоянно используете… Как в Excel разделить текст из одной ячейки в несколько — В руководстве объясняется, как разделить ячейки в Excel с помощью формул и стандартных инструментов. Вы узнаете, как разделить текст запятой, пробелом или любым другим разделителем, а также как разбить строки на…

0 0 голоса

Рейтинг статьи

Добавление гиперссылок, закладок и почтовых ссылок в Excel

Автор Глеб Захаров На чтение 7 мин. Просмотров 433 Опубликовано 08.07.2019

Задумывались ли вы, как добавить гиперссылки, закладки или почтовые ссылки в Excel? Ответы прямо здесь.

Следующие шаги применимы к Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 для Mac, Excel 2016 для Mac, Excel для Mac 2011 и Excel Online.

Во-первых, давайте уточним, что мы подразумеваем под каждым термином.

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

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

Ссылка mailto – это ссылка на адрес электронной почты. Выбор ссылки mailto открывает новое окно сообщения в программе электронной почты по умолчанию и вставляет адрес электронной почты в строку «Кому» сообщения.

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

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

Комбинация клавиш для открытия диалогового окна Вставить гиперссылку : Ctrl + K на ПК или команда + K на Mac.

1. На листе Excel выберите ячейку, которая будет содержать гиперссылку.
2. Введите слово, которое будет действовать как текст привязки, например «Таблицы» или «June_Sales.xlsx», и нажмите Enter .
3. Выберите ячейку с текстом привязки во второй раз.
4. Нажмите и удерживайте клавишу Ctrl (в Windows) или клавишу Команда ⌘ (в Mac).
5. Нажмите и отпустите клавишу K , чтобы открыть диалоговое окно «Вставить гиперссылку».

1. На листе Excel выберите ячейку, которая будет содержать гиперссылку.
2. Введите слово, которое будет действовать как текст привязки, например «Таблицы» или «June_Sales.xlsx», и нажмите Enter .
3. Выберите ячейку с текстом привязки во второй раз.
4. Выберите Вставить . (В Excel 2011 для Mac перейдите в меню Вставка .)
5. Выберите Гиперссылка или Ссылка > Вставить ссылку в группе ссылок. Откроется диалоговое окно «Вставить гиперссылку».

Вот как настроить гиперссылку для перехода на веб-страницу или в файл Excel.

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

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

Примечание. Этот параметр недоступен в Excel Online.

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

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

Создать закладку на тот же лист Excel

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

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

Как создать закладку на тот же лист

В следующем примере создается закладка в другом месте на том же листе Excel.

1. Введите имя в ячейке, которое будет использоваться в качестве текста привязки для закладки, и нажмите Enter .
2. Выберите эту ячейку, чтобы сделать ее активной.
3. Откройте диалоговое окно Вставить гиперссылку .
4. Откройте вкладку Разместить в этом документе (или нажмите кнопку Разместить в этом документе в Excel Online).
5. В текстовом поле Введите ссылку на ячейку введите ссылку на ячейку в другом месте на том же листе, например, «Z100».
6. Выберите ОК , чтобы завершить закладку и закрыть диалоговое окно.

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

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

Создать закладку на другой лист

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

1. Откройте многослойную книгу Excel или добавьте дополнительные листы в один файл листа.
2. На одном из листов введите имя в ячейку, которое будет использоваться в качестве текста привязки для закладки.
3. Выберите эту ячейку, чтобы сделать ее активной.
4. Откройте диалоговое окно Вставить гиперссылку .
5. Выберите вкладку Поместить в этот документ (или нажмите кнопку Поместить в этот документ в Excel Online).
6. Введите ссылку на ячейку в поле Введите ссылку на ячейку .
7. В поле Или выберите место в этом документе , выберите имя листа назначения. Безымянные листы идентифицируются как Sheet1, Sheet2, Sheet3 и так далее.
8. Выберите ОК , чтобы завершить закладку и закрыть диалоговое окно.

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

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

Вставьте ссылку Mailto в файл Excel

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

1. Введите имя в ячейку, которое будет служить якорным текстом для ссылки mailto, и нажмите Enter .
2. Выберите эту ячейку, чтобы сделать ее активной.
3. Откройте диалоговое окно Вставить гиперссылку .
4. Выберите вкладку Адрес электронной почты (или нажмите кнопку Адрес электронной почты в Excel Online).
5. В поле Адрес электронной почты введите адрес электронной почты человека, который получит письмо. Этот адрес вводится в строку Кому нового сообщения электронной почты при выборе ссылки.
6. Под строкой Тема введите тему сообщения. Этот текст вводится в строку темы в новом сообщении. Эта опция недоступна в Excel Online.
7. Выберите ОК , чтобы завершить ссылку на почту и закрыть диалоговое окно.

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

Выберите ссылку mailto, и программа электронной почты по умолчанию откроет новое сообщение с введенным адресом и текстом темы.

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

1. Наведите указатель мыши на гиперссылку, которую нужно удалить. Стрелка указателя должна измениться на символ руки.
2. Щелкните правой кнопкой мыши текст привязки гиперссылки, чтобы открыть контекстное меню.
3. Выберите Удалить гиперссылку .

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

Как связать данные на одной странице электронной таблицы с другим листом — Как делать что-нибудь в приложениях

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

Есть лучший вариант — связать ячейки электронной таблицы, чтобы хранить одни и те же данные в каждом месте. Вот как это работает в Google Таблицах (и тот же трюк работает в Excel, Numbers, Quip и других популярных приложениях для работы с электронными таблицами):

Свяжите ячейки электронной таблицы с помощью!

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

Скажите, что имя вашего листа — «Имена», и вам нужны данные из ячейки B3. Просто введите = Names! B3 в любую ячейку, и вы получите данные из этой ячейки на своем новом листе.

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

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

Нужно вычислить значения из этой ячейки? Просто введите остальную часть вашей функции как обычно. Если Names! B3 имеет значение 3 , и вы наберете = Names! B3 * 3 , вы получите результат 9 в своей новой ячейке, как и следовало ожидать.

Соединить листы в разных таблицах

Есть ячейки в двух разных электронных таблицах , которые вы хотите связать? Лучший вариант — использовать рабочие процессы Google Таблиц Zapier для связывания ваших листов.Zapier может отслеживать новые или обновленные данные в вашей ячейке и копировать их в другую электронную таблицу, чтобы поддерживать актуальность данных в таблицах и учетных записях.

---

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

Узнайте больше о Google Таблицах в бесплатной электронной книге Zapier The Ultimate Guide to Google Sheets .

---

Есть еще вопрос о Google Таблицах или других приложениях? Задайте вопрос команде Zapier

ячеек связи между листами и книгами в Excel

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

Изучение этого в конечном итоге сэкономит много времени и сэкономит массу времени.

Зачем связывать данные ячеек в Excel

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

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

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

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

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

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

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

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

Теперь, если вы щелкните ячейку в Sheet2 , вы увидите, что Excel записывает путь для вас в строке формул.

Например, = Sheet1! C3 , где Sheet1 — имя листа, C3 — ячейка, на которую вы ссылаетесь, а восклицательный знак (!) Используется в качестве разделителя между два.

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

Примечание. Если имя листа содержит пробелы (например, Лист 1 ), то при вводе ссылки в ячейку необходимо заключить имя в одинарные кавычки. Подобно = ’Лист 1 ′! C3 . Вот почему иногда проще и надежнее позволить Excel написать справочную формулу за вас.

Как связать диапазон ячеек

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

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

• На исходной вкладке с данными ( Sheet1 ) выделите ячейки, на которые вы хотите сослаться.
• Скопируйте ячейки ( Ctrl / Command + C или щелкните правой кнопкой мыши и выберите Копировать ).
• Перейдите на другую вкладку ( Sheet2 ) и щелкните ячейку (или ячейки), где вы хотите разместить ссылки.

• Щелкните правой кнопкой мыши ячейку (-а) и выберите Специальная вставка…

• В нижнем левом углу меню выберите Вставить ссылку .

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

Как связать ячейку с функцией

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

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

У вас будет = СУММ (Sheet1! C3: C7) , где функция СУММ суммирует содержимое ячеек C3: C7 в Sheet1.Нажмите Введите , чтобы заполнить формулу.

Как связать ячейки из разных файлов Excel

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

• Откройте оба документа Excel.
• Во втором файле ( Help Desk Geek ) выберите ячейку и введите символ равенства (=).
• Переключитесь на исходный файл ( Online Tech Tips ) и щелкните ячейку, на которую вы хотите создать ссылку.
• Нажмите Введите , чтобы заполнить формулу.

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

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

Стать профессиональным пользователем Microsoft Excel

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

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

Как связать книги в Excel?

Советы по Excel: Связывание книг

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

Как работает связывание данных электронной таблицы?

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

Как создать ссылку на лист?

ВАЖНОЕ ПРИМЕЧАНИЕ. Откройте конечный рабочий лист и каждый исходный рабочий лист в одном сеансе Excel.В противном случае вы не сможете связать книги.

Есть два способа создать ссылку на лист.

Метод № 1

1. Перейдите на рабочий лист назначения, щелкните соответствующую ячейку и введите знак равенства = (НЕ нажимайте клавишу ВВОД).
2. Перейдите непосредственно к исходному листу, выберите соответствующую ячейку. Система окружает исходную ячейку волнистыми линиями. Нажмите Ввод.
3. Вы вернетесь в целевую ячейку, в которой теперь отображаются данные из исходной ячейки.

Метод № 2

1. Выберите исходную ячейку, а затем выберите «Главная»> «Копировать».
2. В целевой ячейке следуйте инструкциям для вашей версии Excel:
   • Excel 2007 и более поздние версии: перейдите на вкладку «Главная». В разделе «Вставить» выберите «Вставить ссылку».
   • Excel 2003 и более ранние версии: перейдите в меню «Правка», выберите «Специальная вставка» и выберите «Вставить ссылку».
3. На исходном листе нажмите кнопку ESC, чтобы избавиться от анимированной границы.

Как связать ряд ячеек?

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

1. Выберите диапазон ячеек, который вы хотите продублировать.
2. Щелкните Копировать.
3. На конечном листе щелкните ячейку, с которой должен начинаться диапазон данных, выберите «Вставить ссылку».

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

Можно ли вручную вводить формулы ссылок в той же книге?

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

1. Введите знак равенства, имя исходного листа, «!» И ячейку. Пример: = Атланта! B6 .

ВАЖНО: Если в имени вашего рабочего листа есть пробелы или специальные символы, вы должны включить одинарные кавычки. Пример: = ’Этот лист’! B6.

Как сделать ссылку на листы в другой книге?

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

= [Имя файла.xlsx] Имя листа! CellReference

ПРИМЕЧАНИЕ. Для версий Excel старше 2007 добавьте расширение xls .

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

Что такое функция автоматического расчета?

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

• Из Excel 2007 в Excel 2019: перейдите в раздел «Расчет» на ленте Excel. Щелкните стрелку рядом с надписью «Параметры расчета» и выберите «Автоматически».
• Щелкните Файл> Параметры Excel. Щелкните Формулы и выберите параметр «Автоматически», указанный под параметрами расчета.

Кто вы лучшая управляемая компания по поддержке ИТ в Сан-Хосе?

BC Networks — лучшая управляемая компания по поддержке ИТ в Сан-Хосе, которая может предложить услуги поддержки и внедрения для всех ваших потребностей в Microsoft Office.Свяжитесь с нами сегодня, чтобы получить дополнительную информацию.

Дэйв Брюэр

|

28 окт.2019 г.

|

Опыт и стратегия — вот что отличает нас от других ИТ-компаний Сан-Хосе, Кремниевой долины и Саут-Бэй. Мы обеспечиваем стабильно оптимальные результаты, следуя нашему тщательно разработанному и зрелому набору ИТ-практик и процедур.

Как связать несколько ячеек в Excel с другого листа | Small Business

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

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

Определяет ширину предыдущего диапазона в столбцах и высоту в строках. В этом примере предположим, что ячейки с A1 по A4 на «Sheet2» содержат список чисел 1, 2, 3 и 4, соответственно, которые будут вашими предыдущими ячейками. Этот прецедентный диапазон составляет один столбец в ширину и четыре строки в высоту.

Щелкните вкладку рабочего листа в нижней части экрана, содержащую пустые ячейки, в которые вы вставите ссылку.В этом примере щелкните вкладку «Лист1».

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

Введите «=», имя рабочего листа, содержащего предыдущие ячейки, «!», Верхнюю левую ячейку предыдущего диапазона, «:» и нижнюю правую ячейку предыдущего диапазона. Одновременно нажмите «Ctrl», «Shift» и «Enter», чтобы заполнить формулу массива. Каждая зависимая ячейка теперь связана с ячейкой в ​​предыдущем диапазоне, который находится в том же соответствующем месте в пределах диапазона. В этом примере введите «= Sheet2! A1: A4» и одновременно нажмите «Ctrl», «Shift» и «Enter».Ячейки с C1 по C4 на «Sheet1» теперь содержат формулу массива «{= Sheet2! A1: A4}», заключенную в фигурные скобки, и показывают те же данные, что и предыдущие ячейки на «Sheet2».

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

Попробуй, это работает

В

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

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

Теперь мне нужны три отдельных листа, по одному для каждого представителя службы поддержки в столбце «Кому назначено», которые будут содержать только билеты в службу поддержки этого человека. Но в дополнение к этому мне нужно, чтобы было возможно следующее:

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

Звук затруднен? Это не!

Следующие шаги объясняют, как настроить это с помощью Excel 2007 или более поздней версии.

(Эта функция также доступна в предыдущих версиях Excel.)

1. Создайте все нужные листы в книге. Здесь я создал лист для каждого представителя службы поддержки.
2. Создайте именованный диапазон для основной таблицы.Выделите данные в таблице (включая заголовки), введите имя диапазона в поле Имя слева от строки формул, затем нажмите Enter. Именованный диапазон необходим Microsoft Query для выбора данных для анализа.
3. Создайте таблицу из основной таблицы. Выделите свои данные, как описано выше. Щелкните Вставить вкладку . Щелкните Таблица . Этот шаг немного избыточен, но преобразование ваших данных в таблицу имеет массу преимуществ, главное из которых состоит в том, что когда вы добавляете в нее новые данные, она автоматически расширяется для захвата новых данных.В свою очередь, это также автоматически расширит ваш именованный диапазон.
4. Перейдите на лист, на который вы хотите поместить результаты запроса. Это может быть другой лист или даже другая книга.
5. Щелкните вкладку Данные . Щелкните Из других источников . Выберите из Microsoft Query . Отобразится окно выбора источника данных .
   
6. Выберите файлы Excel. Нажмите ОК . Отобразится окно выбора книги .
7. Найдите и выберите книгу для запроса.Обратите внимание, что это может быть та же книга, что и открытая в данный момент. Нажмите ОК . Отображается мастер запросов .
8. Именованные диапазоны должны появиться в левой части окна в виде таблицы. Я выберу все столбцы слева, чтобы отобразить их на листах подмножества. Нажмите Далее.
9. На шаге мастера «Фильтровать данные» выберите фильтрацию данных, которые должны отображаться на этом листе. Здесь я делаю лист Джерри, поэтому мне нужны только данные, где Назначено равно Джерри.Щелкните Далее .
10. Следуйте оставшимся шагам мастера, пока не будете готовы выбрать место назначения для своих данных. В диалоговом окне Import Data убедитесь, что Table выбран в качестве способа вставки ваших данных. Я решил вставить данные в A1 на листе Джерри.
11. Поскольку вы вставили данные в виде таблицы, у вас есть особая возможность Обновить, если вы вносите какие-либо изменения в основную таблицу. Вы можете найти Обновить на вкладке «Данные» или на вкладке «Работа с таблицами» «Дизайн» .

Вам нужно будет повторить шаги 4–11 для каждого имеющегося у вас подмножества листов. Но как только все это настроено, у вас есть небольшая симпатичная база данных Excel!

Теги: База данных, Excel, Фильтр, Как к

Как найти внешние ссылки или ссылки в Excel

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

Метод 1: Поиск внешних ссылок с помощью функции поиска

Хотя это не надежный метод, он может значительно сократить ручные усилия. Основная логика этого метода заключается в том, что excel всегда заключает внешние ссылки в длинные скобки «[]». Итак, если вы найдете все скобки «[]», вы легко сможете получить список используемых внешних ссылок.

• Откройте лист Excel, для которого вы хотите найти внешние ссылки.
• После этого нажмите клавиши «Ctrl + F», чтобы открыть «Найти» и заменить диалоговое окно.
• В текстовом поле поиска введите строку « [*] » (без кавычек). Эта строка означает, что результатом будет любая строка, заключенная в длинные скобки.

• Затем в раскрывающемся списке «Искать в» выберите «Формулы» и нажмите кнопку «Найти все».
• Результатом будет набор внешних ссылок, которые используются на листе.

Метод 2: параметр редактирования ссылок

На ленте Excel есть вкладка «Данные», внутри этой вкладки есть параметр «Редактировать ссылки».

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

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

• Откройте свой лист Excel и перейдите на вкладку «Данные», выберите опцию «Редактировать ссылки».
• В окне «Редактировать ссылки» будут перечислены все электронные таблицы, на которые есть ссылки в вашем файле Excel.
• В правой части этого окна редактирования ссылок есть такие параметры, как «Обновить значения (можно использовать для перезагрузки значений)», «Изменить источник (можно использовать для изменения файла, на который указывает ссылка)», «Открыть исходный код (открывает ссылочные файлы Excel) ‘и’ Разрыв ссылок (можно использовать для разрыва ссылок, на которые есть ссылки) ‘.

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

Метод 3. Поиск ссылок на внешние ссылки с помощью макроса Excel

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

• Открыв лист Excel, перейдите на вкладку «Просмотр» и нажмите кнопку «Макросы».

• Теперь введите имя макроса «Fetch_Links» (без кавычек) и нажмите кнопку «Создать».
• Откроется редактор Excel VBA, просто вставьте приведенный ниже код после первой строки.

 Dim aLinks As Variant 
 aLinks = ActiveWorkbook.LinkSources (xlExcelLinks) 
 If Not IsEmpty (aLinks) Then 
 Sheets.Add 
 For i = 1 To UBound (aLinks) 
 Cells (i, 1) .V i) 
 Next i 
 End If 

• Весь код должен выглядеть так же, как показано на скриншоте ниже.

• Теперь просто нажмите кнопку «F5», чтобы запустить макрос. Код создаст новый рабочий лист, содержащий все внешние ссылки, на которые есть ссылки.

Метод 4: Надстройка поиска и удаления ссылок

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

Вы можете найти эту надстройку здесь.

Рекомендуемая литература

Как связать и объединить ваши электронные таблицы в Excel 2019

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

Подключение к другой книге

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

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

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

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

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

(параметры раскрывающегося списка «Получить данные»)

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

Щелкните «Из файла», и вы увидите типы файлов, совместимые с Excel.

(Совместимые типы файлов)

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

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

(импорт данных из внешней книги)

В примере книги есть только один лист, поэтому вы можете увидеть только один рабочий лист с именем «Лист1» в списке параметров книги. Когда создается новая книга, исходный рабочий лист по умолчанию всегда называется «Sheet1» по умолчанию. Вы можете переименовывать листы, что полезно, когда у вас есть несколько листов с разными данными, но большинство небольших книг содержат только один лист с именем Sheet1 по умолчанию.

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

После нажатия «Загрузить в» в подменю открывается окно конфигурации с вопросом, куда вы хотите загрузить данные.

(«Загрузить для импорта данных»)

Когда вы загружаете данные, вы можете сразу загрузить их в сводную таблицу или сводную диаграмму, но в этом примере данные загружаются в отдельную электронную таблицу. Обратите внимание, что параметр по умолчанию — «Новый рабочий лист», но вы также можете импортировать данные на указанный рабочий лист в указанные ячейки, которые вы настраиваете в окне «Импорт данных». Поскольку мы хотим загрузить данные в новый рабочий лист, нажмите «ОК» и позвольте Excel 2019 создать новый рабочий лист с загруженными данными.

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

(импорт форматированных данных)

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

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

Ссылки на рабочие листы

После того, как вы импортировали данные на другой лист, теперь вы можете ссылаться на данные, расположенные на другом листе.Когда Excel создает новый лист, он автоматически присваивает листу собственное имя. Первый лист называется Sheet1. Когда вы создаете новый лист в процессе импорта, следующему листу присваивается имя «Sheetx», где «x» — следующее число в серии. Excel просто увеличивает число из предыдущего листа и добавляет его к имени «Лист».

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

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

<имя листа>! <Ссылка на ячейку>

Имя листа: имя, отображаемое на вкладке рабочего листа в нижней части книги.

Ссылка на ячейку: имя ссылки на ячейку на листе, содержащем ваши данные.

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

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

Ниже приведен синтаксис ссылки на внешнюю книгу, лист и ячейку:

[myworkbook.xls] Sheet1! Cell

Имя в скобках — это имя файла книги. Имя листа ставится после скобок (без пробелов), восклицательного знака, а затем ссылки на имя ячейки. Когда вы вводите любую формулу или функцию на лист Excel, подразумеваются имя книги и рабочего листа. Вам не нужно указывать их, но Excel предполагает, что когда вы вводите просто «A4» (например) в ячейку, вы имеете в виду ссылку на текущее имя книги и имя текущего рабочего листа.Это предположение позволяет избавиться от необходимости вручную вводить имя книги и имя рабочего листа каждый раз, когда вы вводите формулу или вычисление в электронную таблицу Excel.

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

(Расчет формулы со ссылкой на внешний лист)

В этом примере значения в ячейках Sheet2 суммируются и отображаются на первом листе. Обратите внимание, что функция СУММ по-прежнему работает. В качестве единственного параметра требуется диапазон ячеек. Разница между этой формулой и стандартным вычислением состоит в том, что при вычислении функции СУММ лист указан в параметре. При наличии ссылки на лист Excel знает, что вы собираетесь суммировать данные на другом листе, а не использовать ячейки, включенные в текущий лист.

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

Введите знак равенства в ячейку, а затем введите «СУММ (» в активной ячейке. Когда вы вводите открывающую скобку, Excel запрашивает параметр функции.Затем вы можете использовать мышь, чтобы выбрать диапазон ячеек. Вместо того, чтобы выбирать диапазон ячеек на активном листе, вы можете щелкнуть вкладку листа в нижней части книги, а затем выбрать диапазон ячеек. При этом функция автоматически заполняется именем листа и диапазоном ячеек. Вам больше не нужно запоминать ячейки или переключаться между листами, чтобы определить нужные ячейки. Введите закрывающую скобку и нажмите «Enter». Вернитесь к активному листу, и вы увидите конечный продукт.

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