Как в Эксель посчитать количество ячеек, значений, чисел

Функции счета в Excel

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

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

Количество значений в строке состояния Excel

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

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

Настройка строки состояния Excel

Настройка строки состояния

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

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

Для подсчета количества ячеек в Excel предусмотрено две функции:

  1. ЧСТРОК(массив) – считает количество строк в выбранном диапазоне, независимо от того, чем заполнены его ячейки. Формула даёт результат только для прямоугольного массива из смежных ячеек, иначе возвращает ошибку;
Считаем количество строк в Эксель

Считаем количество строк

  1. ЧИСЛСТОЛБ(массив) – аналогична предыдущей, но считает количество столбцов массива

В Эксель нет функции, чтобы определить количество ячеек в массиве, но это можно легко посчитать, умножив количество строк на количество столбцов: =ЧСТРОК(массив)* ЧИСЛСТОЛБ(массив).

Как посчитать пустые ячейки в Excel

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

Считаем пустые ячейки в Эксель

Считаем пустые ячейки

Функция считает ячейку пустой, если в ней ничего не записано, или формула внутри нее возвращает пустую строку.

Как в Эксель посчитать количество значений и чисел

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

Посчитать числовые значения в Excel

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

Если нужно определить количество ячеек, содержащих значения, воспользуемся функцией СЧЁТЗ(значение1;значение2;…). В отличие от предыдущей функции, она посчитает не только числа, а и любые комбинации символов. Если ячейка непустая – она будет посчитана. Если в ячейке формула, которая возвращает ноль или пустую строку – функция ее тоже включит в свой результат.

Посчитать непустые ячейки в Excel

Посчитать непустые ячейки

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

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

  • Массив – диапазон ячеек, среди которых производится подсчет. Можно задавать только прямоугольный диапазон смежных ячеек;
  • Критерий – условие, по которому происходит отбор. Текстовые условия и числовые со знаками сравнения запишите в кавычках. Равенство числу записываем без кавычек. Например:
    • «>0» – считаем ячейки с числами больше нуля
    • «Excel» – считаем ячейки, в которых записано слово «Excel»
    • 12 – счет ячеек с числом 12
Счет ячеек с условием в Эксель

Счет ячеек с условием

Если нужно учесть несколько условий, используйте функцию СЧЁТЕСЛИМН(массив1;критерий1;[массив2];[критерий1]…). Функция может содержать до 127 пар «массив-критерий».

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

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

Счет значений по нескольким условиям

Как определить наиболее часто встречающееся число

Чтобы найти число, которое чаще всего встречается в массиве, есть в Эксель функция МОДА(число1;число2;…). Результатом её выполнение будет то самое число, которое встречается чаще всего. Чтобы определить их количество — можно воспользоваться комбинацией формул суммирования и формул массива.

Если таких чисел несколько – будет выведено то, которое раньше других встречается в списке. Функция работает только с числовыми данными.

Часто встречающееся число вЭксель

Часто встречающееся число

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

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

53 комментария

  1. Дарья

    очень познавательно!!! прям обновляются старые знания)) ждем следующих постов!!

  2. Аноним

    Здравствуйте. Как посчитать количество чисел, при этом одинаковые числа считать как одну единицу Спасибо

    1. Александр Томм (Автор записи)

      Здравствуйте. Пожалуйста, опишите подробнее, что нужно сделать. Форма для обратной связи: http://officelegko.com/obratnaya-svyaz/

  3. Татьяна

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

    1. Александр Томм (Автор записи)

      Татьяна, функция вычисления среднего значения с условием работает только с одним непрерывным массивом. Как вариант решения задачи — сначала удалите нулевые значения. Нажмите Ctrl+F, в поле «Найти» напишите ноль, нажмите «Параметры» и установите галку «Ячейка целиком». Жмите «Найти», а потом Ctrl+A. Будут выделены все ячейки, в которых содержится ноль. Нажмите Del чтобы очистить их. Теперь можно использовать обычную функцию СРЗНАЧ, перечисляя диапазоны ячеек через точку с запятой. Пустые ячейки (где раньше стояли нули) функция будет игнорировать.

  4. Ольга

    Здравствуйте.Как подсчитать количество весенних месяцев в массиве, где встречаются и зимние? Или: как удалить лишние (кроме весенних )месяцы, чтобы найти среднюю температуру весной?

    1. Александр Томм (Автор записи)

      Ольга, здравствуйте. Можно использовать функцию =СРЗНАЧЕСЛИ(диапазон усреднения; диапазон1; условие1; диапазон2; условие2). В аргументах укажите:
      диапазон усреднения — массив с температурами
      диапазон1 — массив с номерами месяцев
      условие1 — «>2» (запишите в кавычках)
      диапазон2 — массив с номерами месяцев
      условие2 — «<6" (запишите в кавычках)
      Такая формула посчитает среднюю температуру весенних месяцев!

      1. Ольга

        Спасибо!)))

        1. Александр Томм (Автор записи)

          Ольга, спасибо за вопрос, жду Вас на страницах блога!

  5. Владимир

    Здравствуйте! Помогите пожалуйста подобрать формулу. Мне нужно посчитать сумму чисел отработанного сотрудником времени за месяц. В строке по каждому сотруднику стоят числа 8 с понедельника по четверг включительно, а в пятницу стоят цифры (8*). Цифра со звёздочкой нужно чтоб считалась как 8, а не как 1. Я пробовал посчитать с помощью формулы =((СУММА(ДИАПАЗОН)+СЧЁТЕСЛИ(ДИАПАЗОН;»8*»))). РЕЗУЛЬТАТ ПОЛУЧАЕТСЯ 57, а мне нужно, чтоб получилось 64.

    1. Александр Томм (Автор записи)

      Владимир, давайте модернизируем Вашу формулу так: =СУММ(ДИАПАЗОН)+СЧЁТЕСЛИ(ДИАПАЗОН;»8*»)*8

  6. Григорий

    Добрый день.
    Подскажите формулы:
    Вот что нужно посчитать.
    В ячейках А1 значение 1+2+1
    А2 значение 2+1
    Так вот необходимо посчитать общее количество цифр (в примере их 5)
    И в дальнейшем если в а1 поставить 1+0+1, а а2 без изменений, то формула так же должна сделать потсчет 5. Ну тоесть воспринимать 0 как единицу.
    Спасибо

    1. Александр Томм (Автор записи)

      Григорий, можно для каждой строки использовать формулу =ДЛСТР(ПОДСТАВИТЬ(A1;»+»;»»)). Сначала функция ПОДСТАВИТЬ удалит плюсы, а ДЛСТР — посчитает количество чисел, которые остались. Потом два значения сложить. Или стразу в одной ячейке записать сумму двух схожих формул для А1 и А2

  7. Наталья

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

    1. Александр Томм (Автор записи)

      Наталья, спасибо Вам за положительный отзыв. Заходите к нам еще, будет много полезного!

  8. Анна

    Здравствуйте!Дана таблица,в ней 4 столбца:ФИО,округ,физика,математика. Нужно найти средний балл по 2 предметам,учеников определенного округа.Спасибо!

    1. Александр Томм (Автор записи)

      Анна, здравствуйте. Воспользуйтесь функцией СРЗНАЧ, аргументами функции укажите ссылки на баллы по предметам Например, так: =СРЗНАЧ(C1:D1). Протяните формулу для всех учеников и получите нужные Вам средние баллы

      1. Анна

        спасибо! а как выбрать название округа? Например, мне нужно найти ср балл по физике и математике учеников южного округа.

        1. Александр Томм (Автор записи)

          Анна, тогда используйте функцию =СРЗНАЧЕСЛИ(Диапазон с округами;»южный округ»;диапазон с оценками). Естественно, диапазон с округами и диапазон с оценками должны быть одинаковой длины.

  9. Наталья

    Здравствуйте! подскажите пожалуйста как провести анализ видов преступления (Причина закрытия) по количеству значений и в процентах если есть таблица со столюцами: №, правонарушение, причина закрытия, статья

    1. Александр Томм (Автор записи)

      Наталья, здравствуйте. Вам подойдет инструмент «Сводная таблица». Через несколько дней я опишу, как он работает. Обязательно сброшу Вам ссылку!

  10. Виктор

    Есть столбец данных
    34,45,
    0
    3,
    56,45,
    Нужно посчитать количество чисел не больше 0, т.е. Должно получиться 5. Как это сделать?
    ДЛСТР(N9)-ДЛСТР(N9;»,»;»»))
    Считает одну ячейку правильно, а сумму столбца не могу посчитать.
    Может есть иной способ.

    1. Александр Томм (Автор записи)

      Виктор, здравствуйте. В Вашем примере 5 не получится, т.к. есть всего 4 числа. Чтобы посчитать числа не больше нуля, используйте функцию: =СЧЁТЕСЛИ(Диапазон;»< =0")

  11. Виктор

    Ошибся, написал лишнее «не». Нужно посчитать числа, как вариант количество запятых, потому что у меня бывают разные числа:36, 79С, 1СО и т.д.

    1. Александр Томм (Автор записи)

      Виктор, т.е. у Вас в одной ячейке может быть несколько чисел/значений? И Вам нужно их посчитать (в каком-то диапазоне, кроме отрицательных и нуля? Верно?

  12. Виктор

    Да именно так.

    1. Александр Томм (Автор записи)

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

  13. Ольга

    Добрый день.
    Подскажите, а если у меня в столбце есть несколько чисел (1, 2, 3. 4, 5…) то можно ли подсчитать кол — во пустых строк между ними? оно бывает разное.
    То есть во второй строке — стоит 1, в пятой строке стоит 2, в десятой строке стоит 3. А как напротив 1 поставить 2, напротив двойки поставить 4 и тд. Спасибо.

    1. Александр Томм (Автор записи)

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

  14. Ira

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

    1. Александр Томм (Автор записи)

      Ира, здравствуйте. Мне было бы проще ответить Вам, имея перед глазами пример. Пришлите пожалуйста документ, или хотя бы пару строк на имейл a.tomm@officelegko.com

  15. Наталья

    Здравствуйте. 31 день-т.е. 31 столбец, в первой строке указаны даты.В следующих строках указаны фио работников. Один сотрудник отработал 9 дней с 23 по 31, другой отработал 20 дней с 1 по 6 и с 9 по 22 число. Мне нужно, чтобы программа мне сказала, с какого по какое число отработал каждый сотрудник. Общее количество человек 169!поэтому вручную высматривать каждого очень долго. Можно ли как-то упростить процесс?

    1. Александр Томм (Автор записи)

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

  16. ОЛЬГА

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

    1. Александр Томм (Автор записи)

      Ольга, здравствуйте. Для диапазона А1:А15 формула будет выглядеть так: =СУММ(ЕСЛИ(ЕПУСТО(A1:A15);0;1/СЧЁТЕСЛИ(A1:A15;A1:A15))). Вы укажите в формуле свой диапазон во всех местах, где встречается ссылка. Данная формула будет игнорировать пустые ячейки.

    2. Александр Томм (Автор записи)

      Ольга, еще уточню. Когда запишете формулу, нажимайте не Enter, а Ctrl+Shift+Enter, поскольку это формула массива!

  17. Ирина

    Здравствуйте!
    Как посчитать количество одинаковых значенй,подскажите, пожалуйста? Например, в строке 12 восьмерок и еще другие числа. Надо посчитать количество восьмерок.
    Спасибо.

    1. Александр Томм (Автор записи)

      Ирина, здравствуйте. Если считать нужно именно восьмерки — воспользуйтесь такой функцией: =СЧЁТЕСЛИ(А1:А20;8). Вместо диапазона А1:А20 подставьте тот, где содержатся Ваши данные.

  18. Ольга

    Здравствуйте, Александр!
    Как посчитать количество заполненных ЧЁТНЫХ ячеек в столбце? Может Вы мне подскажете. Нигде не могу найти

    1. Александр Томм (Автор записи)

      Ольга, здравствуйте. Подскажу, конечно. Используйте формулу массива=СУММ(1-ОСТАТ(А1:А10;2)). Вместо А1:А10 подставьте Ваш массив с данными, после этого нажмите Ctrl+Shift+Enter, т.к. формулы массива закрываются именно так.

      1. Ольга

        Большое спасибо! Но у меня почему-то посчитались все ячейки в столбце, как заполненные, так и не заполненные. А мне нужны именно заполненные и именно чётные. Может я что-то не так делаю. Я скопировала формулу, вставила её в итоговую ячейку, выбрала нужный мне диапазон и нажала Ctrl+Shift+Enter. В итоге вышла цифра, показывающая общее количество ячеек в столбце.

        1. Александр Томм (Автор записи)

          Ольга, если у Вас есть пустые значения — попробуйте еще вот такую формулу массива: =СЧЁТЗ(А1:А10)-СУММ(ОСТАТ(А1:А10;2))

  19. Анна

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

  20. Анна

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

    1. Александр Томм (Автор записи)

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

  21. Михаил

    Здравствуйте, Александр!
    Подскажите решение задачки: в ячейке А1 есть формула (=25,5+25,6). Вопрос: как из этой простой формулы подсчитать количество суммируемых аргументов? Должно получится 2.

    1. Александр Томм (Автор записи)

      Здравствуйте, Михаил. Видимо, данную задачу можно решить только с помощью создания новых функций на языке VBA. Я рекомендую не использовать в формулах значения, а записать все значения в отдельных ячейках и указать в формуле лишь ссылки на них. Если это сделать невозможно — тогда пишем процедуру на VBA

      1. Михаил

        Здравствуйте, Александр!
        А можно ли подсчитать например только количество знаков «+» из этой же формулы : (=25,5+25,6)?

        1. Александр Томм (Автор записи)

          Михаил, здравствуйте. Если в Ваших формулах только сложение, т.е. только знак «+», то можно задачу решить так: =ДЛСТР(Ф.ТЕКСТ(B3))-ДЛСТР(ПОДСТАВИТЬ(Ф.ТЕКСТ(B3);»+»;»»))+1. Формула определит количество слогаемых в ячейке В3

  22. Михаил

    Здравствуйте, Александр!
    Урааа, заработала!!!!!!
    Я только немного добавил =ЕСЛИОШИБКА(ДЛСТР(Ф.ТЕКСТ(F20))-ДЛСТР(ПОДСТАВИТЬ(Ф.ТЕКСТ(F20);»+»;»»));0)+1 — это если в ячейки всего одно значение, а знак «+» отсуствует.
    Спасибо большое!

    1. Александр Томм (Автор записи)

      Михаил, отлично, что у Вас получается совершенствовать формулы и достигать нужного результата! Был рад Вам помочь, возвращайтесь еще на блог officelegko.com!

  23. Серик

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

    1. Александр Томм (Автор записи)

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

Оставить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *