Excel中用周边单元格平均值填充矩阵空白单元格（解决循环引用）

嘿，这个坑我太熟了——用周边平均值填充空白格时踩循环引用，本质就是公式不小心把自己算进去了，Excel直接懵圈：“我要算这个单元格的值，得先知道它自己的值？这不扯嘛！” 给你几个实用的解决办法，按需选：

1. 精准引用周边单元格（适合空白格少的情况） #

直接把当前空白格排除在引用范围外，手动列出周边的8个单元格（边缘单元格就列实际存在的）。比如空白格在B2，公式就写：

=AVERAGE(A1,A2,A3,B1,B3,C1,C2,C3)

这样完全不会涉及自身，自然没有循环引用。缺点是每个空白格都要调整引用，适合零散的空白单元格。

2. 启用迭代计算（适合批量填充大量空白格） #

如果空白格很多，手动改公式太麻烦，就开Excel的迭代计算功能，让它允许一次循环（只计算一次周边平均值，不会无限循环）：

• 点击「文件」→「选项」→「公式」
• 勾选「启用迭代计算」，把最多迭代次数设为1（关键！设多了会反复计算，值就不准了）
• 然后在所有空白单元格（或直接全区域）输入公式：

  =IF(ISBLANK(B2),AVERAGE(OFFSET(B2,-1,-1,3,3)),B2)
  

  这里OFFSET(B2,-1,-1,3,3)是取B2周围3x3的区域，因为开了1次迭代，Excel会自动忽略当前单元格的空白状态，用周边已有的数值算出平均值填充进去，完美避开循环问题。

3. 用Power Query处理（最稳妥，无循环风险） #

如果不想碰公式和迭代，Power Query是最优解——它在后台处理数据，完全不会有循环问题：

• 选中你的数据区域，点击「数据」→「从表格/区域」（记得勾选「我的表格有标题」如果你的数据有表头）
• 在Power Query编辑器里：
  1. 先把空白单元格转成null：点击「主页」→「替换值」，查找内容留空，替换为null，点击确定
  2. 添加自定义列：点击「添加列」→「自定义列」，输入类似以下的公式（根据你的实际列名调整）：

     = List.Average(
         {
             try Table.Rows(#"Changed Type")[Index-1][Column1] otherwise null,
             try Table.Rows(#"Changed Type")[Index+1][Column1] otherwise null,
             try Table.Rows(#"Changed Type")[Index][Column2] otherwise null,
             try Table.Rows(#"Changed Type")[Index][Column0] otherwise null,
             try Table.Rows(#"Changed Type")[Index-1][Column2] otherwise null,
             try Table.Rows(#"Changed Type")[Index-1][Column0] otherwise null,
             try Table.Rows(#"Changed Type")[Index+1][Column2] otherwise null,
             try Table.Rows(#"Changed Type")[Index+1][Column0] otherwise null
         }
       )
     

     这个公式会取当前单元格上下左右及对角线的8个值，自动忽略不存在的边缘单元格，然后求平均
  3. 最后把原来的空白列替换成自定义列的值，点击「关闭并上载」，数据就会回到Excel里，以后数据更新了右键刷新即可。

小提醒 #

如果某个空白格的周边全是空白，以上方法都会返回空白，这时候你可以额外处理——比如用更大范围的平均值，或者在公式里加IFERROR(..., 0)来替换空白为默认值。

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