大量データの計算

質問

ACCESS、EXCEL初心者です。
大量データの計算を、ACCESSやEXCELで計算したいのですが、うまくいく方法が思いつきません。
アイデアをいただけませんでしょうか。

■やりたいこと
１．300行5050列のデータがある（EXCELもしくはCSV）
２．300行のデータに対し、全通りの組み合わせを作る（300×300=90,000行）
３．２の組み合わせより、全項目どうしの比率（割り算）を求める

例）
・以下の3×4のデータがあったとします
　A　11,12,13,14
　B　21,22,23,24
　C　31,32,33,34
・得たい結果は以下です（順不同）
　組み合わせAB　11÷21,12÷22,13÷23,14÷24
　組み合わせAC　11÷31,12÷32,13÷33,14÷34
　組み合わせBA　21÷11,22÷12,23÷13,24÷14
　組み合わせBC　21÷31,22÷32,23÷33,24÷34
　組み合わせCA　31÷11,32÷12,33÷13,34÷14
　組み合わせCB　31÷21,32÷22,33÷23,34÷24
　（組み合わせAA,BB,CCは省略）

現在EXCELで手作業にて組み合わせを作り、計算を行っているのですが、
データ量が多いためか、途中でフリーズしてしまうため、20ファイル程度に分けて計算しております。
毎回手作業で組み合わせの作成とファイルの分割、結合を行うため、作業に数日かかってしまいます。
ACCESSであれば、テーブルの結合により、組み合わせを自動で作れますが、
255列以上のデータが作れないため、CSVデータの読み込みができませんでした。

300×5,050のデータに対し、ファイルを分けることなく
・自動で組み合わせを作る
・自動で計算結果を出力する
ということが実現できませんでしょうか。

回答No.8

ushi2015

こんにちは
Sheet1のデータを、C:\temp フォルダの中に、test1.csv として出力します。
Sub test1()
　　Dim w As Variant
　　Dim x As Variant
　　Dim h As Long
　　Dim i As Long
　　Dim j As Long
　　Dim k As Variant
　　Dim sh1 As Worksheet
　　Dim s As Single

　　Open "C:\temp\test1.csv" For Output As #1

　　s = Timer

　　Set sh1 = Worksheets("Sheet1")

　　x = sh1.Range("A1").Resize(300, 5050).Value

　　For h = 1 To 3 '00
　　　　w = sh1.Cells(h, 1).Resize(, 5050).Value
　　　　k = 0
　　　　For i = 1 To 300
　　　　　　If i <> h Then
　　　　　　　　k = k + 1
　　　　　　　　For j = 1 To 5050
　　　　　　　　　　If x(i, j) = 0 Then
　　　　　　　　　　　　Print #1, 0;
　　　　　　　　　　Else
　　　　　　　　　　　　Print #1, Trim(Round(w(1, j) / x(i, j), 3));
　　　　　　　　　　End If
　　　　　　　　　　If j = 5050 Then
　　　　　　　　　　　　Print #1, ""
　　　　　　　　　　Else
　　　　　　　　　　　　Print #1, ",";
　　　　　　　　　　End If
　　　　　　　　Next
　　　　　　End If
　　　　Next
　　　　Erase w
　　　　DoEvents
　　Next
　　Close #1
　　Debug.Print Timer - s
End Sub
時間はWin7、CPU遅め、メモリ4Gで、20分以上掛かりますし、
ファイルサイズは計算結果を小数点以下3桁にしても2GB以上になります。

回答No.7

imogasi

#3です。処理の最初に、セルのデータをすべて配列に入れて、その処理は配列データを対象に行う例。たった１行でデータの配列に入れられる。
自分のデータ数や場所などの場合に合わせてコードを修正して、何万ものデータで、それなりの時間で終わるかやってみてください。この修正の辺に少々のＶＢＡの少々の経験が要る。
ーーーー
Sub test02()
'下記コードでは、Sheet3にデータを入れて、アクチブにして実行のこと＝＝＞注意。守らないとエラーになる。
'結果はＳｈｅｅｔ４に出る
'----
Dim X As Variant
komokusu = 6 'A-F列の６種・６列
n = 1 'Sheet4のアウトプット行のポインタ　初期行の値設定
X = Range("A1:F7") 'この１行で配列に入れられる
'---
For j = 1 To 7
For i = 1 To 7
If i = j Then '自分自身との組み合わせはスキップ
GoTo p1
Else
'アウトプットシートはＳｈｅｅｔ４
Sheets("Sheet4").Cells(n, "A") = j & "=" & i 'A列　見出し項目
For k = 1 To komokusu '計算結果はSheet4のＢ列より右列へ
Sheets("Sheet4").Cells(n, 1 + k) = X(j, k) / X(i, k)
Next k
n = n + 1 '次のアウトプット、はＳｈｅｅｔ４の次行へ行う用意
End If
p1:
Next i
Next j
End Sub
ーーーー
これは、田中先生の
http://officetanaka.net/excel/vba/speed/s11.htm
の記事の　Test2 が念頭にあります。

回答No.6

kkkkkm

No4,No5です。

元のデータを1010列ずつ5個のファイルに取り出すコードです。No4のコードの下にでも記載してください。
ThisWorkbook.Sheets("Sheet1")
のSheet1の方は5050列あるシートの名前に変更してください。

Sub bunkatu()
Dim retu As Long
Dim FName As String

For retu = 1 To 5050 Step 1010
Workbooks.Add
FName = "Data" & retu & ".xlsx"
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & FName
With ThisWorkbook.Sheets("Sheet1")
.Range(.Cells(1, retu), .Cells(300, retu + 1009)).Copy
End With
Workbooks(FName).Sheets("Sheet1").PasteSpecial
Workbooks(FName).Save
Application.CutCopyMode = False
Next retu
End Sub

回答No.5

kkkkkm

No4です
一部訂正があります。

With Sheets("Sheet1")
MyData = Range(Cells(1, retu), Cells(89700, retu + 100))
End With
を以下のように訂正してください。
With Sheets("Sheet1")
MyData = .Range(.Cells(1, retu), .Cells(89700, retu + 100))
End With

回答No.4

kkkkkm

エクセル2013ですが5050列一度にはメモリ不足という事でできませんでした。
ファイルを分けることなくという事でしたが、元のデータの列を1010列ごとに分割して実行でよろしければ以下のコードExapmle()を標準モジュール(ALTL+F11を押してして開いた画面でMicrosofot Excel Obojectの所を右クリックして挿入→標準モジュール)に記載してぞれぞれ分割したデータごとに実行してください。

Dim MyAns(89700, 101) をDim MyAns(89700, 5050)にしてメモリ不足が出なければExapmle2()の方でいけるかもしれませんが、多分実行途中で「この操作を完了するためのメモリが不足してます」というエラーが出るのではないかと思います。

Sheet1は元のデータのあるシート名Sheet2は計算結果を表示するシート名にそれぞれ書き換えてください。
計算結果が小数点以下4位まででよろしければ
Dim MyAns(89700, 101) As Double
を
Dim MyAns(89700, 101) As Currency
に変更してください。

Option Base 1
Sub Exapmle()
Dim retu As Long
Dim MyData As Variant
Dim MyAns(89700, 101) As Double
Dim i As Long, j As Long, k As Long, l As Long, m As Long, n As Long

For retu = 1 To 1010 Step 101
With Sheets("Sheet1")
MyData = Range(Cells(1, retu), Cells(89700, retu + 100))
End With
l = 1
n = 1
For i = 1 To 300
m = i + 1
If n > 1 Then
m = 1
End If
For j = 1 To 300
If m = i Then
m = m + 1
End If
If m > 300 Then
Exit For
End If
For k = 1 To 101
MyAns(l, k) = MyData(i, k) / MyData(m, k)
Next k
l = l + 1
m = m + 1
n = n + 1
Next j
Next i
With Sheets("Sheet2")
.Range(.Cells(1, retu), .Cells(89700, retu + 100)) = MyAns
End With
Next retu
MsgBox "終了しました", vbInformation
End Sub

Option Base 1
Sub Exapmle2()
Dim MyData As Variant
Dim MyAns(89700, 5050) As Double
Dim i As Long, j As Long, k As Long, l As Long, m As Long, n As Long

With Sheets("Sheet1")
MyData = Range(Cells(1, 1), Cells(89700, 5050))
End With
l = 1
n = 1
For i = 1 To 300
m = i + 1
If n > 1 Then
m = 1
End If
For j = 1 To 300
If m = i Then
m = m + 1
End If
If m > 300 Then
Exit For
End If
For k = 1 To 5050
MyAns(l, k) = MyData(i, k) / MyData(m, k)
Next k
l = l + 1
m = m + 1
n = n + 1
Next j
Next i
With Sheets("Sheet2")
.Range(.Cells(1, 1), .Cells(89700, 5050)) = MyAns
End With
MsgBox "終了しました", vbInformation
End Sub

回答No.3

imogasi

>ACCESS、EXCEL初心者です。
アクセスやエクセルの知識というよりは、プログラムの経験がないと、プログラムを１から独習していたら大変だ。大学院などなら、教室担当助手のような人に教えてもらわないと、時間的にも許容範囲に処理は出きないだろう。
繰り返しの多いものは、省力化はプログラムを使うことを考えないとダメなのは常識。
また配列というものの理解は必須だろう。
ここでは一番とっつきやすいスクリプト言語で、かつエクセルに親和性のあるＶＢＡで例を挙げてやってみる。下記コードがチンプンカンプンなら先は長いと思う。
基本的な処理パターンの少数版の理解の助けになれば。
まずＦｏｒＮｅｘｔの２重ネストについて勉強してみて。
原データがどういうものか、結果をどういう使い方をするのか、判らない（質問に書いてない）のであくまで、匂いだけです。質問者は理系のにおいはする（文系ではこんな問題はあまり出ない）が。
計算式や、組み合わせなどについて間違いだあれば修正して。
ＣＳＶデータやアクセスのテーブルデータをエクセルのシートに取り入れるのは簡単だから勉強してください。
エクセルのシート上の
Ａ１：Ｆ７のテストデータ
11 12 13 14 12 16
21 22 23 24 14 15
31 32 33 34 16 14
31 32 33 34 18 13
42 44 46 48 20 12
42 44 46 48 22 11
34 44 45 48 49 50
上記の例では、組合わせとしては、この場合は、７ｘ（７－１）＝４２種になるのかな。
下記はシートのデータを対象にしていますが、シートのデーター＞配列に乗せるのは簡単。
しかしデータ数がやや多いので、処理時間的に許容限度内かどうかは、やってみて、
うまく行かない時は、他の方法を考え直さないと仕方がない。
処理プログラムの使用メモリの方は大丈夫だろう。
Sub test01()
komokusu = 6 'A-F列の６種
n = 1 'アウトプット行ポインタ
For j = 1 To 7　’７行データ
For i = 1 To 7
If i = j Then
GoTo p1
Else
'Cells(n, "k") = Cells(j, "A") & Cells(i, "A")
Cells(n, 10) = j & "=" & i
For k = 1 To komokusu
Cells(n, 10 + k) = Cells(j, k) / Cells(i, k)
Next k
n = n + 1
End If
p1:
Next i
Next j
End Sub
結果
1=2 0.523809524 0.545454545 0.565217391 0.583333333 0.857142857 1.066666667
1=3 0.35483871 0.375 0.393939394 0.411764706 0.75 1.142857143
1=4 0.35483871 0.375 0.393939394 0.411764706 0.666666667 1.230769231
1=5 0.261904762 0.272727273 0.282608696 0.291666667 0.6 1.333333333
1=6 0.261904762 0.272727273 0.282608696 0.291666667 0.545454545 1.454545455
1=7 0.323529412 0.272727273 0.288888889 0.291666667 0.244897959 0.32
2=1 1.909090909 1.833333333 1.769230769 1.714285714 1.166666667 0.9375
以下第４２行まで略。

回答No.2

Nebusoku3

四則演算だけであればEXCEL関数で十分ではないでしょうか。
ご希望の形とは異なるかも知れませんが案を添付してみます。

EXCEL（添付図）で
B6の部分に関数　fx = B1/B2
B7の部分に関数　fx = B1/B3
B8の部分に関数　fx = B2/B1
B9の部分に関数　fx = B2/B3
B10の部分に関数　fx = B3/B1
B11の部分に関数　fx = B3/B2
を書き込みますとB列が計算されますね。　B6からB11までのセルをコピーし、C6からE6までに貼り付ければ添付図のようになります。　ご希望のものがこんな内容ではないかもしれませんがもし、応用が利くようであればお試しください。

※なお、ACCESSではデータの追加取り込みが可能ですが、それもお試しになられましたでしょうか。

回答No.1

catpow

いくつか方法があると思います。

【１】ExcelVBAでプログラムを組んで計算する。

計算処理を、Excel組み込みのBasic言語を使って作成する。
これですと、プログラムを作成することで、一気に処理できると思います。

【２】Perl, RubyとかＣ＃などの別プログラムで作成して計算する

ＣＳＶ出力したファイルをPerl, RubyとかＣ＃で読み込んで計算する。

なお、Ｃ＃だと、直接にExcelファイルにアクセスしてデータを読むことも、ネットで落とせるライブラリを使うことで、（プログラマにとっては）お手軽に実現できます。
さらに、Ｃ＃だと、エクセルでは難しい高度な処理を高速に実現可能になります。