Как вставить значение в формулу в Excel

Обновлено: 03.07.2024

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

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

Формулы Excel для добавления текста/символа в ячейку

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

Оператор конкатенации

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

Это работает во всех версиях Excel 2007–Excel 365.

Функция СЦЕПИТЬ

Того же результата можно добиться с помощью функции СЦЕПИТЬ:

Эта функция доступна в Excel для Microsoft 365, Excel 2019–2007.

Функция СЦЕП

Чтобы добавить текст в ячейки в Excel 365, Excel 2019 и Excel Online, вы можете использовать функцию СЦЕП, которая является современной заменой СЦЕПИТЬ:

Это общие подходы, и приведенные ниже примеры показывают, как применять их на практике.

Как добавить текст в начало ячеек

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

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

Кроме того, вы можете указать текстовую строку и ссылку на ячейку в качестве входных параметров для функции СЦЕПИТЬ или СЦЕПИТЬ.

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

Во всех версиях Excel:

В Excel 365 и Excel 2019:

Введите формулу в B2, перетащите ее вниз по столбцу, и во все ячейки будет вставлен один и тот же текст.

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

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

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

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

Как добавить текст в конец ячеек в Excel

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

Например, чтобы добавить строку "-US" в конец ячейки A2, используйте следующие формулы:

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

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

Добавить символы в начало и конец строки

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

Для примера добавим строку "Project:" в начало и "-US" в конец существующего текста в A2.

=CONCATENATE("Проект:", A2, "-US")

=CONCAT("Проект:", A2, "-US")

Если строки вводятся в отдельные ячейки, это работает одинаково хорошо:

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

Чтобы поместить значения из нескольких ячеек в одну, соедините исходные ячейки, используя уже знакомые методы: символ амперсанда, функцию СЦЕПИТЬ или СЦЕПИТЬ.

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

Добавить текст из двух ячеек с помощью амперсанда:

Объедините текст из двух ячеек с помощью CONCAT или CONCATENATE:

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

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

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

=TEXTJOIN(", ", ИСТИНА, A2, B2, C2)

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

Чтобы вставить специальный символ в ячейку Excel, необходимо знать его код в системе ASCII. Как только код установлен, передайте его функции CHAR, чтобы вернуть соответствующий символ. Функция CHAR принимает любое число от 1 до 255. Список печатных кодов символов (значения от 32 до 255) можно найти здесь.

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

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

Как добавить текст в формулу в Excel

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

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

=ТЕКСТ(СЕЙЧАС(), "ч:мм AM/PM")

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

Вставить текст перед формулой:

="Текущее время: "&ТЕКСТ(СЕЙЧАС(), "ч:мм AM/PM")

=СЦЕПИТЬ("Текущее время: ", ТЕКСТ(СЕЙЧАС(), "ч:мм AM/PM"))

=CONCAT("Текущее время: ", ТЕКСТ(СЕЙЧАС(), "ч:мм AM/PM"))

Добавить текст после формулы:

=TEXT(NOW(), "h:mm AM/PM")&" - текущее время"

=СЦЕПИТЬ(ТЕКСТ(СЕЙЧАС(), "ч:мм AM/PM"), " - текущее время")

=CONCAT(ТЕКСТ(СЕЙЧАС(), "ч:мм AM/PM"), " - текущее время")

Добавить текст в формулу с обеих сторон:

="Это " &ТЕКСТ(СЕЙЧАС(), "ч:мм AM/PM")& "здесь, в Гомеле"

=СЦЕПИТЬ("Это ", ТЕКСТ(СЕЙЧАС(), "ч:мм AM/PM"), " здесь, в Гомеле")

=CONCAT("Это ", ТЕКСТ(СЕЙЧАС(), "ч:мм AM/PM"), " здесь, в Гомеле")

Как вставить текст после N-го символа

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

Извлеките подстроку, предшествующую вставленному тексту, с помощью функции LEFT:
LEFT(cell, n)
Извлеките подстроку после текста, используя комбинацию RIGHT и LEN:
RIGHT(cell, LEN(cell) -n)
Объедините две подстроки и текст/символ, используя символ амперсанда.

Полная формула имеет следующий вид:

Одни и те же части можно соединить вместе с помощью функции СЦЕПИТЬ или СЦЕПИТЬ:

Эту задачу также можно выполнить с помощью функции ЗАМЕНИТЬ:

Хитрость заключается в том, что аргумент num_chars, определяющий количество заменяемых символов, имеет значение 0, поэтому формула фактически вставляет текст в указанную позицию в ячейке без замена чего либо. Позиция (аргумент start_num) вычисляется с использованием следующего выражения: n+1.Мы добавляем 1 к позиции n-го символа, потому что текст должен быть вставлен после него.

Например, чтобы вставить дефис (-) после второго символа в A2, формула в B2 будет следующей:

=ВЛЕВО(A2, 2) &"-"& ВПРАВО(A2, ДЛСТР(A2) -2)

=СЦЕПИТЬ(ЛЕВО(A2, 2), "-", ПРАВО(A2, ДЛСТР(A2) -2))

Перетащите формулу вниз, и во все ячейки будет вставлен один и тот же символ:

Как добавить текст до/после определенного символа

Чтобы вставить определенный текст до или после определенного символа, необходимо определить положение этого символа в строке. Это можно сделать с помощью функции ПОИСК:

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

Добавить текст после определенного символа

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

Например, чтобы вставить текст (US) после дефиса в A2, используйте следующую формулу:

=ВЛЕВО(A2, ПОИСК("-", A2)) &"(США)"& ВПРАВО(A2, ДЛИН(A2) - ПОИСК("-", A2))

=СЦЕПИТЬ(ЛЕВАЯ(A2, ПОИСК("-", A2)), "(США)", ПРАВАЯ(A2, ДЛСТР(A2) -ПОИСК("-", A2)))

Вставить текст перед определенным символом

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

Как видите, формулы очень похожи на те, которые вставляют текст после символа. Разница в том, что мы вычитаем 1 из результата первого ПОИСКА, чтобы заставить функцию ВЛЕВО пропустить символ, после которого добавляется текст. К результату второго ПОИСКА мы добавляем 1, чтобы функция ПРАВИЛЬНО нашла этот символ.

Например, чтобы поместить текст (US) перед дефисом в A2, используйте следующую формулу:

=ВЛЕВО(A2, ПОИСК("-", A2) -1) &"(США)"& ВПРАВО(A2, ДЛСТР(A2) -ПОИСК("-", A2) +1)

=СЦЕПИТЬ(ЛЕВАЯ(A2, ПОИСК("-", A2) -1), "(США)", ПРАВАЯ(A2, ДЛСТР(A2) -ПОИСК("-", A2) +1))< /p>

Если исходная ячейка содержит несколько вхождений символа, текст будет вставлен до/после первого вхождения.
Функция ПОИСК нечувствительна к регистру и не может различать строчные и прописные буквы. Если вы хотите добавить текст до или после строчной или прописной буквы, используйте функцию НАЙТИ с учетом регистра, чтобы найти эту букву.

Как добавить пробел между текстом в ячейке Excel

На самом деле это всего лишь частный случай двух предыдущих примеров.

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

Например, чтобы вставить пробел после 10-го символа в ячейках A2:A7, введите приведенную ниже формулу в ячейке B2 и перетащите ее через ячейку B7:

=ВЛЕВО(A2, 10) &" "& ВПРАВО(A2, ДЛСТР(A2) -10)

=СЦЕПИТЬ(ЛЕВО(A2, 10), " ", ПРАВО(A2, ДЛСТР(A2) -10))

Во всех исходных ячейках 10-й символ — двоеточие (:), поэтому пробел вставляется именно там, где он нужен:

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

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

=ВЛЕВО(A2, ПОИСК(":", A2)) &" "& ВПРАВО(A2, ДЛСТР(A2)-ПОИСК(":", A2))

=СЦЕПИТЬ(СЛЕВА(A2, ПОИСК(":", A2)), " ", СПРАВА(A2, ДЛСТР(A2)-ПОИСК(":", A2)))

Как добавить тот же текст в существующие ячейки с помощью VBA

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

Добавить текст в начало

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

Если у вас мало опыта работы с VBA, это пошаговое руководство проведет вас через весь процесс: Как вставить и запустить код VBA в Excel.

Макрос 1: добавляет текст в исходные ячейки

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

Макрос 2: помещает результаты в соседний столбец

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

Добавить текст в конец

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

Макрос 1: добавляет текст в исходные ячейки

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

Макрос 2: помещает результаты в другой столбец

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

Добавьте текст или символ в несколько ячеек с помощью Ultimate Suite

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

Установив Ultimate Suite в Excel, выполните следующие действия:

Выберите исходные данные.
На вкладке Ablebits в группе Текст нажмите "Добавить".
На панели Добавить текст введите символ/текст, который вы хотите добавить в выбранные ячейки, и укажите, куда его следует вставить:
- В начале
- В конце
- Перед определенным текстом/символом
- После определенного текста/символа
- После N-го символа с начала или с конца
Нажмите кнопку Добавить текст. Готово!

В качестве примера давайте вставим строку "PR-" после символа "-" в ячейки A2:A7. Для этого настраиваем следующие параметры:

Мгновение спустя мы получаем желаемый результат:

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

< /p>

Как вставить функцию в Excel?

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

Повторное использование всего: добавляйте наиболее часто используемые или сложные формулы, диаграммы и другие объекты в избранное и быстро используйте их повторно в будущем.
Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты объединения: несколько книг и листов в одну; Объединение нескольких ячеек/строк/столбцов без потери данных; Объединить повторяющиеся строки и суммировать.
Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Одна рабочая книга для нескольких файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
Вставить, пропуская скрытые/отфильтрованные строки; Подсчет и сумма по цвету фона; Массовая рассылка персонализированных электронных писем нескольким получателям.
Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировка по неделям, дням, частоте и т. д.; Фильтр по полужирному шрифту, формулам, комментариям.
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Вставить функцию в Excel

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!

Здесь я возьму, например, суммирование.

<р>1. Выберите пустую ячейку, в которую вы хотите вывести результат, и нажмите «Формулы» > «Вставить функцию». Смотрите скриншот:

< бр /> <р>2. В диалоговом окне «Вставить функцию» укажите категорию функции в поле «Или выберите категорию» и выберите функцию в списке «Выберите функцию». В нашем примере мы выбираем функцию Sum. Смотрите скриншот:

< бр /> <р>3. Нажмите OK, чтобы перейти к следующему шагу. В диалоговом окне «Аргументы функции» выберите число или диапазон чисел в текстовом поле «Число1» (вы можете ввести число в него вручную), если у вас есть несколько диапазонов чисел, вы можете выбрать их в текстовых полях «Число2», «Число3» один за другим. . Смотрите скриншот:

< бр /> <р>4. Если вывод результата не требуется, его можно просмотреть в диалоговом окне «Аргумент функции», а затем нажать кнопку «Отмена», чтобы закрыть диалоговое окно. Если вам нужно, вы можете нажать OK, чтобы вывести результат в ячейку. Смотрите скриншот:

< /p>

Как добавить текст к значению ячейки в Excel?

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

Добавить текст к значению ячейки с формулами

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

Если вы хотите добавить текст в начале и конце значения ячейки, вам может помочь функция конкатенации: =CONCATENATE("text ",A1," text").

Например, я хочу отдельно добавить текст "Местный" в начале и добавить текст "Уровень 3" в конце ячейки A2.

<р>1. Выберите пустую ячейку, введите формулу в строку формул и нажмите клавишу Enter. Затем вы можете увидеть, что определенные тексты добавляются в указанное место существующего значения ячейки, как показано ниже на снимке экрана.

=CONCATENATE("Местный ",A2," Уровень 3")

Примечания:

<р>1. Для добавления текста только в начало значения ячейки вы можете использовать формулу: =СЦЕПИТЬ("текст",A2) .

<р>3. Формула ="текст" &A1, =A1& "текст" или ="текст" &A1& "текст" также может вам помочь. А если вы хотите разделить тексты разделителем (запятой), вы можете добавить «,» в формулу, например =A1&","&"text" .

Добавить текст в значение ячейки с помощью Kutools for Excel

Если вам сложно справиться с формулой, вы можете попробовать утилиту «Добавить текст» в Kutools for Excel.

Перед применением Kutools for Excel сначала загрузите и установите его.

<р>1. Выберите ячейки, в которые нужно добавить текст, затем нажмите Kutools > Текст > Добавить текст.

<р>2.В диалоговом окне «Добавить текст», если вам просто нужно добавить текст перед значением ячейки, введите определенный текст в поле «Текст», выберите параметр «Перед первым символом» и нажмите кнопку «ОК». Смотрите скриншот:

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

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

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

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

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

Например, ячейка A3 ниже содержит формулу, которая складывает значение ячейки A2 со значением ячейки A1.

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

Введите формулу

Чтобы ввести формулу, выполните следующие действия.

<р>1. Выберите ячейку.

<р>2. Чтобы Excel знал, что вы хотите ввести формулу, введите знак равенства (=).

<р>3. Например, введите формулу A1+A2.

Совет. Вместо ввода A1 и A2 просто выберите ячейку A1 и ячейку A2.

<р>4. Измените значение ячейки A1 на 3.

Excel автоматически пересчитывает значение ячейки A3. Это одна из самых мощных функций Excel!

Редактировать формулу

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

<р>1. Чтобы изменить формулу, нажмите в строке формул и измените формулу.

Приоритет оператора

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

Сначала Excel выполняет умножение (A1 * A2). Затем Excel добавляет к этому результату значение ячейки A3.

Сначала Excel вычисляет часть в скобках (A2+A3). Затем он умножает этот результат на значение ячейки A1.

Копировать/вставить формулу

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

<р>1. Введите приведенную ниже формулу в ячейку A4.

2а. Выберите ячейку A4, щелкните правой кнопкой мыши и выберите "Копировать" (или нажмите CTRL + C).

<р>. затем выберите ячейку B4, щелкните правой кнопкой мыши и выберите "Вставить" в разделе "Параметры вставки:" (или нажмите CTRL + v).

2б. Вы также можете перетащить формулу в ячейку B4. Выберите ячейку A4, щелкните в правом нижнем углу ячейки A4 и перетащите ее в ячейку B4. Это намного проще и дает точно такой же результат!

Результат. Формула в ячейке B4 ссылается на значения в столбце B.

Вставить функцию

Все функции имеют одинаковую структуру. Например, СУММ(A1:A4). Имя этой функции SUM. Часть в скобках (аргументы) означает, что мы даем Excel диапазон A1: A4 в качестве входных данных. Эта функция складывает значения в ячейках A1, A2, A3 и A4. Нелегко запомнить, какую функцию и какие аргументы использовать для каждой задачи. К счастью, функция «Вставить функцию» в Excel поможет вам в этом.

Чтобы вставить функцию, выполните следующие шаги.

<р>1. Выберите ячейку.

<р>2. Нажмите кнопку "Вставить функцию".

Появится диалоговое окно "Вставить функцию".

<р>3. Найдите функцию или выберите функцию из категории. Например, выберите СЧЁТЕСЛИ из категории Статистические данные.

Откроется диалоговое окно "Аргументы функции".

<р>5. Щелкните поле "Диапазон" и выберите диапазон A1:C2.

<р>6. Нажмите в поле Критерии и введите >5.

Результат. Функция СЧЁТЕСЛИ подсчитывает количество ячеек больше 5.

Примечание: вместо использования функции "Вставить функцию" просто введите =СЧЁТЕСЛИ(A1:C2,">5"). Когда вы дойдете до: =СЧЁТЕСЛИ( вместо ввода A1:C2, просто выберите диапазон A1:C2.

Читайте также: