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

Обновлено: 21.11.2024

У Брайана есть ряд чисел с 240 ячейками. В этой строке числа неуклонно уменьшаются и, в конце концов, в какой-то момент в этих 240 ячейках станут равными 0. Нули будут продолжать заполнять оставшиеся ячейки в строке. Брайану нужно написать уравнение, которое вернет последнее ненулевое значение в строке.

Существует множество способов вернуть желаемое значение. (Разве в Excel не всегда так? Вы можете придумать множество способов получить результат.) В общем, вы можете использовать обычную формулу или формулу массива.

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

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

Вот более короткий вариант формулы, основанный на смещении с правой стороны диапазона, а не с левой:

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

Вот версия, в которой вместо этого используется функция ИНДЕКС:

Эта версия еще короче благодаря функции ПРОСМОТР:

Также можно использовать формулы массива. (Формулы массива вводятся нажатием Ctrl+Shift+Enter.) Здесь используется функция ДВССЫЛ:

В этой формуле массива используется интересная реализация функции ПРОСМОТР для поиска правильного результата:

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

Вот еще более короткий вариант:

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

Формула сначала определяет максимальный столбец в строке (в данном случае строка 6), значение которого не равно нулю, а затем использует функцию ИНДЕКС, чтобы получить значение из этого столбца в этой строке.

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

Биография автора

На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.

БОЛЬШЕ ОТ АЛЛЕНА

Определение сочетаний клавиш для символов

Вам нужно часто использовать символы в данных Excel? Обычно их вставляют с помощью диалогового окна «Символ» .

Поиск документа

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

Применяем коды символов в работе

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

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

Дополнительные советы по Excel (лента)

Автоматическая нумерация строк

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

Поиск адреса наименьшего значения в диапазоне

Поиск наименьшего значения в диапазоне относительно прост; вы можете просто использовать функцию рабочего листа MIN. Обнаружение .

Подсчет звездочек в столбце

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

БЕСПЛАТНАЯ СЛУЖБА. Получайте подобные советы каждую неделю в ExcelTips, бесплатном информационном бюллетене по повышению эффективности. Введите свой адрес и нажмите "Подписаться".

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

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

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

Выберите пустую ячейку, в которую вы хотите поместить результат подсчета, и введите в нее эту формулу =СЧЕТ(ЕСЛИ(A1:E5<>0, A1:E5)) и нажмите клавиши Shift + Ctrl + Enter, чтобы получить результат.

Совет. В формуле A1:E5 — это диапазон ячеек, который вы хотите подсчитать, игнорируя как пустые ячейки, так и нулевые значения.

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

После бесплатной установки Kutools for Excel сделайте следующее:

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

<р>2. В диалоговом окне «Выбор определенных ячеек» установите флажок «Ячейка» в разделе «Тип выбора» и выберите «Не равен критерию» в первом раскрывающемся списке в разделе «Определенный тип», затем введите 0 в следующее текстовое поле. Смотрите скриншот:

<р>3. Нажмите "ОК" или "Применить". Появится диалоговое окно с напоминанием о том, сколько ячеек выбрано, и в то же время были выбраны все ячейки, кроме нулевых значений и пустых ячеек.

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

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

В этом посте рассказывается, как подсчитать количество ячеек с ненулевым значением с помощью формулы в Excel 2013/2016.

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

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

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

Подсчитайте количество ячеек, игнорируя нули и пустые ячейки

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

Введите эту формулу в пустую ячейку и нажмите клавишу Enter на клавиатуре.

Связанные функции

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

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

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

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

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

В этом посте рассказывается, как извлекать уникальные элементы из заданного списка в Microsoft Excel. Как создать новую формулу для получения уникальных значений из ячеек диапазона в Excel. Уникальный список элементов — это .

Функция ЕСЛИ часто используется в листе Excel для возврата ожидаемого «истинного значения» или «ложного значения» на основе результата созданного логического теста. Если вы хотите увидеть, соответствует ли ячейка определенной текстовой строке, такой как «Выигрыш», вы .

Функция ЕСЛИ часто используется на листе Excel для возврата «истинного значения» или «ложного значения» на основе результата логического теста. Если вы хотите увидеть, содержит ли ячейка определенную подстроку 1, например «abc», или подстроку 2, например «def», и возвращает true .

Если вы хотите проверить значения, чтобы увидеть, начинаются ли они с определенных символов, таких как «x», «y» или «z», вы можете создать формулу с функциями COUNTIF и SUM для возврата результатов. ПРИМЕР Вы можете увидеть «ИСТИНА» или .

Обычно мы применяем функцию СРЗНАЧ или соответствующие функции, чтобы получить среднее значение непосредственно на листе Excel. Но в некоторых ситуациях применение только средних релевантных функций не может решить нашу проблему. Иногда мы можем создать формулу с функциями и математическими операциями.

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

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

Что на этой странице

1. Подсчет непустых ячеек — функция СЧЁТЕСЛИ

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

Формула в ячейке D3:

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

В данном случае "<>" означает "не равно" и "ничего", поэтому функция СЧЁТЕСЛИ подсчитывает количество ячеек, которые не равны ничему. Другими словами, непустые ячейки.

2. Подсчет непустых ячеек — функция СЧЕТЧИК

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

Формула в ячейке D4:

3. Функции COUNTIF и COUNTA возвращают неожиданные результаты

Однако бывают ситуации, когда функции СЧЁТЕСЛИ и СЧЁТЕСЛИ возвращают неожиданные результаты, если вы не знаете, как они работают.

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

Ячейки C5 и C9 содержат формулу, которая возвращает пустое значение, и функции СЧЁТЕСЛИ, и СЧЁТЕСЛИ считают эти ячейки непустыми.

Ячейка C8 содержит два символа пробела, а ячейка C12 — один символ пробела. Столбец E показывает их наличие, подсчитывая длину символа. Функции COUNTIF и COUNTA также считают эти ячейки непустыми.

4. Подсчет непустых ячеек - функция СУММПРОИЗВ

Следующая формула подсчитывает все непустые значения в диапазоне ячеек C3:C13, за исключением формул, которые ничего не возвращают. Он проверяет, не равны ли значения в диапазоне ячеек C3:C13 нулю.

Формула в ячейке B16:

4.1 Объяснение формулы в ячейке B16

Шаг 1. Проверьте, не пусты ли ячейки

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

Символы "меньше" и "больше" — это логические операторы, результатом которых всегда являются логические значения.

Шаг 2. Преобразование логических значений

Функция СУММПРОИЗВ не может суммировать логические значения, нам нужно умножить на единицу, чтобы создать массив, содержащий 0 (ноль) и 1.

Они являются их числовыми эквивалентами:
True = 1
FALSE = 0 (ноль)

Шаг 3. Суммирование чисел

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

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

и возвращает 9 в ячейке B16.

5. Формулы, которые ничего не возвращают, считаются пустыми, а символы пробела также пустыми

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

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