Какой лучший способ репликации суммифов, кроме создания формулы массива?
У меня есть следующий набор данных:
и у меня есть следующая таблица:
то, что я хотел бы сделать, это создать формулу массива в таблице, которая устранила бы необходимость во многих формулах суммирования (например, =SUMIFS(D$10:D$18,$A$10:$A$18,$A3,$B$10: $B$18,"y",$C$10:$C$18,"y")), которую нужно будет перетащить вдоль и вниз, чтобы в итоге получилось следующее:
Ответы 1
Попробуйте использовать MAKEARRAY() --> просто и понятно:
=MAKEARRAY(ROWS(A13:A15),COLUMNS(B12:M12),LAMBDA(r,c,
SUM((INDEX(A13:A15,r)=A2:A10)*(B2:B10 = "Y")*(C2:C10 = "Y")*(INDEX(B12:M12,c)=D1:O1)*D2:O10)))
Или используйте одну формулу динамического массива, чтобы вернуть весь массив:
=LET(
_Data, DataTable[#All],
_Name, DROP(TAKE(_Data,,1),1),
_Headers, DROP(TAKE(_Data,1),,3),
_Uniq, UNIQUE(_Name),
_Output, MAKEARRAY(ROWS(_Uniq), COLUMNS(_Headers), LAMBDA(r,c,
SUM((INDEX(_Uniq,r)=_Name)*(INDEX(_Headers,c)=_Headers)*
(INDEX(DROP(_Data,1),,2) = "Y")*(INDEX(DROP(_Data,1),,3) = "Y")*DROP(_Data,1,3)))),
VSTACK(HSTACK(A1,_Headers),HSTACK(_Uniq,_Output)))
Если применимо, можно также использовать GROUPBY():
=VSTACK(HSTACK(A1,D1:O1),
GROUPBY(A2:A10,D2:O10,SUM,,1,,BYROW(B2:C10 = "Y",AND)))
После множества проб и ошибок выяснилось, что этот вариант быстрее и эффективнее остальных:
=LET(
_Conditions, MMULT(N(Conditions = "Y"),{1;1})>1,
_Filter, FILTER(HSTACK(IDData,Vals),_Conditions,""),
_Name, TAKE(_Filter,,1),
_Uniq, UNIQUE(_Name),
_SumVals, MMULT(N(_Uniq=TOROW(_Name)),DROP(_Filter,,1)),
IFNA(INDEX(_SumVals,XMATCH(ID,_Uniq),B1:M1),0))
@andyleary, чтобы иметь GROUPBY(), вам нужно будет включить программу предварительной оценки Office, а также ETA Хорошо, я попробую другой вариант!
@andyleary, можешь попробовать это и дай мне знать: =LET( _Conditions, MMULT(N(B2:C10 = "Y"),{1;1})>1, _Filter, FILTER(HSTACK(A2:A10,D2:O10),_Conditions,""), _Name, TAKE(_Filter,,1), _Uniq, UNIQUE(_Name), VSTACK(HSTACK(A1,D1:O1),HSTACK(_Uniq,MMULT(N(_Uniq=TOROW(_Name)),DROP(_Filter,,1)))))
@andyleary, честно говоря, для больших наборов данных я лично использую Power Query и не полагаюсь на формулы.
@andyleary Сэр, я провел тест скорости со своей стороны с 55K строками данных, на моей стороне не происходит сбоя. Вы можете скачать Excel из здесь Кроме того, я использую игровой ПК и ноутбук, может быть разница в производительности, на самом деле результат был получен в течение секунды. как мгновение ока!
эта формула тоже работает, но опять же не для больших наборов данных. Раньше я уже предпринимал попытки использовать запрос мощности, но снова обнаружил проблемы со скоростью по сравнению с использованием формул в самом Excel. Возможно, мне стоит попробовать еще раз. Хей-хо. Это все хорошая кривая обучения
извините, в моем файле произошла ошибка, из-за которой возникли задержки. Теперь я могу подтвердить, что формулы работают для размера набора данных. Однако последние формулы не ссылаются на A13:A15, что им нужно было бы сделать, поскольку потенциально может быть имя «D», которое должно было бы возвращать ноль. Это возможно? Первая формула делает это, но, судя по всему, не для больших наборов данных: =MAKEARRAY(ROWS(A13:A15),COLUMNS(B12:M12),LAMBDA(r,c, SUM((INDEX(A13:A15,r) )=A2:A10)*(B2:B10 = "Y")*(C2:C10 = "Y")*(INDEX(B12:M12,c)=D1:O1)*D2:O10) ))
@andyleary последние формулы представляют собой одну единую формулу динамического массива, она вернет весь массив, поэтому нет необходимости записывать вручную, a, b, c, d и т. д. Кроме того, нажмите на here, я провел несколько тестов скорости, работает без проблем Если у вас есть Excel, я попробую провести тест скорости, просто удалив личную информацию.
@andyleary также лучше преобразовать исходные диапазоны в Structured References, то есть Tables это поможет формулам автоматически корректироваться при добавлении/удалении новых строк!
как мне отправить вам Excel?
@andyleary просто создайте ссылку на один диск, ссылку на диск Google, ссылку на Dropbox или любой облачный диск и опубликуйте в следующих комментариях.
Это работает?
Да, это так. я сообщу тебе, браво!
Какая польза от столбца D, нужно ли мне это тоже учитывать?
столбец d не нужен для формулы
Окей, понял. Дайте мне минутку, я обновляюсь.
@andyleary Привет, сэр, я обновил другую формулу, она будет немного быстрее. Я протестировал версию для настольного компьютера, только что применил ее в Интернете.
Это обновленная формула, сравнительно более быстрая, чем предыдущая. =LET( _Conditions, BYROW(Conditions,LAMBDA(α,AND(α = "Y"))), _Extract, FILTER(HSTACK(IDData,Vals),_Conditions,""), MAKEARRAY(ROWS(ID),COLUMNS(Headers),LAMBDA(r,c,SUM((INDEX(ID,r)=INDEX(_Extract,,1))*DROP(_Extract,,1)))))
Давайте продолжим обсуждение в чате.
Спасибо. Мне не удалось заставить работать ETA Lambda, и у меня еще нет Groupby. Остальные работали с небольшими наборами данных, но, увы, не с тем размером данных, который у меня есть (около 50 тыс. строк), поскольку мой ноутбук постоянно ломался. Возможно, мне придется переосмыслить, как я это делаю