比较产品使用多种属性 [英] Compare Products use multiple properties
问题描述
我有一个Excelsheet,并想按多个值比较产品.我的Excel看起来像这样:
I have an Excelsheet and want to compare the products by multiple values. My Excel looks like this:
+ ----------- + -------- + ------ + ------- + --------- +------- +
|名称|大卡脂肪|碳水化合物蛋白质|€/kg |
+ ----------- + -------- + ------ + ------- + --------- + ------- +
|产品A |383,00 |5,90 |6,50 |76,00 |21,98 |
|产品B |359,00 |0,70 |3,20 |85,00 |24,90 |
|产品C |380,90 |5,86 |8,79 |73,25 |32,17 |
|产品D |390,00 |5,50 |5,30 |80,00 |18,96 |
+ ----------- + -------- + ------ + ------- + --------- + ------- +
+-----------+--------+------+-------+---------+-------+
| Name| kcal | Fat | Carbs | Protein | €/kg |
+-----------+--------+------+-------+---------+-------+
| Product A | 383,00 | 5,90 | 6,50 | 76,00 | 21,98 |
| Product B | 359,00 | 0,70 | 3,20 | 85,00 | 24,90 |
| Product C | 380,90 | 5,86 | 8,79 | 73,25 | 32,17 |
| Product D | 390,00 | 5,50 | 5,30 | 80,00 | 18,96 |
+-----------+--------+------+-------+---------+-------+
我想通过以下标准找到最佳"产品:
I would like to find the "best" product by the following criteria:
- 最低千卡
- 最低脂肪
- 最低碳水化合物
- 最高蛋白质
- 最低€/kg
是否可以使用excel/google文档来做到这一点?
Is there a way to do this with excel/google docs?
也许是一个附加组件.最高优先级是€/kg,其次是Protein和其他三个蛋白具有相同的重要性.
Maybe a little add-on. The highest priority is €/kg, after that Protein and the other three have the same importance.
推荐答案
什么被认为是最佳"有点含糊,但是您可以考虑将所有这些量加在一起(减去蛋白质)会得到一个矩阵可以得到最小的.
It's a bit vague what would be considered the "best", however you could consider adding all these amounts together (subtract the protein) would give you a matrix in which you can get the minimum.
尝试:
=INDEX(A2:A5,MATCH(MIN(INDEX(B2:B5+C2:C5+D2:D5-E2:E5+F2:F5,)),INDEX(B2:B5+C2:C5+D2:D5-E2:E5+F2:F5,),0))
如果您认为乘法列更为重要,请向其中的任意列添加乘法,例如: * 1.05
.
Add a multiplication, e.g.: *1.05
to any of the columns if you consider them to be of more importance.
这篇关于比较产品使用多种属性的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!