実施環境 : Microsoft Office Professional Plus 2016
0. 概要
以下は、適当に作ったサンプルデータをマトリクスで表示したものです。
そして以下は、同じデータをリストで表示したものです。
この2つについて、相互に変換したい場合があります。
もちろんそのためのソフトウェアを使用してもよいですが、インストールできるソフトに制限がある場合など、 Excel の機能だけで変換できると便利です。
今回はその変換の方法をいくつか紹介します。
1. マトリクス ⇒ リスト
まずは、マトリクスをリストに変換することを考えます。
今回使用するデータは以下の通りです。
1.1. OFFSET 関数
方法はいろいろありますが、今回は OFFSET 関数を用いた方法を紹介します。
以下の空のリストにデータを転記していきます。
まずは、 ROW 関数を用いてリストの各行の行番号を計算します。
=ROW()
開始が2からだと扱いにくいので、2を引いて0始まりにします。
=ROW()-2
これを、先ほどのマトリクスの軸の何番目にあたるかの数値に変換していきます。
まずは、A列の値を縦軸の番号に変換します。
ここでは、 INT 関数を使用します。
INT 関数は数値を整数に切り捨てる関数で、今回は「5で割って切り捨てる」ことにします。
5は横軸の列数で、この処理をすると横軸の列数分だけ同じ数値が並ぶことになります。
=INT((ROW()-2)/5)
次に、B列の値を横軸の番号に変換します。
ここでは、 MOD 関数を使用します。
MOD 関数は割り算のあまりを取得する関数で、今回は「5で割ったあまり」を取得します。
5は横軸の列数で、この処理をすると横軸の列数分までの連番が繰り返し作られることになります。
=MOD(ROW()-2,5)
さて、いよいよ OFFSET 関数の登場です。
OFFSET 関数は引数で動的に指定したセルの値を取得する関数で、以下の文法で記載します。
OFFSET(基準となるセル, 下に何マスずらすか, 右に何マスずらすか)
まずは縦軸について記載してみましょう。
E1を基準として、先ほどの数値分下にずらします。
数値は0始まりだったので、+1します。
なお、コピペしやすいようにE1は$を付けて絶対参照にします。
これで他のセルに式をコピペしてもセル番号がずれません。
=OFFSET($E$1,INT((ROW()-2)/5)+1,0)
次に横軸について記載してみましょう。
E1を基準として、今度は右にずらしていきます。
=OFFSET($E$1,0,MOD(ROW()-2,5)+1)
最後に交点の値をC列にいれていきましょう。
下と右両方にずらすようにします。
=OFFSET($E$1,INT((ROW()-2)/5)+1,MOD(ROW()-2,5)+1)
マトリクスをリストに変換できました。
2. リスト ⇒ マトリクス
今度は逆に、リストをマトリクスに変換することを考えます。
今回使用するデータは以下の通りです。
2.1. ピボットテーブル
まずは一番お手軽な、ピボットテーブルを使用した方法を紹介します。
元データの変更には対応できませんが、元データがどのようなパターンをとっていても対応できるという利点があります。
ピボットテーブルを使用するには、「挿入」⇒「ピボットテーブル」を選択します。
データ範囲を選択し、「 OK 」を押します。
今回は作成場所を新規ワークシートにしているので、以下のような新規シートが作成されます。
右のメニューで、ピボットテーブルの設定をします。
まずは、①すべての項目にチェックを入れます。
ただこれだけだと「縦軸」と「横軸」の両方が「行」に入ってしまいリスト形式のままなので、②「横軸」を「列」にドラッグ&ドロップします。
「値」の集計方法は今回の場合なんでもよいです。
これで、以下のようにマトリクスに変換できます。
2.2. VLOOKUP 関数
他の方法として、 VLOOKUP 関数を使用する方法があります。
少々複雑ですが、値の変更にも対応することができます。
まずは、元のリストに少々手を加えます。
VLOOKUP 関数はリストのある軸から値を検索して他の軸の対応する値を返す関数ですが、、2つの軸を同時に検索することができません。
そのため、縦軸と横軸をまとめて、新たに1つの軸を作ります。
今回は、C列に新しい列を挿入し、「縦軸:横軸」という形で値を作りましょう。
使用する CONCATENATE 関数は文字列を結合する関数です。
=CONCATENATE(A2,":",B2)
次に転記先のマトリクスの軸を作りましょう。
地道に作ってもよいですが、新しいシートで以下のように操作すると簡単です。
まずは新しいシートに列をコピーします。
次に1つの列を選択して、「データ」⇒「重複の削除」を選択します。
1つの列だけ選択していることを確認して、「 OK 」を押します。
確認メッセージが出るので、「 OK 」を押します。
これで、各列について重複を排除した値のリストが取得できます。
これをもとに、転記先のマトリクスを作成します。
このマトリクスに、 VLOOKUP 関数で値を転記していきます。
VLOOKUP 関数は以下の文法で記載します。
VLOOKUP(検索する値, 検索するリスト, 左から何番目の値を返すか, 近似一致か完全一致か)
検索する値は縦軸と横軸を先ほどと同じく CONCATENATE 関数で結合したものです。
なお、今回はセル番号の一部だけ絶対参照にしています。
検索自体は指定したリストの一番左の列を対象に行われるので、今回は C 列から指定します。
返す値は D 列なので、 C 列から見て2番目の列です。
完全一致で検索するので、 FALSE を指定します。
=VLOOKUP(CONCATENATE($F2,":",G$1),$C$1:$D$16,2,FALSE)
これでマトリクスを作成できました。
2.3. OFFSET 関数
かなり限定されたパターンになりますが、2つの軸について「全通り」のパターンが存在し順番に並んでいる場合は OFFSET 関数を使用することも可能です。
以下の空のマトリクスにデータを入れていきましょう。
まずはE列について ROW 関数で行番号をとります。
=ROW()
次に行1について COLUMN 関数で列番号をとります。
=COLUMN()
このままだとやはり扱いにくいので、それぞれ0始まりに変換します。
=ROW()-2
=COLUMN()-6
1.1 と同じようにして、 OFFSET 関数で軸の値を入れていきます。
なお、縦軸については同じ値が横軸の列数=5マス連続で続いているため、5マス飛ばしになるよう5をかける必要があることに注意してください。
=OFFSET($A$2,(ROW()-2)*5,0)
=OFFSET($B$2,COLUMN()-6,0)
最後に、 OFFSET 関数で交点の値を入れていきます。
リスト上の値は1列に並んでいるので、縦軸と横軸の番号の和をとればよいです。
=OFFSET($C$2,((ROW()-2)*5)+(COLUMN()-6),0)
これでマトリクスを作成できました。