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

Excel関数の解説、関数サンプルと高等テクニック
公開日:2014-04-21 最終更新日:2021-01-08

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


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


なぜかと言う理由は、作成者の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,... 省略可能です。
複数条件の合計・件数
・サンプルデータ ・複数条件の合計 ・複数条件の件数 ・スピルと新関数

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

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

関数名 結果
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関数 解説・応用・使用例

OFFSET関数は、検索ワードで最頻出のひとつです。他の関数とは、かなり異質に感じるのかもしれません。機能 基準のセルまたはセル範囲から指定された行数と列数だけシフトした位置にある高さと幅のセル範囲の参照を返します。
MATCH関数 解説・応用・使用例
MATCH関数は、検索ワードで最頻出のひとつです。非常に便利な関数です。少し込み入った事を関数でやろうとした時は、必ず必要になる関数です 機能 セルの範囲内で指定された項目を検索し、その項目の相対的な位置を返します。
選択行の色を変える(条件付き書式,Worksheet_SelectionChange)
クリックまたはカーソルキーで選択セルを移動した場合に、当該行の色を変更して目立たせたせる方法で、条件付き書式と、シートのイベントであるWorksheet_SelectionChangeを使用します。Worksheet_SelectionChangeイベントのみでやろうとすると、直前の選択行の色を元に戻す必要がある為、
他ブックを参照できる関数、他ブックを参照できない関数
時間計算で困ったときの確実な対処方法
・日付・時刻のシリアル値とは ・Excelにおける小数の問題 ・どんな時に問題が発生するか ・確実な時間計算方法 ・TIME関数の制限について ・単純化した結論
VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
・VLOOKUP関数 ・キー列より左側の列を取得したい ・MATCH関数 ・INDEX関数 ・OFFSET関数 ・MATCH関数とINDEX関数を使う ・MATCH関数とOFFSET関数を使う ・キー列より左側の列を取得のまとめ ・配列を使いVLOOKUPでキー列より左側の列を取得
SUMIF関数の良くある間違い
エクセルの関数の中で最も頻繁に使われる関数と言っても過言ではないSUMIF関数ですが、間違った指定をして、合計が合わずに悩み続けて時間を浪費してしまうことあります、そういう間違いで最も多いのが、範囲と合計範囲の指定間違いです。まずは、SUMIF関数の確認 SUMIF関数 範囲の中で、指定した条件を満たすセルの値を合…
論理式とは条件式とは(IF関数,AND関数,OR関数)
エクセルを使いこなす上で論理式はとても重要です、そもそも論理式とは何か、どうして論理式というのか、論理式の作り方、論理式の使い方について解説します。そもそも論理式という言い方が分かりずらいと思う。なぜエクセルでは論理式というのか… Microsoftのヘルプによると、IF関数 構文:IF(logical_test,
先頭の数値、最後の数値を取り出す
数値と文字が混在した文字列から、数値だけを取り出します、先頭の数値や、最後の数値だけを取り出す方法です。A1セルに 1234abcd5678 このA1セルから、1234や5678を取り出します。先頭の数値…1234 =LOOKUP(10^17,LEFT(A1,COLUMN($1:$1))*1) COLUMN($1:…
最後の空白(や指定文字)以降の文字を取り出す
いくつかのスペースやハイフンで区切られた文字列から、最後のスペースやハイフン以降の文字列を取り出します。A1セルに、abcdefghi や abc-def-ghi これらの文字列から、ghiを取り出します。以下では、見やすいように区切り文字は"-"で説明します。
SUMIFの間違いによるパフォーマンスの低下について
再計算が終わらない… そんな経験をした人は多いと思います、原因はさまざまですが、まずは数式を見直してみましょう。単純な四則演算が遅いという事はありません、それはもうPCの問題です。時間のかかる計算としては、大量データの集計計算を多数使っている場合です。


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

VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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