Если ячейка содержит, то подсчитайте, суммируйте, выделите, скопируйте или удалите
В нашем предыдущем уроке мы рассматривали Excel, если содержит формулы, которые возвращают некоторое значение в другой столбец, если целевая ячейка содержит заданное значение. Помимо этого, что еще вы можете сделать, если ячейка содержит определенный текст или число? Множество вещей, таких как подсчет или суммирование ячеек, выделение, удаление или копирование целых строк и многое другое.
Примеры формулы Excel «Подсчитать, если ячейка содержит»
В Microsoft Excel есть две функции для подсчета ячеек на основе их значений: СЧЁТЕСЛИ и СЧЁТЕСЛИМН. Эти функции охватывают большинство, хотя и не все, сценариев. Приведенные ниже примеры научат вас, как выбрать подходящий счетчик, если ячейка содержит формулу для вашей конкретной задачи.
Подсчитайте, содержит ли ячейка какой-либо текст
В ситуациях, когда вы хотите подсчитать ячейки, содержащие какой-либо текст, используйте подстановочный знак звездочки в качестве критерия в формуле СЧЁТЕСЛИ:
СЧЁТЕСЛИ(диапазон“*”)
Или используйте функцию СУММПРОИЗВ в сочетании с ИСТЕКСТ:
СУММПРОИЗВ(–(ISTEX(диапазон)))
Во второй формуле функция ИСТЕКСТ оценивает каждую ячейку в указанном диапазоне и возвращает массив значений ИСТИНА (текст) и ЛОЖЬ (не текст); двойной унарный оператор (–) преобразует ИСТИНА и ЛОЖЬ в 1 и 0; и СУММПРОИЗВ складывает числа.
Как показано на скриншоте ниже, обе формулы дают одинаковый результат:
=СЧЁТЕСЛИ(A2:A10;”*”)
=СУММПРОИЗВ(–(ИСТЕКСТ(A2:A10)))
Вы также можете посмотреть, как подсчитывать непустые ячейки в Excel.
Считать, содержит ли ячейка определенный текст
Чтобы подсчитать ячейки, содержащие определенный текст, используйте простую формулу СЧЁТЕСЛИ, как показано ниже, где диапазон клетки для проверки и текст Текстовая строка для поиска или ссылка на ячейку, содержащую текстовую строку.
СЧЁТЕСЛИ(диапазон“текст“)
Например, для подсчета ячеек в диапазоне A2:A10, содержащих слово «платье», используйте следующую формулу:
=СЧЁТЕСЛИ(A2:A10, “платье”)
Или тот, что показан на скриншоте:
Вы можете найти больше примеров формул здесь: Как подсчитать ячейки с текстом в Excel: любые, определенные, отфильтрованные ячейки.
Подсчет, если ячейка содержит текст (частичное совпадение)
Чтобы подсчитать ячейки, содержащие определенную подстроку, используйте функцию СЧЁТЕСЛИ с подстановочным знаком звездочки.
.
Например, чтобы подсчитать, сколько ячеек в столбце А содержат слово «платье» как часть своего содержимого, используйте следующую формулу:
=СЧЁТЕСЛИ(A2:A10;”*платье*”)
Или введите нужный текст в какую-либо ячейку и соедините эту ячейку с подстановочными знаками:
Подсчет ячеек, содержащих определенную подстроку (частичное совпадение)
Дополнительные сведения см. в разделе Формулы СЧЁТЕСЛИ с частичным совпадением.
Подсчет, если ячейка содержит несколько подстрок (логика И)
Для подсчета ячеек с несколькими условиями используйте функцию СЧЁТЕСЛИМН. Excel COUNTIFS может обрабатывать до 127 пар диапазон/критерий, и будут учитываться только те ячейки, которые соответствуют всем указанным условиям.
Например, чтобы узнать, сколько ячеек в столбце А содержат слова «платье» И «синий», используйте одну из следующих формул:
=СЧЁТЕСЛИМН(A2:A10,”*платье*”, A2:A10,”*синее*”)
Или же
Подсчитайте ячейки, которые соответствуют обоим указанным условиям.
Подсчитайте, если ячейка содержит число
Формула для подсчета ячеек с числами — самая простая формула, которую можно себе представить:СЧИТАТЬ(диапазон
)
Имейте в виду, что функция COUNT в Excel подсчитывает ячейки, содержащие любое числовое значение, включая числа, даты и время, потому что в терминах Excel последние два числа также являются числами.
В нашем случае формула выглядит следующим образом:
=СЧЁТ(A2:A10)
Для подсчета ячеек, НЕ содержащих чисел, используйте функцию СУММПРОИЗВ вместе с ЕЧИСЛО и НЕ:
Формулы для подсчета ячеек, содержащих или не содержащих числа
Сумма, если ячейка содержит текст
Если вы ищете формулу Excel для поиска ячеек, содержащих определенный текст, и суммирования соответствующих значений в другом столбце, используйте функцию СУММЕСЛИ.
Например, чтобы узнать, сколько платьев есть в наличии, используйте следующую формулу:
=СУММЕСЛИ(A2:A10,”*платье*”,B2:B10)
Где A2:A10 — текстовые значения для проверки, а B2:B10 — числа для суммирования.
Если ячейка содержит определенный текст, суммировать числа в другом столбце
Для суммирования по нескольким критериям используйте функцию СУММЕСЛИМН.
Например, чтобы узнать, сколько синих платьев доступно, используйте следующую формулу:
=СУММЕСЛИМН(B2:B10, A2:A10,”*платье*”,A2:A10,”*синее*”)
Или используйте этот:
=СУММЕСЛИМН(B2:B10, A2:A10,”*”&E1&”*”,A2:A10,”*”&E2&”*”)
Сумма ячеек с несколькими критериями
Выполнение различных расчетов на основе значения ячейки
В нашем последнем уроке мы обсудили три разные формулы для проверки нескольких условий и возврата разных значений в зависимости от результатов этих тестов. А теперь давайте посмотрим, как можно выполнять различные вычисления в зависимости от значения в целевой ячейке.
Предположим, у вас есть данные о продажах в столбце B, и вы хотите рассчитать бонусы на их основе: если объем продаж превышает 300 долларов, бонус составляет 10%; при продажах от 201 до 300 долларов бонус составляет 7%; для продаж от 101 до 200 долларов бонус составляет 5%, а при продажах менее 100 долларов бонус отсутствует.
Для этого просто умножьте объем продаж (B2) на соответствующий процент. Как узнать, на какой процент умножать? Путем тестирования различных условий с вложенными ЕСЛИ:
=В2*ЕСЛИ(В2>=300,10%, ЕСЛИ(В2>=200,7%, ЕСЛИ(В2>=100,5%,0)))
В реальных рабочих листах может быть удобнее вводить проценты в отдельные ячейки и ссылаться на эти ячейки в формуле:
=B2*IF(B2>=300,$F$5,ЕСЛИ(B2>=200,$F$4,ЕСЛИ(B2>=100,$F$3,$F$2)))
Выполнение различных расчетов на основе значения ячейки
Условное форматирование Excel, если ячейка содержит определенный текст
Если вы хотите выделить ячейки с определенным текстом, настройте правило условного форматирования Excel на основе одной из следующих формул.
Без учета регистра:ПОИСК(“текст “,topmost_cell
)>0
Деликатный случай:НАЙТИ(“текст “,topmost_cell
)>0
Например, чтобы выделить артикулы, содержащие слова «платье», создайте правило условного форматирования с приведенной ниже формулой и примените его к необходимому количеству ячеек в столбце А, начиная с ячейки А2:
0″ title=”Формула условного форматирования Excel: если ячейка содержит определенный текст”/>
Формула условного форматирования Excel: если ячейка содержит определенный текст
Формула условного форматирования Excel: если ячейка содержит текст (несколько условий)
Чтобы выделить ячейки, содержащие две или более текстовые строки, вложите несколько функций поиска в формулу И. Например, чтобы выделить ячейки «синее платье», создайте правило на основе этой формулы:
0, ПОИСК(“синее”, A2)>0)” title=”Формула условного форматирования Excel: если ячейка содержит несколько условий”/>
Формула условного форматирования Excel: если ячейка содержит несколько условий
Подробные инструкции см. в разделе Как создать правило условного форматирования с помощью формулы.
Если ячейка содержит определенный текст, удалить всю строку
- Если вы хотите удалить строки, содержащие определенный текст, используйте функцию поиска и замены Excel следующим образом:
- Выберите все ячейки, которые вы хотите проверить. Нажмите Ctrl + F, чтобы открыть Найти и заменить
- диалоговое окно. в Найти то, что поле, введите текст или число, которое вы ищете, и нажмите кнопку
- Найти все
- Нажмите на любой результат поиска, а затем нажмите Ctrl + A, чтобы выбрать все. Нажмите на Закрывать кнопка, чтобы закрыть
- Найти и заменить
- Нажмите Ctrl и кнопку минус одновременно (Ctrl -), которая является ярлыком Excel для удаления. в Удалить диалоговое окно, выберитеВесь ряд
и нажмите ОК. Сделанный!
Если ячейка содержит определенный текст, удалите всю строку.
Если ячейка содержит, выберите или скопируйте целые строки
В ситуациях, когда вы хотите выбрать или скопировать строки с соответствующими данными, используйте автофильтр Excel для фильтрации таких строк. После этого нажмите Ctrl + A, чтобы выбрать отфильтрованные данные, Ctrl + C, чтобы скопировать их, и Ctrl + V, чтобы вставить данные в другое место.
Чтобы отфильтровать ячейки с двумя или более критериями, используйте расширенный фильтр, чтобы найти такие ячейки, а затем скопируйте все строки с результатами или извлеките только определенные столбцы.
Вот как вы манипулируете ячейками на основе их значения в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь Excel Если ячейка содержит то – примеры
(файл .xlsx)
Вас также могут заинтересовать
