VBA里怎么搞定隐藏列不被汇总数据库，防止数据错乱的小技巧分享

关于VBA里怎么搞定隐藏列不被汇总，防止数据错乱，这里有几个从实际经验里总结出来的小技巧，很多人用Excel做数据库汇总时，经常会手动或者用VBA隐藏一些不需要的列，比如中间的计算过程、备注信息等，但问题来了，当你用SUM、AVERAGE这类函数，或者用VBA循环整列去汇总数据时，这些隐藏列里的数据常常会被不小心加进去，导致最后结果完全不对，等发现的时候可能已经晚了，下面这些方法,就是专门对付这个麻烦的。

第一个最直接的办法：别用整列引用，而是老老实实指定范围。 这是很多老手都会强调的一点（来源：多个Excel论坛的实战讨论），你有一个数据库从A列到M列，但你知道G列和J列是隐藏的，里面放着一些临时数据，你在写VBA汇总代码的时候，就不要用 Columns("D:M").Sum 这种偷懒的写法，因为这样VBA会真的把D到M每一列，包括隐藏的G和J，全都算进去，你应该明确指定需要的列：Range("D,E,F,H,I,K,L,M").Sum，或者用 Union 方法在VBA里把需要的列区域拼起来，虽然写起来麻烦一点，但这是最保险的，代码意图清清楚楚,自己以后看也不会迷糊。

第二个技巧：利用工作表函数里的“SUBTOTAL”来帮忙。 这个函数有个很大的优点，就是它能自动忽略被隐藏行里的数据（来源：微软官方文档对SUBTOTAL函数的说明），注意，这里主要是针对“隐藏行”，但对于列也有变通用法，你可以在VBA里这样用：WorksheetFunction.Subtotal(109, YourRange)，这个109代表求和，并且忽略隐藏行，如果你的数据是像列表一样一行一行记录的，隐藏了某些行不想汇总，用这个方法就非常有效，但它的局限是，它对手动隐藏的“列”数据无效，它只认行，如果你的数据是横着排列的，或者你隐藏的是列,那这个方法就不太对症了。

针对隐藏列，第三个核心技巧就是：让VBA能判断列是不是隐藏的，只汇总看得见的列。 这才是VBA发挥威力的地方，你不能依赖Excel的标准函数，得自己写逻辑，思路很简单，就是让代码在循环或者计算时，先检查每一列的 Hidden 属性,这里分享一段可以直接用的代码片段：

Function SumVisibleColumns(TargetRange As Range) As Double
    Dim rngCol As Range
    Dim Total As Double
    Total = 0
    For Each rngCol In TargetRange.Columns
        If rngCol.Hidden = False Then
            Total = Total + WorksheetFunction.Sum(rngCol)
        End If
    Next rngCol
    SumVisibleColumns = Total
End Function

这个你自己写的小工具（函数），用法是这样的：你在需要汇总的地方，像用普通SUM一样，调用 =SumVisibleColumns(D1:M100)，它就会自动跳过D到M这个区域里所有被隐藏的列，只把可见列的数字加起来，你可以把它存在你的个人宏工作簿里，随时调用，这个方法的好处是特别灵活，不管你是手动隐藏的列，还是用VBA根据条件自动隐藏的列，它都能准确识别,从根本上避免了数据错乱。

第四个实用建议：在设计和汇总前，先做好数据表的“隔离”。 （来源：基于数据分析工作流程的最佳实践），这更像是一个工作习惯，与其在一个大表格里又放原始数据、又放计算过程、又放分析结果，然后靠隐藏列来整理界面，不如一开始就分开，你可以用VBA，把需要汇总的“干净”数据库单独复制到一个新的工作表，这个新表里只保留需要汇总的那些列，然后所有的汇总计算，都基于这个“干净”的表来进行，原始表你怎么隐藏、怎么调整都没关系，用VBA自动完成这个复制和清理的过程，这样源数据和汇总区物理分开了,就永远不会有隐藏列掺和进来的风险。

最后再提醒一个很容易踩的坑：小心“筛选”造成的隐藏和“手动隐藏”不是一回事。 你用筛选功能隐藏的行，SUBTOTAL函数能处理；但你直接右键隐藏的行或列，SUBTOTAL函数（参数109）只对行有效，对列无效，而上面那个自己写的VBA函数，是通过检查 Hidden 属性工作的，所以无论是哪种方式隐藏的，它都能抓到，搞清楚你数据隐藏的方式，再选对方法,这点很重要。

在VBA里搞定隐藏列不被汇总，关键就是不要相信默认的整列操作，要主动让代码去“看”哪些列是可见的，要么在引用范围上做到精确，要么就写一个智能的判断函数，这是最稳妥、最能防止数据错乱的两个核心技巧，把这些小技巧用到你的日常处理里,能省下大量核对和纠错的时间。