Excel高级应用

Excel高级应用（精选七篇）

Excel高级应用 篇1

一、防止数据重复输入

很多时候, 录入的数据是不允许重复的, 例如学生档案资料中的"学号", 员工资料中的"身份证号"、"毕业证号"等等。这时可以通过设置"数据有效性"来防止数据重复录入。

例如我们要在B列输入身份证号, 可以先选定B列, 然后选择"数据"/"有效性"菜单, 打开"数据有效性"对话框, 在"设置"选项下的"允许"栏, 选择"自定义"选项, 在"公式"文本框中输入公式"=COUNTIF (B:B, B1) =1"。接着切换到"出错警告"选项卡, 在错误信息一栏中输入"您输入了重复数据!", 单击"确定"。现在如果在B列中输入相同的身份证号, 就会弹出提示对话框, 并拒绝接受输入的号码。

二、检查字数

录入数据时, 有些字段的数据长度是有规定的, 例如某校学生的"学号"是五位数, 这可以在数据有效性的"设置"选项卡的"允许"栏中选"文本长度"来轻松设置。但当要输入员工的身份证号码时, 字数要求是15或18位, 这时就需要用函数来检查输入的字数是否满足要求。选中B列, 在数据有效性窗口的"允许"栏中选择"自定义", 在公式一栏中输入"=OR (LEN (B1) =15, LEN (B1=18) "或"= (LEN (B1) =15) + (LEN (B1) =18) "。接着切换到"出错警告"选项卡, 在错误信息一栏中输入"您输入的字数有误!", 单击"确定"即可。

三、实现下拉列表选择录入

采用下拉列表选择录入可以大大地提高数据的录入速度和可靠性。假如学生的生源地只有三个值:浙江, 福建, 江苏。如何实现下拉列表选择录入呢?

首先选定要实现下拉菜单效果的行列或区域, 然后打开"数据有效性"对话框, 在"设置"选项下, "允许"栏中选择"序列", 在"来源"中输入"浙江, 福建, 江苏", 或者先把序列值输入到某一区域, 然后在"来源"文本框中使用该区域引用 (注意:如果数据源区域在不同的工作表, 则应使用自定义名称) , 勾选"忽略空值"和"提供下拉箭头"两个复选框, 单击"确定", 就可以进行选择录入了。

四、建立分类下拉列表

采用下拉菜单选择录入, 可以对数据精确筛选, 但是如果下拉菜单中的选项太多, 则会影响录入的速度。如果这些选项内容可进行分类, 就可以用多个下拉列表 (即级联菜单) 来解决此类问题。

例如, 一电脑销售部要建立销售表, 用以记录每天售出电脑的品牌与型号, 由于品牌多, 各品牌又有多种不同型号, 所以在销售表中有必要将品牌与型号分开录入。

下面, 就以电脑品牌分类列表为例, 介绍操作步骤:

1.建立一个"品牌与型号"工作表, 如表1所示。

2.定义名称。将区域A1:D1, 定义名称为"品牌", 将区域A2A8, 定义名称为"清华同方", 同样, 给其他品牌的型号自定义一个名称。

3.选中"品牌"列的相应单元格区域, 打开"数据有效性"对话框, 选中"序列"选项后, 在"来源"下面的方框中输入公式"=品牌", 单击"确定"。这样, "品牌"列就建立了一个下拉列表。

4.选中"型号"列的相应单元格区域, 打开"数据有效性"对话框, 选中"序列"选项后, 在"来源"下面的方框中输入公式"=IN-DIRECT (A2) " (此处假设"品牌"在A列) , 单击"确定"。

5.以后在"品牌"列相应单元格中选择输入电脑品牌后, 单击"型号"列对应的单元格, 就只有所选品牌的型号显示在下拉列表中了, 如图1所示。

Excel数据有效性虽然很有用, 但还是有局限性的。它只对设置了有效性后输入的数据进行自动检查, 设置有效性前录入的数据不会受检查, 在设置有效性后, 采用复制粘贴法录入的数据, 也不会受检查。这时, 可以使用"公式审核"工具栏上的"圈释无效数据"按钮进行审核。

摘要：从实例出发, 探讨Excel数据有效性在防止数据重复输入、实现下拉列表选择录入、建立分类下拉列表等方面的应用, 并指出数据有效性应配合公式审核来使用, 才能发挥更大的作用。

关键词：数据有效性,Excel函数,公式审核

参考文献

[1].张学港."数据有效性"在Excel中的应用[J].电脑知识与技术 (经验技巧) , 2008, (05)

Excel高级应用 篇2

课程收益：

你可以让员工只用鼠标就能完成你设计的各类表格，并且不会出错。

你可以建立系统化的流程，减少人为操作出现的错误。

你可以系统地实现人力合同的数据库管理。

你可以快速地分析公司管理结构和工资结构是否平衡合理。

你可以建立动态的薪酬调整方案，并找到可选范围。

可以通过离职人员数据多纬度分析，对员工流失的可能性进行提前防范。

你可以通过驱动因素表，直观地建立各部门和岗位目标一致的量化KPI指标体系。

你可以知道你的关键业务员工和有潜力性员工在那里，可以给于他们更大的关注。并有目的的进行人员搭配。

课程大纲：

模块一、小小技巧让你轻轻松松获取所需源头数据

制作用户友好界面的通用表格的方法.

让你不再为收集的表格不符合你的要求而犯愁。

让填表的人明白你的需求，只填你让他填的内容和格式。

如何友好地提示数据错误

案例：友好表格

获取和转换其他系统数据的技巧

从其他系统中取得数据的方法

在Excel中合并、分割数据的要点

进行数据分析前的准备工作要点

如何计算时间

案例：考勤机进出记录的转换

模块二、快速准确地实现数据连接

如何应用Excel函数实现数据连接

数据连接的常用函数

多条件数据连接的方法

如何自动处理数据连接过程中空条件

案例：薪资表的统计与分析

模块三、如何何制作流程化管理的薪资表

会减少数据录入的小窍门

通过基础表实现动态档案管理

报告数据表和核算数据表分离带来的高效办公

避免频繁调整薪资表公式的小方法

常用参数如：计薪日、个税免征额的设定技巧

常用区域数据的设定技巧

利用权限分离实现流程化的薪资编制

通过定调资表固定薪资表的基础薪资项

通过控制可编辑数据项减少人为差错率

薪资表权限分离让薪资审核员轻松签字

案例：薪资表制作与统计分析案例

模块四、人力成本分析的技巧

从薪资表数据到人力成本报告的方法

利用数据透视表快速实现部门、岗位、薪资项目的多维度统计

薪资结构分析的技巧

薪资趋势分析的技巧

案例：人力成本分析报告

模块五、人力成本的趋势动因

人力成本分解及其主动因趋势

将人力成本按照人数、人均成本进行分解

学会利用相关性系数找出人数和产销量之间的动因关系

学会利用相关性系数找出人均成本和CPI指数之间的关系

Excel高级应用 篇3

【关键词】Excel 2010；高级筛选；条件区域

Excel 2010的筛选功能可以帮助我们从已有的复杂数据表中轻松得到所需数据。筛选功能有两种方式：自动筛选和高级筛选。其中“自动筛选”只能用于条件简单的筛选操作，不能实现字段之间包含“或”关系的操作；“高级筛选”则能够完成比较复杂的多条件查询，并能将筛选结果复制到其它位置。

一、高级筛选的操作过程

高级筛选的操作过程分两步进行。

步骤一：单击“数据”选项卡中“排序与筛选”功能区的“高级”命令按钮(如图1所示)，打开“高级筛选”对话框。

图1 排序和筛选

步骤二：设置筛选方式，筛选出满足条件的结果。

(1)在“方式”下，选中“将筛选结果复制到其它位置”的单选按钮(如图2所示)；

(2)单击“列表区域”右侧的拾取器按钮，进行单元格区域选取；

(3)单击“条件区域”右侧的拾取器按钮，选取输入的筛选条件单元格区域；

(4)单击“复制到”右侧的拾取器按钮，设置显示筛选结果的单元格区域；

图2 高级筛选

(5)单击“确定”按钮。系统会自动将符合条件的记录筛选出来并复制到指定的单元格区域。

注意：若要通过隐藏不符合条件的行来筛选区域，请单击“在原有区域显示筛选结果”，系统会自动将符合条件的记录筛选出来并复制到指定的单元区域。

二、应用实例

Excel 2010高级筛选的关键之处在于正确地设置筛选条件，即建立条件区域。条件区域可以是通配符、文本、数值、计算公式和比较式。

在Excel 2010中，条件区域构造的规则是：同一列中的条件表示“或”，同一行中的条件表示“与”。还有“或”、“与”的复合条件，用公式创建条件等。为使读者有直观的认识，下面通过实例具体进行说明。

1.根据工作表某一列构造一个筛选条件

使用通配符“*”进行高级筛选。“*”代表若干个字符。以《学生基本信息表》为例，查找姓“李”的所有学生的记录(如表1所示)。操作步骤。

(1)建立条件区域：在数据区域外的单元格B15中输入被筛选的字段名称“姓名”，在其下方的单元格B16中输入筛选条件“李*”。

(2)使用“数据”选项卡上“排序和筛选”组中的“高级”命令，弹出“高级筛选”对话框，选择筛选方式中的“将筛选结果复制到其它位置”单选按钮(如图3所示)。

表1 学生基本信息表

图3 高级筛选

(3)分别将“列表区域”设置为“$A$2:

$G$13”；“条件区域”设置为“$B$15:$B$16”；

“复制到”设置为“$A$18:$G$18”。

(4)单击“确定”按钮，系统会自动将符合条件的记录筛选出来并复制到指定的从A18开始的单元格区域中(如表2所示)。

表2 筛选结果

2.根据工作表某一列构造复合筛选条件

再以表1学生基本信息表为例。筛选年龄在1991年1月1日至1992年1月1日之间出生的学生的记录。操作步骤：其它操作步骤同上，条件区域设置如下(如表3所示)。

单击“确定”按钮，系统会自动将符合条件的记录筛选出来。

表3 筛选筛选及结果

3.根据工作表多列构造单一筛选条件

如果将筛选条件输入在同一行中，筛选时系统会自动将同时满足所有指定条件的记录筛选出来。即条件之间的关系为“与”关系。在《学生基本信息表》中，查找“性别”为女性、“专业”为嵌入式技术，“入学成绩”在400分以上且是“籍贯”是河南柘城的学生记录。

操作步骤：其它操作步骤同上，在条件区域设置如下(如表4所示)：

单击“确定”按钮，系统会自动将符合条件的记录筛选出来。

表4 筛选条件及结果

4.根据工作表多列构造复合筛选条件

如果要表示“或”关系的条件，则要求在字段下方的不同行输入条件，筛选结果只满足其中任意一个条件。在《学生基本信息表》中，查找“性别”为男性、“专业”为嵌入式技术，“入学成绩”在360分以下，“籍贯”不一定是河南柘城的学生记录等。

表5 筛选条件及结果

操作步骤：其它操作步骤同上，在条件区域设置如下(如表5所示)。

单击“确定”按钮，系统会自动将符合条件的记录筛选出来。

5.根据公式的计算结果构造筛选条件

公式的计算结果可以作为筛选条件，在《学生基本信息表》中，需要找出进入本校所有学生入学成绩的平均分，并筛选出高于平均分的全体同学的记录。操作步骤：

建立条件区域：在D16和E16中分别填写“满足”和“平均分”，在E17中输入计算平均值的公式“=AVERAGE(F3:F13)”，回车后计算结果433直接显示在E17单元格中。在D17中输入筛选条件“=F3>=$E$17”，回车后D17显示为“TRUE”。(因为F3的数值480>433，如图4所示)

图4 条件区域

使用“数据”选项卡上“排序和筛选”组中的“高级”命令，选择筛选方式中的“将筛选结果复制到其它位置”单选按钮(如图5所示)。

图5 高级筛选

单击“确定”。高于平均分的全体同学记录显示出来(如表6所示)。

表6 筛选条件及结果

将公式的计算结果作为筛选条件时务必注意如下两点：

(1)构造条件区域时：要求标题行不能和数据表中标题行相同。如D16中“满足”E16中“平均分”不同于《学生基本信息表》中的标题。

(2)若构造条件区域时，标题行和数据表中标题行相同，计算机则告知“引用无效”。

上例中找出所有学生入学成绩的平均分，用作条件的公式必须使用“相对引用”来引用列标。如“=AVERAGE(F3:F13)”。公式中的其他所有引用必须为“绝对引用”，如“=F3>=$E$17”中的“$E$17”。并且公式的计算结果以“TRUE”或“FALSE”形式出现。

三、结束语

利用Excel 2010高级筛选功能，关键在于巧妙构造条件区域。

(1)非公式筛选，条件区域的标题与数据区域标题保持一致。高级筛选前应将筛选条件输入在工作表的空白区域，且条件区域的位置要与原数据区域保持至少空一行或一列的位置。

(2)用公式创建条件时，必须使用非数据表标题，公式正确，就能计算出相应正确的结果。

(3)用比较式完成筛选应在条件区域的相应单元格中键入“=条目”，否则将不能对数据做出正确筛选。

参考文献：

[1]张增伟.EXCEL函数在编制水利工程报价中的应用[J].水利水电工程造价,2005(04).

[2]单振清,宋雪臣.基于VB的EXCEL动态报表技术[J].山东水利职业学院院刊,2008(01).

[3]梁广洪.巧用EXCEL函数提取身份证中信息[J].铜仁职业技术学院学报,2010(02).

作者简介：

陈文兵（1973—），男，湖北仙桃人，1996年毕业于长江大学，仙桃职业学院计科院副院长，副教授，研究方向：计算机软件开发。

郭萍（1963—），男，湖北仙桃人，1988年毕业于中南财经政法大学，仙桃职业学院计科院副院长，副教授，研究方向：计算机网络安全。

Excel在办公中的高级应用初探 篇4

关键词：Excel,高级应用,数据编辑

Excel是日常办公中使用率最高的数据处理软件,它不仅可以制作出外观漂亮的表格及图表,同时在数据的输入、管理和运算整合方面具备强大的功能。虽然和一些专业的数据库软件相比Excel显得过于简单,但根据用户的需求不同,如果能深入挖掘Exce的“数据编辑”功能,而不仅仅把它作为对外部数据进行加工的文字编辑工具,我们也会体验到Excel在办公应用中的巨大魅力。

下面就通过对几种Excel常用功能的深入探究来实现办公中的高级应用。

1 根据目的对“排序”操作进行高级设置

Excel中的“排序”是一个非常常用的功能,最简单的排序是使用工具栏中的“排序”按钮进行排序。一般会按照当前字段的内容按升序或降序排列,但大多数人没有注意到当当前字段内容为中文汉字时,默认是以字母为基准,即按汉字拼音的首字母在26个英文字母中出现的顺序进行排列。如果想以笔画进行排列时,依次选择“数据”→“排序”命令打开“排序”对话框,再单击左下角的“选项”按钮,在“排序选项”对话框中选取“方法”选项区域中的“笔画排序”,如图1。

如果笔画相同,会按照内码顺序进行排序。不过要注意,当在“排序选项”中选择了排序的方法后,工具栏的排序按钮也将按此处指定的方法进行排序。需要回到默认设置的,要从这里改回设置选项。

2 通过高级筛选进行更详细的筛选

Excel中的“筛选”功能在一些较复杂的数据处理中也是常用的功能,普通的“自动筛选”虽然简单易用,但要以更详细的条件进行数据筛选时,就要使用“高级筛选”的功能。“自动筛选”功能中诸如“对一个字段只能指定两个条件”、“要跨越多个字段进行筛选条件设置时,必须对一个一个字段分别进行设置”等限制,都可以在“高级筛选”中解决。“高级筛选”是在单元格中记录筛选条件,筛选符合条件的记录。表达式的书写开始显得比较麻烦,但一旦习惯之后就可以非常快速地进行复杂数据的筛选操作。如果办公中有需要进行大量数据的筛选和转存时,就一定要善于这个功能。表达式的编写有一定的规则,例如“多条件的指定”、“且(AND)条件”、“或(OR)条件”等的表达式,下面简单介绍。

编写筛选条件时最基本的规则是:编写作为字段的对象名称,并将筛选条件编写在其下的单元格中。筛选表达式可以使用字符串与运算符、万用字符等的组合来表现。表1给出筛选条件的范例。

每一个单元格的表达式都是以上表的类型来编写的。

筛选出符合任一条件的数据,要在纵向来追加表达式。且列于纵向的表达式全部都会作为“OR表达式”来处理。如下面例子

以上表达式会筛选出所有姓刘或贺的记录。

筛选出与多个条件一致的数据时,就要在横向添加表达式。且列于横向的表达式全部都会作为“AND表达式”来处理。例如下:

以上表达式会筛选出所有姓刘并且30岁以上的男性的记录。也可以出现以下形式:

以上表达式会筛选出所有姓刘并且年龄在30岁到40岁之间的男性的记录。还可以有以下形式:

以上表达式会筛选出所有姓刘并且30岁以上的男性,住址不限的记录,或姓名、年龄、性别是什么都可以,只要住址是嘉峪关的女性。

这里要注意,“空格”与“ALL条件”是有差异的。将什么都没有写的单元格当做表达式使用将会成为“ALL条件”,所以要筛选的条件是空白单元格的话应该使用类似于下面的表达式:

上面的表达式会筛选出没有输入“姓名”的记录。

3 利用VBA转存筛选出的数据

在很多利用Excel进行大量数据处理的应用中都要以VBA来达成转存筛选出的数据的过程。一般要将进行了筛选的数据复制到其他位置时,筛选时被隐藏起来的数据将自动排除在复制范围外。一般人们会将筛选出的范围指定为“可见数据库”进行复制:

Set my Rng=Range(“A1”).Current Region

my Rng.Auto Filter Field:=6,Criterial:=”高一”

‘以第6个字段为基础进行筛选

my Rng.Special Cells(xl Cell Type Visible).Copy

‘只复制可见单元格

上面的代码按照刚才提到的特性,也可以改为如下形式,效果是相同的:

Set my Rng=Range(“A1”).Current Region

my Rng.Auto Filter Field:=6,Criterial:=”高一”

my Rng.Copy

‘这样就可以只复制筛选出来的显示范围

还要注意,Excel中,因为筛选而隐藏的列和使用“隐藏”功能而成为隐藏的列其操作方式是不同的。应用了隐藏功能的单元格范围,如果直接复制,就会连同成为隐藏状态的单元格也一起复制。即是,如果制作出有隐藏行与列,并在其中输入函数的情况,在复制时就必须要选取“看得见的单元格”来处理。如果在复制时没有办法处理需要的数据时,要分清楚这两种隐藏状态造成的不同。

4 结束语

只要深入挖掘Excel的功能,探索其“数据编辑”方面的高级应用,其实人们在普通的办公用数据处理中需要的数据库软件的功能都可以由Excel来实现,特别是如果能进一步深入探究使用VBA结合Excel模仿数据库应用程序,更是能实现及其强大的办公应用。使用EXCEL在办公中制作数据库,不但简单易用,还有自由度高,维护的人要求不高的优点,值得大力研究推广。

参考文献

[1]李政,李莹,张羽,李国辉.Excel高级应用案例教程,2010,(5).

[2]黄海.EXCEL公式·函数·图表·VBA一本通,2009,(6).

[3]伍远高.Excel VBA开发技术大全,2009,(6).

Excel高级筛选详解 篇5

1、可以把数据筛选到其他表格

2、可能使用更加复杂的筛选条件，当然实现的功能也远非自动筛选能比的，

高级筛选就是玩条件区域，下面根据实例介绍一下基本设置步骤。

例：根据下面的数据表，把上海的记录筛选到“问题1”工作表中。

1、设置条件区域。高级筛选的条件区域一般是由标题行和下面的条件行组成。但在使用公式时标题行可以留空。本例中图1的A1：A2区域。

2、打开“问题1”工作表，执行“数据”-“筛选”-“高级筛选”，在打开的窗口中进行如下设置。

方式：选取将筛选结果复制到其他位置，

如果在在本工作表中显示筛选结果，可以选取第一个选项

列表区域：选取数据源区域。

条件区域：A1：A2单元格

复制到：这里选取在“问题1”工作表中设置的标题行第3行，注意，标题行的列标题和源数据表中字段名要完全一致。多少一个空格就会出错。

设置完前面的内容后，点确定就可以把所有上海的产品筛选到本工作表中了。

设置并列两个条件

高级筛选中，并列条件可以用列的并列排放即可。

并列两个条件，库别是上海，又要满足类别是电视机，可以如下图设置条件区域。

如果并列三

个条件呢?那就再添加一列..同理可以设置更多条件。

Excel高级应用 篇6

VBA是Basic编程语言中较常用和流行的一种语言。它继承了Basic的很多特性和优点, 使各个对象桌面软件设计更为方便, 随着Basic语言的发展和提高, 功能应用更变得灵活, 维护更方便, 为了统一在Windows下使用宏语言, 并且可以使用Windows的DDE和ODL方法连接这些应用宏语言, Microsoft创立了VB语言的特殊版本, 它就是Visual Basic for Application (简称为VBA) , 要执行一个VBA宏命令, 它要被保存在它对应的应用程序里, 虽然Excel可以连接一个从其他应用程序发来的DDE消息并执行, 但是必须是Excel的VBA宏, 总的来说虽然VBA的主要特征在每个应用程序都差不多, 但是不同的应用程序还是根据不同的需求在VBA中增加特殊命令和对象, 比如Excel中是跟工作表和数据等对象有关, 如果VBA宏命令离开工作表后, 这些VBA宏命令就无法运行和使用了。

VBA服务于Excel越来越普遍, 用户使用宏命令录制比较简单, 另外它所带来的功能除了一些增强特定的宏以外, 宏还可以完成由几个小任务组成的综合性任务, 比如:在学生信息管理中经常用到要从数据库程序中提取数据到Excel表格, 进行数据格式化, 再统计汇总, 并且制作出各个查询功能, 最后进行打印各个报表, 要把这些小操作通过宏组建起来, 那么就是制作一个宏用于输入数据, 一个宏是用于统计汇总, 一个宏是用于查询, 一个宏是用于打印, 最后用VBA语言编程组建小宏执行这些功能, 以方便用户使用。

2 VBA在Excel中的优势

2.1 宏的录制比较简单

宏是主要是一个指令集合, 在Excel中可以录制完成任务的动作来简单化解决。录制宏操作和使用方法比较简单, 不用要求用户了解宏命令编程, 用户在Excel中执行的每一个步骤记录信息都会一一记录存储指定给某个快捷键, 运行宏命令 (按下快捷键) 时Excel会把录制过程中的操作重复呈现, 每次使用相同的操作都可以使用此快捷键, 它可以准确无误执行上次指定的命令。但是宏录制中有一个缺点, 如果宏录制过程中有个错误引用录制会失败, 用户不得不重新录制, 所以录制过程中必须认真仔细。

2.2 解决工作表大量重复操作

在Excel中经常要重复解决大量的数据, 那么就要大量重复的操作, Excel内置函数解决这种问题比较繁琐复杂, 而利用VBA提供的编写程序就事半功倍, 可以简化实际操作提高工作效率。

2.3 实现人机交互操作

对于大量用户不会使用VBA编程语言和宏录制, 那么利用VBA已经编程制作好的人机相结合的窗口, 利用VBA开发好的窗口实现数据处理的交互性, 更能体现Excel人性化、灵活性、动态性, 比如查询学生缴费情况, 在窗口中输入学生学号, 在Excel马上突出显示该学生缴费情况, 或者查询该学生的成绩汇总等窗口, 另外也可以设置某些窗口权限设置。

3 VBA技术应用——财务举例

大量用户利用VBA在Excel中的强大、灵活、方便、便于维护, 都在办公处理使用VBA宏操作功能。在企业中财务分析汇总安全性就是一个很实用的例子, 企业中最重要的是数据安全性, 也就是整个工作簿的安全性, 这是每个公司的所有财务用户所关心的, 防止别人打开工作簿, 获取其中的信息, 或删除修改公司财务数据等, 这些都是公司财务人员在日常工作中经常遇到的问题, 现在用宏代码举例如下:

在财务中计算按照一年365天的算法并不适用于会计的计算, 平年闰年之分和多出的5天给财务计算带来不便, 所以在财务统计时用一年360天来计算, 这样计算在账本上比较清楚。那么在VBA中来解决这个问题, 可以定制一个函数Daycount360来满足财务上的需求, 这个函数直接是以每个月30天来计算, 现在把它定义为Daycount360 (startdate, enddate, parameter) , 可以看出startdate是一个起始日期, 而enddate是作为一个结束日期, 最终Daycount360函数返回一个日期计算的差值, 如果Daycount360函数返回的是一个负值, 取两个日期差值的绝对值, 比如执行:daydis=Daycount360 (“2015-4-1”, “2015-12-1”, false) 与daydis=Daycount360 (“2015-12-1”, “2015-4-1”, false) , 它们返回值分别为正与负值。Parameter参数是逻辑判断, 当值为false会采用欧洲方法, 当值为True时采用美国方法, 但是在财会计算处理中经常会采用欧洲方法。但是在使用该函数一定要谨慎, 因为它是以30天作为一个月来计算的, 对一些需要精确度很高的日期计算就不适用了, 必须把计算改为每年365或者366天计算。在Excel中还提供其他日期函数, 更能方便用户取得相应的日期或者时间, 比如, 显示当前系统时间或者日期函数为now () 、today () , 如果要在其中取出相应的年份、月份等, 可以使用year (today) , month (today) , day (today) , 这些都给用户带来方便。

4 结语

总之VBA是新一代Windows应用软件的语言, 它能给Excel应用程序提供一种方法, 让用户完成Excel中不能具备的功能, 使用户完善决策, 掌握和熟练使用Excel中的VBA工具, 可极大提高办公的自动化和程序的实用性。

摘要：Excel在office软件中是一个功能强大、技术先进的数据表格分析和汇总的系统, 但是计算机毕竟只是帮助人类进行一些简单的数据处理机器, 很多日常繁琐的数据处理还需要使用VBA编辑器来处理, 比如简单的财务大量数据录入、盈利分析等, VBA只要用一个简单的命令控件就能解决, 从而实现自动化。VBA的应用程序与Excel的各对象紧密结合, 使Excel的功能得到扩展, 被越来越多的用户接受和运用。

关键词：VBA,Excel,财务应用,人机交互

参考文献

[1]孙敬杰.Excel VBA入门与实例演练[M].天津:中国青年出版社, 2005.

[2]陈江茹.office2007办公实战宝典[M].北京:中国铁道出版社, 2009.

Excel高级应用 篇7

在日常生产和生活中,经常会遇到数据的提取和统计工作,比如现代企业集团公司不同时期、地点、品种、销售点的销售量和销售额的统计;比如现代宏观经济总量的GDP、GNP和就业率等的统计;比如高校对教学和科研工作量的统计。看似容易简单的结果其实少不了大量人员的细致的工作和大量基础数据的准备。计算机技术应用和普及应该是几大程度的方便以上的工作要求,而目前基础数据存储的格式或中转数据的格式有相当一部分是以EXCEL格式存在的。这并不是说Microsoft公司的EXCEL产品具有无比的优越性,但EXCEL的广泛应用及普及说明EXCEL的方便性和易用性,例如数据编辑和显示一体化、数据之间可关联性操作、大量基础和专业函数的支持等。对数据一般性的查询和查找在EXCEL来说比较容易,而对于统计工作中的数据分组汇总和同比环比的概念就有一定的不方便性。而有人会说EXCEL本身可以实现和胜任这些工作的,但问题如果一个两个文件进行一些复杂操作是没有问题的,如果要考虑这方面工作的长期性和重复性,就会觉得费时费力。这里需要指出的是同比数值主要是本期值与上一年同期值的比较值,环比是指本期值与上期值的比较值,比较值有差比和百分比,比如说今年销售收入比去年增加或减少了300万元(差比)或者是30%(百分比)。

对于这样的问题,很容易想到的方案会有这么两种,一是采用利用EXCEL现有的强大的数据关联计算和显示功能;二是采用数据库技术,将EXCEL数据导入到ACCESS或其他类型的数据库当中再进行相关功能分析及处理。前一个方法明显不适合大量重复性工作,后一种方法要不断的进行EXCEL数据的导出也不适合大量重复性劳动。下面介绍另外一种方法,就是直接将EXCEL文件作为数据库,并对其进行结构化查询操作,虽然不是很难,但需要一定的用户界面支持。

2 查询分析与实现

EXCEL文件的结构是工作簿(Workbook),工作表(Worksheet),行(Row),列(Column),单元格(Cell),一个EXCEL文件可以看作是一个工作簿,一个工作部里面可以多个工作表,一个工作表又有多行和多列,行与列交叉有相应的单元格。这种结构很容易与数据建立对应关系,一个数据库有多个数据表组成,一个数据表又有相应的属性列和元组(行)组成,行与列交叉部分的单元数据为数据项。也就是说EXCEL文件完全可以看作是一个数据库,只不过没有支持如ACCESS里面的查询或存储过程等大小型数据库还应该有的元件。值得推荐的是Microsoft的数据库引擎的连接语法就包括EXCEL文件,连接后,可以如操作ACCESS数据库引擎一样,进行数据的更新和查询。以下简介连接EXCEL文件进行数据查询方面的操作。

2.1 查询语法与功能需求分析

2.1.1 查询语法分析

对数据库的数据进行查询操作,最灵活和经典的语法便是使用结构化查询语言(SQL)语句。虽然定义为查询语言,SQL语言还包含数据定义语言(DDL),数据操作语句(DML)和数据控制语句(DCL),数据查询语句涵盖在数据操作语言里面。SQL的魅力与核心所在就是其灵活而复杂的SELECT语句,下面是SQL的SELECT语句的一般性语法结构,更复杂的可以参考其他资料[1,2,3]。

SELECT[前导谓词]*|字段列表|字段的计算列表FROM数据表列表[WHERE条件组合][GROUP BY字段列表][ORDER BY字段列表]

其中“[”与“]”表示可选项,“|”表示任选项,WHERE、GROUP BY、ORDER BY分别是查询的条件语句、分组语句和排序语句。通过他们可以实现对数据的条件、分组和排序性的组合式查询。前导谓词有ALL、DISTINCT、DISTINCTROW和TOP n等几个,分别表示查询返回记录数的控制。数据计算方面包括合计计算及算数混合运算,一方面可以对于某个字段进行求和(或者汇总SUM)、平均(AVG)、最大或最小(MAX|MIN)、计数(COUNT)、最前最后记录(FIRST|LAST)、方差(Var|VarP)、标准偏差(StDev|StDevP);另一方面可以进行连个字段之间的算数混合运算,例如Table1.field1+Table2.field1 as fieldsum。

2.1.2 查询功能需求分析

一个数据库中包含一个或一个以上的数据表,则实际上的查询需求可能不仅局限于单一数据表的查询,很多需求是多表数据的查询,甚至是跨库复合查询。从单表角度,查询的功能需求有:(1)能够查询相应的自由字段,并能够按照要求排序;(2)能够实现相应的字段数据的分组合计计算(包括汇总、平均等),并按照指定的信息进行排序;(3)数据表中两个字段的算数混合运算;(4)在前述几个需求功能基础上,再附带一定的条件组合。多表查询的功能也有前面4个功能需求,只是字段的数据源于同一数据库的不同数据表或不同数据库的多个数据表。

2.2 查询实现

2.2.1 查询界面的设计

前面分析了SQL查询语句的SELECT句法和相应的查询功能需求,然而对于一般不需要编程或系统开发的使用者则需要一个可视化的交互式界面。通过这个界面使用者只需要进行数据文件、数据表、字段等信息选取就能够实现查询结果的返回,同时能够将查询结果导出到使用者预定的文件中。图1是针对这样的需求而开发的EXCEL数据查询交互操作界面,语言开发平台为Visual Basic 6.0[4],使用的是ADO技术实现查询功能。这是(1)一个单表或多表结合的查询界面,最多支持三个数据表的支持;(2)数据文件、数据表及其字段都是支持动态选取的,即先选定预定的数据文件,系统自动关联给出其所包含的数据表,当选择数据表后系统会自动显示其所有字段;(3)提供自由字段、运算字段、分组字段、排序字段及条件的组合的输入框,通过“→”按钮可以从左边的字段列表框直接选取;(4)界面的右下的数据表格用于查询结果的显示,其左上角的选择框支持数据表格尺寸的放大和回缩;(5)界面还支持查询结果的导出到相应的EXCEL文件,相应方法在后面进行介绍;(6)为了进一步为使用者考虑,界面支持模板以利于操作者进行重复或近似性查询操作。

2.2.2 查询语句的组织

在前面查询语法分析里,可以知道SQL的SELECT语句的组成结构,但是如何通过具体的代码实现的呢?使用Visual Basic语言和ADO技术,第一步是需要创建并打开一个数据库的连接(Connection);第二步是使用前面的连接对象创建并打开一个记录集(RecordSet)。其中SQL的SELECT语句就涵盖在记录集打开的参数里。具体代码如下:

上面两段代码可以分别放在两个函数里,假设左边的函数名为conn_open返回类型为ADODB.Connection,则在函数后面在写一句Set conn_open=conn_ff,就可以将函数内的conn_ff变量通过函数名传递出来,当然s_file为此函数的字符串性参数;同理右边记录集可以定义这样的函数Function rset_open(sqlss as string,conn_oo as adodb.connection,r_w as integer)as adodb.recordset,当然函数最后一句是set rset_open=rs_oo,这样就可以将函数内部记录集变量传递到外面来。这样做的目的是增强代码的灵活性和可维护性。

通过上面两个函数,可以很容易获取一个二维结构的数据记录集,但上面右边代码里面有一个参数很关键,就是sqlss这个变量。下面写一个双表关联的复合查询的SELECT语句以说明问题,其他简单情况读者可以参考相关资料类推。

Sql_str=“SELECT[03月$].销售额-[04月$].销售额AS销售环比增长额0304 FROM[03月$],[04月$]WHERE[03月$].销售产品代码=[04月$].销售产品代码”

上面SELECT语句句法与ACCESS数据库连接使用的SELECT句法没有什么大的区别,就是需要在数据表应用的地方添加了“[”“$”“]”等符号。将上述sqlss变量带入右边代码函数的参数中就可以实现相应产品销售额的03月与04月的数据比较了。

明白一个功能的实现原理不算难,很多程序员面对同样的需求和同样的实现原理,但具体实现的路径差别很大。程序代码的可维护性及灵活性就需要程序员在编写程序代码要进行代码结构的构思与设计。所以本查询系统的灵活性就是体现在如何将使用在交互式界面的输入的查询参数变量转变为类似上面产品销售额查询的SELECT语句。目前笔者已经实现上述所有功能,并能够应用图1所以界面进行EXCEL数据的多种查询,甚至数据表来源于不同的EXCEL文件。

2.2.3 查询数据的输出

关于实现外部数据向EXCEL文件“自动化”输入方面,有这样的几种方法[5,6,7]:(1)逐单元格传输数据;(2)将数组中的数据传输到单元格区域;(3)使用CopyFromRecordset方法向单元格区域传输ADO记录集中的数据;(4)使用VBA技术在Excel工作表上创建一个QueryTable,它包含对ODBC或OLEDB数据源进行查询的结果;(5)将数据传输到剪贴板,然后将剪贴板内容粘贴到Excel工作表中;(6)将数据传输到制表符分隔或逗号分隔的文本文件,然后Excel可以将该文本文件分析为工作表上的单元格;(7)使用A-DO将数据传输到工作表;(8)使用动态数据交换(DDE)将数据传输到Excel。

其中比较方便地将记录集数据导入到EXCEL的方法是第(3)、(4)和(7)方法,然而使用QueryTable方法具有优于方法(3)和(7)的优点[7],一则查询可以保存在QueryTable中,以便稍后能够刷新以获取更新的记录集;二则当向工作表中添加新的QueryTable时,可以指定将工作表上的单元格中已经存在的数据移位,以便容纳新数据。使用QueryTable语法例子是:WorkSheet1.QueryTables.Add(Connection:=rstRecordset,Destination:=Range("A1")),Add方法的两个相应参数分别是指定的记录集和EXCEL数据导入的目标起始单元格,更详细的信息可以参考Microsoft的关于EXCEL的VBA参考帮助[5]。

3 结论

许多使用EXCEL文件进行数据存储和其他功能的操作者可能会遇到这样的高级而复杂的需求,而使用EXCEL一般交互式功能又很难解决的时候,就可以利用类似这样的软件进行复杂的交互式查询。网上也有类似的EXCEL文件数据处理软件,但多是功能比较固定的,比如EXCEL数据表的批量合并等,而上述的查询软件具有一定的通用性,当然也可以进一步扩展到更多表源或更复杂功能的组合查询,且系统中还设计了查询模板的管理,这个附带功能虽然看似简单,然而在实现上也有一定难度。

参考文献

[1]SQL语法参考手册[EB/OL].[2009-01-05].http://tech.ddvip.com/2006-04/11442661803903.html.

[2]循序渐进讲解SQL查询语句高级应用技巧[EB/OL].[2009-01-05].http://tech.ddvip.com/2008-05/121006809344252.html.

[3]罗朝胜.Visual Basic6.0程序设计实用教程[M].第2版.北京:清华大学出版社,2008.

[4]HOW TO:使用Visual C#.NET向Excel工作簿传输数据任务的内容[EB/OL].[2009-01-05].http://www.cnblogs.com/Wiseman/archive/2004/11/26/69113.html.