複数列を1列にまとめる

  1. Home
  2. Excel
  3. Excel Tips

↑こういう表を、↓みたいにしたいと。

明治時代から続く、定番中の定番質問です。まぁ、実際にはもっとデータが多いのでしょう。確かに、意外と面倒くさいです。こうした変換は、一般的に関数でやる方法が紹介されますが、ここでは、関数を使わない手動操作による方法と、関数による方法の両方を解説します。

手動操作による方法

最初にお断りしておきます。この手動操作による方法は、Excel 2007以降でしかできません。試してみましたが、Excel 2003までではできませんでした。あ、もちろん、Excel以外のアプリを使えば可能ですし、正直な話、私がやるときは秀丸エディタを使うと思います。マクロや関数を使うより簡単ではないかと。

  1. まず、表の縦横を変換します。元の表を選択してコピーします
  2. どこか、適当なセルを選択して[形式を選択して貼り付け]ダイアログボックスを開きます ちなみに、Excel 2007からは、Ctrl + Alt + Vで開けます
  3. [行列を入れ替える]をオンにして[OK]ボタンをクリックすると、コピーしたセル範囲の行列を入れ替えて貼り付けられます
  4. [挿入]タブ[テキスト]グループの[テキストボックス]を挿入します
  5. 行列を入れ替えて貼り付けた表を選択してコピーします
  6. テキストボックス内をクリックしてから、Ctrl + Vで貼り付けます
  7. テキストボックス内の文字列をすべて選択して、コピーします すべて選択するにはCtrl + A、コピーするにはCtrl + Cを押すと簡単です
  8. どこか、適当なセルを選択して、Ctrl + Vで貼り付けます
  9. [検索と置換]ダイアログボックスを開き、[検索する文字列]ボックスに半角スペースを入力し、[置換後の文字列]ボックスではCtrl + Jを押します。どちらも表示されませんので、注意深く入力してください。なお、[検索と置換]ダイアログボックスでは、前回指定した文字列が残りますので注意が必要です
  10. [すべて置換]ボタンをクリックすると、半角スペースが改行コードに置換されます
  11. 一見、文字列が消えてしまったように見えますが、実は改行されています
  12. 改行されたセルを編集状態にして、中の文字列をコピーします
  13. 任意のセルに貼り付ければ完成です

手順が多くて、面倒くさいですね。何とか、Excelの標準機能だけ(関数を除く)で実現しようとしたら、こうなってしまいました。まぁ、できるのは間違いありません。ただし、Excel 2007以降です。Excel 2003までのテキストボックスでは、上記の手順6で貼り付けができません。

関数でやる方法

上のように参照するには、次のような計算式を入力します。

列番号のABCを計算で作成するのは面倒くさいので、ここはOFFSET関数で考えます。

こうした関数を1つずつ入力するのは現実的ではありません。最初の数式を作成して、下方向にコピーすれば、自動的に適切な数式が作成されると便利です。そのためには、OFFSET関数の引数が

0,0
1,0
2,0
0,1
1,1
2,1
0,2
1,2
2,2
：：

と、自動的に変化しなければなりません。まず、0→1→2の繰り返しを計算で作ってみましょう。

これには、最初に数式を入力するセル(ここではセルA6)の行番号が重要になります。各行の行番号と、求めたい数値の関係は次のようになります。

こんなときは、行番号をある数値で割った余りに注目します。

数式が入力されているセルの行番号はROW関数で分かります。また、ある数値で割った余りはMOD関数で求められます。

さて、0→0→0→1→1→1→2→2→2という数値は、どんな計算で作成できるでしょう。今度は、ある数値を割った商(答え)を使います。

つーことです。日付の書式設定が反映されていませんので、コピー先のセルに、必要な書式を設定してください。

なぜ3で割るのか？とか、最初のセルが6行目じゃなかったら？などは、それほど難しくありませんので、ぜひ自分で考えてください。仕組みが分かれば応用できますし、逆に、仕組みが分からないようなら、実務で応用することはできません。