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

Обновлено: 20.11.2024

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

Скачать книгу с практиками

Загрузите книгу Excel, которую мы использовали для подготовки этой статьи.

5 быстрых способов подсчета заполненных ячеек в Excel

Способ 1. Использование функции СЧЕТЧИК для подсчета заполненных ячеек в Excel

Давайте сначала познакомимся с нашим набором данных. Здесь я использовал 3 столбца и 7 строк, чтобы показать продажи некоторых продавцов в разных штатах. Вы заметите, что некоторые ячейки пусты. Теперь мы подсчитаем заполненные ячейки столбца C, используя функцию COUNTA. Функция COUNTA используется для подсчета непустых ячеек.

Шаги:

➽ Активировать ячейку D13

➽ Введите приведенную ниже формулу:

➽ Затем нажмите кнопку Enter.

И у нас есть количество заполненных ячеек в столбце C равно 4

Способ 2. Вставка функции СЧЁТЕСЛИМН в Excel для подсчета заполненных ячеек

Теперь подсчитаем заполненные ячейки с помощью функции СЧЁТЕСЛИМН. Эта функция используется для подсчета количества ячеек, соответствующих нескольким критериям в одном или разных диапазонах. Здесь я посчитаю ячейки штата Аризона со значениями продаж.

Шаги:

➽ Введите формулу в ячейку G5, приведенную ниже:

➽ Теперь просто нажмите кнопку Enter, и вы сразу получите результат.

Способ 3. Применение инструмента Excel «Найти и заменить» для подсчета заполненных ячеек

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

Шаг 1:

➽ Выберите диапазон ячеек: от B5 до D11.

➽ Нажмите Ctrl+F. Появится диалоговое окно инструмента «Найти и заменить».

➽ Введите «*» в поле «Найти».

➽ Выберите «Формулы» в раскрывающемся списке «Искать в».

➽ Наконец, нажмите «Найти все».

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

Шаг 2:

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

Метод 4. Объединение функций СУММПРОИЗВ и ДЛСТР для подсчета заполненных ячеек

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

Шаги:

➽ Введите формулу в ячейку D13, приведенную ниже:

➽ Нажмите кнопку "Ввод"

👇 Разбивка формулы:

➥ ДЛСТР(B5:D11)>0

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

➥ –(ДЛСТР(B5:D11)>0)

Эта формула покажет предыдущий результат в двоичном виде, как показано ниже:

➥ СУММПРОИЗВ(–(ДЛСТР(B5:D11)>0))

Наконец, функция СУММПРОИЗВ покажет количество найденных заполненных ячеек, которые будут возвращены как-

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

В этом последнем методе я буду использовать специальную формулу для подсчета всех заполненных ячеек. На самом деле это комбинация функций COLUMNS, ROWS и COUNTBLANK. Функция COLUMNS используется для подсчета номеров столбцов в диапазоне. Функция ROWS используется для подсчета номеров строк в диапазоне. А функция СЧИТАТЬПУСТОТЫ подсчитывает пустые ячейки.

Шаги:

➽ Введите формулу в ячейку G5, приведенную ниже:

➽ Затем просто нажмите кнопку Enter, чтобы получить результат.

👇 Разбивка формулы:

➥ СЧИТАТЬПУСТОТЫ(B5:D11)

Эта формула будет подсчитывать пустые ячейки в диапазоне (B5:D11). Он вернется как-

➥ РЯДЫ(B5:D11)

Он подсчитает количество строк в диапазоне (B5:D11) и вернет как-

➥ КОЛОННЫ(B5:D11)

Он подсчитает количество столбцов в диапазоне (B5:D11) и вернет как-

➥ СТОЛБЦЫ(B5:D11)*СТРОКИ(B5:D11)-СЧЕТПУСТЫЕ(B5:D11)

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

Заключение

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

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

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

Как подсчитать ячейки с текстом в Excel на ПК с Windows

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

Как считать ячейки с текстом в Excel на Mac

Действия для подсчета ячеек с текстом в электронной таблице через macOS такие же, как и в Excel для Windows:

  1. Запустите Excel, затем откройте таблицу, которую хотите проанализировать. Вы также можете просто "дважды щелкнуть" файл, если он использует Excel в качестве программы по умолчанию.
  2. Выберите пустую ячейку в электронной таблице, чтобы ввести формулу.
  3. В пустой ячейке введите « =СЧЁТЕСЛИ (диапазон, критерии) ». Эта формула подсчитывает количество ячеек в указанном диапазоне с текстом в них.
  4. В поле "диапазон" введите диапазон ячеек, который вы хотите просмотреть. Введите первую и последнюю ячейки, разделенные двоеточием. Например, чтобы подсчитать ячейки от B2 до B10, введите « B2:B10 ».
  5. В части «критерии» введите « «*» » в кавычках. Это подсчитывает количество ячеек с текстом в введенном вами диапазоне. Например, ваша полная формула должна выглядеть примерно так: «=СЧЕТЕСЛИ (B2:B10, «*»)».
  6. На клавиатуре нажмите Enter, чтобы применить формулу. Результат появится в выбранной ячейке.

Как подсчитать ячейки с текстом в Excel 365

Чтобы подсчитать ячейки, содержащие текст в электронной таблице, с помощью Excel 365, вы применяете ту же функцию «СЧЁТЕСЛИ», которая используется в Excel для Windows и macOS. Вот как:

  1. Откройте таблицу Excel, которую вы хотите изучить.
  2. Нажмите пустую ячейку, чтобы ввести формулу.
  3. В пустой ячейке введите: « =СЧЁТЕСЛИ (диапазон, критерии) ». Эта формула подсчитывает количество ячеек с текстом в указанном диапазоне ячеек.
  4. В части «диапазон» введите диапазон ячеек, который вы хотите просмотреть. Введите первую и последнюю ячейки, разделенные двоеточием. Например, чтобы подсчитать ячейки с C2 по C11, введите « C2:C1 ».
  5. В поле "критерии" введите или вставьте ""*"" в кавычках. Это подсчитывает количество ячеек (в указанном диапазоне), в которых есть текст. Например, полная формула должна выглядеть примерно так: « =СЧЁТЕСЛИ (C2:C11, «*»)».
  6. Теперь нажмите Enter, чтобы применить только что созданную формулу. Результат отобразится в ячейке формулы.

Как подсчитать ячейки с текстом в Excel в приложении для iPhone

Чтобы подсчитать количество ячеек с текстом в электронной таблице с помощью приложения Excel на iPhone, выполните следующие действия:

Как подсчитать ячейки с текстом в Excel в приложении для Android

Чтобы подсчитать количество ячеек с текстом в электронной таблице с помощью приложения Excel для Android, выполните следующие действия:

  1. Запустите приложение Excel для Android
    2. .
  2. Откройте таблицу, которую хотите просмотреть, нажав Открыть, чтобы просмотреть сохраненные таблицы, затем нажмите нужный файл, чтобы автоматически открыть его.< бр />
  3. Дважды нажмите на пустой ячейке, чтобы ввести формулу «СЧЁТЕСЛИ». Либо нажмите и удерживайте пустую ячейку, затем нажмите Изменить во всплывающем меню.
  4. В пустой ячейке введите « =СЧЁТЕСЛИ (диапазон, критерии) » без кавычек. Эта формула подсчитывает количество ячеек с текстом внутри диапазона ячеек.
  5. Для части формулы "диапазон" введите диапазон ячеек, который вы хотите подсчитать. Введите первую и последнюю ячейки, разделенные двоеточием. Чтобы подсчитать ячейки с E2 по E12 в одном столбце, введите « E2:E12 ».
  6. Для части формулы "критерии" введите " "*" " с кавычками. Это подсчитывает количество ячеек с текстом в указанном диапазоне, включая более одной строки. Ваша полная формула должна выглядеть примерно так: " =СЧЁТЕСЛИ (A2:E12, "*") ".
  7. Теперь нажмите Ввод, чтобы применить формулу. Результат отображается в ячейке формулы.

    8. Как подсчитать количество ячеек в Excel с определенным текстом

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

    1. Запустите Excel на своем устройстве и откройте "назначенный файл" или просто запустите "назначенный файл", если он настроен на открытие Excel по умолчанию.
    2. Нажмите пустую ячейку, чтобы ввести формулу.
    3. В пустой ячейке введите « =СЧЁТЕСЛИ (диапазон, критерии) » без кавычек.
    4. Для части формулы "диапазон" введите диапазон ячеек, который вы хотите подсчитать. Введите первую и последнюю ячейки, разделенные двоеточием. Чтобы подсчитать ячейки от A2 до A20, введите « A2:A20 » без кавычек.
    5. В разделе формулы "критерии" введите ""Excel"" с кавычками. Это подсчитывает количество ячеек с «Excel» в указанном диапазоне. Ваша формула должна выглядеть примерно так: « =СЧЕТЕСЛИ (A2:A20, «Excel») ».

      6. Как подсчитать ячейки с повторяющимся текстом в Excel

      Помимо подсчета ячеек с текстом и определенного текста, вы можете подсчитать количество ячеек с повторяющимся содержимым.

      В следующем примере мы ищем повторяющиеся оценки учащихся. Наша таблица настроена следующим образом:

      • Столбец A – список наших учеников A2:A10.
      • Столбец B – перечислены оценки каждого учащегося (A, B или C).
      • Столбец D — список доступных оценок. D2 – "А", D3 – "В" и D4 – "В".
      • Столбец E — количество баллов по каждой оценке.

      Подсчет ячеек с повторяющимся текстом, включая первый экземпляр

      Чтобы подсчитать количество ячеек в электронной таблице с экземплярами оценок "A", "B" или "C", включая первый экземпляр, введите следующие формулы:

      Теперь у вас есть количество повторяющихся оценок, включая первый экземпляр, указанный в столбце "E".

      Подсчет ячеек с повторяющимся текстом, исключая первый экземпляр

      Чтобы подсчитать количество ячеек в электронной таблице с экземплярами оценок "A", "B" или "C", исключая первый экземпляр, введите следующие формулы:

      • Для экземпляров класса "A", за исключением первого экземпляра, щелкните ячейку E2 и введите формулу "=СЧЁТЕСЛИ ($B$2 : $B$10, D2)-1".
      • Для экземпляров класса «B», за исключением первого экземпляра, щелкните ячейку E3 и введите формулу «=СЧЁТЕСЛИ ($B$2: $B$10, D3)-1».
      • Для экземпляров категории "C", за исключением первого экземпляра, щелкните ячейку E4 и введите формулу "=СЧЁТЕСЛИ ($B$2: $B$10, D4)-1".

      Теперь у вас есть количество повторяющихся оценок, за исключением первого экземпляра, указанного в столбце "E".

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

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

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

      Функция «ПРОМЕЖУТОЧНЫЙ ИТОГ» исключит значения в скрытых строках, поэтому возвращает количество только для выбранного цвета текста.

      Поиск ячеек с текстом

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

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

      Удалось ли вам выяснить, что вам нужно знать о данных вашей электронной таблицы? Насколько полезным вы считаете Excel в целом?Поделитесь своим мнением в разделе комментариев ниже.

      Было бы здорово, если бы существовала функция подсчета цветных ячеек в Excel?

      К сожалению, для этого нет встроенной функции.

      Это легко сделать.

      Это руководство охватывает:

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

      В этом уроке я покажу вам три способа подсчета цветных ячеек в Excel (с VBA и без него):

      1. Использование фильтра и функции ПРОМЕЖУТОЧНЫЕ ИТОГО
      2. Использование функции GET.CELL
      3. Использование пользовательской функции, созданной с помощью VBA.

      Чтобы подсчитать цветные ячейки в Excel, необходимо выполнить следующие два шага:

      • Фильтровать цветные ячейки
      • Используйте функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ для подсчета видимых цветных ячеек (после фильтрации).

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

      В этом наборе данных используются два цвета фона (зеленый и оранжевый).

      Вот шаги подсчета цветных ячеек в Excel:

      Как только вы отфильтруете ячейки, вы заметите, что значение в функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ изменяется и возвращает только количество ячеек, видимых после фильтрации.

      Как это работает?

      Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ использует 102 в качестве первого аргумента, который используется для подсчета видимых ячеек (скрытые строки не учитываются) в указанном диапазоне.

      Если данные не отфильтрованы, они возвращают 19, но если они отфильтрованы, то возвращается только количество видимых ячеек.

      GET.CELL — это функция Macro4, которая была сохранена из соображений совместимости.

      Он не работает, если используется как обычные функции на листе.

      Вот три шага использования GET.CELL для подсчета цветных ячеек в Excel:

      • Создайте именованный диапазон с помощью функции GET.CELL
      • Используйте именованный диапазон для получения цветового кода в столбце
      • Использование номера цвета для подсчета количества окрашенных ячеек (по цвету)

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

      Создание именованного диапазона

      Получение цветового кода для каждой ячейки

      В ячейке рядом с данными используйте формулу =GetColor

      Эта формула вернет 0, если в ячейке НЕТ цвета фона, и вернет определенное число, если цвет фона есть.

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

      Подсчет цветных ячеек с использованием цветового кода

      Если вы выполните описанный выше процесс, у вас будет столбец с числами, соответствующими цвету фона в нем.

      Чтобы получить количество определенного цвета:

      • Где-то под набором данных задайте тот же цвет фона для ячейки, которую вы хотите подсчитать. Убедитесь, что вы делаете это в том же столбце, который вы использовали при создании именованного диапазона. Например, я использовал столбец A, поэтому буду использовать только ячейки столбца A.
      • В соседней ячейке используйте следующую формулу:

      Эта формула даст вам количество всех ячеек с указанным цветом фона.

      Как это работает?

      Функция СЧЁТЕСЛИ использует именованный диапазон (GetColor) в качестве критерия. Именованный диапазон в формуле относится к соседней ячейке слева (в столбце A) и возвращает код цвета для этой ячейки. Следовательно, этот номер цветового кода является критерием.

      Функция СЧЁТЕСЛИ использует диапазон ($F$2:$F$18), содержащий номера цветовых кодов всех ячеек, и возвращает количество на основе числа критериев.

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

      Но если вас устраивает использование VBA, это самый простой из трех методов.

      Используя VBA, мы создадим пользовательскую функцию, которая будет работать как функция COUNTIF и возвращать количество ячеек с определенным цветом фона.

      Вот код:

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

      Чтобы использовать эту функцию, просто используйте ее как любую обычную функцию Excel.

      Синтаксис: =GetColorCount(CountRange, CountColor)

      • CountRange: диапазон, в котором вы хотите подсчитать ячейки с указанным цветом фона.
      • CountColor: цвет, для которого вы хотите подсчитать количество ячеек.

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

      Примечание. Поскольку в книге есть код, сохраните его с расширением .xls или .xlsm.

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

      Откройте электронную таблицу Excel.

      Найдите ячейки, которые нужно посчитать. Например, если у вас есть спорадические данные, которые начинаются в ячейке A1 и продолжаются до E100, то их ссылка будет "A1:E100". Если у вас есть отдельные разделы данных, которые необходимо включить, также получите ссылку на их местоположение, например "G1: G100" или просто "F1" для одной записи.

      Введите "=COUNTA(ссылка1,ссылка2,ссылка3)" и замените ссылки фактическими ссылками на ячейки, которые вы нашли ранее. Вы можете объединить многочисленные ссылки для подсчета непустых данных в отдельных разделах. В этом примере вы должны ввести «=COUNTA(A1:E100,G1:G100,F1)» и нажать «Enter», чтобы подсчитать непустые ячейки в этих диапазонах.

      При необходимости включите функцию в другие расчеты. В примере, если вы подсчитали сумму в ячейке J1 и хотели получить среднее значение в ячейке K1, введите "=J1/СЧЁТЕЗ(A1:E100,G1:G100,F1)" в ячейке K1.

      С. Тейлор начал профессиональную писательскую карьеру в 2009 году и часто пишет о технологиях, науке, бизнесе, финансах, боевых искусствах и природе. Он пишет как для онлайн-изданий, так и для офлайн-изданий, включая Journal of Asian Martial Arts, Samsung, Radio Shack, Motley Fool, Chron, Synonym и другие. Он получил степень магистра биологии дикой природы в Университете Клемсона и степень бакалавра искусств в области биологических наук в Колледже Чарльстона. У него также есть несовершеннолетние по статистике, физике и изобразительному искусству.

      Читайте также: