エクセル違うシートからデータを引っ張る方法は？参照式の作り方を解説（シート名：セル指定：リンク：集計など）

エクセル違うシートからデータを引っ張る方法は？参照式の作り方を解説（シート名：セル指定：リンク：集計など）

Excelでは、入力用のシート、商品マスタのシート、月別集計のシートなど、用途ごとにデータを分けて管理する場面が少なくありません。

別シートに入力されている数値や文字列を参照できれば、同じ情報を何度も入力する手間を抑えられ、修正漏れも防ぎやすくなります。

一方で、シート名の指定方法、絶対参照と相対参照、空白を含むシート名、複数シートの集計などでつまずくケースも多いでしょう。

この記事では、違うシートからデータを引っ張る基本の参照式から、リンク、検索、集計、エラー対策までを順番に解説します。

別シート参照の基本ルール

それではまず、Excelで違うシートのデータを引っ張るときに押さえたい基本ルールについて解説していきます。

シート名とセル番地による参照式

別のシートにあるセルを参照する基本形は、シート名の後に半角の感嘆符を付け、セル番地を書く形式です。

この数式は、売上データという名前のシートにあるB5セルの内容を、数式を入力したセルへ表示します。

参照先が数値なら数値として表示され、文字列なら文字列として表示されます。

日付、時刻、パーセントなども、基本的には参照先の値を読み取る仕組みです。

数式バーに直接入力してもよいですが、実務では参照元のセルをクリックして式を作る方法が便利でしょう。

クリック操作で作る参照リンク

数式を手入力すると、シート名やセル番地の入力ミスが起きることがあります。

そこでおすすめなのが、イコールを入力した後に参照先シートを開き、目的のセルをクリックする方法です。

たとえば集計シートのC4セルに、商品マスタのD2セルを表示したい場合は、最初に集計シートのC4セルを選択します。

次にイコールを入力し、画面下部のシート見出しから商品マスタを選び、D2セルをクリックしてEnterキーを押します。

Excelが自動で正しい参照式を作成するため、初めて別シート参照を設定する場合にも扱いやすい方法です。

参照式を作った後に参照先の内容を変更すると、通常は参照元の表示も自動で更新されます。

値のコピーと参照の違い

コピーした値を貼り付ける操作と、参照式でデータを引っ張る操作は似ていますが、役割は異なります。

値として貼り付けた場合、その時点の内容だけが貼り付けられ、元データを更新しても貼り付け先は変化しません。

一方、参照式でつながっているセルは、参照元の変更に合わせて結果も変わります。

方法	元データ変更後	向いている場面
値の貼り付け	自動更新されない	提出用の確定資料、バックアップ
通常のコピー	数式の内容による	同じ計算式を横展開する作業
別シート参照	自動更新される	一覧表、報告書、集計表

最新の情報を常に反映したいなら、値の貼り付けではなく参照式を選ぶことが重要です。

シート名とセル指定の書き方

続いては、シート名やセル番地を指定する際の書き方を確認していきます。

空白や記号を含むシート名

シート名に空白が入っている場合や、日本語の記号を含んでいる場合は、シート名を単一引用符で囲みます。

四月 売上というシートのC8セルを参照する式です。

シート名に空白がない場合でも、単一引用符を付けておくと式の見た目をそろえやすくなります。

特に、後からシート名に空白を追加する可能性があるブックでは、引用符付きの表記を意識すると修正作業を減らせるでしょう。

ただし、Excelでセルをクリックして参照式を作成すれば、必要な引用符は自動で付けられます。

相対参照と絶対参照の使い分け

参照式を下方向や横方向へコピーすると、セル番地が自動でずれることがあります。

この仕組みを相対参照と呼びます。

たとえば、=商品マスタ!B2 を下へコピーすると、通常は =商品マスタ!B3、=商品マスタ!B4 のように変化します。

行ごとに対応したデータを取得したい一覧表では、相対参照が役立ちます。

一方で、いつでも同じセルを参照したい場合には、列番号と行番号の前にドル記号を付ける絶対参照を使います。

この式をコピーしても、参照先は常に設定シートのB2セルのままです。

消費税率、基準日、目標値、会社名など、固定の情報を参照する場面で便利です。

範囲参照と複数セルの指定

別シートのデータを引っ張る対象は、1セルだけとは限りません。

合計、平均、最大値などを計算する場合は、参照先の範囲を指定します。

たとえば一月から三月までの売上が売上データシートのB2からB4に入力されているなら、次のように合計を求められます。

参照式の中では、シート名の後に感嘆符を一度書き、その後に開始セルと終了セルを指定します。

大量のデータを扱う場合、範囲を必要以上に広くするとファイルが重くなることがあります。

集計対象が明確な場合は、使用中の行だけを指定するほうが管理しやすいでしょう。

別シートからデータを探す関数

続いては、表の中から条件に合うデータを引っ張る関数について確認していきます。

XLOOKUPによる検索参照

商品コードや社員番号を入力すると、別シートから商品名や単価を表示したいことがあります。

このような場面ではXLOOKUP関数が便利です。

検索する値、検索対象の範囲、返したい範囲を順番に指定します。

たとえば、入力シートのA2に商品コードを入れ、商品マスタシートのA列からコードを探し、B列の商品名を返す場合は次の形になります。

最後の該当なしは、見つからなかったときに表示する文字です。

エラーをそのまま見せず、利用者に分かる文言を表示できる点も大きな利点です。

Microsoft 365など比較的新しいExcelで利用しやすい関数ですが、古いバージョンでは使えない場合があります。

VLOOKUPによる縦方向検索

VLOOKUP関数は、表の左端の列で検索し、右側にある列の値を返す定番の関数です。

利用しているExcelの環境によっては、XLOOKUPよりVLOOKUPのほうが使いやすい場合もあります。

基本的な式の形は、検索値、表範囲、取り出す列番号、検索方法の順です。

この例では、商品マスタシートのA列でA2の値を探し、範囲内の2列目にある値を返します。

完全一致を求めるときは、最後の引数にFALSEを設定することが重要です。

省略すると近似一致になる場合があり、商品コードや顧客番号の照合では意図しない結果になるおそれがあります。

検索する列を表範囲の先頭に置く必要がある点は、VLOOKUPを使う際の注意点です。

INDEX関数とMATCH関数の組み合わせ

検索列の位置に左右されず、古いExcelでも使いやすい方法として、INDEX関数とMATCH関数の組み合わせがあります。

MATCH関数で該当データの位置を調べ、INDEX関数でその位置にある値を返す考え方です。

この式では、A2の商品コードを商品マスタシートのA列で完全一致検索し、同じ行のB列にある商品名を取得します。

式は少し長く見えますが、列の追加で参照列番号が変わる問題を避けやすい特徴があります。

検索用の表を将来変更する可能性が高いなら、列番号ではなく列そのものを指定できる方法を選ぶと安心です。

複数シートの集計方法

続いては、月別や拠点別に分かれた複数シートを集計する方法を確認していきます。

SUM関数による個別シートの合計

一月、二月、三月という複数のシートに売上が分かれている場合、それぞれの同じセルを加算できます。

たとえば各シートのB10セルに月間売上が入力されているなら、集計シートで個別に足し合わせます。

参照するシート数が少ない場合には、式の内容が分かりやすく、確認もしやすい方法です。

ただし、月が増えるたびに式を修正する必要があります。

シートの追加忘れを防ぐため、集計表を更新する運用ルールも用意しておくとよいでしょう。

連続したシートの三次元集計

同じレイアウトのシートが連続して並んでいる場合は、三次元参照を利用できます。

開始シートから終了シートまでの同じセルをまとめて集計できるため、月次資料などで便利です。

この式では、一月シートから三月シートまでにあるB10セルを合計します。

途中に集計対象ではないシートが入ると、そのシートのB10セルも合計されるため注意が必要です。

対象外シートを間に挿入しない並び順を保つことが、三次元参照を安全に使うポイントです。

SUMIFS関数による条件付き集計

別シートのデータから、特定の担当者、商品、期間だけを合計したい場合はSUMIFS関数が向いています。

たとえば売上データシートで、担当者名がA列、売上金額がD列に入っている場合を考えます。

この式は、集計シートのA2に入力された担当者名と一致する行だけを探し、売上データシートのD列を合計します。

複数の条件を追加すれば、担当者と月、商品分類と地域といった細かな分析も可能です。

元データの表記ゆれがあると正しく集計できないため、氏名やコードの入力規則を整えることが大切です。

検索や集計の精度は、参照式だけでなく元データの整備にも左右されます。

参照式のエラー対策

続いては、別シート参照で表示されやすいエラーと、確認したい対処方法を確認していきます。

参照先削除によるエラー

参照していたセル、行、列、またはシートを削除すると、数式に参照エラーが表示されることがあります。

代表的なのが、参照先を見つけられないことを示すエラーです。

この場合は、数式バーを確認し、削除されたシート名やセル番地がないかを調べます。

誤って削除した直後であれば、操作の取り消しで戻せる場合があります。

重要なマスタシートを扱うときは、削除前にバックアップを作るか、シート保護を設定すると安心です。

ファイル名とシート名の変更

同じブック内のシート名を変更した場合、通常はExcelが数式内の参照先を自動で更新します。

しかし、別ファイルを参照している場合や、文字列として数式を組み立てている場合は、リンク切れが起きることがあります。

外部ファイルへのリンクでは、参照元ファイルの保存場所が変わっただけでも更新できなくなるケースがあります。

共有フォルダの移動、ファイル名の変更、クラウド上の同期先変更を行う際は注意が必要です。

社内共有で使うExcelは、保存先のルールを先に決めておくことがリンクの安定につながります。

リンクの更新を求めるメッセージが出た場合は、参照先が正しいファイルかを確認してから更新しましょう。

空白セルと文字列の扱い

参照先が空白の場合、参照式の結果は空白に見える場合もあれば、計算式の中でゼロとして扱われる場合もあります。

帳票上で空欄のままにしたいときは、IF関数を組み合わせる方法があります。

この式は、入力シートのB2が空白なら空白を表示し、値があればその値を表示します。

また、数字に見えるデータが文字列として保存されていると、合計や検索が期待どおりに動かないことがあります。

セルの表示形式だけでなく、数式バーで実際の内容を確認する習慣が役立ちます。

見た目が同じでも、数値と文字列はExcelでは別のデータという点を覚えておくと、原因を探しやすくなるでしょう。

別シート参照のまとめ

それでは最後に、違うシートからデータを引っ張る方法を整理していきます。

基本となる参照式は、シート名、感嘆符、セル番地を組み合わせる形です。

単純に1セルを表示するなら =シート名!セル番地 を使い、同じ参照式をコピーする際は相対参照と絶対参照を意識するとよいでしょう。

商品コードや社員番号から情報を取得するなら、XLOOKUP関数、VLOOKUP関数、INDEX関数とMATCH関数の組み合わせが候補になります。

月別シートなどをまとめるときは、SUM関数、三次元参照、SUMIFS関数を目的に合わせて選ぶことが大切です。

参照式は、入力の重複を減らし、表の更新を自動化するための基本機能です。

まずはクリック操作で1つの別シート参照を作り、数式バーで仕組みを確認するところから始めてみてください。

参照先のシート名、セル番地、データ形式を丁寧に確認すれば、リンク切れや集計ミスも防ぎやすくなります。