如何通过VLOOKUP在COUNTIF函数中获取指定表头的条件范围？

嘿，这个问题我太有经验了！碰到列位置动不动就变的Excel表，手动选范围根本不是长久之计，必须用动态匹配表头的方法来解决。下面给你几个实用的方案，保证不管列怎么挪都能准确统计：

方案1：INDEX+MATCH组合（兼容性最强，所有Excel版本都能用） #

这是最经典的动态列定位方法，核心是用MATCH找到表头所在的列号，再用INDEX返回整个列的范围。

统计单个产品类型的总数量 #

公式如下：

=COUNTIF(INDEX($1:$10000,MATCH("product type",$1:$1,0)),"你要统计的产品名称")

• MATCH("product type",$1:$1,0)：在第一行（表头行）精确匹配，找到“product type”对应的列号
• INDEX($1:$10000, [列号])：返回表格中该列的所有数据行（这里假设最多10000行，你可以根据实际数据量调整）
• COUNTIF就会自动统计该列中符合指定产品名称的数量

按分支机构统计指定产品类型的数量 #

如果要精准统计某家分支机构的特定产品数量，用COUNTIFS多条件统计更合适：

=COUNTIFS(INDEX($1:$10000,MATCH("branch code",$1:$1,0)),"目标分支机构代码",INDEX($1:$10000,MATCH("product type",$1:$1,0)),"你要统计的产品名称")

这个公式会同时匹配分支机构代码和产品类型，返回精准的统计结果。

方案2：XLOOKUP（适用于Excel 365/2021及以后版本） #

如果你用的是新版Excel，XLOOKUP比INDEX+MATCH更简洁，一步就能返回目标列：

单个产品类型统计 #

=COUNTIF(XLOOKUP("product type",$1:$1,$1:$10000),"你要统计的产品名称")

XLOOKUP("product type",$1:$1,$1:$10000)直接在表头行找到“product type”，并返回对应的整列数据，省去了INDEX的步骤。

按分支机构统计 #

=COUNTIFS(XLOOKUP("branch code",$1:$1,$1:$10000),"目标分支机构代码",XLOOKUP("product type",$1:$1,$1:$10000),"你要统计的产品名称")

方案3：定义动态名称（方便重复使用） #

如果经常需要统计这些列的数据，可以给它们定义动态名称，后续公式会更简洁：

1. 点击顶部菜单栏的公式 -> 定义名称
2. 名称输入ProductTypeColumn，引用位置输入：=INDEX(Sheet1!$1:$10000,MATCH("product type",Sheet1!$1:$1,0))（把Sheet1换成你的工作表名称）
3. 同理，定义BranchCodeColumn：=INDEX(Sheet1!$1:$10000,MATCH("branch code",Sheet1!$1:$1,0))

之后统计时，公式就可以简化成：

# 单个产品统计
=COUNTIF(ProductTypeColumn,"你要统计的产品名称")

# 按分支机构统计
=COUNTIFS(BranchCodeColumn,"目标分支机构代码",ProductTypeColumn,"你要统计的产品名称")

几个实用小提示 #

• 确保表头行是固定的（比如第一行），而且表头没有合并单元格，否则MATCH会出错
• 如果表头大小写不统一（比如有的表是Product Type，有的是product type），可以用忽略大小写的匹配：MATCH(LOWER("product type"),LOWER($1:$1),0)
• 数据范围的行数（比如$1:$10000）可以根据实际情况调整，要是怕漏数据，也可以设大一点，比如$1:$100000，Excel会自动忽略空行

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