EXCEL函数及数据分析技巧整理备用

0X01 EXCEL怎么删除重复项

方法： 选中区域，数据->删除重复项，勾选字段列名

---

0X02 EXCEL常用函数整理

• =TEXT(A2,“yyyy-mm-dd”)
  公式的作用：将日期格式：YYYY年MM月DD日转换成YYYY-MM-DD
• =CONCATENATE("*******",RIGHT(A2,4))
  公式的作用：将日期格式：替换手机号前七位为*号
• =IF(E2=K2,“相同”,“不同”)
  公式的作用：判断一个条件是否满足，如果满足返回一个值，如果不满足则返回另一个值
• ＝COUNTIF(A1:A1000,A1)
  公式的作用：统计A 列从第一行（A1）到最后一行(A1000)中，有多少个与A1 格相同的数值
• =COUNTIFS(A2:A446,A2,B2:B446,B2)
  公式的作用：同时进行多个条件的计数统计。示例：=COUNTIFS（D2：D15,“<=60”,D2：D15,“>=80”）
• =SUMIF(Sheet1!B:B,A:A,Sheet1!G:G)
• =SUMIF(Sheet1!A:A,$A2,Sheet1!C:C) 加$表示绝对引用
  第一个参数为要计算值的单元格区域，第二个参数为条件，第三个参数为实际求和区域
  公式的作用：将满足条件的数值进行求和
• =SUMIFS(Sheet1!G:G,Sheet1!B:B,A:A,Sheet1!C:C,C:C)
  第一个参数为实际求和区域
  公式的作用：同时满足多个条件时对指定的单元格进行求和
• =VLOOKUP(A:A,Sheet2!A:B,2,0)
  =VLOOKUP(查找什么，在哪查找，需要取回的数据所在的列序号，精确或模糊查找)

VLOOKUP(查找值,查找区域,列序号,匹配类型)
列号：告诉EXcel，我们需要将查询范围里哪一列的数据取回来？
公式的作用：“查找和粘贴”

• =VLOOKUP($A$2:$A$17,’[新创建文件.xlsx]字表1’!$A$2:$E$5000,COLUMN(B1),0)
  公式作用：VLOOKUP函数返回多列结果
  函数COLUMN(B1)返回的是B1单元格所在的列序号

---

0X03 EXCEL数据分析技巧

• 快速填充一列的公式
  先输入要填充的公式,按下SHIFT+CTRL+方向键下,再按下CTRL+D
• 将一列数据变为负数
  =C2*-1，并将公式填充整列
• 将Unix时间戳转换为Excel日期
  =(((k2/60)/60)/24)+DATE(1970,1,1)，并将公式填充整列

iivaf…@gmail.com Thanks you a lot. I find reason. usual utc Unix-like system (Jan 1, 1970) Chrome start from new date 1601 so difference 11644473600 second to calculate usual date. Thanks you to helpfull links
datetime(last_visit_time/1000000-11644473600,‘unixepoch’,‘localtime’) #谷歌浏览器 history.db last_visit_time 17位

---

[1] Vlookup函数：这篇让你一学就会
[2] EXCEL VLOOKUP函数怎么返回多列结果
[3] Excel中如何将日期转换为Unix时间戳（或将时间戳转换为Excel日期）
[4] 解决Excel打开UTF-8编码CSV文件乱码的问题