Excel でのマスターシートの作成と管理:データの効率的なリンクと整理

データセットが大きくなり始めている場合、または関連性が緩い情報を使用している場合は、他のすべてのデータセットにリンクするマスター シートを保持しておくと、明確な概要を取得し、重要な情報を伝えることができます。マスター シートを使用すると、そのままコピーするのではなく、リンクを使用してデータを自動的に更新できます。 Excel でマスター シートへのリンクを作成し、シートを整理する方法は次のとおりです。

オプション 1 – 基本的なセル参照を使用してマスター シートにリンクする

マスター シートとの間でデータを取得する最も簡単な方法は、セルを直接参照することです。これは、シート間で渡す必要がある単一の値に対して機能します。

ステップ 1. リンクされた値を表示するシート内のセルをクリックします。

ステップ 2. 等号 (=) を入力して数式を開始し、ワークブックの下部にあるターゲット シートのタブをクリックします。数式バーには、シート名の後に感嘆符が自動的に表示されます。

ステップ 3. プルしたいセルをクリックして「Enter」を押します。 Excel は、リンクされた値が表示された元のシートに戻ります。

あるいは、クリックせずに参照を直接入力することもできます。構文は =SheetName!CellAddress であるため、「Revenue」という名前のシートからセル B2 を取得するには、「=Revenue!B2」と入力します。シート名にスペースが含まれている場合 (たとえば、「Revenue 2026」)、='Revenue 2026'!B2 のように一重引用符で囲みます。

オプション 2 – Excel のシートをリストとしてマスター シートにリンクする方法

マスター シートにそれぞれのシート (おそらく特定のセル) へのリンクを含めたい場合は、次の構文で HYPERLINK 関数を使用できます。

HYPERLINK (link_location, [friend_name])

ここでのリンクの場所は、一連の文字から「#SheetName!Cell」の形式で解析する必要があるため、注意が必要です。 「フレンドリ名」の部分は、ハイパーリンクの外観 (名前) です。

これを行うには、リンクの場所に次の式を使用する必要があります:“#””&CellReference&”‘!Cell” ここで:

• CellReference は、マスター シートと Excel にリストされているシートの名前です (名前は一致する必要があります。
• セルは、A1、C3 など、リンク先のシートのターゲット セルです。

たとえば、年間収益の単純なマスター シートがあるとします。

ステップ 1. 各シートにリンクを配置したい場合は、最初のオプションの数式を次のように使用できます:=HYPERLINK(“#'”&B2&”‘!A1”, B2)

Excel は、B2 にリストされている名前 (この場合は「Revenue 2020」) を取得し、シート「Revenue 2020」へのリンクをセル A1 に作成します。わかりやすくするために、「フレンドリ名」は単にコピーされています。

ステップ 2. フラッシュフィルするか、フィルハンドルを下にドラッグして数式を適用すると、Excel は自動的にセル範囲を反復処理します。

ステップ 3. マスター シートに新しいシートを追加する場合は、一番下に新しい行を追加して数式を複製するだけです。名前が下部のシート ツールバーに表示されているとおりであることを確認してください。

リンクは (Excel での順序どおりに) 連続している必要はないことに注意してください。

オプション 3 – 名前付き範囲を使用してセルをマスター シートにリンクする

特定のセル アドレスを参照する代わりに、プロセス全体を通じてセルに認識される「名前」を付けることができます。

ステップ 1. 名前を付けたいセルまたは範囲を選択します。

ステップ 2. 画面の左上隅、列 A のすぐ上にある名前ボックスをクリックします。わかりやすい名前を入力し、「Enter」を押して確定します。たとえば、シート「Revenue 2020」のセル B2 に「Revenue2020」という名前を付けました。

「名前マネージャー」を使用して、現在の名前のリストを確認できます。

「ABC123」（3 文字の後に数字）という名前は Excel テーブルのセル参照として使用できるため、作成できないことに注意してください。

ステップ 3. ワークブック内の他のシートでは、「=Name」(引用符なし) と入力するだけでその値を参照できるようになります。また、数式を入力するときに名前が候補として表示されます。

ステップ 3b. 値ではなくセルへのリンクを渡したい場合は、次の式を使用します:=HYPERLINK(“#Name”,link_name)

ステップ 4. 名前の範囲をパターンに従わせた場合は、マスター シートにそれぞれのシートへのハイパーリンクを作成させることで、そのパターンをエミュレートできます。

各名前付き範囲が「RevenueXXXX」という命名規則に従っているとします。「XXXX」は年です。

次に、列 B に年を入力し、C の各セルに次の式を入力します。

=HYPERLINK("#"&CELL("住所",INDIRECT("収益"&B2)),"収益"&B2)

これにより、名前の「Revenue」部分が手動で渡され、次に年の範囲からのセル参照が渡されます。