克服了Excel中谷歌表函数的缺失

Trying to convert or recreate this Google Sheets formula in Excel. What I have is a list of users that may or may not repeat and I am trying to find the number of purchaes, lowest purchase amount, the total price of purchases and the average price of purchases.

尝试在Excel中转换或重新创建这个谷歌表公式。我所拥有的是一个用户列表，可能重复，也可能不重复，我正在试图找到purchaes的数量，最低的购买金额，购买的总价格和购买的平均价格。

Here is my Google Sheets formula that does exactly what I am looking for:

这是我的谷歌表公式，它完全符合我的要求:

=QUERY(Data!A2:E,"select A, sum(E), min(D), sum(D), avg(D) group by A label A 'Email', sum(E) 'Length', min(D) 'Lowest Price', sum(D) 'Total', avg(D) 'Average'",0)

=查询(数据!A2:E，"选择A, sum(E)， min(D)， sum(D)， avg(D) group by A label A 'Email'， sum(E)“长度”,最小值(D)最低价格,和(D)“总”,avg(D)“平均”,0)

Source Data Sheet:

源数据表:

Email                Name   Date        Price    Qty
[e-mail]    User1  1/19/2014   49.00     1
[e-mail]    User1  2/19/2014   49.00     1
[e-mail]    User2  6/7/2013    49.00     1
[e-mail]    User3  5/25/2014   24.95     1
[e-mail]    User4  4/26/2013   49.00     1
[e-mail]    User4  5/26/2013   49.00     1
[e-mail]    User5  6/17/2014   24.95     1
[e-mail]    User5  2/7/2014    49.00     1
[e-mail]    User6  3/25/2014   24.95     1
[e-mail]    User6  4/7/2014    49.00     1
[e-mail]    User6  5/13/2014   49.00     1
[e-mail]    User7  10/23/2013  49.00     1
[e-mail]    User7  11/23/2013  49.00     1
[e-mail]    User8  9/19/2013   24.95     1
[e-mail]    User8  3/14/2015   49.00     1
[e-mail]    User9  5/1/2013    24.95     1
[e-mail]    User9  6/1/2013    49.00     1
[e-mail]    User9  7/1/2013    49.00     1

Output Sheet Sheet:

输出单表:

Email              Length     Lowest Price    Total         Average
[e-mail]    2           $49.00       $98.00        $49.00
[e-mail]    1           $49.00       $49.00        $49.00
[e-mail]    1           $24.95       $24.95        $24.95
[e-mail]    2           $49.00       $98.00        $49.00
[e-mail]    2           $24.95       $73.95        $36.98
[e-mail]    3           $24.95       $122.95       $40.98
[e-mail]    2           $49.00       $98.00        $49.00
[e-mail]    2           $24.95       $73.95        $36.98
[e-mail]    3           $24.95       $122.95       $40.98

I would prefer no VBA but if that is the only way to make it work then so be it.

我不喜欢VBA，但如果这是唯一可行的方法，那就顺其自然吧。

Thanks

谢谢

1 个解决方案

#1

You can use a Pivot Table to get this result.

您可以使用Pivot表获得这个结果。

1. Go to Insert > Pivot Table.
2. 转到插入>透视表。
3. Add the Email to Rows, Qty to Values, and add Price to Values 3 times.
4. 将邮件添加到行，Qty添加到值，将价格添加到值3次。
5. Right click each Price column > Summarize Values By, and select the appropriate aggregation.
6. 右键单击每一个Price列>汇总值，然后选择适当的聚合。
7. You can change the column headers and cell number formats by simply editing the cells, or through the Value Field Settings.
8. 您可以通过编辑单元格或通过值字段设置来更改列标题和单元格编号格式。