楼主 rongjun |
Q:如何以指标线为分界给折线设置不同的颜色? A:为了给折线设置不同的颜色,我们可以将折线分成上下两个部分来绘制。为此必须先计算出折线与指标线的交叉点坐标值,然后将原始数据以指标线为参照值分为上下两组,分别与交叉点坐标组成两组新的坐标值,绘制成两个折线系列。 制作步骤如下: 1、折线虚拟X坐标值:因为折线图的横坐标只作为分类而不具备数据意义,所以必须对折线图的横坐标构造一个虚拟的X值。考虑到本例中存在一月至十二月12种分类,故可以假设横坐标轴的起始值为0.5,最大值为12.5,而折线图的数据点恰好坐落在横坐标轴的两个主要刻度线中间,因此可以得到一月至十二月的虚拟X值为1、2、3……12,如下图 中E3:E12单元格区域所示。 2、判断销售额折线段是否与指标线相交:在F3单元格输入公式
下拉至F14单元格,若F3:F14单元格区域内的公式结果返回TRUE则说明对应的折线段存在交叉点。 3、计算交叉点X值:根据直线相交的数学知识即可得出交点X值的计算公式。在G3单元格输入公式
下拉至G14单元格,G3:G14单元格区域公式返回数值即为交点X值,若返回#N/A则说明该折线段不存在交点。 4、计算交叉点Y值:在H3单元格输入公式
下拉至H14单元格,H3:H14单元格区域公式返回数值即为交点Y值,若返回#N/A则说明该折线段不存在交点。 5、将原数据分为高低点两类:对于高于指标值的数据点划为高点Y值,如在I3单元格输入公式
下拉至I14单元格;对于低于指标值的数据点划为低点Y值,如在J3单元格输入公式
下拉至J14单元格。 6、构造高于指标值系列坐标值:将高点坐标值与交点坐标值合为一组,并按照X值从小到大排列,构成高于指标值系列的XY坐标值。如K3:L26单元格区域所示,在K3单元格输入公式
下拉至K26单元格,在L3单元格输入公式
下拉至L26单元格。 7、构造低于指标值系列坐标值:将低点坐标值与交点坐标值合为一组,并按照X值从小到大排列,构成低于指标值系列的XY坐标值。如M3:N26单元格区域所示,在M3单元格输入公式
下拉至M26单元格,在N3单元格输入公式
下拉至N26单元格。 |
2楼 rongjun |
8、创建折线图:选中A2:B14单元格区域→单击“插入”选项卡→“图表”组→“折线图”→“折线图”。 9、格式化图表:删除图表标题、网格线、图例项,调整图表大小、边框、填充颜色。 |
3楼 rongjun |
10、添加“高于指标值”系列:鼠标右键单击图表→单击“选择数据”→“添加”→“系列名称”输入“高于指标值”→“系列值”选择L3:L26单元格区域→单击“确定”退出“编辑数据系列”对话框→单击“确定”退出“选择数据源”对话框。 11、更改图表类型:鼠标右键单击“高于指标值”系列→单击“更改系列图表类型”→“XY散点图”→“带直线的散点图”→单击“确定”退出“更改图表类型”对话框。鼠标右键单击图表→单击“选择数据”→在“系列”列表框中单击“高于指标值”系列→单击“编辑”→“X轴系列值”选择K3:K26单元格区域→单击“确定”退出“编辑数据系列”对话框→单击“确定”退出“选择数据源”对话框。 12、格式化系列:鼠标右键单击“高于指标值”系列→单击“设置数据系列格式”→单击“线条颜色”选项→“实线”→颜色设置为红色→单击“线型”选项→宽度调整为3磅→关闭“设置数据系列格式”对话框。 |
4楼 rongjun |
13、添加“低于指标值”系列:鼠标右键单击图表→单击“选择数据”→“添加”→“系列名称”输入“低于指标值”→“X轴系列值”选择M3:M26单元格区域→“Y轴系列值”选择N3:N26单元格区域→单击“确定”退出“编辑数据系列”对话框→单击“确定”退出“选择数据源”对话框。 14、格式化系列:鼠标右键单击“低于指标值”系列→单击“设置数据系列格式”→单击“线条颜色”选项→“实线”→颜色设置为蓝色→单击“线型”选项→宽度调整为3磅→关闭“设置数据系列格式”对话框。 |
5楼 rongjun |
15、添加“指标线”系列:鼠标右键单击图表→单击“选择数据”→“添加”→“系列名称”输入“指标线”→“X轴系列值”输入“=双色折线图!$E$3,双色折线图!$E$14”→“Y轴系列值”输入“=双色折线图!$D$3,双色折线图!$D$3”→单击“确定”退出“编辑数据系列”对话框→单击“确定”退出“选择数据源”对话框。 16、格式化系列:鼠标右键单击“指标线”系列→单击“设置数据系列格式”→单击“线条颜色”选项→“实线”→颜色设置为绿色→单击“线型”选项→宽度调整为3磅→关闭“设置数据系列格式”对话框。 进一步美化图表,完成双色折线图的绘制。 双色折线图.rar |
6楼 zhyou |
好方法!学习,谢谢分享 |
7楼 liguangtj |
这样做很复杂啊,哈,最简单的做法就是如果用的时候,粘贴一个图表一个副本,把副本的颜色改成红色,然后裁剪覆盖图片的上半部分。 这是讨巧的做法,但是可以弥补亟需。 专业的做法还是跟楼主学习! |
8楼 mjgdxx |
可以做成系列教程了 |
9楼 hufengyi |
安逸 |
10楼 rabbitllu |
强烈建议楼主出本图表的教材,太精彩了。 |
11楼 boyi1019 |
非常感谢~~~~~~~~~~~~~~~~~~~~~~~~~~ |
12楼 caoxi09 |
讨巧的做法 还不错! 楼主是正道,但是麻烦! |
13楼 LoveJinLee |
要不要这么N啊。 果然是想法更重要。。 有想法,实现起来就简单了~ |
14楼 smupsu |
=IF(F3,(E4*B3-E3*B4-E4*$D$3+E3*$D$3)/(B3-B4),NA()) 有谁能解释下这个公式为什么会取出交叉点的X值呢?说来惭愧,高中代数忘光求了,提醒我下 P(X0 Y0)=Y-A ………………啥的,想不起来了。 |
15楼 lgcmeli |
柱形图双色,比较简单。 |
16楼 老糊涂 |
下载学习了! |
17楼 bishunbiao |
非常精彩,搬个板凳听课 |
18楼 stingwang |
先M着,到时候来试试。谢谢楼主分享 |
19楼 amiaosheng |
这图做起来好难啊 |
20楼 playstand |
真的应该出本书了…… |
21楼 amiaosheng |
这图做起来好难啊 |
22楼 stingwang |
先M着,到时候来试试。谢谢楼主分享 |
23楼 小拇指 |
学习了 , |
24楼 gao1987821 |
精彩,好好学习。 |
25楼 天逸飞扬 |
好方法!学习,谢谢分享 |
26楼 liuxy881102 |
好复杂。 |
27楼 水兵桃桃 |
|
28楼 cocojyg |
学习,谢谢分享 |
29楼 keven |
赞一个,回宿舍好好看看! |
30楼 lrlxxqxa |
谢谢分享 |
31楼 poiuyman5 |
色彩分明,易看易懂,很实用! |
32楼 atpyuan |
1、"=IF(B4<>"",SMALL((B3:B4,$D$3),2)=$D$3)"这个公式中怎么和IF公式有很大的差别啊?不是IF(logical_test, [value_if_true], [value_if_false])的结构?而且“SMALL((B3:B4,$D$3),2)=$D$3”的返回值结果也是可以的。2、“02.=IF(F3,(E4*B3-E3*B4-E4*$D$3+E3*$D$3)/(B3-B4),NA())”这个IF的公式判断条件是什么? |
33楼 13641096715 |
谢谢分享,有一定难度,慢慢消化。 |
34楼 过期品24 |
f |