| |
一応書いてみました。
ついでに、ピボットグラフを利用するとグラフもOKです。
Sub test()
Dim myRange As Range
Dim e
Dim pt As PivotTable
Set myRange = Range("A1", Range("C1").End(xlDown))
On Error Resume Next
Set pt = ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1")
e = Err.Number
On Error GoTo 0
If e <> 0 Then
Set pt = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=myRange).CreatePivotTable _
(TableDestination:=ActiveSheet.Cells(1, 5), _
TableName:="ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1", _
DefaultVersion:=xlPivotTableVersion10)
With pt.PivotFields("日付")
.Orientation = xlRowField
.Position = 1
End With
With pt.PivotFields("場所")
.Orientation = xlColumnField
.Position = 1
End With
pt.AddDataField pt.PivotFields("台数"), "合計 / 台数", xlSum
Else
pt.SourceData = myRange.Address
pt.RefreshTable
End If
'ピボットグラフの作成
Range("E1").Select
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("E1")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
ActiveChart.HasPivotFields = False
End Sub
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
