根据任务二建立的记账凭证汇总表设置打印指定记账凭证的模板，设置完成后可以通过输入“凭证序号”自动返回其他的相关数据。自动生成内容包括“日期”、“摘要”、“总账科目”、“明细科目”、“借方金额”、“贷方金额”、“制单人”、“审核人”。

一、创建记账凭证通用格式

打开【某公司账簿．xls】工作簿，新建工作表并重命名为【打印记账凭证】，如图2－40所示。

特别提醒：E2单元格的格式设置成“文本”，保证与【记账凭证汇总表】中的【序号】列的格式一致。

图2－40 记账凭证通用格式

二、根据“凭证序号E2单元格”自动生成日期

主要利用查找引用函数中的INDEX函数和MATTCH函数嵌套完成。

1．分别返回“年”“月”“日”的公式

“年”——＝INDEX（记账凭证汇总表！A：A，MATCH（E2，记账凭证汇总表！D：D，0））…①

或＝INDEX（记账凭证汇总表！A：A，MATCH（E2，记账凭证汇总表！D：D，0），1）…②

或＝INDEX（记账凭证汇总表！A：C，MATCH（E2，记账凭证汇总表！D：D，0），1）…③

“月”——＝INDEX（记账凭证汇总表！B：B，MATCH（E2，记账凭证汇总表！D：D，0））

或＝INDEX（记账凭证汇总表！B：B，MATCH（E2，记账凭证汇总表！D：D，0），1）

或＝INDEX（记账凭证汇总表！A：C，MATCH（E2，记账凭证汇总表！D：D，0），2）

“日”——＝INDEX（记账凭证汇总表！C：C，MATCH（E2，记账凭证汇总表！D：D，0））

或＝INDEX（记账凭证汇总表！C：C，MATCH（E2，记账凭证汇总表！D：D，0），1）

或＝INDEX（记账凭证汇总表！A：C，MATCH（E2，记账凭证汇总表！D：D，0），3）

说明：其中公式①和②区别在于INDEX函数的第三个参数，由于INDEX函数的第一个参数如果与返回值所在的列一致，那么第三个参数为“1”或者省略；公式②和③的区别在于INDEX函数第一个参数所选区域可大可小，选择公式③对返回“月”和“日”带来复制粘贴的方便。即当返回“年”的公式设置完成后，只用更改INDEX函数中的第三个参数列序号即可完成“月”和“日”的返回。

2．完整日期的返回公式

选择B2单元格，输入公式：“＝INDEX（记账凭证汇总表！A：C，MATCH（E2，记账凭证汇总表！D：D，0），1）＆“年”＆INDEX（记账凭证汇总表！A：C，MATCH （E2，记账凭证汇总表！D：D，0），2）＆“月”＆INDEX（记账凭证汇总表！A：C， MATCH（E2，记账凭证汇总表！D：D，0），3）＆“日””，选择B2和C2单元格跨列居中，如图2－41所示。

图2－41 未输入凭证序号时的日期显示

由于图2－41中未输入凭证序号时的B2单元格显示“＃N／A”，可以使用IF函数使“记××号”中无信息时，打印记账凭证也无“＃N／A”显示，此时应将B2单元格公式更改为：“＝IF（E2＝" "，" "，INDEX（记账凭证汇总表！A：C，MATCH（E2，记账凭证汇总表！D：D，0），1）＆“年”＆INDEX（记账凭证汇总表！A：C，MATCH（E2，记账凭证汇总表！D：D，0），2）＆"月"＆INDEX（记账凭证汇总表！A：C，MATCH（E2，记账凭证汇总表！D：D，0），3）＆"日"）”，更改后如图2－42所示：

图2－42 插入IF函数后未输入凭证序号时的日期显示

在E2单元格中输入“01”检验日期自动显示效果如图2－43所示。

图2－43 输入凭证序号“01”时的日期显示

3．给INDEX函数中嵌套的第二个参数返回行序号函数MATCH定义名称，方便函数参数的直接引用

单击菜单栏中的【插入】︱【名称】︱【定义】命令，弹出“定义名称”对话框，当前工作簿名称输入“凭证存在行号”，在引用位置，输入“＝MATCH（打印记账凭证！＄E＄2，记账凭证汇总表！＄D：＄D，0）”，如图2－44所示，单击【添加】︱【关闭】按钮完成“凭证存在行号”名称的定义。

图2－44 “凭证存在行号”名称定义

定义“凭证存在行号”名称后，再将B2单元格公式替换为：“＝IF（E2＝" "，" "， INDEX（记账凭证汇总表！A：C，凭证存在行号，1）＆"年" ＆INDEX（记账凭证汇总表！A：C，凭证存在行号，2）＆"月" ＆INDEX（记账凭证汇总表！A：C，凭证存在行号，3）＆"日"）”，通过名称定义使我们输入较长的公式简单化。

三、摘要自动显示设置

由于每一张凭证我们无法确定数据有多少行，例如“01”号凭证有三行数据，而“19”号凭证有七行数据，这样一来，我们在设置“摘要”自动显示时不能简单地模仿设置“日期”自动显示的公式，首先要判断多少行数据，因此可以利用E2单元格的“序号”来确定数据存在的行数。同时为了其他单元格公式的快捷输入，利用嵌套OFFSET函数达到其他单元格的公式向下填充和向右复制粘贴公式即可完成。通过分析后得到A4单元格的公式为：

“＝IF（COUNTIF（记账凭证汇总表！＄D：＄D，＄E＄2）－ROW（A1）＞＝0，OFFSET （INDEX（记账凭证汇总表！＄F：＄F，凭证存在行号），ROW（A1）－1，0），" "）”…①

或“＝IF（COUNTIF（记账凭证汇总表！＄D：＄D，＄E＄2）－ROW（A1）＞＝0， OFFSET（INDEX（记账凭证汇总表！＄F：＄K，凭证存在行号，1），ROW（A1）－1，0），""）”…②

输入完成后通过填充柄的功能快速复制公式至A11单元格结束，完成后输入“01”号和“19”号凭证检验摘要的自动显示效果，如图2－45和图2－46所示。

四、“总账科目”、“明细科目”、“借方金额”和“贷方金额”自动显示设置

方法一：与设置“摘要”公式①相同的方法来完成，公式分别如下：

图2－45 “01”号凭证摘要自动显示

图2－46 “19”号凭证摘要自动显示

B4——＝IF（COUNTIF（记账凭证汇总表！＄D：＄D，＄E＄2）－ROW（A1）＞＝0， OFFSET（INDEX（记账凭证汇总表！＄H：＄H，凭证存在行号），ROW（A1）－1，0），""）

C4——＝IF（COUNTIF（记账凭证汇总表！＄D：＄D，＄E＄2）－ROW（A1）＞＝0， OFFSET（INDEX（记账凭证汇总表！＄I：＄I，凭证存在行号），ROW（A1）－1，0），""）

D4——＝IF（COUNTIF（记账凭证汇总表！＄D：＄D，＄E＄2）－ROW（A1）＞＝0， OFFSET（INDEX（记账凭证汇总表！＄J：＄J，凭证存在行号），ROW（A1）－1，0），""）

E4——＝IF（COUNTIF（记账凭证汇总表！＄D：＄D，＄E＄2）－ROW（A1）＞＝0， OFFSET（INDEX（记账凭证汇总表！＄K：＄K，凭证存在行号），ROW（A1）－1，0），""）

方法二：由于设置“摘要”自动显示时嵌套了OFFSET函数，因此可以采用复制粘贴设置“摘要”自动显示的公式②更改函数OFFSET的第三个参数即可，公式分别如下：

B4——＝IF（COUNTIF（记账凭证汇总表！＄D：＄D，＄E＄2）－ROW（A1）＞＝0， OFFSET（INDEX（记账凭证汇总表！＄F：＄K，凭证存在行号，3），ROW（A1）－1，0），""）

C4——＝IF（COUNTIF（记账凭证汇总表！＄D：＄D，＄E＄2）－ROW（A1）＞＝0， OFFSET（INDEX（记账凭证汇总表！＄F：＄K，凭证存在行号，4），ROW（A1）－1，0），""）

D4——＝IF（COUNTIF（记账凭证汇总表！＄D：＄D，＄E＄2）－ROW（A1）＞＝0， OFFSET（INDEX（记账凭证汇总表！＄F：＄K，凭证存在行号，5），ROW（A1）－1，0），""）

E4——＝IF（COUNTIF（记账凭证汇总表！＄D：＄D，＄E＄2）－ROW（A1）＞＝0， OFFSET（INDEX（记账凭证汇总表！＄F：＄K，凭证存在行号，6），ROW（A1）－1，0），""）

并通过填充柄功能快速将B4、C4、D4、E4单元格的公式拖动复制到其他单元格。完成后输入“01”号和“19”号凭证检验效果，如图2－47所示。

图2－47 “01”号凭证

五、“借方金额”和“贷方金额”合计

通过在合计行显示“借方金额”和“贷方金额”的合计值，可以再次检查记账凭证汇总表中会计分录是否正确。

即在D12单元格中输入公式“＝SUM（D4：D11）”，E12单元格中输入公式“＝SUM （E4：E11）”，选中D4：E12单元格区域将格式调整为会计专用格式，并显示千分位分隔符以及保留小数点后面两位数字。完成后输入“01”号凭证检验效果。如图2－48所示。

六、本任务相关函数介绍

1．ROW函数

函数功能：返回给定引用的行号。

图2－48 “01”号凭证

函数语法：ROW（reference）。

参数说明：Reference为需要得到其行号的单元格或单元格区域。

应用实例：公式“＝ROW（A6）”返回6，如果在C5单元格中输入公式“＝ROW（）”，其计算结果为5。

2．INDEX

函数功能：返回表格或区域中的数值或对数值的引用。函数INDEX（）有两种形式：数组和引用。数组形式通常返回数值或数值数组；引用形式通常返回引用。

函数语法：INDEX（array，row＿num，column＿num）——返回数组中指定的单元格或单元格数组的数值。

INDEX（单元格区域，行号，列号）

INDEX（reference，row＿num，column＿num，area＿num）——返回引用中指定单元格或单元格区域的引用。

参数说明：Array为单元格区域或数组常数；Row＿num为数组中某行的行序号，函数从该行返回数值。如果省略row＿num，则必须有column＿num；Column＿num是数组中某列的列序号，函数从该列返回数值。如果省略column＿num，则必须有row＿num。Reference是对一个或多个单元格区域的引用，如果为引用输入一个不连续的选定区域，必须用括号括起来。Area＿num是选择引用中的一个区域，并返回该区域中row＿num和column＿num的交叉区域。选中或输入的第一个区域序号为1，第二个为2，以此类推。如果省略area＿num，则INDEX函数使用区域1。

INDEX函数的功能就是返回指定单元格区域或数组常量。如果同时使用参数行号和列号，函数INDEX返回行号和列号交叉处的单元格中的值。

应用实例：如图2－49所示。

＝INDEX（A1：C6，2，3），意思就是返回A1：C6中行号是2列号是3，即第二行与第三列的交叉处，也就是C2单元格的值，为84。(www.daowen.com)

图2－49 INDEX应用实例

3．MATCH

函数功能：返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH函数。

函数语法：MATCH（lookup＿value，lookup＿array，match＿type）

MATCH（查找值，查找区域，查找模式）

参数说明：Lookup＿value为需要在数据表中查找的数值，它可以是数值（或数字、文本或逻辑值）、对数字、文本或逻辑值的单元格引用。Lookup＿array是可能包含所要查找的数值的连续单元格区域，Lookup＿array可以是数组或数组引用；Match＿type为数字－1、0或1，它说明Excel如何在lookup＿array中查找lookup＿value。如果match＿type为1，函数MATCH查找小于或等于lookup＿value的最大数值。如果match＿type为0，函数MATCH查找等于lookup＿value的第一个数值。如果match＿type为－1，函数MATCH查找大于或等于lookup＿value的最小数值。

注意：MATCH函数返回lookup＿array中目标值的位置，而不是数值本身。如果match＿type为0且lookup＿value为文本，lookup＿value可以包含通配符（“＊”和“？”）。星号可以匹配任何字符序列，问号可以匹配单个字符。

说明：MATCH函数也是一个查找函数。MATCH函数会返回匹配值的位置而不是匹配值本身。在使用时，MATCH函数在众多的数字中只查找第一次出现的，后来出现的它返回的也是第一次出现的位置。

应用实例：如图2－50所示。

图2－50 MATCH应用实例

＝MATCH（41，B2：B5，0），得到结果为4，返回数据区域B2：B5中41的位置。

＝MATCH（39，B2：B5，1），得到结果为2，由于此处无正确匹配，所以返回数据区域B2：B5中（38）的位置。注：匹配的查找值，MATCH函数会查找小于或等于（39）的最大值。

＝MATCH（40，B2：B5，－1），得到结果为＃N／A，由于数据区域B2：B5不是按降序排列，所以返回错误值。

如果match＿type为1，函数MATCH查找小于或等于lookup＿value的最大数值。Lookup＿array必须按升序排列：…，－2，－1，0，1，2，…，A－Z，FALSE，TRUE。

如果match＿type为0，函数MATCH查找等于lookup＿value的第一个数值。Lookup＿array可以按任何顺序排列。

如果match＿type为－1，函数MATCH查找大于或等于lookup＿value的最小数值。Lookup＿array必须按降序排列：TRUE、FALSE、Z－A，…，2，1，0，－1，－2，…，等等。

INDEX函数和MATCH函数应用案例介绍

由于MATCH函数返回值“行号”“列号”刚好是INDEX函数中的第2、3个参数，因此MATCH函数往往作为INDEX函数的一个参数嵌套使用。

图2－51所示的是一个产品的型号和规格的价格明细表。通过这个表的数据，在查询设置工作表中进行一些对应的查询操作。

图2－51 INDEX和MATCH应用案例

①单击B5单元格下拉按钮，选择型号，然后在B6单元格完成型号所在行号的查询。如图2－52所示。

2－52 INDEX和MATCH应用案例

随意选择一个型号，比如A0110，然后在B6单元格输入公式：＝MATCH（＄B＄5，＄D＄4：＄D＄12，0），得到结果“1”。

公式解释：用MATCH函数查找B5单元格这个型号在D4：D12区域中对应的位置。其中的0参数可以省略不写。MATCH函数中0代表精确查找，1是模糊查找。

②单击B9单元格下拉按钮，选择规格，然后在B10单元格完成规格所在列号的查询。如图2－53所示。

图2－53 INDEX和MATCH应用案例

随意选择一个规格，比如101，然后在B10单元格输入公式：＝MATCH（B9，E3：G3， 0），得到结果“1”。

③查询上述工作表中B6和B10单元格所对应的价格。

价格的查询，可以使用index函数完成，在B11单元格输入公式：＝INDEX（E4：G12，B6，B10）可以得到结果为“78”。嵌套上面的match函数，可以将公式改为：＝INDEX（E4：G12，MATCH（B5，D4：D12，0），MATCH（B9，E3：G3，0））同样可以得到结果为“78”。大家可以变化B5和B9中的型号和规格来看看结果是否正确。

4．OFFSET函数

函数功能：以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以是一个单元格或单元格区域，并可以指定返回的行数或列数。

函数语法：OFFSET（reference，rows，cols，height，width）

OFFSET（基点单元格，移动的行数，移动的列数，所要引用的高度，所要引用的宽度）如表2－5所示。

表2－5 OFFSET函数参数说明

续表

应用实例：①OFFSET函数最简单的应用，即函数参数中第四、五个参数“height”与“width”省略，如图2－54所示。

图2－54 OFFSET函数应用实例1

在F3单元格，输入公式：“＝OFFSET（B3，3，2）”，得到结果为70。这个公式的意思就是计算B2单元格往下移动3行并向右移动2列得到的新的单元格D6单元格。

②OFFSET函数通常与其他函数来嵌套使用，如图2－55所示。

在C7单元格，输入公式：“＝SUM（OFFSET（C2，1，2，3，1））”，得到结果为18。这个公式的意思就是计算C2单元格靠下1行并靠右2列的3行1列的区域的和。

图2－55 OFFSET函数应用实例2

可以在公式编辑栏，选中OFFSET（C2，1，2，3，1）部分，按F9键抹黑，得到运算结果为：｛3；8；7｝，此时公式变为：＝SUM（｛3；8；7｝）。从上图可以得知，就是利用OFFSET函数来得到一个新的区域，然后使用SUM函数求出这个新区域的和。

七、工作表和工作簿的保护

（一）工作表的保护

如果希望编辑好的Excel工作表不被用户修改，可以对工作表进行保护。只有锁定的单元格才能被设置保护，默认Excel工作表中的所有单元格都是锁定的。要知道单元格是否被锁定，可以通过右击单元格，选择【设置单元格格式】，在弹出对话框的【保护】选项卡中查看。如图2－56所示。

图2－56 单元格的锁定

1．保护整个工作表

在Excel 2003中，选择某个需要保护的工作表，单击菜单【工具】∣【保护】∣【保护工作表】，打开【保护工作表】对话框，如图2－57所示。在对话框中根据需要选择某个选项，如选择“插入行”后，即表示允许用户在受保护的工作表中插入行。在文本框中输入密码，单击“确定”按钮完成工作表的保护。也可不设置密码，这样在解除工作表保护时就不必输入密码了。

图2－57 工作表的保护

2．保护工作表的部分区域

如果既要保护工作表，又要允许用户对某个区域进行编辑，这时可以对需要编辑的区域取消锁定。例如仅保护工作表中所有包含公式的单元格，步骤如下。

①选择工作表中所有包含数据的区域，或者按Ctrl＋A选择工作表中的所有单元格。单击【格式】∣【单元格】，打开【单元格格式】对话框，选择【保护】选项卡，取消选择“锁定”选项，如图2－58所示，单击“确定”退出【单元格格式】对话框。

图2－58 单元格取消锁定

②单击菜单【编辑】∣【定位】命令，可以按F5或Ctrl＋G，打开【定位】对话框，单击【定位条件】按钮，弹出【定位条件】对话框。选择【公式】选项，如图2－59所示，单击【确定】，即可选择工作表中所有包含公式的单元格。

图2－59 定位条件

③单击菜单【格式】∣【单元格】，打开【单元格格式】对话框，在【保护】选项卡中勾选【锁定】选项，单击【确定】。如果此处还勾选了“隐藏”选项，如图2－60所示，则保护工作表后，选择包含公式的单元格时就不会在编辑栏中显示公式。

图2－60 公式的隐藏

④用前面所述方法对工作表进行保护。这样，所有包含公式的单元格都被设置了保护，用户不能对其进行编辑。而其他单元格则能进行编辑。

3．撤销工作表保护

单击菜单【工具】∣【保护】∣【撤销工作表保护】，如果设置了密码则需输入密码，即可解除对工作表的保护。

（二）工作簿的保护

1．工作簿进行安全保护

与工作表一样，如果希望编辑好的Excel工作簿不被用户删除，可以对工作簿进行保护。

单击【工具】∣【保护】∣【保护工作簿】输入密码，如图2－61所示。

图2－61 工作簿的保护

2．撤销工作簿保护

单击菜单【工具】∣【保护】∣【撤销工作簿保护】，如果设置了密码则需输入密码，即可解除对工作簿的保护。