Excel-根据范围输入使用优先级规则的公式 [英] Excel - Formula using priority rules depending on range inputs

问题描述

这可能是一个简单的解决方法(尽管我认为这可能并非如此)，因此，如果这仅仅是儿童游戏，我会提前道歉.

This is probably a simple fix (although me thinking this, means it probably isn't), so I apologise in advance if this is mere child's play.

在我正在处理的Excel工作表中，我有一个范围(就本例而言为A1:A10)，该范围可以包含A，B或C这3个变量之一(不包括空格).

In an excel sheet I am working on, I have a range (for the sake of this example is A1:A10) which can contain one of 3 variables (not including blanks) - A, B or C.

我需要另一个单元格中的公式来检查所讨论的范围，并根据此优先级中的以下规则输出值:

I require a formula in another cell to review the range in question and output a value based on the following rules in this priority:

1. 如果A出现在列中的任何位置，则不管其他输入如何，都显示A；
2. 如果列中出现B和C，则显示A；
3. 如果只有B出现在列中，则显示B；
4. 如果仅C出现在列中，则显示C；否则，显示C.和
5. 如果该列中的所有单元格均为空白，则显示空白

对于规则1-4，不应考虑该列中的任何空白单元格.只有在所有单元格都为空白(即规则5)的情况下，才应考虑这一点.

For rules 1-4, any blank cells within the column should not be considered. It is only where all cells are blank, i.e. rule 5, that this should be considered.

我尝试了IF公式，但发现这些公式仅考虑单个单元格. 我也尝试过将SUMPRODUCT与IF一起使用，但遇到了障碍.我使用的公式是:

I have tried IF formulas but have found these only consider a single cell. Also I have attempted using SUMPRODUCT along with IF but have hit a snag. The formula I used was:

IF(SUMPRODUCT(--(--(A1:A10="A")),"A",IF(SUMPRODUCT(--(A1:A10="B")),IF(SUMPRODUCT(--(A1:A10="C")),"A",IF(SUMPRODUCT(--(A1:A10="B")),"B",IF(SUMPRODUCT(--(A1:A10="C")),"C","")))))

现在，我知道这似乎很棘手，但是直到规则3正常为止.尝试使用规则4或5时，该公式仅返回 FALSE

Now I know this appears longwinded but until rule 3 it works fine. When trying for rule 4 or 5 the formula only returns FALSE

我全心全意地将上述公式改成周刊，以便使其完全起作用或完全适用于另一个公式，但是我尝试在任何地方搜索并且找不到任何内容(尽管这可能取决于我，所以我不会措辞不佳/正确搜索.)

I'm all for the above formula being tweeked so that it works or for another formula entirely but I've tried searching everywhere and can't find anything on this (although this is probably down to me not phrasing my question/searches correctly).

任何帮助将不胜感激.预先感谢！

Any help would be much appreciated. Thanks in advance!

推荐答案

使用您提供的示例，将产生所需的结果:

Using your provided example, this will yield desired results:

=INDEX({"","C","B","A"},MATCH(SUMPRODUCT({3,2,1},--(COUNTIF(A1:A10,{"A","B","C"})>0)),{0,1,2,3}))

这篇关于Excel-根据范围输入使用优先级规则的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！