Ian's Blog
0%

熟练使用Excel基本功可以解决怎样的问题？

发表于

最近在解决一个Excel需求的过程中，遇到的一些细节比较典型，总算断断续续地挤出时间梳理记录了下来。如果有同学能从中受到启发，就达到了记录的目的。为了避免泄露公司信息，所有日期和敏感数据均做了处理。

一、问题描述

  • 业务系统在自动推送凭证信息时，每个凭证内的会计分录没有按照财务规范的借贷顺序排列。凭证若如此归档，显得不专业。
  • 分录的现有排列顺序非常随机，需要按照凭证序号进行分组，并采用十几种指定序列重新排序。
  • 打印模块不能实现自动重排或实现成本较高。但可以将分录导出为Excel文件。
  • 对Excel文件重新排序后，按照指定格式，批量打印凭证。

由于分录数量大于7000条，凭证数量大于2000个，人工解决显然是不明智的。如果单纯用程序解决这个问题，自然不难。但对于日常仅使用Excel基本功能和函数，很少接触编程的同学来说，乍一看可能觉得无法完成。

二、解决思路

当把一个复杂问题拆到最小，解决方案一般就会自动浮现。

既然排除了纯粹编程的方法，我们可以试着将问题拆解为Excel内置功能和函数能够解决的小问题（例如辅助列、判断和排序），只在必要时使用尽量简单的内置VBA程序（例如循环）。

1. 筛选

  • 系统导出的凭证分录，是一段时间内的所有内容，包括不需要重新排序的凭证分录。观察发现，这部分凭证一定会包含两种科目：唯一类型和不唯一类型。通过创建辅助列，使用countif函数或vlookup函数在指定序列中查找唯一类型的科目编码，对返回结果为false或n/a（error）的凭证编号进行记录，并将该凭证下的不唯一类型科目做标记后（同样可以使用vlookup配合if和iserror函数），筛选删除。

2. 分类和分组

  • Excel自带“自定义排序”的功能，但这次我们不会使用。因为不同的凭证种类，具有同样编号的科目分录顺序也不同，例如数字1、2、3、4在A类凭证中，分录排序为134；在B类凭证中，排序为231，也即数字1可能排在首位，也可能排在末位。使用单一自定义序列的方法，不易处理。所幸大部分凭证有一个特点：凭证包含唯一科目编码（类似上述A类凭证中的唯一编号数字4），或具有特殊的摘要。因此，可以继续使用辅助列、vlookup、countif和*号通配符进行查找分类，分类后使用if函数给予不同的顺序权重。
  • 凭证分组问题，虽然可以通过“主要关键字”和“次要关键字”来解决，我一般习惯使用&符号连接字符串后直接排序，更加直观不易出错。

3. 指定格式批量打印

  • 按照最终的凭证格式，排版制作工作表是最基础的Excel操作。
  • 凭证格式制作完成后，可以选择在一个工作簿中通过多工作表或分页符来生成所有凭证文件，但本次处理的凭证数量大于2000个，并不适合放在一个文件中。最佳方案是将每一份凭证保存为一页或多页的pdf文件，并按需要对多个pdf文件进行批量合并。
  • 将正确排序后的凭证及科目内容，逐行填入到工作表中有两种方法，一是灵活运用match、index和offset函数；二是VBA的for循环语句。一般情况下，在学校计算机课程中稍微注意听讲的同学都能理解和使用for语句。如果使用match等函数填写表单，在处理多页凭证时比较麻烦，而且最终仍要通过for循环语句进行打印，因此直接使用for循环语句来填写工作表并打印。

4. 其他问题

  • 因为凭证数量庞大，且由系统导出，在实际操作以前可能无法对各种可能性及特殊情况考虑的面面俱到。但通过上述推演，相信可以解决95%以上的问题，所以值得尝试。
  • 后续操作时也确实遇到了意外情况，其中最特殊的是包含多次借贷分录的超过20页的单个凭证。此类凭证只有3种科目编码，反复成对出现，只能通过借贷金额的平衡进行分组。解决这个问题时不得不使用VBA程序来穷举，但也只需要使用最基础的if和for语句。

5. 所需知识汇总

  • 辅助列（替代程序中的数组变量）
  • if函数、countif函数和iserror函数（部分替代程序中的判断语句）
  • vlookup函数（部分替代程序中的循环查找语句）
  • *号通配符和&连接字符
  • VBA程序的if和for语句的最基本使用

既然理论上可以使用Excel的基本功解决问题，接下来就可以在正确的方向上投入精力逐步处理细节问题。

三、具体操作

1. 确定排序种类

整理后需要处理的凭证有12种，涉及17个科目编码，每种凭证包含2到5个独立的科目编码，例如：

类型1          类型2           类型3
11220101       220203         1405010301 
6001010301     2221010101     220203
2221010201     6401010104
6401010104     220201
1406010301

我们是否需要设计12种排序方案呢？并不需要，而且这样设计不利于问题的简化。通过整理不同类型凭证中的科目编码关系，在大多数情况下，可以使用以下序列和权重进行无脑排序：

科目编码    权重
100201      11
11220101    12
6001010301  13
2221010201  14
14050106    15
64010102    16
220203      17
2221010101  18
6401010104  19
14050101    20
220201      21
1406010301  22
1405010301  23
22020401    24
22020402    25
100202      26
66030906    27

以上序列的特殊情况：

  • 特殊情况在单个凭证中不会同时出现，因此可以设定同一权重。
  • 摘要包含“付款单过账”时，科目编码100201（权重11）需要排在科目编码220201（权重21）后面，此时可以变更100201的权重为99。
  • 摘要包含“采购入库”时，科目编码220203（权重17）需要排在科目编码1405010301（权重23）后面，此时可以变更220203的权重为99。
  • 摘要包含“库存调拨”时，凭证中的借贷科目编码均为14050101，按照“一借一贷”的原则，将贷方金额大于0的分录权重变更为99。
  • 摘要包含“费用预提”时，属于特殊情况，后面再另行处理。

2. 删除无关凭证

搞清科目编码的范围后，就可以将无关凭证识别出来了。

  • 确认需要输入公式的单元格为“常规”格式（“文本”单元格中的公式不会生效）。
  • 将“科目编码”列通过“数据”-“分列”的方法批量转换为数字。
  • 创建名称为“sort”的新工作表，按顺序在A列录入科目编码，B列录入权重数字
  • 在分录表中新建辅助列1，公式如下（手动换行方便在手机端显示，实际为一行）：
=IF(ISERROR(VLOOKUP(L2,sort!$A$1:$B$17,2,FALSE)),
 C2&E2,VLOOKUP(L2,sort!$A$1:$B$17,2,FALSE))
  • 新建辅助列2，公式如下：
=COUNTIF(M:M,C2&E2)
  • 此时无关凭证的分录均已被标记，将大于0的标记筛选后全部删除即可。标记结果如图（手机浏览时应该可以直接放大页面查看）：
    找出无关凭证

3. 判断权重并排序

  • 按照权重设计思路，直接给出排序辅助列公式如下：
=C2&E2&"-"&IF(OR(AND(OR(COUNTIF(L2,"100201"),COUNTIF(L2,"220203")),
 OR(COUNTIF(K2,"付款单过账*"),COUNTIF(K2,"采购入库*"))),
 AND(COUNTIF(L2,"14050101"),COUNTIF(K2,"库存调拨*"),S2>0)),
 99,VLOOKUP(L2,sort!$A$1:$B$17,2,FALSE))

  • 结果如图：
    权重排序

  • 直接将辅助列升序排列即可。

4. 特殊情况排序

  • 将涉及“费用预提”的凭证筛选出来，单独放置在一个工作表中操作。
  • 以0435号凭证为例，大家就知道是怎么回事了，它的分录条目是138个：
    特殊凭证0435
  • 显然，此前的权重排序法在这里是完全无效的。并且存在科目编码220203与14050106、220203与64010102两个一组，或科目编码220203与14050106、64010102三个一组的情况。
  • 通过观察，借贷金额都是唯一的，先利用这一点创建4个辅助列，将两个一组的情况筛选出来排序：
    辅助排序
  • 辅助列的具体作用及公式如下：
'辅助列1 找出原币金额唯一的科目编码标记为tbc（无与之匹配的借贷金额）
=IF(COUNTIF(S:S,S2)=1,"tbc",2)

'辅助列2 将科目编码的权重按照1、2、3设定
=IF(L2=220203,3,IF(L2=14050106,1,2))

'辅助列3 将tbc标记的其中一个科目编码用唯一行号标记
=IF(AND(M2="tbc",L2=220203),ROW(),"")

'辅助列4 两个一组或三个一组的最终排序编号
=IF(M2="found",C2&E2&"-"&O2&N2,C2&E2&"-"&S2&"-"&N2)
  • 以“辅助列1”为主要关键字，“科目编码”为次要关键字进行升序排序：
    关键字排序
  • 使用快捷键“Ctrl + F11”打开VBA界面，用代码穷举三个一组的匹配情况（关键代码只有几行）：
Sub find_entry_seq()
    '权重1的科目编码范围
    For x_cell = 165 To 193
        a_value = Worksheets("seq").Range("S" & x_cell)
        '权重2的科目编码范围
        For y_cell = 194 To 222
            b_value = Worksheets("seq").Range("S" & y_cell)
            ab_value = a_value + b_value
            '权重3的科目编码范围
            For z_cell = 136 To 164
                c_value = Worksheets("seq").Range("S" & z_cell)
                '因为Excel选项的默认精度可能会导致尾差，此处用范围代替=0
                If Abs(ab_value - c_value) < 0.5 Then
                    Worksheets("seq").Range("O" & x_cell) = Worksheets("seq").Range("O" & z_cell)
                    Worksheets("seq").Range("O" & y_cell) = Worksheets("seq").Range("O" & z_cell)
                    Worksheets("seq").Range("O" & z_cell) = Worksheets("seq").Range("O" & z_cell)
                    Worksheets("seq").Range("M" & x_cell) = "found"
                    Worksheets("seq").Range("M" & y_cell) = "found"
                    Worksheets("seq").Range("M" & z_cell) = "found"
                    Exit For
                End If
            Next z_cell
        Next y_cell
   Next x_cell
End Sub
  • 按下F5执行VBA程序后，所有分录的排序权重就配置好了：
    VBA排序后
  • 将辅助列排序后，最终排序结果符合预期（两个一组或三个一组）：
    最终排序
  • 至此，排序工作全部完成。

5. 打印输出

  • 凭证中包含人民币和美元两种货币，首先制作两种格式的打印模板。
  • 人民币模板：
    人民币模板
  • 美元模板：
    美元模板
  • 为了减少代码量，继续利用辅助列：
    ID辅助列
  • 辅助列的具体作用和公式如下：
'辅助列1 删除重复项以后的凭证号清单
=entries!C2&entries!E2

'辅助列2 该凭证的分录数量（行数）
=COUNTIF(entries!M:M,id!A1)

'辅助列3 该凭证的起始行号（首行为2）
=B1+C1

'辅助列4 该凭证的货币类型
=IF(ISERROR(VLOOKUP(A1,entries!AC:AC,1,FALSE)),"CNY","USD")

'辅助列5 需要打印的凭证总数
=COUNTA(A:A)
  • 编写如下VBA代码，代码量并不多，绝大部分仅仅是按指定单元格填写数据，详见注释：
Sub generate_voucher()
' 自动打印财务凭证
    '关闭窗口刷新
    Application.ScreenUpdating = False
    '指定打印文件的输出目录
    ChDir "C:\MISC\Demo_FVouchers"
    '获取凭证总数并循环打印
    voucher_qty = Worksheets("id").Range("E1")
    For voucher_seq = 1 To voucher_qty
        voucher_currency = Worksheets("id").Range("D" & voucher_seq)
        voucher_line_qty = Worksheets("id").Range("B" & voucher_seq)
        voucher_line = Worksheets("id").Range("C" & voucher_seq)
        voucher_sum = 0
        '计算总金额
        For i = 1 To voucher_line_qty
            voucher_sum = voucher_sum + Worksheets("entries").Range("R" & voucher_line)
            voucher_line = voucher_line + 1
        Next
        voucher_sum = voucher_sum
        voucher_line = Worksheets("id").Range("C" & voucher_seq)
        '判断货币种类并填写数据
        If voucher_currency = "CNY" Then
            Worksheets("sample_rmb").Range("C10") = voucher_sum
            Worksheets("sample_rmb").Range("D10") = voucher_sum
            Worksheets("sample_rmb").Range("B3") = "        日期：" & Format(Worksheets("entries").Range("B" & voucher_line), "yyyy-mm-dd")
            Worksheets("sample_rmb").Range("D1") = Worksheets("entries").Range("Y" & voucher_line)
            Worksheets("sample_rmb").Range("D2") = Worksheets("entries").Range("E" & voucher_line)
            Worksheets("sample_rmb").Range("A11") = "过账人：" & Worksheets("entries").Range("J" & voucher_line)
            Worksheets("sample_rmb").Range("B11") = "审核人：" & Worksheets("entries").Range("H" & voucher_line)
            Worksheets("sample_rmb").Range("D11") = Worksheets("entries").Range("G" & voucher_line)
            '计算该凭证页数（每页5行分录）
            voucher_pages = Application.Ceiling(voucher_line_qty / 5, 1)
            '分页打印
            For p = 1 To voucher_pages
                Worksheets("sample_rmb").Range("D3") = "第" & p & "页/共" & voucher_pages & "页"
                Worksheets("sample_rmb").Range("A5:D9").ClearContents
                For i = 1 To 5
                    voucher_print_line = 4 + i
                    Worksheets("sample_rmb").Range("A" & voucher_print_line) = Worksheets("entries").Range("K" & voucher_line)
                    Worksheets("sample_rmb").Range("B" & voucher_print_line) = Worksheets("entries").Range("L" & voucher_line) & " - " & Worksheets("entries").Range("N" & voucher_line)
                    Worksheets("sample_rmb").Range("C" & voucher_print_line) = Worksheets("entries").Range("R" & voucher_line)
                    Worksheets("sample_rmb").Range("D" & voucher_print_line) = Worksheets("entries").Range("S" & voucher_line)
                    voucher_line = voucher_line + 1
                    voucher_line_qty = voucher_line_qty - 1
                    If voucher_line_qty = 0 Then
                        Exit For
                    End If
                Next
                fname_line = voucher_line - 1
                '统一页码格式，小于10的页码数字前补0
                If p < 10 Then
                    voucher_page = "0" & p
                Else
                    voucher_page = p
                End If
                voucher_fname = "Demo_FVoucher_" & Worksheets("entries").Range("M" & fname_line) & "_" & voucher_page
                '将该凭证页面输出为PDF文件
                Worksheets("sample_rmb").ExportAsFixedFormat Type:=xlTypePDF, Filename:=voucher_fname, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            Next
        Else
            Worksheets("sample_usd").Range("F10") = voucher_sum
            Worksheets("sample_usd").Range("G10") = voucher_sum
            Worksheets("sample_usd").Range("B3") = "     日期：" & Format(Worksheets("entries").Range("B" & voucher_line), "yyyy-mm-dd")
            Worksheets("sample_usd").Range("G1") = Worksheets("entries").Range("Y" & voucher_line)
            Worksheets("sample_usd").Range("G2") = Worksheets("entries").Range("E" & voucher_line)
            Worksheets("sample_usd").Range("A11") = "过账人：" & Worksheets("entries").Range("J" & voucher_line)
            Worksheets("sample_usd").Range("B11") = "审核人：" & Worksheets("entries").Range("H" & voucher_line)
            Worksheets("sample_usd").Range("G11") = Worksheets("entries").Range("G" & voucher_line)
            voucher_pages = Application.Ceiling(voucher_line_qty / 5, 1)
            For p = 1 To voucher_pages
                Worksheets("sample_usd").Range("G3") = "第" & p & "页/共" & voucher_pages & "页"
                Worksheets("sample_usd").Range("A5:G9").ClearContents
                For i = 1 To 5
                    voucher_print_line = 4 + i
                    Worksheets("sample_usd").Range("A" & voucher_print_line) = Worksheets("entries").Range("K" & voucher_line)
                    Worksheets("sample_usd").Range("B" & voucher_print_line) = Worksheets("entries").Range("L" & voucher_line) & " - " & Worksheets("entries").Range("N" & voucher_line)
                    Worksheets("sample_usd").Range("C" & voucher_print_line) = Worksheets("entries").Range("AB" & voucher_line)
                    voucher_fxrate = Worksheets("entries").Range("Q" & voucher_line)
                    If voucher_fxrate = 0 Then
                        voucher_fxrate = 1
                    End If
                    Worksheets("sample_usd").Range("D" & voucher_print_line) = voucher_fxrate
                    Worksheets("sample_usd").Range("E" & voucher_print_line) = Worksheets("entries").Range("P" & voucher_line)
                    Worksheets("sample_usd").Range("F" & voucher_print_line) = Worksheets("entries").Range("R" & voucher_line)
                    Worksheets("sample_usd").Range("G" & voucher_print_line) = Worksheets("entries").Range("S" & voucher_line)
                    voucher_line = voucher_line + 1
                    voucher_line_qty = voucher_line_qty - 1
                    If voucher_line_qty = 0 Then
                        Exit For
                    End If
                Next
                fname_line = voucher_line - 1
                If p < 10 Then
                    voucher_page = "0" & p
                Else
                    voucher_page = p
                End If
                voucher_fname = "Demo_FVoucher_" & Worksheets("entries").Range("M" & fname_line) & "_" & voucher_page
                Worksheets("sample_usd").ExportAsFixedFormat Type:=xlTypePDF, Filename:=voucher_fname, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            Next
        End If
    Next
    '全部凭证打印完毕后，输出一个Done文件做标记
    Worksheets("sample_rmb").ExportAsFixedFormat Type:=xlTypePDF, Filename:="Done", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub
  • 执行VBA程序后打印出的两个测试文件：
    测试文件结果
  • 打印工作完成后，使用PDF编辑器自由合并凭证，即可打印为纸质单据进行归档了。

四、总结

开篇已经说过，单纯使用程序来解决这个问题会更加简洁。但这篇记录想表达的是，一个看似复杂的问题，通过分解后，业余人员也能利用基本功来完成同样的目标。这些基本功是每一位自我评价“熟练使用Excel”的同学都应该掌握的，重点是勇于尝试和分析。

整个问题的解决可以分为排序和打印两大部分，其中每一个细节问题的处理，特别是排序时需要掌握的功能，确实都是Excel的基本功能。

在程序语言的选择上，Python确实好用也比较热门，但针对具体问题，还是应该选择合适的工具。此前在数万行Excel数据无法简单的规划求解，需要做无限逼近以及随机抓取时，Python发挥了优势。不过，处理相对简单、数据量不大的问题时，建议使用Office自带的VBA，学习成本低。