機能と数式 | VBA | セミナー | オンラインソフト | お問い合わせ | その他
Top > Excel > 機能と数式

複数列を1列にまとめる





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



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

手動操作による方法


最初にお断りしておきます。この手動操作による方法は、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行目じゃなかったら?などは、それほど難しくありませんので、ぜひ自分で考えてください。仕組みが分かれば応用できますし、逆に、仕組みが分からないようなら、実務で応用することはできません。





このエントリーをはてなブックマークに追加