Как посчитать количество ячеек со значением в Excel

Excel
Содержание
  1. Как посчитать количество ячеек в Excel
  2. Метод 1. Счетчик ячеек по строке состояния
  3. Метод 2. Подсчет ячеек при помощи функции СЧЕТЗ
  4. Метод 3. Функция СЧЕТ для подсчета ячеек
  5. Метод 4. Функция СЧЕТЕСЛИ
  6. Метод 5. Функция СЧЕТЕСЛИМН для подсчета ячеек
  7. Как посчитать количество ячеек с текстом внутри диапазона
  8. Как посчитать количество пустых и непустых ячеек?
  9. Нулевые строки.
  10. СЧЕТЕСЛИ с несколькими условиями.
  11. Количество чисел в диапазоне
  12. Количество ячеек с несколькими условиями ИЛИ.
  13. Использование СЧЕТЕСЛИ для подсчета дубликатов.
  14. 1. Ищем дубликаты в одном столбце
  15. 2. Сколько совпадений между двумя столбцами?
  16. 3. Сколько дубликатов и уникальных значений в строке?
  17. Часто задаваемые вопросы и проблемы.
  18. Подсчет ячеек в строках и столбцах
  19. Считаем числа и значения
  20. Ставим экселю условия
  21. Разбираемся в критериях
  22. Задачи на логику
  23. Средние значения и множественные формулы
  24. Подсчет Уникальных ТЕКСТовых значений в MS EXCEL
  25. Как найти повторяющиеся значения в Excel.
  26. Подсчитать количество повторяющихся значений в столбце
  27. Подсчет количества определенного значения ячейки в Excel при условии
  28. Процедура подсчета значений в столбце
  29. Способ 1: индикатор в строке состояния
  30. Способ 2: оператор СЧЁТЗ
  31. Способ 3: оператор СЧЁТ
  32. Способ 4: оператор СЧЁТЕСЛИ
  33. Особенности использования функции СЧЁТЕСЛИ в Excel

Как посчитать количество ячеек в Excel

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

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

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

Метод 1. Счетчик ячеек по строке состояния

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

Как подсчитать количество ячеек с текстом в Excel

Несмотря на то, что этот счетчик включен «заводскими» настройками, в некоторых ситуациях его может и не быть. Это происходит, если пользователь отключил его ранее. Для этого следует вызвать контекстное меню строки состояния и активировать пункт «Число». Индикатор снова появится после этих шагов. Как подсчитать количество ячеек с текстом в Excel

Метод 2. Подсчет ячеек при помощи функции СЧЕТЗ

Оператор COUNTA — это очень простой способ подсчета количества ячеек, в которых есть какие-то данные, если вам нужно записать окончательный результат в другую ячейку или использовать его в расчетах другим оператором. Преимущество использования функции в том, что нет необходимости возвращаться к количеству ячеек каждый раз, когда есть какая-то информация, если диапазон изменяется. Содержимое (значение, возвращаемое формулой) изменится автоматически. Как это сделать?

  1. Для начала нам нужно выделить ячейку, куда будет записано итоговое количество заполненных ячеек. Найдите кнопку «Вставить функцию» и нажмите. Как подсчитать количество ячеек с текстом в Excel
  2. После того, как мы выполнили вышеуказанные шаги, появится диалоговое окно, в котором мы должны выбрать нашу функцию. После выбора нажмите на кнопку «ОК». Как подсчитать количество ячеек с текстом в Excel
  3. Затем появится диалоговое окно для ввода аргументов. Они представляют собой ряд ячеек или прямые адреса тех ячеек, которые необходимо проанализировать на предмет заполнения и определить количество. Существует два способа установки диапазона: ручной и автоматический. Чтобы не ошибиться при указании адресов ячеек, лучше выделить соответствующую область после нажатия на поле ввода данных. Если ячейки, количество которых необходимо определить, являются внешними, их необходимо вводить отдельно, заполнять поля «Значение2», «Значение3» и так далее.
  4. Нажмите «ОК».

Как подсчитать количество ячеек с текстом в Excel

Также возможно установить эту функцию вручную. Структура функции: =СЧЁТ(значение1, значение2;…).

Как подсчитать количество ячеек с текстом в Excel

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

Как подсчитать количество ячеек с текстом в Excel

Читайте также: Формулы Эксель: Как создать формулу, Операторы и Функции в Excel

Метод 3. Функция СЧЕТ для подсчета ячеек

Есть еще один оператор, предназначенный для получения количества ячеек. Но отличие от предыдущего оператора в том, что он умеет вычислять только те ячейки, где есть числа. Как использовать эту функцию?

  1. Как и в случае с предыдущей формулой, выберите ячейку, в которую следует записать формулу, и включите мастер функций. Затем выберите «АККАУНТ» и подтвердите свои действия (щелкните левой кнопкой мыши по кнопке «ОК).Как подсчитать количество ячеек с текстом в Excel
  2. Затем появится окно для ввода аргументов. Они такие же, как и в предыдущем способе. Необходимо указать либо диапазон (можно несколько), либо ссылки на ячейки. Нажмите «ОК».Как подсчитать количество ячеек с текстом в Excel

Синтаксис аналогичен предыдущему. Поэтому, если вам нужно ввести его вручную, вам нужно написать следующую строку кода: = COUNT (значение1, значение2;…).

Как подсчитать количество ячеек с текстом в Excel

Затем в области, где написана формула, отобразится количество ячеек, где есть числа.

Как подсчитать количество ячеек с текстом в Excel

Метод 4. Функция СЧЕТЕСЛИ

С помощью этой функции пользователь может определить не только количество ячеек, в которых есть числовые данные, но и те, которые соответствуют определенному критерию. Например, если критерий >50, будут учитываться только те ячейки, где записано число больше пятидесяти. Вы можете указать и другие условия, в том числе логические. Последовательность действий в целом аналогична предыдущим двум способам, особенно на первых этапах. Вам нужно вызвать мастер функций, ввести аргументы:

  1. Область. Это набор ячеек, где будет производиться проверка и расчет.
  2. Критерий. Это условие, по которому будут проверяться ячейки в области.

Синтаксис для ручного ввода: =СЧЁТЕСЛИ(диапазон, критерии).

Как подсчитать количество ячеек с текстом в Excel

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

Метод 5. Функция СЧЕТЕСЛИМН для подсчета ячеек

Функция, аналогичная предыдущей, только обеспечивает контроль по нескольким критериям. Аргументы видны на этом экране.

Как подсчитать количество ячеек с текстом в Excel

Соответственно, для ручного ввода используется следующий синтаксис: =СЧЁТЕСЛИ(диапазон_условий1, условие1, диапазон_условий2, условие2;…).

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

Чтобы подсчитать общее количество ячеек с текстом внутри, установите функцию —ETEXT(диапазон подсчета) в качестве диапазона. Функция, в которую вставляется область, может быть любой из вышеперечисленных. Например, можно использовать функцию СЧЕТЧИК, где в качестве аргумента вместо площади вводим функцию, которая относится к этой площади. Таким образом, несложно определить количество ячеек, в которых есть текст. Еще проще подсчитать, сколько ячеек содержит значение.

Как посчитать количество пустых и непустых ячеек?

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

Непустой.

В некоторых учебниках COUNTIF вы можете найти инструкции для подсчета непустых ячеек, подобные этому:

СЧЁТЕСЛИ(диапазон;»*»)

Но дело в том, что приведенное выше выражение подсчитывает только ячейки, содержащие текстовые значения. А это значит, что те, которые содержат даты и числа, будут рассматриваться как пустые (игнорируемые) и не будут включены в итог!

Если вам нужно общее решение для подсчета всех непустых ячеек в заданном диапазоне, введите:

СЧЁТЕСЛИ(диапазон;»<>» & «»)

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

Также можно подсчитать непустые ячейки в диапазоне:

=СЧЁТ(E2:E22).

Пустой.

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

Считаем ячейки, не содержащие текста:

СЧЁТЕСЛИ(диапазон; «<>» и; «<>» & «*»)

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

Как подсчитать пустые ячейки (все типы значений):

=СЧЁТЕСЛИ(E2:E22;»»)

Есть, конечно, специальная функция для таких случаев

=СЧИТАТЬПУСТО(E2:E22)

Но не все знают о его существовании. Но теперь ты знаешь …

Нулевые строки.

Также помните, что COUNTIF и COUNTBLANK подсчитывают ячейки с пустыми строками, которые только на первый взгляд выглядят пустыми.

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

На самом деле существует такое понятие, как «строка нулевой длины» (или «нулевая строка»). Пустая строка возникает, когда программе нужно вставить значение, но вставлять нечего.

Проблемы начинаются, когда вы пытаетесь произвести с ним какие-то математические вычисления (вычитание, деление, умножение и так далее). Получите сообщение об ошибке #ЗНАЧ!. При этом функции СУММ и СЧЁТ их игнорируют, как будто там есть текст. Внешне его нет.

И самое интересное, что если указать на него мышкой и нажать Удалить (или вкладку Главная — Правка — Удалить содержимое) — то он действительно становится пустым, и формулы и другие функции Excel начинают с ним работать без каких-либо ошибок.

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

=СТРОКИ(E2:E22)*СТОЛБЦ(E2:E22)-СЧЁТЕСЛИ(E2:E22;»<>»&»»)

Где в ячейках могут появляться нулевые строки? Тут может быть несколько вариантов:

  1. Он там в первую очередь потому, что так настраивается загрузка и создание файлов в сторонней программе (типа 1С). В некоторых случаях такие выгрузки настраиваются таким образом, что пустых ячеек как таковых нет — они просто заполняются строкой нулевой длины.
  2. Была создана формула, результатом которой стал текст нулевой длины. Самый простой случай:

=ЕСЛИ(Е1=1.10;»»)

В результате, если в E1 будет записано что-то отличное от 1, программа вернет строку нулевой длины. А если мы потом заменим формулу значением (Специальная вставка — Значения), то получим нашу псевдопустую позицию.

Если вы проверяете какие-то условия с помощью функции ЕСЛИ и планируете в дальнейшем производить математические операции с результатами, то вместо « » лучше поставить 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: Файл — Параметры — Дополнительно — Показать нули в позициях, содержащих нулевые значения.

СЧЕТЕСЛИ с несколькими условиями.

На самом деле функция СЧЁТЕСЛИ в Excel не предназначена для подсчёта количества ячеек по множеству условий. В большинстве случаев я рекомендую использовать ее множественный аналог — функцию СЧЁТЕСЛИМН. Он предназначен только для подсчета количества ячеек, соответствующих двум или более условиям (логическое И). Однако некоторые проблемы можно решить, объединив две или более функций СЧЁТЕСЛИ в одно выражение.

Количество чисел в диапазоне

Одним из наиболее распространенных применений двухкритериальной функции СЧЁТЕСЛИ является определение количества чисел в определённом диапазоне, т е меньше X, но больше Y.

Например, вы можете использовать для вычисления ячеек в диапазоне B2:B9, где значение больше 5 и меньше или равно 15:

=СЧЁТЕСЛИ(B2:B11;»>5″)-СЧЁТЕСЛИ(B2:B11;»>15″)

Количество ячеек с несколькими условиями ИЛИ.

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

Давай сделаем это:

=СЧЁТЕСЛИ(A4:A13;»лимонад»)+СЧЁТЕСЛИ(A2:A11;»*сок»)

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

Как вы понимаете, здесь можно добавить больше условий.

Использование СЧЕТЕСЛИ для подсчета дубликатов.

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

1. Ищем дубликаты в одном столбце

Это простое выражение COUNTIF($A$2:$A$24,A2)>1 найдет все одинаковые записи в A2:A24.

И еще одна формула СЧЁТЕСЛИ(B2:B24,ИСТИНА) сообщит вам, сколько существует дубликатов:

Для более наглядного представления найденных совпадений я использовал условное форматирование значения ИСТИНА.

2. Сколько совпадений между двумя столбцами?

Сравните список2 со списком1. В столбце E мы последовательно берем каждое значение из списка2 и подсчитываем, сколько раз оно встречается в списке1. Если нет совпадений, это уникальное значение. На рисунке они выделены цветом с использованием условного форматирования.

Выражение =СЧЁТЕСЛИ($A$2:$A$24,C2) копируется вниз по столбцу E.

Аналогичный расчет можно делать и наоборот — брать значения из первого списка и искать дубликаты во втором.

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

=СУММПРОИЗВ((СЧЁТЕСЛИ(A2:A24;C2:C24)>0)*(C2:C24<>»»))

Подсчитаем количество уникальных значений в list2:

=СУММПРОИЗВ((СЧЁТЕСЛИ(A2:A24;C2:C24)=0)*(C2:C24<>»»))

Получаем 7 уникальных записей и 16 дубликатов, что видно на рисунке.

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

3. Сколько дубликатов и уникальных значений в строке?

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

Подсчитывает количество дубликатов:

=СУММПРОИЗВ((СЧЁТЕСЛИ(A2:K2;A2:K2)>1)*(A2:K2<>»»))

Видим, что 13 выпало 2 раза.

Подсчет уникальных значений:

=СУММПРОИЗВ((СЧЁТЕСЛИ(A2:K2;A2:K2)=1)*(A2:K2<>»»))

Часто задаваемые вопросы и проблемы.

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

  1. Можно ли считать в несмежном диапазоне ячеек?

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

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

Ошибка: =СЧЁТЕСЛИ(A2;B3;C4;»>0″)

Правильно: = СЧЁТЕСЛИ(A2;»>0″) + СЧЁТЕСЛИ(B3;»>0″) + СЧЁТЕСЛИ(C4;»>0″)

Альтернативный способ — использовать функцию ДВССЫЛ для создания диапазона несмежных ячеек. Например, оба приведенных ниже варианта дают тот же результат, что и на изображении:

=СУММ(СЧЁТЕСЛИ(ДВССЫЛ({«B2:B11″,»D2:D11″}),»=0»))

Или

=СЧЁТЕСЛИ($B2:$B11,0) + СЧЁТЕСЛИ($D2:$D11,0)

  1. Ампсанд и кавычки в формулах СЧЁТЕСЛИ

Q: Когда я должен использовать амперсанд?

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

Итак, вы можете следовать этим правилам:

  • Если вы используете число или ссылку на ячейку в точном соответствии, вам не нужны амперсанд или кавычки. Например:

= СЧЁТЕСЛИ(A1:A10;10) или = СЧЁТЕСЛИ(A1:A10;C1)

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

= СЧЁТЕСЛИ(A2:A10,»яблоко») или = СЧЁТЕСЛИ(A2:A10,»*») или = СЧЁТЕСЛИ(A2:A10,»>5″)

  • Если вашим критерием является выражение со ссылкой или другая функция Excel, вы должны использовать кавычки («»), чтобы начать текстовую строку, и амперсанд (&), чтобы конкатенировать (объединить) и закончить строку. Например:

= СЧЁТЕСЛИ(A2:A10;»>»&D2) или = СЧЁТЕСЛИ(A2:A10;»<=»&СЕГОДНЯ())

Если вы не уверены, нужен вам амперсанд или нет, попробуйте оба способа. В большинстве случаев амперсанд работает просто отлично.

Например, = СЧЁТЕСЛИ(C2: C8;»<=5″) и = СЧЁТЕСЛИ(C2: C8;»<=»&5) работают одинаково хорошо.

  1. Как считать клетки по цвету?

Вопрос: Как считать ячейки по цвету заливки или шрифта, а не по значениям?

Ответ: К сожалению, синтаксис функции не позволяет использовать форматы в качестве условия. Единственный возможный способ суммировать ячейки на основе цвета — использовать макрос или, точнее, определяемую пользователем функцию Excel VBA.

  1. Неправильное имя?

Проблема: я продолжаю получать #NAME? Как я могу это исправить?

Ответ: Вы, скорее всего, указали не тот район. Пожалуйста, проверьте пункт 1 выше.

  1. Формула не работает

Проблема: Моя формула не работает! Что я сделал не так?

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

Будьте очень осторожны с интервалами. Когда я создавал одну из формул для этой статьи, я уже был готов рвать на себе волосы, потому что правильный дизайн (я точно знал, что он правильный!) не работал. Как оказалось, проблема была очевидна… Например, посмотрите на это: =СЧЁТЕСЛИ(A4:A13;»Лимонад»). На первый взгляд ничего страшного, кроме лишнего пробела после открывающей цитаты. Программа проглотит все отлично без сообщения об ошибке, предупреждения или другой индикации. Но если вы действительно хотите посчитать предметы, содержащие слово «лимонад» и начальный пробел, вы будете очень разочарованы….

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

Подсчет ячеек в строках и столбцах

Есть два способа узнать количество секций. Первый дает возможность подсчитывать их построчно в выделенной области. Для этого введите формулу =НЕТРЯД(массив) в соответствующее поле. В этом случае будут учитываться все ячейки, а не только содержащие числа или текст.

Второй вариант — =ЧИСЛОСТОЛБЦА(массив) — работает так же, как и предыдущий, но вычисляет сумму разделов столбца.

Считаем числа и значения

Хочу рассказать вам о трех полезных вещах, которые помогут вам в работе с программой.

  1. Количество чисел в матрице можно вычислить по формуле СЧЁТ(значение1, значение2,…). Он учитывает только те элементы, которые включают числа. То есть, если в любом из них есть написанный текст, они будут пропущены, при этом учитываются даты и время. В этой ситуации нет необходимости расставлять параметры по порядку: например, можно написать =COUNT(A1:C3; B4:C7;…).
  2. Другая статистическая функция — СЧЕТ — подсчитает для вас непустые ячейки в диапазоне, то есть содержащие буквы, цифры, даты, время и даже логические значения ЛОЖЬ и ИСТИНА.
  3. Обратное действие выполняется по формуле, отображающей количество пустых секций — COUNTNULL(массив). Это касается только последовательных выборов.

Ставим экселю условия

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

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

Разбираемся в критериях

  • «>0» — учитываются ячейки с номерами от нуля и выше;
  • «Товар» — засчитываются разделы, содержащие это слово;
  • 15 — вы получаете сумму элементов с заданным числом.

Для большей наглядности приведу подробный пример.

Для подсчета ячеек в зоне от А1 до С2, значение которых больше указанного в ячейке А5, в строке формул нужно ввести =СЧЁТЕСЛИ(A1:C2;»>»&A5).

Задачи на логику

Хотите ввести логические параметры для Excel? Используйте подстановочные знаки * и ?. Первый будет обозначать любое количество произвольных символов, а второй — только один.

Например, вам нужно узнать, сколько ячеек в электронной таблице содержит буква Т без прописных и строчных букв. Установите комбинацию =СЧЁТЕСЛИ(A1:D6,»T*»). Другой пример: вы хотите узнать количество ячеек, содержащих только 3 символа (любых) в том же диапазоне. Затем пишем =СЧЁТЕСЛИ(A1:D6;«. »).

Средние значения и множественные формулы

В качестве условия можно задать даже формулу. Хотите знать, сколько у вас разделов с содержанием выше среднего в определенной области? Затем вы должны написать следующую комбинацию в строке формулы =СЧЁТЕСЛИ(A1:E4;»>»&СРЗНАЧ(A1:E4)).

Если вам нужно подсчитать количество заполненных ячеек с двумя или более параметрами, используйте функцию СЧЁТЕСЛИМН. Например, вы ищете разделы с данными больше 10, но меньше 70. Вы вводите =COUNT(A1:E4,»>10″,A1:E4;«

Подсчет Уникальных ТЕКСТовых значений в MS EXCEL

Например, рассмотрим следующую таблицу:

как-посчитать-количество-значений-в-столбце-excel-6-способов-подсчета-количества-значений-в-столбце-эксел

Цель: подсчитать количество уникальных текстовых индикаторов в A7:A15. Для этого мы используем формулу: =СУММПРОИЗВ((A7:A15<>»»)/ЧИСЛОСЛИ(A7:A15;A7:A15)).

Если в указанных координатах есть еще и числовые показатели, то они тоже будут рассчитаны. Чтобы не считать числа, используйте: =СУММПРОИЗВ(ЭТЕКСТ(A7:A15)/СЧЁТЕСЛИ(A7:A15;A7:A15)).

Как найти повторяющиеся значения в Excel.

Например, у нас есть следующая метка:

как-посчитать-количество-значений-в-столбце-excel-6-способов-подсчета-количества-значений-в-столбце-эксел

Чтобы подсчитать повторяющиеся значения, введите в G5 следующую формулу: =ЕСЛИ(СЧЁТЕСЛИ(A$5:A$10,A5)>1,СЧЁТЕСЛИ(A$5:A5,A5),1). Затем нужно скопировать эту формулу на весь столбец.

Подсчитать количество повторяющихся значений в столбце

Чтобы рассчитать необходимое количество дубликатов в столбце, сделайте следующее:

  1. Мы будем использовать ту же пластину из примера выше. Мы выделяем ячейки в столбце.
  2. Перейдите в окно форматирования.
  3. В поле «Выберите тип правила:» нажмите на пункт «Использовать формулу для определения форматируемых ячеек».
  4. В строке «Формула формата для определения отформатированных ячеек» вводим =СЧЁТЕСЛИ($A:$A,A5)>1.

как-посчитать-количество-значений-в-столбце-excel-6-способов-подсчета-количества-значений-в-столбце-эксел

  1. Прозрачный! Мы реализовали расчет одинаковых показателей в одной колонке, а также выделили повторяющуюся информацию другим цветом.

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

Рассмотрим этот момент на конкретном примере. Допустим, у нас есть следующая метка для работы:

как-посчитать-количество-значений-в-столбце-excel-6-способов-подсчета-количества-значений-в-столбце-эксел

Выберите диапазон E2:E5 и введите формулу: =ЧИСЛОСЛИ(B3:B19;D2:D5). B3:B19 — ячейки, содержащие баллы, а D2:D5 — ячейки, содержащие критерии подсчета количества попаданий. В итоге получаем вот такой результат:

как-посчитать-количество-значений-в-столбце-excel-6-способов-подсчета-количества-значений-в-столбце-эксел

Процедура подсчета значений в столбце

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

Способ 1: индикатор в строке состояния

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

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

Количество-значений-в-столбце-отображаемое-на-шлаке-состояния-в-Microsoft-Excel.png

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

Включение-индикатора-количества-на-штрих-состояния-в-Microsoft-Excel.png

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

Способ 2: оператор СЧЁТЗ

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

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

Всего у оператора может быть до 255 аргументов общей группы «Значение». Аргументы — это просто ссылки на ячейки или диапазон, в котором вы хотите подсчитать значения.

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

Переход-в-Мастер-функций-в-Microsoft-Excel-13.png

Вот почему мы вызвали функцию мастера. Перейдите на вкладку «Статистика» и выберите имя «ЧИСЛО». После этого нажимаем на кнопку «ОК» внизу этого окна.

Переход-в-окно-аргументов-функций-ЩЁТЗ-в-Microsoft-Excel.png

Переходим к окну аргументов функции COUNTA. Он содержит поля ввода для аргументов. Как и количество аргументов, их может быть до 255 единиц. Но для решения стоящей перед нами задачи достаточно одного поля «Value1». Ставим в него курсор, а после этого с нажатой левой кнопкой мыши выделяем на листе столбец, в котором должны быть рассчитаны значения. После того, как координаты столбца отобразятся в поле, нажмите кнопку «ОК» внизу окна аргумента.

Окно-аргументов-функции-ЩЁЦ-в-Microsoft-Excel.png

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

Результат-вычисления-функции-ЩЁЦ-в-Microsoft-Excel.png

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

Способ 3: оператор СЧЁТ

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

Как видите, аргументы COUNT и COUNTZ совершенно одинаковы и являются ссылками на ячейки или диапазоны. Разница в синтаксисе только в названии самого оператора.

  1. Выбираем элемент на листе, где должен отображаться результат. Нажимаем уже известную нам иконку «Вставить функцию».

Переход-в-Мастер-функций-в-программе-Microsoft-Excel-2.png

После запуска мастера функций снова заходим в категорию «Статистические». Затем выберите название «АККАУНТ» и нажмите кнопку «ОК».

Переход-в-окно-аргументов-функций-ЩЁТЬ-в-Microsoft-Excel.png

После запуска окна аргументов оператора COUNT его необходимо ввести в поле. В этом окне, как и в окне предыдущей функции, также может быть представлено до 255 полей, но, как и в прошлый раз, нам нужно только одно из них под названием «Value1». Вводим в это поле координаты столбца, которые нам нужны для выполнения операции. Делаем все так же, как делали эту процедуру для функции COUNT: ставим курсор в поле и выбираем столбец в таблице. После того, как адрес столбца был введен в поле, нажмите «ОК».

Окно-аргументов-функции-СЧЁТ-в-Microsoft-Excel.png

Результат немедленно появится в ячейке, которую мы определили как содержащую функцию. Как видите, программа учитывала только ячейки, содержащие числовые значения. Пустые ячейки и элементы, содержащие текстовые данные, в расчет не включались.

Результат-вычисления-функции-СЧЁТ-в-Microsoft-Excel.png

Способ 4: оператор СЧЁТЕСЛИ

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

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

Аргумент «диапазон» представлен как ссылка на определенный диапазон ячеек, а в нашем случае — на столбец.

Аргумент Criteria содержит указанное условие. Это может быть либо точное числовое или текстовое значение, либо значение, указанное с помощью символов больше (>), меньше () и т д

Подсчитаем, сколько ячеек с названием «Мясо» размещено в первом столбце таблицы.

  1. Выбираем элемент на листе, где будет производиться изготовление готовых данных. Нажмите на иконку «Вставить функцию».

Вставить-функцию-в-Microsoft-Excel-5.png

В мастере функций перейдите на вкладку «Статистика», выберите название СЧЁТЕСЛИ и нажмите кнопку «ОК».

Переход-в-окно-аргументов-функций-ЩЁТЕСЛИ-в-Microsoft-Excel.png

Окно COUNTIF Function Arguments включено. Как видите, в окне есть два поля, соответствующие аргументам функции.

В поле «Диапазон» точно так же, как мы уже не раз описывали выше, вводим координаты первого столбца таблицы.

В поле «Критерии» мы должны ввести условие подсчета. Вводим туда слово «Мясо».

После завершения вышеуказанных настроек нажмите кнопку «ОК».

Окно-аргументов-функции-ЩЁТЕСЛИ-v-Microsoft-Excel.png

Оператор производит расчеты и выводит результат на экран. Как видите, в выбранном столбце 63 ячейки содержат слово «Мясо».

Результат-вычисления-функции-СЧЁТЕСЛИ-в-Microsoft-Excel.png

Немного изменим задачу. Теперь подсчитаем количество клеток в этом же столбце, не содержащих слова «Мясо».

  1. Выбираем ячейку, в которой хотим отобразить результат, и ранее описанным способом вызываем окно аргумента оператора СЧЁТЕСЛИ.

В поле «Диапазон» введите координаты того же первого столбца в таблице, которая была обработана ранее.

В поле «Критерии» введите следующее выражение:

То есть этот критерий задает условие, что мы считаем все элементы, заполненные данными, не содержащими слова «Мясо». Знак «<>» означает «не равно» в Excel».

После указания этих параметров в окне аргументов нажмите кнопку «ОК».

Окно-аргументов-функции-ЩЁТЕСЛИ-в-программе-Microsoft-Excel.png

Результат немедленно отображается в предопределенной ячейке. Он сообщает, что в выделенном столбце есть 190 элементов данных, которые не содержат слова «Мясо».

Результат-вычисления-функции-ЩЁТЕСЛИ-в-программе-Microsoft-Excel.png

Теперь посчитаем все значения в третьем столбце этой таблицы, которые больше числа 150.

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

В поле «Диапазон» вводим координаты третьего столбца нашей таблицы.

Введите следующее условие в поле Критерии:

Это означает, что программа будет учитывать только те элементы в столбце, которые содержат числа больше 150.

Затем, как всегда, нажмите кнопку «ОК».

Подсчет-значений-больше-50-в-окне-аргументов-функций-СЧЁТЕСЛИ-в-Microsoft-Excel.png

После расчета Excel отображает результат в заранее обозначенной ячейке. Как видите, выбранный столбец содержит 82 значения больше 150.

Результат-подсчета-значений-больше-50-функций-СЧЁТЕСЛИ-v-Microsoft-Excel.png

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

Особенности использования функции СЧЁТЕСЛИ в Excel

Функция имеет следующую синтаксическую запись:

=СЧЁТЕСЛИ(площадь; критерии)

Описание аргументов:

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

Примечания:

  1. При подсчете количества вхождений в диапазоне согласно двум различным соотношениям диапазон ячеек можно рассматривать как набор, содержащий два или более непересекающихся подмножества. Например, в таблице «Мебель» нужно найти количество столов и стульев. Для вычислений мы используем выражение =ЧИСЛОСЛИ(B3:B200;»*стол*»)+ЧИСЛОСЛИ(B3:B200;»*стул*»).
  2. Если в качестве критерия указана текстовая строка, обратите внимание, что она не чувствительна к регистру. Например, функция СЧЁТЕСЛИ(A1:A2;»Петров») вернёт значение 2, если ячейки A1 и A2 содержат строки «Петров» и «Петров» соответственно.
  3. Если в качестве аргумента условия передается ссылка на пустую ячейку или пустая строка «», результатом вычисления для любого диапазона ячеек будет числовое значение 0 (ноль).
  4. Функцию можно использовать как формулу массива, если вы хотите вычислить количество ячеек с данными, удовлетворяющими одновременно нескольким критериям. Эта функция будет рассмотрена в одном из примеров.
  5. Оцениваемую функцию можно использовать для определения количества попаданий как по одному, так и по нескольким критериям поиска одновременно. В последнем случае используются две или более функции СЧЁТЕСЛИ, возвращаемые результаты которых складываются или вычитаются. Например, в ячейках A1:A10 хранится последовательность значений от 1 до 10. Чтобы подсчитать количество ячеек с числами больше 3 и меньше 8, выполните следующие действия:
  • написать первую функцию СЧЁТЕСЛИ с критерием «>3»;
  • написать вторую функцию с критерием «>=8»;
  • определить разницу между возвращенными значениями = СЧЁТЕСЛИ(A1:10,»>3″)-СЧЁТЕСЛИ(A1:A10,»>=8″). То есть вычесть из множества (3;+∞) подмножество </a>8;+∞).
Оцените статью
Блог о Microsoft Word