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

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

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


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


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

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

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

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

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

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

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

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

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

と思いきや・・・

SUMIF ・・・ ×
ダメですね。

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

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

DSUM ・・・ ×
DCOUNTA ・・・ ×

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

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

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

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

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

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

他のブックを参照したい関数としては、大体この辺りでしょうか。
全ての関数を確認まではしていませんが、
上記掲載以外の関数は、全て参照できないとお考え下さい。

他ブックを参照する最も簡単な方法

そもそも、単一のセル参照なら問題ありません。

つまり、
=セル番地
これなら、他のブックを問題なく参照できます。

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

配列数式を使って、他ブックを参照する

上記に説明した関数のうち、
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 ・・・新着記事一覧を見る

Byte配列と文字コード関数について|VBA技術解説(8月20日)
PowerQueryの強力な機能をVBAから利用する方法|VBA技術解説(8月4日)
練習問題31(セル結合を解除して値を埋める)|VBA練習問題(7月30日)
練習問題30(マトリックス→リスト形式)|VBA練習問題(7月25日)
Applicationを省略できるApplicationのメソッド・プロパティ一覧|VBA技術解説(7月22日)
コレクション(Collection)の並べ替え(Sort)に対応するクラス|VBA技術解説(7月20日)
CSVの読み込み方法(ジャグ配列)|VBAサンプル集(7月15日)
その他のExcel機能(グループ化、重複の削除、オートフィル等)|VBA入門(7月14日)
オートフィルタ退避回復クラスを複数シート対応させるVBAクラス|VBA技術解説(7月6日)
オートフィルタを退避回復するVBAクラス|VBA技術解説(7月6日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.繰り返し処理(For Next)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.ひらがな⇔カタカナの変換|エクセル基本操作
10.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説



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

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


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




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