第38回目 エクセルのファイルを分析する(その4)

○第38回目　エクセルのファイルを分析する(その4)

　前回問題提起した、材料は横見出しと計算式です。これを使って何か仕様書モドキのものが出来ないか、ということですが、一筋縄では行きません。
　複合技で処理をシステム化するというあたりで、考えて見ます。
　例として、簡単な表を考えてみます。
　1月から12月の売上げ数値があり、それを基に上半期の小計、下半期の小計、年度の総合計をとる、というものです。縦項目はチェーン店でもいいでしょう。下にチェーン店の総合計が入ります。
A列から展開すると､BからG列までが1-6月､H列が上半期小計､IからN列までが7-12月､O列が下半期の小計､P列が年間合計となります｡
　最初の小計は、 =SUM(B4:G4)、
　次は、　　　　　　=SUM(I4:N4)、
　最後は　　　　　 =H4+O4　　となります。
　縦の合計は、　 =SUM(B4:B12)　となります。

　このように式を取り出すのは簡単です。(計算式を文字にするマクロが登場します)
　そして、横見出しを次のように取り出すのも簡単です。
　1月 2月 3月 4月 5月 6月 上半期小計 7月 8月 9月 10月 11月 12月 下半期小計 合計(間はタブで繋いでいる)
　これに列記号を付けてみてはどうでしょうか。これはマクロの役割となります。
B;1月 C;2月 D;3月 E;4月 F;5月 G;6月 H;上半期小計 I;7月 J;8月 K;9月 L;10月 M;11月 N;12月 O;下半期小計 P;合計 Q;
　タブを改行に、;をタブに置き換えます。
　そして、計算式の該当する列記号を見出しに置き換えるのです。(QXエディタのフリーソフト。上記の"XXXタブYYY"がこのソフトでの変換語の与え方の形式。第32回で紹介"複数文字列一括置換マクロ")
　すると
　最初の小計は、 =SUM(1月:6月)、
　次は、　　　　　　=SUM(7月:12月)、
　最後は　　　　　 =上半期+下半期　　となります。
　(注)SUMには、列記号と見間違える英文字が入っているために工夫が必要です。

　結果を整理をすると、
1月
2月
3月
4月
5月
6月
上半期小計 =SUM(1月:6月)
7月
8月
9月
10月
11月
12月
下半期小計 =SUM(7月:12月)
合計　　 =上半期+下半期
　となります。
　※縦の合計は別のやり方になるので、混乱を避けるため省略。

　このようなものを一連の操作で作成できたらどうでしょうか。

　1.横見出しを取得(マクロ)
　 1-2.テキストに貼り付け
　 1-3.タブ等を置換え
　 1-4.コピーし同じものを作る
　 1-5.一つは先頭の列記号を削除しておく
　2.計算式を文字にする(マクロ)
　 2-2.テキストに貼り付け(計算式以外の項目も含めてココピーする)
　 2-3.タブを改行に置換え
　 2-4.1-5の左側にブロックコピーする
　3."1"を用い変換表を完成させる。
　 3-2　ほとんど出来ているのですが、行数の削除文の追加、関数の変換・再変換。後半はすべての処理で共通部分なので一度作ると使い回せます。次のようなものです。例では12行目のところの計算式を取っています。
　　　SUM さむ
　　　ROUND らうんど
　　　IF いふ
　　　LOOKUP るっくあっぷ

　　　11 (-1)
　　　12
　　　13 (+1)

　　　【ここに横見出しの変換表を入れる】

　　　さむ SUM
　　　らうんど ROUND
　　　いふ IF
　　　るっくあっぷ LOOKUP
　3-3 2.4をこの変換表で修正する。

※まだまだ問題があります。別シートの列を使う場合とか、別ファイルの列を使う場合とかがあります。また、固定セルも特別に変換する必要もあります。
※これらの処理はマクロが使えるテキストエディタ(記述の内容はQXエディタを前提)を前提にしています。