需要抓取相邻单元格中的最高和最低数字 [英] Need to grab the highest and lowest number in adjacent cells

问题描述

在 Excel 中，我试图为 AAAA 显示 1-4，为 BBBB 显示 5-7，等等

in Excel, I am attempting to show 1-4 for AAAA, 5-7 for BBBB, etc

Num Words
1   AAAA
2   AAAA
3   AAAA
4   AAAA
5   BBBB
6   BBBB
7   BBBB
8   CCCC
9   CCCC
10  CCCC
11  CCCC
12  CCCC
13  CCCC
14  CCCC
15  DDDD
16  DDDD
17  DDDD
18  DDDD
19  DDDD
20  DDDD

我头脑中的逻辑是，我想查看整个列并获取与单词匹配的一组相邻单元格中的最大数量.从而为每个单词抓取上述输出.

My logic in my head is that I would like to look at the whole column and grab the highest number of a set of adjacent cells which match the words. Thus grabbing the output described above for each word.

感谢您的帮助.

推荐答案

我假设有一个标题行.在 C2 中输入:

I will assume there is a header row. In C2 put:

 =IF(MIN(IF(B2 = B:B, A:A))=A2, A2 & "-" & MAX(IF(B2 = B:B, A:A)),"")

这是一个数组公式，需要用Ctrl-Shift-Enter确认.然后复制下来.

It is an array formula and needs to be Confirmed with Ctrl-Shift-Enter. Then copy down.

注意:

如果您的搜索范围是静态的，则将所有完整的列引用更改为绝对范围，例如A:A 在这种情况下会变成 $A$2:$A$21.它会使公式计算得更快.

If your search range is static then change all the full column references to the absolute range, e.g. A:A in this case would become $A$2:$A$21. It will make the formulas calculate quicker.

这篇关于需要抓取相邻单元格中的最高和最低数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！