Excel 添加现金日记账程序

在我们添加系统程序前,首先要添加日记账程序。具体操作步骤如下所示。

步骤01:切换至“现金日记账”工作表,在“开发工具”里,选择“Visual Basic编辑器”命令,如图10-25所示。

图10-25 选择“Visual Basic”编辑器

步骤02:在弹出的对话框中,输入如图10-26所示的代码。代码如下:


Private Sub WorkSheet_change(ByVal Target As Range)
Dim iRow, iCol, iRow_dn As Integer
Dim rng1, rngl2, rng, cel As Range
iRow = Target.Row
iCol = Target.Column
iRow_dn = [A65536].End(x1Up).Row  'A列的最后一行
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    If iRow > = 3 And iCol = 2 And Cells(iRow, iCol) <> ""  Then
        Cells (iRow, 1) = Date '在第一列填写日期
    ElseIf iRow > = 3 And iCol = 2 And Cells(iRow, iCol) = "" Then
        Range(Cells(iRow, 1), Cells(iRow, 5)).ClearContents
    ElseIf iRow >= 3 And (iCol = 3 Or iCol = 4) And iRow = iRow_dn Then
        Total1 = Application.WorkSheetFunction.Sum(Range("C4:C" & iRow))
        Total1 = Application.WorkSheetFunction.Sum(Range("D4:D" & iRow))
        Cells(iRow, 5) = Total1 - Total2 '在第5列运算
    ElseIf iRow >= 3 And (iCol = 3 Or iCol = 4) And iRow <> iRow_dn Then
        Set rng = Range("E" & iRow & " :E" & iRow_dn)
        For Each cel In rng
            Set rng1 = Range("C4:C" & cel.Row)
            Set rng2 = Range("D4:D" & cel.Row)
            Total1 = Application.WorkSheetFunction.Sum(rng1)
            Total2 = Application.WorkSheetFunction.Sum(rng2)
            cel.Value = Total1 - Total2
    Next cel
End If
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

图10-26 插入现金日记账代码

输入以上代码后,我们可以控制数据的输入和余额的累计计算。如果需要打开“现金日记账”工作表中自动定位到需要输入数据的第一个单元格,还需要输入相关的定位代码。具体代码段如下所示:


Private Sub WorkSheet_Active()
ActiveSheet.[B65536].end(xlUp).Offset(1, 0) .Select '打开现金后自动
        '定位到需要输入数据的第一个单元格
End Sub

Excel 创建现金日记账表格

现金日记账是用来逐日反映库存现金的收入、付出及结余情况的特种日记账,是由单位出纳人员根据审核无误的现金收、付款凭证和从银行提现的银付凭证逐笔进行登记的。为了确保账簿的安全、完整,现金日记账必须采用订本式账簿。

现金日记账通常由出纳人员根据审核后的现金收款凭证和现金付款凭证,逐日逐笔顺序登记。每日业务终了时,应计算、登记当日现金收入合计数、现金支出合计数,以及账面结余额,并将现金日记账的账面余额与库存现金实有数核对,借以检查每日现金收入、付出和结存情况。

具体创建操作步骤如下。

步骤01:启动Excel 2016,单击“文件”菜单,选择新建“空白工作簿”,将工作簿命名为“现金和银行存款管理系统”,然后将Sheet1表命名为“现金日记账”。

步骤02:输入相应的标题题目,输入“日期”“摘要”“收入”“支出”“余额”等标志项,调整文本内容的对齐方式,调整合适的文本格式。

步骤03:选择标志项内容,设置合适的底色。最终效果如图10-1所示。

图10-1 创建“现金日记账”表格

登记现金日记账函数:SUMPRODUCT、INDEX函数

我们先来学习一下登记现金日记账相关函数的语法和功能,这里我们重点讲解SUMPRODUCT函数和INDEX函数。

SUMPRODUCT函数的语法和功能

我们主要从函数的含义、函数的语法格式、函数在日常办公中运用的实例介绍以及函数使用的注意点这4个方面对SUMPRODUCT函数进行讲解。

含义

SUMPRODUCT函数的适用范围在给定的几组数组中,先把数组间对应的元素相乘,然后返回乘积之和,如图3-1所示。

图3-1 SUMPRODUCT函数含义

从字面上可以看出,SUMPRODUCT由两个英文单词组成,sum是和,product是积,所以是乘积之和的意思。

SUMPRODUCT函数的语法格式

SUMPRODUCT(array1,array2,array3,…),array为数组,如图3-2所示。

SUMPRODUCT函数实例——基础用法

当SUMPRODUCT函数的参数中只有一个数组时,即对数组{1;2;3;4;5;6;7}进行求和,1+2+3+4+5+6+7=28,如图3-3所示。

当sumproduct函数中的参数为两个数组时,两个数组的所有元素对应相乘,如图3-4所示。

公式“=sumproduct(A2:A8,B2:B8)”可转化为“=sumproduct(数组1,数组2)”,即“=sumproduct({1;2;3;4;5;6;7},{1;2;3;4;5;6;7})”=1*1+2*2+3*3+4*4+5*5+6*6+7*7=140。

图3-2 SUMPRODUCT函数的语法格式

图3-3 对一个数组求和

图3-4 对两个数组计算

当sumproduct函数中的参数为3个数组时,3个数组的所有元素对应相乘,在E4单元格中输入的公式为“=SUMPRODUCT(A2:A8,B2:B8,C2:C8)”,如图3-5所示。

图3-5 3个数组元素对应相乘

SUMPRODUCT函数实例——多条件求和

我们先来看第一个实例:单条件求和——统计成都发货平台的发货量,如图3-6所示。

图3-6 单条件求和

在E2单元格中输入公式“=SUMPRODUCT((A2:A13=”成都”)*(B2:B13))”。

看到这个公式你可能有疑惑,它跟语法格式好像不一样,其实把它看作只有一个参数,因为当函数中出现由TRUE和FALSE组成的逻辑数组时,公式要写成“=SUMPRODUCT((A2:A13=”成都”)*1,(B2:B13))”格式,乘以1,把它转化成数组才能参与运算,否则就写成最上面的那种形式。

公式分解如下:

“=SUMPRODUCT({数组1}*{数组2})”

“=SUMPRODUCT({TRUE;…..TRUE;…..TRUE}*{11012;…41568;…12506})”

=1*11012+1*41568+1*12506=65086

第二个实例:多条件求和——求发货平台为成都、收货平台为重庆的发货量,如图3-7所示。在E2单元格中输入公式“=SUMPRODUCT((A2:A13=”成都”)*(C2:C13=”重庆”)*(D2:D13))”即可求出结果。

图3-7 多条件求和

SUMPRODUCT函数使用的注意点

  1. SUMPRODUCT函数后面的参数必须是数组,即行和列的维度是一致的。参数维数不一致会返回错误值#VALUE!
  2. SUMPRODUCT函数中以逗号分隔的各个参数必须为数字型数据。
  3. 如果是判断的结果逻辑值,就要乘以1转换为数字。
  4. 如果不用逗号而直接用*号连接,就相当于乘法运算,就不必乘以1。

INDEX函数的语法和功能

和讲解SUMPRODUCT函数一样,我们主要从函数的含义、函数的语法格式、函数在日常办公中运用的实例介绍以及函数使用的注意点这4个方面对INDEX函数进行讲解。

INDEX函数的含义

返回数据表区域的值或对值的引用,如图3-8所示。

图3-8 INDEX函数的含义

Index函数的两种形式:数组和引用。

1)数组形式——返回数组中指定单元格或单元格数组的数值。

2)引用形式——返回引用中指定单元格或单元格区域的引用。

INDEX函数的语法格式

数组形式=INDEX(array,row_num,column_num)=INDEX(数据表区域,行数,列数)

引用形式=index(reference,row_num,column_num,area_num)

=INDEX(一个或多个单元格区域的引用,行数,列数,从第几个选择区域内引用),如图3-9所示。

图3-9 INDEX函数的语法格式

INDEX函数数组形式实例

第一个实例:如图3-10所示,在B8单元格中输入公式“=INDEX(B3:D6,4,3)”,其中,数据表区域(B3:D6),数(4),列数(3),返回数据表区域(B3:D6)第4行第3列的值120。

图3-10 在B8单元格中输入公式

第二个实例:通过INDEX函数和MATCH函数实现单条件匹配查找。

如图3-11所示,利用INDEX进行匹配查找,当数据很多时,我们不可能通过点数来确定INDEX函数中的行数和列数,而是要通过MATCH函数来确定行数和列数。

在B9单元格中输入以下公式:

=INDEX($F$2:$I$6,MATCH(A9,$F$2:$F$6,0),MATCH($B$8,$F$2:$I$2,0))

这里使用绝对引用要注意,B8代表6月份不变要使用绝对引用。

图3-11 单条件匹配查找

INDEX函数引用形式实例

第一个实例:如图3-12所示,在B8单元格中输入公式“=INDEX((B3:D6,G3:I6),4,3)”,其中,一个或多个单元格区域的引用(两个区域B3:D6,G3:I6),行数(4),列数(3),从第几个选择区域内引用(省略,默认第一个区域B3:D6),所以返回120。

图3-12 从第一个区域内引用

第二个实例:如图3-13所示,在B8单元格中输入公式“=INDEX((B3:D6,G3:I6),4,3,2)”,其中,一个或多个单元格区域的引用(两个区域B3:D6,G3:I6),行数(4),列数(3),从第几个选择区域内引用(第二个区域G3:I6),所以返回500。

INDEX函数使用的注意点

Row_num和Column_num必须指向数组中的某个单元格,否则,INDEX函数出错,返回#REF!错误值。

图3-13 从第二个区域内引用

Excel 将“现金日记账表”并另存为“银行存款日记账”

在“现金日记账表格”的基础上,修改表格格式后,另存为“银行存款日记账”。具体操作方法如下所示。

步骤01:右击列标E,从弹出的快捷菜单中单击“删除”命令,删除“对应科目”列,如图3-68所示。

图3-68 删除“对应科目”列

步骤02:右击列标D,从弹出的快捷菜单中单击“插入”命令,插入新列,如图3-69所示。重复此操作,在“摘要”栏左侧插入两列。

图3-69 插入列

步骤03:合并单元格D3:E3,然后分别在D3、D4、E4中输入表格栏目“结算方式”“类”“号码”,如图3-70所示。

图3-70 输入表格栏目

步骤04:选中单元格A1,将单元格中的“现金”更改为“银行存款”,如图3-71所示。

图3-71 修改表格标题

步骤05:在Excel窗口中单击“文件”菜单中的“另存为”命令,打开“另存为”对话框。选择好保存位置后,在“文件名”框中输入“银行存款日记账.xlsx”,然后单击“保存”按钮,如图3-72所示。

步骤06:此时工作簿的名称会更改为“银行存款日记账”,如图3-73所示。

图3-72 “另存为”对话框

图3-73 另存为新工作簿

Excel 打开“现金日记账”工作簿

启动Excel 2016后,接下来使用“文件”菜单来打开上一节中创建的最终文件“现金日记账表格”。

步骤01:在Excel窗口中单击“文件”菜单,然后单击“打开”命令,选择要打开的文件位置,这里我们选择“这台电脑”中的“桌面”选项,如图3-53所示。

图3-53 选择“桌面”

步骤02:在“打开”对话框中选择“现金日记账表格”工作簿,单击“打开”按钮,如图3-54所示。

步骤03:打开后的工作簿如图3-55所示。

图3-54 “打开”对话框

图3-55 打开的工作簿