财务工作情景中经典Excel公式集锦及解析（第一季：科目余额表及明细账）（文末有赠书）
我的图书馆
财务工作情景中经典Excel公式集锦及解析（第一季：科目余额表及明细账）（文末有赠书）
第一季 科目余额表及明细账常用公式1按科目级次筛选需求背景：在财务日常工作中，经常需要将科目余额表或其他代码按层级进行筛选，比如筛选出总账科目、筛选出二级科目。解决方案：由于科目代码的格式都是固定的，比如总账4个字符长度，二级7个字符长度。因而，这个需求实际上就是按字符个数筛选。我们通常是用LEN函数构造辅助列计算字符个数，再对辅助列进行筛选。实际上一个小技巧就可帮助我们轻松按字符个数筛选：选中表格，然后点击自动筛选，再在筛选搜索框中输入“????”（英文半角状态下输入），即可筛选出4个字符长度的记录。要筛选其他长度的记录，以此类推。具体操作详见操作演示：知识点解释：?是通配符，代表单个字符。所以在筛选搜索框里输入几个问号就代表筛选几个字符的记录，对字母、汉字、数字、字符均有效。我们将此知识点进一步拓展，可以按字符个数求和，比如对所有总账科目（字符长度为四个）进行求和的公式：=SUMIF(A2:A22,'????',C2:C22)2判断科目是否为最末级需求背景：日常工作中我们将科目余额表导出，通常包含了第一级到最后一级，要分别筛选总账科目、二级科目等可以使用前文中的技巧，那如果要筛选或标注出最末级科目，该怎么办呢？解决方案：可以使用辅助列法。辅助列可以化繁为简。先使用公式判断是否为最末级，然后筛选出该辅助列为“最末级“的记录行，公式如下：=IF(LEN(A2)>=LEN(A3),'最末级',“”)知识点解释：在《“偷懒的技术：打造财务Excel达人》中说过“要设计一套功能强大的财务工作表，更需要的是表格设计过程中的逻辑思维和函数的拓展应用能力”，在编写公式前，先不要忙着琢磨用什么函数，而应该分析数据的规律，总结出规律后，再编写公式。粗一看，要判断科目是否为最末级，感觉无从下手，但是我们分析一下科目余额表就可发现，同一个总账科目下越是明细级的科目，其代码越长（废话，这个财务人都知道）。也就是说，如果用本行的科目代码字符数与下一行的相比，如果字符数与下一行相等（同级）或比它多（更明细级），它就是最末级的（前提条件是科目余额表要按科目先后顺序排列）。说明：本案例如果使用高级筛选，在F2单元格输入筛选条件公式=LEN(A2)>=LEN(A3)再以其为条件筛选，可筛选出大部分符合条件的记录。由于条件公式中的A3按要求应该为$A$3，但是如果写成这样，就与需求不符了，故写成A3，这样一来就不符合高级筛选“条件公式中除记录的第一行外的所有其他引用要求是绝对引用”这一条件，因而最后一行未筛选出，存在小小的瑕疵，因而不适合使用高级筛选。3分离科目代码和科目名称 需求背景：某些财务软件导出的科目余额表中是“偷懒的技术“这样的格式，需要将代码和名称分离，或者遇到不规范的数据，如人名与手机号“龙逸凡18X”，需要将数字和汉字分离为二列。解决方案：如果科目代码长度一致，或者代码和名字中有某个固定分隔符，则可使用“分列”功能来分离科目代码和名称，如果没有，则需要使用下面的公式：取科目代码=LEFT(A2,2*LEN(A2)-LENB(A2))取科目名称=RIGHT(A2,LENB(A2)-LEN(A2))这个公式不太完善，当名称中有字母或数字时，则公式结果会出错，比如B16、B17单元格，这种情况下提取科目代码需使用下面的数组公式：=LEFT(A2,MIN(IF(MID(A2,ROW($1:$99),1)>='a',ROW($1:$99),99))-1)输入完后需要同时按Ctrl+Shift+Enter三键输入如果电脑上安装了Excel2013以后的版本，也可使用快速填充，具体操作详见下面的操作演示知识点解释：第一个公式中的Len函数：计算字符数。LENB函数：计算字节数，一个汉字为二个字节。所以可以用LENB-LEN计算字符串中汉字的个数。由于公式是根据汉字个数来分离数字和汉字，所以，当名称中包含字母或数字，以及数字和汉字相互夹杂时，公式提取的结果就不符合要求。第二个公式的设计思路是逐个截取字符串中的每一个字符，判断其是否为字母或汉字，即是否为”a”之后的字符（在Excel里，如按升序排列，则字母在数字后，汉字在字母后，也就是1→9→a→z→吖（ā）→酢（zuò），这也是它们的大小顺序）。公式使用ROW($1:$99)生成1到99的常量数组，写成99只是为了保证大于等于字符串的长度，根据实际情况也可改成50，30等。MID(A2,ROW($1:$99),1)是依次截止A2单元格的第1个，第2个。。。第99个。截取出的字符如果是字母或汉字，则给其字符所在位置的顺序号，否则，给它的顺序号是99。再用MIN函数来提取第一个字母、汉字的位置。最后用LEFT来截取左边的数字和字符组成的代码。同理，如果要提取科目名称，用下列公式即可（别忘了最后用三键输入）：=RIGHT(A2,LEN(A2)-MIN(IF(MID(A2,ROW($1:$99),1)>='a',ROW($1:$99),99))+1)当然，使用上述公司还得有个前提，即避免分录名称的第一个字为数字，比如“1小组”、“2车间”等等，而应采用“一小组”、“二车间”的命名方式。4填写完整的科目名称需求背景：财务软件导出的科目余额表为了简洁，在科目名称列，只保留了本级科目的名称，本级之前的名称都没保留，这么做的弊端是筛选时无法根据名称来筛选本科目下某个级次科目，并且当明细科目较多，而我们记不住科目代码时，无法知晓其总账科目是什么。解决方法：用公式写出完整科目名称。在C2单元格编制下面的公式：=IFNA(VLOOKUP(LEFT(A2,-LOOKUP(0,1-FIND('.',A2,ROW($1:$20)))),A$1:C1,3,)&'-'&B2,B2)然后下拉填充即可。知识点解释：这个公式很经典，有二个关键思路，1、找出某符号最后一个的位置。2、利用前面行公式的计算结果。ROW($1:$20)生成1到20的常量数组（假设代码长度都在20以内）。FIND('.',A2,ROW($1:$20))依次从第1位、第2位…第20位开始查找'.'符号。如果没有查找到则出错。此公式将生成一个由错误值及'.'所在位置组成的序列。1-FIND('.',A2,ROW($1:$20))将生成错误值与0,-1,-2….组成的序列。-LOOKUP(0,1-FIND('.',A2,ROW($1:$20)))此公式在序列中查找0，根据其查找原理将返回最后一个'.'前面那个字符的位置。如果对这点还是不太理解，请到龙逸凡博客.cn/aluolhm阅读《深入理解LOOKUP：LOOKUP函数的查找原理》、《公式-LOOKUP(1,-LEFT(A1,ROW($1:$10)))详解》。5取会计科目的最末级科目名称需求背景：财务工作中有时候需要做前面“填写完整的科目名称“的返向操作，在完整会计科目中取最末级科目的名称，类似的需求还有取物料代码1.01.15.38189的最后一节。解决方案：编制下面的公式：=IFNA(RIGHT(B2,LEN(B2)+LOOKUP(0,-FIND('-',B2,ROW($1:$22)))),B2)IFNA函数仅限于Excel2013及Excel2016版，Excel版请使用IFERROR函数。当然，这里我们也可使用“快速填充”功能来提取最末级科目。知识点解释：关于此公式的解释参见前文。6将上面行的科目代码及科目名称往下填充需求背景：某些财务软件批量导出多个科目的明细账时，只有科目的第一行显示了科目代码和本级明细科目的名称，无法进行正确筛选，当该科目明细账较多时，也影响阅读。解决方法：将本科目第一行的科目代码和科目名称填充到本明细科目下所有的空白单元格。选定所有明细账的第一行到最后一行A1：B22单元格区域，按F5调出定位对话框，点击“定位条件”，然后双击“空值”选项（直接双击某选项，等同于点选后该选项再去点“确定”），即可选定A1：B22区域中所有的空白单元格。此时，请勿点击鼠标。直接键入“＝A3”（或键入＝号后，按一下向上的箭头），然后按住ctrl不放，敲击Enter，即可在所有空白单元格键入公式。具体操作见下面的操作演示：知识点解释：F5功能键在Excel中是定位功能的快捷键，定位功能是精确制导的武器，它可根据单元格的属性来选择单元格。常用于选择数字单元格、公式单元格、空白单元格、没有隐藏的单元格。尊重原创，本文经作者授权发布
馆藏&12115
TA的推荐TA的最新馆藏[转]&[转]&[转]&[转]&[转]&[转]&查看: 5937|回复: 10
如何取出科目余额表末级科目编号
中级二, 积分 1038, 距离下一级还需 12 积分
积分学习力
魅力值 影响力
消费券 Ti币好友
在线时间 小时
最后登录月度优秀 次
免费注册成为本站会员，享用更多功能，结识更多Office办公高手！
才可以下载或查看，没有帐号？
如图，如何取出科目余额表最末级的科目编号，而上级科目则返回错误值
未命名.jpg (53.46 KB, 下载次数: 0)
如何取出科目余额表末级科目编号
16:13 上传
16:13 上传
点击文件名下载附件
3.72 KB, 下载次数: 35
如何取出科目余额表末级科目编号
学office，哪能不关注全网最大的Office类微博（新浪）
积分学习力
魅力值 影响力
消费券 Ti币好友
在线时间 小时
最后登录月度优秀 次管理次数 次
本帖最后由 kevinchengcw 于
16:47 编辑
Sub test()
Dim Dic As Object, Arr, I%, N&, Str$
I = 4
Arr = Range(&a2:a& & Cells(Rows.Count, 1).End(3).Row).Formula
Set Dic = CreateObject(&scripting.dictionary&)
For N = LBound(Arr) To UBound(Arr)
& & If Trim(Arr(N, 1)) && && Then
& && &&&Str = Left(Arr(N, 1), I)
& && &&&If Dic.exists(Str) Then
& && && && &If Len(Arr(N, 1)) & Dic(Str) Then Dic(Str) = Len(Arr(N, 1))
& && &&&Else
& && && && &Dic.Add Str, Len(Arr(N, 1))
& && &&&End If
& & End If
Next N
For N = LBound(Arr) To UBound(Arr)
& & If Trim(Arr(N, 1)) && && Then
& && &&&Str = Left(Arr(N, 1), I)
& && &&&If Len(Arr(N, 1)) & Dic(Str) Then Arr(N, 1) = &&
& & End If
Next N
With [e2].Resize(UBound(Arr))
& & .NumberFormatLocal = &@&
& & .Value = Arr
End With
Set Dic = Nothing
End Sub复制代码结果放E列供对比。
学office，哪能不关注全网最大的Office类微博（新浪）
联系方式：请发站内消息给站长 apolloh
D2输入公式，向下复制：=IF(COUNTIF(A:A,A2&&*&)=1,A2,NA())复制代码
学office，哪能不关注全网最大的Office类微博（新浪）
积分学习力
魅力值 影响力
消费券 Ti币好友
在线时间 小时
最后登录月度优秀 次管理次数 次
不知道2 楼的代码对这样的明细可以返回不\
学office，哪能不关注全网最大的Office类微博（新浪）
积分学习力
魅力值 影响力
消费券 Ti币好友
在线时间 小时
最后登录月度优秀 次管理次数 次
liuguansky
就知道你来一定是找碴的，修改那个I值就行了，初级总有个长度标准吧，乱的也可以写出来，只不过麻烦点，但实际应用中又有多少意义呢?
学office，哪能不关注全网最大的Office类微博（新浪）
积分学习力
魅力值 影响力
消费券 Ti币好友
在线时间 小时
最后登录月度优秀 次管理次数 次
4# liuguansky&&
就知道你来一定是找碴的，修改那个I值就行了，初级总有个长度标准吧，乱的也可以写出来，只不过麻烦点，但实际应用中又有多少意义呢?
kevinchengcw 发表于
实际上的科目很正常.
因为科目并不是只有两级,可能会有多级.
学office，哪能不关注全网最大的Office类微博（新浪）
积分学习力
魅力值 影响力
消费券 Ti币好友
在线时间 小时
最后登录月度优秀 次管理次数 次
一般有编码规则的比如这里的4-2-2-2-2-...这样
第一位是4可以确认,但是一级科目下可能不只有2级科目可能有三级,四级科目,而且最末级的科目长度不一定一致.
学office，哪能不关注全网最大的Office类微博（新浪）
积分学习力
魅力值 影响力
消费券 Ti币好友
在线时间 小时
最后登录月度优秀 次管理次数 次
另外,很明显我不是找碴,我是说明你的代码的适用性.可能会与楼主的要求不符.
草版的公式当然更不符了...中间加一个问号估计好点.=1改成&1
学office，哪能不关注全网最大的Office类微博（新浪）
积分学习力
魅力值 影响力
消费券 Ti币好友
在线时间 小时
最后登录月度优秀 次管理次数 次
等花花写个我学习一下
学office，哪能不关注全网最大的Office类微博（新浪）
联系方式：请发站内消息给站长 apolloh
这些争论，还是要看楼主如何定。以1楼的示例来说，我们的方法解决了。是否另有其他规则，那需要提问者加强说明和示例。
学office，哪能不关注全网最大的Office类微博（新浪）
Excel技巧网的会员探讨问题仅代表其个人意见，与网站的立场无关。任何违反国家和地方相关法律法规的言论，本站有义务协助政府相关部门追究发言者的责任！
本站中非注明转载文章与案例的版权为作者与Excel技巧网共有。若非原文作者，本站之外任何单位或个人未经允许，不得将其用于商业用途。
若非原文作者，任何形式的非商业性转载必须获得Excel技巧网或作者允许，并注明作者和出处。
会员发表的帖子如涉及版权纠纷，须自行负责。详情请参考注册时的网站服务条款。
本站特聘法律顾问：沈学律师
Powered by查看: 2663|回复: 18
求助用vba制作的可以由科目余额表直接生成审计底稿示例。
财务人如何应对失业危机？
最新政策解读、就业形势分析
有此证，升职加薪不是梦
你距离最高证书只有一指的距离
港中大-上国会强强联手
全球23强，免联考
求助用vba制作的可以由科目余额表直接生成审计底稿示例。要求有源代码哦
会计视野论坛帖子版权归原作者所有。对发帖人声明原创的帖子，中国会计视野有使用权和转载权。其他网站在写明来源、作者、会计视野论坛首发网址的情况下可以转载，原创作者保留禁止转载和向其他转载网站索取稿酬的权力。
可以参考这个帖子
可以参考这个帖子
/forum.php?mod=viewthread&tid=4937721&extra=page%3D1%26filter% ...
看了一下，没有那个功能，也没有可以借鉴的地方，那个程序倒是挺不错的。
看了一下，没有那个功能，也没有可以借鉴的地方，那个程序倒是挺不错的。
其实你的要求不一定要通过VBA的
其实你的要求不一定要通过VBA的
恩，不管是vb还是c#只要有能达到目的的就行，关键是得有源码啊
恩，不管是vb还是c#只要有能达到目的的就行，关键是得有源码啊
没人知道你的报表格式如何，有什么特别的汇总要求，这样提问是没结果的。
没人知道你的报表格式如何，有什么特别的汇总要求，这样提问是没结果的。
做过审计的都知道啊，从科目余额表生成底稿
没有完全通用的代码，你这样提问，只能说明你不懂VBA。&
做过审计的都知道啊，从科目余额表生成底稿
是没有通用的，但是此处我要 的只是示例，示例懂么亲
是没有通用的，但是此处我要 的只是示例，示例懂么亲
给你个示例有用吗？你会改代码吗？
给你个示例有用吗？你会改代码吗？
会啊，要不我要那个干吗，只要有源码就可以的
给你个示例有用吗？你会改代码吗？
我有一个示例，你改完后能否共享一下？
我有一个示例，你改完后能否共享一下？
可以啊，我邮箱
能否也给我发一份，谢谢，
sc204400 发表于
我有一个示例，你改完后能否共享一下？
我直接做报表，都不用科目余额表
站长推荐 /1
申报IPO在结构上财务规范占60%为主，其中财务规范化占的时间也是最长的，从资料准备和财务相关总价是占80%以上。所以很多公司都以财务为主开展申报工作。本次嘉宾主要分享IPO财务相关部份，要准备什么，要做什么，要达到什么程度，容易出现哪些问题等等。活动由元年云快报支持举办。
Powered by