shikumika’s diary

日々の事務作業で手間なことを簡単にできる仕組み(自動化、方法など)、困ったことの解決方法を調べた備忘録

【Excel】複数行ごとの表を一行に変換(リストのような単票の変換)

Excelの表で、1件のデータが複数行ごとに入力されている場合に、集計等の効率化のために1行のデータに変換する方法です。

例えば、下図はExcelの3行分に1件分のデータが入力されており、1行に変換するアウトプットイメージです。

Excelの3行分に1件分のデータが入力されており、1行に変換するアウトプットイメージ

複数行ごとの表を一行に変換するアウトプットイメージ

このように、リストのように入力はされているが、同じ列に異なる値が入力された表を変換する事例です。

変換する方法はいくつかありますが、「Excelのみを使う場合」で個人的によく実施する方法です。

内容:

事例の前提

下図のとおり、3行ごとに1件分のデータが入力されている表で、繰り返しの行が3行で固定。

A列やC列は1行分のデータ、B列やC列は3行分のデータが入力されている。単純な繰り返しであるが、空欄箇所もある。

3行ごとに1件分のデータが入力されている。ただし、空欄箇所もある。

事例の前提イメージ

実施手順

1.セルを参照する数式で1行分のアウトプットを作成する

3行分のデータの「最初の1件分」について、1行のデータを作成する。

例えば、下図のようにセル範囲A2:D4に1件分のデータがある場合、
「セルF2: =A2」「セルG2: =B2」「セルH2: =B3
のように作成する。

セル範囲A2:D4の上端は2行目に該当するため、2行目の位置に数式を入力している。

また、1行としたときのタイトルも記入してある。

3行分のデータ(最初の1件)について、1行のデータを作成している

セルを参照する数式で1行分のアウトプット作成

2.数式を対象となる件数分、下方向にコピーする

変換したい表と同じ行まで、数式をコピーする(事例では、16行目まで)。

余分な行と思えるものも多数あるが、あとで抽出するため、一旦そのままコピーしておく。

変換したい表の範囲まで、下方向に数式をコピーしている

数式を対象となる件数分、下方向にコピーの結果

3.残したい行を識別できる列で、フィルターをする

数式のコピーでは、余分な行のデータも含まれるため、残したい行を識別できる列で、フィルターをする。

この事例では、「A列に数字がある行」もしくは「C列に値がある行」が残したい行と一致する。A列でフィルターの結果は次のとおり。

なお、残したい行の識別可能な情報がないケースは、前述の手順で数式コピーを該当列だけにコピーしたり、数式等で識別できる情報を付加する。

A列の数字がある行と数式の残したい行が一致している事例

残したい行を識別できる列でフィルターの結果

4.抽出した結果を別シートに値のみ貼り付け

抽出した結果をコピー、別シートに値のみ貼り付けする。

事例では上図のセル範囲F1M14(フィルター後)をコピーしている。

別シートに値のみ貼り付けをした結果が次のとおり。

抽出した数式部分の値を別シートに貼り付けしている

抽出した結果(数式部分)を別シートに値のみ貼り付けイメージ

5.空欄の”0”を一括置換で削除

この事例では、数式で空欄箇所は”0”になっている。

他に”0”となる値はないケースは、「セル内容が完全に同一であるものを検索する」で空欄に置換する。

0を「セル内容が完全に同一であるものを検索する」で空欄に置換

空欄の”0”を一括置換で削除イメージ

以上で、冒頭のアウトプットイメージのとおり、以下となる。

”0”が空欄になっている

アウトプット

補足説明

元の表で空欄と”0”が混在している場合

もし、元の表で空欄と”0”が混在している場合は、上記1の手順でセルを参照する数式において、次のようにIF文で空欄の場合の結果を変える。

=IF(A2="","",A2)

繰り返しの行が一定でない場合(3行と4行の混在など)

数式の工夫(前述のようなIF文の活用など)でパターンかできるかを検証し、対応する。

shikumika.org

繰り返し作業が必要な場合

今回の方法は一回だけの作業などに適している。元データを都度入手し、繰り返し同様の作業をする場合はPowerQueryやVBA等が効率的となる。

以上、Excelで複数行ごとの表を一行に変換する方法でした。