Excel если текст содержит формула
Excel если текст содержит формула
Функция ЕСЛИ СОДЕРЖИТ
Наверное, многие задавались вопросом, как найти функцию в EXCEL«СОДЕРЖИТ» , чтобы применить какое-либо условие, в зависимости от того, есть ли в текстовой строке кусок слова , или отрицание, или часть наименования контрагента, особенно при нестандартном заполнении реестров вручную.
Такой функционал возможно получить с помощью сочетания двух обычных стандартных функций – ЕСЛИ и СЧЁТЕСЛИ .
Рассмотрим пример автоматизации учета операционных показателей на основании реестров учета продаж и возвратов (выгрузки из сторонних программ автоматизации и т.п.)
У нас есть множество строк с документами Реализации и Возвратов .
Все документы имеют свое наименование за счет уникального номера .
Нам необходимо сделать признак « Только реализация » напротив документов продажи, для того, чтобы в дальнейшем включить этот признак в сводную таблицу и исключить возвраты для оценки эффективности деятельности отдела продаж.
Выражение должно быть универсальным , для того, чтобы обрабатывать новые добавляемые данные .
Для того, чтобы это сделать, необходимо:
-
Начинаем с ввода функции ЕСЛИ (вводим «=» , набираем наименование ЕСЛИ , выбираем его из выпадающего списка, нажимаем fx в строке формул).
В открывшемся окне аргументов, в поле Лог_выражение вводим СЧЁТЕСЛИ() , выделяем его и нажимаем 2 раза fx.
Далее в открывшемся окне аргументов функции СЧЁТЕСЛИ в поле «Критерий» вводим кусок искомого наименования *реализ* , добавляя в начале и в конце символ * .
Такая запись даст возможность не думать о том, с какой стороны написано слово реализация (до или после номера документа), а также даст возможность включить в расчет сокращенные слова «реализ.» и «реализац.»
Теперь мы можем работать и сводить данные только по документам реализации исключая возвраты . При дополнении таблицы новыми данными, остается только протягивать строку с нашим выражением и обновлять сводную таблицу.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее “перевести”)
Источник: excelspb.ru
Функция ЕСЛИ
В этом курсе:
Функция ЕСЛИ — одна из самых популярных функций в Excel. Она позволяет выполнять логические сравнения значений и ожидаемых результатов.
Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно.
Например, функция =ЕСЛИ(C2=”Да”;1;2) означает следующее: ЕСЛИ(С2=”Да”, то вернуть 1, в противном случае вернуть 2).
Функция ЕСЛИ, одна из логических функций, служит для возвращения разных значений в зависимости от того, соблюдается ли условие.
ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])
Условие, которое нужно проверить.
Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.
Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.
Простые примеры функции ЕСЛИ
В примере выше ячейка D2 содержит формулу: ЕСЛИ(C2 = Да, то вернуть 1, в противном случае вернуть 2)
В этом примере ячейка D2 содержит формулу: ЕСЛИ(C2 = 1, то вернуть текст “Да”, в противном случае вернуть текст “Нет”). Как видите, функцию ЕСЛИ можно использовать для сравнения и текста, и значений. А еще с ее помощью можно оценивать ошибки. Вы можете не только проверять, равно ли одно значение другому, возвращая один результат, но и использовать математические операторы и выполнять дополнительные вычисления в зависимости от условий. Для выполнения нескольких сравнений можно использовать несколько вложенных функций ЕСЛИ.
B2;”Превышение бюджета”,”В пределах бюджета”)” xmlns_AntiXSS=”urn:AntiXSSExtensions” />
=ЕСЛИ(C2>B2;”Превышение бюджета”;”В пределах бюджета”)
В примере выше функция ЕСЛИ в ячейке D2 означает: ЕСЛИ(C2 больше B2, то вернуть текст “Превышение бюджета”, в противном случае вернуть текст “В пределах бюджета”)
B2;C2-B2;””)” xmlns_AntiXSS=”urn:AntiXSSExtensions” />
На рисунке выше мы возвращаем не текст, а результат математического вычисления. Формула в ячейке E2 означает: ЕСЛИ(значение “Фактические” больше значения “Плановые”, то вычесть сумму “Плановые” из суммы “Фактические”, в противном случае ничего не возвращать).
В этом примере формула в ячейке F7 означает: ЕСЛИ(E7 = “Да”, то вычислить общую сумму в ячейке F5 и умножить на 8,25 %, в противном случае налога с продажи нет, поэтому вернуть 0)
Примечание: Если вы используете текст в формулах, заключайте его в кавычки (пример: “Текст”). Единственное исключение — слова ИСТИНА и ЛОЖЬ, которые Excel распознает автоматически.
Распространенные неполадки
0 (ноль) в ячейке
Не указан аргумент значение_если_истина или значение_если_ложь. Чтобы возвращать правильное значение, добавьте текст двух аргументов или значение ИСТИНА/ЛОЖЬ.
Как правило, это указывает на ошибку в формуле.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
Источник: support.office.com
СОДЕРЖИТ_ОДНО_ИЗ
Данная функция является частью надстройки 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 применяется для записи определенных условий. Сопоставляются числа и/или текст, функции, формулы и т.д. Когда значения отвечают заданным параметрам, то появляется одна запись. Не отвечают – другая.
Логические функции – это очень простой и эффективный инструмент, который часто применяется в практике. Рассмотрим подробно на примерах.
Синтаксис функции ЕСЛИ с одним условием
Синтаксис оператора в Excel – строение функции, необходимые для ее работы данные.
Разберем синтаксис функции:
Логическое_выражение – ЧТО оператор проверяет (текстовые либо числовые данные ячейки).
Значение_если_истина – ЧТО появится в ячейке, когда текст или число отвечают заданному условию (правдивы).
Значение,если_ложь – ЧТО появится в графе, когда текст или число НЕ отвечают заданному условию (лживы).
Оператор проверяет ячейку А1 и сравнивает ее с 20. Это «логическое_выражение». Когда содержимое графы больше 20, появляется истинная надпись «больше 20». Нет – «меньше или равно 20».
Внимание! Слова в формуле необходимо брать в кавычки. Чтобы Excel понял, что нужно выводить текстовые значения.
Еще один пример. Чтобы получить допуск к экзамену, студенты группы должны успешно сдать зачет. Результаты занесем в таблицу с графами: список студентов, зачет, экзамен.
Обратите внимание: оператор ЕСЛИ должен проверить не цифровой тип данных, а текстовый. Поэтому мы прописали в формуле В2= «зач.». В кавычки берем, чтобы программа правильно распознала текст.
Функция ЕСЛИ в Excel с несколькими условиями
Часто на практике одного условия для логической функции мало. Когда нужно учесть несколько вариантов принятия решений, выкладываем операторы ЕСЛИ друг в друга. Таким образом, у нас получиться несколько функций ЕСЛИ в Excel.
Синтаксис будет выглядеть следующим образом:
Здесь оператор проверяет два параметра. Если первое условие истинно, то формула возвращает первый аргумент – истину. Ложно – оператор проверяет второе условие.
Примеры несколько условий функции ЕСЛИ в Excel:
Таблица для анализа успеваемости. Ученик получил 5 баллов – «отлично». 4 – «хорошо». 3 – «удовлетворительно». Оператор ЕСЛИ проверяет 2 условия: равенство значения в ячейке 5 и 4.
В этом примере мы добавили третье условие, подразумевающее наличие в табеле успеваемости еще и «двоек». Принцип «срабатывания» оператора ЕСЛИ тот же.
Расширение функционала с помощью операторов «И» и «ИЛИ»
Когда нужно проверить несколько истинных условий, используется функция И. Суть такова: ЕСЛИ а = 1 И а = 2 ТОГДА значение в ИНАЧЕ значение с.
Функция ИЛИ проверяет условие 1 или условие 2. Как только хотя бы одно условие истинно, то результат будет истинным. Суть такова: ЕСЛИ а = 1 ИЛИ а = 2 ТОГДА значение в ИНАЧЕ значение с.
Функции И и ИЛИ могут проверить до 30 условий.
Пример использования оператора И:
Пример использования функции ИЛИ:
Как сравнить данные в двух таблицах
Пользователям часто приходится сравнить две таблицы в Excel на совпадения. Примеры из «жизни»: сопоставить цены на товар в разные привозы, сравнить балансы (бухгалтерские отчеты) за несколько месяцев, успеваемость учеников (студентов) разных классов, в разные четверти и т.д.
Чтобы сравнить 2 таблицы в Excel, можно воспользоваться оператором СЧЕТЕСЛИ. Рассмотрим порядок применения функции.
Для примера возьмем две таблицы с техническими характеристиками разных кухонных комбайнов. Мы задумали выделение отличий цветом. Эту задачу в Excel решает условное форматирование.
Исходные данные (таблицы, с которыми будем работать):
Выделяем первую таблицу. Условное форматирование – создать правило – использовать формулу для определения форматируемых ячеек:
В строку формул записываем: =СЧЕТЕСЛИ (сравниваемый диапазон; первая ячейка первой таблицы)=0. Сравниваемый диапазон – это вторая таблица.
Чтобы вбить в формулу диапазон, просто выделяем его первую ячейку и последнюю. «= 0» означает команду поиска точных (а не приблизительных) значений.
Выбираем формат и устанавливаем, как изменятся ячейки при соблюдении формулы. Лучше сделать заливку цветом.
Выделяем вторую таблицу. Условное форматирование – создать правило – использовать формулу. Применяем тот же оператор (СЧЕТЕСЛИ).
Здесь вместо первой и последней ячейки диапазона мы вставили имя столбца, которое присвоили ему заранее. Можно заполнять формулу любым из способов. Но с именем проще.
Источник: exceltable.com
ЕСЛИ: функция Эксель для дополнительной проверки содержимого
Здравствуйте. Сегодня учимся проверять тип содержимого ячейки и комбинировать полученную информацию с функцией ЕСЛИ . Будем определять, содержится ли в ячейке текст, число или формула и т.п.
Если вы еще не знаете, как работает функция ЕСЛИ – прочтите в этой статье и возвращайтесь сюда!
Проверка в Excel, если содержит текст
Что делать, если нужно проверить, содержит ли ячейка строку текста. Используем функцию: =ЕТЕКСТ(ссылка) . Функция вернет ИСТИНА, когда ссылка указывает на ячейку с текстом, ЛОЖЬ – в противном случае. Вот несколько примеров работы:
Получили такие результаты:
Тип проверяемого значения | Результат |
---|---|
Число | ЛОЖЬ |
Дата | ЛОЖЬ |
Текст | ИСТИНА |
Число в текстовом формате | ИСТИНА |
Пустая ячейка | ЛОЖЬ |
ИСТИНА | |
Формула | Зависит от типа вычисленного значения |
Ссылка | Зависит от типа значения в ячейке, на которую ссылаемся |
То есть, функция считает текстом не только строку, а и число в текстовом формате ячейки, непечатаемый символ.
Обратный результат даст функция =ЕНЕТЕКСТ(ссылка) , которая проверяет, чтобы в ячейке было нетекстовое значение:
Таблица с итогами:
Тип проверяемого значения | Результат |
---|---|
Число | ИСТИНА |
Дата | ИСТИНА |
Текст | ЛОЖЬ |
Число в текстовом формате | ЛОЖЬ |
Пустая ячейка | ИСТИНА |
Непечатаемый символ | ЛОЖЬ |
Формула | Зависит от типа вычисленного значения |
Ссылка | Зависит от типа значения в ячейке, на которую ссылаемся |
Как будет выглядеть формула в Excel: если ячейка содержит текст, то значение, в противном случае – пустая строка.
Вот так: =ЕСЛИ(ЕТЕКСТ(A1);»Это текст»;»») . Т.е. аргументы:
- Условие: ЕТЕКСТ(А1)
- Значение, если это текст: строка «Это текст»
- Значение, если это не текст: пустая строка (обозначается «»)
Функция Excel ЕЧИСЛО
Вы можете проверить, содержится ли в ячейке цифра. Используется функция ЕЧИСЛО , Excel возвращает такие результаты:
То есть, Excel считает числом цифру, дату, формулу, вернувшую число и ссылку на число.
В Экселе нет функции, проверяющей, что это не число. Чтобы сделать такую операцию, применяем логическую функцию НЕ . Вот так: =НЕ(ЕЧИСЛО(А1)) .
Проверка чётности и нечётности значения
Чтобы определить, чётно число или нет, используем функции:
- ЕЧЁТН – проверить чётность числа
- ЕНЕЧЁТ – определить нечётность
Результаты работы функций:
Проверяемое значение | Функция | Результат |
---|---|---|
1 | ЕЧЁТН(ссылка) | ЛОЖЬ |
2 | ЕЧЁТН(ссылка) | ИСТИНА |
1 | ЕНЕЧЁТ(ссылка) | ИСТИНА |
2 | ЕНЕЧЁТ(ссылка) | ЛОЖЬ |
Если вы попытаетесь проверить нечисловые значения, функции вернут ошибку #ЗНАЧ!
Для условия, если ячейка четная, функция ЕСЛИ может быть построена так: =ЕСЛИ(ЕЧЁТН(A2);»Это чётное значение»;»») . Получим такую таблицу результатов:
Обратите внимание на особенности:
- Функция восприняла дату, как обычное число и выдала правильный результат
- Проверка текста и непечатаемого символа дали ошибку #ЗНАЧ!
- Число в текстовом формате (ячейка А6) было приведено к числовому и проверено, получен правильный результат
- Результат, когда в ячейке формула или ссылка – зависит от результата вычисления или значения в ячейке по ссылке
Проверка в Excel, если ячейка пустая
Для проверки пустоты, есть функция =ЕПУСТО() . Она дает такие результаты:
Видим, что получаем ИСТИНУ лишь тогда, когда в ячейке пустота.
Теперь напишем формулу в Эксель, если пустая ячейка, то выведем какое-то значение: =ЕСЛИ(ЕПУСТО(A2);»Пустая ячейка»;»») .
Похожая проверка в Excel, если не пусто, то формула будет такая: =ЕСЛИ(НЕ(ЕПУСТО(A2));»Ячейка заполнена»;»») .
Проверка на логическое значение
Функция ЕЛОГИЧ проверит, находится ли в ячейке ИСТИНА или ЛОЖЬ. Если это так, вернет ИСТИНУ. В противном случае – ЛОЖЬ
Это все вспомогательные функции, о которых я хотел рассказать. Применяйте их в комбинации с функцией ЕСЛИ и логическими функциями, чтобы получить максимальный эффект возможностей программы. Сейчас готов ответить на ваши вопросы, пишите комментарии!
Источник: officelegko.com
Функция ЕСЛИ() в EXCEL
Функция ЕСЛИ() относится к наиболее часто используемым функциям.
Синтаксис функции
ЕСЛИ(лог_выражение;значение_если_истина;[значение_если_ложь])
Лог_выражение — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. =ЕСЛИ(A1>=100;”Бюджет превышен”;”ОК!”) Т.е. если в ячейке A1 содержится значение большее или равное 100, то формула вернет ОК!, а если нет, то Бюджет превышен .
В качестве аргументов функции, могут участвовать формулы, например: =ЕСЛИ(A1>100;СУММ(B1:B10);СУММ(C1:C10)) Т.е. если в ячейке A1 содержится значение >100, то суммирование ведется по столбцу B , а если меньше, то по столбцу С .
Вложенные ЕСЛИ
В EXCEL 2007 в качестве значений аргументов значение_если_истина и значение_если_ложь можно для построения более сложных проверок использовать до 64 вложенных друг в друга функций ЕСЛИ() . =ЕСЛИ(A1>=100;”Бюджет превышен”;ЕСЛИ(A1>=90;”Крупный проект”;ЕСЛИ(A1>=50;”Средний проект”;”Малый проект “)))
Вышеуказанную формулу можно усложнять и дальше, но есть другие подходы:
Для функции ВПР() необходимо создать в диапазоне A3:B6 таблицу значений:
Если требуется вывести разный текст в случае наличия в ячейке А1 отрицательного значения, положительного значения или 0, то можно записать следующую формулу:
или, если требуется вместо текстовых значений вывести формулы, можно использовать ссылки на диапазон ячеек (содержащих формулы)
=ПРОСМОТР(A24;<-1E+307;0;1E-307>;A27:A29) (см. файл примера )
Опущен третий аргумент [значение_если_ложь]
Третий аргумент функции не обязателен, если его опустить, то функция вернет значение ЛОЖЬ (если условие не выполнено). =ЕСЛИ(A1>100;”Бюджет превышен”) Если в ячейке A1 содержится значение 1, то вышеуказанная формула вернет значение ЛОЖЬ.
Вместо ИСТИНА или ЛОЖЬ в первом аргументе введено число
Т.к. значение ЛОЖЬ эквивалентно 0, то формулы =ЕСЛИ(0;”Бюджет превышен”;”ОК!”) или (если в ячейке A1 содержится значение 0) =ЕСЛИ(A1;”Бюджет превышен”;”ОК!”) вернут ОК!
Если в ячейке A1 находится любое другое число кроме 0, то формула вернет Бюджет превышен . Такой подход удобен, когда проверяется равенство значения нулю.
Связь функции ЕСЛИ() с другими функциями использующие условия
EXCEL содержит также другие функции, которые можно применять для анализа данных с использованием условий. Например, для подсчета количества вхождений чисел в диапазоне ячеек используется функция СЧЁТЕСЛИ() , а для сложения значений, удовлетворяющих определенным условиям, используется функция СУММЕСЛИ() .
Функция ЕСЛИ() , как альтернативный вариант, может быть также использована для подсчета и сложения значений с использованием условий. Ниже приведены иллюстрирующие примеры.
Пусть данные находятся в диапазоне A6:A11 (см. файл примера)
Подсчитаем сумму значений, которые больше 10 с помощью функции СУММЕСЛИ() , записав =СУММЕСЛИ(A6:A11;”>10″) . Аналогичный результат (23) можно получить с помощью формулы массива =СУММ(ЕСЛИ(A6:A11>10;A6:A11)) (для ввода формулы в ячейку вместо ENTER нужно нажать CTRL+SHIFT+ENTER )
Теперь подсчитаем количество вхождений чисел больше 10 в диапазоне ячеек A6:A11 =СЧЁТЕСЛИ(A6:A11;”>10″) . Аналогичный результат (2) можно получить с помощью формулы массива =СЧЁТ(ЕСЛИ(A6:A11>10;A6:A11))
Теперь, когда принцип понятен, с помощью функции ЕСЛИ() можно конструировать и другие формулы с условиями. Например, нахождение минимального значения среди чисел больше 10: =МИН(ЕСЛИ(A6:A11>10;A6:A11))
Источник: excel2.ru