初心者データサイエンティストの備忘録

調べたことは全部ここに書いて自分の辞書を作る

Excelで縦持ちデータから横持ちデータに変換する方法

 Excelで縦持ちデータを、横持ちデータに変換する方法についてのメモ。ピボットを使うと簡単だが、ピボットが使えない状態で関数を使って実現する方法を記載する。

 まず、問題設定をする。図1の縦持ちデータを図2の横持ちデータに変換する。つまり、商品名と日付が列になっている状態から、商品名と日付のクロスで販売個数が分かるようにしたい。

f:id:aisinkakura_datascientist:20220315013315j:plain:w300
図1:縦持ちデータ

f:id:aisinkakura_datascientist:20220315233021j:plain:w300
図2:横持ちデータ

 このとき、まず初手としてはVLOOKUPを使う。VLOOKUPの使い方については、このサイトが分かりやすかった。 dekiru.net

 ただし、今回の場合は、商品名と日付の2つの条件をVLOOKUPの検索値(1個目の引数)にしなければいけない。 そこで、まず最初に商品名と日付を結合させた列を作成する(図3)。

f:id:aisinkakura_datascientist:20220315014705j:plain:w300
図3:検索値を1つにまとめる

 この列を作ることで、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である。

f:id:aisinkakura_datascientist:20220315233914j:plain:w300
図4:VLOOKUPを入れた表

 図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である。

f:id:aisinkakura_datascientist:20220315235008j:plain:w300
図5:IFERRORも入れた表

これで欲しかった表ができた。