近似matchを使用したドライブの使用量推移グラフ
2021年04月23日
前回、各行での総和計算でエクセルがフリーズして困った。
今回はその対策についての紹介。
対象のデータは前回と同じ。
・CドライブとDドライブを解析した結果が各シートに出力されている。
・データはD列の更新日時昇順にソートされており、E列のファイルサイズが集計対象。
DIRコマンドの詳細は過去の記事を参照。
ファイル一覧解析マクロ
まず集計用のシートを追加し、作成したいグラフに合わせて
B列にX軸となるデータ列を作成。
C列、D列は、match関数で対象の日時が何行目に該当するか検索。
検索対象のデータを昇順にソートしたうえで照合の種類を1に設定することで、
検索値を超えない最大値の行数が返ってくる。
例:C4=MATCH(B4,dir_c.txt!$D$1:$D$170037,1)
1行目から該当行までのファイルサイズを合計することで、
ドライブの使用量を算出する。
参照先を可変にする場合は、indirect関数を使用する。
例:E4=SUM(INDIRECT("シート名!E1:E" & C4))
一見、match関数を使用すると余計重くなりそうなところではあるが、
現実的な粒度でX軸を設定することで処理を軽くすることができる。
出来上がったグラフはこんな感じ。
Dドライブが垂直に上昇している部分を調べたら、Cドライブをバックアップした日だった。
今回はその対策についての紹介。
対象のデータは前回と同じ。
・CドライブとDドライブを解析した結果が各シートに出力されている。
・データはD列の更新日時昇順にソートされており、E列のファイルサイズが集計対象。
DIRコマンドの詳細は過去の記事を参照。
ファイル一覧解析マクロ
まず集計用のシートを追加し、作成したいグラフに合わせて
B列にX軸となるデータ列を作成。
C列、D列は、match関数で対象の日時が何行目に該当するか検索。
検索対象のデータを昇順にソートしたうえで照合の種類を1に設定することで、
検索値を超えない最大値の行数が返ってくる。
例:C4=MATCH(B4,dir_c.txt!$D$1:$D$170037,1)
1行目から該当行までのファイルサイズを合計することで、
ドライブの使用量を算出する。
参照先を可変にする場合は、indirect関数を使用する。
例:E4=SUM(INDIRECT("シート名!E1:E" & C4))
一見、match関数を使用すると余計重くなりそうなところではあるが、
現実的な粒度でX軸を設定することで処理を軽くすることができる。
出来上がったグラフはこんな感じ。
Dドライブが垂直に上昇している部分を調べたら、Cドライブをバックアップした日だった。
PR
Comment