スポンサーリンク
マクロのソースコード
今回はExcelで、いくつかセルを選択しているときに、その中で色の付いていないセルだけを選択し直すマクロを紹介する。
「そんなことをして何の役に立つんだ」とか「どうして色の付いていないセルなんだ。色の付いているセルを選ぶなら分かるが。」とか思われるかもしれないが、その辺は後で説明する。
まずはソースコードから。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Sub jumpToNoneColorCells() '色の付いていないセルだけを選択する Dim rng As Range Dim selectRng As Range For Each rng In Selection.SpecialCells(xlCellTypeVisible) '選択範囲のうち可視セルのみを対象として検索 If rng.Interior.ColorIndex = xlNone Then 'セルに色が付いていない場合 If selectRng Is Nothing Then '最初にヒットした場合 Set selectRng = rng Else Set selectRng = Application.Union(selectRng, rng) '色が付いていないセルを選択範囲に追加していく End If End If Next rng If Not selectRng Is Nothing Then selectRng.Select'取得されたセル範囲を選択する End Sub |
このマクロは、Excelの個人用マクロブックに組み込んで使うと良いだろう。
もしこれを、「色の付いたセルだけを選択する」マクロに変更したければ、途中の
If rng.Interior.ColorIndex = xlNone Then ‘セルに色が付いていない場合
を、
xlNone Then ‘セルに色が付いている場合
と修正すれば良い。
経理の開示業務では面倒が多い
なぜこんなマクロを作ったのか。それは、私が経理業務で感じている煩わしさと関係している。
まず前提知識として経理では、「現金」だとか「売掛金」だとか様々な項目のことを「勘定科目」とか「科目」と呼ぶ。
以下、これらを単に「科目」と呼んで進める。
さて経理業務では、特に世間に決算書を開示するといった業務において、
「金額の小さい科目は『その他』という科目にまとめる」
「千円単位に直す」
とかいう面倒臭い作業がしょっちゅう発生する。
例えば次の図で「科目名」とある列は、自分の会社で実際に使用している科目の名前だ。
しかしその中で「立替金」「前渡金」「仮払金」という科目については、他の科目の残高に比べて非常に少額の残高しかない。
こういう場合、それらの科目について「立替金」「前渡金」「仮払金」というように個別の科目をいちいち表示する事はせずに、「その他」として合計金額をまとめなければならない。
どういう科目を「その他」とするかには、全体の20%未満だったらとか具体的基準があることもあれば、ある程度は自分たちの判断で決めていくものもある。
しかも、円単位で表示していた金額を、千円単位とか百万円単位(切り捨て)に直すという作業も必要になる。
最終的に、次の図のように直していくことになる。
「その他」の計算が面倒
銀行預金などの残高が大きな科目は、30,000千円だと表示されているなら、それを見たまま入力すれば良い。
しかし「立替金」「前渡金」「仮払金」の集まった「その他」科目については、それらの合計金額を改めて計算しないと入力できない。
この例では1,855+2,438+1,650となるわけだが、もっとややこしい計算になることは多いし、暗算とかではやってられんしとても面倒だ。
実際の作業としては、元々の円単位でのデータがExcelの表にまとまっていて、その中から「その他」に該当する科目を選び出して、その合計を計算したりすることになる。
世間に開示する決算用データというのは、Excelファイルの中で作業が完結するわけではなく、プロネクサスだとかいう外部のシステムを使って開示用のデータを入力していくのだけど、その入力したデータが元データのExcelファイルと合致しているかの目視照合は、どうしても必要になる。
たとえば次の図は、6/30の決算情報を7/2には開示するという(普通そんなことはできなくて、1ヶ月前後の日数が掛かる)、開示が超早いことで知られるあみやき亭の開示情報の抜粋だが、百万円単位で開示されていて「その他」科目もある。
これらを、円単位で持っている元のExcelデータと合致しているか、照合していくわけだ。
いやもちろん「その他」科目の合計をいちいち計算し直したりしなくても良いよう、自動で計算されておくようにExcelファイル上で最善を尽くしはする。でもそれも限界がある。
たとえば「試しにこの科目は『その他』科目に含めずにいこう」とか逆に「この科目は『その他』科目に含めてしまおう」とかアドリブを要することもある。
そういう場合、今回の例のように円単位データの中で「立替金」「前渡金」「仮払金」科目が離れ離れの行にあったりした場合、それらの合計を求めるのが面倒だ。
そういう場合の応急的作業としてよくやるのが、「その他」以外の科目はストレートに照合できるからそれら科目の残高のセルに照合のつど色を付けていき、色が付けられず残った科目が「その他」科目なので、それらの残高をマウスでドラッグして合計を求めるといった安直なやり方だ。
お分かりかと思うが、こういう場合、「その他」科目の離れ離れのセル(この例の場合、C5・C7・C9セル)を毎回選んでいく(方法としてはCtrlキーを押しながら選択)のは面倒だ。
C2～C9セルをガッと一気に選んで、その中で色の付いていないセルだけを選び直すことができれば、ステータスバーに「その他」科目の合計値が表示される(ステータスバーの合計値表示など、参考記事はこちら)。これを実現したいと思ったのが、冒頭のマクロだ。
同マクロを個人用マクロブックに組み込んでおいて、色の付いているセル・付いていないセルを同時に一気に選びまくってからマクロを起動すれば、色の付いていないセルだけを選択し直すことができる。
余談：ジャンプ機能の拡充
今回のやつは、Excelの「ジャンプ」機能の中に、「色の付いていないセル」にジャンプするといった機能があれば標準機能だけで目的達成できたわけだ。
でもそれはないので代用でマクロを作ったわけで、ソースコード中の
If rng.Interior.ColorIndex = xlNone Then ‘セルに色が付いていない場合
の条件文を別の条件に書き換えれば、かなり色々なオリジナルのパターンでジャンプ機能をカスタマイズすることが可能だということだ。
ソースコードの解説
ソースコードを再掲し、少し解説を加える。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Sub jumpToNoneColorCells() '色の付いていないセルだけを選択する Dim rng As Range Dim selectRng As Range For Each rng In Selection.SpecialCells(xlCellTypeVisible) '選択範囲のうち可視セルのみを対象として検索 If rng.Interior.ColorIndex = xlNone Then 'セルに色が付いていない場合 If selectRng Is Nothing Then '最初にヒットした場合 Set selectRng = rng Else Set selectRng = Application.Union(selectRng, rng) '色が付いていないセルを選択範囲に追加していく End If End If Next rng If Not selectRng Is Nothing Then selectRng.Select'取得されたセル範囲を選択する End Sub |
まず
For Each rng In Selection.SpecialCells(xlCellTypeVisible)‘選択範囲のうち可視セルのみを対象として検索
では、「SpecialCells(xlCellTypeVisible) 」で、ユーザが選択したセルの中で対象を可視セル＝見えているセルだけに限定し直すわけだ。
可視セルだけを選ぶという操作については、別記事でも触れています。
次に、今回の一番のキーが
Set selectRng = Application.Union(selectRng, rng) ‘色が付いていないセルを選択範囲に追加していく
の箇所。
まず、色が付いていないセルの1個めがヒットしたら、それをselectRngというものにセットするわけだけど、それが
|
1 2 |
If selectRng Is Nothing Then '最初にヒットした場合 Set selectRng = rng |
の部分。「selectRng Is Nothing」の条件がTrueとなっている間というのは、まだ色無しセルが検索ヒットしていない状態なわけで、それがヒットしたなら
で初めてselectRng をセットしようということだ。
では、2個め以降の色無しセルがヒットした場合は、いまセットしたselectRngに追加していかないといけない。
C3セルが1個めにヒットしたなら、C5やC7といった離れ離れのセルも、selectRngに追加していかないといけないわけだ。
そういう風に、セルが離れ離れであろうと、変数の中にセルを追加していくのがApplication.Unionだ。
私もそんなに使うことのないテクニックだけど、今回のようなジャンプ機能の拡充みたいな用途だったら、かなり便利かと思う
うーん、解説がすごくクドく小難しくなってしまった。オブジェクトがどうのと専門用語は極力排していこうと思うのだけど、そういうのはやはり無理があるのかな。
スポンサーリンク
