タイトル : Re: 2次元配列のクロス集計 投稿日 : 2015/04/15(Wed) 10:28 投稿者 : 魔界の仮面弁士
> データ例(srcData): 配列の並びが (x, y) なのか (row, col) なのか分かりませんが、 データ量の増加時に ReDim Preserve することを考えると前者かな…? > どのようなコードを記載すればよいでしょうか? 私なら、こうかな。 並び替えが必要な場合や、配列の縦横が実際とは逆な場合は 適宜読み替えてみてください。 '====== 元データ ====== Dim srcData() As String ReDim srcData(2, 7) srcData(0, 0) = "北海道": srcData(1, 0) = "09:00": srcData(2, 0) = "晴れ" srcData(0, 1) = "北海道": srcData(1, 1) = "10:00": srcData(2, 1) = "曇り" srcData(0, 2) = "青森" : srcData(1, 2) = "09:00": srcData(2, 2) = "曇り" srcData(0, 3) = "青森" : srcData(1, 3) = "09:00": srcData(2, 3) = "曇り" srcData(0, 4) = "秋田" : srcData(1, 4) = "10:00": srcData(2, 4) = "雨" srcData(0, 5) = "岩手" : srcData(1, 5) = "09:00": srcData(2, 5) = "雨" srcData(0, 6) = "岩手" : srcData(1, 6) = "10:00": srcData(2, 6) = "曇り" srcData(0, 7) = "岩手" : srcData(1, 7) = "11:00": srcData(2, 7) = "晴れ" '====== 変換後 ====== Dim pivotData() As String Dim iX As Long, iY As Long Dim maxRecord As Long maxRecord = UBound(srcData, 2) If maxRecord < 0 Then MsgBox "No Data", vbExclamation Exit Sub End If '横軸に用いる「県名」、縦軸に用いる「時刻」を列挙 Dim dicX As Object, dicY As Object Set dicX = CreateObject("Scripting.Dictionary") Set dicY = CreateObject("Scripting.Dictionary") Dim n As Long For n = 0 To maxRecord If Not dicX.Exists(srcData(0, n)) Then dicX.Add srcData(0, n), dicX.Count + 1 If Not dicY.Exists(srcData(1, n)) Then dicY.Add srcData(1, n), dicY.Count + 1 Next '配列サイズ確定 ReDim pivotData(dicX.Count, dicY.Count) 'ヘッダー部の生成 pivotData(0, 0) = "時刻" For n = 1 To dicX.Count pivotData(n, 0) = dicX.Keys()(n - 1) Next For n = 1 To dicY.Count pivotData(0, n) = dicY.Keys()(n - 1) Next 'データ行の生成 For n = 0 To maxRecord iX = dicX(srcData(0, n)) iY = dicY(srcData(1, n)) pivotData(iX, iY) = srcData(2, n) Next '====== 結果確認 ====== Debug.Print Tab(1); String(10, "-") For iY = 0 To UBound(pivotData, 2) Debug.Print Tab(1); iY; ":"; For iX = 0 To UBound(pivotData, 1) Debug.Print iX; "="; pivotData(iX, iY), Next Next Debug.Print Tab(1); String(10, "=") |