基于条件条件的查找模式 [英] Finding Mode based of conditional criteria

问题描述

我希望能够在给定条件下在相邻列中找到最常用的值.例如:

I would like to be able to find the most common value given a certain criteria in an adjacent column. For example:

>**ROW A**  -----   **ROW B**  
>Blue ---------- Jason  
>Blue ---------- Jason  
>Blue ---------- Charles   
>Red ---------- Alfred  
>Red ---------- Alfred  
>Red ---------- Barry  
>Green --------- Barry

我想要一个看起来像下面的公式

I would like a formula that could look like the following

if(X=Row_A_Value,Mode of X)

例如，如果为"BLUE"，则输出将为"JASON".如果为红色"，则输出为"ALFRED"

For instance if "BLUE" then the output would be "JASON". If "RED" the output would be "ALFRED"

我尝试使用Index(Mode(Match))却没有任何运气...

I've tried using Index(Mode(Match)) without any luck...

感谢您的帮助！

推荐答案

不是最漂亮的公式，但是此数组公式有效:(为便于阅读而添加了换行符)

Not exactly the prettiest formula, but this array formula works: (line break added for readability)

= INDEX(B1:B7,MATCH(MAX(COUNTIFS(A1:A7,"Blue",B1:B7,B1:B7)),
  COUNTIFS(A1:A7,"Blue",B1:B7,B1:B7),0))

也许有一种更有效的方法，但这只是我想到的第一个.

There probably is a more efficient way but this is just the first one I could think of.

很明显，只需将上面公式中的"Blue"的两个实例替换为"Red"即可搜索红色.

Obviously, just replace the two instances of "Blue" in the formula above with "Red" in order to search for red instead.

请注意，这是一个数组公式，因此在键入此公式后，您必须按 Ctrl + Shift + Enter ，而不仅仅是输入.

Note this is an array formula, so you must press Ctrl+Shift+Enter after typing this formula rather than just Enter.

请参见下面的工作示例.

See below, working example.

这篇关于基于条件条件的查找模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！