1. まとめトップ

【Excel Tips】縦のリストを横に並び替える方法

複数の関数を組み合わせたり、マクロを使ったりせず、大量に縦に並んだリストをキーごとに横に並べ替える方法を紹介します。

更新日: 2017年05月06日

7 お気に入り 22787 view
お気に入り追加

この記事は私がまとめました

majisonさん

関数やVBAを駆使して実現する方法

キーにする値と共に縦に並んだリストをキーごとに横に並べる(可変長のリストを作りたい)場面にはみなさん割と出くわすのではないでしょうか?
質問サイトを見ても関数を複数組み合わせることでこれを実現する方法が紹介されています。

あれ、難しくない??

どの方法も関数を複数組み合わせたり、VBAでマクロを組んだりとそこそこのスキルを必要とする方法なので、エクセルの初級者だとまず内容を理解しようとする時点でつまずいてしまいそうです。

そこでここではややアナログなやり方にはなりますが、関数等の知識をあまり必要としないエクセル初級者にも優しい方法をご説明します。
手順も文章にするとまぁまぁ長いですが、1つ1つは簡単です^^

① まずはキーごとにソート

まずは一まとめにするキーごとにソートします。2列目の値でのソートについては任意ですが、ここで上に来ているものから順に左に並ぶことになるので、その点を留意してください。

②各キーの先頭行を特定する。

左に列を追加し、Exact関数を追加します。Exact関数は2つの値を比較して一致すればTRUE、違っていればFALSEを返す関数です。
自分の行のキーと1つ前の行のキーを比較します。こうすると先頭行は前の行とキーが違うのでFALSEが返り2行目以降ではTRUEが返ります。

③各キーの先頭行4列目に値を代入する

キーの先頭行(=②でFALSEとなった行)の4列目に3列目と同じ値を=で代入(a)します。終わったらオートフィルタで②でFALSEとなった行に絞り込み、全てのキーの先頭行に(a)の内容をコピーします。

④③と各行の値を引っ付ける

③で4行目に値が埋まっていない残りの行全てに対し、「1つ上の行の4列目(③で埋めたセル)と自身の行の3列目(値)を&でつなぎます。(実際には区切り文字を挟むので&”、”&でつないでいきます。)こうすると下に行くほど、その行より上の値を全部つないだ長い文字列になっていきます。

※1つのセルに入力が終わったら他のセルは③同様に②がTRUEになっている行にコピーでOKです。最終的にセルごとに値を収めたい場合でもとりあえずカンマなどを区切り文字にして1文字列につないで下さい。

⑤4列目を実数に変換する

4列目はこの時点では数式が入力されている状態ですので、ここで実数に変更します。
4列目全体を行ごと選択し「コピー」。その後選択範囲は変更しないまま「値として貼り付け」を実行します。

⑥各キーの末尾行を特定する。

実数に変更が終わったら②で追加した一番左の列を一旦クリアし、今度は自身の行のキーと1つ下の行のキーを比較するExact関数を追加し、②と逆で今度は各キー一番下の行を特定します。
間違えていなければ各キー一番下の行はFALSE、それ以外はTRUEとなっているはずです。

⑦不要な行、列を削除

ここまで来たら不要な行と列を消すだけです。
まずは⑥でTRUEとなった行をオートフィルタで絞って表示し行全体を削除。全ての行の値が連結された末尾の行だけを残します。
さらに一番左の列、および元々の値を格納していた列を削除します。

⑧セルごとに値を分ける

1つのセルに値がつながったままで良い場合は⑦で作業完了です。
セルごとに値を分けたい方はさらに値の列を選択した状態で「データ」→「区切り位置」でセルごとに値を分割して作業完了です。お疲れ様でした!

1 2