欢迎关注天善智能微信公众号，我们是专注于商业智能BI，大数据，数据分析领域的垂直社区。

本文为小编学习天善学院李奇老师用数据说话-Excel BI商业智能分析零基础精讲课程第一章第五节笔记。

Excel商业智能分析中常用的到的五种方法：数据标准化、加权平均、转换变量类型、直方图、盒须图。

重要性：不同指标进行对比评价时，经常会遇到由于各指标间的性质，量纲，或者是数量级的不同而造成各指标间的水平相差很大时，直接用原始指标值进行数据分析，就会突出数值较高的指标在综合分析中的作用。为了保证结果的可靠性，需要对原始的指标数据进行标准化处理。

方法：主要介绍2种：

MIN-MAX标准化：新数据=（原数据-极小值）/（极大值-极小值）

注：原数据映射在0-1区间内，同意数量级，方便进行进一步比较、分析

Z-SCORE标准化：新数据=（原数据-均值）/标准差

注：围绕0上下波动，大于0说明高于平均水平，小于0说明低于平均水平。

利用交叉表求权重方法介绍：

1. 纵向和横向对比，横向重要则为1，纵向重要为0

2. 横向加总

3. 每个阶段合计值/合计总值*100%

实操部分：

变量类型：

1. 名义型变量： 值与值之间没有等级顺序之分，仅代表不同类的事物。

例： 性别、民族、职业

2. 有序型变量： 值与值之间有等级顺序之分，不仅能够代表事物的分类，还能代表事物按某种特性的排序。

例： 销售阶段、优良中差

3. 连续型变量：不仅能将变量区分类别和等级，而且可以确定变量之间的数量差别和间隔距离。

例： 营业额、身高、体重

实操：

频数与频率

频数是落在各类别中的数据个数。各类别频数与总频数之比称频率。频数和频率分别从绝对数和相对数上，反映出数据在各变量值上的分布状况。（学习直方图前了解这两个概念）

组距=（最大值-最小值）/组数

1. 选择数据

2. 设置接受区域

3. 调整频率分布

直方图与柱形图区别：柱形图看的是高度，对比数值，直方图看的是面积，组距内频率的分布情况，展现整体分布趋势。

实操：

调出数据分析库之后打开直方图

设置好参数

注意：此时显示的是频数而不是频率，需要调整一下。

设置好之后调整直方图，邮件调出数据系列格式，“分类间距”调为“0”。加上相应轮廓即可。

盒须图用来体现数据分散情况，版本建议excel2016版。

四分位数：将数据由小到大排列并分成四等份，处于三个分割点位置的数值就是四分位数

上边缘 = Q3+1.5*（Q3-Q1）

下边缘 = Q1-1.5*（Q3-Q1）

通过盒须图，可以清晰发现一组数据的分散情况。

实战：

选中所有数据之后，找出所有图标中的“箱形图”

本章笔记就到这里，后续继续更新哈，欢迎大家关注。感兴趣的同学也可以留言一起交流学习，记得点赞哈。

更多精彩内容，请登陆天善学院：hellobi。

Excel 使用技巧集锦——163种技巧

目录

一、基本方法7

1.快速选中全部工作表7

2.快速启动Excel7

3.快速删除选定区域数据7

4.给单元格重新命名7

5.在Excel中选择整个单元格范围7

6.快速移动/复制单元格8

7.快速修改单元格式次序8

8.彻底清除单元格内容8

9.选择单元格8

10.为工作表命名9

11.一次性打开多个工作簿9

12.快速切换工作簿9

13.选定超级链接文本（微软Office技巧大赛获奖作品）10

14.快速查找10

15.修改默认文件保存路径10

16.指定打开的文件夹10

17.在多个Excel工作簿间快速切换10

18.快速获取帮助11

19.创建帮助文件的快捷方式11

20.双击单元格某边移动选定单元格11

21.双击单元格某边选取单元格区域11

22.快速选定不连续单元格11

23.根据条件选择单元格11

24.复制或移动单元格12

25.完全删除Excel中的单元格12

26.快速删除空行12

27.回车键的粘贴功能12

28.快速关闭多个文件12

29.选定多个工作表12

30.对多个工作表快速编辑13

31.移动和复制工作表13

32.工作表的删除13

33.快速选择单元格13

34.快速选定Excel区域（微软Office技巧大赛获奖作品）13

35.备份工件簿14

36.自动打开工作簿14

37.快速浏览长工作簿14

38.快速删除工作表中的空行14

39.绘制斜线表头14

40.绘制斜线单元格15

41.每次选定同一单元格15

42.快速查找工作簿15

43.禁止复制隐藏行或列中的数据15

44.制作个性单元格16

二、数据输入和编辑技巧16

1.在一个单元格内输入多个值16

2.增加工作簿的页数16

3.奇特的F4键16

4.将格式化文本导入Excel16

5.快速换行17

6.巧变文本为数字17

7.在单元格中输入0值17

8.将数字设为文本格式18

9.快速进行单元格之间的切换（微软Office技巧大赛获奖作品）18

10.在同一单元格内连续输入多个测试值18

11.输入数字、文字、日期或时间18

12.快速输入欧元符号19

13.将单元格区域从公式转换成数值19

14.快速输入有序文本19

15.输入有规律数字19

16.巧妙输入常用数据19

17.快速输入特殊符号20

18.快速输入相同文本20

19.快速给数字加上单位20

20.巧妙输入位数较多的数字21

21.将WPS/Word表格转换为Excel工作表21

22.取消单元格链接21

23.快速输入拼音21

24.插入“√”21

25.按小数点对齐22

26.对不同类型的单元格定义不同的输入法22

27.在Excel中快速插入Word表格22

28.设置单元格字体22

29.在一个单元格中显示多行文字23

30.将网页上的数据引入到Excel表格23

31.取消超级链接23

32.编辑单元格内容23

33.设置单元格边框23

34.设置单元格文本对齐方式24

35.输入公式24

36.输入人名时使用“分散对齐”（微软Office技巧大赛获奖作品）24

37.隐藏单元格中的所有值（微软Office技巧大赛获奖作品）24

38.恢复隐藏列24

39.快速隐藏/显示选中单元格所在行和列（微软Office技巧大赛获奖作品）24

40.彻底隐藏单元格25

41.用下拉列表快速输入数据25

42.快速输入自定义短语25

43.设置单元格背景色25

44.快速在多个单元格中输入相同公式26

45.同时在多个单元格中输入相同内容26

46.快速输入日期和时间26

47.将复制的单元格安全地插入到现有单元格之间26

48.在Excel中不丢掉列标题的显示26

49.查看与日期等效的序列数的值27

50.快速复制单元格内容27

51.使用自定义序列排序（微软Office技巧大赛获奖作品）27

52.快速格式化Excel单元格27

53.固定显示某列27

54.在Excel中快速编辑单元格27

55.使用自动填充快速复制公式和格式28

56.为单元格添加批注28

57.数据自动输入28

58.在Excel中快速计算一个人的年龄28

59.快速修改单元格次序29

60.将网页上的数据引入到Excel表格中29

三、图形和图表编辑技巧29

1.在网上发布Excel生成的图形29

2.创建图表连接符30

3.将Excel单元格转换成图片形式插入到Word中30

4.将Word内容以图片形式插入到Excel表格中30

5.将Word中的内容作为图片链接插入Excel表格中30

6.在独立的窗口中处理内嵌式图表31

7.在图表中显示隐藏数据31

8.在图表中增加文本框31

9.建立文本与图表文本框的链接31

10.给图表增加新数据系列31

11.快速修改图表元素的格式32

12.创建复合图表32

13.对度量不同的数据系列使用不同坐标轴32

14.将自己满意的图表设置为自定义图表类型32

15.复制自定义图表类型33

16.旋转三维图表33

17.拖动图表数据点改变工作表中的数值33

18.把图片合并进你的图表33

19.用图形美化工作表34

20.让文本框与工作表网格线合二为一34

21.快速创建默认图表34

22.快速创建内嵌式图表35

23.改变默认图表类型35

24.快速转换内嵌式图表与新工作表图表35

25.利用图表工具栏快速设置图表35

26.快速选取图表元素36

27.通过一次按键创建一个Excel图表36

28.绘制平直直线36

四、函数和公式编辑技巧36

1.巧用IF函数清除Excel工作表中的036

2.批量求和37

3.对相邻单元格的数据求和37

4.对不相邻单元格的数据求和37

5.利用公式来设置加权平均38

6.自动求和38

7.用记事本编辑公式38

8.防止编辑栏显示公式38

9.解决SUM函数参数中的数量限制39

10.在绝对与相对单元引用之间切换39

11.快速查看所有工作表公式39

12.实现条件显示39

五、数据分析和管理技巧40

1.管理加载宏40

2.在工作表之间使用超级连接40

3.快速链接网上的数据40

4.跨表操作数据40

5.查看Excel中相距较远的两列数据41

6.如何消除缩位后的计算误差（微软Office技巧大赛获奖作品）41

7.利用选择性粘贴命令完成一些特殊的计算41

8.Web查询41

9.在Excel中进行快速计算42

10.自动筛选前10个42

11.同时进行多个单元格的运算（微软Office技巧大赛获奖作品）42

12.让Excel出现错误数据提示43

13.用“超级连接”快速跳转到其它文件43

六、设置技巧43

1.定制菜单命令43

2.设置菜单分隔线43

3.备份自定义工具栏44

4.共享自定义工具栏44

5.使用单文档界面快速切换工作簿44

6.自定义工具栏按钮44

基本方法

1.快速选中全部工作表

右键单击工作窗口下面的工作表标签，在弹出的菜单中选择“选定全部工作表”命令即可（）。

2.快速启动Excel

如果想在启动系统自动运行Excel，可以这样操作: 1.双击“我的电脑”图标，进入Windows目录，依次打开“Start Menu\Programs\启动”文件夹; 2.打开Excel所在的文件夹，用鼠标将Excel图标拖到“启动”文件夹，这时Excel的快捷方式就被复制到“启动”文件夹中，下次启动Windows就可快速启动Excel了。 如果Windows系统已启动，你可用以下方法快速启动Excel: 方法一:单击“开始→文档”命令里的任一Excel工作簿即可。 方法二:用鼠标从“我的电脑”中将Excel应用程序拖到桌面上，然后从快捷菜单中选择“在当前位置创建快捷方式”，以后启动时只需双击快捷方式即可。

3.快速删除选定区域数据

如果用鼠标右键向上或向左(反向)拖动选定单元格区域的填充柄时，没有将其拖出选定区域即释放了鼠标右键，则将删除选定区域中的部分或全部数据(即拖动过程中变成灰色模糊的单元格区域，在释放了鼠标右键后其内容将被删除)。

4.给单元格重新命名

Excel给每个单元格都有一个默认的名字，其命名规则是列标加横标，例如D3表示第四列、第三行的单元格。如果要将某单元格重新命名，可以采用下面两种方法: 1.只要用鼠标单击某单元格，在表的左上角就会看到它当前的名字，再用鼠标选中名字，就可以输入一个新的名字了。 2.选中要命名的单元格，单击“插入→名称→定义”命令，显示“定义名称”对话框，在“在当前工作簿中的名称”框里输入名字，单击“确定”按钮即可（）。 注意:在给单元格命名时需注意名称的第一个字符必须是字母或汉字，它最多可包含255个字符，可以包含大、小写字符，但是名称中不能有空格且不能与单元格引用相同。

5.在Excel中选择整个单元格范围

在Excel中，如果想要快速选择正在处理的整个单元格范围，按下“Ctrl+Shift+ *”。 注意:该命令将选择整个列和列标题，而不是该列表周围的空白单元格——你将得到所需的单元格。这一技巧不同于全选命令，全选命令将选择工作表中的全部单元格，包括那些你不打算使用的单元格。

6.快速移动/复制单元格

先选定单元格，然后移动鼠标指针到单元格边框上，按下鼠标左键并拖动到新位置，然后释放按键即可移动。若要复制单元格，则在释放鼠标之前按下Ctrl即可。

7.快速修改单元格式次序

在拖放选定的一个或多个单元格至新位置的同时，按住Shift键可以快速修改单元格内容的次序。方法为:选定单元格，按下Shift键，移动鼠标指针至单元格边缘，直至出现拖放指针箭头，然后进行拖放操作。上下拖拉时鼠标在单元格间边界处会变成一个水平“工”状标志，左右拖拉时会变成垂直“工”状标志，释放鼠标按钮完成操作后，单元格间的次序即发生了变化。

8.彻底清除单元格内容

先选定单元格，然后按Delete键，这时仅删除了单元格内容，它的格式和批注还保留着。要彻底清除单元格，可用以下方法:选定想要清除的单元格或单元格范围，单击“编辑→清除”命令，这时显示“清除”菜单（），选择“全部”命令即可，当然你也可以选择删除“格式”、“内容”或“批注”中的任一个。

9.选择单元格

选择一个单元格，将鼠标指向它单击鼠标左键即可; 选择一个单元格区域，可选中左上角的单元格，然后按住鼠标左键向右拖曳，直到需要的位置松开鼠标左键即可; 若要选择两个或多个不相邻的单元格区域，在选择一个单元格区域后，可按住Ctrl键，然后再选另一个区域即可; 若要选择整行或整列，只需单击行号或列标，这时该行或该列第一个单元格将成为活动的单元格; 若单击左上角行号与列标交叉处的按钮，即可选定整个工作表。

10.为工作表命名

为了便于记忆和查找，可以将Excel的sheet1、sheet2、sheet3工作命名为容易记忆的名字，有两种方法:1.选择要改名的工作表，单击“格式→工作表→重命名”命令，这时工作表的标签上名字将被反白显示，然后在标签上输入新的表名即可。 2.双击当前工作表下部的名称，如“Sheet1”，再输入新的名称。

11.一次性打开多个工作簿

利用下面的方法可以快速打开多个工作簿: 方法一 打开工作簿(*.xls)所在的文件夹，按住Shift键或Ctrl键，并用鼠标选择彼此相邻或不相邻的多个工作簿，将它们全部选中，然后按右键单击，选择“打开”命令，系统则启动Excel 2002，并将上述选中的工作簿全部打开。 方法二 将需要一次打开的多个工作簿文件复制到C:\Windows\Application Data\Microsoft\Excel\XLSTART文件夹中，以后启动Excel 2002时，上述工作簿也同时被全部打开。 方法三 启动Excel 2002，单击“工具→选项”命令，打开“选项”对话框，点击“常规”标签，在“启动时打开此项中的所有文件”后面的方框中输入一个文件夹的完整路径(如d:\Excel)，单击“确定”退出。然后将需要同时打开的工作簿复制到上述文件夹中，以后当启动Excel 2002时，上述文件夹中的所有文件(包括非Excel格式的文档)被全部打开。 方法四 在Excel 2002中，单击“文件→打开”命令，按住Shift键或Ctrl键，在弹出的对话框文件列表中选择彼此相邻或不相邻的多个工作簿，然后按“打开”按钮，就可以一次打开多个工作簿。 方法五 用上述方法，将需要同时打开的多个工作簿全部打开，再单击“文件→保存工作区”命令，打开“保存工作区”对话框，取名保存。以后只要用Excel 2002打开该工作区文件，则包含在该工作区中的所有工作簿即被同时打开。

12.快速切换工作簿

对于少量的工作簿切换，单击工作簿所在窗口即可。要对多个窗口下的多个工作簿进行切换，可以使用“窗口”菜单。“窗口”菜单的底部列出了已打开工作簿的名字，要直接切换到一个工作簿，可以从“窗口”菜单选择它的名字。“窗口”菜单最多能列出9个工作簿，若多于9个，“窗口”菜单则包含一个名为“其他窗口”的命令，选用该命令，则出现一个按字母顺序列出所有已打开的工作簿名字的对话框，只需单击其中需要的名字即可。

13.选定超级链接文本（微软Office技巧大赛获奖作品）

如果需要在Excel中选定超级链接文本而不跳转到目标处，可在指向该单元格时，单击并按住可选定单元格。

14.快速查找

在执行查找操作之前，可以将查找区域确定在某个单元格区域、整个工作表（可选定此工作表内的任意一个单元格）或者工作簿里的多个工作表范围内。在输入查找内容时，可以使用问号（？）和星号（*）作为通配符，以方便查找操作。问号（？）代表一个字符�...

年底了，总结汇报和来年规划必不可少，如何让大家能够直观看到明年一年的情况，比如预测一下明年的可能性？

Excel在2016版本中，提供了一个数据预测功能，通过它就可以做出一个预测图表，这样一目了然的方式自然可以让大家看得更加清楚。

以销售数据来说好了，先录入销售数据，这里以2015年到2016年的数据为例，圈选这些数据项，然后切换选项卡到“数据”，找到右侧的“预测工作表”点击。

弹出的图表中，蓝色代表实际销售数据，趋势预测、置信下限、置信上限都是橙色——也就是预测数据。不过这三个数据几乎都是平直的，显然不是正常的预测值，没有参考价值。

这是因为没有正确设置范围数据的缘故。点击图表中的“选项”折开选项内容，首先是预测结束日期，比如我们设置到2018年4月1日（根据自己实际情况设置），预测启始时间设置为2017年4月1日（这之后都是未发生的数据，要预测的起点）。之后是预测中周期的规律，切换“季节性”到“手动设置”，设置一个周期比如12，代表一个周期为12个月，在这个基础上计算预测数据，这里也可以跟据自己需求修改，比如4个月（1个季度）。

这里要讲解一下选项设置的几个重要参数

预测开始：从历史数据中的哪一期数据开始预测。

置信区间：设置预测值的上限和下限；该值越小，则上下限之间的范围越小。

使用以下方式填充缺失插值点：默认为“内插”，是根据数据的加权平均值计算出的插值；也可以将其置为“0”，即不进行缺值的插值计算。

使用以下方式聚合重复项：举例来说，如果2016年1月份有好几个数据，Excel在计算预测值时会将一个月内的多个值进行“聚合”，“聚合”的方式包括平均（默认）、计数、最大/最小/中值等，这就要看你表格数据的具体情况了。

设置完毕点击确认，可以看到图表左侧的数据处多了要预测但未实际发生的数据，三列数据分别是趋势预测（数字）、置信下限（数字）、置信上限（数字），这分别代表着预测值，预测下限、预测上限。而右侧的图表，可以看到波峰波谷的情况，当然，这种预测表格是根据往年的销售波动来检测的，Excel还没有到可以将不可预测的情况包含在内哦。最后，这个图表不仅可以用波峰形式来体现，也可以用柱状图的形态来表现，在图表中的右上角有切换选项，不妨看看哪个更适合自己。完成设置后，还可以将图表复制到PPT中作为路演素材，是不是方便呢？

大家在使用Office2016的时候有没有发现：以往我们需要大量代码、公式才能制作出的内容，好多都已经集成在程序内，只是微软并没有很大方的将它们展现出来。大家在遇到某些难题的时候，可以随时咨询“办公便签”，办公小达人们都是这么养成的哦！

                                                                                                                ——电脑爱好者

作者: 土地情报今年初我写了一篇“如何更好地理解IRR”的文章目的是指导我们如何按照我们的想法去调整IRR的大小。但我们实际工作中我们还经常会遇到IRR报错或者为负数的情况这个时候通过之前文章中的方法就不适用。

今天这篇文章就对测算表IRR报错或者负数的情况进行了一些探索以期对大家的实际工作有所帮助也希望能和大家一起交流学习。

备注为了方便大家的阅读我把之前的文章直接放到了前半部分如果之前已经看过的读者可以直接看后半部分。

1

作为一名房地产投资人员就永远绕不开内部收益率即IRR很多公司在前期土地研判时一般考量两个指标一个是净利润率另一个便是IRR比如我们知道的部分公司就要求净利润率及IRR必须达到双15%这个项目才可获取。

净利润率非常好理解但是这个IRR在房地产项目测算结果出来时很多朋友却不是特别理解到底IRR能够代表什么深层次的含义我们从这个指标当中能够得到什么信息IRR的高低对一个项目的影响在哪里

要知道IRR有什么用你首先得理解NPV是怎么算出来的。技术经济学的课本给我们讲到内部收益率IRR是基于净现值产生的一个概念它是指项目在整个计算期内当净现值等于零时的折现率。

为了便于理解我们举一个例子假设万科2017年某项目的现金流如下

我们常规做法应该怎么计算这个项目的IRR呢我们采用的是试错法的方式假设这个项目的折现率为x将每一期现金流折现到第1期合计净现值为0即

拿到这个公式可纳闷了这特么可怎么计算呢

按照传统的方式我们只有分别假定一个折现率去试错假设x=6%公式左侧为负假设x=4%公式左侧为正那么我们就可以推断IRR是介于4%-6%之间。这么算实在太麻烦。

在科技发达的今天好在excel给我们提供了强大的计算工具我们用IRR函数简单一拉结果就出来了最终算出这个项目的IRR=5%

IRR=5%说明了什么问题呢说明当这个项目现金流按5%折现时净现值刚好等于0如果小一点按4%折现净现值就大于0如果再大一点按6%折算净现值就小于0了这个项目就不可行了而且折现率此时越大那么亏得越多。

2

好了我们现在再回过头来重新理解IRR。

IRR是指在考虑资金时间价值的情况下项目投资在未来产生的投资收益现值刚好等于投资成本时的折现率。再进一步理解内部收益率是投资项目最低应达到的收益率。

IRR指标能够较为综合地反映项目管理、资本运营的效率和效益值涉及项目的资金计划是房地产企业对项目运营监控的核心指标。

计算NPV所用的折现率是一个项目的加权平均融资成本。每家企业都有自己的融资成本通常来说是个固定的值。

知乎上项梓例举了一个非常好理解的例子比如我开发一个住宅项目折现率为10%计算出项目的NPV>0说明这个项目是可以盈利的。现在我计算出这个项目的IRR是5%。当折现率等于5%的时候这个项目的NPV就变成0了。假如现在我的自有资金只够支持50%的前期投入另外的50%要去贷款。我找信托找基金给到我的贷款年利率是10%那我要不要贷款来做这个项目呢经过这一计算我们知道这个项目当我的融资成本超过5%IRR的时候NPV就会变成负的。说明不值得去贷款来做项目。

这个例子说明一个项目的IRR越高我越敢贷款来做项目。由此我们可以推断这个项目是非常难做的因为你去市场上是很难找到融资成本低于5%以下的钱的。

3

那紧接着我们要思考第二个问题了那就是如何来提高我们的IRR这样可以指导我们在做测算的时候应该如何去“随心所欲”地调整我们的现金流来匹配我们想要的IRR指标作为一名资深的投资人员我想你懂的。

我们还是采用上面的例子5%的IRR实在太低在很多发展商眼里这个项目肯定是通不过的因此作为一名资深的投资人员我们要想办法“做高”这个IRR。

根据上面我们列出的求IRR的公式我们首先应该想到的是在保证现金支出节奏不变的情况下尽可能“早”地实现销售收入。

因此我们尝试着把整个项目的现金流入都往前摞动1期即第5期的2000万摞到第4期实现第6期1500万摞到第5期实现第7期的500万摞到第6期实现因此这个项目的现金流就成了这个样子

此时用excel计算得出IRR=6%nice! 提高了1%。

同理在此基础上我们试着把现金流出往后延1期试试

此时用excel计算得出IRR=9%挺好又提高了3%。由于咱们这个例子中期数比较少现金流的数据也较少我们也只摞动了1期因此IRR的变动幅度还比较矜持。

若我们按照一个实际的房地产开发项目的现金流来计算收入往前多摞动几期支出往后也多摞动几期效果会更加明显。

在此基础上我们再增加销售收入试试比如再第7期增加1000万

此时计算得出IRR=18%已经比较理想了

同理在此基础上我们在第2期再减少支出1000万

此时计算得出IRR=44%相当不错了

由此我们基本发现了提升IRR四条最基本规律

①“快收”尽可能地把销售收入提前比如争取提前开盘、加快推售节奏。②“慢支”尽可能地把现金流出往后比如争取土地款分期支付、施工方工程款垫资、条件允许下延缓各类费用支付节奏。③“多收”尽可能多增加销售收入优化规划方案尽量多布局高溢价产品差异化定位把握自主定价权。④“少支”尽可能节约成本成本部同事测算时留给自己的“富裕量”部分投资同事应该是要多去争取的。

这篇文章发布后很多朋友留言其中有1个代表性的问题集中回复一下

问题现金流采用年度、季度、月度数据求解出来的IRR之间是什么关系

由于一般房地产项目的建设周期较短一个高层项目从拿地到交付的时间周期也就3年左右所以用年度现金流序列来计算IRR就不太合理所以房地产项目一般采用季度或者月度现金流序列来计算IRR。

他们之间就犹如名义利率与实际利率的关系。比如你去找银行贷款年利率是8%按月付息。由于是按月付息因此8%是年名义利率月利率=8%/12=0.66%年实际利率=1+0.66%^12?1=8.2%。可见名义利率与实际利率是有差异的且一般来讲实际利率高于名义利率。

同理我们假设P0为年初现金流初值当然P0同时亦为该年首季度和首月度初值P为终值。IRR年度、IRR季度、IRR月度分别为年度、季度、月度内部收益率。

则有P= P0*1+IRR年度= P0*1+IRR季度^4 =P0*1+IRR月度^12于是IRR年度=1+IRR季度^4-1=1+IRR月度^12-1

可见IRR年度≠4* IRR季度≠12* IRR月度这一点很容易出错大家一定要注意

4

除了提升IRR的问题外我们在实际测算过程中还经常遇到这样一种情况那就是测算出来IRR为负数这可如何是好搞得很多朋友一脸懵逼。

比如有这样一笔现金流

通过EXCEl求解得出IRR=-8%为一个负数。

此时大家一定非常迫切想搞清楚这几个问题

1、 IRR为负数是什么含义负数是什么情况产生的2、 测算表求解出IRR为负数后我们应该怎么调整现金流

为了得出更加一般性的规律我们首先得回归到IRR求解的数学模型中比如拿我们上面列举的求解公式来看

我们可以把这个方程抽象为

数学上我们把这种方程叫做一元n次多项式它是n次方程。n次方程应该有n个解其中包括负数根和重根很明显负根并无经济意义。只有正实数根才能是项目的IRR而方程的正实根可能不止一个。

而n次方程式的正实根的数目可用笛卡尔符号规则进行判断即正实根的个数不会超过项目现金流量序列多项式系数系列的正负号变化的次数p如遇有系数为零可视为无符号。

是不是越看越懵逼到底是什么意思呢能不能说人话......

直白一点就是说这个项目的现金流求解出来的IRR的个数取决于这笔现金流的“现金流”注意这里的现金流对应到测算表中应为净现金流由正转负或者由负转正的次数假设这个次数为p

如果P=0正负号变化0次则方程无根

如果P=1正负号变化1次则方程有唯一根。

如果P=2正负号变化2次则方程的正实根2个。

也就是说若项目的净现金流t=0,1,2,,n的正负号仅变化1次内部收益率方程肯定有唯一解。

而当净现金流序列的正负号有多次变化两次或两次以上内部收益率方程可能有多解。

5

为了便于大家更好的理解这里列举几种典型的现金流

(1)

这笔现金流序列正负号仅变化1次因此内部收益率方程只有唯一解。果不其然通过excel计算得出IRR=20%这是一笔比较常规的现金流与我们前文所列举的现金流特征一致。

2

这笔现金流序列正负号仅变化1次因此内部收益率方程只有唯一解。但这笔现金流计算得出IRR=-8%为负数。

问题来了为什么会出现负数呢

我们对比一下上面两笔现金流序列可以从中找到一个重大区别那就是“累计净现金流”的区别第一笔累计净现金流=500为正因此IRR为正而第二笔累计净现金流=-1000为负因此IRR为负。

通过这种观察得出结论是不是偶然呢我们进一步验证。

我们把第2笔现金流中第6期的金额调整为3000使得累计净现金流=0此时我们计算出IRR=0如图

进一步我把第6期净现金流金额再增加1000使得累计净现金流为正。果不其然此时计算得出IRR=6%成为正值了如图

为了得出一个更一般性的结论来指导工作我们再看几笔特殊的现金流:

3

这笔现金流序列正负号变化0次因此内部收益率方程应无解通过excel验算果然报错

4

这笔现金流序列正负号变化2次因此内部收益率方程至多有2个解即至多存在2个IRR。

5

这笔现金流序列正负号变化3次因此内部收益率方程至多有3个解即至多存在3个IRR。

看完以上五笔现金流案例我们大概可以总结出一个更一般性的结论来指导我们测算工作

1、若方案的第一笔净现金流0即正常的项目我们第一笔资金基本是支出的也就是我们投入的一部分现金流序列仅改变一次正负号且累计净现金流0此时项目必有唯一的“正”IRR解。

2、若方案的第一笔净现金流0现金流序列仅改变一次正负号且累计净现金流0此时项目必有唯一的“负”IRR解铺排的此现金流方案不可行需要调整。

3、若方案的现金流序列不改变正负号则方案的IRR不存在不能用IRR来评价此方案。

4、若方案的0现金流序列符号变化多次则方案的IRR个数不超过现金流序列符号变化的次数。在这种情况也可能会有唯一的IRR解也有可能无解。

显然我们在测算的时候是不希望IRR出现负值负值无意义也不希望IRR出现多解因为此时IRR无法确定而导致IRR失效。

查阅了很多文献在IRR存在多个解的情况下用excel的IRR函数就非常受局限因为无论由谁来做或无论你做多少次excel均只给出一个解这显然是不合适的。但尽管在这种情况下我们很多公司的测算表也是非常暴力地使用excel的IRR函数直接进行求解我个人认为此时求解出来的结果是有待商榷的。

因此我们在铺排现金流的时候期初为负后续期为正累计净现金流为正是我们需要关注的三个要点此时求解出来的IRR是唯一确定的正值。

严/肃/广/告/时/间

关注3Fang网微信公众号

即刻获取更多商房资讯

上次给大家分享了《2017年最全的excel函数大全6—日期和时间函数（下）》，这次分享给大家财务函数（上）。

ACCRINT 函数—返回定期支付利息的债券的应计利息

描述

返回定期付息证券的应计利息。

用法

ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])

重要:应使用 DATE 函数输入日期，或者将日期作为其他公式或函数的结果输入。 例如，使用函数 DATE(2008,5,23) 输入 2008 年 5 月 23 日。 如果日期以文本形式输入，则会出现问题。

ACCRINT 函数用法具有下列参数：

Issue 必需。 有价证券的发行日。

First_interest 必需。 有价证券的首次计息日。

Settlement 必需。 有价证券的结算日。 有价证券结算日是在发行日之后，有价证券卖给购买者的日期。

Rate 必需。 有价证券的年息票利率。

Par 必需。 证券的票面值。 如果省略此参数，则 ACCRINT 使用 ￥10,000。

Frequency 必需。 年付息次数。 如果按年支付，frequency = 1；按半年期支付，frequency = 2；按季支付，frequency = 4。

Basis 可选。 要使用的日计数基准类型。

calc_method可选。 一个逻辑值，指定当结算日期晚于首次计息日期时用于计算总应计利息的方法。 如果值为 TRUE (1)，则返回从发行日到结算日的总应计利息。 如果值为 FALSE (0)，则返回从首次计息日到结算日的应计利息。 如果不输入此参数，则默认为 TRUE。

其他

Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1，而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。

issue、first_interest、settlement、frequency 和 basis 将被截尾取整

如果 issue、first_interest 或 settlement 不是有效日期，则 ACCRINT 将返回 #VALUE! 错误值。

如果 rate ≤ 0 或 par ≤ 0，则 ACCRINT 将返回 #NUM! 错误值。

如果 frequency 不是数字 1、2 或 4，则 ACCRINT 将返回 #NUM! 错误值

如果 basis 0 或 basis 4，则 ACCRINT 将返回 #NUM! 错误值。

如果 issue ≥ settlement，则 ACCRINT 将返回 #NUM! 错误值

函数 ACCRINT 的计算公式如下：

其中：

Ai = 奇数期内第 i 个准票息期的应计天数。

NC = 奇数期内的准票息期期数。 如果该数含有小数位，则向上进位至最接近的整数。

NLi = 奇数期内准票息期的正常天数。

案例

ACCRINTM 函数—返回在到期日支付利息的债券的应计利息

描述

返回在到期日支付利息的有价证券的应计利息。

用法

ACCRINTM(issue, settlement, rate, par, [basis])

重要: 应使用 DATE 函数输入日期，或者将日期作为其他公式或函数的结果输入。 例如，使用函数 DATE(2008,5,23) 输入 2008 年 5 月 23 日。 如果日期以文本形式输入，则会出现问题。

ACCRINTM 函数用法具有下列参数：

Issue 必需。 有价证券的发行日。

Settlement 必需。 有价证券的到期日。

Rate 必需。 有价证券的年息票利率。

Par 必需。 证券的票面值。 如果省略此参数，则 ACCRINTM 使用 ￥10,000。

Basis 可选。 要使用的日计数基准类型。

其他

Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1，而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。

Issue、settlement 和 basis 将被截尾取整。

如果 issue 或 settlement 不是有效日期，则 ACCRINTM 将返回 #VALUE! 错误值。

如果 rate ≤ 0 或 par ≤ 0，则 ACCRINTM 将返回 #NUM! 错误值。

如果 basis 0 或 basis 4，则 ACCRINTM 将返回 #NUM! 错误值。

如果 issue ≥ settlement，则 ACCRINTM 将返回 #NUM! 错误值。

ACCRINTM 的计算公式如下：

ACCRINTM = par x rate x A/D

其中：

A = 按月计算的应计天数。 在计算到期付息的利息时指发行日与到期日之间的天数。

D = 年基准数。

案例

AMORDEGRC 函数—使用折旧系数返回每个记帐期的折旧值

描述

返回每个结算期间的折旧值。 该函数主要为法国会计系统提供。 如果某项资产是在该结算期的中期购入的，则按直线折旧法计算。 该函数与函数 AMORLINC 相似，不同之处在于该函数中用于计算的折旧系数取决于资产的寿命。

用法

AMORDEGRC(cost, date_purchased, first_period, salvage, period, rate, [basis])

重要:应使用 DATE 函数输入日期，或者将日期作为其他公式或函数的结果输入。 例如，使用函数 DATE(2008,5,23) 输入 2008 年 5 月 23 日。 如果日期以文本形式输入，则会出现问题。

AMORDEGRC 函数用法具有下列参数：

Cost 必需。 资产原值。

Date_purchased 必需。 购入资产的日期。

First_period 必需。 第一个期间结束时的日期。

Salvage 必需。 资产在使用寿命结束时的残值。

Period 必需。 期间。

Rate 必需。 折旧率。

Basis 可选。 要使用的年基准。

其他

Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1，而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。

此函数返回折旧值，截止到资产生命周期的最后一个期间，或直到累积折旧值大于资产原值减去残值后的成本价。

折旧系数为：

最后一个期间之前的那个期间的折旧率将增加到 50%，最后一个期间的折旧率将增加到 100%。

如果资产的生命周期在 0 到 1、1 到 2、2 到 3 或 4 到 5 之间，将返回 #NUM! 错误值。

案例

AMORLINC 函数—返回每个记帐期的折旧值

描述

返回每个结算期间的折旧值。 该函数主要为法国会计系统提供。 如果某项资产是在该结算期的中期购入的，则按直线折旧法计算。

用法

AMORLINC(cost, date_purchased, first_period, salvage, period, rate, [basis])

重要:应使用 DATE 函数输入日期，或者将日期作为其他公式或函数的结果输入。 例如，使用函数 DATE(2008,5,23) 输入 2008 年 5 月 23 日。 如果日期以文本形式输入，则会出现问题。

AMORLINC 函数用法具有下列参数：

Cost必需。 资产原值。

Date_purchased必需。 购入资产的日期。

First_period必需。 第一个期间结束时的日期。

Salvage必需。 资产在使用寿命结束时的残值。

Period必需。 期间。

Rate必需。 折旧率。

Basis可选。 要使用的年基准。

其他

Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1，而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。

案例

COUPDAYBS 函数—返回从票息期开始到结算日之间的天数

描述

COUPDAYBS 函数返回从付息期开始到结算日的天数。

用法

COUPDAYBS(settlement, maturity, frequency, [basis])

重要:应使用 DATE 函数输入日期，或者将日期作为其他公式或函数的结果输入。 例如，使用函数 DATE(2008,5,23) 输入 2008 年 5 月 23 日。 如果日期以文本形式输入，则会出现问题。

COUPDAYBS 函数用法具有下列参数：

Settlement必需。 有价证券的结算日。 有价证券结算日是在发行日之后，有价证券卖给购买者的日期。

Maturity必需。 有价证券的到期日。 到期日是有价证券有效期截止时的日期。

Frequency必需。 年付息次数。 如果按年支付，frequency = 1；按半年期支付，frequency = 2；按季支付，frequency = 4。

Basis可选。 要使用的日计数基准类型。

其他

Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1，而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。

结算日是购买者买入息票（如债券）的日期。 到期日是息票有效期截止时的日期。 例如，在 2008 年 1 月 1 日发行的 30 年期债券，六个月后被购买者买走。 则发行日为 2008 年 1 月 1 日，结算日为 2008 年 7 月 1 日，而到期日是在发行日 2008 年 1 月 1 日的 30 年后，即 2038 年 1 月 1 日。

所有参数都将被截尾取整。

如果 settlement 或 maturity 不是有效日期，则 COUPDAYBS 返回错误值 #VALUE!。

如果 frequency 不为数字 1、2 或 4，则 COUPDAYBS 返回错误值 #NUM!。

如果 basis 0 或 basis 4，则 COUPDAYBS 返回错误值 #NUM!。

如果 settlement ≥ maturity，则 COUPDAYBS 返回错误值 #NUM!。

案例

COUPDAYS 函数—返回包含结算日的票息期天数

描述

返回结算日所在的付息期的天数。

用法

COUPDAYS(settlement, maturity, frequency, [basis])

重要:应使用 DATE 函数输入日期，或者将日期作为其他公式或函数的结果输入。 例如，使用函数 DATE(2008,5,23) 输入 2008 年 5 月 23 日。 如果日期以文本形式输入，则会出现问题。

COUPDAYS 函数用法具有下列参数：

Settlement必需。 有价证券的结算日。 有价证券结算日是在发行日之后，有价证券卖给购买者的日期。

Maturity必需。 有价证券的到期日。 到期日是有价证券有效期截止时的日期。

Frequency必需。 年付息次数。 如果按年支付，frequency = 1；按半年期支付，frequency = 2；按季支付，frequency = 4。

Basis可选。 要使用的日计数基准类型。

其他

Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1，而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。

结算日是购买者买入息票（如债券）的日期。 到期日是息票有效期截止时的日期。 例如，在 2008 年 1 月 1 日发行的 30 年期债券，六个月后被购买者买走。 则发行日为 2008 年 1 月 1 日，结算日为 2008 年 7 月 1 日，而到期日是在发行日 2008 年 1 月 1 日的 30 年后，即 2038 年 1 月 1 日。

所有参数都将被截尾取整。

如果 settlement 或 maturity 不是有效日期，则 COUPDAYS 返回 错误值 #VALUE!。

如果 frequency 不为数字 1、2 或 4，则 COUPDAYS 返回 错误值 #NUM!。

如果 basis 0 或 basis 4，则 COUPDAYS 返回 错误值 #NUM!。

如果 settlement ≥ maturity，则 COUPDAYS 返回 错误值 #NUM!。

案例

COUPDAYSNC 函数—返回从结算日到下一票息支付日之间的天数

描述

返回从结算日到下一票息支付日之间的天数。

用法

COUPDAYSNC(settlement, maturity, frequency, [basis])

重要:应使用 DATE 函数输入日期，或者将日期作为其他公式或函数的结果输入。 例如，使用函数 DATE(2008,5,23) 输入 2008 年 5 月 23 日。 如果日期以文本形式输入，则会出现问题。

COUPDAYSNC 函数用法具有下列参数：

Settlement必需。 有价证券的结算日。 有价证券结算日是在发行日之后，有价证券卖给购买者的日期。

Maturity必需。 有价证券的到期日。 到期日是有价证券有效期截止时的日期。

Frequency必需。 年付息次数。 如果按年支付，frequency = 1；按半年期支付，frequency = 2；按季支付，frequency = 4。

Basis可选。 要使用的日计数基准类型。

其他

Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1，而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。

结算日是购买者买入息票（如债券）的日期。 到期日是息票有效期截止时的日期。 例如，在 2008 年 1 月 1 日发行的 30 年期债券，六个月后被购买者买走。 则发行日为 2008 年 1 月 1 日，结算日为 2008 年 7 月 1 日，而到期日是在发行日 2008 年 1 月 1 日的 30 年后，即 2038 年 1 月 1 日。

所有参数都将被截尾取整。

如果 settlement 或 maturity 不是有效日期，则 COUPDAYSNC 返回错误值 #VALUE!。

如果 frequency 不为数字 1、2 或 4，则 COUPDAYSNC 返回错误值 #NUM!。

如果 basis 0 或 basis 4，则 COUPDAYSNC 返回错误值 #NUM!

如果 settlement ≥ maturity，则 COUPDAYSNC 返回错误值 #NUM!。

案例

COUPNCD 函数—返回结算日之后的下一个票息支付日

描述

返回一个表示在结算日之后下一个付息日的数字。

用法

COUPNCD(settlement, maturity, frequency, [basis])

重要:�...

很多朋友觉得Excel函数太多了，而且每种都有不同的应用条件，根本记不过来。

如果你也有同样的感受，不想学习太多的函数公式，那么也一定要学习的函数就是-SUMPRODUCT。

无论是条件求和、条件计数、加权平均，这些常用的统计计算它都能做得到！还在为函数太多记不住发愁吗？快来学习它吧~~更多Excel学习和问题请加群：289393114、570064677

今日大纲：

① SUMPRODUCT基础应用

② SUMPRODUCT多条件计数

③ SUMPRODUCT多条件求和

④ 计算加权平均值(权重已知)

⑤ 计算加权平均值(权重未知)

⑥ 交叉查询

⑦ 分组排名

SUMPRODUCT函数的工作原理为：

在给定的几组数组中，将数组间对应的元素先相乘(PRODUCT)，后相加(SUM)。但是就凭这个相乘、相加的计算，就能实现很多功能。

语法

SUMPRODUCT(array1, [array2], [array3], ...)

其中：

array1 必需，它是相应元素需要进行相乘并求和的第一个数组参数。

array2, array3,... 为可选。可以是 2 到 255 个数组参数，其相应元素需要进行相乘并求和。更多Excel学习和问题请加群：289393114、570064677

数组参数必须具有相同的维数。 否则，函数 SUMPRODUCT 将返回 #VALUE! 错误值 #REF!。

1

基础应用

SUMPRODUCT函数的最典型应用：

下面例子中有单价和数量，我们要求所有产品的总价。

其中A12单元格中的公式为：

=SUMPRODUCT(B3:B9,C3:C9)

公式所起到的作用，就像我在旁边蓝色虚框中标出的一样，先将B3到B9中的每一个数，分别与C3到C9中的数相乘。然后再将这些乘积进行加和，就得到了我们要的结果。更多Excel学习和问题请加群：289393114、570064677

2

多条件计数

求成绩大于80分的女生的数量：

我们一共有两个条件：

① 成绩，也就是D列的数，要大于80；

② 性别，也就是C列，为“女”。

D13单元格中的公式为：

=SUMPRODUCT(($C$3:$C$11=$C$3)*($D$3:$D$11>80))

公式中，

$C$3:$C$11=$C$3代表，C3到C11区域等于C3，也就是“女”。符合条件的，会返回“TRUE”（相当于1），不符合条件的，返回“FALSE”（相当于0）.

$D$3:$D$11>80，同理，这几个单元格“成绩”大于80的，返回“TRUE”（1），不符合条件的，返回“FALSE”（0）。

然后两个数组分别相乘。更多Excel学习和问题请加群：289393114、570064677

1*0=0

0*0=0

1*1=1

就得到下面第3列的结果。

然后在将这一列结果进行相加，就得到2.

（此计算过程，下面几个例子很相似，就不再赘述）

3

多条件求和

下面求和的例子，跟计数的区别就在于，最后多乘一个数组。

=SUMPRODUCT(($C$3:$C$11=$C$3)*($D$3:$D$11>80)*($D$3:$D$11))

也就是将刚才的列再与“成绩”那一列，D3：D11进行相乘，然后在进行加和，便可得到。更多Excel学习和问题请加群：289393114、570064677

4

加权平均值(权重已知)

加权平均值的用法也很常见。例如期末成绩由下面3部分构成，每一部分的权重分别为20%，30%，50%。

E4单元格中公式为：

=SUMPRODUCT(B4:D4,B$3:D$3)

与之前例子不同的是，这次是不同行之间先相乘，然后再把列进行相加。

B$3:D$3用了“混合引用”，目的是为了保证公式向下复制时，这一行数据的引用不会发生变化。（对混合引用不了解的戳这里）

5

加权平均值(权重未知)

这里的权重未知，指的是没有一个明确的百分比，只有一个绝对的数量。

这样的情况也很好处理，我们只需用跟上个例子一样的过程，让“数量”和“成本”分别相乘后再相加，然后在除以“数量”的和即可。

图中A9单元格中的公式为：

=SUMPRODUCT(B3:B6,C3:C6)/SUM(B3:B6)

6

交叉查询

交叉查询用在这里可以说又是一神作了。

通常我们熟悉的解决方法是用INDEX+MATCH函数来完成，这种方法我们之前也写过教程了（戳这里查看这个教程）

但是，万能的SUMPRODUCT也一样能行。

下图中，我们要求“超市3”“火龙果”的价格。

=SUMPRODUCT(($B$4:$B$9=B13)*($C$3:$G$3=C13)*($C$4:$G$9))

前两段分开看很好理解，就是判断是否符合条件，符合的返回"TRUE",不符合的返回"FALSE"。

但是，它俩分别是一个列数组和一个行数组，相乘后得到的是一个二维的矩阵。更多Excel学习和问题请加群：289393114、570064677

($B$4:$B$9=B13)*($C$3:$G$3=C13)

如下图：

然后，再把($C$4:$G$9)这个区域的每一个单元格，分别与上面的结果相乘。

最后在将所有的数相加，就得到9.8.

7

分组排名

下面这个例子，"小组"这一列中有两个不同的值，“一组”和“二组”。现在我们要针对“销售额”按不同的组进行排名。

D3单元格中公式为：

=SUMPRODUCT(($B$3:$B$10=B3)*($C$3:$C$10>C3))+1

($B$3:$B$10=B3)不解释了

($C$3:$C$10>C3)就是拿所有的“销售额”跟C3（这里是相对引用，向下复制时会变成C4，C5，……）比，比它大的会返回TRUE。

但是这样一来排第一的数，是没有比它大的，会返回0。所以我们在公式的最后面有个“+1”，这就变成我们想要的“排名”的样子了。更多Excel学习和问题请加群：289393114、570064677

01、如何在已有的单元格中批量加入一段固定字符？ 例如：在单位的人事资料，在excel中输入后，由于上级要求在原来的职称证书的号码全部再加两位，即要在每个人的证书号码前再添上两位数13，如果一个一个改的话实在太麻烦了，那么我们可以用下面的办法，省时又省力： 1）假设证书号在A列，在A列后点击鼠标右键，插入一列，为B列； 2）在B2单元格写入：="13" & A2 后回车； 3）看到结果为 13xxxxxxxxxxxxx 了吗？鼠标放到B2位置，单元格的下方不是有一个小方点吗，按着鼠标左键往下拖动直到结束。当你放开鼠标左键时就全部都改好了。 若是在原证书号后面加13 则在B2单元格中写入：=A2 & “13” 后回车。 02、如何设置文件下拉窗口的最下面的最近运行的文件名个数？ 打开“工具”，选“选项”，再选“常规”，在“最近使用的文件清单”下面的文件个数输入框中改变文件数目即可。若不在菜单中显示最近使用的文件名，则将“最近使用的文件清单”前的复选框去掉即可。 03、在EXCEL中输入如“1-1”、“1-2”之类的格式后它即变成1月1日，1月2日等日期形式，怎么办？ 这是由于EXCEL自动识别为日期格式所造成，你只要点击主菜单的“格式”菜单，选“单元格”，再在“数字”菜单标签下把该单元格的格式设成文本格式就行了。 04、在EXCEL中如何使它象WORD一样的自动定时保存文件？ 点击“工具”菜单“自动保存”项，设置自动保存文件夹的间隔时间。如果在“工具”菜单下没有“自动保存”菜单项，那么执行“工具”菜单下“加载宏...”选上“自动保存”，“确定”。然后进行设置即可。 05、用Excel做多页的表格时，怎样像Word的表格那样做一个标题，即每页的第一行（或几行）是一样的。但是不是用页眉来完成？ 在EXCEL的文件菜单－页面设置－工作表－打印标题；可进行顶端或左端标题设置，通过按下折叠对话框按钮后，用鼠标划定范围即可。这样Excel就会自动在各页上加上你划定的部分作为表头。 06、在Excel中如何设置加权平均？ 加权平均在财务核算和统计工作中经常用到，并不是一项很复杂的计算，关键是要理解加权平均值其实就是总量值（如金额）除以总数量得出的单位平均值，而不是简单的将各个单位值（如单价）平均后得到的那个单位值。在Excel中可设置公式解决（其实就是一个除法算式），分母是各个量值之和，分子是相应的各个数量之和，它的结果就是这些量值的加权平均值。 07、如果在一个Excel文件中含有多个工作表，如何将多个工作表一次设置成同样的页眉和页脚？如何才能一次打印多个工作表？ 把鼠标移到工作表的名称处（若你没有特别设置的话，Excel自动设置的名称是“sheet1、sheet2、sheet3.......”），然后点右键，在弹出的菜单中选择“选择全部工作表”的菜单项，这时你的所有操作都是针对全部工作表了，不管是设置页眉和页脚还是打印你工作表。 08、EXCEL中有序号一栏，由于对表格进行调整，序号全乱了,可要是手动一个一个改序号实在太慢太麻烦，用什么方法可以快速解决？ 如果序号是不应随着表格其他内容的调整而发生变化的话，那么在制作EXCEL表格时就应将序号这一字段与其他字段分开，如在“总分”与“排名”之间空开一列，为了不影响显示美观，可将这一空的列字段设为隐藏，这样在调整表格（数据清单）的内容时就不会影响序号了。 09、用Excel2000做成的工资表，只有第一个人有工资条的条头（如编号、姓名、岗位工资.......)，想输出成工资条的形式。怎么做？ 这个问题应该这样解决：先复制一张工资表，然后在页面设置中选中工作表选项，设置打印工作表行标题，选好工资条的条头，然后在每一个人之间插入行分页符，再把页长设置成工资条的高度即可。 使用自定义方式重装了一遍中文office，Excel的打印纸选项中只有A4一种，怎么办？ 随便安装一个打印机驱动程序就可以了。 10、在Excel中小数点无法输入，按小数点，显示的却是逗号，无论怎样设置选项都无济于事，该怎么办？ 这是一个比较特殊的问题，我曾为此花了十几个小时的时间，但说白了很简单。在Windows的控制面板中，点击“区域设置”图标，在弹出的“区域设置属性”对话面板上在“区域设置”里选择“中文（中国）”，在“区域设置属性”对话面板上在“数字”属性里把小数点改为“.”（未改前是“，”），按“确定”按钮结束。这样再打开Excel就一切都正常了。 11、如何快速选取特定区域？ 使用F5键可以快速选取特定区域。例如，要选取A2：A1000，最简便的方法是按F5键，出现“定位”窗口，在“引用”栏内输入需选取的区域A2：A1000。 12、如何快速返回选中区域？ 按Ctr+BacksPae（即退格键）。 13、如何快速定位到单元格？ 方法一：按F5键，出现“定位”对话框，在引用栏中输入欲跳到的单元格地址，单市“确定”按钮即可。 方法二：单击编辑栏左侧单元格地址框，输入单元格地址即可。 14、“Ctrl＋*”的特殊功用 一般来说，当处理一个工作表中有很多数据的表格时，通过选定表格中某个单元格，然后按下 Ctrl＋* 键可选定整个表格。Ctfl＋* 选定的区域是这样决定的：根据选定单元格向四周辐射所涉及到的有数据单元格的最大区域。 15、如何快速选取工作表中所有包含公式的单元格？ 有时，需要对工作表中所有包含公式的单元格加以保护，或填入与其他单元格不同的颜色，以提醒用户注意不能在有此颜色的区域内输入数据。以下方法可以帮助快速选取所有包含公式的单元格：选择“编辑”\“定位”，单击“定位条件”按钮，在“定位条件”对话框中选择“公式”项，按“确定”按钮即可。 16、如何在不同单元格中快速输入同一数内容？ 选定单元格区域，输入值，然后按 Ctrl＋ Ener键，即可实现在选定的单元格区域中一次性输入相同的值。 17、只记得函数的名称，但记不清函数的参数了，怎么办？ 如果你知道所要使用函数的名字，但又记不清它的所有参数格式，那么可以用键盘快捷键把参数粘贴到编辑栏内。 具体方法是：在编辑栏中输入一个等号其后接函数名，然后按 Ctr＋ A键，Excel则自动进入“函数指南——步骤 2之2”。当使用易于记忆的名字且具有很长一串参数的函数时，上述方法显得特别有用。 18、如何把选定的一个或多个单元格拖放至新的位置？ 按住Shift键可以快速修改单元格内容的次序。 具体方法是： 选定单元格，按下Shift键，移动鼠标指针至单元格边缘，直至出现拖放指针箭头（空心箭头），然后按住鼠标左键进行拖放操作。上下拖拉时鼠标在单元格间边界处会变为一个水平“工”状标志，左右拖拉时会变为垂直“工”状标志，释放鼠标按钮完成操作后，选定的一个或多个单元格就被拖放至新的位置。 19、如何让屏幕上的工作空间变大？ 可以将不用的工具栏隐藏，也可以极大化Excel窗口，或者在“视图”菜单中选择“全屏显示”命令。 20、如何使用快显菜单？ 快显菜单中包括了一些操作中最常用的命令，利用它们可以大大提高操作效率。首先选定一个区域，然后单击鼠标右健即可调出快显菜单，根据操作需要选择不同命令。 21、如何使用快显菜单？快显菜单中包括了一些操作中最常用的命令，利用它们可以大大提高操作效率。首先选定一个区域，然后单击鼠标右健即可调出快显菜单，根据操作需要选择不同命令。 22、如何防止Excel自动打开太多文件？ 当Excel启动时，它会自动打开Xlstart目录下的所有文件。当该目录下的文件过多时，Excel加载太多文件不但费时而且还有可能出错。解决方法是将不该位于Xlstart目录下的文件移走。另外，还要防止EXcel打开替补启动目录下的文件：选择“工具”\“选项”\“普通”，将“替补启动目录”一栏中的所有内容删除。 23、如何去掉网格线？ 1）除去编辑窗口中的表格线 单击“工具”菜单中的“选项”，再选中“视图”，找到“网格线”，使之失效； 2）除去打印时的未定义表格线 有时会出现这样的情况：你在编辑时未定义的表格线（在编辑窗中看到的也是淡灰色表格线），一般情况下在打印时是不会打印出来的，可有时却偏偏不听使唤给打印出来了，特别是一些所谓的“电脑”VCD中编辑的Excel表格更是这样。要除去这些表格线，只要在单击“文件”、“页面设置”、“工作表”菜单，点击一下“网格线”左边的选择框，取消选择“网格线”就行了。 24、如何快速格式化报表？ 为了制作出美观的报表，需要对报表进行格式化。有快捷方法，即自动套用Excel预设的表格样式。方法是： 选定操作区域，选取“格式”菜单中的“自动套用格式”命令，在格式列表框中选取一款你满意的格式样式，按“确定”按钮即可。要注意的是，格式列表框下面有包括“数字”、“边框线”、“字体”等6个“应用格式种类”选项，若某项前面的“x”不出现，则在套用表格样式时就不会用该项。 25、如何快速地复制单元格的格式？ 要将某一格式化操作复制到另一部分数据上，可使用“格式刷”按钮。选择含有所需源格式的单元格，单击工具条上的“格式刷”按钮，此时鼠标变成了刷子形状，然后单击要格式化的单元格即可将格式拷贝过去。 26、如何为表格添加斜线？ 一般我们习惯表格上有斜线，而工作表本身并没有提供该功能。其实，我们可以使用绘图工具来实现： 单击“绘图”按钮，选取“直线”，鼠标变成十字型．将其移至要添加斜线的开始位置，按住鼠标左键拖动至终止位置，释放鼠标，斜线就画出来了。另外，使用“文字框”按钮可以方便地在斜线上下方添加文字，但文字周围有边框，要想取消它，可选中文字框，调出快显菜单，选择“对象格式”\“图案”，选择“无边框”项即可。 27、如何快速地将数字作为文本输入？ 在输入数字前加一个单引号“”’，可以强制地将数字作为文本输入。 28、如何定义自己的函数？ 用户在Excel中可以自定义函数。切换至 Visual Basic模块，或插入一页新的模块表（Module），在出现的空白程序窗口中键入自定义函数VBA程序，按Enter确认后完成编 写工作，Excel将自动检查其正确性。此后，在同一工作薄内，你就可以与使用Exed内部函数一样在工作表中使用自定义函数，如： Function Zm(a) If a＜ 60 Then im＝‘不及格” Else Zm=“及格” End If End Function 29、如何在一个与自定义函数驻留工作簿不同的工作簿内的工作表公式中调用自定义函数？ 可在包含自定义函数的工作薄打开的前提下，采用链接的方法(也就是在调用函数时加上该函数所在的工作簿名)。假设上例中的自定义函数Zm所在工作薄为MYUDF.XLS，现要在另一不同工作簿中的工作表公式中调用Zm函数，应首先确保MYUDF.XLS被打开，然后使用下述链接的方法：=MYUDF.XLS！ ZM(b2) 30、如何快速输入数据序列？ 如果你需要输入诸如表格中的项目序号、日期序列等一些特殊的数据系列，千万别逐条输入，为何不让Excel自动填充呢？在第一个单元格内输入起始数据，在下一个单元格内输入第二个数据，选定这两个单元格，将光标指向单元格右下方的填充柄，沿着要填充的方向拖动填充柄，拖过的单元格中会自动按Excel内部规定的序列进行填充。如果能将自己经常要用到的某些有规律的数据（如办公室人员名单），定义成序列，以备日后自动填充，岂不一劳永逸！选择“工具”菜单中的“选项”命令，再选择“自定义序列”标签， 在输入框中输入新序列，注意在新序列各项2间要输入半角符号的逗号加以分隔（例如：张三，李四，王二），单击“增加”按钮将输入的序列保存起来。 31、使用鼠标右键拖动单元格填充柄 上例中，介绍了使用鼠标左键拖动单元格填充柄自动填充数据序列的方法。其实，使用鼠标右键拖动单元格填充柄则更具灵活性。在某单元格内输入数据，按住鼠标右键沿着要填充序列的方向拖动填充柄，将会出现包含下列各项的菜单：复制单元格、以序列方式填充、以格式填充、以值填充；以天数填充、以工作日该充、以月该充、以年填充；序列此时，你可以根据需要选择一种填充方式。 32、如果你的工作表中已有某个序列项，想把它定义成自动填充序列以备后用，是否需要按照上面介绍的自定义序列的方法重新输入这些序列项？ 不需要。有快捷方法：选定包含序列项的单元格区域，选择“工具”\“选项”\“自定义序列”，单击“引入”按钮将选定区域的序列项添加至“自定义序列”对话框，按“确定”按钮返回工作表，下次就可以用这个序列项了。 33、上例中，如果你已拥育的序列项中含有许多重复项，应如何处理使其没有重复项，以便使用“引入”的方法快速创建所需的自定义序列？ 选定单元格区域，选择“数据”\“筛选”\“高级筛选”，选定“不选重复的记录”选项，按“确定”按钮即可。 34、如何对工作簿进行安全保护？ 如果你不想别人打开或修改你的工作簿，那么想法加个密码吧。打开工作薄，选择“文件”菜单中的“另存为”命令，选取“选项”，根据用户的需要分别输入“打开文件口令”或“修改文件D令”...

转载自百家号作者：解晴新生

教师节了，没什么礼物送给老师的，整理了15个最常用的Excel公式送给您们，主要包含分数统计、排名计算、成绩等级计算、绩点和加权平均分、随机座位、对筛选数据计算等6个方面。希望有所帮助吧。

第一：成绩统计使用最多的5个函数

虽然现在有很多学校不再公布排名了，但老师们还是需要了解一下整体的情况，然后因材施教。

SUM、AVERAGE、MAX、MIN用于获取学生的总分、平均分、最高分和最低分。RANK函数用于获取学生成绩排名，分数相同的会得到相同的排名，如下表的第一名有两个，因此第二名就没有了，第三个人直接是第3名。RANK函数第一个参数就是某个成绩，第二个参数是所有成绩所在单元格引用。

第二：成绩等级有关的IF和AND函数

成绩不再排名之后，家长们至少会知道自己的孩子的成绩属于哪个等级，有分为“优秀、良、及格、不及格”的，也有分为“A、B、C”等。

一个IF和一个AND函数，外加一些必要的嵌套，就可以获得各种等级数据了。另外，可以给不同的等级设置不同的条件格式，这样可以让数据一目了然。

第三：人数统计相关的函数

常见的有考生人数的统计、及格人数的统计。一般用COUNT系列的函数。直接统计就用COUNT或COUNTA函数，一个条件就用COUNTIF函数，多个条件就用COUNTIFS函数。“人数”前有几个修饰词就表示有几个条件。

第四：绩点和加权平均分

绩点的大致规则是这样的，60分以下不算绩点，60分算一个绩点，每多5分，多0.5个绩点，这样我们就可以用公式“=LOOKUP(成绩,{绩点临界分数列表},{绩点列表})”来获得每门课程的绩点。

加权平均分就是在计算平均分时，把课程权重也加上，学分多的权重大，最后对平均分的影响也大。使用的公式是“SUMPRODUCT(学分所在单元格,成绩所在单元格)/SUM(学分所在单元格)”。

第五：考生座位随机安排

按顺序填充所有的座位，然后在辅助列输入“rand()”函数产生随机数，接着按照随机数排列即可给每位考生随机安排好座位。

第六：subtotal统计筛选出的学生数据

如果你希望只对筛选出来的学生成绩进行统计，就不要错过subtotal函数。

subtotal函数的格式为“=subtotal(函数序号,引用1,[引用2],...)”可以用于统计总分、平均分、最高分、人数等。具体步骤可以参考文章《WPS Excel：筛选数据后，自动更新计算结果和序号》。

谢谢阅读，每天学一点，省下时间充实自己。欢迎点赞、评论、关注和点击头像。

给大家介绍一个函数，SUMPRODUCT,(在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。)

SUMPRODUCT(array1, [array2], [array3], ...)

在工作中，我们经常会遇到求加权平均的例子，比如下图中，求加权平均价格的算法是每个价格乘以产量权重的乘积之和除以总产量，既可以得到结果。

利用函数的算法，我们只需将产量和价格两个区域作为两个参数，结果就可以作为分子，再除以总产量即可。

欢迎大家关注八棵柏树，有问题留言。

　　EXCEL中求加权平均数的方法：

1. EXCEL中没有专门的加权平均函数；

2. 但可以利用加权平均数的公式，用其它函数将其求出，加权平均值的求法是：各数值乘以相应的权数，然后相加求和得到总体值，再除以总的单位数；

3. 转换到EXCEL，就是乘积和除以和，所以可以用SUMPRODUCT乘积和函数除以SUM求和函数来得到，如下图中求均价，用：=SUMPRODUCT(A2:A4*B2:B4)/SUM(A2:A4)即可。

（本文内容由百度知道网友505548375贡献）