求职大厂,却被面试官“毒打”:不会这五个技能的人,正在失去职场竞争力

编按

不知道正在学习Excel的小伙伴有没有这样的烦恼---认真快速学习了很多Excel知识,但是忘得也很快。其实Excel作为办公工具,要快速学习和掌握需要与实际工作应用相结合。今天我们可爱的作者给大家奉上Excel五大实操技巧,分分钟提升工作效率~

嗨喽嗨喽,大家好!今天小可给大家带来的是实际工作中常用的Excel操作技巧。例如,按单元格颜色对单元格计数、创建动态引用名称、统计不重复个数……学会这些操作,希望能让各位读者老爷远离加班,拿上高薪水!!!

1.根据单元格颜色进行统计

如图,A2:B9区域是2021年春晚节目单,我们要对该区域根据单元格颜色进行计数。要求统计出蓝色和红色、黄色单元格各多少个。

【第一步】选中D2单元格,然后点击【公式】选项卡下的【名称管理器】,在【名称管理器】对话框选择【新建】,接着弹出【新建名称】对话框。

在【名称】栏输入“颜色”,在【引用位置】输入公式:=GET.CELL(63,A2)*NOW()^0,最后点击【确定】按钮。

扫码入群,下载Excel练习文件,同步操作

GET.CELL函数可以取得目标单元格的一些信息,例如字体类型、字体大小、颜色、行高、列宽等等。

公式的第一个参数可以通过代号来返回信息的类别,例如本例中的“63”表示返回单元格的填充色的代码。

这里附上一些常用的代号及其含义~~~

第二个参数即指定目标单元格。NOW函数则利用它的易失性特点,当单元格颜色发生改变时,刷公式时可以得到新的结果。

【第二步】在D2单元格输入:=颜色,接着把公式向右向下做拖动,将公式填充至D2:E9单元格区域。再给这个区域添加一个朴实优雅的黑色边框~~~

如下图,在D13:F15区域是颜色的“信息”和“计数”,在F13单元格输入公式:=COUNTIF($D$2:$E$9,E13),再向下复制填充公式至F15单元格。就得到每种颜色单元格的个数了!

温馨提示:GET.CELL是宏表函数,包含宏表函数的工作簿要保存为“启用宏的工作簿”文件格式(.xlsm),并打开工作簿时允许启用宏

2.创建动态引用的几种方法

在日常工作时,经常会调用另外一个工作表的内容,我们若想实现调用后的数据跟随数据源数据的变动而变动。那么我们就需要创建动态引用举个例子,AB列是数据源,我要调用它们到DE两列。当数据源B13单元格输入“123”时,对应位置E13单元格也跟着变化,这就是动态引用。

方法一:OFFEST+COUNT

在D2单元格输入公式:=OFFSET(A2,,,COUNTA(A:A),1)&"",按<Ctrl+Shift+Enter>三键结束,再向右向下复制填充公式。

因为单元格的相对引用,公式的第一个参数A2跟着公式向右移动一列变为B2,所以E2单元格的公式为:=OFFSET(B2,,,COUNTA(B:B),1)&""

【公式解读】

①D列用OFFEST函数以A2为参照点,第一参数不向下移、第二参数不向左移,第三参数向下扩展COUNTA(A:A)行(A列空单元格的最后一行),第四参数向右扩展一行,以上面返回结果作为引用区域;E列同理。

②&””:作用是不让数据源的空单元格在调用到对应位置后显示为0。如下动图,若不在公式后面加上&””,则E2单元格的显示为0;若加上&””,则E2单元格显示为空单元格。

方法二:OFFEST+MACTH

在G2单元格输入公式:=OFFSET(A2,,,MATCH("座",A:A,1),1)&"",再向右向下复制填充公式。

由于单元格的相对引用关系, H2单元格公式为:=OFFSET(B2,,,MATCH("座",B:B,1),1)&""

【公式解读】

①G列用OFFEST函数以A2为参照点,不向下移、不向左移,向下扩展行MATCH("座",B:B,1)(A列非空单元格的最后一行),向右扩展一行;H列同理。

②&”” :作用是不让数据源的空单元格在调用到对应位置后显示为0。

3.统计不重复个数

统计不重复值在工作中的使用频率非常之高。

如图,要统计A列名称的不重复个数,结果显示在C2单元格

在C2单元格输入公式:=SUM(N(MATCH($A$2:$A$11,$A$2:$A$11,0)=ROW(1:10))),按<Ctrl+Shift+Enter>三键结束

【公式解读】①MATCH($A$2:$A$11,$A$2:$A$11,0)部分:精确查找出A2:A11区域姓名第一次出现的位置,当姓名出现多次时,MACTH函数只返回数据第一次出现的位置。内存数组结果如下图

MATCH($A$2:$A$11,$A$2:$A$11,0)=ROW(1:10)部分:分析可知,只有第一次出现的姓名返回的MACTH值才能与对应的数组ROW(1:10)相等所以返回的内存结果为

③最后用N函数将TRUE转为1,将FALSE转为0,再用SUM函数求和就得到了不重复姓名的个数。

4.制作图书自动目录

如下图,要为数据源制作章节目录,最终结果为目标图所示,目录为【数字+ . +数字】的形式。大家都知道如何在Word中制作目录,那你知道如何用Excel制作目录吗?来学习学习这个常用技巧吧!

【第一步】先把B列数据复制到E列

【第二部】在D1单元格输入公式:=IF(A1="",COUNTA($A$1:A1)&"."&(ROW()-MATCH("座",$A$1:A1,1)),A1),再向下复制填充公式。

【公式解读】

COUNTA($A$1:A1)部分:对A列中非空单元格计数,得到篇名为“章”的序号。

MATCH("座",$A$1:A1,1)部分:利用MATCH函数在$A$1:A1中升序查找,因为字符“座”是一个排序在后的相对较大字符,而第二个参数中的字符均小于该字符,因而公式结果将定位到$A$1:A1最后一个非空单元格所在的位置。

③(ROW()-MATCH("座",$A$1:A1,1))部分:ROW函数得到当前行号,再减去MACTH函数得到的行号,得到相应的小节号。

5.匹配查找简称

如下图所示,A列是一些专业术语的全称,要求根据D列所提供的相应简称,将A列的全称换为简称放到B列。

在B2单元格输入公式:=INDEX($D$2:$D$7,MATCH(1,COUNTIF(A2,REPLACE("*"&$D$2:$D$7&"*",3,,"*")),0))&"",按<Ctrl+Shift+Enter>结束,再向下复制填充公式.

【公式解读】

①REPLACE("*"&$D$2:$D$7&"*",3,,"*")部分:将D2:D7单元格所提供的简称前中后都加上通配符“*”,将此作为COUNTIF函数的第二参数。结果显示如下图:

②COUNTIF(A2,REPLACE("*"&$D$2:$D$7&"*",3,,"*"))部分:统计A2单元格是否能匹配加了通配符后的简称。如果能匹配,则返回结果1;反之,返回结果0;以A2单元格为例,该部分公式返回的内存数组为

③MATCH(1,COUNTIF(A2,REPLACE("*"&$D$2:$D$7&"*",3,,"*")),0)部分:MACTH函数精确查找出该内存数组“1”所在位置。以A2单元格为例,MATCH函数返回的结果为“1”。

④最后MATCH函数的结果作为INDEX函数在D2:D7区域返回的行数,以得到相应的简称。

今天的分享就到这里啦~你还有更多想学习的技巧请在下方留言哦~~~

今日互动话题

在评论区留下你的足迹叭~

你在日常工作中用得最多的Excel技巧是什么?

阅读推荐

关注我们,发现更多Excel优质教程

比VBA好用100倍!拆分工作表,用数据透视表5秒就搞定!

靠一只“猪”一秒拆分上千个工作表?!同事的骚操作看呆我......

不懂这个“人类高质量Excel技巧”,就不要轻易在简历上写“精通Excel”

大厂HR:有这种Excel思维的实习生,我真的想高薪留下来

小瞧日期函数的都被打脸了!同事用这个函数做Excel时间表,效率提高35%!

(0)

相关推荐