蓁祎家进步中

# Excel

返回：office

# 一个身份证号，生日、年龄、性别、籍贯、退休时间都有了

🔝🔝 Excel

  • 首先咱们来提取出生年月，D2输入以下公式，向下复制。=--TEXT(MID(B2,7,8),"0-00-00")
    由于TEXT函数的结果是文本型的内容，所以加上两个小减号 -- ，也就是计算负数的负数，通过四则运算，变成真正的日期序列。这样做的目的是为了便于后续的统计汇总。
  • 接下来咱们要计算年龄，计算年龄时，可以借助刚刚已经提取出的出生年月，E2单元格输入以下公式，向下复制。=DATEDIF(D2,"2020-1-1","y")
  • 领导除了关心年龄，更关心的应该就是性别了，使用以下公式，可以提取出员工的性别信息：=IF(MOD(MID(B2,17,1),2),"男","女")
  • 接下来，咱们要根据提取出的出生年月和性别以及职级信息，来计算退休年月，根据现有法规，男性退休年龄为60岁，女性干部退休年龄为55岁，女性年退休为50岁。G2输入以下公式，向下复制。=EDATE(D2,IF(F2="男",720,IF(F2&C2="女干部",660,600)))
  • 最后咱们再看看查找重复身份证号码的问题。I2输入以下公式，向下复制。=IF(COUNTIF(B:B,B2&"*")>1,"有重复","无重复")

# Excel文件档案管理系统

🔝🔝 Excel

=HYPERLINK("文件所处的文件夹路径/"&A2,"点击打开")

  • &A2的含义
    &的作用是连接字符，因为我们经过第一步已经得到了文件所处文件夹的路径，但是超链接必须要精确到哪个文件，所以最后要连接上文件名。A列有对应的文件名称，所以直接连接对应单元格的值就行。
  • “点击打开”的含义
    超链接会显示成“点击打开”这四个字，这是自定义的，如果你设置成“超链接”，在表格中，就会显示成“超链接”，

DIR *.* /B >文件名列表.CSV——获取路径下所有文件全名

# 函数

求和

# 求和

# SUMIFS

sumfis是用来多条件求和的，因条件不一样，其参数不固定,sumifs(求和区域,条件区域1,条件1,条件区域2,条件2...),至少是3个参数，既单条件求和,根据求和条件不一亲，也可以是5个，7个，9个参数....

  • 单条件求和
=SUMIFS(C:C,B:B,"电视机")
1

条件文本必须用双引号括起来
求和区域C，条件区域B，条件"电视机"
如果E6单元格的值就是电视机，可以直接引用，使用的公式是：

=SUMIFS(C:C,B:B,E6)
1
  • 多条件求和
=SUMIFS(C:C,B:B,"电视机",A:A,"杭州")
1

其中两个条件是没有先后顺序的，公式也可以是：

=SUMIFS(C:C,A:A,"杭州",B:B,"电视机")
1

当然和单条件求和一样，文本也可以引用单元格的位置

  • 模糊求和

需要求出华为手机的销售汇总，机型里面只要带了华为关键词的算，那么我们可以搭配通配符号*，输入的公式是：

=SUMIFS(C:C,B:B,"华为*")
1

当然如果某个单元格中的值是华为的话，可以使用公式：

=SUMIFS(C:C,B:B,E6&"*")
1

需要使用单元格&"*"连接起来

  • 多个条件值求和

前面的条件区域的值，都是对应1个条件的，比如机型里面，就只对应了华为，如果我们现在需要把华为P30和华为mate20这2个机型求出来的话

❶大部分小伙伴可能用两个SUMIFS公式加起来：

=SUMIFS(C:C,B:B,"华为P30")+SUMIFS(C:C,B:B,"华为Mate20")
1

❷也可以使用SUMPRODUCT和SUMIFS搭配使用：

=SUMPRODUCT(SUMIFS(C:C,B:B,{"华为P30","华为Mate20"}))
1
  • SUMIFS遇到时间值计算

要求最近三天华为的销售情况，因为今天是2019年7月6日，时间大于7月4日的就是需求，我们使用公式：

=SUMIFS(C:C,B:B,"华为*",A:A,">2019-7-4")
1

当遇到时间输入时间值时，可以这么写 当时间值位于单元格内的话，需要这么写：

=SUMIFS(C:C,B:B,"华为*",A:A,">"&E6)
1

如果使用的是时间函数的话，书写的格式和引用单元格的方式差不多，使用公式：

=SUMIFS(C:C,B:B,"华为*",A:A,">"&TODAY()-3)
1

所以遇到时间条件时：

">某个时间"
">"&单元格引用
">"&时间函数引用
1
2
3

# 三种快速求和

返回

  • Alt+=

1、鼠标先选择我们需要求和的单元格区域；
2、选择区域后按快捷键alt+=，这样即可对我们需要求和的空白单元格区域进行输入sum就和函数。

# 小技巧

# 给汉字增加拼音

1.word辅助

word中添加好之后，复制到excel中，采用公式：=PHONETIC(B2)

# 金额大小写转换

NUMBERSTRING

NUMBERSTRING(INT(A2),2)&"元"&NUMBERSTRING(MID(A2,LEN(INT(A2))+2,1),2)&"角"&NUMBERSTRING(MID(A2,LEN(INT(A2))+3,1),2)&"分"

# Ctrl+G

  • 快速核对两列数据差异
  • 快速批量查找并删除单元格批注
  • 批量删除表格中的图片

# word表格不变形地复制到excel

1、在Word文档点击“文件”菜单——另存为（或直接按【F12】），保存类型选择“网页(.htm;.html)”，将Word表格保存为网页格式。

2、启动Excel，点击“文件”菜单——打开，点击“浏览”找到刚才保存的网页文件打开，这时，我们会发现在Excel中打开的表格与Word中基本一致，有些地方稍作调整就行了。

# 二级联动下拉

excel1.gif

# 翻译

=FILTERXML(WEBSERVICE("http://fanyi.youdao.com/translate?&i="&A2&"&doctype=xml&version"),"//translation")

# 保护

返回顶部

# 整体数据保护

当前整个工作表希望只能被查看，可以在审阅选项卡下，点击保护工作表，然后直接输入一个密码就可以了，这样设置之后，整个工作表里面的内容都不能修改，只能查看了

# 部分数据保护

❶选中可以被修改的内容，在字体的扩充选项下，选择保护，然后取消锁定单元格
❷然后在审阅里面保护工作表，输入一个密码进行保护

这样设置之后，只有选中单元格中的内容能够修改，其它区域的内容进行修改的时候，会有一个错误提示。

# 输入密码才能修改

如果希望B列至D列的数据在修改前需要输入密码，才能修改，B列，C列，D列分别是三个主管进行填写的，它们都设置了不同的密码

制作过程：
在审阅选项止下，选择允许修订区域，然后选择每个区域，设置不同的权限密码，我们这里设置了3个区域，密码分别是1，2，3。最后点击保护工作表

# excel字符串连接的5种方式

  • 1、连接符&
  • 2、PHONETIC函数文本连接
  • 3、CONCATENATE 函数
  • 4、CONCAT-将区域文本（或单个文本）联接为一个字符串。
  • 5、TEXTJOIN-将从多个区域和/或字符串文本结合在一起，包括您指定将结合每个文本值之间的分隔符。