编按：

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

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

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

第一步：选中D2单元格，然后点击【公式】选项卡下的【名称管理器】，在【名称管理器】对话框选择【新建】，接着弹出【新建名称】对话框。在【名称】栏输入“颜色”，在【引用位置】输入公式：=GET.CELL(63,A2)*NOW()^0，最后点击【确定】按钮。

GET.CELL函数可以取得目标单元格的一些信息，例如字体类型、字体大小、颜色、行高、列宽等等。公式的第一个参数可以通过代号来返回信息的类别，例如本例中的“63”表示返回单元格的填充色的代码；

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

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

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

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

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

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

在D2单元格输入公式：=OFFSET(A2,,,COUNTA(A:A),1)&""，按三键结束，再向右向下复制填充公式。
因为单元格的相对引用，公式的第一个参数A2跟着公式向右移动一列变为B2,所以E2单元格的公式为：=OFFSET(B2,,,COUNTA(B:B),1)&""

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

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

在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。

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

如图，要统计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函数求和就得到了不重复姓名的个数。

如下图，要为数据源制作章节目录，最终结果为目标图所示，目录为【数字+ . +数字】的形式。
大家都知道如何在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函数得到的行号，得到相应的小节号。

如下图所示，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区域返回的行数，以得到相应的简称。

今天的分享就到这里啦~咱们下次再见~~~

本文配套的练习课件请加入QQ群：902294808下载。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

扫下方二维码关注公众号，可随时随地学习Excel：

相关推荐：

Excel实用案例：SUBSTITUTE嵌套函数对文本单元格的判断和计算

Excel数字提取技巧:从包含文字的单元格中提取所有数字的万能公式

Excel数字提取技巧:从无规律文本中提取手机号的5种方法

Excel数字提取技巧:用简单公式从混合文本中提取数字的3种情景

版权申明：

本文作者小可；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。