Excel高级应用课程课件

主讲： 资深EXCEL实战专家韩小良 1980元/人/两天
金融财务建模____应用实例
➢ 投资组合收益率和方差计算及其VBA实现 ➢ 投资组合有效边界模型及其VBA实现 ➢ 投资组合风险优化决策模型及其VBA实现 ➢ 投资组合风险价值模型及其VBA实现 ➢ 资本资产定价模型的建立及其VBA实现 ➢ Black-Scholes期权定价模型及其VBA实现 ➢ 二叉树（二项式）期权定价模型及其VBA实现 ➢ 期货套期保值计算的VBA实现 ➢ 投资项目决策与理财模型的建立及其VBA实现
➢ 动态了解公司员工的流入和流出变化情况； ➢ 分析员工今年薪酬变化，便于为明年的薪酬控制提供依据 ➢ 每天、每月都要计算员工的考勤，计算员工的工资和奖金
，制作工资条，把工资准确无误地发放到每个员工账户， 并及时通知每个员工； ➢ 每年都要把全公司上百人甚至上千人的工资进行汇总，制 作五险一金汇总表，制作个税代扣代缴表 ➢ 员工的生日、合同、退休日期快要到了，如何才能提前提 醒，以免到时候手忙脚乱；新员工的试用期快要到了，如 何及时提醒聘用者签订正式劳动合同 ➢ 如何评价每个业务人员的销售业绩，并根据业绩计算薪酬
➢ 使格式化文档更简便快捷 ➢ 提供专门设计的可利用的且具有独创性的内容 ➢ 容易看到工作成果，就像已经打印出来的一样 ➢ 更容易维护电子表格和进行格式更新 ➢ 满足一些长期从事与打印相关顾客的需求 ➢ 提供一些美观的文档示例 ➢ 更容易移动内容(例如，图表)到其它Office应用程序
(例如，PowerPoint) ➢ 在Word，PowerPoint和Excel中的所有操作方法都是
参考书:《金融财务建模与计算——基于VBA与MATLAB实现》
作者： 朱顺泉 编著、出 版 社： 电子工业出版

2.报表布局、数据项目顺序不合理；钩稽关系不明确甚至错误；
3.数据缺失、漏填漏报；忘记注明金额单位和制表人等项目；
4.部门名称或员工姓名出现错字、别字；
5.部门名称或员工姓名等项目用字每次不能保持一致；
6.为了美化格式或由于输入手误，字串中间、前后使用空格；
7.为求得格式好看，大量使用“合并居中”功能；
一、图表制作技巧
2021/4/7
1. 体验Excel电子表格 2. 表格创建的应用技巧 3. 图表的类型与分析目标 4. 用表格数据创建分析图表 5. 图表的修饰与编辑技巧 6. 关于图表应用的综合练习
第1页/共24页
案例：创建工资统计表
• 输入数据内容
• 填充法输入——月份 • 复制粘贴导入数据 • 用语音进行数字校对
第21页/共24页
21
认识宏
• 什么是宏 • 宏就是所有指令的集合
• 宏的使用时机 • 重复性高的操作 • 减少错误发生
关于宏的演示
2021/4/7
第22页/共24页
22
宏的录制与执行
• 宏的录制 • 通过“工具”菜单“宏”的“录制新宏”进行录制
• 宏的执行 • 通过“工具”菜单“宏”来执行 • 设置快捷键执行 • 宏的安全性设置为“中”
7
查询和引用函数
当需要在数据清单或表格中查找特定数值，或者需要查找某一单元 格的引用时，可以使用查询和引用工作表函数。
8
数学和三角函数
通过数学和三角函数，可以处理简单的计算，例如对数字取整、计 算单元格区域中的数值总和或复杂计算。
9 统计函数
统计工作表函数用于对数据区域进行统计分析。
10 文本函数
通过文本函数，可以在公式中处理文字串。

在公式中使用单元格引用




相对引用 绝对引用 混合引用 引用的切换F4 九九乘法表

复制公式与条件格式的关系
第一部分 Excel函数

Excel名称技巧

使用常量名称
：(冒号） 设定一个区域 B1:B6 ，（逗号）设定两个区域的联合 B1,B6 （空格）设定两个区域的交叉 B1:B6 A3:D3

区域引用运算符

第一部分 日期与时间函数




计算日期 计算天数 计算日期为第几个星期 计算表示日期的数值 计算表示时间的数值 从日期中提取出年、月、日和星期 从时间中提取出时、分和秒
第一部分 日期与时间函数

取出当前系统时间/日期信息

NOW、TODAY
HOUR、DAY、MONTH、YEAR
prod = prod * x(i, j) Next j


Next i
RangeTest = WorksheetFunction.Transpose(Array(prod, r, c)) End Function
IF-Then语句

Function SimpleIf2(Parameter)

End Sub
在VBA如何使用Excel中的函数



Function myln(x) myln= Ln(x) End Function 出现了错误？
使用数组

Sub UseArray() Dim X(2) As Double


X(0) = 0
X(1) = 1 X(2) = 2

据”“筛选”“自动筛选”平均分：>=70与 <=90 例：姓李的人？ 方法：选定数据区内任意单元格单击“数 据”“筛选”“自动筛选”姓名：李*
20
4.3分类汇总
例：各班人数表？ 方法：选定“班级”列任意单元格先按升序排
序单击“数据”“分类汇总”分类字段：班 级，汇总方式：计数，汇总项：班级2级显示。 例：各班计算机、外语的最高分？ 方法：先按班级排序…… “分类汇总”分 类字段：班级，汇总方式：最大值，汇总项： 计算机、外语2级显示。
掌握常用编辑功能（1_提高录入效率）
–省力输入：自动填充 –定位空白单元格： –批量录入：ctrl+ 回车 –选择性粘贴：粘贴数值
掌握快捷键：F4 双手操作：鼠标、键盘齐上阵
8
2.3打印技巧
设置打印内容：选定区域；整个 工作表；工作薄；设置打印区域
设置打印标题（例） 照相机:将多张工作表内容照到
排序（按学历）分类汇总（分类字段： 学历；汇总方式：计数；汇总项：学历） 2级显示
• 直接计算法： • 图表计算法：选取各学历数字部分单击
“图表向导”按钮第2步“源数据”“系 列”“分类标志： 选取各学历名 称“图表选项”“数据标志”“显示百分 比”……
28
6.5综合练习透视表
21
4.4 数据透视表
例：同时计算出各班计算机、外语的最高分及最 低分？
方法：选定数据源数据区内任意单元格“数 据”“数据透视表和图表报告”下一步下一 步选版式列字段：班级；数据：最大（小） 值项：计算机；最大（小）值项：外语……
22
图表
5.1生成图表 方法：选定数据区单击“图表
语法：VLOOKUP（查找值，数 据表，列序号，［匹配条件］）

• 公式和函数入门 • 排序 • 筛选 • 分类汇总 • 实用函数讲解
第19页/共90页
公式和函数
公式的使用
公式是用运算符将数据、单元格地址、函数等连接在一起 的式子，以等号“=”开头
=5*10-20
常数运算
=A6+B1
对单元格A6和B1中的值相加
=SQRT(10+A2) 开方根
使用Excel函数，SQRT表示求
求最大值
MIN
求最小值
COUNT 求数值数据的个数
COUNTIF 条件求数值数据的个数
第26页/共90页
函数应用举例
例2 计算基本工资总数，并将结果存放在单元格C7 (1) 单击单元格C7 (2) 键入公式“=SUM(C2:C6)”，并按回车键
例3 计算补贴总数和扣除总数，并将结果分别存放在单元格D7和 E7中
●成绩≥80的班11的学生 A. 成绩>=80 OR 班号=11
B. 成绩>=80 AND 班号=11
●班11及班21的学生 A. 班=11 OR 班号=21 B. 班=11 AND 班号=21
●班11所有不及格的学生
第31页/共90页
自动筛选
自动筛选采用简单条件来快速筛选记录，将不满足条件的记 录暂时隐藏起来，而将满足条件的记录显示在工作表上
= B2＜42 = B2＞=32 = B2＜12 = A3+80
比较结果为TRUE (设B2的值是12) 比 较 结 果 为 FA L S E 比较结果为FALSE 假设A3为日期型数据，则A3+80表
示日期A3加上80天
第21页/共90页
比较条件式
用于描述一个条件。例如
>5

分类标志易读 时间趋向弱
Survey Results EXCEL高级技能培训
25%
30%
Store locations are convenient
Store hours are convenient
Stores are w ell-maintained
I like your w eb site
Employees are friendly
2002 1992 1994 1996 1998 2000 2002 0
EXCEL高级技能培训
Number of Employees: 1992 - 2002
22
20
40
60
80
100
120
图表替数据说话-根据数据设计图表
First Half Regional Sales by Month: 2001-2002
内容
高效数据处理技巧 函数运用技巧与常见问题排错 汇总分析及常见问题排错 图表制作与设计技巧
EXCEL高级技能培训
1
EXCEL数据处理与分析能力提升
2
EXCEL高级技能培训
高效数据处理技巧
快速输入文本 批量填充空白单元格去除合并 排序，实现隔行插行，做工资条 快速去除数据中的文本 快速去除数据中大量超链接
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
0
Web Site Visitors 5,000 10,000 15,000 20,000 25,000 30,000
30,000
Web Site Visitors
25,000
20,000
15,000
10,000

（或者所有项目）。 数据源：用于创建数据透视表的数据。
10
;.
创建数据透视表
定义数据位置 定义数据 实现数据透视表 使用对话框规划数据透视表 使用透视表字段列表工具规划数据透视表
11
;.
数据透视表项目组合
实例： 12
日期 2010-1-1 2010-1-2 2010-1-3 2010-1-4 。。。 2010-1-31 2010-2-1 。。。 2010-2-28 2010-3-1
22
;.
基于多重合并数据区域创建数据透视表
财务科：
科目名称
1月
2月
3月
4月
5月
6月
办公费
2036 143 7023 5499 3190 3539
差旅费
8486 1391 7661 7865 3695 3588
福利
4829 7832 3696 4079 5656 7292
工资
3581 2812 2452 5303 8886 646
总计
1,230,576 141,986 46,442,449 47,815,011
8
;. 例题1
数据透视表实例
新/老客户 Existing
求和项:开户总 数
账户类型
开户部门
CD checking
Central
936,484
53
IRA 127,699
Savings 120
总计 1,064,356
North County
销售额
1,231
4,561
121
454
。。。
84
262
。。。
12,865
165
;.

（5）文本运算符“&” 可以将一个或多个文本连接为一个组合文本例如：& （连字符）将两个文本值连接或串起来产生一个边续 的文本值例如“SEA”&“GATE”产生“SEAGATE”。
LOGO
二、公式与函数
Excel公式与函数
第 13 页
（6）引用运算符 可以将单元格区域合并计算。表12-2引用运算符ห้องสมุดไป่ตู้含义和示例。
4、RIGHT函数，反回文本值最右边的字符。 表达式： 5、TEXT函数，设置数字格式并将其转换为文本。 表达式：
LOGO
二、公式与函数
Excel公式与函数
第 16 页
1、IF函数，根据条件进行判断，诺条件成立返回一个值， 不成立则返回另一个值。 表达式： 2、And函数，判断条件是否成立，如果是返回“TRUE”， 否则返回“FALSE”。 表达式： 3、OR函数，如果任意条件为“TRUE”，则返回“TRUE”。 表达式：
高级用户
中级用户
初级用户
一、基础
Excel基础知识
第5 页
Excel2010的基本界面
功能组 快速工具栏 功能选项卡
列标签
行标签
显示比例及视图方式
工作表标签
一、基础
工作簿的操作
Excel基础知识
第6 页
工作簿的常规操作：新 建、打开、保存、关闭 等。 工作簿的密保功能。
一、基础
工作表及单元格操作
引用运算符 含义 示例
：冒号
区域运算符 ，对两个引 用之间，包括两个引用 F2:H7 在内的所有单元格进行 引用
［
，逗号
联合运算符 ，将多个引 SUM （ A5:C15,E5:F15 ） 用合并为一个引用 LOGO

（6）单击数据透视表中字段名右侧的筛选箭头，可对数据进行筛选。
数据透视表
更改数据透视表的汇总方式
默认情况下，“数值”区域下的数据透 视表字段显示为 SUM。如果 Excel 将数据 解释为文本，则会显示为 COUNT。这就是 为何需要确保不混合使用值字段的数据类 型。如果要更改计算类型，可以先单击字 段名称右侧的箭头，然后选择“值字段设 置”选项，打开“值字段设置”对话框来 更改计算类型。
数据透视表
查看明细数据
如果想查看数据透视表中某个数据所对应的详细数据，有两种 方法：
• 单击某个要查看的数据，单击鼠标右键，在快捷菜单中单击“显示详 细信息”；
• 用鼠标双击要查看的数据。
数据透视表
刷新数据透视表
在创建数据透视表后，如果对数据源中的数据进行了更改，需 要单击“数据透视表工具|选项”下“数据”组中的“刷新”按钮， 所做的更改才能反映到数据透视表中。
Office高级应用
Excel 高级应用 数据透视图
数据透视图
数据透视图以图形形式呈现数据透视表中的汇总数据，其作用与普通图表相 同，可以更为形象地对数据进行比较。
为数据透视图提供源数据的是相关联的数据透视表。在相关联的数据透视表 中对字段布局和数据所做的更改，会立即放映到数据透视图中。数据透视图及其 相关联的数据透视表必须始终位于同一个工作簿中。与普通图表的区别在于，当 创建数据透视图时，数据透视图的图表区中将显示字段筛选器，以便对基本数据 进行排序和筛选。
Excel 高级应用 宏功能的简单使用
宏功能的简单使用
宏是可运行任意次数的一个操作或一组操作，可用来自动执行重 复任务。如果总是需要在Excel中重复执行某个任务，则可以录制一 个宏来自动执行这些任务。在创建一个宏后，也可以编辑宏。