公式计算中移除引号及COUNTIF公式因数据源新增前置列失效问题

我来帮你搞定这两个Excel公式的痛点，都是日常数据复制粘贴时容易踩的坑：

一、解决数据源新增列后COUNTIF引用失效的问题 #

你遇到的核心问题是COUNTIF使用了固定列标（比如B:B）作为引用范围，当数据源在Business列前新增列，复制粘贴到测试表后，Business列的位置会偏移，但公式里的列标不会自动更新，导致统计范围错误。这里有几个靠谱的解决方案：

1. 改用Excel结构化表格（最推荐） #

把你的测试表数据转换成结构化表格（按下Ctrl+T，勾选“我的表格有标题”），之后直接用表头名称来引用列，公式会自动追踪表头，不管列怎么移动都不会出错：

=COUNTIF(Table1[Business], "test")

注：Table1是你创建表格时的默认名称，可右键表格重命名

2. 定义动态名称引用Business列 #

选中测试表中Business列的所有数据（包括表头），点击公式栏左侧的名称框，输入一个自定义名称（比如Business_Column）并回车。之后公式用这个名称代替列标：

=COUNTIF(Business_Column, "test")

后续即使Business列位置变化，只要重新选中新的Business列更新这个名称，或者用动态名称公式（=OFFSET($A$1,0,MATCH("Business",$1:$1,0),COUNTA($A:$A),1)）让它自动追踪列位置。

3. 用INDEX+MATCH动态定位列范围 #

如果不想用表格或名称，可以用MATCH找到Business表头的列位置，再用INDEX定位整列范围，这样不管前面加多少列，都能准确锁定Business列：

=COUNTIF(INDEX($A:$Z,1,MATCH("Business",$1:$1,0)):INDEX($A:$Z,ROWS($A:$A),MATCH("Business",$1:$1,0)), "test")

$A:$Z是你数据覆盖的列范围，可根据实际调整

二、解决统计时移除引号的需求 #

如果你的数据源中Business列的内容带引号（比如单元格显示为"test"），而你需要统计去掉引号后等于test的实例数量，可以用SUMPRODUCT结合SUBSTITUTE来实现，直接在公式中完成去引号和统计：

=SUMPRODUCT(--(SUBSTITUTE(Table1[Business], """", "")="test"))

解释：

• SUBSTITUTE(..., """", "")：把单元格中的双引号替换为空（Excel里用两个双引号代表一个实际引号）
• --(...)：把布尔判断结果（TRUE/FALSE）转换成1/0
• SUMPRODUCT：对所有符合条件的1求和，得到总数

如果是用动态列引用的版本，公式可以写成：

=SUMPRODUCT(--(SUBSTITUTE(INDEX($A:$Z,1,MATCH("Business",$1:$1,0)):INDEX($A:$Z,ROWS($A:$A),MATCH("Business",$1:$1,0)), """", "")="test"))

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