- 帖子
- 175
- 主題
- 8
- 精華
- 0
- 積分
- 207
- 點名
- 307
- 作業系統
- WIN 10
- 軟體版本
- Office 2010
- 閱讀權限
- 20
- 性別
- 女
- 註冊時間
- 2011-6-30
- 最後登錄
- 2024-12-26
     
|
22#
發表於 2011-9-7 14:43
| 只看該作者
回復 21# maxchou
雖然如此,還是可以做的,之前提的網址所下載的那個funchart5_brickyard.zip 把rngBlank從來源資料移除,就可以做到(如附件)
 brickyard-R1.rar (14.94 KB)
,其中有一段說明如下:
"All of the hard work in this file is done using defined names. These names create a series of 100 bars, then work out which plant is associated with each bar, and what the 'value' of the bar should be. The 100 bars are plotted on the chart and then formatted such that they appear to be a single column. To improve the accuracy, change the rngBins defined name to use up to 4,000 points (instead of the two 100's there already). "
rngVol =OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-1,1)
rngVCum =OFFSET(rngVol,0,1)
rngCost =OFFSET(rngVol,0,2)
rngBins =TRANSPOSE(SUM(rngVol)*ROW(Sheet1!$1:$100)/100)
rngPlant =MATCH(rngBins,rngVCum,1)
rngSer1 =IF(rngPlant=1,INDEX(rngCost,1),0)
rngSer2 =IF(rngPlant=2,INDEX(rngCost,2),0)
rngSer3 =IF(rngPlant=3,INDEX(rngCost,3),0)
rngSer4 =IF(rngPlant=4,INDEX(rngCost,4),0)
|
|
