【Excel】エクセルで別シートのデータを参照・反映させる方法（数式・VLOOKUP・マクロ・条件）

Excelで集計表、入力用シート、商品マスターなどを分けて管理していると、別シートの値を自動で参照したい場面が増えます。

手入力で転記すると更新漏れや入力ミスが起こりやすいため、数式、検索関数、VBAを使い分けて連動させることが大切です。

この記事では、1行目に見出しがあるサンプルデータを使い、別シートのデータを参照して反映する実践的な方法を解説します。

別シート参照の基本数式

それではまず、別シートのセルを直接参照して値を反映する基本の数式について解説していきます。

別シート参照では、参照元のシート名、感嘆符、セル番地をつないで指定します。

同じブック内のセル参照

最も単純な方法は、反映先のセルに別シートのセル番地を入力する方法です。

たとえば集計シートのB2に、売上入力シートのB2を表示するなら、数式バーへ=売上入力!B2と入力します。

シート名に空白がない場合は、この形でそのまま利用できます。

数式を確定すると、集計シート側には売上入力シートのB2の値が表示されます。

参照元の値を12500から15000へ変更すれば、参照先も再計算されて15000へ変わります。

これは値をコピーする処理ではなく、常に参照元を見るリンクである点が重要です。

空白を含むシート名の指定

シート名に空白や記号が入っている場合は、シート名全体をシングルクォーテーションで囲みます。

シート名の前後にシングルクォーテーションを付けると、Excelがシート名の範囲を正しく判断できます。

「東京 支店」のように空白を含む名前でも、=’東京 支店’!C5と書けば参照可能です。

数式を手入力する代わりに、反映先セルで「=」を入力してから参照元シートをクリックし、目的のセルを選択しても構いません。

この操作ならExcelがシート名とセル番地を自動入力するため、入力ミスの防止につながります。

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

数式を下方向や右方向へコピーする場合は、セル番地が移動する相対参照と、固定される絶対参照を区別します。

たとえば集計シートのB2に=売上入力!B2と入力して下へコピーすると、B3では=売上入力!B3になります。

一方で、税率のように売上入力シートのF2だけを常に参照したい場合は、=売上入力!$F$2のようにドル記号を付けます。

複数行の明細を反映するときは相対参照、共通設定値を掛け合わせるときは絶対参照が向いています。

複数セルと範囲の反映方法

続いては、複数のデータを別シートからまとめて反映する方法を確認していきます。

範囲を意識して参照すると、毎月の明細作成や日次集計の作業を短縮できます。

数式のコピーによる連続反映

明細表で行ごとに同じ列を参照したいときは、最初のセルに数式を入力し、フィルハンドルでコピーします。

集計シートのB2に=売上入力!B2を入力し、セル右下の小さな四角を下へドラッグします。

参照先はB3、B4へと変化するため、対応する行のデータを連続して表示できます。

表の途中に空白行がある場合でも、数式自体はコピーされます。

ただし、参照元と参照先で行順が異なる場合は、単純なセル参照では正しいデータを取得できません。

そのような場合は、後述するVLOOKUPやXLOOKUPで商品コードなどを照合します。

同じ位置の表をまとめて参照する方法

月別シートと年間集計シートで表の配置が同じなら、必要なセルへ参照式を展開する方法が便利です。

たとえば売上入力シートのB2からE10を集計シートへ対応表示する場合、集計シートのB2に=売上入力!B2を入力して、右と下へコピーします。

列も行も相対参照のままなので、B2はB2、C2はC2、B3はB3を参照する仕組みです。

元の表と同じレイアウトを作る用途では、数式コピーが最も分かりやすい選択です。

書式まで自動で同期するわけではないため、罫線、表示形式、条件付き書式は反映先で別途設定します。

外部ブック参照とエラー表示

別のExcelファイルのデータを参照することも可能です。

参照元ブックを開いた状態で「=」を入力し、別ブックのセルをクリックすると、[売上.xlsx]入力!B2のような数式が作成されます。

参照元ブックを閉じると、数式には保存場所を含むパスが追加されることがあります。

ファイル名の変更、保存場所の移動、ネットワークドライブの切断は外部参照エラーにつながる要因です。

参照先で#REF!が出た場合は、参照元のシートや列が削除されていないかを調べましょう。

更新確認のメッセージが出る外部リンクは、信頼できるファイルかを確認してから更新する姿勢も必要です。

VLOOKUPとXLOOKUPによる検索参照

続いては、商品コードや社員番号などのキーを基準に、別シートから必要な項目を取得する検索参照を確認していきます。

行の順番が違っていても、検索関数ならキーが一致するデータを探して表示できます。

VLOOKUPの基本構文

VLOOKUPは、範囲の左端列で検索した値を見つけ、指定した列番号の値を返す関数です。

注文入力シートのB2にこの数式を入れると、A2の商品コードに対応する商品名が商品マスターシートから返されます。

第1引数のA2は検索値、第2引数は検索範囲、第3引数の2は範囲内で返す列、第4引数のFALSEは完全一致を意味します。

商品コード、社員番号、部門コードのように重複しない識別子を検索値にすることが正確な参照の基本です。

XLOOKUPによる柔軟なデータ取得

Microsoft 365やExcel 2021以降では、XLOOKUPを使うと検索列と戻り列を別々に指定できます。

この数式は、注文入力シートのA2を商品マスターのA列で探し、該当した行のB列にある商品名を返します。

XLOOKUPは検索列が左端にない場合にも使えるため、VLOOKUPより表の設計に左右されにくい関数です。

4番目の引数に「該当なし」を指定しておくと、見つからないときに#N/Aではなく分かりやすい文字を表示できます。

検索結果をオートフィルで反映する方法

注文入力シートのB2へ検索式を入力したら、セル右下のフィルハンドルを最終行までダブルクリックします。

A列に商品コードが連続している場合、Excelはデータの最終行まで数式をコピーします。

単価をC列へ表示する式では、VLOOKUPの列番号を3に変えるか、XLOOKUPの戻り範囲を商品マスター!$C$2:$C$100にします。

1行目がヘッダーなら、検索範囲は通常2行目から開始すると見出しを検索対象に含めずに済みます。

検索値の前後に余計な空白があると一致しないことがあるため、必要ならTRIM関数で空白を除去します。

条件付きでデータを表示する数式

続いては、条件を満たすときだけ別シートのデータを表示する方法を確認していきます。

IF関数を組み合わせれば、未入力や未承認のデータを空白にし、見やすい帳票を作れます。

IF関数で空白時の表示を制御する方法

参照元が空白のときに0を表示したくない場合は、IF関数で判定します。

入力シートのB2が空白なら反映先も空白となり、値があるときだけその値を表示します。

未入力セルをそのまま参照すると0が表示されるケースでは、IF関数による空白制御が有効です。

日報、申請書、請求書のように未入力行を見せたくない帳票で役立ちます。

承認状態による参照の切り替え

ステータスが「承認済み」の場合だけ金額を表示するには、条件式に文字列比較を使います。

この数式では、入力シートのC2が承認済みならD2の金額を表示し、それ以外は空白です。

条件に使う文字は、入力規則のリストで統一すると表記ゆれによる判定漏れを防げます。

「承認済」「承認済み」「承認完了」が混在すると、見た目が似ていてもExcelでは別の文字列です。

IFERRORを使った検索エラーの処理

VLOOKUPやXLOOKUPで該当データがないと、#N/Aなどのエラーが表示されます。

利用者向けの帳票では、IFERROR関数で表示を整えると分かりやすくなります。

検索できたときは商品名を表示し、エラーになったときだけ未登録と表示する数式です。

IFERRORは便利ですが、参照範囲の誤りまで隠してしまうため、作成直後はエラー原因も必ず確認します。

VBAによる別シート転記の自動化

続いては、数式ではなくVBAで別シートのデータを転記し、処理を自動化する方法を確認していきます。

確定時だけ値を保存したい場合や、複数の整形処理をまとめたい場合はVBAが適しています。

値をそのまま転記する基本マクロ

次のマクロは、入力シートのA2:D100を集計シートのA2へ値としてコピーします。

Sub 別シートへ転記()
    Worksheets("入力").Range("A2:D100").Copy
    Worksheets("集計").Range("A2").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Copyで範囲をコピーし、PasteSpecialのxlPasteValuesで数式や書式ではなく値だけを貼り付けます。

転記先の既存データを上書きする可能性があるため、実行前に対象範囲を確認しましょう。

最終行までを自動判定する方法

データ件数が毎回変わる場合、固定のA2:D100では余分な空白まで転記することがあります。

そこで、A列の最終行を取得して転記範囲を可変にします。

Sub 最終行まで転記()
    Dim lastRow As Long
    lastRow = Worksheets("入力").Cells(Rows.Count, "A").End(xlUp).Row
    Worksheets("入力").Range("A2:D" & lastRow).Copy
    Worksheets("集計").Range("A2").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

1行目がヘッダーであれば、データ開始位置をA2に固定し、最終行だけを自動で取得する設計が分かりやすいでしょう。

転記前のデータ消去と安全な運用

前回の転記結果が残ると、今回より件数が少ない場合に古い行が残ってしまいます。

転記前に集計シートの既存値を消去する場合は、見出しを残してA2:D1000などの対象範囲をClearContentsで空にします。

ただし、数式や入力欄まで消さないよう、消去範囲は慎重に決める必要があります。

まとめ エクセルで別シートのデータを参照・反映させる方法

エクセルで別シートのデータを参照・反映させるには、目的に合わせて方法を選ぶことが重要です。

同じセル位置の値を連動させるなら=シート名!セル番地、コードを基準に商品名や単価を取り出すならVLOOKUPまたはXLOOKUPが向いています。

未入力時の表示を整える場合はIF関数、検索エラーを見やすくする場合はIFERROR関数を組み合わせましょう。

毎回同じ転記作業を繰り返す場合は、VBAによる値貼り付けの自動化も有効です。

参照元のシート名、検索キー、絶対参照の範囲を最初に整えておくと、数式のコピーや更新が格段に管理しやすくなります。

まずは直接参照とXLOOKUPから試し、業務に合った連動表を作成していきましょう。