Excel。結合したセルにデータをコピーするとズレるので、どうにかしたい
＜INDEX+ROW関数＞
結合したセルは、ちょっとクセというか困ったことがあります。
例えば、次のような場合。
A列に店舗名があって、C:D列のように、上半期下半期がある表に店舗名を設定したいわけです。
先に、C2:C3をセル結合しておいたとしても、A2:A5を単純にコピーして貼り付けても、セルの結合は解除されるし、店舗名も希望のように設定することができません。
やりたいのは、このような表にしたいわけです。
ですから、単純なコピーではダメですし、セル参照によるオートフィルで数式をコピーも意味を成しません。
今回のように、4店舗程度ならば、自分で入力したほうが早いかもしれませんが、件数が増えてしまうと、とても大変な作業になってしまいます。
このようにセル結合した時には、ちょっとクセがあるというわけですね。
では、どのようにしたらいいのでしょうか？
C2には、このような数式を設定して、オートフィルで数式をコピーしてみましょう。
=INDEX($A$2:$A$5,ROW(A2)/2)
INDEX関数を使うことで、希望のように、店舗名を設定することができました。
ROW関数はつかっていますが、シンプルなINDEX関数だけで対応できるのです。
では、この数式を見ていきます。
最初の引数は、配列ですが、これは、範囲なので、$A$2:$A$5を設定します。
2つ目の引数は、行番号ですが、ここがポイント。ROW関数をつかっています。
このROW関数の意味を知るために、E列にROW関数の結果を算出してみました。
結合されたセルは、上側の行番号が算出されていることがわかります。
A2:A5までのデータにある行番号は1～4なので、ROW関数で算出した結果をオートフィルで数式をコピーしてしまうと、2/4/6…と2の倍数で増えてしまうので、2で除算することで、1～4の数値を算出することができます。
なので、INDEX関数の2つ目の引数である行番号には、ROW(A2)/2と設定しているわけです。
よって、C2のINDEX関数は、
=INDEX($A$2:$A$5,ROW(A2)/2)
とすることで、オートフィルで数式をコピーするだけで、結合したセルに、値を設定することができます。
INDEX関数は、色々使える関数ですので、色々試してみるといいかもしれませんね。
