Объединение текста и чисел
Предположим, что для подготовки массовой рассылки необходимо создать грамматически правильное предложение из нескольких столбцов данных. Или, может быть, вам нужно форматировать числа с текстом, не влияя на формулы, использующие эти числа. В Excel существует несколько способов объединения текста и чисел.
Использование числового формата для отображения текста до или после числа в ячейке
Если столбец, который требуется отсортировать, содержит как числа, так и текст(например, Продукт No 15, Продукт 100, Продукт 200), он может сортироваться неправильно. Ячейки, содержащие 15, 100 и 200, можно отформатировать таким образом, чтобы они отображались на листе как Product #15, Product #100 и Product #200.
Используйте пользовательский формат чисел для отображения числа с текстом без изменения поведения сортировки числа. Таким образом, вы измените способ отображения числа, не изменяя его значение.
- Выделите ячейки, которые нужно отформатировать.
- На вкладке Главная в группе Число щелкните стрелку .
Для отображения
Используйте код
Как это работает
12 — продукт No 12
Текст, заключенный в кавычки (включая пробел), отображается перед числом в ячейке. В коде «0» представляет число, содержащееся в ячейке (например, 12).
12:00 по 12:00 EST
Текущее время отображается в формате даты и времени h:mm AM/PM, а текст «EST» отображается после времени.
-12 как $-12.00 Дефицит и 12 как избыток $12,00
$0,00 «избыток»;$0,00 «Дефицит»
Значение отображается в формате валюты. Кроме того, если ячейка содержит положительное значение (или 0), после значения отображается значение «Избыток». Если ячейка содержит отрицательное значение, вместо этого отображается слово «Нехватка».
Объединение текста и чисел из разных ячеек в одну ячейку с помощью формулы
При объединении чисел и текста в ячейке числа становятся текстовыми и больше не функционируют как числовые значения. Это означает, что вы больше не сможете выполнять с ними математические операции.
Для объединения чисел используйте функции CONCATENATE или CONCAT, TEXTили TEXTJOIN и оператор амперсанд (&).
- В Excel 2016, Excel Mobile и Excel в Интернетесцепление заменено функцией CONCAT . Хотя функция CONCATENATE по-прежнему доступна для обратной совместимости, рекомендуется использовать CONCAT, так как в будущих версиях Excel сцепление может быть недоступно.
- ФУНКЦИЯ TEXTJOIN объединяет текст из нескольких диапазонов и (или) строк и включает разделитель, указанный между каждым текстовым значением, которое будет объединено. Если в качестве разделителя используется пустая текстовая строка, функция эффективно объединит диапазоны. ФУНКЦИЯ TEXTJOIN недоступна в Excel 2013 и предыдущих версиях.
Примеры
См. различные примеры на рисунке ниже.
Внимательно изучите использование функции TEXT во втором примере на рисунке. При соединении числа со строкой текста с помощью оператора объединения используйте функцию TEXT для управления способом отображения числа. Формула использует базовое значение из указанной ячейки (.4 в этом примере), а не отформатированное значение, которое отображается в ячейке (40 %). Функция TEXT используется для восстановления форматирования чисел.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Как привязать значение одной ячейки к другой в Excel
Доступ к данным на основе привязки позволяет надстройкам контента и области задач постоянно получать доступ к определенной области документа или электронной таблицы с помощью идентификатора. Надстройка сначала должна установить привязку, вызвав один из методов, связывающих часть документа с уникальным идентификатором: 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 убедитесь, что данные есть только в самой левой ячейке.
Читайте также:
- Полное удаление oracle 12c
- Discord не видит микрофон
- Компьютерное программное обеспечение Ddj 200
- Grub install warning partition label gpt has no bios boot partition embedding is not possible
- Как сделать логотип в фотошопе для ютуба
Создание связи между двумя таблицами в Excel
Вы применяли функцию ВПР, чтобы переместить данные столбца из одной таблицы в другой? Так как в Excel теперь есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами на основе совпадающих данных в них. Затем можно создать листы Power View или сводные таблицы и другие отчеты с полями из каждой таблицы, даже если они получены из различных источников. Например, если у вас есть данные о продажах клиентам, вам может потребоваться импортировать и связать данные логики операций со временем, чтобы проанализировать тенденции продаж по годам и месяцам.
Все таблицы в книге указываются в списках полей сводной таблицы и Power View.
При импорте связанных таблиц из реляционной базы данных Excel часто может создавать эти связи в модели данных, формируемой в фоновом режиме. В других случаях необходимо создавать связи вручную.
- Убедитесь, что книга содержит хотя бы две таблицы и в каждой из них есть столбец, который можно сопоставить со столбцом из другой таблицы.
- Вы можете отформатировать данные как таблицу или импортировать внешние данные в виде таблицы на новом.
- Присвойте каждой из таблиц понятное имя: На вкладке Работа с таблицами щелкните Конструктор >Имя таблицы и введите имя.
- Убедитесь, что столбец в одной из таблиц имеет уникальные значения без дубликатов. Excel может создавать связи только в том случае, если один столбец содержит уникальные значения. Например, чтобы связать продажи клиента с логикой операций со временем, обе таблицы должны включать дату в одинаковом формате (например, 01.01.2012) и по крайней мере в одной таблице (логика операций со временем) должны быть перечислены все даты только один раз в столбце.
- Щелкните Данные>Отношения.
Если команда Отношения недоступна, значит книга содержит только одну таблицу.
- В окне Управление связями нажмите кнопку Создать.
- В окне Создание связи щелкните стрелку рядом с полем Таблица и выберите таблицу из раскрывающегося списка. В связи «один ко многим» эта таблица должна быть частью с несколькими элементами. В примере с клиентами и логикой операций со временем необходимо сначала выбрать таблицу продаж клиентов, потому что каждый день, скорее всего, происходит множество продаж.
- Для элемента Столбец (чужой) выберите столбец, который содержит данные, относящиеся к элементу Связанный столбец (первичный ключ). Например, при наличии столбца даты в обеих таблицах необходимо выбрать этот столбец именно сейчас.
- В поле Связанная таблица выберите таблицу, содержащую хотя бы один столбец данных, которые связаны с таблицей, выбранной в поле Таблица.
- В поле Связанный столбец (первичный ключ) выберите столбец, содержащий уникальные значения, которые соответствуют значениям в столбце, выбранном в поле Столбец.
- Нажмите кнопку ОК.
Дополнительные сведения о связях между таблицами в Excel
- Примечания о связях
- Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании
- «Могут потребоваться связи между таблицами»
- Шаг 1. Определите, какие таблицы указать в связи
- Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблицы к другой
Примечания о связях
- Вы узнаете, существуют ли связи, при перетаскивании полей из разных таблиц в список полей сводной таблицы. Если вам не будет предложено создать связь, то в Excel уже есть сведения, необходимые для связи данных.
- Создание связей аналогично использованию VLOOKUP: вам нужны столбцы, содержащие совпадающие данные, чтобы Excel могли ссылаться на строки в одной таблице с строками из другой таблицы. В примере со временем в таблице Customer должны быть значения дат, которые также существуют в таблице аналитики времени.
- В модели данных связи таблиц могут быть типа «один к одному» (у каждого пассажира есть один посадочный талон) или «один ко многим» (в каждом рейсе много пассажиров), но не «многие ко многим». Связи «многие ко многим» приводят к ошибкам циклической зависимости, таким как «Обнаружена циклическая зависимость». Эта ошибка может произойти, если вы создаете прямое подключение между двумя таблицами со связью «многие ко многим» или непрямые подключения (цепочку связей таблиц, в которой каждая таблица связана со следующей отношением «один ко многим», но между первой и последней образуется отношение «многие ко многим»). Дополнительные сведения см. в статье Связи между таблицами в модели данных.
- Типы данных в двух столбцах должны быть совместимы. Подробные сведения см. в статье Типы данных в моделях данных.
- Другие способы создания связей могут оказаться более понятными, особенно если неизвестно, какие столбцы использовать. Дополнительные сведения см. в статье Создание связи в представлении диаграммы в Power Pivot.
Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании
Вы можете узнать о связях обеих таблиц и логики операций со временем с помощью свободных данных на Microsoft Azure Marketplace. Некоторые из этих наборов данных очень велики, и для их загрузки за разумное время необходимо быстрое подключение к Интернету.
- Запустите надстройку Power Pivot в Microsoft Excel и откройте окно Power Pivot.
- Нажмите Получение внешних данных >Из службы данных >Из Microsoft Azure Marketplace. В мастере импорта таблиц откроется домашняя страница Microsoft Azure Marketplace.
- В разделе Price (Цена) нажмите Free (Бесплатно).
- В разделе Category (Категория) нажмите Science & Statistics (Наука и статистика).
- Найдите DateStream и нажмите кнопку Subscribe (Подписаться).
- Введите свои учетные данные Майкрософт и нажмите Sign in (Вход). Откроется окно предварительного просмотра данных.
- Прокрутите вниз и нажмите Select Query (Запрос на выборку).
- Нажмите кнопку Далее.
- Чтобы импортировать данные, выберите BasicCalendarUS и нажмите Готово. При быстром подключении к Интернету импорт займет около минуты. После выполнения вы увидите отчет о состоянии перемещения 73 414 строк. Нажмите Закрыть.
- Чтобы импортировать второй набор данных, нажмите Получение внешних данных >Из службы данных >Из Microsoft Azure Marketplace.
- В разделе Type (Тип) нажмите Data Данные).
- В разделе Price (Цена) нажмите Free (Бесплатно).
- Найдите US Air Carrier Flight Delays и нажмите Select (Выбрать).
- Прокрутите вниз и нажмите Select Query (Запрос на выборку).
- Нажмите кнопку Далее.
- Нажмите Готово для импорта данных. При быстром подключении к Интернету импорт займет около 15 минут. После выполнения вы увидите отчет о состоянии перемещения 2 427 284 строк. Нажмите Закрыть. Теперь у вас есть две таблицы в модели данных. Чтобы связать их, нужны совместимые столбцы в каждой таблице.
- Убедитесь, что значения в столбце DateKey в таблице BasicCalendarUS указаны в формате 01.01.2012 00:00:00. В таблице On_Time_Performance также есть столбец даты и времени FlightDate, значения которого указаны в том же формате: 01.01.2012 00:00:00. Два столбца содержат совпадающие данные одинакового типа и по крайней мере один из столбцов (DateKey) содержит только уникальные значения. В следующих действиях вы будете использовать эти столбцы, чтобы связать таблицы.
- В окне Power Pivot нажмите Сводная таблица, чтобы создать сводную таблицу на новом или существующем листе.
- В списке полей разверните таблицу On_Time_Performance и нажмите ArrDelayMinutes, чтобы добавить их в область значений. В сводной таблице вы увидите общее время задержанных рейсов в минутах.
- Разверните таблицу BasicCalendarUS и нажмите MonthInCalendar, чтобы добавить его в область строк.
- Обратите внимание, что теперь в сводной таблице перечислены месяцы, но количество минут одинаковое для каждого месяца. Нужны одинаковые значения, указывающие на связь.
- В списке полей, в разделе «Могут потребоваться связи между таблицами» нажмите Создать.
- В поле «Связанная таблица» выберите On_Time_Performance, а в поле «Связанный столбец (первичный ключ)» — FlightDate.
- В поле «Таблица» выберитеBasicCalendarUS, а в поле «Столбец (чужой)» — DateKey. Нажмите ОК для создания связи.
- Обратите внимание, что время задержки в настоящее время отличается для каждого месяца.
- В таблице BasicCalendarUS перетащите YearKey в область строк над пунктом MonthInCalendar.
Теперь вы можете разделить задержки прибытия по годам и месяцам, а также другим значениям в календаре.
Советы: По умолчанию месяцы перечислены в алфавитном порядке. С помощью надстройки Power Pivot вы можете изменить порядок сортировки так, чтобы они отображались в хронологическом порядке.
- Таблица BasicCalendarUS должна быть открыта в окне Power Pivot.
- В главной таблице нажмите Сортировка по столбцу.
- В поле «Сортировать» выберите MonthInCalendar.
- В поле «По» выберите MonthOfYear.
Сводная таблица теперь сортирует каждую комбинацию «месяц и год» (октябрь 2011, ноябрь 2011) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, потому что канал DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу логики операций со временем, ваши действия будут другими.
«Могут потребоваться связи между таблицами»
По мере добавления полей в сводную таблицу вы получите уведомление о необходимости связи между таблицами, чтобы разобраться с полями, выбранными в сводной таблице.
Хотя Excel может подсказать вам, когда необходима связь, он не может подсказать, какие таблицы и столбцы использовать, а также возможна ли связь между таблицами. Чтобы получить ответы на свои вопросы, попробуйте сделать следующее.
Шаг 1. Определите, какие таблицы указать в связи
Если ваша модель содержит всего лишь несколько таблиц, понятно, какие из них нужно использовать. Но для больших моделей вам может понадобиться помощь. Один из способов заключается в том, чтобы использовать представление диаграммы в надстройке Power Pivot. Представление диаграммы обеспечивает визуализацию всех таблиц в модели данных. С помощью него вы можете быстро определить, какие таблицы отделены от остальной части модели.
Примечание: Можно создавать неоднозначные связи, которые являются недопустимыми при использовании в сводной таблице или отчете Power View. Пусть все ваши таблицы связаны каким-то образом с другими таблицами в модели, но при попытке объединения полей из разных таблиц вы получите сообщение «Могут потребоваться связи между таблицами». Наиболее вероятной причиной является то, что вы столкнулись со связью «многие ко многим». Если вы будете следовать цепочке связей между таблицами, которые подключаются к необходимым для вас таблицам, то вы, вероятно, обнаружите наличие двух или более связей «один ко многим» между таблицами. Не существует простого обходного пути, который бы работал в любой ситуации, но вы можете попробоватьсоздать вычисляемые столбцы, чтобы консолидировать столбцы, которые вы хотите использовать в одной таблице.
Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблице к другой
После того как вы определили, какая таблица не связана с остальной частью модели, пересмотрите столбцы в ней, чтобы определить содержит ли другой столбец в другом месте модели соответствующие значения.
Предположим, у вас есть модель, которая содержит продажи продукции по территории, и вы впоследствии импортируете демографические данные, чтобы узнать, есть ли корреляция между продажами и демографическими тенденциями на каждой территории. Так как демографические данные поступают из различных источников, то их таблицы первоначально изолированы от остальной части модели. Для интеграции демографических данных с остальной частью своей модели вам нужно будет найти столбец в одной из демографических таблиц, соответствующий тому, который вы уже используете. Например, если демографические данные организованы по регионам и ваши данные о продажах определяют область продажи, то вы могли бы связать два набора данных, найдя общие столбцы, такие как государство, почтовый индекс или регион, чтобы обеспечить подстановку.
Кроме совпадающих значений есть несколько дополнительных требований для создания связей.
- Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликаты. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это означает, что не может быть несколько нулей в столбце подстановок.
- Типы данных столбца подстановок и исходного столбца должны быть совместимы. Подробнее о типах данных см. в статье Типы данных в моделях данных.
Подробнее о связях таблиц см. в статье Связи между таблицами в модели данных.
Как в Excel привязать значение одной ячейки к другой?
В ячейку, куда мы хотим вставить связь, ставим знак равенства (так же как и для обычной формулы), переходим в исходную книгу, выбираем ячейку, которую хотим связать, щелкаем Enter. Вы можете использовать инструменты копирования и автозаполнения для формул связи так же, как и для обычных формул.
Как сделать Зависимость одной ячейки от другой в Excel?
В окне «Параметры Excel» на вкладке «Дополнительно» в группе «Показать параметры для следующей книги» раскройте список книг и выберите нужную книгу для настройки. В графе «Для объектов показывать» активируйте пункты: «Все» – для отображения стрелок зависимостей; «Скрыть объекты» – для скрытия стрелок зависимостей.
Как связать значения ячеек Excel?
Объединение текста из двух или нескольких ячеек в одну
- Выделите ячейку, в которую вы хотите вставить объединенные данные.
- Введите = (знак равенства) и выберите первую ячейку, которую нужно объединить.
- Введите символ & и пробел, заключенный в кавычки.
- Выберите следующую ячейку, которую нужно объединить, и нажмите клавишу ВВОД. Пример формулы: =A2&» «&B2.
Как сделать в Excel ссылку на одну и ту же ячейку?
- Щелкните ячейку, в которую нужно ввести формулу.
- В строка формул введите = (знак равенства).
- Выберите ячейку с нужным значением или введите ссылку на нее. Вы можете ссылаться на одну ячейку, диапазон ячеек, расположение на другом или в другой книге. …
- Нажмите клавишу ВВОД.
Как связать данные расположенные на разных листах Excel?
- Найдите ячейку, на которую хотите сослаться, и запомните, где она находится. …
- Перейдите к нужному листу. …
- Выбранный лист откроется.
- Найдите и выделите ячейку, в которой должно появиться значение. …
- Введите знак равенства (=), название листа с восклицательным знаком(!)
Как посмотреть зависимые ячейки в Excel?
- Укажите ячейку, для которой следует найти зависимые ячейки.
- Чтобы отобразить стрелку трассировки для каждой ячейки, зависимой от активной ячейки, на вкладке Формулы в группе Зависимости формул нажмите кнопку Зависимые ячейки .
Как найти зависимую ячейку на другом листе?
- Выбрать ячейку для проверки и перейти в меню ФОРМУЛЫ.
- Нажать на кнопку «Влияющие ячейки»
- При наличии ссылок на другие листы, мы увидим стрелку с указанием на другой лист.
- Необходимо кликнуть два раза по стрелке и вызвать окно перехода.
- В открывшемся окне, выбираем ссылку на необходимый лист и нажимаем ОК.
Как привязать строку к ячейке в Excel?
Как связать соседние ячейки в Excel?
Самое простое решение-выбрать вторую ячейку и нажать = . Это положит начало процессу создания Фомулы. Теперь либо введите ссылку на 1-ю ячейку (например, A1 ), либо нажмите на первую ячейку и нажмите enter. Это должно привести к тому, что вторая ячейка будет ссылаться на значение первой ячейки.
Как подтянуть информацию в Excel из одного файла в другой?
Необходимо открыть вкладку «Power Query». В разделе «Данные Excel» нажимаем кнопку (пиктограмму) «Из таблицы». Далее нужно выбрать диапазон ячеек, из которых нужно «притянуть» информацию и нажимаем «Ок». После выбора области данных появится окно настройки вида новой таблицы.
Как в Экселе сделать чтобы число не менялось?
Для этого необходимо задать формат ячейки – что в ней будет находиться число, а не цифра. Для этого, щелкаем правой кнопкой мыши по ячейке и выбираем вкладку “Формат ячеек”. В ней выбираем вариант “Текстовый”. Все, после этого в данной ячейке Excel даже набрав цифры с точкой у вас не появится число и месяц.
Как сделать в excel чтобы ячейки не менялась в формуле?
- и нажимаете клавишу «F4». …
- это означает, что если вы протяните формулу, то ссылка на ячейку $F$4 останется на месте, т. …
- Если еще раз нажмете клавишу «F4», то ссылка станет $F4:
- Это означает, что зафиксирован столбец F и он не будет перемещаться, когда вы будите протаскивать формулу, а ссылка на строку 4 будет двигаться.
Какие типы ссылок на ячейки существуют?
Как сделать связь между листами в Excel?
В ячейку, куда мы хотим вставить связь, ставим знак равенства (так же как и для обычной формулы), переходим в исходную книгу, выбираем ячейку, которую хотим связать, щелкаем Enter. Вы можете использовать инструменты копирования и автозаполнения для формул связи так же, как и для обычных формул.
Как использовать данные с другого листа в Excel?
Ссылка на лист, отделенная от ссылки на диапазон значений.
- Щелкните ячейку, в которую нужно ввести формулу.
- В строка формул введите = (знак равенства) и формулу, которую нужно использовать.
- Щелкните ярлычок листа, на который нужно сослаться.
- Выделите ячейку или диапазон ячеек, на которые нужно сослаться.
Как ввести в формулу ссылку на ячейку другой книги?
Вы выберите ячейку или ячейки, для которых вы хотите создать внешнюю ссылку. Введите = (знак равенства). Перейдите в книгу-источник и щелкните книгу, содержаную ячейки, которые вы хотите связать. Нажмите F3, выберите имя, на которое будет ссылаться ссылка, и нажмитеввод.
[expert_bq можно закрывать окно поиска, нажав стандартную кнопку закрытия в верхнем правом углу окна, так как нашу задачу можно считать решенной. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Как видим, после этого действия все элементы столбца будут закрашены, согласно тому числовому значению, которое в них размещено. Теперь можно закрывать окно поиска, нажав стандартную кнопку закрытия в верхнем правом углу окна, так как нашу задачу можно считать решенной.
Заливка ячеек в зависимости от значения в Microsoft Excel
- Выделим диапазон с данными, к которым мы хотим применить условное форматирование => кликнем по пункту «Условное форматирование» на панели инструментов => выберем условие выделения «Больше…» и укажем первое условие (если больше 10, то зеленая заливка). Такие же действия повторим для каждого из условий (больше 20 и больше 30). Не смотря на то, что мы применили три правила, данные в таблице закрашены зеленым цветом:
Необходимо открыть вкладку «Power Query». В разделе «Данные Excel» нажимаем кнопку (пиктограмму) «Из таблицы». Далее нужно выбрать диапазон ячеек, из которых нужно «притянуть» информацию и нажимаем «Ок». После выбора области данных появится окно настройки вида новой таблицы.
[expert_bq можно закрывать окно поиска, нажав стандартную кнопку закрытия в верхнем правом углу окна, так как нашу задачу можно считать решенной. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Элемент списка знаком нам по формам на сайтах. Удобно выбирать уже готовые значения. Например, никто не вводит месяц вручную, его берут из такого перечня. Заполнить выпадающий список в Excel можно с использованием различных инструментов. В статье рассмотрим каждый из них.Частичное совпадение текста в excel
- Выбрать ячейку для проверки и перейти в меню ФОРМУЛЫ.
- Нажать на кнопку «Влияющие ячейки»
- При наличии ссылок на другие листы, мы увидим стрелку с указанием на другой лист.
- Необходимо кликнуть два раза по стрелке и вызвать окно перехода.
- В открывшемся окне, выбираем ссылку на необходимый лист и нажимаем ОК.
Теперь нам нужно окрасить элементы, в которых располагаются величины в диапазоне от 400000 до 500000 рублей. В этот диапазон входят числа, которые соответствуют шаблону «4. ». Вбиваем его в поле поиска и щелкаем по кнопке «Найти все», предварительно выделив нужный нам столбец.
[expert_bq только необходимо указать если клиент совершал заказ в текущем месяце, то в соответствующую ячейку следует вводить текстовое значение заказ. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] В первую очередь для регистра с номерами клиентов создадим заголовки столбцов с зеленым цветом и актуальными месяцами, которые будут автоматически отображать периоды времени. Для этого в ячейку B1 введите следующую формулу:Способ 2: использование инструмента «Найти и выделить»
Как в экселе менять цвет ячейки в зависимости от значения – да очень просто и быстро. Для выделения ячеек цветом предусмотрена специальная функция «Условное форматирование», находящаяся на вкладке «Главная»: