エクセル関数超技 | 他ブックを参照できる関数、他ブックを参照できない関数 | Excel関数の解説、関数サンプルと高等テクニック



最終更新日:2014-04-21

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


他のブックを参照する関数を入れた場合、

そのブックが開いていないとエラーになってしまう関数があります、

一方、ブックが開いていなくても、正しく結果取得できる関数があります。

なぜかと言う理由は、作成者の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.xls]Sheet1!$A:$A,A1,[Book1.xls]Sheet1!$B:$B)

{=SUM(IF([Book1.xls]Sheet1!$A:$A=A1,[Book1.xls]Sheet1!$B:$B))}

このようになります。

ほぼ同様ですが、

=COUNTIF(範囲,検索条件,合計範囲)

これを

{=SUM(IF(範囲=検索条件,1)}

つまり、条件に一致したら1にして、それを集計すれば件数になります。

一応、例としては、

自身のA1セルを条件として、Book1のA列を条件範囲として件数を数える場合、

=COUNTIF([Book1.xls]Sheet1!$A:$A,A1)

=SUM(IF([Book1.xls]Sheet1!$A:$A=A1,1))

※範囲指定について

上記では、$A:$A、このように列全体として書いています。
参照しているブックが閉じている状態では、$A$1:$A$65536、このように行範囲が限定されて表示されます。
また、
配列関数ですので、列全体を指定すると、データ量によっては膨大な再計算時間がかかります。
ここでは、記述を簡略化するために、列全体としましたが、
配列関数を使う場合は、行数を限定するようにしたほうがパフォーマンスが良くなります。


ただし、

そもそも論として、他のブックを参照することは、あまり望ましいことではありません。

そのような必要性がある場合は、マクロ(VBA)で処理するようにして下さい。





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

時間計算で困ったときの確実な対処方法
VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
SUMIF関数の良くある間違い
論理式とは条件式とは(IF関数,AND関数,OR関数)
先頭の数値、最後の数値を取り出す
最後の空白(や指定文字)以降の文字を取り出す
SUMIFの間違いによるパフォーマンスの低下について

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

SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)

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

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
7.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
8.ひらがな⇔カタカナの変換|エクセル基本操作
9.定数と型宣言文字(Const)|ExcelマクロVBA入門
10.CSVの読み込み方法|ExcelマクロVBAサンプル集



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

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


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

    ↑ PAGE TOP