身份证号码是个人最重要信息,单位人事部门为了对每个员工信息进行保密,往往在常用的EXCEL工作表里隐藏身份证号码有部分数字,如:函数实现公式:在D2单元格,输入公式:=REPLACE(C3,7,8,'********'),再往下填充,即可隐藏所有身份证号码部分数字。该公式的解释是:对C3单元格的......
12-29 84 excel公式技巧 excel函数公式 Excel常用函数 excel教程
做人事管理的某位朋友,说想要用函数查询单位不同部门员工信息,随意输入单位,所在部门的员工信息会自动查询,结果如下:上中的查询效果是由函数VLOOKUP实现的,过程如下解:第一步:建立辅助列在“部门”前增加一列,在A3单元格输入公式“=COUNTIF($B$3:B3,$H$3)”。其中:$B$3:......
12-29 468 excel公式技巧 excel函数公式 Excel常用函数 excel教程
朋友留言:您好,请教一下怎么用LEFT公式把A列中的工号和员工姓名分开到B列(纯数字)和C列(RIGHT公式,纯中文姓名)?谢谢!数据如下:实现方法第一种方法:函数法第一步先将文本分离出来:在C2单元格输入公式:“=RIGHT(A2,LENB(A2)-LEN(A2))”,向下填充,即可提取所有员工姓......
12-29 136 excel公式技巧 excel函数公式 Excel常用函数 excel教程
今天学习群里一位朋友传来一副图片,问:如何把分布不规则的字母部分单独提取出来?像这种字母、汉字、数字无规律分布的内容,如果想把字母、汉字、数字单独提取出来,利用公式,是非常难的。下面,教大家一种Excel与Word双剑合璧的方法,简单方便,一学即会!提取所有字母将A1:A3单元格的内容复制到空白WORD......
12-29 423 excel公式技巧 excel函数公式 Excel常用函数 excel教程
VLOOKUP函数要求查询值必须位于查询区域的首列。比如,下中的数据:原数据区,“部门”位于“姓名”的左侧,而要求按照姓名去查询部门,那直接用VLOOKUP函数进行查找,是查不到结果的。我们需要构建一个新的查询数据区,将“姓名”置于“部门”的左侧。这种新的查询数据区,可以通过IF和CHOOSE两个函数来实现......
12-29 292 excel公式技巧 excel函数公式 Excel常用函数 excel教程
Excel没有提供忽略隐藏列汇总的函数,比如下:虽然E列与G列隐藏了,但E、G两列的数据还是参与了求和汇总。如何使隐藏列的数据不参与汇总呢?效果如下动:方法解释第一步:建立辅助行在第11行建立辅助行,B11中输入函数“=CELL('width',B1)”,向右填充,计算出B列到G列各列的列宽,如......
12-29 119 excel公式技巧 excel函数公式 Excel常用函数 excel教程
今天朋友在根据身份证号码筛选员工信息的时候出现错误:同一个身份证号码,高级筛选出好几位员工,错误出现过程如下动:这是什么原因?问题解析原因分析高级筛选的结果如下:分析结果,我们可以看出:筛选出的三位员工,他们的身份证号码的前15位是一致的。原因是Excel对同一串数字的有效辨识,仅限于15位......
12-29 418 excel公式技巧 excel函数公式 Excel常用函数 excel教程
在很多时候,统计员工工龄时,需要精确到天数。精确到天数的计算,有一个很好的函数——DATEDIF,这个函数在EXCEL里是隐藏的。选中C3:E12区域,输入函数:“=DATEDIF(B3:B12,TODAY(),{'y','ym','md'})”,按“CTRL+SHIFT+ENTER”组合键结束,即可计算出......
12-28 360 excel公式技巧 excel函数公式 Excel常用函数 excel教程
最近几天,不止一位朋友问同一个问题:有很多工作簿,是从下属分部门收集上来的,怎样实现数据汇总计算?那今天就详细一多工作簿合并的步骤。今天的步骤,是用EXCEL2016完成的。EXCEL2016提供了强大的“PowerQuery”,即数据查询功能,能帮助我们不用费时费人力粘贴、不用多数人接受不了的VBA、不用专业......
12-28 71 excel公式技巧 excel函数公式 Excel常用函数 excel教程
EXCEL做数据分析的时候,经常会遇到分段统计数量的问题。今天以学生成绩分析为例,来述如何使用FREQUENCY函数简单方便的统计各分数段的人数。示例数据如下图:关键操作函数实现选中E2:E6单元格区域,输入公式:“=FREQUENCY(B2:B16,{60,70,80,90}-0.1)”,......
12-28 280 excel公式技巧 excel函数公式 Excel常用函数 excel教程
朋友留言,要学习制作“带照片的员工信息查询表”,输入员工编号,会自动跳出该员工的所有信息,包括照片。类似下面的动:为了不侵犯肖像权,来做植物信息查询表了。视频解关键操作原始信息表:(照片的批量导入,参考往期Excel工作表中批量插入照片)数据有效性(数据验证)制作编号下拉菜单在B1......
12-27 200 excel公式技巧 excel函数
很多时候,数据输入并不规范,比如下表中的员工姓名和业绩挤在一个单元格里,要求统计业绩最大值。这种不规范的数据并不是不能统计,只是给统计带来了麻烦。视频解关键操作公式实现在C2单元格输入公式:{=MAX((SUBSTITUTE(B2,ROW($1:$100),)B2)*ROW($1......
12-27 337 excel公式技巧 excel函数 excel函数技巧
作为管理或者统计工作者,往往从各个部门收集上来的数据填写非常不规范,比如下:A列中同样的设备,填写的名称不一样,这为后期的统计与分析带来麻烦。我们要把这些不规范的设备名称改写成标准名称。关键操作第一步:建立关键字与标准名称对应表首先对不规则的商品名称分析,提取出关键字,建立关键字与标准名称之间的对应关......
12-27 101 excel公式技巧 excel函数公式 Excel常用函数 excel教程
一位做电商数据分析的朋友说,他要对所管理的六个仓库的销售额进行对比统计,统计出每个仓库的最高与最低销售额。他有几万行的数据,简化到下面几行,以方便清楚统计公式。公式实现在E2单元格输入公式:“=MAX(IF($A$2:$A$16=D2,$B$2:$B$16))”,按“Ctrl+Shift+Enter”结束,......
12-27 84 excel公式技巧 excel函数公式 Excel常用函数 excel教程
EXCEL输入数据时,经常会输入不规范或者无效的数据,对数据的统计工作带来很大的麻烦。数据验证能够建立特定的规则,限制单元格可以输入的内容,从而规范数据输入,提高数据统计与分析效率。数据验证,在EXCEL2010及以前的版本中称为“数据有效性”。使用示例一、规范性别输入利用数据验证输入性别,不仅数据规范......
12-27 223 excel公式技巧 excel函数公式 Excel常用函数 excel教程
一位负责人事管理的朋友传来数据表,简化如下:要求:把三个及以上月份考核业绩低于6的员工姓名标识出来。操作过程1、选定A3:A18区域;2、【开始】——【条件格式】—【新建规则】,如下图:3、在【新建格式规则】对话框中选择【使用公式确定要设置格式的单元格】;4、在【为符合此公式的值设置格式......
12-27 470 excel公式技巧 excel函数公式 Excel常用函数 excel教程
下表是某电子产品商店五天内的销售情况,需要统计,在这五天内,一共销售出了几种商品。公式实现在E2单元格输入公式:“=SUM(1/COUNTIF(B2:B16,B2:B16))”,组合键结束。结果如下:公式解析“=SUM(1/COUNTIF(B2:B16,B2:B16))”其中:C......
12-27 414 excel公式技巧 excel函数公式 Excel常用函数 excel教程
下图是某电商各仓库各种产品的出货量,要求统计宁波仓鼠标出货量大于1000且小于5000的出货量总和。 公式实现在F5单元格输入公式:=SUMIFS(D2:D15,B2:B15,'宁波仓',C2:C15,'鼠标',D2:D15,'>1000',D2:D15,'<5000')。&nbs......
12-27 395 excel公式技巧 excel函数公式 Excel常用函数 excel教程
我们在EXCEL数据表中录入数据时,新添加数据单元格是默认不加边框,而为吧保持表格的完整与美观,我们经常手工添加边框,如下动图: 这样在某种程度上降低了我们数据处理的效率。今天,韩老师讲两种方法,实现添加数据,自动智能添加边框。方法一:条件格式条件格式设置,效果如下动图:......
12-27 121 excel公式技巧 excel函数公式 Excel常用函数 excel教程
我们经常在进行数据处理的时候,经常会遇到阿拉伯数字与中文数字之间的转换,尤其遇到“钱”的问题时。而EXCEL提供的设置单元格格式,根本满足不了这种需求。今天跟大家利用NUMBERSTRING和TEXT函数实现数字在阿拉伯与中文格式之间的转变。阿拉伯转中文数字阿拉伯数字转中文数字常用的两种函数是NUMBE......
12-27 63 excel公式技巧 excel函数公式 Excel常用函数 excel教程