Excel（エクセル）で別のシート・別のファイル（ブック）の情報や数字を参照する方法

workstation IT
2018.10.28

Excel使う上で、1つのファイル（ブック）やシートだけでなく、他のページを参照したい場合もあると思います。使っていく上で知ると知らないでは大違いですので、ここではその方法をご紹介しています。

スポンサーリンク

他のシートを参照する方法

別のシートを参照する
シート名!対象
sans1
まずは参照するためのシートを作り、そこに計算するためのデータを入れておきます。
sans2
こっちのシートでデータベースを参照し、会員の人数と平均年齢を算出してみたいと思います。
sans4
入力されたデータの数を数える（空白のセルは無視される）には、CountまたはCountA関数を使います。のちの発展を願ってA2:A1000を対象にしたものの、このままだとSheet1のA2～A1000を参照してしまうので、
sans3
頭にシートの名前と!を付けて、=CountA(参照シート!A2:A1000)と入力します。
sans5
ワークシート「参照シート」のA2～A1000が計算され、現在の会員数が表示されました。
sans6
平均も同様です。年齢が入力されているのは参照シートのC列なので、=AVERAGE(参照シート!C2:C1000)のように入力します。
sans7
参照シートのデータを使って、平均年齢を求めることができました。
sans8
今度は「参照シート」の名称を「先月」に変更、あらたに「先々月」というシートを作り、「先々月」に「先月」のデータをコピーしておきます。
sans9
さらに「先月」シートにひとり会員を追加します。
sans11
この状態でSheet1に戻ると、式の中で参照しているシート名は自動的に修正されています。
sans14
先月の会員数-先々月の会員数を計算して、先月増えた会員数を計算してみましょう。
sans12
式としては=CountA(先月!A2:A1000)-CountA(先々月!A2:A1000)のようになります。
sans13
先々月と比較し、先月増えた会員数が表示されました。

※複数のシートの同じセルをまとめて計算する（くし刺し集計する）場合は、シートが連続していれば=sum(Sheet2:Sheet5!A1)のような記述も可能です。

スポンサーリンク

他のブックを参照する方法

上のシートの参照に加えて、ブック名を最初に[]で囲って入れることで、他のexcelシートを参照することが可能です。

別のブックを参照する
[ブック名.拡張子]シート名!セル名
※同じ階層（同じフォルダ内）に置いてある場合のみ
sans15
今度は別にデータベースをファイルとして作り、データを入力しておきます。ファイル名は「参照ブック」としました。
sans16
「参照ブック」のデータを、さっきのブックから参照したいと思います。設定的には「参照ブック」が全国のデータ、Book1は支店データです。
このふたつのファイルは同じディレクトリ（フォルダ）に置いておきます。
sans17
別のブック（ファイル）のデータを参照する場合は、まず[]で囲んだブック名を書き、
sans18
その後に参照するシート名!対象と続けます。今回は会員数を数えるので、=CountA([参照ブック.xlsx]参照シート!A2:A1000)となります。
sans19
無事全国の会員数を求めることができました（支店出してる場合ではない気もします）。
sans20
せっかくなので、当店の会員数/全国の会員数で、全体に対する当店の会員数比率も求めてみます。
sans21
書式を%表示にすることで、全体に対する比率を求めることができました。

リンクの自動更新について

sans22
参照先のブックが開かれていない状態でリンクを貼ったブックを起動すると、「リンクの自動更新が無効にされました」と警告が表示されます。
sans23
オプションをクリックするとダイアログが開きます。
他のブックに対してリンクが貼られているが、この内容を自動的に更新（反映）してもよいか、という内容です。
セキュリティ的に問題があるので信頼できないファイルの場合は「不明なコンテンツから保護する」のままにしておき、確認したほうが無難です。
sans24
今回のように自分で作った場合はもちろん、特に問題ないと判断できる場合は、「このコンテンツを有効にする」にチェックをつけてOKを押します。

2010以降のバージョンでは、コンテンツの有効化をクリックすればリンクが更新されます。

別階層のブックを参照する場合

別のブックを参照する
‘パス\[ブック名.拡張子]シート名!対象
（例)=SUM(‘C\Users\You\Desktop\[参照ファイル.xlsx]参照シート!A1:C1)
sans25
ところで、さきほど[ブック名.拡張子]シート名!セル名という形式で入力した参照先は、データを更新すると自動的に絶対パスの位置表示に内容が変更されます。
=COUNTA('C:\Users\User\Desktop\[参照ブック.xlsx]参照シート'!A2:A1000)
絶対パスというのは、コンピュータ上の位置が頭から順番に記載されている住所表記のような状態で、対して「そのファイルがある場所」からどう行ったかを記述したものが相対パスと呼ばれます。
よって、自分のパソコンで特定のフォルダのデータベースを参照できる関数が、他のパソコンでもうまく動くとは限らなかったりします。
場合によってはブックを直接参照せず、適宜データをコピーしたほうが良いかもしれません。

他のフォルダに置いたデータを参照したい場合、上記のような形でパスを指定すれば可能です。

Excel（エクセル）：ワークシート
ワークシート関連の操作方法まとめです。 ワークシートの使い方 ワークシートの作成・削除・挿入など、基本操作まとめです。 ワークシートのコピーについてです。 ブック間でやりとり 特定のシートを別の...
sunagitsune.com
2021.02.16
関数・演算子・メソッド・プロパティ名から探すExcel/VBA（マクロ）使い方・組み合わせ方まとめ
こちらはExcelやメソッドの諸機能を、機能の名称から探せるまとめ記事です。
sunagitsune.com
2018.12.30
スポンサーリンク

コメント