○第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))
これには、お手上げです。
しばらく時間を…ということにします。
次回までに考えてみてください。
2009年8月30日日曜日
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関数の平均額となります。
これを通常のコピーで、他のたて計のセルに計算式をコピーします。
登録:
コメント (Atom)
