自动化办公之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下,谢谢。
在这里插入图片描述

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

总裁余(余登武)

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值