自动化办公之excel教程（5）:公式与函数的应用大全

一.公式篇

一.输入和编辑公式
第一步：选择单元格

第二步：在上方输入公式，输入公式时如B3我们直接点击B3就自动输入了。
公式以等号开头，带上单元格位置。

第三步：回车， 出现+号时 并往下拉。或者双击即能填满整列结果。

编辑公式
选择单元格然后在上方进行修改

公式移动或者复制
我们选择一个带公式单元格把它复制粘贴到其他单元格，其他单元格的结果会自动根据公式计算。图中第一个结果有公式，我们复制粘贴两个，新的结果并不是4790元，而是通过计算得到的。

复制数据后，选择粘贴，粘贴可以选。

公式的相对引用和绝对引用，混合引用
相对引用前面介绍了，就是复制粘贴公式时，公式会随着位置而自动修改。
绝对应用步骤如下。
第一步：输入公式，我们想要L3不变

第二步：我们选择L3，按住F4键，出现如图。$号表示固定。

第三步：双击，或者出现+号往下拉

查看下新的数据公式

混合应用
$A3表示A列绝对3行相对

二.使用和编辑名称
第一步：公式选项卡，名称管理器 输入汉字基本工资
点击引用位置，然后点击右下角那个下拉框，对位置进行框选。
得到如图，点击确定

第二步：重复多次

第三步：在单元格里输入公式，

三.公式的审核
我们发现数据中有一个错误。

第一步：选择一个单元格，点击公式选项卡
下面有追踪引用单元格

结果，图中的这些点表示行列
我们发现引用了一个中文

错误检查
首先选择单元格，在公式选项卡下点击错误检查

显示公式

二.函数篇

1.函数介绍和结构
.函数的分类
公式选项卡下，点击插入函数
函数的输入都是英文格式下。

或者点击图中fx按钮

操作示范
第一步：选择单元格

第二步：选择函数，我选择平均

第三步：点击第二步的图右侧，选择区域，回车得到结果
图中我选的区域为两行。

函数是一个难点，可以单击F1键，右侧出现帮助页面，输入查询即可

2.数学计算函数

数学函数：求和，最大，最小，平均，排名

求和函数
我们直接在括号里输入=sum(),然后将鼠标放在sum()括号内，框选区域即可
或者点击插入函数操作

平均值函数

最大值函数

最小值函数

排名rank函数
初始表格

第一步：找到rank函数

第二步：数值 是对那个进行排名，引用是数值范围。可以直接框选区域

第三步：由前图看出来有点不对，那是我们引用区域是相对引用，将相对引用改绝对引用。

3.条件统计函数函数
条件统计函数有
if:判断真假
Count if:有条件统计
Sum if:有条件的求和
Count ifs:有多条件的统计
Sum ifs:有多条件的求和

if:判断真假
If函数(条件判断句、成立返回的值、不成立返回的值)
原始表格

第一步：选择单元格，找到if函数

第二步：输入

第三步：结果

第四步：在开始选项卡里，条件格式，可以修改颜色
选一个单元格，然后按图

下拉框选区域

Count if:有条件统计
Count if 函数：有条件的统计(统计范围、统计条件)
原始表格中的男职工人数，女职工人数，统计是有条件的。

第一步：选中单元格，找到Count if函数

第二步：区域选择，Ctil+Shift+向下的键 ，可以选择一列。
条件选择，把鼠标放在任意一个为男的单元格上。

结果：

Sum if:有条件的求和 average if 有条件求平均值
前文中我们相求男职工的平均年龄。
Sum if函数: 有条件的求和(统计范围、统计条件、求和范围)

第一步：选择单元格，找到Sum if

第二步：
区域应该是性别和年龄
条件放在任意一个男的单元格上，
求和区域：年龄

第三步：手动在公式后面添加/19

或者使用average if

Count ifs:有多条件的统计
Countifs多条件统计(条件区域1、条件1、条件区域2、条件2……)
原始表格
统计结果：为统计书在指定书店的销量

第一步：选择单元格，找到Count ifs

第二步：区域有图书名称和书店名称 表见前文

Sum ifs:有多条件的求和
Sumifs多条件求和(求和区域、条件区域1、条件1、条件区域2、条件2)
第一步：选择单元格，找到sum ifs

第二步：求和区域为销量（本），区域为图书名称，书店名称

第三步：结果

3.查找引用函数
查找引用函数有
VLOOKUP:进行列查找
Index:按指定的行列查找
Match：查找在指定数组区域中的位置

VLOOKUP:进行列查找
第一步：单元格选择G9 打开vlookup函数

第二步：
查找值我选择G8即资产编号
数据表：选择整个数据表
列：选第二列即资产名称
匹配条件：0为精确匹配

第三步：结果
在单元格中返回冲床

可以将数据表改为绝对引用，然后往下拉，修改查找列

Index:按指定的行列查找
原始表格和方法见图

参数页面

结果

Match：查找在指定数组区域中的位置

使用方法见图

参数页面

结果：返回5，第5行

Match index联合使用

使用方法见图，match部分返回行数，

4.财务函数
PMT函数：返回贷款的每期付款额
IPMT函数：返回贷款的利息
FV函数：计算一笔投资的未来值
PV函数：返回投资的现值

PMT函数：返回贷款的每期付款额

PMT函数：返回贷款的每期付款额
1、rate:贷款利率
2、nper:贷款的付款时间数
3、PV：本金
4、FV：最后一次付款后希望得到的现金余额
5、type:指定各期的付款时间在期末还是在期初。1是期初指月初还上个月的。0是期末指月末还这个月的

原始表格，贷款数额和年和年利率见图，计算按年还和按月还的钱

第一步：

结果：每年还的钱

按月还

利率/12

IPMT函数：返回贷款的利息

IPMT函数：返回贷款的利息
1、rate: 贷款利率
2、per: 计算利息数额的期数
3、nper：付款的总期数
4、PV：本金
5、type: 指定各期的付款时间在期末还是在期初

原始表格

第一步：参数设置

第二步：修改引用方式，部分改为绝对引用

结果

FV函数：计算一笔投资的未来值

FV函数：计算一笔投资的未来值
1、rate:各期利率
2、nper:总投资期
3、Pmt：各期应所支付的金额
4、PV：本金
5、type: 指定各期的付款时间在期末还是在期初

原始表格

参数设置
因为是向银行存，手上钱变少，所以为-。因为为月，所以年利率除以12

PV函数：返回投资的现值

PV函数：返回投资的现值
1、rate:各期利率
2、nper:总投资期
3、Pmt：各期应所支付的金额
4、FV：本金
5、type: 指定各期的付款时间在期末还是在期初

原始表格

参数设置
定期支付为负号，向银行支付
得到为正，取出来。

结果

5.时间日期函数

Date:返回日期的序列号
Now:显示系统的当前日期和时间
Today：返回系统的当前日期
Days360：按每年360天计算相差天数
Workday:计算工作日相关日期

Date:返回日期的序列号，time返回时间
原始表格

参数设置

结果

时间函数

可以在单元格格式设置里更改时间格式

Now:显示系统的当前日期和时间

Datedif函数：计算两个日期相差的年数、月数、天数
1、开始日期 2、结束日期 3、代码(Y:年 M:月 D:日)

2.修改引用方式，结果

Days360：按每年360天计算相差天数

DAYS360函数：按照每年360天计算相差天数
1、 start-date:表示期间天数的开始日期
2、 end-date:表示期间天数的结束日期
3、method：采用欧洲方法还是美国方法

Workday:计算工作日相关日期

Workday函数：计算工作日相关日期
1、 start-date:表示开始日期的日期
2、 days:表示之前或者之后不含周末以及节假日的天数
3、Holidays：表示工作日历中排除日期值

其他文章传送门：
自动化办公之excel教程（1）：工作薄，工作表，单元格基本操作
自动化办公之excel教程（2）：各种数据的输入，自动填充数据，表格操作小技巧
自动化办公之excel教程（3）：数据编辑操作，表格的美化操作，应用表格样式和单元格样式，制作报销汇总单
自动化办公之excel教程（4）:使用艺术字，图片，图形美化工作表
自动化办公之excel教程（6）:数据的分析和处理
自动化办公之excel教程（7）：图表和数据透视表（学会后，excel可视化大屏不是梦）
自动化办公之excel教程（8）：单变量求解，规划求解，页面布局，打印设置

电气工程的计算机萌新：余登武。写博文不容易。如果你觉得本文对你有用，请点个赞支持i下，谢谢。