Проверка любого из двух текстовых значений

Крис хочет подсчитать ячейки, содержащие текстовое значение A или текстовое значение B в любом месте текста ячейки. Если в ячейке есть и A, и B, она хочет посчитать их, но только один раз. Например, у Криса есть три ячейки, содержащие «семя яблока», «яблоню» и «семечко персика», и она хочет знать количество ячеек, содержащих «яблоко» или «семя». (Правильный ответ, который должен быть возвращен: 3.)

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

 = СЧЁТЕСЛИ (A1: A9, «* яблоко *») + СЧЁТЕСЛИ (A1: A9, «* семя *») - СЧЁТЕСЛИ (  A1: A9, «* семя * яблоко *») - СЧЁТЕСЛИ (A1: A9, «* яблоко * семя *») 

Формула подсчитывает все ячейки, содержащие либо «яблоко», либо « seed “, а затем вычитает все ячейки, содержащие” seed “, за которым следует” apple “(оба слова в ячейке) или” apple “, за которым следует” seed “(те ​​же слова в обратном порядке).

Другое решение, это немного короче, основано на функциях COUNTA и FIND, как показано здесь:

 = COUNTA (A1: A9) -SUMPRODUCT (- (ISERROR (FIND (  "яблоко", A1: A9))) * - ISERROR (FIND ("seed", A1: A9))) 

Формула считает ячейки, содержащие значения, а затем вычитает все те ячейки, которые не содержат ни «яблоко», ни «семя». Обратите внимание, что функция НАЙТИ чувствительна к регистру, что означает, что найдено только «яблоко», а не «Яблоко» или «ЯБЛОКО». Если вам нужно что-то без учета регистра, замените НАЙТИ на ПОИСК.

Вы также можете, если хотите, использовать одну из функций базы данных Excel. Если у вас есть заголовок столбца для ваших исходных фраз, это не так сложно сделать, и в результате получается самая короткая формула. Все, что вам нужно сделать, это создать соответствующую таблицу критериев. Например, предположим, что ваши данные находятся в формате A1: A9, а первая ячейка столбца содержит заголовок, например «Мои фразы». В другом столбце вы должны поместить тот же заголовок, а затем в две ячейки непосредственно под ним поместить эти две формулы:

 * apple ** seed * 

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

 = DCOUNTA (A1: A9,1, D1: D3) 

Конечно, вы также можете использовать формулу массива (вводимую нажатием Ctrl + Shift + Enter ), чтобы получить ответ.. Ниже приводится одна из таких формул, которая, опять же, полагается на фразы, которые проверяются на принадлежность к A1: A9:

 = SUM (- ((ISNUMBER (FIND ("яблоко", A1: A9  )) + ISNUMBER (FIND ("seed", A1: A9)))> 0)) 

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

 Функция FindTwoStrings (rng As Range, s1 As String, _ s2 As String) As Integer Application.Volatile If TypeName (rng)  "Range  "Затем выйти из функции Dim cell As Range For each cell In rng.Cells If (InStr (1, UCase (cell.Value), UCase (s1), _ vbTextCompare)> 0) Or (InStr (1, UCase (cell.Value)  ), _ UCase (s2), vbTextCompare)> 0) Then _ FindTwoStrings = FindTwoStrings + 1 Следующая функция CellEnd 

Чтобы использовать функцию, вы можете использовать эту формулу в ячейке:

 = FindTwoStrings (A1: A9, «яблоко», «семя») 

Оцените статью
Frestage.ru
Добавить комментарий