他ブックを参照できる関数、他ブックを参照できない関数
他のブックを参照する関数を入れた場合、
そのブックが開いていないとエラーになってしまう関数があります。
一方、ブックが開いていなくても、正しく結果取得できる関数があります。
どの関数が使えて、どの関数がつかえないのか・・・
他ブックを参照できる関数、他ブックを参照できない関数
代表的な関数を一つずつ確認してみましょう。
以下、○×で表記します。
○ ・・・ 正しく取得できている
× ・・・ エラー(#VALUE)
SUM ・・・ ○
これは問題ないです。
これも問題ありません。
まったく問題なし。
これも問題ないです。
INDEX ・・・ ○
いやー、結構大丈夫じゃないですか。
と思いきや・・・
ダメですね。
あれれ、という感じでしょうか。
では、データベース関数ならどうか。
DCOUNTA ・・・ ×
では、とっておきのINDIRECTは、
まあ、これはそんな感じでしょうかね。
むしろ、VLOOKUPが別格な感じになります。
SUMPRODUCT関数:後日追記
実際に検証しましたが、問題なく取得できています。
SUMIFやCOUNTIFが他ブックを取得できない代替えとしてSUMPRODUCT関数を使えるという事になります。
ただし、SUMPRODUCTを使った条件集計は、配列を理解する必要があり難解な数式になりがちです。
他ブックを参照できる関数、他ブックを参照できない関数のまとめ
関数名 | 結果 |
SUM | ○ |
IF | ○ |
COUNTA | ○ |
SUMIF | × |
COUNTIF | × |
VLOOKUP | ○ |
MATCH | ○ |
INDEX | ○ |
DSUM | × |
DCOUNTA | × |
INDIRECT | × |
SUMPRODUCT | ○ |
他のブックを参照したい関数としては、大体この辺りでしょうか。
全ての関数を確認まではしていませんが、
上記掲載以外の関数についても、参照できるものと出来ないものがあります。
もし使う必要があるなら、実際に試して確認してください。
テーブル構造化参照では他ブックは取得できません
=SUM('パス\Book1.xlsx'!テーブル1[列1])

他ブックのテーブルは参照しないようにしてください。
テーブルでも、直接セルアドレス(B2:B11等)を指定してください。
他ブックを参照する最も簡単な方法
つまり、
=セル番地
これなら、他のブックを問題なく参照できます。
データ範囲を全て、=セル番地、これでどこかのセルに取得しておけば良いです。
そして、そのセルを使って計算するようにしておけば問題ありません。
しかし、これは、あまりにも無駄ですし、
それなら、そもそも、別ブックである必要性がないですよね。
配列数式を使って、他ブックを参照する
SUMIF
COUNTIF
このあたりの関数については、出来たら他ブック参照したいと思う方も多いでしょう。
なんとか他の関数で出来ないか・・・
SUM ・・・ ○
IF ・・・ ○
この二つの関数で何とかならないかと考えたくなります。
これらの関数の組み合わせなら参照出来るんじゃないでしょうか。
これを
{=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で処理したほうが良いでしょう。
同じテーマ「エクセル関数応用」の記事
OFFSET関数 解説・応用・使用例
MATCH関数 解説・応用・使用例
選択行の色を変える(条件付き書式,Worksheet_SelectionChange)
他ブックを参照できる関数、他ブックを参照できない関数
時間計算で困ったときの確実な対処方法
VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
SUMIF関数の良くある間違い
論理式とは条件式とは(IF関数,AND関数,OR関数)
先頭の数値、最後の数値を取り出す
最後の空白(や指定文字)以降の文字を取り出す
SUMIFの間違いによるパフォーマンスの低下について
新着記事NEW ・・・新着記事一覧を見る
シートコピー後のアクティブシートは何か|ツイッター出題回答 (2023-09-19)
Excel関数の引数を省略した場合について|ツイッター出題回答 (2023-09-14)
セル個数を返すRange.CountLargeプロパティとは|VBA技術解説(2023-09-08)
記号を繰り返してグラフ作成(10単位で折り返す)|ツイッター出題回答 (2023-08-28)
シートを削除:不定数のシート名に対応|VBAサンプル集(2023-08-24)
ランクによりボイントを付ける(同順位はポイントを分割)|ツイッター出題回答 (2023-08-22)
OneDrive使用時のThisWorkbook.Pathの扱い方|VBA技術解説(2023-07-26)
列幅不足による###表示や指数表示を判定する|VBA技術解説(2023-07-12)
シートを削除:不定数のシート名に対応|VBAサンプル集(2023-07-04)
シート関数のCOUNTIFS,SUMIFS,MAXIFSと同じ処理|Power Query(M言語)入門(2023-02-28)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.繰り返し処理(For Next)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.マクロとは?VBAとは?VBAでできること|VBA入門
7.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
8.並べ替え(Sort)|VBA入門
9.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
10.条件分岐(IF)|VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。