Excel公式跨列拖动行号递增及两集合相等值计数问题

问题1：拖动公式跨列时实现行号递增 #

默认横向拖动公式时，Excel会自动递增列号、保持行号不变，但要反过来让行号随列数增加而变化，这几个方法亲测靠谱：

• 方法1：用INDEX函数
  假设你要把A列的内容逐行提取到第一行的B、C、D…列，直接写这个公式：

  =INDEX(A:A, COLUMN(A1))
  

  原理很简单：COLUMN(A1)在B列时返回2，C列返回3，刚好对应A2、A3的行号。输入完公式后横向拖动填充柄，行号就会跟着列数自动递增。

• 方法2：用OFFSET函数
  如果需要更灵活的偏移控制，试试这个：

  =OFFSET($A$1, COLUMN(A1)-1, 0)
  

  $A$1是固定的起始单元格，COLUMN(A1)-1代表向下偏移的行数（B列时偏移1行到A2，C列偏移2行到A3），最后一个0表示保持在A列不偏移。

• 方法3：ROW+COLUMN组合
  要是你只是需要生成递增的行号数值，直接用这个更简单：

  =ROW(A1) + COLUMN(A1) - 1
  

  这个公式在B1会得到2，C1得到3，完全满足横向拖动时数值递增的需求。

问题2：统计两个集合中相等值的数量（彩票兑奖场景） #

这个场景本质就是找两个数组的交集元素个数，下面两种方法都能快速搞定：

基础版：单个对比集合 #

假设初始中奖号码在A1:A15，待核对的号码在B1:B15，用这个公式直接算出匹配数量：

=SUMPRODUCT(COUNTIF(A1:A15, B1:B15))

• COUNTIF(A1:A15, B1:B15)会逐个检查B列的每个值是否在A列存在，存在返回1，不存在返回0；
• SUMPRODUCT把这些结果加起来，就是最终的匹配总数。

进阶版：批量处理多个对比集合 #

如果你有N个待核对集合（比如C1:C15、D1:D15…），在E1单元格输入公式后下拉即可批量计算：

=SUMPRODUCT(COUNTIF($A$1:$A$15, B1:B15))

把公式里的B1:B15换成对应的列（比如C1:C15），就能一次性算出所有集合的匹配数。

另外，还有个更直观的写法：

=SUM(--(ISNUMBER(MATCH(B1:B15, A1:A15, 0))))

• MATCH返回每个B列值在A列的位置，不存在则返回错误值；
• ISNUMBER把结果转成TRUE/FALSE，--再将布尔值转成1/0；
• SUM相加后就是匹配的总数量。

内容的提问来源于stack exchange，提问作者Ivan Machado