Excel for macでPower Queryを使わずにマトリックス表をリスト表に変換して箱ひげ図を描くの巻

はじめに

いま、Mac版のExcel（Excel for Mac）でマトリックス表（クロス集計表）のデータがあります。なお、データは適当に作ったものです。

これで箱ひげ図を描きたいのですが、マトリックス表のデータ直接からだといまいち思うような箱ひげ図が描けないのです。

一応、マトリックス表からもこんな箱ひげ図を描画できるのですが、欲しいのはこんな一緒くたにまとめられてしまった密なのじゃなくて。。。

こんなのが欲しいのです。伝わるでしょうか？

こちらはboxが分離されていて、配置バランスも良く、軸のラベルとboxとの対応も見やすいと思います。

Excelで箱ひげ図をこのように描画するためには、データはリスト表（テーブル形式）で記述されている必要があることがわかりました。

というわけで今回の内容は、

・MacのExcelでマトリックス表をリスト表に変換（unpivot）して箱ひげ図を描く

です。

経緯をざっくり

もう少し詳しい経緯は以下の通りです。

忘れないようにといろいろ記載していますのでちょっと蛇足気味になっています。

・Excelで、マトリックス表のデータからだとイマイチ思うように箱ひげ図が書けないことがわかりました。リスト表のデータにしてやるとうまく描けました。

なので、

・Excelで所望の箱ひげ図を描くために、マトリックス表からリスト表への変換（unpivot; ピボット解除）が必要となりました。

毎回地道に手作業で表データの切り張りをしたくなかったのでうまいやり方がないか調べると、

・Windows版のExcel（Excel 2016相当以降）でPower Queryの「データの取得と変換」でマトリックス表を一発でリスト表にできることがわかりました。

しかしながら、

・ワタシが使っているMac版のExcel（Excel for Mac バージョン16.39）だと、まだPower Queryの「データの取得と変換」の機能が実装されていないことがわかりました。

ので、

・公開されているExcelのマクロを使わせていただき対応しました。

マトリックス表のデータで箱ひげ図を描いてみるが思うようにならない

冒頭にも記載しましたが、上記のマトリックス表のデータからExcelで箱ひげ図を描画すると。。。

ね、なんか違う。

系列がまとめられてしまって中央に寄りすぎ。box名がそれぞれのX軸のところで記述できないのでなんかわかりにくい。

また、ここには記載していませんが勝手に系列を解釈されて系列の設定がなんかうまくいかない（うまくいくケースもあると思うけど）。

もうちょっと足掻いてみる

調べてみると、Windows版のExcelでは、「データ系列の書式設定」から、「要素の間隔を調整」して、boxのプロット間隔の調整がができるみたいなのです。

Excel for Mac では「要素の調整」の設定項目は「軸のオプション」にあります。
「要素の間隔」0.1にすればboxのプロットが幅全体に行き渡る感じになりました。

でも、これだけだとなんか不格好なので、boxの幅をさらに調整したいところ。

んが、boxの幅をいじる設定が全く見当たらない。。。

もう諦めました。。。

Excelでワタシの所望の箱ひげ図を描くためにはリスト表であることが必要

さて、うまい方法がないか調べたところ、

“単一のデータ系列または複数のデータ系列を選びます”

Officeのサポートの箱ひげ図の関連ページ
2020年7月31日確認

ですって。

要は

マトリックス表

ではなく

リスト表

を使いなさいよ、と。

確かにリスト表のデータに変換（手動で）してやってみたらうまくいった。
うん、欲しかったのは正にこんな感じ。

でもね。。。

イヤよイヤよ、マトリックス表からリスト表に手動で変換するのがメンドくさすぎるわ。。。それに手動での変換だとヒューマンエラーが多分がっつり起きるでしょうし。。。

あーごめんなさいねーこんな表データにしちゃって。

最初からリスト表で作成しておけばこんな苦労はしなかったでしょうよ。

でもしょうがないじゃないのまとめ終わったマトリックス表しかないんだから。。。

しょうがないからマトリックス表をリスト表に変換する

まあ、マトリックス表からコピペでゴリゴリとリスト表に変換してもいいのですが、やはり毎回やるのは忍びなく骨も折れます。

データ量が多い場合は対応が難しくなりますし、しかもワタシはミスする自信が、ある。

「データの取得と変換」でマトリックス表を一発でリスト表にできることがわかりましたがExcel for macではまだ使えない

なんかうまい方法がないかな？と調べてみると。。。

Excelには2016年版あたりから「データの取得と変換」というのが実装されていて、これで一発でマトリックス表をリスト表に変換できる、と検索するといっぱい出てきます。これだ！

んが、ワタシが使っているExcel for macでは「データの取得と変換」がどこを探しても見当たらないんです。

ついでに「データの取得と変換」を司どるPower Queryも見当たらないんです。。。どういうこと？

こういうことでした。

ご要望にお応えして、Excel for Mac での Power Query のサポートへ向けた取り組みが始動しました。

https://answers.microsoft.com/ja-jp/msoffice/forum/all/ご要望にお/e9738f22-42e4-4d64-a2fc-dbc449544e9f
2020.7.31確認

Excel for Microsoft 365 for Mac
Microsoft 365 のサブスクライバーである場合は、既存の Power query クエリクエリを更新し、VBA を使用して新しいクエリを作成して編集することができます。

Excel 2016 および Excel 2019 for Mac
Get & Transform (Power Query) はサポートされていません。

https://support.microsoft.com/ja-jp/office/get-transform-power-query-e9332067-8e49-46fc-97ff-f2e1bfa0cb16
2020.7.31確認

Excel for Mac では、ローカルファイルの Power Query の更新に加えて、VBA によるクエリの作成もサポートされるようになりました。 Power Query エディターでの作成はまだサポートされていません。

https://support.microsoft.com/ja-jp/office/excel-for-mac-で-power-query-を使用する-7b2f337d-e7d2-4fdf-bf00-3dfbb1e5e9c5
2020.7.31確認

どうやらExcel for Macに完全体のPower Queryが乗せられて、「データの取得と変換」が使えるようになるのはもう少し先のことになりそうです。知らんけど。

ならばWindowsのExcelを使用してやればいいのですが、うーんなんかそれもめんどくさい。。。

マクロでunpivotすることにしました

じゃあVBAでやればいいのね。。。で探してみると。。。

これだ！マクロを作ってくれている人がいる。

https://superuser.com/questions/78439/how-to-unpivot-or-reverse-pivot-in-excel

Sub unPivot()
Dim oTarget As Range
Dim oSource As Range
Dim oCell As Range

Set oSource = Names("Source").RefersToRange
Set oTarget = Names("Target").RefersToRange

For Each oCell In oSource
    If oCell.Value <> "" Then
        oTarget.Activate
      ' get the column header
        oTarget.Value = oCell.Offset(-(oCell.Row - oSource.Row + 1), 0).Text 
      ' get the row header
         oTarget.Offset(0, 1).Value = oCell.Offset(0, _
           -(oCell.Column - oSource.Column + 1)).Text 
      ' get the value
        oTarget.Offset(0, 2).Value = oCell.Text 
      ' move the target pointer to the next row
        Set oTarget = oTarget.Offset(1, 0) 
    End If
Next
Beep
End Sub

ありがたやありがたや。

このコードを使わせていただきました。以下にやり方を記載します。

unpivot用マクロの使い方

1. まず、開発タブからVisual Basicを開いて、該当のコードを入力して保存し、マクロ「unPivot」を作成します。
（なお、開発タブが見当たらない場合は「環境設定」の「リボンとツールバー」の「リボン」の「メインタブ」にて開発タブを表示するよう設定しておきます。）

2. 次に、unpivotしたいデータがあるセル範囲に次のように名前をつけます。

データのセル範囲に”Source”と名前をつけます

unpivotしたあとのデータを置きたい最初のセルに”Target”と名前をつけます。

SourceとTargetのセル同士は重ならないようにしておきます。

3. 最後にMacroを実行。

実行結果

でけた。ので、このリスト表データから箱ひげ図を作成します。

でけた。

はい、Excel for macにおいて、マトリックス表からリスト表へのunpivotをマクロで実施することで、欲しいBoxplotが簡単に描けるようになりました。めでたしめでたし。

おまけメモ（リスト表からマトリックス表を作るには）

今回はマトリックス表をunpivotしてリスト表にする方法でした。

じゃあ逆にリスト表からマトリックス表を作りたいんだけど？

っていう場合はExcelに備わっているピボットテーブルを使えば簡単にできるっと。これはMac版でも機能が入っているのでひと安心。

実施環境

Excel for Mac バージョン16.39(20071300)
（Microsoft 365（旧称：Office365））
macOS Catalina　バージョン 10.15.5