【理解】运用数据透视表制作三栏账

最新推荐文章于 2024-10-31 21:32:30 发布

取啥都被占用

最新推荐文章于 2024-10-31 21:32:30 发布

阅读量1.8k

点赞数

分类专栏： Excel_VBA 文章标签：长公式 vba

本文链接：https://blog.csdn.net/u011410413/article/details/43795033

版权

Excel_VBA 专栏收录该内容

38 篇文章 4 订阅

订阅专栏

本文介绍了如何利用Excel2010数据透视表功能，通过借贷自定义式、VBA编程实现三栏账的高效制作。详细解释了借贷显示、自定义式应用、数据有效性与名称管理器的使用，同时展示了科目表与白底二级目录如何联动。通过定义代码和公式，实现了基于一级目录的动态二级目录更新。

摘要由CSDN通过智能技术生成

今天学习了三栏账制作出自书本《Excel2010数据透视表应用大全》，要我说在全书中，实现这个事件步骤还是不少的。然而要写一篇小文章的关键在于，对于我个人来说这个三栏账对Excel的公式和功能的应用还是很丰富的，并且很有用。以下就是实现效果，高亮部分是自己觉得需要注明的地方。

三个红框是想着重介绍的第一部分，

首先讲借贷那个，其实它就是个表格自定义格式的应用，具体公式如下。

[>0]"借";[<0]"贷";"平"

再说它隔壁那一竖排，为啥没有正负号呢？还是因为自定义格式。

#,##0.00_);#,##0.00_)

最后那个白底二级目录其实呼应了蓝底。蓝底的二级会因为白底的二级而改变。这个主要是VBA实现的啦。但是白底的那个二级目录可以因为选不同的一级目录而改变哦。它应用了数据有效性的功能，使用了List格式。而这里真正比较tricky的其实在于这个变动的list是怎么来的呢？其实这是使用了名称管理器定义好了这个list如何随一级目录改变的。

以下截图是科目表，即一级科目与二级科目的关系。

名称管理器里面要定义一个

code2=OFFSET(科目表!$B$1,<span style="color:#ff0000;">MATCH(三栏账!$C$6,科目表!$B$1:$B$998,)-1</span>,2,<span style="color:#ff0000;">COUNTIF(科目表!$B$4:$B$998,三栏账!$C$6)</span>)

公式解析：先定位好抓list的位置，是从科目表的B1开始。让三栏目账的一级目录算选的项目来match科目表里面的一级目录的位置。找到以后往B栏的右边移两步，也就到了二级科目了。然后这个list的长度就是最后的参数，数一下这个一级目录的步长，取下一级目录的步长即可。二级目录的List也就全了。

最后再注明一下白底二级目录怎么带动蓝底二级目录。代码本身不难。但要小心那些乱码处（如果Excel版本是英文那么 km2 = "All"，是中文的话应该是"全部"）。其他几处乱码也是这个问题。

Sub select2()

   Dim km2$
   Application.ScreenUpdating = False

   If Range("i6") = "" Then
     km2 = "<span style="color:#ff0000;">È«²¿</span>"
   Else
     km2 = Range("i6")
   End If
   
   ActiveSheet.PivotTables("<span style="color:#ff0000;">Êý¾ÝÍ¸ÊÓ±í1</span>").PivotFields("<span style="color:#ff0000;">¶þ¼¶¿ÆÄ¿</span>").CurrentPage = km2
   Application.ScreenUpdating = True

End Sub