わえなび ワード&エクセル問題集

当サイトにリンクを張るのは自由です。WordとExcelの練習問題の動画を「Youtube」で無料公開しています。みんなで勉強しましょう!

【Excel関数】シート間の参照、複数シートの計算と串刺し、3D集計

オートSUMボタンの使い方をひと通り学習した後で必ずと言っていいほど登場するのが、串刺し集計(3D集計)と呼ばれる計算です。しかし、教科書通りに手順を説明するだけで初心者が本当に使えるようになるのでしょうか?

習っている初心者の皆さんはたぶん、心の中で「こんな計算どこで使うのか???」と思っているに違いありません。

串刺し集計(3D集計)を説明する前に、初心者に皆さんに理解してもらわなければいけないことがあります。

  • 複数のシートがあった場合に、シート間でセルの参照ができること
  • 複数のシートで足し算、引き算、掛け算、割り算が可能であること
  • 複数のシートで(串刺しではない)SUM関数が普通に使えること

これらのことが理解できて初めて、串刺しをする意味が分かるのです。

f:id:waenavi:20190127215446j:plain

 

そこで、今回は、シート間のセル参照と複数シートにまたがって計算することを理解したうえで、串刺しをする効果について解説します。

目次

問題文のあとに簡単な操作方法を解説していますが、静止画では、わかりにくいと思いますので、最後に動画を載せています。ぜひご覧ください。

1.準備(サンプルファイルはこちら)

わえなび公式サイト(waenavi.jp)では、この記事で使用しているExcelのデータを無料で配布しています。ご自由にお取りください。5-8-2、6-12の「サンプルファイル(ZIP)」です。

 

2.シート間の参照

(1)シート間相対参照

問題

Sheet1のA列のデータが、Sheet2のA列にも表示されるように参照しなさい。また、Sheet1の名前を「元データ」に変えなさい。

f:id:waenavi:20190127193242j:plain

 

解説

Sheet1にデータがあります。

f:id:waenavi:20190127194021j:plain

 

Sheet2に計算式を入れます。

f:id:waenavi:20190127194024j:plain

 

イコールを入力したあとでSheet1に切り替えます。

f:id:waenavi:20190127194029j:plain

f:id:waenavi:20190127194032j:plain

 

A1を選択します(クリックします)。セルの参照について、詳しくはファンダメンタルExcel Program5-3の動画参照。

f:id:waenavi:20190127194036j:plain

 

Enterキーで確定します。

f:id:waenavi:20190127194040j:plain

 

びっくりマーク「!」はシート名であることを表します。このように別のシートのセルを参照することもできます。

f:id:waenavi:20190127194044j:plain

 

オートフィルをします。相対参照になります。これで完成です。(相対参照について、詳しくはファンダメンタルExcel Program5-4の動画参照)

f:id:waenavi:20190127194047j:plain

 

Sheet1に切り替えます。データを変えます。

f:id:waenavi:20190127194051j:plain

 

Sheet2も連動します。このように2枚のシートのデータが必ず同じデータであることが保証されます。

f:id:waenavi:20190127194054j:plain

 

Sheet1の名前を変えます。確定します。

f:id:waenavi:20190127194102j:plain

 

Sheet2の計算式を見ます。計算式のシート名も変わっています。シート名を変えるとそのシートを参照している数式のシート名も自動的に変わります。

f:id:waenavi:20190127194110j:plain

 

(2)シート間絶対参照

問題

Sheet1のA1に文字列を入力すると、Sheet2に同じ文字列が30個表示されるようにしなさい。

f:id:waenavi:20190127193411j:plain

 

解説

Sheet1にデータを入力します。

f:id:waenavi:20190127194949j:plain

 

Sheet2にそれを参照する計算式を入力します。イコールを入力します。

f:id:waenavi:20190127194953j:plain

 

オートフィルをすることを考えると絶対参照にします。(絶対参照について、詳しくはファンダメンタルExcel Program5-6の動画参照)

f:id:waenavi:20190127194957j:plain

 

確定します。

f:id:waenavi:20190127195001j:plain

 

オートフィルをします。これで完成です。

f:id:waenavi:20190127195004j:plain

 

Sheet1のデータを変えると連動してSheet2も変わります。

f:id:waenavi:20190127195008j:plain

f:id:waenavi:20190127195011j:plain

 

(3)シート間複合参照

問題

Sheet1にこのように入力した。

f:id:waenavi:20190127193427j:plain

 

これを利用してSheet2に次の表を作りなさい。

f:id:waenavi:20190127193432j:plain

 

解説

イコールを入力します。

f:id:waenavi:20190127195217j:plain

 

セルA2をクリックします。

f:id:waenavi:20190127195221j:plain

 

「&」を入力します。(文字列連結演算子&について、詳しくはファンダメンタルExcel Program5-6の動画参照)

f:id:waenavi:20190127195225j:plain

 

セルB1をクリックします。これは複合参照のパターンです。Aと1に$マークを付けます。(複合参照について、詳しくはファンダメンタルExcel Program5-8の動画参照)

f:id:waenavi:20190127195229j:plain

 

確定します。コピーをします。

f:id:waenavi:20190127195232j:plain

 

貼り付けます。

f:id:waenavi:20190127195236j:plain

 

3.複数シートの計算と串刺し・3D集計

問題

本社・大阪・福岡の3つの支店で、5つの商品の目標販売台数と現時点での販売台数がわかっている。次の指示に従って4つのシートを完成させなさい。

f:id:waenavi:20190127205542j:plain

 

設問(1)

シート「集計」に次の表を入力しなさい。

f:id:waenavi:20190127205729j:plain

 

解説

シート名を集計にします。(シート名変更について、詳しくはファンダメンタルExcel Program4-1の動画参照)

f:id:waenavi:20190127210108j:plain

 

表を入力します。計算式はありませんので、単に直接入力していくだけです。オートフィルを使って効率よく入力します。

f:id:waenavi:20190127210113j:plain

 

数値を入力します。価格はカンマ区切りです。これで完成です。

f:id:waenavi:20190127210117j:plain

 

設問(2)

シート「本社」に次の表を入力しなさい。

f:id:waenavi:20190127205732j:plain

 

解説

シート名を本社にします。

f:id:waenavi:20190127210416j:plain

 

項目名を入力します。商品名と本体価格は、集計のシートを参照します。イコールを入力します。

f:id:waenavi:20190127210420j:plain

 

集計のシートのセルA3を参照します。

f:id:waenavi:20190127210424j:plain

 

オートフィルをします。これで、相対参照により集計のシートと本社のシートは連動します。

f:id:waenavi:20190127210427j:plain

 

販売台数を入力します。これで完成です。

f:id:waenavi:20190127210432j:plain

 

設問(3)

シート「大阪」「福岡」もシート「本社」と同じ表を作成しなさい。ただし、大阪・福岡の販売台数は次の通りである。

f:id:waenavi:20190127205735j:plain

 

解説

本社のシートを2回コピーします。(シートのコピーについて、詳しくはファンダメンタルExcel Program4-1の動画参照)

f:id:waenavi:20190127210608j:plain

 

シート名を大阪と福岡にします。

f:id:waenavi:20190127210611j:plain

 

大阪のシートで1行目を大阪営業所に変えます。販売台数を変えます。

f:id:waenavi:20190127210614j:plain

 

福岡のシートで1行目を福岡営業所に変えます。販売台数を変えます。これで完成です。

f:id:waenavi:20190127210618j:plain

 

設問(4)

3枚のシートを選択して本体価格*販売台数で合計金額を求めなさい。

f:id:waenavi:20190127205739j:plain

 

解説

3つのシートを選択します。

f:id:waenavi:20190127211235j:plain

 

合計と入力します。本体価格に販売台数をかけて合計金額を求めます。

f:id:waenavi:20190127211240j:plain

f:id:waenavi:20190127211246j:plain

 

オートフィルをします。

f:id:waenavi:20190127211253j:plain

 

シートが複数選択されている状態を解除して、1つだけにします。これで完成です。

f:id:waenavi:20190127211300j:plain

 

設問(5)

シート「集計」のF~I列に次の4つを計算しなさい。

f:id:waenavi:20190127205743j:plain

  1. 今後、本社が目標を達成するのに必要な販売台数
  2. 3店の合計販売金額
  3. 3店の平均販売金額
  4. 今後、3店で目標を達成するのに必要な販売台数

解説

商品1について、本社の目標は10台です。現時点では5台売れていますので、目標達成まであと5台です。引き算します。

f:id:waenavi:20190127212144j:plain 

 

セルの前にシート名とビックリマーク(!)が付きます。

f:id:waenavi:20190127212147j:plain

 

オートフィルをします。これで完成です。

f:id:waenavi:20190127212151j:plain

 

3店の合計金額を求めます。各シートに分かれている合計金額を足します。3店舗の金額を足し算で(=本店!D3+大阪!D3+福岡!D3)足せばよいのですが面倒です。そこでオートSUMを使います。

オートSUMのボタンを押します。

f:id:waenavi:20190127212154j:plain

 

本社から福岡までシートを選択します。

f:id:waenavi:20190127212158j:plain

 

セルD3を選択します。

f:id:waenavi:20190127212202j:plain

 

確定します。オートフィルをして完成です。

f:id:waenavi:20190127212206j:plain

 

計算式はこのようになっています。3つのシートのセルD3を足す計算式「=本社!D3+大阪!D3+福岡!D3」を、「=SUM(本社:福岡!D3)」と表します。このように異なるシートの同じセルを合計することを、串刺し、または、3D集計といいます。本社から福岡のシートで、セルD3の合計を出す計算式になっています。

f:id:waenavi:20190127212209j:plain 

 

オートSUMボタン、複数シートの選択、セル選択、確定の順番です。

f:id:waenavi:20190127212213j:plain

 

SUMによって合計をする範囲はすべて同じ場所である必要があり、同じ形である必要があります。

f:id:waenavi:20190127212217j:plain

 

また、計算式を入れて結果を表示するセル範囲も同じ形である必要はありますが、場所が同じである必要はありません

f:id:waenavi:20190127212220j:plain

f:id:waenavi:20190127212224j:plain

 

平均も同じようにして求めます。オートSUMボタンで平均を選びます。

f:id:waenavi:20190127212227j:plain

 

本社から福岡までシートを選択します。

f:id:waenavi:20190127212231j:plain

 

セルD3を選択します。

f:id:waenavi:20190127212235j:plain 

 

確定します。オートフィルをして完成です。

f:id:waenavi:20190127212239j:plain

 

最後に、3店で目標を達成するのに必要な販売台数を求めます。とりあえず3店舗の合計を求めます。オートSUMのボタンを押します。

f:id:waenavi:20190127212243j:plain

 

本社から福岡までシートを選択します。

f:id:waenavi:20190127212247j:plain

 

セルC3を選択します。

f:id:waenavi:20190127212251j:plain

 

確定します。イコールのあとに「C3-」と入力します。これで全社の目標台数から引くことになります。

f:id:waenavi:20190127212254j:plain

 

オートフィルをします。これで完成です。

f:id:waenavi:20190127212257j:plain

 


解説は以上です。


4.動画版はこちら(無料)

この記事は、わえなび実力養成講座「ファンダメンタルExcel」Program 5-8-2、6-12 の2本のYoutube動画を書き起こしたものです。

わえなび

 

わえなび

 

ちなみに、オートSUM関連の動画は全12回のシリーズです。すべて無料公開しています。ぜひご覧ください。

 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]