○第112回目 計算式の分析の活用例
第107回から前回までのマクロの活用例です。
二段階活用をします。
第一段階は、目で見てチェックする場合です。
複雑な計算式のセルにあわせて、計算式の中の座標参照マクロを動かします。
第一に、計算式の取得が必要かどうかを聞きます。前の処理での座標が残っていますので、前のままでいい場合は、1以外を入れます。
コンボボックスが出てきます。矢印キーでおくっていくと、座標が出てきます。そこで目的となる座標に移動です。シート間も移動しますので便利です。
移動先で、何かもっと調べたいときは、終了とします。そこでいろいろ調べものをして終わったら、また、このマクロを動かします。前の情報が残っているので計算式の取得はノーとします。そこで次の項目の座標にいくことができます。元の計算式の座標も入っていますので、そこへの移動も可能です。
ある場所でさらに先を見てみたいときは、このマクロを動かし、計算式を取得します。後は大体同じとなります。
複数の計算式の座標を貯めることもできますが、それは余力があったときでいいと思います。あまり多すぎますと、どの座標がどんな意味を持っていたのか覚えられなくなってしまいますから、混乱の元です。
この操作での問題点は、記録が残らないことです。簡単なものなら、確認後チェック済みとしておけばいいのですが、複雑な場合とか、誰かに説明をする必要がある場合とかは、文書化する必要があります。
そこで第二段階目のものです。
第111回で照会した、｢計算式で参照されている場所の情報を集めた一覧表の作成｣です。途中途中で情報を文書化しておけば、後の処理がしやすくなります。私の場合は、一覧表は新しいエクセルシートとなりますが、エクセルでは情報の加工が面倒なので、いったんテキストエディタにコピーしています。意外とエクセルは新しいシートを作るだけでも容量が大きくなってしまうので、小容量化にも適しています。
印刷してチェックしたり、コメントを追記したりして資料として整理します。また、QXマクロの中で便利だったのは、文字罫線で囲うという処理です。計算式の内容一覧表は、同じ形式なのでつなげてしまうと、どこから始まっているのかわかりづらくなるので、一回ごとに枠で囲みます。ブロック単位に整理されるので見やすくなります。
この部分を例としてやってみます。
┏━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ 印刷してチェックしたり、コメントを追記したりして資料として ┃
┃整理します。また、QXマクロの中で便利だったのは、文字罫線で囲 ┃
┃うという処理です。計算式の内容一覧表は、同じ形式なのでつなげ ┃
┃てしまうと、どこから始まっているのかわかりづらくなるので、一 ┃
┃回ごとに枠で囲みます。ブロック単位に整理されるので見やすくな ┃
┃ります。 ┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━┛
(末尾の罫線がおかしくなっていますが、ここでの表示上どうしようもないようです)
となります。(解除機能もありますので、やり直しが簡単にできます)
この一覧表で問題になるのは、計算式が主であるため、説明文字が少ないことです。このマクロではできるだけ文字情報をとるようにしています。座標のほかに、シート名、列見出し、行見出し、セルの値などです。でも不十分です。
この場所について調べなおしたいということがあります。この場合は、指定位置へのジャンプマクロが有効です。このマクロでは、シート間の移動もできるのですが、わずらわしいときは手操作でシートを移動させて、シート内の移動マクロを動かします。
位置は、－で区切って、列の場合は列記号でも列番号でも可能としています。また、おおよそ移動として、カーソルのある列のある行、カーソルのある行のある列という指定もできます。前者の場合は2-とか、後者の場合は-6とかです、それぞれ、カーソルのある行のＢ列、カーソルのある列の6行目に移動します。(通常コマンドの編集－ジャンプでも大差はないかもしれません)
そんなこんなで、必要な情報を調べて資料を整理していきます。
文字罫線で囲ったものにインデントなどでアクセント(前空白、タブ、引用符など)をつけて、計算式の深さを表すこともできそうですね。
【やってみた率直な感想】
資料として残る。
次の日の再開がスムーズにできる。
操作が面白いので、疲れない。
資料が膨大になる可能性があるので逐一コメントを付すなどして整理をしないと後で見るとわからなくなる。
ある一部を他人に依頼し作業を分担することができる(できそう)。
文書化するので、頭が混乱しない。
だいたいは印刷するので、画面を見っぱなしということがなく、目が疲れない。
2009年3月21日土曜日
2009年3月15日日曜日
第111回目 計算式で参照されている場所の情報を集めた一覧表
○第111回目 計算式で参照されている場所の情報を集めた一覧表
計算式の場所に飛ぶだけではなく、その情報を集めて、計算式の明細表を作れないか、というテーマです。
これは今までのことを考えればとても簡単です。問題は、明細の様式です。
("とても簡単"ではなく、"それほど難しくありません"と訂正します)
1.セル座標及び計算式
2.各項目に数値を入れた計算式
3.計算式内の各項目の、横見出し(その列の3-5行までとか)、座標、値と計算式
4.3を場所の数だけ繰り返す。
ということで、十分そうです。
さらにとなると、計算式を持つ場所の場合、さらにその計算式の項目の情報を加えるというのが考えられます。
まずは一重とします。
感じは、
1.セル座標及び計算式
2.各項目に数値を入れた計算式(lookup関数などの場合は非常に難しいので、対応するかしないかも含めて要検討)
3.計算式内の項目の座標、値と計算式
(その項目が計算式である場合、計算式、その中の各項目の値と計算式
となります。きっちりと対応するためには、二重の配列変数が必要となりますね)
明細表のシートは新しいものを作りましょう。
計算式から場所の項目を取り出すのが2回となりますので、この機能をサブルーチン化しておいたほうがいいでしょう。
まずはもととなる計算式から、シート名、座標をとりだしましょう。まずはこれが最初です。
これは前回作成した機能をコピーしてください。
場所の情報が取得できたら、その場所に飛んでいって、その場所の計算式と値を取得します。場所の情報として、範囲指定のものがありますが、その場合は、とりあえず取得しないことにします(一番先頭の場所のものを取得することも考えられます。また、最初と最後の座標がありますので、それぞれの値を取得しコロンでつなげるというのも考えられます)。
計算式と値の取得は次の通りです。
If InStr(p_xy_A1(i), ":") = 0 Then '範囲指定の場合は、:があります。
p_siki(i) = "***" & Selection.Formula
p_val(i) = Selection.Value
Else
p_siki(i) = "@@@" '範囲指定の場合は、計算式が取得できません。
p_val(i) = ""
End If
計算式内容の一覧表は、新しいシートに作成します。
シート名を指定する必要がありますが、処理のたびごと重複してしまいますので、名前の後に日付と時間をつけて重複を避けます。
b = "計算式内容" & Date & Left$(Time, 6)
とし、シート名に不適な文字を削除します。下のものは /を削除しています。他には:があります。
Do While 1
p1 = InStr(b, "/")
If p1 = 0 Then Exit Do
b = Left$(b, p1 - 1) & Mid$(b, p1 + 1)
Loop
表示内容は、オリジナルの計算式、その中に使われている項目となり、
その内訳は、場所の情報、計算式、値となります。それぞれに見出しをつけましょう。
C4から打ち出すことにします。
For i = 0 To p_xy_no
Cells(4 + i * 5, 3) = "シート名及び座標"
Cells(5 + i * 5, 3) = "計算式"
Cells(6 + i * 5, 3) = "値"
Cells(4 + i * 5, 4) = p_sh_name(i) & "---" & p_xy_A1(i)
Cells(5 + i * 5, 4) = p_siki(i)
Cells(6 + i * 5, 4) = p_val(i)
Next
最後に、用紙のスタイルも指定しておきます。結構大きなものとなりますので、A3の横長としました。90%の縮小としました。
'サイズ、向きの変数値への変換
p_size = xlPaperA3
p_muki = xlLandscape
'サイズ、向きの変更
With ActiveSheet.PageSetup
.Zoom = 90
.PaperSize = p_size
.Orientation = p_muki
End With
※付加すべき(？)機能
1.オリジナルの計算式の項目が、計算式であった場合、さらにその先の計算式を分析する
2.オリジナルの計算式は長い場合がある(分析したい計算式は通常長いものとなります)ので、長さをきって複数行で表示する。
3.オリジナルの計算式に実際の数値を埋め込んだものも表示する。
4.3との関係で、特殊な関数の場合、その結果を取得する。例えば、LOOKUP関数は、計算式で三項目は取得できますが、関数全体の結果を持ってくるということです。対象とする関数をどうするかは、ニーズいかんですが、あまり広げすぎると、すべてそれ専用の処理を付加することになるので、大変になるでしょう。ほかには、SUM関数、SUMPRODUCT関数などが考えられます。
5.その項目のたて及び横見出しを取得する。場所の見当がつかないので、あてずっぽうとなります。例えば、たて見出しは、該当する行のA列からB列、横見出しは該当する列の3行目から5行目とかに、きめ打ちします。
計算式の場所に飛ぶだけではなく、その情報を集めて、計算式の明細表を作れないか、というテーマです。
これは今までのことを考えればとても簡単です。問題は、明細の様式です。
("とても簡単"ではなく、"それほど難しくありません"と訂正します)
1.セル座標及び計算式
2.各項目に数値を入れた計算式
3.計算式内の各項目の、横見出し(その列の3-5行までとか)、座標、値と計算式
4.3を場所の数だけ繰り返す。
ということで、十分そうです。
さらにとなると、計算式を持つ場所の場合、さらにその計算式の項目の情報を加えるというのが考えられます。
まずは一重とします。
感じは、
1.セル座標及び計算式
2.各項目に数値を入れた計算式(lookup関数などの場合は非常に難しいので、対応するかしないかも含めて要検討)
3.計算式内の項目の座標、値と計算式
(その項目が計算式である場合、計算式、その中の各項目の値と計算式
となります。きっちりと対応するためには、二重の配列変数が必要となりますね)
明細表のシートは新しいものを作りましょう。
計算式から場所の項目を取り出すのが2回となりますので、この機能をサブルーチン化しておいたほうがいいでしょう。
まずはもととなる計算式から、シート名、座標をとりだしましょう。まずはこれが最初です。
これは前回作成した機能をコピーしてください。
場所の情報が取得できたら、その場所に飛んでいって、その場所の計算式と値を取得します。場所の情報として、範囲指定のものがありますが、その場合は、とりあえず取得しないことにします(一番先頭の場所のものを取得することも考えられます。また、最初と最後の座標がありますので、それぞれの値を取得しコロンでつなげるというのも考えられます)。
計算式と値の取得は次の通りです。
If InStr(p_xy_A1(i), ":") = 0 Then '範囲指定の場合は、:があります。
p_siki(i) = "***" & Selection.Formula
p_val(i) = Selection.Value
Else
p_siki(i) = "@@@" '範囲指定の場合は、計算式が取得できません。
p_val(i) = ""
End If
計算式内容の一覧表は、新しいシートに作成します。
シート名を指定する必要がありますが、処理のたびごと重複してしまいますので、名前の後に日付と時間をつけて重複を避けます。
b = "計算式内容" & Date & Left$(Time, 6)
とし、シート名に不適な文字を削除します。下のものは /を削除しています。他には:があります。
Do While 1
p1 = InStr(b, "/")
If p1 = 0 Then Exit Do
b = Left$(b, p1 - 1) & Mid$(b, p1 + 1)
Loop
表示内容は、オリジナルの計算式、その中に使われている項目となり、
その内訳は、場所の情報、計算式、値となります。それぞれに見出しをつけましょう。
C4から打ち出すことにします。
For i = 0 To p_xy_no
Cells(4 + i * 5, 3) = "シート名及び座標"
Cells(5 + i * 5, 3) = "計算式"
Cells(6 + i * 5, 3) = "値"
Cells(4 + i * 5, 4) = p_sh_name(i) & "---" & p_xy_A1(i)
Cells(5 + i * 5, 4) = p_siki(i)
Cells(6 + i * 5, 4) = p_val(i)
Next
最後に、用紙のスタイルも指定しておきます。結構大きなものとなりますので、A3の横長としました。90%の縮小としました。
'サイズ、向きの変数値への変換
p_size = xlPaperA3
p_muki = xlLandscape
'サイズ、向きの変更
With ActiveSheet.PageSetup
.Zoom = 90
.PaperSize = p_size
.Orientation = p_muki
End With
※付加すべき(？)機能
1.オリジナルの計算式の項目が、計算式であった場合、さらにその先の計算式を分析する
2.オリジナルの計算式は長い場合がある(分析したい計算式は通常長いものとなります)ので、長さをきって複数行で表示する。
3.オリジナルの計算式に実際の数値を埋め込んだものも表示する。
4.3との関係で、特殊な関数の場合、その結果を取得する。例えば、LOOKUP関数は、計算式で三項目は取得できますが、関数全体の結果を持ってくるということです。対象とする関数をどうするかは、ニーズいかんですが、あまり広げすぎると、すべてそれ専用の処理を付加することになるので、大変になるでしょう。ほかには、SUM関数、SUMPRODUCT関数などが考えられます。
5.その項目のたて及び横見出しを取得する。場所の見当がつかないので、あてずっぽうとなります。例えば、たて見出しは、該当する行のA列からB列、横見出しは該当する列の3行目から5行目とかに、きめ打ちします。
2009年3月8日日曜日
○第110回目 計算式から場所情報を取得できないか(その4)
○第110回目 計算式から場所情報を取得できないか(その4)
計算式の中の項目の場所へ飛ぶために、計算式から場所情報を取得できないか、というテーマの続きのです。関数名、演算子などの削除すべき文字列を、エクセルマクロの中で削除することにします。
今回のテーマは、もととなる計算式から、シート名、座標をとりだすことのです。
削除する文字列群、カンマなどに置き換える文字列群を区分けします。配列変数に文字列を指定して、For Next文で繰り返します。
これによって、カンマなどで区切られた文字列が出来上がりますので、その文字列を取得し、シート名がないものは、当該シート名を付加して配列変数に格納します。
del_mojis(i)の文字列を削除し、カンマに置き換える処理は次の通りとなります。
del_mojis(i)の文字列を削除するだけの場合は、最後の式中、『& ","』の部分をなくします。
For i = 1 To ll100
If del_mojis(i) <> "" Then
Do While 1
p1 = InStr(b, del_mojis(i))
If p1 = 0 Then Exit Do
b = Left$(b, p1 - 1) & "," & Mid$(b, p1 + Len(del_mojis(i)))
Loop
End If
Next
想定外の文字列がある場合は、後の処理でエラーになりますので、またここに戻って、不要な文字列を削除するなりしてください。
【削除または置き換える文字列】
"ROUND"、"LOOKUP"、"IF"、"INT"、"SUMPRODUCT"
四則演算記号、括弧など
このように、求めたものから、変数名を取得します。カンマに挟まれたものが変数の候補です。p_xy_A1(i)に格納しておきます。
次に、取得した情報が定数か否かを判定します。定数は不要ですので。
定数は、If IsNumeric(変数名が入る) <> False で判定します。
その次は、シート名がついているか否かです。これは"!"のありなしで判断します。あれば、分割し、シート名と座標を分離します。ない場合は、座標のみですので、シート名として当該シート名を貼付けます。
p1 = InStr(p_xy_A1(i), "!")
If p1 <> 0 Then
p_sh_name(i) = Left(p_xy_A1(i), p1 - 1)
p_xy_A1(i) = Mid(p_xy_A1(i), p1 + 1)
シート名がない場合は、当該シート名をつけます。
p_sh_name(i) = ActiveSheet.Name
シート名と座標は一緒にして使うことがありますので、結合したものとして、
p_mm0(i) = p_sh_name(i) & " --- " & p_xy_A1(i) としておきます。
また、同じ項目が計算式中に複数記述されている場合がありますので、重複した分は削除して、詰めておきます。
このようにして整理した後の、場所の数を把握しておきます。p_xy_no。
この配列変数は共通なので前の処理で何が入っているのかわかりませんので、場所の数以上の内容はクリアすることを忘れずに。
場所の情報が取得できましたので、このあとは、コンボボックスを呼び出して、その中から座標を選択して、その場所に移動するという今まで処理につながります。
計算式の中の項目の場所へ飛ぶために、計算式から場所情報を取得できないか、というテーマの続きのです。関数名、演算子などの削除すべき文字列を、エクセルマクロの中で削除することにします。
今回のテーマは、もととなる計算式から、シート名、座標をとりだすことのです。
削除する文字列群、カンマなどに置き換える文字列群を区分けします。配列変数に文字列を指定して、For Next文で繰り返します。
これによって、カンマなどで区切られた文字列が出来上がりますので、その文字列を取得し、シート名がないものは、当該シート名を付加して配列変数に格納します。
del_mojis(i)の文字列を削除し、カンマに置き換える処理は次の通りとなります。
del_mojis(i)の文字列を削除するだけの場合は、最後の式中、『& ","』の部分をなくします。
For i = 1 To ll100
If del_mojis(i) <> "" Then
Do While 1
p1 = InStr(b, del_mojis(i))
If p1 = 0 Then Exit Do
b = Left$(b, p1 - 1) & "," & Mid$(b, p1 + Len(del_mojis(i)))
Loop
End If
Next
想定外の文字列がある場合は、後の処理でエラーになりますので、またここに戻って、不要な文字列を削除するなりしてください。
【削除または置き換える文字列】
"ROUND"、"LOOKUP"、"IF"、"INT"、"SUMPRODUCT"
四則演算記号、括弧など
このように、求めたものから、変数名を取得します。カンマに挟まれたものが変数の候補です。p_xy_A1(i)に格納しておきます。
次に、取得した情報が定数か否かを判定します。定数は不要ですので。
定数は、If IsNumeric(変数名が入る) <> False で判定します。
その次は、シート名がついているか否かです。これは"!"のありなしで判断します。あれば、分割し、シート名と座標を分離します。ない場合は、座標のみですので、シート名として当該シート名を貼付けます。
p1 = InStr(p_xy_A1(i), "!")
If p1 <> 0 Then
p_sh_name(i) = Left(p_xy_A1(i), p1 - 1)
p_xy_A1(i) = Mid(p_xy_A1(i), p1 + 1)
シート名がない場合は、当該シート名をつけます。
p_sh_name(i) = ActiveSheet.Name
シート名と座標は一緒にして使うことがありますので、結合したものとして、
p_mm0(i) = p_sh_name(i) & " --- " & p_xy_A1(i) としておきます。
また、同じ項目が計算式中に複数記述されている場合がありますので、重複した分は削除して、詰めておきます。
このようにして整理した後の、場所の数を把握しておきます。p_xy_no。
この配列変数は共通なので前の処理で何が入っているのかわかりませんので、場所の数以上の内容はクリアすることを忘れずに。
場所の情報が取得できましたので、このあとは、コンボボックスを呼び出して、その中から座標を選択して、その場所に移動するという今まで処理につながります。
2009年3月1日日曜日
第109回目 計算式で参照されている場所に飛ばすことができないか(その3)
○第109回目 計算式で参照されている場所に飛ばすことができないか(その3)
計算式から場所の情報をどうやってとるか。
考え方は、特殊記号、特殊文字を区切り記号とみなして、分解していくというものです。
簡単な例では、=a3+b3という計算式があれば、特殊記号=と+で、計算式からa3とb3を取り出すことができます。
次の式などはどうでしょうか。
=ROUND(AA91*LOOKUP($B91,シート01!$Z$7:$Z$112,シート01!$AA$7:$AA$112),2)
=IF(S10<>0,ROUND(V74*(0.5)*12*LOOKUP(INT(0.5),シート01!$AU$7:$AU$110,シート01!$AX$7:$AX$110)/1000,1),0)
規則性があって、何とか抜き出せそうな感じがしますね。
そういえば、昔、似たようなものをやったことがありますね。
エクセルマクロから、変数名を抜き出すというものでした。
あれは、第何回だったでしょうか。
ちょっとこのブログファイルをQXマクロの検索語を含むレコードの抜き出しで見てみましょうか。検索語としては、"変数名"としました。
第43回目 ちょっと寄道の２
第42回目 エクセルのファイルを分析する(その5：ちょっと寄道)
でした。
変換表を使って、じゃまものを削除または半角スペースに変換してみたところ、次のとおりとなりました。
AA91 $B91 シート01!$Z$7:$Z$112 シート01!$AA$7:$AA$112 2
S10 0 V74 0.5 12 0.5 シート01!$AU$7:$AU$110 シート01!$AX$7:$AX$110 1000 1 0
この後、半角スペースをひとつの","にかえるとそれらしくなりますね。
,AA91,$B91,シート01!$Z$7:$Z$112,シート01!$AA$7:$AA$112,2,
,S10,0,V74,0.5,12,0.5,シート01!$AU$7:$AU$110,シート01!$AX$7:$AX$110,1000,1,0,
ここからはQXマクロで処理することにします。
シート名がついたものは、シート名(TAB)座標、シート名がないものは(TAB)座標とします。一場所一行とします。
このやり方の場合は、QXエディタとエクセルの間を何回もいききしますので、操作がかったるくなりますね。
それほど大量なものではないので、あちこちいかないで、エクセルマクロでやるべきでしょうね。そんなには難しいマクロではありませんからね。
流れはこんな感じでしょう。
1.(処理したいセルにカーソルを置いて実行)セルの計算式を取得
2.計算式から、いらない文字列を削除したりして、場所情報をカンマで区切ったものを作る。あわせて、計算式の座標を取得しておく。
3.一つ一つの場所情報をシート名、座標にわけ、配列変数に格納。当該シートの場合は、場所情報にはシート名はないので、当該シート名をつける。
4.コンボボックスに場所情報をセットする。
移動と終了のボタンをつける。
5.場所が選択され移動のボタンが押されたら、当該場所に移動する。これを繰り返す。
6.終了のボタンが押されたら終了とする。
これで構想が出来上がりました。
計算式から場所の情報をどうやってとるか。
考え方は、特殊記号、特殊文字を区切り記号とみなして、分解していくというものです。
簡単な例では、=a3+b3という計算式があれば、特殊記号=と+で、計算式からa3とb3を取り出すことができます。
次の式などはどうでしょうか。
=ROUND(AA91*LOOKUP($B91,シート01!$Z$7:$Z$112,シート01!$AA$7:$AA$112),2)
=IF(S10<>0,ROUND(V74*(0.5)*12*LOOKUP(INT(0.5),シート01!$AU$7:$AU$110,シート01!$AX$7:$AX$110)/1000,1),0)
規則性があって、何とか抜き出せそうな感じがしますね。
そういえば、昔、似たようなものをやったことがありますね。
エクセルマクロから、変数名を抜き出すというものでした。
あれは、第何回だったでしょうか。
ちょっとこのブログファイルをQXマクロの検索語を含むレコードの抜き出しで見てみましょうか。検索語としては、"変数名"としました。
第43回目 ちょっと寄道の２
第42回目 エクセルのファイルを分析する(その5：ちょっと寄道)
でした。
変換表を使って、じゃまものを削除または半角スペースに変換してみたところ、次のとおりとなりました。
AA91 $B91 シート01!$Z$7:$Z$112 シート01!$AA$7:$AA$112 2
S10 0 V74 0.5 12 0.5 シート01!$AU$7:$AU$110 シート01!$AX$7:$AX$110 1000 1 0
この後、半角スペースをひとつの","にかえるとそれらしくなりますね。
,AA91,$B91,シート01!$Z$7:$Z$112,シート01!$AA$7:$AA$112,2,
,S10,0,V74,0.5,12,0.5,シート01!$AU$7:$AU$110,シート01!$AX$7:$AX$110,1000,1,0,
ここからはQXマクロで処理することにします。
シート名がついたものは、シート名(TAB)座標、シート名がないものは(TAB)座標とします。一場所一行とします。
このやり方の場合は、QXエディタとエクセルの間を何回もいききしますので、操作がかったるくなりますね。
それほど大量なものではないので、あちこちいかないで、エクセルマクロでやるべきでしょうね。そんなには難しいマクロではありませんからね。
流れはこんな感じでしょう。
1.(処理したいセルにカーソルを置いて実行)セルの計算式を取得
2.計算式から、いらない文字列を削除したりして、場所情報をカンマで区切ったものを作る。あわせて、計算式の座標を取得しておく。
3.一つ一つの場所情報をシート名、座標にわけ、配列変数に格納。当該シートの場合は、場所情報にはシート名はないので、当該シート名をつける。
4.コンボボックスに場所情報をセットする。
移動と終了のボタンをつける。
5.場所が選択され移動のボタンが押されたら、当該場所に移動する。これを繰り返す。
6.終了のボタンが押されたら終了とする。
これで構想が出来上がりました。
登録:
コメント (Atom)
