专栏首页猴子聊数据分析做完这套面试题,你才敢说懂Excel
原创

做完这套面试题,你才敢说懂Excel

下面的题目来自一份商品专员的面试题,其中有涉及到条件格式、自定义排序、数据验证制作下拉菜单、查找引用类函数、文本提取函数等等技能。

满满的干货技能可不是商品专员“专属”,如果你能熟练掌握,在平日工作中,肯定也会事半功倍,下面一起来看看吧。

问题1:将“销售员ID”列重复项标记出来

对重复项进行标记,也就是说判断单元格的值是否有重复,有重复,即进行标记。因此在这里可以用到【条件格式】功能。

选中“销售员ID”列,【条件格式】-【突出显示单元格规则】-【重复值】,在弹出的【重复值】设置窗口里,可对重复值的单元格格式进行设置。

最终效果如下,重复出现的销售员ID,就会标识出来。

条件格式除了可以对重复项进行格式设置,还可以对大于某范围的、小于某范围的、介于某范围等等的单元格进行设置,甚至还可以自定义条件规则。

条件格式拓展学习:如何使复杂的数据一目了然?

问题2:按“产品线”进行升序排列

题目要求对“产品线”进行升序排列,首先选定“产品线”列,然后【排序和筛选】-【升序】,在弹出的“排序提醒”窗口里,选择【扩展选定区域】

【扩展选定区域】也就是对排序的区域进行扩展,使得整个表格都进行相应的排序,除了对“产品线”进行排序外,其他列也会对应地跟着排序。

如果勾选了“以当前选定区域进行排序”,指的是只将选定的区域进排序,就只对“产品线”列进行排序,而其他列是不会动的。

最终结果如下图:

实际工作中,我们遇到的排序要求,可能不只是单纯的“降序”或“升序”,而是希望按指定的顺序来排列。例如,使“产品线”的产品按照电脑用品-家居园艺-户外运动-工业用品-工艺收藏来排序,这里就需要用到“自定义排序”功能了。

单击销售记录表内任一单元格-【排序和筛选】-【自定义排序】

在弹出的“排序”窗口中,“主要关键字”选择“产品线”,因为我们是对“产品线”列进行排序;“排序依据”,选择“单元格值”,根据单元格里的值进行排序;“次序”选择“自定义序列”,因为我们希望产品线的值能按我们自定义的顺序来排列。

然后在“自定义序列”窗口中,按照想要的顺序先后输入序列,注意每输入完成一个后,要按enter键换行,再输入另一个。

最终结果如下,即产品线就是按照我们自定义的序列:电脑用品-家居园艺-户外运动-工业用品-工艺收藏来排序了。

问题3:将产品线固定为:电脑用品、工业用品、工艺收藏、户外运动、家居园艺,并制作为下拉选项,输入其他值时提醒:非有效产品线

如上图所示,需求是对“产品线”列制作下拉菜单,使得产品线为固定的几个选项。而且,在输入其他值时会有提醒。

也就是说,我们给“产品线”列建了一个统一的数据录入规范。保证原始数据录入的规范可以为我们后续数据的整理节省很多时间,想必这个需求大家在工作中也经常会遇到。而这个功能的实现,在Excel里叫做【数据验证】

选定要进行数据验证的单元格区域-【数据】-【数据验证】

在弹出的“数据验证”对话窗口里,选择“序列”、“来源”处。可以手动输入题目要求的序列:电脑用品、工业用品、工艺收藏等。注意,各个序列值之间用英文逗号隔开。

也可以像本案例操作中一样,因为已在Excel单元格中输入了相对应的序列值 ,所以,直接选择序列值所在的该单元格区域即可。

如图,设置了数据验证的区域,鼠标单击时,就会在右边出现下拉按钮,点击,就会弹出我们刚才设置好的序列菜单。

那如果输入其他值时想要有提醒,又该如何设置呢?

对设置了数据验证的区域进行“出错警告”:选定设置了数据验证的区域-【数据】-【数据验证】,在弹出的“数据验证”对话窗口里的“出错警告”里进行设置。“标题”,可根据实际需要起个名称;“错误信息”,就是当别人输入其他值时你希望能弹出的提示信息,如本案例为:非有效产品线。

设置好出错警告后,当我在设置了数据验证的区域里输入不是“电脑用品,工业用品,工艺收藏,户外运动,家居园艺”中的任意一个值时,就会弹出提示,如下图:

问题4:为销售员匹配对应的小组,并将小组-销售员-产品线组合到一个单元格

如上图所示,表2是每个成员对应的小组信息,要求为表1的销售员匹配上对应的小组信息。

说到“匹配”,很多人第一时间估计就会想到用vlookup函数。vlookup函数在查找匹配上,的确是一个利器,但vlookup函数在查询的时候只能从左往右查询,且查询对象所在的列,必须要在查询区域的第一列,也就是说,只能通过A列来查询B列或其它列,而不能通过B列来逆查询A列。

所以在这里,并不是用vlookup而是用index+match函数来实现需求。首先,用match函数来定位位置信息,查找出表1中的销售员,在表2中的位置。如在单元格D24中输入公式:

=match(B24,$K$24:$K$28,0)

意思是,对B24单元格的值,即“高小明”,在区域K24:K28中,也就是在表2的“成员”列中进行匹配,看他是位于第几。返回结果 是4。即高小明在成员列中,是第4个。

用match匹配出位置信息后,我们再嵌套index引用函数,将match匹配出来的位置信息作为index的第二个参数传回给index。公式如下:

=index($J$24:$J$28,match(B24,$K$24:$K$28,0))

意思是,在区域J24:J28中,也就是表2的“小组”列中进行查找引用,引用第几个值呢?就是根据match函数的结果来进行“扫描”。如本案例中,在前边的步骤已经用match函数定位到“高小明”位于第4行,所以,最终引用返回的是“小组”列中第4行的值,也就是“战无不胜组”。公式向下填充,即可返回所有销售员对应的组别信息。

第二个需求是将小组-销售员-产品线组合到一个单元格

合并文本,如果不想用函数,最简单的方式就是用文本连接符&,即把需要连接合并的文本用这个&连起来即可。如下:

但是,如果要连接比较多列的文本,用文本连接符来连,虽然简单,但容易出错,特别是中间还要连接分隔符。

所以,我们还可以用到文本连接函数textjoin,其语法如下:

=textjoin(文本分隔符,是否忽略空值,要连接的文本)

所以,我们的公式可以这样写:

=textjoin("-",,D24,B24,C24)

其中第2个参数,默认true,可以省略不写。

问题5:因工作需要分析财务付款时间,请将下表中的付款时间提取出来

如上图所示,就是需要从“操作时间”列里对应的内容中,把“付款”的对应时间提取出来。

我们观察一下“操作时间”列里的文本规律,发现每个操作都会有对应的文本标识,如“创建”时间前,是“创建”这两个关键字。“付款”时间前,是“付款”这两个关键字。

我们要把付款的时间提取出来,所以就需要确定“付”字在文本中的位置,然后提取出固定长度的文本时间即可。

所以,我们先用文本查找函数find,确定“付”所处的位置,然后再用mid文本提取函数来进行提取。

=mid(F3,find("付",F3)-1,23)

公式如何理解呢?由里往外看。首先,用find函数,查找“付”字在文本里的位置;然后再嵌套mid函数。

Mid函数语法为:

=mid(要进行文本提取的文本,从第几位字符开始提取,提取多少个字符)

因为我们已经利用find函数找到了“付”字所在的文本位置,所以,我们就从其所在位置-1处开始进行提取,提取多少个字符呢?我们手动数一下,“付款:2019-05-13 09:14:35”这一串文本字符串的字符长度为23,所以,就是提取23个字符。

6.总结

上面介绍到的Excel操作技能,如运用数据验证来制作下拉菜单、index+match函数的强大查找引用、文本提取类函数等,都是日常工作中最常用到的,所以小伙伴们赶紧实操起来吧。

推荐:人工智能时代的必学技能

下面的题目来自一份商品专员的面试题,其中有涉及到条件格式、自定义排序、数据验证制作下拉菜单、查找引用类函数、文本提取函数等等技能。

满满的干货技能可不是商品专员“专属”,如果你能熟练掌握,在平日工作中,肯定也会事半功倍,下面一起来看看吧。

问题1:将“销售员ID”列重复项标记出来

对重复项进行标记,也就是说判断单元格的值是否有重复,有重复,即进行标记。因此在这里可以用到【条件格式】功能。

选中“销售员ID”列,【条件格式】-【突出显示单元格规则】-【重复值】,在弹出的【重复值】设置窗口里,可对重复值的单元格格式进行设置。

最终效果如下,重复出现的销售员ID,就会标识出来。

条件格式除了可以对重复项进行格式设置,还可以对大于某范围的、小于某范围的、介于某范围等等的单元格进行设置,甚至还可以自定义条件规则。

条件格式拓展学习:如何使复杂的数据一目了然?

问题2:按“产品线”进行升序排列

题目要求对“产品线”进行升序排列,首先选定“产品线”列,然后【排序和筛选】-【升序】,在弹出的“排序提醒”窗口里,选择【扩展选定区域】

【扩展选定区域】也就是对排序的区域进行扩展,使得整个表格都进行相应的排序,除了对“产品线”进行排序外,其他列也会对应地跟着排序。

如果勾选了“以当前选定区域进行排序”,指的是只将选定的区域进排序,就只对“产品线”列进行排序,而其他列是不会动的。

最终结果如下图:

实际工作中,我们遇到的排序要求,可能不只是单纯的“降序”或“升序”,而是希望按指定的顺序来排列。例如,使“产品线”的产品按照电脑用品-家居园艺-户外运动-工业用品-工艺收藏来排序,这里就需要用到“自定义排序”功能了。

单击销售记录表内任一单元格-【排序和筛选】-【自定义排序】

在弹出的“排序”窗口中,“主要关键字”选择“产品线”,因为我们是对“产品线”列进行排序;“排序依据”,选择“单元格值”,根据单元格里的值进行排序;“次序”选择“自定义序列”,因为我们希望产品线的值能按我们自定义的顺序来排列。

然后在“自定义序列”窗口中,按照想要的顺序先后输入序列,注意每输入完成一个后,要按enter键换行,再输入另一个。

最终结果如下,即产品线就是按照我们自定义的序列:电脑用品-家居园艺-户外运动-工业用品-工艺收藏来排序了。

问题3:将产品线固定为:电脑用品、工业用品、工艺收藏、户外运动、家居园艺,并制作为下拉选项,输入其他值时提醒:非有效产品线

如上图所示,需求是对“产品线”列制作下拉菜单,使得产品线为固定的几个选项。而且,在输入其他值时会有提醒。

也就是说,我们给“产品线”列建了一个统一的数据录入规范。保证原始数据录入的规范可以为我们后续数据的整理节省很多时间,想必这个需求大家在工作中也经常会遇到。而这个功能的实现,在Excel里叫做【数据验证】

选定要进行数据验证的单元格区域-【数据】-【数据验证】

在弹出的“数据验证”对话窗口里,选择“序列”、“来源”处。可以手动输入题目要求的序列:电脑用品、工业用品、工艺收藏等。注意,各个序列值之间用英文逗号隔开。

也可以像本案例操作中一样,因为已在Excel单元格中输入了相对应的序列值 ,所以,直接选择序列值所在的该单元格区域即可。

如图,设置了数据验证的区域,鼠标单击时,就会在右边出现下拉按钮,点击,就会弹出我们刚才设置好的序列菜单。

那如果输入其他值时想要有提醒,又该如何设置呢?

对设置了数据验证的区域进行“出错警告”:选定设置了数据验证的区域-【数据】-【数据验证】,在弹出的“数据验证”对话窗口里的“出错警告”里进行设置。“标题”,可根据实际需要起个名称;“错误信息”,就是当别人输入其他值时你希望能弹出的提示信息,如本案例为:非有效产品线。

设置好出错警告后,当我在设置了数据验证的区域里输入不是“电脑用品,工业用品,工艺收藏,户外运动,家居园艺”中的任意一个值时,就会弹出提示,如下图:

问题4:为销售员匹配对应的小组,并将小组-销售员-产品线组合到一个单元格

如上图所示,表2是每个成员对应的小组信息,要求为表1的销售员匹配上对应的小组信息。

说到“匹配”,很多人第一时间估计就会想到用vlookup函数。vlookup函数在查找匹配上,的确是一个利器,但vlookup函数在查询的时候只能从左往右查询,且查询对象所在的列,必须要在查询区域的第一列,也就是说,只能通过A列来查询B列或其它列,而不能通过B列来逆查询A列。

所以在这里,并不是用vlookup而是用index+match函数来实现需求。首先,用match函数来定位位置信息,查找出表1中的销售员,在表2中的位置。如在单元格D24中输入公式:

=match(B24,$K$24:$K$28,0)

意思是,对B24单元格的值,即“高小明”,在区域K24:K28中,也就是在表2的“成员”列中进行匹配,看他是位于第几。返回结果 是4。即高小明在成员列中,是第4个。

用match匹配出位置信息后,我们再嵌套index引用函数,将match匹配出来的位置信息作为index的第二个参数传回给index。公式如下:

=index($J$24:$J$28,match(B24,$K$24:$K$28,0))

意思是,在区域J24:J28中,也就是表2的“小组”列中进行查找引用,引用第几个值呢?就是根据match函数的结果来进行“扫描”。如本案例中,在前边的步骤已经用match函数定位到“高小明”位于第4行,所以,最终引用返回的是“小组”列中第4行的值,也就是“战无不胜组”。公式向下填充,即可返回所有销售员对应的组别信息。

第二个需求是将小组-销售员-产品线组合到一个单元格

合并文本,如果不想用函数,最简单的方式就是用文本连接符&,即把需要连接合并的文本用这个&连起来即可。如下:

但是,如果要连接比较多列的文本,用文本连接符来连,虽然简单,但容易出错,特别是中间还要连接分隔符。

所以,我们还可以用到文本连接函数textjoin,其语法如下:

=textjoin(文本分隔符,是否忽略空值,要连接的文本)

所以,我们的公式可以这样写:

=textjoin("-",,D24,B24,C24)

其中第2个参数,默认true,可以省略不写。

问题5:因工作需要分析财务付款时间,请将下表中的付款时间提取出来

如上图所示,就是需要从“操作时间”列里对应的内容中,把“付款”的对应时间提取出来。

我们观察一下“操作时间”列里的文本规律,发现每个操作都会有对应的文本标识,如“创建”时间前,是“创建”这两个关键字。“付款”时间前,是“付款”这两个关键字。

我们要把付款的时间提取出来,所以就需要确定“付”字在文本中的位置,然后提取出固定长度的文本时间即可。

所以,我们先用文本查找函数find,确定“付”所处的位置,然后再用mid文本提取函数来进行提取。

=mid(F3,find("付",F3)-1,23)

公式如何理解呢?由里往外看。首先,用find函数,查找“付”字在文本里的位置;然后再嵌套mid函数。

Mid函数语法为:

=mid(要进行文本提取的文本,从第几位字符开始提取,提取多少个字符)

因为我们已经利用find函数找到了“付”字所在的文本位置,所以,我们就从其所在位置-1处开始进行提取,提取多少个字符呢?我们手动数一下,“付款:2019-05-13 09:14:35”这一串文本字符串的字符长度为23,所以,就是提取23个字符。

6.总结

上面介绍到的Excel操作技能,如运用数据验证来制作下拉菜单、index+match函数的强大查找引用、文本提取类函数等,都是日常工作中最常用到的,所以小伙伴们赶紧实操起来吧。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 腾讯这套SpringMvc面试题你懂多少(面试必备)

    答:SpringMvc 是 spring 的一个模块,基于 MVC 的一个框架,无需中间整合层来整

    美的让人心动
  • 想去Google做AI?先看完这套面试指南(附面试题)

    用户1737318
  • 还自己写代码?VBA录制宏了解下

    我们回顾下之前的内容,主要涉及到变量、强制申明、For循环结构、IF分支结构、程序调试以及重要的字符串知识等内容,可以看到我们已经学到了很多实用性的VBA内容。

    猴子数据分析
  • 数据分析经典语录

    【数据分析三字经】①学习:先了解,后深入;先记录,后记忆;先理论,后实践;先模仿,后创新; ②方法:先思路,后方法;先框架,后细化;先方法,后工具;先思考,后动...

    小莹莹
  • PowerBI 是不是商业智能领域的法拉利 你怎么看

    这里会分成三个部分: 1、什么是当下靠谱的商业智能 2、这种商业智能和我们有什么关系 3、我们如何获得这种能力

    BI佐罗
  • 这些年,Excel不知道坑死了多少人,你有幸免吗?

    近日一篇名为 Excel界地震 微软宣布 跨4代人34岁的 VLOOKUP 退休 刷爆朋友圈,几小时就像病毒一样传播起来并很快得到了10W+的阅读,太香了。几乎...

    BI佐罗
  • 数据分析没有思路怎么办

    数据只是数据分析的素材,数据分析则是一个系统工作。就像我们想做一个大盘鸡招待客人,光从菜市场买一只冰鲜鸡回来是远远不够的,还得:

    接地气的陈老师
  • 根据此文的步骤,你也可以「独立完成一个项目的测试 + 发布」

    很多同学,工作了五六年,都没有机会(也许是:不敢)独立负责一个完整项目的测试(独立负责一个项目测试后的上线流程,机会就更少了) 。

    IDO老徐
  • 听说这个爬虫面试题很难?看完你就知道怎么做了

    最近(2019年6月)有一个爬虫面试题(http://shaoq.com:7777/exam)在圈内看起来挺火的,经常在各个爬虫群里看到它被提到,而几乎所有提到...

    云爬虫技术研究笔记

扫码关注云+社区

领取腾讯云代金券