Excel チャート学習折衷的な OFFSET 動的テーブル

冬の始まりで寒いですね！家に暖房がないの？動いたり、足を踏み鳴らしたり、手をこすったりするだけで、暖かく感じられます。冬が始まり、もうすぐ一年が終わりますね！まだ給料上がらないの？時計をこれまでとは異なる動きにし、更新を同期し、ダイナミックに表示します。自分の手間を省くだけでなく、上司も「給料」を支払うことを好みます...

# #最初の動き: 動的なグラフで上司を感動させましょう

上司は、あなたが報告してきた単純な表 (下) を見ることに慣れていますが、今あなたは突然グラフ (下) を見せます。動的にクエリできることは、「上司の批判的な心を勝ち取ることができますか?」とあなたは言います。ご褒美で上司を驚かせることができますか?

今のレポート明日のレポート

この変更を実現するにはどうすればよいですか?とても簡単で、動的なチャートを作成するだけです。

ダイナミック チャートとは、上司がさまざまな領域を選択し、チャートにさまざまなデータが表示されることを意味します。これを実現するには 3 つのステップがあります。1 つのステップは、上司が選択できるドロップダウン メニューを作成すること、1 つのステップは、選択に応じて動的に変化するデータ領域を作成すること、そして 1 つのステップは、データに基づいてグラフを挿入することです。動的データ領域。

動的データ領域は VLOOKUP 関数を使用して実装するのが一般的ですが、今回は通常の方法ではなく、OFFSET 関数を使用して動的データ領域を完成させます。

ステップ 1: ドロップダウン選択を行う

1. セル J1 を選択し、[データ] タブの [データ検証] をクリックします。

2. [データ検証] ウィンドウの下部にある [設定] オプションで、[許可] に [シリーズ] を選択し、5 つの売上が含まれるセルを選択します。領域がソースとして配置されます。=$A$2:$A$6」を選択し、「OK」をクリックします。

#ステップ 2: 動的に更新される補助データ領域を作成します

3。次のステップでは、セル J1 の値に基づいてグラフを動的に更新します。 J1は「北京エリア」を選択。セルB7に「=OFFSET(B1,MATCH($J$1,$A$2:$A$6,0),0)」と入力します。次に、数式が右側のセル G7 に入力されます。このようにして、セル B7:G7 は、1 月から 6 月までの北京地域の販売量を返します。

分析:

OFFSET を使用して "B1" を参照系として取得し、オフセット行の数は次のとおりです。 MATCH 関数を使用して $J を取得します$A$2 の $1 :$A$6 の位置では、オフセット列番号は 0 であり、オフセットがないことを意味します。図 J1 に示すように、値は「北京地域」で、$A$2:$A$6 の位置は 1 です。OFFSET で返される値は、「B1」を参照系として 1 行下にオフセットした参照値です。このようにして、選択エリア$J$1が変化し続けると、セルB7:G7が対応するエリアの売上データを取得することになります。

4. 次に、平均線データを設定し、セル B8 に「=AVERAGE($B$7:$G$7)」と入力して、$B$7:$G$7 の平均値を取得します。次に、数式が右側のセル G8 に入力されます。選択領域$J$1が変化すると$B$7:$G$7も変化し、それに応じて平均値も変化します。

#ステップ 3: グラフの作成

5. 設定された補助行に基づいてグラフを作成します。タイトルB1:G1と補助行B7:G8領域を選択し、「挿入」タブの「グラフ」グループにある「二次元縦棒グラフ」をクリックします。

#6. セル K1 に「=J1&"売上データ"」と入力します。

グラフのタイトル ボックスをクリックし、編集バーに「=Sheet2!$K$1」と入力すると、グラフのタイトルとデータ検証領域が同時に更新されます。

#7. [グラフ ツール] の [デザイン] タブにある [グラフの種類の変更] をクリックします。

8. [グラフの種類の変更] ウィンドウで、[すべてのグラフ] オプションの下の [組み合わせ] をクリックし、平均値が存在する系列を「」に変更します。折れ線グラフ」。

#9. 最後に、グラフの凡例を削除し、補助データの B7:G8 と K1 セルのフォントを白で非表示に変更すれば完了です。

2 番目のアクション: リンク。ピボット テーブルとデータ ソースを同期的に更新できるようにします。

ピボット テーブルをデータ ソースと同期して更新したい場合は、次のように導入しました。達成する前のスーパーテーブルをあなたに。さらに、OFFSET も使用できます。図に示すように、左側のデータ ソースに基づいて右側のピボット テーブルが挿入されます。

次に、次のように、データ ソースの更新後にピボット テーブルを同期的に更新する必要があります。

ステップ 1: 名前を定義します

1. [式] タブの [定義名] オプション グループで [名前の定義] をクリックします。

#2. 「新しい名前」ウィンドウで、「名前」列に「データ」を入力し、「基準位置」に次の式を入力します。 #=OFFSET(Sheet1 !$A$1,,,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

##分析:

「Sheet1」は、データが配置されているワークシートです。この関数は参照系として "Sheet1!$A$1" を使用し、オフセットを行わず (オフセットの行と列は空です)、テーブル データ全体を動的に返します。 COUNTA(Sheet1!$A:$A) はテーブル データの行数を取得するために使用され、COUNTA(Sheet1!$1:$1) はテーブル データの列数を取得するために使用されます。取得される結果は動的であり、テーブル内の行と列の数が増減するにつれて変化します。

ステップ 2: データ ソースを変更する

3. ピボット テーブル上の任意のセルをクリックすると、[ピボット テーブル] が表示されます。ツール」が表示されます。次に、「ピボットテーブル ツール」の「分析」タブで「データ ソースの変更」をクリックします。

#4. [ピボットテーブル データ ソースの変更] ウィンドウで、[テーブル/範囲] を先ほど定義した名前の [データ] に変更し、[OK] をクリックします。

#ステップ 3: 同期の更新

5. 次に、データの最後の行にデータを追加します。

#6. ピボット テーブルを右クリックし、[更新] コマンドを選択すると、ピボット テーブルが更新されます。

Offset 関数を使用して、2 つの「アクション」を実装しました。最初のアクションは、ダイナミック チャートを使用して上司に好印象を与え、「給与」をオープンできるようにすることです。 2 番目のアクションは、ピボット テーブルをデータ ソースにリンクすることです。手間を省きます。さて、急いでフォームを動かしてください!手足の冷えは避けられない冬がやってきますが、給料が上がると心も温まりますね！

関連する学習の推奨事項: Excel チュートリアル

以上がExcel チャート学習折衷的な OFFSET 動的テーブルの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。