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

Подсчитайте ячейки, если они не пустые, с помощью функции COUNTA

В Google Sheets есть функция COUNTA, которая подсчитывает все непустые ячейки в наборе данных (т. Е. Все ячейки, содержащие любую текстовую строку или число).

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

Вы можете использовать приведенную ниже формулу COUNTA для подсчета всех непустых ячеек в наборе данных:

= СЧЁТ (A2: A13)

Хотя формула COUNTA дает правильный результат, в этом случае она может дать вам неправильный результат, если есть:

• Космический персонаж
• Нулевая / пустая строка (= ””).
• Апостроф (‘)

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

Ниже приведен пример, в котором у меня есть пустая строка (= ””) в ячейке A4 и апостроф в ячейке A8. Вы можете видеть, что результат формулы COUNTA — 11 (хотя только девять ячеек заполнены именами — или, по крайней мере, так кажется невооруженным глазом).

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

Подсчет различных чисел.

Чтобы посчитать различные числовые значения (числа, даты и время), используйте функцию ЕЧИСЛО:

Считаем, сколько имеется различных чисел в диапазоне A2: A10:

Результат вы можете посмотреть ниже.

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

И вот еще один способ подсчета чисел:

Применительно к примеру ниже:

=СУММ(—(ЧАСТОТА(A2:A10; A2:A10)>0))

Как видите, здесь игнорируются записи, в которых имеются буквы.

Пошагово разберём, как это работает.

Функция ЧАСТОТА возвращает массив цифр, которые соответствуют интервалам, заданным имеющимися числами. В этом случае мы сравниваем один и тот же набор чисел для массива данных и для массива интервалов.

Результатом является то, что ЧАСТОТА() возвращает массив, который представляет собой счетчик для каждого числового значения в массиве данных.

Это работает, потому что ЧАСТОТА() возвращает ноль для любых чисел, которые ранее уже появились в списке. Ноль возвращается и для текстовых данных. Поэтому полученный массив выглядит следующим образом: 

{3:0:0:2:0:0}

Как видите, обрабатываются только числа. Ячейки A7:A10 игнорируются, потому что там текст. А функция ЧАСТОТА() работает только с числами.

Теперь каждое из этих чисел проверяем на условие «больше нуля».

Получаем:

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

Теперь превращаем ИСТИНА и ЛОЖЬ в 1 и 0 соответственно. Делаем это при помощи двойного отрицания. Проще говоря, это двойной минус, который не меняет величину числа, но позволяет получить реальные числа, когда это вообще возможно:

{1:0:0:1:0:0}

А теперь функция СУММ складывает всё и получаем результат: 2.

Формулы для подсчета пустых ячеек.

Функция СЧИТАТЬПУСТОТЫ.

Функция СЧИТАТЬПУСТОТЫ предназначена для подсчета пустых ячеек в указанном диапазоне. Она относится к категории статистических функций и доступна во всех версиях Excel начиная с 2007.

Синтаксис этой функции очень прост и требует только одного аргумента:

СЧИТАТЬПУСТОТЫ(диапазон)

Где диапазон — это область вашего рабочего листа, в которой должны подсчитываться позиции без данных.

Вот пример формулы в самой простейшей форме:

Чтобы эффективно использовать эту функцию, важно понимать, что именно она подсчитывает

1. Содержимое в виде текста, чисел, дат, логических значений или ошибок, не учитывается.
2. Нули также не учитываются, даже если скрыты форматированием.
3. Формулы, возвращающие пустые значения («»), — учитываются.

Глядя на рисунок выше, обратите внимание, что A7, содержащая формулу, возвращающую пустое значение, подсчитывается по-разному:

• СЧИТАТЬПУСТОТЫ считает её пустой, потому что она визуально кажется таковой.
• СЧЁТЗ  обрабатывает её как имеющую содержимое, потому что она фактически содержит формулу.

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

Как вы видите на рисунке выше, для подсчета непустых ячеек отлично подходит функция СЧЁТЗ:

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

Применяем СЧЁТЕСЛИ или СЧЁТЕСЛИМН.

Другим способом подсчета пустых ячеек в Excel является использование функций  СЧЁТЕСЛИ или СЧЁТЕСЛИМН  с пустой строкой («») в качестве критериев.

В нашем случае формулы выглядят следующим образом:

или

Возвращаясь к ранее сказанному, вы можете также использовать выражение

Результаты всех трёх формул, представленных выше, будут совершенно одинаковыми. Поэтому какую из них использовать — дело ваших личных предпочтений.

Подсчёт пустых ячеек с условием.

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

Например, чтобы определить количество позиций, в которых записано «Бананы» в столбце A и ничего не заполнено в столбце C, используйте эту формулу:

Или введите условие в предопределенную позицию, скажем F1, что будет гораздо правильнее:

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

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

Подсчитайте количество ячеек с ненулевыми значениями по формуле

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

1. Выберите пустую ячейку для отображения результата подсчета (здесь я выбираю ячейку H1). Скопируйте и вставьте формулу = СЧЁТЕСЛИ (A1: E8; «<> 0») в Панель формул, а затем нажмите Enter ключ. Смотрите скриншот:

2. Затем подсчитывается общее количество ненулевых значений выбранного диапазона и отображается в ячейке H1.

Базовые ноты:

1. С помощью приведенной выше формулы вы можете подсчитать ненулевые ячейки, включая пустые. Если вы хотите игнорировать как нулевые, так и пустые ячейки, примените эту формулу: =COUNTA(A1:D10)-COUNTIF(A1:D10,»=0″), Затем нажмите Enter ключ для получения результата, см. снимок экрана:

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

Подсчитайте количество ячеек с ненулевыми значениями с помощью Kutools for Excel

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

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Выберите диапазон, в котором вы хотите подсчитать количество ненулевых ячеек. Затем нажмите Kutools > Выберите > Выбрать определенные ячейки. Смотрите скриншот:

2. Во всплывающем Выбрать определенные ячейки диалоговое окно, выберите Ячейка в Тип выбора раздел укажите Не равно в Конкретный тип раскрывающийся список и введите номер в пустое поле. Наконец нажмите кнопку OK кнопка. Смотрите скриншот:

3. Затем появится диалоговое окно, в котором указано, сколько ячеек с ненулевыми значениями существует в выбранном диапазоне. И он уже напрямую исключил пустые ячейки. Щелкните значок OK кнопки, эти ячейки выбираются немедленно.

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

Подсчет действительно пустых ячеек.

Во всех предыдущих примерах мы подсчитывали позиции, включая те, которые кажутся пустыми, но на самом деле содержат пустоту («»), возвращаемую некоторыми формулами. Если вы хотите исключить их из результата подсчета, вы можете использовать эту общую формулу:

ЧСТРОК( диапазон ) * ЧИСЛСТОЛБ( диапазон ) — СЧЁТЗ( диапазон )

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

Например, чтобы определить количество абсолютно пустых клеток в диапазоне A2: A8, используйте следующую формулу:

На скриншоте ниже показан результат:

Как видите, псевдо-пустая ячейка с формулой здесь не учитывается.

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

Не стесняйтесь делиться любыми другими подсказками, которые могут у вас возникнуть. Будьте счастливы и преуспевайте в Excel!

Также рекомендуем:

5
1
голос

Рейтинг статьи

Как подсчитать пустые строки в Excel.

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

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

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

Вместо жесткого указания количества столбцов вы можете использовать функцию ЧИСЛСТОЛБ (COLUMNS в английской версии) для его автоматического вычисления:

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

Разберём, как это работает:

• Сначала вы проверяете весь диапазон на наличие непустых ячеек с помощью выражения типа A2:D10 <> «», а затем приводите возвращаемые логические значения ИСТИНА и ЛОЖЬ к 1 и 0 с помощью двойного отрицания (—). Результатом этой операции является двумерный массив единиц (означают непустые ячейки) и нулей (пустые).
• При помощи СТРОКА создаётся вертикальный массив числовых ненулевых значений, в котором количество элементов равно количеству столбцов диапазона. В нашем случае диапазон состоит из 4 столбцов (A2:В10), поэтому мы получаем такой массив: {1; 2; 3; 4}
• Функция МУМНОЖ вычисляет матричное произведение вышеупомянутых массивов и выдает результат вида: {7; 10; 6; 0; 5; 6; 0; 5; 6}. В этом массиве для нас имеет значение только нулевые значения, указывающие на строки, в которых все клетки пусты.
• Наконец, вы сравниваете каждый элемент полученного выше массива с нулем, приводите ИСТИНА и ЛОЖЬ к 1 и 0, а затем суммируете элементы этого последнего массива: {0; 0; 0; 1; 0; 0; 1; 0; 0}. Помня, что 1 соответствуют пустым строкам, вы получите желаемый результат.

Если приведенная выше формула кажется вам слишком сложной для понимания, возможно, вам больше понравится эта:

Здесь вы используете функцию СЧЁТЕСЛИ, чтобы узнать, сколько значений содержится в каждой строке, а ДВССЫЛ «подает» строки в СЧЁТЕСЛИ одну за другой. Результатом этой операции является массив вида {3; 4; 3; 0; 2; 3; 0; 2; 3}. Проверка на 0 преобразует указанный выше массив в {0; 0; 0; 1; 0; 0; 1; 0; 0}, где единицы представляют пустые строки. Вам остается просто сложить эти цифры.

Обратите также внимание, что это формула массива

На скриншоте выше вы можете увидеть результат работы этих двух формул.

Также следуем отметить важную особенность работы этих выражений с псевдо-пустыми ячейками. Добавим в C5

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

Внешне таблица никак не изменится, поскольку эта формула возвращает пустоту. Однако, второй вариант подсчёта обнаружит её присутствие. Ведь если что-то записано, значит, ячейка уже не пустая. По этой причине результат количества пустых строк будет изменён с 2 на 1.

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

СЧЕТ()

​ ячеек с данными.​​ диапазон или несколько​​ она отлично подойдет,​ рассказано ниже. Во-вторых,​. Если же требуется​ ячеек рабочего листа​ в Excel требуется​ функцией.​ «СЧЕТ»).​ возможности. Рассмотрим специальные​Если функция СЧЕТЕСЛИ ссылается​

​Найдем число партий товара​ полезна для подсчета​ решении проблемы первым​«Главная»​​ область, включающую несколько​​ конкретный столбец, а​

​ Как можно отметить,​ диапазонов ячеек.​ когда количество заполненных​

​ можно использовать специальные​​ подсчитать количество с​​ Excel с применением​ подсчитать количество ячеек​=СЧЁТЕСЛИ (ДИАПАЗОН КОТОРЫЙ​

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

​ ячеек нужно отобразить​ функции. С их​ условием​ различного вида условий.​ на рабочем листе.​​ НАДО ПРОСМОТРЕТЬ; КАКОЕ​​ в Excel​

СЧЕТЕСЛИ()

​ в Excel и​​ другой книге, то​​ не менее минимального и​ в других случаях. ​ число и будет​ блоке инструментов​ будет считать исключительно​Ctrl​ Если вы не​ «ОК».​

​ непосредственно в какой-то​ помощью можно осуществить​ИЛИ​ Например, приведенная ниже​

​ Это могут быть​​ ЧИСЛО ИЩЕМ)​Формула нашла количество значений​ примеры с двумя​ необходимо, чтобы эта​ не более максимального​Разберем подробнее применение функции​ указывать на численность​

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

​ условиями.​ книга была открыта.​ (Условие И -​ СУММПРОИЗВ():​​ строк, которые удовлетворяют​​жмем на кнопку​Пользователям, у которых небольшой​ ячейкам, в тех​

​ этом, тогда лучше​ действий в выделенной​ вы сможете наблюдать​ подсчета. Обо всем​ функций​

​ ячеек, содержащих отрицательные​​ ячейки, содержащие только​​: а если вместо​ При большом числе​Посчитаем, сколько ячеек содержат​Аргумент «Критерий» нужно заключать​ строка таблицы соответствует​Результатом вычисления A2:A13=D2 является​ конкретному условию.​«Условное форматирование»​

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

​ строк (больше тысячи)​ текст «столы» и​ в кавычки (кроме​ критерию, когда ее​

​ массив {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} Значение​​Урок:​​. Выбираем пункт​ формулами в Экселе,​​ пустыми в выделенной​​ на нижней панели.​ число, соответствующее числу​ показаниях сразу же.​​ прямо сейчас.​​. Например, следующая формула​Следующая формула возвращает количество​​ в некоторых случаях,​​ например. с буквами​ подобное сочетание функций​ «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;»столы»)+СЧЁТЕСЛИ(A1:A11;»стулья»).​ ссылок).​​ поле удовлетворяет обоим​​ ИСТИНА соответствует персики.​​Условное форматирование в Эксель​​«Правила выделения ячеек»​

​ проще работать с​ колонке. При этом​ Если осматривать число​ ячеек с цифрами​Выделите ячейку, в которой​А начнем мы с​ подсчитывает ячейки, значения​ ячеек, значение которых​​ их содержимое должно​​ формула пишет ошибку​​ может оказаться полезным.​​ Для указания нескольких​Функция не учитывает регистр​​ критериям одновременно). ​​ Результат можно увидеть,​​Урок:​​. Далее открывается пункт​​ данным оператором через​​ выделяем не более​ ячеек надо постоянно,​ в выбранном диапазоне.​ будет выводиться результат​

​ инструкции, как в​

office-guru.ru>

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

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

Примечание. Это формула массива, поэтому обязательно нажмите Ctrl + Shift + Enter, чтобы корректно ввести её. Как только вы это сделаете, Excel автоматически заключит всё выражение в {фигурные скобки}, как показано на скриншоте ниже. Ни в коем случае нельзя вводить фигурные скобки вручную, это не сработает.

В этом примере мы считаем уникальные имена в диапазоне A2: A10, поэтому наше выражение выглядит так:

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

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

Как работает формула подсчета уникальных значений?

Как видите, здесь используются 3 разные функции – СУММ, ЕСЛИ и СЧЁТЕСЛИ. Посмотрим, что делает каждая из них:

Функция СЧЁТЕСЛИ считает, сколько раз каждое отдельное значение появляется в анализируемом диапазоне.

В этом примере СЧЁТЕСЛИ(A2:A10;A2:A10)возвращает массив {3:2:2:1:1:2:3:2:3}.

Функция ЕСЛИ оценивает каждый элемент в этом массиве, сохраняет все единицы (то есть, уникальные) и заменяет все остальные цифры нулями.

Итак, функция ЕСЛИ(СЧЁТЕСЛИ(A2:A10;A2:A10)=1;1;0) преобразуется в  ЕСЛИ({3:2:2:1:1:2:3:2:3}) = 1,1,0).

И далее она превращается в массив чисел  {0:0:0:1:1:0:0:0:0}. Здесь 1 означает уникальное значение, а 0 – появляющееся более 1 раза.

Наконец, функция СУММ складывает числа в этом итоговом массиве и выводит общее количество уникальных значений. Что нам и нужно.

Как посчитать количество слова или символа в столбце или ячейке Excel?

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

Подсчитайте количество экземпляров числа или слова в столбце с формулой

В этом разделе я представляю формулу для подсчета количества экземпляров числа или слова в столбце.

Выберите пустую ячейку и введите эту формулу = СЧЁТЕСЛИ (A1: A10; «Звезды»), Нажмите Enter key, затем подсчитывает количество экземпляров слова «Звезды» в диапазоне столбцов. Смотрите скриншот:

В формуле A1: A10 — это диапазон столбцов, из которого вы хотите посчитать слово или число, а звезды — это слово, которое вы хотите подсчитать, вы можете изменить его на число, символ или другие слова по своему усмотрению.

Подсчитайте количество экземпляров символа или слова в ячейке с формулой

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

Подсчитайте определенный символ в одной ячейке с помощью формулы

Выберите пустую ячейку, введите эту формулу = LEN (F1) -LEN (ПОДСТАВИТЬ (F1; G1; «»)), Нажмите Enter, чтобы получить результат. Смотрите скриншот:

Чаевые: в этой формуле F1 — это ячейка, из которой вы хотите посчитать символы, а G1 — это символ, который вы хотите подсчитать, вы можете изменить их по своему усмотрению.

Подсчитайте определенное слово в одной ячейке с помощью формулы

Выберите пустую ячейку, введите эту формулу = (LEN (A14) -LEN (ПОДСТАВИТЬ (A14, B14, «»))) / LEN (B14), Нажмите Enter ключ для получения результата. Смотрите скриншот:

Наконечник: в этой формуле A14 — это используемая вами ячейка, B14 — это слово, которое вы хотите посчитать, вы можете изменить их по своему усмотрению.

Подсчитайте конкретное слово в ячейке с помощью Kutools for Excel

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

Подсчитайте количество всех слов в диапазоне или ячейке с помощью Kutools for Excel

В некоторых случаях вам может потребоваться подсчитать количество всех слов в диапазоне или ячейке так же, как общее количество в документе Word. Очевидно, вы можете скопировать содержимое ячеек в документ Word, чтобы подсчитать общее количество слов, но здесь я представляю удобный инструмент добавления — Kutools for Excel вам, с его Подсчитать слова в диапазоне утилита для быстрого подсчета слов в диапазоне или ячейке.

Kutools for Excel, с более чем 300 удобные функции, облегчающие вашу работу.

После установки Kutools for Excel, сделайте следующее:（Бесплатная загрузка Kutools for Excel прямо сейчас!)

1. Выберите пустую ячейку, в которую вы поместите подсчитанный результат, нажмите Kutools > Формула Помощник > Формула Помощник. Смотрите скриншот:

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

3. Нажмите Ok. И было подсчитано общее количество слов в выбранном диапазоне.

Различные текстовые значения.

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

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

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

{=СУММ(ЕСЛИ(ЕТЕКСТ(A2:A10);1/СЧЁТЕСЛИ(A2:A10; A2:A10);»»))}

Не забываем, что это формула массива.

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

В общем виде это выглядит так:

Предположим, у вас есть список имен сотрудников вместе с часами работы над проектом, и вы хотите знать, сколько человек в этом участвовали. Глядя на данные, вы можете увидеть, что имена повторяются. А вы хотите пересчитать всех, кто хотя бы раз появился в этом списке.

Применяем формулу массива:

Она является более сложной, чем аналогичная, которая использует функцию ЧАСТОТА() для подсчета различных чисел. Это потому, что ЧАСТОТА() не работает с текстом. Поэтому ПОИСКПОЗ преобразует имена в номера позиций, которые может обрабатывать ЧАСТОТА().

Если какая-либо из ячеек в диапазоне пустая, вам необходимо использовать более сложную формулу массива, которая включает в себя функцию ЕСЛИ:

Примечание: поскольку логическая проверка в операторе ЕСЛИ содержит массив, то наше выражение сразу становится формулой массива, которая требует ввода через Ctrl+Shift+Enter. Поэтому же СУММПРОИЗВ была заменена на СУММ.Применительно к нашему примеру это выглядит так:

Теперь «сломать» этот расчет может только наличие ячеек с ошибками в исследуемом диапазоне.

Как посчитать кол-во заполненных ячеек (Формулы/Formulas)

​ её с легкостью​​ которая расположена внизу​​Excel выделит диапазоны ячеек,​ языке. Эта страница​ формулы. К примеру,​ непустых ячеек в​​ достаточно​Это уже следующая​​Еще вариант, обычная​​: Так а что​​ ниже приведенная формула​

​ т того, является​​ значений, которые больше​.​​Аргумент​, следует в его​ текстовых), содержащихся в​

​ сможем приспособить для​​ окна, около параметра​ а при нажатии​​ переведена автоматически, поэтому​ чтобы посчитать количество​ диапазоне, то можно​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИ($C$6:$AK$6;15)​​ задача)​ формула (правда после​ считать-то надо?​ подсчитывает количество всех​ ли значение числом,​ числа 150.​После того, как вышеуказанные​​«Диапазон»​ поле внести запись.​ целевом столбце.​ наших нужд, а​«Количество»​

​ клавиши ВВОД появится​​ ее текст может​ ячеек, значения в​ воспользоваться статистической функцией​​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИ($C$6:$AK$6;20)​Сначала мы считали,​ 3 выходных трудно​В файле только​

​ пустых ячеек из​​ текстом или просто​​Выделяем ячейку для вывода​​ настройки выполнены, жмем​представляется в виде​​ В этом окне,​Как видим, в отличие​ именно для подсчета​​будет отображаться число​

​ результат:​​ содержать неточности и​​ которых больше среднего​​СЧЕТЗ​У Вас подсчёт​ что Абонент1 после​ пока думать)))​

​ некие данные, никаких​​ диапазона B2:E2:​ пустой ячейкой. Рассмотрим​​ результата и производим​ на кнопку​ ссылки на конкретный​ как и в​ от предыдущего способа,​​ элементов столбца, заполненных​ значений, содержащихся в​Если известно, что нужно​ грамматические ошибки. Для​ значения, можно воспользоваться​

​. Непустыми считаются ячейки,​​ в Вашем случае​

​ 2-х пустых месяцев​​=МИН(ЕСЛИОШИБКА(АГРЕГАТ(15;6;1/(1/(ЕПУСТО(B2:H2)*ЕПУСТО(C2:I2)*СТОЛБЕЦ(A2:G2)))-1;1);9^9);СЧЁТ(B2:I2))​​ расчетов или пояснений…​​Как видно из выше​

​ все эти функции​​ переход в окно​​«OK»​ массив ячеек, а​ окне предыдущей функции,​ данный вариант предлагает​

​ столбце

В подсчете​​ учесть только числа​​ нас важно, чтобы​​ следующей формулой:​ содержащие текст, числовые​ не зависит от​ перестает «жить» и​IrinaK​IrinaK​ приведенных примеров, каждая​ в действии на​ аргументов функции​

​ будут участвовать ячейки,​​ и даты, но​​ эта статья была​​Если одного условия Вам​ значения, дату, время,​

​ месяца.​​ начинает «жить» Абонент2.​

​:​​: Нужно посчитать сколько​​ из функций обладает​​ практическом примере.​

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

​AlexM помог мне​​sboy​​ месяцев живет абонент.​ своим специальным предназначением​Ниже на рисунке представлены​.​ выдает результат на​Аргумент​​ полей, но, как​ с возможным его​​Всего у оператора может​

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

​В поле​​ экран. Как видим,​​«Критерий»​ и в прошлый​​ сохранением там. Но,​ насчитываться до 255​ и т.д.). Пустые​Подсчет символов в ячейках​ секунд и сообщить,​​ статистической функцией​ ЛОЖЬ.​​Pleer​Теперь задача посчитать​ предложенный вариант, но​ 2 месяцев нет​ процесс обработки данных,​

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

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

excelworld.ru>

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

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

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

Чтобы лучше понять этот расчет, разберём следующий пример.

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

Предполагая, что список доставленных товаров находится в столбце B (начиная с B2), и мы считаем число вхождений «A». Выражение выглядит следующим образом:

Чтобы понять логику расчётов, давайте разделим процесс на отдельные этапы:

• Сначала вы подсчитываете общую длину строки в B2:ДЛСТР(B2)
• Затем вы используете функцию ПОДСТАВИТЬ, чтобы удалить все вхождения буквы «А» в B2, заменив ее пустой строкой («»):ПОДСТАВИТЬ(B2;»А»;»»)
• Затем вы подсчитываете длину получившейся строки без буквы «А»:ДЛСТР(ПОДСТАВИТЬ(B2;»А»;»»))
• Наконец, вы вычитаете длину строки без буквы «А» из первоначальной длины.

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

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

Примечание. Функция ПОДСТАВИТЬ в Excel чувствительна к регистру, поэтому в приведенном выше выражении также учитывается регистр. Например, B7 содержит 2 вхождения буквы «A» — одно в верхнем регистре и второе в нижнем регистре. Учитываем только символы верхнего регистра, потому что мы передали «A» функции ПОДСТАВИТЬ.

Различные текстовые значения с условием.

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

Чтобы решить эту проблему, вам может помочь этот вариант:

Введите это в пустую ячейку, куда вы хотите поместить результат, F2, например. А затем нажмите Shift + Ctrl + Enter вместе, чтобы получить правильный результат.

Поясним: здесь A2:A18 это список покупателей, с учётом которого вы ограничиваете область расчётов, B2: B18 — перечень товаров, в котором вы хотите посчитать уникальные значения, Е2 содержит критерий, на основании которого подсчет ограничивается только конкретным покупателем.

Второй способ.

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

Применительно к нашему примеру:

С учетом ограничений ЕСЛИ() функция ПОИСКПОЗ определяет порядковый номер только для строк, которые соответствуют критериям.

Если какая-либо из ячеек в диапазоне критериев пустая, вам необходимо скорректировать расчёт, добавив дополнительно ЕСЛИ для обработки пустых ячеек.  Иначе они будут переданы в функцию ПОИСКПОЗ, которая в ответ сгенерирует сообщение об ошибке.

Вот что получилось после корректировки:

То есть все действия и расчёты мы производим, если в столбце B нам встретилась непустая ячейка: ЕСЛИ(B2:B10 <> «»….

Если у вас есть два критерия, вы можете расширить логику формулы путем добавления другого вложенного ЕСЛИ.

Поясним. Определим, сколько наименований товара находилось в первой партии первого покупателя.

Критерии запишем в G2 и G3.

В общем виде это выглядит так:

Подставляем сюда реальные данные  и получаем результат:

В первой партии 2 наименования товара, хотя и 3 позиции.