1行に複数ある項目を1行1レコードに変換する方法

Excelの表の作り方はいろいろありますが、リスト形式の表は、1行に1レコード（1行に1つの情報）となっている必要があります。

この記事では、横に長い表を4列ずつで区切り、縦に長い表（リスト）に変換する方法を解説します。

1行1レコードにすると、テーブルやピボットテーブルを作成でき、データの分析が容易になります。

参考Power Query（パワークエリ）が使える環境であれば、以下の記事で解説している方法が効率的です。同じ項目の情報が横に複数出現する表は、Power Queryエディターで列をマージし、ピボット解除して、結合した列を分解することでデータを整形できます。以下の記事では、同じ表で解説しています。

元となる表の確認

例として次のような表があります。

1行に3つのデータが入っているような表ですね。

セル［F2］から［I5］を切り取ってセル［B6］へ貼り付け、セル［J2］から［M5］を切り取ってセル［B10］へ貼り付け、A列もコピー＆貼り付けして、最後にA列を昇順に並べ替える方法が思いつきます。

ですが、複雑な表になるとそれも面倒です。

横に長い表で、1行に並ぶデータが1個だったり5個だったりするため、空白セルもあります。

方法はいろいろありますが、そのうちの1つを紹介します。

効率的にできる内容があれば参考にしてください。

VBAも関数も苦手な方向けの解説です。

［Sheet1］に元の表があり、［Sheet2］に求める表を作成します。

表作成の準備

［Sheet2］に下のように、項目行を作成します。

そして、［数式］タブの［ワークシート分析］にある［数式の表示］をクリックします。

数式の入力

セル［A2］には、［=Sheet1!A2］と入力します。

［＝］を入力して、シートを［Sheet1］に切り替えてセル［A2］をクリックすると数式を入力できます。

同じようにして、セル［B2］にも数式を入力します。

行のみに絶対参照をつけます。数式を入力した後、［F4］キーを2回押すといいです。

絶対参照については、以下の記事を参照してください。

セル［B3］には［=Sheet1!F$2］、セル［B4］には［=Sheet1!J$2］と入力します。

数式をコピーします。

セル［B2］からセル［B4］を範囲選択して、E列までドラッグします。

A列も数式を入力します。

数式の結果を確認

数式が間違っていないかを確認します。

［数式］タブの［ワークシート分析］にある［数式の表示］をクリックします。

数式の結果が表示されます。間違いがあれば、ここで修正します。

これで1行分のデータを変換できます。

［=］を［#］に置換

再度、［数式の表示］をクリックして数式を表示します。

キーボードで［Ctrl］+［F］キーを押して、［検索と置換］ダイアログボックスを表示します。

［検索する文字列］に半角で［=］、［置換後の文字列］に半角で［#］を入力して、［すべて置換］をクリックします。

メッセージウィンドウが表示されたら、［OK］ボタンをクリックします。

［検索と置換］ダイアログボックスも［閉じる］ボタンをクリックして閉じます。

数式を入力した範囲を選択して、セル［E13］まで下へドラッグします。

下のようになります。

一区切りずつ縦に並べられます。

A列は多少面倒ですが、以下のように修正します。

手作業が大変な場合は、3行ずつ同じ値を入力する方法を参照してください。効率よく入力できます。

参考A列は、下のような数式を入力して、セル［A5］からセル［A7］を選択して、下へフィルハンドルをドラッグです。

数式に変換

完成したら、［#］を［=］に置換します。

キーボードから［Ctrl］キー+［F］キーを押して、［検索と置換］ダイアログボックスを表示します。

［検索する文字列］に半角で［#］、［置換後の文字列］に半角で［=］を入力して、［すべて置換］をクリックします。

メッセージウィンドウが表示されたら、［OK］ボタンをクリックして、［検索と置換］ダイアログボックスも閉じます。

［数式］タブの［ワークシート分析］グループにある［数式の表示］をクリックして、数式の結果を表示します。

値の貼り付け

最後に範囲選択して、値を貼り付けます。

数式を入力した範囲を選択します。ここでは項目行も選択していますが、それでも構いません。

［ホーム］タブの［クリップボード］グループにある［コピー］をクリックします。

［貼り付け］ボタンの下方をクリックして、メニューから［値］をクリックします。

キーボードから操作

キーボードから操作する場合は、［Ctrl］+［C］でコピーし、［アプリケーション］→［V］で［値］の貼り付けを実行します。

［アプリケーション］キーがない場合は、［Shift］+［F10］でショートカットメニューを表示できます。

Microsoft 365のExcel バージョン 2308 (ビルド 16731.20170) では、［値の貼り付け］のショートカットキー［Ctrl］+［Shift］+［V］が追加されています。Excel 2024でも動作する場合があります。

Excelの「値の貼り付け」のショートカットキーが追加された

値の貼り付けについては、以下の記事で解説しています。

参考1行に並ぶデータが1個だったり5個だったりすると、セルに［0］が並ぶ行があるため、その行のみを削除してください。

テーブルやピボットテーブルに変換するには、1行1レコードとなるようにします。そのほかにも注意点があります。

もし、テーブルやピボットテーブルに変換できない場合は、以下の記事を参考に表を見直してください。

テーブルにすると、以下の記事で解説している便利な機能が使えるようになります。