○第130回目 計算式作成のお手伝い(一般的な計算式、究極モドキ)
計算式作成のお手伝い(一般的な計算式)については、『第124回目 計算式作成のお手伝い(その３一般的な計算式)』で取り上げました。
さらに改良を重ねましたので、どうでしょうかというご紹介です。
追加した機能
1.座標を入力する際に、1回で複数の座標を演算子でつないでいれることができる(この入力内容は計算式としての要件を備えている必要がある)。
2.計算式の途中に、座標を入れることが出来る。つまり、途中の座標を修正することが出来る。
1.はわかれば、簡単でした。無理やりに座標のあとに文字を入力し、そしてカーソル動かすと座標がはいる、これを繰り返していけば大丈夫なのです。だが、文字列を作り上げて、Enterキーを押すとエラーになるのです。エラーになってもいいから、入れた内容を取得出来ないか、といろいろやってみたのですが駄目でした。
必要は発明の母です。
inputboxメソッドでは、typeの中で座標のほかに、文字、数値、数式(=0)などがあるのです。
数式にしてみたらどうか。数式としての形式が正しければ大丈夫なのです。何個でも座標が続けられます。それは適当においておいて。
計算式の入力＝⇒文字列の入力＝⇒計算式の入力＝⇒…
これを続けていけばいいのです。
ということで、基本部分は解決です(計算式の入力ではイコールが入りますのでそれは除いてください)。
ここでの問題は、計算式を追加するということは、座標が複数あるということです。次回にどの座標からはじめるのは指定しなければなりません。今回入力した計算式から座標を切り出す、という機能を入れます。
どの座標の切り出すか、これは、決め打ちです。
最後の四則演算子から後ろが、かなりの確率で座標といえます。まずは、この基準で組んでみることにします。
2.の点です。
これは単純でした。この仕組みは適当な回数、座標等の入力、文字列の入力、を繰り返すということです。ですから、文字列の入力の時に、特殊文字を入れておき、次の座標等の入力結果を、その文字列と置き換えればいいのです。すると、これまでの計算式の中の複数の場所に、座標等を入れることができます。
ほとんどアイデアという点で勝負ありというのものです(コーディングは、これまでの技術で十分ということです)。
Ques.ちょっと、聞きたいのですが、詳細な操作内容がわからないので聞くのですが、このマクロは、通常の操作と比べてどこにメリットがあるのですか。
＝⇒答え 実はメリットはそれほどないといってもいい場合があります。
計算式を初めて作る場合
通常の操作の場合は、
=を入力
関数等を入力
カーソルを動かして座標を指定
演算子等を入力
これの繰り返しです。
このマクロの場合は
マクロの起動。少なくとも3タッチほどはあるでしょう。
関数等を入力、Enterキー
カーソルを動かして座標を指定 Enterキー
演算子等を入力 Enterキー
これの繰り返しです。
各処理で少なくとも、ワンタッチほど多くかかります。
ここでは、カーソルを動かす操作は詳しく書いていませんが、その点が少し変わります。通常では、計算式を入れたいセルからすべて動きますが、このマクロの場合は、直前の回の最後に指定したセルからカーソルを動かします。
この部分に多大な操作をしている場合は、このマクロのメリットがあるといえましょう。
例えば、近くにある二つのセル同士での計算などは、メリットがないでしょう。
例えば、遠くにある二つのセル同士での計算などは、メリットがでてきます。セルの数が多いほどメリットが出てきます。
この場合、操作に工夫が必要です。1回目の指定は座標は一つしか指定しないのです。そうすると、2回目の座標等の指定の際には、1回目に指定したセルから始まります。
次に計算式の修正です。
通常の操作の場合は
F2を押す
カーソルで直したい箇所に動かし、修正後の内容を追加する、または修正後の内容と置き換える。
これの繰り返しです。
このマクロの場合は
マクロの起動。少なくとも3タッチほどはあるでしょう。
後ろに追加する場合は、初めて計算式を入れる場合と同じです。
途中の座標等を修正する場合は、その箇所を..で置き換えます。Enterキー
カーソルを動かしたり、文字列を入力したりして、座標を含めた計算式を作ります。Enterキー。
自動的に、..の部分が置き換わります。
これの繰り返しです。
Ques.そうですか。座標等の入力は計算式としての要件を必要とするということですが、座標の範囲を指定したらどうなりますか。つまり、"=b3:b10"というのを作ったら、b3:b10が、エラーにならずに計算式に追加されますか。
＝⇒答え 不明なのでやってみましょう。
計算式としては張り付きました。例えば、=AP3:AP23のようになりましたが、valueエラーになってしまいました。
ということですが、次の文字列の入力の際に修正することで対応できますね。
ここで問題が発生しました。範囲指定の場合、次の回に、指定した範囲の始めのセルに移動しなのです。一つの座標に分割する必要がありますね。
したがって、四則演算子のほかにコロンの位置も調べてその前後関係で、座標を切り出すことにしましょう。また、最後はカッコが付いている可能性も高いので、最後にあるカッコは取り除くことにしましょう。
※コーディング関係は次回です。
2009年12月29日火曜日
2009年9月22日火曜日
第129回目 計算式の分析での追加テクニック
○第129回目 計算式の分析での追加テクニック
いろいろと作業を行っていますと、当然想定外のことが起こって、マクロの修正を余儀なくされます。その中で、紹介しておくべきことがありました。
それは、セル群に名前をつけていて、その名前を使って計算式を記述しているという場合の処理です。
あるシートのある列(T列とします)全体を、qqqという名前で定義しています。
そして計算式は、
=qqq*12 と記述しています。
あれおかしいですね。計算式は一つのセルなのに、qqqはセル群ですね。どこをもってくるのでしょうか？
この場合は、計算式が入るセルの行数と同じ行のものが持ってこられます。
計算式のセルがB20であれば、T20*12という計算結果となります。lookup関数のような結果となります。すると、このような計算式の記述のほうが、簡潔でかつ、わかりやすくなりますね。
※名前をつけたセルを使うという例は、自分としてはあまりないので、多くのケースを網羅しているとはいえませんということを最初に断っておきます。
続けますと、そのように計算式を入れたところ、後で計算式を見てみると、なんと前にブック名がくっついているのです。
=book12!qqq*12
さて、テーマは、book12!qqqという場所にどう飛んでいったらいいのかということです。
答えは単純で、
Range("qqq").select です。
これで、該当するシートのT列が選択されます。
まずは、簡単ですね…、と思いきやこの処理が出来ずにエラーになる場合があるのです。100%正しい理由はわかりません。
・シートが違っている時に起きる。
・名前がつけられたすべてのセル群についてエラーになるのではない。
解明できなかったので、同じシートであれば、飛んでいける点を採用しました。その名前があるシートを見つけ、そこで飛べばいいのではないかということです。
エラーレジーム機能(エラーが生じた場合、次の行に飛びそこから実行)を使って、一番目のシートから試してみるのです、エラーであれば次のシートで試す。エラーでなければそこが正解。
コーディングでのポイントは次のとおりです。
On Error GoTo ErrorTrap
For k = 1 To ii_max9 '名前付きセルのあるシートを探す
Sheets(k).Select
errflag = 10
Range(p_xy_A1(i)).Select
If errflag <> 109 Then Exit For
Next
aaa4 = Selection.Address
gyo22 = Selection.Row
retu22 = Selection.Column
ErrorTrap:
errflag = errflag * 10 + 9
Resume Next
次は行数の把握です。
これは、前にもいったとおり、計算式のあるセルの行にします。
問題は、一つのセルに名前がついていた場合です。この場合は、行を求めるということは不要となりますね。
複数セルに名前がついている場合は、
Cells(計算式のあるセルの行,retu22)を使ってコーディングをします。
以上のことを盛り込むことで、名前がつけれたセル群に対する処理は可能となります。
※名前がついた場所へのジャンプ処理でのマクロ。参考まで。
Application.Goto Reference:="qqq"
※内容整理のためにしばらく休みます(2ヶ月ほど)。
いろいろと作業を行っていますと、当然想定外のことが起こって、マクロの修正を余儀なくされます。その中で、紹介しておくべきことがありました。
それは、セル群に名前をつけていて、その名前を使って計算式を記述しているという場合の処理です。
あるシートのある列(T列とします)全体を、qqqという名前で定義しています。
そして計算式は、
=qqq*12 と記述しています。
あれおかしいですね。計算式は一つのセルなのに、qqqはセル群ですね。どこをもってくるのでしょうか？
この場合は、計算式が入るセルの行数と同じ行のものが持ってこられます。
計算式のセルがB20であれば、T20*12という計算結果となります。lookup関数のような結果となります。すると、このような計算式の記述のほうが、簡潔でかつ、わかりやすくなりますね。
※名前をつけたセルを使うという例は、自分としてはあまりないので、多くのケースを網羅しているとはいえませんということを最初に断っておきます。
続けますと、そのように計算式を入れたところ、後で計算式を見てみると、なんと前にブック名がくっついているのです。
=book12!qqq*12
さて、テーマは、book12!qqqという場所にどう飛んでいったらいいのかということです。
答えは単純で、
Range("qqq").select です。
これで、該当するシートのT列が選択されます。
まずは、簡単ですね…、と思いきやこの処理が出来ずにエラーになる場合があるのです。100%正しい理由はわかりません。
・シートが違っている時に起きる。
・名前がつけられたすべてのセル群についてエラーになるのではない。
解明できなかったので、同じシートであれば、飛んでいける点を採用しました。その名前があるシートを見つけ、そこで飛べばいいのではないかということです。
エラーレジーム機能(エラーが生じた場合、次の行に飛びそこから実行)を使って、一番目のシートから試してみるのです、エラーであれば次のシートで試す。エラーでなければそこが正解。
コーディングでのポイントは次のとおりです。
On Error GoTo ErrorTrap
For k = 1 To ii_max9 '名前付きセルのあるシートを探す
Sheets(k).Select
errflag = 10
Range(p_xy_A1(i)).Select
If errflag <> 109 Then Exit For
Next
aaa4 = Selection.Address
gyo22 = Selection.Row
retu22 = Selection.Column
ErrorTrap:
errflag = errflag * 10 + 9
Resume Next
次は行数の把握です。
これは、前にもいったとおり、計算式のあるセルの行にします。
問題は、一つのセルに名前がついていた場合です。この場合は、行を求めるということは不要となりますね。
複数セルに名前がついている場合は、
Cells(計算式のあるセルの行,retu22)を使ってコーディングをします。
以上のことを盛り込むことで、名前がつけれたセル群に対する処理は可能となります。
※名前がついた場所へのジャンプ処理でのマクロ。参考まで。
Application.Goto Reference:="qqq"
※内容整理のためにしばらく休みます(2ヶ月ほど)。
2009年9月6日日曜日
第128回目 計算式の分析;if文(その2)
○第128回目 計算式の分析;if文(その2)
どこのカンマを本当のものと見極めるか。
夢を見ました。大きな長細い皿の上に大小の皿が乗っかっています。皿の上に、また皿が乗っかっているものがあります。皿の中には豆粒の大きさのものがあります。二つほど入っています。よく見ると、一番下の皿に直接乗っかっている豆粒状のものは2つあります。他のものは上の皿に乗っていました。
これです。皿はカッコと思えば、一番下の皿に直接乗っかっているものが求めるものになるのではないか、と思われます。
これをマクロで書けばいいのです。
ユーリイカ!
というわけで、他の皿(カッコ)に乗っかっていないものを探します。
カッコの始まりの位置と終わりの位置がわかりますので、その中に含まれているカンマかどうかがわかります。
これで解決しました。
あとはできるはずです。
=IF(lookup(xxx,xxx:xxx,xxx:xxx)>0.1,If(xxx>0.1,round(B23*1.234,2),lookup(xxx,xxx:xxx,xxx:xxx)),sumproduct(xxx:xxx,xxx:xxx))
IF(の終わりの位置…4
IF(文の後の)の位置…123
IF(文の中の()1の位置…11と31
IF(文の中の()2の位置…39と94
IF(文の中の()3の位置…53と65
IF(文の中の()4の位置…73と93
IF(文の中の()5の位置…106と122
カンマの位置…15,23,36,47,63,77,85,95,114
これらの情報から、該当するカンマは36と95となります。
この位置で切り分ければいいことになります。
ということでこの問題は解決しました。
なに、カッコのランク付けのやりかたがわからない？
○第126回目 計算式の分析;if関数(その前に)の後半をみてください。
考え方は次のとおりです。
1.最初の(を見つける。
2.次の(を見つける。
3.1以降の最初の)を見つける。
4.1と2の間に3があるか。ある場合は、1と3が対応します。
4-2.対応したら、3の位置の後から、1以下を繰り返えす。
5.ない場合は、2のカッコを1とおき、2－4の操作をカッコがなくなるまで繰り返します。
これによりランク1のカッコが見つかりますので、該当したカッコを別の記号に置き換えて、1に戻って1以下の処理をカッコがなくなるまで繰り返します。それぞれ5が終わった段階で、ひとつのレベルのもののカッコが決まっていきます。
カッコの位置を探していくときに、前の部分を切り落として探していきますが、最終的には全体でみなければいけないので、全体の位置をも把握しながらということが、注意点でしょうか。
どこのカンマを本当のものと見極めるか。
夢を見ました。大きな長細い皿の上に大小の皿が乗っかっています。皿の上に、また皿が乗っかっているものがあります。皿の中には豆粒の大きさのものがあります。二つほど入っています。よく見ると、一番下の皿に直接乗っかっている豆粒状のものは2つあります。他のものは上の皿に乗っていました。
これです。皿はカッコと思えば、一番下の皿に直接乗っかっているものが求めるものになるのではないか、と思われます。
これをマクロで書けばいいのです。
ユーリイカ!
というわけで、他の皿(カッコ)に乗っかっていないものを探します。
カッコの始まりの位置と終わりの位置がわかりますので、その中に含まれているカンマかどうかがわかります。
これで解決しました。
あとはできるはずです。
=IF(lookup(xxx,xxx:xxx,xxx:xxx)>0.1,If(xxx>0.1,round(B23*1.234,2),lookup(xxx,xxx:xxx,xxx:xxx)),sumproduct(xxx:xxx,xxx:xxx))
IF(の終わりの位置…4
IF(文の後の)の位置…123
IF(文の中の()1の位置…11と31
IF(文の中の()2の位置…39と94
IF(文の中の()3の位置…53と65
IF(文の中の()4の位置…73と93
IF(文の中の()5の位置…106と122
カンマの位置…15,23,36,47,63,77,85,95,114
これらの情報から、該当するカンマは36と95となります。
この位置で切り分ければいいことになります。
ということでこの問題は解決しました。
なに、カッコのランク付けのやりかたがわからない？
○第126回目 計算式の分析;if関数(その前に)の後半をみてください。
考え方は次のとおりです。
1.最初の(を見つける。
2.次の(を見つける。
3.1以降の最初の)を見つける。
4.1と2の間に3があるか。ある場合は、1と3が対応します。
4-2.対応したら、3の位置の後から、1以下を繰り返えす。
5.ない場合は、2のカッコを1とおき、2－4の操作をカッコがなくなるまで繰り返します。
これによりランク1のカッコが見つかりますので、該当したカッコを別の記号に置き換えて、1に戻って1以下の処理をカッコがなくなるまで繰り返します。それぞれ5が終わった段階で、ひとつのレベルのもののカッコが決まっていきます。
カッコの位置を探していくときに、前の部分を切り落として探していきますが、最終的には全体でみなければいけないので、全体の位置をも把握しながらということが、注意点でしょうか。
2009年8月30日日曜日
第127回目 計算式の分析;if文(その1)
○第127回目 計算式の分析;if文(その1)
if文とは、( )で記述されたものであり、カッコの前がifのもの、とします(この定義だと、sumif文も含まれそうですが、含みません)。
if文には、三項目があります。条件部分、条件が成立した時の処理、不成立の時の処理、となります。
また、複数のif文があった場合、入れ子になっている場合と、独立している場合があります。
この点については、個々のif文に注目して、その中の構造は見ませんので、上記の区分は意識しなくてよいことにします。
どんな構造であっても、例えばif文が4つあれば、四つのそれぞれの構造を個別に分析するということになります。
・IF(B23>0.1,INT(B23*100),IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000))))
この計算式は、発生させた乱数から二桁の数を作り出すものです。B23に乱数が入っています。乱数は、0から0.99999…なので、有効数字がどこから始まっているかによって、何をかけたらいいのかが、変わってきます。
この例でいうと、
・IF(B23>0.1,INT(B23*100),IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000))))
・IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000)))
・IF(B23>0.001,INT(B23*10000),INT(B23*100000))
となります。
それぞれのif文において、中を三つに分けているのですが、それにはカンマで判断します。
上記のいずれの場合でも、1番目のカンマと2番目のカンマの位置で分ければいいことになります。非常に簡単でした。
例として、一番上の条件式からは、次のような情報をとります。
B23>0.1 が成り立っているかどうか(trueかfalseか)
INT(B23*100) の値
IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000)))) の値
全体を示すと以下のようになります。
IF(B23>0.1,INT(B23*100),IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000)))) 86
**B23>0.1 0.865214 True
**INT(B23*100) 86
**IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000))) 865
IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000))) 865
**B23>0.01 0.865214 True
**INT(B23*1000) 865
**IF(B23>0.001,INT(B23*10000),INT(B23*100000)) 8652
IF(B23>0.001,INT(B23*10000),INT(B23*100000)) 8652
**B23>0.001 0.865214 True
**INT(B23*10000) 8652
**INT(B23*100000) 86521
簡単というわけにはいきませんでした。次の場合はどうしますか(計算式の記述は省略しています)。
カンマが関数の中にもあるので、カンマで分割するということが、上手くいかないのです。
・IF(lookup(xxx,xxx:xxx,xxx:xxx)>0.1,If(xxx>0.1,round(B23*1.234,2),lookup(xxx,xxx:xxx,xxx:xxx)),sumproduct(xxx:xxx,xxx:xxx))
これには、お手上げです。
しばらく時間を…ということにします。
次回までに考えてみてください。
if文とは、( )で記述されたものであり、カッコの前がifのもの、とします(この定義だと、sumif文も含まれそうですが、含みません)。
if文には、三項目があります。条件部分、条件が成立した時の処理、不成立の時の処理、となります。
また、複数のif文があった場合、入れ子になっている場合と、独立している場合があります。
この点については、個々のif文に注目して、その中の構造は見ませんので、上記の区分は意識しなくてよいことにします。
どんな構造であっても、例えばif文が4つあれば、四つのそれぞれの構造を個別に分析するということになります。
・IF(B23>0.1,INT(B23*100),IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000))))
この計算式は、発生させた乱数から二桁の数を作り出すものです。B23に乱数が入っています。乱数は、0から0.99999…なので、有効数字がどこから始まっているかによって、何をかけたらいいのかが、変わってきます。
この例でいうと、
・IF(B23>0.1,INT(B23*100),IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000))))
・IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000)))
・IF(B23>0.001,INT(B23*10000),INT(B23*100000))
となります。
それぞれのif文において、中を三つに分けているのですが、それにはカンマで判断します。
上記のいずれの場合でも、1番目のカンマと2番目のカンマの位置で分ければいいことになります。非常に簡単でした。
例として、一番上の条件式からは、次のような情報をとります。
B23>0.1 が成り立っているかどうか(trueかfalseか)
INT(B23*100) の値
IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000)))) の値
全体を示すと以下のようになります。
IF(B23>0.1,INT(B23*100),IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000)))) 86
**B23>0.1 0.865214 True
**INT(B23*100) 86
**IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000))) 865
IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000))) 865
**B23>0.01 0.865214 True
**INT(B23*1000) 865
**IF(B23>0.001,INT(B23*10000),INT(B23*100000)) 8652
IF(B23>0.001,INT(B23*10000),INT(B23*100000)) 8652
**B23>0.001 0.865214 True
**INT(B23*10000) 8652
**INT(B23*100000) 86521
簡単というわけにはいきませんでした。次の場合はどうしますか(計算式の記述は省略しています)。
カンマが関数の中にもあるので、カンマで分割するということが、上手くいかないのです。
・IF(lookup(xxx,xxx:xxx,xxx:xxx)>0.1,If(xxx>0.1,round(B23*1.234,2),lookup(xxx,xxx:xxx,xxx:xxx)),sumproduct(xxx:xxx,xxx:xxx))
これには、お手上げです。
しばらく時間を…ということにします。
次回までに考えてみてください。
2009年8月22日土曜日
第126回目 計算式の分析;if関数(その前に)
○第126回目 計算式の分析;if関数(その前に)
計算式の分析とは、何ぞ。
例えば、複雑なif関数の場合、各項目の値を取得して、計算式のチェックに役立てようとするものです。
□『第111回目 計算式で参照されている場所の情報を集めた一覧表』でやったような気がしますが、それとはどう違うのですか。
＝⇒いい質問ですね。
違うところは確かにあるのですが、それほど取り上げるほどのものがあるかと、言われると…。
まあ、いろいろなことにチャレンジしてやってみたということで、理解願います。
=INT(IF(B23>0.1,INT(B23*100),IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000))))/10)*10+(INT((B23-INT(B23*10)/10)*90)+1)
という計算式がありました。
『計算式で参照されている場所の情報を集めた一覧表』では、計算式の値、数値入り計算式、使われている場所の値とその計算式を示すものです。
具体的には、
・値 12
・数値入り計算式 ***=INT(IF(0.119291635991611>0.1,INT(0.119291635991611*100),IF(0.119291635991611>0.01,
INT(0.119291635991611*1000),IF(0.119291635991611>0.001,INT(0.119291635991611*10000),
INT(0.119291635991611*100000))))/10)*10+(INT((0.119291635991611-INT(0.119291635991611*10)/10)*90)+1)
・使われているセル
乱数 (3)---B23
計算式 ***=RAND()
値 0.119291636
この例の場合は、元となっている値が0.119…なので、一番最初のif文が成立して、計算結果としては、12となったということがわかります。
この場合は、if文の値、そして各項目の値がわかりません。今回のテーマは、これを求めようとするものです。
・IF(B23>0.1,INT(B23*100),IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000))))
・B23>0.1
・INT(B23*100)
・IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000)))
さらに、この場合は,if文がさらにありますので、次のif文、次のif文と分解しいって、上記の値を求めるということとなります。
□最後の項目はif文ではないのですが、これはどうなりますか？
＝⇒if文ではないので、無視しました。難しくはないのですが、複雑になるので、分析対象はif文だけにしています。課題として認識しておきます。
イメージが決まりましたので、具体的に進めていきましょう。
まずは、カッコの分析からです。
カッコの分析とは、何番目のカッコ開くが、何番目のカッコ閉じるに対応しているかというものです(これに"カッコの深さ"を入れていくのはそれほど難しくありません)。 発想としては、始めすべてのカッコ開く、カッコ閉じるの場所を取得し、それらの関係から、カッコの対応をつけていくことが考えられます。
対応するカッコの間には、カッコ開く、カッコ閉じるがまったくないか、同数あるかとなります。
と思っていましたが、さらに違う発想もあるということがわかりました。
その違う発想とは、
1.最後のカッコ開くとその次のカッコ閉じるは対応している
2.このカッコ開くを含めて、それ以降を削除したもので、同じようにやっていくと、対応するカッコが見つかる時があるが、それも対応するカッコとなる。ない場合は3へ。
3.対応するカッコがわかったら、もとの計算式の中で、そのカッコを違う記号に置き換えておく。これが一番深いレベルのカッコです。
4.1.に戻り、カッコを置き換えた計算式を元に同様の処理をする。これが次のレベルのカッコです。
5.カッコがなくなるまで続けます。
【一例】
IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000)))
カッコを@で置き換えていきます。
1. IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT@B23*100000)))
2. IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT@B23*100000@))
3. IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT
1. IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT@B23*10000),INT
2. IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT@B23*10000@,INT
3. IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT
対応するカッコがなくなったので、カッコを整理する。
4. IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT@B23*10000@,INT@B23*100000@))
カッコを\で置き換えていきます。
1. IF(B23>0.01,INT(B23*1000),IF\B23>0.001,INT@B23*10000@,INT@B23*100000@))
2. IF(B23>0.01,INT(B23*1000),IF\B23>0.001,INT@B23*10000@,INT@B23*100000@\)
3. IF(B23>0.01,INT(B23*1000),IF
1. IF(B23>0.01,INT\B23*1000),IF
2. IF(B23>0.01,INT\B23*1000\,IF
3. IF(B23>0.01,INT
対応するカッコがなくなったので、カッコを整理する。
4. IF(B23>0.01,INT\B23*1000\,IF\B23>0.001,INT@B23*10000@,INT@B23*100000@\)
カッコを#で置き換えていきます。
1. IF#B23>0.01,INT@B23*1000@,IF\B23>0.001,INT@B23*10000@,INT@B23*100000@\)
2. IF#B23>0.01,INT@B23*1000@,IF\B23>0.001,INT@B23*10000@,INT@B23*100000@\#
終了
(わかりやすくするために、各回での置換え記号を変えています)。
カッコの深さは3となっています。
まずは、これをマクロに組みます。対応するカッコは、二つの配列変数に始めの位置、終わりの位置として、取得しておきます。
(深さも同時に取得しておくと、カッコを順番に、小カッコ、中カッコ、大カッコ…と変えることができます(小カッコは変える必要はありませんが…))
'計算式を取得する
keisansiki = "=(A20+B20)*(C20+(D20-E20)/(F20+(G20-(H20+J20))))"
keisansiki = Mid$(keisansiki, 2) 'イコールをとる
keisansiki0 = keisansiki
keisansiki2 = keisansiki
a = keisansiki
計算式としては、オリジナルのもの(keisansiki0)、カッコを仮に変えたもの(keisansiki)、カッコを正式なものに変えたもの(keisansiki2)、判断に使うもの(a)と4つ作りました。
'順番にしたカッコの取得
For i = 1 To 10
kakko1(i) = Mid$("({［〔「『〈《【【", i, 1)
kakko9(i) = Mid$(")}］〕」』〉》】】", i, 1)
Next
'計算式の中から(の位置を取得する。後ろから取得していく
m = 0
rebel = 1
Do While 1
p1 = InStr(keisansiki, "(")
If p1 = 0 Then Exit Do
Do While 1
p1 = InStrRev(a, "(")
If p1 = 0 Then Exit Do
p2 = InStr(Mid$(a, p1 + 1), ")") 'これ以降にカッコ閉じるがあるか
If p2 <> 0 Then
m = m + 1 'カッコの番号
pk1(m) = p1 'カッコ始めの位置
pk9(m) = p1 + p2 'カッコ閉じるの位置
kakko_rebel(m) = rebel 'カッコの深さ
keisansiki = Left$(keisansiki, p1 - 1) & "<" & _
Mid$(keisansiki, p1 + 1, p2 - 1) & ">" & _
Mid$(keisansiki, p1 + p2 + 1) '仮のカッコに置換
keisansiki2 = Left$(keisansiki2, p1 - 1) & kakko1(rebel) & _
Mid$(keisansiki2, p1 + 1, p2 - 1) & kakko9(rebel) & _
Mid$(keisansiki2, p1 + p2 + 1) '正式のカッコに置換
End If
a = Left$(a, p1 - 1)
Loop 'この深さのカッコの取得終了
a = keisansiki '既に処理されたカッコは、小カッコから仮のものに置換えられている
rebel = rebel + 1 '次の深さのカッコを探しにいく
Loop
m_cnt = m 'カッコの組数
rebel_cnt = rebel - 1 'カッコの深さ
【入力値と処理結果】
(A20+B20)*(C20+(D20-E20)/(F20+(G20-(H20+J20))))
↓
(A20+B20)*〔C20+(D20-E20)/［F20+{G20-(H20+J20)}］〕
※ここまでの機能だけでも、カッコが多い計算式を見るときにとても便利です。
計算式の分析とは、何ぞ。
例えば、複雑なif関数の場合、各項目の値を取得して、計算式のチェックに役立てようとするものです。
□『第111回目 計算式で参照されている場所の情報を集めた一覧表』でやったような気がしますが、それとはどう違うのですか。
＝⇒いい質問ですね。
違うところは確かにあるのですが、それほど取り上げるほどのものがあるかと、言われると…。
まあ、いろいろなことにチャレンジしてやってみたということで、理解願います。
=INT(IF(B23>0.1,INT(B23*100),IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000))))/10)*10+(INT((B23-INT(B23*10)/10)*90)+1)
という計算式がありました。
『計算式で参照されている場所の情報を集めた一覧表』では、計算式の値、数値入り計算式、使われている場所の値とその計算式を示すものです。
具体的には、
・値 12
・数値入り計算式 ***=INT(IF(0.119291635991611>0.1,INT(0.119291635991611*100),IF(0.119291635991611>0.01,
INT(0.119291635991611*1000),IF(0.119291635991611>0.001,INT(0.119291635991611*10000),
INT(0.119291635991611*100000))))/10)*10+(INT((0.119291635991611-INT(0.119291635991611*10)/10)*90)+1)
・使われているセル
乱数 (3)---B23
計算式 ***=RAND()
値 0.119291636
この例の場合は、元となっている値が0.119…なので、一番最初のif文が成立して、計算結果としては、12となったということがわかります。
この場合は、if文の値、そして各項目の値がわかりません。今回のテーマは、これを求めようとするものです。
・IF(B23>0.1,INT(B23*100),IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000))))
・B23>0.1
・INT(B23*100)
・IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000)))
さらに、この場合は,if文がさらにありますので、次のif文、次のif文と分解しいって、上記の値を求めるということとなります。
□最後の項目はif文ではないのですが、これはどうなりますか？
＝⇒if文ではないので、無視しました。難しくはないのですが、複雑になるので、分析対象はif文だけにしています。課題として認識しておきます。
イメージが決まりましたので、具体的に進めていきましょう。
まずは、カッコの分析からです。
カッコの分析とは、何番目のカッコ開くが、何番目のカッコ閉じるに対応しているかというものです(これに"カッコの深さ"を入れていくのはそれほど難しくありません)。 発想としては、始めすべてのカッコ開く、カッコ閉じるの場所を取得し、それらの関係から、カッコの対応をつけていくことが考えられます。
対応するカッコの間には、カッコ開く、カッコ閉じるがまったくないか、同数あるかとなります。
と思っていましたが、さらに違う発想もあるということがわかりました。
その違う発想とは、
1.最後のカッコ開くとその次のカッコ閉じるは対応している
2.このカッコ開くを含めて、それ以降を削除したもので、同じようにやっていくと、対応するカッコが見つかる時があるが、それも対応するカッコとなる。ない場合は3へ。
3.対応するカッコがわかったら、もとの計算式の中で、そのカッコを違う記号に置き換えておく。これが一番深いレベルのカッコです。
4.1.に戻り、カッコを置き換えた計算式を元に同様の処理をする。これが次のレベルのカッコです。
5.カッコがなくなるまで続けます。
【一例】
IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT(B23*100000)))
カッコを@で置き換えていきます。
1. IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT@B23*100000)))
2. IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT@B23*100000@))
3. IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT(B23*10000),INT
1. IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT@B23*10000),INT
2. IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT@B23*10000@,INT
3. IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT
対応するカッコがなくなったので、カッコを整理する。
4. IF(B23>0.01,INT(B23*1000),IF(B23>0.001,INT@B23*10000@,INT@B23*100000@))
カッコを\で置き換えていきます。
1. IF(B23>0.01,INT(B23*1000),IF\B23>0.001,INT@B23*10000@,INT@B23*100000@))
2. IF(B23>0.01,INT(B23*1000),IF\B23>0.001,INT@B23*10000@,INT@B23*100000@\)
3. IF(B23>0.01,INT(B23*1000),IF
1. IF(B23>0.01,INT\B23*1000),IF
2. IF(B23>0.01,INT\B23*1000\,IF
3. IF(B23>0.01,INT
対応するカッコがなくなったので、カッコを整理する。
4. IF(B23>0.01,INT\B23*1000\,IF\B23>0.001,INT@B23*10000@,INT@B23*100000@\)
カッコを#で置き換えていきます。
1. IF#B23>0.01,INT@B23*1000@,IF\B23>0.001,INT@B23*10000@,INT@B23*100000@\)
2. IF#B23>0.01,INT@B23*1000@,IF\B23>0.001,INT@B23*10000@,INT@B23*100000@\#
終了
(わかりやすくするために、各回での置換え記号を変えています)。
カッコの深さは3となっています。
まずは、これをマクロに組みます。対応するカッコは、二つの配列変数に始めの位置、終わりの位置として、取得しておきます。
(深さも同時に取得しておくと、カッコを順番に、小カッコ、中カッコ、大カッコ…と変えることができます(小カッコは変える必要はありませんが…))
'計算式を取得する
keisansiki = "=(A20+B20)*(C20+(D20-E20)/(F20+(G20-(H20+J20))))"
keisansiki = Mid$(keisansiki, 2) 'イコールをとる
keisansiki0 = keisansiki
keisansiki2 = keisansiki
a = keisansiki
計算式としては、オリジナルのもの(keisansiki0)、カッコを仮に変えたもの(keisansiki)、カッコを正式なものに変えたもの(keisansiki2)、判断に使うもの(a)と4つ作りました。
'順番にしたカッコの取得
For i = 1 To 10
kakko1(i) = Mid$("({［〔「『〈《【【", i, 1)
kakko9(i) = Mid$(")}］〕」』〉》】】", i, 1)
Next
'計算式の中から(の位置を取得する。後ろから取得していく
m = 0
rebel = 1
Do While 1
p1 = InStr(keisansiki, "(")
If p1 = 0 Then Exit Do
Do While 1
p1 = InStrRev(a, "(")
If p1 = 0 Then Exit Do
p2 = InStr(Mid$(a, p1 + 1), ")") 'これ以降にカッコ閉じるがあるか
If p2 <> 0 Then
m = m + 1 'カッコの番号
pk1(m) = p1 'カッコ始めの位置
pk9(m) = p1 + p2 'カッコ閉じるの位置
kakko_rebel(m) = rebel 'カッコの深さ
keisansiki = Left$(keisansiki, p1 - 1) & "<" & _
Mid$(keisansiki, p1 + 1, p2 - 1) & ">" & _
Mid$(keisansiki, p1 + p2 + 1) '仮のカッコに置換
keisansiki2 = Left$(keisansiki2, p1 - 1) & kakko1(rebel) & _
Mid$(keisansiki2, p1 + 1, p2 - 1) & kakko9(rebel) & _
Mid$(keisansiki2, p1 + p2 + 1) '正式のカッコに置換
End If
a = Left$(a, p1 - 1)
Loop 'この深さのカッコの取得終了
a = keisansiki '既に処理されたカッコは、小カッコから仮のものに置換えられている
rebel = rebel + 1 '次の深さのカッコを探しにいく
Loop
m_cnt = m 'カッコの組数
rebel_cnt = rebel - 1 'カッコの深さ
【入力値と処理結果】
(A20+B20)*(C20+(D20-E20)/(F20+(G20-(H20+J20))))
↓
(A20+B20)*〔C20+(D20-E20)/［F20+{G20-(H20+J20)}］〕
※ここまでの機能だけでも、カッコが多い計算式を見るときにとても便利です。
2009年8月15日土曜日
第125回目 計算式作成のお手伝い(その４、余興的にlookup関数)
第125回目 計算式作成のお手伝い(その４、余興的にlookup関数)
lookup関数をつくるというのは、ほとんどこれまでのものを活用すれば可能です。何ら新しいものはありません。
ちょっと時間があったので余興的につくってみました。
lookup関数の記述の仕方。
$AW$5xxx=LOOKUP(AX5,$BG$5:$BG$43,$BH$5:$BH$43)
座標を参照する範囲は、同一ファイル内としました。
必要な座標は、計算式を入れるセル、検査値(例えば、商品コード)のセル、一覧表におけるコード値の範囲、一覧表における参照値(例えば、商品コードに対応する金額)の範囲の四項目です。このうち範囲を持つのが、三番目と四番目ですが、数が同じなので四番目は先頭のセルのみでいいことになります。
指定の順番はこのとおりとしますが、最初の計算式を入れるセルにカーソルを置いた段階でマクロを実行しますので、マクロの中では三つ指定することとなります。
座標について考えると、第一項目と第二項目、第三項目と第四項目は、同じ行である可能性が高いですね。同じ対応でみると、同一シートである可能性も高いですね。
処理の流れ
1.セルの座標を取得します。このセルは計算式を入れる場所ですので、シート名も取っておきます。
2.次の項目のシートを指定し、シートを移動します。
3.次の項目のセルを指定し、その座標を取得します。
2と3を三回繰り返します。二回目の場合は範囲の指定となります。他は一つのセルの指定だけです。この際、Application.InputBoxにおいて、セルの初期値をうまく設定する必要があります。
4.必要回数の指定が終わったら、計算式をつくります。
第二項目は相対参照形式にし、第三項目と第四項目は絶対参照形式にします。
5.計算式から、計算式のあるセルと同じシート名を取り除きます。
6.計算式を貼付けるシートに移動し、セルを指定し、計算式を貼付けます。エラーの場合があるため、最初は文字計算式で貼付けてみてください。
lookup関数をつくるというのは、ほとんどこれまでのものを活用すれば可能です。何ら新しいものはありません。
ちょっと時間があったので余興的につくってみました。
lookup関数の記述の仕方。
$AW$5xxx=LOOKUP(AX5,$BG$5:$BG$43,$BH$5:$BH$43)
座標を参照する範囲は、同一ファイル内としました。
必要な座標は、計算式を入れるセル、検査値(例えば、商品コード)のセル、一覧表におけるコード値の範囲、一覧表における参照値(例えば、商品コードに対応する金額)の範囲の四項目です。このうち範囲を持つのが、三番目と四番目ですが、数が同じなので四番目は先頭のセルのみでいいことになります。
指定の順番はこのとおりとしますが、最初の計算式を入れるセルにカーソルを置いた段階でマクロを実行しますので、マクロの中では三つ指定することとなります。
座標について考えると、第一項目と第二項目、第三項目と第四項目は、同じ行である可能性が高いですね。同じ対応でみると、同一シートである可能性も高いですね。
処理の流れ
1.セルの座標を取得します。このセルは計算式を入れる場所ですので、シート名も取っておきます。
2.次の項目のシートを指定し、シートを移動します。
3.次の項目のセルを指定し、その座標を取得します。
2と3を三回繰り返します。二回目の場合は範囲の指定となります。他は一つのセルの指定だけです。この際、Application.InputBoxにおいて、セルの初期値をうまく設定する必要があります。
4.必要回数の指定が終わったら、計算式をつくります。
第二項目は相対参照形式にし、第三項目と第四項目は絶対参照形式にします。
5.計算式から、計算式のあるセルと同じシート名を取り除きます。
6.計算式を貼付けるシートに移動し、セルを指定し、計算式を貼付けます。エラーの場合があるため、最初は文字計算式で貼付けてみてください。
2009年8月9日日曜日
第124回目 計算式作成のお手伝い(その３一般的な計算式)
○第124回目 計算式作成のお手伝い(その３一般的な計算式)
これまでは、SUMPRODUCT関数という特殊なものを扱ってきましたが、一般的に利用できるマクロはできないでしょうか。
四則演算を使った普通の計算式をつくるために便利なものはないでしょうか、という問題意識です。
通常の方法で計算式をつくる時(カーソルを動かして座標を取得し、間に四則演算子等を加えていく方式)に、気になるところは、セルを指定し終わると、カーソルが計算式のある場所に戻ってしまうことでしょうか(この問題はマウスでやれば出てきません。キーボードならではの問題です)。
例えば、AX7に、=B7/B8という式を入れる場合を考えます。
1.AX7で=を入れる
2.カーソルをB7まで移動します
3./をいれます。ここでカーソルはAX7に戻ります。
4.カーソルをB8まで移動します。
5.Enterを押します
問題は、3.のあとのカーソルの戻りです。
これがないと計算式作成において、便利なのですが、これの抑止の仕方がわかりません。
別の問題(問題というかどうか不明)は、シート移動をした時のカーソルの位置です。通常ではあるがままです。つまり、以前にそのシートで最後にカーソルがあった位置となります。これは当然で当たり前のことなのですが、場合によっては、移動前のシートでの場所と同じところにカーソルがあって欲しいと思うときがあります。
それはさておき、計算式の作成において、最も多いのは座標の入力です。通常はカーソル動かして行います。これをキーボードから入れるのはいやになってしまいます。ここをカバーしたマクロでないと駄目です。
1.座標の指定は、Application.InputBoxメソッドで行います。
Set セル範囲 = Application.InputBox(Prompt:="次のセット範囲の先頭のセルを指定してください", Default:=AAA1,Type:=8)
aaa2 = セル範囲.Address
これと、InputBox関数を絡み合わせて何かを作るという発想で、何とか計算式を作って見ましょう。
2.keisansiki = "xxx= "とおいて、
次に、keisansiki = keisansiki + aaa2 とします。
これをInputBox関数の初期値として設定します。
a = InputBox("計算式に必要な文字を追加してください。", , keisansiki)
keisansiki = a
1.と2.を必要なだけ繰り返します。
1.から2.にいった時、カーソルの場所は直前に指定した場所になります。前の例では、B7にあるということです。ですから、ここからカーソルを次ぎのB8に動かすだけでとなります。
この機能を作って操作してみますと、若干ながらまだひっかかりがありました。
そこで、InputBox関数の時、追加の場合と置換えの場合とに分けることにしました。
追加の場合とは、今までのものに今回入力したものを追加するというものです。
keisansiki = keisansiki + a ということになります。
判定は、"= "が残っているかいないかにしました。
残っていない＝⇒追加、残っている置換え。
ここでのkeisansikiは、文字列となっていますが、先頭の"xxx"をとると計算式になります。InputBox関数の入力の際に、これをとれば直接計算式としてセルに入力されます。
シートを移動した場合は…
通常処理の場合、移動したシートでのカーソルの場所は、画面の左上端のようです。ですからおおむね前に指定した位置ということになります。
したがって、カーソルの位置の問題(カーソルが戻ってしまうという問題)は、同一シート内で考慮すればいいものとなります。
まずは、同一シート内の場合のみで作ってみたらどうでしょうか(シートの移動が手作業では大変な場合は、このマクロにシート移動を組み込めばメリットが出てきます)。
他のシート、他のファイルを参照したいという場合は、ニーズいかんでしょう。そのようなケースは、計算式を作るという作業のうちのおおむね5%程度しかないのかもしれません。自分のニーズと相談してどこまで拡張するかは考えてください。広げれば操作が多くなるというデメリットが、出てきます。
意外だった盲点。
座標の入力、文字列の入力を終了とする判断はマクロでどう書くか。
文字列の入力の際…キャンセルキーを押すと、入力値に""が入るのでそれで判断。
座標の入力の際…キャンセルキーを押すとエラーになるのでそれで判断。
・最初に
On Error GoTo ErrorTrap
・最後に
Exit Sub
ErrorTrap:
errflag = 99
Resume Next
End Sub
・Application.InputBox文の箇所に
Set セル範囲 = Application.InputBox(Prompt:="使用するセルを指定してください。終了はキャンセルキー。", Default:=bbb, Left:=10, Top:=2, Type:=8)
If errflag = 99 Then Exit Do
以上です。
追伸；実際に試してみると、いい感じです。しかし、いつも計算式をはなから作るというときばかりではありません。そこで、既にある計算式を取得するかどうかを聞いてみて、必要な場合は既存の計算式を取得するという機能もメリットがあります。この場合、計算式が入っていない場合も取得するとしておいたほうがいいと思います。こうすれば、文字計算式も取得できますので。
＝⇒現在では、この計算式を作るマクロを、座標入力はinputメソッドのままとして、演算子などの入力をフォームのテキストボックスを使用したものに変えています。
メリット…画面が大きく取れる。文字のフォントを大きくすることができる。表示内部でのコピーの操作ができる。長い計算式でも、一覧できる。その後、inputメソッドで取得する座標を好きな位置に追加することができる(計算の途中に..を入れておき、その部分を置き換える)。
というものがあげられます。テキストボックスベースのものにチャレンジしてみてください。
参考までのフォームに貼り付けたマクロ文を載せておきます。
変数はパブリック変数として定義してください。
Private Sub UserForm_Initialize() 'ユーザーフォームを初期化する
TextBox1.MultiLine = True
TextBox1.Text = p_aa '値をセットする
End Sub
Private Sub TextBox1_Change() 'ボックス内に動きがあると動く
p_aa = TextBox1.Text '選択結果を取得
End Sub
Private Sub CommandButton1_Click()
'続けるボタンがクリックされた
Unload Me 'ユーザーフォームを閉じる
p_flag = 0
End Sub
Private Sub CommandButton2_Click()
'終了ボタンがクリックされた
Unload Me 'ユーザーフォームを閉じる
p_flag = 9
End Sub
ワークシート側でのマクロの記述
p_aa = keisansiki
UserForm3.Show
'ここでUserForm3のマクロに飛ぶ。そのマクロ場終了すると戻る。
keisansiki = p_aa
これまでは、SUMPRODUCT関数という特殊なものを扱ってきましたが、一般的に利用できるマクロはできないでしょうか。
四則演算を使った普通の計算式をつくるために便利なものはないでしょうか、という問題意識です。
通常の方法で計算式をつくる時(カーソルを動かして座標を取得し、間に四則演算子等を加えていく方式)に、気になるところは、セルを指定し終わると、カーソルが計算式のある場所に戻ってしまうことでしょうか(この問題はマウスでやれば出てきません。キーボードならではの問題です)。
例えば、AX7に、=B7/B8という式を入れる場合を考えます。
1.AX7で=を入れる
2.カーソルをB7まで移動します
3./をいれます。ここでカーソルはAX7に戻ります。
4.カーソルをB8まで移動します。
5.Enterを押します
問題は、3.のあとのカーソルの戻りです。
これがないと計算式作成において、便利なのですが、これの抑止の仕方がわかりません。
別の問題(問題というかどうか不明)は、シート移動をした時のカーソルの位置です。通常ではあるがままです。つまり、以前にそのシートで最後にカーソルがあった位置となります。これは当然で当たり前のことなのですが、場合によっては、移動前のシートでの場所と同じところにカーソルがあって欲しいと思うときがあります。
それはさておき、計算式の作成において、最も多いのは座標の入力です。通常はカーソル動かして行います。これをキーボードから入れるのはいやになってしまいます。ここをカバーしたマクロでないと駄目です。
1.座標の指定は、Application.InputBoxメソッドで行います。
Set セル範囲 = Application.InputBox(Prompt:="次のセット範囲の先頭のセルを指定してください", Default:=AAA1,Type:=8)
aaa2 = セル範囲.Address
これと、InputBox関数を絡み合わせて何かを作るという発想で、何とか計算式を作って見ましょう。
2.keisansiki = "xxx= "とおいて、
次に、keisansiki = keisansiki + aaa2 とします。
これをInputBox関数の初期値として設定します。
a = InputBox("計算式に必要な文字を追加してください。", , keisansiki)
keisansiki = a
1.と2.を必要なだけ繰り返します。
1.から2.にいった時、カーソルの場所は直前に指定した場所になります。前の例では、B7にあるということです。ですから、ここからカーソルを次ぎのB8に動かすだけでとなります。
この機能を作って操作してみますと、若干ながらまだひっかかりがありました。
そこで、InputBox関数の時、追加の場合と置換えの場合とに分けることにしました。
追加の場合とは、今までのものに今回入力したものを追加するというものです。
keisansiki = keisansiki + a ということになります。
判定は、"= "が残っているかいないかにしました。
残っていない＝⇒追加、残っている置換え。
ここでのkeisansikiは、文字列となっていますが、先頭の"xxx"をとると計算式になります。InputBox関数の入力の際に、これをとれば直接計算式としてセルに入力されます。
シートを移動した場合は…
通常処理の場合、移動したシートでのカーソルの場所は、画面の左上端のようです。ですからおおむね前に指定した位置ということになります。
したがって、カーソルの位置の問題(カーソルが戻ってしまうという問題)は、同一シート内で考慮すればいいものとなります。
まずは、同一シート内の場合のみで作ってみたらどうでしょうか(シートの移動が手作業では大変な場合は、このマクロにシート移動を組み込めばメリットが出てきます)。
他のシート、他のファイルを参照したいという場合は、ニーズいかんでしょう。そのようなケースは、計算式を作るという作業のうちのおおむね5%程度しかないのかもしれません。自分のニーズと相談してどこまで拡張するかは考えてください。広げれば操作が多くなるというデメリットが、出てきます。
意外だった盲点。
座標の入力、文字列の入力を終了とする判断はマクロでどう書くか。
文字列の入力の際…キャンセルキーを押すと、入力値に""が入るのでそれで判断。
座標の入力の際…キャンセルキーを押すとエラーになるのでそれで判断。
・最初に
On Error GoTo ErrorTrap
・最後に
Exit Sub
ErrorTrap:
errflag = 99
Resume Next
End Sub
・Application.InputBox文の箇所に
Set セル範囲 = Application.InputBox(Prompt:="使用するセルを指定してください。終了はキャンセルキー。", Default:=bbb, Left:=10, Top:=2, Type:=8)
If errflag = 99 Then Exit Do
以上です。
追伸；実際に試してみると、いい感じです。しかし、いつも計算式をはなから作るというときばかりではありません。そこで、既にある計算式を取得するかどうかを聞いてみて、必要な場合は既存の計算式を取得するという機能もメリットがあります。この場合、計算式が入っていない場合も取得するとしておいたほうがいいと思います。こうすれば、文字計算式も取得できますので。
＝⇒現在では、この計算式を作るマクロを、座標入力はinputメソッドのままとして、演算子などの入力をフォームのテキストボックスを使用したものに変えています。
メリット…画面が大きく取れる。文字のフォントを大きくすることができる。表示内部でのコピーの操作ができる。長い計算式でも、一覧できる。その後、inputメソッドで取得する座標を好きな位置に追加することができる(計算の途中に..を入れておき、その部分を置き換える)。
というものがあげられます。テキストボックスベースのものにチャレンジしてみてください。
参考までのフォームに貼り付けたマクロ文を載せておきます。
変数はパブリック変数として定義してください。
Private Sub UserForm_Initialize() 'ユーザーフォームを初期化する
TextBox1.MultiLine = True
TextBox1.Text = p_aa '値をセットする
End Sub
Private Sub TextBox1_Change() 'ボックス内に動きがあると動く
p_aa = TextBox1.Text '選択結果を取得
End Sub
Private Sub CommandButton1_Click()
'続けるボタンがクリックされた
Unload Me 'ユーザーフォームを閉じる
p_flag = 0
End Sub
Private Sub CommandButton2_Click()
'終了ボタンがクリックされた
Unload Me 'ユーザーフォームを閉じる
p_flag = 9
End Sub
ワークシート側でのマクロの記述
p_aa = keisansiki
UserForm3.Show
'ここでUserForm3のマクロに飛ぶ。そのマクロ場終了すると戻る。
keisansiki = p_aa
2009年8月1日土曜日
第123回目 計算式作成のお手伝い(その２;前回の続き)
第123回目 計算式作成のお手伝い(その２;前回の続き)
0.最初の処理
シート名、当該シート番号を取得しておきます。
shname1 = ActiveSheet.Name
ii_max = Sheets.Count 'これはグラフのシートを含む全シートの数です。
ii0 = ActiveSheet.Index 'これは当該シートの番号です。
1.最初の範囲の取得
これは定番の処理ですが、ちょっと問題なのは、列行とも複数の指定であった場合、縦に掛けるのか横に掛けるの判断です。正解はないのですが、指定した行数･列数が小さいほうを掛ける方向とします。例えば2行10列を指定していたら、下の方向に掛けていきます。
''範囲情報の取得
AAA = Selection.Address
gyo1 = Selection.Row
gyo_cnt = Selection.Rows.Count
gyo9 = gyo1 + gyo_cnt - 1
retu1 = Selection.Column
retu_cnt = Selection.Columns.Count
retu9 = retu1 + retu_cnt - 1
AAA1 = Cells(gyo1, retu1).Address
AAA9 = Cells(gyo9, retu9).Address
If gyo_cnt < tate =" 1" tate =" 1" i =" 1" i =" 1" kaisuu =" i" kubun =" InputBox("> ll100 Then ii_max = ll100
For i = 1 To ii_max 'ワークシートの数だけ繰り返す
ww(i) = Sheets(i).Name '取得したシート名を取得する
Next i
ii0 = ActiveSheet.Index
'シートの選択。ファイルが一つの場合は何回もする必要はありませんが、複数のファイルの処理を想定しています。
For i = 1 To ii_max 'ファイルが一つの場合は何回もする必要はありませんが、複数のファイルの処理を想定しています。
msg01 = msg01 & Str$(i) & "-" & ww(i) & " "
Next i
msg00 = "飛び先のシート番号を入れてください。 "
h1 = InputBox(msg00 & Str(ii_max) & "以下。 キャンセルは999。そのままのシートの場合は、0。" & vbCrLf & msg01, "シート ジャンプ", 0)
h2 = Val(h1)
If h2 > ll100 Then Exit Sub 'キャンセルにより終了
If h2 > ii_max Then h2 = ii_max '範囲外をいれた場合は、最後のシートの指定とする。
If h2 = 0 Then h2 = ii0
Sheets(h2).Select
shname2 = ActiveSheet.Name
'ループの抜け出し
If kubun <> 1 Then Exit Do
4.次ぎの要素の先頭座標の指定
カーソルで先頭座標を指定します。
Set セル範囲 = Application.InputBox(Prompt:="次のセット範囲の先頭のセルを指定してください", Default:=AAA1, Left:=10, Top:=2, Type:=8)
aaa2 = セル範囲.Address
5.SUMPRODUCTの構成要素をつくる
この段階の指定は、1列または1行なので、指定された座標から構成要素をつくります。
ここでも列の場合は、記号から番号、番号から記号へと変換します。
3.に戻る
6.計算式の貼付け先のセルの設定
Set セル範囲 = Application.InputBox(PROMPT:="'計算式の貼付け先のセルを指定してください", Default:=AAA90, Left:=10, Top:=2, Type:=8)
AAA2 = セル範囲.Address
shname2 = ActiveSheet.Name
7.計算式の貼付け
'要素をつなげる
For i = 1 To kaisuu
a = a & hani(i) & ","
Next
a = Left$(a, Len(a) - 1) '最後のカンマを取る
a = "=sumproduct(" & a & ")"
' a = "xxx=sumproduct(" & a & ")" 'この場合は文字列になります
'貼付け先と同一シート名を取り除く
d = "'" & shname2 & "'!"
Do While 1
p1 = InStr(a, d)
If p1 = 0 Then Exit Do
a = Left$(a, p1 - 1) & Mid$(a, p1 + Len(d))
Loop
'計算式の貼付け
Range(aaa2).Select
Range(aaa2).Formula = a
Selection.Style = "Comma [0]"
※記述したマクロは主な部分のものです
□更なる機能アップ
SUMPRODUCTの結果を総人数などで割り、平均を求める場合があります。そこでついでに割る数があるかどうか、ある場合はその座標を指定するという機能です。
通常は加重平均をとるという事例が多いのではないでしょうか。
例；年齢別に人数と給料額が並んでいます。全員の平均給料額はいくらですか。
人数の下には総人数を、給料額の下には全員の平均値を入れます。
まずは、総人数を、SUM関数で求めます。次に今回のマクロを使って給料の総額を求め、総人数で割って平均を求めましょう。給料額の下には全員の平均値を入れます。
△SUM関数ってSUMPRODUCT関数に似ていますよね。同時にできないのでしょうか。
＝⇒同時にはできませんが、SUMPRODUCT関数を作るマクロで、一列(行)のみの指定であった場合は、SUM関数とするというのはどうでしょうか。そうすれば一つのマクロで二つの関数が作成できます。
△総人数と平均値を入れる場所は近いですね。
＝⇒そうです。ですから割る数と入れる場所と計算式を入れる場所の指定の際の初期値は工夫をするといいと思います。カーソルの移動が非常に便利になります。
△たてかける横のSUMPRODUCT関数はどうでしょうか。
＝⇒用途があるのでしょうか。まったくないとはいえませんが、かなりまれではないでしょうか。たてか横で作って計算式を修正したらどうでしょうか。そもそもSUMPRODUCT関数でそんなことができるのでしょうか。
＝⇒できませんでした。#VALUE!となってしまいました。
△そのような事例が出てきたらどうしましょうか。
＝⇒どちらかを変形して、たて同士、横同士にしてからという考えはどうでしょうか。たてを横にするとかいうのは、すでにやったような気がします。
『第18回目 凝った計算式の作り方』で取り上げていました。しかし、これはエクセルマクロの計算式で行うもので、ちょっと操作が多くなります。
△二次元の表の加重平均はどうなりますか。
＝⇒テーマは、たて6区分、横54区分に分けられた人数と給料(平均)があります。これの合計レベルの平均の給料を求めるということになります。
まずは、人数のたて計、横計をとります。
次に、給料の横計は、人数表の1行目と給料表の1行目のSUMPRODUCT関数の平均額となります。
これを通常のコピーで、他の横計のセルに計算式をコピーします。
給料のたて計は、人数表の1列目と給料表の1列目のSUMPRODUCT関数の平均額となります。
これを通常のコピーで、他のたて計のセルに計算式をコピーします。
0.最初の処理
シート名、当該シート番号を取得しておきます。
shname1 = ActiveSheet.Name
ii_max = Sheets.Count 'これはグラフのシートを含む全シートの数です。
ii0 = ActiveSheet.Index 'これは当該シートの番号です。
1.最初の範囲の取得
これは定番の処理ですが、ちょっと問題なのは、列行とも複数の指定であった場合、縦に掛けるのか横に掛けるの判断です。正解はないのですが、指定した行数･列数が小さいほうを掛ける方向とします。例えば2行10列を指定していたら、下の方向に掛けていきます。
''範囲情報の取得
AAA = Selection.Address
gyo1 = Selection.Row
gyo_cnt = Selection.Rows.Count
gyo9 = gyo1 + gyo_cnt - 1
retu1 = Selection.Column
retu_cnt = Selection.Columns.Count
retu9 = retu1 + retu_cnt - 1
AAA1 = Cells(gyo1, retu1).Address
AAA9 = Cells(gyo9, retu9).Address
If gyo_cnt < tate =" 1" tate =" 1" i =" 1" i =" 1" kaisuu =" i" kubun =" InputBox("> ll100 Then ii_max = ll100
For i = 1 To ii_max 'ワークシートの数だけ繰り返す
ww(i) = Sheets(i).Name '取得したシート名を取得する
Next i
ii0 = ActiveSheet.Index
'シートの選択。ファイルが一つの場合は何回もする必要はありませんが、複数のファイルの処理を想定しています。
For i = 1 To ii_max 'ファイルが一つの場合は何回もする必要はありませんが、複数のファイルの処理を想定しています。
msg01 = msg01 & Str$(i) & "-" & ww(i) & " "
Next i
msg00 = "飛び先のシート番号を入れてください。 "
h1 = InputBox(msg00 & Str(ii_max) & "以下。 キャンセルは999。そのままのシートの場合は、0。" & vbCrLf & msg01, "シート ジャンプ", 0)
h2 = Val(h1)
If h2 > ll100 Then Exit Sub 'キャンセルにより終了
If h2 > ii_max Then h2 = ii_max '範囲外をいれた場合は、最後のシートの指定とする。
If h2 = 0 Then h2 = ii0
Sheets(h2).Select
shname2 = ActiveSheet.Name
'ループの抜け出し
If kubun <> 1 Then Exit Do
4.次ぎの要素の先頭座標の指定
カーソルで先頭座標を指定します。
Set セル範囲 = Application.InputBox(Prompt:="次のセット範囲の先頭のセルを指定してください", Default:=AAA1, Left:=10, Top:=2, Type:=8)
aaa2 = セル範囲.Address
5.SUMPRODUCTの構成要素をつくる
この段階の指定は、1列または1行なので、指定された座標から構成要素をつくります。
ここでも列の場合は、記号から番号、番号から記号へと変換します。
3.に戻る
6.計算式の貼付け先のセルの設定
Set セル範囲 = Application.InputBox(PROMPT:="'計算式の貼付け先のセルを指定してください", Default:=AAA90, Left:=10, Top:=2, Type:=8)
AAA2 = セル範囲.Address
shname2 = ActiveSheet.Name
7.計算式の貼付け
'要素をつなげる
For i = 1 To kaisuu
a = a & hani(i) & ","
Next
a = Left$(a, Len(a) - 1) '最後のカンマを取る
a = "=sumproduct(" & a & ")"
' a = "xxx=sumproduct(" & a & ")" 'この場合は文字列になります
'貼付け先と同一シート名を取り除く
d = "'" & shname2 & "'!"
Do While 1
p1 = InStr(a, d)
If p1 = 0 Then Exit Do
a = Left$(a, p1 - 1) & Mid$(a, p1 + Len(d))
Loop
'計算式の貼付け
Range(aaa2).Select
Range(aaa2).Formula = a
Selection.Style = "Comma [0]"
※記述したマクロは主な部分のものです
□更なる機能アップ
SUMPRODUCTの結果を総人数などで割り、平均を求める場合があります。そこでついでに割る数があるかどうか、ある場合はその座標を指定するという機能です。
通常は加重平均をとるという事例が多いのではないでしょうか。
例；年齢別に人数と給料額が並んでいます。全員の平均給料額はいくらですか。
人数の下には総人数を、給料額の下には全員の平均値を入れます。
まずは、総人数を、SUM関数で求めます。次に今回のマクロを使って給料の総額を求め、総人数で割って平均を求めましょう。給料額の下には全員の平均値を入れます。
△SUM関数ってSUMPRODUCT関数に似ていますよね。同時にできないのでしょうか。
＝⇒同時にはできませんが、SUMPRODUCT関数を作るマクロで、一列(行)のみの指定であった場合は、SUM関数とするというのはどうでしょうか。そうすれば一つのマクロで二つの関数が作成できます。
△総人数と平均値を入れる場所は近いですね。
＝⇒そうです。ですから割る数と入れる場所と計算式を入れる場所の指定の際の初期値は工夫をするといいと思います。カーソルの移動が非常に便利になります。
△たてかける横のSUMPRODUCT関数はどうでしょうか。
＝⇒用途があるのでしょうか。まったくないとはいえませんが、かなりまれではないでしょうか。たてか横で作って計算式を修正したらどうでしょうか。そもそもSUMPRODUCT関数でそんなことができるのでしょうか。
＝⇒できませんでした。#VALUE!となってしまいました。
△そのような事例が出てきたらどうしましょうか。
＝⇒どちらかを変形して、たて同士、横同士にしてからという考えはどうでしょうか。たてを横にするとかいうのは、すでにやったような気がします。
『第18回目 凝った計算式の作り方』で取り上げていました。しかし、これはエクセルマクロの計算式で行うもので、ちょっと操作が多くなります。
△二次元の表の加重平均はどうなりますか。
＝⇒テーマは、たて6区分、横54区分に分けられた人数と給料(平均)があります。これの合計レベルの平均の給料を求めるということになります。
まずは、人数のたて計、横計をとります。
次に、給料の横計は、人数表の1行目と給料表の1行目のSUMPRODUCT関数の平均額となります。
これを通常のコピーで、他の横計のセルに計算式をコピーします。
給料のたて計は、人数表の1列目と給料表の1列目のSUMPRODUCT関数の平均額となります。
これを通常のコピーで、他のたて計のセルに計算式をコピーします。
2009年7月20日月曜日
○第122回目 計算式作成のお手伝い
○第122回目 計算式作成のお手伝い
【第99回目 計算式の加工(DIV/0対応、端数処理とif文)も参照してください】
まずは、"計算式作成のお手伝い"の意味から。
エクセルの作業を行っていたところ、次のような計算式を作成する必要が出てきました。
=SUMPRODUCT(別のシート1!BB4:BB23,別のシート1!BA38:BA57)
(※SUMPRODUCTとは、各要素を掛け算した結果を足したものという意味の関数です)
この計算の作成は若干面倒なので、何か工夫はないかと考えていました。
改良すべき点は、
1.最初のセルの範囲の指定は必要であるが、二番目以降は先頭のセルのみ指定する。
2.掛ける要素が横とか縦に既に並んでいる場合は、全体の範囲を指定する。あわせて続けて掛ける要素の指定ができる。
3.次の処理でのカーソルの位置は、原則として前の処理のカーソルの位置とする。
4.シートの移動も可能とする。
5.一列指定(又は一行指定)の場合は、SUMPRODUCTの変わりにSUMとなる。
というくらいでしょうか。
※4.については、前回参照。5.についてはおまけ的要素なので省略。
さて、それでは、
マクロの実行は、既に最初に参照する範囲が指定されているところからです(計算式を入れる場所は一番最後に指定します)。
0.最初の処理
シート名、当該シート番号を取得しておきます。
1.最初の範囲の取得
これは定番の処理ですが、ちょっと問題なのは、列行とも複数の指定であった場合、縦に掛けるのか横に掛けるの判断です。正解はないのですが、指定した行数･列数が小さいほうを掛ける方向とします。例えば2行10列を指定していたら、下の方向に掛けていきます。
例えば、EB9:EK10と指定した場合。
xxx=SUMPRODUCT(EB9:EK9,EB10:EK10)
EP9:EQ17)と指定した場合。
xxx=SUMPRODUCT(EP9:EP17,EQ9:EQ17)
2.SUMPRODUCTの構成要素をつくる
1.での指定が1列または1行であれば、要素は座標そのままとなりますが、複数の指定である場合は、それをわける必要があります。また、最後に計算式を入れる場所がわかりませんので、シート名を付加しておきます。
座標に、『'シート名'!』というものを付加してください(『 』の中)。
範囲の分割では、列が問題になりますが、列記号を列番号に変換して、列番号ごとに一つの範囲をつくり、列番号を列記号に変換して、要素とします。
3.次の要素にかかるシートの指定
ループ処理になるので、続けて範囲を指定するかどうかを聞きます。ループを抜け出すのは、シートの指定後であり、4の直前となります。
4.次の要素の先頭座標の指定
カーソルで先頭座標を指定します。
5.SUMPRODUCTの構成要素をつくる
この段階の指定は、1列または1行なので、指定された座標から構成要素をつくります。
ここでも列の場合は、列記号から列番号、列番号から列記号へと変換します。
3.に戻る
6.計算式の貼付け
計算式を貼付ける座標を指定します(ブック、シートはこの処理の直前に指定されています)。
指定後、計算式を作り貼付けます。
ここで問題は、シート名がついているということです。したがって、この段階で、計算式を入れる場所と等しいシート名は削除します。
instr関数で当該シート名があるかどうか見てください。あったら削除です。なお、純粋なシート名には、'とか!はついていませんので注意してください。
最初は、貼付けた時、計算式だとエラーになる場合が考えられますので、計算式としてではなく、文字列として貼付けてください。=の頭に何か文字をつければ計算式ではなくなります。
※次は詳細に掘り下げます。
【第99回目 計算式の加工(DIV/0対応、端数処理とif文)も参照してください】
まずは、"計算式作成のお手伝い"の意味から。
エクセルの作業を行っていたところ、次のような計算式を作成する必要が出てきました。
=SUMPRODUCT(別のシート1!BB4:BB23,別のシート1!BA38:BA57)
(※SUMPRODUCTとは、各要素を掛け算した結果を足したものという意味の関数です)
この計算の作成は若干面倒なので、何か工夫はないかと考えていました。
改良すべき点は、
1.最初のセルの範囲の指定は必要であるが、二番目以降は先頭のセルのみ指定する。
2.掛ける要素が横とか縦に既に並んでいる場合は、全体の範囲を指定する。あわせて続けて掛ける要素の指定ができる。
3.次の処理でのカーソルの位置は、原則として前の処理のカーソルの位置とする。
4.シートの移動も可能とする。
5.一列指定(又は一行指定)の場合は、SUMPRODUCTの変わりにSUMとなる。
というくらいでしょうか。
※4.については、前回参照。5.についてはおまけ的要素なので省略。
さて、それでは、
マクロの実行は、既に最初に参照する範囲が指定されているところからです(計算式を入れる場所は一番最後に指定します)。
0.最初の処理
シート名、当該シート番号を取得しておきます。
1.最初の範囲の取得
これは定番の処理ですが、ちょっと問題なのは、列行とも複数の指定であった場合、縦に掛けるのか横に掛けるの判断です。正解はないのですが、指定した行数･列数が小さいほうを掛ける方向とします。例えば2行10列を指定していたら、下の方向に掛けていきます。
例えば、EB9:EK10と指定した場合。
xxx=SUMPRODUCT(EB9:EK9,EB10:EK10)
EP9:EQ17)と指定した場合。
xxx=SUMPRODUCT(EP9:EP17,EQ9:EQ17)
2.SUMPRODUCTの構成要素をつくる
1.での指定が1列または1行であれば、要素は座標そのままとなりますが、複数の指定である場合は、それをわける必要があります。また、最後に計算式を入れる場所がわかりませんので、シート名を付加しておきます。
座標に、『'シート名'!』というものを付加してください(『 』の中)。
範囲の分割では、列が問題になりますが、列記号を列番号に変換して、列番号ごとに一つの範囲をつくり、列番号を列記号に変換して、要素とします。
3.次の要素にかかるシートの指定
ループ処理になるので、続けて範囲を指定するかどうかを聞きます。ループを抜け出すのは、シートの指定後であり、4の直前となります。
4.次の要素の先頭座標の指定
カーソルで先頭座標を指定します。
5.SUMPRODUCTの構成要素をつくる
この段階の指定は、1列または1行なので、指定された座標から構成要素をつくります。
ここでも列の場合は、列記号から列番号、列番号から列記号へと変換します。
3.に戻る
6.計算式の貼付け
計算式を貼付ける座標を指定します(ブック、シートはこの処理の直前に指定されています)。
指定後、計算式を作り貼付けます。
ここで問題は、シート名がついているということです。したがって、この段階で、計算式を入れる場所と等しいシート名は削除します。
instr関数で当該シート名があるかどうか見てください。あったら削除です。なお、純粋なシート名には、'とか!はついていませんので注意してください。
最初は、貼付けた時、計算式だとエラーになる場合が考えられますので、計算式としてではなく、文字列として貼付けてください。=の頭に何か文字をつければ計算式ではなくなります。
※次は詳細に掘り下げます。
2009年6月20日土曜日
第121回目 印刷属性のコピー
○第121回目 印刷属性のコピー
印刷属性のコピーです。
具体的には、
用紙の大きさ、向き
拡大縮小
各マージン
ヘッダーとフッタ
列及び行見出し
印刷範囲
など
を、ほとんどそっくりそのまま、他のシートにコピーしようというものです。
(まずは、一つのシートのみにコピーすることからはじめましょう。次に適用するシートの範囲を指定できることを考えましょう。実は番号の指定機能は既に図形作成システムでできあがっています。そこでは、"1.5-8.12.20-23"という与え方が可能です。これを借用しましょう)
印刷書式の設定をマクロの記録で取ってみますと、
おおむね次のとおりとなります(一部分)。
With ActiveSheet.PageSetup
.RightHeader = "&D;&T"
.RightFooter = "&9&Z&F&A"
.LeftMargin = Application.InchesToPoints(0.984251968503937)
.BottomMargin = Application.InchesToPoints(0.62992125984252)
' .Orientation = xlLandscape
' .PaperSize = xlPaperA4
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
これは印刷属性の設定です。
そして、この右辺側は変数でも可能です。
次に、もとのシートから印刷属性を取得です。
印刷属性の取得はどうなるでしょうか。
With ActiveSheet.PageSetup
mi_r = .PrintTitleRows
End With
つまりイコールの左右を逆にすればいいことになります。
とても簡単です。
このように取得した印刷属性を、コピー先のシートに行き、設定してあげればよいことになります。
この中から必要なものを選んでやってみてください。
With ActiveSheet.PageSetup
.PrintTitleRows = mi_r
.PrintTitleColumns = mi_c
.PageSetup.PrintArea = p_a
.LeftHeader = h_l
.CenterHeader = h_c
.RightHeader = h_r
.LeftFooter = f_l
.CenterFooter = f_c
.RightFooter = f_r
.LeftMargin = m_l
.RightMargin = m_r
.TopMargin = m_t
.BottomMargin = m_b
.HeaderMargin = m_h
.FooterMargin = m_f
.CenterHorizontally = c_h
.CenterVertically = c_v
.Orientation = y_muki
.PaperSize = y_size
.FirstPageNumber = f_p_n
.Order = p_o
.Zoom = p_zoom
.FitToPagesWide = f_p_w
.FitToPagesTall = f_p_t
.PrintErrors = p_e
End With
おしまい。
印刷属性のコピーです。
具体的には、
用紙の大きさ、向き
拡大縮小
各マージン
ヘッダーとフッタ
列及び行見出し
印刷範囲
など
を、ほとんどそっくりそのまま、他のシートにコピーしようというものです。
(まずは、一つのシートのみにコピーすることからはじめましょう。次に適用するシートの範囲を指定できることを考えましょう。実は番号の指定機能は既に図形作成システムでできあがっています。そこでは、"1.5-8.12.20-23"という与え方が可能です。これを借用しましょう)
印刷書式の設定をマクロの記録で取ってみますと、
おおむね次のとおりとなります(一部分)。
With ActiveSheet.PageSetup
.RightHeader = "&D;&T"
.RightFooter = "&9&Z&F&A"
.LeftMargin = Application.InchesToPoints(0.984251968503937)
.BottomMargin = Application.InchesToPoints(0.62992125984252)
' .Orientation = xlLandscape
' .PaperSize = xlPaperA4
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
これは印刷属性の設定です。
そして、この右辺側は変数でも可能です。
次に、もとのシートから印刷属性を取得です。
印刷属性の取得はどうなるでしょうか。
With ActiveSheet.PageSetup
mi_r = .PrintTitleRows
End With
つまりイコールの左右を逆にすればいいことになります。
とても簡単です。
このように取得した印刷属性を、コピー先のシートに行き、設定してあげればよいことになります。
この中から必要なものを選んでやってみてください。
With ActiveSheet.PageSetup
.PrintTitleRows = mi_r
.PrintTitleColumns = mi_c
.PageSetup.PrintArea = p_a
.LeftHeader = h_l
.CenterHeader = h_c
.RightHeader = h_r
.LeftFooter = f_l
.CenterFooter = f_c
.RightFooter = f_r
.LeftMargin = m_l
.RightMargin = m_r
.TopMargin = m_t
.BottomMargin = m_b
.HeaderMargin = m_h
.FooterMargin = m_f
.CenterHorizontally = c_h
.CenterVertically = c_v
.Orientation = y_muki
.PaperSize = y_size
.FirstPageNumber = f_p_n
.Order = p_o
.Zoom = p_zoom
.FitToPagesWide = f_p_w
.FitToPagesTall = f_p_t
.PrintErrors = p_e
End With
おしまい。
2009年6月14日日曜日
第120回目 ジャンプマクロ、違うファイル(ブック)へはできないものか
○第120回目 ジャンプマクロ、違うファイル(ブック)へはできないものか
これまでのジャンプでは、同一シート内のある場所又は、当該ブック内の他のシート内のある場所へジャンプするというものでした。
これと同様に、計算式等のコピーについても、同一ブック内という制約がありました。これを、開いているどのブックにも移動できるという機能を考えました。
結果的には、たいした手間もなくできました。
簡単にいうと、シートの時と同じように、開いているブック名を取得すれば大体は終了となります。
あれこれ調べていたら、参考になる例示がありましたので、それを元に作成してみました。
┏━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ http://www.happy500z.com/YNxv252.html#8
┃ 8) ブックが開いているか調べる
┃Sub ブックが開いているか調べる()
┃ ブック名 = "BBB.xls" 'BBBにはブック名を記入
┃ '※１
┃ For Each 各ブック In Workbooks
┃ If 各ブック.Name = ブック名 Then
┃ MsgBox "開いています。", , ブック名
┃ Exit Sub
┃ End If
┃ Next
┃ MsgBox "開いていません。", , ブック名
┃End Sub
┗━━━━━━━━━━━━━━━━━━━━━━━━━┛
□これを使いブック名を取得します。
'初期状態の情報を取得
fname1 = ActiveWorkbook.Name
shname1 = ActiveSheet.Name
ii_max = Sheets.Count
ii0 = ActiveSheet.Index
'開かれているブック名､数を取得する
b_name(0) = fname1
i = 1
For Each 各ブック In Workbooks
b_name(i) = 各ブック.Name
i = i + 1
Next
b_cnt = i - 1
□次の問題は、同一シート内のジャンプの操作の手間を少なくする工夫です。
ブックの指定で、当該ブックの当該シートの場合は、99(デフォルト値)
当該ブックの別のシートの場合は、0
別のブックの別のシートの場合は、1～
このために、ブック名の配列変数の0番目には、最初のブック名を入れています。(b_name(0) = fname1)
'ブックの移動
bk_no = InputBox("使用したいブックを指定してください。そのままは0。そのままのシートの場合は、99。 " & vbCrLf & _
"01=⇒" & b_name(1) & vbCrLf & _
"02=⇒" & b_name(2) & vbCrLf & _
"03=⇒" & b_name(3) & vbCrLf & _
【途中略】
"14=⇒" & b_name(14) & vbCrLf & _
"15=⇒" & b_name(15) & vbCrLf _
, xpos:=2000, ypos:=3000, Default:=99)
bk_no0 = bk_no
If bk_no <> 15 Then bk_no = 0
□ブックの移動後に、移動先のブックでのシート属性、名を取得
'ブックの移動
Windows(b_name(bk_no)).Activate
'移動先のブックでのシートの属性取得
ii2 = ActiveSheet.Index
ii_max2 = Sheets.Count
'シート名の取得
cc = Sheets.Count
If cc > ll100 Then cc = ll100
For i = 1 To cc 'ワークシートの数だけ繰り返す
ww(i) = Sheets(i).Name 'シート名を取得する
Next i
'現在のシート番号の取得
For ii = 1 To cc
If ActiveSheet.Name = ww(ii) Then Exit For 'シート名と同じ番号を取得する
Next ii
sheet_no = ii
□シート番号を指定して、当該シートに移動する
msg00 = "飛び先のシート番号を入れてください。 "
h1 = InputBox(msg00 & Str(cc) & "以下。 キャンセルは999。そのままのシートの場合は、0。" & vbCrLf & msg01, "シート ジャンプ", 0)
h2 = Val(h1)
If h2 = 0 Then h2 = sheet_no '当該シートの場合
Sheets(h2).Select
※msg01には、シート番号とシート名をつなげて入れておきます。
※セルへの移動は、第25回を参照してください。
以上はジャンプ関係の処理ですが、圧倒的にはコピー機能でつかうほうが多いので、コピー機能の中に、他のブックに飛べるような機能を追加してください。コマンドをコピーだけでほとんどマクロは完成します(コピーの場合は、最後の"セルへの移動"の機能は省いてもいいと思います)。
筆者のコピー関係のマクロは、計算式のコピーと、値のコピーの二種類です。これをワンタッチ(Ctrl+QまたはCtrl+W)で動かすことができます。処理の範囲を開いているブック全部に広げたおかげで、だいぶ操作が楽になりました。
このところ他のブックを参照しながらの仕事が多くなったからだと思います。
※更なる改善点
・指定した情報を持っておき、次の指示の時の初期値としておく。次の処理も同じブックへコピーする場合、非常に便利。
・コピーの場合、コピー元の座標を指定するが、コピー先の別のブックのシートでも、同じ座標にするかどうかを聞く機能。同じ形式のシートになっている場合は、非常に便利。
・コピー終了後、コピー元に戻るかの指定
・逆指定によるコピー。コピー先から指定し、次にコピー元を指定する。
※コピー機能は、非常によく使うものです。そこで、上記のように他のブックにコピーできるもののほかに、同一シート内のコピーのみに特化したマクロも持っておくと便利です。他のブックへコピー、その次は、同一シート内にコピー、次は他のブックという場合もありますので、指示情報を生かすためです。
次回は、印刷属性のコピーを取り上げます。もちろん他のブックのシートまでコピーは可能です。この機能も便利の一言です。
これまでのジャンプでは、同一シート内のある場所又は、当該ブック内の他のシート内のある場所へジャンプするというものでした。
これと同様に、計算式等のコピーについても、同一ブック内という制約がありました。これを、開いているどのブックにも移動できるという機能を考えました。
結果的には、たいした手間もなくできました。
簡単にいうと、シートの時と同じように、開いているブック名を取得すれば大体は終了となります。
あれこれ調べていたら、参考になる例示がありましたので、それを元に作成してみました。
┏━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ http://www.happy500z.com/YNxv252.html#8
┃ 8) ブックが開いているか調べる
┃Sub ブックが開いているか調べる()
┃ ブック名 = "BBB.xls" 'BBBにはブック名を記入
┃ '※１
┃ For Each 各ブック In Workbooks
┃ If 各ブック.Name = ブック名 Then
┃ MsgBox "開いています。", , ブック名
┃ Exit Sub
┃ End If
┃ Next
┃ MsgBox "開いていません。", , ブック名
┃End Sub
┗━━━━━━━━━━━━━━━━━━━━━━━━━┛
□これを使いブック名を取得します。
'初期状態の情報を取得
fname1 = ActiveWorkbook.Name
shname1 = ActiveSheet.Name
ii_max = Sheets.Count
ii0 = ActiveSheet.Index
'開かれているブック名､数を取得する
b_name(0) = fname1
i = 1
For Each 各ブック In Workbooks
b_name(i) = 各ブック.Name
i = i + 1
Next
b_cnt = i - 1
□次の問題は、同一シート内のジャンプの操作の手間を少なくする工夫です。
ブックの指定で、当該ブックの当該シートの場合は、99(デフォルト値)
当該ブックの別のシートの場合は、0
別のブックの別のシートの場合は、1～
このために、ブック名の配列変数の0番目には、最初のブック名を入れています。(b_name(0) = fname1)
'ブックの移動
bk_no = InputBox("使用したいブックを指定してください。そのままは0。そのままのシートの場合は、99。 " & vbCrLf & _
"01=⇒" & b_name(1) & vbCrLf & _
"02=⇒" & b_name(2) & vbCrLf & _
"03=⇒" & b_name(3) & vbCrLf & _
【途中略】
"14=⇒" & b_name(14) & vbCrLf & _
"15=⇒" & b_name(15) & vbCrLf _
, xpos:=2000, ypos:=3000, Default:=99)
bk_no0 = bk_no
If bk_no <> 15 Then bk_no = 0
□ブックの移動後に、移動先のブックでのシート属性、名を取得
'ブックの移動
Windows(b_name(bk_no)).Activate
'移動先のブックでのシートの属性取得
ii2 = ActiveSheet.Index
ii_max2 = Sheets.Count
'シート名の取得
cc = Sheets.Count
If cc > ll100 Then cc = ll100
For i = 1 To cc 'ワークシートの数だけ繰り返す
ww(i) = Sheets(i).Name 'シート名を取得する
Next i
'現在のシート番号の取得
For ii = 1 To cc
If ActiveSheet.Name = ww(ii) Then Exit For 'シート名と同じ番号を取得する
Next ii
sheet_no = ii
□シート番号を指定して、当該シートに移動する
msg00 = "飛び先のシート番号を入れてください。 "
h1 = InputBox(msg00 & Str(cc) & "以下。 キャンセルは999。そのままのシートの場合は、0。" & vbCrLf & msg01, "シート ジャンプ", 0)
h2 = Val(h1)
If h2 = 0 Then h2 = sheet_no '当該シートの場合
Sheets(h2).Select
※msg01には、シート番号とシート名をつなげて入れておきます。
※セルへの移動は、第25回を参照してください。
以上はジャンプ関係の処理ですが、圧倒的にはコピー機能でつかうほうが多いので、コピー機能の中に、他のブックに飛べるような機能を追加してください。コマンドをコピーだけでほとんどマクロは完成します(コピーの場合は、最後の"セルへの移動"の機能は省いてもいいと思います)。
筆者のコピー関係のマクロは、計算式のコピーと、値のコピーの二種類です。これをワンタッチ(Ctrl+QまたはCtrl+W)で動かすことができます。処理の範囲を開いているブック全部に広げたおかげで、だいぶ操作が楽になりました。
このところ他のブックを参照しながらの仕事が多くなったからだと思います。
※更なる改善点
・指定した情報を持っておき、次の指示の時の初期値としておく。次の処理も同じブックへコピーする場合、非常に便利。
・コピーの場合、コピー元の座標を指定するが、コピー先の別のブックのシートでも、同じ座標にするかどうかを聞く機能。同じ形式のシートになっている場合は、非常に便利。
・コピー終了後、コピー元に戻るかの指定
・逆指定によるコピー。コピー先から指定し、次にコピー元を指定する。
※コピー機能は、非常によく使うものです。そこで、上記のように他のブックにコピーできるもののほかに、同一シート内のコピーのみに特化したマクロも持っておくと便利です。他のブックへコピー、その次は、同一シート内にコピー、次は他のブックという場合もありますので、指示情報を生かすためです。
次回は、印刷属性のコピーを取り上げます。もちろん他のブックのシートまでコピーは可能です。この機能も便利の一言です。
2009年6月7日日曜日
○第119回目 計算式をチェックする(その6)
○第119回目 計算式をチェックする(その6)
前回の宿題です。ｎ回目のチェックをしている時、チェック元のシート上の座標と計算式を格納する配列変数の添え字との関係は、というものでした。
まず記号の整理をします。
シートの有効範囲の最後は…gyo1とretu1とします。
チェック範囲の先頭は…g20とr20とします。
今チェックしているセルの座標は、i行、j列とします。
i行は、チェック範囲のたて位置を200で区切ったとき位置は次のようになります。
(Int((i - g20) / ll200) + 1)
先頭位置を20行と仮定すると、219行までは一番目のブロック、220行からは二番目のブロックとなります。
同様に、j列は
(Int((j - r20) / ll150) + 1)
となります。
ブロック内のi行の位置は、g20 と ブロック番号-1 掛ける 200 をiから引いたものに +1 したものになります。
i - g20 - [(Int((i - g20) / ll200) + 1) - 1]*200 + 1
i = 219では、219 - 20 - (0 + 1 - 1) * 200 + 1 = 200となります。
i = 220では、220 - 20 - (1 + 1 - 1) * 200 + 1 = 1となります。
このような変換式で、セルの座標が配列変数の添え字と結び付けられます。
(列も同様です)
チェックの回数は、i、jに依存しているので不要でした。
宿題はおしまい。
また、今の例でのチェックの範囲の先頭の行は、
ｘ- 20 - (0 + 1 - 1) * 200 + 1 = 1
ｘ- 20 - (1 + 1 - 1) * 200 + 1 = 1
を解けばいいことになります。それぞれｘは、20と220になります。
これから、チェック範囲の最後も出てきます。チェック範囲の最後はシートの有効範囲を超えませんので注意願います。
7.元のシートでの計算式の中のシート名をチェック先のシート名に置き換える
それぞれのシート名は、ww1()、ww2()にありますので、違っている場合のみ、ww1()をww2()に変換してください。
'シート名の変換。nはシート数の添え字。
For n = 1 To ii_max
If ww1(n) <> ww2(n) Then
Do While 1
p1 = InStr(mm(j1, i1), ww1(n))
If p1 = 0 Then Exit Do
mm(j1, i1) = Left$(mm(j1, i1), p1 - 1) & ww2(n) & Mid$(mm(j1, i1), p1 + Len(ww1(n)))
Loop
End If
Next n
8.チェック先のファイルの同番号のシートを指定して、チェック範囲内の計算式と、既に取得した計算式との同一チェック
計算式で違う箇所は大体はシート名なので、シート名を変換してやれば一致するはずです。変換は7.でおこなっているので同一性の比較は単純にやればいいことになります。
この段階で、チェックする範囲の色をチェック範囲の色に全部変えます。その上でチェックでエラーになった場合のみ、指定の色をつけるということにします。
有り有りで同一＝⇒何もしない
有り有りで違う＝⇒3 赤色
有り無し ＝⇒7 濃い桃色
無し有り ＝⇒40 肌色
If a <> "" And mm(j1, i1) <> "" Then
If a <> mm(j1, i1) Then Cells(i, j).Interior.ColorIndex = 3: err_cnt1(m) = err_cnt1(m) + 1 '赤色
If a = "" And mm(j1, i1) <> "" Then Cells(i, j).Interior.ColorIndex = 7: err_cnt2(m) = err_cnt2(m) + 1 '濃い桃色
If a <> "" And mm(j1, i1) = "" Then Cells(i, j).Interior.ColorIndex = 40: err_cnt3(m) = err_cnt3(m) + 1 '肌色
※ここでいう変数aは、チェック先のセルの計算式のことです。
err_cnt1(m)の添え字は、チェック回数のことです。
9.チェック単位でエラー件数を表示する
8.でエラーに該当したら、エラー件数をカウントしておきましょう。1回のチェックが終わったら、msgboxでエラー件数を表示しておきます。
エラー件数以外には、チェックしたシート名とチェック範囲を表示します。
10.チェック回数だけ繰り返す。チェック単位数の最大は100回とする(時間が分単位でかかる)
100回の場合かなり時間がかかります。とりあえずは、巨大な表が対象となる場合があるとするならば、20回ぐらいで試してみてはどうでしょうか。
11.次のシートへの処理に行き、繰り返す
※現在の問題点
現在チェックできるのは、ファイルが異なっても、パラメータは同じシートの同じ座標になっている場合です。
しかし、例えば男女別にファイルは分かれているが、パラメータは男女とも、例えば、男のファイルのあるシートに両方が入っている場合があります。この場合はパラメータの座標が男女で異なります。
これには対応できないのです。男のファイルには男のパラメータしか入っていなくて、女のパラメータと同じ座標だという場合しかできないということです。
この場合がことごとくエラーになってしまうのです。
使っている座標が違うのですからエラーとするのが当然ですが、よくみるとそのパラメータの箇所だけが違っていて、後はまったく同じなのです。つまり、正しい計算式なのです。このような場合は現時点ではお手上げですが、何とかならないのか、と思っています。
エラー箇所が少なければいいのですが、このエラーが2万セルもあると、手作業で確認していくのは不可能になってしまいます。
正当もどきの計算式をどうやって、本当のエラーから区分したらよいのか？
例えば、
1回目は無条件にチェックをかける。
→エラー原因を分析し、それが正当と判断できれば、変換表を作り、座標の列記号を変換して、再度チェックをかけるというのはどうかと思っています。
変換表のファイルをあけておき、そこから列記号の変換表を入手するという構想です。
当初考えていたものより、かなり複雑なものに発展してきてしまいました。
ファイル間の計算式同一性チェックは、当面は荷が重いということにしておきましょう。
前回の宿題です。ｎ回目のチェックをしている時、チェック元のシート上の座標と計算式を格納する配列変数の添え字との関係は、というものでした。
まず記号の整理をします。
シートの有効範囲の最後は…gyo1とretu1とします。
チェック範囲の先頭は…g20とr20とします。
今チェックしているセルの座標は、i行、j列とします。
i行は、チェック範囲のたて位置を200で区切ったとき位置は次のようになります。
(Int((i - g20) / ll200) + 1)
先頭位置を20行と仮定すると、219行までは一番目のブロック、220行からは二番目のブロックとなります。
同様に、j列は
(Int((j - r20) / ll150) + 1)
となります。
ブロック内のi行の位置は、g20 と ブロック番号-1 掛ける 200 をiから引いたものに +1 したものになります。
i - g20 - [(Int((i - g20) / ll200) + 1) - 1]*200 + 1
i = 219では、219 - 20 - (0 + 1 - 1) * 200 + 1 = 200となります。
i = 220では、220 - 20 - (1 + 1 - 1) * 200 + 1 = 1となります。
このような変換式で、セルの座標が配列変数の添え字と結び付けられます。
(列も同様です)
チェックの回数は、i、jに依存しているので不要でした。
宿題はおしまい。
また、今の例でのチェックの範囲の先頭の行は、
ｘ- 20 - (0 + 1 - 1) * 200 + 1 = 1
ｘ- 20 - (1 + 1 - 1) * 200 + 1 = 1
を解けばいいことになります。それぞれｘは、20と220になります。
これから、チェック範囲の最後も出てきます。チェック範囲の最後はシートの有効範囲を超えませんので注意願います。
7.元のシートでの計算式の中のシート名をチェック先のシート名に置き換える
それぞれのシート名は、ww1()、ww2()にありますので、違っている場合のみ、ww1()をww2()に変換してください。
'シート名の変換。nはシート数の添え字。
For n = 1 To ii_max
If ww1(n) <> ww2(n) Then
Do While 1
p1 = InStr(mm(j1, i1), ww1(n))
If p1 = 0 Then Exit Do
mm(j1, i1) = Left$(mm(j1, i1), p1 - 1) & ww2(n) & Mid$(mm(j1, i1), p1 + Len(ww1(n)))
Loop
End If
Next n
8.チェック先のファイルの同番号のシートを指定して、チェック範囲内の計算式と、既に取得した計算式との同一チェック
計算式で違う箇所は大体はシート名なので、シート名を変換してやれば一致するはずです。変換は7.でおこなっているので同一性の比較は単純にやればいいことになります。
この段階で、チェックする範囲の色をチェック範囲の色に全部変えます。その上でチェックでエラーになった場合のみ、指定の色をつけるということにします。
有り有りで同一＝⇒何もしない
有り有りで違う＝⇒3 赤色
有り無し ＝⇒7 濃い桃色
無し有り ＝⇒40 肌色
If a <> "" And mm(j1, i1) <> "" Then
If a <> mm(j1, i1) Then Cells(i, j).Interior.ColorIndex = 3: err_cnt1(m) = err_cnt1(m) + 1 '赤色
If a = "" And mm(j1, i1) <> "" Then Cells(i, j).Interior.ColorIndex = 7: err_cnt2(m) = err_cnt2(m) + 1 '濃い桃色
If a <> "" And mm(j1, i1) = "" Then Cells(i, j).Interior.ColorIndex = 40: err_cnt3(m) = err_cnt3(m) + 1 '肌色
※ここでいう変数aは、チェック先のセルの計算式のことです。
err_cnt1(m)の添え字は、チェック回数のことです。
9.チェック単位でエラー件数を表示する
8.でエラーに該当したら、エラー件数をカウントしておきましょう。1回のチェックが終わったら、msgboxでエラー件数を表示しておきます。
エラー件数以外には、チェックしたシート名とチェック範囲を表示します。
10.チェック回数だけ繰り返す。チェック単位数の最大は100回とする(時間が分単位でかかる)
100回の場合かなり時間がかかります。とりあえずは、巨大な表が対象となる場合があるとするならば、20回ぐらいで試してみてはどうでしょうか。
11.次のシートへの処理に行き、繰り返す
※現在の問題点
現在チェックできるのは、ファイルが異なっても、パラメータは同じシートの同じ座標になっている場合です。
しかし、例えば男女別にファイルは分かれているが、パラメータは男女とも、例えば、男のファイルのあるシートに両方が入っている場合があります。この場合はパラメータの座標が男女で異なります。
これには対応できないのです。男のファイルには男のパラメータしか入っていなくて、女のパラメータと同じ座標だという場合しかできないということです。
この場合がことごとくエラーになってしまうのです。
使っている座標が違うのですからエラーとするのが当然ですが、よくみるとそのパラメータの箇所だけが違っていて、後はまったく同じなのです。つまり、正しい計算式なのです。このような場合は現時点ではお手上げですが、何とかならないのか、と思っています。
エラー箇所が少なければいいのですが、このエラーが2万セルもあると、手作業で確認していくのは不可能になってしまいます。
正当もどきの計算式をどうやって、本当のエラーから区分したらよいのか？
例えば、
1回目は無条件にチェックをかける。
→エラー原因を分析し、それが正当と判断できれば、変換表を作り、座標の列記号を変換して、再度チェックをかけるというのはどうかと思っています。
変換表のファイルをあけておき、そこから列記号の変換表を入手するという構想です。
当初考えていたものより、かなり複雑なものに発展してきてしまいました。
ファイル間の計算式同一性チェックは、当面は荷が重いということにしておきましょう。
2009年5月31日日曜日
第118回目 計算式をチェックする(その5)
○第118回目 計算式をチェックする(その5)
ファイル等を超えた計算式の同一性チェック
一つの計算システムをシートに作っておき、それを必要な数だけコピーして使うとか、ファイルそのものをコピーして別のパラメータにかえて使うということがあります。
その場合、普通に考えると問題はないのですが、実際運用していくといろいろな問題が出てきます。その一つに修正があります。修正点があると、すべてのシートを同じように修正なければなりません。やっていくうちに、すべて修正したのかはっきりしなくなってしまうことがあります。また、ある一部分を修正し忘れているということもあります。それをチェックしようというものです。
計算システムのコピーには、三つのケースが考えられます。
1.ファイルそのものをコピーした
2.同一ファイルの別シートにコピーした
3.同一シートの別箇所にコピーした
これらはすべて応用でできますので、1.のファイル同士で計算式をチェックするを取り上げてみます。
前提；
1.ファイルをコピーしたので、シートの順番が同じになっている。
2.シート名は二つのファイルで違っている可能性がある。
3.計算システムは、ファイル外からのリンクが張られているものではない。
実行上の前提；
1.チェック元、チェック先の両方のファイルは開かれている
2.チェック元のチェックしたいシートにチェック先のファイル名が記されており、その場所をカーソルで指定した状態から実行する。
チェックの機能；
1.該当するシートから先の複数のシートを一気に処理できる
2.シートのおおよその範囲内(ボーリング調査で自動的に判断する)の計算式をチェックする
3.チェック範囲の先頭の位置は指定する
4.1回のチェックでは、最大で150列、200行の範囲をチェックする。チェックすべき範囲がそれより大きい場合は、複数回のチェックとなる。自動的に複数回チェックの処理をする。
5.チェックの前に、計算式上のシート名を変換しておく。変換表を作っておく。
6.エラーの種類によって色を変える。また、チェックした範囲に色をつける
計算式が、有り有りで違う＝⇒3 赤色
計算式が、有り無し ＝⇒7 濃い桃色
計算式が、無し有り ＝⇒40 肌色
チェック範囲 ＝⇒36 アイボリ
7.エラーの種類ごとに件数を表示する。これは一チェック単位ごととなる
【流れ】
0.相手のファイル名のあるセルを指定してから実行
1.自分のファイル名とシート名の取得。現在のシートの番号を取得
2.相手のシート名の取得(1で取得したシート番号と同じという前提)
3.チェックするシート数の入力
4.チェックの範囲のボーリング調査により自動取得。
5.チェックする範囲の先頭位置の指定
6.元のシートでの計算式を取得
7.元のシートでの計算式の中のシート名をチェック先のシート名に置き換える(変換表のある範囲を指定)
8.チェック先のファイルにおける同一番号のシートを指定して、チェック範囲内の計算式と、既に取得した計算式との同一チェック
計算式が、有り有りで違う＝⇒3 赤色
計算式が、有り無し ＝⇒7 濃い桃色
計算式が、無し有り ＝⇒40 肌色
チェック範囲内に色を付ける＝⇒36 アイボリ
9.チェック単位でエラー件数を表示する
10.チェック単位の回数だけ繰り返す。チェック単位数の最大は100回とする(時間が分単位でかかる)
11.次のシートへの処理にいき、繰り返す
思ったよりは長丁場になります。
1.自分のファイル名とシート名の取得。現在のシートの番号を取得
'ファイル名等、現在表示のブックの基本情報取得
F_name1 = ActiveWorkbook.Name
sh_name1 = ActiveSheet.Name
ii_max = Worksheets.Count
f_name2 = Selection.Value 'チェック先のファイル名の取得
'シート名(ワークシート)の取得、全部
For i = 1 To ii_max
ww1(i) = Worksheets(i).Name
Next i
'現在のシート名番号の把握
For i = 1 To ii_max
If ww1(i) = sh_name1 Then ii0 = i: Exit For
Next i
早くもここで問題が起きました。この部分だけでは正しいのですが、テストをしたときに、問題が生じました。シート名がマッチせず、計算式中のシート名の変換ができなかったのです。
原因を調べたところ(原因がわかるまで2時間程度かかってしまいました)、取得したシート名の中の括弧が全角になっていたのでした(実際のシート名は半角の括弧です)。
計算式中のシート名では、括弧は半角です。これではマッチしません。
'シート名の（）を()に変換する。
rep_moji1(1) = "（"
rep_moji1(2) = "）"
rep_moji2(1) = "("
rep_moji2(1) = ")"
For i = 1 To 2
For n = 1 To ii_max
Do While 1
p1 = InStr(ww1(n), rep_moji1(i))
If p1 = 0 Then Exit Do
ww1(n) = Left$(ww1(n), p1 - 1) & rep_moji2(1) & Mid$(ww1(n), p1 + 1)
Loop
Next n
Next
※2.で取得するチェック先のシート名においても同様の処理をお忘れずに。
2.相手のシート名の取得
Workbooks(f_name2).Activate
'シート名(ワークシート)の取得、全部
For i = 1 To ii_max
ww2(i) = Worksheets(i).Name
Next
※以下省略。
3.チェックするシート数の入力
ck_sh_cnt = InputBox("チェックするシート数を入れてください。例えば ", , 1)
以下は、シート単位での繰り返しになります。
4.チェックの範囲の検索。ボーリング調査
元のシートに戻って、シートの有効範囲のボーリング調査をします。前回参照。
5.チェックする範囲の先頭位置の指定
4.で求めた有効範囲に一時的に色をつけ、有効範囲を明示した上で、チェックの先頭位置を指定します。指定後、色は消しておきます。
Dim セル範囲 As Range
Set セル範囲 = Application.InputBox(Prompt:="チェックする先頭のセルの座標を入れてください。", Type:=8)
6.元のシートでの計算式を取得
前処理
パラメータはすべて取得が終わったので、そのパラメータをもとに、チェック範囲、チェック回数を求めます。
チェック元シートの計算式は、チェック単位でその都度配列変数に格納します。
チェック範囲の先頭と最後の座標が与えられているので、たてに何回、横に何回チェックをしなければならないのかを求めます。これを掛けたものが、チェック回数となります。
たての回数…チェックの行数-1 割る 200の商 +1 となります。
よこの回数…チェックの列数-1 割る 150の商 +1 となります。
チェックは上から下、終わったら、横へという流れでおこないます。
配列変数は二次元の配列変数、mm(j, i)とします。最初の添え字は列を表し、次の添え字は行をあらわしています。Cells()とは、逆ですので注意願います。
さて、問題です。シート内でｎ回目のチェックをしています。チェック元のシート上の座標はわかります。では、そのセルの計算式を格納する配列変数の添え字は行列それぞれいくつでしょうか。
配列変数では添え字1から有効とします。0ではありません。
ファイル等を超えた計算式の同一性チェック
一つの計算システムをシートに作っておき、それを必要な数だけコピーして使うとか、ファイルそのものをコピーして別のパラメータにかえて使うということがあります。
その場合、普通に考えると問題はないのですが、実際運用していくといろいろな問題が出てきます。その一つに修正があります。修正点があると、すべてのシートを同じように修正なければなりません。やっていくうちに、すべて修正したのかはっきりしなくなってしまうことがあります。また、ある一部分を修正し忘れているということもあります。それをチェックしようというものです。
計算システムのコピーには、三つのケースが考えられます。
1.ファイルそのものをコピーした
2.同一ファイルの別シートにコピーした
3.同一シートの別箇所にコピーした
これらはすべて応用でできますので、1.のファイル同士で計算式をチェックするを取り上げてみます。
前提；
1.ファイルをコピーしたので、シートの順番が同じになっている。
2.シート名は二つのファイルで違っている可能性がある。
3.計算システムは、ファイル外からのリンクが張られているものではない。
実行上の前提；
1.チェック元、チェック先の両方のファイルは開かれている
2.チェック元のチェックしたいシートにチェック先のファイル名が記されており、その場所をカーソルで指定した状態から実行する。
チェックの機能；
1.該当するシートから先の複数のシートを一気に処理できる
2.シートのおおよその範囲内(ボーリング調査で自動的に判断する)の計算式をチェックする
3.チェック範囲の先頭の位置は指定する
4.1回のチェックでは、最大で150列、200行の範囲をチェックする。チェックすべき範囲がそれより大きい場合は、複数回のチェックとなる。自動的に複数回チェックの処理をする。
5.チェックの前に、計算式上のシート名を変換しておく。変換表を作っておく。
6.エラーの種類によって色を変える。また、チェックした範囲に色をつける
計算式が、有り有りで違う＝⇒3 赤色
計算式が、有り無し ＝⇒7 濃い桃色
計算式が、無し有り ＝⇒40 肌色
チェック範囲 ＝⇒36 アイボリ
7.エラーの種類ごとに件数を表示する。これは一チェック単位ごととなる
【流れ】
0.相手のファイル名のあるセルを指定してから実行
1.自分のファイル名とシート名の取得。現在のシートの番号を取得
2.相手のシート名の取得(1で取得したシート番号と同じという前提)
3.チェックするシート数の入力
4.チェックの範囲のボーリング調査により自動取得。
5.チェックする範囲の先頭位置の指定
6.元のシートでの計算式を取得
7.元のシートでの計算式の中のシート名をチェック先のシート名に置き換える(変換表のある範囲を指定)
8.チェック先のファイルにおける同一番号のシートを指定して、チェック範囲内の計算式と、既に取得した計算式との同一チェック
計算式が、有り有りで違う＝⇒3 赤色
計算式が、有り無し ＝⇒7 濃い桃色
計算式が、無し有り ＝⇒40 肌色
チェック範囲内に色を付ける＝⇒36 アイボリ
9.チェック単位でエラー件数を表示する
10.チェック単位の回数だけ繰り返す。チェック単位数の最大は100回とする(時間が分単位でかかる)
11.次のシートへの処理にいき、繰り返す
思ったよりは長丁場になります。
1.自分のファイル名とシート名の取得。現在のシートの番号を取得
'ファイル名等、現在表示のブックの基本情報取得
F_name1 = ActiveWorkbook.Name
sh_name1 = ActiveSheet.Name
ii_max = Worksheets.Count
f_name2 = Selection.Value 'チェック先のファイル名の取得
'シート名(ワークシート)の取得、全部
For i = 1 To ii_max
ww1(i) = Worksheets(i).Name
Next i
'現在のシート名番号の把握
For i = 1 To ii_max
If ww1(i) = sh_name1 Then ii0 = i: Exit For
Next i
早くもここで問題が起きました。この部分だけでは正しいのですが、テストをしたときに、問題が生じました。シート名がマッチせず、計算式中のシート名の変換ができなかったのです。
原因を調べたところ(原因がわかるまで2時間程度かかってしまいました)、取得したシート名の中の括弧が全角になっていたのでした(実際のシート名は半角の括弧です)。
計算式中のシート名では、括弧は半角です。これではマッチしません。
'シート名の（）を()に変換する。
rep_moji1(1) = "（"
rep_moji1(2) = "）"
rep_moji2(1) = "("
rep_moji2(1) = ")"
For i = 1 To 2
For n = 1 To ii_max
Do While 1
p1 = InStr(ww1(n), rep_moji1(i))
If p1 = 0 Then Exit Do
ww1(n) = Left$(ww1(n), p1 - 1) & rep_moji2(1) & Mid$(ww1(n), p1 + 1)
Loop
Next n
Next
※2.で取得するチェック先のシート名においても同様の処理をお忘れずに。
2.相手のシート名の取得
Workbooks(f_name2).Activate
'シート名(ワークシート)の取得、全部
For i = 1 To ii_max
ww2(i) = Worksheets(i).Name
Next
※以下省略。
3.チェックするシート数の入力
ck_sh_cnt = InputBox("チェックするシート数を入れてください。例えば ", , 1)
以下は、シート単位での繰り返しになります。
4.チェックの範囲の検索。ボーリング調査
元のシートに戻って、シートの有効範囲のボーリング調査をします。前回参照。
5.チェックする範囲の先頭位置の指定
4.で求めた有効範囲に一時的に色をつけ、有効範囲を明示した上で、チェックの先頭位置を指定します。指定後、色は消しておきます。
Dim セル範囲 As Range
Set セル範囲 = Application.InputBox(Prompt:="チェックする先頭のセルの座標を入れてください。", Type:=8)
6.元のシートでの計算式を取得
前処理
パラメータはすべて取得が終わったので、そのパラメータをもとに、チェック範囲、チェック回数を求めます。
チェック元シートの計算式は、チェック単位でその都度配列変数に格納します。
チェック範囲の先頭と最後の座標が与えられているので、たてに何回、横に何回チェックをしなければならないのかを求めます。これを掛けたものが、チェック回数となります。
たての回数…チェックの行数-1 割る 200の商 +1 となります。
よこの回数…チェックの列数-1 割る 150の商 +1 となります。
チェックは上から下、終わったら、横へという流れでおこないます。
配列変数は二次元の配列変数、mm(j, i)とします。最初の添え字は列を表し、次の添え字は行をあらわしています。Cells()とは、逆ですので注意願います。
さて、問題です。シート内でｎ回目のチェックをしています。チェック元のシート上の座標はわかります。では、そのセルの計算式を格納する配列変数の添え字は行列それぞれいくつでしょうか。
配列変数では添え字1から有効とします。0ではありません。
2009年5月5日火曜日
第117回目 計算式をチェックする(その4)
○第117回目 計算式をチェックする(その4)
今回は力を抜いて、前回で求めたエラー箇所のカウントと、その場所への移動を考えてみます。
ある場所への移動は、既に計算式中の座標に移動でまとめましたので、計算式中の座標を求める代わりに、エラーの場所の座標を取得することで可能となります。
しかしながら、エラーを対象とするので、エラー件数の目途がたちません。一定の件数以上になった場合おしまい、とするやり方もありますが、ここではさらに簡単に、検索範囲の一番最初のエラーを見つけたらおしまいとします。なお、エラー件数は全範囲で調べます。したがって、移動したいエラーの色を指定することにします(移動先のエラーとエラー件数カウントでの色の範囲がちぐはぐとなりますが、あまり気にしないでください。エラーの座標の蓄積はあまり意味がないような気がします。実際、計算式チェックをやってみますと、エラーの件数が多くてしょうがないからです)。
チェック範囲内のエラー箇所－これは色で判別する－の数をカウントします。
範囲内のセルを一つずつ調べていき、セルの色が該当する色かどうかで件数をカウントします。
エラーの内容は、
46⇒赤色＝計算式を構成する項目数が異なる
27⇒黄色＝座標の偏差が基準値と違う
44⇒オレンジ色＝チェック1(ニ行目による)
43⇒きみどり色＝チェック1(一行目による)
これまでのチェックは、調べる範囲を指定していましたが、ここでは使い勝手がいいように、自動的にそのシートのおおよその有効範囲を調べます。それをまずはチェック範囲とします。そのうちの一部も可能とします。すなわち、カーソルで指定する位置を先頭として、最後は有効範囲の端とするものです。
そのシートのおおよその有効範囲とはどうやって求めるのか…。
先頭の位置をカーソルで指定し、行数と列数を与え、範囲を求める。これでは範囲を指定しているのと同じです。先頭のセルを指定するのは必要ですが、それだけで何とか求められないか…。
おおよそですので、いい加減な方法をとります。
ボーリング調査です。
行を例として取り上げて見ましょう。
列を6個ほど決めて、その列の最終有効セルを求めます。求められた6つのセルの行数の一番大きいところを行の有効範囲とします。
全部おこなえば正確な最大有効行数がでますが、それまでやる必要はないのではということです。
同様に最大有効列は、行を適当に6つほど取り、その中で一番大きな列数を求めればいいのです。
ある列の行の一番の端はどうやって求めるのでしょうか。上から移動させていったのでは、埒があきません。一番下から上に向かって調べるのです。同様に列は、一番右端の列から左に向かって調べるのです。
Range("h65536").Select
Selection.End(xlUp).Select '表の上端へ
その場所の行数は、 Selection.Row です。
Range("iv50").Select
Selection.End(xlToLeft).Select '表の左端へ
その場所の列数は Selection.Column です。
このボーリング調査でこのシートのおおよその端がわかります。先頭位置はカーソルで与えますので、これでチェックの範囲は決まります。
このマクロはかなり簡単だと思われたのですが…。
該当する色の場所に止まりません。素通りで終了してしまうのです。
違う色コードを入力しても止まりません。
はてどうしたのでしょうかと、頭を抱えました。
そこであることに気がつきました。それは数字に見えるが文字である場合があるということです。
この場合もそれでした。数字として入れたつもりなのですが、それが文字列となっていて、それを数値変換してくれるのが普通なのですが、ある場合は、その変換がなされずに、文字列のまま認識されてしまい、入力した色コードに一致したセルがないということになったのです。入力値をVALで数値化したら、うまくいきました。
盲点です。お気を付けください。
もう一つの失敗は、列と行の二重のfor文であるので、該当するセルを見つけた場合、二重のfor文を抜け出さなければならないことでしょう。一回だけでは該当する場所が最後の列の最初の行の場所になってしまいます。
今回のマクロは、マクロ的には紹介すべきものがありませんが、おおよそのシートの有効範囲を探す、というのが面白かったかもしれません。
今回は力を抜いて、前回で求めたエラー箇所のカウントと、その場所への移動を考えてみます。
ある場所への移動は、既に計算式中の座標に移動でまとめましたので、計算式中の座標を求める代わりに、エラーの場所の座標を取得することで可能となります。
しかしながら、エラーを対象とするので、エラー件数の目途がたちません。一定の件数以上になった場合おしまい、とするやり方もありますが、ここではさらに簡単に、検索範囲の一番最初のエラーを見つけたらおしまいとします。なお、エラー件数は全範囲で調べます。したがって、移動したいエラーの色を指定することにします(移動先のエラーとエラー件数カウントでの色の範囲がちぐはぐとなりますが、あまり気にしないでください。エラーの座標の蓄積はあまり意味がないような気がします。実際、計算式チェックをやってみますと、エラーの件数が多くてしょうがないからです)。
チェック範囲内のエラー箇所－これは色で判別する－の数をカウントします。
範囲内のセルを一つずつ調べていき、セルの色が該当する色かどうかで件数をカウントします。
エラーの内容は、
46⇒赤色＝計算式を構成する項目数が異なる
27⇒黄色＝座標の偏差が基準値と違う
44⇒オレンジ色＝チェック1(ニ行目による)
43⇒きみどり色＝チェック1(一行目による)
これまでのチェックは、調べる範囲を指定していましたが、ここでは使い勝手がいいように、自動的にそのシートのおおよその有効範囲を調べます。それをまずはチェック範囲とします。そのうちの一部も可能とします。すなわち、カーソルで指定する位置を先頭として、最後は有効範囲の端とするものです。
そのシートのおおよその有効範囲とはどうやって求めるのか…。
先頭の位置をカーソルで指定し、行数と列数を与え、範囲を求める。これでは範囲を指定しているのと同じです。先頭のセルを指定するのは必要ですが、それだけで何とか求められないか…。
おおよそですので、いい加減な方法をとります。
ボーリング調査です。
行を例として取り上げて見ましょう。
列を6個ほど決めて、その列の最終有効セルを求めます。求められた6つのセルの行数の一番大きいところを行の有効範囲とします。
全部おこなえば正確な最大有効行数がでますが、それまでやる必要はないのではということです。
同様に最大有効列は、行を適当に6つほど取り、その中で一番大きな列数を求めればいいのです。
ある列の行の一番の端はどうやって求めるのでしょうか。上から移動させていったのでは、埒があきません。一番下から上に向かって調べるのです。同様に列は、一番右端の列から左に向かって調べるのです。
Range("h65536").Select
Selection.End(xlUp).Select '表の上端へ
その場所の行数は、 Selection.Row です。
Range("iv50").Select
Selection.End(xlToLeft).Select '表の左端へ
その場所の列数は Selection.Column です。
このボーリング調査でこのシートのおおよその端がわかります。先頭位置はカーソルで与えますので、これでチェックの範囲は決まります。
このマクロはかなり簡単だと思われたのですが…。
該当する色の場所に止まりません。素通りで終了してしまうのです。
違う色コードを入力しても止まりません。
はてどうしたのでしょうかと、頭を抱えました。
そこであることに気がつきました。それは数字に見えるが文字である場合があるということです。
この場合もそれでした。数字として入れたつもりなのですが、それが文字列となっていて、それを数値変換してくれるのが普通なのですが、ある場合は、その変換がなされずに、文字列のまま認識されてしまい、入力した色コードに一致したセルがないということになったのです。入力値をVALで数値化したら、うまくいきました。
盲点です。お気を付けください。
もう一つの失敗は、列と行の二重のfor文であるので、該当するセルを見つけた場合、二重のfor文を抜け出さなければならないことでしょう。一回だけでは該当する場所が最後の列の最初の行の場所になってしまいます。
今回のマクロは、マクロ的には紹介すべきものがありませんが、おおよそのシートの有効範囲を探す、というのが面白かったかもしれません。
2009年4月26日日曜日
第116回目 計算式をチェックする(その3)
○第116回目 計算式をチェックする(その3)
3.からです。
3.ブロックの中央あたりのセルを選び出し、その後ろの行との列の偏差、行の偏差、定数の偏差(通常は0)を求めます。チェックの基準値とします。
'基準位置(中間地点)において偏差1(列),2(行)を取得します。
ii0 = Int((gyo_cnt + 1) / 2)
For m = 1 To mm1(ii0, 0) 'mm1(**, 0)には項目数が入っています。
hensa1(m) = mm1(ii0 + 1, m) - mm1(ii0, m)
hensa2(m) = mm2(ii0 + 1, m) - mm2(ii0, m)
Next m
4.先頭の次のセルからチェックです。計算式の入っていないセルはチェックしません。計算式なしのセルは最後に色付けをします。
'計算式のチェック。一つでも違いがあれば色をつけます。
For ii = 2 To gyo_cnt '一番最初のセルは、前のセルがないのでチェックできません。
For m = 1 To mm1(ii0, 0) '基準となるセルの項目数までチェックします。
If mm_sh(ii, m) <> mm_sh(ii - 1, m) Then flag = 99: Exit For
If mm1(ii, m) - mm1(ii - 1, m) <> hensa1(m) Then flag = 99: Exit For
If mm2(ii, m) - mm2(ii - 1, m) <> hensa2(m) Then flag = 99: Exit For
Next
'色コード⇒色 20⇒水色、27⇒黄色、46⇒赤色、44⇒オレンジ色
If flag = 99 Then '偏差が異なる
Cells(gyo1 + ii - 1, retu1 + jj - 1).Interior.ColorIndex = 27
flag = 0
End If
If mm1(ii, 0) <> mm1(ii0, 0) Then '項目数が異なる
Cells(gyo1 + ii - 1, retu1 + jj - 1).Interior.ColorIndex = 46
End If
'ここまではある一列の中での処理。終了後、次の列の処理に移ります。
必要な配列変数を初期化して、次の列に処理を移します。
'全部終了後、計算式のないセルに色をつけます。
For j = 1 To retu_cnt
For i = 1 To gyo_cnt
a = Cells(gyo1 + i - 1, retu1 + j - 1).Formula
If InStr(a, "=") = 0 Then
Cells(gyo1 + i - 1, retu1 + j - 1).Interior.ColorIndex = 20
End If
Next i
Next j
7.全部終了したら、ダンプリストを取るか取らないかの指定をします。出力場所はこの時、指定します。配列変数は、当然最後の列の状態しかありません。ダンプリストは、問題があるところに限定し、再度処理を走らせてから、取ってください。
'デバック用内部変数のダンプ出力
Dim セル範囲 As Range
db_flag = InputBox("デバック用ダンプリストを出力しますか。最後の列だけになります。" _
, Default:=9, xpos:=2000, ypos:=3000)
If db_flag = 1 Then
Set セル範囲 = Application.InputBox _
(Prompt:="出力先のセルを選択してください", Type:=8)
AAA = セル範囲.Address
retu21 = Range(AAA).Column
gyo21 = Range(AAA).Row
For i = 1 To gyo_cnt 'チェックした計算式の数は、行数となります。
Cells(gyo21 + (i - 1) * 3, retu21) = mm1(i, 0) '項目数
For m = 1 To mm1(i, 0)
Cells(gyo21 + (i - 1) * 3, retu21 + m) = mm_sh(i, m) 'シート名
Cells(gyo21 + (i - 1) * 3 + 1, retu21 + m) = mm1(i, m) '列の偏差
Cells(gyo21 + (i - 1) * 3 + 2, retu21 + m) = mm2(i, m) '行の偏差
Next
Next
End If
※この変数のダンプをとる処理は使い回しがきくかもしれません。整理して活用度の高いものにしておきましょう。
※チェックマクロは、1.数値を除いてのチェック、2.列偏差、行偏差の同一性のチェックを紹介しましたが、どちらもそれだけでは不十分なところがありますので、この二つを合体させる必要があります。単純につなげればできますとはいえませんが、うまく重複するところと、順番を調整して合体させるといいと思います。
※次回はちょっと力を抜いて、エラー箇所のカウントと、その場所への移動を考えて見ます。
3.からです。
3.ブロックの中央あたりのセルを選び出し、その後ろの行との列の偏差、行の偏差、定数の偏差(通常は0)を求めます。チェックの基準値とします。
'基準位置(中間地点)において偏差1(列),2(行)を取得します。
ii0 = Int((gyo_cnt + 1) / 2)
For m = 1 To mm1(ii0, 0) 'mm1(**, 0)には項目数が入っています。
hensa1(m) = mm1(ii0 + 1, m) - mm1(ii0, m)
hensa2(m) = mm2(ii0 + 1, m) - mm2(ii0, m)
Next m
4.先頭の次のセルからチェックです。計算式の入っていないセルはチェックしません。計算式なしのセルは最後に色付けをします。
'計算式のチェック。一つでも違いがあれば色をつけます。
For ii = 2 To gyo_cnt '一番最初のセルは、前のセルがないのでチェックできません。
For m = 1 To mm1(ii0, 0) '基準となるセルの項目数までチェックします。
If mm_sh(ii, m) <> mm_sh(ii - 1, m) Then flag = 99: Exit For
If mm1(ii, m) - mm1(ii - 1, m) <> hensa1(m) Then flag = 99: Exit For
If mm2(ii, m) - mm2(ii - 1, m) <> hensa2(m) Then flag = 99: Exit For
Next
'色コード⇒色 20⇒水色、27⇒黄色、46⇒赤色、44⇒オレンジ色
If flag = 99 Then '偏差が異なる
Cells(gyo1 + ii - 1, retu1 + jj - 1).Interior.ColorIndex = 27
flag = 0
End If
If mm1(ii, 0) <> mm1(ii0, 0) Then '項目数が異なる
Cells(gyo1 + ii - 1, retu1 + jj - 1).Interior.ColorIndex = 46
End If
'ここまではある一列の中での処理。終了後、次の列の処理に移ります。
必要な配列変数を初期化して、次の列に処理を移します。
'全部終了後、計算式のないセルに色をつけます。
For j = 1 To retu_cnt
For i = 1 To gyo_cnt
a = Cells(gyo1 + i - 1, retu1 + j - 1).Formula
If InStr(a, "=") = 0 Then
Cells(gyo1 + i - 1, retu1 + j - 1).Interior.ColorIndex = 20
End If
Next i
Next j
7.全部終了したら、ダンプリストを取るか取らないかの指定をします。出力場所はこの時、指定します。配列変数は、当然最後の列の状態しかありません。ダンプリストは、問題があるところに限定し、再度処理を走らせてから、取ってください。
'デバック用内部変数のダンプ出力
Dim セル範囲 As Range
db_flag = InputBox("デバック用ダンプリストを出力しますか。最後の列だけになります。" _
, Default:=9, xpos:=2000, ypos:=3000)
If db_flag = 1 Then
Set セル範囲 = Application.InputBox _
(Prompt:="出力先のセルを選択してください", Type:=8)
AAA = セル範囲.Address
retu21 = Range(AAA).Column
gyo21 = Range(AAA).Row
For i = 1 To gyo_cnt 'チェックした計算式の数は、行数となります。
Cells(gyo21 + (i - 1) * 3, retu21) = mm1(i, 0) '項目数
For m = 1 To mm1(i, 0)
Cells(gyo21 + (i - 1) * 3, retu21 + m) = mm_sh(i, m) 'シート名
Cells(gyo21 + (i - 1) * 3 + 1, retu21 + m) = mm1(i, m) '列の偏差
Cells(gyo21 + (i - 1) * 3 + 2, retu21 + m) = mm2(i, m) '行の偏差
Next
Next
End If
※この変数のダンプをとる処理は使い回しがきくかもしれません。整理して活用度の高いものにしておきましょう。
※チェックマクロは、1.数値を除いてのチェック、2.列偏差、行偏差の同一性のチェックを紹介しましたが、どちらもそれだけでは不十分なところがありますので、この二つを合体させる必要があります。単純につなげればできますとはいえませんが、うまく重複するところと、順番を調整して合体させるといいと思います。
※次回はちょっと力を抜いて、エラー箇所のカウントと、その場所への移動を考えて見ます。
2009年4月19日日曜日
第115回目 計算式をチェックする(その2)
○第115回目 計算式をチェックする(その2)
3.座標の偏位が途中からずれているぞ、というテーマです。
まずは、どういうことをチェックするか(どのような機能を持たせるか)などを整理しておきます。
計算式から座標(パス名、シート名を含む)及び定数を抜き出し、それを比較する。座標の列記号は列番号に変換します。上下の形式で、座標の行列番号に規則性があるかどうかをみます。また、パス名・シート名、定数は同じものが使われているかどうかみます。
規則性の基準は一列ブロックのほぼ中央のセルとその下のセルの間において、列番号の偏差、行番号の偏差、定数などの差を求め、それを偏差の基準値とします。
以下の順で色設定の優先度が強くなるとしました。
・計算式が入っていないセル
・比較項目の数が上下で異なっているセル。
・偏差(下の計算式から算出)が、基準となる偏差と違うセル(パス名・シート名が異なるものも含む)。
重複している座標はそのまま整理せず使用します。
関数、演算子等は、削除しているのでチェックの対象外となります。
(関数については、前回のチェックマクロで十分だと思います)
デバックのため変数値のダンプリストの出力の有無を入れます。出力場所は中で指定します。
流れです。
1.(チェックしたい範囲が指定された状態からはじめます)指定された範囲の列番号、行番号、列数、行数を取得します。
2.(一列単位で処理します)計算式を一つずつ取得して、不要なもの(関数、四則演算子など)を削除して、座標などの必要な項目をカンマで区切ります。残るのは、パス名･シート名、座標、定数です。パス名･シート名は、それ用の配列変数に入れます。座標は、列番号と行番号を求めてそれ用の配列変数に入れます。これも別々の配列変数に入れます。定数は列番号を入れる配列変数にのみに入れます。
これを一列の中のすべてのセルについては行います。
3.ブロックの中央あたりのセルを選び出し、その前の行との列の偏差、行の偏差、定数の偏差(通常は0でしょうね)を求めます。チェックの基準値となります。
4.先頭の次のセルからチェックです。計算式の入っていないセルはチェックしません。計算式なしのセルは最後に色付けをします。
パス名･シート名は等しいことが正当条件です。
列番号、行番号、定数は、前のセルとの偏差が基準値と等しいことが正当条件です。
ここで問題が起きました。チェックする項目数が異なる場合があることです。したがって、項目数が基準のセルと異なる場合もエラーにします。
とりあえず、列番号、行番号、定数用の配列変数は0で初期化しているので、項目数が異なる場合でも、チェックは基準のセルが持っている項目数分だけ行いますので、チェックしてもエラーになりません。
5.色をつける順番は、偏差等が異なる、チェック項目数が異なる、計算式がないとなります。
6.一ブロック分が終了したので、必要な配列変数を初期化して、次の列に処理を移します。
7.全部終了したら、ダンプリストを取るかとらないかの指定をします。出力場所はこの時、指定します。配列変数は、当然最後の列の状態しかありません。ダンプリストは、問題があるところに限定し処理を走らせてから、取ってください。
具体的にしていきます。(1.は定番なので省略します)
まずは配列の変数の紹介です。
Dim mm_sh(100, 200)…パス名･シート名用(添え字の最初は行をあらわし、次はその計算式の中の項目を表す。以下同じ)
Dim mm1(100, 200)…列番号･定数用
Dim mm2(100, 200)…行番号用
Dim hensa1(100)…列番号･定数用の偏差
Dim hensa2(100)…行番号用の偏差
2.計算式を一つずつ取得して、座標などの必要な項目を取得する。
これも前とかでやったものです。
まずは、パス名にあるコロンを変えます。C:とかのコロンです。ここでは、C:、D:、F:のコロンをC_、D_、F_にします。
'計算式の取得
a = "***" & Cells(gyo1 + ii - 1, retu1 + jj - 1).Formula
If InStr(a, "=") = 0 Then a = "" '計算式でない場合はクリアする
b = a
'ドライブ名のつなぎの置き換え。 :を _へ
del_mojis(1) = "C:"
del_mojis(2) = "D:"
del_mojis(3) = "F:"
For i = 1 To 3
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) & Left(del_mojis(i), 1) & "_" & Mid$(b, p1 + Len(del_mojis(i)))
Loop
End If
Next
'関数名の削除(省略)
"ROUND"、"LOOKUP"、"IF"、"INT"、"SIGN"、"SUMPRODUCT"、"INDIRECT"
"ADDRESS"、"MAX"、 "MIN"、 "SUM"
を削除します。
'特殊文字の置き換え(省略)
"("、")"、""""、"<"、">"、"="、"*"、"/"、"-"、"+"
":"、"^"、",,,,,"、",,,,"、",,,"、",,"
をカンマ一つに置き換えます。
'カンマで区切られた項目の取得
Do loop文でカンマのある位置を元に項目を分解して配列変数に入れます。最後の一個に気をつけてください(文字列中にカンマがない場合は、残っているのが最後の項目になります。これを取得するのに注意してください)。
これはDo loop文の外で取得します。以下の文では省略しています。また、取得した項目数を求めておいてください。
Do While 1
p1 = InStr(b, ",")
If p1 = 0 Then Exit Do
If Left$(b, p1 - 1) <> "" Then
p_xy_A1(i) = Left$(b, p1 - 1)
i = i + 1
End If
b = Mid$(b, p1 + 1)
Loop
'パス名･シート名と座標等の分離。座標でシート名がない場合は、当該シート名
パス名･シート名があるかないかは、"!"があるかないかで調べます。
定数の場合は、シート名はヌルにしています。
For i = 0 To p_xy_no 'これは項目数(0からカウント)
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)
ElseIf IsNumeric(p_xy_A1(i)) = False Then
p_sh_name(i) = ActiveSheet.Name
Else
p_sh_name(i) = ""
End If
Next
'シート名、列番号、行番号を格納する。mm1(ii, 0)は項目の個数(1からのカウント)です。
今まで取得したものと、この時にこれを入れるmm1(ii, 0)とは、一つずれているので注意してください。
座標の場合、座標から列番号と、行番号を求めます。
mm1(ii, 0) = p_xy_no + 1 'iiは行の値です。
For k = 1 To p_xy_no + 1
mm_sh(ii, k) = p_sh_name(k - 1)
If p_xy_A1(k - 1) <> "" And IsNumeric(p_xy_A1(k - 1)) = False Then
mm1(ii, k) = Range(p_xy_A1(k - 1)).Column
mm2(ii, k) = Range(p_xy_A1(k - 1)).Row
ElseIf IsNumeric(p_xy_A1(k - 1)) = True Then 'これは定数の場合です。
mm1(ii, k) = p_xy_A1(k - 1)
mm2(ii, k) = 0
Else
mm1(ii, k) = 0
mm2(ii, k) = 0
End If
Next k
※これでやっと一つのセルの計算式の分解が終わりましたので、次のセルに処理を移します。
Next ii
※長くなりましたので、今回はこれでおしまい。
3.座標の偏位が途中からずれているぞ、というテーマです。
まずは、どういうことをチェックするか(どのような機能を持たせるか)などを整理しておきます。
計算式から座標(パス名、シート名を含む)及び定数を抜き出し、それを比較する。座標の列記号は列番号に変換します。上下の形式で、座標の行列番号に規則性があるかどうかをみます。また、パス名・シート名、定数は同じものが使われているかどうかみます。
規則性の基準は一列ブロックのほぼ中央のセルとその下のセルの間において、列番号の偏差、行番号の偏差、定数などの差を求め、それを偏差の基準値とします。
以下の順で色設定の優先度が強くなるとしました。
・計算式が入っていないセル
・比較項目の数が上下で異なっているセル。
・偏差(下の計算式から算出)が、基準となる偏差と違うセル(パス名・シート名が異なるものも含む)。
重複している座標はそのまま整理せず使用します。
関数、演算子等は、削除しているのでチェックの対象外となります。
(関数については、前回のチェックマクロで十分だと思います)
デバックのため変数値のダンプリストの出力の有無を入れます。出力場所は中で指定します。
流れです。
1.(チェックしたい範囲が指定された状態からはじめます)指定された範囲の列番号、行番号、列数、行数を取得します。
2.(一列単位で処理します)計算式を一つずつ取得して、不要なもの(関数、四則演算子など)を削除して、座標などの必要な項目をカンマで区切ります。残るのは、パス名･シート名、座標、定数です。パス名･シート名は、それ用の配列変数に入れます。座標は、列番号と行番号を求めてそれ用の配列変数に入れます。これも別々の配列変数に入れます。定数は列番号を入れる配列変数にのみに入れます。
これを一列の中のすべてのセルについては行います。
3.ブロックの中央あたりのセルを選び出し、その前の行との列の偏差、行の偏差、定数の偏差(通常は0でしょうね)を求めます。チェックの基準値となります。
4.先頭の次のセルからチェックです。計算式の入っていないセルはチェックしません。計算式なしのセルは最後に色付けをします。
パス名･シート名は等しいことが正当条件です。
列番号、行番号、定数は、前のセルとの偏差が基準値と等しいことが正当条件です。
ここで問題が起きました。チェックする項目数が異なる場合があることです。したがって、項目数が基準のセルと異なる場合もエラーにします。
とりあえず、列番号、行番号、定数用の配列変数は0で初期化しているので、項目数が異なる場合でも、チェックは基準のセルが持っている項目数分だけ行いますので、チェックしてもエラーになりません。
5.色をつける順番は、偏差等が異なる、チェック項目数が異なる、計算式がないとなります。
6.一ブロック分が終了したので、必要な配列変数を初期化して、次の列に処理を移します。
7.全部終了したら、ダンプリストを取るかとらないかの指定をします。出力場所はこの時、指定します。配列変数は、当然最後の列の状態しかありません。ダンプリストは、問題があるところに限定し処理を走らせてから、取ってください。
具体的にしていきます。(1.は定番なので省略します)
まずは配列の変数の紹介です。
Dim mm_sh(100, 200)…パス名･シート名用(添え字の最初は行をあらわし、次はその計算式の中の項目を表す。以下同じ)
Dim mm1(100, 200)…列番号･定数用
Dim mm2(100, 200)…行番号用
Dim hensa1(100)…列番号･定数用の偏差
Dim hensa2(100)…行番号用の偏差
2.計算式を一つずつ取得して、座標などの必要な項目を取得する。
これも前とかでやったものです。
まずは、パス名にあるコロンを変えます。C:とかのコロンです。ここでは、C:、D:、F:のコロンをC_、D_、F_にします。
'計算式の取得
a = "***" & Cells(gyo1 + ii - 1, retu1 + jj - 1).Formula
If InStr(a, "=") = 0 Then a = "" '計算式でない場合はクリアする
b = a
'ドライブ名のつなぎの置き換え。 :を _へ
del_mojis(1) = "C:"
del_mojis(2) = "D:"
del_mojis(3) = "F:"
For i = 1 To 3
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) & Left(del_mojis(i), 1) & "_" & Mid$(b, p1 + Len(del_mojis(i)))
Loop
End If
Next
'関数名の削除(省略)
"ROUND"、"LOOKUP"、"IF"、"INT"、"SIGN"、"SUMPRODUCT"、"INDIRECT"
"ADDRESS"、"MAX"、 "MIN"、 "SUM"
を削除します。
'特殊文字の置き換え(省略)
"("、")"、""""、"<"、">"、"="、"*"、"/"、"-"、"+"
":"、"^"、",,,,,"、",,,,"、",,,"、",,"
をカンマ一つに置き換えます。
'カンマで区切られた項目の取得
Do loop文でカンマのある位置を元に項目を分解して配列変数に入れます。最後の一個に気をつけてください(文字列中にカンマがない場合は、残っているのが最後の項目になります。これを取得するのに注意してください)。
これはDo loop文の外で取得します。以下の文では省略しています。また、取得した項目数を求めておいてください。
Do While 1
p1 = InStr(b, ",")
If p1 = 0 Then Exit Do
If Left$(b, p1 - 1) <> "" Then
p_xy_A1(i) = Left$(b, p1 - 1)
i = i + 1
End If
b = Mid$(b, p1 + 1)
Loop
'パス名･シート名と座標等の分離。座標でシート名がない場合は、当該シート名
パス名･シート名があるかないかは、"!"があるかないかで調べます。
定数の場合は、シート名はヌルにしています。
For i = 0 To p_xy_no 'これは項目数(0からカウント)
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)
ElseIf IsNumeric(p_xy_A1(i)) = False Then
p_sh_name(i) = ActiveSheet.Name
Else
p_sh_name(i) = ""
End If
Next
'シート名、列番号、行番号を格納する。mm1(ii, 0)は項目の個数(1からのカウント)です。
今まで取得したものと、この時にこれを入れるmm1(ii, 0)とは、一つずれているので注意してください。
座標の場合、座標から列番号と、行番号を求めます。
mm1(ii, 0) = p_xy_no + 1 'iiは行の値です。
For k = 1 To p_xy_no + 1
mm_sh(ii, k) = p_sh_name(k - 1)
If p_xy_A1(k - 1) <> "" And IsNumeric(p_xy_A1(k - 1)) = False Then
mm1(ii, k) = Range(p_xy_A1(k - 1)).Column
mm2(ii, k) = Range(p_xy_A1(k - 1)).Row
ElseIf IsNumeric(p_xy_A1(k - 1)) = True Then 'これは定数の場合です。
mm1(ii, k) = p_xy_A1(k - 1)
mm2(ii, k) = 0
Else
mm1(ii, k) = 0
mm2(ii, k) = 0
End If
Next k
※これでやっと一つのセルの計算式の分解が終わりましたので、次のセルに処理を移します。
Next ii
※長くなりましたので、今回はこれでおしまい。
2009年4月12日日曜日
第114回目 計算式をチェックする
○第114回目 計算式をチェックする
似ているようですが、前回とは別テーマです。
出来上がったエクセルのシステムにおける計算式をチェックしようというものです。
そんなものができるはずがない、というのが本当のところでしょう。
実はそのとおりなのです。
一般的に使えるようにする、それゆえに、非常に簡単なチェックしかできるわけがないということになります。ということは、一般的に使える、それゆえに、非常に簡単なチェックならできるということになります。(これを否定からの肯定への変換といいます。よくコップに半分の水がはいっている例があげられます。この状態を、半分しか入っていないと見るのか、半分(も)入っていると見るのか、どうせなら、半分(も)入っているというふうに見ようじゃないか、というのと同じです)
エクセルのシステムを作ると、全部はできませんが、部分々々、おおよそのところはチェックをします。それを前提として、漏れやすいもの(特に頭の中で)を対象にします。
チェック内容は？(ブロック単位でのチェックとなります)
1.計算式が抜けていた(何かのはずみでクリアしてしまったのでしょう)。
2.計算式を最後まで直してなかった(縦方向、または、横方向にコピーし忘れていたということでしょう)。
3.座標の偏位が途中からずれている。
4.SUM関数の指定範囲が一部分ずれている。
こんなところにしておきます。
対応の考え方です。
1.計算式がないところに色をつけます。変なところに色がついているかどうかでわかります。
2.(簡単にするため縦方向のブロックを考えます)問題は、計算式が違うというのをどうやって判断するかです。一つ一つの計算式は異なっていますので、厳密に見てしまうと、みんな違うはずです。
通常は一つ計算式を作って、縦にコピーします。すると座標は変わりますが、計算式の構造は変わりません。つまり、ここに着目して計算式の構造が変わっているかどうかで判断します。この対応としては、計算式から数値を取り除いてそれで一致するかどうかを見ることにします。
3.(これも縦方向のブロックを考えます)2.で取り除いた数値に着目します。この差が全部の計算式で一定かどうかを見ることにします。
4.(これは横方向のブロックを考えます。縦方向のものは、2,3で対応できる)SUM関数内の最初と最後の座標(ここでは行数)が全部の計算式で同じかどうかを見ることにします。これをやるためには、まず、横の一般系(3に対応するもの)を考えたほうがいいです。
具体例
1.計算式が抜けていた。
範囲を選択してから実行します。
とあるセルに計算式があるかどうかを判断し、計算式がない場合は色をつけます。
Dim CCC As Object
For Each CCC In Selection
If CCC.HasFormula <> True Then
CCC.Interior.ColorIndex = 42
End If
Next
色をなくす場合は次のようにします。
CCC.Interior.ColorIndex = xlColorIndexNone
すべてのセルが対象であれば、if文はいりません。
ちょっとしたコマンドの使い方さえ知っていればこれは思ったより簡単です。
2.計算式を最後まで直していなかった。
これは少し分析してからではないと混乱します。
1.範囲内の計算式を配列変数に入れます。計算式がない場合はヌルとします。配列変数は二次元となります。セルの移動の仕方は、列に飛び、列が終わると次の行に飛ぶということになります。(以下の3.の関係でこの段階で計算式のないセルに色をつけます)
2.その計算式から数値及び"."を除きます。
3.計算式は、1行目にある場合と2行目にある場合があります。また、場合によっては一行目と2行目以降の計算式が異なることがあります。
そこでチェックの内容ですが、
一行目に計算式がない場合は、一行目を元にした計算式のチェックはしません。
二行目に計算式がない場合は、二行目を元にした計算式のチェックはしません。
一行目、二行目に計算式がある場合は、それぞれでつける色を変えます。重なる場合は二行目の色が上塗りされます。計算式がないセルはチェックしません(前回の機能を含めて、この場合は別な色をつけることにします)。
とします。
・計算式がないセル
・一行目の計算式と異なるセル(次の場合は二行目のチェックの結果が優先されます)
・二行目の計算式と異なるセル
に色がつくことになります。
1.範囲内の計算式を配列変数に入れます
'選択範囲の座標の取得
AAA = Selection.Address
p1 = InStr(AAA, ":")
AAA1 = Left(AAA, p1 - 1)
AAA9 = Mid(AAA, p1 + 1)
retu1 = Range(AAA1).Column
gyo1 = Range(AAA1).Row
retu9 = Range(AAA9).Column
gyo9 = Range(AAA9).Row
gyo_cnt = gyo9 - gyo1 + 1
retu_cnt = retu9 - retu1 + 1
'計算式の取得
For j = 1 To retu_cnt
For i = 1 To gyo_cnt
mm(j, i) = Cells(gyo1 + i - 1, retu1 + j - 1).Formula
if instr(mm(j, i),"=") = 0 then
mm(j, i) = ""
Cells(gyo1 + i - 1, retu1 + j - 1).Interior.ColorIndex = 20
end if
Next i
Next j
※"="が入っているかで計算式かどうか判断しています。入っていない場合は、色をつけます。
2.その計算式から数値及び"."を除きます。
これは、『第110回目 計算式から場所情報を取得できないか』で"ある文字を除く"処理の説明がありますので、それを参照してください。
これによって計算式から、0-9と.が除かれます。
3.計算式が同一かどうかのチェック
一行目、二行目に計算式がない場合はチェックしません。また、計算式のないセルはチェックしません。
'計算式のチェック
For j = 1 To retu_cnt
For i = 2 To gyo_cnt
If (mm(j, i) <> "") Then
If (mm(j, 1) <> "") and (mm(j, i) <> mm(j, 1)) Then
Cells(gyo1 + i - 1, retu1 + j - 1).Interior.ColorIndex = 43
End If
If (mm(j, 2) <> "") and (mm(j, i) <> mm(j, 2)) Then
Cells(gyo1 + i - 1, retu1 + j - 1).Interior.ColorIndex = 44
End If
End If
Next i
Next j
※カラーパレットについては下記参照
http://www.feedsoft.net/excel/tips/vba_63.html
※以下は次回以降
3.座標の偏位が途中からずれている。
4.SUM関数の指定範囲が一部分ずれている。
似ているようですが、前回とは別テーマです。
出来上がったエクセルのシステムにおける計算式をチェックしようというものです。
そんなものができるはずがない、というのが本当のところでしょう。
実はそのとおりなのです。
一般的に使えるようにする、それゆえに、非常に簡単なチェックしかできるわけがないということになります。ということは、一般的に使える、それゆえに、非常に簡単なチェックならできるということになります。(これを否定からの肯定への変換といいます。よくコップに半分の水がはいっている例があげられます。この状態を、半分しか入っていないと見るのか、半分(も)入っていると見るのか、どうせなら、半分(も)入っているというふうに見ようじゃないか、というのと同じです)
エクセルのシステムを作ると、全部はできませんが、部分々々、おおよそのところはチェックをします。それを前提として、漏れやすいもの(特に頭の中で)を対象にします。
チェック内容は？(ブロック単位でのチェックとなります)
1.計算式が抜けていた(何かのはずみでクリアしてしまったのでしょう)。
2.計算式を最後まで直してなかった(縦方向、または、横方向にコピーし忘れていたということでしょう)。
3.座標の偏位が途中からずれている。
4.SUM関数の指定範囲が一部分ずれている。
こんなところにしておきます。
対応の考え方です。
1.計算式がないところに色をつけます。変なところに色がついているかどうかでわかります。
2.(簡単にするため縦方向のブロックを考えます)問題は、計算式が違うというのをどうやって判断するかです。一つ一つの計算式は異なっていますので、厳密に見てしまうと、みんな違うはずです。
通常は一つ計算式を作って、縦にコピーします。すると座標は変わりますが、計算式の構造は変わりません。つまり、ここに着目して計算式の構造が変わっているかどうかで判断します。この対応としては、計算式から数値を取り除いてそれで一致するかどうかを見ることにします。
3.(これも縦方向のブロックを考えます)2.で取り除いた数値に着目します。この差が全部の計算式で一定かどうかを見ることにします。
4.(これは横方向のブロックを考えます。縦方向のものは、2,3で対応できる)SUM関数内の最初と最後の座標(ここでは行数)が全部の計算式で同じかどうかを見ることにします。これをやるためには、まず、横の一般系(3に対応するもの)を考えたほうがいいです。
具体例
1.計算式が抜けていた。
範囲を選択してから実行します。
とあるセルに計算式があるかどうかを判断し、計算式がない場合は色をつけます。
Dim CCC As Object
For Each CCC In Selection
If CCC.HasFormula <> True Then
CCC.Interior.ColorIndex = 42
End If
Next
色をなくす場合は次のようにします。
CCC.Interior.ColorIndex = xlColorIndexNone
すべてのセルが対象であれば、if文はいりません。
ちょっとしたコマンドの使い方さえ知っていればこれは思ったより簡単です。
2.計算式を最後まで直していなかった。
これは少し分析してからではないと混乱します。
1.範囲内の計算式を配列変数に入れます。計算式がない場合はヌルとします。配列変数は二次元となります。セルの移動の仕方は、列に飛び、列が終わると次の行に飛ぶということになります。(以下の3.の関係でこの段階で計算式のないセルに色をつけます)
2.その計算式から数値及び"."を除きます。
3.計算式は、1行目にある場合と2行目にある場合があります。また、場合によっては一行目と2行目以降の計算式が異なることがあります。
そこでチェックの内容ですが、
一行目に計算式がない場合は、一行目を元にした計算式のチェックはしません。
二行目に計算式がない場合は、二行目を元にした計算式のチェックはしません。
一行目、二行目に計算式がある場合は、それぞれでつける色を変えます。重なる場合は二行目の色が上塗りされます。計算式がないセルはチェックしません(前回の機能を含めて、この場合は別な色をつけることにします)。
とします。
・計算式がないセル
・一行目の計算式と異なるセル(次の場合は二行目のチェックの結果が優先されます)
・二行目の計算式と異なるセル
に色がつくことになります。
1.範囲内の計算式を配列変数に入れます
'選択範囲の座標の取得
AAA = Selection.Address
p1 = InStr(AAA, ":")
AAA1 = Left(AAA, p1 - 1)
AAA9 = Mid(AAA, p1 + 1)
retu1 = Range(AAA1).Column
gyo1 = Range(AAA1).Row
retu9 = Range(AAA9).Column
gyo9 = Range(AAA9).Row
gyo_cnt = gyo9 - gyo1 + 1
retu_cnt = retu9 - retu1 + 1
'計算式の取得
For j = 1 To retu_cnt
For i = 1 To gyo_cnt
mm(j, i) = Cells(gyo1 + i - 1, retu1 + j - 1).Formula
if instr(mm(j, i),"=") = 0 then
mm(j, i) = ""
Cells(gyo1 + i - 1, retu1 + j - 1).Interior.ColorIndex = 20
end if
Next i
Next j
※"="が入っているかで計算式かどうか判断しています。入っていない場合は、色をつけます。
2.その計算式から数値及び"."を除きます。
これは、『第110回目 計算式から場所情報を取得できないか』で"ある文字を除く"処理の説明がありますので、それを参照してください。
これによって計算式から、0-9と.が除かれます。
3.計算式が同一かどうかのチェック
一行目、二行目に計算式がない場合はチェックしません。また、計算式のないセルはチェックしません。
'計算式のチェック
For j = 1 To retu_cnt
For i = 2 To gyo_cnt
If (mm(j, i) <> "") Then
If (mm(j, 1) <> "") and (mm(j, i) <> mm(j, 1)) Then
Cells(gyo1 + i - 1, retu1 + j - 1).Interior.ColorIndex = 43
End If
If (mm(j, 2) <> "") and (mm(j, i) <> mm(j, 2)) Then
Cells(gyo1 + i - 1, retu1 + j - 1).Interior.ColorIndex = 44
End If
End If
Next i
Next j
※カラーパレットについては下記参照
http://www.feedsoft.net/excel/tips/vba_63.html
※以下は次回以降
3.座標の偏位が途中からずれている。
4.SUM関数の指定範囲が一部分ずれている。
2009年4月5日日曜日
第113回目 計算式の内部の関数値を求める
○第113回目 計算式の内部の関数値を求める
これは、xxx=lookup(……)*ｃ10という式があったとき、lookup(……)の結果を求めようとするものです。他には、SUM関数、SUMPRODUCT関数などが考えられます。
これは、次のような流れで作ります。
1.計算式中の該当する部分のみを抜き出す。上の例では、lookup(……)の部分を抜き出すということになります。
2.その部分を計算式の形式に整えて、特殊な場所のセルに計算式として埋め込みます。
3.そのセルには計算結果が出てきますので、その結果を変数に入れます。また、エクセルの外で使えるように、クリップボードに貼付けます。処理終了後、特殊な場所のセルをクリアします。
4.計算式の内容一覧表の中に、該当する計算式とその結果を表示します。
1.において
最初の位置は簡単に取得できますが、後ろの位置は、いろいろな場合があるので、ちょっと工夫が必要です。現実には、それ以降のカンマの位置を求め、そこからそれ以降のカッコの閉じの位置を最後の位置としました。元にある数値が複雑な形式の場合は、参照の元となる変数の記述にカッコが使われる場合があるので、このようにしています。
しかしながら、要素が三つあるので、二つ目のカンマ以降のカッコの閉じのほうがより正確でしょう。応用ですので各自チャレンジしてみてください。
3.において
セルに計算式を埋め込むのは簡単ですが、それをクリップボードに入れるのは若干注意が必要です。コピペを実際に行ってからではないとクリップボードにはいかないからです。当該セルに当該セルの内容をデータのみコピペしてしまい、その後、そのセルをクリアするというのがいいようです。変数値から直接クリップボードに入れる関数があればいいのですが、今のところわかりませんので、このようにしています。
他の関数においても、それ専用に考えていけばいいと思います。すべてが一つのやり方ではできるとは思いませんので。
このことにより、計算式の中の項目を数値に置き換えることがかなりできるようになります。計算式のデバックがより簡単になると思います。
これは、xxx=lookup(……)*ｃ10という式があったとき、lookup(……)の結果を求めようとするものです。他には、SUM関数、SUMPRODUCT関数などが考えられます。
これは、次のような流れで作ります。
1.計算式中の該当する部分のみを抜き出す。上の例では、lookup(……)の部分を抜き出すということになります。
2.その部分を計算式の形式に整えて、特殊な場所のセルに計算式として埋め込みます。
3.そのセルには計算結果が出てきますので、その結果を変数に入れます。また、エクセルの外で使えるように、クリップボードに貼付けます。処理終了後、特殊な場所のセルをクリアします。
4.計算式の内容一覧表の中に、該当する計算式とその結果を表示します。
1.において
最初の位置は簡単に取得できますが、後ろの位置は、いろいろな場合があるので、ちょっと工夫が必要です。現実には、それ以降のカンマの位置を求め、そこからそれ以降のカッコの閉じの位置を最後の位置としました。元にある数値が複雑な形式の場合は、参照の元となる変数の記述にカッコが使われる場合があるので、このようにしています。
しかしながら、要素が三つあるので、二つ目のカンマ以降のカッコの閉じのほうがより正確でしょう。応用ですので各自チャレンジしてみてください。
3.において
セルに計算式を埋め込むのは簡単ですが、それをクリップボードに入れるのは若干注意が必要です。コピペを実際に行ってからではないとクリップボードにはいかないからです。当該セルに当該セルの内容をデータのみコピペしてしまい、その後、そのセルをクリアするというのがいいようです。変数値から直接クリップボードに入れる関数があればいいのですが、今のところわかりませんので、このようにしています。
他の関数においても、それ専用に考えていけばいいと思います。すべてが一つのやり方ではできるとは思いませんので。
このことにより、計算式の中の項目を数値に置き換えることがかなりできるようになります。計算式のデバックがより簡単になると思います。
2009年3月21日土曜日
第112回目 計算式の分析の活用例
○第112回目 計算式の分析の活用例
第107回から前回までのマクロの活用例です。
二段階活用をします。
第一段階は、目で見てチェックする場合です。
複雑な計算式のセルにあわせて、計算式の中の座標参照マクロを動かします。
第一に、計算式の取得が必要かどうかを聞きます。前の処理での座標が残っていますので、前のままでいい場合は、1以外を入れます。
コンボボックスが出てきます。矢印キーでおくっていくと、座標が出てきます。そこで目的となる座標に移動です。シート間も移動しますので便利です。
移動先で、何かもっと調べたいときは、終了とします。そこでいろいろ調べものをして終わったら、また、このマクロを動かします。前の情報が残っているので計算式の取得はノーとします。そこで次の項目の座標にいくことができます。元の計算式の座標も入っていますので、そこへの移動も可能です。
ある場所でさらに先を見てみたいときは、このマクロを動かし、計算式を取得します。後は大体同じとなります。
複数の計算式の座標を貯めることもできますが、それは余力があったときでいいと思います。あまり多すぎますと、どの座標がどんな意味を持っていたのか覚えられなくなってしまいますから、混乱の元です。
この操作での問題点は、記録が残らないことです。簡単なものなら、確認後チェック済みとしておけばいいのですが、複雑な場合とか、誰かに説明をする必要がある場合とかは、文書化する必要があります。
そこで第二段階目のものです。
第111回で照会した、｢計算式で参照されている場所の情報を集めた一覧表の作成｣です。途中途中で情報を文書化しておけば、後の処理がしやすくなります。私の場合は、一覧表は新しいエクセルシートとなりますが、エクセルでは情報の加工が面倒なので、いったんテキストエディタにコピーしています。意外とエクセルは新しいシートを作るだけでも容量が大きくなってしまうので、小容量化にも適しています。
印刷してチェックしたり、コメントを追記したりして資料として整理します。また、QXマクロの中で便利だったのは、文字罫線で囲うという処理です。計算式の内容一覧表は、同じ形式なのでつなげてしまうと、どこから始まっているのかわかりづらくなるので、一回ごとに枠で囲みます。ブロック単位に整理されるので見やすくなります。
この部分を例としてやってみます。
┏━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ 印刷してチェックしたり、コメントを追記したりして資料として ┃
┃整理します。また、QXマクロの中で便利だったのは、文字罫線で囲 ┃
┃うという処理です。計算式の内容一覧表は、同じ形式なのでつなげ ┃
┃てしまうと、どこから始まっているのかわかりづらくなるので、一 ┃
┃回ごとに枠で囲みます。ブロック単位に整理されるので見やすくな ┃
┃ります。 ┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━┛
(末尾の罫線がおかしくなっていますが、ここでの表示上どうしようもないようです)
となります。(解除機能もありますので、やり直しが簡単にできます)
この一覧表で問題になるのは、計算式が主であるため、説明文字が少ないことです。このマクロではできるだけ文字情報をとるようにしています。座標のほかに、シート名、列見出し、行見出し、セルの値などです。でも不十分です。
この場所について調べなおしたいということがあります。この場合は、指定位置へのジャンプマクロが有効です。このマクロでは、シート間の移動もできるのですが、わずらわしいときは手操作でシートを移動させて、シート内の移動マクロを動かします。
位置は、－で区切って、列の場合は列記号でも列番号でも可能としています。また、おおよそ移動として、カーソルのある列のある行、カーソルのある行のある列という指定もできます。前者の場合は2-とか、後者の場合は-6とかです、それぞれ、カーソルのある行のＢ列、カーソルのある列の6行目に移動します。(通常コマンドの編集－ジャンプでも大差はないかもしれません)
そんなこんなで、必要な情報を調べて資料を整理していきます。
文字罫線で囲ったものにインデントなどでアクセント(前空白、タブ、引用符など)をつけて、計算式の深さを表すこともできそうですね。
【やってみた率直な感想】
資料として残る。
次の日の再開がスムーズにできる。
操作が面白いので、疲れない。
資料が膨大になる可能性があるので逐一コメントを付すなどして整理をしないと後で見るとわからなくなる。
ある一部を他人に依頼し作業を分担することができる(できそう)。
文書化するので、頭が混乱しない。
だいたいは印刷するので、画面を見っぱなしということがなく、目が疲れない。
第107回から前回までのマクロの活用例です。
二段階活用をします。
第一段階は、目で見てチェックする場合です。
複雑な計算式のセルにあわせて、計算式の中の座標参照マクロを動かします。
第一に、計算式の取得が必要かどうかを聞きます。前の処理での座標が残っていますので、前のままでいい場合は、1以外を入れます。
コンボボックスが出てきます。矢印キーでおくっていくと、座標が出てきます。そこで目的となる座標に移動です。シート間も移動しますので便利です。
移動先で、何かもっと調べたいときは、終了とします。そこでいろいろ調べものをして終わったら、また、このマクロを動かします。前の情報が残っているので計算式の取得はノーとします。そこで次の項目の座標にいくことができます。元の計算式の座標も入っていますので、そこへの移動も可能です。
ある場所でさらに先を見てみたいときは、このマクロを動かし、計算式を取得します。後は大体同じとなります。
複数の計算式の座標を貯めることもできますが、それは余力があったときでいいと思います。あまり多すぎますと、どの座標がどんな意味を持っていたのか覚えられなくなってしまいますから、混乱の元です。
この操作での問題点は、記録が残らないことです。簡単なものなら、確認後チェック済みとしておけばいいのですが、複雑な場合とか、誰かに説明をする必要がある場合とかは、文書化する必要があります。
そこで第二段階目のものです。
第111回で照会した、｢計算式で参照されている場所の情報を集めた一覧表の作成｣です。途中途中で情報を文書化しておけば、後の処理がしやすくなります。私の場合は、一覧表は新しいエクセルシートとなりますが、エクセルでは情報の加工が面倒なので、いったんテキストエディタにコピーしています。意外とエクセルは新しいシートを作るだけでも容量が大きくなってしまうので、小容量化にも適しています。
印刷してチェックしたり、コメントを追記したりして資料として整理します。また、QXマクロの中で便利だったのは、文字罫線で囲うという処理です。計算式の内容一覧表は、同じ形式なのでつなげてしまうと、どこから始まっているのかわかりづらくなるので、一回ごとに枠で囲みます。ブロック単位に整理されるので見やすくなります。
この部分を例としてやってみます。
┏━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ 印刷してチェックしたり、コメントを追記したりして資料として ┃
┃整理します。また、QXマクロの中で便利だったのは、文字罫線で囲 ┃
┃うという処理です。計算式の内容一覧表は、同じ形式なのでつなげ ┃
┃てしまうと、どこから始まっているのかわかりづらくなるので、一 ┃
┃回ごとに枠で囲みます。ブロック単位に整理されるので見やすくな ┃
┃ります。 ┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━┛
(末尾の罫線がおかしくなっていますが、ここでの表示上どうしようもないようです)
となります。(解除機能もありますので、やり直しが簡単にできます)
この一覧表で問題になるのは、計算式が主であるため、説明文字が少ないことです。このマクロではできるだけ文字情報をとるようにしています。座標のほかに、シート名、列見出し、行見出し、セルの値などです。でも不十分です。
この場所について調べなおしたいということがあります。この場合は、指定位置へのジャンプマクロが有効です。このマクロでは、シート間の移動もできるのですが、わずらわしいときは手操作でシートを移動させて、シート内の移動マクロを動かします。
位置は、－で区切って、列の場合は列記号でも列番号でも可能としています。また、おおよそ移動として、カーソルのある列のある行、カーソルのある行のある列という指定もできます。前者の場合は2-とか、後者の場合は-6とかです、それぞれ、カーソルのある行のＢ列、カーソルのある列の6行目に移動します。(通常コマンドの編集－ジャンプでも大差はないかもしれません)
そんなこんなで、必要な情報を調べて資料を整理していきます。
文字罫線で囲ったものにインデントなどでアクセント(前空白、タブ、引用符など)をつけて、計算式の深さを表すこともできそうですね。
【やってみた率直な感想】
資料として残る。
次の日の再開がスムーズにできる。
操作が面白いので、疲れない。
資料が膨大になる可能性があるので逐一コメントを付すなどして整理をしないと後で見るとわからなくなる。
ある一部を他人に依頼し作業を分担することができる(できそう)。
文書化するので、頭が混乱しない。
だいたいは印刷するので、画面を見っぱなしということがなく、目が疲れない。
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.終了のボタンが押されたら終了とする。
これで構想が出来上がりました。
2009年2月22日日曜日
第108回目 計算式で参照されている場所に飛ばすことができないか(その2)
○第108回目 計算式で参照されている場所に飛ばすことができないか(その2)
続きです。
場所の与え方です。
まずは試験運転です。
セルを2列用意して、シート名と座標を書き込みます。現在のシート名と同じ場合はヌルでいいです。複数行書き込みます。
この場所情報の範囲を選択してから、マクロを実行させることとします。
三つの配列変数を用意します。シート名、座標、それらを結合したものです。
選択範囲の座標を求め(よく出てくる定番のマクロです)、セル内の情報を配列変数に取得します。シート名がヌルの場合は、当該シート名を入れてください。
当該シート名は、= ActiveSheet.Nameとなります。
二つの情報を結合してください。
p_mm0(i) = p_mm1(i) & " --- " & p_mm2(i)
p_mm1(i)；シート名 p_mm2(i)；座標
そして、UserForm1.Showで、ユーザーフォームマクロに引き渡します。
※ユーザーフォームマクロには変更はありません。
さて、コンボボックスで場所が選択され、こちらのマクロに戻ってきました。
その情報の場所に移動します。これは本当に簡単です。
配列変数の添え字は、p_bですので、それを使って、あっという間です。
'選択先に飛ぶ
Worksheets(p_mm1(p_b)).Select
Range(p_mm2(p_b)).Select
ここまでくると、このセルの値と計算式は、簡単に取得できますね。
値と計算式を配列変数に入れておけば、計算式の内容一覧表を作れることになります。
※ここで面白いことに気がつきました。もっと早く気づいているべきなのかもしれませんが、変数値が当該マクロを終了してもそのまま残っているのです。
パブリック変数だからのようです(今までまったく気がつきませんでした)。
最初は場所の情報のあるセルを選択して実行します。そして、その中から一つ指定して、その場所に移動しました。そこで終了します。
次の違う場所に行きたい、といったときどうなるのでしょうか。データが残っていればそれを使えばいいですよね。
ちょっとした修正(指定しているセルが一行である場合は、情報の取得は行わない)を施しておくと、二回目以降はマクロの実行だけでいいのです。
次は、もっとレベルアップして、シート名、座標を与えるのではなく、直接、計算式から取得するという機能を付加することにチャレンジしてみましょう。
続きです。
場所の与え方です。
まずは試験運転です。
セルを2列用意して、シート名と座標を書き込みます。現在のシート名と同じ場合はヌルでいいです。複数行書き込みます。
この場所情報の範囲を選択してから、マクロを実行させることとします。
三つの配列変数を用意します。シート名、座標、それらを結合したものです。
選択範囲の座標を求め(よく出てくる定番のマクロです)、セル内の情報を配列変数に取得します。シート名がヌルの場合は、当該シート名を入れてください。
当該シート名は、= ActiveSheet.Nameとなります。
二つの情報を結合してください。
p_mm0(i) = p_mm1(i) & " --- " & p_mm2(i)
p_mm1(i)；シート名 p_mm2(i)；座標
そして、UserForm1.Showで、ユーザーフォームマクロに引き渡します。
※ユーザーフォームマクロには変更はありません。
さて、コンボボックスで場所が選択され、こちらのマクロに戻ってきました。
その情報の場所に移動します。これは本当に簡単です。
配列変数の添え字は、p_bですので、それを使って、あっという間です。
'選択先に飛ぶ
Worksheets(p_mm1(p_b)).Select
Range(p_mm2(p_b)).Select
ここまでくると、このセルの値と計算式は、簡単に取得できますね。
値と計算式を配列変数に入れておけば、計算式の内容一覧表を作れることになります。
※ここで面白いことに気がつきました。もっと早く気づいているべきなのかもしれませんが、変数値が当該マクロを終了してもそのまま残っているのです。
パブリック変数だからのようです(今までまったく気がつきませんでした)。
最初は場所の情報のあるセルを選択して実行します。そして、その中から一つ指定して、その場所に移動しました。そこで終了します。
次の違う場所に行きたい、といったときどうなるのでしょうか。データが残っていればそれを使えばいいですよね。
ちょっとした修正(指定しているセルが一行である場合は、情報の取得は行わない)を施しておくと、二回目以降はマクロの実行だけでいいのです。
次は、もっとレベルアップして、シート名、座標を与えるのではなく、直接、計算式から取得するという機能を付加することにチャレンジしてみましょう。
2009年2月14日土曜日
第107回目 計算式で参照されている場所に飛ばすことができないか(その1)
○第107回目 計算式で参照されている場所に飛ばすことができないか(その1)
前回のうちの一つです。
6.計算式で参照されている場所に飛ばすことができないか。
その第一段階として、
・複数の場所を与えて、その中から選択して移動できる。コンボボックスを使う。
に挑戦します。
コンボボックスって、どうやって作って、どうやって文字を登録して、どうやって使うのでしょうか。
1.作り方
エクセルのVBAのシート(エクセルシートからAlt+F11で移動)において、挿入-ユーザーフォームでユーザーフォームを作ります。そこでツールを用いてコンボボックス(窓です)とその上にコマンドボタンを一つ作ります。コマンドボタンの表示文字を｢終了｣にします。
2.文字の登録
マクロ上に、次のように書きます。
p_mm0(0) = "りんご"
p_mm0(1) = "バナナ"
p_mm0(2) = "みかん"
p_mm0(3) = "メロン"
p_mm0(4) = "ぶどう"
UserForm1.Show
'ここでUserForm1のマクロに飛ぶ。そのマクロ場終了すると戻る。
ここで注意点は、ユーザフォームのマクロはまったく違うプロシージャです。したがって、変数を引き継がなければならないので、使用する変数をみなパブリック変数にします。
Public p_mm0(20)
これは、モジュールシートの一番先頭に書いておいてください。
3.コンボボックスの表示
今度はユーザーフォームにいきます。
ユーザーフォームでF7を押すと、ユーザーフォームのマクロシートが出てきます。
ここに続きのマクロを書きます。
Private Sub UserForm_Initialize() 'ユーザーフォームを初期化する
ComboBox1.List = p_mm0 'コンボボックスのリストに配列の値をセットする
End Sub
4.選んだ文字列を取得する
コンボボックスの中から文字を選びます、これはエンターキーで決めます。
この動きを処理するのが次のマクロです。これもユーザーフォームのマクロです。
このマクロは、コンボボックス内に動きがあると動きます。
Private Sub ComboBox1_Change() 'コンボボックス内に動きがあると動く
p_a = ComboBox1.Text '選択結果を取得
p_b = ComboBox1.ListIndex '選択された配列のインデックスを取得
End Sub
ここで、p_a、p_bもマクロシート上でパブリック変数として定義しておいてください。
5.選択の終了
選択を終了させるには、終了ボタンを押します。エンターキーでもいいです。文字列をきめてから、エンターキーを2回押すと終了します。
次のマクロは、終了ボタンが押されたときに動くマクロです。
Private Sub CommandButton1_Click()
If ComboBox1.MatchFound Then '一致する項目がリストの中にあれば
Unload Me 'ユーザーフォームを閉じる
Else 'なければ
MsgBox "やり直してください", vbExclamation, "みつかりません"
End If
End Sub
6.選択結果の表示
5．が終わりますと、2.のマクロに戻ります。
2．のマクロの後に、選択結果を表示しすマクロを続けます。
(UserForm1.Show；分かりやすくするために重複して書いています)
MsgBox (p_mm0(p_b) & " --- " & p_b)
または
MsgBox (p_a & " --- " & p_b)
これで選んだ文字と配列変数の番号が表示されます。
※骨格はこれでおしまいです。後はこれをアレンジしていきます。
前回のうちの一つです。
6.計算式で参照されている場所に飛ばすことができないか。
その第一段階として、
・複数の場所を与えて、その中から選択して移動できる。コンボボックスを使う。
に挑戦します。
コンボボックスって、どうやって作って、どうやって文字を登録して、どうやって使うのでしょうか。
1.作り方
エクセルのVBAのシート(エクセルシートからAlt+F11で移動)において、挿入-ユーザーフォームでユーザーフォームを作ります。そこでツールを用いてコンボボックス(窓です)とその上にコマンドボタンを一つ作ります。コマンドボタンの表示文字を｢終了｣にします。
2.文字の登録
マクロ上に、次のように書きます。
p_mm0(0) = "りんご"
p_mm0(1) = "バナナ"
p_mm0(2) = "みかん"
p_mm0(3) = "メロン"
p_mm0(4) = "ぶどう"
UserForm1.Show
'ここでUserForm1のマクロに飛ぶ。そのマクロ場終了すると戻る。
ここで注意点は、ユーザフォームのマクロはまったく違うプロシージャです。したがって、変数を引き継がなければならないので、使用する変数をみなパブリック変数にします。
Public p_mm0(20)
これは、モジュールシートの一番先頭に書いておいてください。
3.コンボボックスの表示
今度はユーザーフォームにいきます。
ユーザーフォームでF7を押すと、ユーザーフォームのマクロシートが出てきます。
ここに続きのマクロを書きます。
Private Sub UserForm_Initialize() 'ユーザーフォームを初期化する
ComboBox1.List = p_mm0 'コンボボックスのリストに配列の値をセットする
End Sub
4.選んだ文字列を取得する
コンボボックスの中から文字を選びます、これはエンターキーで決めます。
この動きを処理するのが次のマクロです。これもユーザーフォームのマクロです。
このマクロは、コンボボックス内に動きがあると動きます。
Private Sub ComboBox1_Change() 'コンボボックス内に動きがあると動く
p_a = ComboBox1.Text '選択結果を取得
p_b = ComboBox1.ListIndex '選択された配列のインデックスを取得
End Sub
ここで、p_a、p_bもマクロシート上でパブリック変数として定義しておいてください。
5.選択の終了
選択を終了させるには、終了ボタンを押します。エンターキーでもいいです。文字列をきめてから、エンターキーを2回押すと終了します。
次のマクロは、終了ボタンが押されたときに動くマクロです。
Private Sub CommandButton1_Click()
If ComboBox1.MatchFound Then '一致する項目がリストの中にあれば
Unload Me 'ユーザーフォームを閉じる
Else 'なければ
MsgBox "やり直してください", vbExclamation, "みつかりません"
End If
End Sub
6.選択結果の表示
5．が終わりますと、2.のマクロに戻ります。
2．のマクロの後に、選択結果を表示しすマクロを続けます。
(UserForm1.Show；分かりやすくするために重複して書いています)
MsgBox (p_mm0(p_b) & " --- " & p_b)
または
MsgBox (p_a & " --- " & p_b)
これで選んだ文字と配列変数の番号が表示されます。
※骨格はこれでおしまいです。後はこれをアレンジしていきます。
2009年1月31日土曜日
第106回目 エクセルでの複雑な計算システムのチェック
○第106回目 エクセルでの複雑な計算システムのチェック
エクセルでの複雑な計算システムのチェックです。
計算内容は入り組んでいて、計算結果はかなりの分量があるという代物です。
チェック結果もまとめ上げて文書化しなければならないので、そのつどメモを取っていく必要があります。
出来立てのほやほやのシステムですので、いろいろなところにエラーがある事が想定されます。
チェックの観点としては、
1.計算式の構成が正しいのかどうかをチェックする。
2.参照しているセルが正しいのかをチェックする。
3.具体的な数字を入れていき、計算結果が正しいのかをチェックする。
4.計算式が、たて、横に正しくコピーされているかどうかをチェックする。
計算式も簡単なものもあれば、長いものもあります。そこで使った手法は、ある範囲内の計算式を文字化してどこかにコピーすると言うことです。それを、テキストエディタに貼り付けて、それをみながらチェックするという方法をとりました。
以前仕様書作成で用いた、列記号を実際の項目名に変換するということも考えたのですが、おおよそは列記号が何を意味するのかはすぐ覚えてしまいので、時間の関係でそこまではしませんでした(この手法を使った場合、事前の準備に時間がかかるので、気分的に避けてしまったというのが本音でしょうか)。
一つの計算式が正しいのが分かると、それが横と下にうまく移っているかどうかをみます。
ここで思ったことは、例えば上と下の式の違いがすぐ分かるようなものはできないということです。通常は、上と下では、行数が一つずつ違うというものではないでしょうか。もしそのようなことであれば、一瞬にして分かるというのはできるのではないかということです。違っていればおかしいぞということになります。
また、次の段階に移っていった時に気がついたことですが、複数の計算式をテキストエディタに移すと、どれがどのセルのものなのかわからなくなってしまいます。セルの座標を付加して文字化した計算式にする必要があるのではないです。XX=XXXXX…というものにするということです。こちらのほうはそれほど難しくないと思います。
説明資料は、視覚的にする必要がありますので、グラフ、図形を主としたものになります。資料作成の段階になると、資料を作るための手法として、役に立つマクロはないか、ということが関心の的になります。
ということが、今回の仕事の中で気がついたことでした。
整理すると、
1.エクセルの計算式を、セル座標ではなく分かりやすい項目名で表すことをもっとスムーズにできないか。
2.計算式が正しく下と横にコピーされているかのチェックができないか(コピーしてみればいいのかもしれませんが…)。
3.計算式を文字化する場合、計算式の入っているセルの座標も付加できないか。
4.グラフの作成、図形の作成、グラフにコメントをつけるの各システムの操作マニュアルが不十分であったので、それらを整備する必要があるのではないか。
5.よく使う図形配置をパターン化し、何種類かのテンプレートを作成したらどうか。
6.計算式で参照されている場所に飛ばすことができないか。場所の与え方は、複数の場所を一度に与えて、その中から選択して移動できるというのはどうか。
7.計算式で参照されている場所の値、計算式を持ってこれないか。
8.6と7のために、計算式から場所の座標が取れないか。100%は難しいだろう。
ということを思いました。
これらのテーマを順番をつけてひとつずつ整理していきたいと思っています(中には対応が非常に簡単なものもあります)。
エクセルでの複雑な計算システムのチェックです。
計算内容は入り組んでいて、計算結果はかなりの分量があるという代物です。
チェック結果もまとめ上げて文書化しなければならないので、そのつどメモを取っていく必要があります。
出来立てのほやほやのシステムですので、いろいろなところにエラーがある事が想定されます。
チェックの観点としては、
1.計算式の構成が正しいのかどうかをチェックする。
2.参照しているセルが正しいのかをチェックする。
3.具体的な数字を入れていき、計算結果が正しいのかをチェックする。
4.計算式が、たて、横に正しくコピーされているかどうかをチェックする。
計算式も簡単なものもあれば、長いものもあります。そこで使った手法は、ある範囲内の計算式を文字化してどこかにコピーすると言うことです。それを、テキストエディタに貼り付けて、それをみながらチェックするという方法をとりました。
以前仕様書作成で用いた、列記号を実際の項目名に変換するということも考えたのですが、おおよそは列記号が何を意味するのかはすぐ覚えてしまいので、時間の関係でそこまではしませんでした(この手法を使った場合、事前の準備に時間がかかるので、気分的に避けてしまったというのが本音でしょうか)。
一つの計算式が正しいのが分かると、それが横と下にうまく移っているかどうかをみます。
ここで思ったことは、例えば上と下の式の違いがすぐ分かるようなものはできないということです。通常は、上と下では、行数が一つずつ違うというものではないでしょうか。もしそのようなことであれば、一瞬にして分かるというのはできるのではないかということです。違っていればおかしいぞということになります。
また、次の段階に移っていった時に気がついたことですが、複数の計算式をテキストエディタに移すと、どれがどのセルのものなのかわからなくなってしまいます。セルの座標を付加して文字化した計算式にする必要があるのではないです。XX=XXXXX…というものにするということです。こちらのほうはそれほど難しくないと思います。
説明資料は、視覚的にする必要がありますので、グラフ、図形を主としたものになります。資料作成の段階になると、資料を作るための手法として、役に立つマクロはないか、ということが関心の的になります。
ということが、今回の仕事の中で気がついたことでした。
整理すると、
1.エクセルの計算式を、セル座標ではなく分かりやすい項目名で表すことをもっとスムーズにできないか。
2.計算式が正しく下と横にコピーされているかのチェックができないか(コピーしてみればいいのかもしれませんが…)。
3.計算式を文字化する場合、計算式の入っているセルの座標も付加できないか。
4.グラフの作成、図形の作成、グラフにコメントをつけるの各システムの操作マニュアルが不十分であったので、それらを整備する必要があるのではないか。
5.よく使う図形配置をパターン化し、何種類かのテンプレートを作成したらどうか。
6.計算式で参照されている場所に飛ばすことができないか。場所の与え方は、複数の場所を一度に与えて、その中から選択して移動できるというのはどうか。
7.計算式で参照されている場所の値、計算式を持ってこれないか。
8.6と7のために、計算式から場所の座標が取れないか。100%は難しいだろう。
ということを思いました。
これらのテーマを順番をつけてひとつずつ整理していきたいと思っています(中には対応が非常に簡単なものもあります)。
2009年1月17日土曜日
第105回目 ハイパーコピデルはどのくらい便利なのか？
○第105回目 ハイパーコピデルはどのくらい便利なのか？
□ハイパーコピデルのいいところは何ですか。
＝⇒まずは、コピー関係の処理に強いということです。マクロを作っているとき、あの部分を持ってきて、それを修正しようということがあります。そのときのコピーが楽です。
まず、貼付ける場所を決めます。それから、画面を動かしていって、コピーしたところを探していきます。見出しジャンプも効きますから、大きく動かしてその後小さく動かして目的の場所に移動します。目的の場所でエンターキーと矢印キーでコピーもとを指定します。通常は行単位が多いので、上下の矢印キーだけで十分です。範囲を指定後にエンターキーを押すと、元も場所に、コピーもとがコピーされます。画面も元の場所に戻っています。離れているときなどは効果抜群です。
□コピー作業での他の例をあげてみてください。
＝⇒ 前の事例の場合ですが、コピーもとから指定することも可能です。これも便利な機能です。つまり、ここにあれをコピーするという場合と、これをあそこにコピーするという場合の両方ができるのです。
文字列をコピーする場合も同じです。コピーの範囲の最初をエンターキーで指定すると、カーソルが自動的に右に動いていきます。短いものならばそれを止めて微調整で範囲の指定ができます。もちろん長い文字列の場合もカーソルを止めて矢印キーで範囲指定できます。
また、同じ操作で、*を押すことによって、移動にもなります。
単にその行をコピーしたいときは、Ｂを押します。
□今、コピーと移動でしたが、削除はどのようにするのですか。
＝⇒削除には、一字一字の削除、範囲削除、文末までの一気削除、一行削除があります。
キー操作としては、Deleteキー、範囲を指定した後でDeleteキー、BackSpaceキー、Ｔキーです。範囲指定の場合は、Ｄを押してから範囲を指定し、エンターキーなど(Spaceキー、Deleteキー)で削除するのが普通です。操作は、Ｄ、(カーソルが自動的に動く)、矢印キーで範囲を指定、エンターキーとなります。
なお、文末までの削除はBackSpaceキー、一行削除はＴのワンタッチです。
□場所が離れている場合便利そうですね。移動の機能で便利なのにはどんなものがありますか。
＝⇒よく使うのが見出しジャンプです。操作は、Ｍ、(見出し一覧のダイアログが出る)、矢印キーで動かしたい見出しにあわせる、エンターキーとなります。
小さい順位並べると、矢印キー、Home(行頭)、End(行末)、PageUp、Down、１(文頭)、２(文末)となります。また、QXエディタの特徴であるタブ式で複数のファイルの編集ができることに対応して、タブの移動ができます。前のタブヘはF1、後ろのタブヘはF2となります。
違うタブの文字列をコピーすることも基本機能です。
移動関係ではもう一つあります。今までの移動先(大事な操作の時のみ場所を取得)を覚えているので、その一覧表から移動することもできます。移動先一覧表は、F9ででます。ファイル名、行数、その行の内容が出ますので、おおよその見当ができます。
□動かした先の情報の履歴を持っているんですか。履歴という面でほかに何がありますか。
＝⇒文字の挿入の機能はありますが、ハイパーコピデルの弱いところです。ワンタッチで入力のダイアログが出ますが、ＩＭＥをオフにしているので、漢字入力の場合は、変換キーを押してからとなります。ちょっと手間がかかります(この部分は改良されました)。それを補助する意味合いで、過去に入れた文字列を保存しています(現時点では最大40個)。それを呼び出して再利用ができます。再利用した文字列は一番先頭に来ます。
同様の処理をしているのは、コピー･移動した文字列です。これも再利用ができます。これらは可変な情報なのですが、別に固定した情報も持っています。よく使う文字列を登録して利用しています。これも40個ありますので便利です。
これらの情報は、ファイルに出力していますので、ハイパーコピデルを終了させ、再び動かしても、引き継がれ、終了前と同じように利用可能となります。
補助機能として面白いのが、括弧の挿入です。括弧にはいろいろな種類がありますが、必要なものを登録して、指定した文字列を括弧で囲みます。括弧使いの人は泣いて喜びます。
□操作はどうなんですか。
＝⇒基本はワンタッチで、後は矢印キーで指定し、エンターキーです。矢印キーは数回動かしますが、それプラス２タッチでできるということになります。
□囲むということでは他に何かありますか。
＝⇒これはもう趣味の領域になりますが、ルビ表示をさせるときがよくあるので、ルビ表示用の文字列で囲むということもしています。その中では、太字にする、上付き文字にする、下付文字にする、割注に入れるなども設定しています。それでひらめきましたが、この中にマクロの関数を入れてみようと思います。例えば、"mid("と",)" で囲むというイメージです。まだ設定数は少ないので、もっと活用できそうです。
□文章を作成する際に、誤変換したものを再変換するということがありますが、この機能はどうなっていますか。
＝⇒最初はまったく考えませんでしたが、そのようなニーズもでてきたので、取り入れ
ています。再変換したい文字のところにカーソルをおき、ワンタッチで処理します。文字入力ダイアログにカーソルのあった場所の単語が取り込まれ、変換キーを押すと再変換してくれます。エンターキーで文書に書き込まれ、修正完了となります。文字入力ダイアログが出ているので、文字を追加するとか修正するとか削除するとかも可能となります。
□ほしい文字列がうまく取り込まれないことも考えられますが。
＝⇒その通りです。そこで、指定した文字列を入力ダイアログに取り込み、それを再変換、修正するという機能も入れました(この機能はそれほど使われないので、他の機能を追加したことに伴い削除しました)。
□これらの操作もワンタッチなんですか。
＝⇒機能呼び出しはワンタッチです。後は変換キー、矢印キーとエンターキーです。
□プログラム作成用としての機能にはほかに何がありますか。
＝⇒プログラム作成用のことももちろん考えています。いくつかありますが、便利なのは指定範囲の行頭にコメント行にするために" ''"を挿入する機能です。これの逆の、" ''"をとるというものもあります。デバック行を使ってテストするときなど便利です。あとは、例えばイコールの前後をひっくり返すというのがあります。
pos_fl$[0] = @Filename$ の文を、=を中心にひっくり返します。次のようになります。スペースの問題はありますが、まあまあでしょう。
@Filename$=pos_fl$[0]
これも記号を複数設定できますので、"="だけでなく、自分のニーズに合ったものを設定できます。
また、変数名を取り込むというものもあります。DIM文を範囲指定して処理をします。変数名が取り込まれます。取り込まれた変数名は利用可能となります。最大40個ですので、その範囲で保有ができます。追加すると、先に保有されたものが削除されていきます。また、使った変数名は一番前に移動します。保有している変数名を全部書き出すことができます。これは変数名の保有が一過性であることからです。次にハイパーコピデルを動かし、書き出した変数名を取り込むと再現されます。書き出した変数名を直接修正できますので、不要なものを削除、必要なものを追加できます。この処理はワンタッチとはなりませんでした。もうキーがなくなってしまったからです。具体的には、F6、矢印キーで処理を指定、エンターキー、矢印キーなどで指定、エンターキーとなります。
□最後に、言っておきたい機能をどうぞ。
＝⇒このハイパーコピデルの有効性は、ケース･バイ･ケースです。つまり、その人のニーズいかんです。
それでは、最後として、2.5項目ほど取り上げます。
まずは、置換え関係です。検索もありますが、便利なのは置換のほうです。
このように使います。
まず、置き換えたい単語の先頭にカーソルを置きます。そしてワンタッチキー(Ｊ)です。すると、カーソルの単語が取得され、入力ダイアログがでてきます。これでよければそのままエンターキー、悪ければ修正してエンターキーです。すると、次は置換え後の文字を指定するダイアログが出ます。初期値は先ほどの置き換えたい文字列です。ここで修正をし、エンターキーです。指定範囲の状態になっているので矢印キーで範囲を設定しエンターキーです。するとその指定範囲内で置換えられます。これは便利です。特にマクロ(プログラム)作成の際です。同様な操作で検索及び検索の続行ができます。それぞれワンタッチです。これらあわせて1.5項目でしょうか。
最後は、このハイパーコピデルの欠点を補うものです。ハイパーコピデルのもとで処理をしているとき、やはり、何回かに1回は、ハイパーコピデルを動かしているのを忘れてしまい、文字を作成するためにキーをたたきます。すると、ハイパーコピデルでのワンタッチキーとなってしまうので、意図しない動きとなってしまいます。その際は、処理がどこへいっているのかわからないことがあるので、その場合は、右クリックからマクロの終了を選びます。
しかし、マクロを終了させるというも芸がありません。そこで、意図せず何かの処理をさせてしまった場合(Ｔを押すと一行削除になります)、処理を前に戻すという機能はどうでしょうか。
このケースだけでなく、その他の場合のためにも、処理を一回戻すという機能を入れています。あわせて、戻した機能を1回進めるというのも入れています。操作キー、矢印キーで処理を選択、エンターキーという操作です。これも、前回の処理が前に来ていますので、それを知っていると、操作が省略されて、操作キー、エンターキーのツータッチで可能です。
聞くところによると、操作を戻す際の一連の操作の定義することができます。複数の操作を一括して定義できるということです。それを定義すれば、個々の細かい処理がまとめられ、一瞬で元に戻ります。これには対応していませんが、機能を決めて対応すると、さらに便利になると思っています。
長くなりましたので、今回はおしまいにします。
□ハイパーコピデルのいいところは何ですか。
＝⇒まずは、コピー関係の処理に強いということです。マクロを作っているとき、あの部分を持ってきて、それを修正しようということがあります。そのときのコピーが楽です。
まず、貼付ける場所を決めます。それから、画面を動かしていって、コピーしたところを探していきます。見出しジャンプも効きますから、大きく動かしてその後小さく動かして目的の場所に移動します。目的の場所でエンターキーと矢印キーでコピーもとを指定します。通常は行単位が多いので、上下の矢印キーだけで十分です。範囲を指定後にエンターキーを押すと、元も場所に、コピーもとがコピーされます。画面も元の場所に戻っています。離れているときなどは効果抜群です。
□コピー作業での他の例をあげてみてください。
＝⇒ 前の事例の場合ですが、コピーもとから指定することも可能です。これも便利な機能です。つまり、ここにあれをコピーするという場合と、これをあそこにコピーするという場合の両方ができるのです。
文字列をコピーする場合も同じです。コピーの範囲の最初をエンターキーで指定すると、カーソルが自動的に右に動いていきます。短いものならばそれを止めて微調整で範囲の指定ができます。もちろん長い文字列の場合もカーソルを止めて矢印キーで範囲指定できます。
また、同じ操作で、*を押すことによって、移動にもなります。
単にその行をコピーしたいときは、Ｂを押します。
□今、コピーと移動でしたが、削除はどのようにするのですか。
＝⇒削除には、一字一字の削除、範囲削除、文末までの一気削除、一行削除があります。
キー操作としては、Deleteキー、範囲を指定した後でDeleteキー、BackSpaceキー、Ｔキーです。範囲指定の場合は、Ｄを押してから範囲を指定し、エンターキーなど(Spaceキー、Deleteキー)で削除するのが普通です。操作は、Ｄ、(カーソルが自動的に動く)、矢印キーで範囲を指定、エンターキーとなります。
なお、文末までの削除はBackSpaceキー、一行削除はＴのワンタッチです。
□場所が離れている場合便利そうですね。移動の機能で便利なのにはどんなものがありますか。
＝⇒よく使うのが見出しジャンプです。操作は、Ｍ、(見出し一覧のダイアログが出る)、矢印キーで動かしたい見出しにあわせる、エンターキーとなります。
小さい順位並べると、矢印キー、Home(行頭)、End(行末)、PageUp、Down、１(文頭)、２(文末)となります。また、QXエディタの特徴であるタブ式で複数のファイルの編集ができることに対応して、タブの移動ができます。前のタブヘはF1、後ろのタブヘはF2となります。
違うタブの文字列をコピーすることも基本機能です。
移動関係ではもう一つあります。今までの移動先(大事な操作の時のみ場所を取得)を覚えているので、その一覧表から移動することもできます。移動先一覧表は、F9ででます。ファイル名、行数、その行の内容が出ますので、おおよその見当ができます。
□動かした先の情報の履歴を持っているんですか。履歴という面でほかに何がありますか。
＝⇒文字の挿入の機能はありますが、ハイパーコピデルの弱いところです。ワンタッチで入力のダイアログが出ますが、ＩＭＥをオフにしているので、漢字入力の場合は、変換キーを押してからとなります。ちょっと手間がかかります(この部分は改良されました)。それを補助する意味合いで、過去に入れた文字列を保存しています(現時点では最大40個)。それを呼び出して再利用ができます。再利用した文字列は一番先頭に来ます。
同様の処理をしているのは、コピー･移動した文字列です。これも再利用ができます。これらは可変な情報なのですが、別に固定した情報も持っています。よく使う文字列を登録して利用しています。これも40個ありますので便利です。
これらの情報は、ファイルに出力していますので、ハイパーコピデルを終了させ、再び動かしても、引き継がれ、終了前と同じように利用可能となります。
補助機能として面白いのが、括弧の挿入です。括弧にはいろいろな種類がありますが、必要なものを登録して、指定した文字列を括弧で囲みます。括弧使いの人は泣いて喜びます。
□操作はどうなんですか。
＝⇒基本はワンタッチで、後は矢印キーで指定し、エンターキーです。矢印キーは数回動かしますが、それプラス２タッチでできるということになります。
□囲むということでは他に何かありますか。
＝⇒これはもう趣味の領域になりますが、ルビ表示をさせるときがよくあるので、ルビ表示用の文字列で囲むということもしています。その中では、太字にする、上付き文字にする、下付文字にする、割注に入れるなども設定しています。それでひらめきましたが、この中にマクロの関数を入れてみようと思います。例えば、"mid("と",)" で囲むというイメージです。まだ設定数は少ないので、もっと活用できそうです。
□文章を作成する際に、誤変換したものを再変換するということがありますが、この機能はどうなっていますか。
＝⇒最初はまったく考えませんでしたが、そのようなニーズもでてきたので、取り入れ
ています。再変換したい文字のところにカーソルをおき、ワンタッチで処理します。文字入力ダイアログにカーソルのあった場所の単語が取り込まれ、変換キーを押すと再変換してくれます。エンターキーで文書に書き込まれ、修正完了となります。文字入力ダイアログが出ているので、文字を追加するとか修正するとか削除するとかも可能となります。
□ほしい文字列がうまく取り込まれないことも考えられますが。
＝⇒その通りです。そこで、指定した文字列を入力ダイアログに取り込み、それを再変換、修正するという機能も入れました(この機能はそれほど使われないので、他の機能を追加したことに伴い削除しました)。
□これらの操作もワンタッチなんですか。
＝⇒機能呼び出しはワンタッチです。後は変換キー、矢印キーとエンターキーです。
□プログラム作成用としての機能にはほかに何がありますか。
＝⇒プログラム作成用のことももちろん考えています。いくつかありますが、便利なのは指定範囲の行頭にコメント行にするために" ''"を挿入する機能です。これの逆の、" ''"をとるというものもあります。デバック行を使ってテストするときなど便利です。あとは、例えばイコールの前後をひっくり返すというのがあります。
pos_fl$[0] = @Filename$ の文を、=を中心にひっくり返します。次のようになります。スペースの問題はありますが、まあまあでしょう。
@Filename$=pos_fl$[0]
これも記号を複数設定できますので、"="だけでなく、自分のニーズに合ったものを設定できます。
また、変数名を取り込むというものもあります。DIM文を範囲指定して処理をします。変数名が取り込まれます。取り込まれた変数名は利用可能となります。最大40個ですので、その範囲で保有ができます。追加すると、先に保有されたものが削除されていきます。また、使った変数名は一番前に移動します。保有している変数名を全部書き出すことができます。これは変数名の保有が一過性であることからです。次にハイパーコピデルを動かし、書き出した変数名を取り込むと再現されます。書き出した変数名を直接修正できますので、不要なものを削除、必要なものを追加できます。この処理はワンタッチとはなりませんでした。もうキーがなくなってしまったからです。具体的には、F6、矢印キーで処理を指定、エンターキー、矢印キーなどで指定、エンターキーとなります。
□最後に、言っておきたい機能をどうぞ。
＝⇒このハイパーコピデルの有効性は、ケース･バイ･ケースです。つまり、その人のニーズいかんです。
それでは、最後として、2.5項目ほど取り上げます。
まずは、置換え関係です。検索もありますが、便利なのは置換のほうです。
このように使います。
まず、置き換えたい単語の先頭にカーソルを置きます。そしてワンタッチキー(Ｊ)です。すると、カーソルの単語が取得され、入力ダイアログがでてきます。これでよければそのままエンターキー、悪ければ修正してエンターキーです。すると、次は置換え後の文字を指定するダイアログが出ます。初期値は先ほどの置き換えたい文字列です。ここで修正をし、エンターキーです。指定範囲の状態になっているので矢印キーで範囲を設定しエンターキーです。するとその指定範囲内で置換えられます。これは便利です。特にマクロ(プログラム)作成の際です。同様な操作で検索及び検索の続行ができます。それぞれワンタッチです。これらあわせて1.5項目でしょうか。
最後は、このハイパーコピデルの欠点を補うものです。ハイパーコピデルのもとで処理をしているとき、やはり、何回かに1回は、ハイパーコピデルを動かしているのを忘れてしまい、文字を作成するためにキーをたたきます。すると、ハイパーコピデルでのワンタッチキーとなってしまうので、意図しない動きとなってしまいます。その際は、処理がどこへいっているのかわからないことがあるので、その場合は、右クリックからマクロの終了を選びます。
しかし、マクロを終了させるというも芸がありません。そこで、意図せず何かの処理をさせてしまった場合(Ｔを押すと一行削除になります)、処理を前に戻すという機能はどうでしょうか。
このケースだけでなく、その他の場合のためにも、処理を一回戻すという機能を入れています。あわせて、戻した機能を1回進めるというのも入れています。操作キー、矢印キーで処理を選択、エンターキーという操作です。これも、前回の処理が前に来ていますので、それを知っていると、操作が省略されて、操作キー、エンターキーのツータッチで可能です。
聞くところによると、操作を戻す際の一連の操作の定義することができます。複数の操作を一括して定義できるということです。それを定義すれば、個々の細かい処理がまとめられ、一瞬で元に戻ります。これには対応していませんが、機能を決めて対応すると、さらに便利になると思っています。
長くなりましたので、今回はおしまいにします。
2009年1月10日土曜日
第104回目 "どこでもコピデル"の大改良。ハイパーコピデルへの変身？
○第104回目 "どこでもコピデル"の大改良。ハイパーコピデルへの変身？
"どこでもコピデル"の大改良をしました。
その名も"スーパーコピデル"段階のものに引き上げた後で、さらに改良を行い"ハイパーコピデル"としました。
第101回目で紹介したものにいろいろな機能を追加しました。
マクロの構造は簡単言うと、追加した処理を新たなキーに割り付けるということです。
機能の全体像をまとめてみました。
******** ハイパーコピデル ********
※文字の入力機能はついていますが、大量に文字を入力するのには向きません(操作をひとつ必要なのと作成する文章の長さに制約があるので、40文字程度に区切って入れなければならないこと)。
※大量に入力した文字の第1回目の修正(主に誤字脱字の部分)には、それほど向いていません。
【基本情報】
'@トグルマップ…1
'@Hyper_CopyDell_Mojis.mac
【基本機能】
□コピー、削除、移動を行う
□機能の切り替えは、原則コピー(Ｃ)とし、削除はＤ、移動は＊(コピーと移動の切替)とする。移動後及び削除後はコピーモードに再セットする。
□貼付位置を指定し、対象文字列を選択しEnter(スペース、削除の場合はさらにDeleteでも可)で実行。
□コピーでコピー元から指定する逆指定…F8(F8は操作キー。以下同じ)
□指定の初期化…ESC
□ファイルのセーブ…Ｓ
□マクロの終了…F12。
【特殊コピー1】
□直前に貼付けたもの再度貼付…F4。。
□コマンド命令辞書からの貼付…F7。("ex"を入れると、"exit"、"exit for"、"exit do"などが表示されその中から選択し入力する)
□文字の挿入…Ｈ(キーボードからの挿入)
□一文字の挿入…Ｌ(キーボードからの挿入。基本的にはひらがな一文字。"ka"で"か"が入力される)
□キーボードから挿入した文字(MAX25)から選択し貼付…５。使用したもの順に並べ替えることにした。
□コピーした文字(MAX25)から選択し貼付…Ｑ。使用したもの順に並べ替えることにした。
□特殊文字の挿入…Ｎ(、, ,\t,。,＝⇒,=,である。,○,…,→,',",・,□)。使用したもの順に並べ替えることにした。
※これまでのもの(一文字挿入は除く)はすべて外部に出力されファイル化されている。次回に再利用可能となっている。
□日付の挿入…Ｉ
□TAB等(TABは初期値)の行頭への挿入…TAB
□かっこ( (『【《〈「" )で囲む…Ｕ。
□{ルビ }飾り文字等で囲む…Ｒ。中で範囲の後ろの位置を選択する。ルビ、上付き、下付き、太字、割注
【特殊コピー2】
□全文指定をコピー…Ａ
通常の場合は、先頭位置の指定の段階でＡ、ENTERをおす。逆指定方式も可能。
□区切り記号で囲まれてる範囲をコピー…Ｏ(行頭の○で囲まれた部分)または７(行頭の'で囲まれた部分)
□ブロック(箱型指定)コピー(移動、削除も可能)…８
□数値による行コピー…Ｘ(中で範囲(行)の後ろの位置を選択する)
□ ''の挿入…Ｙ(中で範囲(行)の後ろの位置を選択する)コマンドをコメント文にするためのもの
□ ''の削除…Ｚ
□文末までクリップボードへ貼付…Ｗ
【画面操作】
□矢印キー、Home、End、PageUp、PageDownが使用可能
□ウィンドウの移動…F1、F2キー
□見出し一覧からの移動…Ｍ
□今までの主な場所への移動…F9(別ウインドへも移動可能)
□文頭へ移動…１
□文末へ移動…２
【キー入力】
□改行…Ctrl
□一字削除…Deleteキー
□数字の入力…テンキー
□一行コピー…Ｂ
□一行削除…Ｔ
□文末まで削除…BackSpace
【特殊処理】
□指定された文書群を行単位で逆転する。移動も可能とした…Ｇ
□文字列の逆転。タブまたは指定文字列の前後を逆転…Ｅ(中で範囲(行)の後ろの位置を選択する) AAA = @Line を @Line = AAA にするときに使用する。
□空行、空白行の削除…Ｋ(中で範囲(行)の後ろの位置を選択する)
□文字罫線で囲む(一行が長い文字数でも可能)… Ｗ
□文字罫線を削除する…Ｖ(中で範囲(行)の後ろの位置を選択する)
□印刷指定…Ｐ(部分印刷可能、指定した数の見出しの範囲を印刷可能)
□文字の検索…Ｆ。指定してから実行(最後尾の変更はできる)。指定しない場合は、変更できる。
□文章一行単位でのソート…Ｗ。(指定範囲の文章をソートする)。
□指定文字の置換え…Ｊ。範囲を指定した場合は、最後尾の変更が可能。指定しない場合はカーソルのあるところの単語を取得するのでそれをもとに修正。置換え範囲はその後設定する。
□処理を一回戻す。一回進める。…０の中で選択
□文字の再変換… ４
□これまでの文章中の漢字等(単語)を取得し、それを貼付ける。… ３
"クリップボード"という文字を入れたい。この記事の中の"クリップボード"という文字があるので、それを利用(取得)して入れてしまう。
"どこでもコピデル"の大改良をしました。
その名も"スーパーコピデル"段階のものに引き上げた後で、さらに改良を行い"ハイパーコピデル"としました。
第101回目で紹介したものにいろいろな機能を追加しました。
マクロの構造は簡単言うと、追加した処理を新たなキーに割り付けるということです。
機能の全体像をまとめてみました。
******** ハイパーコピデル ********
※文字の入力機能はついていますが、大量に文字を入力するのには向きません(操作をひとつ必要なのと作成する文章の長さに制約があるので、40文字程度に区切って入れなければならないこと)。
※大量に入力した文字の第1回目の修正(主に誤字脱字の部分)には、それほど向いていません。
【基本情報】
'@トグルマップ…1
'@Hyper_CopyDell_Mojis.mac
【基本機能】
□コピー、削除、移動を行う
□機能の切り替えは、原則コピー(Ｃ)とし、削除はＤ、移動は＊(コピーと移動の切替)とする。移動後及び削除後はコピーモードに再セットする。
□貼付位置を指定し、対象文字列を選択しEnter(スペース、削除の場合はさらにDeleteでも可)で実行。
□コピーでコピー元から指定する逆指定…F8(F8は操作キー。以下同じ)
□指定の初期化…ESC
□ファイルのセーブ…Ｓ
□マクロの終了…F12。
【特殊コピー1】
□直前に貼付けたもの再度貼付…F4。。
□コマンド命令辞書からの貼付…F7。("ex"を入れると、"exit"、"exit for"、"exit do"などが表示されその中から選択し入力する)
□文字の挿入…Ｈ(キーボードからの挿入)
□一文字の挿入…Ｌ(キーボードからの挿入。基本的にはひらがな一文字。"ka"で"か"が入力される)
□キーボードから挿入した文字(MAX25)から選択し貼付…５。使用したもの順に並べ替えることにした。
□コピーした文字(MAX25)から選択し貼付…Ｑ。使用したもの順に並べ替えることにした。
□特殊文字の挿入…Ｎ(、, ,\t,。,＝⇒,=,である。,○,…,→,',",・,□)。使用したもの順に並べ替えることにした。
※これまでのもの(一文字挿入は除く)はすべて外部に出力されファイル化されている。次回に再利用可能となっている。
□日付の挿入…Ｉ
□TAB等(TABは初期値)の行頭への挿入…TAB
□かっこ( (『【《〈「" )で囲む…Ｕ。
□{ルビ }飾り文字等で囲む…Ｒ。中で範囲の後ろの位置を選択する。ルビ、上付き、下付き、太字、割注
【特殊コピー2】
□全文指定をコピー…Ａ
通常の場合は、先頭位置の指定の段階でＡ、ENTERをおす。逆指定方式も可能。
□区切り記号で囲まれてる範囲をコピー…Ｏ(行頭の○で囲まれた部分)または７(行頭の'で囲まれた部分)
□ブロック(箱型指定)コピー(移動、削除も可能)…８
□数値による行コピー…Ｘ(中で範囲(行)の後ろの位置を選択する)
□ ''の挿入…Ｙ(中で範囲(行)の後ろの位置を選択する)コマンドをコメント文にするためのもの
□ ''の削除…Ｚ
□文末までクリップボードへ貼付…Ｗ
【画面操作】
□矢印キー、Home、End、PageUp、PageDownが使用可能
□ウィンドウの移動…F1、F2キー
□見出し一覧からの移動…Ｍ
□今までの主な場所への移動…F9(別ウインドへも移動可能)
□文頭へ移動…１
□文末へ移動…２
【キー入力】
□改行…Ctrl
□一字削除…Deleteキー
□数字の入力…テンキー
□一行コピー…Ｂ
□一行削除…Ｔ
□文末まで削除…BackSpace
【特殊処理】
□指定された文書群を行単位で逆転する。移動も可能とした…Ｇ
□文字列の逆転。タブまたは指定文字列の前後を逆転…Ｅ(中で範囲(行)の後ろの位置を選択する) AAA = @Line を @Line = AAA にするときに使用する。
□空行、空白行の削除…Ｋ(中で範囲(行)の後ろの位置を選択する)
□文字罫線で囲む(一行が長い文字数でも可能)… Ｗ
□文字罫線を削除する…Ｖ(中で範囲(行)の後ろの位置を選択する)
□印刷指定…Ｐ(部分印刷可能、指定した数の見出しの範囲を印刷可能)
□文字の検索…Ｆ。指定してから実行(最後尾の変更はできる)。指定しない場合は、変更できる。
□文章一行単位でのソート…Ｗ。(指定範囲の文章をソートする)。
□指定文字の置換え…Ｊ。範囲を指定した場合は、最後尾の変更が可能。指定しない場合はカーソルのあるところの単語を取得するのでそれをもとに修正。置換え範囲はその後設定する。
□処理を一回戻す。一回進める。…０の中で選択
□文字の再変換… ４
□これまでの文章中の漢字等(単語)を取得し、それを貼付ける。… ３
"クリップボード"という文字を入れたい。この記事の中の"クリップボード"という文字があるので、それを利用(取得)して入れてしまう。
2009年1月2日金曜日
第103回目 QXマクロを使っての仕事(一例)
○第103回目 QXマクロを使っての仕事(一例)
現在、力を入れているのは、業務上のものも含めての辞典作りです。業務上のものは言及できませんので、エクセルマクロを作るための辞典作りを題材にします。
エクセルマクロの辞典ですから、エクセルマクロ命令の使用例を取りまとめています。行頭にある○でひとつの区切りとしています。
行頭以外で使われている○は、単なる記号とみなします。その○の以下に、コマンド、機能の説明、機能の検索キー、使用例などを記述します。
現在、重複もありますが、286項目、1400行となっています。この辞書の内容は、既存のマクロから引っ張ってきています。また、マクロのホームページからの引用もあります。
この辞書は、行頭の○から次の行頭の○までを1レコードとしてみなしています。ですから新しい情報は気にせずに追加していけばいいのです。
そして、ソートをかけることができるます(これはマクロ)。すると順番に並ぶので、通常機能の検索機能を使って、該当するところに移動し、その内容を見るということができます。これは説明文章の中の文字にも反応しますので、目的とするものにまず最初にはいきつくことはできません。
また、○が見出しに設定されているので、QXエディタで通常機能の見出し一覧を表示しそこから該当箇所に飛ぶこともできます。
次の使用方法は、検索文字を設定して、その文字のあるレコード(行頭の○から次の行頭の○まで)のみを、新しいファイルに抜き出すということができます(これはマクロです)。
例えば、"input"という言葉で抜き出してみると、次のようなものが出てきます。
(たくさんあるので一部のみ)
'---------------------------------------------------------------------
○inputbox関数
z_no0 = InputBox(msg01 & ii9 & " 以内", xpos:=1000, ypos:=2000, Default:=settei_ti0)
'---------------------------------------------------------------------
○シートの移動(シートは相対位置で指定します)
ii_max = ActiveWorkbook.Sheets.Count
ii0 = ActiveSheet.Index
sh_cnt = InputBox("グラフのあるシートの場所を入れてください。… -1 , 0 , 1 …など", Default:=-1, xpos:=2000, ypos:=3000)
Sheets(ii0 + sh_cnt).Select
'---------------------------------------------------------------------
○シートの移動1
Do
flag = 0
sh_cnt = InputBox("新しいシートの場所を入れてください。… -1 , 0 , 1 …など", Default:=1, xpos:=2000, ypos:=3000)
'シートオーバーの場合の歯止め、再入力
'---------------------------------------------------------------------
以下略。
この中から、使えそうな構文をコピーするということが可能となります。またこのファイルを基にして、さらに検索をかけることで、さらに絞り込むことも可能です。
自分の外に知識を移してそこへのつながりをしっかりしておくと、結構便利だと思います。辞典的なものは分量が多くなるので、そこから必要な部分のみをセレクトするという機能が備わっていないと、辞典というデータベースを作っただけになります。
このような辞書を活用してエクセルマクロを作ります。
次に、デバックに伴う修正ですが、まず、エクセルのマクロ用のモジュールをエクスポートします。そして、そのファイルをQXエディタで開くというマクロを作っておきます(通常操作では、拡張子basのファイルをQXエディタで開くように関連付けをして、エクスポートしたモジュールを開けばいいことになりますが、操作が何回か余計にかかります。そこで、マクロが登場するのですが、このマクロでは、二桁の数字を入力して、moduleNNを開くことができますので、拡張性があります。)。
直接修正するのが簡単な場合は、モジュール上で直接修正しますが、テキストエディタでの修正が便利な場合は、QXエディタで、そのモジュールを開くマクロを動かします。そして、QXエディタで修正を行います。
(クリップボードを経由した、通常の処理、すなわち、モジュールの中で、コピーをしその内容をQXエディタに貼付けるという操作でも同じようなことになります。こちらも操作が単純です。＝⇒モジュール上の操作で、Ctrl+上下の矢印(次のSubの先頭に移動)がありますが、その操作を知らないときに作ったので、この操作を知った今は、主に、クリップボード経由でQXエディタとの間のやり取りをしています)
QXエディタの中では、前々回紹介した"どこでもコピデル"も使いながら、修正していきます。
(QXエディタからエクセルマクロへの移植は、コピー･ペーストの手作業になってしまいます。これも自動化できるといいのですが、その方法が考え付きません)
また、基本コマンドについては、その頭文字からサーチできる短縮入力機能がQXエディタにはついているので、その機能(データは自分で作ります)も利用して入力します。こちらは文字数がかなり短く限定されているので、使用例も短く指定しています。
つまり、大きな機能の使用例を探すのは辞典を利用して、ちょっとした構文を探すのは短縮入力を利用するという感じです。
(QXエディタの短縮入力は、"見出し" TAB "用語など"という形式なっていて、見出しが複数の英文字で自動サーチできます。Enterを押すと、"用語など"が貼付けられます)
こう書いていたら、ひらめきました。
エクセルマクロはあるひとつのエクセルファイルの複数のモジュールに保存してあります。マクロを保存するファイルはひとつに特化しているということです。
バックアップとして、モジュールをエクスポートしています(拡張子は.bas)。
このbasはテキストファイルなので、関連付けをつけておけばQXエディタで開けます。
この一連のbasを一括結合してテキストファイルとして開く、というQXエディタのマクロを作ります。
これを動かすと、ほぼ全部のエクセルマクロが結合され、一つのファイルができます。これに検索をかけることができれば、コマンドの使用例としてはこれに勝るものはありません。
したがって、このファイルを用いて、こんどは行頭の'を区切り記号として、情報を抽出することのマクロがあればいいことになります。原型はすでにできてるので、○の代わりに、'を設定すればいいことになります。
これもひとつの辞典となります。区切りとしては適切に付加されているとはいえませんが、おおむねという感じで活用できそうです。
こういうことをやっていてつくづく思うのは、事務の7割がたがいわゆるワード系、筆者で言えばテキストエディタ系の作業になるのではないかということです。ここを攻める必要があると思います。
エクセルマクロのマクロといっても、ある場面においてはいいテキストエディタが重要である、といえると思います。そして、"読み、書き、そろばん"の"書き"の部分の役割としても、いいテキストエディタは必需品なのではないかと思っています。
皆様方においても、ワード一辺倒ではなく、テキストエディタのいいものを活用することが、目立たないことですが、事務の能率に大いに寄与するのではないかと、ひそかに思っています。
現在、力を入れているのは、業務上のものも含めての辞典作りです。業務上のものは言及できませんので、エクセルマクロを作るための辞典作りを題材にします。
エクセルマクロの辞典ですから、エクセルマクロ命令の使用例を取りまとめています。行頭にある○でひとつの区切りとしています。
行頭以外で使われている○は、単なる記号とみなします。その○の以下に、コマンド、機能の説明、機能の検索キー、使用例などを記述します。
現在、重複もありますが、286項目、1400行となっています。この辞書の内容は、既存のマクロから引っ張ってきています。また、マクロのホームページからの引用もあります。
この辞書は、行頭の○から次の行頭の○までを1レコードとしてみなしています。ですから新しい情報は気にせずに追加していけばいいのです。
そして、ソートをかけることができるます(これはマクロ)。すると順番に並ぶので、通常機能の検索機能を使って、該当するところに移動し、その内容を見るということができます。これは説明文章の中の文字にも反応しますので、目的とするものにまず最初にはいきつくことはできません。
また、○が見出しに設定されているので、QXエディタで通常機能の見出し一覧を表示しそこから該当箇所に飛ぶこともできます。
次の使用方法は、検索文字を設定して、その文字のあるレコード(行頭の○から次の行頭の○まで)のみを、新しいファイルに抜き出すということができます(これはマクロです)。
例えば、"input"という言葉で抜き出してみると、次のようなものが出てきます。
(たくさんあるので一部のみ)
'---------------------------------------------------------------------
○inputbox関数
z_no0 = InputBox(msg01 & ii9 & " 以内", xpos:=1000, ypos:=2000, Default:=settei_ti0)
'---------------------------------------------------------------------
○シートの移動(シートは相対位置で指定します)
ii_max = ActiveWorkbook.Sheets.Count
ii0 = ActiveSheet.Index
sh_cnt = InputBox("グラフのあるシートの場所を入れてください。… -1 , 0 , 1 …など", Default:=-1, xpos:=2000, ypos:=3000)
Sheets(ii0 + sh_cnt).Select
'---------------------------------------------------------------------
○シートの移動1
Do
flag = 0
sh_cnt = InputBox("新しいシートの場所を入れてください。… -1 , 0 , 1 …など", Default:=1, xpos:=2000, ypos:=3000)
'シートオーバーの場合の歯止め、再入力
'---------------------------------------------------------------------
以下略。
この中から、使えそうな構文をコピーするということが可能となります。またこのファイルを基にして、さらに検索をかけることで、さらに絞り込むことも可能です。
自分の外に知識を移してそこへのつながりをしっかりしておくと、結構便利だと思います。辞典的なものは分量が多くなるので、そこから必要な部分のみをセレクトするという機能が備わっていないと、辞典というデータベースを作っただけになります。
このような辞書を活用してエクセルマクロを作ります。
次に、デバックに伴う修正ですが、まず、エクセルのマクロ用のモジュールをエクスポートします。そして、そのファイルをQXエディタで開くというマクロを作っておきます(通常操作では、拡張子basのファイルをQXエディタで開くように関連付けをして、エクスポートしたモジュールを開けばいいことになりますが、操作が何回か余計にかかります。そこで、マクロが登場するのですが、このマクロでは、二桁の数字を入力して、moduleNNを開くことができますので、拡張性があります。)。
直接修正するのが簡単な場合は、モジュール上で直接修正しますが、テキストエディタでの修正が便利な場合は、QXエディタで、そのモジュールを開くマクロを動かします。そして、QXエディタで修正を行います。
(クリップボードを経由した、通常の処理、すなわち、モジュールの中で、コピーをしその内容をQXエディタに貼付けるという操作でも同じようなことになります。こちらも操作が単純です。＝⇒モジュール上の操作で、Ctrl+上下の矢印(次のSubの先頭に移動)がありますが、その操作を知らないときに作ったので、この操作を知った今は、主に、クリップボード経由でQXエディタとの間のやり取りをしています)
QXエディタの中では、前々回紹介した"どこでもコピデル"も使いながら、修正していきます。
(QXエディタからエクセルマクロへの移植は、コピー･ペーストの手作業になってしまいます。これも自動化できるといいのですが、その方法が考え付きません)
また、基本コマンドについては、その頭文字からサーチできる短縮入力機能がQXエディタにはついているので、その機能(データは自分で作ります)も利用して入力します。こちらは文字数がかなり短く限定されているので、使用例も短く指定しています。
つまり、大きな機能の使用例を探すのは辞典を利用して、ちょっとした構文を探すのは短縮入力を利用するという感じです。
(QXエディタの短縮入力は、"見出し" TAB "用語など"という形式なっていて、見出しが複数の英文字で自動サーチできます。Enterを押すと、"用語など"が貼付けられます)
こう書いていたら、ひらめきました。
エクセルマクロはあるひとつのエクセルファイルの複数のモジュールに保存してあります。マクロを保存するファイルはひとつに特化しているということです。
バックアップとして、モジュールをエクスポートしています(拡張子は.bas)。
このbasはテキストファイルなので、関連付けをつけておけばQXエディタで開けます。
この一連のbasを一括結合してテキストファイルとして開く、というQXエディタのマクロを作ります。
これを動かすと、ほぼ全部のエクセルマクロが結合され、一つのファイルができます。これに検索をかけることができれば、コマンドの使用例としてはこれに勝るものはありません。
したがって、このファイルを用いて、こんどは行頭の'を区切り記号として、情報を抽出することのマクロがあればいいことになります。原型はすでにできてるので、○の代わりに、'を設定すればいいことになります。
これもひとつの辞典となります。区切りとしては適切に付加されているとはいえませんが、おおむねという感じで活用できそうです。
こういうことをやっていてつくづく思うのは、事務の7割がたがいわゆるワード系、筆者で言えばテキストエディタ系の作業になるのではないかということです。ここを攻める必要があると思います。
エクセルマクロのマクロといっても、ある場面においてはいいテキストエディタが重要である、といえると思います。そして、"読み、書き、そろばん"の"書き"の部分の役割としても、いいテキストエディタは必需品なのではないかと思っています。
皆様方においても、ワード一辺倒ではなく、テキストエディタのいいものを活用することが、目立たないことですが、事務の能率に大いに寄与するのではないかと、ひそかに思っています。
登録:
コメント (Atom)
