Подсчет количества непустых ячеек в столбце за определенную дату.

[kaufman]

Здравствуйте. Просьба не судить строго, ибо возможно для кого-то данная задача окажется очень простой. Вот суть задачи:  есть два столбца "А" и "В", столбец "А" содержит даты и имеет формат "дата", столбец "В" содержит произвольный текст в ячейках и имеет формат "текст", но некоторые ячейки могут быть пустыми. Нужно подсчитать количество заполненных ячеек в столбце "В" по месяцам, с учетом даты в столбце "А".  Т.е. в отдельных ячейках подсчитывается количество заполненных ячеек столбца "В" за январь, февраль, март и т.д. Во вложении пример того, как это должно выглядеть. Есть функция СЧЕТЕСЛИ(В:В;"<>"&""), но она считает все заполненные ячейки столбца, как её можно переписать для решения? Ну или может есть другие функции, позволяющие решить данную задачу?

[eeigor]

=SUMPRODUCT($B$2:$B$8<>"";TEXT($A$2:$A$8;"MMMM")=$C2)

Upd1:
В файле добавлен ещё один способ получения итогов через Multiple Operations: таблица подстановки с одним входом (параметром).
Для корректировки диапазонов вы можете использовать функцию СМЕЩ(OFFSET) или задать диапазон с заведомо большей нижней границей.
Upd2:
Плюс сводная таблица. Однако, тут я... "в стадии освоения". Возможности очень малы...

[kaufman]

Спасибо большое, а возможно ли такое реализовать, если вообще убрать столбец "С" с названиями месяцев (как в прикрепленном файле, ну т.е. убрать привязку к месяцам, а отталкиваться только от дат в столбце "А")?

P.S.: понял только как работает формула - =SUMPRODUCT($B$2:$B$8<>"";TEXT($A$2:$A$8;"MMMM")=$C2), что идет дальше затруднительно. Если есть возможность, можно более подробно расписать, что происходит в других столбцах ( формулы - =Ф.ТЕКСТ(G1)&" →", или Multiple Operation). Ну ссылки, где про это можно почитать с примерами, желательно...

[eeigor]

Поправил сводную таблицу: теперь ОК

возможно ли такое реализовать, если вообще убрать столбец "С" с названиями месяцев... а отталкиваться только от дат в столбце "А"

Думаю, что этот простой для Excel вопрос здесь не решён. Могу ошибаться (?). Я не использую сводные таблицы в виду их малой функциональности. Но в данном примере всё вполне приемлемо, кроме форматирования (жирных рамок и отсутствия возможности сохранить форматирование).
В частности, столбец C здесь нужен только для решения со сводной таблицей. В формуле SUMPRODUCT он не используется. Удалите столбец C вместе со сводной таблицей.

Upd1:

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

Я постарался ответить на ваш вопрос. Всё это непростые темы. Но теперь вы знаете, что искать. Ответы ищите в книгах Уокенбаха (или на его принципах) по Excel.

Upd2:
Я обновил файл. Загрузите ещё раз.
Формула =IF(ISBLANK(A2);"";DATE(YEAR(A2);MONTH(A2);"1"))
предполагает, что данные будут выходить за пределы одного года (используется при группировке дат по месяцам в сводной таблице).

[kaufman]

Спасибо, буду разбираться.

[eeigor]

@kaufman, вот, например, ссылка на таблицы данных или, по другому, подстановки (то, что здесь назвали MULTIPLE.OPERATIONS – в Excel это формула ТАБЛИЦА). В моём примере – с одним входом (параметром или переменной «месяц»). ...если это прибавит ясности. Это целый скрытый механизм работы, и выполняемых операций действительно много. Но сложность всё-таки не в этом, а в понимании операций с массивами, и вы, как написали, разобрались с работой функции СУММПРОИЗВ (SUMPRODUCT). Это главное. В таблице подстановки используется именно она, только много раз, поэтому операции множественные (multiple*).

Upd1:
* Впрочем, этот термин может означать и другое: множественные операции с данными и ячейками, которые остаются «за кадром».

[bigor]

Есть функция СЧЕТЕСЛИ

Код Выделить Развернуть

=COUNTIFS($B$2:$B$8;"<>";$A$2:$A$8;">="&C26;$A$2:$A$8;"<="&EOMONTH(C26;0))

месяц вводится как 01.01.2021 и форматом ячейки выводим только месяц
см ячейки залитые желтым

[eeigor]

@Bigor, тоже оригинально реализовано условие BETWEEN:
$A$2:$A$8;">="&C26;$A$2:$A$8;"<="&EOMONTH(C26;0)

[eeigor]

Пожалуй, собраны все решения. В стороне осталось только одно:
промежуточные итоги с условием (subtotal with condition*), но это больше похоже на трюк, а сами данные требуют сортировки по дате. Зато данные будут собраны в группы.

Upd1:
Ответ на это решение размещён здесь, если кому-то интересно (автор Laurent Longre).

[kaufman]

А можно в формулах указывать диапазон ячеек от заданной (допустим "А1" до последней заполненной на листе допустим "А8"). Если на листе появляется заполненная ячейка "А9", то диапазон автоматически в формулах становится как "А1:А9" вместо "А1:А8"? Т.е. возможно ли задавать диапазон ячеек в формулах типа "А1:ENDLIST", просто данных много с такими формулами, и если указывать весь диапазон ("А:А" или "А1:А1048576"), то libre office calc очень сильно тормозит.

[economist]

И Calc и Excel неплохо оптимизированы для обработки "целых столбцов", т.е. формулы

=СУММ("A:A") - весь столбец
=СУММ("A1:A10") - реально заполненные данные
=СУММ("A1:A1000") - с "запасом" на несколько десятилетий

будут работать одинаково быстро. Это же касается всех простых функций суммирования:
СУММЕСЛИ (СЧЁТЕСЛИ)
СУММЕСЛИМН (итд)

Но СУММПРОИЗВ, а также все функции ссылки, автоподстановки - будет тупить. Большинство советов от MS будут полезны в части "ускорения":

https://docs.microsoft.com/ru-ru/office/vba/excel/concepts/excel-performance/excel-tips-for-optimizing-performance-obstructions#minimize-the-used-range

[eeigor]

Чем вас не устраивает диапазон «с запасом»?
A2:A999. Только ниже ничего не размещать. Можно использовать функцию СМЕЩ (OFFSET)*, тогда диапазон будет динамическим, как вы просите, но эта функция волатильная или, как их здесь называют, – «летучая», то есть пересчитываемая при любом обновлении данных, даже не затрагиваемом зависимые с ней ячейки. Это плохо, но при умеренном объёме данных не критично. Со сводной таблицей не работает: там можно использовать только абсолютные ссылки, в отличие от Excel.

Upd1:
* Хотя есть и неволатильная вариация с функцией ИНДЕКС (INDEX), но это тоже из области «сложного для восприятия». Может и приведу пример, но самому требуется обратиться к «источнику». Вам это надо (неволатильный динамический диапазон без всяких «запасов»)?

[kaufman]

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

[bigor]

Это как бы другой вопрос, нехорошо объединять все в одну тему. И хотелось бы пример в формате ods

[economist]

символы "+" или "-", нужно в соседнюю ячейку вывести сумму значений

СЧЁТЕСЛИ()