搜索
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(E20,REPT(|,ABS(E2)),)，设置右对齐；H列绘制正数，=IF(E20,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(C80,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(C30,C40),F3+C4,IF(C30,F3,IF(C40,C3+F3,IF(C40,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点的注意事项同样适用于本点。用EXCEL绘制精确的甘特图2007-08-25今天有个朋友由于在没有project软件的情况下，紧急需要绘制精确的甘特图。我在家里有没有project软件，不过我在excel图表方面有一定的技巧，解决了他的问题。如果你也想知道如何在一分钟之内利用excel图表功能作出甘特图来，请花几分钟看完这篇文章：这是完成的样子：首先，你要输入进度数据，如下图然后，启动图表制作向导程序：选中“簇状条形图”，单击【下一步】选择适当的数据区域，要包括任务名称、开始、工期这3列，并选择“系列出生在列”，单击【下一步】选定适当的数据系列，这是非常关键的一步，应保证系列“开始”在“工期”的上面，单击【完成】提前完成向导程序这就是向导程序完成的图表，当然它还不满足我们的要求，接下来我们对其进行必要的设置：1，反转“Y”轴：双击图表中的“Y”坐标轴，并勾选“分类次序反转”选项：得到的结果已经接近最终的成果了：2，下面的步骤应该能猜到了，就是隐藏《开始》系列：这个相对简单完成后，不错嘛，快完工喽3，接下来要调整X轴的坐标，以适应进度度的尺度习惯：这里，需要说明的是日期坐标会被转化为数字显示，这样调整坐标轴最大、最小值的时候容易混淆，不要紧接着看吧：在一个单元格里输入一个比项目最早日期还早几天的一个日期如此例就可以是2007-1-8，然后点击“，”按钮使其数字化即可得到该日期的数值。坐标轴的最大值就是项目完成日期后的几天，安装上面的方法计算出来，一并填入坐标轴格式窗口，便大功告成了！当然还可以设置更加漂亮的外观，不过这不是重点，大家可以自己掌握。怎么样？效果不错吧？如果你手头工具不足，就可以用这个办法了。更新根据网友的提示，youtube上面的一段视频居然和我介绍的方法一样。刚知道的时候简直快气疯了，我和好友goofy研究出这个方法是在一台赛扬PC上，用的是office97。但后来就释然了，感叹传播的效力。相比之下，还有类似涂鸦形式的excel甘特图模板（下载地址：excel-zip），如下图所示：这里，你需要手动定义所有东西包括日历、任务条等等。但是图示效果更佳规整，更加符合专业习惯。随即在办公室里展开