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

КАК НАЙТИ НЕПУСТЫЕ ЯЧЕЙКИ В EXCEL

Microsoft Excel — это мощная программа для работы с электронными таблицами, где множество данных может быть сохранено и обработано. Когда вам необходимо найти непустые ячейки в Excel, есть несколько способов сделать это.

1. Используйте функцию COUNTA: Функция COUNTA позволяет сосчитать количество непустых значений в диапазоне ячеек. Вы можете выбрать нужный диапазон ячеек и применить функцию COUNTA к этому диапазону. Результатом будет количество непустых ячеек.

2. Используйте фильтр: Фильтр в Excel позволяет отобразить только непустые ячейки в вашем диапазоне данных. Щелкните на заголовок столбца и выберите «Фильтр». После этого вы сможете выбрать опцию «Непустые значения» и отобразить только ячейки с данными.

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

4. Используйте формулу IF: Формула IF позволяет проверять условия и возвращать определенное значение, в зависимости от результатов проверки. Вы можете использовать формулу IF для проверки каждой ячейки в диапазоне и вернуть некоторое значение, если ячейка не является пустой. Таким образом, вы можете быстро найти непустые ячейки.

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

5 Трюков Excel, о которых ты еще не знаешь!

Видео №39. Excel. Формула счета непустых ячеек.

Как Выделить Все Непустые Ячейки в Excel

Простой подсчет количества символов в ячейках Excel. #ТрюкиExcel

Урок 3 курса по Power BI \

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

Excel урок 38. Как найти последнюю заполненную ячейку в столбце и взять из нее данные

Аналог функции ВПР в Excel ➤ Поиск всех совпадений

Михаил Захаров

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

Вам также может понравиться:

КАК ПОСЧИТАТЬ КОЛИЧЕСТВО НЕПУСТЫХ ЯЧЕЕК В EXCEL

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

Для подсчета количества непустых ячеек в Excel, примените следующую формулу: =COUNTA(диапазон_ячеек). Вместо «диапазон_ячеек» укажите интересующий вас диапазон, например, A1:A10.

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

Например, чтобы подсчитать количество непустых ячеек в диапазонах A1:A10 и B1:B10, введите формулу =COUNTA(A1:A10, B1:B10).

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

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

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

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

Видео №39. Excel. Формула счета непустых ячеек.

Считаем количество заполненных ячеек в Excel (СЧЁТЗ)

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

Сравнение двух списков в Excel (Различия \u0026 Сходства)

Михаил Захаров

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

Вам также может понравиться:

Как подсчитать непустые ячейки в Excel (8 полезных методов)

В этом учебном пособии я покажу вам 8 Вы можете использовать эти методы даже в больших наборах данных, чтобы быстро определить количество пустых ячеек. В этом учебнике вы также узнаете некоторые важные инструменты и методы excel, которые будут очень полезны при решении любых задач, связанных с excel.

Скачать Практическое пособие

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

Count Cells that are Not Blank.xlsx

8 полезных методов подсчета непустых ячеек в Excel

Для наглядного объяснения шагов мы взяли краткий набор данных. Набор данных содержит приблизительно 7 ряды и 3 Изначально мы отформатировали все ячейки, содержащие долларовые значения, в формате Бухгалтерский учет формат. Для всех наборов данных мы имеем 3 уникальные столбцы, которые Предметы , Январские продажи и февральские продажи Хотя впоследствии мы можем изменить количество колонок, если это будет необходимо.

1. использование функции COUNTA

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

Шаги:

• Сначала перейдите в ячейку D10 и вставьте следующую формулу:

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

Смотрите также: Как использовать XLOOKUP для возврата пустого значения вместо 0

2. Применение функции COUNTIF

Функция COUNTIF в excel подсчитывает ячейки в диапазоне, которые удовлетворяют определенному единственному условию. Мы можем использовать эту функцию для подсчета ячеек, которые не являются пустыми.

Шаги:

• Для начала дважды щелкните на ячейке D10 и введите приведенную ниже формулу:

• Затем нажмите кнопку Войти и вы должны получить количество ячеек с данными.

3. Использование функции COUNTIFS

Функция COUNTIFS в excel аналогична предыдущей функции, за исключением того, что она может принимать несколько условий. Выполните следующие шаги, чтобы подсчитать ячейки, которые не являются пустыми, используя эту функцию.

Смотрите также: Как создать форму ввода данных с выпадающим списком в Excel (2 метода)

Шаги:

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

• Затем нажмите кнопку Войти и, следовательно, будет найдено общее количество ячеек с данными внутри ячейки D10 .

4. Подсчет незаполненных ячеек с помощью функции COUNTBLANK

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

Шаги:

• Чтобы запустить этот метод, перейдите в ячейку D10 и введите следующую формулу:

• После этого нажмите кнопку Войти или щелкните на любой пустой ячейке.
• Сразу же это даст вам количество заполненных ячеек внутри ячейки D10 в качестве 14 .

5. использование функции SUMPRODUCT

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

Шаги:

• Как и ранее, вставьте приведенную ниже формулу в ячейку D10 :

• Наконец, нажмите кнопку Войти и мы должны получить результат в виде 14 .

6. Применение функции LEN для подсчета незаполненных ячеек

Функция LEN в excel в основном измеряет длину текстовой строки. Мы будем использовать эту функцию с функция SUMPRODUCT для подсчета ячеек, которые не являются пустыми.

Шаги:

• Чтобы начать процесс, перейдите к ячейке D10 и введите приведенную ниже формулу:

• Затем нажмите Войти и это подсчитает общее количество ячеек со значением данных внутри ячейки D10 .

7. Использование функции «Найти и выбрать

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

Шаги:

• Для этого метода выделите все ячейки из B4 на D9 .
• Затем перейдите к Главная вкладку и нажмите на Найти и выбрать по адресу Редактирование .
• Здесь нажмите на Найти .

• Теперь, в новом Найти и заменить окно, тип * в Найдите то, что поле и нажмите кнопку Найти все .

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

8. подсчет в строке состояния

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

Шаги:

• Прежде всего, выделите все ячейки из B4 на D9 .
• Следовательно, вы должны получить общее количество заполненных ячеек в нижней части окна excel.

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

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

Шаги:

• Для начала дважды щелкните на ячейке D10 и вставьте приведенную ниже формулу:

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

Заключение

Я надеюсь, что вы смогли применить методы, которые я показал в этом руководстве по подсчету непустых ячеек в excel. Как вы можете видеть, существует довольно много способов достижения этой цели. Поэтому выбирайте метод, который лучше всего подходит для вашей ситуации. Если вы застряли на каком-либо из шагов, я рекомендую пройти через них несколько раз, чтобы прояснить любую путаницу. Наконец, чтобы узнать больше excel техники, следуйте нашим ExcelWIKI сайт. Если у вас есть какие-либо вопросы, пожалуйста, дайте мне знать в комментариях.

Предыдущий пост Как использовать функцию WEEKDAY в Excel (с 8 примерами)
Следующий пост Формула VLOOKUP для сравнения двух столбцов в разных листах Excel

Hugh West

Хью Уэст — опытный тренер и аналитик Excel с более чем 10-летним опытом работы в отрасли. Он имеет степень бакалавра в области бухгалтерского учета и финансов и степень магистра делового администрирования. Хью страстно любит преподавать и разработал уникальный подход к обучению, которому легко следовать и который легко понять. Его экспертные знания Excel помогли тысячам студентов и специалистов по всему миру улучшить свои навыки и преуспеть в своей карьере. В своем блоге Хью делится своими знаниями со всем миром, предлагая бесплатные учебные пособия по Excel и онлайн-обучение, чтобы помочь отдельным лицам и компаниям полностью раскрыть свой потенциал.

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

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

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

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

Инструмент «Выделить группу ячеек»

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

На ленте меню Главная выбираем Найти и выделить, затем — Bыделить группу ячеек. В открывшемся окне активируем кнопку Пустые ячейки и жмем ОК.

В результате все клетки без значений будут выделены.

Вы можете использовать «Цвет заливки» на вкладке «Главная», чтобы изменить цвет фона пустых ячеек и зафиксировать выделение.
Обратите внимание, что этот инструмент не обнаруживает псевдо-пустые позиции — с формулами, возвращающими пустое значение. То есть, они не будут выделены.

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

Также вы, я думаю, заметили, что аналогичным образом можно выделить и непустые ячейки. Просто нужно выбрать другой объект поиска — либо Константы, либо Формулы.

Функция поиска и замены.

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

1. Выберите диапазон, в котором нужно считать пустоты. Если вы не укажете диапазон, то поиск будет идти по всей таблице. Затем нажмите на ленте «Найти и выделить» или же используйте комбинацию клавиш Ctrl + F .
2. Оставьте поле Найти незаполненным.
3. Нажмите Параметры и установите флажок Ячейка целиком.
4. Выберите формулы или значения из раскрывающегося списка Область поиска:
   • Если вы выберете Поиск значений, инструмент будет считать все пустые ячейки, включая псевдо-пустые со значениями типа «».
   • Выберите параметр «Формулы» для поиска и подсчёта абсолютно пустых позиций. «Пустые» формулы учтены не будут.
5. Нажмите кнопку «Найти все», чтобы увидеть результаты. Вы увидите количество найденного в нижнем левом углу.

При необходимости, используя вкладку Заменить, вы можете заполнить всех их нулями или каким-либо текстом («Нет данных», к примеру).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Количество пустых ячеек как условие в формуле ЕСЛИ.

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

Хотя в Excel нет встроенной функции ЕСЛИСЧИТАТЬПУСТОТЫ, вы можете легко создать свою собственную формулу, используя вместе функции ЕСЛИ и СЧИТАТЬПУСТОТЫ. Вот как:

• Создаем условие, что количество пробелов равно нулю, и помещаем это выражение в логический тест ЕСЛИ:
  СЧИТАТЬПУСТОТЫ(B2:D2)=0
• Если логический результат оценивается как ИСТИНА, выведите «Нет пустых».
• Если же — ЛОЖЬ, возвращаем «Пустые».

Полная формула принимает такой вид:

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

=ЕСЛИ(СЧИТАТЬПУСТОТЫ(B2:D2)=0; СУММ(B2:D2); «Пустые»)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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