Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как подсчитать подстановочные знаки (звездочки или вопросительные знаки) в Excel?

Подстановочные знаки в EXCEL

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

Если у вас есть ряд текстовых значений, и вы хотите найти или подсчитать эти значения по определенным критериям, использование подстановочных знаков (* и ?) значительно расширяет возможности поиска.

Подстановочный знак ? * (звездочка) обозначает последовательность символов любой длины. Вопросительный знак означает один символ любой длины, а звездочка — последовательность символов любой длины.

Использование.

Найти

Один из символов Пример: условие «bar?n» соответствует результатам «barin», «baron» и «ram».

Любое количество символов Пример: Условие «*-восток» соответствует результатам «северо-восток» и «юго-восток».

(каракули), за которым следуют ?, * или

Используется знак вопроса, звездочка или тильда. При поиске условия «an06»

?» вы найдете «an06?

Использование в функциях

Предположим, что задача состоит в подсчете ячеек, содержащих все склонения слова hammer (например, hammer, hammer, hammer, hammer, hammer, hammer и т.д.). Самый простой способ решить эту проблему — использовать критерии отбора с подстановочным знаком * (звездочка). Идея заключается в следующем: для условий выбора с текстовыми значениями в качестве критерия может быть задана только часть текстового значения (в нашем случае молоток), а остальная часть может быть заменена подстановочными знаками. В нашем случае критерий будет следующим — молоток*.

Текстовые значения (склонения слова hammer) попадают в диапазон A2:A10, поэтому напишите =CALCULATION(A2:A10; «hammer*») . В диапазоне A2:A10 результат вычислит все склонения слова hammer.

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

  • СЧЁТЕСЛИ() (см. статью Подсчет текстовых значений с единственным критерием ) и СЧЁТЕСЛИМН()
  • СУММЕСЛИ() и СУММЕСЛИМН()
  • СРЗНАЧЕСЛИ()
  • В таблице условий функций БСЧЁТ() (см. статью Подсчет значений с множественными критериями ), а также функций БСЧЁТА() , БИЗВЛЕЧЬ() , ДМАКС() , ДМИН() , БДСУММ() , ДСРЗНАЧ()
  • ПОИСК()
  • ВПР() и ГПР()
  • ПОИСКПОЗ()
Читайте так же:
Как переместить выделенные ячейки наверх в Excel?

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

Использование в инструменте Найти…

Вы также можете использовать подстановочные знаки в стандартном инструменте Find……. ( Главная/Редактировать/Найти и выберите/Найти… или нажмите CTRL+F )

На рисунке выше показано, как найти ячейки, содержащие слово из пяти букв, первые 4 из которых — «drill. «.

Использование в Расширенном фильтре

Использование символов подстановки в расширенном фильтре описано в этой статье.

Использование в Условном форматировании

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

Формула суммы ячеек в Excel

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

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

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

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

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

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

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

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

Альтернативный вариант с помощью SUMSLIMN

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

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

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

Читайте так же:
Как очистить ячейки, если они кажутся пустыми, но их нет в Excel?

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

В приведенном примере формула для F5 :

В SUMMESLIMN логическое поведение является автоматическим. Все, что нам нужно сделать, это поместить две пары диапазон/критерий в один и тот же диапазон (B5:B9).

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

Когда оба критерия вычислены как True в одной строке, SUMMESLIMN добавляет значения в столбец C.

Обратите внимание, что SUMMESLYMN не чувствителен к регистру.

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

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

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

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

Одним из решений является использование функции SUMMER с NUMBER + SEARCH или HELP.

Формула основана на нахождении текста в ячейке:

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

Читайте так же:
Как подсчитать количество слов в ячейке или диапазоне ячеек в Excel?

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

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

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

Этот параметр чувствителен к регистру.

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

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

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

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

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

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

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

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

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

Альтернативный вариант с использованием SUMSLIMN

Вы также можете использовать функцию SUMMARY. SUMSLIMN может обрабатывать больше критериев, и порядок аргументов отличается от порядка в SUMSLIMN. Аналогична формуле SUMSLYMN:

Читайте так же:
Как показать проценты в столбчатой ​​диаграмме с накоплением в Excel?

Обратите внимание, что в функции SUMMESLYMN интервал суммы всегда идет первым.

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

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

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

Вот формула в ячейке G5:

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

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

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

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

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

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

Альтернативно с помощью SUMSLIMN

Вы также можете использовать функцию SUM в сумме, если ячейки начинаются с. SUMLIMN может обрабатывать более одного критерия, и порядок аргументов отличается от порядка в SUMLIMN. Соответствующая формула для SUMLIMN:

Обратите внимание, что в функции SUMMESLYMN интервал суммы всегда стоит на первом месте.

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

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

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

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

Каждый экземпляр SUMMESLY предоставляет промежуточный итог, один для продаж на Западе и один для продаж на Севере. Формула просто складывает эти два итога вместе.

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

Читайте так же:
Как преобразовать дату и метку времени Unix в Excel?

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

Вы также можете использовать SUMMPROIZE для подсчета ячеек с логикой ИЛИ. Формула:

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

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

SUMMPROIZE не так быстр, как SUMMESLY, но разница в скорости незаметна при небольших наборах данных.

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

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

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

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

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

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

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

Альтернативный вариант с SUMMESLIMN.

Вы также можете использовать функцию SUM, если ячейки не пустые. SUMLIMN может обрабатывать более одного критерия, и порядок аргументов отличается от SUMLIMN. Эта формула SUMMESLIMN аналогична формуле SUMMESLIMN, приведенной выше:

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector