Наверное, многие задавались вопросом, как найти в EXCEL функцию «СОДЕРЖИТ» для применения любого условия, в зависимости от того, содержит ли строка текста часть слова, отрицание или часть имени аналога, особенно при заполнении регистры но нет.
Эта функциональность может быть достигнута с помощью комбинации двух общих стандартных функций: ЕСЛИ и СЧЁТЕСЛИ .
Рассмотрим пример автоматизации учета операционных показателей на основе бухгалтерских книг по продажам и возвратам (загрузка из сторонних программ автоматизации и т.д.)
У нас много линий реализации и возвратных документов .
Все документы имеют собственное название благодаря уникальному номеру .
Нам нужно создать атрибут «Только продажи» перед торговыми документами, чтобы в дальнейшем включить этот атрибут в сводную таблицу и исключить возвраты для оценки эффективности отдела продаж.
Выражение должно быть универсальным для обработки вновь добавленных данных .
Для этого вам необходимо:
Начнем с ввода функции ЕСЛИ (введите «=», введите имя SE, выберите его из раскрывающегося списка, нажмите fx в строке формул).
В открывшемся окне аргументов в поле Log_expression введите COUNTIF (), выберите его и нажмите fx 2 раза.
Также в открытом окне аргументов функции СЧЁТЕСЛИ в поле «Критерий» введите часть желаемого имени * инструмент *, добавив символ * в начале и в конце * .
Эта запись позволит вам не думать, с какой стороны написано слово «реализация» (до или после номера документа), а также позволит вам включить в расчет сокращенные слова «реализовано» и «разработчик.»
Теперь мы можем работать и агрегировать данные только по документам реализации, без учета возвратов. При добавлении новых данных в таблицу остается только удлинить строку с нашим выражением и обновить сводную таблицу.
Если вам понравился материал или даже он оказался полезным, вы можете поблагодарить автора, переведя определенную сумму, используя кнопку ниже:
(для перевода по карте нажмите VISA, а затем «перевести»)
Мы находим слово в диапазоне ячеек, которое соответствует критерию: точное соответствие критерию, совпадение с учетом регистра, совпадение только части символов слова и т.д.
Пусть список значений, которые вы ищете, содержит только отдельные слова (см. Столбец A на рисунке ниже).
Совет: О поиске слова в списках, состоящих из текстовых строк (то есть в ячейке содержится не одно слово, а несколько, разделенных пробелами), вы можете прочитать в статье Выделение ячеек с помощью ТЕКСТА с использованием условного форматирования в MS EXCEL.
Задачу поиска текстового значения в диапазоне ячеек можно разделить на несколько типов:
< >поиск значений, точно соответствующих критерию;поиск значений, содержащих критерий;значения ищутся с учетом РЕГИСТРА.
Это самый простой случай. Здесь вы можете использовать формулу, подобную приведенной ниже
= СЧЁТЕСЛИ (5 $: 11 австралийских долларов, «яблоки»)
Формула возвращает количество найденных значений, соответствующих критериям (см. Файл примера).
Типичный вопрос для этого типа поиска: есть ли в списке слово со слогом MA?
Чтобы ответить на такие вопросы, вам нужно указать в качестве критерия часть текстового значения. Например, чтобы выбрать все ячейки, содержащие все склонения слова яблоко (яблоко, яблоко, яблоко и т.д.), Вы можете использовать критерий подстановочного знака * (звездочка). Для этого нужно использовать конструкцию * яблоки*.
Типичная формула = СЧЁТЕСЛИ (5 долларов США: 11 австралийских долларов, «* MA*»)
Чувствительность к регистру означает, что вам нужно создавать сложные формулы или использовать дополнительные столбцы. Наиболее часто используемые формулы основаны на чувствительных к регистру функциях НАЙТИ (), ПОИСКПОЗ() .
Формула массива = ИЛИ (ПОИСКПОЗ («яблоки»; A5: A11)) дает ответ на вопрос, существует ли такой элемент в списке.
СОВЕТ:
Вы также можете ознакомиться с идеями поиска в статье «Найти текстовые значения в списках». Часть 1. Регулярные исследования.
У меня есть файл Excel с двумя листами Excel. Листы называются первым и вторым.
имя первого листа: first
имя первого листа: first
имя второго листа: второй
где мой кот
… На первом листе, если какое-либо животное не содержится на втором листе, оно должно быть выделено. «Улитка» должна быть выделена на листе животного
… Я использовал для этого формулу поиска в Excel. Я перехожу к условному форматированию и использую формулу, чтобы определить, какие ячейки нужно форматировать. Я реализовал код
= НЕ (ЕЧИСЛО (ПОИСК (‘Лист 1’! $ A: $ A, $ A: $ A))) В результате выбраны все животные
Что я пытаюсь сделать, так это то, что если животное не находится ни в одном из столбцов, оно будет выделено. Однако это не сработает. Вы можете решить эту проблему?
Когда ячейка содержит ошибку, например #VALUE! , это не текст с фразой «VALUE»; не то, что вы можете (или должны) искать таким образом. Это своего рода заполнитель, который показывает, где находится ошибка.
Чтобы определить, использует ли формула или функция ошибку, используйте ЕСТЬ ОШИБКА или ЕСЛИОШИБКА .
Например, если вы хотите вернуть Not Found, если ваша формула выдает ошибку, вы можете использовать:
Я предпочитаю ВПР для поиска совпадений.
Например, вы можете вставить ячейку B2 в первый рабочий лист :
. а затем введите или скопируйте формулу до ячейки A7 .
Если совпадающая фраза найдена в Second, то она покажет эту фразу, в противном случае будет выдана ошибка.
На этот раз, используя в качестве примера ISERROR (а также IF), вы можете увидеть, было ли совпадение, используя вместо этого следующую формулу в ячейке B2 на первом листе :
. а затем введите или скопируйте формулу до ячейки A7 .
[/ g3]
Есть документ в формате Excel. Есть много строк. В некоторых строках, в определенной ячейке слово одно и то же, скажем — «TheWord», но эти строки разбросаны по всей таблице. Как сделать эти строки последовательно?
Например, в ячейке 10 строк со словом «Таблица» и 10 слов со словом «Улица», и эти два слова не могут появиться в ячейке. Затем эти строки располагаются последовательно (сначала строка со словом Table, затем со словом Street, затем снова Table и так далее). Сначала вы должны составить 10 строк со словом Table, а затем 10 строк со словом Street.
Реализация может быть разной.
Это зависит, в частности, от того, сколько столбцов в таблице нужно искать по слову — один или несколько; сколько слов вы ищете (например, если их много, то ЕСЛИ будет неудобно или даже невозможно использовать). Предположим, что есть столбец, и это столбец A, и есть два поисковых слова: Table и Street.
Создайте дополнительный столбец B с такой формулой, как
Перетащите его вниз параллельно исходным данным. Затем ключевые слова TableStreet для каждой строки будут выбраны в столбце B и другие, если строка не содержит никаких ключевых значений.
Теперь вы можете выбрать столбцы A и B и отсортировать по значениям столбца B.
Если есть несколько ключевых слов, из-за ограничения количества вложений функции ЕСЛИ этот подход не будет работать. Тогда лучше использовать вариант с функцией ВЫБРАТЬ:
Работает так же: растянуть, отсортировать по второму столбцу.
Каждое новое ключевое слово добавляется к первому аргументу SELECT как элемент
+ ISNUMBER (SEARCH («keyword_n + 1», A1)) * [индекс + 1]
где keyword_n + 1 — это желаемое ключевое слово, а [index + 1] — это следующий индекс по порядку. В результате первый аргумент CHOOSE сокращается до числового значения, равного индексу элемента, который вы ищете *. В конце формулы находится список значений, возвращаемых этим индексом. Новое ключевое слово в виде текстовой строки добавляется в конец.
Остерегайтесь, в отличие от первого метода, при отсутствии строки ключевых слов в тексте возникает ошибка «#VALUE!» (вместо «другое», как в предыдущем примере).
* Работает правильно, если строка не может содержать более одного ключевого слова одновременно. Если у вас есть линия и с Street, и с Table, вы получите чушь собачьей. То же самое и с предыдущим методом.
У нас есть данные для анализа наших продуктов. Мы должны автоматически выбирать все продукты, выпущенные в 2006 году. К сожалению, в таблице отсутствует еще один столбец, содержащий значение года производства каждого продукта. Но вы можете видеть, что во фрагменте идентификатора (ID) товара указан год выпуска. Если это так, нам нужно найти какой-то текст в Excel. А затем мы будем использовать условное форматирование с текстовыми функциями в формуле. Благодаря этому мы реализуем решение проблемы со скоростью света.
Пример таблицы артефактов:
Чтобы выбрать продукцию 2006 года выпуска по идентификатору, выполните следующие действия:
< >Выделите диапазон ячеек A2: A10 и выберите инструмент: ГЛАВНАЯ — Стили — Условное форматирование — Новое правило».
Выберите: «Использовать формулу для определения форматированных ячеек». 
Чтобы найти фрагмент текста в ячейке Excel, введите формулу: = MID (A2; 5; 4) = «2006″Нажмите кнопку «Формат», чтобы установить красный цвет заливки ячейки. И нажмите ОК во всех открытых окнах.
Продукция, выставляемая в цвете с 2006 года:
Далее разберем принцип формулы и ее модификацию с аналогичными текстовыми функциями.
Чтобы легко понять, как нам удалось отобразить определенные значения с помощью цвета с помощью условного форматирования, мы вкратце разберем шаги. Сначала мы извлекаем часть текста, а затем сравниваем его с желаемым значением. Но как извлечь текст из ячейки в Excel? Обратите внимание на функцию = PSTR () в формуле правила. Эта функция возвращает часть строки, взятую из каждой ячейки в столбце A, которая указана в первом аргументе. Ссылка в первом аргументе должна быть относительной, поскольку формула применяется к каждой ячейке в столбце A. Второй аргумент функции указывает количество символов в исходном тексте, с которого должен начинаться сегмент строки. Третий аргумент указывает количество символов, которые следует взять после определенного символа (во втором аргументе) в исходном тексте. В результате функция = MID () возвращает только часть текста длиной 4 символа, взятую из пятой буквы в каждой ячейке столбца A. Это «2005» или «2006». За функцией следует оператор сравнения со строковым значением «2006». Если ячейка содержит такой фрагмент текста, ей будет назначен новый формат.
вы можете использовать другие текстовые функции аналогичным образом с условным форматированием. Например, с помощью функции = ПРАВО () мы можем отобразить в цвете определенную группу товаров из партии C. Для этого вам нужно использовать формулу:
Здесь все просто, функция позволяет выделить фрагмент текста из ячейки Excel, начиная с правой стороны исходного текста. Требуемое количество символов указывается во втором аргументе функции ВПРАВО. Затем все, что возвращает функция, сравнивается со значением строки «C».
Во всех приведенных выше примерах очень легко использовать текстовые функции при условном форматировании, поскольку длина строк в исходных данных одинакова. Но что, если у нас есть разные типы индикаторов с разной длиной символа в исходных данных, и нам все равно нужно выбрать 2006 год или группу «С».
В решении этой проблемы нам поможет дополнительная текстовая функция в формуле = НАЙТИ (). В первом случае формула будет выглядеть так:
Для удобного подбора идентификаторов с разной длиной текстовой строки товаров из группы «С» воспользуемся следующей формулой:
Функция = НАЙТИ () ищет фрагмент текста в ячейке Excel. Затем он возвращает номер символа, в котором был найден текст, указанный в первом аргументе. Во второй теме указываем, где найти текст. И третий аргумент — это номер позиции для поиска в исходном тексте. Третий аргумент позволяет нам двигаться по линии. Например, если символ «C» используется в идентификаторе 2 раза. В этом случае пользователь указывает третий аргумент соответствующим образом.
Поскольку функция возвращает желаемое число, мы отлично используем его в качестве аргументов для других функций (MID и RIGHT).
