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

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

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

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

СЧЕТЕСЛИ

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

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

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

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

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

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

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

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

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

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

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

Общее число столбцов и строк в диапазоне Excel

Общее число столбцов и строк в диапазоне Excel

=ЧИСЛСТОЛБ(диапазон) Колонка

=ЧСТРОК(диапазон) Строка

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

Всего строк в диапазоне
Общее количество ячеек в диапазоне

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

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

=ЧИСЛСТОЛБ(D5:E12)

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

ЧИСЛСТОЛБ подсчитывает количество столбцов и возвращает результат в виде числа.

 

Файл Общее число столбцов и строк в диапазоне xls

 

Подсчет клеток, которые содержат либо 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

=ТРАНСП(ЕСЛИ(диапазон=»»;»»;диапазон))

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

В примере показано, формула массива в F4 является:

=ТРАНСП(ЕСЛИ(C4:D11=»»;»»;C4:D11))

Обратите внимание: это формула массива, который должен быть введен с помощью Control + Shift + ввод через весь диапазон F4:M5.

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

ТРАНСП функция автоматически переносит значения в горизонтальной ориентации на вертикальную ориентацию и наоборот.

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

Да, еще раз, перед тем как воспользоваться «Control + Shift + ввод» перейдите в ячейку и помощью Shift выделите диапазон от первой ячейки вниз или в право, после этого нажимайте Control + Shift + ввод.

Файл Транспонировать таблицу без нулей xls

 

 

 

Подсчет ячеек, содержащих ошибки Excel

Подсчет ячеек, содержащих ошибки Excel

=СУММПРОИЗВ(—ЕОШ(диапазон))

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

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

=СУММПРОИЗВ(—ЕОШ(D5:D11))

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

Функция СУММПРОИЗВ принимает один или более массивов и возвращает сумму произведений соответствующих чисел. Если подается только один массив, он просто суммирует элементы массива.

Функция ЕОШ вычисляется для каждой ячейки есть ли ошибка.  Результатом является массив значений Истина или Ложь:

{ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ}

Интернет-оператор (называемый двойной одинарный) приводит истинные/ложные значения в 0 и 1. Результирующий массив выглядит так:

{1;1;1;0;0;0;0}

Тогда функция sumproduct суммы элементов в массиве и возвращает общее, что в примере число 2.

Примечание: ЕОШ подсчитывает все ошибки, кроме #Н/д. если вы хотите тоже посчитайте #н/д, использовать функцию ЕОШИБКА вместо ЕОШ.

Вы также можете использовать функцию СУММ для подсчета ошибок. Структура формула такая же, но ее необходимо вводить как формулу массива (нажмите Control + Shift + ввод, а не просто ввод). После ввода формулы будет выглядеть следующим образом:

{=Сумм(—ЕОШ(D5:D11))}

Файл Подсчет ячеек, содержащих ошибки xls

 

 

 

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

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

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

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

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

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

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

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

 

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

 

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

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

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

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

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

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

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

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

 

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

Подсчет клеток, которые пустые и содержат значения Excel

Подсчет клеток, которые пустые и содержат значения Excel

=СЧЕТЗ(диапазон)

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

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

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

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

=СЧЁТЗ($C$5:$C$11)

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

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

Для подсчета клеток, которые пусты, можно воспользоваться функцией СЧИТАТЬПУСТОТЫ вот так:

=СЧИТАТЬПУСТОТЫ($C$5:$C$11)

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

 

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

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

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

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

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

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

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

F5 = «КД»

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

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

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

 

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

 

Подсчет клеток, которые содержат положительные и отрицательные числа Excel

Подсчет клеток, которые содержат положительные и отрицательные числа Excel

=СЧЁТЕСЛИ(диапазон;»>0 критерий»)

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

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

=СЧЁТЕСЛИ($C$5:$C$11;»>0″)

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

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

Вы можете легко изменить эту формулу для подсчета ячеек на основе других критериев. Например, посчитать все ячейки со значением меньше -20, используйте эту формулу:

=СЧЁТЕСЛИ($C$5:$C$11;»<=-20″)

 

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