excel 圖表問題 @ vbd391t的部落格

標題:

excel 圖表問題

發問:

假設workbook 有兩張sheets. Sheet1 and Chart1Sheet1 column A 係日期 (eg, 2001/1/1 至 2005/12/31)Sheet1 column B 係數量 (數量係randon 既)Chart1 的x axis use Sheet1 column A dataChart1 的y axis use Sheet1 column B dataInitially , the chart1 contain all date of sheet1Now, i would like to put a start date in sheet1 D1 and end... 顯示更多假設workbook 有兩張sheets. Sheet1 and Chart1 Sheet1 column A 係日期 (eg, 2001/1/1 至 2005/12/31) Sheet1 column B 係數量 (數量係randon 既) Chart1 的x axis use Sheet1 column A data Chart1 的y axis use Sheet1 column B data Initially , the chart1 contain all date of sheet1 Now, i would like to put a start date in sheet1 D1 and end date in D2 so that the chart1 can show the data between the period. What shall i do ? 更新: 莫非無人識答??

最佳解答:

應該很多人識答, 但很多人(包括我)都不願回答Chart的問題, 因種類繁多, 單靠在這裡的有限文字, 很難說得清楚 Excel 的圖表本身, 應無內建功能, 去set 祇要原來資料的某一個範圍, 所以祇有去寫巨集: Sub date_change() Dim date1, date2 As Date Set xxx = Sheets(“Sheet1”).Range(“A:A”) date1 = Sheets(“Sheet1”).Range(“D1”) date2 = Sheets(“Sheet1”).Range(“D2”) If xxx(1) >= date1 Then Set a1 = xxx(1) Else For Each xx In xxx If xx > date1 Then Set a1 = xx.Offset(-1, 0) Exit For End If Next End If For x = 1 To 10000 If a1.Offset(x, 0) > date2 Or IsEmpty(a1.Offset(x, 0)) = True Then Set a2 = a1.Offset(x - 1, 1) Exit For End If Next aaa = a1.Address & “:” & a2.Address Sheets(“Chart1”).Select ActiveChart.SetSourceData Source:=Sheets(“Sheet1”).Range(aaa), PlotBy:=xlColumns End Sub