[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『実行中に指定した任意の数だけ配列をつくるには』（めろす）
CSVファイルから読み込んだデータから必要な配列の数を見つけ、その数だけ配列を定義するにはどうしたらよいか教えていただけますでしょうか？使用ソフトはExcel2007、Windows Vistaです。
最終的には、下のリストのような処理をしたいと思っています。VBA経験薄なので、こつこつと部分毎に教えていただきながら仕上げるつもりです。
この投稿では、下の４の部分に的を絞って質問させていただきます。
１．CSVファイルのヘッダ行を読み込む ２．読み込んだヘッダ行を新規ワークシートフィールド毎のセルに分割して書き込む ３．ヘッダ行から、列数を割り出す ４．列数分の一次元配列を作る ５．各列の3行目以降最終行まで（数値データ部分）を配列に格納する ６．一列目の配列について、平均値、偏差値、最大値、etc数項目の統計値を計算する ７．ワークシートに書き出した該当するフィールド名の下に、５の計算値を書き出す ８．同じように残りの全ての列について５と６の作業を行う
処理対象のCSVファイルは複数あり、最初の2行がヘッダで、以下が数値という点は共通ですが、列の数、行の数はファイルによって異なります（列数は10〜100、行数は10000〜40000）。とりあえずは、一つの対象ファイルをファイル参照ウィンドウで選択してファイル単位で処理を行うスクリプトにしたいと思っています。
不定数の配列を作るには、列数の数だけループを回し、配列を作るサブルーチンを呼び出す、というのが現実的な方法でしょうか？
このような処理をするスクリプトのサンプルをまだ見たことがないのですが、よいサンプルがありましたら、教えていただければ幸いです。
ちなみに、上の３で一次元配列を使おうとしている理由は、データ量が多いからです。
また、一度データをワークシートに読み込んでしまえばエクセルのワークシート関数が使えますが、やはりデータ量が多いので時間がかかるのではないかと思い、列を丸ごと一次元配列に読み込んでから、できるだけ少ないループの中で処理をしてしまったほうがよいだろうという考えからです。
また、他にもっと賢い方法をご存じでしたら教えていただければ幸いです。よろしくお願いします。
とりあえず、列数でしたらLineInputで1行分だけ変数に取得してから CSVなので","でSplitすればその要素数+1が列数という事になると思いますが。 （ｍｏｍｏ）
アドバイスありがとうございます。質問に書いた３の、ヘッダを読み込んで列数を割り出すところまではどうにかできました。下のコードがそうです。
この後、CSVファイルのヘッダから読み取った列数、numColの数だけ一次配列を作り、CSVの3行目以下のデータのうち、ItemAのデータを配列Aに、ItemBのデータを配列Bに、ItemCのデータを配列Cに入れたいのですが、このように、実行中に配列の数を決めて定義する方法が分かりません。
もしご存じでしたら教えて下さい。
ちなみにcsvファイルは単純化していますが以下のような構造です。
ItemA, ItemB, ItemC mg, mm, degC 0.098, -7.850, -1.4232 0.251, -9.546, 0.02728 0.386, -7.462, 0.80192 2.440, -57.56, 1.4674 0.492, -10.69, -0.37535 0.474, -15.22, 0.24144
以下が途中までできているコードです。
Sub MelosTest()
Dim FileType, Prompt As String
Dim FileNamePath As Variant
Dim textline, csvline() As String
Dim Rowcnt, Colcnt, numCol As Integer
Dim ch1 As Long
Dim SummaryWS As Worksheet
FileType = "CSV ﾌｧｲﾙ (*.csv),*.csv"
Prompt = "CSV File を選択してください"
FileNamePath = SelectFileNamePath(FileType, Prompt)
If FileNamePath = False Then
End
End If
ch1 = FreeFile
Open FileNamePath For Input As #ch1
Set SummaryWS = Worksheets.Add(After:=Worksheets(Worksheets.Count))
SummaryWS.Name = "解析"
SummaryWS.Activate
'一行目を読み込み
Line Input #ch1, textline
'一行目を丸ごと入力し、最後のフィールドが空白でも読み込まれるよう、
'最後に",end"を追加する
Cells(1, 2) = textline & ",end"
'ダブルクォーテーションを削除
textline = Replace(textline, """", "")
'カンマで分離
csvline() = Split(textline, ",")
'配列渡しでセルに代入
'一列目は項目名フィールドとして空けておき、
'二列目からデータフィールド名を入れる
Range(Cells(1, 2), _
Cells(1, UBound(csvline()) + 2)) = csvline()
Rowcnt = Rowcnt + 1
'同じように二行目を読み込む
Line Input #ch1, textline
Cells(2, 2) = textline & ",end"
textline = Replace(textline, """", "")
csvline() = Split(textline, ",")
'一列目は解析項目フィールドなので、2列目からデータフィールドを入れる
Range(Cells(2, 2), _
Cells(2, UBound(csvline()) + 2)) = csvline()
'最終列が256列までと仮定し列数を数える
numCol = Range("IV" & 1).End(xlToLeft).Column - 1
Debug.Print "numCol = " & numCol
End Sub
よろしくお願いします。
（めろす）
持って居られるイメージの事は出来ると思いますが データを配列に入れて ＞６．一列目の配列について、平均値、偏差値、最大値、etc数項目の統計値を計算する この部分を計算する方法は確立しているのでしょうか？ etc数項目 がどの様な物なのか分かりませんが ワークシート関数で用意されているような計算をするのなら 単純にエクセルで開いて、数式を埋めて計算させて 結果だけを残して、不要部分は削除 と言う処理でも良さそうに思いますが。。。
＞４．列数分の一次元配列を作る ＞５．各列の3行目以降最終行まで（数値データ部分）を配列に格納する の部分を決行するなら、 ＞ItemAのデータを配列Aに、ItemBのデータを配列Bに、ItemCのデータを配列Cに と、配列名は問わない様なので 私なら、配列の中に配列を入れるかな。。。
↓シート上のA1セルから連続してデータがあって
3行目以降は数値しかない事を想定して居ます。
'------
Sub 配列の中に配列を()
Dim i As Long, ii As Long, tbl As Variant
Dim sr As Double, msg As String
Dim hh As Variant, x As Variant, cnt As Long
tbl = Range("A1").CurrentRegion.Value
'１．配列を入れる配列を作成（Item数分）
ReDim hh(1 To UBound(tbl, 2))
'２．何行有るか分からないだろうから、テキトーに作成して
ReDim x(1 To Rows.Count)
' これまたイイカゲンに配列の中にセット
For ii = 1 To UBound(tbl, 2)
hh(ii) = x
Next
'３．データを配列の中の配列に入れていく
For i = 3 To UBound(tbl, 1)
cnt = cnt + 1 'タブンUBound(tbl, 1)は事前に分からないだろうから、カウント
For ii = 1 To UBound(tbl, 2)
hh(ii)(cnt) = tbl(i, ii)
Next
Next
'４．例えば、合計
For i = 1 To UBound(hh, 1)
For ii = 1 To cnt
sr = sr + hh(i)(ii)
Next
msg = msg & i & " 番目の合計は " & sr & vbLf
sr = 0
Next
MsgBox msg
End Sub
'------
ちなみに、作成して居られるコードでItem数は 「UBound(csvline()) + 1」で得られると思いますよ？
上のコードは、仕組みが分かったら 実状に合わせて細かく・柔軟に修正してください。
もしも構想だけで１番から順に進んでいるのなら、先に ＞６．一列目の配列について、平均値、偏差値、最大値、etc数項目の統計値を計算する この部分を考えた方が良いと思いますが。 「SelectFileNamePath」みたいなのが有るのかな？
(HANA)
前にご質問に来られてたのですね。 [[20100317174148]] 『CSVファイルの２行目までだけ読み込みたい』（めろす）
この印象では「３行目から読み込みたい」 なんて質問が続きそうですが。。。
＞「SelectFileNamePath」みたいなのが有るのかな？ を想定して、４〜の部分を別の方法で書いてみます。
と言っても大した変更ではありませんが。。。 x = hh(1) とかで、hh(1)に入っている配列をxに取り出せます。
'------
'４．例えば、合計
For i = 1 To UBound(hh, 1)
x = hh(i)
For ii = 1 To cnt
sr = sr + x(ii)
Next
msg = msg & i & " 番目の合計は " & sr & vbLf
sr = 0
Next
MsgBox msg
'------
配列 x は
ReDim x(1 To Rows.Count)
なんてテキトーなことをしているので
受け渡し前には ReDim Preserve が必要かもしれませんが
これはまたもう少し先の話ですね。
(HANA) すみません、コード修正しました。 2010/3/21 15:45
連休中すっかり離れていたのでお返事が遅くなり、すみません。
素晴らしいアイデアをご提案くださり、ありがとうございます。配列の中に配列を作る、という方法は私も考えていたのですが、いくつの配列を作るか分からない状態で可能なのか分からなかったので、あえてトライしていませんでした。ご提案の方法で早速試してみます。
ワークシートに3行目以降のデータを読み込んで、ワークシート関数で計算する、という方法の方が、確かに簡単だと思います。実際にやってみました。ですが、データ量が多いためか、かなり時間がかかります。
ワークシート関数を使うとすると、関数を一回呼ぶ毎に、対象フィールドの全レコードをループで読むのですよね、おそらく。私のデータでやると、行数が1万行以上、フィールド数も数十、また、一フィールドにつき、10以上の統計値を計算するとなると、何百回ももループを回すことになり、相当な時間になると思います。
それよりは、データをワークシートに読み込まず、配列に入れてしまって、一回配列全体をループで読む間に、まとめて数種類の計算をさせてしまった方が効率的なのかな、と想像しました。
まだやってみていないので、実際どちらが効率的なのかは分かりませんが、試してみて判断したいと思います。
ともかく。ありがとうございました。
（めろす）
要するに・・・
１．全てをシートか配列に読み込んでから計算する。 ２．１行づつ読み込みながら計算して読み込んだデータは破棄していく。
の２通りある。という事ですね。
１の場合計算は簡単だがメモリーや時間の消費が激しい。 ２の場合計算ロジックの組み立てが必要だがメモリーも時間も少なくできる。
なので、HANAさんが仰るように計算の部分の組み立てが出来るのであれば 普通にLineInputで済んでしまうように思います。
（ｍｏｍｏ）
下のように、頂いたコードを修正して、私が作ったコードの中に入れてみました。
でも、一番肝心な、csvから読み込んだ個々の数値を”配列の中の配列”のに書き込む部分でエラーが出てしまいました。以下の一行の部分です。”型が一致しません”と出てきます。
hh(numRow)(i) = csvline(i)
どこが間違っているのか分かりません。もしできましたら、間違えた場所をご指摘いただけませんでしょうか？よろしくお願いします。
Sub MelosTest2()
Dim FileType, Prompt As String
Dim FileNamePath As Variant
Dim textline, csvline() As String 'とりあえずcsvline()というサイズの無い配列つくる
Dim Rowcnt, Colcnt, numCol As Integer
Dim ch1 As Long
Dim SummaryWS, DataWS As Worksheet
FileType = "CSV ﾌｧｲﾙ (*.csv),*.csv"
Prompt = "CSV File を選択してください"
FileNamePath = SelectFileNamePath(FileType, Prompt)
If FileNamePath = False Then
End
End If
ch1 = FreeFile
Open FileNamePath For Input As #ch1
’統計値等を書き込むワークシートを作る
Set SummaryWS = Worksheets.Add(After:=Worksheets(Worksheets.Count))
SummaryWS.Name = "Stats"
SummaryWS.Activate
'１．CSVファイルのヘッダ行を読み込む
'一行目を読み込み
Line Input #ch1, textline
'一行目を丸ごと入力し、最後のフィールドが空白でも読み込まれるよう、
'最後に",end"を追加する
Cells(1, 2) = textline & ",end"
'ダブルクォーテーションを削除
textline = Replace(textline, """", "")
'カンマで分離
csvline() = Split(textline, ",")
'２．読み込んだヘッダ行を新規ワークシートフィールド毎のセルに分割して書き込む
'配列渡しでセルに代入
'一列目は項目名フィールドとして空けておき、二列目からデータフィールド名を入れる
Range(Cells(1, 2), _
Cells(1, UBound(csvline()) + 2)) = csvline()
Rowcnt = Rowcnt + 1
'同じように二行目を読み込む
Line Input #ch1, textline
Cells(2, 2) = textline & ",end"
textline = Replace(textline, """", "")
csvline() = Split(textline, ",")
'一列目は解析項目フィールドなので、2列目からデータフィールドを入れる
Range(Cells(2, 2), _
Cells(2, UBound(csvline()) + 2)) = csvline()
'３．ヘッダ行から、列数を割り出す
'最終列が256列までと仮定すると最終列はIVなのでIV列から
'左に向かってのデータのある列の数を数える
numCol = Range("IV" & 1).End(xlToLeft).Column - 1
Debug.Print "numCol = " & numCol
'４．（列数分の一次元配列のつもりだったが） ' 3行目以降のデータを読み込む配列の配列を作る
'以下、HANAさんご提案のコードをカスタマイズ
'---------------
Dim i As Long, ii As Long, tbl As Variant
Dim sr As Double, msg As String
Dim hh As Variant, x As Variant, cnt As Long
tbl = Range("A1").CurrentRegion.Value
'１．配列を入れる配列を作成（Item数分）
ReDim hh(1 To numCol)
'２．何行有るか分からないだろうから、テキトーに作成して
' ReDim x(1 To Rows.Count)
' これまたイイカゲンに配列の中にセット
For ii = 1 To numCol
hh(ii) = x
Next
'列数が分かったので、csvline()の要素数を再定義
ReDim csvline(1 To numCol)
'データを配列の中の配列に入れていく
numRow = 1
Do Until EOF(ch1)
'三行目を読み込み
Line Input #ch1, textline
'ダブルクォーテーションを削除
textline = Replace(textline, """", "")
'カンマで分離
csvline() = Split(textline, ",")
'５．各列の3行目以降最終行まで（数値データ部分）を配列に格納する
'配列の配列hhに、csvline()に読み取った一行分のデータを入れる
i = 1
Do While i <= numCol
hh(numRow)(i) = csvline(i)
i = i + 1
Loop
numRow = numRow + 1
Loop
numRow = numRow - 1
'行数が分かったので、行数を再定義
ReDim x(1 To numRow)
' ６．平均値、偏差値、最大値、etc数項目の統計値を計算する
' 例えば、合計
For i = 1 To numRow
For ii = 1 To numCol
sr = sr + hh(i)(ii)
Next
msg = msg & i & " 番目の合計は " & sr & vbLf
sr = 0
Next
MsgBox msg
'----------------- 'HANAさんご提案のコードカスタマイズ部分終わり
End Sub
ご質問にあった、６．の統計値を計算する方法については、配列に読み込んでしまえば、どうにかなると思っているので（甘い？）、読み込み段階ができてから真剣に考えます。
どうかよろしくお願いします。
（めろす）
ｍｏｍｏ様
コメントありがとうございます。大体おっしゃる通りですが、正確に言うと、
１．すべてをシートに読み込んでからワークシート関数などを使って統計値を計算する ２．すべてを配列に読み込んでから原始的に数式で統計値を計算する ３．１行づつ読み込みながら計算して読み込んだデータは破棄していく
のオプションになるかと思いますが、３の方法は使わないと思います。なぜなら、標準偏差なども計算しようと思っているからです。標準偏差などは、平均値を出してから、各値と平均値との差を再度計算しなくてはならないので、すべてのデータをキープしておかなくてはなりません。
１と２とを比べると、２の方が、実質的に回すループの回数を少なくできるので、処理速度が速いとおもったのですが、どうなのでしょうね？
とりあえず、試してみます。
（めろす）
まぁ、VBAなんで 計算はどうにでも成ると思いますが。。。 私は、標準偏差を自分で計算しようとは思わないですけどね。 他の計算との兼ね合いも有るかもしれませんが。
ぱっと見たところ、寧ろ
tbl = Range("A1").CurrentRegion.Value
が要らなくて
' ReDim x(1 To Rows.Count)
が要ると思います。
また、
csvline() = Split(textline, ",")
では、要素が0から始まるので
hh(numRow)(i) = csvline(i-1)
・・・かな？
hh や x の要素を 0 から初めて 揃える方が良いかもしれませんが。
こちら（↓）は、直接セルの値を見ているので少し状況が違いますが こんな記事が有ります。
Office TANAKA - Excel VBA高速化テクニック[関数も使え] http://officetanaka.net/excel/vba/speed/s9.htm
(HANA)
あんまり言葉だけでも面白くないので、簡単なサンプルを作ってみました。 データの容量とかによってはメモリーは消費しますが、割と早めに動くかな？ と、思いますがあまり大容量のテスト用データを作れないので試してませんが。
ロジック的にはFSOのTextStreamで一気に読み込んでSplitで分割整理をかけて ２次元配列のデータを作ります。 あとはApplication.WorksheetfunctionでIndexを使えば列でも行でも計算はしやすいと思います。
めろすさんのデータで動くといいのですが。
Sub test()
Dim buf As Variant, buf2 As Variant, Dat() As Double
Dim i As Long, j As Long
With CreateObject("Scripting.FileSystemObject").GetFile("D:\Test.csv").OpenAsTextStream
buf = .ReadAll
.Close
End With
buf = Split(Left(buf, Len(buf) - 2), vbCrLf)
ReDim Dat(1 To UBound(buf) - 1, 1 To UBound(Split(buf(0), ",")) + 1)
For i = 0 To UBound(buf) - 2
buf2 = Split(buf(i + 2), ",")
For j = 0 To UBound(buf2)
Dat(i + 1, j + 1) = buf2(j)
Next j
Next i
With Application.WorksheetFunction
For i = 1 To UBound(Dat, 2)
MsgBox i & "列目の合計は" & .Sum(.Index(Dat, 0, i)) & vbCrLf & _
i & "列目の平均は" & .Average(.Index(Dat, 0, i))
Next i
End With
End Sub
（ｍｏｍｏ）
ご指摘ありがとうございます。おかげさまで、ご指摘の点以外の間違いにも気づき、直したら、うまくいきました。以下が成功コードです。
Sub MelosTest2()
Dim FileType, Prompt As String
Dim FileNamePath As Variant
Dim textline, csvline() As String 'とりあえずcsvline()というサイズの無い配列つくる
Dim Rowcnt, Colcnt, numCol As Integer
Dim ch1 As Long
Dim SummaryWS, DataWS As Worksheet
FileType = "CSV ﾌｧｲﾙ (*.csv),*.csv"
Prompt = "CSV File を選択してください"
FileNamePath = SelectFileNamePath(FileType, Prompt)
If FileNamePath = False Then
End
End If
ch1 = FreeFile
Open FileNamePath For Input As #ch1
'統計値等を書き込むワークシートを作る
Set SummaryWS = Worksheets.Add(After:=Worksheets(Worksheets.Count))
SummaryWS.Name = "Stats"
SummaryWS.Activate
'１．CSVファイルのヘッダ行を読み込む
'一行目を読み込み
Line Input #ch1, textline
'一行目を丸ごと入力し、最後のフィールドが空白でも読み込まれるよう、
'最後に",end"を追加する
Cells(1, 2) = textline & ",end"
'ダブルクォーテーションを削除
textline = Replace(textline, """", "")
'カンマで分離
csvline() = Split(textline, ",")
'２．読み込んだヘッダ行を新規ワークシートフィールド毎のセルに分割して書き込む
'配列渡しでセルに代入
'一列目は項目名フィールドとして空けておき、二列目からデータフィールド名を入れる
Range(Cells(1, 2), _
Cells(1, UBound(csvline()) + 2)) = csvline()
Rowcnt = Rowcnt + 1
'同じように二行目を読み込む
Line Input #ch1, textline
Cells(2, 2) = textline & ",end"
textline = Replace(textline, """", "")
csvline() = Split(textline, ",")
'一列目は解析項目フィールドなので、2列目からデータフィールドを入れる
Range(Cells(2, 2), _
Cells(2, UBound(csvline()) + 2)) = csvline()
'３．ヘッダ行から、列数を割り出す
'最終列が256列までと仮定すると最終列はIVなのでIV列から
'左に向かってのデータのある列の数を数える
numCol = Range("IV" & 1).End(xlToLeft).Column - 1
Debug.Print "numCol = " & numCol
'４．（列数分の一次元配列のつもりだったが） ' 3行目以降のデータを読み込む配列の配列を作る
'以下、HANAさんご提案のコードをカスタマイズ
'---------------
Dim i As Long, ii As Long, tbl As Variant
Dim sr As Double, msg As String
Dim hh As Variant, x As Variant, cnt As Long
tbl = Range("A1").CurrentRegion.Value
'１．配列を入れる配列を作成（Item数分）
ReDim hh(1 To numCol)
'２．何行有るか分からないだろうから、テキトーに作成して
ReDim x(1 To Rows.Count)
' これまたイイカゲンに配列の中にセット
For ii = 1 To numCol
hh(ii) = x
Next
'列数が分かったので、csvline()の要素数を再定義
ReDim csvline(1 To numCol)
'データを配列の中の配列に入れていく
numRow = 1
Do Until EOF(ch1)
'三行目を読み込み
Line Input #ch1, textline
'ダブルクォーテーションを削除
textline = Replace(textline, """", "")
'カンマで分離
csvline() = Split(textline, ",")
'５．各列の3行目以降最終行まで（数値データ部分）を配列に格納する
'配列の配列hhに、csvline()に読み取った一行分のデータを入れる
i = 1
Do While i <= numCol
hh(i)(numRow) = Val(csvline(i - 1))
Debug.Print "hh(" & i & ")(" & numRow & ") = " & hh(i)(numRow)
i = i + 1
Loop
numRow = numRow + 1
Loop
numRow = numRow - 1
Debug.Print numRow
'行数が分かったので、行数を再定義
ReDim x(1 To numRow)
' ６．平均値、偏差値、最大値、etc数項目の統計値を計算する
' 例えば、合計
SummaryWS.Cells(3, 1) = "合計"
For i = 1 To numCol
For ii = 1 To numRow
sr = sr + hh(i)(ii)
Next
SummaryWS.Cells(3, i + 1) = sr
sr = 0
Next
'----------------- 'HANAさんご提案のコードカスタマイズ部分終わり
End Sub
とりあえず、５の段階までクリアし、６の段階の試験的計算までできました。
ありがとうございました。
（めろす）
p.s. VBAの高速化について、おもしろそうな記事を紹介してくださり、 ありがとうございます。6の段階までいったら、ワークシート関数を使ったのと、 配列のみでやるのと、両方試してみようと思います。
おお、スマートそうなサンプルコードを作ってくださり、ありがとうございます！
まず理解して（これに時間かかりそうですが）、私のデータで試してみます。（めろす）
ネックはやはり、データ量なのかもしれませんが。。。 ↓開いて計算式を埋め込むコードです。
'------
Sub 開いて数式で()
Dim FileNamePath As String, mxr As Long, mxc As Long
'ファイルを指定
FileNamePath = Application.GetOpenFilename("Excelファイル(*.csv),*.csv")
If FileNamePath = "False" Then
MsgBox "キャンセルされました。"
Exit Sub
End If
'ファイルを開く
Workbooks.Open Filename:=FileNamePath
Application.ScreenUpdating = False '画面更新停止
'データの最終行列を取得
mxc = Cells(1, Columns.Count).End(xlToLeft).Column
mxr = Cells(Rows.Count, 1).End(xlUp).Row
'一列挿入して見出しをセット
Application.Calculation = xlManual '計算方法手動
Range("A:A").Insert Shift:=xlToRight
Range("A" & mxr + 1).Resize(3).Value = _
Application.Transpose(Array("平均", "標準偏差", "最大値"))
'それぞれ数式を入力
Range("B" & mxr + 1).Resize(, mxc).Formula = _
"=AVERAGE(B3:B" & mxr & ")" '平均値
Range("B" & mxr + 2).Resize(, mxc).Formula = _
"=STDEVA(B3:B" & mxr & ")" '標準偏差
Range("B" & mxr + 3).Resize(, mxc).Formula = _
"=MAX(B3:B" & mxr & ")" '最大値
ActiveSheet.Calculate '再計算
'値化
Range("B" & mxr + 1).Resize(3, mxc).Value = _
Range("B" & mxr + 1).Resize(3, mxc).Value
'データ部分削除
Range("3:" & mxr).Delete Shift:=xlUp
Application.Calculation = xlAutomatic '計算方法自動
'シート名変更＆移動
Sheets(1).Name = "解析"
With ThisWorkbook
Sheets("解析").Move After:=.Sheets(.Worksheets.Count)
End With
Application.ScreenUpdating = True '画面更新再開
End Sub
'------
・・・って思ったんですけど、「偏差値」って「標準偏差」の事とは違うんですかね。 関数が無いなら、地道に計算が必要なので、配列のものなんですね？
(HANA)
また新しいコードをご提案下さり、本当にありがとうございます。
別な用事があり、すぐに手を付けられないのですが、後ほど是非試させていただきます。
ご助言ほんとに感謝いたします。
（めろす）
教えていただいたコードを試してみました。ワークシートに読み込んでからワークシート関数で計算しても、速くできますね！驚きました。ほとんど私がやりたかった通りの事が、こんな少しのコードでできてしまうとは。
遅かったのは、n番目に大きい（小さい）数値を抽出するユーザ関数でした。先日こちらで教えていただいたいくつかの方式(https://www.excel.studio-kazu.jp/kw/20100310185601.html?t=205943)をいくつか試したのですが、40列、17000行くらいのデータでやると、数分かかったりします。それでも、弥太郎さんに作っていただいたスクリプトだと、でも割と速く（30秒くらい？）で計算してくれました。
以下が、私がとりあえず私がやってみたかった計算です。
'------
Sub MelosTest3()
Dim FileNamePath As String, mxr As Long, mxc As Long
'ファイルを指定
FileNamePath = Application.GetOpenFilename("Excelファイル(*.csv),*.csv")
If FileNamePath = "False" Then
MsgBox "キャンセルされました。"
Exit Sub
End If
'ファイルを開く
Workbooks.Open Filename:=FileNamePath
Application.ScreenUpdating = False '画面更新停止
'データの最終行列を取得
mxc = Cells(1, Columns.Count).End(xlToLeft).Column
Debug.Print "mxc = " & mxc
mxr = Cells(Rows.Count, 1).End(xlUp).Row
Debug.Print "mxr = " & mxr
'一列挿入して見出しをセット
Application.Calculation = xlManual '計算方法手動
Range("A:A").Insert Shift:=xlToRight
Range("A" & mxr + 1).Resize(13).Value = _
Application.Transpose(Array("空白セル数", "平均", "標準偏差", "最大値", "第２最大値", _
"第３最大値", "第４最大値", "第５最大値", "第５最小値", "第４最小値", _
"第３最小値", "第２最小値", "最小値"))
'データ行の下1行目、2行目・・・にそれぞれ数式を入力
Range("B" & mxr + 1).Resize(, mxc).Formula = _
"=COUNTBLANK(B3:B" & mxr & ")" '空白セル数
Range("B" & mxr + 2).Resize(, mxc).Formula = _
"=AVERAGE(B3:B" & mxr & ")" '平均値
Range("B" & mxr + 3).Resize(, mxc).Formula = _
"=STDEV(B3:B" & mxr & ")" '標準偏差
Range("B" & mxr + 4).Resize(, mxc).Formula = _
"=MAX(B3:B" & mxr & ")" '最大値
Range("B" & mxr + 5).Resize(, mxc).Formula = _
"=MelosTest3.xls!GetNthLargest(B3:B" & mxr & ", 2)" '第２最大値
Range("B" & mxr + 6).Resize(, mxc).Formula = _
"=MelosTest3.xls!GetNthLargest(B3:B" & mxr & ", 3)" '第３最大値
Range("B" & mxr + 7).Resize(, mxc).Formula = _
"=MelosTest3.xls!GetNthLargest(B3:B" & mxr & ", 4)" '第４最大値
Range("B" & mxr + 8).Resize(, mxc).Formula = _
"=MelosTest3.xls!GetNthLargest(B3:B" & mxr & ", 5)" '第５最大値
Range("B" & mxr + 9).Resize(, mxc).Formula = _
"=MelosTest3.xls!GetNthSmallest(B3:B" & mxr & ", 5)" '第５最小値
Range("B" & mxr + 10).Resize(, mxc).Formula = _
"=MelosTest3.xls!GetNthSmallest(B3:B" & mxr & ", 4)" '第４最小値
Range("B" & mxr + 11).Resize(, mxc).Formula = _
"=MelosTest3.xls!GetNthSmallest(B3:B" & mxr & ", 3)" '第３最小値
Range("B" & mxr + 12).Resize(, mxc).Formula = _
"=MelosTest3.xls!GetNthSmallest(B3:B" & mxr & ", 2)" '第２最小値
Range("B" & mxr + 13).Resize(, mxc).Formula = _
"=MIN(B3:B" & mxr & ")" '最小値
ActiveSheet.Calculate '再計算
'値化
Range("B" & mxr + 1).Resize(13, mxc).Value = _
Range("B" & mxr + 1).Resize(13, mxc).Value
'データ部分削除
Range("3:" & mxr).Delete Shift:=xlUp
Application.Calculation = xlAutomatic '計算方法自動
'シート名変更＆移動
Sheets(1).Name = "解析"
With ThisWorkbook
'以下のコードはなぜかバグります（めろす）
'Sheets("解析").Move After:=.Sheets(.Worksheets.Count)
End With
Application.ScreenUpdating = True '画面更新再開
End Sub
'------
Function GetNthLargest(rng As Range, n As Integer)
' もとの名前はFunction Hasire(rng As Range, n As Integer)
'（作成者：弥太郎さん）
'2010/3/10
'https://www.excel.studio-kazu.jp/kw/20100310185601.html?t=205943
'------------------------
Dim dic As Object, i As Long, tbl, x()
Set dic = CreateObject("scripting.dictionary")
tbl = rng.Value
For i = 1 To UBound(tbl, 1)
If tbl(i, 1) <> "" Then
dic(tbl(i, 1)) = Empty
End If
Next i
GetNthLargest = Application.Large(dic.keys, n)
End Function
Function GetNthSmallest(rng As Range, n As Integer)
'-=Hasire(rng As Range, n As Integer)を小さい方に修正
'2010/3/10
'------------------------
Dim dic As Object, i As Long, tbl, x()
Set dic = CreateObject("scripting.dictionary")
tbl = rng.Value
For i = 1 To UBound(tbl, 1)
If tbl(i, 1) <> "" Then
dic(tbl(i, 1)) = Empty
End If
Next i
GetNthSmallest = Application.Small(dic.keys, n)
End Function
せっかくなので、これからｍｏｍｏさんに提案していただいたコードでも同じ処理を試してみようと思います。
どうもありがとうございました！
（めろす）
たぶん、速度では単純なOpen＋ワークシート関数には敵わないですね。 ネイティブな機能の方がほとんどの場合高速ですので。
私のコードは単に配列に取り込む考え方だけのものですので。 ちなみに配列に取り込む場合でも普通に開いていきなりVariant型の変数に取り込んだ方が早いと思います。
で、XL2007以前のエクセルで1枚のシートに収まりきらないようなデータを配列に取り込む場合なんかには 私のコードの有効性が出てくるんだと思います。
時と場合によりけり。という事でしょうね。
（ｍｏｍｏ）
すみません。以下のところを、フォルダ参照ウィンドウから開きたいファイルを指定するには、どうしたらよいか教えていただけますか？
With CreateObject("Scripting.FileSystemObject").GetFile("D:\Test.csv").OpenAsTextStream
buf = .ReadAll
.Close
End With
基本的な質問ですみません。
（めろす）
開いて計算させても大丈夫でしたか。
スピードは分かりませんが、その様なシート構成に成っているなら 計算式の部分は、以下の様にしても良さそうです。 ※第２〜５最大値 と 第５〜２最小値 の式以外の部分は同じです。
'------
'データ行の下1行目、2行目・・・にそれぞれ数式を入力
Range("B" & mxr + 1).Resize(, mxc).Formula = _
"=COUNTBLANK(B3:B" & mxr & ")" '空白セル数
Range("B" & mxr + 2).Resize(, mxc).Formula = _
"=AVERAGE(B3:B" & mxr & ")" '平均値
Range("B" & mxr + 3).Resize(, mxc).Formula = _
"=STDEV(B3:B" & mxr & ")" '標準偏差
Range("B" & mxr + 4).Resize(, mxc).Formula = _
"=MAX(B3:B" & mxr & ")" '最大値
Range("B" & mxr + 5).Resize(4, mxc).Formula = _
"=LARGE(B$3:B$" & mxr & ",COUNTIF(B$3:B$" & mxr & ","">=""&B" & mxr + 4 & ")+1)"
'第２〜５最大値
Range("B" & mxr + 9).Resize(4, mxc).Formula = _
"=SMALL(B$3:B$" & mxr & ",COUNTIF(B$3:B$" & mxr & ",""<=""&B" & mxr + 10 & ")+1)"
'第５〜２最小値
Range("B" & mxr + 13).Resize(, mxc).Formula = _
"=MIN(B3:B" & mxr & ")" '最小値
'------
遅くなるかな。。。？
ただ、普通に開けるみたいなので、開いて 配列に取り込んだ後、弥太郎さんのコードを参考に 一列毎に処理をしていくともう少し早いと思います。 今は、一つのセルに値を出す毎に処理をしていますので。
(HANA)
Dim fname As String
fname = Application.GetOpenFilename
If fname <> "False" Then
Workbooks.Open FileName:=fname
End If
以下のページに書いてありました。
http://www.officepro.jp/excelvba/book_new/index3.html
（めろす）
おお、もっと処理を効率化する方法があったのですね。
ありがとうございます。早速利用させていただきます。
（めろす）
いや、この式に変更して早くなるかどうかは分かりません。
「処理の効率化」と言う事であれば
ユーザー定義関数の
Set dic = CreateObject("scripting.dictionary")
tbl = rng.Value
For i = 1 To UBound(tbl, 1)
If tbl(i, 1) <> "" Then
dic(tbl(i, 1)) = Empty
End If
Next i
この部分は、例えば 4列有った場合 4回行われれば良いですが
現在は、各セルに答えを出すたびに行われるので
4×8回 ＝ 32回行われていますね？
40列有ったら 40×8回
更に、中のループを考えると 17000行有ったら 17000×40×8回 ＝ 5440000 です。 列毎に処理をするなら 17000×40 ＝ 680000 で済みますね。
先の御質問時に、これらの情報（データ量・一度に求める個数・データ配置等） が載せられていたら、また違った回答が寄せられていたと思います。
(HANA)
そう言えば、先の質問時に ＞以下の様に、空白セルのある入力値の範囲から と成ってますね。。。
これは、「どの列に最終行が有るか分からない」と言う事でしょうか？ それとも、「間が抜けている事は有っても、最後の行は揃っている」 と思っても良いのでしょうか？
最後の行が揃っていない場合、極端な話をすると A列は100行目までしかデータが無いけど B列は1000行までデータがある なんて事態があり得そうですが、そうですか？
その場合、現在 データの有る最終行を ＞mxr = Cells(Rows.Count, 1).End(xlUp).Row で取得していますが、別の方法をとるのが良さそうです。
(HANA)
HANAさんのコードをお借りして こんな感じで。
Dim FileNamePath As String, mxr As Long, mxc As Long
FileNamePath = Application.GetOpenFilename("Excelファイル(*.csv),*.csv")
If FileNamePath = "False" Then
MsgBox "キャンセルされました。"
Exit Sub
End If
With CreateObject("Scripting.FileSystemObject").GetFile(FileNamePath).OpenAsTextStream
buf = .ReadAll
.Close
End With
（ｍｏｍｏ）
なるほど。具体的な例を挙げて効率の違いを説明してくださってありがとうございます。
私もぼんやりと、実際に実行されるループの数が少ない方が速かろうなぁ、と思っていましたが、そこまで具体的には考えませんでした。
いっぺんに沢山聞くと大きな問題を丸投げしてるように思われるかと思って、質問を小出しにしていたのですが、かえって回り道になってしまいましたね。すみません。
＞そう言えば、先の質問時に ＞＞以下の様に、空白セルのある入力値の範囲から ＞ と成ってますね。。。
＞ これは、「どの列に最終行が有るか分からない」と言う事でしょうか？ ＞ それとも、「間が抜けている事は有っても、最後の行は揃っている」 ＞ と思っても良いのでしょうか？
たしかに、最終行のセルに値のないセルも十分ありえますね。ですが、私が今相手にしているデータに関して言うと、一列目は時系列の時間にあたるデータなので、空白はあり得ません。A列の最終セルを最終行と見なせば間違いない、というデータです。A列だけ見て最終行を見つければよいのですね。
注意してくださってありがとうございます。
（めろす）
ファイルの指定の仕方を教えてくださってありがとうございます。
（めろす）
それで、実行時間はどうでしたか？ せっかくなので、早くなってると良いのですが。
＞質問を小出しにしていたのですが、かえって回り道になってしまいましたね。 何処の話をしていますか？
前の関数のお話でしたら、小出しにしていたから と言うよりは ご説明に不足があったから だと思いますが。 まぁ、説明があっても丁度良い回答が得られるかどうかは分かりませんけどね。
＞私が今相手にしているデータに関して言うと、一列目は時系列の時間にあたるデータなので、 ＞空白はあり得ません。 で、データは何列目からなんですか？ B列から始まるなら、数式はC列（旧B列）から入れるんじゃないですか？ 或いは、A列が列方向の見出しと考えるなら1列目の挿入をせずに処理をする。
あれ？そう言えば。。。この掲示板ですが 文頭に半角スペースを入れると、コメント欄で改行をした位置で 改行がそのまま表示されるってご存じでしたか？ _←この位置に、半角スペース。 意図的に自動折り返しにしてあるのかな。。。？ (HANA)
HANA様
ご報告遅くなって申し訳ありません。以下の(A)の2番目から5番目に大きい値を抽出する式と2番目から5番目に小さい値を抽出する式の同じ処理を、(B)のようにまとめた式に書き換えたときの実行時間ですが、約21秒から約4秒に短縮できました！
(A) -----------------------------------
Range("B" & mxr + 5).Resize(, mxc).Formula = _
"=MelosTest3.xls!GetNthLargest(B3:B" & mxr & ", 2)" '第２最大値
Range("B" & mxr + 6).Resize(, mxc).Formula = _
"=MelosTest3.xls!GetNthLargest(B3:B" & mxr & ", 3)" '第３最大値
Range("B" & mxr + 7).Resize(, mxc).Formula = _
"=MelosTest3.xls!GetNthLargest(B3:B" & mxr & ", 4)" '第４最大値
Range("B" & mxr + 8).Resize(, mxc).Formula = _
"=MelosTest3.xls!GetNthLargest(B3:B" & mxr & ", 5)" '第５最大値
Range("B" & mxr + 9).Resize(, mxc).Formula = _
"=MelosTest3.xls!GetNthSmallest(B3:B" & mxr & ", 5)" '第５最小値
Range("B" & mxr + 10).Resize(, mxc).Formula = _
"=MelosTest3.xls!GetNthSmallest(B3:B" & mxr & ", 4)" '第４最小値
Range("B" & mxr + 11).Resize(, mxc).Formula = _
"=MelosTest3.xls!GetNthSmallest(B3:B" & mxr & ", 3)" '第３最小値
Range("B" & mxr + 12).Resize(, mxc).Formula = _
"=MelosTest3.xls!GetNthSmallest(B3:B" & mxr & ", 2)" '第２最小値
---------------------------------
(B)------------------------------
Range("B" & mxr + 5).Resize(4, mxc).Formula = _
"=LARGE(B$3:B$" & mxr & ",COUNTIF(B$3:B$" & mxr & ","">=""&B" & mxr + 4 & ")+1)"
'第２〜５最大値
Range("B" & mxr + 9).Resize(4, mxc).Formula = _
"=SMALL(B$3:B$" & mxr & ",COUNTIF(B$3:B$" & mxr & ",""<=""&B" & mxr + 10 & ")+1)"
'第５〜２最小値
---------------------------------
ただ、一つ問題が。範囲に２種類しか数値がない場合、３番目に大きな値を出すべきところ、一番大きな値が出てきてしまいます。単独の式の場合、正直に、"#NUM!"というエラーが出てくるのですが。
＞＞私が今相手にしているデータに関して言うと、一列目は時系列の時間にあたるデータなので、 ＞＞空白はあり得ません。 ＞で、データは何列目からなんですか？ ＞B列から始まるなら、数式はC列（旧B列）から入れるんじゃないですか？ ＞或いは、A列が列方向の見出しと考えるなら1列目の挿入をせずに処理をする。
実のところ、解析の対象とするデータは４列目からです。一列目に”年”、二列目に”ユリウス日”、３列目に”時刻”が入っています。 おっしゃるとおり、最初の３列が列方向の見出しなので、計算の必要はないのですが、とりあえず、細かい部分は後で調整すればいいやってことで、無視してました。でもこの余計な計算を省けばもっと速く処理できますよね。
ところで、掲示板の書き方について、アドバイスありがとうございます。 （めろす）
ｍｏｍｏ様
少し前にｍｏｍｏさんに教えていただいた２次元配列に入れてしまうコードを以下の様に少し変えて試してみました。
Sub MelosTest4() Dim buf As Variant, buf2 As Variant, Dat() As Double Dim i As Long, j As Long Dim csvline() As String
Dim FileNamePath As String, mxr As Long, mxc As Long
FileNamePath = Application.GetOpenFilename("Excelファイル(*.csv),*.csv")
If FileNamePath = "False" Then
MsgBox "キャンセルされました。"
Exit Sub
End If
With CreateObject("Scripting.FileSystemObject").GetFile(FileNamePath).OpenAsTextStream
'bufという一次元配列に、データ全体を読み込んでしまう
buf = .ReadAll
.Close
End With
Dim SummaryWS As Worksheet Set SummaryWS = Worksheets.Add(After:=Worksheets(Worksheets.Count)) 'SummaryWS.Name = "解析" SummaryWS.Activate
'bufを改行で分割して1次元配列として書き直す buf = Split(Left(buf, Len(buf) - 2), vbCrLf)
csvline() = Split(buf(0), ",")
'配列渡しでセルに代入
SummaryWS.Range(Cells(1, 2), _
Cells(1, UBound(csvline()) + 2)) = csvline()
csvline() = Split(buf(1), ",")
SummaryWS.Range(Cells(2, 2), _
Cells(2, UBound(csvline()) + 2)) = csvline()
'2次元配列Datのサイズを再定義。 ReDim Dat(1 To UBound(buf) - 1, 1 To UBound(Split(buf(0), ",")) + 1)
For i = 0 To UBound(buf) - 2
'bufの3行目以降をカンマでちょん切って1次元配列buf2に入れる
buf2 = Split(buf(i + 2), ",")
For j = 0 To UBound(buf2)
Dat(i + 1, j + 1) = buf2(j)
Next j
Next i
With Application.WorksheetFunction
For i = 1 To UBound(Dat, 2)
SummaryWS.Cells(3, 1).Value = "Average"
SummaryWS.Cells(3, i + 1) = .Average(.Index(Dat, 0, i))
Next i
End With
Debug.Print "Done!" End Sub
データを二次元配列に入れるところまでは速かった（ほぼ１秒）のですが、Averageなどのワークシート関数を使って計算するところで時間が取られてしまいました（Averageだけで６秒くらい）。当初の予想に反して、配列でやるより、ワークシートに読み込んでからやった方が速いのかな、という感想です。
ともあれ、いろんなやり方を教えていただけて勉強になりました。今後の参考にさせていただきます。ありがとうございました。
（めろす）
ところで、また追加の質問をさせてください。（こういう時は新しく質問をたてるべきなのでしょうか・・・？）
これまでと同じ作業の一部なのですが、上のMelosTest3()のスクリプトに以下のような処理を加えたいと思っています。
1. MelosTest3()の中の以下の式で出した最小値の出現回数を求める
Range("B" & mxr + 13).Resize(, mxc).Formula = _
"=MIN(B3:B" & mxr & ")" '最小値
2. この最小値を同じ範囲から除いた上で平均値・標準偏差を再計算する
これらをそれぞれ一つの数式で行えるものならば、"="以下の式だけ変えて加えるだけなので簡単だと思うのですが、可能でしょうか？
アドバイスを頂けましたら幸いです。 （めろす）
検証お疲れ様です。 予想どおり。と言う感じですね
計算はやはりシート上で行う方が早いと思いますし INDEX関数を使う時点で時間が掛かるのだと思います。
私の回答は、当初の本題である『実行中に指定した任意の数だけ配列をつくるには』に対して 配列を作りながら読み込むのではなく、全部読み込んでから分離する。という 違った視点からの問題に対するアタックの仕方の１つだと思っていただければと思います。
前述のとおり、シートに収まりきらないくらい大きなデータの場合にも有効ですし （バイナリで読んだ方がよいかもしれませんが） 今後色んな場面で使えると思いますので。
では、報告ありがとうございました。 頑張ってください。 （ｍｏｍｏ）
＞約21秒から約4秒に短縮できました！ 4秒ですか、それは思いがけず早いですね。
＞ただ、一つ問題が。範囲に２種類しか数値がない場合、３番目に大きな値を出すべきところ、 ＞一番大きな値が出てきてしまいます。 に関しては、こんな式でやってみるとどうですか？
'------
Range("B" & mxr + 5).Resize(4, mxc).Formula = _
"=IF(ISERROR(B" & mxr + 4 & "),B" & mxr + 4 & _
",LARGE(B$3:B$" & mxr & ",COUNTIF(B$3:B$" & mxr & ","">=""&B" & mxr + 4 & ")+1))"
'第２〜５最大値
Range("B" & mxr + 9).Resize(4, mxc).Formula = _
"=IF(ISERROR(B" & mxr + 10 & "),B" & mxr + 10 & _
",SMALL(B$3:B$" & mxr & ",COUNTIF(B$3:B$" & mxr & ",""<=""&B" & mxr + 10 & ")+1))"
'第５〜２最小値
'------
追加の御質問に関してですが ＞1. 最小値の出現回数を求める これは、COUNTIF関数で求められます。 同じ様に追加してもらえば良いと思います。
＞2. この最小値を同じ範囲から除いた上で平均値・標準偏差を再計算する これは、一度最小値の出現回数まで求めて値化した後 元のデータから、最小値を除いたデータで もう一度数式を入れて計算するのが簡単だと思います。
変数を３つ追加して下さい。 Dim i As Long, ii As Long, tbl As Variant
コードは、↑の数式の修正と、１の質問も追加して
'====元コード・ここから====
Range("A" & mxr + 1).Resize(13).Value = _
Application.Transpose(Array("空白セル数", "平均", "標準偏差", "最大値", "第２最大値", _
"第３最大値", "第４最大値", "第５最大値", "第５最小値", "第４最小値", _
"第３最小値", "第２最小値", "最小値"))
'====元コード・ここまで====
↓
'====入れ替え・ここから====
Range("A" & mxr + 1).Resize(16).Value = _
Application.Transpose(Array("空白セル数", "平均", "標準偏差", "最大値", "第２最大値", _
"第３最大値", "第４最大値", "第５最大値", "第５最小値", "第４最小値", _
"第３最小値", "第２最小値", "最小値", _
"最小値の個数", "最小値を除いた平均", "最小値を除いた標準偏差"))
'====入れ替え・ここまで====
'====元コード・ここから====
Range("B" & mxr + 5).Resize(4, mxc).Formula = _
"=LARGE(B$3:B$" & mxr & ",COUNTIF(B$3:B$" & mxr & ","">=""&B" & mxr + 4 & ")+1)"
'第２〜５最大値
Range("B" & mxr + 9).Resize(4, mxc).Formula = _
"=SMALL(B$3:B$" & mxr & ",COUNTIF(B$3:B$" & mxr & ",""<=""&B" & mxr + 10 & ")+1)"
'第５〜２最小値
Range("B" & mxr + 13).Resize(, mxc).Formula = _
"=MIN(B3:B" & mxr & ")" '最小値
ActiveSheet.Calculate '再計算
'値化
Range("B" & mxr + 1).Resize(13, mxc).Value = _
Range("B" & mxr + 1).Resize(13, mxc).Value
'====元コード・ここまで====
↓
'====入れ替え・ここから====
Range("B" & mxr + 5).Resize(4, mxc).Formula = _
"=IF(ISERROR(B" & mxr + 4 & "),B" & mxr + 4 & _
",LARGE(B$3:B$" & mxr & ",COUNTIF(B$3:B$" & mxr & ","">=""&B" & mxr + 4 & ")+1))"
'第２〜５最大値
Range("B" & mxr + 9).Resize(4, mxc).Formula = _
"=IF(ISERROR(B" & mxr + 10 & "),B" & mxr + 10 & _
",SMALL(B$3:B$" & mxr & ",COUNTIF(B$3:B$" & mxr & ",""<=""&B" & mxr + 10 & ")+1))"
'第５〜２最小値
Range("B" & mxr + 13).Resize(, mxc).Formula = _
"=MIN(B3:B" & mxr & ")" '最小値
Range("B" & mxr + 14).Resize(, mxc).Formula = _
"=COUNTIF(B3:B" & mxr & ",B" & mxr + 13 & ")" '最小値の個数
ActiveSheet.Calculate '再計算
'値化・・・↓で配列から戻すのでココでは不要になる
'最小値をデータから除く
tbl = Range("A1").Resize(mxr + 14, mxc + 1).Value
For i = 2 To mxc + 1
For ii = 3 To mxr
If tbl(ii, i) = tbl(mxr + 13, i) Then
tbl(ii, i) = Empty
End If
Next
Next
Range("A1").Resize(mxr + 14, mxc + 1).Value = tbl
'最小値を除いた平均値,標準偏差
Range("B" & mxr + 15).Resize(, mxc).Formula = _
"=AVERAGE(B3:B" & mxr & ")" '平均値
Range("B" & mxr + 16).Resize(, mxc).Formula = _
"=STDEV(B3:B" & mxr & ")" '標準偏差
ActiveSheet.Calculate '再計算
'値化
Range("B" & mxr + 15).Resize(2, mxc).Value = _
Range("B" & mxr + 15).Resize(2, mxc).Value
'====入れ替え・ここまで====
で良いと思います。
(HANA)
HANA様
あああ、ありがとうございます！！！ やっぱり一行では無理ですよね。なるほど、配列に入れ直すという方法がありましたか。
本当に助かりました。 これでこれからの仕事がサクサク進みそうです。 お世話になりました！ （めろす）
＞やっぱり一行では無理ですよね。 いや、無理ではないと思いますが。 （標準偏差の方は分かりませんが、少なくとも平均の方は。） https://www.excel.studio-kazu.jp/cgi-bin/estindex/estseek2.cgi?phrase=%E6%9C%80%E5%B0%8F%E5%80%A4%E3%80%80%E3%80%80%E5%B9%B3%E5%9D%87&perpage=10&attr=@uri+STRINC+kazuwiki&order=@uri+STRD&clip=-1&navi=0
ただ、以前の↓と同じ様な状況になるかもしれません。 [[20100310185601]] 『空白のある値の範囲からｘ番目に大きい値を抽出し』（めろす） ユーザー定義関数も含め、このときの数式を使ったものより 今回シートに埋め込んだ数式の方が処理が速かったですよね？
試したわけではないので実際はどうかわかりませんが =AVERAGE(A1:A10) =SUM(A1:A10)/COUNT(A1:A10) の二つはどちらも平均が出ますが、 処理の速度は前者の方が早いのではないかと思います。 注・あくまでも勝手なイメージですが。
どの様なデータから、何が必要なのか。 数式を使って処理をするのか。 マクロを使って処理をするのか。 その場合、どこで何をどの様に使っていくのか。 先にしっかり情報を収集し、イメージをして、計画を立ててから 作業に移るのが良いと思います。
エクセルは結構なんとでもなると思うので、余計に 一番最初に目についた方法が、一番良いかどうかは分からないと思います。
たとえば、最小値を除いた平均 （範囲の合計−最小値の合計）／（範囲の個数−最小値の個数） （範囲の合計−最小値＊最小値の個数）／（範囲の個数−最小値の個数） この二つを考えた場合、今回なら 最小値と最小値の個数は別のセルで出してあるので そのセルを利用する事にする(SUM-セルの値×セルの値）／（COUNT−セルの値）と (SUM-SUMIF)/(COUNT-セルの値)とするよりも 処理は早く済むかもしれません。
また、場合によってはこの計算の為だけにでも最小値の個数をどこかのセルに 求めておいて（SUM-MIN×セルの値）／（COUNT−セルの値）とした方が 早いこともあるかもしれません。
しかし、やってみると、やはり（SUM-SUMIF)/(COUNT-COUNTIF)が一番早いかもしれません。
実際の所、このくらいの事であれば比べることに時間を費やす程の事ではないと思いますが。
まぁ、今回は標準偏差も求めますし どうせマクロも使っているので 最小値を削除したデータにした後、同じ数式で処理するのが 全体的に見て、簡単じゃないかと思いました。
そういえば、最初の数式の計算後、値化する前に入れられた数式を確認してもらうと分かるのですが ２番目に小さい値を取得する式の中に最小値の個数を数える式が含まれています。 IF(・・・,"",SMALL(・・・,COUNTIF(・・・)+1)) の COUNTIF(・・・) の部分。
最小値の個数は別のセルで計算していて、二重に計算することになるので ２番目に小さい値を取得する式は、他の式とは別にして 最小値の個数のセルの値を参照することにすると良いかもしれません。
こちらも、目に見えて処理スピードが変わるかどうかは分かりません。。。 同じ式を入れておくとコードもすっきりしますし ちょっとした変更があった時に意識しなくて良いので このままの方が良いかもしれません。
同じ結果を導くために、いろいろな方法があるので たくさんの方法を仕入れておいて 丁度良い所で、丁度良い方法が選べるようになるのが良いと思います。
(HANA)
HANA様
処理の効率化について、いろいろ考察を聞かせてくださりありがとうございます。 本当にエクセルのワークシート関数があんなに速いとは、ありがたい発見でした。 例として挙げられたケースの違いもイメージができました。 いろんな方法があり得るのですね。なるほど、という感じです。
>どの様なデータから、何が必要なのか。 >数式を使って処理をするのか。 >マクロを使って処理をするのか。 >その場合、どこで何をどの様に使っていくのか。 >先にしっかり情報を収集し、イメージをして、計画を立ててから >作業に移るのが良いと思います。
おっしゃる通りですね。とにかく今回は初めて一からスクリプトを組み立てようとしたもので、後の方の処理までまったくイメージが湧きませんでした。 今回の経験でずいぶんと知恵がつきましたので、次回からもう少しスマートに計画できるのではないかと思います。
またアドバイスよろしくお願いします。 ありがとうございました。 （めろす）
すみません、重要な書き忘れがありました。。。
わざわざ書かなくても、ご理解いただけているとは思いますが ＞＞ ユーザー定義関数も含め、このときの数式を使ったものより ＞＞ 今回シートに埋め込んだ数式の方が処理が速かったですよね？ これは、たとえば、３番目に大きい値を求めようと思った時に ２番目に大きい値がセルに計算済みだったからです。
先の質問時に寄せられた回答は、２番目に大きい値がセルに計算されていなくても ３番目に大きい値が得られる関数に成っていると思います。
単独で「○番目だけが欲しい」なんて時はとても役に立つと思いますし だって、５番目に大きい値を調べたいだけなのに １番目、２番目、３番目、４番目 と求めておいて 初めて ５番目の値が得られるのでは面倒ですからね。 ・・・処理スピードとの兼ね合いもあるとは思いますが・・・。 今回程大きなデータでなければ、問題ないものだったと思います。
こういった事でも、状況に合わせてより良い方法が変わってくると思います。
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.