Функция если ячейка содержит определенный текст

Функция если ячейка содержит определенный текст

Функция ЕСЛИ СОДЕРЖИТ

Наверное, многие задавались вопросом, как найти функцию в EXCEL«СОДЕРЖИТ» , чтобы применить какое-либо условие, в зависимости от того, есть ли в текстовой строке кусок слова , или отрицание, или часть наименования контрагента, особенно при нестандартном заполнении реестров вручную.

Такой функционал возможно получить с помощью сочетания двух обычных стандартных функций – ЕСЛИ и СЧЁТЕСЛИ .

Рассмотрим пример автоматизации учета операционных показателей на основании реестров учета продаж и возвратов (выгрузки из сторонних программ автоматизации и т.п.)

У нас есть множество строк с документами Реализации и Возвратов .

Все документы имеют свое наименование за счет уникального номера .

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

Выражение должно быть универсальным , для того, чтобы обрабатывать новые добавляемые данные .

Для того, чтобы это сделать, необходимо:

    Начинаем с ввода функции ЕСЛИ (вводим «=» , набираем наименование ЕСЛИ , выбираем его из выпадающего списка, нажимаем fx в строке формул).

В открывшемся окне аргументов, в поле Лог_выражение вводим СЧЁТЕСЛИ() , выделяем его и нажимаем 2 раза fx.



Далее в открывшемся окне аргументов функции СЧЁТЕСЛИ в поле «Критерий» вводим кусок искомого наименования *реализ* , добавляя в начале и в конце символ * .

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

  • Аргумент «Диапазон» — это соответствующая ячейка с наименованием документа.
  • Далее нажимаем ОК , выделяем в строке формул ЕСЛИ и нажимаем fx и продолжаем заполнение функции ЕСЛИ.
  • В Значение_если_истина вводим « Реализация », а в Значение_если_ложь – можно ввести прочерк « — »
  • Далее протягиваем формулу до конца таблицы и подключаем сводную.
  • Теперь мы можем работать и сводить данные только по документам реализации исключая возвраты . При дополнении таблицы новыми данными, остается только протягивать строку с нашим выражением и обновлять сводную таблицу.

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

    Сумма, если ячейки содержат звездочку

    Чтобы посчитать сумму, если ячейки содержат звездочку, можно использовать функцию СУММЕСЛИ со специальным символом «тильда» — «

    В примере показано, ячейка G6 содержит эту формулу:

    Эта формула суммирует суммы в столбце D, когда значение в столбце C содержит «*».

    Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».

    Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символов» и так далее.

    Читайте также:  Philips xenium e106 dual sim black

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

    ). Тильда причины Excel следует рассматривать следующий символ буквально.

    В этом случае мы используем «

    *», чтобы соответствовать символу звездочки, но он окружен звездочками по бокам, для того чтобы соответствовать звездочкам в любом месте ячейки. Если вы просто хотите, чтобы соответствовало звездочкам на конце ячейки, используйте: «*

    Альтернатива с СУММЕСЛИМН

    Вы также можете использовать функцию СУММЕСЛИМН. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. С СУММЕСЛИМН, диапазон сумма всегда стоит на первом месте в списке аргументов, затем пара/диапазон критериев:

    Сумма, если ячейки содержат X и Y

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

    В показанном примере, формула в F5:

    Функция СУММЕСЛИМН основана на логике и такое поведение является автоматическим. Нам просто нужно поставить два диапазона/критерии пары, работающие на одном диапазоне (В5:В9).

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

    Когда оба критерия рассчитывает Истина в том же ряду, СУММЕСЛИМН суммирует значения в столбец C.

    Обратите внимание, что СУММЕСЛИМН не чувствителен к регистру.

    Сумма, если ячейки содержат либо x, либо Y

    Чтобы посчитать сумму, если ячейки содержат либо одну текстовую строку или другую (т. е. содержащие «кошка» или «крыса») вы можете использовать функция СУММПРОИЗВ.

    Когда вы суммируете ячейки с критерием «или», вы должны быть осторожны, чтобы не произошел двойной счет, когда существует вероятность того, что оба критерия будет рассчитывать Истина. В показанном примере, мы хотим просуммировать значения в столбце C, когда ячейки в столбце B содержат слово «кошка» или «крыса». Мы не можем использовать СУММЕСЛИМН по двум критериям, т. к. СУММЕСЛИМН основан на логике. И если мы попытаемся использовать две функции СУММЕСЛИМН (т.е. СУММЕСЛИМН + СУММЕСЛИМН) у нас будет двойной счет, потому что есть клетки, которые содержат как «кошка», так и «крыса»

    Одно из решений-использовать функцию СУММПРОИЗВ с ЕЧИСЛО + ПОИСК или НАЙТИ.

    Эта формула основана на формуле, что находит текст внутри ячейки:

    При заданном диапазоне ячеек, этот фрагмент будет рассчитывать массив значений Истина или Ложь, одно значение для каждой ячейки диапазона. Поскольку мы делаем это дважды (один раз для «кошки» и один раз для «крысы»), мы получим два массива.

    Далее, мы добавляем эти массивы вместе (с +), которая создает новый единый массив чисел. Каждое число в этом массиве является результатом добавления истинных и ложных значений в исходных двух массивах вместе. В показанном примере, массив выглядит так:

    Читайте также:  Ошибка сервиса плей маркет

    Нам нужно добавить эти цифры, но мы не хотим, чтобы удвоился счет. Поэтому мы должны убедиться, что любое значение больше нуля, всего лишь раз пересчитать. Чтобы сделать это, мы превращаем все значения Истина или Ложь, проверяя массив с «>0». Это рассчитывает Истина или Ложь:

    Которые мы затем преобразовываем в 1/0 с помощью двойного отрицания (—):

    Параметр чувствителен к регистру

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

    Сумма, если ячейки содержат определенный текст

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

    В примере показано, ячейка G4 содержит эту формулу:

    Эта формула суммирует суммы в столбце D, когда значение в столбце C содержит «футболка». Обратите внимание, что СУММЕСЛИ-это не регистр.

    Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».

    Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символа» и так далее.

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

    Альтернатива с СУММЕСЛИМН

    Вы также можете использовать функцию СУММЕСЛИМН. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН:

    Обратите внимание, что диапазон суммирования всегда стоит первым в функции СУММЕСЛИМН.

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

    В сумме, если ячейки заканчиваются определенным текстом, можно использовать функцию СУММЕСЛИ.

    В примере показано, ячейка G5 содержит эту формулу:

    Эта формула суммирует ячейки именованного диапазона сумма (D5:D11), только если ячейки именованного диапазона (С5:С11) оканчиваются на «шапка».

    Обратите внимание, что СУММЕСЛИ не поддерживает регистр. Критерию «*шапка» соответствует любой текст, который заканчивается «Шапка» или «шапка».

    Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».

    Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символа» и так далее.

    Чтобы соответствовали все элементы, которые оканчиваются на «шапка» перед текстом нужно поставить звездочку (*):

    Обратите внимание, что вы должны заключить текст и подстановочные знаки в двойные кавычки («»).

    Альтернатива с СУММЕСЛИМН

    Вы также можете использовать функцию СУММЕСЛИМН в сумме, если клетки начинаются с. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН:

    Обратите внимание, что диапазон суммирования всегда стоит первым в функции СУММЕСЛИМН.

    Сумма, если равен х или у

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

    Читайте также:  Что писать в head

    В примере, мы подсчитываем все продажи либо от Западного или Северного региона. Формула в ячейке G6 является:

    =СУММЕСЛИ(C5:C14; «Западный»; E5:E14)+СУММЕСЛИ(C5:C14; «Северный»; E5:E14)

    Каждый экземпляр СУММЕСЛИ обеспечивает промежуточный итог, один для продаж на Западе, один для продаж на Севере. Формула просто добавляет эти два результата вместе.

    СУММЕСЛИ с аргументом массива

    Более элегантное решение, чтобы дать функции СУММЕСЛИ более одного значения для критериев, использовать константу массива. Чтобы сделать это, постройте нормальный СУММЕСЛИ, но пакет критериев в синтаксисе массива — фигурные скобки, с отдельными элементами, разделенные запятыми. И, наконец, обернуть всю функцию СУММЕСЛИ в функцию СУММ. Это необходимо, потому что СУММЕСЛИ будет рассчитывать один результат для каждого элемента массива критериев. Они должны быть добавлены вместе, чтобы получить один результат.

    СУММПРОИЗВ альтернатива

    Вы можете также использовать СУММПРОИЗВ для подсчета ячеек с логикой ИЛИ. Формула:

    =СУММПРОИЗВ( количество * (( регион = «Западный» ) + ( регион = «Северный» )))

    Это также может быть записано в виде:

    СУММПРОИЗВ не так быстра, как СУММЕСЛИ, но разница в скорости не заметна с меньшими наборами данных.

    Сумма, если ячейки не пустые

    =СУММЕСЛИ( диапазон ; «<>» ; суммарный_диапазон )

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

    В показанном примере, ячейка G4 содержит следующую формулу:

    Эта формула суммирует суммы в колонке D только тогда, когда значение в столбце С не пусто

    Функция СУММЕСЛИ поддерживает все стандартные операторы Excel, в том числе не равно к, который вводится в <>.

    При использовании оператора в критериях функции как СУММЕСЛИ, необходимо заключить его в двойные кавычки ( «»). При использовании только «<>» в качестве критерия, вы можете думать о значении как «не равно пустой», или «не пусто».

    Альтернатива с СУММЕСЛИМН

    Вы можете также использовать функцию суммы СУММЕСЛИМН, если ячейки не являются пустыми. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эта формула СУММЕСЛИМН эквивалентна формуле СУММЕСЛИ выше:

    С СУММЕСЛИМН диапазон сумма всегда стоит первым.

    Описание функции

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

    Функция =ЕФОРМУЛА(ЯЧЕЙКА) возвращает ИСТИНА если указанная ячейка содержит формулу и ЛОЖЬ в противном случае. Имеет всего 1 аргумент:

    • ЯЧЕЙКА — Ссылка на ячейку в которой необходимо выполнить поиск формулы.

    Пример

    Пример использования данной функции приведен на картинке справа.

    Код на VBA

    Данная функция входит в состав надстройки VBA-Excel. Также вы можете внедрить ее в свой проект, использовав код ниже.

    Ссылка на основную публикацию
    Функция датазнач в excel
    Возвращает числовой формат даты, представленной в виде текста. Функция ДАТАЗНАЧ используется для преобразования даты из текстового представления в числовой формат....
    Файловый менеджер для ubuntu server
    Работа с файлами в операционной системе Ubuntu осуществляется через соответствующий менеджер. Все дистрибутивы, разработанные на ядре Linux, позволяют юзеру всячески...
    Файлы dll чем открыть
    Файлы формата DLL открываются специальными программами. Существует 2 типа форматов DLL, каждый из которых открывается разными программами. Чтобы открыть нужный...
    Функция если ячейка содержит определенный текст
    Функция ЕСЛИ СОДЕРЖИТ Наверное, многие задавались вопросом, как найти функцию в EXCEL«СОДЕРЖИТ» , чтобы применить какое-либо условие, в зависимости от...
    Adblock detector