Excel 参照行の削除でも可変しない関数

質問

Excel 参照行の削除でも可変しない関数

同じ形式で、数シートにわたり入力された文字をまとめた表を別のシート(シート１)に作成しています。
シート2以降の参照元データーは一定のルールで入力されていますが、削除や追加が頻繁に行われます。削除や追加の度にシート1Ｂ列・Ｃ列の関数が参照する行数が追随して可変しないようにしたいのですが可能でしょうか。

具体的には、シート2において6行目から9行目を削除した際に、シート1では「B2＝みかん」／「C2＝イチゴ」と繰り上がり、B行・C行は常に指定した行を参照するようにしたいと思っています。

＜シート1＞
　Ａ Ｂ　　　　 Ｃ
1 タイトル りんご　　ばなな　（← 3行目）
2 タイトル かき　　　なし 　 （← 8行目）
3 タイトル みかん　イチゴ （←13行目）

・B列:「=IF(ISERROR('Sheet2'!$B3:$B3),"",'Sheet2'!$B3:$B3)」
・C列:「=IF(ISERROR('Sheet2'!$D3:$D3),"",'Sheet2'!$D3:$D3)」

＜シート2、3…＞

参照したいセルは、Ｂ列とＤ列の3行目、8行目、13行目…と一定の間隔で入力されており、全てのシートにおいて同じ箇所に入力されています。

シートごとにカテゴリーの個数は(図A1:E4までを１カテゴリーと考えています)異なり、作業の度にカテゴリーの追加や削除が頻繁にあります。

うまく説明できているか不安ですが、良い方法を教えてください。

回答No.1

keithin · ベストアンサー

一番シンプルには，Shee1のＢ列に
=IF(ISERROR(INDIRECT("'Sheet2'!B3")),"",INDIRECT("'Sheet2'!B3"))
=IF(ISERROR(INDIRECT("'Sheet2'!B8")),"",INDIRECT("'Sheet2'!B8"))
=IF(ISERROR(INDIRECT("'Sheet2'!B13")),"",INDIRECT("'Sheet2'!B13"))
のように仕込んでおきます。

コピーしてつるつるっと増やせる式で回答しようかなとも思いましたが，セル範囲だけでなくシート自体も削除したり追加したりの編集にも耐える式を今はわざわざ書いていらっしゃるので，上記のようにコピー出来ないカタイ式で書いた方が適切と思います。

＃参考
=IF(ISERROR(INDIRECT("'Sheet2'!A1")),"",INDEX(INDIRECT("'Sheet2'!B:B"),ROW(A1)*5-2))

＃余談
あなたの式がいま「'Sheet2'!$B3:$B3」のような書きぶりになっているのは，シート名が「Sheet1」などのような言葉ではない事と，セル結合したセルをクリックして数式を書かせているためです。
あまり安全な表記ではないので，できるだけ上述で回答したような，結合したセル範囲の左上の１個のセルを参照する式で書くようになさってください。

お礼

早々の回答ありがとうございます。

まさにやりたかったことで、100％希望が叶いました。

＞上記のようにコピー出来ないカタイ式で書いた方が適切と思います。
説明不足にもかかわらず、意図まで汲み取って頂きありがとうございます。

また、細かいご指摘も頂き大変勉強になりました。未熟なため日々格闘中ですが、今回教えていただいた「INDIRECT」も使いこなせるように頑張ります。

本当にありがとうございます。