在工作,很多时候我们会使用EXCEL对众多数据进行分析和比较,今天小编就给大家介绍如何用REPT、ABS、ROUNDUP、IF函数做一个一目了然的数据对比表格,给你的报表加分。废话不多说,首先上结果图:
一、 REPT函数。
REPT函数:重复指定数目的指定文本内容。
参数:REPT(需要重复的文本,重复次数)
例如,我们让单元格重复显示5个井号:"#":
在单元格中输入:"=REPT("#",5)"
例如,我们让单元格重复显示6个竖线:"|":
在单元格中输入:"=REPT("|",6)"
二、 "&"字符串连接符:
"&"符号可以使两个字符串连接起来。
例如,我们在单元格中输入:"="hello "&"world!""
"&"运算符也可以连接多个单元格的字符串。
例如,我们在C1单元格显示连接A1的字符串和B1的字符串:
在C1单元格输入:"=A1&" "&B1"
三、 ABS函数。
ABS函数:返回绝对值。
例如:
四、 ROUNDUP函数。
ROUNDUP函数:向上舍入数字。
参数:ROUNDUP(数字,保留小数点后几位)
例如:ROUNDUP(3.1415,1)的值是3.2;ROUNDUP(3.1415,2)的值是3.15;ROUNDUP(3.1415,3)的值是3.142;ROUNDUP(3.1415,4)的值是3.1415;ROUNDUP(3.1415,0)的值是4。
根据例子,我们可以看出,ROUNDUP函数并不是四舍五入,而是后一位有数字,则前一位向上收1。
五、 IF函数。
关于IF函数,我们在前文已经有了非常详细的叙述,包括IF函数的应用(职场技术|在EXCEL中使用简单的IF语句来筛选数据)以及IF函数的嵌套使用(职场技术|用EXCEL随机函数RAND和IF函数制作口算练习表(上篇))。我们在这里仅大致介绍其参数构成:
参数:IF(逻辑表达式,当值为"真"时,当值为"假"时)
六、 RANK函数。关于RANK函数,我们在前文已经有了非常详细的叙述,实际上RANK函数就是返回一个数字在数列中的排名(职场技术|EXCEL中统计函数RANK和LARGE的使用方法(干货))。我们在这里仅大致介绍其参数构成:
参数:RANK(数字,数列,排序方式)
七、 案例一,下表是某公司的产品销售统计表,我们用REPT函数来实现条形图的显示:
首先,我们用竖线"|"的多少来表示产品销售数量,考虑到销售数量上万,那我们假定以2000的倍数来代表竖线的重复数目。另外,竖线的重复数目是个整数,那么,我们采用刚刚介绍的ROUNDUP函数来完成整数的生成。则在上表C3单元格输入:
=REPT("|",ROUNDUP(B3/2000,0))
然后,我们要在竖线构成的条形后面显示销量的名词,这里我们需要用到字符串连接符"&"和排名函数RANK。
获得B3单元格在B列的名词,我们需要输入:RANK(B3,$B$3:$B$12)
用字符串连接符"&"连接函数后,C3单元格的表达式变为:
=REPT("|",ROUNDUP(B3/2000,0))&"第"& RANK(B3,$B$3:$B$12)&"名"
选中C3单元格然后下拉,得到最终结果:
八、 案例二,根据各店铺的运营情况统计表,制作如下盈亏方块图:
首先,我们来看一下原始数据:
其次,我们制作如下表格:
然后,我们需要在G列显示亏损情况,在I列显示盈利情况。这里涉及到一个逻辑判断,如果利润为负,则在G列对应单元格显示亏损情况,而在I列对应单元格不作显示;如果利润为正,则在G列不作显示,而在I列显示盈利情况。
根据以上逻辑,我们在G3单元格(对应汉中店)采用IF函数:
=IF(D3>0,"",进行显示操作)
这里说明一下上式,如果D3>0,那么返回"空"(两个连续的引号),如果D3小于等于0,那么进行显示操作。
显示操作其实又是利用REPT函数来重复显示方框字符。
这里有个问题需要明确,首先REPT重复字符的数量是个非负数,因此我们需要用到ABS函数来求绝对值,然后REPT重复字符的数量又是个整数,因此我们需要用到ROUNDUP函数将数值向上收一位变成大于等于1的整数。
假定返回的方框字符数量是1000的倍数,因此,这个函数这样用:
REPT("■",ABS(ROUNDUP(D3/1000,0)))
用ROUNDUP返回一个大于等于1或小于等于-1的数值,然后用ABS求绝对值,让方块的显示数量为大于等于1的正整数。
现在把上面REPT的公式加入到IF语句里面就变成了:
=IF(D3>0,"", REPT("■",ABS(ROUNDUP(D3/1000,0))))
我们还需要在最左边显示利润额,那么,在上面的公式前面再加上:
D3&得到最终公式:
=IF(D3>0,"",D3&REPT("■",ABS(ROUNDUP(D3/1000,0))))
I列盈利同G列亏损的公式,只不过需要将D3>0改为D3<0:
=IF(D3<0,"",REPT("■",ABS(ROUNDUP(D3/1000,0)))&D3)
得到最终结果:
PS:今天的分享就到这里了,喜欢的朋友记得关注我们,后期还会有更多实例讲解推出,不要错过精彩内容哦!
,