Подставить - функция в excel с примерами

Видео: Excel: как изменить цифры на буквы в заголовках столбцов?

Мы уже разбирали как быстро данные в Excel по группе ячеек — в столбце или массиве данных. Но как это сделать в одной ячейке и желательно формулой? К примеру у вас есть список машин, и каждый день вам присылают файл с новым списком машин, нужно проверить есть ли такие номера в вашем основном списке. Но как обычно в номера машин добавляют пробелы или слеши, а самый худший вариант букву О вместо нуля Если вы регулярно выполняете одну и ту же операцию, то вручную нажимать Ctrl + H каждый раз неудобно, плюс придется запоминать какие именно символы нужно заменять. Функции ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel это отличный выход из ситуации.

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

Разбирать будем на примере.

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

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

ПОДСТАВИТЬ(текст;стар_текст;нов_текст;)

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

=ПОДСТАВИТЬ(A:A;» «;»»;2 ) И в итоге вы получите А 777АА 01

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

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

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

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

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

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

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

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

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

Очень похоже на , честно говоря:)

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

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

ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel. Что еще?

Обе функции учитывают регистр! Т.е. если вы указали большую букву А, маленькая буква заменяться не будет!

Функция ПОДСТАВИТЬ доступна только в версиях 2007 и выше, будьте аккуратны если конечный пользователь будет использовать более ранние версии!

Поделитесь нашей статьей в ваших соцсетях:

Функция ЗАМЕНИТЬ(

), английский вариант REPLACE(), замещает указанную часть знаков текстовой строки другой строкой текста. «Указанную часть знаков» означает, что нужно указать начальную позицию и длину заменяемой части строки. Функция используется редко, но имеет плюс: позволяет легко вставить в указанную позицию строки новый текст.

Синтаксис

Аргументы функции ПОДСТАВИТЬ описаны ниже.

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

Стар_текст Обязательный. Заменяемый текст.

Нов_текст Обязательный. Текст, на который заменяется «стар_текст».

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

Дисперсия выборки

Дисперсия выборки ( выборочная дисперсия, sample variance ) характеризует разброс значений в массиве относительно среднего .

Все 3 формулы математически эквивалентны.

Из первой формулы видно, что дисперсия выборки это сумма квадратов отклонений каждого значения в массиве от среднего , деленная на размер выборки минус 1.

В MS EXCEL 2007 и более ранних версиях для вычисления дисперсии выборки используется функция ДИСП() , англ. название VAR, т.е. VARiance. С версии MS EXCEL 2010 рекомендуется использовать ее аналог ДИСП.В() , англ. название VARS, т.е. Sample VARiance. Кроме того, начиная с версии MS EXCEL 2010 присутствует функция ДИСП.Г(), англ. название VARP, т.е. Population VARiance, которая вычисляет дисперсию для генеральной совокупности . Все отличие сводится к знаменателю: вместо n-1 как у ДИСП.В() , у ДИСП.Г() в знаменателе просто n. До MS EXCEL 2010 для вычисления дисперсии генеральной совокупности использовалась функция ДИСПР() .

Дисперсию выборки можно также вычислить непосредственно по нижеуказанным формулам (см. файл примера ) =КВАДРОТКЛ(Выборка)/(СЧЁТ(Выборка)-1) =(СУММКВ(Выборка)-СЧЁТ(Выборка)*СРЗНАЧ(Выборка)^2)/ (СЧЁТ(Выборка)-1) – обычная формула =СУММ((Выборка -СРЗНАЧ(Выборка))^2)/ (СЧЁТ(Выборка)-1 ) – формула массива

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

Дисперсия выборки является точечной оценкой дисперсии распределения случайной величины, из которой была сделана выборка . О построении доверительных интервалов при оценке дисперсии можно прочитать в статье Доверительный интервал для оценки дисперсии в MS EXCEL .

Пример 3: Заменяем последний символ пробела

Чтобы не заменять все вхождения текстовой строки, Вы можете использовать аргумент instance_num (номер_вхождения) для указания, какое из вхождений нужно заменить. В следующем примере представлен список ингредиентов для рецепта, где требуется заменить только последний символ пробела.

Функция LEN (ДЛСТР) в ячейке C3 подсчитывает количество символов в ячейке B3. Функция SUBSTITUTE (ПОДСТАВИТЬ) заменяет все символы пробела пустой строкой, а вторая функция LEN (ДЛСТР) находит длину обработанной строки. Длина оказывается на 2 символа короче, значит в строке было 2 пробела.

В ячейке D3 функция SUBSTITUTE (ПОДСТАВИТЬ) заменяет второй символ пробела новой строкой » | «.

=SUBSTITUTE(B3,» «,» | «,C3) =ПОДСТАВИТЬ(B3;» «;» | «;C3)

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

=SUBSTITUTE(B3,» «,» | «,LEN(B3)-LEN(SUBSTITUTE(B3,» «,»»))) =ПОДСТАВИТЬ(B3;» «;» | «;ДЛСТР(B3)-ДЛСТР(ПОДСТАВИТЬ(B3;» «;»»)))

30 функций Excel за 30 дней: ПОДСТАВИТЬ (SUBSTITUTE)

Вчера в марафоне 30 функций Excel за 30 дней мы употребляли функцию OFFSET (СМЕЩ), чтоб вернуть ссылку, также узрели, что она весьма похожа на функцию INDEX (ИНДЕКС). Не считая этого мы узнали, что функция OFFSET (СМЕЩ) пересчитывается при любом изменении данных на листе, а INDEX (ИНДЕКС) лишь при изменении собственных аргументов.

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

Как указывает практика, в неких ситуациях резвее и проще применять команды Find/Replace (Отыскать/Поменять), когда нужно создать подмену чувствительную к регистру.

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

Функция ПСТР

ПСТР возвращает из указанной строки часть текста в заданном количестве символов, начиная с указанного символа.

Синтаксис: ПСТР(текст; начальная_позиция; количество_знаков)

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

Пример использования:

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

Аргумент количество_знаков может превышать допустимо возможную длину возвращаемых символов. Т.е. если в рассмотренном примере вместо количество_знаков = 12, было бы указано значение 15, то результат не изменился, и функция так же вернула строку «функции ПСТР».

Для удобства использования данной функции ее аргументы можно подменить функциями «НАЙТИ» и «ДЛСТР», как это было сделано в примере с функцией «ЗАМЕНИТЬ».

Как использовать функцию ПОДСТАВИТЬ в Microsoft Excel

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

Откройте Microsoft Excel
Выберите соответствующую ячейку
Добавить новую строку
Изменить код страны для номера телефона

1]Откройте Microsoft Excel

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

Как всегда, вы должны сначала открыть программу, щелкнув ярлык на рабочем столе, панели задач или в меню «Пуск». Как только это будет сделано, Microsoft Excel должен быть запущен и готов к работе.

2]Выберите соответствующую ячейку

Пришло время выбрать ячейку, в которой вы хотите, чтобы результаты отображались. Как вы можете видеть на изображении, у нас есть ячейка со словами TWC обсуждает и предлагает Windows 11, советы и рекомендации по Windows 10. Но в другой ячейке мы собираемся внести небольшое изменение, заменив старую строку на «Клуб Windows обсуждает и предлагает Windows 11», «Советы и рекомендации по Windows 10».

3]Добавить новую строку

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

=ПОДСТАВИТЬ(B2;»TWC»,»Клуб Windows»)

Как только новая строка будет добавлена в соответствующую ячейку, нажмите клавишу Enter, чтобы продемонстрировать изменения. Как видите, наша старая строка находится в ячейке B2, а новая строка находится в ячейке C2. Однако вы можете выбрать ту ячейку, которая работает лучше всего.

4]Изменить код страны для номера телефона

Если вам нужно изменить код страны для нескольких телефонных номеров, вы также можете использовать функцию ПОДСТАВИТЬ, чтобы сделать это. Задача очень похожа на то, что мы обсуждали выше, так что давайте посмотрим.

Итак, если ваш номер телефона 81486245014, но вы хотите изменить его на 1486245014, мы можем сделать это с легкостью.

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

=ПОДСТАВИТЬ(B2,»85″,»1″,1)

Нажмите клавишу Enter, и изменения сразу же должны появиться в вашем документе Excel.

Читайте: Конвертируйте JSON в Excel с помощью бесплатных онлайн-конвертеров

Что такое подставить и заменить в Excel?

ПОДСТАВИТЬ заменяет один или несколько экземпляров заданной текстовой строки или символа, в то время как ЗАМЕНА фокусируется на замене символов в заданной позиции текстовой строки.

Что делает замена текстовой функции?

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

Как заменить значения в Excel?

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

Сложно ли выучить Excel?

Если вы новичок, то мы должны сказать, что Microsoft Excel может быть сложным инструментом для освоения. Однако, когда дело доходит до изучения основ, это довольно просто и займет совсем немного времени. Гораздо проще, если вы получите помощь от школы или онлайн-курса.

Каковы пять самых мощных функций?

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

СЦЕПИТЬ
ВПР
СРЕДНИЙ
СУММА
ТЕКСТ

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

REGEXEXTRACT – извлекаем нужный фрагмент текста с помощью регулярных выражений

Сложность: для продвинутых пользователей.

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

Синтаксис:

=REGEXEXTRACT(текст; регулярное_выражение)

Применение

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

Поэтому проще выгрузить список URL в таблицу и извлечь из них домены с помощью REGEXEXTRACT. Для этого в формуле нужно указать такое регулярное выражение:

Протягиваем формулу для всего списка URL. Затем с помощью функции UNIQUE получаем уникальные имена доменов (без дублей). Итоговый список можем использовать для настройки таргетинга в Google Ads.

Дополнительная информация

совпадение искомых данных может быть точным и приблизительным;
при сопоставлении по приблизительной точности данных убедитесь, что данные в таблицах отсортированы в убывающем порядке (от большого к малому). Иначе, результат сопоставления будет некорректным;
при сопоставлении данных по приблизительной точности:– если функция не найдет искомое значение, она выдаст наибольшую величину, которая будет меньше чем значения поиска;– если функция при сопоставлении выдает ошибку #N/A, то искомое значение меньше чем самая маленькая величина в искомом диапазоне;– вы можете использовать подстановочные знаки для искомых значений.

Исправляем ошибки в тексте с помощью функции ПОДСТАВИТЬ

Одним из примеров применения функции ПОДСТАВИТЬ является наличие дефиса в русскоязычных словах или апострофа перед суффиксом ‘S в английских названиях фирм, которые искажают работу функции ПРОПНАЧ. Данная функция должна только первые буквы в словах изменять на большие, прописные. А в результате в исходном слове где иметься апостроф функция возвращает 2 большие буквы, что искажает результат ее работы:

Дело в том, что текстовая функция ПРОПНАЧ работает по принципу замены всех первых символов на большую букву, которые находятся после символов, которые не соответствуют буквам: . -,’,*,/, и т.д. Поэтому если внутри слова находится символы, которые не являются буквами работа функции ПРОПНАЧ будет искажена.

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

В основе выше указанной формулы лежит функция ПОДСТАВИТЬ, которая требует заполнения 3-х обязательных из 4-х аргументов:

Текст – исходный текст или ссылка на ячейку с исходным текстом в котором следует выполнить замену символов.
Стар_текст – старый заменяемый текст.
Нов_текст – новый текст на который следует заменить старый.
Номер_вхождения – опциональный необязательный аргумент. Если строка содержит несколько одинаковых старых заменяемых текстов, то с помощью этого аргумента можно указать какой именно по очереди заменить старый текст. Если этот аргумент опущен, тогда заменяются все найденные одинаковые старые заменяемые тексты в исходной строке.

Если внимательно присмотреться к формуле целиком, но легко заменить что функция ПОДСТАВИТЬ используется здесь 2 раза. Не только в основе, а и в качестве аргумента для функции ПРОПНАЧ. Поэтому чтобы детально проанализировать данную формулу и не запутаться разобьем ее на 3 части, так как в ней применяется 3 функции.

Сначала функция ПОДСТАВИТЬ используется для замены апострофа на временный текст «zzz». На первый взгляд такой подход выглядит не профессиональным и бессмысленным, но это не так. Чтобы получить правильный результат при работе функции ПОДСТАВИТЬ и при этом не удалять из текста символы отличные от букв, нам придется пойти на небольшую хитрость в Excel. Сохраняя в оригинале наличие всех символов в исходном тексте. Первая часть формулы для второй части вернет такой результат для следующей обработки:

Вторая часть формулы охватывает первую часть и работает с тем, что первая формула возвратила ей в результате, а именно ту же исходную строку, но с временным текстом «zzz» вместо апострофа «’». Далее в работу вступает пострадавшая функция ПРОПНАЧ, которая заменяет во всех словах первые буквы на большие. В результате получим текстовую строку следующего вида:

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

А после осталось лишь функции ПОДСТАВИТЬ обратно заменить временный текст «zzz» на апостроф «’». Чтобы слова приобрели свой оригинальный вид по наличию тех же символов.

Иногда приходиться находить собственные альтернативные решения в Excel если нет стандартных предусмотренных инструментов.

Заменить первый символ в ячейке excel

Други — срочно!кто знает как в множестве ячеек листа избавиться от ненужного символа переноса строки?чертов квадратик не дает вычислять и корректно конвертировать содержимое.

Добавление от 12.12.2004 01:23:

замена сработала с кодом 010 — НО! после этого функция замены вырубается

спасибо за ответы

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

вопрос — можно ли в ПУСТЫЕ ячейки забить нули автозаменой? похоже нет. а лопатить огромные листы на тысячи позиций и вручную заполнять пустые позиции — это тупо и долго.

Наверно мы с Вами говорим о разных вещах.

Настоящая автозамена, это когда мы вводим в ячейку, например z, а получаем 0 (Сервис-Автозамена)Естественно, что для этого нам необходимо предварительно указать, что z — это неправильный вариант, а 0 — правильный.

Вы, по всей видимости, имеете ввиду меню Правка — команда Заменить

Но это не важно, так как в принципе нам ничего не мешает выделить диапазон ячеек, содержащий пустые ячейки, а затем ввести 0 сразу во все эти ячейки. — Выделяете диапазон ячеек (можно выделить несмежные и не обязательно выделять строго пустые ячейки), затем в меню Правка-Перейти-Выделить-Пустые ячейки-ОК— Вводим 0, затем CTRL + ENTER *

— Выделяете диапазон ячеек (можно выделить несмежные и не обязательно выделять строго пустые ячейки), затем в меню Правка-Перейти-Выделить-Пустые ячейки-ОК— Вводим 0, затем CTRL + ENTER *

SS-18Чтобы определить код символа можно воспользоваться функцией КОДСИМВ.Допустим, символ переноса строки находится на пятой позиции в ячейке G30, тогда формула:=КОДСИМВ(ПСТР(G30;5;1))возвратит значение 10

Удалить этот символ из ячейки можно с помощью формулы:=ПОДСТАВИТЬ(G30;СИМВОЛ(10);»»)

В некоторых случаях может понадобиться заменить этот символ другим, например, пробелом:=ПОДСТАВИТЬ(G30;СИМВОЛ(10);СИМВОЛ(32))

Другой способ удалить символ с кодом 10 — использовать функцию ПЕЧСИМВ:=ПЕЧСИМВ(G30)

SS-18замена сработала с кодом 010 — НО! после этого функция замены вырубается

Когда вызываешь Замену во второй раз, в полях ввода ОСТАЮТСЯ предыдущие значения!То есть там стоИт сомвол 010, но его не видно, ты дописываешь что-то. и такая комбинация символов, действительно, не встречается.Метод: после вызова Замены поставить курсор в поле «Что» и несколько раз нажать BackSpace и Delete.

Забить пустые ячейки нулями? Нет проблем!Выдели ячейку где-то внутри таблицы, можно в первой строке, где заголовки.Правка-Перейти-Выделить-Текущую область (выделил всю таблицу).Ctrl-H — Что:пустая строка, Заменить на:0 — Заменить все.

Поторопился я обрадоваться — не помогает стирание спецсимвола и не работает функция замены после его удаления. Стирание поля в графе «заменить» — не приводит к ее активизации. — не работают оба способа замены массивы пустых ячеек на ноль — в способе Nosorogвыделяется ТОЛЬКО вся таблица ( включая заголовки и графы с названием) и нули вставляются ТОЛЬКО вокруг ячеек массива. в способе Vitaly K.вообще не активна функция ОПЕРАЦИИ в специальной вставке — ее просто НЕТ ТАМ.

Попробуй задействовать такой макрос:

код: Он, собственно, применяет функцию ПЕЧСИМВ к каждой ячейке таблицы, и пишет 0 в каждую пустую ячейку.

2pashulkaхммм.. уточняю — у меня дома стоит excel 2000-й. попытка использовать режим «правка-спецвставка» показывает окошко в котором и в помине нет группы ОПЕРАЦИЯ, а есть только выбор вида текста.. ну там Unicode или другой. и больше никаких .. -отсюда вопрос — как в Excel 2000 получить указанный режим функции спец вставка?Vic_KСпасибо за вопрос, но я не знаю как формируется файл из 1С.. знаю, что у жены на работе что-то сбито в ее настройках, после того как там покопался вызванный программист по 1С. а вызывать их на каждый сбой ох как дорого, да еще с такими результатами. все вышеописанные глюки появились именно после визита программиста. т.е. справеливее сказать, что он настраивал именно то, что просили добавить, но потом обнаруживались ужасные последствия в других местах.Политика у них такая что-ли, чтобы делать людей зависимыми от их «услуг» и на бабки разводить?

извините, ответил не квалифицировано, как смог.

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

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

=ЗАМЕНИТЬ(A2;10;6;”февраль”) =ПОДСТАВИТЬ(A2; “январь”;”февраль”)

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

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

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

Функция НАЙТИ

Возвращает число, являющееся вхождением первого символа подстроки, искомого текста. Если текст не найден, то возвращается ошибка «#ЗНАЧ!».

Синтаксис: =НАЙТИ(искомый_текст; текст_для_поиска; )

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

Пример использования:

Из отрывка стихотворения великого поэта С.А.Есенина находим вхождение первого символа строки «птица». Поиск осуществляется с начала строки. Если в приведенном примере поиск осуществлялся бы с 40 символа, то функция в результате вернула ошибку, т.к. позиции вхождения не было найдено.

Использование ЕСЛИ с функциями И, ИЛИ и НЕ

Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат.

=ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)

Но что делать, если необходимо проверить несколько условий, где, допустим, все условия должны иметь значение ИСТИНА или ЛОЖЬ (И), только одно условие должно иметь такое значение (ИЛИ) или вы хотите убедиться, что данные НЕ соответствуют условию? Эти три функции можно использовать самостоятельно, но они намного чаще встречаются в сочетании с функцией ЕСЛИ.

Используйте функцию ЕСЛИ вместе с функциями И, ИЛИ и НЕ, чтобы оценивать несколько условий.

ЕСЛИ(И()): ЕСЛИ(И(лог_выражение1; ; …), значение_если_истина; ))

ЕСЛИ(ИЛИ()): ЕСЛИ(ИЛИ(лог_выражение1; ; …), значение_если_истина; ))

ЕСЛИ(НЕ()): ЕСЛИ(НЕ(лог_выражение1), значение_если_истина; ))

Условие, которое нужно проверить.

Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.

Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.

Общие сведения об использовании этих функций по отдельности см. в следующих статьях: И, ИЛИ, НЕ. При сочетании с оператором ЕСЛИ они расшифровываются следующим образом:

И: =ЕСЛИ(И(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

ИЛИ: =ЕСЛИ(ИЛИ(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

НЕ: =ЕСЛИ(НЕ(условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

Ниже приведены примеры распространенных случаев использования вложенных операторов ЕСЛИ(И()), ЕСЛИ(ИЛИ()) и ЕСЛИ(НЕ()). Функции И и ИЛИ поддерживают до 255 отдельных условий, но рекомендуется использовать только несколько условий, так как формулы с большой степенью вложенности сложно создавать, тестировать и изменять. У функции НЕ может быть только одно условие.

Ниже приведены формулы с расшифровкой их логики.

=ЕСЛИ(И(A2>0;B2 0;B4 50);ИСТИНА;ЛОЖЬ)

Если A6 (25) НЕ больше 50, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае значение не больше чем 50, поэтому формула возвращает значение ИСТИНА.

Если значение A7 («синий») НЕ равно «красный», возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ.

Обратите внимание, что во всех примерах есть закрывающая скобка после условий. Аргументы ИСТИНА и ЛОЖЬ относятся ко внешнему оператору ЕСЛИ. Кроме того, вы можете использовать текстовые или числовые значения вместо значений ИСТИНА и ЛОЖЬ, которые возвращаются в примерах

Кроме того, вы можете использовать текстовые или числовые значения вместо значений ИСТИНА и ЛОЖЬ, которые возвращаются в примерах.

Вот несколько примеров использования операторов И, ИЛИ и НЕ для оценки дат.

Ниже приведены формулы с расшифровкой их логики.

Если A2 больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае 12.03.14 больше чем 01.01.14, поэтому формула возвращает значение ИСТИНА.

=ЕСЛИ(И(A3>B2;A3 B2;A4 B2);ИСТИНА;ЛОЖЬ)

Если A5 не больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ.

Работа со строками в 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))

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

Пример 2 В строке «Пример работы со строками в Excel» необходимо все пробелы заменить на знак «_», так же перед словом «Excel» добавить «MS».

Формула будет следующая:

=ПОДСТАВИТЬ(ЗАМЕНИТЬ(A1;ПОИСК(«excel»;A1);0;»MS «);» «;»_»)

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

Все. Если есть вопросы, задавайте, не стесняйтесь

Добавление функции ЗНАЧЕН

Теперь модернизируем формулу, чтобы в итоге получить числовое значение, а не текст, как в исходном варианте. Для этого понадобится функция ЗНАЧЕН, которую просто нужно вписать в строку, поставив дополнительные кавычки.

Выполните редактирование, чтобы в итоге получить примерно такой результат: =ЗНАЧЕН(ПОДСТАВИТЬ(A2;».»;»,»)).

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

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

Посмотрите на таблицу и удалите лишнюю информацию, которая после преобразования больше не понадобится.. Работа с массивами данных в Microsoft Excel часто заставляет юзера вносить правки в текст, что особенно актуально при импорте дробных чисел

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

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

Функция 27: SUBSTITUTE (ПОДСТАВИТЬ)

Функция SUBSTITUTE (ПОДСТАВИТЬ) заменяет старый текст на новый текст внутри текстовой строки. Функция заменит все повторения старого текста, пока не будет выполнено определённое условие. Она чувствительна к регистру.

Как можно использовать функцию SUBSTITUTE (ПОДСТАВИТЬ)?

Функция SUBSTITUTE (ПОДСТАВИТЬ) заменяет старый текст новым внутри текстовой строки. Вы можете использовать её для того, чтобы:

Изменять название региона в заголовке отчёта.
Удалить непечатаемые символы.
Заменить последний символ пробела.

Синтаксис SUBSTITUTE (ПОДСТАВИТЬ)

Функция SUBSTITUTE (ПОДСТАВИТЬ) имеет вот такой синтаксис:

SUBSTITUTE(text,old_text,new_text,instance_num) ПОДСТАВИТЬ(текст;стар_текст;нов_текст;номер_вхождения)

text (текст) – текстовая строка или ссылка, где будет осуществлена замена текста.
old_text (стар_текст) – текст, который надо заменить.
new_text (нов_текст) – текст, который будет вставлен.
instance_num (номер_вхождения) – номер вхождения текста, который нужно заменить (не обязательный аргумент).

Ловушки SUBSTITUTE (ПОДСТАВИТЬ)

Функция SUBSTITUTE (ПОДСТАВИТЬ) может заменить все повторения старого текста, поэтому, если Вам нужно заменить только определённое вхождение, используйте аргумент instance_num (номер_вхождения).
Если нужно сделать замену без учёта регистра, используйте функцию REPLACE (ЗАМЕНИТЬ).