Polytech-soft.com

ПК журнал
5 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция бсчёта в excel

Функция БСЧЁТ() — Подсчет с множественными условиями в EXCEL

Функция БСЧЁТ() , английский вариант DCOUNT(), подсчитывает строки в таблице данных, которые удовлетворяют заданным условиям.

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

Иногда, функция БСЧЁТ() действительно удобна, особенно при использовании сложных критериев с подстановочными знаками или критериев на основе формул. Подробно эти задачи решаются в статьях Подсчет значений с множественными критериями ( Часть 2 , Часть 3 , Часть 4 ).

Подготовка исходной таблицы

Для использования этой функции требуется чтобы:

  • исходная таблица имела заголовки столбцов;
  • критерии были оформлены виде небольшой отдельной таблицы с заголовками;
  • заголовки таблицы критериев совпадали с заголовками исходной таблицы (если критерий не задается формулой).

Синтаксис функции БСЧЁТ()

БСЧЁТ(база_данных; поле; критерий)

Аргумент База_данных представляет собой таблицу (см. строки 8-13 на рисунке выше), по одному из столбцов которой производится подсчет значений. Верхняя строка таблицы должна содержать заголовки столбцов.

Поле — это название заголовка столбца, по которому производится подсчет. Аргумент Поле можно заполнить введя:

  • текст с заголовком столбца в двойных кавычках, например «Продавец» или «Продажи»,
  • число (без кавычек), задающее положение столбца в таблице (указанной в аргументе база_данных ): 1 — для первого столбца, 2 — для второго и т.д.
  • ссылку на заголовок столбца.

Подразумевается, что этот столбец содержит числа, инача функция вернет 0.

Примечание : Если значения в этом столбце содержат числа, сохраненные в текстовом формате , то нужно использовать функцию БСЧЁТА() .

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

Критерий — интервал ячеек, который содержит задаваемые условия (т.е. ссылка на таблицу критериев). См. строки 2-4 на рисунке выше. Этот интервал ячеек аналогичен таблице для Расширенного фильтра и функции БДСУММ() и, конечно, БСЧЁТА() .

Задачи

Предположим, что в диапазоне A 8:B13 имеется таблица продаж, содержащая поля (столбцы) Продавец и Продажи (см. рисунок выше и файл примера ) .

Задача 1 (с одним критерием)

Подсчитаем количество продаж на сумму >3000. Т.е. подсчет будем вести по столбцу Продажи .

  • Создадим табличку критериев (желательно над исходной таблицей, чтобы она не мешала добавлению новых данных в таблицу), например в диапазоне A2:A3 (см. рисунок выше). Табличка должна состоять из заголовка (совпадает с названием заголовка столбца исходной таблицы, к которому применяется критерий) и собственно критерия (условия отбора);
  • Условия отбора могут быть записаны в текстовом формате. В нашем случае, просто >3000
  • Теперь можно записать саму формулу =БСЧЁТ(B8:B13;B8;A2:A3) В B8:B13 находится База_данных (исходная таблица). Столбец А (Продавец) можно в данном случае не включать в базу_данных , т.к. по нему не производится подсчет и он не участвует в отборе. B8 – это ссылка на заголовок столбца по которому будет производиться подсчет ( Продажи ), в нем должны быть числовые значения. A2:A3 – ссылка на табличку критериев.

Альтернативное решение — простая формула = СЧЁТЕСЛИ(B9:B13;A3) .

Вывод : для простых однокритериальных задач огород с функцией =БСЧЁТ() городить не стоит.

Задача 2 (Два критерия (разные столбцы), условие И)

Подсчитаем Число продаж Белова, которые меньше 3000.

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

Записать саму формулу можно так =БСЧЁТ(A8:B13;B8;A2:B3)

Альтернативное решение — простая формула = СЧЁТЕСЛИМН(A9:A13;»Белов»;B9:B13;A3) .

Задача 3 (Два критерия (один столбец), условие И)

Подсчитаем число продаж в интервале >3000 и =БСЧЁТ(B8:B13;B8;D2:E3)

Альтернативное решение простая формула =СЧЁТЕСЛИМН(B9:B13;»>3000″;B9:B13;»

Задача 4 (Два критерия (один столбец), условие ИЛИ)

Подсчитаем число продаж 6000.

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

Записать саму формулу можно так =БСЧЁТ(B8:B13;B8;A2:A4)

Альтернативное решение формула =СЧЁТЕСЛИ(B9:B13;» 6000″)

Задача 5 (Два критерия (разные столбцы), условие ИЛИ)

Подсчитаем продажи Белова и продажи, любого продавца, которые >6000 (также исключим возможное дублирование: продажи Белова, которые >6000).

Читать еще:  Скрытая видеозапись с веб камеры

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

Записать саму формулу можно так =БСЧЁТ(A8:B13;B8;H2:I4)

Альтернативное решение формула =СЧЁТЕСЛИ(A9:A13;»Белов»)+СЧЁТЕСЛИ(B9:B13;»>6000″)-СЧЁТЕСЛИМН(B9:B13;»>6000″;A9:A13;»Белов»)

Задача 6 (Условия отбора, созданные в результате применения формулы)

Подсчитаем количество продаж с величиной выше среднего.

В качестве условия отбора можно использовать значение, вычисляемое при помощи формулы. Формула должна возвращать результат ИСТИНА или ЛОЖЬ.

Для этого введем в ячейку F 3 формулу =B9>СРЗНАЧ($B$9:$B$13) , а в F 2 вместо заголовка введем, произвольный поясняющий текст, например, « Больше среднего » (заголовок не должен повторять заголовки исходной таблицы).

Обратите внимание на то, что диапазон нахождения среднего значения введен с использованием абсолютных ссылок ( $B$9:$B$13 ), а среднее значение сравнивается с первым значением диапазона, ссылка на который задана относительной адресацией ( B9 ). Это необходимо, поскольку при вычислении функции БСЧЁТ() EXCEL увидит, что B9 — это относительная ссылка, и будет перемещаться вниз по диапазону по одной записи за раз и возвращать значение либо ИСТИНА, либо ЛОЖЬ (больше среднего или нет). Если будет возвращено значение ИСТИНА, то соответствующая строка таблицы будет учтена при подсчете. Если возвращено значение ЛОЖЬ, то строка учтена не будет.

Записать формулу можно так =БСЧЁТ(B8:B13;B8;F2:F3)

Альтернативное решение формула = СЧЁТЕСЛИ(B9:B13;»>»&СРЗНАЧ($B$9:$B$13))

Задача 7 (3 критерия)

Подсчитаем Число продаж Белова, которые выше среднего, и все продажи Батурина.

Табличка с критериями в этом случае выглядит так:

Записать формулу можно так =БСЧЁТ(A8:B13;B8;F2:G4)

Альтернативное решение формула = СЧЁТЕСЛИМН(B9:B13;»>»&СРЗНАЧ($B$9:$B$13);A9:A13;»Белов»)+СЧЁТЕСЛИ(A9:A13;»Батурин»)

Функции БСЧЁТ и БСЧЁТА для счета с несколькими условиями в Excel

Функция БСЧЁТ в Excel предназначена для подсчета числа ячеек с числовыми значениями в указанном столбце базы данных или списка, соответствующих установленному критерию, и возвращает соответствующее числовое значение.

Функция БСЧЁТА в Excel используется для определения количества ячеек с любыми значениями в указанном столбце базы данных либо списка, которые соответствуют определенному в качестве соответствующего аргумента условию, и возвращает число таких ячеек.

Примеры использования функций БСЧЁТ и БСЧЁТА в Excel

Пример 1. В списке хранятся данные о количестве часов, проработанных сотрудниками предприятия 29.08.2018. Определить число работников, которые работали сверх нормы (свыше 8 часов).

Вид исходной таблицы вместе с таблицей критериев:

Для поиска числа работников, проработавших более 8 часов, используем формулу:

  • A10:B18 – диапазон ячеек, в котором находится исходная таблица (база данных);
  • B10 – ячейка, в которой хранится наименование столбца (поля);
  • B1:B2 – диапазон ячеек с критериями поиска.

Теперь при изменении числового значения в ячейке B2 автоматически обновляется число работников в ячейке C2 – соответственно.

Поиск неточного совпадения текста в Excel

Пример 2. Определить количество работников, фамилия которых начинается на «Соло». Продемонстрировать различие в использовании функции БСЧЁТ и БСЧЁТА для подсчета количества ячеек, содержащих текст.

Используем таблицу из первого примера. Создадим таблицу условий:

Используем функцию БСЧЁТА:

В результате расчетов получим:

Используем для подсчета количества работников функцию БСЧЁТ:

В результате вычислений функция вернет следующее значение:

Такое поведение обусловлено тем, что функция БСЧЁТ предназначена для работы только с числовыми значениями. При использовании ее для подсчета количества ячеек с текстом или иными нечисловыми типами данных будет возвращен некорректный результат.

Примеры счета с несколькими условиями в Excel

Пример 3. В базе данных хранятся записи о массе отгруженных фруктов в различные страны, а также общей стоимости отправленного товара. Определить, сколько раз проводилась отгрузка яблок в Украину общей массой свыше 2000 кг, стоимость которых превышает среднюю стоимость яблок в таблице.

Вид таблицы данных:

Таблица критериев имеет следующий вид:

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

В данном случае выполняется сравнение значения, хранящегося в ячейке D11 со средним значением стоимости отгруженных яблок, возвращаемым функцией СРЗНАЧЕСЛИ. Функция БСЧЁТА выполнит проверку этого выражения для каждого значения в столбце D:D, соответствующего установленным критериям (страна – Украина, фрукты – яблоки, масса отгрузки – свыше 2000 кг).

Для расчетов используем следующую формулу:

Второй аргумент не указан, поскольку поиск выполняется по всей таблице. Результат вычислений:

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

Рассматриваемые функции имеют схожий синтаксис:

Читать еще:  Разница в таблице excel

=БСЧЁТ( база_данных; поле; условия )

=БСЧЁТА( база_данных; поле; условия )

Описание аргументов функций:

  • база_данных – обязательный для заполнения аргумент, принимающий данные в виде ссылки на диапазон ячеек, в которых находится база данных либо список;
  • поле – необязательный аргумент, принимающий текстовые данные, которые характеризуют наименование столбца (поля) для поиска. Может быть представлен в виде числа, соответствующего порядковому номеру столбца относительно первого. Для использования функции БСЧЁТ требуется, чтобы указанное поле содержало данные числового типа. Для функции БСЧЁТА это условие обязательным не является;
  • условия – обязательный аргумент, принимающий ссылку на диапазон ячеек, в которых содержатся критерии поиска.
  1. В качестве условия должна быть передана ссылка на диапазон из как минимум двух ячеек, в одной из которых содержится название поля базы данных, а во второй – критерий поиска.
  2. Таблицу с условиями не следует располагать под базой данных или списком, в которых выполняется поиск, поскольку они могут со временем пополняться новыми записями.
  3. Если требуется выполнить подсчет количества ячеек без какого-либо условия, можно создать таблицу с названием требуемого поля базы данных, а в качестве аргумента условия передать ссылку на ячейку с названием и расположенной под ней пустой ячейкой.
  4. База данных в Excel является списком связанных данных, содержащий поля (столбцы) и записи (строки). Наличие пустых ячеек или неименованных полей в базе данных не допускается.
  5. Столбец с непустыми ячейками в базе данных называется полем, поэтому один из аргументов рассматриваемых функций имеет название «поле». Фактически требуется передать название столбца таблицы или списка, под которым расположены ячейки для выполнения поиска.
  6. Аргумент поле обеих функций может быть не указан. В этом случае поиск будет произведен во всех полях базы данных или списка.
  7. Основным отличием функции БСЧЁТ от функции БСЧЁТА является то, что первая предназначена для работы только с ячейками, содержащими числовые значения. Вторая функция может выполнять поиск и подсчет количества ячеек, соответствующих установленному критерию, которые содержат данные других типов.

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

Добрый день уважаемый пользователь!

В этой статье я хочу продолжить хорошую традицию по изучении функций и героем моей статьи станет одна из полезных статистических функций – функция СЧЕТ в Excel. Эта функция позволит вам подсчитать какое-то количество значений: числовых, логических и других в нужном вам диапазоне аргументов. В Excel также есть много походных функций от этой функции, это и СЧЕТЕСЛИ, и СЧЕТЗ, и СЧЕТЕСЛИМН, которые расширяют возможности материнской функции, а также дают ей дополнительные преимущества. Но рассмотрим мы основу, которая и определяет функциональность всей линейки. Самым традиционным и распространённым способом, когда применяется эта функция, это когда нужно подсчитать в Excel количество ячеек, которые содержат числа. Вот пример, в котором рассматриваемая функция подсчитает и вернет то количество ячеек, в которых присутствуют числа в диапазоне «A1:C3»: А теперь давайте более подробно рассмотрим синтаксис, который использует функция СЧЕТ в Excel:

= СЧЕТ(значение№1; [ значение№2 ]; …), где:

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

В разных версиях Excel, функциональность, которую имеет функция СЧЕТ, отличается. В версиях Excel 2003 и более ранних функция работала только с 30 аргументами, а вот начиная с версии Excel 2007 и новее, количество используемых аргументов достигает значения 255, каждый из них может содержать в себе большое количество данных.

Когда используется функция СЧЕТ, необходимо знать нюансы применения функции, от этого зависит успешность и эффективность вашей формулы:

  • В случаях, когда аргументом функции выступает массив или ссылка, то происходит учёт только числовых значений. Тексты, логические значения, пустые ячейки, значение ошибок, которые возникают в ссылках и массивах, будут игнорироваться;
  • Также функция СЧЁТ учитывает текстовые отображение чисел и логические значения, которые присутствовали в вашем списке аргументов;
  • Если возникает нужда произвести подсчёт логических значений, значений разнообразных ошибок или текстовые элементы, то наиболее удачным вариантом будет использование функции СЧЁТЗ;
  • В случаях, когда аргументы, которые невозможно преобразовать в числовые значения, это текст или значения ошибок, функция будет их пропускать;
  • Рассматриваемая функция будет учитывать только те аргументы, которые являются числами, датой или текстовым отображением чисел (к примеру, это может быть число, которое заключили в кавычки «1»);
  • В случае необходимости подсчёта числа по определённому критерию, то лучше всего подойдет функция СЧЁТЕСЛИМН или СЧЁТЕСЛИ;
  • Для подсчёта количества непустых ячеек в вашем диапазоне подойдет функция СЧЁТЗ;
  • Когда вам нужно пересчитать, сколько пустых ячеек есть в таблицах Excel, рекомендую поработать с функцией СЧИТАТЬПУСТОТЫ.
Читать еще:  Выравнивание по ширине в excel

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

  • Дата, время и числа, функция СЧЁТ определяет, как числовые;
  • Логические значения и текстовые отображение чисел, функция будет учитывать по-разному в зависимости от того, где хранится значение: в ячейке, в массиве или прямо было введено в список ваших аргументов;
  • Функция СЧЕТ игнорирует ошибки, текст, который не в состоянии быть преобразованным в числа и пустые ячейки.

А теперь давайте на более интересном примере рассмотрим, как функция СЧЁТ в Excel считает количество ячеек, которые содержат разнообразные числовые значения. Красным цветом я выделил те значения, которые учитываются формулой в примере. Ну вот, как работает функция СЧЕТ в Excel, я вам поведал. Очень надеюсь, что вы почерпнули для себя полезную информацию, которая помогла вам в достижении поставленной задачи. Жду от вас комментарии, делитесь информацией с друзьями в социальных сетях, пусть нас будет больше. Если вам интересны другие функции Excel, жду вас в «Справочнике функций».

До новых встреч на страницах TopExcel.ru!

Деньги не сделают вас счастливее. У меня сейчас 50 миллионов, и я так же счастлив, как и тогда, когда у меня было 48 миллионов.
Арнольд Шварценеггер

Excel: Функция СЧЕТ

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

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

Значение1 – обязательный аргумент, все остальные аргументы являются необязательными и могут быть опущены.

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

В более ранних версиях Excel (например, Excel 2003 года), функция Счет обрабатывала 30 аргументов. Начиная с версии Excel 2007, можно использовать до 255 аргументов.

Числа, даты и время учитываются функцией Счет как числовые.

Ссылки на пустые ячейки, ошибки, текстовые или логические значения игнорируются.

Если число сохранено как текст, оно не будет посчитано!

Примеры использования функции Счет.

  1. Подсчет количества ячеек в диапазоне, который полностью состоит из чисел:

  1. В диапазоне есть пустые ячейки, ячейки содержащие числа и текст.

Функция Счет игнорирует пустые ячейки, и ячейки содержащие текст.

  1. Ячейки, содержащие значения даты и времени функция Счет учитывает:

  1. Функция Счет может подсчитывать количество ячеек сразу в нескольких несмежных диапазонах:

II .Чтобы посчитать количество непустых ячеек в диапазоне, воспользуйтесь функцией Счётз.

Функция Счётз

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

Функция Счётз учитывает данные любого типа, включая ошибки.

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

Например, в ячейке С2 — пустая строка, т.е. С2=««. Функция Счётз учтет эту ячейку при подсчете.

III . Если требуется посчитать количество пустых ячеек в Excel, используйте функцию Считатьпустоты .

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

Ячейки, содержащие пустые строки (=««), засчитываются как ПУСТЫЕ.

Например, в ячейке С2 — пустая строка, т.е. С2=««. Функция Считатьпустоты учтет эту ячейку при подсчете.

Понравилось? Поделись с друзьями

Семинары. Вебинары. Конференции

Актуальные темы. Лучшие лекторы Москвы и РФ. Сертификаты ИПБР. Более 30 тематик в месяц.

Ссылка на основную публикацию
Adblock
detector