excel常见函数

在excel里使用的函数

平均数 - AVERAGE(数值范围)
总和 - SUM(数值范围)

最高/大 - MAX(数值范围)
最低/小 - MIN(数值范围)
第几高/大 - LARGE(数值范围，数字第几个)
第几低/小 - SMALL(数值范围，数字第几个)
月份 - MONTH(数值范围) 把具体日期变成月份数字
今天日期 - TODAY()
现在时间 - NOW()
按年或者月计算经过多久 - DATEDIF(起始时间, TODAY(), “y/m/d/md/yd/ym”)
算出除周六日假期日期 - NETWORKDAYS(起始日,结束日,除周末外的假日)
不被周六周日限制可自行选择哪天是假日 - NETWORKDAYS.INTL(起始日,结束日,自定周末,假日)

当搜索文字类型的资料时，必须使用双引号，否则会报错
使用绝对参照时需添加$在字母和数字前, 或者全选点f4
首字母大写 - proper()

条件函数

普通的条件函数 - IF(条件,”value if true”, “value if false”)
当有多个case时 - IFS(case1,”value under this case”, case2,”value under this case”,case3,”value under this case”, etc)
||
2016年前的excel不支持以上IFS, 那么可以用IF套IF
IF(case1,”value under this case”, IF(case2, “value under this case”, IF(case3, “value under this case”, “value under left case”)))

查询函数

单向查询
- 查询值以column排列: VLOOKUP(需查询值的key，查询的范围[第一个column必须是以查询值那列开始然后以L型查询]，需表查询值的value在这行内排第几个，是否模糊查询[TRUE=模糊查询, FALSE=精确查询]）
- 查询值以row排列: HLOOKUP(被查询值,查询范围,传回的列数)
多向查询
- 给location 找那个位置得值
  1. 单行单列 - INDEX(行/列范围,第几位)
  2. 多行多列 - INDEX(范围,row, col) 可以像坐标一样定位数值
- 给值找那个值所对应的location
  1. 单行单列 - MATCH(查找对象,查找范围,比对方式[1->小于, 0->完全符合, -1->大于])
- 实际应用:
  找到一个人根据他的名字便获得他的所有信息
  这样需要两个信息名字和想要知道信息
  可以通过MATCH单行单列得找到两个信息所在的位置这样便可知道横纵坐标然后通过INDEX(寻找范围,横坐标,纵坐标)找到想要的信息

需注意查询范围有分为相对引用和绝对引用: 相对引用是随着拉下的行变动的，但是绝对引用是不会随着拉下的行变动的

查询出现问题时

如果查询出现问题时，如查询的值不在范围之内，可以使用IFERROR函数去处理问题时应该回应的 -> IFERROR(VLOOKUP()[查不到值],”查无此人或者可以写你想要回应的信息”)
如查询的值是空值时，可以使用条件函数，IF(查询值=””,””,IFERROR(VLOOKUP()[查不到值],”查无此人或者可以写你想要回应的信息”)) <- 如果查询值是空值时，留空白

计数函数

COUNT(范围) 算表格总共多少数据但只能求数字数据如果是文字的话都会变成0
COUNTA(范围) 可以数不只数字的数据还可以数文字的数据
COUNTIF/COUNTIFS(范围, 想要比对的数值) 算有多少个在规定范围内符合对比数值，这个比对的数值可以是在括号里写好也可以是单独在另一个格子里然后reference到那个格子
- 可以”>”, “<”, “>=”, “<=”, “=”
- 如需对比格子里的值可以在后面加&
- 还可以对比多个数值 COUNTIFS(条件一的范围，条件一，条件二的范围，条件二…) 返回符合所有条件的总个数
SUMIFS(想要算和的范围，条件一的范围，条件一，条件二的范围，条件二…) 对比完所有条件后符合这些条件的数值求总和

定义名称和INDIRECT

定义名称->可在Formulas里Define Name去定义先选取想要定义的范围然后点击Define Name 里面会自动帮你填写这个variable的名字你也可以自行改动 range也可以改动
- 这个名称就像是global variable，可以用在任何函数里比如数在某个范围里有多少个数值，这次可以直接COUNT(名称)
INDIRECT->非直接性找连着的数值
- 实际应用: 查询一本书或者一个菜单里的list
  1. 绑定相应的list到对应的书名或者菜单: 通过全选数值范围到Formulas里选create from selection 选择想要代表这列数值的名称可以是最左/右/上/下就是这个菜单名或者书名这个步骤也可以用定义名称做选定list的范围然后命名想要定义的名称
  2. 设置可选取书籍或者菜单的范围: 在选择书籍或者菜单title下的格子点击Data里的validation 设置可选的value 选List 范围是书籍或者菜单的位置
  3. 绑定有什么内容可以选择在选好书籍或者菜单的情况下: 在选择范围下的格子同意点击Data里的validation 设置可选的value 选List 范围是INDIRECT(选择好的书籍或者菜单的格子)
  - 这样便可以再选好书籍或者菜单后就可以知道这个书籍或菜单里有什么选项

排名函数

RANK.EQ(主体,比较范围,排序方式[0->de, 1->in])
-> 当出现同样成绩时 RANK.EQ会把排名写成一样
RANK.AVG(主体,范围)
-> 当出现同样成绩时 RANK.EQ会把排名写成平均值

通过函数来获取文字资料

LEFT(资料位置,抓取字数)
RIGHT(资料位置,抓取字数)
MID(资料位置,开始位置,抓取字数)
FIND(寻找的文字,资料位置,搜寻起点)
LEN(资料位置) - 计算存储格内空格和字母

求随机函数

RANDBETWEEN(最小值,最大值) 可随机产生鉴于中间的随机值
实际例子:

1. 随机分配给学生试卷 有两个选择->A B卷
方法一: 通过附表 INDEX(附表范围, RANDBETWEEN(1,2))
方法二: 不用附表 CHOOSE(RANDBETWEEN(1,2),"A卷","B卷") 
2. 平均平分
上面的方法确实可以随机分配 但是不能平均平分 这个时候可以通过RAND() 算出随机数 然后根据随机数RANK() 再用RANK()/# 想要平分多少个 这样可以得数不好分辨 可以用ROUNDUP(刚才求得数,0) 这样可以得到整数 相同个数的整数 这样便可以做随机平均评分CHOOSE(ROUNDUP(),"A组","B组")

Commentaires

Articles

73

Catégories

11