ワークブック全体で条件付き書式設定をマスターする:外部データから書式をトリガーする

 

外部ブックの値に基づく条件付き書式設定を使用すると、ある Excel ブック内のセルを、別のブックに保存されているデータに基づいて自動的に書式設定できます。この機能は、ビジネス環境で動的なレポート、ダッシュボード、複数ファイル間のデータ比較を作成するために不可欠です。

このチュートリアルでは、外部ワークブックの値に基づいて条件付き書式設定をトリガーする方法を示します。

実際の四半期売上高を 1 つのファイルで追跡し、四半期ごとの売上目標を別のファイルで追跡するとします。実績シートでは、外部ファイルから正しい目標を取得して、目標を下回る実際の売上を強調表示します。

方法 1:外部参照を使用したヘルパー列

これは、すべての Excel バージョンで機能する最も信頼性の高い方法です。ヘルパー列で外部参照を含むワークシート式を使用できます。ヘルパー列の値に基づいて条件付き書式設定を適用します。

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

まず、上記のサンプル データを含む両方のワークブックを作成して保存します。

• 「売上目標.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）を選択します。
• ホームに移動します タブ>> 条件付き書式を選択します>> 新しいルールを選択します .
• [数式を使用して書式設定するセルを決定する] を選択します。 .
• 次の数式を入力します。

• [フォーマット] をクリックします。>> 明るい赤の塗りつぶし色を選択します。
• [OK] をクリックします。 .

さらにルールを追加:

必要に応じて四半期ごとに繰り返します。

第 2 四半期:

• 次の数式を入力します。

• [フォーマット] をクリックします。>> ライトブルーの塗りつぶし色を選択します。
• [OK] をクリックします。 .

第 3 四半期:

• 次の数式を入力します。

• [フォーマット] をクリックします。>> ライトグリーンの塗りつぶし色を選択します。
• [OK] をクリックします。 .

第 4 四半期:

• 次の数式を入力します。

• [フォーマット] をクリックします。>> 明るい紫色の塗りつぶし色を選択します。
• [OK] をクリックします。 .

ステップ 4:ヘルパー列を非表示にする (オプション)

• 列 G:J を選択します。
• 右クリック>> [非表示] を選択します。 .

データには外部ワークブックの値に基づいた条件付き書式が表示されますが、Excel は内部ヘルパー列を使用して外部参照の制限を回避します。

方法 2:Power Query ソリューションを使用する

Power Query は、Excel 365 または Excel 2016 以降を使用するユーザーに堅牢なソリューションを提供します。

ステップ 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" ' <--- Update this to your file path
 
 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")
 
 ' Data rows: 2 to 6, columns: 2 (B/Q1) to 5 (E/Q4)
 For i = 2 To 6 ' Rows: products
 For j = 2 To 5 ' Columns: 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) ' Light red
 Else
 wsActual.Cells(i, j).Interior.Pattern = xlNone ' No color
 End If
 End If
 Next j
 Next i
 wbTarget.Close SaveChanges:=False
 MsgBox "Highlighting complete.", vbInformation
End Sub

• ファイル パスを Sales Target ファイルへのフル パスで更新します。
• マクロはターゲット ワークブックを開きます。
• 各製品と各四半期をループします。
• 売上額が目標を下回る場合、セルは薄赤色でハイライト表示されます。
• マクロはターゲット ワークブックを自動的に閉じます。

保存して実行:

• ワークブックをマクロ有効ファイル (.xlsm) として保存します。
• 開発者に移動します。 タブ>> マクロを選択します .
• ターゲット未満のハイライト売上を選択します>> [実行] をクリックします。 .

出力:

うまくいかないこと:直接の外部参照と名前付き範囲

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

• 外部ワークブックの直接参照 (例:=[Sales_Targets.xlsx]Quarterly_Targets!B2) は、条件付き書式設定ルールでは許可されません。 Excel はエラーをスローします。
• 名前付き範囲 外部ワークブックで定義されたものは、別のワークブックの条件付き書式設定で参照できません。
• INDIRECT または同様の関数を使用しても、このコンテキストでは複数のファイルでは機能しません。

条件付き書式設定ルールで外部値を使用する直接的でネイティブな方法はありません。

推奨事項

• ほとんどのビジネスの場合: Power Query を使用して外部データをインポートします。堅牢で、更新をサポートし、すべてのロジックを 1 つのワークブック内に保持します。
• アドホックまたはクイックチェックの場合: 両方のファイルを開いたままにしても構わない場合は、外部参照を含むヘルパー列を使用します。
• 自動化された継続的なソリューションの場合: 特に大規模なデータセットの場合、手動での自動化と書式設定には VBA を使用します。

結論

外部条件付き書式設定は、ファイル間のデータを動的に視覚化できる強力な機能です。シナリオと利便性に基づいて、任意の方法を使用できます。常にセットアップを徹底的にテストし、将来の参照やチーム メンバーとのコラボレーションのために外部依存関係の明確な文書を維持することを忘れないでください。

外部ワークブックの値に基づいて条件付き書式設定をトリガーすることは、Excel ではネイティブに実行できません。

ソリューション付きの高度な Excel 演習を無料で入手しましょう!