【应用案例02】零部件截面图（已评）

cleverzhzhf

02 零部件截面图_题目_小翟.zip (30.74 KB, 下载次数: 70)

2014-8-1 09:36 上传

点击文件名下载附件

关键部分的公式，F4:H4数组公式填充：

1. =({10,0,10}-IF(ROW()<9,,INDEX(A:A,10-SUM(N(ROW()-8<=SUBTOTAL(9,OFFSET($B$4,,,COLUMN(A:F))))))))/{2,-1,2}

复制代码

为了动态标签，I3公式：

1. =IF(ROW()=3,"总长度"&SUM(B4:B9),IF(COUNTIF($G3:G$4,0)=6,5,NA()))

复制代码

最终效果做了半天，发现竟然是纵坐标轴搞鬼。
对于三维格式，在我这里不能把纵坐标轴的“最大值”改为非“自动”，要不然柱形图就变成一条条的了。。。（附记：这是在2007的版本下的情况）
后来在2010版本下进行修改，坐标轴的“最大值”可以随意改，相近的柱子会自动结合成一个。


害得我想了很多旁门左道，其中“图1”用了阶梯图，首先用散点图构造阶梯图外轮廓，其次用柱形图填充内部。
构造阶梯图时候，用了2个方法，之前用的误差线，忘了保存了。现在图里只剩下纯点的构造。
不过最终效果不完全达到要求。

悠悠的炊烟

零部件截面图总结

一、           构思

紧扣零部件截面图的水平对称性做文章。

1.      上、下两部分灰色背景是对称的，只要设计巧妙做图数据源是可以共用的

2.      零部件以水平中心线为轴也是对称的

3.      只看下半部分灰色背景图的话，这就是不等宽柱形图。对于不等宽柱形图常用的方法有

1)       柱形图：根据数据决定柱子的数量（例如数据为3的话，就需要3根柱形），柱形间距为0

2)       面积图：该方法较普遍，网上有很多详细的教程教授做法

二、           具体实施

1.      堆积柱形图

三个数据系列，用函数构建做图数据是本题的难点，其中：

1)       上、下灰色部分因对称所以数据一模一样，可共用一个数据源（一个人在同一时间不能同时踏入两条河流，但同一数据源在图表中是可以多次使用的），这里用1个公式，见D列；

2)       中间的零部件用另1个公式，见E列；

3)       元器件最右端中部动态显示该零部件的“总长度”：通过散点定位，X：单元格H3，Y：5。选中散点标签=单元格H2的内容。

附送图表：背景为一个整体，零部件截面图凸显在灰色背景上

利用零部件以水平中心线为轴的对称性，有两个数据系列

1)       系列1：公式同前述零部件公式基本一致，只是除2

2)       系列2：为系列1的负数

2.      面积图

车任静、头版皆采用了此法，正所谓英雄所见略同，棒极啦。

1)       两个数据系列，共用同一辅助做图数据源；

2)       主、次坐标系，其中次坐标设成“逆序刻度值”，使得次坐标图形反转

3)       中间的零部件借用绘图区背景

4)       主、次坐标的分类轴分别设置》位置坐标轴：在刻度线上

5)       标签：散点图

发现异常及排异：

验证过程中发现当长度都为5时，图形出现异常现象，是由于：

X轴方向的最大值为35，但只能有一个35。当长度皆为5时，出现3个35导致图形异常，因此修改一下X轴方向的公式，保持其最大值35只有一个。

当长度都为5时，X轴方向数据： sum({0;5;5;10;10;15;15;20;20;25;25;30;30;35})= 245

正确性验证和排异是保证图表成功的最后一个步骤，切切不可忽视

3.      堆积面积图

基本做法同不等距柱形图相似，详见相禹的做法，重点在构建数据源，然后作图。

注意：X轴的坐标轴类型选择“日期坐标轴”。

三、           教训：

题目对公式的限制本意在激发大家好好利用零部件的对称性做文章，没想到大侠们纷纷在公式上做文章，拿你们这些大神这么好呢。

下次出题时要力争不给你们留偷奸耍滑的机会。

---------------------------------------------------------------------------------------------

批改

总结的做法及批改详见附件。如有误判或不妥处，请找我。

欢迎大家在开贴后踊跃讨论

cleverzhzhf

把公式汇总一遍。当拥有相当好的构图思路的时候，公式上就能简单一些。
当构图思路一般，有时只能用公式基础来弥补吧~

flykiss80	柱形图	=A4+C4
		=(10-G5)/2
		=IFERROR(VLOOKUP(E5,$A$4:$B$10,2,TRUE),0)
车仁静	面积图，分作主次纵坐标轴	=SUM(OFFSET(B$3,,,(ROW()+1)/3))
		=IF(MOD(ROW(),3)=0,0,5-OFFSET(A$3,(ROW()-1)/3,)/2)
delete_007	柱形图	=IF((ROW(A2)<=SUM($B$4:$B$9)+5)*(ROW(A2)>5),LOOKUP(ROW(A2)-6,SUMIF(OFFSET(B$3,,,ROW($1:$6)),">0"),A$4:A$9)/{-2,1,-2})+{5,0,5}
w83365040371	面积图	=IF(ROW(A1)=1,1,IF(ROW(A1)=16,36,SUM(OFFSET(B$3,,,ROW(A1)/2))+6))
		=IF(COLUMN(A1)=1,N(OFFSET(A$2,ROW(A2)/2,)),(10-$B12)/2)
being117	面积图	=$C$2+SUM($B$4:B4)
		=IF(ROW()-3>$C$2,5-VLOOKUP(ROW()-4,IF({1,0},$C$3:$C$9,$A$4:$A$10),2,1)/2,5)
		=IF(ROW()-3>$C$2,VLOOKUP(ROW()-4,IF({1,0},$C$3:$C$9,$A$4:$A$10),2,1),0)
wangg913	面积图	=SMALL(IF(ROW($1:$8)<8,SUMIF(OFFSET(B$3,,,ROW($1:$7)),">0"),SUM(B$4:B$9)-2.5)+{0,0},ROW(A2))+5
		=5-LOOKUP(D3-5,SUMIF(OFFSET(B$3,,,ROW($1:$7)),"<>"),A$4:A$10)/2
cleverzhzhf	柱形图	=({10,0,10}-IF(ROW()<9,,INDEX(A:A,10-SUM(N(ROW()-8<=SUBTOTAL(9,OFFSET($B$4,,,COLUMN(A:F))))))))/{2,-1,2}
悠悠的炊烟	柱形图	=10/2-E9/2
		=INDEX(A:A,SMALL(IF(COLUMN($1:$1)<=$B$4:$B$13,ROW($B$4:$B$13),4^8),ROW(A1)))
	面积图	=IF(SUM($D$4:D4)>=245,0,IF(ROW()-ROW($B$4)<COUNT($B$5:$B$11)*2+2,IFERROR(SUM(OFFSET($B$5,,,INT((ROW()-ROW($D$4))/2))),0),35))
		=10/2-IFERROR(INDEX($A$5:$A$11,INT((ROW()-ROW($D$3))/2))/2,0)