计算清单中的所有唯一值+它们的总数量和每件商品的总价格(其中成本随时间变化) [英] Count all unique values in list + their Total quantity and Total price per Item cost (where cost changes over time)

问题描述

我想要以下内容:

• 另一个列表中唯一值的列表
• 在另一个列表中该值的总出现次数
• 商品总价

成本随时间变化，因此这必须计入值的总数.

在这里播放:

使用的功能:

• QUERY

I would like to have the following:

• List of unique values from another list
• Total occurrences of that value in the other list
• Total price of the items

Cost changes over time, so this must count towards the total quantity of the value.

Play here: https://docs.google.com/spreadsheets/d/1CCwoBxXYGPpR3Fog7gtyQXfH_soaZBKBHvxeikFnfzI/edit?usp=sharing

解决方案

Since the prices may change over time, the formula used in your previous linked question cannot be used.

Instead, please use the following formula which takes into account the price change.

=QUERY(QUERY(A1:C,"select A, sum(B), sum(B)*C where A is not null group by A, C" ), 
         "select Col1, sum(Col2), sum(Col3) group by Col1 order by sum(Col3) desc label sum(Col2) 'Total Quantity', sum(Col3) 'Item Cost' ")

Functions used:

• QUERY

这篇关于计算清单中的所有唯一值+它们的总数量和每件商品的总价格(其中成本随时间变化)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！