Как посчитать количество строк в excel

Как посчитать количество строк в Excel

Функция КОЛИЧЕСТВОСТРОК возвращает номер последней заполненной строки в Excel. Определяет количество строк как текущего листа, так и заданного.

Описание функции

Функция =КОЛИЧЕСТВОСТРОК( [ССЫЛКА] ) имеет один необязательный аргумент.

• [ССЫЛКА] — Ссылка на любую ячейку листа, в котором необходимо посчитать количество строк. По умолчанию (если аргумент не указан) функция применяется к активному листу.

Ниже приведен пример работы данной формулы.

Пример

Определение количества строк на текущем листе.

Код на VBA

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

Function КОЛИЧЕСТВОСТРОК(Optional ССЫЛКА As Variant) As Long Dim rng As Range If IsMissing(ССЫЛКА) Then Set rng = ActiveCell Else Set rng = ССЫЛКА End If КОЛИЧЕСТВОСТРОК = rng.Parent.UsedRange.row - 1 + rng.Parent.UsedRange.Rows.Count End Function

Надстройка
VBA-Excel

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

Функция ЧСТРОК возвращает количество строк в диапазоне Excel

Функция ЧСТРОК в Excel предназначена для определения числа строк, содержащихся в константе массива или числа строк в диапазоне ячеек, ссылка на который передана в качестве аргумента этой функции, и возвращает определенное числовое значение.

Как посчитать количество строк листа в Excel

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

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

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

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

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

Пример 2. На листе Excel находится таблица, первый столбец которой (id) находится в столбце A:A. Определить число пустых строк между началом листа и шапкой таблицы.

Вид части листа с данными:

Для определения искомого значения используем формулу:

С помощью функции ПОИСКПОЗ определяем номер строки, в которой находится значение названия первого столбца таблицы. Функция ИНДЕКС возвращает ссылку на ячейку, в которой было определено это значение. Поскольку эта функция возвращает данные ссылочного типа, в результате вычислений выражение типа A1:ИНДЕКС принимает вид A1:A10. Поскольку нас интересуют только пустые строки, вычитаем 1 из значения, найденного функцией ЧСТРОК.

Правила использования функции ЧСТРОК в Excel

Функция ЧСТРОК имеет следующую синтаксическую запись:

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

1. Если в качестве аргумента функции передано числовое значение, оно будет интерпретировано как константа массива с одним элементом, поэтому функция ЧСТРОК вернет значение 1. Например, результат выполнения =ЧСТРОК(5) будет 1.
2. Если аргумент функции указан в виде логических или текстовых данных, рассматриваемая функция вернет код ошибки #ЗНАЧ!
3. При использовании констант массивов для разделения строк используют знак «:». Например, константа массива содержит 3 строки.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Как посчитать количество видимых строк в Excel

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

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ может выполнять вычисления, как СЧЁТ, СУММ, МАКС, МИН, и многие другие.

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

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

Если вы скрываете строки вручную (т.е. правой кнопкой мыши, Скрыть), а не с помощью автоматического фильтра используйте эту версию вместо той:

Только с критериями

=СУММПРОИЗВ((диапазон=критерий)*( ПРОМЕЖУТОЧНЫЕ.ИТОГИ (3; СМЕЩ (диапазон; ЧСТРОК;0;1))))

Для подсчета видимых строк только с критериями, вы можете использовать довольно сложную формулу, основанную на СУММПРОИЗВ, ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СМЕЩ.

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

Решение состоит в том, чтобы использовать СУММПРОИЗВ, применив с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ (через СМЕЩ) и критерии. В показанном примере формула в С12:

=СУММПРОИЗВ ((C5:C8 = С10) * (ПРОМЕЖУТОЧНЫЕ.ИТОГИ (103;СМЕЩ(C5;СТРОКА(C5:C8) — МИН(СТРОКА(C5:C8));0))))

Суть этой формулы вычисление массива внутри СУММПРОИЗВ. Первый массив применяет критерии, а второй массив обрабатывает «проблему видимости».

Критерии применяется с частью формулы:

Который генерирует массив следующим образом:

Где ИСТИНА означает «отвечает критериям». Обратите внимание, что поскольку мы используем умножение (*) внутри первого (и только) массива, значения ИСТИНА/ЛОЖЬ будут автоматически преобразованы:

Для учета видимости применяется фильтр с использованием ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

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

Хитрость заключается в том, чтобы использовать СМЕЩ, подающую ПРОМЕЖУТОЧНЫЕ.ИТОГИ одну ссылку на строку, так что смещение будет рассчитывать один результат для каждой строки.

Конечно, для этого требуется еще один трюк, который должен дать СМЕЩ массив, содержащий один номер для каждой строки, начиная с нуля. Мы делаем это с помощью:

Что будет генерировать массив вроде этого:

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

= ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ (C5;СТРОКА(C5: C8) — МИН(СТРОКА(C5: C8)); 0))

покупка

Подсчитать количество строк, содержащих определенные значения в Excel

Нам может быть легко подсчитать количество ячеек с определенным значением на листе Excel. Однако получить количество строк, содержащих определенные значения, может быть довольно сложно. В этом случае более сложная формула, основанная на функциях СУММ, ММУЛЬТИ, ТРАНСПОРТ и СТОЛБЕЦ, может оказать вам услугу. В этом руководстве будет рассказано о том, как создать эту формулу для решения этой задачи в Excel.

• Подсчитать количество строк, содержащих определенные значения

Подсчитать количество строк, содержащих определенные значения

Например, у вас есть диапазон значений на листе, и теперь вам нужно подсчитать количество строк с заданным значением «300», как показано ниже:

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

<=SUM(–(MMULT(–(data=X),TRANSPOSE(COLUMN(data)))>0))>
Array formula, should press Ctrl + Shift + Enter keys together.

• data : Диапазон ячеек, которые нужно проверить, содержат ли они определенное значение;
• X : Конкретное значение, которое вы используете для подсчета строк.

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

=SUM(—(MMULT(—($A$2:$C$12=300),TRANSPOSE(COLUMN($A$2:$C$12)))>0))

2, Затем нажмите Shift + Ctrl + Enter вместе, чтобы получить правильный результат, см. снимок экрана:

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

=SUM(—(MMULT(—($A$2:$C$12=300),TRANSPOSE(COLUMN($A$2:$C$12)))>0))

• — 2 австралийских доллара: 12 канадских долларов = 300 : Это выражение проверяет, существует ли значение «300» в диапазоне A2: C12, и сгенерирует результат массива ИСТИНА и ЛОЖЬ. Двойной отрицательный знак используется для преобразования ИСТИНА в 1 и ЛОЖЬ в 0. Итак, вы получите следующий результат: . Массив, состоящий из 3 строк и 1 столбцов, будет работать как arrayXNUMX в функции MMULT.
• ТРАНСПОРТ (КОЛОНКА (2 доллара США: 12 канадских долларов)) : Функция COLUMN здесь используется для получения номера столбца диапазона A2: C12, она возвращает массив из 3 столбцов, например: . Затем функция TRANSPOSE меняет этот массив на трехстрочный массив , который функционирует как array3 в функции MMULT.
• MMULT (- ($ A $ 2: $ C $ 12 = «Джоанна»), TRANSPOSE (COLUMN ($ A $ 2: $ C $ 12))) : Эта функция MMULT возвращает матричное произведение двух вышеуказанных массивов, вы получите следующий результат: .
• SUM(—(MMULT(—($A$2:$C$12=»Joanna»),TRANSPOSE(COLUMN($A$2:$C$12)))>0))= SUM(—>0) : Сначала проверьте значения в массиве больше 0: Если значение больше 0, отображается ИСТИНА; если меньше 0, отображается ЛОЖЬ. Затем двойной отрицательный знак заставляет ИСТИНЫ и ЛОЖЬ равняться 1 и 0, поэтому вы получите следующее: СУММ (). Наконец, функция СУММ суммирует значения в массиве и возвращает результат: 6.

Советы:

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

=SUM(—(MMULT(—(ISNUMBER(SEARCH(«Joanna»,A2:C12))),TRANSPOSE(COLUMN($A$2:$C$12)))>0))

Используемая относительная функция:

• СУММА:
• Функция СУММ в Excel возвращает сумму предоставленных значений.

• МУЛЬТИ:
• Функция Excel MMULT возвращает матричное произведение двух массивов.

• ТРАНСПОРТ:
• Функция TRANSPOSE вернет массив в новой ориентации на основе определенного диапазона ячеек.

• КОЛОНКА:
• Функция COLUMN возвращает номер столбца, в котором отображается формула, или номер столбца для данной ссылки.

Другие статьи:

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

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

• Подсчитать количество ячеек равно одному из многих значений
• Предположим, у меня есть список продуктов в столбце A, теперь я хочу получить общее количество конкретных продуктов Apple, Grape и Lemon, которые перечислены в диапазоне C4: C6 из столбца A, как показано на скриншоте ниже. Обычно в Excel простые функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН не работают в этом сценарии. В этой статье я расскажу о том, как быстро и легко решить эту задачу с помощью комбинации функций СУММПРОИЗВ и СЧЁТЕСЛИ.