Excelで縦持ちデータを、横持ちデータに変換する方法についてのメモ。ピボットを使うと簡単だが、ピボットが使えない状態で関数を使って実現する方法を記載する。
まず、問題設定をする。図1の縦持ちデータを図2の横持ちデータに変換する。つまり、商品名と日付が列になっている状態から、商品名と日付のクロスで販売個数が分かるようにしたい。
このとき、まず初手としてはVLOOKUPを使う。VLOOKUPの使い方については、このサイトが分かりやすかった。 dekiru.net
ただし、今回の場合は、商品名と日付の2つの条件をVLOOKUPの検索値(1個目の引数)にしなければいけない。 そこで、まず最初に商品名と日付を結合させた列を作成する(図3)。
この列を作ることで、VLOOKUP関数の検索値に入れる2つの条件を1つにまとめることができる。 このような前準備を経て、図2:横持ちデータのB2セルにVLOOKUP関数を入れる。 B2セルは、A2セルとB1セルの2条件をがキーとなっている。そこで、B2セルには絶対参照に注意しながら次の関数を入れる。セルの番号は図2、図3を見ていただきたい。
=VLOOKUP($A2&B$1, $A$21:$D$28, 4, False)
上記関数をB2セルに入れて、B2:D4セルまでコピペしてできた表が図4である。
図4を見るとVLOOKUPを入れただけで横持ちデータはほぼほぼ完成しているが、C2セルでエラーを吐いてしまっている。 エラーを吐いた原因は、元の縦持ちデータに2022/3/16の「よなよなエール」が存在しないからである。今回は販売ログ(購入があったらその履歴を記録したようなデータ)を想定しているので、このような縦持ちデータに存在しない行がある場合、その日付のその商品の販売個数0とする。 これをExcel上で反映させるためにIFERROR関数を使う。IFERROR関数の説明はこのサイトが分かりやすかった。 office-hack.com
先ほどのB2セルの関数を次のように置き換える。
=IFERROR(VLOOKUP($A2&B$1, $A$21:$D$28, 4, False), 0)
これを先ほどと同様に、B2:D4セルまでコピペしてできた表が図5である。
これで欲しかった表ができた。