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

Excel関数の解説、関数サンプルと高等テクニック
最終更新日:2020-06-07

他ブックを参照できる関数、他ブックを参照できない関数


他のブックを参照する関数を入れた場合、
そのブックが開いていないとエラーになってしまう関数があります。
一方、ブックが開いていなくても、正しく結果取得できる関数があります。


なぜかと言う理由は、作成者のMSに聞かないとわからないことですが、
どの関数が使えて、どの関数がつかえないのか・・・

使えない関数の場合、代用できる関数があるか等を考察してみました。

他ブックを参照できる関数、他ブックを参照できない関数

参照するブックが閉じた状態でも、正しく取得できているかどうか、
代表的な関数を一つずつ確認してみましょう。
以下、○×で表記します。
○ ・・・ 正しく取得できている
× ・・・ エラー(#VALUE)

代表的な関数から順次試してみると、
SUM ・・・ ○
これは問題ないです。

IF ・・・ ○
これも問題ありません。

COUNTA ・・・ ○
まったく問題なし。

VLOOKUP ・・・ ○
これも問題ないです。

MATCH ・・・ ○
INDEX ・・・ ○
いやー、結構大丈夫じゃないですか。
と思いきや・・・

SUMIF ・・・ ×
ダメですね。

COUNTIF ・・・ ×
あれれ、という感じでしょうか。

SUMとIFが大丈夫なのに、SUMIFがだめなんですね。
では、データベース関数ならどうか。

DSUM ・・・ ×
DCOUNTA ・・・ ×

やはりダメです。
では、とっておきのINDIRECTは、

INDIRECT ・・・ ×
まあ、これはそんな感じでしょうかね。

全体としては、ある列を条件として、他の列から情報を得るといったものがダメだということになります。
むしろ、VLOOKUPが別格な感じになります。

SUMPRODUCT関数:後日追記

ツイッターで、SUMPRODUCT関数も使えると教えてもらいました。
個人的に、SUMPRODUCT関数で他ブックを参照したこともなく、完全に抜かしてしまいました。
実際に検証しましたが、問題なく取得できています。

一連のツイートの中にも書かれていますが、
SUMIFやCOUNTIFが他ブックを取得できない代替えとしてSUMPRODUCT関数を使えるという事になります。
ただし、SUMPRODUCTを使った条件集計は、配列を理解する必要があり難解な数式になりがちです。

SUMPRODUCT関数については以下を参照してください。
SUMPRODUCT関数(配列の対応する要素の積の合計)
引数として指定した配列の対応する要素間の積をまず計算し、さらにその和を返します。SUMPRODUCT関数の書式 SUMPRODUCT(配列1,[配列2],[配列3],...) 配列1 計算の対象となる要素を含む最初の配列引数を指定します。配列2,配列3,... 省略可能です。
複数条件の合計・件数
複数条件の合計、複数条件の件数を求める関数式です。以下の表で説明します。複数条件の合計 「みかん」の「M」の合計売上は まず、2007以降ならSUMIFSが使えますので、問題はないと思います。=SUMIFS(C2:C10,A2:A10,"みかん",B2:B10,"M") SUMIFS(合計範囲,

他ブックを参照できる関数、他ブックを参照できない関数のまとめ

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

関数名 結果
SUM
IF
COUNTA
SUMIF ×
COUNTIF ×
VLOOKUP
MATCH
INDEX
DSUM ×
DCOUNTA ×
INDIRECT ×
SUMPRODUCT

他のブックを参照したい関数としては、大体この辺りでしょうか。
全ての関数を確認まではしていませんが、
上記掲載以外の関数は、全て参照できないと考えた方が良いでしょう。
ただし、検証漏れがあり取得できる関数が残されている可能性はあるかもしれません。

テーブル構造化参照では他ブックは取得できません

テーブルでの構造化参照を用いた場合は、他のブックが閉じている場合は全ての関数で取得できません。

テーブル構造化参照とは、以下のような参照です。
=SUM('パス\Book1.xlsx'!テーブル1[列1])

他ブックのテーブルを参照しているブックを開くと、以下のメッセージが出てしまいます。

エクセル Excel 他ブックを参照できる関数

参照しているブックを開けば取得できるとはいえ何かと不便です。
他ブックのテーブルは参照しないようにしてください。
テーブルでも、直接セルアドレス(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))

※範囲指定について
上記では、$A:$A、このように列全体として書いています。
ただし、配列数式の場合、
列全体を指定すると、データ量によっては膨大な再計算時間がかかってしまう場合が出てきます。
ここでは記述を簡略化するために列全体としましたが、
配列関数を使う場合は、行数を限定するようにしたほうがパフォーマンスが良くなります。

他ブックを参照することについて

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



同じテーマ「エクセル関数超技」の記事

OFFSET関数 解説・応用・使用例
MATCH関数 解説・応用・使用例
選択行の色を変える(条件付き書式,Worksheet_SelectionChange)
他ブックを参照できる関数、他ブックを参照できない関数
時間計算で困ったときの確実な対処方法
VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
SUMIF関数の良くある間違い
論理式とは条件式とは(IF関数,AND関数,OR関数)
先頭の数値、最後の数値を取り出す
最後の空白(や指定文字)以降の文字を取り出す
SUMIFの間違いによるパフォーマンスの低下について


新着記事NEW ・・・新着記事一覧を見る

Variantの数値型と文字列型の比較|エクセル雑感(7月1日)
VBAのVariant型について|VBA技術解説(6月30日)
VBAのString型の最大文字数について|エクセル雑感(6月20日)
VBAで表やグラフをPowerPointへ貼り付ける|VBAサンプル集(6月19日)
アクティブシート以外の表示(Window)に関する設定|VBA技術解説(6月17日)
マクロ記録での色のマイナス数値について|エクセル雑感(6月16日)
ツイッター投稿用に文字数と特定文字で区切る|エクセル雑感(6月15日)
日付の謎:IsDateとCDate|エクセル雑感(6月14日)
IFステートメントの判定|エクセル雑感(6月13日)
インクリメンタルサーチの実装|ユーザーフォーム入門(6月12日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ