Excel现金日记账制作教程和实例

*设置现金日记账的格式和公式
图1 现金日记账
*设置现金日记账的格式和公式
1.设置现金日记账要素
(1)标题：现金日记账。 (2)日期：为了查看和筛选日期方便，月、日分为两列设置。 (3)凭证编号：是现金收支的记账凭证编号。在和财务软件中明细账核 对时，凭证编号是主要的核对依据。 (4)类别：是相对于标准日记账格式新添加的部分，主要作用是为现金 日报表汇总做准备。本列由于内容固定，所以采取下拉列表方式录入。 (5)摘要：是账簿不可缺少的部分，记录现金收支的详细情况。 (6)数据项：借方是现金收入部分，贷方是现金支出部分，余额是本笔 业务发生后的现金余额。 (7)“√”：是否已记账，如果记账为“√”，否则为空白。
*自动生成现金日报表
2.本月累计收入和支出项目公式
本月累计求和方法和本日合计相同，都是使用SUMPRODUCT函数进行多 条件求和。
“现金日记账!$B$7:$B$1836=MONTH(现金日报表!$D$24))*(现金日记 账!$C$7:$C$1836<=DAY(现金日报表!$D$24)”：由于是本月累计，所以在 对现金日记账求和时应满足两个条件：一是月份相同；二是日期小于等于当 天的日期。
表!$D$4))*(现金日记账!$C$7:$C$1836=DAY(现金日报表!$D$4))*(现金日 记账!$E$7:$E$1836=现金日报表!C8)*现金日记账!$J$7:$J$1836)
*自动生成现金日报表
1.本日收入和支出项目公式
由于是多条件求和，所以公式中使用了SUMPRODUCT函数。 “现金日记账!$B$7:$B$1836=MONTH(现金日报表!$D$4)”：是利用 SUMPRODUCT进行求和的第一个条件，如 所示，日期分为两列，而 所 示的现金日报表中的日期为标准的日期格式，所以要用MONTH(现金日报 表!$D$4)取出月份数，再和 中的B列月份数相比较。 “现金日记账!$E$7:$E$1836=现金日报表!C8”：因为不需要根据收入项 目求和，所以让“现金日记账!$E$7:$E$1836”区域的收入项目和“现金日报 表!C8”区域的“类别”进行比较，构成了SUMPRODUCT的第三个求和条件。 “现金日记账!$J$7:$J$1836”：是实际计算的数值区域，因为收入要统 计借方发生额，所以公式中使用“现金日记账!$J$7:$J$1836”作为计算区域。
（3）本年累计收入和支出项目公式；
（4）本日现金余额公式；
（5）昨日现金余额公式。
*自动生成现金日报表
图13 现金日报表
*自动生成现金日报表
1.本日收入和支出项目公式
在日报表中，收入和支出项目是和现金日记账的“类别”列保持一致的， 所以统计本日的收入和支出要根据日期和具体项目进行有条件求和。
（1）本日收入公式 D8=SUMPRODUCT((现金日记账!$B$7:$B$1836=MONTH(现金日报
*设置现金日记账的格式和公式
如 所示，图中内容为已完成的现金日记账格式（日记账内的数据是 为了演示方便而事先录入的数据）。设置现金日记账的格式，需要完成下列 工作。
(1)设置现金日记账要素。 (2)设置表格连线和字体格式。 (3)设置单元格内容缩进。 (4)设置下拉列表选取输入。 (5)设置“√”号录入。 (6)保护已记账记录不被修改。 (7)设置余额公式。
2.自动设置日记账的边线和字体格式
(2)设置随记录增加而自动添加的边线。打开【条件格式】对话框，在
【条件 2】中选取“公式”，在后边的文本框中输入公式“=$G1<>""”,如
所示.单击
按钮，打开【单元格格式设置】对话框，设置边线格
式为单元格四周边线。
字为红色
图3 设置边线
*设置现金日记账的格式和公式
（2）选取E列，执行【数据】/【有效性】命令，打开【数据有效性】 对话框，在【设置】选项卡的“允许”项中选取“序列”，然后在“来源” 下的文本框中输入公式“=类别”，如 所示。
*设置现金日记账的格式和公式
4.设置下拉列表选取输入
图9 设置下拉列表
*设置现金日记账的格式和公式
4.设置下拉列表选取输入
5.设置“√”号录入
在某笔业务入账时需要在后面的M列输入记账符号“√”。由于“√” 需要通过插入特殊符号或复制粘贴方式录入。录入速度比较慢，但通过单元 格自定义格式设置，可以用录入数字1代替“√”，且又不影响显示“√”。
选取M列，执行【格式】/【单元格】命令，打开【单元格格式】对话 框，选取【数值】选项卡，在“分类”列表中选取“自定义”项，在“类型” 中输入代码“[=1]"√";G/通用格式”，如 所示。
(3)边线设置后的结果见 。
*设置现金日记账的格式和公式
3.设置单元格内容缩进
在“类别”列和“摘要”列中，默认情况下，单元格内容会靠近边线， 如 所示，如果设置单元格内容缩进，日记账整体布局会更美观。
图4 默认单元格对齐格式
*设置现金日记账的格式和公式
3.设置单元格内容缩进
选取E列和G列，执行【格式】/【单元格】命令，打开【单元格格式】 对话框，选取【对齐】选项卡，调节缩进数值为1，如 所示，设置结果 如 所示。
图5 单元格缩进
图6 设置单元格缩进效果
*设置现金日记账的格式和公式
4.设置下拉列表选取输入
在E列“类别”项，由于可输入内容相对固定，所以需要设置下拉列表， 以便录入时选取相应内容。
（1）定义数据源。首先在建立的“下拉列表项目”工作表的A列输入待 选项目，然后再选取已设置好的下拉列表区域，如 所示A1：A10区域。 执行【插入】/【名称】/【定义】命令，打开【定义名称】对话框，将选取 区域定义名称为“类别”，如 所示。
2.自动设置日记账的边线和字体格式
因为本步中的边线设置和第（1）步中的字体颜色设置为同一区域，所 以第（2）步设置的条件格式应是在第（1）步基础上添加的新条件。
=$G1<>""：在现金日记账中，因为摘要列常为非空内容，所以条件格式 设置以摘要列为参照。摘要列如果有内容，则日记账内整行添加边线，否则 为空白区域。
*自动生成现金日报表
1.本日收入和支出项目公式
图14 现金日记账
*自动生成现金日报表
1.本日收入和支出项目公式
（2）本日支出公式 D14==SUMPRODUCT((现金日记账!$B$7:$B$1836=MONTH(现金日报 表!$D$4))*(现金日记账!$C$7:$C$1836=DAY(现金日报表!$D$4))*(现金日 记账!$E$7:$E$1836=现金日报表!C14)*现金日记账!$K$7:$K$1836)
*设置现金日记账的格式和公式
2.自动设置日记账的边线和字体格式
如 所示，在现金日记账中，字体颜色和边线格式并不是手工设置， 而是让Excel识别录入的内容及录入内容行数后，自动设置字体、边线的样 式和格式。
(1)设置“本日合计”、“本月累计”、“本年累计”字体为红色，选 取B至M列，执行【格式】/【条件格式】命令，打开【条件格式】对话框， 在【条件 1】下拉列表中选取“公式”，在后边的文本框中输入公式 “=OR(B1="本日合计",B1="本月累计",B1="本年累计")”，如 所示，单击。
“=OR(B1="本日合计",B1="本月累计",B1="本年累计")”：是设置的判断 条件。其实只需设置G列条件格式即可，但为了方便设置，以及更改日记账 条件格式，所以这里设置从B列B1开始；而且除了“摘要”列以外，其他列不 会出现“本日合计”等内容，所以字体格式不会受到实质性的影响。
*设置现金日记账的格式和公式
除了计算区域更改为现金日记账的贷方区域外，其他计算依据和收入公 式相同。
*自动生成现金日报表
2.本月累计收入和支出项目公式
（1）本月累计收入公式 E8=SUMPRODUCT((现金日记账!$B$7:$B$1836=MONTH(现金日报
表!$D$4))*(现金日记账!$C$7:$C$1836<=DAY(现金日报表!$D$4))*(现金日 记账!$E$7:$E$1836=现金日报表!C8)*现金日记账!$J$7:$J$1836)
*设置现金日记账的格式和公式
7.设置余额公式
根据余额的计算原理和一些特殊要求，设置如下公式： L7=IF(AND(J7="",K7=""),0,IF(OR(G7={"本日合计","本月累计","本年累计 "}),L6,L6+J7-K7))
正常余额公式=前一条记录的余额+本行借方-本行贷方（L6+J7-K7） AND(J7="",K7="")：是防止后面为空行时，余额也一直计算下去。 IF(OR(G7={"本日合计","本月累计","本年累计"}),L6：因为存在本日 合计、本月累计或本年累计行，所以设置这些行的数据不参与运算，直接等 于上一行余额数，如 所示。
*设置现金日记账的格式和公式
5.设置“√”号录入
图10 设置M列格式
*设置现金日记账的格式和公式
6.保护已记账记录不被修改
记账是指已在财务软件的现金明细账中登记。下面的方法可以防止已有 记账符号（“√”）的数据被修改。
选取G列到L列，打开【数据有效性】对话框，选取“允许”下拉框中的 “自定义”，然后在公式文本框中输入公式“=$M1=""”，如 所示。
*设置现金日记账的格式和公式
7.设置余额公式
图12 设置余额公式
*自动生成现金日报表
现金日报表是出纳每天必做的报表之一，如
所示，现金日报表真
实反映了当天的现金流入和流出情况，现金日报表的自动生成是根据现金日
ห้องสมุดไป่ตู้
记账的记录，并通过公式完成的，现金日报表需要设置下列公式。
（1）本日收入和支出项目公式；
（2）本月累计收入和支出项目公式；
*自动生成现金日报表
按钮，打开【单元格格式设置】对话框，设置字体格式为红色加粗， 值得注意的是，一定要设置边框为四周线条。
*设置现金日记账的格式和公式
2.自动设置日记账的边线和字体格式
字为红色
图2 设置字体格式
*设置现金日记账的格式和公式
2.自动设置日记账的边线和字体格式
B至M整列选取：原因是随着日记账记录的不停增加，边线和字体格式的 设置区域也要随之增加，这样设置的好处，是条件格式可以应用到所有新增 加的记录。
*设置现金日记账的格式和公式
6.保护已记账记录不被修改
图11 设置数据有效性
*设置现金日记账的格式和公式
6.保护已记账记录不被修改
公式“=$M1=""”：在本行M列中单元格如果为空，则可以修改，反之不 能修改。注意公式中的“$M1”的引用方法，列要绝对引用。
如果要修改数据或记录，先删除M列显示的“√”（实际数据是1），再 修改相应数据。
“=类别”是引用第一步定义的名称。“类别”代表“下拉列表项 目!$A$1:$A$10”区域。
由于在选取有效性范围时包含E列的E1:E5区域，而这个区域不需要设 置下拉列表，所以需要删除该区域的数据在效性设置。方法是选取该区域， 更改【数据有效性】对话框中“允许”的“序列”选项为“任何值”。
*设置现金日记账的格式和公式
*设置现金日记账的格式和公式
4.设置下拉列表选取输入
图7 建立和选取数据源
*设置现金日记账的格式和公式
4.设置下拉列表选取输入
图8 定义数据源
*设置现金日记账的格式和公式
4.设置下拉列表选取输入
因为数据源单独存放在“下拉列表项目”工作表中，和现金日记账不在 同一个工作表，而数据有效性又不能直接引用其他表的数据区域，所以需要 定义名称，然后才能使用数据有效性建立下拉列表。
现金管理在财务管理中占有重要地位。随着电算化的普及， 大多数公司和企业都安装了财务软件，在财务软件里可以很方 便的查看现金明细账。但是，由于现金发生的即时性以及其他 方面的特殊原因，现金的收入和支出可能会出现不及时入账的 情况，所以即使应用财务软件，作为出纳也要登记“现金日记 备查账”。本节主要是介绍如何用Excel建立现金日记账，并能 自动生成现金日报表。
（2）本月累计支出公式 E14=SUMPRODUCT((现金日记账!$B$7:$B$1836=MONTH(现金日报 表!$D$24))*(现金日记账!$C$7:$C$1836<=DAY(现金日报表!$D$24))*(现金 日记账!$E$7:$E$1836=现金日报表!C34)*现金日记账!$K$7:$K$1836)