ExcelマクロでMsgBoxの処理をインターセプト

Excelマクロで以下のMacro1ように、中でMsgBoxを呼び出していると、いちいちメッセージボックスが表示される。


これを別マクロ(Macro2)から複数回呼び出すと、複数回メッセージボックスが表示され、何度も応答しなければならなくなる。

Macro1を別マクロから複数回呼び出しにも耐えられるように、Macro1に手を加えないで、メッセージボックスにOKボタンを押したことにしたい。


そこで、MsgBoxの呼び出しをインターセプトしてみる。

オブジェクトブラウザによると、MsgBoxはVBAライブラリにあるFunctionで第1引数が必須で、戻り値がVbMsgBoxResultのよう。

ということで、VBAライブラリよりも早く呼び出されるように、同プロジェクトの標準モジュールにMsgBox関数を定義する。
まず、標準モジュール追加で、



追加されたモジュールのオブジェクト名をわかりやすく「MsgBoxインターセプト」とする。




この「MsgBoxインターセプト」標準モジュールにMsgBox関数を追加する。



コードは以下のもの。
If文の条件をインターセプトするかどうかの判断で、インターセプトするなら、Then以降の部分に処理を記述し、
インターセプトしないなら、Else以降の部分で、VBAライブラリのMsgBox関数を受け取った引数で呼び出して、結果をMsgBoxに格納する。


Function MsgBox(Prompt As String, Optional Buttons As VbMsgBoxStyle = vbOKOnly, Optional Title, Optional HelpFile, Optional Context) As VbMsgBoxResult
    If Left(Prompt, 2) = "終了" Then
        MsgBox = vbOK
        Exit Function
    Else
        MsgBox = VBA.MsgBox(Prompt, Buttons, Title, HelpFile, Context)
    End If
End Function



これで、メッセージボックスを表示したくない場合の動きができる。
実用ではメッセージボックスを表示しない代わりに、引数と呼び出し時間をログ出力する方がよいかもです。

Excelセル結合での裏技と注意点

  Excelではセル結合という機能があり、これを使うと長方形の複数セルを1まとめで表示させることができる。

この機能を使うことは(他の有識者と同様に)私もお勧めしませんが、使わなければならない時や、
使っても実害がないときで、ダメな理由を説明するのが面倒な時があり、私自身たまに使っています。

実例として、以下のA2～A6セルを結合しようとすると、


以下のメッセージのように結合したセルは、左上セル以外のセルが空欄になる。
(A2セルの値はグループAだが、A3セルからA6のセルの値は空)


なので、オートフィルターを使ってA列の値が「グループA」の行を抽出しようとすると、A2の行のみ表示される。


で、ここからが本題。結合したA2からA5セルと同形のセルをコピーし、「形式を選択して貼り付け」から、
  「数式(F)」もしくは「数式と数値の書式(R)」もしくは「値と数値の書式(U)」をすると、結合セルの左上以外のセルにもコピーされる。

  
  
結果、A7セルで表示している、A2～A6までで値が入っているセルの数え上げが5(貼り付け前は1)、E列で表示しているA列とB列の一致が
2行目から6行目まで一致となっている(貼り付け前は2行目のみ一致)。
これで、オートフィルターも使えるようになる。


問題はここから、
集計する予定があるセルを結合した場合、結合時点では、表示されている値のみで集計される。(C7セルが1)



ただ、同じように形式:数式で貼り付けをした場合、非表示の値も集計結果に表れる。(C7セルが15)




普通に過ごしていたら、こんなことはしないと思うのだけど、
数式で貼り付けすると結合セルの中身まで更新されることが一般的ではない状況なので、悪い考えを持った人が、
自分の数字をごまかすために使ってくるかもしれないと警戒する必要はあるかと思います。
(Excelのごまかし表示方法は、テキストボックスでの覆いかぶせなど、いくらでもあるので結局Excelの作成者の人を見て信じるしかないのですが)

Excelのピボットテーブルでより自由な集計へ

Excelのピボットテーブルでは、単純な集計なら直観的にできます。
ただ複雑な集計となると、少し直感的でない操作をする必要があるので備忘録。

まずは単純な集計
例えば、以下の2015-2020年の県別人口から、

  

  
 ピボットテーブルで、県別最大人口を出すには、[挿入]メニューピボットテーブルで、
  

ピボットテーブルをつくり、

ドラッグアンドドロップで、「県名」を「行」の場所に配置し、「人口(千人)」を、「値」の場所に配置し、


「合計/人口(千人)」と表示された部分を左クリックし「値フィールドの設定」から、合計を最大に切り替える。


  
で基本的な集計はできた。
  
  
SQL文で疑似的に書くと以下のような感じ。
SELECT 県名,MAX(人口(千人)) FROM 県別人口 GROUP BY 県名

で本題。以下のような100万人を超えた県には"〇"そうでない県には"×"のようなものを書きたい場合。(SQLでは以下のイメージ)

SELECT 県名,CASE WHEN MAX(人口(千人)) > 1000 THEN '〇' ELSE '×' END FROM 県別人口 GROUP BY 県名

どうするかというと、OFFICE2016以降であればDAXでメジャー式が使える、という記事です。


まず、ピボットテーブルを挿入するときに出てくる以下のダイアログで、「このデータをデータモデルに追加する」にチェックを入れておくと、


列をまとめたテーブル名が表示されるようになる



このテーブル名を右クリックし、[メジャーの追加]を選ぶと、集計用の式が書けるようになる。


  


適当にメジャーを作ってみて、



OKを押すと、メジャーが表示されるようになる。


これを値の部分にドラッグアンドドロップすると、IF条件で表示を〇×に切り替えることができる。
  

ヘルプページ

ヘルプ開設

PowerBIでRank5以降をその他としてまとめた円グラフの作成（ただしスライサーなど外部フィルターは効かない）

PowerBIでは、
[日付]と[種類]というテーブル(左の表)から、指定期間(上のスライダー)での種類の発生度数を円グラフにすること(中央の円グラフ)は、
簡単な部類ですが、発生度数上位5位以外をまとめてそれ以外をその他とする円グラフにすること(左の円グラフ)は、何をすればよいか
わからず、手が止まってしまう。




いろいろ調べてやれることと限界が見えてきたので、まとめて記事化しておく。



まず簡単とした円グラフですが、元のテーブル


に、メジャー　「MCOUNT種類 = COUNT('テーブル2'[種類])」を追加して、円グラフで使えるようにする。


視覚化で円グラフを選択し、凡例に「種類」、値に「MCOUNT種類」とすると、1つ目の図の中央の円グラフが表示される。



で、ここから問題。

やりたいこととしては元のテーブルに、さらに以下のようなメジャーを追加する。
MRANK種類数 = rankx(ALL('テーブル2'[種類]),[MCOUNT種類])
Mrank5種類 = if([MRANK種類数]<=5,max('テーブル2'[種類]),"その他")
MRank5種類順 = if([MRANK種類数]<=5,[MRANK種類数],6)
テーブル2から、日付を除く全ての列とメジャーを表示してみると以下のようになる。




上のデータで作りたい円グラフは、[Mrank5種類]を凡例に、[MCOUNT種類]を円グラフの値に、[MRank5種類順]を凡例の表示順にしたもの。




ただ、今回の問題にあたりPowerBIの円グラフでは、凡例にメジャーが使えないことで、ランク5以下の種類をその他とするようなことが難しいという制限がある。
その回避手段として、以下を考えて作ってみる。
　サマリーの新規テーブルを作り、Mrank5種類をメジャーでなく、列値として確定してしまう。
　この弊害として、サマリーテーブルでは、日付列がなくなる。
　　→スライサーなど外部フィルターは視覚化部品の描画の直前で適応されるため、サマリーテーブルに指定日付範囲を適用できない。
　　　→外部フィルターの適用は諦め、Filter関数などであらかじめ絞り込まれたテーブルで、サマリーテーブルを作る。

とりあえずやってみる。

まず、1つ目の新テーブルとして、Filter関数で元のテーブルから絞り込みをする。
[モデリング]メニュー→[新しいテーブル]ボタンで、以下の入力をしてみる。
Filteredテーブル = FILTER('テーブル2','テーブル2'[日付]>=dt"2025-6-7"&& 'テーブル2'[日付]<=dt"2025-7-6")

(FILTER関数の結果を直でテーブルとして使うことに抵抗があるなら、以下でもいいかな。)
Filteredテーブル = CALCULATETABLE('テーブル2','テーブル2'[日付]>=dt"2025-6-7", 'テーブル2'[日付]<=dt"2025-7-6")

追加したFilteredテーブルに以下の3つのメジャーを追加する。
ftMCOUNT種類 = COUNT('Filteredテーブル'[種類])
ftMRANK種類数 = rankx(ALL('Filteredテーブル'[種類]),[ftMCOUNT種類])
ftMrank5種類 = if([ftMRANK種類数]<=5,max('Filteredテーブル'[種類]),"その他")


2つ目の新テーブルとして、サマリーテーブルを作る。
[モデリング]メニュー→[新しいテーブル]ボタンで、以下の入力をしてみる。
sumt = 
var s1=SUMMARIZE('Filteredテーブル','Filteredテーブル'[種類])
var s2=ADDCOLUMNS(s1,"MCOUNT種類",[ftMCOUNT種類],"種類(上位5個)",[ftMrank5種類],"表示順",[ftMRANK種類数])
return s2

2つ目に追加したテーブルにメジャーを2つ追加する。

Value = sum([MCOUNT種類])
Order =MIN([表示順])

円グラフに使うデータは以下の、「種類(上位5個)」,「Value」,「Order」。



新しい円グラフをつくり、凡例を「種類(上位5個)」,値を「Value」,ヒントを「Order」とする。円グラフに選択したデータの設定列はこんな感じ。




視覚化部品右上の3点リーダーから、軸の並び替えを選ぶ



で、一番上の右の円グラフが完成する。ただ、今回の対応でサマリーテーブルを作っており、サマリーテーブルを作る時点で日付列がなくなり、[その他]を固定している。
なので、レポートビュー上に配置したスライサーやその他の資格化部品からの相互作用やレポートビュー上のフィルターからの、[日付列]からの絞り込みができず、さらに[その他]列を条件によって変えることができない、という制限がある。
この点で使い物になるのかどうかといったところ。

もっといい方法があるのかもしれないけど、私にはここまででした。

ただ、円グラフなどのような割合を示すグラフなら合計100%に意味があるが、そうでないグラフであれば、その他の集計をしなくてもあまり気にならない気がします。
そもそも3個以上の項目間の比較をする場合、円グラフではなく、棒グラフや折れ線グラフの方が良い気がします。

PowerBI Desktopで、時間単位でのスライサーの小細工

PowerBI Desktopは、以前モダンExcelと呼ばれた Power Query, Power Pivot, Power Viewの部分を取り込み進化させたようなもので、大量データの可視化に優れたツールです。
なので、httpのアクセスログやエラーログ、パフォーマンスログ、各ネットワーク機器の通信ログ、マイクロサービスの各サービスログを連結し可視化することにより分析が捗る気がします。
というわけで、いろいろ使ってみた結果、一番はまった部分である時間単位でのスライサーの部分について、記事化してみます。

スライサーというのは、閲覧者が操作可能なフィルターで、以下の部品です。

これを配置し、スタイルを「指定の値の間」フィールドにdatetime型の列を指定すると、以下のようにカレンダー選択部品付きの日付単位のフィルターになってしまいます。

23:50:02～23:53:10の時間を指定したいのにこれでは、4月3日～6月5日までの日付単位になってしまい、意図したものとはなりません。

ただ数値型のデータに対して、「指定の値の間」フィルターなら以下のように行けそうで、これを使います。



まず、datetime列をもつデータにカスタム列TimeValueを追加します。
以下のように、対象テーブルを選択して、

列の追加ボタンを押します。

数式入力で以下のように、「列 =」の部分を「TimeValue=CONVERT([日時],DOUBLE)」として、

Enterを押すと、データにTimeValue列が表示されます。

これをスライサーのフィールドに指定すると、小数点以下も指定できそうなものになります。

ただ、数字では何日の何時かわからないので、入力テキストボックスを表示せず[日時]列の最小値最大値のメジャーのラベルで日時を表示したい。
入力テキストボックスの非表示はできなさそうなので透明にして、上にラベルを張り付けることにします。
入力テキストボックスの非表示はできなさそうなので透明化は、
スライサーヘッダの表示トグルスイッチをOFF,

フォーマットオプションで、書式をカスタム、形式コードに半角スペースを入れて、表示を無効化する。

表示部品に、「カード」を追加し、データに「日時」列を選択すると、デフォルトで最小値が表示される。


「カード」で、吹き出しの値のフォントサイズを10,カテゴリラペルの表示トグルスイッチをOFF

全般タブの背景の透過率を100%にする。

このカードをコピペして、複製したカードのフィールドの「最も古い日時」の下向きアイコンをクリックして現れるメニューで「最新の日付」から「最初の日付」に変えると、
「フィールド」の表示が、「最新の日時」となる。(最新の日付が最も古い日時で、最初の日付が最新の日時とは？と疑問に思わない)

元のカードとコピペしたカードのサイズをいい感じにして、スライダーの上に配置する。


スライダー表示時に入力テキストボックスがピコピコ表示されないように、「選択項目」を表示させ、「レイヤーの…」のZオーダーをスライサーの上にカードが並ぶようにする。



完成ではあるが、入力テキストボックスが使えず、操作性がよくないので、スライサーを2段階にする。

まずは、スライサー、カード2つを選択、コピーして、


ペーストして、下側に配置する。


上側のスライダーは、下側のスライダーに影響を受けないようにするために、下側のスライサーを選択したのち、メニュー→書式→「相互作用を編集」を押して編集開始する。


選択したスライサー以外の部品について、相互作用の選択ボタンが現れる。コピペした時点で、コピペ元のスライサーには、相互作用なしとなっていてもラベルは連動してしないので、
以下のように上部スライサー、上部最小値ラベル、上部最大値ラベルの相互作用なしマークをクリックする


「相互作用を編集」を押して編集終了し、上部のスライサーを選択し、「相互作用を編集」を押して編集開始し、

下部のスライサーは、上部のスライサーにより、フィルターされるようにする。



「相互作用を編集」を押して編集終了する。

作業をまとめると、以下です。

日時フィールドを数値型に変換し、それをスライサーにすることで、小数点以下の範囲(時分秒の単位)でのスライサーがつかえる。
ただ、数値では時分秒がわからないので、スライサーの値表示ではなく、カードでの表示で日時を表示する。
カードの表示を前面にするため、いろいろ試行錯誤する。(この部分があまりうまく動かないかも)
スライダーだけではピンポイントの指定が難しいため、1つは相互作用を受けないスライサー(とラベル)とすることで、2段階のスライダーにする。

今回数値化した日時の値では、1秒加算するためには1/24/60/60=0.00001157...の約0.00001を加算すればよく、
計算により意図した日時に対応する値を求められる。
なので、スライサーの値を(例えばフォントを背景色に合わせることにより)見た目には表示させないが、
数値を入力できるようにしておけば、意図した日時を指定できるので、組み立てが少し複雑な2段階のスライダーはやりすぎかな？