ブログトップ

カクレ理系のやぶにらみ

tamm.exblog.jp

時間のある方はお読みください。軽い気持ちで読み始めると頭が痛くなります。

セルの参照式を行列入れ替えてコピーする、あるいは縦方向に並んだデータを横方向に並び替える

 たとえば、縦(列)方向に入力されているデータを横(行)方向に並べ替えたいという場合があります。この場合、縦方向のデータを選択してコピーしてから、右クリックで「形式を選択して貼り付け」を選び、「値」にチェックをいれてから「行列を入れ替える」をチェックして「OK」をクリックする方法があります。

c0136904_21383444.jpg


 ただ、この方法だと数値の貼付になってしまうので、元のデータが更新された場合同じ作業をしなければコピー先に変更が反映されることはありません。これだとちょっと不便ですよね。
 そこで、今回はOFFSET関数と作業用セルを使って、データが入っているセルの参照式を横方向に一気にコピーするという方法をご紹介します。なお、この方法の応用技として、横方向に1個動くと縦方向に2個動いたセルを参照するというトリッキーな複写ができるので、覚えておいて損はないかもしれません。


 下の図をご覧ください。

c0136904_2138454.jpg



 セルB4から縦方向に文字が入力されています。これを横方向に並べ替えたいのですが、数式を使ってこれをやってのけようというのが今回の目的です。

 そのために、まずOFFSET関数についておさらいしておきましょう。OFFSET関数の引数は次の通りです。

 OFFSET(基準となるセル,行数,列数,高さ,幅)

 ここで、「高さ」と「幅」は省略可能であり、今回は使わないのでないものと思ってもらって構いません。そうするとOFFSET関数の構文は次の通りとなります。

 OFFSET(基準となるセル,行数,列数)

 これが何を意味するかというと、「基準となるセルから縦方向に動かす数(行数)と横方向に動かす数(列数)を指定すると、その分移動した先のセルを参照する」ということになります。
 具体例をあげてみましょう。

 =OFFSET(A1,1,1) という数式は、セルA1から縦方向に1つ横方向にも1つ動いたところ、つまりセルB2を参照します。

 同様に、=OFFSET(A1,1,0)という数式はセルA1から縦方向に1つだけ動いたところであるセルA2を参照します(横方向の移動は0です)。

 そこで図に戻ると、セルB4には「あ」という文字が入力されていて、セルB5には「い」という文字が入力されています。セルB5というのは、基準となるセルであるB4から縦方向に1つ下に移動したセルでもあります。同様に、セルB6は基準となるセルB4から縦方向に2つ下に動いたセルということになります。

 そこで、まず作業用セルを用意しましょう。
 まず、セルE9に1という数値を入力します。次にその右隣であるセルF9に「=E9+1」という数式を入力し、この数式を右方向にコピー&ペーストします。(数式の答えが14となるところまでコピペしてください。)

 次に、セルD10に「=B4」という数式を入力します。すると「あ」という文字が表示されます。今度はセルE10に「=OFFSET($B$4,E9,0)」という数式を入力します。その結果は「い」という文字が表示されることを確認してください。

 ここでちょっと説明しておくと、OFFSET関数の引数は数値なので、答えが数値になる関数や数式を引数に入れることもできるのです。つまり、セルE9には2という数値が入っているので、「=OFFSET($B$4,E9,0)」という数式は「=OFFSET($B$4,2,0)」と同じなのです。
 今回のように、縦方向にデータが入っている場合、基準となるセルからの距離が1つずつ増えていくわけなので、OFFSET関数の引数も1つずつ増えていくことになります。その場合、引数を数値で入力するよりも作業用セルを使う方が作業効率は圧倒的に改善されます。
 作業用セルにあらかじめ連続する数値をいれておき、OFFSET関数の引数としてこの作業用セル参照するように数式を設定して、右方向にコピペすれば、OFFSET関数の引数が自動的に変化してくれるようになるのです。

 もう一つ注意していただきたいのは、基準となるセルであるB4を絶対参照で指定しておくということです。こうしておけば、OFFSET関数をコピペしても基準となるセルは固定されたままです。
 試しに、元のデータを変更すると、横方向に並び替えたところにもその変更が反映されるはずです。


 なお、応用編として、作業用セルの値を2つずつ増やすようにすると、縦方向に1つおきに参照するようになります。よかったら試してみてください。

追記
 今回は縦方向だけの移動でしたが、縦横の移動をがあるという場合、作業用セルを2段にして1段目を行数、2段目を列数に充てて、それぞれ参照する数式をOFFSET関数に組み込んでください。
by t_am | 2016-07-09 21:40 | Excel のあの手この手