エクセル数式：参照元の変更を反映したい

質問

エクセル2003です。

ある名簿のファイルAと
それを元に統計を出す為別のファイルBを作っています。

ファイルBにはファイルAのC列とO列を材料にして条件の合う件数が出るように下記のような式が多数入っています。

=SUMPRODUCT((LEFT([ブック名（A）.xls]シート名!C1:C200,2)="●●")*(NOT(ISERROR(FIND("△○△",[ブック名（A）.xls]シート名!O1:O200)))))

ファイルAにおいて、例えば198行目を追加や削除するとこれにそってファイルBの式は変わらないのでしょうか。（C1:C200→C1:C201とかC1:C199など）

他に都合があって$C$1:$C$200や$C1:$C200としていたこともあったのですが、いづれもファイルAの変化は反映されず、毎回置き換えなり修正をしなくてはなりません。
いい方法があるでしょうか。

回答No.5

makohand · ベストアンサー

ANo3のmakohandです。

> エクセルがどういう構造なのか、とても興味あるところです。

すみません。私も自分でこうして使っているというだけで理論までは分かりません。
私が使っているものでは同一ファイル内においては数式は変更されて、それを参照する値も変更されます。
元のファイルにシートを追加できないとなると私の知識ではすみませんが分かりません。
私のつたない知識内でしようとするとファイルが数個あっても追加シートをそれぞれのファイルに追加して（1つの追加シートを作ったらそのシートをコピーして参照シート名を置換）しまうと思います。

お礼

そうだったのですか！
勉強になりました。

つたないなんてとんでもないです。

私にとってはひとつ大きななぞが解けました。

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

回答No.4

Wendy02

こんばんは。Wendy02です。

#3のhakohandさんのおっしゃるとおりですね。うっかりしていました。その後で、元のシートに解があって、そのセルにある解をリンクで呼び出せばよかったのでした。

失礼しました。

お礼

いえいえ、いろいろと考えてくださって感謝しています。

エクセルって奥が深いですね。
本当にありがとうございました。

回答No.3

makohand

このファイルBに入っている式をファイルAの新しいシートに置くことはできないでしょうか。
ファイルBではそのファイルAの新しいシートのその数式が入っているセルを呼び出すようにするのです。
するともともとのシートの行を追加や削除するとファイルAの新しいシートの数式の範囲は自動的に変わり、それを呼び出しているファイルBの値も変わります。

補足

アドバイスありがとうございます。

ということは・・・同じファイル内であれば参照元に沿って数式は変更されるのでしょうか。

実際にはデータが多すぎて、ファイルごとに小分けしているので今回いただいた案はちょっと実現できそうにないのですが（ごめんなさい）
エクセルがどういう構造なのか、とても興味あるところです。

できれば再度お答えいただけませんか？宜しくお願いします。

回答No.2

Wendy02

こんにちは。　Wendy02です。

>ご心配のとおり、多々空白がありまして…(；^_^A

例えば、MATCH関数に、ありえないダミーの文字を探させます。

参照範囲をそれぞれ
=INDIRECT("Sheet1!$C$1:$C$" &MATCH("甕",Sheet1!$C:$C,1))

=INDIRECT("Sheet1!$O$1:$O$" &MATCH("甕",Sheet1!$O:$O,1))

としたら、最後の文字列まで取れます。

なお、相手のブックを開けない限りは、その範囲は取得できませんから、もしも、ブックを開けない場合は、ユーザー定義関数を作るしかありません。

回答No.1

Wendy02

こんにちは。

範囲を名前－登録することでしょうね。

名前定義
-----------------------------
名前：
範囲1

参照範囲：
シート名!C1:C200
-----------------------------
名前：
範囲2

参照範囲：
シート名!O1:O200
-----------------------------

もし、その都度領域が変わるようでしたら、

参照範囲をそれぞれ
=INDIRECT("Sheet1!$C$1:$C$" &COUNTA(Sheet1!$C:$C))

=INDIRECT("Sheet1!$O$1:$O$" &COUNTA(Sheet1!$O:$O))

文字列でしたら、COUNTA, 数値だけでしたら、COUNT ですね。
ただし、COUNT や、COUNTA は、空白行があると、正しく出ません。

=SUMPRODUCT((LEFT(ブック名（A）.xls!範囲1,2)="●●")
*(NOT(ISERROR(FIND("△○△",ブック名（A）.xls!範囲2)))))

特別な数式でなかったら取れるはずです。

お礼

早速ありがとうございます。

>ただし、COUNT や、COUNTA は、空白行があると、正しく出ません。

ご心配のとおり、多々空白がありまして…(；^_^A

すぐにはできないのですが試してみます。
取り急ぎ、ありがとうございました。