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

複雑な参照「結合セル→単一セル」



連続して入力されている単一セルを、結合セルから参照する仕組みは、下記のページをご覧ください。


さて、今回は逆のパターンです。



こちらは難しいですから覚悟してくださいね。なお、ここでは上図のように、入力されているセル(ここではA列)を、式で参照するセル(ここではC列)をで表します。これ、後で重要になってきますからね。なお、今回もINDIRECT関数でやります。OFFSET関数でも考え方は一緒です。

まず、次の前提で考えます。

  • 結合されているのは2行
  • 結合セルの先頭セルは1行目
  • 参照するセルの先頭セルは1行目

結合セルは、結合されている複数セルのうち、左上のセルがアドアレスになりますので、次のように参照できればいいわけです。



こうした参照式での必需品であるROW関数を入力してみましょう。



つまり、ここでは

1 のとき 1 になる
2 のとき 3 になる
3 のとき 5 になる

ような仕組みを考えればいいわけです。とりあえず、ROW関数の結果を2倍してみましょうか。



おお!いきなりキタ−!これ1を引けば欲しい結果になりませんか?



なあ〜んだ、超簡単でしたね。あとはこれに、文字列の"A"を結合してアドレスを作り、そのアドレスをINDIRECT関数で参照すればいいんです。





と、まぁ、

  • 結合されているのは2行
  • 結合セルの先頭セルは1行目
  • 参照するセルの先頭セルは1行目

という前提でしたら超簡単です。では、ちょっと変えてみましょう。



今度は、結合されているセルの行数が「3」です。したがって、ここでは

1 のとき 1 になる
2 のとき 4 になる
3 のとき 7 になる

ような仕組みが必要です。これは、次のようになります。



じゃ、結合されているセルの行数が「4」だったら、どうなるでしょう。



ここまでをまとめると、次のような法則が分かります。



このように、結合している行数が一定でしたら参照可能です。つまり、次のように、結合している行数が不定のときは参照できません。



上図のような場合は、元データのレイアウトを修正してください。てか、上図のように入力することが間違っています。
ちなみに、今回はシンプルなケースで解説しました。これがもし、参照元の結合セルや参照先のセルが1行目ではなく途中から始まっていたら…などの条件が加わると、上記の考え方だけでは対応できなくなります。そのときは、パズルを解くような気持ちで、がんばってください。





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