在审针中如何利用Excel进行抽样

在审针中如何利用Excel进行抽样

在当前审计软件应用并不是很普遍的情况下，注册会计师如何规避因审计抽样不具有代表性而产生的审计风险是每位注册会计师面临的现实问题。而Excel软件的强大功能为我们审计抽样提供了科学有效的途径，不仅能提高审计效率，还能降低审计风险。本文结合笔者使用Excel2003的情况谈谈注册会计师在审计过程中如何利用Excel进行抽样并生成抽样清单。一、利用函数RAND进行审计随机抽样随机抽样是抽样总体中的每个样本都有相同机会被抽中的一种抽样方法。而Excel中的函数RAND0产生的正是一个介于0到1之间的均匀分布的随机数。如要生成a与b之间的随机实数，公式应改成：RAND0*(b-a)+a注册会计师在审计抽样时，可以利用Excel中的另一函数ROUND对该公式所产生的随机实数进行四舍五入取整求得所需的随机数。例如，从一组有20个审计对象的抽样总体中随机选择4个样本，具体抽样公式和结果详见图1。上述使用RAND函数随机抽样的结果表示本次抽到的样本分别是序号为2、4、6和19的审计对象。这里简要介绍函数ROUND的使用方法：ROUND返回的是某个数字按指定位数取整后的数字。ROUND(number,num_digits)Number为需要进行四舍五入的数字。Num_digits为指定的位数，按此位数对Number进行四舍五入。如公式为“=ROUND(2.15,1)”，则其表示的意思是将2.15四舍五入到一个小数位，其结果显示为2.2。如公式为“=ROUND(2.15,0)”，则其结果显示为2。在使用函数RAND生成一随机数后，如按F9或者对其他单元格修改确认后，函数RAND将会重新产生一个随机数。在上图中按F9后，审计随机抽样结果单元格内则显示为另一组随机数，即抽到的样本序号分别为7、10、3和17，具体详见图2。二、利用数据分析中的抽样功能进行审计抽样Excel2003软件中“工具／数据分析／抽样”提供了周期抽样和随机抽样两种功能。（一）周期抽样周期抽样（等距抽样）是指按照相同的间隔从审计对象总体中等距离地选取样本的一种选样方法。利用这种抽样方法，操作者只需要输入周期间隔，计算机自动将输入区域（即审计对象总体）中位于每一间隔点处的数值复制到输出列中。例如，在图1中的审计对象总体中，以每隔4个对象的间隔来抽取审计样本，其操作步骤如下：1.打开“工具／数据分析／抽样”如果Excel中尚未安装“数据分析”工具，则应选择“工具／加载宏”，在加载宏对话框中选择“数据分析库—VBA函数”后确定即可，此时可能需要在安装光盘的支持下才能加载“数据分析库”。数据分析加载成功后，可以在工具栏的下拉菜单中看到“数据分析”选项。2.“输入区域”选择A1∶A21，即A列“序号”，是抽样总体中每个单元的编号，“抽样方法”选择“周期”，“间隔”输入4，“输出选项”选择“输出区域”，并选择F2（只要输入“输出区域”左上角的单元格即可）。具体如图3所示。值得注意的是，输入区域的数据必须是数值型数据，否则无法抽样，并显示出错信息。如果抽样总体中没有数值型数据，则应为抽样总体中创建数值型数据后方可抽样，如本例中为抽样总体创建一个序号。图1RAND函数示例之一图2RAND函数示例之二图3间隔为4周期抽样示例3.单击确认得到抽样结果，即得到F2∶F6共5个周期抽样的审计样本，如图4所示。（二）随机抽样在数据分析随机抽样中，只要输入所需的样本数，计算机将进行随机抽样。数据分析中的随机抽样和周期抽样操作除了抽样的方法选取不一样外，其他操作完全一样。如果选择的是“周期抽样”，则在“间隔”框内输入间隔数；如果选择的是“随机抽样”，则在“样本数”框内输入所需要的样本数。同样利用图1的总体数据应用数据分析中的随机抽样功能进行抽样，其操作步骤如下：1.打开“工具／数据分析／抽样”。2.“输入区域”选择A1∶A21，“抽样方法”选择“随机”，“样本数”输入5，“输出选项”选择“输出区域”，并选择G2。具体如图5所示。3.单击确认得到抽样结果，即得到G2∶G6共5个随机抽样的审计样本，如图6所示。数据分析中的随机抽样产生的随机数与函数RAND产生的随机数不同之处在于，前者产生的随机数一般保持不变，不会像后者产生的随机数那样因按F9或者对表格中其他单元格修改确定而改变。在随机抽样时，总体中任何一个数据因存在可能被多次抽取情况，因此在抽样结果中可能会出现样本重复的现象，随机抽样所得到的实际样本数量可能小于所需数量。因此，注册会计师在利用Excel随机抽样选取样本时，应根据经验适当调增样本数量，以保证最终所得样本数量不少于所需数量，从而达到审计抽样的目的。三、利用数据分析中的随机数发生器功能进行审计随机抽样这种方法就是应用Excel菜单：“工具／数据分析／随机数发生器……”来审计抽样。例如，要在图1的抽样总体中随机抽取5个样本，注册会计师就可以利用Excel中的随机数发生器功能在H2∶H6单元格内生成5个介于1至20之间均匀分布的随机数，以取整后的整数作为审计抽取的样本。具体操作步骤如下：图4间隔为4周期抽样结果图5随机抽样示例图6随机抽样结果图7随机数发生器示例图8取整后的5个随机数（一）打开“工具／数据分析／随机数发生器”。（二）填写“随机数发生器”对话框中的选项，具体如图7所示。其中，“变量个数”是指抽样时拟抽取的变量个数，在注册会计师审计抽样时的变量个数为1，即在审计对象总体中选取一组样本，因此，对话框中的“变量个数”输入1。“随机数个数”是指审计所需抽取的样本个数，此例中应输入5。“分布”是指用于创建随机数的分布方法，而在审计抽样中要创建的随机数是呈均匀分布的，因此例中的“分布”选择“均匀”分布，同时将参数设置为介于1与20。“随机数基数”在审计抽样时可以忽略。“输出区域”输入F2。图9用VLOOKUP生成的样本清单（三）单击确认得到5个在1至20之间均匀分布的随机数，如图8F列所示。对于得到的这5个随机数，应用函数ROUND四舍五入取整（详见图8），取整后的随机数可作为对总体为20个审计对象的随机抽样。由于利用这种方法抽取审计样本也会出现样本重复的现象，因此，注册会计师在审计抽样时也应考虑适当增加样本数量，以达到抽样的效果。四、利用函数VLOOKUP生成抽样清单注册会计师再通过上述方法确定样本后，如何快捷地将被抽取到的样本数据生成一张样本清单呢?函数VLOOKUP可以有效解决这一问题。VLOOKUP是一个查找函数，其功能是在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。VLOOKUP(lookup_value,table_array，col_index_num,range_lookup)lookup_value：为需要在数组第一列中查找的数值。table_array：为需要在其中查找数据的数据表。col_index_num：为table_array中待返回的匹配值的列序号。range_lookup：为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。如果为TRUE(1)或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookuo_value的最大数值；如果rang_value为FALSE(0)，函数VLOOKUP将返回精确匹配值。利用函数VLOOKUP能将上述3种方法抽取的样本快速地输入相应的信息，形成抽样清单。以下以第三种方法抽样结果为例说明如何使用函数VLOOKUP生成抽样清单，具体操作如下：在H2∶K2的每个单元格中分别输入函数VLOOKUP，可得到各样本对应的“月份”、“凭证号”、“内容”和“金额”等信息。如单元格H2应输入样本序号为14的月份信息，即为9月，因此在单元格H2中输入的公式为“=VLOOKUP(G2,$A$2∶$E$21,2,1