○第27回目 計算式関係2
【ちょっとしたアイデア】
大きな表を作る場合が多い方などは、その表の終わり(行でも列でも)にカーソルを飛ばす場合、どうやっていますか。
普通はCtrl+↓ですが、新規の表の場合は止まりません。
そこで、
列の場合であれば、PageDounで大きく動かして、あとは矢印キーでセルを選択する、となります。
色々表を加工していくと、Ctrl+↓で止まるようになります。
そこで、表の下の端、右の端に計算式を埋め込むマクロを作っておき、最初にそれを動かすことによって、表の端にアンカーを打ち込むというアイデアです。
当然ながら、一回は表の範囲を指定する必要があります。
範囲を指定してから動かす
範囲の端の座標を取得する
端の座標に何も入っていない場合は、ヌルをうめこむ計算式を設定する。
(作業の途中で行なう場合、すでに必要なデータなりを入力した後だったことを考慮しています)
これはもうこれまでの応用です。
・指定範囲の座標を取得し、列記号は列番号に変換する 第22回
・計算式が入っているか。データとしてヌルが入っているか。 第26回
・ヌルを与える計算式 『=""』
やってみらたヌル式がうまくいきません。
"は特殊な記号なので、
Cells(gyo11 + i, retu19).Formula = "="""""
というように与えなければなりません。
文字としての"は、"で囲むということです。
一部を掲載しておきます。
'列レベルの設定
For i = 0 To retu19 - retu11
If Cells(gyo19, retu11 + i).Formula = "" And Cells(gyo19, retu11 + i) = "" Then
Cells(gyo19, retu11 + i).Formula = "="""""
End If
Next i
後はロジックの組み立てですのがチャレンジして見てください。
ロジックの組み立てとしては、手ごろな例題です。
表の下端･右端に、計算式として、sum関数を埋め込むのも便利かもしれません。
これは一過程余計に必要。第26回参照
2007年7月29日日曜日
2007年7月25日水曜日
第26回目 計算式関係
○第26回目 計算式関係
セルに入っているデータの種類として、計算式というものがあります。
これを作成したりすることを考えて見ます。
計算式もデータとなりますので、
Cells(12,2) = "=sum(b3:b10)"
ということで計算式が入ってしまいます。
Cells(12,3).Formula = "=sum(c3:c10)"
というほうが正式なようです。
計算式は座標を書きこまなければならないので、このままでは汎用性に欠けます。
つまり、上記の式では、左辺は変数で記述できますが、右辺は出来ないということです。
これを何とかしないとマクロの意味合いがありません。
問題を明確化しておきます。
問題；b列からl列までの12行目に、それぞれ3行目から10行目までの合計の計算式を作ってください、ということになります。
こんなのは通常のエクセルでやればいいのではないか、ということになりますが、応用が利きます(？)ので取り上げてみます。
列記号と列数値の対応が問題になります。
このことは第16回で問題として掲げておいたところです。
'列名を配置変数に格納して、列番号との対応をとる。
Dim r_no(312)
aa="abcdefghijklmnopqrstuvwxyz"
for i＝1 to 26
r_no(i) = mid(aa,i,1)
next i
For i = 1 To 11
For j = 1 To 26
r_no(i * 26 + j) = r_no(i) & r_no(j)
Next j
Next i
このように事前にセットしておくと、マクロは次のようになります。
for i=2 to 12
Cells(12,i).Formula = "=sum(" & r_no(i) & "3:" & r_no(i) & "10)"
next i
ということで、多少めんどくさいですが、あっという間に終わってしまいます。
このマクロの問題点は、
1.通常のエクセル操作で簡単に出来るため意味がない
2.プログラムをそのつど修正していくので汎用性がない
ということになります。
1は、その人にニーズがあるかないか、直面しているかしていないということに依存してしまい、一概に問題点と言えませんが、
2は、工夫の余地がおおいにありですね。
工夫については、エクセルのセルに変数を入れておき、それを用いて計算式を作るということが考えられます。
例の場合の変数とは、"=sum("、r_no(i)、"3:"、r_no(i)、"10)"の5項目となります。そして、その内二つが動く変数となります。
工夫の内容を明確化しておきましょう。
動かない変数(最大6個)、動く変数(最大5個)をセルにセットしておき、それを用いて計算式を作るというマクロ、ということになります。
当然ながら、計算式では、動かない変数と動く変数が必ず交互に現れ、最後は動かない変数となります。
計算式を入れる場所も与える必要がありますので、その点も考慮する必要があります。
デバックも入れるとそう簡単には出来そうもありませんね。
ということで、別件。
セルに計算式が入っているかどうかの判断です。
計算式は言ってみればデータなので、どうやって区分するかです。
計算式が入っている時 … cells(2,12).HasFormula = True
計算式が入っていない時… cells(2,12).HasFormula = False
となります。
これを使った例を一つ。
Sub a50Table_Clear01()
'範囲を指定して実行する。
Dim CCC As Object
For Each CCC In Selection
If CCC.HasFormula <> True Then
CCC.ClearContents 'クリア
End If
Next
End Sub
これは指定した範囲内で計算式のセルを残して、データをクリアするというものです。
マクロを知らない時、このようなニーズがありましたが、そのときはその作業を人にやらせてしまいました。
その後、マクロではこんな簡単にできるんだと、びっくりした一例です。
また、上の応用として、計算式の入っているセルのみ保護するということも簡単に出来ます。
セルに入っているデータの種類として、計算式というものがあります。
これを作成したりすることを考えて見ます。
計算式もデータとなりますので、
Cells(12,2) = "=sum(b3:b10)"
ということで計算式が入ってしまいます。
Cells(12,3).Formula = "=sum(c3:c10)"
というほうが正式なようです。
計算式は座標を書きこまなければならないので、このままでは汎用性に欠けます。
つまり、上記の式では、左辺は変数で記述できますが、右辺は出来ないということです。
これを何とかしないとマクロの意味合いがありません。
問題を明確化しておきます。
問題；b列からl列までの12行目に、それぞれ3行目から10行目までの合計の計算式を作ってください、ということになります。
こんなのは通常のエクセルでやればいいのではないか、ということになりますが、応用が利きます(？)ので取り上げてみます。
列記号と列数値の対応が問題になります。
このことは第16回で問題として掲げておいたところです。
'列名を配置変数に格納して、列番号との対応をとる。
Dim r_no(312)
aa="abcdefghijklmnopqrstuvwxyz"
for i＝1 to 26
r_no(i) = mid(aa,i,1)
next i
For i = 1 To 11
For j = 1 To 26
r_no(i * 26 + j) = r_no(i) & r_no(j)
Next j
Next i
このように事前にセットしておくと、マクロは次のようになります。
for i=2 to 12
Cells(12,i).Formula = "=sum(" & r_no(i) & "3:" & r_no(i) & "10)"
next i
ということで、多少めんどくさいですが、あっという間に終わってしまいます。
このマクロの問題点は、
1.通常のエクセル操作で簡単に出来るため意味がない
2.プログラムをそのつど修正していくので汎用性がない
ということになります。
1は、その人にニーズがあるかないか、直面しているかしていないということに依存してしまい、一概に問題点と言えませんが、
2は、工夫の余地がおおいにありですね。
工夫については、エクセルのセルに変数を入れておき、それを用いて計算式を作るということが考えられます。
例の場合の変数とは、"=sum("、r_no(i)、"3:"、r_no(i)、"10)"の5項目となります。そして、その内二つが動く変数となります。
工夫の内容を明確化しておきましょう。
動かない変数(最大6個)、動く変数(最大5個)をセルにセットしておき、それを用いて計算式を作るというマクロ、ということになります。
当然ながら、計算式では、動かない変数と動く変数が必ず交互に現れ、最後は動かない変数となります。
計算式を入れる場所も与える必要がありますので、その点も考慮する必要があります。
デバックも入れるとそう簡単には出来そうもありませんね。
ということで、別件。
セルに計算式が入っているかどうかの判断です。
計算式は言ってみればデータなので、どうやって区分するかです。
計算式が入っている時 … cells(2,12).HasFormula = True
計算式が入っていない時… cells(2,12).HasFormula = False
となります。
これを使った例を一つ。
Sub a50Table_Clear01()
'範囲を指定して実行する。
Dim CCC As Object
For Each CCC In Selection
If CCC.HasFormula <> True Then
CCC.ClearContents 'クリア
End If
Next
End Sub
これは指定した範囲内で計算式のセルを残して、データをクリアするというものです。
マクロを知らない時、このようなニーズがありましたが、そのときはその作業を人にやらせてしまいました。
その後、マクロではこんな簡単にできるんだと、びっくりした一例です。
また、上の応用として、計算式の入っているセルのみ保護するということも簡単に出来ます。
2007年7月22日日曜日
第25回目 ジャンプのマクロ
○第25回目 ジャンプのマクロ
同一シート内への任意場所へジャンプするマクロを考えます。
ジャンプ先の与え方は、列－行とします。26-15とかですね。
いやいや、列記号のほうがやりやすい。すると、aa-15とかになりますね。
今カーソルがあるセルに注目して、そのセルと同じ列で行を指定するとか、同じ行で列だけ指定する、というものありそうですね。
これらを全部まとめてしまいましょう。
ポイントは、入力値の分析・分解と、ジャンプです。
ジャンプはそのセルに行けばいいのですから、
Cells(gyo, retu).select で十分ですね。
入力形式は、27-20、AA-20、27-、AA-、-20となります。後半の三つは、指示されていない部分はアクティブセルの行または列を使います。
アクティブセルの座標は、
ActiveCell.Select
bbb = Selection.Address
retu = Range(bbb).Column
gyo = Range(bbb).Row
でした。
さて入力値の分解です。
"-"の位置が問題になります。
"-"の位置は、
p1 = InStr(hh, "-")となります。
ない場合は、指定間違いですので、処理を終了させましょう。 exit sub
位置が1の場合は、列の省略形です。
位置が1でない場合は、その位置が、文字の長さと一致していると、行の省略形となります。
文字の長さは、 len(AA) となります。
列部分が数値でなければ、列記号による指定です。これは、
if IsNumeric(Left(hh, p1 - 1)) = true で判断できます
もう一つ大切なのは、列記号を列番号に変える方法です。
列記号がAAにはいっているとすると、
Range(AA & "1").column で列番号になります。
あとは、変数名の整合性と、ロジックの組み立てだけです。
がんばって作ってみてください。
同一シート内への任意場所へジャンプするマクロを考えます。
ジャンプ先の与え方は、列－行とします。26-15とかですね。
いやいや、列記号のほうがやりやすい。すると、aa-15とかになりますね。
今カーソルがあるセルに注目して、そのセルと同じ列で行を指定するとか、同じ行で列だけ指定する、というものありそうですね。
これらを全部まとめてしまいましょう。
ポイントは、入力値の分析・分解と、ジャンプです。
ジャンプはそのセルに行けばいいのですから、
Cells(gyo, retu).select で十分ですね。
入力形式は、27-20、AA-20、27-、AA-、-20となります。後半の三つは、指示されていない部分はアクティブセルの行または列を使います。
アクティブセルの座標は、
ActiveCell.Select
bbb = Selection.Address
retu = Range(bbb).Column
gyo = Range(bbb).Row
でした。
さて入力値の分解です。
"-"の位置が問題になります。
"-"の位置は、
p1 = InStr(hh, "-")となります。
ない場合は、指定間違いですので、処理を終了させましょう。 exit sub
位置が1の場合は、列の省略形です。
位置が1でない場合は、その位置が、文字の長さと一致していると、行の省略形となります。
文字の長さは、 len(AA) となります。
列部分が数値でなければ、列記号による指定です。これは、
if IsNumeric(Left(hh, p1 - 1)) = true で判断できます
もう一つ大切なのは、列記号を列番号に変える方法です。
列記号がAAにはいっているとすると、
Range(AA & "1").column で列番号になります。
あとは、変数名の整合性と、ロジックの組み立てだけです。
がんばって作ってみてください。
2007年7月21日土曜日
第24回目 セルの範囲をマクロ処理中に指定する
○第24回目 セルの範囲をマクロ処理中に指定する
セルの範囲の指定について、通常は指定してからマクロを動かすことで事足りるのですが、2つ以上あるとこの手が使えません。
その場合は…、
といって最初からこのことがわかっていたわけではなく、人のマクロに関する解説を見ていて、最近気がついたことです。
このやり方はとても便利です。
sub a19セル範囲の設定
Dim セル範囲 As Range
'セル範囲の設定
ActiveCell.Select
bbb = Selection.Address '初期値として与えるため
Set セル範囲 = Application.InputBox(Prompt:="セル範囲を指定してください", Default:=bbb, Left:=10, Top:=2, Type:=8)
AAA = セル範囲.Address
MsgBox ("設定範囲は " & AAA & " です")
end sub
注意点は、Default:=bbb, Left:=10, Top:=2, Type:=8の部分になります。
Default:=bbb ここでは、アクティブセルの座標を初期値として設定しています。ですから、カーソルをあらかじめ指定範囲の先頭においてください。
Left:=10, Top:=2 はこのボックスの表示位置の調整です
Type:=8 セル参照ということです。
【参照】
http://members.at.infoseek.co.jp/t_shun/My_Page/Excel-VBA/vba_page22.htm#InputBox%20%83%81%83\%83b%83h
キーボードでの範囲指定は、Shiftを押しながら矢印キーを動かします。このマクロでは、結果が表示できますので、色々試してみてください。
よく使う操作で、コピーがありますが、今回のマクロを応用すると操作が簡単になります。(全部貼り付けの場合は、形式の設定がないので、通常のキーボードによる操作のほうが早いです)
コピーの場合、複写元と、複写先の二箇所のセルを指定することになります。
その範囲をこの命令を二回使い、セルを指定し、コピーの時の操作マクロの記録を組み合わせれば簡単につくれます(貼付けの機能ごとに作る必要がありますが、よく使う、"値と数値の書式形式コピー"と"数式と数値の書式形式コピー"ぐらいつくっておくといいでしょう。よく使うマクロとなりますのでショートカットを設定し、ショートカットで動かしてください)。
範囲を指定してマクロを動かし、二度目だけを今回のやり方にするほうが、操作が早そうです。いろいろ工夫があるところですね。
セルの範囲の指定について、通常は指定してからマクロを動かすことで事足りるのですが、2つ以上あるとこの手が使えません。
その場合は…、
といって最初からこのことがわかっていたわけではなく、人のマクロに関する解説を見ていて、最近気がついたことです。
このやり方はとても便利です。
sub a19セル範囲の設定
Dim セル範囲 As Range
'セル範囲の設定
ActiveCell.Select
bbb = Selection.Address '初期値として与えるため
Set セル範囲 = Application.InputBox(Prompt:="セル範囲を指定してください", Default:=bbb, Left:=10, Top:=2, Type:=8)
AAA = セル範囲.Address
MsgBox ("設定範囲は " & AAA & " です")
end sub
注意点は、Default:=bbb, Left:=10, Top:=2, Type:=8の部分になります。
Default:=bbb ここでは、アクティブセルの座標を初期値として設定しています。ですから、カーソルをあらかじめ指定範囲の先頭においてください。
Left:=10, Top:=2 はこのボックスの表示位置の調整です
Type:=8 セル参照ということです。
【参照】
http://members.at.infoseek.co.jp/t_shun/My_Page/Excel-VBA/vba_page22.htm#InputBox%20%83%81%83\%83b%83h
キーボードでの範囲指定は、Shiftを押しながら矢印キーを動かします。このマクロでは、結果が表示できますので、色々試してみてください。
よく使う操作で、コピーがありますが、今回のマクロを応用すると操作が簡単になります。(全部貼り付けの場合は、形式の設定がないので、通常のキーボードによる操作のほうが早いです)
コピーの場合、複写元と、複写先の二箇所のセルを指定することになります。
その範囲をこの命令を二回使い、セルを指定し、コピーの時の操作マクロの記録を組み合わせれば簡単につくれます(貼付けの機能ごとに作る必要がありますが、よく使う、"値と数値の書式形式コピー"と"数式と数値の書式形式コピー"ぐらいつくっておくといいでしょう。よく使うマクロとなりますのでショートカットを設定し、ショートカットで動かしてください)。
範囲を指定してマクロを動かし、二度目だけを今回のやり方にするほうが、操作が早そうです。いろいろ工夫があるところですね。
2007年7月16日月曜日
第23回目 階段状の活用
○第23回目 階段状の活用
第22回の最後のところで、
『第18回目から第20回目までの『凝った計算式の作り方』で紹介した計算式など、この機能で軽く出来てしまうのである。これは驚くべきことである。』
と書きましたが、その解説をします。
第18回、その2 横のものを縦にする。
まず、通常のやり方で、単純に縦計(20個あったとします)をとった行を作ります。
セルの挿入で、その部分を適当なところまで右に移動します。
周りには邪魔になるものがなくなってところで、階段上のセルの挿入を動かします。
まずは、その部分(20個)を指定して、一つずつ増やす方式で下に移動させます。
つぎに、先頭から縦に範囲を20個設定し、こんどは一つずつ減らす方式で横に移動させます。
最初の処理で斜めになったものが、今度は縦一列になります。
これで横のものが縦になります。あとは適当にセルを削除してもって行きたいところに移動させます。通常のコピーではなくセルの削除にしてください。通常のコピーの場合は式の内容が変わってしまいます。
これと同様に、縦のものを横にすることができます。
第19回 階段状に合計をとる
20列30行のデータ部があるとします。
その下に、階段状に合計をとって見ましょう。
階段状の合計とは、次のようなことです。
最初の合計を1-30行の合計とすると、その左となりは2-30行の合計、3-30行の合計…となります。
一つ下は、2-30行の合計ですので、その左となりは3-30行の合計、4-30行の合計…となります。
やり方です。
まず最初のセルに1－30行の合計の式を作ります。30は固定したいので"$"をつけます。
これを、縦横(20列30行)にコピーしてください。これは通常の処理ですね。
ここで、合計値の入っている一番上の部分を横に30セル範囲指定します。
階段状のセルの挿入を動かします。一つずつ減らす方式となります。
右あがりの形態になったかと思います。
そこで、一番最初に入れたセルから、左に計算式を見てください。求める形になっています。(下の計算式は当然です)
これで計算式は完成していますので、右上がりになっている余計な部分をセル削除してください。
すると、あたかも最初から求める計算式を入れたのではないかというようになります。
第22回の最後のところで、
『第18回目から第20回目までの『凝った計算式の作り方』で紹介した計算式など、この機能で軽く出来てしまうのである。これは驚くべきことである。』
と書きましたが、その解説をします。
第18回、その2 横のものを縦にする。
まず、通常のやり方で、単純に縦計(20個あったとします)をとった行を作ります。
セルの挿入で、その部分を適当なところまで右に移動します。
周りには邪魔になるものがなくなってところで、階段上のセルの挿入を動かします。
まずは、その部分(20個)を指定して、一つずつ増やす方式で下に移動させます。
つぎに、先頭から縦に範囲を20個設定し、こんどは一つずつ減らす方式で横に移動させます。
最初の処理で斜めになったものが、今度は縦一列になります。
これで横のものが縦になります。あとは適当にセルを削除してもって行きたいところに移動させます。通常のコピーではなくセルの削除にしてください。通常のコピーの場合は式の内容が変わってしまいます。
これと同様に、縦のものを横にすることができます。
第19回 階段状に合計をとる
20列30行のデータ部があるとします。
その下に、階段状に合計をとって見ましょう。
階段状の合計とは、次のようなことです。
最初の合計を1-30行の合計とすると、その左となりは2-30行の合計、3-30行の合計…となります。
一つ下は、2-30行の合計ですので、その左となりは3-30行の合計、4-30行の合計…となります。
やり方です。
まず最初のセルに1－30行の合計の式を作ります。30は固定したいので"$"をつけます。
これを、縦横(20列30行)にコピーしてください。これは通常の処理ですね。
ここで、合計値の入っている一番上の部分を横に30セル範囲指定します。
階段状のセルの挿入を動かします。一つずつ減らす方式となります。
右あがりの形態になったかと思います。
そこで、一番最初に入れたセルから、左に計算式を見てください。求める形になっています。(下の計算式は当然です)
これで計算式は完成していますので、右上がりになっている余計な部分をセル削除してください。
すると、あたかも最初から求める計算式を入れたのではないかというようになります。
2007年7月15日日曜日
第22回目 階段状の処理
○第22回目 階段状の処理
第21回で、
『 マクロではどうなるでしょうか。
色々な方法が考えられます。
1.計算式中、61以上のあるセルをクリアする。
2.物理的階段状にセルをクリアする。
3.複数の置き換えが可能なマクロで、置き換える。』
とあり、3のマクロを作りましたが、ひょなことから、2の類型を適用する事例があることを発見しました。
処理はセルの内容をクリアするのではなく、セルを挿入していくことです。
データの形態を階段状にしてしまおうというのです。これを二回繰り返すと、あとでセル(当然四角形で与える)を削除することによって、階段状になったものから、通常の四角の形にするということも出来ます。
基本機能は、セルを一つずつ多くして挿入していくことですが、
1.行及び列に対して処理できる。
2.例えば行を指定した場合、左端から一つずつ増やして挿入するのか、右端から一つずつ増やして挿入するのかの指定が出来る。
(削除は怖いのでこのマクロの対象とはしない。しかし、削除は、この挿入機能と通常の削除操作で可能です。すなわち、階段状のものに逆階段状にセルを挿入していき、正方形にしたあとで、セルを削除するということです)
前提としては、挿入したい範囲を指定しておきます。行で指定すれば下への挿入。列で指定すれば、右への挿入となります。
基本処理をマクロの記録でとって見ましょう。
Sub marco01()
Range("AE54:AG55").Select
Selection.Insert Shift:=xlDown
Range("AJ54:AK60").Select
Selection.Insert Shift:=xlToRight
End Sub
簡単な命令ですね。
次に、指定範囲の座標から必要な情報を取得します。
列1～列9、行1
行1～行9、列1 のいずれかです。
指定範囲の座標から、とってみましょう。
AAA = Selection.Address
p1 = InStr(AAA, ":")
AAA1 = Left(AAA, p1 - 1)
AAA9 = Mid(AAA, p1 + 1)
MsgBox ("セルの座標は、" & AAA & "最初の座表は、" & AAA1 & "最後の座標は、" & AAA9 & " です")
更に、列･行に分けて見ましょう。
p1 = InStr(Mid(AAA1, 2), "$")
retu1 = Mid(AAA1, 2, p1 - 1)
gyo1 = Mid(AAA1, p1 + 2)
p1 = InStr(Mid(AAA9, 2), "$")
retu9 = Mid(AAA9, 2, p1 - 1)
gyo9 = Mid(AAA9, p1 + 2)
MsgBox ("先頭のセルは、 " & retu1 & "--" & gyo1 & "最後のセルは、" & retu9 & "--" & gyo9 & " です")
別の方法で、列番号などをとってみましょう。
retu1=range(AAA1).column
gyo1=range(AAA1).row
retu9=range(AAA9).column
gyo9=range(AAA9).row
これまでのところを少し整理してみましょう
Sub a20セルの階段状挿入()
'範囲を指定したあとに実行する
AAA = Selection.Address
p1 = InStr(AAA, ":")
AAA1 = Left(AAA, p1 - 1) '先頭の座標
AAA9 = Mid(AAA, p1 + 1) '最後の座標
'チェック
MsgBox ("セルの座標は、" & AAA & "最初の座表は、" & AAA1 & "最後の座標は、" & AAA9 & " です")
'列番号と、行番号を取得
retu1 = Range(AAA1).Column
gyo1 = Range(AAA1).Row
retu9 = Range(AAA9).Column
gyo9 = Range(AAA9).Row
'チェック
MsgBox ("先頭のセルは、 " & retu1 & "--" & gyo1 & "最後のセルは、" & retu9 & "--" & gyo9 & " です")
次の段階へ。
'挿入する階段の形態の入力
flag1 = InputBox("一つずつ増やしていきますか(1)、減らしていきますか(-1) ", , 1)
'行に対して挿入していく場合
If gyo1 = gyo9 Then '先頭と最後の行が同じということは、行に対して挿入することになる
kaisuu = retu9 - retu1 + 1 '何列分あるかということ
If flag1 = 1 Then
kosuu0 = 1 '挿入するセル数の初期値
Else
kosuu0 = kaisuu '逆の場合は、初期値は回数分となります
flag1 = -1 'あとで使うのでキチンとしておく
End If
'チェック
' MsgBox ("回数は、 " & kaisuu & "--" & "挿入するセル数の初期値は、 " & kosuu0 & " です")
For i = 0 To kaisuu - 1 'ゼロからはじめるので最後はマイナス1しておく必要がある
kosuu = kosuu0 - 1 + i * flag1 '挿入するセル数-1
Range(Cells(gyo1, retu1 + i), Cells(gyo1 + kosuu, retu1 + i)).Select
Selection.Insert Shift:=xlDown
Next i
End If
'列に対して挿入していく場合
If gyo1 <> gyo9 Then
kaisuu = gyo9 - gyo1 + 1
If flag1 = 1 Then
kosuu0 = 1 '挿入するセル数の初期値
Else
kosuu0 = kaisuu '逆の場合は、初期値は回数分となります
flag1 = -1 'あとで使うのでキチンとしておく
End If
For i = 0 To kaisuu - 1
kosuu = kosuu0 - 1 + i * flag1 '挿入するセル数-1
Range(Cells(gyo1 + i, retu1), Cells(gyo1 + i, retu1 + kosuu)).Select
Selection.Insert Shift:=xlToRight
Next i
End If
End Sub
この機能を使う用途は広いのではないかと思われた。
第18回目から第20回目までの『凝った計算式の作り方』で紹介した計算式など、この機能で軽く出来てしまうのである。これは驚くべきことである。
確かに、通常はセルを階段状に使うとかいうものは少ないので、イメージはつきづらいのかもしれません。
難しいことはさておき、ここで使われたテクニックは、いろいろと活用できるともいます。
第21回で、
『 マクロではどうなるでしょうか。
色々な方法が考えられます。
1.計算式中、61以上のあるセルをクリアする。
2.物理的階段状にセルをクリアする。
3.複数の置き換えが可能なマクロで、置き換える。』
とあり、3のマクロを作りましたが、ひょなことから、2の類型を適用する事例があることを発見しました。
処理はセルの内容をクリアするのではなく、セルを挿入していくことです。
データの形態を階段状にしてしまおうというのです。これを二回繰り返すと、あとでセル(当然四角形で与える)を削除することによって、階段状になったものから、通常の四角の形にするということも出来ます。
基本機能は、セルを一つずつ多くして挿入していくことですが、
1.行及び列に対して処理できる。
2.例えば行を指定した場合、左端から一つずつ増やして挿入するのか、右端から一つずつ増やして挿入するのかの指定が出来る。
(削除は怖いのでこのマクロの対象とはしない。しかし、削除は、この挿入機能と通常の削除操作で可能です。すなわち、階段状のものに逆階段状にセルを挿入していき、正方形にしたあとで、セルを削除するということです)
前提としては、挿入したい範囲を指定しておきます。行で指定すれば下への挿入。列で指定すれば、右への挿入となります。
基本処理をマクロの記録でとって見ましょう。
Sub marco01()
Range("AE54:AG55").Select
Selection.Insert Shift:=xlDown
Range("AJ54:AK60").Select
Selection.Insert Shift:=xlToRight
End Sub
簡単な命令ですね。
次に、指定範囲の座標から必要な情報を取得します。
列1～列9、行1
行1～行9、列1 のいずれかです。
指定範囲の座標から、とってみましょう。
AAA = Selection.Address
p1 = InStr(AAA, ":")
AAA1 = Left(AAA, p1 - 1)
AAA9 = Mid(AAA, p1 + 1)
MsgBox ("セルの座標は、" & AAA & "最初の座表は、" & AAA1 & "最後の座標は、" & AAA9 & " です")
更に、列･行に分けて見ましょう。
p1 = InStr(Mid(AAA1, 2), "$")
retu1 = Mid(AAA1, 2, p1 - 1)
gyo1 = Mid(AAA1, p1 + 2)
p1 = InStr(Mid(AAA9, 2), "$")
retu9 = Mid(AAA9, 2, p1 - 1)
gyo9 = Mid(AAA9, p1 + 2)
MsgBox ("先頭のセルは、 " & retu1 & "--" & gyo1 & "最後のセルは、" & retu9 & "--" & gyo9 & " です")
別の方法で、列番号などをとってみましょう。
retu1=range(AAA1).column
gyo1=range(AAA1).row
retu9=range(AAA9).column
gyo9=range(AAA9).row
これまでのところを少し整理してみましょう
Sub a20セルの階段状挿入()
'範囲を指定したあとに実行する
AAA = Selection.Address
p1 = InStr(AAA, ":")
AAA1 = Left(AAA, p1 - 1) '先頭の座標
AAA9 = Mid(AAA, p1 + 1) '最後の座標
'チェック
MsgBox ("セルの座標は、" & AAA & "最初の座表は、" & AAA1 & "最後の座標は、" & AAA9 & " です")
'列番号と、行番号を取得
retu1 = Range(AAA1).Column
gyo1 = Range(AAA1).Row
retu9 = Range(AAA9).Column
gyo9 = Range(AAA9).Row
'チェック
MsgBox ("先頭のセルは、 " & retu1 & "--" & gyo1 & "最後のセルは、" & retu9 & "--" & gyo9 & " です")
次の段階へ。
'挿入する階段の形態の入力
flag1 = InputBox("一つずつ増やしていきますか(1)、減らしていきますか(-1) ", , 1)
'行に対して挿入していく場合
If gyo1 = gyo9 Then '先頭と最後の行が同じということは、行に対して挿入することになる
kaisuu = retu9 - retu1 + 1 '何列分あるかということ
If flag1 = 1 Then
kosuu0 = 1 '挿入するセル数の初期値
Else
kosuu0 = kaisuu '逆の場合は、初期値は回数分となります
flag1 = -1 'あとで使うのでキチンとしておく
End If
'チェック
' MsgBox ("回数は、 " & kaisuu & "--" & "挿入するセル数の初期値は、 " & kosuu0 & " です")
For i = 0 To kaisuu - 1 'ゼロからはじめるので最後はマイナス1しておく必要がある
kosuu = kosuu0 - 1 + i * flag1 '挿入するセル数-1
Range(Cells(gyo1, retu1 + i), Cells(gyo1 + kosuu, retu1 + i)).Select
Selection.Insert Shift:=xlDown
Next i
End If
'列に対して挿入していく場合
If gyo1 <> gyo9 Then
kaisuu = gyo9 - gyo1 + 1
If flag1 = 1 Then
kosuu0 = 1 '挿入するセル数の初期値
Else
kosuu0 = kaisuu '逆の場合は、初期値は回数分となります
flag1 = -1 'あとで使うのでキチンとしておく
End If
For i = 0 To kaisuu - 1
kosuu = kosuu0 - 1 + i * flag1 '挿入するセル数-1
Range(Cells(gyo1 + i, retu1), Cells(gyo1 + i, retu1 + kosuu)).Select
Selection.Insert Shift:=xlToRight
Next i
End If
End Sub
この機能を使う用途は広いのではないかと思われた。
第18回目から第20回目までの『凝った計算式の作り方』で紹介した計算式など、この機能で軽く出来てしまうのである。これは驚くべきことである。
確かに、通常はセルを階段状に使うとかいうものは少ないので、イメージはつきづらいのかもしれません。
難しいことはさておき、ここで使われたテクニックは、いろいろと活用できるともいます。
第21回目 これまでのテクニックの整理(第11回以降)
○第21回目 これまでのテクニックの整理(第11回以降)
◎第20回目
□置換え
Range("X15:X18").Select
Selection.Replace What:="*61*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
□指定した範囲の座標
AAA = Selection.Address '実行前に指定した範囲の座標がここに入る
◎第19回目
□文字列の加工
=LEFT(B67,9) & B66 $ RIGHT(B67,6)
xxx=sum(b7:b$60) を
xxx=sum(b :b$60)に分けるのです
↑ ↑
9 右から6文字目
◎第17回目
□"1-56のカラーコード"、または"カラーパレットのインデックス番号"
http://www.geocities.jp/ogawa7a/sheets_14.html#color
□余り a1=(i mod 3)
□商 a2=(i \ 3)
□ 7.GoToステートメント
http://excelvba.pc-users.net/fol6/6_7.html
◎第16回目
□色の設定は、
Cells(a, b).Interior.ColorIndex = 13 '1-56のカラーコード
または
Cells(a, b).Interior.Color = RGB(255,0,0) '赤明度、緑明度、青明度
◎第14回目
□セルの使い方
if cells(4,3+j) = ii then
◎第13回目
[免責事項]
このブログに掲載するマクロを使用した際に不具合が生じても、筆者がその責任を負うことはありません。
また、紹介するマクロ等に関しても同様です。
あくまで自己責任でご利用ください。
この旨は、第1回目にも追記しました。
◎第12回目
□配列変数の定義
Dim mm(8,8)
◎第11回目
便利マクロの事例
【例1】セルを結合させる。同時に文字中央にする。
【例2】格子状に罫線を引く
【例3】標準的なヘッダー、フッターの設定
【例4】そのシートの内容をノンストップで印刷。(シートの全ページが印刷されます。通常は印刷ダイアログが出て、手間が一つ余計にかかります)
【例5】指定範囲だけを印刷
【例6】文字を縦位置で中央揃えに
◎第20回目
□置換え
Range("X15:X18").Select
Selection.Replace What:="*61*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
□指定した範囲の座標
AAA = Selection.Address '実行前に指定した範囲の座標がここに入る
◎第19回目
□文字列の加工
=LEFT(B67,9) & B66 $ RIGHT(B67,6)
xxx=sum(b7:b$60) を
xxx=sum(b :b$60)に分けるのです
↑ ↑
9 右から6文字目
◎第17回目
□"1-56のカラーコード"、または"カラーパレットのインデックス番号"
http://www.geocities.jp/ogawa7a/sheets_14.html#color
□余り a1=(i mod 3)
□商 a2=(i \ 3)
□ 7.GoToステートメント
http://excelvba.pc-users.net/fol6/6_7.html
◎第16回目
□色の設定は、
Cells(a, b).Interior.ColorIndex = 13 '1-56のカラーコード
または
Cells(a, b).Interior.Color = RGB(255,0,0) '赤明度、緑明度、青明度
◎第14回目
□セルの使い方
if cells(4,3+j) = ii then
◎第13回目
[免責事項]
このブログに掲載するマクロを使用した際に不具合が生じても、筆者がその責任を負うことはありません。
また、紹介するマクロ等に関しても同様です。
あくまで自己責任でご利用ください。
この旨は、第1回目にも追記しました。
◎第12回目
□配列変数の定義
Dim mm(8,8)
◎第11回目
便利マクロの事例
【例1】セルを結合させる。同時に文字中央にする。
【例2】格子状に罫線を引く
【例3】標準的なヘッダー、フッターの設定
【例4】そのシートの内容をノンストップで印刷。(シートの全ページが印刷されます。通常は印刷ダイアログが出て、手間が一つ余計にかかります)
【例5】指定範囲だけを印刷
【例6】文字を縦位置で中央揃えに
2007年7月11日水曜日
第20回目 凝った計算式の作り方3、最後の調整
○第20回目 凝った計算式の作り方3、最後の調整
問題は、コピーすると、7行目を一つずつコピーするので、後ろの列の後ろの行になると、60行より大きなものができてしまうのです。
これは次のような意味です。
65行目のL列の計算式は、"=sum(L17:L$60)"となっています。
これを118(65+60-7)行目にコピーしますと、"=sum(L$60:L70)"となってしまうのです。これは無意味な計算式です。ちなみに、B列では、"=sum(B60:B$60)"となって、有効です。
無効な計算式のセルを力技でクリアすというのもありますが、能がないので違い方法を考えるべきでしょう。
エクセルの処理でやろうとすると、置き換えになります。無効な計算式は中に61以上の数値を含みますので、この性質を使います。
"*61*"をヌル(結果的にクリアされる)に変えるのです。これを62,63…と必要なだけ繰り換えすのです。
マクロではどうなるでしょうか。
色々な方法が考えられます。
1.計算式中、61以上のあるセルをクリアする。
2.物理的階段状にセルをクリアする。
3.複数の置き換えが可能なマクロで、置き換える。
3.でやってみましょうか。著者もまだ作っておらず、使い回しがききそうと、いう理由。
置換えに関して、マクロの記録で操作マクロをとって見ますと、次のようになります。
Sub Macro1()
' Macro1 Macro
Range("X15:X18").Select
Selection.Replace What:="*61*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
これを少しアレンジして、置換え元と置換え後が変数で動くかどうかを確かめます。
Sub a18Macro1()
'
' Macro1 Macro
a1 = "*61*"
a2 = ""
Range("X15:X18").Select
Selection.Replace What:=a1, Replacement:=a2, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
大丈夫です。動きました。(このように少しずつ確認していきます)
次に、もっと進めて。
置換え元の文字列、置換え後の文字列を作る。
Dim a_zen(20)
Dim a_go(20)
For i = 0 To 20
j = 61 + i
a_zen(i) = "*" & j & "*" '61から81を含むセルを指定するための文字列
a_go(i) = "" '置換え後はヌルにする。すなわちクリア。
Next i
置換え処理をループさせる。
AAA = Selection.Address '実行前に指定した範囲の座標がここに入る
For i = 0 To 20
Range(AAA).Select
Selection.Replace What:=a_zen(i), Replacement:=a_go(i), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i
【完成版】
Sub a18Macro1()
'複数項目の置換え。置換え元数値は自動生み出しとしている
'置き換えたい範囲を指定したあとで実行する。
Dim a_zen(20)
Dim a_go(20)
For i = 0 To 20
j = 61 + i
a_zen(i) = "*" & j & "*" '61から81を含むセルを指定するための文字列
a_go(i) = "" '置換え後はヌルにする。すなわちクリア。
Next i
AAA = Selection.Address
' MsgBox ("セルの座標は、" & AAA & " です") 'テスト行
For i = 0 To 20
Range(AAA).Select
Selection.Replace What:=a_zen(i), Replacement:=a_go(i), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i
End Sub
置換え元と置換え後文字列を一々指定する場合の文例
これをテキストファイルで必要な文字列に作っておき、必要な分をエクセルのマクロに貼り付けるといいでしょうね。
a_zen(0) = "*" & 61 & "*"
a_zen(1) = "*" & 62 & "*"
a_zen(2) = "*" & 63 & "*"
a_zen(3) = "*" & 64 & "*"
a_zen(4) = "*" & 65 & "*"
a_zen(5) = "*" & 66 & "*"
a_zen(6) = "*" & 67 & "*"
a_zen(7) = "*" & 68 & "*"
a_zen(8) = "*" & 69 & "*"
a_zen(9) = "*" & 70 & "*"
a_zen(10) = "*" & 71 & "*"
a_zen(11) = "*" & 72 & "*"
a_zen(12) = "*" & 73 & "*"
a_zen(13) = "*" & 74 & "*"
a_zen(14) = "*" & 75 & "*"
a_zen(15) = "*" & 76 & "*"
a_zen(16) = "*" & 77 & "*"
a_zen(17) = "*" & 78 & "*"
a_zen(18) = "*" & 79 & "*"
a_zen(19) = "*" & 80 & "*"
a_zen(20) = "*" & 81 & "*"
a_go(0) = ""
a_go(1) = ""
a_go(2) = ""
a_go(3) = ""
a_go(4) = ""
a_go(5) = ""
a_go(6) = ""
a_go(7) = ""
a_go(8) = ""
a_go(9) = ""
a_go(10) = ""
a_go(11) = ""
a_go(12) = ""
a_go(13) = ""
a_go(14) = ""
a_go(15) = ""
a_go(16) = ""
a_go(17) = ""
a_go(18) = ""
a_go(19) = ""
a_go(20) = ""
交互に並べた文例
a_zen(0) = "*" & 61 & "*"
a_go(0) = ""
a_zen(1) = "*" & 62 & "*"
a_go(1) = ""
a_zen(2) = "*" & 63 & "*"
a_go(2) = ""
a_zen(3) = "*" & 64 & "*"
a_go(3) = ""
a_zen(4) = "*" & 65 & "*"
a_go(4) = ""
a_zen(5) = "*" & 66 & "*"
a_go(5) = ""
a_zen(6) = "*" & 67 & "*"
a_go(6) = ""
a_zen(7) = "*" & 68 & "*"
a_go(7) = ""
a_zen(8) = "*" & 69 & "*"
a_go(8) = ""
a_zen(9) = "*" & 70 & "*"
a_go(9) = ""
a_zen(10) = "*" & 71 & "*"
a_go(10) = ""
a_zen(11) = "*" & 72 & "*"
a_go(11) = ""
a_zen(12) = "*" & 73 & "*"
a_go(12) = ""
a_zen(13) = "*" & 74 & "*"
a_go(13) = ""
a_zen(14) = "*" & 75 & "*"
a_go(14) = ""
a_zen(15) = "*" & 76 & "*"
a_go(15) = ""
a_zen(16) = "*" & 77 & "*"
a_go(16) = ""
a_zen(17) = "*" & 78 & "*"
a_go(17) = ""
a_zen(18) = "*" & 79 & "*"
a_go(18) = ""
a_zen(19) = "*" & 80 & "*"
a_go(19) = ""
a_zen(20) = "*" & 81 & "*"
a_go(20) = ""
※もう少し機能アップできますがしつこくなるのでこの辺で終了。
各自工夫してください。
問題は、コピーすると、7行目を一つずつコピーするので、後ろの列の後ろの行になると、60行より大きなものができてしまうのです。
これは次のような意味です。
65行目のL列の計算式は、"=sum(L17:L$60)"となっています。
これを118(65+60-7)行目にコピーしますと、"=sum(L$60:L70)"となってしまうのです。これは無意味な計算式です。ちなみに、B列では、"=sum(B60:B$60)"となって、有効です。
無効な計算式のセルを力技でクリアすというのもありますが、能がないので違い方法を考えるべきでしょう。
エクセルの処理でやろうとすると、置き換えになります。無効な計算式は中に61以上の数値を含みますので、この性質を使います。
"*61*"をヌル(結果的にクリアされる)に変えるのです。これを62,63…と必要なだけ繰り換えすのです。
マクロではどうなるでしょうか。
色々な方法が考えられます。
1.計算式中、61以上のあるセルをクリアする。
2.物理的階段状にセルをクリアする。
3.複数の置き換えが可能なマクロで、置き換える。
3.でやってみましょうか。著者もまだ作っておらず、使い回しがききそうと、いう理由。
置換えに関して、マクロの記録で操作マクロをとって見ますと、次のようになります。
Sub Macro1()
' Macro1 Macro
Range("X15:X18").Select
Selection.Replace What:="*61*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
これを少しアレンジして、置換え元と置換え後が変数で動くかどうかを確かめます。
Sub a18Macro1()
'
' Macro1 Macro
a1 = "*61*"
a2 = ""
Range("X15:X18").Select
Selection.Replace What:=a1, Replacement:=a2, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
大丈夫です。動きました。(このように少しずつ確認していきます)
次に、もっと進めて。
置換え元の文字列、置換え後の文字列を作る。
Dim a_zen(20)
Dim a_go(20)
For i = 0 To 20
j = 61 + i
a_zen(i) = "*" & j & "*" '61から81を含むセルを指定するための文字列
a_go(i) = "" '置換え後はヌルにする。すなわちクリア。
Next i
置換え処理をループさせる。
AAA = Selection.Address '実行前に指定した範囲の座標がここに入る
For i = 0 To 20
Range(AAA).Select
Selection.Replace What:=a_zen(i), Replacement:=a_go(i), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i
【完成版】
Sub a18Macro1()
'複数項目の置換え。置換え元数値は自動生み出しとしている
'置き換えたい範囲を指定したあとで実行する。
Dim a_zen(20)
Dim a_go(20)
For i = 0 To 20
j = 61 + i
a_zen(i) = "*" & j & "*" '61から81を含むセルを指定するための文字列
a_go(i) = "" '置換え後はヌルにする。すなわちクリア。
Next i
AAA = Selection.Address
' MsgBox ("セルの座標は、" & AAA & " です") 'テスト行
For i = 0 To 20
Range(AAA).Select
Selection.Replace What:=a_zen(i), Replacement:=a_go(i), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i
End Sub
置換え元と置換え後文字列を一々指定する場合の文例
これをテキストファイルで必要な文字列に作っておき、必要な分をエクセルのマクロに貼り付けるといいでしょうね。
a_zen(0) = "*" & 61 & "*"
a_zen(1) = "*" & 62 & "*"
a_zen(2) = "*" & 63 & "*"
a_zen(3) = "*" & 64 & "*"
a_zen(4) = "*" & 65 & "*"
a_zen(5) = "*" & 66 & "*"
a_zen(6) = "*" & 67 & "*"
a_zen(7) = "*" & 68 & "*"
a_zen(8) = "*" & 69 & "*"
a_zen(9) = "*" & 70 & "*"
a_zen(10) = "*" & 71 & "*"
a_zen(11) = "*" & 72 & "*"
a_zen(12) = "*" & 73 & "*"
a_zen(13) = "*" & 74 & "*"
a_zen(14) = "*" & 75 & "*"
a_zen(15) = "*" & 76 & "*"
a_zen(16) = "*" & 77 & "*"
a_zen(17) = "*" & 78 & "*"
a_zen(18) = "*" & 79 & "*"
a_zen(19) = "*" & 80 & "*"
a_zen(20) = "*" & 81 & "*"
a_go(0) = ""
a_go(1) = ""
a_go(2) = ""
a_go(3) = ""
a_go(4) = ""
a_go(5) = ""
a_go(6) = ""
a_go(7) = ""
a_go(8) = ""
a_go(9) = ""
a_go(10) = ""
a_go(11) = ""
a_go(12) = ""
a_go(13) = ""
a_go(14) = ""
a_go(15) = ""
a_go(16) = ""
a_go(17) = ""
a_go(18) = ""
a_go(19) = ""
a_go(20) = ""
交互に並べた文例
a_zen(0) = "*" & 61 & "*"
a_go(0) = ""
a_zen(1) = "*" & 62 & "*"
a_go(1) = ""
a_zen(2) = "*" & 63 & "*"
a_go(2) = ""
a_zen(3) = "*" & 64 & "*"
a_go(3) = ""
a_zen(4) = "*" & 65 & "*"
a_go(4) = ""
a_zen(5) = "*" & 66 & "*"
a_go(5) = ""
a_zen(6) = "*" & 67 & "*"
a_go(6) = ""
a_zen(7) = "*" & 68 & "*"
a_go(7) = ""
a_zen(8) = "*" & 69 & "*"
a_go(8) = ""
a_zen(9) = "*" & 70 & "*"
a_go(9) = ""
a_zen(10) = "*" & 71 & "*"
a_go(10) = ""
a_zen(11) = "*" & 72 & "*"
a_go(11) = ""
a_zen(12) = "*" & 73 & "*"
a_go(12) = ""
a_zen(13) = "*" & 74 & "*"
a_go(13) = ""
a_zen(14) = "*" & 75 & "*"
a_go(14) = ""
a_zen(15) = "*" & 76 & "*"
a_go(15) = ""
a_zen(16) = "*" & 77 & "*"
a_go(16) = ""
a_zen(17) = "*" & 78 & "*"
a_go(17) = ""
a_zen(18) = "*" & 79 & "*"
a_go(18) = ""
a_zen(19) = "*" & 80 & "*"
a_go(19) = ""
a_zen(20) = "*" & 81 & "*"
a_go(20) = ""
※もう少し機能アップできますがしつこくなるのでこの辺で終了。
各自工夫してください。
2007年7月9日月曜日
第19回目 凝った計算式の作り方2
○第19回目 凝った計算式の作り方2
斜めに数値を変化させていく表を作りました。(一つ横にいくと一つ下がる)
その数値を最初から最後まで足すという式を作ります。
一つ横にいくと一つ下がると変化させていくという計算式は簡単です。単純なコピーで出来ます。
だが、一つずつずれている同一のグループの和となると、同じ行であれば、SUM関数で簡単ですが、一つずつずれているので、一筋縄ではいきません。
(表には二種類の性格を持ったグループがあります。一つは最初からある、つまり最初の列にあるグループです。もう一つは、途中から発生するグループです。ここでは前者のグループのみを考えます)
マクロを作るのも手です。
が、ここでは計算式で作ってみます。
考え方です。
あるグループより下の合計を作る。
それをコピーして、もう一つ下のグルーの合計を求める。この上下の差が、求めたい一つのグループの合計となります。
第一番目が眼目です。
データはB7から、L60まであります。
最初のB列の合計は7行目から60行目まで。
次のC列の合計は8行目から60行目まで。
次のD列の合計は9行目から60行目まで。
となります。この合計は、65行目に入れます。
計算式の仕組みを表しますと、列記号と開始行番号が変わり、終わりの行番号が変わらないという形になります。
では、単純にコピーしてみましょう。最初のセルに式を作ります。これを横にコピーするのです。
B65は、"=sum(b7:b60)"となります。
(これは列記号が1桁ですので単純にやっていますが、本来は、二桁にして、完成後に列を削除してください)
あとのために、"=sum(b7:b$60)"としてください。
これを横にコピーします。
ここで問題になるのは、開始行です。この開始行を変えるのです。
まず、式を文字列にして見ましょう。
エクセルの置換で、"="を"xxx="してください。
これを67行目に値コピーをします。
66行目には、7から始まり+1づつしてください。隣の列は8行目から60行目の合計だからです。
この二つのセルの内容を一部結合させます。
それを、65行目に入れましょう。
式は、=LEFT(B67,9) & B66 $ RIGHT(B67,6)となります。
xxx=sum(b7:b$60) を
xxx=sum(b :b$60)に分けるのです
↑ ↑
9 右から6文字目
これを横にコピーします。
次のこれらを、65行に値コピーをします。
エクセルの置換で、"xxx="を"="に修正すると出来上がりです。
これが正しいことを確かめたら、これを66行目以降にコピーします。
すなわち、今度は、B列では一つ上がって8行目からの合計になります。(終了行は"$"をつけているので変わりません)
これで完成と思いきや、まだなのです。
問題は、コピーすると、7行目を一つずつコピーするので、後ろの列の後ろの行になると、60行より大きなものができてしまうのです。
今作った計算式を、xxxをつけて文字列にして対処してみましょう。
またまた難しいです。不要な箇所が階段状になるからです。
斜めに数値を変化させていく表を作りました。(一つ横にいくと一つ下がる)
その数値を最初から最後まで足すという式を作ります。
一つ横にいくと一つ下がると変化させていくという計算式は簡単です。単純なコピーで出来ます。
だが、一つずつずれている同一のグループの和となると、同じ行であれば、SUM関数で簡単ですが、一つずつずれているので、一筋縄ではいきません。
(表には二種類の性格を持ったグループがあります。一つは最初からある、つまり最初の列にあるグループです。もう一つは、途中から発生するグループです。ここでは前者のグループのみを考えます)
マクロを作るのも手です。
が、ここでは計算式で作ってみます。
考え方です。
あるグループより下の合計を作る。
それをコピーして、もう一つ下のグルーの合計を求める。この上下の差が、求めたい一つのグループの合計となります。
第一番目が眼目です。
データはB7から、L60まであります。
最初のB列の合計は7行目から60行目まで。
次のC列の合計は8行目から60行目まで。
次のD列の合計は9行目から60行目まで。
となります。この合計は、65行目に入れます。
計算式の仕組みを表しますと、列記号と開始行番号が変わり、終わりの行番号が変わらないという形になります。
では、単純にコピーしてみましょう。最初のセルに式を作ります。これを横にコピーするのです。
B65は、"=sum(b7:b60)"となります。
(これは列記号が1桁ですので単純にやっていますが、本来は、二桁にして、完成後に列を削除してください)
あとのために、"=sum(b7:b$60)"としてください。
これを横にコピーします。
ここで問題になるのは、開始行です。この開始行を変えるのです。
まず、式を文字列にして見ましょう。
エクセルの置換で、"="を"xxx="してください。
これを67行目に値コピーをします。
66行目には、7から始まり+1づつしてください。隣の列は8行目から60行目の合計だからです。
この二つのセルの内容を一部結合させます。
それを、65行目に入れましょう。
式は、=LEFT(B67,9) & B66 $ RIGHT(B67,6)となります。
xxx=sum(b7:b$60) を
xxx=sum(b :b$60)に分けるのです
↑ ↑
9 右から6文字目
これを横にコピーします。
次のこれらを、65行に値コピーをします。
エクセルの置換で、"xxx="を"="に修正すると出来上がりです。
これが正しいことを確かめたら、これを66行目以降にコピーします。
すなわち、今度は、B列では一つ上がって8行目からの合計になります。(終了行は"$"をつけているので変わりません)
これで完成と思いきや、まだなのです。
問題は、コピーすると、7行目を一つずつコピーするので、後ろの列の後ろの行になると、60行より大きなものができてしまうのです。
今作った計算式を、xxxをつけて文字列にして対処してみましょう。
またまた難しいです。不要な箇所が階段状になるからです。
2007年7月8日日曜日
第18回目 凝った計算式の作り方1
第18回目 凝った計算式の作り方1
たまに、単純にコピーしたのでは複写できない計算式があります。
例えば、横に展開されている合計を、縦にするというようなものです。
コピーの縦横変換で出来るにはできるのですが、操作が入ってしまい、自動化が出来ればと思うものです。
このような用途はたまに出てきます。
その1 縦のものを横にする。
BA5から縦に合計が40個並んでいます。これをB列の50行目から、横に展開します。
式は簡単なのです。B50に"=BA5"というように計算式を入れていけばいいのです。
このような場合工夫はないものでしょうか。
BA5の5のところを+1していけばいいのです。
B51に文字として、xxx=BA といれます。
B52に5といれます。
B52から横に+1していきます。C52は6になり、D52は7になります。
B53にこれらの文字を結合します。=$B$51 & B52 とし、これを横にコピーします。
すると、計算結果は、xxx=BA5、xxx=BA6、xxx=BA7…というようになります。
50行目にコピーします。値のみです。
ここで頭の"xxx"をとれば、求める数式になるのです。
これは、置き換えで出来ます。(うまく数式にならないことがあります。原因はよくわかりません。操作を戻して、"50行目にコピーします。値のみです"の後に、同じ場所に、更に文字を"罫線の除くすべて"を指定してコピーしてからするとうまくいきます)
この考え方は、変数的な要素を入れて文字列を結合し、文字列を数式に戻すということです。
その2 横のものを縦にする。
50行目に合計が横にB列から40個並んでいます。これをBA列の5行目から、縦に展開します。
今度は数字ではなく、文字記号を変数にする必要があります。B,C,D,E…と変化させていき、文字列を結合すればいいのです。
では、どのように列記号の文字を作っていけばいいのでしょうか。
簡単です。配列変数にA(1列)からIV(256列)までを入れていき、配列の番号と列記号を対応させておけばいいんです。
IVは、"I" & "V" ですので、工夫すればすぐ出来ます。力技でもそれなりの時間で出来ます。
他にもいろいろな工夫があると思います。
たまに、単純にコピーしたのでは複写できない計算式があります。
例えば、横に展開されている合計を、縦にするというようなものです。
コピーの縦横変換で出来るにはできるのですが、操作が入ってしまい、自動化が出来ればと思うものです。
このような用途はたまに出てきます。
その1 縦のものを横にする。
BA5から縦に合計が40個並んでいます。これをB列の50行目から、横に展開します。
式は簡単なのです。B50に"=BA5"というように計算式を入れていけばいいのです。
このような場合工夫はないものでしょうか。
BA5の5のところを+1していけばいいのです。
B51に文字として、xxx=BA といれます。
B52に5といれます。
B52から横に+1していきます。C52は6になり、D52は7になります。
B53にこれらの文字を結合します。=$B$51 & B52 とし、これを横にコピーします。
すると、計算結果は、xxx=BA5、xxx=BA6、xxx=BA7…というようになります。
50行目にコピーします。値のみです。
ここで頭の"xxx"をとれば、求める数式になるのです。
これは、置き換えで出来ます。(うまく数式にならないことがあります。原因はよくわかりません。操作を戻して、"50行目にコピーします。値のみです"の後に、同じ場所に、更に文字を"罫線の除くすべて"を指定してコピーしてからするとうまくいきます)
この考え方は、変数的な要素を入れて文字列を結合し、文字列を数式に戻すということです。
その2 横のものを縦にする。
50行目に合計が横にB列から40個並んでいます。これをBA列の5行目から、縦に展開します。
今度は数字ではなく、文字記号を変数にする必要があります。B,C,D,E…と変化させていき、文字列を結合すればいいのです。
では、どのように列記号の文字を作っていけばいいのでしょうか。
簡単です。配列変数にA(1列)からIV(256列)までを入れていき、配列の番号と列記号を対応させておけばいいんです。
IVは、"I" & "V" ですので、工夫すればすぐ出来ます。力技でもそれなりの時間で出来ます。
他にもいろいろな工夫があると思います。
2007年7月7日土曜日
第17回目 データ処理マクロの基礎2-5
○第17回目 データ処理マクロの基礎2-5
前回出てきた"1-56のカラーコード"、すなわち"カラーパレットのインデックス番号"については、下記のホームページを参照してください。
http://www.geocities.jp/ogawa7a/sheets_14.html#color
今回は、行の部分と3*3ブロックの色塗りです。
列のマクロをコピーして、修正するといいでしょう。
セルのコントロールの仕方だけを注視すればすぐできると思います。
行の場合の検査値は、O列=15列に入っています。
数値と行番号の対応は列と同じです。
色をぬる場所が異なります。
今度は、横に色をぬっていきます。それから次の行に移ります。
'行の部分
For i = 0 To 8 '下に動かす
If Cells(14 + suuti + i * 9, 15) = 9 Then 'ここを修正
For j = 0 To 8 '横に動かす
Cells(4 + i, 3 + j).Interior.ColorIndex = 6 '黄色 ここを修正
Next j
End If
Next i
それでは、3*3ブロックの部分です。
'3*3ブロック
For i = 0 To 8 '3*3ブロックの番号順に動かす
If Cells(14 + suuti + i * 9, 15) = 9 Then 'ここを修正
For j = 0 To 8 '3*3ブロックの中を動かす
Cells(4 + i, 3 + j).Interior.ColorIndex = 6 '黄色 ここを修正
Next j
End If
Next i
上記のものは、ただ行の部分をコピーし、コメント部分を少し修正しただけです。
動くものはjとiがあります。jは、ブロックの中をうごくもので、iはブロックの場所が動くものです。
3つづという規則性がありますので、3でわって商と余りを使います。
jについては、余りが行の位置、商が列の位置を示します。
ブロックの左上のセルから見て、(基準行数+余り、基準列番号+商)となっています。
iによってこの基準となるセルが動きます。これも表の左上のセル(4,3)から見て、
iの商と余りを用いて、(4+余り*3,3+商*3)となります。
'3*3ブロック
For i = 0 To 8 '3*3ブロックの番号順に動かす
a1=4+(i mod 3)*3
a2=3+(i\ 3)*3
If Cells(14 + suuti + i * 9, 17) = 9 Then
For j = 0 To 8 '3*3ブロックの中を動かす
b1=4+(j mod 3)
b2=3+(j \ 3)
Cells(a1+b1,a2+b2).Interior.ColorIndex = 8 '水色
Next j
End If
Next i
このあとに、数値が入っているセルにも色をぬりましょう。
判断は数字が入っているかヌルがはいっているか否かです。
これは表だけでできます。
'数値の入っているセル
For i = 0 To 8
For j = 0 To 8
If Cells(4 + j, 3+i) <> "" Then ' ""はヌル(空白)のこと
Cells(4 + j, 3+i).Interior.ColorIndex = 15 '灰色
End If
Next j
Next i
あとは、追加すべき機能としては、一々終了しないで、繰り返すかどうかを聞く機能でしょう。
Inputboxで、終了する場合は99を、繰り返す場合の新しい調べたい数値を入れるようにしたらどうでしょうか。
suuti = InputBox("終了しますか。終了の場合は99を、繰り返す場合は1-9までの値を入れてください。", , 99)
このsuutiをif文で聞いてみてください。
さて、繰り返す場合、どうやって、上に戻るのでしょうか？
goto文があるのですが、ほとんど使用例をみたことがないのです…
以下のような記述を見つけましたので、少しだけの使用にしておきましょう。
http://excelvba.pc-users.net/fol6/6_7.html
7.GoToステートメント
---------------------------------------------------------------------------
プロシージャ内で処理を分岐させたい場合にはGoToステートメントを使用することができます。ただし、GoToステートメントを多用するとプログラムが分かりにくくなるのでなるべく使用しない方が良いでしょう。
Sub Goto文()
MsgBox ("Gotoステートメントのテスト1")
GoTo Label1
MsgBox ("Gotoステートメントのテスト2") '→実行されない
Label1:
MsgBox ("Gotoステートメントのテスト3")
End Sub
ここに記述されたとおり、
Label1:
と
GoTo Label1 というペアで使います。
ちょっとした応用問題ですので、各自チャレンジしてみてください。
前回出てきた"1-56のカラーコード"、すなわち"カラーパレットのインデックス番号"については、下記のホームページを参照してください。
http://www.geocities.jp/ogawa7a/sheets_14.html#color
今回は、行の部分と3*3ブロックの色塗りです。
列のマクロをコピーして、修正するといいでしょう。
セルのコントロールの仕方だけを注視すればすぐできると思います。
行の場合の検査値は、O列=15列に入っています。
数値と行番号の対応は列と同じです。
色をぬる場所が異なります。
今度は、横に色をぬっていきます。それから次の行に移ります。
'行の部分
For i = 0 To 8 '下に動かす
If Cells(14 + suuti + i * 9, 15) = 9 Then 'ここを修正
For j = 0 To 8 '横に動かす
Cells(4 + i, 3 + j).Interior.ColorIndex = 6 '黄色 ここを修正
Next j
End If
Next i
それでは、3*3ブロックの部分です。
'3*3ブロック
For i = 0 To 8 '3*3ブロックの番号順に動かす
If Cells(14 + suuti + i * 9, 15) = 9 Then 'ここを修正
For j = 0 To 8 '3*3ブロックの中を動かす
Cells(4 + i, 3 + j).Interior.ColorIndex = 6 '黄色 ここを修正
Next j
End If
Next i
上記のものは、ただ行の部分をコピーし、コメント部分を少し修正しただけです。
動くものはjとiがあります。jは、ブロックの中をうごくもので、iはブロックの場所が動くものです。
3つづという規則性がありますので、3でわって商と余りを使います。
jについては、余りが行の位置、商が列の位置を示します。
ブロックの左上のセルから見て、(基準行数+余り、基準列番号+商)となっています。
iによってこの基準となるセルが動きます。これも表の左上のセル(4,3)から見て、
iの商と余りを用いて、(4+余り*3,3+商*3)となります。
'3*3ブロック
For i = 0 To 8 '3*3ブロックの番号順に動かす
a1=4+(i mod 3)*3
a2=3+(i\ 3)*3
If Cells(14 + suuti + i * 9, 17) = 9 Then
For j = 0 To 8 '3*3ブロックの中を動かす
b1=4+(j mod 3)
b2=3+(j \ 3)
Cells(a1+b1,a2+b2).Interior.ColorIndex = 8 '水色
Next j
End If
Next i
このあとに、数値が入っているセルにも色をぬりましょう。
判断は数字が入っているかヌルがはいっているか否かです。
これは表だけでできます。
'数値の入っているセル
For i = 0 To 8
For j = 0 To 8
If Cells(4 + j, 3+i) <> "" Then ' ""はヌル(空白)のこと
Cells(4 + j, 3+i).Interior.ColorIndex = 15 '灰色
End If
Next j
Next i
あとは、追加すべき機能としては、一々終了しないで、繰り返すかどうかを聞く機能でしょう。
Inputboxで、終了する場合は99を、繰り返す場合の新しい調べたい数値を入れるようにしたらどうでしょうか。
suuti = InputBox("終了しますか。終了の場合は99を、繰り返す場合は1-9までの値を入れてください。", , 99)
このsuutiをif文で聞いてみてください。
さて、繰り返す場合、どうやって、上に戻るのでしょうか？
goto文があるのですが、ほとんど使用例をみたことがないのです…
以下のような記述を見つけましたので、少しだけの使用にしておきましょう。
http://excelvba.pc-users.net/fol6/6_7.html
7.GoToステートメント
---------------------------------------------------------------------------
プロシージャ内で処理を分岐させたい場合にはGoToステートメントを使用することができます。ただし、GoToステートメントを多用するとプログラムが分かりにくくなるのでなるべく使用しない方が良いでしょう。
Sub Goto文()
MsgBox ("Gotoステートメントのテスト1")
GoTo Label1
MsgBox ("Gotoステートメントのテスト2") '→実行されない
Label1:
MsgBox ("Gotoステートメントのテスト3")
End Sub
ここに記述されたとおり、
Label1:
と
GoTo Label1 というペアで使います。
ちょっとした応用問題ですので、各自チャレンジしてみてください。
登録:
コメント (Atom)
