Excelの表で、1件のデータが複数行ごとに入力されている場合に、集計等の効率化のために1行のデータに変換する方法です。
例えば、下図はExcelの3行分に1件分のデータが入力されており、1行に変換するアウトプットイメージです。
このように、リストのように入力はされているが、同じ列に異なる値が入力された表を変換する事例です。
変換する方法はいくつかありますが、「Excelのみを使う場合」で個人的によく実施する方法です。
内容:
事例の前提
下図のとおり、3行ごとに1件分のデータが入力されている表で、繰り返しの行が3行で固定。
A列やC列は1行分のデータ、B列やC列は3行分のデータが入力されている。単純な繰り返しであるが、空欄箇所もある。
実施手順
1.セルを参照する数式で1行分のアウトプットを作成する
3行分のデータの「最初の1件分」について、1行のデータを作成する。
例えば、下図のようにセル範囲A2:D4に1件分のデータがある場合、
「セルF2: =A2」「セルG2: =B2」「セルH2: =B3」
のように作成する。
セル範囲A2:D4の上端は2行目に該当するため、2行目の位置に数式を入力している。
また、1行としたときのタイトルも記入してある。
2.数式を対象となる件数分、下方向にコピーする
変換したい表と同じ行まで、数式をコピーする(事例では、16行目まで)。
余分な行と思えるものも多数あるが、あとで抽出するため、一旦そのままコピーしておく。
3.残したい行を識別できる列で、フィルターをする
数式のコピーでは、余分な行のデータも含まれるため、残したい行を識別できる列で、フィルターをする。
この事例では、「A列に数字がある行」もしくは「C列に値がある行」が残したい行と一致する。A列でフィルターの結果は次のとおり。
なお、残したい行の識別可能な情報がないケースは、前述の手順で数式コピーを該当列だけにコピーしたり、数式等で識別できる情報を付加する。
4.抽出した結果を別シートに値のみ貼り付け
抽出した結果をコピー、別シートに値のみ貼り付けする。
事例では上図のセル範囲F1M14(フィルター後)をコピーしている。
別シートに値のみ貼り付けをした結果が次のとおり。
5.空欄の”0”を一括置換で削除
この事例では、数式で空欄箇所は”0”になっている。
他に”0”となる値はないケースは、「セル内容が完全に同一であるものを検索する」で空欄に置換する。
以上で、冒頭のアウトプットイメージのとおり、以下となる。
補足説明
元の表で空欄と”0”が混在している場合
もし、元の表で空欄と”0”が混在している場合は、上記1の手順でセルを参照する数式において、次のようにIF文で空欄の場合の結果を変える。
=IF(A2="","",A2)
繰り返しの行が一定でない場合(3行と4行の混在など)
数式の工夫(前述のようなIF文の活用など)でパターンかできるかを検証し、対応する。
繰り返し作業が必要な場合
今回の方法は一回だけの作業などに適している。元データを都度入手し、繰り返し同様の作業をする場合はPowerQueryやVBA等が効率的となる。
以上、Excelで複数行ごとの表を一行に変換する方法でした。