Excelで縦のデータを別シートで数式を利用して横に並べたいこともあるでしょう。
そのときに使うと便利な関数をまとめてみました。
縦に並ぶデータ。数式を使って横に並べたい
今回の事例は、ExcelでSheet1で縦に入力したデータを、
Sheet2に数式を入力して、前述した縦のデータを横に並べたいというものです。
「横で入力して数式コピーすればいいじゃん」と思われるかもしれませんが、データは通常、上から下に入力するもの。
むげに横に広げるのは、美しくないかなーと。
「じゃあ、数式で反映させる方を縦にすれば…」となるわけですが、これはインポートの形式が決まっているので仕方なく。
ということで、縦に並んでいるもとのデータを、別のシートで横に並べる方法をとりあげてみます。
OFFSET・INDEXを使う
1つはOFFSETを使う方法。次のような数式です。
=OFFSET(基準セル, 行方向の移動, 列方向の移動)
基準セルから下にいくつずらすか、右にいくつずらすかを決めて、そのセルにある値を返します。
仮にE3セルを基準セルとした場合、=OFFSET(E3, 1, 0)とすれば、下に1だけ動いて、右に動くのはゼロなので、E3セルの1つ下の行のE4セルの値が表示されます。また、「=OFFSET(E3, 1, 2)」とすれば、E3セルから下に1つ、右に2つズレるのでG4セルの値が返されるわけです。
ところがです。この数式を1つ入力したら、あとは範囲指定してCtrl＋Rで右のセルに数式コピーしたいわけです。すると、すべてE3セルの1つ下になってしまうので、すべて同じ値になってしまいます。
そこで、E3セルを基準セルとして数式コピーしたときに「1,2,3…」と1つずつズレるようします。
そこで数式を反映させたい最初のセルで次のような数式にします。
=OFFSET(E3, COLUMN（A1）-1,0)
COLUMN（A1）というのは、セルの列数を返す関数。
- A列なら → 1
- B列なら → 2
- C列なら → 3 …
つまり、横にコピーすると、数字が1ずつ増えることになります。
A1セルは1列目にあるので、「1」になります。基準セルがE3セルなので、「COLUMN（A1）-1」として、「0」。下にも移動しません。
数式をコピーすれば、下に動く行数が「1、2、3…」と変わっていきます。これにをCOLUMNを使うわけです。
| 列 | 数式（Sheet2） | 参照するセル（Sheet1） | 結果 |
|---|---|---|---|
| A列 | =OFFSET($E$3,0,0) | E3 | 11 |
| B列 | =OFFSET($E$3,1,0) | E4 | 11 |
| C列 | =OFFSET($E$3,2,0) | E5 | 15 |
ということで、縦のデータを横に並べることができました。
同じような関数でINDEX関数もあります。
INDEX関数はリスト範囲を指定して、その中の何番目のデータを選ぶかを決める関数です。
=INDEX(Sheet1!$E:$E,行番号,列番号)
今回の場合だとE2セルからE15セルの1列だけをリストとして指定し、何番目かを指定します。
ただ、先程のOFFSETとは行番号の指定方法が違います。「COLUMN(A1)-1」とはしないで、「=INDEX(imput!$E$3:$E$15,COLUMN(A1),0)」とすれば、リストの1行目のデータとしてE3セルの値が表示されます。
こちらも数式を横にコピーすれば、同じように縦のデータが横に並びます。
TRANSPOSE関数を使う
これとは別に縦のデータを一度の操作で横に並べたいなら、TRANSPOSE関数を使うと一発で並べられます。
＝TRANSPOSE(範囲)
数式を反映させたい最初のセルで「＝TRANSPOSE」として、
もとデータの範囲を指定→Enterで、
一度にすべてのセルに数式が反映されます。（Microsoft365の場合の仕様です。）これがシンプルかなと。
やり方はいろいろありますので、お好みで。
こんな感じでExcelの縦横データを自由に反映できると、Excelの腕も上がりますのでぜひ試してみていただければ。その場合は、COLUMN（列番号）とROW（行番号）も数式コピーするなら欠かせないので、使っていきましょう。
【編集後記】
昨日は個別コンサルティング（ショート）。
はじめての方でしたが、楽しく話せました。
その後、スタバに行き、
ブログ、セミナー準備、読書など。
夜は長男(10)のゲーム
プレイを見たりでした。
【昨日の1日1新】
※「1日1新」→詳細はコチラ
FORESIA テレビ転倒防止ベルト
DodoxDIY新型フリードシフトパネルカバー
スタバ アサイー ベリー フラペチーノ ブラック ver.
