EXCEL 結合されたセルを関数で参照する方法について

質問

お世話になります。

EXCELにてDBとして入力されたものを必要な部分のみ抜き出したファイルを作成する為、
関数で元シートへのセル参照させるシートを作成したのですが
結合された部分の参照ができなく困っております。

<例>～～～～～～～～～～～～～～～～～～～～～～～～～
SheetA [A1～A10][A11～A20]が結合セル
SheetB [A1]に「='SheetA'!A1」 [A2]に「='SheetA'!A2」 [A3]に・・・[A20]まで同様
とさせるとA1とA11のみSheet1の値が反映されてA2～A10、A12～A20が出力されない。
～～～～～～～～～～～～～～～～～～～～～～～～～～～

A2～A10をA1とさせるとすればいいことですが
実際には数百件のデータ入力があり、結合部分も複数存在しています。
今後DBが増えていくことを考え、関数式セル右下ひっぱりの連続データ作成で対応させたいのですが、
SheetBでの A2 が "Null" であったら上方向へ検索し文字入力があった部分を引用させるといった
関数をくむことが出来ませんでした。

なにかいい知恵をご教示頂けましたら幸いと思います。

ことばではわかり難いかもしれませんのでJPG添付もさせていただきます。

回答No.3

okormazd · ベストアンサー

添付図の場合のように、
E10:=IF(B2<>"",B2,OFFSET(E10,-1,0))
F10:=C2
E10:F10を選択して下にフィルドラッグではダメですか。

お礼

出来ました！まさにこれです！
OFFSETとという関数自体使ったことが無いので一生懸命検索で何とかしようとしていました。

下に伸ばした時、結合セル数関係無しに反映された時に思わず鳥肌が立ちましたw
心より御礼を申し上げます！

回答No.2

bin-chan

＃１です。大失敗。
modでは無く、intでした。

なので以下の通りに訂正。

１は、Row関数()で現在の行番号がわかります。
1行目なら参照するのはセルA1、2行目ならセル11です。
10行ワンセットなのでint関数で整数の商を求めますが途中で±1します。
式[=int((Row()-1)/10)+1]を作成してください。
1　→　1
2　→　11
3　→　21
が求められることを確認してください。

２は、セル参照を文字列で指定できるIndirect関数を使用します。
式[=indirect("SheetA!A"& int((Row()-1)/10)+1)]です。
これでSheetBのセルはSheetAのセルを10行おきに参照できます。
データが増えたら、SheetBのセルをドラッグでコピーして対応してください。

回答No.1

bin-chan

解決のために以下の２点を考えてみてください。

１．例示の１０行でワンセットの場合、セットの先頭行を計算で求める。
２．数値で計算された位置をセル参照させる。

※見出し行が無いものとして説明。実際には見出し行の分オフセットしてください。

１は、Row関数()で現在の行番号がわかります。
1行目なら参照するのはセルA1、2行目ならセル11です。
10行ワンセットなのでmod関数で剰余を求めますが途中で±1します。
式[=mod((Row()-1),10)+1]を作成してください。
1　→　1
2　→　11
3　→　21
が求められることを確認してください。

２は、セル参照を文字列で指定できるIndirect関数を使用します。
式[=indirect("SheetA!A"& mod((Row()-1),10)+1)]です。
これでSheetBのセルはSheetAのセルを10行おきに参照できます。
データが増えたら、SheetBのセルをドラッグでコピーして対応してください。

補足

> bin-chan 様

ご回答ありがとうございます。
結合セルが固定数ならばうまくいく話だったのですが、そうではない為
いただきました回答では駄目でした。

ボトルネックなのが「DB増徴にともない出力も簡単に増やせること」でありまして
「連続データ作成」での対応が可能であることが必須でありますorz