我一周前推出了我最大的谷歌表单公式，我已经达到了我所能做的极限。我通常总是通过搜索来找到问题的答案，但这次没有。
自2017年公司成立以来，我已经导出了一份项目列表，共有9000多条线路。
里面有很多专栏，但我们感兴趣的是：
- 开始日期
- 组织
- 状态(汇票、报价单或订单(
- 汇票或报价金额
- 订单金额
- 状态(死亡、丢失、取消、临时、打开、保留、活动、已完成(
数据截图
在另一张表中，我想显示一些基于某些条件但基于唯一值的列来自客户。
作为列：
- A/客户端
- B/收入(如果状态不同于丢失、死亡或取消，则为订单金额列的总和(
- C/合同数量(如果状态不同于丢失、失效或取消，则为订单数量(
- D/汇票和报价单的编号(因此，如果状态等于汇票或报价单，并且状态不同于丢失、失效或取消(
- E/丢失次数(当状态等于丢失、失效或取消时(
- F/每份合同的平均金额(B除以C(
- G/转换率(C除以C+D+E(
除此之外，我还有专门的单元格来选择日期，这样该表的用户就可以按月份和/或年份进行排序(见屏幕截图(。
客户视图屏幕截图
我首先使用A列中的UNIQUE函数来输出客户列表。然后，我根据自己的标准在其他专栏中制作了SUMPRODUCT。但是6列×600行的SUMPRODUCT，谷歌主页每次更改日期都需要5分钟才能给我一个结果。。。所以我环顾四周，发现了UNIQUE、FILTER和SUMIF的组合。
为了帮助我，我在第一个表的末尾创建了列(见第一张屏幕截图(，这将帮助我进行SUMIFS(法语为SOMME.SI(，只需要添加每一行，并以第一列和日期的唯一性为标准。如果我这么做了，那是因为我不能在ArrayFormula中进行SUMPRODUCTs。
通过一点点尝试，我想出了一个巨大的公式。
=sort(
{unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),
ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!W2:W)),
ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!X2:X)),
ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!Y2:Y)),
ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!Z2:Z)),
SIERREUR(ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!W2:W))/
ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!X2:X)),""),
SIERREUR(ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!X2:X))/
(ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!X2:X))+
ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!Y2:Y))+
ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!Z2:Z))),"")}
,A4,SI(B4="croissant",VRAI,FAUX))
当你看到它的时候真的很可怕，但它是有效的。另一方面，考虑到公式的大小，我告诉自己，我可能做得很糟糕，必须有一个更简单的解决方案。
谈到这个公式，它允许我使用正确的值正确显示每一列我的约会需要1秒钟的时间来改变。
我想使用过滤视图对列进行升序排序，但过滤视图不适用于这种公式。因此，我能够使用SORT，这让我可以选择排序哪一列和排序方向，这要归功于里面的IF(法语中的SI(。
我的最后一个问题，正如你在客户视图屏幕截图中看到的，我看到的客户是0。这些客户没有订单，只是丢失、死亡或取消了项目。我不能把它们排除在我的计算之外，因为我还需要知道与其他客户失去的项目数量。最后，经过计算，我只需要不向那些与我们一起赚了0美元的客户展示。
你觉得这个巨大的公式怎么样？它听起来对你来说是最优的吗？对于0美元的客户，我该如何隐藏他们？
非常感谢你花时间阅读这篇长文，以及你能给我的答案！：(
感谢Aresvik的评论，它引导我找到了正确的解决方案，我能够用这个公式解决我的所有问题：
=QUERY(
QUERY(
'DATA OPPORTUNITY'!A2:Z,"select E, sum(W), max(W), sum(X), sum(Z), sum(W)/sum(X),
sum(X)/(sum(X)+sum(Y)+sum(Z))
where" &SI(F2<>""," E='"&F2&"' and "," ")& "B >= date '"&TEXTE(B1,"yyyy-mm-dd")&"'
and B <= date '"&TEXTE(B2,"yyyy-mm-dd")&"'
group by E label E'', sum(W)'', max(W)'', sum(X)'', sum(Z)'', sum(W)/sum(X)'',
sum(X)/(sum(X)+sum(Y)+sum(Z))''"
)
, "select * where Col2 <> 0 and Col2 is not null Order By
Col"&SIERREUR(EQUIV(D1,5:5,0),1)&SI(D2="A to Z"," asc"," desc")
)
查询中的查询允许我在第一个查询的计算中考虑丢失的合同，但在第二个查询中不显示在目标期间未报告收入的客户。与最初的项目相比，我添加了max(W(，这使我能够了解客户的最高项目。我还添加了对单元格的引用，以了解我们要按哪一列排序，以及它是增加还是减少。最后，在"；其中"；仅当单元格已填充时才显示的标准，只允许显示一个客户。