Excel - 如何计算包含某些字符串的范围（即各列）中的行数？ [英] Excel - How to count number of rows in range (i.e. various columns) that contain certain string?

问题描述

我有一个字符串的列表，我想看看是否和是否在每个字符串在范围B中表示多少行。我的数据看起来像这样：

列表A：

  E 
 1 aaa 
 2 bbb 
 3 ccc 
  

范围B：

 code> ABC 
 1 aaa --- --- 
 2 bbb ccc bbb 
 3 aaa --- --- 
  / pre> 
 
 
我想要的结果是在aaa的列表A中，它会告诉我2，bbb1和ccc1.已经尝试使用以下数组公式执行此操作，但似乎不起作用：
  = SUM（IF（COUNTIF范围B，列表A（$ A1）> 0,1,0））
  
 
 
解决方案
尽管我喜欢避免鼓励使用易失性的 
 
 
 
 F2中的数组公式是
  = SUM（SIGN（COUNTIF（OFFSET（A $ 2， R $（$ 1：$ 99）-1,0,1,3），E2）））
  
公式需要用 Ctrl  +  Shift  + 输入来确定。一旦正确输入了第一个单元格，就可以像任何其他公式一样填充或复制。
 
 
 
¹  OFFSET 被认为是一个易失性函数，可以在任何时候重新计算工作簿更改。 
 
I have a list A of strings and I want to see if and if yes in how many rows each string is represented in range B. My data looks something like this:

List A:
   E
1 aaa
2 bbb
3 ccc
Range B:
   A      B      C
1 aaa    ---    ---
2 bbb    ccc    bbb
3 aaa    ---    ---
My desired result would be that in list A for "aaa" it would tell me 2, for "bbb" 1 and for "ccc" 1. Have been trying to do this with the below array formula but it doesn't seem to be working:
=SUM(IF(COUNTIF(Range B,List A($A1)>0,1,0))

解决方案
As much as I like to avoid encouraging the use of the volatile OFFSET¹ function, it seems the most likely candidate in this situation.

        

The array formula in F2 is,
=SUM(SIGN(COUNTIF(OFFSET(A$2, ROW($1:$99)-1, 0, 1, 3), E2)))
Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula.

¹OFFSET is considered a volatile function that recalculates whenever anything in the workbook changes.

                        
这篇关于Excel  - 如何计算包含某些字符串的范围（即各列）中的行数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！