○第46回目 条件式の作り方
第43回でとり上げたものを題材にします。
『エクセルマクロから変数を取り出すマクロの説明；
これを最終行まで繰り返しますが、問題は変数名ではないものまでが入っているということです。一例を挙げれば、数値です。あとは、変数ではあるが、外の場所に記述されているもの、カッコ内のものの一部です。
i 0 gyo_cnt 2 ←"0","2"は数値
p1 (AAA） ←"(AAA)"はほかの場所で必ず出てくるもの
AAA1 (AAA p1 1) ←"(AAA"、"1)"はカッコ内の一部。
← "ｐ1"はカッコ内の一部ですが、その判断ができないので、生かされます。
dd2(i） i ← "dd2(i)"は配列変数なので、カッコ内を100にします。』
とあります。
変数名の候補から、上の条件で候補を絞ったり、配列変数に加工を施します。
条件を書いていきますと、
1.先頭が数字であるものは、対象外
2.先頭が"("であるものは、対象外
3.途中に"("がなく、最後が")"であるものは対象外
4.途中に"("があり、最後が")"でないものは対象外
5.途中に"("があり、最後が")"であるものは配列の変数
6.その他のものは、通常の変数
ここまででだいたい80％完成です。
後は構成と、機械的な翻訳です
その他があるので、順番にやっていくしかなそうです。
変数名の候補を、a$ とします。
(記述はQXマクロに準じています)
1. Left(a$,1) >= "0" and Left(a$,1） <= "9" は対象外
2. Left(a$,1) = "(" は対象外
3. instr(a$,"(") = 0 and right(a$,1） = ")" は対象外
4. instr(a$,"(") <> 0 and right(a$,1） <> ")" は対象外
5. instr(a$,"(") <> 0 and right(a$,1) = ")" は対象
6. その他は は対象
となります。
これをif文で組立てればいいのです。
1-4までは、対象外なので逆の条件を書きます。
if Left(a$,1) < "0" or Left(a$,1) > "9" then
if Left(a$,1) <> "(" then
if instr(a$,"(") <> 0 or right(a$,1) <> ")" then
if instr(a$,"(") = 0 or right(a$,1) = ")" then
if instr(a$,"(") <> 0 or right(a$,1) = ")" then
5の処理
else
6の処理
これを段ずれさせ、end ifをifの数だけ追加します。
if Left(a$,1) < "0" or Left(a$,1) > "9" then
if Left(a$,1) <> "(" then
if instr(a$,"(") <> 0 or right(a$,1) <> ")" then
if instr(a$,"(") = 0 or right(a$,1) = ")" then
if instr(a$,"(") <> 0 or right(a$,1) = ")" then
5の処理
else
6の処理
end if
end if
end if
end if
end if
となります。これで完成です。
ということで、最初の段階の言葉による条件の書きだしが、だいたい80%を占めているという意味がおわかりかと思います。
いろいろなミスは避けられませんので、チェックは十分必要です。
練習問題
変数名と思われるものを抜き出しました。これを更に分類してみましょう。
分類に基づき、変数名の頭にll01とかを付加していきます。
変数名の分類する。配列変数(ll01を付加)、文字の配列変数(ll02を付加)、文字用変数(ll03を付加)、間に"_"があるもの(ll04を付加)、一文字のもの(ll05を付加)、二文字のもの(ll06を付加)、定数(ll19を付加)、その他(ll07を付加)
文字の配列変数…変数配列であり、どこかに"$"がある。(文字変数に$をつけるといいうのは、エクセルマクロでは廃れた記述方法ですが…)
文字用変数…最後に"$"がある。
定数…頭の二文字が、ll、lc、lrのいずれかに該当する
頭に付加する処理は、mm1$(i) = "llxx" & mm1$(i) となります。
これまでの課題と違うところは、対象外のケースがなく、すべての場合に必要な処理を行なうということです。
2007年9月30日日曜日
2007年9月25日火曜日
第45回目 エクセルのファイルを分析する(その6-2)
○第45回目 エクセルのファイルを分析する(その6-2)
シートの移動です。
今回はシートを後ろに動かす場合です。
n_new(i)《=更新後の順番》 > n_gen(i)《=現在の順番》 が後ろに動かす場合です。
この場合、
1.n_gen(i)-1までのシートの順番は変わりません
2.n_gen(i)を含めてn_new(i)-1までのシートの順番は-1となります
3.n_gen(i)のシートの順番はn_new(i)となります
4.n_new(i)+1からのシートの順番は変わりません
となります。
・各条件を単純に書いていきます。
1. n_gen(j) <= n_gen(i)-1 変わりません
4.n_new(i)+1 <= n_gen(j) 変わりません
2.n_gen(i)+1 <= n_gen(j) <= n_new(i) -1となります
3. n_gen(j) = n_gen(i) n_new(i)となります
・1または4の条件の反対(2または3である条件)を書きます。
if (n_gen(j) > n_gen(i)-1) and (n_new(i)+1 > n_gen(j)) then
:
end if
・2の条件を書きます。
if (n_gen(i)+1 <= n_gen(j)) and (n_gen(j) <= n_new(i)) then
2の処理
else
3の処理
end if
まとめると、
if (n_gen(j) > n_gen(i)-1) and (n_new(i)+1 > n_gen(j)) then
if (n_gen(i)+1 <= n_gen(j)) and (n_gen(j) <= n_new(i)) then
2の処理
else
3の処理
end if
end if
また、別の書き方としては、3の処理がただ一つの番号だけであることに注目して、for の外に出してしまうのです。
For j = 0 To gyo_cnt - 1
2の処理だけを書く(if文で2に限定する)
next j
3の処理(n_new(i) = n_gen(i))(if文で3に限定する)
こちらのほうが、場合の手としてですが簡潔ですね。
全体的に整理しました。
'シートの移動
For i = 0 To gyo_cnt - 1
'前に移動
If n_new(i) <>
省略
Else
'後ろに移動
If n_new(i) > n_gen(i) Then
Sheets(n_gen(i)).Move after:=Sheets(n_new(i))
For j = 0 To gyo_cnt - 1
If n_gen(i) + 1 <= n_gen(j) And n_gen(j) <= n_new(i) Then
n_gen(j) = n_gen(j) - 1
End If
Next
n_gen(i) = n_new(i)
End If
End If
Next
最後はおまじないのシートの削除です。シート削除了解の確認のメッセージはだしません。無条件削除となります。
'マジナイのシートを削除。確認メッセージを出さない
Application.DisplayAlerts = False
Sheets(1).Delete
Application.DisplayAlerts = True '元の状態に戻しておく
これでシートの移動は完成です。
※更なる改良。
新しい順番を考える時、最初から位置からの順番が決まるわけではありません。
これとこれはこっちが前だなとか、色々考えて順番の大小関係を決めて、それをもとに1からの順番が出来るのです。そこで、その大小関係を記述した段階のものでマクロを動かすというものです。
マクロの中では、それぞれのシートの数値の大小をソートして、1からの連番を求めます。後は現在のマクロの機能でシートを移動させるということです。この処理を追加してみてはいかがでしょうか。
更には、現在のシートの順番が入っていると思われるD列もそうなっているかわかりません。単なる大小関係で記述されているかもしれません(例えば、四桁の数値の大小関係で順番を決めている)。
この点も考慮すると完成度があがります。
"エクセルのファイルを分析する"のテーマについては一応終了です。
ひとこと
このテーマは、日々更新していくことが必要ですね。
特にシートの中の分析は、色々と試行錯誤を繰り返すべきでしょう。その中で出てきた必要な機能をマクロで組んでみる、という繰り返しをし完成度を高くしていくものと思います。
シートの移動です。
今回はシートを後ろに動かす場合です。
n_new(i)《=更新後の順番》 > n_gen(i)《=現在の順番》 が後ろに動かす場合です。
この場合、
1.n_gen(i)-1までのシートの順番は変わりません
2.n_gen(i)を含めてn_new(i)-1までのシートの順番は-1となります
3.n_gen(i)のシートの順番はn_new(i)となります
4.n_new(i)+1からのシートの順番は変わりません
となります。
・各条件を単純に書いていきます。
1. n_gen(j) <= n_gen(i)-1 変わりません
4.n_new(i)+1 <= n_gen(j) 変わりません
2.n_gen(i)+1 <= n_gen(j) <= n_new(i) -1となります
3. n_gen(j) = n_gen(i) n_new(i)となります
・1または4の条件の反対(2または3である条件)を書きます。
if (n_gen(j) > n_gen(i)-1) and (n_new(i)+1 > n_gen(j)) then
:
end if
・2の条件を書きます。
if (n_gen(i)+1 <= n_gen(j)) and (n_gen(j) <= n_new(i)) then
2の処理
else
3の処理
end if
まとめると、
if (n_gen(j) > n_gen(i)-1) and (n_new(i)+1 > n_gen(j)) then
if (n_gen(i)+1 <= n_gen(j)) and (n_gen(j) <= n_new(i)) then
2の処理
else
3の処理
end if
end if
また、別の書き方としては、3の処理がただ一つの番号だけであることに注目して、for の外に出してしまうのです。
For j = 0 To gyo_cnt - 1
2の処理だけを書く(if文で2に限定する)
next j
3の処理(n_new(i) = n_gen(i))(if文で3に限定する)
こちらのほうが、場合の手としてですが簡潔ですね。
全体的に整理しました。
'シートの移動
For i = 0 To gyo_cnt - 1
'前に移動
If n_new(i) <>
省略
Else
'後ろに移動
If n_new(i) > n_gen(i) Then
Sheets(n_gen(i)).Move after:=Sheets(n_new(i))
For j = 0 To gyo_cnt - 1
If n_gen(i) + 1 <= n_gen(j) And n_gen(j) <= n_new(i) Then
n_gen(j) = n_gen(j) - 1
End If
Next
n_gen(i) = n_new(i)
End If
End If
Next
最後はおまじないのシートの削除です。シート削除了解の確認のメッセージはだしません。無条件削除となります。
'マジナイのシートを削除。確認メッセージを出さない
Application.DisplayAlerts = False
Sheets(1).Delete
Application.DisplayAlerts = True '元の状態に戻しておく
これでシートの移動は完成です。
※更なる改良。
新しい順番を考える時、最初から位置からの順番が決まるわけではありません。
これとこれはこっちが前だなとか、色々考えて順番の大小関係を決めて、それをもとに1からの順番が出来るのです。そこで、その大小関係を記述した段階のものでマクロを動かすというものです。
マクロの中では、それぞれのシートの数値の大小をソートして、1からの連番を求めます。後は現在のマクロの機能でシートを移動させるということです。この処理を追加してみてはいかがでしょうか。
更には、現在のシートの順番が入っていると思われるD列もそうなっているかわかりません。単なる大小関係で記述されているかもしれません(例えば、四桁の数値の大小関係で順番を決めている)。
この点も考慮すると完成度があがります。
"エクセルのファイルを分析する"のテーマについては一応終了です。
ひとこと
このテーマは、日々更新していくことが必要ですね。
特にシートの中の分析は、色々と試行錯誤を繰り返すべきでしょう。その中で出てきた必要な機能をマクロで組んでみる、という繰り返しをし完成度を高くしていくものと思います。
2007年9月22日土曜日
第44回目 エクセルのファイルを分析する(その6)
○第44回目 エクセルのファイルを分析する(その6)
"エクセルのファイルの分析"の最後に、一つのシート名等の追記及びシートの並べ替えを考えます。
シートを追加しました。その際、シートの目次を更新する必要があります。その対応です。
手動で、行を挿入します。そこに、該当するシート番号のシート名等を追記するものです。基本は、第35回(第36回で改修した後のもの)のものを原形として作成します。
"すべてのシートのシート名等"であるものを"指定するシート名等"に変更すればいいのです。
シートの指定は、シート番号で行なうのがいいでしょう。
・該当するシート番号の入力
・そのシート名、リンク、コメントを貼り付ける
となります。
Sheets(i).selectでシートを特定してから、シート名等を取得し、貼り付けてください。
次のテーマです。
色々作業を行なっていると、シートの順番を変えたほうが整理上いいということが起こります。
それに対応するものですが、与えるデータは単純なものを考えます。
それは、シートごとに、現シートの順番(1から)と新しいシートの順番の対応させたデータです。
1＝⇒1、2＝⇒2、3＝⇒6、4＝⇒3、5＝⇒4、6＝⇒5というようにです。(シート数6個の場合)
いままでの処理では、D列はシートの番号、E列は作業域となっていますので、そのE列に新しい順番を入れていきます。この2列を指定します。
指定した後からマクロを動かすとします。
指定範囲の座標の取得は毎度おなじみですね。
'座標の取得
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 i = 0 To gyo_cnt - 1
n_gen(i) = cells(gyo1+i,retu1) + 1 '現在のシート番号
n_new(i) = cells(gyo1+i,retu2) + 1 '新しいシート番号
Next i
プラス1はおまじないです。
'おマジナイのシートを追加。一番最初に追加されます。
Sheets(1).Select
Sheets.Add.Name = "Dummy"
これからが眼目です。
シートの移動は、
Sheets(2).Move after:=Sheets(4) です。
これを動かすとシートの内容は、
1.2.3.4.5.6から、1.3.4.2.5.6となります
また、元に戻してから
Sheets(6).Move after:=Sheets(4) を動かすと、
1.2.3.4.5.6から、1.2.3.4.6.5となります。動き方がちょこっと違います。
また、一番先頭にシートを動かすには、この構文では無理です。
そこで、おまじないのシートを先頭に追加しました。
Sheets(6).Move after:=Sheets(1) を動かすと、
1.2.3.4.5.6.7から、1.6.2.3.4.5.7となります。同じ構文で可能になります。
そして、おまじないのシートは一番最後に跡形もなく消します。
具体的な処理は、一つのシートを置き換えていき、その他のシートの番号を新しい順番に更新していきます。そして、次ぎの置換えを行います、という繰り返しとなります。繰り返しの回数はgyo_cnt回となります。
考えていると頭がこんがらがってきます。
処理をシートを前に動かす場合、後ろに動かす場合とに分けて考えます。
Sheets(2).Move after:=Sheets(4)は、前者の例で、
Sheets(6).Move after:=Sheets(4)は、後者の例です。
n_new(i)《=更新後の順番》 < j =" 0">、< ⇒ =>、>= ⇒ <、> ⇒ <=、= ⇒ <> 、<> ⇒ =
"1でない"、かつ、"4でない"
if (n_gen(j) > n_new(i)-1) and (n_gen(i)+1 > n_gen(j)) then
:
end if
・2を逆にしてみましょう。
もとの条件は
(n_new(i) <= n_gen(j)) and (n_gen(j) <= n_gen(i)-1) となっています。これを単純に変えて、かつ、andの処理をすればいいのです。 and と orはお互いに反対になっていますので、and は or へ、or は andとなります。 したがって、 (n_new(i) > n_gen(j)) or (n_gen(j) > n_gen(i)-1)
となります。
これを使うと、次のようになります。
if (n_new(i) > n_gen(j)) or (n_gen(j) > n_gen(i)-1) then
3の処理
else
2の処理
end if
しかし、普通の書き方は次ぎのようになります。
if (n_new(i) <= n_gen(j)) and (n_gen(j) <= n_gen(i)-1) then 2の処理 else 3の処理 end if まとめると、 if (n_gen(j) > n_new(i)-1) or (n_gen(i)+1 > n_gen(j)) then '1と4の場合は末尾のend ifに飛び、ここでは何の処理もされない
if (n_new(i) <= n_gen(j)) and (n_gen(j) <= n_gen(i)-1) then
2の処理
else
3の処理
end if
end if
頭を酷使しましたので、ここまでとします。
"エクセルのファイルの分析"の最後に、一つのシート名等の追記及びシートの並べ替えを考えます。
シートを追加しました。その際、シートの目次を更新する必要があります。その対応です。
手動で、行を挿入します。そこに、該当するシート番号のシート名等を追記するものです。基本は、第35回(第36回で改修した後のもの)のものを原形として作成します。
"すべてのシートのシート名等"であるものを"指定するシート名等"に変更すればいいのです。
シートの指定は、シート番号で行なうのがいいでしょう。
・該当するシート番号の入力
・そのシート名、リンク、コメントを貼り付ける
となります。
Sheets(i).selectでシートを特定してから、シート名等を取得し、貼り付けてください。
次のテーマです。
色々作業を行なっていると、シートの順番を変えたほうが整理上いいということが起こります。
それに対応するものですが、与えるデータは単純なものを考えます。
それは、シートごとに、現シートの順番(1から)と新しいシートの順番の対応させたデータです。
1＝⇒1、2＝⇒2、3＝⇒6、4＝⇒3、5＝⇒4、6＝⇒5というようにです。(シート数6個の場合)
いままでの処理では、D列はシートの番号、E列は作業域となっていますので、そのE列に新しい順番を入れていきます。この2列を指定します。
指定した後からマクロを動かすとします。
指定範囲の座標の取得は毎度おなじみですね。
'座標の取得
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 i = 0 To gyo_cnt - 1
n_gen(i) = cells(gyo1+i,retu1) + 1 '現在のシート番号
n_new(i) = cells(gyo1+i,retu2) + 1 '新しいシート番号
Next i
プラス1はおまじないです。
'おマジナイのシートを追加。一番最初に追加されます。
Sheets(1).Select
Sheets.Add.Name = "Dummy"
これからが眼目です。
シートの移動は、
Sheets(2).Move after:=Sheets(4) です。
これを動かすとシートの内容は、
1.2.3.4.5.6から、1.3.4.2.5.6となります
また、元に戻してから
Sheets(6).Move after:=Sheets(4) を動かすと、
1.2.3.4.5.6から、1.2.3.4.6.5となります。動き方がちょこっと違います。
また、一番先頭にシートを動かすには、この構文では無理です。
そこで、おまじないのシートを先頭に追加しました。
Sheets(6).Move after:=Sheets(1) を動かすと、
1.2.3.4.5.6.7から、1.6.2.3.4.5.7となります。同じ構文で可能になります。
そして、おまじないのシートは一番最後に跡形もなく消します。
具体的な処理は、一つのシートを置き換えていき、その他のシートの番号を新しい順番に更新していきます。そして、次ぎの置換えを行います、という繰り返しとなります。繰り返しの回数はgyo_cnt回となります。
考えていると頭がこんがらがってきます。
処理をシートを前に動かす場合、後ろに動かす場合とに分けて考えます。
Sheets(2).Move after:=Sheets(4)は、前者の例で、
Sheets(6).Move after:=Sheets(4)は、後者の例です。
n_new(i)《=更新後の順番》 < j =" 0">、< ⇒ =>、>= ⇒ <、> ⇒ <=、= ⇒ <> 、<> ⇒ =
"1でない"、かつ、"4でない"
if (n_gen(j) > n_new(i)-1) and (n_gen(i)+1 > n_gen(j)) then
:
end if
・2を逆にしてみましょう。
もとの条件は
(n_new(i) <= n_gen(j)) and (n_gen(j) <= n_gen(i)-1) となっています。これを単純に変えて、かつ、andの処理をすればいいのです。 and と orはお互いに反対になっていますので、and は or へ、or は andとなります。 したがって、 (n_new(i) > n_gen(j)) or (n_gen(j) > n_gen(i)-1)
となります。
これを使うと、次のようになります。
if (n_new(i) > n_gen(j)) or (n_gen(j) > n_gen(i)-1) then
3の処理
else
2の処理
end if
しかし、普通の書き方は次ぎのようになります。
if (n_new(i) <= n_gen(j)) and (n_gen(j) <= n_gen(i)-1) then 2の処理 else 3の処理 end if まとめると、 if (n_gen(j) > n_new(i)-1) or (n_gen(i)+1 > n_gen(j)) then '1と4の場合は末尾のend ifに飛び、ここでは何の処理もされない
if (n_new(i) <= n_gen(j)) and (n_gen(j) <= n_gen(i)-1) then
2の処理
else
3の処理
end if
end if
頭を酷使しましたので、ここまでとします。
2007年9月19日水曜日
第43回目 ちょっと寄道の２
○第43回目 ちょっと寄道の２
前回説明したように、エクセルマクロから命令等不要な部分を削除します。
そこから、変数を抜き出すというマクロです。
変数は、
半角スペース+変数名+半角スペース＋変数名+半角スペース…、となっていて変数名の数はわかりませんが、最後は半角スペースで終わっています。
一部を取り出すと次のようになっています(末尾の半角スペースが削除されています。自動設定をしているために、保存すると末尾スペースが削除されてしまうからです)。
AAA
p1 (AAA )
AAA1 (AAA p1 1)
retu1 (AAA1)
gyo1 (AAA1)
AAA9 (AAA p1 1)
retu9 (AAA9)
gyo9 (AAA9)
gyo_cnt gyo9 gyo1 1
retu_cnt retu9 retu1 1
k 0 1
i 0 gyo_cnt 1
dd1(i) (gyo1 i retu1 k)
dd2(i) i
i
i 0 gyo_cnt 2
j i 1 gyo_cnt 1
dd1(j) dd1(i)
AA dd1(j)
dd1(j) dd1(i)
dd1(i) AA
bb dd2(j)
dd2(j) dd2(i)
dd2(i) bb
：
これから、
Dim AA,AAA,AAA1,AAA9,bb
Dim dd1(100),dd2(100),dd3(100),gyo1,gyo9
Dim gyo_cnt,i,j,k,n_gen(100)
Dim n_new(100),n_old(100),p1,retu1,retu9
Dim retu_cnt,
というものを抜き出すのです。配列定義の(100)は、一意的に(100)にしているものです。
ソートをかけていますので、配列変数と通常の変数が混在しています。
マクロを見て行きます。
全文対象なので、一行目から最後まで同じことを繰り返します。
最初の段階では重複は削除しません。
変数名はスペースに区切られていますので、半角スペースを探していきます。二回探して始まりと終わりの位置をとります。
変数名を取り終わったら、配列変数に格納して、格納した変数までを削除したもの(スペースは残す)で同じように探していきます。スペースが二つなければその行は終了です。
これを最終行まで繰り返しますが、問題は変数名ではないものまでが入っているということです。一例を挙げれば、数値です。あとは、変数ではあるが、外の場所に記述されているもの、カッコ内のものの一部です。
i 0 gyo_cnt 2 ←"0","2"は数値
p1 (AAA) ←"(AAA)"はほかの場所で必ず出てくるもの
AAA1 (AAA p1 1) ←"(AAA"、"1)"はカッコ内の一部。
← "ｐ1"はカッコ内の一部ですが、その判断ができないので、生かされます。
dd2(i) i ← "dd2(i)"は配列変数なので、カッコ内を100にします。
取得した変数名をソートし、重複部分を除きます。
そして、五こずつ、Dimに続けて表示します。
流れはこんなものです。
具体的に見てみましょう。(これはQXマクロです)
'初めのおまじない
if @hwnd = 0 then exit proc
'選択範囲を記憶
y_begin = @SelectStartLine 'これは物理的行数
y_end = @ScrLineToCrLine(@SelectEndLine) 'これは論理的行数
@BlockSelectEnd
if y_begin = 0 then
y_begin = 1 'これは物理的行数
y_end = @ScrLineToCrLine(@LastLine) 'これは論理的行数
end if
'メイン処理
'指定された範囲の中における、変数を持つ文字列を抜き出す。
'形式は、半角スペース+変数十半角スペースとなっている
@Line = y_begin
i= 0
do ＝⇒このdoは指定範囲内で繰り返すという意味のもの
a$ = @textCr$(@Line) ＝⇒一行の内容を変数に入れる
b$ = a$
do while 1 ＝⇒一行の中から変数名を抜き出す。変数名の数がわからないので、無限ループになっている。
rr$ = " "
p1 = instr(b$,rr$) ＝⇒rr$は半角スペースのこと。一つ目のスペースの位置取得
p2 = instr(mid$(b$,p1+1),rr$) ＝⇒二つ目のスペースの一取得。p2は最初のスペースは除いた位置である。変数名の長さ+1となる
if p2 = 0 then exit do '抜け出る
c$= left$(mid$(b$,p1+1,p2-1),1) ＝⇒抜き出した変数名候補の先頭の文字を取得
d$= right$(mid$(b$,p1+1,p2-1),1) ＝⇒抜き出した変数名候補の最後の文字を取得
if c$ < "0" or c$ > "9" then ' 0<= c$ <=9 は除外 ＝⇒数値は除く
if c$ <> "(" then 'c$ = ( は除外 ＝⇒"("で始まっているものは除く
p3 = instr(mid$(b$,p1+1,p2-1),"(")
if p3 = 0 or d$ = ")" then '途中に( があり、かつ、末尾が ) でない は除外
if p3 <> 0 or d$ <> ")" then '途中に( がなく、かつ、末尾が ) である は除外
if p3 <> 0 and d$ = ")" then '途中に( がある、かつ、末尾が ) である
mm1$[i] = left$(mid$(b$,p1+1,p2-1),p3) & "100)" ＝⇒配列変数なので、配列数を一意的に100とする。
i = i + 1
else '途中に( がなく、または、末尾が ) でない
mm1$[i] = mid$(b$,p1+1,p2-1)
i = i + 1
end if
end if
end if
end if
end if
b$ = mid$(b$,p2)
loop
@MoveNextLineCr '改行し次の行へ
loop while @ScrLineToCrLine(@Line) < y_end
m_end = i-1 '定数の個数(0を含める)
if m_end = 0 then exit proc
'ソート
for i = 0 to m_end-1 'ソート
min$=mm1$[i]
for j = i+1 to m_end
if min$ > mm1$[j] then
min$ = mm1$[j]
mm1$[j] = mm1$[i]
mm1$[i] = min$
end if
next
next
'重複した場合クリアする
for i = 0 to m_end-1
for j = i+1 to m_end
if mm1$[i] <> mm1$[j] then exit for
mm1$[j] = ""
next
next
'重複の削除。つめる。
j = 0
for i = 0 to m_end
if mm1$[i] <> "" then
mm1$[i-j] = mm1$[i]
else
j=j+1
end if
next
m_end2 = m_end - j '最終的な定数の数
'末尾に追加。五単位で改行。間はカンマで区切る
@InsertF "\n" ＝⇒改行命令
@InsertF "\n"
i=0
@Insert "Dim "
for m= 0 to m_end2
i=i+1
if i= ll005 then
@Insert mm1$[m]
@InsertF "\n"
@Insert "Dim "
i=0
else
@Insert mm1$[m]
@Insert ","
end if
next
@InsertF "\n"
@InsertF "\n"
※これはQXマクロである。
※今後の改修点
・QXマクロプログラムでも変数を抜き出せるようにする。(削除表(変換表)の作りで終了)
・変数の種類別に、変数名を分類し、その順番に出力する。配列変数、文字用変数、その他の分類
・その他の更なる分類。一文字のもの、二文字のもの、間に"_"があるもの、その他
こんなところを改善点として考えています。
次回から本道に戻ります。
前回説明したように、エクセルマクロから命令等不要な部分を削除します。
そこから、変数を抜き出すというマクロです。
変数は、
半角スペース+変数名+半角スペース＋変数名+半角スペース…、となっていて変数名の数はわかりませんが、最後は半角スペースで終わっています。
一部を取り出すと次のようになっています(末尾の半角スペースが削除されています。自動設定をしているために、保存すると末尾スペースが削除されてしまうからです)。
AAA
p1 (AAA )
AAA1 (AAA p1 1)
retu1 (AAA1)
gyo1 (AAA1)
AAA9 (AAA p1 1)
retu9 (AAA9)
gyo9 (AAA9)
gyo_cnt gyo9 gyo1 1
retu_cnt retu9 retu1 1
k 0 1
i 0 gyo_cnt 1
dd1(i) (gyo1 i retu1 k)
dd2(i) i
i
i 0 gyo_cnt 2
j i 1 gyo_cnt 1
dd1(j) dd1(i)
AA dd1(j)
dd1(j) dd1(i)
dd1(i) AA
bb dd2(j)
dd2(j) dd2(i)
dd2(i) bb
：
これから、
Dim AA,AAA,AAA1,AAA9,bb
Dim dd1(100),dd2(100),dd3(100),gyo1,gyo9
Dim gyo_cnt,i,j,k,n_gen(100)
Dim n_new(100),n_old(100),p1,retu1,retu9
Dim retu_cnt,
というものを抜き出すのです。配列定義の(100)は、一意的に(100)にしているものです。
ソートをかけていますので、配列変数と通常の変数が混在しています。
マクロを見て行きます。
全文対象なので、一行目から最後まで同じことを繰り返します。
最初の段階では重複は削除しません。
変数名はスペースに区切られていますので、半角スペースを探していきます。二回探して始まりと終わりの位置をとります。
変数名を取り終わったら、配列変数に格納して、格納した変数までを削除したもの(スペースは残す)で同じように探していきます。スペースが二つなければその行は終了です。
これを最終行まで繰り返しますが、問題は変数名ではないものまでが入っているということです。一例を挙げれば、数値です。あとは、変数ではあるが、外の場所に記述されているもの、カッコ内のものの一部です。
i 0 gyo_cnt 2 ←"0","2"は数値
p1 (AAA) ←"(AAA)"はほかの場所で必ず出てくるもの
AAA1 (AAA p1 1) ←"(AAA"、"1)"はカッコ内の一部。
← "ｐ1"はカッコ内の一部ですが、その判断ができないので、生かされます。
dd2(i) i ← "dd2(i)"は配列変数なので、カッコ内を100にします。
取得した変数名をソートし、重複部分を除きます。
そして、五こずつ、Dimに続けて表示します。
流れはこんなものです。
具体的に見てみましょう。(これはQXマクロです)
'初めのおまじない
if @hwnd = 0 then exit proc
'選択範囲を記憶
y_begin = @SelectStartLine 'これは物理的行数
y_end = @ScrLineToCrLine(@SelectEndLine) 'これは論理的行数
@BlockSelectEnd
if y_begin = 0 then
y_begin = 1 'これは物理的行数
y_end = @ScrLineToCrLine(@LastLine) 'これは論理的行数
end if
'メイン処理
'指定された範囲の中における、変数を持つ文字列を抜き出す。
'形式は、半角スペース+変数十半角スペースとなっている
@Line = y_begin
i= 0
do ＝⇒このdoは指定範囲内で繰り返すという意味のもの
a$ = @textCr$(@Line) ＝⇒一行の内容を変数に入れる
b$ = a$
do while 1 ＝⇒一行の中から変数名を抜き出す。変数名の数がわからないので、無限ループになっている。
rr$ = " "
p1 = instr(b$,rr$) ＝⇒rr$は半角スペースのこと。一つ目のスペースの位置取得
p2 = instr(mid$(b$,p1+1),rr$) ＝⇒二つ目のスペースの一取得。p2は最初のスペースは除いた位置である。変数名の長さ+1となる
if p2 = 0 then exit do '抜け出る
c$= left$(mid$(b$,p1+1,p2-1),1) ＝⇒抜き出した変数名候補の先頭の文字を取得
d$= right$(mid$(b$,p1+1,p2-1),1) ＝⇒抜き出した変数名候補の最後の文字を取得
if c$ < "0" or c$ > "9" then ' 0<= c$ <=9 は除外 ＝⇒数値は除く
if c$ <> "(" then 'c$ = ( は除外 ＝⇒"("で始まっているものは除く
p3 = instr(mid$(b$,p1+1,p2-1),"(")
if p3 = 0 or d$ = ")" then '途中に( があり、かつ、末尾が ) でない は除外
if p3 <> 0 or d$ <> ")" then '途中に( がなく、かつ、末尾が ) である は除外
if p3 <> 0 and d$ = ")" then '途中に( がある、かつ、末尾が ) である
mm1$[i] = left$(mid$(b$,p1+1,p2-1),p3) & "100)" ＝⇒配列変数なので、配列数を一意的に100とする。
i = i + 1
else '途中に( がなく、または、末尾が ) でない
mm1$[i] = mid$(b$,p1+1,p2-1)
i = i + 1
end if
end if
end if
end if
end if
b$ = mid$(b$,p2)
loop
@MoveNextLineCr '改行し次の行へ
loop while @ScrLineToCrLine(@Line) < y_end
m_end = i-1 '定数の個数(0を含める)
if m_end = 0 then exit proc
'ソート
for i = 0 to m_end-1 'ソート
min$=mm1$[i]
for j = i+1 to m_end
if min$ > mm1$[j] then
min$ = mm1$[j]
mm1$[j] = mm1$[i]
mm1$[i] = min$
end if
next
next
'重複した場合クリアする
for i = 0 to m_end-1
for j = i+1 to m_end
if mm1$[i] <> mm1$[j] then exit for
mm1$[j] = ""
next
next
'重複の削除。つめる。
j = 0
for i = 0 to m_end
if mm1$[i] <> "" then
mm1$[i-j] = mm1$[i]
else
j=j+1
end if
next
m_end2 = m_end - j '最終的な定数の数
'末尾に追加。五単位で改行。間はカンマで区切る
@InsertF "\n" ＝⇒改行命令
@InsertF "\n"
i=0
@Insert "Dim "
for m= 0 to m_end2
i=i+1
if i= ll005 then
@Insert mm1$[m]
@InsertF "\n"
@Insert "Dim "
i=0
else
@Insert mm1$[m]
@Insert ","
end if
next
@InsertF "\n"
@InsertF "\n"
※これはQXマクロである。
※今後の改修点
・QXマクロプログラムでも変数を抜き出せるようにする。(削除表(変換表)の作りで終了)
・変数の種類別に、変数名を分類し、その順番に出力する。配列変数、文字用変数、その他の分類
・その他の更なる分類。一文字のもの、二文字のもの、間に"_"があるもの、その他
こんなところを改善点として考えています。
次回から本道に戻ります。
2007年9月15日土曜日
第42回目 エクセルのファイルを分析する(その5：ちょっと寄道)
○第42回目 エクセルのファイルを分析する(その5：ちょっと寄道)
だいたい出来ていたので、先駆けて、機能アップを考えました。
それは、シートの目次のシートの内容を基にして、シートをソートすることです。
目次を見ていると、これを、こうちょっことこのうしろに動かしてみたいな、など、
シートをこういう風に並べ変えてみたいな、という気が起こります。
その際、目次のシート一覧を操作して、シートが動いたらいいのではないかと思っって、プログラムを考えてみました。
すると、いつものようにバグがでてきました。だいぶ時間がかかりました。原因は、変数名にちょっとしたタイプミスがあったのです。見るのは主にロジックであるため、こんなところに間違いがあるとは、思いません。
いつまでたってもわからないのです。
結果的に、結構な時間を費やしてしまいました。
ということで、これを機に変数の定義を必須とすることにしました。
毎度毎度のことですが、今回はいい機会と思って、その対応をきっちりするようにすることとしました。
寄道とは、変数の事前宣言に関するものです。
モジュールの先頭に、変数定義の必須を宣言すればいいのです。
するとコンパイラがチェックしてくれます。
そのおまじないは、 Option Explicit と書くことです。
モジュール上は、一番上から
Option Explicit
Sub xxxx()
：
となります。
今回のテーマは、変数の抜き出しを自動化してしまえないかということです。
プログラムを作った後に、変数を抜き出し、それをもとに変数の定義を作るというものです。
仕組みを考えてみました。
Sub文やコメント文は関係ありません。それを削除してしまいます。
ifとかforとかnextなどは変数ではありません。削除しましょう。
ということで、削除すべき(ヌルに変換すべき)項目などをきめて、QXエディタの複数変換マクロを動かすのです。
その後で変数を取り出し、重複をなくして、変数名を取得して、Dim文に付けてるというものです。
途中で、空行の削除のマクロも使います。
変換表は、次々に追加していけばいいのです。削除してみて、残っているものをどんどん追加していけばいいのです。原理的にも作業的にも簡単でした。
これは、QXエディターのマクロとなります。
だいたい出来ていたので、先駆けて、機能アップを考えました。
それは、シートの目次のシートの内容を基にして、シートをソートすることです。
目次を見ていると、これを、こうちょっことこのうしろに動かしてみたいな、など、
シートをこういう風に並べ変えてみたいな、という気が起こります。
その際、目次のシート一覧を操作して、シートが動いたらいいのではないかと思っって、プログラムを考えてみました。
すると、いつものようにバグがでてきました。だいぶ時間がかかりました。原因は、変数名にちょっとしたタイプミスがあったのです。見るのは主にロジックであるため、こんなところに間違いがあるとは、思いません。
いつまでたってもわからないのです。
結果的に、結構な時間を費やしてしまいました。
ということで、これを機に変数の定義を必須とすることにしました。
毎度毎度のことですが、今回はいい機会と思って、その対応をきっちりするようにすることとしました。
寄道とは、変数の事前宣言に関するものです。
モジュールの先頭に、変数定義の必須を宣言すればいいのです。
するとコンパイラがチェックしてくれます。
そのおまじないは、 Option Explicit と書くことです。
モジュール上は、一番上から
Option Explicit
Sub xxxx()
：
となります。
今回のテーマは、変数の抜き出しを自動化してしまえないかということです。
プログラムを作った後に、変数を抜き出し、それをもとに変数の定義を作るというものです。
仕組みを考えてみました。
Sub文やコメント文は関係ありません。それを削除してしまいます。
ifとかforとかnextなどは変数ではありません。削除しましょう。
ということで、削除すべき(ヌルに変換すべき)項目などをきめて、QXエディタの複数変換マクロを動かすのです。
その後で変数を取り出し、重複をなくして、変数名を取得して、Dim文に付けてるというものです。
途中で、空行の削除のマクロも使います。
変換表は、次々に追加していけばいいのです。削除してみて、残っているものをどんどん追加していけばいいのです。原理的にも作業的にも簡単でした。
これは、QXエディターのマクロとなります。
2007年9月12日水曜日
第41回目 こんな簡単だったのか！緊急講座、グラフ編
○第41回目 こんな簡単だったのか！緊急講座、グラフ編
※エクセル2007で作成したものです。
いつかグラフを取り上げる時がくるので、その材料を集めていましたが、その中で非常に簡単で有益で面白いものがありましたので紹介します。
(残念ながら、エクセル2007で作成したので、エクセル2003の場合、コマンドが異なっているため動きません。操作マクロの記録をとって各自アレンジするしかないようです)
グラフの作成というといつも心理的なおっくうさがありました。範囲を指定してウイザードでグラフを作る。そこまではいいのですが、その後のタイトルを入れる、Ｙ軸のメモリを修正するという部分です。マウスを使って、細かな指定をしなければならないので、キーボード派にとっては、なんとなくめんどくさいなと思ってしまうのです。うまくそのものをクリックできないのも要因の一つです。
グラフを作るということのほとんど80%ほどは単純なものです。それをもっと簡単にすっきりと出来たらどうだろう、というのが長年の思いでした。
それが出来るのでした。感激します。
グラフを作るときに必要な情報は…
使用するデータ、タイトル、Ｙ軸のメモリ(最小値、最大値、メモリの幅)であり、
使用するデータは分解すると、データがあるシート名とデータの範囲座標です。新規一括作成の場合は、データ系列の名前、Ｘ軸の表示内容、データ値は一緒にして一つの指定で可能です。
それを、セルに入れておいて(下のような内容を。最後のものはＹ軸の表示桁数)、これを基にグラフを作れというマクロになります。
ＸＸＸＸの推移、sheet01、$B$16:$O$107、-0.05、0.05、0.01、0.000(文字)
(それぞれの項目は一つのセルにいれ、下に向かって入力する。当該シートのデータを使う場合はシート名はヌルでも可能)
最初のセルにカーソルをおいてからマクロを動かすようにしました。
主な処理は次のようになります。
'グラフの作成
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range(sh_name & "!" & han_i)
ActiveChart.ChartType = xlLine '折れ線グラフのことです
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = y_min
ActiveChart.Axes(xlValue).MaximumScale = y_max
ActiveChart.Axes(xlValue).MajorUnit = y_memori
Selection.TickLabels.NumberFormatLocal = y_keta
'タイトルの設定
ActiveChart.SetElement (msoElementChartTitleAboveChart) 'グラフの外側に設定
' ActiveChart.SetElement (msoElementChartTitleCenteredOverlay) 'オーバーレイ。グラフと重なるということ。
ActiveChart.ChartTitle.Text = g_title
'グラフ専用シートを追加し移動。2007の場合、グラフはシート埋め込み型になるので専用シートへの移動が必要となりました
ActiveChart.Location Where:=xlLocationAsNewSheet
この前に、セルにあるパラメータを取得すればいいことになります。
次のようになります。
'設定されたセルの座標を取得
AAA = Selection.Address
AAA1 = AAA
retu1 = Range(AAA1).Column
gyo1 = Range(AAA1).Row
'パラメータの取得
g_title = Cells(gyo1, retu1)
If Cells(gyo1 + 1, retu1) <> "" Then
sh_name = Cells(gyo1 + 1, retu1)
Else
sh_name = ActiveSheet.Name
End If
han_i = Cells(gyo1 + 2, retu1)
y_min = Cells(gyo1 + 3, retu1)
y_max = Cells(gyo1 + 4, retu1)
y_memori = Cells(gyo1 + 5, retu1)
y_keta = Cells(gyo1 + 6, retu1) '表示ケタ。例えば0.000という与え方。文字で与える
これを前後してつなげるとグラフ新規一括作成のマクロとなります。
前にも言いましたが、グラフ関係は2007と2003とで大きな差があるようです。ちょっとした文法が異なるために、動かないなんて、もったいないです。
よく考えれば、マクロの勉強になるのでいいのかもしれません。
マクロの一括作成を、操作マクロの記録で取って見れば、後半の部分の修正がわかると思います。
このマクロは心理的にもかなり効率的だと思いましたので、順番を省みずにご紹介しました。
※エクセル2007で作成したものです。
いつかグラフを取り上げる時がくるので、その材料を集めていましたが、その中で非常に簡単で有益で面白いものがありましたので紹介します。
(残念ながら、エクセル2007で作成したので、エクセル2003の場合、コマンドが異なっているため動きません。操作マクロの記録をとって各自アレンジするしかないようです)
グラフの作成というといつも心理的なおっくうさがありました。範囲を指定してウイザードでグラフを作る。そこまではいいのですが、その後のタイトルを入れる、Ｙ軸のメモリを修正するという部分です。マウスを使って、細かな指定をしなければならないので、キーボード派にとっては、なんとなくめんどくさいなと思ってしまうのです。うまくそのものをクリックできないのも要因の一つです。
グラフを作るということのほとんど80%ほどは単純なものです。それをもっと簡単にすっきりと出来たらどうだろう、というのが長年の思いでした。
それが出来るのでした。感激します。
グラフを作るときに必要な情報は…
使用するデータ、タイトル、Ｙ軸のメモリ(最小値、最大値、メモリの幅)であり、
使用するデータは分解すると、データがあるシート名とデータの範囲座標です。新規一括作成の場合は、データ系列の名前、Ｘ軸の表示内容、データ値は一緒にして一つの指定で可能です。
それを、セルに入れておいて(下のような内容を。最後のものはＹ軸の表示桁数)、これを基にグラフを作れというマクロになります。
ＸＸＸＸの推移、sheet01、$B$16:$O$107、-0.05、0.05、0.01、0.000(文字)
(それぞれの項目は一つのセルにいれ、下に向かって入力する。当該シートのデータを使う場合はシート名はヌルでも可能)
最初のセルにカーソルをおいてからマクロを動かすようにしました。
主な処理は次のようになります。
'グラフの作成
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range(sh_name & "!" & han_i)
ActiveChart.ChartType = xlLine '折れ線グラフのことです
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = y_min
ActiveChart.Axes(xlValue).MaximumScale = y_max
ActiveChart.Axes(xlValue).MajorUnit = y_memori
Selection.TickLabels.NumberFormatLocal = y_keta
'タイトルの設定
ActiveChart.SetElement (msoElementChartTitleAboveChart) 'グラフの外側に設定
' ActiveChart.SetElement (msoElementChartTitleCenteredOverlay) 'オーバーレイ。グラフと重なるということ。
ActiveChart.ChartTitle.Text = g_title
'グラフ専用シートを追加し移動。2007の場合、グラフはシート埋め込み型になるので専用シートへの移動が必要となりました
ActiveChart.Location Where:=xlLocationAsNewSheet
この前に、セルにあるパラメータを取得すればいいことになります。
次のようになります。
'設定されたセルの座標を取得
AAA = Selection.Address
AAA1 = AAA
retu1 = Range(AAA1).Column
gyo1 = Range(AAA1).Row
'パラメータの取得
g_title = Cells(gyo1, retu1)
If Cells(gyo1 + 1, retu1) <> "" Then
sh_name = Cells(gyo1 + 1, retu1)
Else
sh_name = ActiveSheet.Name
End If
han_i = Cells(gyo1 + 2, retu1)
y_min = Cells(gyo1 + 3, retu1)
y_max = Cells(gyo1 + 4, retu1)
y_memori = Cells(gyo1 + 5, retu1)
y_keta = Cells(gyo1 + 6, retu1) '表示ケタ。例えば0.000という与え方。文字で与える
これを前後してつなげるとグラフ新規一括作成のマクロとなります。
前にも言いましたが、グラフ関係は2007と2003とで大きな差があるようです。ちょっとした文法が異なるために、動かないなんて、もったいないです。
よく考えれば、マクロの勉強になるのでいいのかもしれません。
マクロの一括作成を、操作マクロの記録で取って見れば、後半の部分の修正がわかると思います。
このマクロは心理的にもかなり効率的だと思いましたので、順番を省みずにご紹介しました。
2007年9月8日土曜日
第40回目 エクセルのファイルを分析する(その4-3)
○第40回目 エクセルのファイルを分析する(その4-3)
今回は計算式を文字化するということです。
範囲を指定しておいてからマクロを動かします。
今回は座標の取得は不要です。
第26回の時のものを利用します。
Sub a50Table_Clear01()
'範囲を指定して実行する。
Dim CCC As Object
For Each CCC In Selection
If CCC.HasFormula <> True Then
CCC.ClearContents 'クリア
End If
Next
End Sub
if文とクリアのところを変えればいいのです。
If CCC.HasFormula = True Then
CCC = "xxx" & CCC.Formula
End If
となります。
実行後、操作によりCtrl+cでコピーして、テキストに貼り付けて下さい。
わかるともいますが、同じ行のセルはタブで結ばれていて行が変わると改行になるのです。そういう形式でエクセルのデータはテキストに貼り付けられます。
また、ここでのfor文の中の動きですが、横に動いていき、行が終われば下の行に行くという動きをします。
反対も必要です。
メインの処理は、
Dim CCC As Object
For Each CCC In Selection
Selection.Replace What:="xxx", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
となります(簡単にいうと、"xxx"を削除するという処理)。
質問です。
横見出しにかかるもの(前回)と計算式にかかるもの(今回)とではマクロの複雑度が違います。
横見出しも計算式と同様な操作で出来ないのでしょうか(列記号の付加は無視します)。
なかなか面白い質問だと思います。
今回は計算式を文字化するということです。
範囲を指定しておいてからマクロを動かします。
今回は座標の取得は不要です。
第26回の時のものを利用します。
Sub a50Table_Clear01()
'範囲を指定して実行する。
Dim CCC As Object
For Each CCC In Selection
If CCC.HasFormula <> True Then
CCC.ClearContents 'クリア
End If
Next
End Sub
if文とクリアのところを変えればいいのです。
If CCC.HasFormula = True Then
CCC = "xxx" & CCC.Formula
End If
となります。
実行後、操作によりCtrl+cでコピーして、テキストに貼り付けて下さい。
わかるともいますが、同じ行のセルはタブで結ばれていて行が変わると改行になるのです。そういう形式でエクセルのデータはテキストに貼り付けられます。
また、ここでのfor文の中の動きですが、横に動いていき、行が終われば下の行に行くという動きをします。
反対も必要です。
メインの処理は、
Dim CCC As Object
For Each CCC In Selection
Selection.Replace What:="xxx", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
となります(簡単にいうと、"xxx"を削除するという処理)。
質問です。
横見出しにかかるもの(前回)と計算式にかかるもの(今回)とではマクロの複雑度が違います。
横見出しも計算式と同様な操作で出来ないのでしょうか(列記号の付加は無視します)。
なかなか面白い質問だと思います。
2007年9月5日水曜日
○第39回目 エクセルのファイルを分析する(その4-2)
○第39回目 エクセルのファイルを分析する(その4-2)
前回で取り上げたテーマの"横見出しを取得(マクロ)"の詳細です。
横見出しを、"B;1月 C;2月 D;3月 E;4月 F;5月 G;6月 H;上半期小計"のように取得するというものです。タブのだしかたははよくわかりませんので、"////"にしておきます。後で置換えしてください。
横見出しを指定した後、マクロを動かすという前提です。
ここで考えなければならないのは、横見出し行は複数行あるということと、中には結合セルの場合があるということです。結合セルの場合は、見出しは一番左端のセルに入っています。他のセルは空白です。結合セルの場合、単に見出しをつなげるだけではまずいので、そのときは間を"_"でつなぎます。セルの中で改行している場合は、どのようなものが取れるのでしょうか。この点は無視します。
パターン分けを考えますと(三行の場合)、単純、一行目がセルの結合･二行目以下は単純、一行目はセルの結合･二行目もセルの結合･三つ目は単純、一行目は単純･二行目もセルの結合･三つ目は単純。
それではセルが結合されているかどうかの判定。
If Cells(j, i).MergeCells then
実は、さらに結合されていてデータが入っているかをきくべきなので、実際は、
If Cells(j, i).MergeCells And Cells(j, i) = "" Then
となります。
データが入っているところまで、列をさかのぼりますので、iのところをマイナス1していきます。しかし、横見出しで設定した範囲を越えてはいけませんので、その制限をつけます。
後は組立てです。
座標の取得は、第33回の'座標の取得を参照です。
横見出しの
開始列 retu1
開始行 gyo1
終了列 retu9
終了行 gyo9
行数 gyo_m_cnt
列数 retu_cnt
となっています。
列記号と列番号の対応のために処理は、第26回参照。
r_no(i)；列記号
以上のことを基にして、横見出しを結合して配列変数に入れていってみましょう。単純ケースを想定します。二重のfor文になります。
For i = 1 To retu_m_cnt
mm(i) = r_no(retu1+i-1) & ";"
For j = 1 To gyo_m_cnt
mm(i) = mm(i) & cells(gyo1+j-1,retu1+i-1)
Next j
Next i
これを結合します。
For i = 1 To retu_m_cnt
y_midashi = y_midashi & mm(i)& "////"
Next i
これを特定のセルに張付けます。この場合セルの座標が問題になりますので、支障のないところに張付けて、クリップボードに貼り付けまで行います。
Cells(65536, 1) = y_midashi
Cells(65536, 1).Select
Selection.Copy
処理後に、テキストに張付けら得れるかどうかまで確かめてください。
後はセルが結合セルだった場合の処理です。
For j = 1 To gyo_m_cnt
mm(i) = mm(i) & cells(gyo1+j-1,retu1+i-1)
Next j
の部分を変えます。
For j = 1 To gyo_m_cnt
if cells(gyo1+j-1,retu1+i-1).MergeCells And cells(gyo1+j-1,retu1+i-1) = "" Then
for k =1 to i-1 step 1
if cells(gyo1+j-1,retu1+i-1-k).MergeCells And cells(gyo1+j-1,retu1+i-1-k) <> "" Then
if gyo1+j-1 = gyo1 then
mm(i) = mm(i) & cells(gyo1+j-1,retu1+i-1-k)
else
mm(i) = mm(i) & "_" & cells(gyo1+j-1,retu1+i-1-k)
end if
end if
Next k
else
mm(i) = mm(i) & cells(gyo1+j-1,retu1+i-1)
end if
Next j
こんなもんでしょうかね。
前回で取り上げたテーマの"横見出しを取得(マクロ)"の詳細です。
横見出しを、"B;1月 C;2月 D;3月 E;4月 F;5月 G;6月 H;上半期小計"のように取得するというものです。タブのだしかたははよくわかりませんので、"////"にしておきます。後で置換えしてください。
横見出しを指定した後、マクロを動かすという前提です。
ここで考えなければならないのは、横見出し行は複数行あるということと、中には結合セルの場合があるということです。結合セルの場合は、見出しは一番左端のセルに入っています。他のセルは空白です。結合セルの場合、単に見出しをつなげるだけではまずいので、そのときは間を"_"でつなぎます。セルの中で改行している場合は、どのようなものが取れるのでしょうか。この点は無視します。
パターン分けを考えますと(三行の場合)、単純、一行目がセルの結合･二行目以下は単純、一行目はセルの結合･二行目もセルの結合･三つ目は単純、一行目は単純･二行目もセルの結合･三つ目は単純。
それではセルが結合されているかどうかの判定。
If Cells(j, i).MergeCells then
実は、さらに結合されていてデータが入っているかをきくべきなので、実際は、
If Cells(j, i).MergeCells And Cells(j, i) = "" Then
となります。
データが入っているところまで、列をさかのぼりますので、iのところをマイナス1していきます。しかし、横見出しで設定した範囲を越えてはいけませんので、その制限をつけます。
後は組立てです。
座標の取得は、第33回の'座標の取得を参照です。
横見出しの
開始列 retu1
開始行 gyo1
終了列 retu9
終了行 gyo9
行数 gyo_m_cnt
列数 retu_cnt
となっています。
列記号と列番号の対応のために処理は、第26回参照。
r_no(i)；列記号
以上のことを基にして、横見出しを結合して配列変数に入れていってみましょう。単純ケースを想定します。二重のfor文になります。
For i = 1 To retu_m_cnt
mm(i) = r_no(retu1+i-1) & ";"
For j = 1 To gyo_m_cnt
mm(i) = mm(i) & cells(gyo1+j-1,retu1+i-1)
Next j
Next i
これを結合します。
For i = 1 To retu_m_cnt
y_midashi = y_midashi & mm(i)& "////"
Next i
これを特定のセルに張付けます。この場合セルの座標が問題になりますので、支障のないところに張付けて、クリップボードに貼り付けまで行います。
Cells(65536, 1) = y_midashi
Cells(65536, 1).Select
Selection.Copy
処理後に、テキストに張付けら得れるかどうかまで確かめてください。
後はセルが結合セルだった場合の処理です。
For j = 1 To gyo_m_cnt
mm(i) = mm(i) & cells(gyo1+j-1,retu1+i-1)
Next j
の部分を変えます。
For j = 1 To gyo_m_cnt
if cells(gyo1+j-1,retu1+i-1).MergeCells And cells(gyo1+j-1,retu1+i-1) = "" Then
for k =1 to i-1 step 1
if cells(gyo1+j-1,retu1+i-1-k).MergeCells And cells(gyo1+j-1,retu1+i-1-k) <> "" Then
if gyo1+j-1 = gyo1 then
mm(i) = mm(i) & cells(gyo1+j-1,retu1+i-1-k)
else
mm(i) = mm(i) & "_" & cells(gyo1+j-1,retu1+i-1-k)
end if
end if
Next k
else
mm(i) = mm(i) & cells(gyo1+j-1,retu1+i-1)
end if
Next j
こんなもんでしょうかね。
2007年9月1日土曜日
第38回目 エクセルのファイルを分析する(その4)
○第38回目 エクセルのファイルを分析する(その4)
前回問題提起した、材料は横見出しと計算式です。これを使って何か仕様書モドキのものが出来ないか、ということですが、一筋縄では行きません。
複合技で処理をシステム化するというあたりで、考えて見ます。
例として、簡単な表を考えてみます。
1月から12月の売上げ数値があり、それを基に上半期の小計、下半期の小計、年度の総合計をとる、というものです。縦項目はチェーン店でもいいでしょう。下にチェーン店の総合計が入ります。
A列から展開すると､BからG列までが1-6月､H列が上半期小計､IからN列までが7-12月､O列が下半期の小計､P列が年間合計となります｡
最初の小計は、 =SUM(B4:G4)、
次は、 =SUM(I4:N4)、
最後は =H4+O4 となります。
縦の合計は、 =SUM(B4:B12) となります。
このように式を取り出すのは簡単です。(計算式を文字にするマクロが登場します)
そして、横見出しを次のように取り出すのも簡単です。
1月 2月 3月 4月 5月 6月 上半期小計 7月 8月 9月 10月 11月 12月 下半期小計 合計(間はタブで繋いでいる)
これに列記号を付けてみてはどうでしょうか。これはマクロの役割となります。
B;1月 C;2月 D;3月 E;4月 F;5月 G;6月 H;上半期小計 I;7月 J;8月 K;9月 L;10月 M;11月 N;12月 O;下半期小計 P;合計 Q;
タブを改行に、;をタブに置き換えます。
そして、計算式の該当する列記号を見出しに置き換えるのです。(QXエディタのフリーソフト。上記の"XXXタブYYY"がこのソフトでの変換語の与え方の形式。第32回で紹介"複数文字列一括置換マクロ")
すると
最初の小計は、 =SUM(1月:6月)、
次は、 =SUM(7月:12月)、
最後は =上半期+下半期 となります。
(注)SUMには、列記号と見間違える英文字が入っているために工夫が必要です。
結果を整理をすると、
1月
2月
3月
4月
5月
6月
上半期小計 =SUM(1月:6月)
7月
8月
9月
10月
11月
12月
下半期小計 =SUM(7月:12月)
合計 =上半期+下半期
となります。
※縦の合計は別のやり方になるので、混乱を避けるため省略。
このようなものを一連の操作で作成できたらどうでしょうか。
1.横見出しを取得(マクロ)
1-2.テキストに貼り付け
1-3.タブ等を置換え
1-4.コピーし同じものを作る
1-5.一つは先頭の列記号を削除しておく
2.計算式を文字にする(マクロ)
2-2.テキストに貼り付け(計算式以外の項目も含めてココピーする)
2-3.タブを改行に置換え
2-4.1-5の左側にブロックコピーする
3."1"を用い変換表を完成させる。
3-2 ほとんど出来ているのですが、行数の削除文の追加、関数の変換・再変換。後半はすべての処理で共通部分なので一度作ると使い回せます。次のようなものです。例では12行目のところの計算式を取っています。
SUM さむ
ROUND らうんど
IF いふ
LOOKUP るっくあっぷ
11 (-1)
12
13 (+1)
【ここに横見出しの変換表を入れる】
さむ SUM
らうんど ROUND
いふ IF
るっくあっぷ LOOKUP
3-3 2.4をこの変換表で修正する。
※まだまだ問題があります。別シートの列を使う場合とか、別ファイルの列を使う場合とかがあります。また、固定セルも特別に変換する必要もあります。
※これらの処理はマクロが使えるテキストエディタ(記述の内容はQXエディタを前提)を前提にしています。
前回問題提起した、材料は横見出しと計算式です。これを使って何か仕様書モドキのものが出来ないか、ということですが、一筋縄では行きません。
複合技で処理をシステム化するというあたりで、考えて見ます。
例として、簡単な表を考えてみます。
1月から12月の売上げ数値があり、それを基に上半期の小計、下半期の小計、年度の総合計をとる、というものです。縦項目はチェーン店でもいいでしょう。下にチェーン店の総合計が入ります。
A列から展開すると､BからG列までが1-6月､H列が上半期小計､IからN列までが7-12月､O列が下半期の小計､P列が年間合計となります｡
最初の小計は、 =SUM(B4:G4)、
次は、 =SUM(I4:N4)、
最後は =H4+O4 となります。
縦の合計は、 =SUM(B4:B12) となります。
このように式を取り出すのは簡単です。(計算式を文字にするマクロが登場します)
そして、横見出しを次のように取り出すのも簡単です。
1月 2月 3月 4月 5月 6月 上半期小計 7月 8月 9月 10月 11月 12月 下半期小計 合計(間はタブで繋いでいる)
これに列記号を付けてみてはどうでしょうか。これはマクロの役割となります。
B;1月 C;2月 D;3月 E;4月 F;5月 G;6月 H;上半期小計 I;7月 J;8月 K;9月 L;10月 M;11月 N;12月 O;下半期小計 P;合計 Q;
タブを改行に、;をタブに置き換えます。
そして、計算式の該当する列記号を見出しに置き換えるのです。(QXエディタのフリーソフト。上記の"XXXタブYYY"がこのソフトでの変換語の与え方の形式。第32回で紹介"複数文字列一括置換マクロ")
すると
最初の小計は、 =SUM(1月:6月)、
次は、 =SUM(7月:12月)、
最後は =上半期+下半期 となります。
(注)SUMには、列記号と見間違える英文字が入っているために工夫が必要です。
結果を整理をすると、
1月
2月
3月
4月
5月
6月
上半期小計 =SUM(1月:6月)
7月
8月
9月
10月
11月
12月
下半期小計 =SUM(7月:12月)
合計 =上半期+下半期
となります。
※縦の合計は別のやり方になるので、混乱を避けるため省略。
このようなものを一連の操作で作成できたらどうでしょうか。
1.横見出しを取得(マクロ)
1-2.テキストに貼り付け
1-3.タブ等を置換え
1-4.コピーし同じものを作る
1-5.一つは先頭の列記号を削除しておく
2.計算式を文字にする(マクロ)
2-2.テキストに貼り付け(計算式以外の項目も含めてココピーする)
2-3.タブを改行に置換え
2-4.1-5の左側にブロックコピーする
3."1"を用い変換表を完成させる。
3-2 ほとんど出来ているのですが、行数の削除文の追加、関数の変換・再変換。後半はすべての処理で共通部分なので一度作ると使い回せます。次のようなものです。例では12行目のところの計算式を取っています。
SUM さむ
ROUND らうんど
IF いふ
LOOKUP るっくあっぷ
11 (-1)
12
13 (+1)
【ここに横見出しの変換表を入れる】
さむ SUM
らうんど ROUND
いふ IF
るっくあっぷ LOOKUP
3-3 2.4をこの変換表で修正する。
※まだまだ問題があります。別シートの列を使う場合とか、別ファイルの列を使う場合とかがあります。また、固定セルも特別に変換する必要もあります。
※これらの処理はマクロが使えるテキストエディタ(記述の内容はQXエディタを前提)を前提にしています。
登録:
コメント (Atom)
