Как вставить амперсанд в excel
Как используется функция СЦЕПИТЬ в Excel
Здравствуй дорогой, читатель!
В этой статье я бы хотел осветить такую возможность Excel, как соединение нескольких ячеек с разными адресами в одно значение с одним адресом. Соединить, возможно, любое значение, будь то текст или число, если используется функция СЦЕПИТЬ в Excel и эта операция доступна одновременно для 255 ячеек. Так что можно даже собрать какой-нибудь рассказ.
Применять возможность сцепления ячеек в Excel в основном нужно когда вы комбинируете тексты с цифрами, собираете новое значение для дальнейших вычислений или вам нужен результат, в той форме которую получить вычислениями невозможно.
А также часто функция СЦЕПИТЬ используется когда нужно работать с выгруженными данными других программ, так там данные вводятся по ячейкам и соответственно так и выгружаются, а вот нам есть необходимость работать с соединенными значениями или с измененными знаками точки вместо запятых, и таким образом, мы приходим к тому что используем рассматриваемую функцию.
На данный момент существует 3 способа сцепить ячейки в Excel:
Но рассмотрим мы только первые 2 варианта, ну а о третем поговорим уж в другой раз. Итак:
С помощью функции СЦЕПИТЬ, как я выше уже писал, можно объединить до 255 ячеек и до 8 192 символов. Что позволяет производить громадное количество комбинаций для наших нужд. Есть также небольшой нюанс в использовании, не стоить забывать о пробелах, программа, увы, сама их не ставит и что бы не получить слитый текст в одно слово, между значениями нужно указать пробел как текстовое значение (пример: “ “). В таком случае функция СЦЕПИТЬ в Excel сработает на 100% и вы получите нужный вам результат.
2. Сцепить диапазон ячеек в Excel при помощи оператора & (амперсанд) вместо функции СЦЕПИТЬ
Амперсанд — это своеобразный знак “+” для текстовых значений, которые нам нужно соединить. Найти амперсанд можно на клавиатуре, возле циферки “7”, ну по крайней мере, на большинстве клавиатур там он и находится. А если его нет, значит, внимательно посмотрите куда его перенесли. А так данный вариант похож, как и функция СЦЕПИТЬ, за исключением специфики орфографии и об этом не стоит забывать, так, к примеру, функция СЦЕПИТЬ сама ставит кавычки, а вот при использовании амперсанда вы прописываете их вручную. Но вот в возможности склеить значения в ячейках Excel по скорости, использование 2 варианта самое оптимальное.
Рассмотрим несколько примеров по использувании функции СЦЕПИТЬ в Excel:
Пример №1:
Нам надо сцепить текстовые значения, а именно ФИО сотрудников выгруженное с другой программы (зачастую выгруженные таблицы базы данных), но разбросанное в разных ячейках. Задача на первый взгляд легка, так оно, конечно, и есть, за исключением того, что нам надо фамилия и инициалы сотрудников, то есть сократить имя и по отчеству. И это можно сделать если использовать сочетание функций, а именно функция, которая, позволяет извлекать из текста первые буквы — функция ЛЕВСИМВ, в таком случае мы получим фамилию с инициалами в одной формуле.
Пример №2:
Нам надо сцепить разрозненную информацию о договорах его номер и дату заключения. К примеру, у нас есть “Договор на транспортные перевозки” “№23” “02.09.2015” и нам надо получить все данные одним предложением “Договор на транспортные перевозки №23 от 02.09.2015 года”. Но при использовании возможности сцепить диапазон ячеек с помощью функции СЦЕПИТЬ, мы не сможем получить нужный нам результат так будет произведена склейка текстовых значений, а нас есть дата. Соответственно, данные будут исковерканы. Для получения результата необходимо использовать дополнительно функцию ТЕКСТ. Она позволит, назначить для даты соответствующий формат «ДД.ММ.ГГГГ», соответственно формату мы получим данные двузначные для дней “ДД” и месяца “ММ” и четырёхзначное для года “ГГГГ”. Таким образом, мы сможем получить правильный конечный результат.
Файл с примерами можно забрать сдесь.
А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!
Не забудьте поблагодарить автора!
Экономия — способ тратить деньги без удовольствия.
Арман Салакру
Вставка текста в ячейку с формулой в Microsoft Excel
Довольно часто при работе в Excel существует необходимость рядом с результатом вычисления формулы вставить поясняющий текст, который облегчает понимание этих данных. Конечно, можно выделить для пояснений отдельный столбец, но не во всех случаях добавление дополнительных элементов является рациональным. Впрочем, в Экселе имеются способы поместить формулу и текст в одну ячейку вместе. Давайте разберемся, как это можно сделать при помощи различных вариантов.
Процедура вставки текста около формулы
Если просто попробовать вставить текст в одну ячейку с функцией, то при такой попытке Excel выдаст сообщение об ошибке в формуле и не позволит совершить такую вставку. Но существует два способа все-таки вставить текст рядом с формульным выражением. Первый из них заключается в применении амперсанда, а второй – в использовании функции СЦЕПИТЬ.
Способ 1: использование амперсанда
Самый простой способ решить данную задачу – это применить символ амперсанда (&). Данный знак производит логическое отделение данных, которые содержит формула, от текстового выражения. Давайте посмотрим, как можно применить указанный способ на практике.
У нас имеется небольшая таблица, в которой в двух столбцах указаны постоянные и переменные затраты предприятия. В третьем столбце находится простая формула сложения, которая суммирует их и выводит общим итогом. Нам требуется в ту же ячейку, где отображается общая сумма затрат добавить после формулы поясняющее слово «рублей».
При этом, если мы попытаемся поставить пробел вручную, то это ничего не даст. Как только будет нажата кнопка Enter, результат снова «склеится».
Естественно, что все указанные действия проделывать не обязательно. Мы просто показали, что при обычном введении без второго амперсанда и кавычек с пробелом, формульные и текстовые данные сольются. Вы же можете установить правильный пробел ещё при выполнении второго пункта данного руководства.
При написании текста перед формулой придерживаемся следующего синтаксиса. Сразу после знака «=» открываем кавычки и записываем текст. После этого закрываем кавычки. Ставим знак амперсанда. Затем, в случае если нужно внести пробел, открываем кавычки, ставим пробел и закрываем кавычки. Щелкаем по клавише Enter.
Для записи текста вместе с функцией, а не с обычной формулой, все действия точно такие же, как были описаны выше.
Текст также можно указывать в виде ссылки на ячейку, в которой он расположен. В этом случае, алгоритм действий остается прежним, только сами координаты ячейки в кавычки брать не нужно.
Способ 2: применение функции СЦЕПИТЬ
Также для вставки текста вместе с результатом подсчета формулы можно использовать функцию СЦЕПИТЬ. Данный оператор предназначен для того, чтобы соединять в одной ячейке значения, выводимые в нескольких элементах листа. Он относится к категории текстовых функций. Его синтаксис следующий:
Всего у этого оператора может быть от 1 до 255 аргументов. Каждый из них представляет либо текст (включая цифры и любые другие символы), либо ссылки на ячейки, которые его содержат.
Посмотрим, как работает данная функция на практике. Для примера возьмем все ту же таблицу, только добавим в неё ещё один столбец «Общая сумма затрат» с пустой ячейкой.
Устанавливаем курсор в поле «Текст1». Вписываем туда слово «Итого». Писать текстовые выражения можно без кавычек, так как программа проставит их сама.
Потом переходим в поле «Текст2». Устанавливаем туда курсор. Нам нужно тут указать то значение, которое выводит формула, а значит, следует дать ссылку на ячейку, её содержащую. Это можно сделать, просто вписав адрес вручную, но лучше установить курсор в поле и кликнуть по ячейке, содержащей формулу на листе. Адрес отобразится в окошке аргументов автоматически.
В поле «Текст3» вписываем слово «рублей».
После этого щелкаем по кнопке «OK».
Между кавычками должен находиться пробел. В целом в строке функций должно отобразиться следующее выражение:
=СЦЕПИТЬ(«Итого»;» «;D2;» «;»рублей»)
Щелкаем по клавише ENTER. Теперь наши значения разделены пробелами.
Таким образом, можно сказать, что существуют два способа вписать в одну ячейку формулу и текст: при помощи амперсанда и функции СЦЕПИТЬ. Первый вариант проще и для многих пользователей удобнее. Но, тем не менее, в определенных обстоятельствах, например при обработке сложных формул, лучше пользоваться оператором СЦЕПИТЬ.
Помимо этой статьи, на сайте еще 12516 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Функция СЦЕПИТЬ (CONCATENATE)
В этой заметке рассмотрим, как склеить содержимое из нескольких ячеек, чтобы все оказалось в одной ячейке. Это распространенная задача и она довольно легко решается с помощью функции Excel СЦЕПИТЬ. Эта функция умеет не только соединять ячейки, но и добавлять любые другие символы. В строке формул выглядит примерно так.
Здесь между ячейками вставлены пробелы (» «) и в конце дорисовывается восклицательный знак.
Однако эти самые пробелы являются первым неприятным моментом при использовании функции СЦЕПИТЬ, т.к. функция склеивает ячейки без «шва» и пробелы приходится добавлять вручную. Если нужно соединить только несколько слов, то беда не большая, но когда нужно склеить хотя бы с десяток ячеек, то ручная добавка пробелов слегка утомляет. Чтобы немного облегчить процесс, можно посоветовать вставить пробел в отдельную ячейку и затем просто делать на нее ссылку.
Так дело идет немного быстрее. Хотя и не очень эффективно – потребуется занять отдельную ячейку. А если нечаянно эту ячейку удалить, то получится вообще нехорошо. В целом при небольшом количестве данных, работают оба варианта.
Кстати, не все знают, что в Excel для склеивания ячеек также предусмотрен специальный оператор – & (амперсанд). Его нужно просто поставить между связываемыми ячейками или символами.
Вставлять амперсанд не удобно – нужно вначале переключиться на английскую раскладку, а затем нажать Shift + 7. Поэтому рекомендуется воспользоваться специальной комбинацией – удерживая клавишу Alt нажать 3 и 8 на цифровой клавиатуре. Здорово помогает сохранить душевное равновесие.
Формула СЦЕПИТЬ и амперсанд исправно работают до тех пор, пока не приходится соединять большое количество ячеек. Замучаешься вставлять «;» либо «&». Более того, между ячейками, как привило, добавляется пробел. К сожалению, указать в качестве аргумента целый диапазон нельзя (уже можно). Выход из этой ситуации следующий.
Функция СЦЕПИТЬ для большого количества ячеек
Один из вариантов заключатся в том, чтобы в качестве ссылки на ячейки указать массив данных. Следует сразу отметить, что данные могут располагаться по горизонтали или вертикали. Если данные расположены в одной строке, то делаем следующее. Для примера, отдельные по ячейкам слова находится в 5-й строке. Теперь в пустой ячейке указываем весь диапазон для соединения и через амперсанд (&) добавляем пробел (» «).
Затем нажимаем F9, для того, чтобы формула выдала результат вычисления, в нашем случае это будет массив.
Как видно к каждому слову добавился пробел, а между словами стоит точка с запятой – как раз то, что нужно для вставки в формулу СЦЕПИТЬ. Теперь убираем лишние скобки и вставляем этот массив в формулу. Нажимаем Enter.
Данные могут быть расположены не по горизонтали, (как в примере выше), а по вертикали. В этом случае в получаемом массиве значения будут разделены двоеточием. Придется сделать один лишний шаг, чтобы заменить их на точку с запятой (через Найти и заменить, горячая клавиша Ctrl + H, либо еще быстрее — использовать функцию ТРАНСП, чтобы из горизонтального массива сделать вертикальный).
Этот вариант работает неплохо, но подходит для одноразового использования, т.к. исходная информация не связана с исходными ячейками. Для изменения придется лезть внутрь формулы или повторять действия для новых данных. Кроме того, существует ограничение по длине одной формулы, и соединить таким образом несколько тысяч ячеек не всегда получается.
Поэтому переходим к следующему способу. Он, признаться, выглядит не очень эстетично. Зато дешево, надежно, и практично! (с) Во всяком случае прием помогает решить проблему. А это самое главное. Нам потребуется один дополнительный столбец (или строка, смотря как расположены исходные данные для соединения).
Рассмотрим только вертикальное расположение данных (горизонтальное будет работать аналогично). Суть в том, чтобы рядом с данными создать столбец с формулой СЦЕПИТЬ, которая последовательно будет присоединять по одному слову по мере продвижения вниз. В нашем случае первая формула будет содержать только слово «Александр » (и пробел), следующая ниже добавляет «Сергеевич » (и пробел) и т.д.
Искомой ячейкой будет самая последняя, объединяющая уже все слова. Результат можно оставить в виде формулы, а можно сохранить как значение, удалив все расчеты.
Соединение текста и даты
И еще один трюк с формулой СЦЕПИТЬ. Довольно часто приходится соединять текст с датой. Дата, как известно, это простое порядковое число (отсчет начинается с 1 января 1900 года), которому придается нужный формат даты. Если же соединить напрямую текст и дату, то на выходе получится указанный текст и простое число. Возьмем, например, слово «Сегодня » (не забываем добавить пробел в конце) и функцию СЕГОДНЯ (всегда возвращающую текущую дату).
Как видно, ничего не получилось.
Для правильной склейки нужно «обернуть» дату в формулу ТЕКСТ, которая придает указанный формат числу.
Формат ДД.ММ.ГГ указывает, в каком виде должна отражаться дата. Если, например, указать формат ДД.ММ.ГГГГ, то дата будет выглядеть так: 03.09.2015 (год состоит из 4-х цифр).
Кстати, добавлять текст к числу или дате можно не только с помощью функции СЦЕПИТЬ. Можно использовать пользовательский числовой формат. У меня когда-то в рабочем файле красовалась автоматически обновляемая надпись.
Поэтому я всегда был прекрасно осведомлен о текущей дате и дне недели. При этом содержимое ячейки состояло только из одной функции СЕГОДНЯ.
Реализовать подобную идею довольно просто. Но это уже чистое форматирование. Расскажу при случае подробней.
Объединить текст в Excel
Необходимость объединить текст в Excel появляется очень при работе с многими документами, но многие не знают, как можно очень просто и быстро справится с этой задачей с помощью табличного редактора MS Excel.
В этой статье мы на примерах рассмотрим, как в Excel объединить ячейки с текстом. Объединить текст в нескольких ячейках можно несколькими способами. Рассмотрим каждый из них.
Все рассмотренные далее способы объединения текста применяются для всех версий: Excel 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2016. Рассмотрим каждый из них.
Объединить ячейки с текстом с помощью знака амперсанда
Итак, первый способ. Самый простой способ объединить текст в нескольких ячейках в Excel – это использовать знак амперсанда (&).
Например, у нас есть таблица с тремя столбцами в которых содержится фамилия, имя и отчество сотрудников компании или учащихся, студентов.
Объединить текст в Excel – Таблица с данными
Нам необходимо создать столбец, который будет содержать в одной ячейке фамилию, имя и отчество. То есть объединить текст в нескольких ячейках Excel.
3. Далее нам необходимо поставить разделитель, чтобы наша фамилия «не слиплась» с именем и отчеством. В данном случае мы будем использовать пробел. Для этого после амперсанда ставим пробел, заключенный в кавычки ( » » ).
Разделитель необязательно должным пробелом, вы можете использовать любой символ, знак или комбинацию. Для этого необходимо заключить их в кавычки. Например, если вы хотите разделить объединяемый текст с помощью запятых, то используйте запятую и пробел после нее ( «, » ).
5. Опять таки не забываем по разделитель – пробел в кавычках ( » » ).
7. Нажимаем клавишу Enter и любуемся полученным результатом.
Получившаяся формула выглядит так:
И в нашей ячейке D2 появился объединенный текст из трех ячеек.
Объединить текст в Excel – Объединить текст с помощью амперсанда
Чтобы заполнить все оставшиеся ячейки достаточно выделить ячейку, и перетащить маркер заполнения вниз. Теперь все ячейки заполнены объединенным текстом, и столбец « ФИО » выглядит следующим образом:
Объединить текст в Excel – Объединенный текст для всех сотрудников
Если необходимо объединить текст в двух ячейках, например, объединить фамилию и имя, то формула для первого человека будет выглядеть так:
Объединить текст в Excel – Объединить 2 ячейки с текстом
Давайте рассмотрим еще один случай, когда необходимо объединить текст в нескольких ячейках, но не полностью. Например, получить в ячейке фамилию и инициалы человека.
Для получения желаемого результата введите следующую формулу:
Таким образом мы получим следующую таблицу:
Объединить текст в Excel – Объединенный текст фамилии и инициалов с помощью амперсанда
Обратите внимание, что в данном случае мы в качестве разделителя используем точку и пробел ( «. » ). Перейдем с следующему способу, как объединить текст в ячейках в Excel.
Объединить текст в ячейках с помощью функции СЦЕПИТЬ
В этом примере для объединения текста в ячейках мы будем использовать функцию «СЦЕПИТЬ», которая объединяет две или несколько текстовые строки в одну, позволяя комбинировать их с произвольным текстом. Рассмотрим пример, как в Excel объединить столбцы с текстом.
Добавим к таблице столбец с названием города.
Объединить текст в Excel – Дополнение исходной таблицы
Пусть мы хотим не только объединить ячейки с текстом, но и составить такого вида текст: « Иванов Иван Иванович, проживающий в городе Москва ».
Тогда в ячейке мы вводим в следующую формулу:
=СЦЕПИТЬ(A2;» «;B2;» «; C2;», проживающий в городе «;D2)
Заполняем оставшиеся ячейки с помощью маркера заполнения и в результате получаем такой результат:
Объединить текст в Excel – Объединение текста с помощью формулы СЦЕПИТЬ
Рассмотрим случай, когда нам необходимо объединить текст, дату и числа из разных ячеек в одну.
Объединить текст с датой или числами
Данная функция ТЕКСТ позволяет изменить представление числа или времени в удобочитаемом виде. В нашем случае мы будем использовать ее для корректного отображения числа и даты при объединении числовых значений и значений даты с текстовой строкой.
Предположим у нас есть следующая таблица:
Объединить текст в Excel – Исходная таблица с данными
Предположим, что мы хотим не просто объединить текст, а составить предложение, в котором будет говорится, что такой-то сотрудник, такого-то числа реализовал столько-то товаров, и сколько это составило в процентах от общего объема продаж.
Для этого мы должны ввести в ячейку (D2) следующую формулу для первого сотрудника:
=СЦЕПИТЬ(A2; » «; ТЕКСТ($B$1;»ДД.ММ.ГГ»);» реализовал «; B2;» единиц товаров, что составило «;ТЕКСТ(C2;»0%»);» от общего объема продаж»)
Не стоит пугаться. Сейчас мы разберем эту формулу по порядку.
1. А2 – ссылка на ячейку фамилии нашего сотрудника.
2. » « – далее мы вставляем разделитель, в нашем случае это пробел.
3. ТЕКСТ($B$1;»ДД.ММ.ГГ») – при помощи функции ТЕКСТ мы задаем вид даты.
$B$1 – это абсолютная ссылка на дату, ДД.ММ.ГГ – это формат отображаемой даты. В нашем случае исходная дата 13 ноября 2017 г., а мы ее преобразовываем в 13.11.17 (ДД.ММ.ГГ).
4. » реализовал » – продолжаем формировать необходимое нам предложение.
5. B2 – ссылка на ячейку, где хранится информация о количестве реализованной продукции.
6. » единиц товаров, что составило » – добавляем текст.
7. ТЕКСТ(C2;»0%») – ссылка на ячейку C2 указывает на количество процентов, а «0%» – процентный формат.
8. » от общего объема продаж» – завершаем наше предложение.
И заполняем для остальных сотрудников с помощью маркера заполнения.
В результате мы получаем следующее:
Объединить текст в Excel – Объединить текст в ячейке с датой и числами
Ну вот, в итоге мы объединили текст из нескольких ячеек и получили полноценные предложения, которые можно будет вставить, к примеру в отчет.
Объединить текст в ячейках с помощью функции ОБЪЕДИНИТЬ
В версии MS Excel 2016 появилась еще одна функция для объединения текста – функция ОБЪЕДИНИТЬ. Если вы пользователь более ранних версий, то для того чтобы объединить текст, используйте вышеописанные способы.
С помощью функции ОБЪЕДИНИТЬ можно объединить текст из нескольких ячеек, строк или диапазонов.
Сперва разберем синтаксис функции ОБЪЕДИНИТЬ:
Рассмотрим пример, как объединить текст в Excel используя функцию ОБЪЕДИНИТЬ.
