Рекурсивное округление в SUM

Автор petav, 27 июля 2021, 06:23

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

petav

Дано:
Столбец А со значениями "Duration(sec)":
300
480
300
240
300
240
480
240
780
660
840
300
600
300
960
900
1800
480
2100

Необходимо:
1) каждое значение округлить до 300
=CEILING.MATH(A_;300)
2) Сложить
=SUM(A_:A_)

Выполнить в одно действие
Возможно?

bigor

#1
Добрый день
Попробуйте так
=SUM(CEILING.MATH(A1:A10;300)) массивная вводится 3 мы кнопками ctrl+shift+enter
Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут

sokol92

Или "обычную" формулу

=SUMPRODUCT(CEILING.MATH(A1:A19;300))
Владимир.

petav


petav

=SUMPRODUCT(CEILING.MATH($Tickets.C4:C999;300))

В формуле нужно указать диапазон от С4 до "пока есть данные"
Такое возможно?

eeigor

#5
Да, возможно, если в заданном диапазоне (с запасом*, например $C$999) вычислить последнюю ячейку с данными, используя комбинацию (схематично):
$C$4:INDEX(...;MATCH(...)). Критерий поиска: число, заведомо большее тех, что встречаются в вашем наборе данных. 9,99E+307 – самое большое число.

Другой вариант: через функцию OFFSET, где для определения нижней границы нужно вычислить количество непустых строк в столбце C, вычев из него количество "иных" непустых строк над диапазоном данных этого столбца (заголовок и пр.).

В обоих случаях нельзя помещать постороннюю информацию под данными (снизу). Иначе «поиск/счёт» будет неверным.

UPD:
* «Запас» влияет на быстродействие (сколько ячеек просматривать).

Вместо C4:C999
вставьте:
$C$4:INDEX($C$4:$C$999;MATCH(9,99E+307;$C$4:$C$999))
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

petav

Цитата: petav от  7 августа 2021, 01:40=SUMPRODUCT(CEILING.MATH($Tickets.C4:C999;300))
Цитата: eeigor от  7 августа 2021, 07:25Вместо C4:C999
вставьте:
$C$4:INDEX($C$4:$C$999;MATCH(9,99E+307;$C$4:$C$999))

Libre Office Версия: 5.2.7.2
Лист "Billing"
Ячейка "B3"
Заменил с:
=SUMPRODUCT(CEILING.MATH($Tickets.C4:C9999;300))
600

на:
=SUMPRODUCT(CEILING.MATH($Tickets.$C$4:INDEX($C$4:$C$999;MATCH(9,99E+307;$C$4:$C$999));300))
#Н/Д

Файл https://disk.yandex.ru/i/rxIjWjQlQn3fEw



eeigor

#7
Цитата: petav от  8 августа 2021, 01:38#Н/Д
В представленном вами примере данные отсутствуют.
Я начал вводить данные в столбце C, начиная с ячейки C4, и появился результат.
Файл Excel был открыт мной в среде, указанной в подписи.

Цитата: petav от  7 августа 2021, 01:40В формуле нужно указать диапазон от С4 до "пока есть данные"
Такое возможно?
Ответ был дан на этот вопрос. В суть вашей "математики" и структуры листа не вникал.
Вы можете убедиться сами, если введете в любую ячейку формулу:
=INDEX($C$4:$C$999;MATCH(9,99E+307;$C$4:$C$999))
Эта формула покажет последнее число в столбце. Попробуйте вводить данные в столбце.


Функция INDEX возвращает поддиапазон, для которого указаны номер строки и столбца или имя диапазона. В зависимости от контекста, функция INDEX возвращает ссылку или значение.

Функцию INDEX можно добавлять после двоеточия (возвращает ссылку). Это плохо документированная возможность. Именно она и была использована.
Я заглянул в документацию: примера использования нет. Увы.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

petav

Цитата: eeigor от  8 августа 2021, 02:54В представленном вами примере данные отсутствуют.
Данные на втором листе с именем "Tickets"

Файл https://disk.yandex.ru/i/rxIjWjQlQn3fEw
Такая запись формулы на листе Billing в ячейке B3:
=SUMPRODUCT(CEILING.MATH($Tickets.C4:INDEX($Tickets.C4:$Tickets.C999;MATCH(9,99E+307;$Tickets.C4:$Tickets.C999));300))
Берет данные со второго листа Tickets начиная с ячейки C4

Спасибо за помощь

eeigor

#9
Цитата: petav от  8 августа 2021, 04:18Берет данные со второго листа Tickets начиная с ячейки C4
Вы правильно дополнили ссылку именем листа, и у вас всё заработало. Для упрощения чтения ссылки желательно присвоить имя диапазону $Tickets.$C$4:$Tickets.$C$999 (например "Данные"). Тогда читается проще:
=SUMPRODUCT(CEILING.MATH($Tickets.$C$4:INDEX(Данные;MATCH(9,99E+307;Данные));300))

Вы даже можете присвоить это имя всей конструкции:
$Tickets.$C$4:INDEX($Tickets.$C$4:$Tickets.$C$999;MATCH(9,99E+307;$Tickets.$C$4:$Tickets.$C$999))
Тогда:
=SUMPRODUCT(CEILING.MATH(Данные;300))

Вместо 9,99E+307 можете ввести, как уже говорил, любое число, заведомо превосходящее ваши числа, например миллион (10^6). Смысл в том, что поиск должен "пройти" всё до конца и, не найдя ничего, остановиться на последнем значении, что вам и требуется ("пока есть данные"). Однако для меня число 9,99E+307 красноречиво "сигнализирует" о применяемом "трюке".

Цитата: eeigor от  8 августа 2021, 02:54Функцию INDEX можно добавлять после двоеточия (возвращает ссылку).
Ещё раз повторю, что этот приём не документирован.

Цитата: eeigor от  7 августа 2021, 07:25Другой вариант: через функцию OFFSET, где для определения нижней границы нужно вычислить количество непустых строк в столбце C, вычев из него количество "иных" непустых строк над диапазоном данных этого столбца (заголовок и пр.).
В отличие от функции OFFSET функция INDEX не является "летучей", то есть изменчивой (volatile), и не будет пересчитываться при каждом изменении данных, что эффективнее.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community