В различных случаях может потребоваться подсчет количества ячеек в Excel. В этой статье мы рассмотрим, как посчитать блоки с определенными значениями, пустые и находятся ли они в заданных условиях. Для этого мы будем использовать следующие функции: COUNT, COUNTIF, COUNTIFS, COUNTNUT.
Заполненных
Сначала рассмотрим самый простой способ. Если вам нужно подсчитать количество заполненных блоков в Excel, выделите в документе диапазон ячеек (есть несколько способов, о них читайте, перейдя по ссылке) и в «Строке состояния» обратите внимание на пункт «Сумма «. Там будет написано количество непустых блоков: все с текстом и цифрами учитывается, а пустые нет.
Если вам нужно посчитать блоки в таблице, заполненной определенными значениями, и использовать это число в формулах для расчетов, то этот способ не подойдет, так как данные в таблице могут периодически меняться. Поэтому перейдем к рассмотрению функций.
Где введены числа
Функция COUNT: подсчет блоков, заполненных только числовыми значениями. Выберите H1, поставьте «=», напишите функцию «COUNT». Укажите желаемый диапазон (F1:G10) в качестве аргумента функции. Если диапазонов несколько, разделите их знаком «;». – (F1:G10;B3:C8) .
Всего 20 блоков. Тот, в котором написан текст, не учитывался, а засчитывались заполненные датой и временем.
С определенным текстом или значением
Функция СЧЁТЕСЛИ: позволяет подсчитать количество блоков, соответствующих заданным критериям. В качестве аргумента прописывается диапазон: B2:B13, и до «;» указан критерий — «> 5» .
Например, есть таблица, которая показывает, сколько килограммов того или иного продукта было продано за день. Подсчитаем, сколько было продано товаров весом более 5 килограммов. Для этого нужно посчитать, сколько блоков находится в столбце «Вес», где значение больше пяти. Функция будет выглядеть так: =СЧЁТЕСЛИ(B2:B13;»>5″). Он подсчитает количество блоков с контентом больше пяти.
Чтобы распространить функцию на другие блоки и, например, изменить условия, необходимо зафиксировать выбранный диапазон. Вы можете сделать это, используя абсолютные ссылки в Excel.
Функция также может вычислять:
— количество ячеек с отрицательными значениями: =СЧЁТЕСЛИ(B2:B13;»»&A10) ;
— ячейки со значением больше 0: =СЧЁТЕСЛИ(B2:B13;»>0″) ;
– непустые блоки выбранного диапазона: =СЧЁТЕСЛИ(B2:B13;»») .
Вы также можете использовать функцию СЧЁТЕСЛИ для вычисления ячеек в Excel, содержащих текст. Например, посчитаем, сколько фруктов на столе. Выделим область и в качестве критерия укажем «фрукты». Все блоки с заданным словом будут засчитаны. Вы не можете набирать текст, просто выберите прямоугольник, который его содержит, например C2 .
Для формулы СЧЁТЕСЛИ в любом случае будут подсчитаны ячейки, содержащие текст «Фрукты» и «фрукты».
Также в качестве критериев можно использовать специальные символы: «*» и «?». Они применимы только к тексту.
Посчитаем, сколько товаров начинается на букву А: «А*». При указании «абрикос*» будут учитываться все продукты, начинающиеся на «абрикос»: абрикосовый сок, абрикосовый джем, абрикосовый жмых.
Символ «?» Вы можете заменить любую букву в слове. При вводе критерия «фуукт» — будут учитываться слова фрукт, фуукт, фуукт.
Для подсчета слов в ячейках, состоящих из определенного количества букв, поставьте знаки вопроса в ряд. Для подсчета товаров с 5 буквами в названии установим «. » .
Если вы установите звездочку в качестве критерия, все блоки, содержащие текст, будут учитываться из выбранного диапазона.
С несколькими критериями
Функция СЧЁТЕСЛИМН используется, когда нужно указать несколько условий, максимальное её количество в Excel — 126. В качестве аргумента: установить первый диапазон значений и указать условие, через «;» установить второй диапазон и написать для него условие: =СЧЁТЕСЛИМН(B2:B13;»>5″;C2:C13;»фрукты») .
В первом диапазоне мы указали, что вес больше пяти килограммов, во втором выбрали, чтобы это были фрукты.
Пустых блоков
Функция СЧИТАТЬПУСТОТЫ подсчитает количество пустых ячеек в заданном диапазоне.
В Excel не так уж сложно подсчитать количество ячеек, содержащих текст или числовые значения. Используйте для этого специальные функции и задавайте условия. С ее помощью можно считать как пустые блоки, так и те, на которых написаны определенные слова или буквы.
Как выполнить подсчёт ячеек со значением
Здравствуйте дорогие читатели.
Прежде чем приступить к этой теме, хотелось бы рассказать вам об отличном образовательном продукте по теме Excel, под названием «Неизвестный Excel», там все четко и качественно изложено. Рекомендую.
Ну а теперь вернемся к теме.
Вероятно, вы представитель одной из профессий, в которых Excel незаменим. Ведь он позволяет производить расчеты, составлять списки, таблицы и графики, заполнять журналы и решать множество других задач, связанных с числами.
Однако не все, кто работает с этим приложением, знают весь его функционал и умеют его реализовать. Ты один из них? Тогда вы пришли в правильное место. В частности, сегодня мы обсудим, как посчитать количество ячеек со значением в Excel. Есть несколько способов сделать это. Они зависят от типа контента, который вам нужно рассказать. Разберем самые популярные из них.
Самый быстрый способ
Самый простой, но в то же время поверхностный способ подсчета элементов в строке состояния. Ваш номер отображается в нижней панели открытого окна.
Если вы хотите установить некоторые простые параметры для счетчиков, откройте настройки строки состояния. Это можно сделать, щелкнув по нему правой кнопкой мыши. В появившемся окне обратите внимание на часть, где написано «Среднее», «Количество», «Количество чисел», «Минимум», «Максимум» и «Сумма».
Выберите нужный вариант и узнайте больше о том, что содержит ваша таблица.
Подсчет ячеек в строках и столбцах
Есть два способа узнать количество секций. Первый позволяет считать их построчно в выбранном диапазоне. Для этого введите формулу =НЕТРЯД(массив) в соответствующее поле. В этом случае будут учитываться все ячейки, а не только содержащие числа или текст.
Второй вариант — =NUMBERCOLUMN(массив) — работает аналогично предыдущему, но вычисляет сумму разделов в столбце.
Считаем числа и значения
Я расскажу вам о трех полезных вещах, которые помогут вам в работе с программой.
- Вы можете подсчитать, сколько чисел содержится в массиве, используя формулу СЧЁТ(значение1, значение2,…). Учитываются только те элементы, в состав которых входят числа, то есть если в каком-либо из них написан текст, то они пропускаются, при этом учитываются даты и время. В этой ситуации не нужно выставлять параметры по порядку: можно написать, например, =СЧЁТ(A1:C3;B4:C7;…).
- Другая статистическая функция COUNT подсчитает для вас непустые ячейки в диапазоне, то есть те, которые содержат буквы, цифры, даты, время и даже логические значения FALSE и TRUE.
- Обратное действие выполняется по формуле, возвращающей количество пустых секций — COUNTNULL(массив). Применяется только к непрерывному выбору.
Ставим экселю условия
Когда необходимо посчитать элементы с определенным значением, то есть соответствующим какому-то формату, используется функция СЧЁТЕСЛИ (матрица, критерий). Чтобы было понятнее, нужно разобраться в терминах.
Массив — это диапазон элементов, между которыми ведется счет. Это может быть только непрерывный прямоугольный набор соседних ячеек. Критерием является именно то условие, по которому производится отбор. Если он содержит текст или числа со знаками сравнения, мы заключаем его в кавычки. Когда условие оценивается только как число, кавычки не нужны.
Разбираемся в критериях
- «>0» — учитываются ячейки с номерами от нуля и выше;
- «Товар» — засчитываются разделы, содержащие это слово;
- 15 — вы получаете сумму элементов с заданным номером.
Для наглядности приведу подробный пример.
Для подсчета ячеек в области от A1 до C2, значение которых больше указанного в ячейке A5, в строке формул необходимо ввести =СЧЁТЕСЛИ(A1:C2;»>»&A5).
Задачи на логику
Вы хотите установить логические параметры для Excel? Используйте подстановочные знаки * и ?. Первый будет обозначать любое количество произвольных символов, а второй — только один.
Например, вам нужно знать, сколько у вас ячеек в электронной таблице с буквой Т без учета регистра. Установите соединение =СЧЁТЕСЛИ(A1:D6,»T*»). Другой пример: вы хотите узнать количество ячеек, содержащих только 3 символа (любых) в одном диапазоне. Затем мы пишем =СЧЁТЕСЛИ(A1:D6;«. »).
Средние значения и множественные формулы
Вы даже можете установить формулу в качестве условия. Хотите узнать, сколько у вас разделов с содержанием выше среднего в определенном диапазоне? Затем вам нужно ввести следующую комбинацию в строке формул =СЧЁТЕСЛИ(A1:E4;»>»&СРЗНАЧ(A1:E4)).
Если вам нужно подсчитать количество заполненных ячеек по двум и более параметрам, используйте функцию СЧЁТЕСЛИМН. Например, найдите разделы с данными больше 10, но меньше 70. Введите =СЧЁТЕСЛИ(A1:E4,»>10″,A1:E4;«
2016 – 2019 «Блог Дмитрия Васиярова”
Перепечатка, использование материалов с этого сайта разрешены только с согласия автора.
Способы подсчета количества ячеек в диапазоне с данными
В Excel есть несколько функций, позволяющих подсчитать количество пустых ячеек или ячеек с данными определенного типа в диапазоне.
Щелкните ячейку, в которой вы хотите отобразить результат.
На вкладке «Формулы» нажмите «Дополнительные функции», наведите указатель мыши на «Статистика» и выберите одну из следующих функций:
COUNT: подсчитывает количество непустых ячеек.
COUNT: подсчитывает количество ячеек, содержащих числа.
COUNTBLANK — подсчитывает количество пустых ячеек.
СЧЁТЕСЛИ: подсчитывает ячейки, соответствующие заданным условиям.
Совет: Чтобы ввести несколько условий, используйте функцию СЧЁТЕСЛИ.
Выберите диапазон ячеек и нажмите клавишу RETURN.
Щелкните ячейку, в которой вы хотите отобразить результат.
На вкладке «Формулы» нажмите «Вставить», выберите «Статистика» и выберите одну из следующих функций:
COUNT: подсчитывает количество непустых ячеек.
COUNT: подсчитывает количество ячеек, содержащих числа.
COUNTBLANK — подсчитывает количество пустых ячеек.
СЧЁТЕСЛИ: подсчитывает ячейки, соответствующие заданным условиям.
Совет: Чтобы ввести несколько условий, используйте функцию СЧЁТЕСЛИ.
Выберите диапазон ячеек и нажмите клавишу RETURN.
Подсчет заполненных ячеек в Microsoft Excel
При выполнении определенных задач при работе с таблицей может возникнуть необходимость подсчета ячеек, заполненных данными. Excel предоставляет такую возможность с помощью встроенных инструментов. Давайте узнаем, как выполнить такую процедуру в этой программе.
Подсчет ячеек
В Excel количество заполненных ячеек можно посмотреть с помощью счетчика в строке состояния или с помощью различных функций, каждая из которых подсчитывает элементы, заполненные определенным типом данных.
Способ 1: счетчик на строке состояния
Самый простой способ подсчета ячеек, содержащих данные, — использовать информацию счетчика, которая находится в правой части строки состояния, слева от кнопок режима просмотра в Excel. Всякий раз, когда на листе выбран диапазон, где все элементы пусты или только один элемент содержит значение, этот индикатор будет скрыт. Счетчик автоматически появляется при выделении двух и более непустых ячеек, и сразу же отображает их количество после слова «Count».
Но, хотя по умолчанию этот счетчик включен и только ждет, пока пользователь выберет определенные элементы, в некоторых случаях его можно отключить вручную. Тогда вопрос об их включении становится актуальным. Для этого щелкните правой кнопкой мыши на строке состояния и в открывшемся списке установите флажок напротив пункта «Сумма». После этого счетчик снова будет отображаться.
Способ 2: функция СЧЁТЗ
Вы можете подсчитать количество заполненных ячеек, используя функцию COUNT. Отличается от предыдущего способа тем, что позволяет зафиксировать в отдельной ячейке счет определенного диапазона. То есть для просмотра информации о нем не нужно будет постоянно выбирать район.
- Выбираем область, в которой будет отображаться результат расчета. Щелкните значок «Вставить функцию».
Откроется окно Мастера функций. Ищем элемент «COUNT» в представленном списке. После выбора этого имени нажмите кнопку «ОК».
Откроется окно аргументов. Аргументами этой функции являются ссылки на ячейки. Ссылку на диапазон можно набрать вручную, но лучше поставить курсор в поле «Значение1», куда нужно ввести данные, и выделить соответствующую область на листе. Если вам необходимо посчитать заполненные ячейки в нескольких удаленных диапазонах, то координаты второго, третьего и последующих диапазонов необходимо ввести в поля с именами «Значение2», «Значение3» и т.д. Когда все данные введены. Нажмите кнопку «ОК».
- После ввода формулы программа в заранее выбранной области отображает результат подсчета заполненных ячеек заданного диапазона.
Способ 3: функция СЧЁТ
Также для подсчета заполненных ячеек в Excel тоже есть функция подсчета. В отличие от приведенной выше формулы, здесь учитываются только ячейки, заполненные числовыми данными.
- Как и в предыдущем случае, выберите ячейку, в которой будут отображаться данные, и таким же образом запустите мастер функций. В нем выбираем оператора с названием «АККАУНТ». Нажмите кнопку «ОК».
Откроется окно аргументов. Аргументы те же, что и для предыдущего метода. По своей функции они являются связями с клетками. Вставляем координаты диапазонов в лист, где нужно подсчитать количество ячеек, заполненных числовыми данными. Нажмите кнопку «ОК».
Чтобы вручную ввести формулу, мы придерживаемся следующего синтаксиса:
- После этого в области, в которой находится формула, отобразится количество ячеек, заполненных числовыми данными.
Способ 4: функция СЧЁТЕСЛИ
Эта функция позволяет подсчитывать не только количество ячеек, заполненных числовыми выражениями, а только те, которые соответствуют определенному условию. Например, если установить условие «> 50», будут учитываться только те ячейки, которые содержат значение больше числа 50. Также можно установить значения «» (не равно) и т д
- После того, как вы выбрали ячейку для отображения результата и запустили мастер функций, выберите запись «СЧЁТЕСЛИ». Нажмите кнопку «ОК».
Откроется окно аргументов. У этой функции два аргумента: диапазон, в котором подсчитываются клетки, и критерий, то есть условие, о котором мы говорили ранее. В поле «Диапазон» введите координаты обрабатываемой области, а в поле «Критерии» введите условия. После этого нажмите кнопку «ОК».
Для ручного ввода шаблон выглядит так:
- После этого программа подсчитывает заполненные ячейки выделенного диапазона, соответствующие заданному условию, и отображает их в области, указанной в первом абзаце этого метода.
Способ 5: функция СЧЁТЕСЛИМН
Оператор СЧЁТЕСЛИМН является расширенной версией функции СЧЁТЕСЛИ. Он используется, когда необходимо указать более одного условия соответствия для разных диапазонов. Всего можно указать до 126 условий.
- Назначаем ячейку, в которой будет отображаться результат, и запускаем Мастер функций. Ищем в нем элемент «СЧЁТЕСЛИМН». Выберите его и нажмите кнопку «ОК».
Откроется окно аргументов. На самом деле аргументы функции те же, что и выше: «Диапазон» и «Условие». Разница лишь в том, что диапазонов и соответствующих им условий может быть много. Вводим адреса диапазонов и соответствующие им условия, после чего нажимаем кнопку «ОК».
Синтаксис этой функции:
- После этого приложение подсчитывает заполненные ячейки из указанных диапазонов, отвечающих заданным условиям. Результат отображается в заранее отмеченной области.
Как видите, простейший подсчет количества заполненных ячеек в выделенном диапазоне можно увидеть в строке состояния Excel. Если вам необходимо вывести результат в отдельную область на листе, а уж тем более произвести расчет с учетом определенных условий, то на помощь в этом случае придут специализированные функции.
Подробнее: Как включить автосохранение в Excel
Спасибо автору, поделитесь статьей в социальных сетях.
Функция СЧЁТЕСЛИ и подсчет количества значения ячейки в Excel
Функция СЧЕТЕСЛИ в Excel используется для подсчета количества ячеек в рассматриваемом диапазоне, содержащих данные, соответствующие критериям, переданным в качестве второго аргумента этой функции, и возвращает соответствующее числовое значение.
Функцию СЧЁТЕСЛИ можно использовать для анализа числовых значений, текстовых строк, дат и других типов данных. С его помощью можно определить количество неповторяющихся значений в диапазоне ячеек, а также количество ячеек с данными, которые лишь частично соответствуют заданным критериям. Например, электронная таблица Excel содержит столбец с именами клиентов. Чтобы определить количество клиентов с таким же именем, как Иванов, вы можете ввести функцию =СЧЁТЕСЛИ(A1:A300;»*Иванов*»). Символ «*» обозначает любое количество символов до и после подстроки «Иванов».
Примеры использования функции СЧЁТЕСЛИ в Excel
Пример 1. Электронная таблица Excel содержит данные о ежедневных продажах магазина бытовой техники. Определите, какую часть продаваемой продукции составляет оборудование Samsung.
Просмотр исходной таблицы данных:
Для расчета используем формулу:
- C3:C17 — ряд ячеек, содержащих названия компаний продаваемого оборудования;
- «Samsung» — критерии поиска (точное совпадение);
- A17 — ячейка, в которой хранится номер последней продажи, соответствующий общему количеству продаж.
Процентная доля проданной техники Samsung составляет 40%.
Подсчет количества определенного значения ячейки в Excel при условии
Пример 2. По результатам сданных экзаменов необходимо составить таблицу, содержащую данные о количестве учащихся, сдавших предмет на 5, 4, 3 балла соответственно, а также не сдавших предмет.
Вид исходной таблицы:
Сначала выберите ячейки E2: E5, введите следующую формулу:
- B3:B19 — диапазон ячеек с отметками для проверки;
- D2:D5 — диапазон ячеек, содержащих критерии для подсчета количества совпадений.
В итоге получаем таблицу:
Статистический анализ посещаемости с помощью функции СЧЁТЕСЛИ в Excel
Пример 3. В таблице Excel хранятся данные о просмотрах страниц сайта пользователями за день. Определите количество пользователей сайта в день, а также сколько раз в день пользователи с логинами по умолчанию и user_1 посещали сайт.
Вид исходной таблицы:
Так как у каждого пользователя есть свой уникальный идентификатор в базе данных (Id), давайте посчитаем количество посетителей сайта в день по следующей формуле массива и нажмем комбинацию клавиш Ctrl+Shift+Enter для его подсчета:
Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив дробных чисел 1/число_вхождений, например, для пользователя с псевдонимом sam это значение равно 0,25 (4 вхождения). Общая сумма этих значений, вычисленная функцией СУММ, соответствует количеству уникальных вхождений, то есть количеству пользователей на сайте. Полученное значение:
Чтобы определить количество просмотров страниц по умолчанию для пользователей и user_1, пишем формулу:
В результате расчета получаем:
Особенности использования функции СЧЁТЕСЛИ в Excel
Функция имеет следующую синтаксическую запись:
=СЧЁТЕСЛИ(диапазон, критерии)
- диапазон — обязательный аргумент, который ссылается на одну или несколько ячеек, в которых вы хотите определить количество совпадений с указанными критериями.
- критерий: условие, по которому производится подсчет количества совпадений в рассматриваемом диапазоне. Условие может быть логическим выражением, числовым значением, текстовой строкой, значением типа Дата, ссылкой на ячейку.
- Подсчитав количество вхождений в диапазоне при двух разных условиях, диапазон ячеек можно рассматривать как набор, содержащий два или более непересекающихся подмножества. Например, в таблице «Мебель» нужно найти количество столов и стульев. Для вычислений мы используем выражение =СЧЁТЕСЛИ(B3:B200;»*стол*»)+СЧЁТЕСЛИ(B3:B200;»*стул*»).
- Если в качестве критерия указана текстовая строка, обратите внимание, что регистр не важен. Например, функция СЧЁТЕСЛИ(A1:A2; «Петров») вернёт значение 2, если ячейки A1 и A2 содержат строки «Петров» и «Петров» соответственно.
- Если в качестве аргумента критерия передается ссылка на пустую ячейку или пустая строка «», результатом вычисления для любого диапазона ячеек будет числовое значение 0 (ноль).
- Функцию можно использовать как формулу массива, если вы хотите вычислить количество ячеек с данными, которые удовлетворяют сразу нескольким критериям. Эта особенность будет рассмотрена в одном из примеров.
- Рассматриваемую функцию можно использовать для определения количества совпадений как по одному, так и по нескольким критериям поиска сразу. В последнем случае используются две или более функции СЧЁТЕСЛИ, возвращаемые результаты которых складываются или вычитаются. Например, в ячейках A1:A10 хранится последовательность значений от 1 до 10. Чтобы подсчитать количество ячеек с числами больше 3 и меньше 8, выполните следующие действия:
- написать первую функцию СЧЁТЕСЛИ с критерием «>3»;
- написать вторую функцию с критерием «>=8»;
- определить разницу между возвращаемыми значениями =СЧЁТЕСЛИ(A1:10;»>3″)-СЧЁТЕСЛИ(A1:A10;»>=8″). То есть вычесть из множества (3;+∞) подмножество [8;+∞).
