Как сделать подстановку текста в Excel?

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

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

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

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

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

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

Функция Т

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

 Синтаксис функции:

= Т(_значение_), где:

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

Пример применения:

Функция ПЕЧСИМВ

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

Синтаксис функции:

= ПЕЧСИМВ(_текст_), где:

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

Пример применения:

Функция РУБЛЬ

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

 Синтаксис функции:

= РУБЛЬ(_число_, _число_знаков_), где:

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

Пример применения:   Стоить помнить, что форматирование ячейки с помощью функции РУБЛЬ, в отличие от стандартного форматирование через контекстное меню, превращает свой результат в текст, в отличие от других способов. Но, в принципе, использовать в формулах результат функции возможно, так как MS Excel имеет возможность превращать числа которые вносятся как текст, в процессе вычисления в числовые значения.

Функция ПОИСК

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

Синтаксис функции:

ПОИСК (_искомый_текст_; _текст_для_начала_поиска_;[_начальная_позиция_]), где:

• искомый текст – указывается текстовое значение которое необходимо отыскать. Можно использовать символы подстановки;
• текст для начала поиска – указывается текст или ссылка на ячейку содержащую текстовое значение;
• начальная позиция – является необязательным аргументом и при его отсутствии по умолчанию имеет значение 1. Указывает с какой позиции в тексте необходимо начать поиск.

Пример применения:

Функция ТЕКСТ

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

Обращаю внимание! Функция ТЕКСТ производит преображение числовых значений в текст и как следствие вычисление в дальнейшем станет невозможным. Рекомендую исходные данные сохранять отдельно от результатов использования функции. При необходимости создания формул всегда есть возможность обратится к исходным значениям, что исключит ошибку при использовании результатов работы функции ТЕКСТ.

Синтаксис функции:

= ТЕКСТ(_значение_;_формат_), где:

• значение – указывается числовое значение или указывается ссылка на него;
• формат – вводится формат который будет применен к аргументу «Значение».

Пример применения:

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

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

Синтаксис функции:

= СЦЕПИТЬ(_текст№1_;_ [текст№2]_;….), где:

• текст№1 – являет собой первый элемент который нужно сцепить с другими, обязательный аргумент;
• текст№2 – не является обязательным аргументом. Предоставляет 255 значений, как элементы слияния.

Пример применения:Обращаю ваше внимание что функция не умеет ставить пробелы между аргументами, поэтому вы сделаете это самостоятельно. Так же при желании можно сцеплять текст с помощью знака амперсанда «&» не используя функцию. Более детально о возможностях вы прочтете в статье «Как используется функция СЦЕПИТЬ в Excel».

Функция ФИКСИРОВАННЫЙ

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

Синтаксис функции:

= ФИКСИРОВАННЫЙ(_число_;_число_знаков_;_без_разделителей_), где:

• число – это ссылка на числовое значение или число, которое будет округлено и превращено в текст;
• число знаков – указываем количество цифр после запятой;
• без разделителей – этот аргумент является логическим значением и если он указан как ИСТИНА, то функция не будет включать разделители тысяч в текст который возвращается.

Пример применения:  Обращаю ваше внимание что для аргумента «Число знаков» есть возможность указать до 127 значащих цифр, также если аргумент отрицательный, число будет округлено до десятичного знака, а в случае отсутствия аргумента, по умолчанию его значение будет равно 2. Если для аргумента «Без разделителей» указана ЛОЖЬ или он отсутствует, разделители тысяч будут включены. Также напоминаю, что отформатированное число функцией ФИКСИРОВАННЫЙ будет переделано в текст.

Источник: http://topexcel.ru/tekstovye-funkcii-v-excel-chast-3/

Режим MultiRow: один документ с данными из нескольких строк

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

Предположим, что у нас есть вот такая таблица в Excel, и перед нами стоит задача сформировать приказ на премирование сотрудников:

• Причём, должно получиться не 14 отдельных документов (по количеству сотрудников), а один (или 4, по одному приказу на каждый отдел)
• В этом нам поможет режим с названием MultiRow, реализованный специально для таких задач.
• Включить режим MultiRow можно в настройках на вкладке «Дополнительно»:

Если нам нужен ОДИН общий приказ, мы включаем только первую галочку («Включить режим Multirow»), а если нужны отдельные приказы для 4 отделов, то включаем вторую опцию «Формировать по каждому шаблону столько файлов, сколько уникальных значений содержится в столбце», и указываем номер столбца с отделами — 3 «С»:

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

Инструкции по настройке шаблонов для режима Multirow:

• для шаблонов формата Excel
• для шаблонов формата Word

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

ВНИМАНИЕ: настроенный файл шаблона и исходная таблицу Excel, на примере которых написана эта инструкция, прикреплены в конце этой статьи.
Можете скачать файлы, закинуть шаблон в папку Шаблоны, открыть таблицу Excel, и протестировать.

После того, как мы настроили шаблон, и поместили его в папку Шаблоны, можно пробовать сформировать документы.
Для этого:

1. выделяем в исходной таблице несколько строк
   (либо в настройках на вкладке «Исходная таблица» включаем галочку «Формировать документы ПО ВСЕМ заполненным строкам таблицы»)
2. нажимаем на панели инструментов кнопку

Если мы всё настроили правильно, то по завершении заполнения приказов прогрессбар будет выглядеть так:

а в папке Документы должно появиться 4 файла: (использовалась маска имени файла вида {%filename%} — {Отдел}.{%ext%} )

Открываем один из сформированных файлов, и видим в нём следующее:

Если же мы в настройках не включали опцию «Формировать по каждому шаблону столько файлов, сколько уникальных значений содержится в столбце», то на выходе должны получить ОДИН документ, с данными из всех строк исходной таблицы:

Источник: https://ExcelVBA.ru/programmes/FillDocuments/manuals/multirow

Работа со строками в Excel. Текстовые функции Excel

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

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

В этой статье я коротко опишу необходимые функции для работы со строками категории «Текстовые» и некоторые рассмотрим на примерах.

Функции категории «Текстовые»

Итак, рассмотрим основные и полезные функции категории «Текстовые», с остальными можно ознакомиться самостоятельно.

• БАТТЕКСТ (Значение) – функция преобразующая число в текстовый тип;
• ДЛСТР (Значение) – вспомогательная функция, очень полезна при работе со строками. Возвращает длину строки, т.е. кол-во символов содержащихся в строке;
• ЗАМЕНИТЬ (Старый текст, Начальная позиция, число знаков, новый текст) – заменяет указанное кол-во знаков с определенной позиции в старом тексте на новый;
• ЗНАЧЕН (Текст) – преобразует текст в число;
• ЛЕВСИМВ (Строка, Кол-во знаков) – очень полезная функция, возвращает указанное кол-во символов, начиная с первого символа;
• ПРАВСИМВ (Строка, Кол-во знаков) – аналог функции ЛЕВСИМВ, с той лишь разницей, что возврат символов с последнего символа строки;
• НАЙТИ (текст для поиска, текст в котором ищем, начальная позиция) – функция возвращает позицию, с которой начинается вхождение искомого текста. Регистр символов учитывается. Если необходимо не различать регистр символов, воспользуйтесь функцией ПОИСК. Возвращается позиция только первого вхождения в строке!
• ПОДСТАВИТЬ (текст, старый текст, новый текст, позиция) – интересная функция, на первый взгляд похожа на функцию ЗАМЕНИТЬ, но функция ПОДСТАВИТЬ способна заменить на новую подстроку все вхождения в строке, если опущен аргумент «позиция»;
• ПСТР (Текст, Начальная позиция, Кол-во знаков) – функция похожа на ЛЕВСИМВ, но способна возвратить символы с указанной позиции:
• СЦЕПИТЬ (Текст1, Текст 2 …. Текст 30) – функция позволяет соединить до 30-ти строк. Так же, можно воспользоваться символом «&», выглядеть будет так «=”Текст1” & ”Текст2” & ”Текст3”»;

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

Пример 1 Дан набор строк:

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

Извлечем в столбец В номера накладных. Для этого найдем так называемый ключевой символ или слово. В нашем примере видно, что перед каждым номером накладной стоит «№», а длина номера накладной 6 символов. Воспользуемся функциями НАЙТИ и ПСТР. Пишем в ячейку B2 следующую формулу :

=ПСТР(A2;НАЙТИ(«№»;A2)+1;6)

Разберем формулу. Из строки А2 с позиции следующей после найденного знака «№», мы извлекаем 6 символов номера.

Теперь извлечем дату. Тут все просто. Дата расположена в конце строки и занимает 8 символов. Формула для С2 следующая:

• =ПРАВСИМВ(A2;8)
• но извлеченная дата у нас будет строкой, чтоб преобразовать ее в дату необходимо после извлечения, текст перевести в число:
• =ЗНАЧЕН(ПРАВСИМВ(A2;8))
• а затем, задать формат отображения в ячейке, как это сделать было описано в статье «Формат данных в Excel».

Ну и последнее, для удобства дальнейшей фильтрации строк, введем столбец месяц, который мы получим из даты. Только для создания месяца нам необходимо откинуть день и заменить его на «01». Формула для D2:

=ЗНАЧЕН(СЦЕПИТЬ(«01»;ПРАВСИМВ(A2;6))) или =ЗНАЧЕН(«01″&ПРАВСИМВ(A2;6))

Задайте формат ячеке «ММММ ГГГГ». Результат:

1. Пример 2   В строке «Пример работы со строками в Excel» необходимо все пробелы заменить на знак «_», так же перед словом «Excel» добавить «MS».
2. Формула будет следующая:
3. =ПОДСТАВИТЬ(ЗАМЕНИТЬ(A1;ПОИСК(«excel»;A1);0;»MS «);» «;»_»)

Для того, чтоб понять данную формулу, разбейте ее на три столбца. Начните с ПОИСК,  последней будет ПОДСТАВИТЬ.

Источник: https://www.programm-school.ru/tekstovie_funkcii_excel.html

Подставить и заменить в excel, функции подставить() и заменить() в эксель

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

Но как обычно в номера машин добавляют пробелы или слеши, а самый худший вариант букву О вместо нуля  Если вы регулярно выполняете одну и ту же операцию, то вручную нажимать Ctrl + H каждый раз неудобно, плюс придется запоминать какие именно символы нужно заменять.

Функции ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel это отличный выход из ситуации.

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

Есть номер А 777 АА 01 из него надо формулой сделать А777АА01 формулой.

Функция ПОДСТАВИТЬ в Excel

Как работает формула показано на первой картинке. Состав разберем ниже:

ПОДСТАВИТЬ(текст;стар_текст;нов_текст;[номер_вхождения])

• Текст — адрес ячейки или сам текст в котором надо сделать замену символов, в примере А 777 АА 01
• Стар_текст — что нам нужно заменить? Пробел.
• Нов_текст — на что нужно заменить? Меняем на ничего, т.е. на пусто в формуле, это будет «»
• Номер_вхождения — заполнять необязательно, но при этом реквизит очень полезный, в официальной справке говорится о «вхождении символа», но объясню простым языком. Здесь вы можете указать номер символа в строке (пробела в нашем случае). Т.е. если нужно заменить только пробел между 777 и АА (это второй по счету пробел в номере), формула будет выглядеть как

=ПОДСТАВИТЬ(A:A;» «;»»;2)

И в итоге вы получите А 777АА 01

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

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A:A;» «;»»);».»;»»);»/»;»»)

В данном случае мы убираем пробел, точку, и слэш в номере «А 777 АА/01.»

Функция ЗАМЕНИТЬ и ЗАМЕНИТЬБ в Excel

Функция ЗАМЕНИТЬ делает почти все тоже самое, что и подставить, но делает это с определенного символа. Аргументы такие:

ЗАМЕНИТЬ(стар_текст;начальная_позиция;число_знаков;нов_текст)

Пример. Необходимо первую букву номера машины заменить на другую. Из А777АА01 сделать Б777АА01

Сначала описание аргументов:

• Стар_текст — как и в ПОДСТАВИТЬ, текст в котором нужно заменить т.е. А777АА01
• Начальная_позиция — с какого символа производить замену, с первого.
• Число_знаков сколько знаков меняем — один
• Нов_текст — и на что меняем? На букву Б

ЗАМЕНИТЬБ — это маленькая загадка Excel, даже в иностранных интернетах мало информации о ней. Если вкратце, то просто ЗАМЕНИТЬ меняет любой символ (даже если он размером 2 байта), ЗАМЕНИТЬБ меняет только однобайтные символы (аргумент число_знаков меняется на). Практического применения я для себя не нашел 🙂 Если кто-нибудь подскажет, буду благодарен!

Источник: https://excelworks.ru/2017/02/16/funktsii-podstavit-i-zamenit-v-excel/

Функция ПОДСТАВИТЬ() в MS EXCEL

Функция ПОДСТАВИТЬ(), английский вариант SUBSTITUTE(), заменяет определенный текст в текстовой строке на новое значение. Формула =ПОДСТАВИТЬ(A2; «январь»;»февраль») исходную строку «Продажи (январь)» превратит в строку «Продажи (февраль)».

Синтаксис функции

ПОДСТАВИТЬ(исходный_текст;старый_текст;новый_текст;[номер_вхождения])

Исходный_текст  — текст или ссылка на ячейку, содержащую текст, в котором заменяется часть строки. Старый_текст  — заменяемый текст. Новый_текст  — текст, на который заменяется старый_текст.

Номер_вхождения  — определяет, какое вхождение фрагмента Старый_текст нужно заменить фрагментом. Если этот аргумент определен, то заменяется только заданное вхождение фрагмента Старый_текст.

В противном случае все вхождения фрагмента Старый_текст в текстовой строке заменяются фрагментом Новый_текст.

Примеры

• Пусть в ячейке А6 введена строка Продажи (январь), прибыль (январь).
• Чтобы заменить оба слова январь, на февраль, запишем формулу (см. файл примера внизу статьи):
• =ПОДСТАВИТЬ(A6; «январь»;»февраль»)

Функция ПОДСТАВИТЬ() может заменить только первое, только второе и т.д. вхождение слова «январь».

Записав формулу =ПОДСТАВИТЬ(A2; «январь»;»февраль»;2) получим строку Продажи (январь), прибыль (февраль).

Если строка содержит слова в разных регистрах, например, Продажи (январь), прибыль (ЯНВАРЬ), то для замены слова январь на февраль можно порекомендовать формулу =ПОДСТАВИТЬ(СТРОЧН(A2); «январь»;»февраль») В результате получим продажи (февраль), прибыль (февраль).

Функция ПОДСТАВИТЬ() vs ЗАМЕНИТЬ()

Пусть в ячейке А2 введена строка Продажи (январь). Чтобы заменить слово январь, на февраль, запишем формулы:

=ЗАМЕНИТЬ(A2;10;6;»февраль») =ПОДСТАВИТЬ(A2; «январь»;»февраль») т.е. для функции ЗАМЕНИТЬ() потребовалось вычислить начальную позицию слова январь (10) и его длину (6). Это не удобно, функция ПОДСТАВИТЬ() справляется с задачей гораздо проще.

Кроме того, функция ЗАМЕНИТЬ() заменяет по понятным причинам только одно вхождение строки, функция ПОДСТАВИТЬ() может заменить все вхождения или только первое, только второе и т.д. Поясним на примере. Пусть в ячейке А2 введена строка Продажи (январь), прибыль (январь).

Запишем формулы: =ЗАМЕНИТЬ(A2;10;6;»февраль») =ПОДСТАВИТЬ(A2; «январь»;»февраль») получим в первом случае строку Продажи (февраль), прибыль (январь), во втором — Продажи (февраль), прибыль (февраль).

Записав формулу =ПОДСТАВИТЬ(A2; «январь»;»февраль»;2) получим строку Продажи (январь), прибыль (февраль).

Кроме того, функция ПОДСТАВИТЬ()  может работает с учетом регистра, а ЗАМЕНИТЬ() по понятным причинам не может.

Источник: https://excel2.ru/articles/funkciya-podstavit-v-ms-excel-podstavit