问题描述
在我的电子表格中，比较耳机，我们的说明基本上是使用尽可能多的不同公式.
In my spreadsheet, comparing headphones, our instruction is basically to use as many different formulas as possible.
因此，作为电子表格的一部分，我希望能够显示每个制造商最昂贵的耳机、每个制造商最便宜的耳机、每个制造商评价最高的耳机等等......到目前为止，我已经能够得到一些主要工作的数组公式.
So as part of the spreadsheet, I would like to be able to show the most expensive headphones per manufacturer, cheapest per manufacturer, best rated per manufacturer etc...So far I have been able to get some mostly working array formulas.
例如，此公式用于获取价格最高的制造商Sennheiser"的型号:
For example, this formula works to get the model of the manufacturer "Sennheiser" with the highest price:
=INDEX($Data.$B$5:$L$32,SMALL(IF($Data.$E$5:$E$32 = $Sheet2.D17, ROW($Data.$B$5:$E$32) - ROW($Data.$B$5) + 1, ROW($Data.$E$32) + 1), 1), 2)
E 列是价格列，D17 是sennheiser"的预先计算的最高价格
Column E is the Price column and D17 is a pre-calculated Max price for "sennheiser"
这很好用，直到您获得 2 个具有相同价格/评级或其他任何东西的耳机.然后它开始返回错误的值.
This works fine, until you get 2 headphones with the same price / rating or whatever. THen it starts returning the wrong values.
所以我尝试了在互联网上找到的各种解决方案，例如
So I tried various solutions that I found on the interwebs, like
AND(condition1, condition2)
condition1 * AND(cndition2)
condition1 * condition2
但出于某种原因，这些似乎都不适用于数组公式.我收到 #N/A 或 #VALUE 以及其他各种错误.所以基本上我想知道如何修改我的公式，甚至是一个全新的公式，以检查最低价格和正确的制造商.
but none of this seems to work with an array formula for some reason. I get #N/A or #VALUE and various other errors.SO basically I would like to know how to modify my formula, or even a completely new formula, to check for lowest price AND the correct manufacturer.
我希望我的问题很清楚，所以我上传了电子表格以了解我在说什么.
I hope my question is clear, so I have uploaded the spreadsheet to get some idea of what I am talking about.
http://dl.dropbox.com/u/18816338/Stats%20Analysis%20%20%281%29.xlsm
提前致谢
推荐答案
通常 AND 在这里不起作用，因为 AND 返回单个结果而不是数组....但是 * 应该没问题，即在 B3 中尝试这个公式
Typically AND won't work here because AND returns a single result rather than an array....but * should be OK, i.e. try this formula in B3
=INDEX(Data!C$5:C$32,MATCH(1,(Data!$E$5:$E$32=$D3)*(Data!$B$5:$B$32=$A3),0))
使用 CTRL+SHIFT+ENTER 确认并复制到 C3 然后向下两列
confirmed with CTRL+SHIFT+ENTER and copied to C3 and then down both columns
这将找到价格和制造商的第一个匹配项并返回相关型号/类型
That will find the first match for both price and manufacturer and return the relevant model/type
这篇关于Excel Array 公式 IF(多个条件)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！