Excel软件在日常办公中的实战技巧应用

王景珊

(景德镇学院,江西 景德镇 333000)

0 引言

谈到Excel软件的应用,人们更多的会想到财务工作人员,Excel内置的数学、财务、统计等多种函数,及数据管理与分析等多种方法和工具,确实能够满足财务日常工作需要。随着办公自动化的普及,Excel软件得到广泛应用。以下为部分实战技巧案例,考虑到运行截图不占用太多文章空间,采用较少数据描述操作过程,实际工作中当数据较多时,操作步骤是相同[1]。

1 制作随机抽样名单

单位行政管理部门如果有重要的随机抽样调查,或是随机抽取获奖名单等类似活动时,工作人员不需要编写程序完成,Excel软件就提供了相应的函数,帮助工作人员快速完成此项任务,以下以随机抽奖为例,讲解制作随机抽样名单(假设抽取3名获奖人员)的过程,其步骤如下。

输入需要随机抽取的所有名单(本案例参与抽奖人员名单放置在c3:c11)——假设此次抽取3名获奖人员——利用INDEX函数和RANDBETWEEN函数——分别在D3,D6,D9输入公式——公式内容为:=INDEX($C$3:$C$11,RANDBETWEEN(1,9))——按住F9(键盘)不放即可不断随机产生不同的名单——直到喊停确定最后的获奖名单。

INDEX函数主要用于数据引用,其格式为INDEX(array,row_num,[column_num]),功能是返回表中给定区域(第1个参数),指定行和列(第2,3个参数)位置的值,则行、列交叉位置的值就是我们需要获取的值,如果第1个参数给定的区域为一行(或一列)的线性结构,则后面只需要指定列(或行)一个位置即可。RANDBETWEEN函数则是产生指定范围的随机数,其格式为RANDBETWEEN(bottom,top),bottom是范围的左边界整数,top是范围的右边界整数,结果是返回bottom与top之间的随机整数[2]。

本案例输入的公式是INDEX($C$3:$C$11,RANDBETWEEN(1,9)),INDEX函数的第1个参数$C$3:$C$11为线性结构,一列9行数据,给定了参与抽奖人员名单,INDEX函数的第2个参数,则使用了RANDBETWEEN(1,9)随机产生1~9之间的数字,所以在D3,D6,D9三个单元格,输入公式=INDEX($C$3:$C$11,RANDBETWEEN(1,9)),就能够在9名参与人员中,随机获取3名获奖人员名单,按住键盘功能键[F9]不放,随机数会不断变化,与我们平时在电视屏幕看到的抽奖过程一样,数据不断在变化,直到喊停,锁定最后结果。

2 批量图片链接

如果工作人员在某个时刻需要收集的数据中含有大量图片信息,处理的方法有多种,比如把这些图片全部直接插入到Excel表中,理论上是可行的,但会占用表格空间。若采用图片链接的方式插入图片,数据表格清晰简洁,感觉会比较好。但采用什么方法插入超链接呢,如果图片数量少,可以把图片一张一张的插入超链接,当数量较多,多到成百上千,一张一张的插入则费时费力,所以在实际工作中最好的方式是选择批量处理的方法插入链接,工作效率非常高。本案例只讲解批量图片链接的方法,忽略实际工作中其他数据,并使用6张图片讲解批量图片链接过程,图片存放在E:photo,具体步骤如下。

2.1 批量获取图片文件名

(1)在E:photo新建一个文本文件(扩展名为.txt),主文件名任意,本案例取名tupian.txt。

(2)文本文件tupian.txt的内容:dir/b/n/on>list.txt,可以把主文件名list改用其他名称。作用是将当前文件夹的文件名列举出来,并写入到list.txt文件中。

(3)将文本文件tupian.txt扩展名修改为.bat,此时会弹出一个对话框,点击确定更改即可,保存后会产生一个list.txt文件。

(4)双击打开list.txt文件,就会发现E:photo文件夹里所有图片文件的文件名瞬间全部获取,自动放置在list.txt文件中。

2.2 批量设置超链接

设置超链接,主要借助HYPERLINK函数完成。

(1)把list.txt文件获取到的文件名复制到Excel表格A2:A7中。

(2)超链接函数HYPERLINK的格式为HYPERLINK(“路径文件名”“文件名”),格式中的两个参数,若使用单元格,则双引号不需要。

(3)在B2单元格输入图片保存路径E:photo,并复制到B3:B7。

(4)在C2单元格输入公式=B2&A2,将文件夹路径B2与文件名A2用&运算合并,并复制到C3:C7。

(5)将第(4)步内容选择性粘贴复制成文本(此时B列数据可以删除,也可以不删除)。

(6)在D2单元格输入公式=HYPERLINK(C2,A2),并复制到D3:D7,这样使用超链接函数HYPERLINK快速完成所有图片的链接。

(7)单击D2:D7任意一个单元格,可以链接打开所需要的图片,大大节约了表格空间,效果如图1所示。

图1 插入图片链接

(8)把A,B,C三列的内容进行隐藏,只显示D列的链接内容即可。

A,B,C三列的内容进行隐藏,是因为D列的链接需要它们,作为中间数据在表格中我们又不需要它们,所以只作隐藏不能删除。在工作中有类似的不需要显示的中间数据,都可以作隐藏处理。

3 批量设置重要数据掩码

通常一些重要的信息,从安全角度考虑会用掩码显示,比如在银行ATM进行存款或取款时,回执单上不会显示完整的银行账号,只会显示带有∗号的银行账号,在工作单位,也有类似的情况,有些数据对外是不便公开显示全部信息,比如职工的身份证号,需要保护职工的个人隐私,每当遇到这些情况,可使用REPLACE函数设置数据的掩码。REPLACE函数的功能是将一个字符串中的部分字符用另一个字符串替换,其格式为REPLACE(要替换的字符串,开始位置,替换个数,新的文本),本案例原始的完整身份号码(放置在F3:F7),添加掩码的身份证信息,如图2所示。具体操作过程如下。

图2 掩码信息显示

“学历”字段前面增加一列,字段名为“身份证号码”——在G3单元格输入公式=REPLACE(F3,9,6,“∗∗∗∗∗∗”)——将G3公式复制到G4;G7——为确保数据安全将G3:G7数据复制并在原位置选择性粘贴文本(可以不做)——将F列数据(身份证完整信息)设置为隐藏。

特别注意一点,添加了掩码的身份证信息,需要依赖于身份证完整数据(放置在F3:F7),所以原始完整信息依然需要保存,否则造成数据信息不完整,就拿前面所讲的银行ATM回执单,虽然呈现出来的是部分带∗号的银行账号,但在银行的数据库中一定是需要保存完整的银行账号的,才能够正确记录着每位客户的收支信息。这也就是最后一个步骤将F列数据(身份证完整信息)设置为隐藏的原因[3]。

4 结语

Excel软件的实战技巧是基础知识的延伸和拓展,在以后的工作中继续努力,将理论和实践有机结合在一起,探索更多方便且实用的方法和技巧应用于日常工作,提高日常办公的工作效率。