Excelで横方向の表から縦方向の値を取得する方法｜コピーしても参照先を自動変更する関数

Excelでは、表の配置によって同じデータでも参照方法を工夫する必要があります。特に、縦方向に並んだデータを横方向の表へ表示したい場合、単純なセル参照ではコピーしたときに意図した結果にならないことがあります。

例えば、月別売上データが縦に管理されている一方で、集計表では横方向に表示したい場合があります。この記事では、コピーしても参照先が自動的に変化する数式を使って、効率よく値を取得する方法を解説します。

単純なセル参照では横コピーした時に問題が起きる理由

例えばB2セルに「=B6」と入力すると、B2にはB6の値が表示されます。しかし、この数式をC2へコピーすると、自動的に「=C6」へ変化します。

そのため、縦方向に並んだデータを順番に取得したい場合、単純なコピーでは参照方向が合わず、期待した結果になりません。

今回のように、B2ではB6、C2ではB7、D2ではB8というように、行番号だけを変化させたい場合は別の関数を使う必要があります。

縦に並んだデータを横方向へ展開する場合は、INDEX関数を使う方法が便利です。

B2セルには次の数式を入力します。

=INDEX($B$6:$B$8,COLUMN(A1))

この数式を右方向へコピーすると、C2ではB7、D2ではB8の値を順番に取得できます。

INDEX関数は、指定した範囲から指定した位置の値を取り出す関数です。COLUMN関数を組み合わせることで、コピーするたびに取得する位置を1つずつ変更できます。

OFFSET関数を利用しても、コピーに合わせて参照位置を変更できます。

B2セルの場合は、次のような数式になります。

=OFFSET($B$6,COLUMN(A1)-1,0)

この数式では、基準となるセルをB6に固定し、COLUMN関数で右へコピーするごとに行方向へ1つずつ移動しています。

B2ではB6、C2ではB7、D2ではB8というように、縦方向のデータを横方向へ展開できます。

Excelには、縦横の向きを入れ替えるためのTRANSPOSE関数もあります。

例えば、B6:B8の範囲を横方向へ表示したい場合、TRANSPOSE関数を利用すると一度に変換できます。

新しいExcelでは、次のような数式を入力できます。

=TRANSPOSE(B6:B8)

この方法は、データ全体の向きを変更したい場合に特に便利です。ただし、1セルずつ参照を変えたい場合はINDEX関数やOFFSET関数のほうが適しています。

例えば、B6:B8に以下のようなデータがあるとします。

1月　90
2月　120
3月　110

この売上データを横方向の集計表へ表示する場合、INDEX関数を使えば、B2に90、C2に120、D2に110を自動表示できます。

このような方法を使うと、元データが増えた場合でも数式をコピーするだけで対応でき、手入力によるミスも防げます。

Excelで縦方向の値を横方向へ表示したい場合、単純なセル参照ではコピー時に参照先がずれてしまいます。

コピーしても正しく順番に取得したい場合は、INDEX関数とCOLUMN関数を組み合わせた「=INDEX($B$6:$B$8,COLUMN(A1))」のような数式が便利です。

また、データ全体の向きを変更する場合はTRANSPOSE関数、参照位置を細かく制御したい場合はOFFSET関数など、目的に合わせて関数を使い分けることでExcel作業を効率化できます。