エクセル関数応用
他ブックを参照できる関数、他ブックを参照できない関数

参照するブックが閉じた状態でも、正しく取得できているかどうか、
代表的な関数を一つずつ確認してみましょう。
以下、○×で表記します。
○ ・・・ 正しく取得できている
× ・・・ エラー(#VALUE)
代表的な関数から順次試してみると、
SUM ・・・ ○
これは問題ないです。
IF ・・・ ○
これも問題ありません。
COUNTA ・・・ ○
まったく問題なし。
VLOOKUP ・・・ ○
これも問題ないです。
MATCH ・・・ ○
INDEX ・・・ ○
いやー、結構大丈夫じゃないですか。
と思いきや・・・
SUMIF ・・・ ×
ダメですね。
COUNTIF ・・・ ×
あれれ、という感じでしょうか。
SUMとIFが大丈夫なのに、SUMIFがだめなんですね。
では、データベース関数ならどうか。
DSUM ・・・ ×
DCOUNTA ・・・ ×
やはりダメです。
では、とっておきのINDIRECTは、
INDIRECT ・・・ ×
まあ、これはそんな感じでしょうかね。
全体としては、ある列を条件として、他の列から情報を得るといったものがダメだということになります。
むしろ、VLOOKUPが別格な感じになります。

ツイッターで、SUMPRODUCT関数も使えると教えてもらいました。

個人的に、SUMPRODUCT関数で他ブックを参照したこともなく、完全に抜かしてしまいました。
実際に検証しましたが、問題なく取得できています。
一連のツイートの中にも書かれていますが、
SUMIFやCOUNTIFが他ブックを取得できない代替えとしてSUMPRODUCT関数を使えるという事になります。
ただし、SUMPRODUCTを使った条件集計は、配列を理解する必要があり難解な数式になりがちです。
SUMPRODUCT関数については以下を参照してください。

参照できる関数と、参照できない関数をまとめると、

他のブックを参照したい関数としては、大体この辺りでしょうか。
全ての関数を確認まではしていませんが、
上記掲載以外の関数についても、参照できるものと出来ないものがあります。
もし使う必要があるなら、実際に試して確認してください。

テーブルでの構造化参照を用いた場合は、他のブックが閉じている場合は全ての関数で取得できません。
テーブル構造化参照とは、以下のような参照です。
=SUM('パス\Book1.xlsx'!テーブル1[列1])
他ブックのテーブルを参照しているブックを開くと、以下のメッセージが出てしまいます。

参照しているブックを開けば取得できるとはいえ何かと不便です。
他ブックのテーブルは参照しないようにしてください。
テーブルでも、直接セルアドレス（B2:B11等）を指定してください。

そもそも、単一のセル参照なら問題ありません。
つまり、
=セル番地
これなら、他のブックを問題なく参照できます。
解決策として、もっとも簡単なのは、
データ範囲を全て、=セル番地、これでどこかのセルに取得しておけば良いです。
そして、そのセルを使って計算するようにしておけば問題ありません。
しかし、これは、あまりにも無駄ですし、
それなら、そもそも、別ブックである必要性がないですよね。

上記に説明した関数のうち、
SUMIF
COUNTIF
このあたりの関数については、出来たら他ブック参照したいと思う方も多いでしょう。
なんとか他の関数で出来ないか・・・
そこで考えたいのは、
SUM ・・・ ○
IF ・・・ ○
この二つの関数で何とかならないかと考えたくなります。
これらの関数の組み合わせなら参照出来るんじゃないでしょうか。
=SUMIF(範囲,検索条件,合計範囲)
これを
{=SUM(IF(範囲=検索条件,合計範囲)}
このように書き直します。
{・・・}
この{}は、配列数式であることを意味します。
普通に関数を入力した後、
最後にCtrl+Shift+Enterで入力確定すると、
数式が、{}で囲まれて、配列数式となります。
これなら、他ブックの集計が可能です。
少し難しいので、具体的な例を、
自身のA1セルを条件として、Book1のA列を条件範囲としてB列を集計する場合、
=SUMIF([Book1.xlsx]Sheet1!$A:$A,A1,[Book1.xlsx]Sheet1!$B:$B)
↓
{=SUM(IF([Book1.xlsx]Sheet1!$A:$A=A1,[Book1.xlsx]Sheet1!$B:$B))}
このようになります。
※[Book1.xlsx]の部分は実際のシートではフルパスで表示されます。
ほぼ同様ですが、
=COUNTIF(範囲,検索条件,合計範囲)
これを
{=SUM(IF(範囲=検索条件,1)}
つまり、条件に一致したら1にして、それを集計すれば件数になります。
一応、例としては、
自身のA1セルを条件として、Book1のA列を条件範囲として件数を数える場合、
=COUNTIF([Book1.xlsx]Sheet1!$A:$A,A1)
↓
=SUM(IF([Book1.xlsx]Sheet1!$A:$A=A1,1))
※範囲指定について

ここまで、
他ブックを参照できる関数、他ブックを参照できない関数として解説してきましたが、
そもそも論として、
他のブックを参照することは、あまり望ましいことではありません。
常にリンク切れの問題がつきまといます。
他ブックを参照する必要性がある場合は、
マクロVBAで処理したほうが良いでしょう。

同じテーマの記事
新着記事
アクセスランク