OpenOffice/Libre Office Calc - подсчет количества записей с непустыми полями

Содержание

Вопрос или проблема
Ответ или решение
Шаги для решения задачи:
Завершение:

Вопрос или проблема

Мне пришлось использовать Calc вместо моего любимого и хорошо известного Excel, потому что мы используем открытый исходный код, бесплатно и так далее.
И мне нужна помощь. У меня есть таблица со структурой:
Столбец A – название группы;
Столбец B – подгруппы – от 1 до 7 на группу;
Столбцы C до CF – дни мероприятий с рабочими часами.

Мне нужно просто сосчитать, сколько подгрупп в каждой группе работают в конкретном году (в таблице).
Если любое поле рабочих часов в диапазоне C:AF больше 0, это означает, что подгруппа работает в этом году (хотя бы один раз). Мой результат должен быть единственным числом для каждой группы, т.е. 5 для Группы A, 0 для Группы C и так далее.

Есть ли у вас идея, как это рассчитать элегантно (под этим я имею в виду способ, который позволяет мне просто перетащить формулу вниз), не написав очень большие формулы с множественными вложенными “countif() и sum()?
Я пробовал с sum.product(), но не знаю, как это использовать в этом случае.

Есть ли какие-нибудь советы?

Это массивная формула, так что

выберите достаточное количество ячеек для размещения результата
введите следующее в первую ячейку
нажмите Control + Shift + Enter (Windows) или Command + Shift + Enter (Mac)
в примере я выбрал A13:B15, ввел формулу в A13 и скорректировал диапазон, чтобы исключить значения #N/A

=LET(
    hours, C2:CF9,
    group_and_sub, A2:B9,
    sum_hours, MMULT(hours + 0, SEQUENCE(COLUMNS(hours), , , 0)),
    with_active_subs, FILTER(group_and_sub, sum_hours),
    u_group, UNIQUE(INDEX(with_active_subs, , 1)),
    u_row_indices, SEQUENCE(ROWS(u_group)),
    sub_group_count, INDEX(
        FREQUENCY(XMATCH(INDEX(with_active_subs, , 1), u_group), u_row_indices),
        u_row_indices,
        1
    ),
    CHOOSE({1, 2}, u_group, sub_group_count)
)

FREQUENCY возвращает количество элементов в контейнерах + 1, поэтому, используя INDEX(freq, u_row_indices,1), мы исключаем последнюю строку
Так как HSTACK недоступен, мы можем использовать CHOOSE, чтобы расположить массивы рядом друг с другом

Ответ или решение

Вам нужно подсчитать количество подгрупп в каждой группе, которые работают в определенном году, используя Calc из пакетного приложения OpenOffice или LibreOffice. Основная задача заключается в определении, есть ли хотя бы одно рабочее время в диапазоне C:CF, и получения итогового числа подгрупп, работающих в каждой группе. Рассмотрим, как вы можете добиться этого с помощью формул, без излишней сложности и с возможностью простого перетаскивания формулы вниз.

Шаги для решения задачи:

Подготовка таблицы:
Убедитесь, что ваша таблица настроена правильно. Колонка A содержит названия групп, колонка B – подгруппы, а колонки C до CF – рабочие часы.
Использование формул:
Мы воспользуемся комбинацией функций UNIQUE, FILTER, и COUNTIF для вычисления поставленной задачи более элегантно. Формула будет выглядеть следующим образом:
```
=SUMPRODUCT((C2:CF9>0)*(A2:A9=UNIQUE(A2:A9)))
```
Давайте разберем эту формулу и объясним, как она работает:
- C2:CF9>0: Проверяет, есть ли рабочие часы, больше нуля.
- (A2:A9=UNIQUE(A2:A9)): Сравнивает группы с уникальными значениями групп, чтобы убедиться, что считается только один раз для каждой группы.
- SUMPRODUCT: Суммирует результаты перемножения массивов, что дает заветные количества подгрупп.
Динамическое количество подгрупп:
Чтобы получить число подгрупп для каждой группы, можно использовать следующую формулу:
```
=COUNTIF(FILTER(B2:B9, (C2:CF9>0)*(A2:A9=A2)), "<>") 
```
- FILTER: Отбирает только те подгруппы из колонки B, которые соответствуют текущей группе в колонке A и где соответствующие рабочие часы больше 0.
- COUNTIF: Подсчитывает количество не пустых значений, возвращаемых функцией FILTER.
Применение формулы:
Введите первую формулу в ячейку, чтобы получить число подгрупп для первой группы, и затем перетащите её вниз для остальных групп.

Завершение:

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

Дополнительные ссылки и инструкции по работе с Calc вы сможете найти в официальной документации LibreOffice или OpenOffice.

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

OpenOffice/Libre Office Calc – подсчет количества записей с непустыми полями

Вопрос или проблема

Ответ или решение

Шаги для решения задачи:

Завершение: