Подсчет-позиций-по-цвету-СЧЕТЕСЛИ-devsap

=СЧЕТЕСЛИ(диапазон,критерий)

Подсчет ячеек, содержащих определенный текст

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

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

Пример проблемы и решения

В показанном примере, у нас есть небольшой набор данных. Все Заказы на брюки, которые приходят в 4 различных цветах: красный, синий, серый и черный.

Слева, мы с помощью СЧЕТЕСЛИ представить разбивку по цветам. Формула в ячейке G9-это:

=СЧЕТЕСЛИ(цвета,G9)

где «цвет» — это именованный диапазон ячеек E5:E13. Мы используем именованный диапазон в этом случае, чтобы принять формулу легко скопировать сводную таблицу. Кроме того, можно использовать абсолютную ссылку вот так:

=СЧЕТЕСЛИ($E$5:$E$11,G9) ; абсолютный адрес вариант

Как эта формула работает

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

В этом случае, мы тестируем равенства, поэтому нам не нужно использовать какие-либо логические операторы. Можно просто ввести именованный диапазон «цветной» ассортимент, и ссылку на соседнюю ячейку в столбце F для критериев.

Файл Подсчет-позиций-по-цвету-СЧЕТЕСЛИ-devsap

Подсчет значений на другом листе (СЧЕТЕСЛИ)

 

http://devsap.ru/wp-content/uploads/2017/10/ПОДСЧЕТ-значений-на-ДРУГОМ-ЛИСТЕ-СЧЕТЕСЛИ-DEVSAP.jpg

=СЧЁТЕСЛИ(Лист2!$1:$1048576;C5) С5= Катя

Объяснение

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

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

=СЧЕТЕСЛИ(Лист2!1:1048576,С4)

Как эта формула работает

Второй лист книги, Лист2, содержит неопределенное количество имен в диапазоне.

Функция СЧЕТЕСЛИ имеет диапазон и критерии. В этом случае, СЧЕТЕСЛИ, мы выделяем диапазон равный всем строкам в Лист2.

Лист2!1:1048576

Для критериев, мы используем ссылку на С4, в которых содержится «Катя». Тогда СЧЕТЕСЛИ возвращает 6, так как есть 6 ячеек в Лист2 равные значению «Катя».

Содержит и равна

Если вы хотите посчитать все ячейки, содержащие значение в С4, вместо того, чтобы все клетки равна С4, добавить символы к таким критериям, как этот:

=СЧЕТЕСЛИ(Лист2!1:1048576,»*»&С4&»*»)

Теперь СЧЕТЕСЛИ будет подсчитать ячейки с подстроки «Катя» в любом месте в клетке.

Производительности

В общем, это не хорошая практика, чтобы указать диапазон, включающий все ячейки листа. Это может вызвать серьезные проблемы с производительностью, поскольку ассортимент включает в себя миллионы и миллионы клеток. Когда это возможно, ограничить диапазон для разумный область.

Поиск на нескольких листах

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

Подсчет количества повторов во всей книге

Для подсчета матчей за всю книгу, вы можете использовать формулу на основе функции СЧЕТЕСЛИ и функции СУММПРОИЗВ. Что то похожее на

D5: = СУММПРОИЗВ( СЧЕТЕСЛИ (ДВССЫЛ( «‘» & листы & «‘!А1:Z10000»

Функция СЧЕТЕСЛИ в Excel

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

Файл Excel ПОДСЧЕТ-значений-на-ДРУГОМ-ЛИСТЕ-(СЧЕТЕСЛИ)-devsap

Поиск в Диапазоне совпадения определенного текста СЧЕТЕСЛИ devsap

Поиск в Диапазоне совпадения определенного текста СЧЕТЕСЛИ devsap

=СЧЕТЕСЛИ («диапазон»;»*»&значение&»*»)>0

Связанные формулы

Диапазон содержит конкретное значение
Спектр содержит одну из многих значений
Спектр содержит один из множества подстрок
Ячейка содержит определенный текст
Ячейка содержит одну из многих вещей

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

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

=СЧЕТЕСЛИ («Диапазон»,»*»&E6&»*»)>0

Как эта формула работает

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

Звездочка (*) является подстановочным знаком для одного или нескольких символов. Объединяя звездочки перед и после значения в E^, в Формуле будет рассчитывать на подстроки, если он появляется в любом месте в любую ячейку диапазона.

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

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

=Если(СЧЕТЕСЛИ(диапазон,»*»&значение&»*»),»Да»,»Нет»)

Рабочий вариант.

=СЧЁТЕСЛИ($B$6:$B$10;»*»&E6&»*»)>0

=ЕСЛИ(СЧЁТЕСЛИ($B$6:$B$10;»*»&E6&»*»)>0;»ДА»;»НЕТ»)

Файл Excel Поиск в Диапазоне совпадения определенного текста СЧЕТЕСЛИ devsap

 

 

Как использовать функцию СЧЕТЕСЛИ devsapexcel

Как использовать функцию СЧЕТЕСЛИ devsapexcel

 

Описание

Функция СЧЕТЕСЛИ в Excel подсчитывает количество ячеек в диапазоне, который соответствует заданным критериям.

Если вы хотите применить несколько критериев, попробуйте использовать функции СЧЕТЕСЛИМН.

Синтаксис

Синтаксис для функцмии СЧЕТЕСЛИ в  Excel-это:

СЧЕТЕСЛИ( диапазон, критерий )
Параметры или Аргументы

диапазон
Диапазон ячеек, которые вы хотите рассчитывать на основе критериев.
критерии
Критерии, используемые для определения, какие клетки считать.

Пример (как функция)

Давайте посмотрим на некоторые функции Excel СЧЕТЕСЛИ примеры и узнайте, как использовать функцию СЧЕТЕСЛИ в функцию рабочего листа в программе Excel:

 

На основании изложенного таблицы Excel, СЧЕТЕСЛИ в следующих примерах вернется:

1. =СЧЕТЕСЛИ(C8:C12; С8)
Результат: 1

2. =СЧЕТЕСЛИ(C:C; С8)
Результат: 1

3. =СЧЕТЕСЛИ(C8:C12; «>=2001»)
Результат: 2
Используя Именованные Диапазоны

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

Например, мы создали именованный диапазон называемой «семьи», который ссылается на столбец C на листе 1.

 

Затем мы ввели следующие данные в Excel:

 

На основе таблицы Excel выше:

4. =СЧЕТЕСЛИ(семья, С8)
Результат: 1

5. =СЧЕТЕСЛИ («семья», «>=2001»)
Результат: 2

Для просмотра именованных диапазонов: в меню Вставка выберите пункт имя > определить.

пытаюсь использовать СЧЕТЕСЛИ на выделение ячеек (не обязательно один сплошной спектр), и синтаксис функции не позволит. Есть ли другой способ сделать это?

Вот пример что я хочу быть в состоянии сделать:

6. не работает  =СЧЕТЕСЛИ(c7,c11,d10,c18,c17;»>2003″)

Ответ: к сожалению, функция СЧЕТЕСЛИ не поддерживает несколько диапазонов. Однако, вы могли бы попробовать суммирования нескольких COUNTIFs.

Например:

7.  =Сумм(СЧЕТЕСЛИ(c7;»>2003″);СЧЕТЕСЛИ(c11;»>2003″);СЧЕТЕСЛИ(d10;»>2003″);СЧЕТЕСЛИ(c18;»>0″);СЧЕТЕСЛИ(c17;»>0″))

Или

8. =СЧЁТЕСЛИ(C6;»>2003″)+СЧЁТЕСЛИ(C10;»>2003″)+СЧЁТЕСЛИ(D9;»>2003″)+СЧЁТЕСЛИ(C17;»>0″)+СЧЁТЕСЛИ(C16;»>0″)

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

Например:

9. =СЧЁТЕСЛИ(C3:C18;»>=2004″)

Я хочу заменить 2004 с ячейки F6. Как мне это сделать?

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

10.  =СЧЁТЕСЛИ(C4:C19;»>=»&F7)

Вопрос:я хотел бы сделать следующее:

11.  =СЧЁТЕСЛИ(АБС(C4:C19);»>2004″)

т. е. подсчитать количество значений в диапазоне C4:А19, которые имеют ненулевой величины. Синтаксис я пробовал не работает. Не могли бы вы помочь?

Ответ: потому что Вы не можете применить функцию ABS в диапазоне C4:А19, то надо разбить формулу на две функции СЧЕТЕСЛИ следующим образом:

12.  =СЧЁТЕСЛИ(C3:C18;»>2004″)+СЧЁТЕСЛИ(C3:C17;»<2000″)

Это позволит подсчитать количество значений, которые больше 2004 или меньше 2000.

 

 

Файл Excel Как использовать функцию СЧЕТЕСЛИ devsap

СЧЕТЕСЛИ/СЧЕТЗ процентное соотношение

СЧЕТЕСЛИ/СЧЕТЗ процентное соотношение

=СЧЕТЕСЛИ(диапазон,критерий)/СЧЕТЗ(диапазон)

Связанные формулы

СЧЕТЕСЛИ

Для вычисления процентов ,можно использовать СЧЕТЕСЛИ , вместе с СЧЕТЗ.

В примере, показанаг в Формуле Н9:

=СЧЕТЕСЛИ(Диапазон,F9)/СЧЕТЗ(Диапазон)

=СЧЁТЕСЛИ($B$5:$B$10;F9)/СЧЁТЗ($B$5:$B$10)

Как эта формула работает

СЧЕТЕСЛИ настроена на подсчет клеток в именованный диапазон «Статус», которая относится к $B$5:$B$10. Критерии  F9.

функция СЧЕТЗ просто подсчитывает все непустые ячейки в указанном диапазоне Статус ($B$5:$B$10) для получения общего количества.

Результат СЧЕТЕСЛИ делится на СЧЕТЗ результат процент. Все значения в столбце H  отформатированы «процент» процентный Формат.

 

Файл СЧЕТЕСЛИ/СЧЕТЗ процентное соотношение excel

СчЁтесли не равно Excel

СчЁтесли не равно Excel

=СЧЕТЕСЛИ («диапазон»,»критерий <>х»)

Связанные формулы

СЧЕТЕСЛИ

Если вам нужно подсчитать Количество ячеек, которые содержат значения, не равные определенному значению, можно использовать функцию СЧЕТЕСЛИ. В общем виде формула  диапазон ячеек, и X представляет значение (критерий). Суммируется все значения которые не попали под критерий.

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

=СЧЁТЕСЛИ( C5:C10;»<>100″)

Как работает формула:

СЧЕТЕСЛИ подсчитывает количество ячеек в диапазоне, которые удовлетворяют критериям.

В этом примере мы используем «» (логический оператор «не равно») и количество ячеек в диапазоне C5:C10, что не равно «100». СЧЕТЕСЛИ возвращает в результате суму значений ячеек не соответствующие критерию.

СЧЕТЕСЛИ является не чувствительным к регистру.

Если вы хотите использовать значение из другой ячейки как часть критерия, использование амперсанда ( & ), чтобы объединить так:

=СЧЕТЕСЛИ («диапазон»,»<>»&А1)

Если значение в ячейке А1 является «100», то критерии будут «100» после объединения, и СЧЕТЕСЛИ подсчет ячеек не будет равна 100.

Файл Подсчет значений <> не равно и СЧЁТЕСЛИ Excel

Подсчет клеток, которые содержат либо x или Y Excel

Подсчет клеток, которые содержат либо x или Y Excel

=СУММПРОИЗВ(—((ЕЧИСЛО(НАЙТИ(«критерий»;диапазон)) + ЕЧИСЛО(НАЙТИ(«критерий»;диапазон)))>0))

Связанные формулы

ЕЧИСЛО   Выдает логическое значение ИСТИНА, если аргумент ссылается на число.

НАЙТИ     Ищет вхождение одного текста в другой (с учетом регистра).

Вариант № 1

Ячейка содержит определенный текст
Подсчет ячеек, содержащих определенный текст
Подсчет клеток

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

При подсчете клеток с помощью «или» критерии, вы должны быть осторожны, чтобы не получить двойной счет. Например, если подсчет ячеек, содержащих «asd» или «edf»  вы не можете просто сложить две функции СЧЕТЕСЛИ потому что это может привести к двойному подсчету клеток, которые содержат как «asd» и «edf».

Решение, вы можете использовать функцию СУММПРОИЗВ с функцией ЕЧИСЛО + НАЙТИ  комбо. Формула в ячейке E12-это:

=СУММПРОИЗВ(—((ЕЧИСЛО(НАЙТИ(«asd»;D5:D11)) + ЕЧИСЛО(НАЙТИ(«edf»;D5:D11)))>0))

Эта формула основана на формуле:

Функции ЕЧИСЛО(НАЙТИ(«edf»;D5:D11)

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

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

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

Наконец, функция СУММПРОИЗВ добавляет эти цифры вверх.

Вариант № 2

Вспомогательный столбец решение

С вспомогательным столбцом для проверки каждой ячейки в отдельности, проблема менее сложна. Мы можем использовать СЧЕТЕСЛИ с двумя значениями (при условии, как «массив»). Формула в H5 :

=—(СУММ(СЧЁТЕСЛИ(G5;{«*asd*»;»*edf*»}))>0)

СЧЕТЕСЛИ возвращает массив, который содержит два пункта: счетчик для «asd» и рассчитывает на «edf». Во избежание двойного счета, мы добавляем элементы и затем принудительно результат «истина/ложь» с «>0». Наконец, преобразуем значения Истина или Ложь на 1 и 0 с двойной отрицательный (—).

Конечный результат 1 или 0 для каждой ячейки. Чтобы получить общую для всех ячеек в диапазоне, вы будете просто просуммировать столбец.

 

Файл Подсчет клеток, которые содержат либо x или Y xls

 

Подсчет ячеек, содержащих по пять символов EXCEL

Подсчет ячеек, содержащих по пять символов EXCEL

=СЧЕТЕСЛИ («диапазон»,»?????»)

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

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

=СЧЁТЕСЛИ(C5:C11;»?????»)

Вот как эта формула работает:

СЧЕТЕСЛИ подсчитывает количество ячеек в диапазоне, которые содержат пять знаков соответствия содержимого каждой ячейки с образцом»?????», который подается в качестве критериев для СЧЕТЕСЛИ. «?» Символ является подстановочным знаком в Excel, что означает «любой символ», поэтому эта модель будет подсчитать ячейки, которая содержит любые пять символов. Содержащие клетки удовлетворяющие критерию возвращается как число, которое, в примере это число 1.

 

Файл подсчет ячеек, содержащих по пять символов  xls 

 

Подсчет ячеек, содержащих отрицательные числа

Подсчет ячеек, содержащих отрицательные числа

=СЧЕТЕСЛИ («диапазон»;»<0″)

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

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

=СЧЁТЕСЛИ(C5:C11;»<0″)

Вот как эта формула работает:

СЧЕТЕСЛИ подсчитывает Количество ячеек в диапазоне, которые соответствуют заданным условиям. В данном случае критерии подается как «<0».

 

Файл Подсчет ячеек, содержащих отрицательные числа xls

Подсчет клеток, которые начинаются или содержат разнsе символы Excel

Подсчет клеток, которые начинаются или содержат разные символы Excel

=СЧЕТЕСЛИ («диапазон»,»*»&критерий&»*»)

Связанные формулы

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

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

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

F5 = «КД»

=СЧЁТЕСЛИ($C$5:$C$11;»*»&F5&»*»)

Вот как эта формула работает:

СЧЕТЕСЛИ подсчитывает количество ячеек в диапазоне, которые начинаются или содержат КД путем сопоставления содержимого каждой ячейки с образцом «*»&ячейка&»*», которая указывается в качестве критерия. Символ » * » (звездочка) является подстановочным знаком в Excel что означает «любое количество символов». Количество ячеек, которые соответствуют этому шаблону возвращается в виде числа.

 

Файл Подсчет клеток, которые начинаются или содержат разные символы xls