Суммируем колонку блоками по общему ID, округляя блок до 300

[eeigor]

Присвоить имя конструкции, ссылающейся на лист? У меня такое работает только на том же листе (и в случае, когда конструкция локальна для этого листа, и когда она глобальна).

У меня всё работает как в MS Excel, так и в LO Calc. Никаких препятствий (особенностей) не вижу.

[sokol92]

квадратичные алгоритмы

Бывает гораздо  хуже.  Например, при решении системы линейных уравнений стандартными методами время O(N^3).

[mikekaganski]

У меня всё работает как MS Excel, так и в LO Calc. Никаких препятствий (особенностей) не вижу.

Вот файл, в котором заданы именованные выражения глобальной видимости, которые использованы в формулах Billing.B3, Billing.B4, Tickets.A95 и Tickets.A96. У меня на листе Billing получается 0 и #N/A - а на листе Tickets они работают.

И забавнее всего - они перестали работать после сохранения и переоткрытия (причём не только XLSX, но и ODS). Версия 7.2.0.2 - проверю на регрессию.

P.S. Нестыковки с именами диапазонов - с именем листа вначале, без имени в конце - артефакт.

[eeigor]

Но понял ли автор как работает предложенная ему формула?
Вот результат вычислений (округление опущено для ясности) на примере данных из файла – SUMIFS/COUNTIFS:

=SUMPRODUCT({720:420:540:300:300:480:1800:1200:1200:1200:2820:2820:2820:2820:2160:2160:2160:2160:300:240:4680:4680:4680:4680:4680:2880:2880:2880:2880:2880:540:2040:2040:2040:2040:2220:2220:780:600:600:600:4800:4800:4800:4800:4800:4800:300:1200:1200:540:540:720:720:540:720:480:720:480:840:840:840:300:1080:1080:1080:1320:1320:1320:720:1260:1260:1260:1260:480:480:300:1260:1260:1260:1260:780:780:900:360:1200:1200}/{1:1:1:1:1:1:1:3:3:3:4:4:4:4:4:4:4:4:1:1:5:5:5:5:5:5:5:5:5:5:1:4:4:4:4:2:2:1:1:2:2:6:6:6:6:6:6:1:2:2:2:2:1:1:1:1:1:1:1:3:3:3:1:3:3:3:3:3:3:1:4:4:4:4:2:2:1:4:4:4:4:2:2:1:1:2:2})

[eeigor]

Михаил, я попытал имена в вашем файле.
Например, посчитал количество записей DURATIONS. Сначала ошибка (502), потом вдруг появляется результат спустя время, пока я выполнял какие-то несвязанные действия...
Впрочем, подобные ошибки с задержкой вычислений я наблюдал и раньше.

При этом файл XL смог без ошибок загрузить только в LO Calc.


UPD:
Вызвал на листе формулу:
=COUNT(IDS)
...и после этого ваша вся формула посчиталась. Странно, однако  
UPD:
После открытия – ошибка 502 снова. Но формулы считались без изменений!!

Можно попробовать, например, сохранить LAST_ROW на листе

[eeigor]

Михаил, ошибку удалось устранить, удалив имя INF и вставим его значение непосредственно в формулу LAST_ROW

[petav]

Диапазон берём с запасом. Весь столбец – не рекомендую. Файл и так тормозит

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


=SUMPRODUCT(CEILING(SUMIFS(Tickets.C4:C999;Tickets.K4:K999;Tickets.K4:K999);300)/COUNTIFS(Tickets.K4:K999;Tickets.K4:K999))

#DIV/0!

Я не могу понять
По кусочкам нет 0!

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


Результат Формла
3         COUNTIFS(Tickets.K4:K999;Tickets.K4:K999)
1200      SUMIFS(Tickets.C4:C999;Tickets.K4:K999;Tickets.K4:K999)
2700      CEILING(SUMIFS(Tickets.C4:C999;Tickets.K4:K999;Tickets.K4:K999);900)
675       CEILING(SUMIFS(Tickets.C4:C999;Tickets.K4:K999;Tickets.K4:K999);900)/COUNTIFS(Tickets.K4:K999;Tickets.K4:K999)

Все вместе 0!

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


Результат Формла
#DIV/0!   SUMPRODUCT(CEILING(SUMIFS(Tickets.C4:C999;Tickets.K4:K999;Tickets.K4:K999);900)/COUNTIFS(Tickets.K4:K999;Tickets.K4:K999))


[mikekaganski]

Михаил, ошибку удалось устранить, удалив имя INF и вставим его значение непосредственно в формулу LAST_ROW

Спасибо!
Написал баг 143809.

Однако Excel выдаёт для этих формул другие значения - 0 и 900.

[mikekaganski]

#DIV/0!

На каких данных? Вы ведь писали про строки без идентификаторов - хотя в Вашем тестовом файле их не было. И предложение было использовать IFERROR. Обратите внимание, что расчёт по частям в таком виде для "массивной" формулы (SUMPRODUCT делает своё колдовство) не даст правильной картины (если только Вы не повторите этот расчёт по частям для каждой строки! - а тогда не забудьте, что надо бы сделать ссылки абсолютными).

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

SUMPRODUCT(IFERROR(CEILING(SUMIFS(Tickets.C4:C999;Tickets.K4:K999;Tickets.K4:K999);900)/COUNTIFS(Tickets.K4:K999;Tickets.K4:K999);0))

[sokol92]

Однако Excel выдаёт для этих формул другие значения - 0 и 900.

Если формулу из #23 записать в Excel как массивную (понятнее будет SUM(IFERROR(... ), то результат будет верный (66600).

В немассивной формуле, по-видимому, "магия" SUMPRODUCT не распространяется на IFERROR.  

[eeigor]

В немассивной формуле, по-видимому, "магия" SUMPRODUCT не распространяется на IFERROR.

И если это действительно так, то надо просто ввести SUMPRODUCT как массивную и никак иначе (чтобы заставить "работать" немассивные формулы, входящие в её состав). Возможно, это не очевидно. "Магия" SUMPRODUCT распространяется только на самое себя.
Обычно я просто пробую: не работает, ввожу как массивную. Если заработало, то всё понятно. Но, кмк, ISERROR в Exel не массивная. Странно, что в Calc работает.

[mikekaganski]

В немассивной формуле, по-видимому, "магия" SUMPRODUCT не распространяется на IFERROR.  

Возможно, это не очевидно. "Магия" SUMPRODUCT распространяется только на самое себя.

Как мне кажется, тут некоторая нестыковочка.
Формула, о которой идёт речь - это

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

=SUMPRODUCT(IFERROR(CEILING.MATH(SUMIFS(DURATIONS;IDS;IDS);300)/COUNTIFS(IDS;IDS);0))

Действительно, при устранении IFERROR она начинает работать в Excel. Но значит ли это, что "Магия SUMPRODUCT распространяется только на самое себя"? Ведь SUMPRODUCT в работающей формуле

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

=SUMPRODUCT(CEILING.MATH(SUMIFS(DURATIONS;IDS;IDS);300)/COUNTIFS(IDS;IDS))

делает "массивной" третий аргумент функции SUMIFS, которая вложена в CEILING.MATH, которая уже является аргументом SUMPRODUCT. Согласно ODF, аргументы этой функции - ForceArray, что значит "заставляет вычисление выражения аргумента производиться в нескалярном режиме". Я выделил "выражения", потому что выражением аргумента является всё подвыражение, вычисление которого даёт значение аргумента - со всеми вложенными подвыражениями. Нет никакого ограничения для "распространения магии". Так что правильно Calc действует. Между прочим, Microsoft - один из членов технического комитета OpenDocument в OASIS. Так что если что-то в определении функции их не устраивало, у них всегда есть возможность вносить предложения и исправления.

В любом случае, искусственное ограничение такого рода ("на IFERROR не распространяется действие нескалярного режима") попросту непоследовательно. Ну да ладно.

[eeigor]

OFFTOP
С IFERROR разобрались.

надо просто ввести SUMPRODUCT как массивную и никак иначе (чтобы заставить "работать" немассивные формулы, входящие в её состав)

Уточню сказанное, применительно к Excel, следующей выдержкой из книги:

En
If you have an array operation in the IF function, the formula requires Ctrl+Shift+Enter,
regardless of where the IF function sits in the larger formula. This means that even if
you put an IF function that contains an array operation into a function argument that can
handle array operations (like the array arguments in the AGGREGATE or
SUMPRODUCT functions), the formula will still require Ctrl+Shift+Enter. In some
cases, rather than use the IF function with an array operation in a function argument that
does not require Ctrl+Shift+Enter, it may be less ambiguous to not use the IF function
and instead find an alternative formula.
To prove this to yourself, you can enter this formula:
=AGGREGATE(15,6,IF($A$3:$A$13<>$F$2, IF($B$3:$B$13=E5,$C$3:$C$13)),1)
You get a #VALUE! error. If you were to enter the formula using Ctrl+Shift+Enter, the
formula would work, but it would defeat the goal of inventing an array formula that does not
require Ctrl+Shift+Enter.
Therefore, if you are to remain faithful in your attempt to create a formula that does not
require Ctrl+Shift+Enter, you need to adjust the formula element by removing the IF
functions altogether. If you rearrange the two conditions and the range of numbers, you go
from this formula element:
IF($A$3:$A$13<>$F$2,IF($B$3:$B$13=E5, $C$3:$C$13)) to this formula element:
$C$3:$C$13/(($A$3:$A$13<>$F$2)*($B$3:$B$13=E5))

Ru
Если у вас есть операция с массивом в функции IF, для формулы требуется Ctrl+Shift+Enter,
независимо от того, где функция IF находится в формуле большего размера. Это означает, что даже если
вы поместите функцию IF, содержащую операцию с массивом, в аргумент функции, который может
обрабатывать операции с массивом (например, аргументы массива в функциях AGGREGATE или
SUMPRODUCT), для формулы все равно потребуется Ctrl+Shift+Enter. В некоторых
случаях вместо использования функции IF с операцией массива в аргументе функции, для которой
не требуется Ctrl+Shift+Enter, может быть менее двусмысленным не использовать функцию IF
и вместо этого найдите альтернативную формулу.
Чтобы доказать это себе, вы можете ввести эту формулу:
=AGGREGATE(15,6,IF($A$3:$A$13<>$F$2, IF($B$3:$B$13=E5,$C$3:$C$13)),1)
Вы получаете ошибку #VALUE!. Если бы вы ввели формулу с помощью Ctrl+Shift+Enter,
формула работала бы, но это помешало бы цели создания формулы массива, для которой не
требуется Ctrl+Shift+Enter.
Поэтому, если вы хотите сохранить верность в своей попытке создать формулу, для которой не
требуется Ctrl+Shift+Enter, вам необходимо настроить элемент формулы, удалив все IF
функции. Если вы измените два условия и диапазон чисел, вы перейдете
от этого элемента формулы:
IF($A$3:$A$13<>$F$2,IF($B$3:$B$13=E5, $C$3:$C$13)) к этому элементу формулы:
$C$3:$C$13/(($A$3:$A$13<>$F$2)*($B$3:$B$13=E5))

Источник: "Ctrl+Shift+Enter: Mastering Excel Array Formulas" by Mike Girvin (2013)

[petav]

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

SUMPRODUCT(IFERROR(CEILING(SUMIFS(Tickets.C4:C999;Tickets.K4:K999;Tickets.K4:K999);900)/COUNTIFS(Tickets.K4:K999;Tickets.K4:K999);0))

Благодарю
Работает как требуется.
Libre Office
Версия: 5.2.7.2
ID сборки: 1:5.2.7-1+deb9u11
Потоков ЦП: 2; Версия ОС: Linux 4.9; Отрисовка ИП: по умолчанию; VCL: x11;
Локаль: ru-RU (ru_RU.UTF-8); Calc: group

[kompilainenn]

Версия: 5.2.7.2

Ох ё, а почему не обновляете ЛО?