Google検索

Windows10 & Office2016 Excel2016

1行に複数の同じ項目名がある表を1行1レコードの表に変更するには

投稿日:2018年12月9日 更新日:

Excelの表の作り方は様々です。リストとなる表は、1行に1レコード(1行に1個の情報)となっていなければいけません。

横に長い表を4列ずつで区切って縦に長い表(リスト)に変換する場合の方法です。1行1レコードにすると、テーブルやピボットテーブルの作成ができて、データの分析が容易になります。

横に長い表を縦に変更

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

先頭行に複数の同じ項目名がある表をPower Queryで分析可能なテーブルに変換

Excel2016から標準で装備されているPower Query(パワークエリ)を使用すると、効率よくデータを加工することができます。 Ex ...

続きを見る

元となる表の確認

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

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

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

1行に3つのデータが入っているサンプル表

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

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

方法はいろいろありますが、一つのやり方として紹介します。

少しでも効率的にできる内容があれば真似てやってみてください。

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

[Sheet1]に元の表があって、[Sheet2]に求める表を作成していきます。

表作成の準備

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

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

数式の入力

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

[=]を入力して、シートを[Sheet1]に切り替えてセル[A2]をクリックすると数式を入力することができます。

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

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

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

相対参照と絶対参照と複合参照は【 F4 】キーで切替

セルの参照方法は、相対参照、絶対参照、複合参照があります。 絶対参照と複合参照では、[$]を挿入する必要があります。 このとき、直接入力して ...

続きを見る

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

数式の入力

数式をコピーします。

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

数式のコピー

A列も数式を下のように入力します。

数式の完成

数式の結果を確認

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

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

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

これで一行分のデータを変換できたわけです。

数式の表示をオフに

[=]を[#]に置換

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

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

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

[=]を[#]に置換

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

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

置換の完了

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

ドラッグしてコピー

下のようになります。

一区切りずつ縦に並べることができました。

ドラッグの結果

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

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

同じ数字が複数行に並ぶ連番(1.1.1.2.2.2)を入力するには

Excelで1つのセルに数字を入力して、オートフィルをかけると簡単に連番を入力することができます。 オートフィルは元になるセルの■(フィルハ ...

続きを見る

A列の修正

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

A列の数式

数式に変換

完成したら、[#]を[=]に置換します。

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

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

[#]を[=]に置換

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

置換の完了

数式に戻ります。

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

数式の結果を表示

値の貼り付け

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

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

範囲選択

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

値の貼り付け

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

数式の結果のみ貼り付けるには[値]の貼り付け

Excelで下のような表があって、C列にはA列とB列の合計値を求める関数が入力されています。 数式で求めた値をそのまま保存したいということが ...

続きを見る

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

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

もし、テーブルやピボットテーブルに変換できないようであれば、以下の記事を参考にして表を見直してみてください。

テーブルやピボットテーブルでうまくいかない場合の見直し点

Excelのテーブルやピボットテーブルは、データベースとしての管理や分析が簡単にできるようになる、とても便利な機能です。 でも、既存の表をテ ...

続きを見る

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

テーブルの便利機能(テーブルに変換して効率アップ)

Excelのテーブルはとても便利なものです。 通常の表をテーブルに変換することによって、データベースとしての機能が自動的に設定され、管理や分 ...

続きを見る

Profile

執筆者/はま

おすすめ記事と広告

-Windows10 & Office2016 Excel2016

Copyright © 2001-2019 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2019 AllRights Reserved Powered by AFFINGER4.