Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

30 Июль 2021, 16:59 *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

Войти
Новости: Доступно и просто о работе в офисных пакетах
 
   Начало   Помощь Поиск Войти Регистрация    задать вопрос  
Страниц: 1 2 »   Вниз
  Печать  
Автор Тема: Подсчет количества непустых ячеек в столбце за определенную дату.  (Прочитано 1162 раз)
0 Пользователей и 1 Гость смотрят эту тему.
kaufman
Участник
**
Offline Offline

Сообщений: 6


« Стартовое сообщение: 7 Февраль 2021, 18:41 »

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


* текст.PNG (6.63 Кб, 441x176 - просмотрено 18 раз.)
Записан
eeigor
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 626



« Ответ #1: 7 Февраль 2021, 19:54 »

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

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


* Снимок экрана от 2021-02-07 19-52-46.png (36.18 Кб, 845x258 - просмотрено 18 раз.)

* Снимок экрана от 2021-02-07 20-51-56.png (89.61 Кб, 545x347 - просмотрено 16 раз.)
* ex-countif-on-month.ods (16.97 Кб - загружено 5 раз.)
« Последнее редактирование: 7 Февраль 2021, 22:08 от eeigor » Записан

Ubuntu 18.04 LTS • LO 7.1.3.2 Community
kaufman
Участник
**
Offline Offline

Сообщений: 6


« Ответ #2: 7 Февраль 2021, 22:17 »

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

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


* изображение_2021-02-07_221141.png (5.14 Кб, 357x182 - просмотрено 11 раз.)
« Последнее редактирование: 7 Февраль 2021, 22:19 от kaufman » Записан
eeigor
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 626



« Ответ #3: 7 Февраль 2021, 22:43 »

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

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

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

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

* ex-countif-on-month.ods (16.74 Кб - загружено 9 раз.)
« Последнее редактирование: 8 Февраль 2021, 08:13 от eeigor » Записан

Ubuntu 18.04 LTS • LO 7.1.3.2 Community
kaufman
Участник
**
Offline Offline

Сообщений: 6


« Ответ #4: 7 Февраль 2021, 23:11 »

Спасибо, буду разбираться.
Записан
eeigor
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 626



« Ответ #5: 8 Февраль 2021, 06:14 »

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

Upd1:
* Впрочем, этот термин может означать и другое: множественные операции с данными и ячейками, которые остаются «за кадром».
« Последнее редактирование: 8 Февраль 2021, 08:16 от eeigor » Записан

Ubuntu 18.04 LTS • LO 7.1.3.2 Community
Bigor
Мастер
*****
Offline Offline

Пол: Мужской
Сообщений: 1 139


« Ответ #6: 8 Февраль 2021, 08:20 »

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

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

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

* ex-countif-on-month-2.ods (15.32 Кб - загружено 5 раз.)
Записан

Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут
eeigor
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 626



« Ответ #7: 8 Февраль 2021, 08:36 »

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

Всё хорошо
« Последнее редактирование: 8 Февраль 2021, 08:40 от eeigor » Записан

Ubuntu 18.04 LTS • LO 7.1.3.2 Community
eeigor
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 626



« Ответ #8: 8 Февраль 2021, 09:03 »

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

Upd1:
Ответ на это решение размещён здесь, если кому-то интересно (автор Laurent Longre).
« Последнее редактирование: 8 Февраль 2021, 13:18 от eeigor » Записан

Ubuntu 18.04 LTS • LO 7.1.3.2 Community
kaufman
Участник
**
Offline Offline

Сообщений: 6


« Ответ #9: 8 Февраль 2021, 09:03 »

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

Сообщений: 1 514


« Ответ #10: 8 Февраль 2021, 09:16 »

И 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
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 626



« Ответ #11: 8 Февраль 2021, 09:23 »

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

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

Ubuntu 18.04 LTS • LO 7.1.3.2 Community
kaufman
Участник
**
Offline Offline

Сообщений: 6


« Ответ #12: 8 Февраль 2021, 10:16 »

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


* 1.PNG (3.07 Кб, 362x160 - просмотрено 14 раз.)
Записан
Bigor
Мастер
*****
Offline Offline

Пол: Мужской
Сообщений: 1 139


« Ответ #13: 8 Февраль 2021, 10:20 »

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

Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут
economist
Форумчанин
***
Offline Offline

Сообщений: 1 514


« Ответ #14: 8 Февраль 2021, 10:27 »

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

СЧЁТЕСЛИ()
Записан

Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...
Страниц: 1 2 »   Вверх
  Печать  
 
Перейти в:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.21 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!