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

问题描述

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

  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 
  

我的我的脑海里的逻辑是，我想看看整个列，并且抓住最多数量的一组相邻单元格匹配的单词。感谢任何帮助。

解决方案

我将假设有一个标题行。在C2中：

  = IF（MIN（IF（B2 = B：B，A：A））= A2，A2 & - & MAX（IF（B2 = B：B，A：A）），）
  

它是一个数组公式，需要使用Ctrl-Shift-Enter确认。然后复制。

注意：

如果您的搜索范围是静态的，请将全部列引用全部更改为绝对范围，例如A：A在这种情况下会成为$ A $ 2：$ A $ 21。这将使公式计算更快。

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.

Thanks for any assistance.

解决方案

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)),"")

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

Note:

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屋！