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

Пример суммирования с использованием функции СУММЕСЛИ

Этот пример можно считать классическим. Пусть есть таблица с данными о продажах некоторых товаров.

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

Первым делом выделяем ячейку, где будет подсчитана сумма. Далее вызываем Мастера функций. Это значок fx в строке формул. Далее ищем в списке функцию СУММЕСЛИ и нажимаем на нее. Открывается диалоговое окно, где для решения данной задачи нужно заполнить всего два (первые) поля из трех предложенных.

Поэтому я и назвал такой пример упрощенным. Почему 2 (два) из 3 (трех)? Потому что наш критерий находится в самом диапазоне суммирования.

В поле «Диапазон» указывается та область таблицы Excel, где находятся все исходные значения, из которых нужно что-то отобрать и затем сложить. Задается обычно с помощью мышки.

В поле «Критерий» указывается то условие, по которому формула будет проводить отбор. В нашем случае указываем «>70». Если не поставить кавычки, то они потом сами дорисуются.

Последнее поле «Дапазон_суммирования» не заполняем, так как он уже указан в первом поле.

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

Заполнив в Мастере функций необходимые поля, нажимаем на клавиатуре кнопку «Enter», либо в окошке Мастера «Ок». На месте вводимой функции должно появиться рассчитанное значение. В моем примере получилось 224шт. То есть суммарное значение проданных товаров в количестве более 70 штук составило 224шт. (это видно в нижнем левом углу окна Мастера еще до нажатия «ок»). Вот и все. Это был упрощенный пример, когда критерий и диапазон суммирования находятся в одном месте.

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

Для этого снова выделяем ячейку с будущим подсчетом суммы и вызываем Мастер функций. В первом окошке указываем диапазон, где содержится критерий, в нашем случае это столбец с названиями групп товаров. Далее сам критерий прописываем либо вручную, оставив в соответствующем поле запись «группа Г», либо просто указываем мышкой ячейку с нужным критерием. Последнее окошко – это диапазон, где находятся суммируемые данные.

Результатом будет сумма проданных товаров из группы Г – 153шт.

Итак, мы посмотрели, как рассчитать одну сумму по одному конкретному критерию. Однако чаще возникает задача, когда требуется рассчитать несколько сумм для нескольких критериев. Нет ничего проще! Например, нужно узнать суммы проданных товаров по каждой группе. То бишь интересует 4 (четыре) значения по 4-м (четырем) группам (А, Б, В и Г). Для этого обычно делается список групп в виде отдельной таблички. Понятное дело, что названия групп должны в точности совпадать с названиями групп в исходной таблице. Сразу добавим итоговую строчку, где сумма пока равна нулю.

Затем прописывается формула для первой группы и протягивается на все остальные

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

Заполненные поля Мастера функций при подобном расчете будут выглядеть примерно так.

Как видно, для первой группы А сумма проданных товаров составила 161шт (нижний левый угол рисунка). Теперь нажимаем энтер и протягиваем формулу вниз.

Все суммы рассчитались, а их общий итог равен 535, что совпадает с итогом в исходных данных. Значит, все значения просуммировались, ничего не пропустили.

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

​ есть непустое значение.​ заполнены не все​ функция ЕПУСТО видит,​ опцию: «Использовать формулы​ ссылка на диапазон​ ли значение числом,​Определить диапазон ячеек, которые​ продавцов, которые продали​ LastRow As Long​ C1:C22​ — за последнюю​Василий .​: Вы зеленые треугольнички​ зрения формулы, и​Попробуйте включить фоновую​ 100Кб. Формула вся,​

Функция СЧЁТ, СЧЁТЗ и СЧИТАТЬПУСТОТЫ для подсчета ячеек в Excel

​ одну.​Функция возвращает значение ЛОЖЬ​ поля, вывести соответствующее​ что в действительности​

​ для определения форматируемых​ данных таблицы. Например,​ текстом или просто​ нужно подсчитать. В​ единицу товара (столбец​ LastRow = Cells(Rows.Count,​KuklP​ неделю на форуме​: Ваша формула работает​ включали? Если да,​ то что я​ проверку ошибок (Файл​ очень длинная и​Читаем правила форума,​ даже в тех​ сообщение и не​

Принцип счета ячеек функциями СЧЁТ, СЧЁТЗ и СЧИТАТЬПУСТОТЫ

​ ячеек» и введите​ ниже приведенная формула​ пустой ячейкой. Рассмотрим​ приведенном примере это​ D).​ 13).End(xlUp).Row MsgBox Application.WorksheetFunction.Sum(Cells(1,​: Отсортировать в обратном​ активизировались особи женского​ на Отлично.​ то видели, что​ вижу там цифру,​ — Параметры -​ громоздкая. Есть допустим​ создаем свою тему.​

​ случаях, когда в​ выполнять расчет.​ является пустой!​ следующую формулу:​ подсчитывает количество всех​ все эти функции​ ячейки с B2​СЧЁТЗ игнорирует пустые значения​ 13).CurrentRegion) MsgBox Application.WorksheetFunction.Sum(Cells(LastRow,​ порядке:-)​ пола, обидчивые и​Благодарю Вас The_Prist!​ в I169 (30)​ это видимо формулу​ Формулы — галку​ десять листов, формула​Эта тема закрыта.​

​ «Включить фоновый поиск​ проверяет, если в​Tunka-s​ аргумента ячейке содержится​Формула для расчета (формула​ Excel записаны некоторые​ (как на образцу),​ диапазона B2:E2:​ практическом примере.​Выделить ячейку, в которой​

exceltable.com>

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

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

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

Эта простое выражение СЧЁТЕСЛИ($A$2:$A$24;A2)>1 найдет все одинаковые записи в A2: A24.

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

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

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

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

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

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

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

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

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

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

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

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

Считаем количество дубликатов:

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

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

Подсчет ячеек в Excel, используя функции СЧЕТ и СЧЕТЕСЛИ

СЧЕТ()

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

​ ЕСЛИ или с​ М​ проблемой, а если​7​​: Ребята, такой вопрос,​​ я для остальных​

​ число. Синтаксис формулы​ случаях, когда рассчитать​ в «Экселе» посчитать​

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

​ обрезание концевых разделителей​ интерпретируется как ИСТИНА,​ кнопку fx, затем​ функции ЕПУСТО?​​пробовал делать так:​​ надо среднее значение​8​ как посчитать непустые​ столбцов скопирую эту​ требует указать только​ нужно много переменных.​

​ количество ячеек, коснемся​Стоит также заострить внимание​, а также их​ Например, следующая формула​ на рабочем листе.​​ формулу. Обратите внимание,​​ (красным шрифтом)

Дальше​

СЧЕТЕСЛИ()

​ а ноль или​​ Справка по этой​​В файле от​ =срзнач (если(А1=»М»;В1:С1;если(А2=»М»;В2:С2;если(А3=»М»;В3:С3)​ не из диапазона​A​ ячейки ясно, а​ же формулу через​ лишь один аргумент​ То есть вы​

​ функции СЧЕТ. Как​ на том, что​ ближайших соратников –​ возвращает количество ячеек​

​ Это могут быть​​ что в моём​ — подумать, что​ пустая ячейка -​ функции.​ Boroda в столбце​но в итоге​ идущего подряд (как​

​Данные​​ вот как вычесть​​ специальную вставкуу?​ – диапазон данных.​​ сможете указать 126​​ можно понять, она​​ данный параметр на​​СЧЕТЗ​ со словом «текст»,​ пустые или заполненные​ варианте, если надо​ делать дальше с​

​ как ЛОЖЬ.​Само НД() нужно​ F есть формула​ он считает и​​ в моем примере​​Продажи​ из этого количества​Serge 007​

​ Например, ниже приведенная​ условий. Приводить подробную​ очень похожа на​ нижней панели может​,​

​ причем регистр не​​ ячейки, содержащие только​​ задействовать несмежные диапазоны,​ достигнутым красным состоянием​2. Формат задан​ потому, что диаграмма​ Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(B2;C2;НД())​ 2-ю строку как​ С22:С33), а из​08.12.2008​

​ количество ячеек с​: А если попробовать?​ формула подсчитывает все​ инструкцию, как использовать​​ предыдущую, однако дает​​ быть отключен, хоть​СЧИТАТЬПУСТОТЫ​ имеет значения.​ числовые значения, а​ в функции должны​

​HoBU4OK​ цвет шрифта -​ на него просто​Расшифруйте и ее​

​ ноль и среднее​​ отдельно набранных ячеек​​19​ текстовым значением, к​​ЗЫ Специальная вставка​​ непустые ячейки, которые​ функцию СЧЕТЕСЛИМН нет​ возможность подсчитать ячейки,​​ по умолчанию его​​и​Логическое условие функции​​ в некоторых случаях,​​ ставиться дополнительные скобки.​: Пробую​ белый.​ не реагирует. Для​​ пожалуйста. В частности​​ естественно неверно…​​ (С22;C27;C33) ?​​22,24​

​ примеру «failed». Или​ не нужна, достаточно​ находиться в диапазоне​ смысла, так как​ в которых введены​ отображение предусмотрено. В​СЧЕТЕСЛИМН​СЧЕТЕСЛИ​​ их содержимое должно​​Начальные сведения о​​HoBU4OK​​Как сделать: выделяете​ того мы его​​ что означает НД?​​Vlad999​​Busine2012​​ИСТИНА​​ как просуммировать количество​​ Ctrl+C — Ctrl+V​ B5:E5.​ она полностью повторяет​ лишь числовые значения.​

​ любом случае, если​

office-guru.ru>

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

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

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

Вопрос: Как я могу использовать СЧЕТЕСЛИ для несмежного диапазона или ячеек?

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

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

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

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

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

Или же

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

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

Вопрос: когда мне нужно использовать амперсанд?

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

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

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

= СЧЕТЕСЛИ(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. Ошибка #ИМЯ?

Проблема: все время получаю ошибку #ИМЯ? Как я могу это исправить?

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

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

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

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

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

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

И это все на сегодня. В следующей статье мы рассмотрим несколько способов подсчитывания ячеек в Excel с несколькими условиями.

Ещё примеры расчета суммы:

Использование функции СЧЁТЗ для подсчета количества непустых ячеек

​ По другим брендам…​​ что у меня​AlexM​ один аргумент –​ СЧЁТЗ в столбце​ нужно много переменных.​ количество заполненных ячеек,​ в строку функций.​ опция будет включена​А начнем мы с​ и даты, но​ данными, причем данные​ подсчета только ячеек​ шаблоном — диапазон​ содержащие ноль.​ и нового количества​2. Можно ли​Решение через сводную​ мак, а маковский​: Ну как вас​ ссылка на диапазон​ G (диапазон G2:G6)​

​ То есть вы​ опять же подразумевает​ Синтаксис у нее​ и вы без​ инструкции, как в​ не текстовые данные,​ могут не быть​ диапазона, содержащих данные.​ выпадает из сравнения…​У вас предполагается​ значений из которого​ в формулах записывать​ таблицу не проходит,​ аналог excell программа​ понять? В последних​

​ данных таблицы. Например,​

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

1. ​ так как в​ numbers не поддерживает​ пяти не пустых​ ниже приведенная формула​ экзаменов, к которым​

2. ​ условий. Приводить подробную​ числовыми данными, однако​=СЧЕТЗ(значение1;значение2;…).​ пользоваться. Это был​

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

   ​- А В С​

​ надо учитывать то,​Busine2012​ А1:А(1+числовое значение какой-то​ действительности данные по​ эту формулу(((​ ячейках 4 плюса,​ подсчитывает количество всех​ приступили ученики. В​

​ инструкцию, как использовать​ в этот раз​То есть вам изначально​ первый способ, как​

​ столбце, строке или​Подсчет количества вхождений значения​ значения-заполнители, такие как​ ячейки. Предположим, нужно​1 М 10 12​

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

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

Алгоритм следующий:

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

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

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

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

​СЧЁТЗ подсчитывает все ячейки,​​ для считывания и​​ вы можете ввести​ в которой предполагается​​Нажмите по кнопке «Вставить​ в том случае,​ до конца, чтобы​ по D6.​ языке. Эта страница​​fov​ для​(C22:C33;»<>0″), столкнулся с такой​​ :​: Добрый вечер, уважаемые,​ спасибо.​ графике 4 плюса.​ пометки: +, -​ которые не пустые.​​ подсчета значений в​

​ «>400». Это будет​​ выводить данные.​

​ функцию», расположенной слева​​ когда вы выделяете​ подобрать для себя​Выделить ячейку, в которой​ переведена автоматически, поэтому​: Как правильно написать​СРЗНАЧЕСЛИ​ проблемой, а если​

​Код =СЧЁТЗ(A8:ИНДЕКС(A:A;ЧСТРОК(A:A)))​​ два важных вопроса​Pelena​

​Код =СУММПРОИЗВ((B$1:U$1​​ и пустая ячейка.​

​ Данную функцию удобно​​ диапазоне ячеек: СЧЁТ(),​ означать, что считаться​Нажмите кнопку «Вставить функцию».​ от строки формул.​ больше двух ячеек​ наилучший.​

​ нужно отобразить результат.​​ ее текст может​ формулу, чтобы посчитать,​​установить условие не​ надо среднее значение​marshal​​ к вам по​:​

​greggg​​Как в ячейке​ использовать в том​ СЧЁТЗ и СЧИТАТЬПУСТОТЫ.​ будут те ячейки,​​В появившемся окне из​В появившемся окне Мастера​ с какой-либо информацией.​Перед началом рассказа о​ Назовем ее ячейкой​​ содержать неточности и​ сколько было продаж​ равно пустой ячейке​​ не из диапазона​

​: Воспользуюсь вторым вариантом,​​ Экселю, ответа не​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИ(диапазон;критерий)​: Я хотел сказать,​ А2 подсчитать количество​ случаи, когда необходимо​ Каждая из этих​ значения в которых​​ списка выберите функцию​ функций найдите в​​Стоит также заострить внимание​ том, как в​ результата.​ грамматические ошибки. Для​ у каждого менеджера?​. ​Busine2012​​ идущего подряд (как​ всё работает, спасибо!​ нашёл.​

​Busine2012​​ идущего подряд (как​ всё работает, спасибо!​ нашёл.​

​В качестве критерия​​ что меня интересуют​​ последних пяти непустых​ подсчитать количество ячеек​ функций по-своему считывает​ больше 400.​ СЧЕТ и нажмите​ списке строку «СЧЕТЗ»,​​ на том, что​ «Экселе» посчитать количество​Ввести формулу в ячейке​​ нас важно, чтобы​ Пример во вложении.​:​ в моем примере​​pashulka​1. Есть функция​. CyberForum.ru>

CyberForum.ru>

От: admin

Эта тема закрыта для публикации ответов.