Excel使用学习

将Unix 10位的时间戳转换为指定日期格式

=TEXT((P2+8*3600)/86400+70*365+19,"yyyy-mm-dd hh:mm:ss")

Excel 的时期实际上是序列值，它以1900-01-01为1，每过一天序列值+1；

Unix时间戳是从1970-01-01 00:00:00 UTC 开始到现在经过的秒杀；

所以在Excel中转换Unix的时间戳时需要+70年的时间；

• TEXT()，是Excel公式；

• P2，是时间戳所在单元格；

• 8*3600，为中国所在东八区时区，所以+8*3600 ；

• P2 + 8*3600，计算当前时区的时间（秒）；

• (P2+8*3600)/86400，转换单位为天；

• (P2+8*3600)/86400+70*365，加上1900到1970这70年；

• (P2+8*3600)/86400+70*365+19，闰年多出来的天数；

  1900年到1970年共17个闰年，考虑到Excel将1900-01-01作为1，那么公式应该+18，为什么要加19？

  答：这是Excel的一个BUG，1900年也被当做闰年了，因此需要多+1。

注意1：另外，在Excel的工具-> 选项->重新计算中，有个1904年日期系统，如果勾选这个选项，上面的公式应当将70改为66，即(P2+8*3600)/86400+66*365+19

注意2： 如果需要转换的Unix时间戳为13位的ms格式，那么公式P2部分需要先除1000，即(P2/1000 + 8*3600)/86400 + 66*365 + 19

转换效果如下：

1658746074	->	2022-07-25 18:47:54

Excel 删除筛选的内容

1. 通过筛选功能，在名字列筛选选择张三李四。
2. 选择将要删除的数据整行。
3. 通过快捷键Ctrl+G调出定位窗口→“定位”→选择“可见单元格”→“确定”。
4. 在所定位的行范围内右键选择删除。

这样就解决了删除excel中筛选的内容的问题了。

查询函数VLOOKUP

vlookup

• 公式基础使用

  vlookup(用哪一列查询,在哪个表中的什么范围使用,取第几列[需要多列，可以使用{}括号，用逗号分隔],精确匹配/模糊匹配)

  vlookup(A2,Sheet1!A:E,{2,5,3},0)  表示用A来查询，在Sheet1!A:E范围查询，取2,5,3三列，精确匹配。 该函数会自动扩展三列。

• iferror(正确的值,出错后给出的默认值（例如：””）)

• 下拉填充，选择不带格式填充（这个可以解决填充后覆盖原有行的颜色或者其他格式）

• 常量数组，

  • 常规写函数后，按住F9，这样会将依赖的表，自动转化为常量数组。
  • 好处：此时可以删除依赖的辅助表中的数据，结果也不会出错。

if，可以嵌套