Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

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

И ТОГИ. Функция СУММЕСЛИ может использоваться для выполнения многих вычислений, включая СУММЕСЛИ, СУММ, МАКС, МИН и др.

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

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

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

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

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

Для расчета строк, видимых только при наличии критериев, можно использовать довольно сложную формулу, основанную на элементах SUMMARY, TOTALS и QUANTITY.

Функция TOTALS.TOTAL может легко генерировать итоги и счетчики для скрытых и не скрытых строк. Однако он не может работать с критериями (например, как TOTALS или SUMS).

Количество видимых строк с критерием

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

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

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

Частично эти критерии выглядят следующим образом:

Следующий код генерирует массив:

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

Читайте так же:
Как подсчитать количество ячеек / строк до первого пробела в Excel?

P ROMOTE применяет фильтр для учета видимости. ИТОГИ.

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

Хитрость заключается в использовании SMESH, который дает PROMOTE.TOTALS ссылку на каждую строку, так что смещение вычисляет результат для каждой строки.

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

Как можно создать такой массив:

Таким образом, второй массив, который обрабатывает видимость с помощью PROMISE. ИТОГИ получаются следующим образом:

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

Excel: Функции СЧЕТЕСЛИ и СЧЕТЕСЛИМН

Если вы хотите подсчитать количество ячеек, удовлетворяющих условию в Excel, вы можете использовать опцию Count as many as you want.

Функция Счётесли

Число подсчетов (интервал; критерий)

Функция СЧЁТЕСЛИ

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

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

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

Критерий проверки должен быть заключен в кавычки.

Критерий не чувствителен к регистру. К примеру, функция не увидит разницы между словами «налог» и «НАЛОГ».

Примеры использования функции Счётесли.

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

Счётесли(А1:С2;"<0") Диапазон — А1:С2 , критерий — "<0"

Функция СЧЁТЕСЛИ

Функция СЧЁТЕСЛИ

  1. Подсчитайте количество ячеек, значение которых больше содержимого ячейки A4 :

Функция СЧЁТЕСЛИ

  1. Подсчет количества ячеек со словом "текст" (регистр не имеет значения).

Счётесли(А1:С2;"текст") Диапазон — А1:С2 , критерий — "текст"

Читайте так же:
Как превратить лист в миллиметровку в Excel?

Функция СЧЁТЕСЛИ

  1. Для текстовых значений в критериях можно использовать символы * и ?

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

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

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

Счётесли(А1:С2;"Т * ") Диапазон — А1:С2 , критерий — "Т * "

Функция СЧЁТЕСЛИ

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

Счётесли(А1:С2;". ") Диапазон — А1:С2 , критерий — ". "

Функция СЧЁТЕСЛИ

В функции Count If используется только один критерий.

Функция Countslimn может быть использована для подсчета нескольких условий.

Функция Счётеслимн

Счётеслимн (диапазон1; условие1; [диапазон2]; [условие2]; …).

Функция Счётеслимн

Функция аналогична функции Счётеслимн, за исключением того, что может содержать до 127 диапазонов и условий, где первое является обязательным, а последующие – нет.

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

Пример использования:

  1. Подсчитайте количество ячеек, содержащих даты определенного периода (например, после 15 января и до 1 марта 2015 года).

Диапазон один — C1:C8 , условия — ">15.01.2015" и "<1.03.2015"

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector