EXCEL图表制作rept函数.docx
- 文档编号:9606240
- 上传时间:2023-05-20
- 格式:DOCX
- 页数:13
- 大小:369.66KB
EXCEL图表制作rept函数.docx
《EXCEL图表制作rept函数.docx》由会员分享,可在线阅读,更多相关《EXCEL图表制作rept函数.docx(13页珍藏版)》请在冰点文库上搜索。
EXCEL图表制作rept函数
EXCEL图表制作rept函数
——in-cellbar图之终结篇
⏹REPT函数功能
按照给定的次数重复显示文本,可以通过函数REPT来不断地重复显示某一文本字符串,对单元格进行填充。
⏹REPT函数语法
REPT(text,number_times)
▲Text:
必需。
需要重复显示的文本。
▲Number_times:
必需。
用于指定文本重复次数的正数。
注解:
●如果number_times为0,则REPT返回""(空文本)。
●如果number_times不是整数,则将被截尾取整。
●REPT函数的结果不能大于32,767个字符,否则,REPT将返回错误值#VALUE!
。
示例:
如果将示例复制到一个空白工作表中,可能会更容易理解该示例。
⏹用REPT函数模拟表格中的条形图
在财经类杂志报刊上,经常可以看到带有bar图的表格,使表格更直观,也不需要增加大的空间。
在excel2007中提供了此功能,但2003中没有。
不过,用rept函数可以方便的模拟。
用rept做简易条形图确实非常巧妙和方便,特别是数据行数比较多时。
即使2007的数据条也不见得比它好,因为2007的数据条不是按比例的。
如图,在数据列的右边填入如下公式:
=REPT("|",100*D5/$D$22),每行的重复次数是它占总数的百分比,设置字体颜色,就是一个很好的bar图啦。
注意,模拟列的字体不要用中文,否则很难看了。
这样甚至比做图还方便,特别是在分析一组数据时,用图表来模拟,都没有这样方便。
下边,总结一下用rept做图的多种情形。
✓一般单元格条图
之前的日志已说明做法,=REPT("|",E2),Arial字体,不再细述。
在宋体字下,竖线会显得很疏散,不象图表。
可使用Arial字体,则可使竖线更紧凑。
建议使用8磅大小。
有热心网友指出,如果reptWebdings字体的"g"字符,可出现完全连接的方块字符。
但是,在相同的宽度下,用竖线"|"可rept的次数更多,从而精确度更高,因此仍建议使用Arial字体的竖线"|"。
此外,Gautami字体也可以,也是连在一起,就是条形显得比较窄。
✓带上数据标签
使用=rept("|",B2)&""&B2,可让图表带上数值标签。
✓有负数的情况
当源数据存在正负数的情况,可分两列进行rept,图中G列绘制负数,=IF(E2<0,REPT("|",ABS(E2)),""),设置右对齐;H列绘制正数,=IF(E2>0,REPT("|",ABS(E2)),""),设置左对齐。
✓用参数调整图表宽度
源数据很大时,我们不可能照数据重复字符,那样图表会太大或者太小了。
根据原数据大小,可使用合适的系数,调整rept的次数,从而调整图表至合适的宽度,如rept("|",B2*n)或rept("|",B2/n)。
如果是百分比数据,可rept("|",B2*100)。
比较好的做法是用一个单元格存放这个系数,并定义名称如β,然后=rept("|",B2*β),这样可以很方便的调整β的值,而不需要手动去修改各个公式。
✓成对条形图
成对条形图,如果用rept也非常简便。
上图中,F3的公式=C3&""&REPT("|",C3*β),H3的公式=REPT("|",D3*β)&""&D3,其中名称β用来调整图表宽度。
在商业分析中,经常有以下情况需要用图表来表现:
●一项市场调查研究中,男性和女性、赞同和反对、满意和不满意的两方面的消费者,他们在某些项目上的指标分布特性
●一项产品组合决策中,乐观场景和悲观场景下各产品的获利情况
●一个产品试销活动中,不同门店渠道使用不同的折扣率,销量与折扣率的是否存在比例关系
以上情况,如果用图表来表现其中的关系,可以使用成对的条形图(pairedbars),据说有人称作“旋风图”,也比较形象。
在Excel中做这种图,需要一些技巧,今天整理一下这个问题。
主要的技巧是:
将其中一列数据转换为负数,使之出现在分类轴的左边,同时使用自定义数据格式将负值显示为正数
做法与步骤:
1、对原数据B~D列,准备转换数据如F~H列,其中G列=-C列,I~K列为较高级辅助数据,后面再说。
2、以F~H列数据作堆积条形图。
3、这时,分类轴的分类项目次序与表格相反,可在分类轴-》坐标轴格式-》勾选“分类次序反转”,这个地方是一个小技巧。
4、在分类轴的坐标轴格式中设置刻度线标签在图外。
5、删除数值轴。
如欲保留数值轴,注意由于左边是负的,需要调整显示为正数,方法:
数值轴-》坐标轴格式-》数字-》自定义-》0.0;0.0;0.0,这个地方是关键技巧之一,后面也要用到。
6、设置显示左右条形图的数值标签,并调整到合适位置。
对左边的负值标签需要使用上面的自定义数据格式技巧,使之显示为正数。
7、如觉得第6步手动调整麻烦,该步骤可使用以下高级方法替代:
借用辅助序列显示标签值。
方法是:
分别将j列和k列的辅助数据加入图表,这时左右条形图顶端各出现一个新的序列,设置其无框、透明,使用标签修改工具宏,分别设置其数据标签为C列和D列,则左右条形图的数值标签位置非常好了,也没有负数烦恼了。
参见后面的隐形辅助序列显示出来的情况。
8、进行其他格式化,达到上图效果,不一一细述。
有时候,希望做成下图效果,就是把分类标签放在左右条形图的中间。
方法1,技巧有2点。
1、在准备数据时增加一个占位的虚拟序列,图中的I列。
2、在前面的第2步时,以F~I列作堆积条形图,然后在“数据序列格式”的“系列次序”中调整顺序,使条形图呈正确顺序。
然后设置这个虚拟序列为无框、透明。
3、将I2加入图表,修改图表类型为散点图,设置Y误差线=10,设置误差线格式,则模拟了左侧的坐标轴。
4、其他步骤同前。
如果将隐形的辅助序列显示出来后的情况就是这样的,可仔细看体会其中的做法。
方法2,拼接法
即使用C列和D列的数据分别作两个条形图,然后拼接在一起,看过我前面杂志级图表方法的朋友应该会知道做法。
不过,有一个技巧值得提一下,用C列(正数)做条形图时,要让条形往左,办法是数值轴-》刻度-》数值次序反转,想不到吧,不需要用负数了。
下面这个图就是在PPT中的拼接做法例子。
这种分析图表在社会人口学中的案例,就是人口金字塔,人口金字塔是按人口年龄和性别表示人口分布的特种塔状条形图,是形象地表示某一人口的年龄和性别构成的图形。
水平条代表每一年龄组男性和女性的数字或比例。
金字塔中各个年龄性别组相加构成了总人口。
补充一个拼接法做的成对条形图例子。
✓横向瀑布图
瀑布图是经营分析工作中的常用图表,用来解释从一个数字到另一个数字的变化过程,比如:
●从去年的业务收入到今年的业务收入,哪些产品各影响收入增减多少
●从销售收入到税后利润,各类成本费用各影响多少
●......
在麦肯锡为CT做的BPR咨询的材料中,用来做收入增减影响因素分析。
基恩的《用图表说话》提到,麦肯锡的咨询顾问们把它叫做瀑布图。
现在介绍下用excel做瀑布图的解决方案,看下面的图片,作图的过程描述得已经很清楚。
(注意,介绍图都留下了excel的行列号,熟悉excel的朋友一看就可以明白作图的原理和过程)
简单说明一下:
1、进行构图和数据设计。
将瀑布图的柱子分为3组,一是开始和结束的起始数字,二是增长类的数字,三是减少类的数字,另外需要设计一列占位的数字。
这样便于分别进行格式化。
2、根据构图准备数据。
准备的作图数据包括,起点终点值、占位序列、正数序列、负数序列、累计序列,每一序列数据都有它的用途。
以第8行为例,公式如下:
●占位序列的公式为:
=IF(C8<0,I8,I7)
●正数序列的公式为:
=IF(C8>=0,C8,"")
●负数序列的公式为:
=IF(C8>=0,"",ABS(C8))
●累计序列的公式为:
=SUM($C$7:
C8)
3、作图。
用起点终点值、占位序列、正数序列、负数序列的数据做堆积柱形图,将占位序列设置为无框无色,达到隐形,从而实现其他数据序列“悬浮”起来的效果。
4、将累计列数据加入图表,设置图表类型为折线图,添加误差线X,设定值1,于是出现了连接各项目的横线。
再设置折线图无色无点,实现隐形的效果。
5、绘制上下箭头的图形,设置颜色和边框,分别贴入正数序列、负数序列,实现用上升和下降箭头示意的效果。
6、进行其他格式化,不再一一细述,达到如上图效果,OK,一个完美的瀑布图完成。
上面介绍了瀑布图的做法,而Bissantz产品中的表格中的横向瀑布图,则占用更少的空间。
下面的动画很好地说明如何简化一个图表的过程,可节省80%的空间,也很值得借鉴。
有老外高手,居然用再简单不过的rept做出了这种表格中的横向瀑布图,令人叫绝,下面是做法截图。
瀑布图的思路是用辅助数据占位留空,而这里,我们用rept空格来实现留空。
●D4的公式:
=REPT("",F4/$C$14)&REPT("|",ABS(C4)*1.5/$C$14),即按F4重复空格""+按C4*1.5重复竖线"|"。
●F4的公式:
=IF(AND(C3<0,C4<0),F3+C4,IF(C3<0,F3,IF(C4>0,C3+F3,IF(C4<0,C3+F3+C4)))),即计算需要留的空位,是bar的起始位置。
●C14为一个调整系数,根据源数值大小,调整此系数可方便的调整图表的宽度,使之适合于表格。
如设置名称后引用更好。
●对图表单元格设置条件格式,根据C列的数值,正数为蓝色、负数为红色。
需要说明的是:
1)重复竖线时为什么要乘1.5呢?
因为""和"|"的宽度并不一样,直接重复显然图表不成比例。
而有高人发现,在Arial字体、8磅大小的情况下,""的宽度是"|"的1.5倍,因此,在留空时我们用rept("",x),而在绘竖线时用rept("|",x*1.5),则绘出的图表恰好成比例,实在是高。
2)这种瀑布图,因同时用到""和"|",需要在显示比例为100%时才显示正确,变化显示比例会导致图表变形,请注意此点。
✓甘特图
根据第5点同样的原理,有人运用rept来做甘特图,也是很巧妙,不过实用性并不大。
E3的公式:
=REPT("",(C3-MIN($C$3:
$C$11)))&REPT("|",1.5*(D3-C3+1)),复制下去。
第5点的注意事项同样适用于本点。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL 图表 制作 rept 函数