Excel常用函数总结--记一次数据操作中使用函数

一、数据库中取出数据

SELECT id AS 编号,name  FROM `1301`  GROUP BY 编号,name UNION ALL
SELECT id AS 编号,name  FROM `1302`  GROUP BY 编号,name UNION ALL
SELECT id AS 编号,name  FROM `1303`  GROUP BY 编号,name

group by : 分组查询
union   ： 表联合

导出 -> excel数据表

二、excel表数据处理

• VLOOKUP（vertical lookup：竖直查找）列联合

=VLOOKUP( A:A ,Sheet1!A:B,   2   ,    0     )
=VLOOKUP(查找值,查找范围,输出列号,[匹配条件])
输出列号：是在查找范围中的第几列
匹配条件：FALSE（可0代替）表示精确查找，TRUE表示模糊查找

• IFERROR,excel去除#N/A等错误值
  IFERROR(值,错误值)
  其中value的错误格式有#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?、#NULL等。例如：

=IFERROR(VLOOKUP(A1/B1,"错误")
=IFERROR(VLOOKUP(C2,A:B,2,0),0)

• EXCEL把滚动条快速拖动到有数据的最后一行
  当写好公式下拉时，excel有几万行，拉下来太耗费时间，快捷方法
  方法1：将鼠标移到写好公式单元格的右下角,当鼠标形状变为细十字形时,双击左键
  方法2：复制公式单元格，选中要拖动的区域后，粘贴

• 数据透视表
  打开：数据 -> 数据透视表 -> 选择区域
  操作：拖动 选择某列为行区域、列区域、数据区域
  关闭：数据透视表工具 -> 删除

• COUNTIF（统计符合条件的数量）

countif(数据区域,条件表达式)

例：统计A列的数值，是否在G列中

=IF(COUNTIF(G:G,A2)>0,"在","不在")

• Excel中数值取整及INT和TRUNC及ROUND

数值取整（四舍五入）      'ROUND'
数值取整（去小数位）      'INT'、'TRUNC'
INT  TRUNC 在负数范围内所得到的结果是不同的，这是他们的唯一区别

• SUMIF条件求和函数

SUMIF( 区域 , 条件 , [求和区域] )
=SUMIF(B:B,">90")   #对B列大于90的求和
=SUMIF(A:A,A1,B:B)  #求A列为A1对应的B列的数值之和

• 日期相减、时间相减

日期相减： 先把日期数据单元格格式设为日期格式，之后直接相减，如=F2-D2（默认为天数）
datedif函数是excel的隐藏函数，主要用于计算日期之差，不是四舍五入，是向下取整。
datedif(开始日期,终止日期,比较单位)

	A	B
1	2015/4/8 9:00
2	2016/4/8 10:26

求年数差：=DATEDIF(A1,A2,"y")
求月份差：=DATEDIF(A1,A2,"m")
求天数差：=DATEDIF(A1,A2,"d")

时间相减（直接相减后乘以相应时间单位）：

求小时数：=(A2-A1)*24
求分钟输：=(A2-A1)*24*60

TEXT(A2-A1,”hh:mm:ss”)，得到的结果也是小时 间隔数

• excel下拉菜单
  数据 -> 有效性 -> 设置选项卡 -> 有效性条件选为“序列” -> 来源：如： 男,女

---

转载请务必保留此出处：http://blog.youkuaiyun.com/fgf00/article/details/51105121