今回はExcelでの行列入れ替えを関数で行う方法です。
入れ替え方はデータによって方法が異なるのですが、今回の方法は以下のようなデータを入れ替えます。
※今回の方法は規則性のあるデータに限ります
手作業で一個ずつデータをコピペするのもいいですが、まとまったデータですとかなり時間短縮できます。
難易度：
使う関数はINDEXとROW関数だけです。
シンプルなので比較的簡単ですよ。
今回の手順
- パターンを調べる
- ｎ番目の行数＝n番目の列となる式を入力する
- １で求めた項目数分の式を作る
- 不要な列を削除
大きくこの４つの流れになります。
1. パターンを調べる
今回の手順で最も重要な場所です。
このデータの中に項目数がいくつあるのかを調べることと、規則性があることを確認します。
今回のデータは、名称・日にち・時間・場所の４つの項目がありそのデータ郡が５つあります。
そして規則性があります。
※規則性がないと抜き出した際に本来抜き出したいデータの順にならなくなるため、規則性があることが重要です。
項目数４という数字を覚えておいてください。
もし項目数が３つの場合は３になります。
2. ｎ番目の行数＝n番目の列となる式を入力する
数式は以下のようになります。
=INDEX($B$2:$B$21,(ROW()-2)*4+1,1)
※今回のデータの場合のD2セルに入っているものです。
INDEXではB列しか抜き出す必要がないので、=INDEX($B$2:$B$21,(ROW()-2)*4+1,1)
青のアンダーラインの弾いている場所は固定になります。
そして今回のｎ番目の行数＝n番目の列となる式ですが、(ROW()-2)*4+1に当たります。
式だけだとわかりづらいと思いますので、画像も入れて説明します。
上記のように行と列を入れ替えたいので、行数を求める関数ROWを使って求めていきます。
ROW()-2)*4+1を求めると(2-2)×4＋1=1となります。
※ROW（）では行数が２行目になるので２という数が求められます。
これで基本の計算式ができましたので、次は列の数分数式を変えていきます。
3. １で求めた項目数分の式を作る
次は下図でいるところの②・③・④の式を作っていきます。
②：=INDEX($B$2:$B$21,(ROW()-2)*4+2,1)
③：=INDEX($B$2:$B$21,(ROW()-2)*4+3,1)
④：=INDEX($B$2:$B$21,(ROW()-2)*4+4,1)
青いアンダーラインの部分だけ変更します。
後はオートフィルをすれば⑤以降の数値も自動的に出力できるようになっています。
4. 不要な列のデータを削除
これで行と列の入れ替えができましたので、不要な行のデータを削除していきます。
行を入れ替えたデータを全て選択します。
Ctrl+Cでコピーをします。
右クリックで貼り付け→値貼り付けを選択
不要な列を削除すれば完成
COLUMN関数を使うと手順３は省ける
関数を一つ追加するの必要があるのですが、列数分式を作る手順３の工程を省く方法があります。
その方法はCOLUMN関数を使うと列を自動的に求めることができるので、更に時短になります。
COLUMN関数を追加するのはこの場所です。
=INDEX($B$2:$B$21,(ROW()-2)*4+1,1)
=INDEX($B$2:$B$21,(ROW()-2)*4+COLUMN()-3,1)
COLUMNで‐３するのは出力する先を１列目とするためです。
D列＝４列目。
COLUMNを使えば自動的に列数を取得できますので、あとはオートフィルで全ての行列を入れ替えることが可能になります。
まとめ
いかがでしたでしょうか。
ホームページなどの情報から引っ張ってくる時、ホームページは縦長のデータが多いのでそういった時に役立ちますよ。
今回使用したテストデータも掲載しておきますので、下記ダウンロードボタンよりどうぞ！
コメント