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

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

23 Сентябрь 2021, 15:55 *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

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

Сообщений: 9


« Стартовое сообщение: 27 Июль 2021, 06:23 »

Дано:
Столбец А со значениями "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
Мастер
*****
Offline Offline

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


« Ответ #1: 27 Июль 2021, 12:33 »

Добрый день
Попробуйте так
Код:
=SUM(CEILING.MATH(A1:A10;300))
массивная вводится 3 мы кнопками ctrl+shift+enter
« Последнее редактирование: 27 Июль 2021, 12:35 от Bigor » Записан

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

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


WWW
« Ответ #2: 27 Июль 2021, 13:15 »

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

Код:
=SUMPRODUCT(CEILING.MATH(A1:A19;300))
Записан

Владимир.
petav
Участник
**
Offline Offline

Сообщений: 9


« Ответ #3: 7 Август 2021, 01:38 »

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

Код:
=SUMPRODUCT(CEILING.MATH(A1:A19;300))
Спасибо!
Записан
petav
Участник
**
Offline Offline

Сообщений: 9


« Ответ #4: 7 Август 2021, 01:40 »

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

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

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



« Ответ #5: 7 Август 2021, 07:25 »

Да, возможно, если в заданном диапазоне (с запасом*, например $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))
« Последнее редактирование: 8 Август 2021, 03:09 от eeigor » Записан

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

Сообщений: 9


« Ответ #6: 8 Август 2021, 01:38 »

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

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



« Ответ #7: 8 Август 2021, 02:54 »

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

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


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

Функцию INDEX можно добавлять после двоеточия (возвращает ссылку). Это плохо документированная возможность. Именно она и была использована.
Я заглянул в документацию: примера использования нет. Увы.
« Последнее редактирование: 8 Август 2021, 03:20 от eeigor » Записан

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

Сообщений: 9


« Ответ #8: 8 Август 2021, 04:18 »

В представленном вами примере данные отсутствуют.
Данные на втором листе с именем "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
Форумчанин
***
Offline Offline

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



« Ответ #9: 8 Август 2021, 09:30 »

Берет данные со второго листа 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), и не будет пересчитываться при каждом изменении данных, что эффективнее.
« Последнее редактирование: 8 Август 2021, 10:22 от eeigor » Записан

Ubuntu 18.04 LTS • LO 7.2.0.2 Community
Страниц: 1   Вверх
  Печать  
 
Перейти в:  

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