想知道是否可以按降序對圖表資料顯示進行排序：
我不知道該怎么做。
我唯一知道的是如何瀏覽系列值：
Set s = cht.FullSeriesCollection(1)
For i = 1 To s.Points.Count
If s.Values(i) < 0 Then 'JustAnExample
'WhateverIwant
End If
Next i
此外，上面的圖表是使用作業表中的資料構建的：
uj5u.com熱心網友回復：
請測驗下一個解決方案。由于您沒有發布圖表創建代碼，我想象了一些這樣做的事情：
Sub createStackedColChart_Arrays()
Dim sh As Worksheet, arr1, arr2, arrN, arrD
Dim chartName As String, arrSort, i As Long
Set sh = ActiveSheet 'use here the necessary sheet
chartName = "MyChartSorted"
arr1 = sh.Range("A2:D2").value 'first series array
arr2 = sh.Range("A3:D3").value 'second series array
arrN = sh.Range("A1:D1").value 'X axes values array
'Create the reference array of summarized values per column:
ReDim arrSort(1 To UBound(arr1, 2))
For i = 1 To UBound(arr1, 2)
arrSort(i) = arr1(1, i) CLng(arr2(1, i))
Next i
'_______________________________________________
'sort arrays according to reference one (arrSort):
sortArrs arrSort, arrN, arr1, arr2
'if the (testing) chart exists, delete it:
On Error Resume Next
ActiveSheet.ChartObjects(chartName).Delete
On Error GoTo 0
'create the necessary chart:
With ActiveSheet.ChartObjects.Add(left:=100, width:=375, top:=75, height:=225).Chart
.Parent.Name = chartName 'name it to have a reference when delete it
.SeriesCollection.NewSeries.Values = arr1 'add first series
.SeriesCollection.NewSeries.Values = arr2 'add first series
.HasTitle = True 'set it to allow a Title
.chartTitle.text = "My Sorted Chart" 'set the Title
.ChartType = xlColumnStacked 'set the chart type
.SeriesCollection(1).XValues = arrN 'add values to X axis
End With
End Sub
Sub sortArrs(arrS, arrN, arr1, arr2) 'being passed byRef, the initial arrays are filtered
Dim i As Long, nxtEl As Long, tmp, tmpN, tmp1, tmp2
For i = LBound(arrS) To UBound(arrS) - 1 'iterate between the arrS elements (except the last):
For nxtEl = i 1 To UBound(arrS) 'iterate between the arrS elements (starting from the second one):
If arrS(i) < arrS(nxtEl) Then 'sort the arrays according to the element values (< means descending)
tmp = arrS(i): tmpN = arrN(1, i): tmp1 = arr1(1, i): tmp2 = arr2(1, i)
arrS(i) = arrS(nxtEl): arrN(1, i) = arrN(1, nxtEl)
arr1(1, i) = arr1(1, nxtEl): arr2(1, i) = arr2(1, nxtEl)
arrS(nxtEl) = tmp: arrN(1, nxtEl) = tmpN
arr1(1, nxtEl) = tmp1: arr2(1, nxtEl) = tmp2
End If
Next nxtEl
Next i
End Sub
請在測驗后發送一些反饋。
如果您需要動態圖表，這意味著在參考范圍（在您的示例中為 A1:D3）中發生任何值更改時重繪 它，Change可以使用作業表事件。如果在上述范圍內發生變化，該事件將呼叫上述函式。如果需要，請復制涉及的作業表代碼模塊中的下一段代碼：
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1:D3")) Is Nothing Then
createStackedColChart_Arrays 'if need to change the Sub name, please adapt it here...
End If
End Sub
編輯：
使用作業表中所有現有行/列的更詳細的動態版本。最后一列在第一行（列標題）上計算：
Sub createStackedColChart_Arrays_Dynamic()
Dim sh As Worksheet, lastR As Long, lastCol As String, arrN, arrSort
Dim chartName As String, dict As Object, i As Long, j As Long
Set sh = ActiveSheet 'use here the necessary sheet
lastR = sh.Range("A" & sh.rows.count).End(xlUp).row 'last row of A:A column
lastCol = Split(sh.cells(1, sh.Columns.count).End(xlToLeft).Address, "$")(1) 'extract the last column Letter
chartName = "MyChartSorted"
Set dict = CreateObject("Scripting.Dictionary") 'create the necessary dictionary object
For i = 2 To lastR
dict.Add i - 1, sh.Range("A" & i & ":" & lastCol & i).value 'place in the dictionary the ranges to become chart series
Next i
arrN = sh.Range("A1:" & lastCol & 1).value 'X axes values (names array)
'Create the reference array of summarized values per column:
ReDim arrSort(1 To UBound(arrN, 2))
For i = 1 To UBound(arrN, 2)
For j = 1 To dict.count
arrSort(i) = arrSort(i) dict(j)(1, i) 'add each column value to summarize
Next j
Next i
'_______________________________________________
'Debug.Print Join(arrSort, "|"): Stop
sortDArrs arrSort, arrN, dict 'sort the involved arrays (ranges) according to arrSort sorted descending
'if the (testing) chart exists, delete it:
On Error Resume Next
ActiveSheet.ChartObjects(chartName).Delete
On Error GoTo 0
'create the necessary chart:
With ActiveSheet.ChartObjects.Add(left:=100, width:=375, top:=80, height:=225).Chart
.Parent.Name = chartName 'name it to have a reference when delete it
For i = 1 To dict.count 'add a new series from the dictionary (sorted) items:
.SeriesCollection.NewSeries.Values = dict(i) 'add the series
Next i
.HasTitle = True 'set it to allow a Title
.chartTitle.text = "My Sorted Chart" 'set the Title
.ChartType = xlColumnStacked 'set the chart type
.SeriesCollection(1).XValues = arrN 'add values to X axis
End With
End Sub
Sub sortDArrs(arrS, arrN, dict As Object) 'sort descending all involved arrays/ranges
Dim i As Long, nxtEl As Long, tmp, tmpN, arrTemp, arrT, k As Long, j As Long
ReDim arrTemp(dict.count - 1): ReDim arrT(1 To 1, 1 To UBound(arrN, 2))
For i = LBound(arrS) To UBound(arrS) - 1 'iterate between the arrS elements (except the last):
For nxtEl = i 1 To UBound(arrS) 'iterate between the arrS elements (starting from the second one):
If arrS(i) < arrS(nxtEl) Then 'sort the arrays according to the element values (< means descending)
tmp = arrS(i): tmpN = arrN(1, i) 'memorize the element temporaty walue
For k = 0 To UBound(arrTemp): arrTemp(k) = dict(k 1)(1, i): Next k 'do the same for each dictionary item
arrS(i) = arrS(nxtEl): arrN(1, i) = arrN(1, nxtEl)
For k = 1 To dict.count - 1 'the arrays content of a dictionary item cannot be changed directly!!!
arrT = dict(k): arrT(1, i) = dict(k)(1, nxtEl): dict(k) = arrT 'it cam be changed in this way
Next k
arrS(nxtEl) = tmp: arrN(1, nxtEl) = tmpN 'switch the array element value to the memorized one
For k = 1 To dict.count 'do the same in each dictionary item array:
arrT = dict(k): arrT(1, nxtEl) = arrTemp(k - 1): dict(k) = arrT
Next k
End If
Next nxtEl
Next i
End Sub
作業表Change事件應該有必要的手動調整。它可以自動確定，但是，為了避免在最后一行上為每個添加的標題或值運行代碼，還應該定位一個特殊的單元格，并且在Sub添加新范圍時跳過運行的事件。讓我們說“停止”這個詞。洗掉后，一切都應該正常作業（自動計算lastR和LastCol與上述代碼類似）。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/369560.html