【Excel】外部ワークブックの値で条件付き書式をトリガーする3つの方法を徹底解説

別のワークブックに保存されたデータをもとに、セルを自動的に書式設定できるのが「外部ワークブックの値による条件付き書式」です。複数ファイルにまたがる動的なレポートやダッシュボード、データ比較を作成するうえで欠かせない機能であり、ビジネスの現場で非常に重宝します。

本記事では、外部ワークブックの値をトリガーにして条件付き書式を設定する具体的な方法を解説します。

たとえば、実績の四半期売上を1つのファイルで管理し、四半期ごとの売上目標を別のファイルで管理しているとします。実績シート側で「目標を下回った売上」を自動的に強調表示したい場合、外部ファイルから正しい目標値を取得する必要があります。

方法1:外部参照を使った作業用列(ヘルパー列)

これはすべてのExcelバージョンで動作する、最も確実な方法です。外部参照を含む数式を作業用列に入力し、その列の値をもとに条件付き書式を適用します。

ステップ1:ワークブックを準備する

まず、サンプルデータを使って両方のブックを作成・保存します。

• 「Sales Target.xlsx」を作成し、目標データを入力します。
• デスクトップまたは任意のフォルダーに保存します。
• 「Actual Sales.xlsx」を作成し、実績売上データを入力します。
• 同じ場所に保存します。

ステップ2:外部参照を使った作業用列を作成する

• 「Actual Sales.xlsx」に作業用列を追加します(G列以降を使用します)。
• セルG2を選択し、次の数式を入力します。

=[SalesTarget.xlsx]Quarterly_Targets!B2

• 数式を右方向へオートフィルし、H2、I2、J2にもコピーします。

• 値を更新する場合は、「Sales Target.xlsx」ファイルを選択します。

• セルG2:J2を選択します。
• 数式を下方向へドラッグし、残りのセルにもコピーします。

ステップ3:作業用列を使って条件付き書式を適用する

ここからは、内部参照だけで条件付き書式を設定できます。

• セル範囲(B2:B6)を選択します。
• ホームタブ >> 条件付き書式 >> 新しいルールを選択します。
• 「数式を使用して、書式設定するセルを決定する」を選びます。
• 次の数式を入力します。

=B2<G2

• 書式をクリック >> 薄い赤の塗りつぶし色を選択します。
• OKをクリックします。

ルールを追加する:

必要に応じて、各四半期ごとに同じ操作を繰り返します。

第2四半期:

=C2<H2

• 書式をクリック >> 薄い青の塗りつぶし色を選択します。
• OKをクリックします。

第3四半期:

=D2<I2

• 書式をクリック >> 薄い緑の塗りつぶし色を選択します。
• OKをクリックします。

第4四半期:

=E2<J2

• 書式をクリック >> 薄い紫の塗りつぶし色を選択します。
• OKをクリックします。

ステップ4:作業用列を非表示にする(任意)

• G~J列を選択します。
• 右クリック >> 非表示を選択します。

これで、外部ワークブックの値に基づく条件付き書式が表示されるようになります。Excelは内部的に作業用列を利用することで、外部参照の制限を回避しています。

方法2:Power Queryを使ったソリューション

Excel 365またはExcel 2016以降をお使いの方には、Power Queryが堅牢な解決策になります。

ステップ1:Power Queryで外部データを取り込む

• 「Actual Sales.xlsx」ブックを開きます。
• データタブ >> データの取得 >> ファイルから >> ブックからを選択します。
• 「Sales Target.xlsx」ファイルを参照して選択します。
• 「Quarterly_Targets」テーブルを選びます。
• インポートをクリックします。

• ナビゲーターウィンドウでデータシートを選択します。
• データの変換をクリックします。

• Power Queryエディターで以下の操作を行います。
  • 列名を目的に合わせて変更します(Target_Q1、Target_Q2など)。
  • ホームタブ >> 閉じて次に読み込むを選択します。

• テーブルを選択 >> 新しいワークシートを選びます。
• OKをクリックします。

ステップ2:条件付き書式を適用する

取り込んだデータに対して、方法1と同じ標準的な条件付き書式を適用します。参照先はすべて内部データのみです。

• セル範囲(B2:B6)を選択します。
• ホームタブ >> 条件付き書式 >> 新しいルールを選択します。
• 「数式を使用して、書式設定するセルを決定する」を選びます。
• 次の数式を入力します。

=B2<Quarterly_Targets!$B2

• 書式をクリック >> 薄い赤の塗りつぶし色を選択します。
• OKをクリックします。

• 残りの四半期についても同様にルールを追加します。

第2四半期:

=C2<Quarterly_Targets!$C2

第3四半期:

=D2<Quarterly_Targets!$D2

第4四半期:

=E2<Quarterly_Targets!$E2

• 目標値が変わったら、いつでもPower Queryを更新できます。
  • 右クリック >> 更新を選択します。
• データの変更頻度が高い場合は、自動更新をスケジュールすることも可能です。
• データタブ >> クエリと接続を選択します。
• クエリを右クリック >> プロパティを選択します。

• 更新間隔に5分を指定します。
• OKをクリックします。

この方法なら外部データが自動的に更新され、参照に関する制限も回避できます。

方法3:VBAマクロによる完全自動化

VBAに慣れている方は、外部データをもとに条件付き書式を更新するマクロを作成できます。実績と目標を自動的に比較し、参照元ファイルが閉じている状態でも書式を適用できる点が大きな魅力です。

VBAエディターを開く手順は以下のとおりです。

• 実績売上のブックを開きます。
• 開発タブ >> Visual Basicを選択します。またはAlt + F11キーを押します。
• プロジェクトウィンドウで対象のブックを右クリックします。
• 挿入 >> 標準モジュールを選択します。

• 以下のVBAコードをコピーして貼り付けます。

VBAコード:

Sub HighlightSalesBelowTarget()
 Dim targetFilePath As String
 targetFilePath = "C:\Users\Sales Target.xlsx" ' <--- ご自身のファイルパスに変更してください
 
 Dim wbTarget As Workbook
 Dim wsTarget As Worksheet
 Dim wsActual As Worksheet
 Dim i As Long, j As Long
 Dim salesValue As Variant, targetValue As Variant
 
 Set wsActual = ThisWorkbook.Sheets("Performance_Data")
 Set wbTarget = Workbooks.Open(targetFilePath, ReadOnly:=True)
 Set wsTarget = wbTarget.Sheets("Quarterly_Targets")
 
 ' データ行:2〜6行目、列:2列目(B/Q1)〜5列目(E/Q4)
 For i = 2 To 6 ' 行:製品
 For j = 2 To 5 ' 列:Q1〜Q4
 salesValue = wsActual.Cells(i, j).Value
 targetValue = wsTarget.Cells(i, j).Value
 If IsNumeric(salesValue) And IsNumeric(targetValue) Then
 If salesValue < targetValue Then
 wsActual.Cells(i, j).Interior.Color = RGB(255, 199, 206) ' 薄い赤
 Else
 wsActual.Cells(i, j).Interior.Pattern = xlNone ' 塗りつぶしなし
 End If
 End If
 Next j
 Next i
 wbTarget.Close SaveChanges:=False
 MsgBox "強調表示が完了しました。", vbInformation
End Sub

• Sales Targetファイルへのフルパスに合わせて、コード内のファイルパスを更新します。
• マクロが目標ブックを自動的に開きます。
• 各製品・各四半期を順番にループ処理します。
• 売上値が目標を下回る場合、該当セルが薄い赤色で強調表示されます。
• 処理完了後、目標ブックは自動的に閉じられます。

保存して実行:

• ブックをマクロ有効ブック(.xlsm)形式で保存します。
• 開発タブ >> マクロを選択します。
• HighlightSalesBelowTargetを選択 >> 実行をクリックします。

実行結果:

注意:直接参照や名前付き範囲は使用できない

一部のExcelバージョンでは、「条件付き書式の基準には他のブックへの参照を使用できません」という警告が表示されます。

• 外部ブックへの直接参照(例:=[Sales_Targets.xlsx]Quarterly_Targets!B2)は、条件付き書式のルールでは許可されていません。Excelはエラーを返します。
• 外部ブックで定義された名前付き範囲は、別のブックの条件付き書式から参照できません。
• INDIRECT関数などを使っても、この用途ではファイルをまたいだ参照は機能しません。

つまり、条件付き書式のルール内で外部の値を直接扱えるネイティブな方法は存在しないのです。

おすすめの使い分け

• 多くの企業向け:Power Queryで外部データを取り込むのがおすすめです。堅牢性が高く、更新機能に対応しており、すべてのロジックを1つのブック内にまとめられます。
• 臨時対応や簡単なチェック向け:両方のファイルを開いたまま運用しても問題ないなら、外部参照を使った作業用列が手軽で便利です。
• 継続的な自動化が必要な場合:特に大規模なデータセットでは、VBAによる完全自動化が最適な選択肢になります。

まとめ

外部データに基づく条件付き書式は、複数ファイルにまたがる動的なデータ可視化を実現する強力な機能です。ご自身の状況や利便性に応じて、いずれかの方法を選択してください。設定後は必ず十分なテストを行い、外部依存関係については明確なドキュメントとして記録を残すことで、将来的な参照やチームメンバーとの共同作業が格段にスムーズになります。

なお、外部ブックの値をもとに条件付き書式を直接トリガーすることは、Excelではネイティブにサポートされていない点に留意しておきましょう。