スポンサーリンク
上図のようなExcelで、「集計」シートのC2セルに「売上」シートのB列:担当者が＜加藤＞の売上金額をSUMIF関数で求めようとした場合(今回の例題では、求め方そのものに大した意味はなくて、別シートを参照することだけ何となく追えば良いです)。
計算式の入力方法を、オーソドックスにマウスを使ってやる場合、順に追うと
(2)「集計」シートに戻りB2セルをクリック
(3)「売上」シートのC2:C15セルまでをドラッグ
という感じになる。
ここで上記(2)の操作をやったとき、いったん別シート(売上シート)をクリックしていた関係から、下図のように自分自身のシート名を参照する「集計!」という文字が自動で付加されてしまう。
自シートを参照する計算式は邪魔で面倒
この例の場合、自動追加された「集計!」の文字は削除してしまって良いわけだが、こういう場合は皆さんはどうされているだろうか。
特に気にならないからそのまま放置するという人もいるかもしれないが、もしこのシート名が凄く長かったりしたら、放置しておくと絶対に見苦しくなるし計算式も分かりにくくなる。
もちろんこの例の場合のように1箇所だけなら「集計!」の文字をサッとDeleteすれば簡単なことだ。
しかしシート名がもっと凄く長かったり、こういう自シート参照の式があちこち多数箇所に放置され残っていたら、消す作業も割と面倒だろう。
多数箇所に残っている場合の対処は、検索・置換で何とかなるにはなる。
「集計!」の文字を全部削除する置換をすれば良い。
しかし、もし「集計」シートの名称が「2018集計」とか最初が数字で始まる名称だったりした場合は、自シート参照の計算式は「‘2018集計‘!」のようにシングルクォーテーション「‘」の文字が追加されることになる。
だから置換するにせよ、シングルクォーテーション文字が付くのか付かないのか確認しないといけないし、こういう作業をしょっちゅうすることになったら地味に面倒だ。
マクロを作った
SUMIF関数に限らず、VLOOKUP関数とかでもだし、あるいは関数すら使わずとも、こういった問題は結構発生する。
そこで私は、計算式にシングルクォーテーション文字が付く場合でも付かない場合でも、自分自身のシート名を参照する式は全て問答無用でその参照部分を削除するマクロを作成してみた。
このマクロのソースコードそのものに大した重要性はなくて、大事なのはそこに至る考え方・過程の方だから、それを述べていく。
さて、計算式にシングルクォーテーション文字が付くのか付かないのか毎回判断する手間が面倒だというのならば、「シングルクォーテーション文字が付かない」ケースに力尽くで一括変換してしまえば良い。
元々のシート名がどんなものであろうともそれを行うためには、シート名をいったん仮のものに変更してしまえば良い。
だがその仮シート名が既存の他のシート名と重複してしまってはいけないので、決して重複しないシート名を設けなければならない。
シート名に限らず、そういう絶対に重複しない名前を作るためには、日付・時刻(特に時・分・秒単位まで)を使った名称にするのが良いやり方だと思う。
そこで下記のマクロでは、現在時刻を時・分・秒まで取って、重複のありえないシート名を仮に設ける記述をしている。
しかし現在時刻を用いるやり方というのは必然的に数値を用いることになるので、そのままでは自シート参照計算式にシングルクォーテーション文字が付いてしまうことになり邪魔だ。
そこで、先頭に数字ではない「A」の文字を追加することによって、シングルクォーテーション文字が発生しないようにしている。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
Sub delete_SelfReferenceFormula() '自シートを参照する式の不要部分を削除 Dim ws As Worksheet Dim rng As Range Dim wsName As String Dim tmpName As String Dim deleteStr As String Dim siki As String If MsgBox("自シートを参照する式を削除しますか？", vbQuestion + vbOKCancel, "確認") = vbCancel Then Exit Sub Set ws = ActiveSheet tmpName = "A" ＆ Format$(Now, "mmddhhmmss") '現在時刻を利用して、重複のあり得ないシート名を作成 deleteStr = tmpName ＆ "!" With ws wsName = .Name '元のシート名を保存しておく .Name = tmpName '現在時刻から生成した一時的シート名 For Each rng In .Cells.SpecialCells(xlCellTypeFormulas) '計算式の入ったセルは全て対象にする siki = rng.Formula '計算式 siki = Replace$(siki, Find:=deleteStr, Replace:="") '自シートを参照する部分を削除 rng.Formula = siki '修正後の計算式に直す Next rng .Name = wsName '元のシート名に戻す End With End Sub |
このマクロは、Excelの個人用マクロブックに組み込んで使うと良いだろう。
上記のマクロでは、計算式の入ったセルを総当たりしていって、変数sikiを加工するやり方をしてみている。
これの他には、Excelの通常機能である置換機能をマクロで用いて「cells.Replace」とかいう処理をする方法もあり、それの方がシンプルといえばシンプルだ。
しかし私は、Excelの検索置換機能をなるべくマクロで使いたくはない。
なぜならマクロの中で検索置換を行うと、そのとき行った検索の条件などがそのまま残ってしまって、次に使う人に迷惑をかけることになりかねないからだ。
その検索条件をリセットしておくという方法もあるが、リセットしたらしたでそれも迷惑になりかねないし、またそのリセット処理が面倒だ。
だから今回の例では、検索置換機能は使わずなるべく変数の中で処理するようにしている。
検索置換機能に限らず、Excelのセルを直接参照はせずになるべく変数の中で処理完結するのが、スマートなやり方だと思う。
この辺りも、今後、他の色々なマクロの中で述べていきたい。
スポンサーリンク
