Excel если содержит часть слова

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

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее “перевести”)

Источник: excelspb.ru

СОДЕРЖИТ_ОДНО_ИЗ

Данная функция является частью надстройки MulTEx

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

Вызов команды через стандартный диалог:

Мастер функций–Категория “MulTEx”– СОДЕРЖИТ_ОДНО_ИЗ

Вызов с панели MulTEx:

Сумма/Поиск/Функции – Текстовые – СОДЕРЖИТ_ОДНО_ИЗ

Синтаксис:
=СОДЕРЖИТ_ОДНО_ИЗ( A2 ; $B$2:$B$6 )
=СОДЕРЖИТ_ОДНО_ИЗ( A1 ; $E$2:$E$6 )
=СОДЕРЖИТ_ОДНО_ИЗ( A2 ; $E$2:$E$6 ;1;”, “)
=СОДЕРЖИТ_ОДНО_ИЗ( A2 ; $E$2:$E$6 ;0)

Текст( A2 ) – указывается ссылка на ячейку с текстом или текст, значения в котором необходимо сравнить со списком. Если указывается напрямую текст, то в таком случае он должен быть заключен в кавычки:
=СОДЕРЖИТ_ОДНО_ИЗ(“привет”)

Список( $E$2:$E$6 ) – список значений, присутствие которых необходимо проверить в Тексте. Указывается исключительно диапазон ячеек. Диапазон может состоять из нескольких строк и столбцов.

ЧтоВыводить(0) – необязательный аргумент. Указывается числовое значение 1 или 0. Если указано 1 или аргумент опущен – результатом функции будут все значения Списка, найденные в аргументе Текст, записанные через точку-с-запятой. Если указать 0, то результатом функции будет логическое значение ИСТИНА (TRUE) или ЛОЖЬ (FALSE) . ИСТИНА – если хоть одно из значений Списка содержится в Тексте. ЛОЖЬ – если нет ни одного совпадения. По умолчанию принимает значение 1.

Разделитель(“, “) – необязательный аргумент. Указывается символ или группа символов, которые будут использоваться для объединения значений, найденных в Списке. Указывается только если аргумент ЧтоВыводить равен 1 или опущен. Если разделитель не указан, то по умолчанию в качестве разделителя применяется “точка-с-запятой”. Чтобы вывести значения без разделителя следует указать пустую строку: “”.

Источник: www.excel-vba.ru

ЕСЛИ: функция Эксель для дополнительной проверки содержимого

Здравствуйте. Сегодня учимся проверять тип содержимого ячейки и комбинировать полученную информацию с функцией ЕСЛИ . Будем определять, содержится ли в ячейке текст, число или формула и т.п.

Если вы еще не знаете, как работает функция ЕСЛИ – прочтите в этой статье и возвращайтесь сюда!

Проверка в Excel, если содержит текст

Что делать, если нужно проверить, содержит ли ячейка строку текста. Используем функцию: =ЕТЕКСТ(ссылка) . Функция вернет ИСТИНА, когда ссылка указывает на ячейку с текстом, ЛОЖЬ – в противном случае. Вот несколько примеров работы:

Получили такие результаты:

То есть, функция считает текстом не только строку, а и число в текстовом формате ячейки, непечатаемый символ.

Обратный результат даст функция =ЕНЕТЕКСТ(ссылка) , которая проверяет, чтобы в ячейке было нетекстовое значение:

Таблица с итогами:

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

Вот так: =ЕСЛИ(ЕТЕКСТ(A1);»Это текст»;»») . Т.е. аргументы:

• Условие: ЕТЕКСТ(А1)
• Значение, если это текст: строка «Это текст»
• Значение, если это не текст: пустая строка (обозначается «»)

Функция Excel ЕЧИСЛО

Вы можете проверить, содержится ли в ячейке цифра. Используется функция ЕЧИСЛО , Excel возвращает такие результаты:

То есть, Excel считает числом цифру, дату, формулу, вернувшую число и ссылку на число.

В Экселе нет функции, проверяющей, что это не число. Чтобы сделать такую операцию, применяем логическую функцию НЕ . Вот так: =НЕ(ЕЧИСЛО(А1)) .

Проверка чётности и нечётности значения

Чтобы определить, чётно число или нет, используем функции:

• ЕЧЁТН – проверить чётность числа
• ЕНЕЧЁТ – определить нечётность

Результаты работы функций:

Если вы попытаетесь проверить нечисловые значения, функции вернут ошибку #ЗНАЧ!

Для условия, если ячейка четная, функция ЕСЛИ может быть построена так: =ЕСЛИ(ЕЧЁТН(A2);»Это чётное значение»;»») . Получим такую таблицу результатов:

Обратите внимание на особенности:

1. Функция восприняла дату, как обычное число и выдала правильный результат
2. Проверка текста и непечатаемого символа дали ошибку #ЗНАЧ!
3. Число в текстовом формате (ячейка А6) было приведено к числовому и проверено, получен правильный результат
4. Результат, когда в ячейке формула или ссылка – зависит от результата вычисления или значения в ячейке по ссылке

Проверка в Excel, если ячейка пустая

Для проверки пустоты, есть функция =ЕПУСТО() . Она дает такие результаты:

Видим, что получаем ИСТИНУ лишь тогда, когда в ячейке пустота.

Теперь напишем формулу в Эксель, если пустая ячейка, то выведем какое-то значение: =ЕСЛИ(ЕПУСТО(A2);»Пустая ячейка»;»») .

Похожая проверка в Excel, если не пусто, то формула будет такая: =ЕСЛИ(НЕ(ЕПУСТО(A2));»Ячейка заполнена»;»») .

Проверка на логическое значение

Функция ЕЛОГИЧ проверит, находится ли в ячейке ИСТИНА или ЛОЖЬ. Если это так, вернет ИСТИНУ. В противном случае – ЛОЖЬ

Это все вспомогательные функции, о которых я хотел рассказать. Применяйте их в комбинации с функцией ЕСЛИ и логическими функциями, чтобы получить максимальный эффект возможностей программы. Сейчас готов ответить на ваши вопросы, пишите комментарии!

Источник: officelegko.com

Текстовые функции Excel

ФИО, номера банковских карт, адреса клиентов или сотрудников, комментарии и многое другое –все это является строками, с которыми многие сталкиваются, работая с приложением Excel. Поэтому полезно уметь обрабатывать информацию подобного типа. В данной статье будут рассмотрены текстовые функции в Excel, но не все, а те, которые, по мнению office-menu.ru, самые полезные и интересные:

Список всех текстовых функций Вы можете найти на вкладке «Формулы» => выпадающий список «Текстовые»:

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

Возвращает подстроку из текста в порядке слева направо в заданном количестве символов.

Синтаксис: =ЛЕВСИМВ(текст; [количество_знаков])

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

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

Формула: =ЛЕВСИМВ(“Произвольный текст”;8) – возвращенное значение «Произвол».

Функция ПРАВСИМВ

Данная функция аналогична функции «ЛЕВСИМВ», за исключением того, что знаки возвращаются с конца строки.

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

Формула: =ПРАВСИМВ(“произвольный текст”;5) – возвращенное значение «текст».

Функция ДЛСТР

С ее помощью определяется длина строки. В качестве результата возвращается целое число, указывающее количество символов текста.

Синтаксис: =ДЛСТР(текст)

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

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

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

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

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

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

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

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

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

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

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

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

Здесь в строке, содержащейся в ячейке A1, подменяется слово «старый», которое начинается с 19-го символа и имеет длину 6 символов, на слово «новый».

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

• Аргумент «начальная_позиция» подменим функцией «НАЙТИ»;
• В место аргумент «количество_знаков» вложим функцию «ДЛСТР».

В результате получим формулу: =ЗАМЕНИТЬ(A1;НАЙТИ(“старый”;A1);ДЛСТР(“старый”);”новый”)

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

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

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

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

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

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

Строка в ячейке A1 содержит текст, в котором имеются 2 подстроки «старый». Нам необходимо подставить на место первого вхождения строку «новый». В результате часть текста «…старый-старый…», заменяется на «…новый-старый…».

Если ли бы последний аргумент был опущен, то результатом бы стала строка «строка, содержащая новый-новый текст».

Функция ПСТР

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

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

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

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

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

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

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

Функция СЖПРОБЕЛЫ

Данная функция удаляется все лишние пробелы: пробелы по краям и двойные пробелы между словами. После обработки строк функцией остаются только одиночные пробелы между словами.

Синтаксис: =СЖПРОБЕЛЫ(текст)

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

=СЖПРОБЕЛЫ( ” Текст с лишними пробелами между словами и по краям “ )

Результатом выполнения функции будет строка: “Текст с лишними пробелами между словами и по краям” .

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

С помощью функции «СЦЕПИТЬ» можно объединить несколько строк между собой. Максимальное количество строк для объединения – 255.

Синтаксис: =СЦЕПИТЬ(текст1; [текст2]; …)

Функция должна содержать не менее одного аргумента

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

Функция возвратит строку: «Слово1 Слово2».

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

Вместо использования данной функции можно применять знак амперсанда «&». Он так же объединяет строки. Например: «=”Слово1″&” “&“Слово2″».

Источник: office-menu.ru

Сложение с одним критерием в EXCEL (Часть2.ТЕКСТовый критерий)

Для суммирования значений по одному диапазону на основе данных другого диапазона используется функция СУММЕСЛИ() . Рассмотрим случай, когда критерий применяется к диапазону содержащему текстовые значения.

Пусть дана таблица с перечнем наименований фруктов и их количеством (см. файл примера ).

Если в качестве диапазона, к которому применяется критерий, выступает диапазон с текстовыми значениями, то можно рассмотреть несколько типов задач суммирования:

• суммирование значений, если соответствующие им ячейки в диапазоне поиска соответствуют критерию (простейший случай);
• в критерии применяются подстановочные знаки (*, ?) ;
• критерий сравнивается со значениями в диапазоне поиска с учетом РЕгиСтРА .

Рассмотрим эти задачи подробнее.

Значение соответствует критерию

Найдем количество всех значений “Яблоки” , т.е. просуммируем значения из столбца Количество, для которых соответствующее значение из столбца Фрукты в точности равно “Яблоки” (без учета РЕГИСТРА) .

Для подсчета используем формулу =СУММЕСЛИ(A3:A13;”яблоки”;B3:B13)

Критерий яблоки можно поместить в ячейку D 5 , тогда формулу можно переписать следующим образом: =СУММЕСЛИ(A3:A13;D5;B3 :B13 )

В качестве диапазона суммирования можно указать лишь первую ячейку диапазона – функция СУММЕСЛИ() просуммирует все правильно: =СУММЕСЛИ(A3:A13; D5 ;B3)

В критерии применяются подстановочные знаки (*, ?)

Просуммируем значения из столбца Количество, для которых соответствующее значение из столбца Фрукты содержит слово Яблоки (без учета РЕгиСТРА) .

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

Решение задачи выглядит следующим образом (учитываются значения содержащие слово яблоки в любом месте в диапазоне поиска): =СУММЕСЛИ($A$3:$A$13;”*яблоки*”;B3)

Альтернативный вариант без использования подстановочных знаков выглядит более сложно: =СУММПРОИЗВ(B3:B13*НЕ(ЕОШ(ПОИСК(“яблоки”;A3:A13))))

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

Задача . Просуммировать значения, если соответствующие ячейки:

Задача

Критерий

Формула

Результат

Примечание

заканчиваются на слово яблоки , например, Свежие яблоки

11

Использован подстановочный знак * (перед значением)

начинаются на слово яблоки , например, яблоки местные

20

Использован подстановочный знак * (после значения)

начинаются с гру и содержат ровно 6 букв

= СУММЕСЛИ($A$3:$A$13; “гру. “;B3)

56

Использован подстановочный знак ?

Критерий сравнивается со значениями в диапазоне поиска с учетом РЕгиСТРА

Учет РЕгиСТра приводит к необходимости создания более сложных формул. Чаще всего используются формулы на основе функций НАЙТИ() и СОВПАД() учитывающих регистр.

Ниже приведены формулы для суммирования чисел, если соответствующие значения совпадают с критерием с учетом регистра.

Просуммировать значения, если соответствующие ячейки:

Критерий

Формула

Результат

Примечание

в точности равны Яблоки с учетом регистра

содержат значение Яблоки в любом месте текстовой строки с учетом регистра

= СУММ(ЕСЛИ( СОВПАД(“Яблоки”;A3:A13);1;0) *B3:B13)

В статье Сложение по условию (один Числовой критерий) рассмотрен случай, когда критерий применяется к числовым значениям из диапазона, по которому производится суммирование.

Источник: excel2.ru