阅读是最好的习惯

【原】excel区间查询技巧:不一样的区间取值方法,来了解一下

【原】excel区间查询技巧:不一样的区间取值方法,来了解一下

编按:哈喽,大家好!在前面的文章中,给大家分享了9种常规的区间查询的方法,不知道大家还有印象吗?今天我们又来说区间查询,不过这次我们要分享的可不是常规的方法,是7种另类的区间查询的方法,保证小伙伴们是闻所未闻见所未见,如同打开新世界大门一般!赶紧来看看吧!

【原】excel复制粘贴:如何将数据粘贴到筛选区域中?

编按:哈喽,大家好!看到标题相信大家都会纳闷吧。复制粘贴不是excel中最简单,最基础的操作吗?怎么可能90%的人都不会呢?今天咱们要说的可不是普通的复制粘贴,而是将数据复制粘贴到筛选后的区域中。说到这里可能又有小伙伴们疑惑了,“小编,你可别逗我,数据是不能直接复制粘贴到筛选后的区域中的,这可是常识!”别着急,跟着小编继续往下看,你就知道了! ************ 如果我对你说,我可以将一个连续区域中的值,快速粘贴到筛选后不连续的区域中,你信吗?例如要将下图右侧连续的“目标值”列粘贴到筛选过 更多

Ctrl Q,Excel中最强大的快捷键,没有之一

添加数据条添加数据条添加色阶为大于某一数值的数据填充颜色在这里我们设置为大于500填充红色,首先选择数据区域,然后按ctrl+q选择大于然后在跳出的对话框中,左侧输入500,右侧选择需要设置的颜色即可为前10%填充颜色5.快速插入图表利用这个功能也是可以快速插入常见的五种图表的。首先点击数据的任何一个区域,然后按下快捷键Ctrl+Q,在右下角中点击图表然后选择需要的图表即可如果没有自己需要的图表也可以点击更多图表,进入图表的插入界面6.行方向汇总数据点击数据的任何一个区域,然后按ctrl+q调出 更多

【前言】

我们之前说过“等级评定、区间取值”的话题,记得当时给出了三大类,共九种函数的操作方法:《老是加班还没加班费?谁让你不会excel区间查询的三大套路!》,今天我们接着聊区间查询。只不过作者E图表述希望通过本篇阅读,可以让同学们学到一些少数人才会的“另类”操作。

【正文】

既然之前和大家说过关于区间取值的方法,那么今天就给大家换换“口味”,不再引用数字或者文本,本次案例我们来引用“非字体字符”,是什么呢?见下图:

“满天都是小星星”,这样评定等级的方式应该不陌生吧,很多的企业都会这样要求,它区别于“ABCD”式或者百分数形式的评级,优点就在于可以很直观的看出“孰强孰弱”。当然同学们也可以使用“微图表”操作,效果是一样的,只是我们通过本例,可以了解并练习一些函数。话不多言,直接开始。

方法一

G2单元格输入函数:

=LOOKUP(B3,{0,60,70,80,90,100},$F$3:$F$8)

下拉填充柄将函数填充至下方单元格,完成。

这是LOOKUP函数的标准用法,通过常量数组{0,60,70,80,90,100}和单元格区域F3:F8的匹配,达到区间取值的效果。只不过,我们这里引出的是“图形字符”。

方法二

是不是觉得方法一比较常规了,那再看看方法二:

G2单元格输入函数:

=INDEX($F$3:$F$8,MATCH(B3,{0,60,70,80,90,100}))

下拉填充柄将函数填充至下方单元格,完成。

INDEX+MATCH函数的经典用法,通过MATCH(B3,{0,60,70,80,90,100})确定“得分”在常量数组中的序号,作为INDEX被索引的序号,索引出单元格区域F3:F8中对应的图形字符。

方法三

上面的两个方法,是不是要被有的同学 “吐槽”,没有什么新意啊?其实基础的内容还是有必要掌握的,不积硅步无以至千里,下面就应同学们的要求,请看“方法三”:

G2单元格输入函数:

=TEXT(TEXT(TEXT(B3,”[>=100]★★★★★;[>=90]★★★★☆;0″),”[>=80]★★★☆☆;[>=70]★★☆☆☆;0″),”[>=60]★☆☆☆☆;☆☆☆☆☆”)

下拉填充柄将函数填充至下方单元格,完成。

这个函数的用法就不多见了吧。TEXT函数对于“单元格值的格式”来说是一个万能函数,我们选中某个单元格,按CTRL+1快捷键,弹出“设置单元格格式”窗口。

这里涉及的所有分类中,只有一种效果是TEXT函数实现不了的,其他的都可以用TEXT函数实现。关于TEXT函数的话题比较大,在之前的文章《Excel教程:最魔性的TEXT函数,看一眼就心动~》中,我们也介绍过,这里就不展开了,下次再给它开一个专题来讨论。

“方法三”是典型的用TEXT函数代替IF函数的用法(在之前的文章《如果函数有职业,TEXT绝对是变装女皇!》中有介绍过),TEXT函数代替条件函数用法的语法: 

TEXT(数值,”[条件1]显示格式;[条件2]显示格式;否则显示格式;文本显示格式”),我们可以看到TEXT函数的第二参数有四个部分组成,其中第四个格式是“文本格式”,因为我们本案例使用的是对数字的判断,所以这个格式可以省略,变成下面的语法:

TEXT(数值,”[条件1]显示格式;[条件2]显示格式;否则显示格式”),大家通过语法可以看出,前两个是判断条件的,当满足时返回需要的“小星星格式”,如果不满足的时候,我们规定了一个“0”的格式。在TEXT函数中这个“0”不是数字,而是一个“占位符”,返回的依然是数值本身。看到这里,我们需要记住一个规则:TEXT函数作为判断条件使用的时候,最多只能判断两个条件。当有第三个、第四个条件时,我们需要再使用一个TEXT函数来判断,还有更多的条件,以此类推。

再回到我们“方法三”给出的函数,一共6个条件,所以我们使用了3个TEXT函数嵌套判断,完成了效果。

方法四

跳过刚刚“烧脑”的方法三,让我们来看看轻松一些的方法四。

G2单元格输入函数:

=MID(“★★★★★☆☆☆☆☆”,7-MATCH(B3,{0,60,70,80,90,100}),5)

下拉填充柄将函数填充至下方单元格,完成。

函数解析:我们列出★★★★★☆☆☆☆☆这样的一个图形字符串,通过MID函数“断位取值”的思路,得到我们的需求。

方法五

在方法四中,我们利用了数学的思路,得到了结果。在方法五中同样也适用,我们利用OFFSET函数来解决这个需求。

G2单元格输入函数:

=OFFSET($F$2,MAX(1,INT((B3-60)/10+2)),,,)

下拉填充柄将函数填充至下方单元格,完成。

INT((B3-60)/10+2)的作用,就是为了确定OFFSET函数的第二参数向下移动的行数,列表如下:

这样OFFSET就可以根据分数,确定从基础单元格F2开始,下移的行数,第三参数为空,默认为0;第四、五参数为空,默认为1,这样就形成了我们上面的函数,得到需求的效果。

方法六

G2单元格输入函数:

=INDEX(★☆,MAX(1,INT((B3-60)/10+2)))

下拉填充柄将函数填充至下方单元格,完成。

“方法六”的做法,和“方法五”类似,写出这个案例,主要是为了让同学们能够学会一个“看”函数的习惯。在这个函数中,有一个★☆图形字符串,可是这个字符串既不是“数字”,也没有加英文状态的双引号,作者E图表述教给大家一个经验:在公式函数中,如果看到一个没有加英文状态双引号的“字符串”时,十有八九这是一个“自定义名称”。我们本方法就是用了这个技巧。

按CTRL+F3,弹出“名称管理器”窗口,点击“新建”按键,按下图设置名称即可,再用MAX+INT的方式确定索引号(同方法五),再用INDEX索引出对应值即可。

方法七

G2单元格输入函数:

=REPT(“★”,MAX(0,(B3-60)/10+1))&REPT(“☆”,5-INT(MAX(0,(B3-60)/10+1)))

下拉填充柄将函数填充至下方单元格,完成。

这个就是今天重点想给大家介绍的一个函数——REPT函数,这个函数只有两个参数,其功能是根据指定次数重复文本录入。

语法:REPT(TEXT值,重复的次数)

这里需要注意一点:重复的次数,可以通过计算得到,如果得到的是小数,REPT函数默认只取整数部分,即ROUNDDOWN函数的效果,例如:

根据这个特性,我们上面公式中计算重复次数的方式,才有意义。这里面的“数学思维”你能看懂吗?

【编后语】

EXCEL是统计、是数据分析、是“算数”的软件,每一个EXCELER的操作都应该和数学有关,即便我们处理文本字符串,这其中有的时候同样离不开“数学的思维”。这个思路希望可以给各位同学,在工作上带来一些新的创意,可以让工作更轻松,更有“乐趣”。

****部落窝教育-excel区间取值方法****

原创:E图表述/部落窝教育(未经同意,请勿转载)

【原】excel复制粘贴:如何将数据粘贴到筛选区域中?

编按:哈喽,大家好!看到标题相信大家都会纳闷吧。复制粘贴不是excel中最简单,最基础的操作吗?怎么可能90%的人都不会呢?今天咱们要说的可不是普通的复制粘贴,而是将数据复制粘贴到筛选后的区域中。说到这里可能又有小伙伴们疑惑了,“小编,你可别逗我,数据是不能直接复制粘贴到筛选后的区域中的,这可是常识!”别着急,跟着小编继续往下看,你就知道了! ************ 如果我对你说,我可以将一个连续区域中的值,快速粘贴到筛选后不连续的区域中,你信吗?例如要将下图右侧连续的“目标值”列粘贴到筛选过 更多

Ctrl Q,Excel中最强大的快捷键,没有之一

添加数据条添加数据条添加色阶为大于某一数值的数据填充颜色在这里我们设置为大于500填充红色,首先选择数据区域,然后按ctrl+q选择大于然后在跳出的对话框中,左侧输入500,右侧选择需要设置的颜色即可为前10%填充颜色5.快速插入图表利用这个功能也是可以快速插入常见的五种图表的。首先点击数据的任何一个区域,然后按下快捷键Ctrl+Q,在右下角中点击图表然后选择需要的图表即可如果没有自己需要的图表也可以点击更多图表,进入图表的插入界面6.行方向汇总数据点击数据的任何一个区域,然后按ctrl+q调出 更多

赞(0) 打赏
未经允许不得转载:微精选 » 【原】excel区间查询技巧:不一样的区间取值方法,来了解一下
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏