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

Автор kaufman, 7 февраля 2021, 18:41

0 Пользователи и 1 гость просматривают эту тему.

kaufman

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

eeigor

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

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

kaufman

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

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

eeigor

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

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

Upd1:
Цитата: kaufman от  7 февраля 2021, 18:41Ну или может есть другие функции, позволяющие решить данную задачу?
Я постарался ответить на ваш вопрос. Всё это непростые темы. Но теперь вы знаете, что искать. Ответы ищите в книгах Уокенбаха (или на его принципах) по Excel.

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

kaufman


eeigor

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

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

bigor

Цитата: kaufman от  7 февраля 2021, 18:41Есть функция СЧЕТЕСЛИ

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

месяц вводится как 01.01.2021 и форматом ячейки выводим только месяц
см ячейки залитые желтым
Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут

eeigor

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

:beer:
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

eeigor

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

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

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), но это тоже из области «сложного для восприятия». Может и приведу пример, но самому требуется обратиться к «источнику». Вам это надо (неволатильный динамический диапазон без всяких «запасов»)?
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

kaufman

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

bigor

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

economist

Цитата: kaufman от  8 февраля 2021, 10:16символы "+" или "-", нужно в соседнюю ячейку вывести сумму значений

СЧЁТЕСЛИ()
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...