趣味プログラミングblog

vlookup関数やmatch関数は、検索文字にHITした行を探してくれて便利。
ただ、検索文字が複数行にあって、２つ目以降に登場すると簡単には取ってこれない。

今回は、特定のキーワードのある行以降の検索文字を拾ってくるものを作ってみた。

前回、アクセス解析のタグのミスに気付いた。今回は、間違ったタグを作り直してソースを修正するもの。超地味。。。

忍者ブログのアクセスログは以下の３つのソースに共通のコードを使っている。
（１）scriptタグのsrc　/ufo/190330800
（２）a hrefタグのリンク先URL　/bin/gg?190330800
（３）イメージソースのURL　/bin/ll?190330800

上記はページ１のタグの一部。
ページ２は、190330801
ページ１１は190330810じゃなくて19033080a
ページ３７も190330810じゃなくて19033080A
ページ６３でようやく190330810

エクセルの関数で場合分けするのめんどいので、適当に対処。

ベースとなる行を対象にsubstituteで置換。
codeとchar関数を駆使して、アルファベットをインクリメントする。

ページ73　L74セル=SUBSTITUTE($L$1,"0800","081" & CHAR(CODE("a")+B74-73))

あぁ６０ページも差し替えかぁ・・・。

前回、各行での総和計算でエクセルがフリーズして困った。
今回はその対策についての紹介。

対象のデータは前回と同じ。
・CドライブとDドライブを解析した結果が各シートに出力されている。
・データはD列の更新日時昇順にソートされており、E列のファイルサイズが集計対象。

DIRコマンドの詳細は過去の記事を参照。
ファイル一覧解析マクロ

---

まず集計用のシートを追加し、作成したいグラフに合わせて
B列にX軸となるデータ列を作成。

C列、D列は、match関数で対象の日時が何行目に該当するか検索。
検索対象のデータを昇順にソートしたうえで照合の種類を1に設定することで、
検索値を超えない最大値の行数が返ってくる。

例：C4=MATCH(B4,dir_c.txt!$D$1:$D$170037,1)



１行目から該当行までのファイルサイズを合計することで、
ドライブの使用量を算出する。

参照先を可変にする場合は、indirect関数を使用する。

例：E4=SUM(INDIRECT("シート名!E1:E" & C4))


一見、match関数を使用すると余計重くなりそうなところではあるが、
現実的な粒度でX軸を設定することで処理を軽くすることができる。


出来上がったグラフはこんな感じ。

Dドライブが垂直に上昇している部分を調べたら、Cドライブをバックアップした日だった。

今回のPRA関係の話。

あるデータを変換して出力する処理を永遠とやっているＰＣがあって、
ＰＣの空き容量が心配になったのでDirコマンドで全ファイル検索。

詳細は過去の記事[ファイル一覧解析マクロ]を参照。
（今使っているものとちょっと違うので、再掲載だな）

---

フォルダ内の全ファイルのサイズと更新日時を取得したので、
更新日時順にならべて、ファイルサイズを１行目から対象行までをSumで計算。
（例　:　E3なら = sum($D2:$D3)　

実はこの数式が厄介で、１行目のセルの値を変更すると全行の値が更新される。

当然、自動再計算はオフに設定して操作するんだけど、
このSumを計算している列を消そうとするとエクセルがフリーズする。
１万行なら帰ってくるけど、５万行超えたあたりから帰ってくる素振りがない。

列の削除もだめ、範囲を選択してDeleteボタンもだめ、空白セルをコピーしてもだめ。

どの操作もエクセルが上から処理するのがいけないと思いつき、
選択範囲の下から数式を削除するマクロを組んでみたらビンゴ！

無事消すことができた。

以下、ソース。

エクセルのテーブルで気づいたので投稿。

表データを扱うとき、見た目のためだけにテーブル機能を使って
１行ごとに色分けをしていた。

テーブル機能を使うと、指定した範囲がデータベースのような感じになって
項目指定もセルのアドレスを指定するのではなく、
テーブル名と列タイトル名で指定することになる。

テーブルを使用している場合に、
「＝」と打ってから参照先のセルをクリックして指定すると
以下のように列タイトル名が出てきて勝手に絶対参照になる。


「=テーブル1[@Folder]」・・・＠があると同じ行のセル、＠がないと全行の意味
Excel 「テーブル」機能のメリット・デメリット概要 | Excel Excellent Technics (excel-excellent-technics.com)

相対参照にするには、クリックで指定せずにアドレス名を直接入力する。