Как привязать значение одной ячейки к другой в Excel
Обновлено: 21.11.2024
Доступ к данным на основе привязки позволяет надстройкам контента и области задач постоянно получать доступ к определенной области документа или электронной таблицы с помощью идентификатора. Надстройка сначала должна установить привязку, вызвав один из методов, связывающих часть документа с уникальным идентификатором: addFromPromptAsync, addFromSelectionAsync или addFromNamedItemAsync. После установления привязки надстройка может использовать предоставленный идентификатор для доступа к данным, содержащимся в связанной области документа или электронной таблицы. Создание привязок обеспечивает следующее значение для вашей надстройки.
- Разрешает доступ к общим структурам данных в поддерживаемых приложениях Office, таким как таблицы, диапазоны или текст (непрерывный ряд символов).
- Включает операции чтения/записи, не требуя от пользователя выбора.
- Устанавливает связь между надстройкой и данными в документе. Привязки сохраняются в документе, и к ним можно получить доступ позже.
Установление привязки также позволяет вам подписаться на данные и события изменения выбора, которые относятся к этой конкретной области документа или электронной таблицы. Это означает, что надстройка уведомляется только об изменениях, происходящих в связанной области, а не об общих изменениях во всем документе или электронной таблице.
Объект Bindings предоставляет метод getAllAsync, предоставляющий доступ к набору всех привязок, установленных в документе или электронной таблице. Доступ к отдельной привязке можно получить по ее идентификатору с помощью методов Bindings.getByIdAsync или Office.select. Вы можете установить новые привязки, а также удалить существующие, используя один из следующих методов объекта Bindings: addFromSelectionAsync, addFromPromptAsync, addFromNamedItemAsync или releaseByIdAsync.
Типы привязки
Существует три различных типа привязок, которые вы указываете с помощью параметра bindingType при создании привязки с помощью методов addFromSelectionAsync, addFromPromptAsync или addFromNamedItemAsync.
Привязка текста — привязка к области документа, которая может быть представлена в виде текста.
В Word допустимо большинство непрерывных выделений, а в Excel только выделенные ячейки могут быть целью привязки текста. В Excel поддерживается только обычный текст. В Word поддерживаются три формата: обычный текст, HTML и Open XML для Office.
Матричная привязка — привязка к фиксированной области документа, которая содержит табличные данные без заголовков. Данные в матричной привязке записываются или читаются как двумерный массив, который в JavaScript реализован как массив массивов. Например, две строки строковых значений в двух столбцах могут быть записаны или прочитаны как [['a', 'b'], ['c', 'd']] , а один столбец из трех строк может быть записан или читается как [['a'], ['b'], ['c']] .
В Excel любой смежный набор ячеек можно использовать для установления привязки матрицы. В Word только таблицы поддерживают матричную привязку.
Привязка таблицы — привязка к области документа, содержащей таблицу с заголовками. Данные в привязке таблицы записываются или считываются как объект TableData. Объект TableData предоставляет данные через свойства заголовков и строк.
Любая таблица Excel или Word может быть основой для привязки таблицы. После установки привязки таблицы каждая новая строка или столбец, которые пользователь добавляет в таблицу, автоматически включается в привязку.
После создания привязки с помощью одного из трех методов addFrom объекта Bindings вы можете работать с данными и свойствами привязки, используя методы соответствующего объекта: MatrixBinding, TableBinding или TextBinding. Все три этих объекта наследуют методы getDataAsync и setDataAsync объекта Binding, которые позволяют взаимодействовать с привязанными данными.
Когда следует использовать матричные и табличные привязки? Когда табличные данные, с которыми вы работаете, содержат итоговую строку, вы должны использовать матричную привязку, если скрипт вашей надстройки должен получить доступ к значениям в итоговой строке или определить, что выбор пользователя находится в итоговой строке. Если вы установите привязку таблицы для табличных данных, содержащих общую строку, свойство TableBinding.rowCount, а также свойства rowCount и startRow объекта BindingSelectionChangedEventArgs в обработчиках событий не будут отражать общую строку в своих значениях. Чтобы обойти это ограничение, необходимо использовать привязку матрицы для работы со строкой итогов.
Добавить привязку к текущему выбору пользователя
В следующем примере показано, как добавить текстовую привязку с именем myBinding к текущему выделению в документе с помощью метода addFromSelectionAsync.
В этом примере указан тип привязки — текст. Это означает, что для выделения будет создан TextBinding. Различные типы привязки предоставляют разные данные и операции. Офис.BindingType — это перечисление доступных значений типа привязки.
Второй необязательный параметр — это объект, указывающий идентификатор создаваемой новой привязки. Если идентификатор не указан, он создается автоматически.
Анонимная функция, которая передается функции в качестве последнего параметра обратного вызова, выполняется после завершения создания привязки. Функция вызывается с одним параметром, asyncResult , который обеспечивает доступ к объекту AsyncResult, предоставляющему статус вызова. Свойство AsyncResult.value содержит ссылку на объект Binding того типа, который указан для вновь созданной привязки. Вы можете использовать этот объект Binding для получения и установки данных.
Добавить привязку из подсказки
В следующем примере показано, как добавить привязку текста с именем myBinding с помощью метода addFromPromptAsync. Этот метод позволяет пользователю указать диапазон для привязки с помощью встроенного в приложение запроса на выбор диапазона.
В этом примере указан тип привязки — текст. Это означает, что TextBinding будет создан для выбора, указанного пользователем в приглашении.
Второй параметр — это объект, содержащий идентификатор создаваемой новой привязки. Если идентификатор не указан, он создается автоматически.
Анонимная функция, переданная функции в качестве третьего параметра обратного вызова, выполняется после завершения создания привязки. Когда функция обратного вызова выполняется, объект AsyncResult содержит состояние вызова и вновь созданную привязку.
На рис. 1 показано встроенное в Excel окно выбора диапазона.
Рис. 1. Пользовательский интерфейс выбора данных Excel
Добавить привязку к именованному элементу
В следующем примере показано, как добавить привязку к существующему именованному элементу myRange в качестве привязки «матрицы» с помощью метода addFromNamedItemAsync и назначить идентификатор привязки как «myMatrix».
Для Excel параметр itemName метода addFromNamedItemAsync может ссылаться на существующий именованный диапазон, диапазон, указанный с помощью стиля ссылок A1 ("A1:A3"), или таблицу. По умолчанию при добавлении таблицы в Excel первой добавляемой таблице присваивается имя «Таблица1», второй добавляемой таблице — «Таблица2» и так далее. Чтобы присвоить понятное имя таблице в пользовательском интерфейсе Excel, используйте свойство Имя таблицы в меню Работа с таблицами | Вкладка "Дизайн" на ленте.
В Excel при указании таблицы в качестве именованного элемента необходимо полностью уточнить имя, чтобы включить имя рабочего листа в имя таблицы в следующем формате: "Лист1!Таблица1"
В следующем примере создается привязка в Excel к первым трем ячейкам в столбце A ("A1:A3" ), присваивается идентификатор "MyCities" , а затем в эту привязку записываются названия трех городов.
Для Word параметр itemName метода addFromNamedItemAsync ссылается на свойство Title элемента управления содержимым Rich Text. (Вы не можете привязываться к элементам управления содержимым, отличным от элемента управления содержимым Rich Text.)
По умолчанию элементу управления содержимым не присвоено значение Title*. Чтобы назначить осмысленное имя в пользовательском интерфейсе Word, после вставки элемента управления содержимым Rich Text из группы «Элементы управления» на вкладке «Разработчик» на ленте используйте команду «Свойства» в группе «Элементы управления», чтобы отобразить диалоговое окно «Свойства элемента управления содержимым». Затем задайте для свойства Title элемента управления содержимым имя, на которое вы хотите сослаться из своего кода.
В следующем примере создается привязка текста в Word к элементу управления форматированным текстовым содержимым с именем "FirstName", назначается идентификатор "firstName" и затем отображается эта информация.
Получить все привязки
В следующем примере показано, как получить все привязки в документе с помощью метода Bindings.getAllAsync.
Анонимная функция, которая передается в функцию в качестве параметра обратного вызова, выполняется после завершения операции. Функция вызывается с одним параметром asyncResult , который содержит массив привязок в документе. Массив повторяется для создания строки, содержащей идентификаторы привязок. Затем строка отображается в окне сообщения.
Получить привязку по идентификатору с помощью метода getByIdAsync объекта Bindings
В следующем примере показано, как использовать метод getByIdAsync для получения привязки в документе путем указания его идентификатора. В этом примере предполагается, что привязка с именем myBinding была добавлена в документ с помощью одного из методов, описанных ранее в этом разделе.
В примере первый параметр id — это идентификатор привязки, которую нужно получить.
Анонимная функция, которая передается функции в качестве второго параметра обратного вызова, выполняется после завершения операции.Функция вызывается с одним параметром asyncResult, который содержит статус вызова и привязку с идентификатором "myBinding".
Получить привязку по идентификатору с помощью метода select объекта Office
В следующем примере показано, как использовать метод Office.select для получения обещания объекта Binding в документе путем указания его идентификатора в строке селектора. Затем он вызывает метод Binding.getDataAsync для получения данных из указанной привязки. В этом примере предполагается, что привязка с именем myBinding была добавлена в документ с помощью одного из методов, описанных ранее в этом разделе.
Если обещание метода select успешно возвращает объект Binding, этот объект предоставляет только следующие четыре метода объекта: getDataAsync, setDataAsync, addHandlerAsync и removeHandlerAsync. Если обещание не может вернуть объект Binding, обратный вызов onError можно использовать для доступа к объекту asyncResult.error, чтобы получить дополнительную информацию. Если вам нужно вызвать член объекта Binding, отличный от четырех методов, представленных возвращаемым обещанием объекта Binding. с помощью метода select вместо этого используйте метод getByIdAsync, используя свойство Document.bindings и метод Bindings.getByIdAsync для получения объекта Binding.
Освободить привязку по идентификатору
В следующем примере показано, как использовать метод releaseByIdAsync для освобождения привязки в документе путем указания его идентификатора.
В этом примере первый параметр id — это идентификатор привязки, которую нужно освободить.
Анонимная функция, которая передается функции в качестве второго параметра, является обратным вызовом, который выполняется после завершения операции. Функция вызывается с одним параметром asyncResult, который содержит статус вызова.
Чтение данных из привязки
В следующем примере показано, как использовать метод getDataAsync для получения данных из существующей привязки.
myBinding — это переменная, содержащая существующую привязку текста в документе. В качестве альтернативы вы можете использовать Office.select для доступа к привязке по ее идентификатору и запустить вызов метода getDataAsync следующим образом:
Анонимная функция, передаваемая в функцию, представляет собой обратный вызов, который выполняется после завершения операции. Свойство AsyncResult.value содержит данные в myBinding . Тип значения зависит от типа привязки. Привязка в этом примере является текстовой привязкой. Поэтому значение будет содержать строку. Дополнительные примеры работы с привязками матриц и таблиц см. в разделе о методе getDataAsync.
Записать данные в привязку
В следующем примере показано, как использовать метод setDataAsync для установки данных в существующей привязке.
myBinding — это переменная, содержащая существующую привязку текста в документе.
В примере первый параметр — это значение, которое нужно установить для myBinding . Поскольку это текстовая привязка, значением является строка. Различные типы привязки принимают разные типы данных.
Анонимная функция, передаваемая в функцию, представляет собой обратный вызов, который выполняется после завершения операции. Функция вызывается с одним параметром asyncResult , который содержит статус результата.
Начиная с выпуска пакета обновления 1 (SP1) для Excel 2013 и соответствующей сборки Excel в Интернете теперь можно задавать форматирование при записи и обновлении данных в связанных таблицах.
Обнаружение изменений в данных или выборе в привязке
В следующем примере показано, как присоединить обработчик событий к событию DataChanged привязки с идентификатором "MyBinding".
myBinding — это переменная, которая содержит существующую текстовую привязку в документе.
Первый параметр eventType метода addHandlerAsync указывает имя события, на которое нужно подписаться. Office.EventType — это перечисление доступных значений типов событий. Office.EventType.BindingDataChanged возвращает строку "bindingDataChanged".
Функция dataChanged, которая передается в функцию в качестве второго параметра handler, представляет собой обработчик событий, который выполняется при изменении данных в привязке. Функция вызывается с одним параметром eventArgs, который содержит ссылку на привязку. Эту привязку можно использовать для получения обновленных данных.
Аналогичным образом можно определить, когда пользователь изменяет выбор в привязке, присоединив обработчик событий к событию SelectionChanged привязки. Для этого укажите для параметра eventType метода addHandlerAsync значение Office.EventType.BindingSelectionChanged или "bindingSelectionChanged" .
Вы можете добавить несколько обработчиков для данного события, снова вызвав метод addHandlerAsync и передав дополнительную функцию обработчика событий для параметра handler. Это будет работать правильно, если имя каждой функции обработчика событий уникально.
Удалить обработчик событий
Чтобы удалить обработчик события, вызовите метод removeHandlerAsync, передав тип события в качестве первого параметра eventType и имя удаляемой функции обработчика событий в качестве второго параметр обработчика. Например, следующая функция удалит функцию обработчика события dataChanged, добавленную в примере из предыдущего раздела.
Если необязательный параметр handler опущен при вызове метода removeHandlerAsync, будут удалены все обработчики событий для указанного типа события.
Обычно мы используем формулу =A1, чтобы связать ячейку A1 с другой ячейкой на листе. Но это может ссылаться только на значение ячейки. Если вы хотите сослаться на значение ячейки, а также на ее формат, вам нужно попробовать другой метод. В этой статье представлены два способа достижения этой цели.
Формат ссылки и значение из другой ячейки с вставкой связанного изображения
Предположим, вы хотите сослаться на формат и значение из ячейки A1, сделайте следующее, чтобы сделать это.
<р>1. Выберите ячейку (A1), на которую нужно сослаться, затем скопируйте ее, нажав клавиши Ctrl + C. <р>2. Перейдите к ячейке, которую вы хотите связать со ссылочной ячейкой, щелкните ее правой кнопкой мыши и выберите > Специальная вставка > Связанное изображение. Смотрите скриншот:
Теперь формат и значение ячейки A1 ссылаются на указанную ячейку. Формат и значение этих двух ячеек будут синхронизированы, как показано ниже.
Формат автоматической ссылки и значение из другой ячейки с помощью VBA
Вы можете автоматически ссылаться на формат и значение из другой ячейки, запустив приведенный ниже сценарий VBA.
<р>1. Щелкните правой кнопкой мыши вкладку листа, содержащую ячейку, на которую нужно сослаться, и выберите "Просмотреть код" в контекстном меню. <р>2. Во всплывающем окне Microsoft Visual for Applications скопируйте и вставьте приведенный ниже код VBA в окно кода.Код VBA: ссылочный формат и значение из другой ячейки
Примечание. В коде Sheet1 — это имя листа, содержащего ячейку, на которую нужно ссылаться как на значение, так и на формат. A1 и E2 означают, что ячейка A1 будет автоматически ссылаться на ячейку E2.
С этого момента при изменении значения в ячейке A1 листа Sheet1 его значение и формат будут немедленно ссылаться на ячейку E2.
Microsoft Excel – очень мощный многоцелевой инструмент, которым может пользоваться каждый. Но если вы каждый день работаете с электронными таблицами, вам может понадобиться знать больше, чем просто основы использования Excel. Знание нескольких простых приемов может иметь большое значение в работе с Excel. Хорошим примером является знание того, как связать ячейки в Excel между листами и книгами.
Изучив это, вы сэкономите много времени и избавитесь от путаницы в долгосрочной перспективе.
Зачем связывать данные ячеек в Excel
Умение ссылаться на данные на разных листах является ценным навыком по нескольким причинам.
Во-первых, это упростит организацию электронных таблиц. Например, вы можете использовать один лист или книгу для сбора необработанных данных, а затем создать новую вкладку или новую книгу для отчетов и/или суммирования.
После того как вы свяжете ячейки между ними, вам нужно только изменить или ввести новые данные в одну из них, а результаты автоматически изменятся в другой. И все это без необходимости перемещаться между различными электронными таблицами.
Во-вторых, этот прием позволяет избежать дублирования одних и тех же чисел в нескольких таблицах. Это сократит ваше рабочее время и вероятность ошибки в расчетах.
В следующей статье вы узнаете, как связать отдельные ячейки на других листах, связать диапазон ячеек и как связать ячейки из разных документов Excel.
Как связать две отдельные ячейки
Начнем со связывания двух ячеек, расположенных на разных листах (или вкладках), но в одном файле Excel. Для этого выполните следующие действия.
- На Листе 2 введите в ячейку символ равенства (=).
- Перейдите на другую вкладку (Лист1) и щелкните ячейку, на которую хотите создать ссылку.
- Нажмите Enter, чтобы завершить формулу.
Теперь, если щелкнуть ячейку на Листе2, вы увидите, что Excel записывает путь для вас в строке формул.
Например, =Лист1!C3, где Лист1 – это имя листа, C3 – ячейка, на которую вы ссылаетесь, а восклицательный знак (!) используется в качестве разделителя между ними.
Используя этот подход, вы можете создавать ссылки вручную, не выходя из исходного листа. Просто введите формулу ссылки прямо в ячейку.
Примечание. Если имя листа содержит пробелы (например, Лист 1), вам необходимо поместить имя в одинарные кавычки при вводе ссылки в ячейку. Нравится =’Лист 1’!C3. Вот почему иногда проще и надежнее позволить Excel написать справочную формулу за вас.
Как связать диапазон ячеек
Еще один способ связать ячейки в Excel – связать целый диапазон ячеек с разных вкладок Excel. Это полезно, когда вам нужно хранить одни и те же данные на разных листах, не редактируя оба листа.
Чтобы связать несколько ячеек в Excel, выполните следующие действия.
- На исходной вкладке с данными (Лист1) выделите ячейки, на которые вы хотите сослаться.
- Скопируйте ячейки (Ctrl/Command + C или щелкните правой кнопкой мыши и выберите "Копировать").
- Перейдите на другую вкладку (Лист2) и щелкните ячейку (или ячейки), в которую вы хотите поместить ссылки.
- Щелкните правой кнопкой мыши по ячейкам и выберите "Специальная вставка...".
- В левом нижнем углу меню выберите "Вставить ссылку".
Когда вы нажимаете на новые связанные ячейки на Листе 2, вы можете увидеть ссылки на ячейки из Листа 1 на вкладке формул. Теперь всякий раз, когда вы меняете данные в выбранных ячейках на Листе 1, автоматически изменяются данные в связанных ячейках на Листе 2.
Как связать ячейку с функцией
Связывание с кластером ячеек может быть полезно, когда вы выполняете суммирование и хотите хранить их на листе отдельно от исходных необработанных данных.
Допустим, вам нужно написать функцию СУММ на Листе2, которая будет ссылаться на ряд ячеек из Листа1. Для этого перейдите на Лист2 и щелкните ячейку, в которую вы хотите поместить функцию. Напишите функцию как обычно, но когда дело доходит до выбора диапазона ячеек, перейдите на другой лист и выделите их, как описано выше.
Вы получите =СУММ(Лист1!C3:C7), где функция СУММ суммирует содержимое ячеек C3:C7 на Листе1. Нажмите Enter, чтобы завершить формулу.
Как связать ячейки из разных файлов Excel
Процесс связывания между различными файлами Excel (или книгами) практически такой же, как описано выше. За исключением случаев, когда вы вставляете ячейки, вставляйте их в другую электронную таблицу, а не в другую вкладку. Вот как это сделать за 4 простых шага.
- Откройте оба документа Excel.
- Во втором файле (Help Desk Geek) выберите ячейку и введите символ равенства (=).
- Переключитесь на исходный файл (Интернетовые технические советы) и щелкните ячейку, на которую хотите создать ссылку.
- Нажмите Enter, чтобы завершить формулу.
Теперь формула для связанной ячейки также содержит имя другой книги в квадратных скобках.
Если вы закроете исходный файл Excel и снова посмотрите на формулу, вы увидите, что теперь в ней также указано местоположение всего документа. Это означает, что если вы переместите исходный файл, на который вы ссылаетесь, в другое место или переименуете его, ссылки перестанут работать. Вот почему надежнее хранить все важные данные в одном файле Excel.
Стать профессиональным пользователем Microsoft Excel
Связывание ячеек между листами — это только один пример того, как вы можете фильтровать данные в Excel и упорядочивать электронные таблицы. Ознакомьтесь с некоторыми другими советами и рекомендациями по Excel, которые мы собрали, чтобы помочь вам стать опытным пользователем.
Какие еще полезные лайфхаки для Excel вы знаете и используете?Знаете ли вы какие-либо другие творческие способы связать ячейки в Excel? Поделитесь ими с нами в разделе комментариев ниже.
Аня – внештатный писатель по технологиям. Родом из России, в настоящее время она работает удаленным работником и цифровым кочевником. Имея образование в области журналистики, лингвистики и технического перевода, Аня не могла представить свою жизнь и работу без ежедневного использования современных технологий. Всегда ища новые способы облегчить свою жизнь и образ жизни, не зависящий от местоположения, она надеется поделиться своим опытом в качестве техно- и интернет-зависимого в своих письмах. Прочитать полную биографию Ани
Понравился ли вам этот совет? Если это так, загляните на наш собственный канал на YouTube, где мы рассказываем о Windows, Mac, программном обеспечении и приложениях, а также предлагаем множество советов по устранению неполадок и видео с практическими рекомендациями. Нажмите кнопку ниже, чтобы подписаться!
Одним из наиболее часто используемых способов является использование параметра Объединить и центрировать на вкладке "Главная".
Проблема с использованием Объединить и центрировать заключается в том, что он может объединять ячейки, но не текст в этих ячейках (т. е. вы теряете некоторые данные при объединении ячеек).
Допустим, у нас есть набор данных, как показано ниже:
Если я выберу ячейки A1 и B1 и воспользуюсь параметром "Объединить и центрировать", он сохранит текст из крайней левой ячейки (в данном случае A1), но вы потеряете данные из всех других ячеек.
Однако Excel не совсем безжалостен — он предупреждает вас, прежде чем это произойдет.
Если вы попытаетесь объединить ячейки, содержащие текст, появится всплывающее окно с предупреждением об этом (как показано ниже).
Если вы продолжите и нажмете OK, две ячейки будут объединены, а текст останется только из самой левой ячейки. В приведенном выше примере он объединит A1 и B1 и покажет только текст John.
Это руководство охватывает:
Объединить ячейки в Excel без потери данных
Если вы не хотите терять текст из объединенных ячеек, используйте формулу СЦЕПИТЬ. Например, в приведенном выше случае введите следующую формулу в ячейку C1: =СЦЕПИТЬ(A1", ",B1)
Здесь мы объединяем ячейки A1 и B1 и используем пробел в качестве разделителя. Если вам не нужен разделитель, вы можете просто пропустить его и использовать формулу =СЦЕПИТЬ(A1,B1).
Кроме того, вы можете использовать любой другой разделитель, например запятую или точку с запятой.
Этот результат функции СЦЕПИТЬ находится в другой ячейке (в ячейке C1). Поэтому вы можете скопировать его (как значения) в ячейку, которую вы хотите объединить.
Вы также можете использовать знак амперсанда для объединения текста. Например, вы также можете использовать =A1&», «&B1
Преимущество отсутствия объединения ячеек в Excel
Когда вы используете параметр «Объединить и центрировать» для объединения ячеек, он лишает вас возможности сортировать этот набор данных. Если вы попытаетесь отсортировать набор данных, содержащий какие-либо объединенные ячейки, появится всплывающее окно, как показано ниже:
Альтернатива использованию слияния и центрирования
Если вы хотите объединить ячейки в разных столбцах в одну строку, воспользуйтесь альтернативой Объединить и расположить по центру — параметром Отцентрировать по выделенному.
Вот как это использовать:
- Выберите ячейки, которые хотите объединить.
- Нажмите Control + 1, чтобы открыть диалоговое окно форматирования ячеек.
- На вкладке "Выравнивание" в раскрывающемся списке "По горизонтали" выберите "По центру выделенного".
Это объединит ячейки таким образом, что все, что вы вводите в крайнюю левую ячейку, будет центрировано, однако вы по-прежнему можете выбирать каждую ячейку по отдельности. Это также не показывает ошибку при попытке отсортировать данные.
ПРИМЕЧАНИЕ. Для работы Center to Across убедитесь, что данные есть только в самой левой ячейке.
Читайте также: