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

[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]

Добрый день
Попробуйте так

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

=SUM(CEILING.MATH(A1:A10;300)) массивная вводится 3 мы кнопками ctrl+shift+enter

[sokol92]

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

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

=SUMPRODUCT(CEILING.MATH(A1:A19;300))

[petav]

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

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

=SUMPRODUCT(CEILING.MATH(A1:A19;300))

Спасибо!

[petav]

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

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

[eeigor]

Да, возможно, если в заданном диапазоне (с запасом*, например $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))

[petav]

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

Вместо 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]

#Н/Д

В представленном вами примере данные отсутствуют.
Я начал вводить данные в столбце C, начиная с ячейки C4, и появился результат.
Файл Excel был открыт мной в среде, указанной в подписи.

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

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


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

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

[petav]

В представленном вами примере данные отсутствуют.

Данные на втором листе с именем "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]

Берет данные со второго листа 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 красноречиво "сигнализирует" о применяемом "трюке".

Функцию INDEX можно добавлять после двоеточия (возвращает ссылку).

Ещё раз повторю, что этот приём не документирован.

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

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