第01回 SUM関数を活用!

返品管理表を作成しよう

エクセルの計算方法で、もっとも利用頻度の高い集計方法は『合計』です。

合計を求めるために用意されている『SUM関数』を利用すると、範囲選択するだけで合計が求められます。大量データでも、SUM関数を使えば簡単に合計が求められます。

また、SUM関数はツールバーに[オートSUM]ボタンとして用意されているので、ボタンクリックだけで合計を瞬時に求められます。

今回は商品の返品管理表を使用して、合計や累計をSUM関数で求めていきましょう。

また、返品率を求める際には『絶対参照』を使用し、数式の作成方法をマスターしていきましょう。

▼ 図1-1　今回使用する関数

ボタンでSUM関数を設定してみよう

[オートSUM]ボタンを利用すると、複数範囲の合計を一発で求めることができます。

ボタンをクリックすると、自動的にSUM関数が挿入され、合計範囲も選択されるため確定するだけでOKです。合計範囲はドラッグしなおすことで変更も可能。また、複数の合計をまとめて求めることもできます。

▼ 図2-1　[オートSUM]ボタンで合計を求める

1. “関東エリア”シートの見出しをクリックする

2. 合計を表示させたいセルD10を選択する

3. [オートSUM]ボタンをクリックする

4. 自動的に合計を求める数式「=SUM（D4:D9）」が入力される

5. [Enter]キーを押して数式を確定すると、合計が表示される

《Excel 2007の補足》

また、複数セルの集計も一回の操作でまとめて求めることができます。

合計するデータと結果を表示させたい領域を選択し、[オートSUM]ボタンをクリックします。▼ 図2-2　複数の集計を一回の操作で行う

1. セル範囲C4からD10を範囲選択する

2. [オートSUM]ボタンをクリックする

オートカルク機能

数式や関数を設定する前に計算結果を知りたい場合は、『オートカルク機能』を使うと便利です。

① 計算結果を知りたいセル範囲を選択する

② ステータスバーを右クリックし、計算方法を選択する

③ 計算結果が表示される

離れたシートの合計を算出する

離れたシートの値をSUM関数で集計してみましょう。

“返品管理”シートに“関東エリア”と“関西エリア”の売上個数と返品個数を集計します。

▼ 図3-1　離れたシートの値を合計する

1. “返品管理”シートの見出しをクリックする

2. 集計結果を表示したいセル範囲C4からD10を選択する

3. [オートSUM]ボタンをクリックする

集計するシートでセルを選択し、数式に「, （カンマ）」を加えます。別のブックに集計したいデータがある場合は、あらかじめそのブックを開き、集計したいデータのあるシートを選択しておきましょう。

▼ 図3-2　1つ目のシートを指定

4. “関東エリア”シートの見出しをクリックする

5. 集計したい値が入力されているセルC4をクリックする

6. 数式バー内の「関東エリア!C4」の後に「,」を入力する

続いて、“関西エリア”シートのデータを選択します。

シートが連続している場合は、[Shift]キーを押しながらシート見出しをクリックすると複数シートのデータを集計できます。

▼ 図3-3　2つ目のデータを選択

7. “関西エリア”シートの見出しをクリックする

8. 集計したい値が入力されているセルC4をクリックする

数式が完成したら、確定します。

ただし、[Enter]キーで確定すると1つのセルにしか数式は入力されないため、[オートSUM]ボタンをクリックして確定しましょう。▼ 図3-4　選択範囲に数式を適用させる

9. [オートSUM]ボタンをクリックする

10. 選択していたセル範囲C4からD10、全てのセルに関東エリアと関西エリアの合計数が表示される

数式入力の基本をマスターしよう

Excelは数式を入力することで多様な計算を可能にしています。Excelでは「=」に続いて、「+（足し算）」「-（引き算）」「*（掛け算）」「/(割り算)」の演算子を使って数式を作成します。

▼ 図4-1　セルに数式を作成する

数式のコピーを行うと、セルの値は相対的に参照されて参照先が1つずつずれます。これを『相対参照』といいます。

しかし、全体の構成比などを求めたい場合、各データを同じ合計値で割る必要があるため、数式のコピーを行うと参照先の合計セルがずれてしまい、正しい答えが求められません。

▼ 図4-2　セルに数式をコピーする

コピーをしても参照セルがずれないように固定するには、『絶対参照』を使います。絶対参照を設定した数式には「$」の文字が追加され、コピーを行っても常に同じセルを参照します。

下図の例では、「=D4/D10」のD10セルに絶対参照を設定し、「=D4/$D$10」としました。

コピーした数式は「=D5/$D$10」、「=D6/$D$10」???というように常にD10を参照するため、正しい値を求めることができます。

▼ 図4-3　絶対参照を使った数式をコピーする

また、「D$10」というように「行のみ」「列のみ」を固定する『複合参照』を使うこともできます。

絶対参照、複合参照は「$」を直接入力するか、[F4]キーを押すことで設定できます。

[F4]キーを押す回数で、参照方法を切り替えることができます。

また、累計を求めたい場合などはSUM関数に絶対参照を利用します。SUM関数の合計範囲を指定する際に、先頭セルに絶対参照を使用します。これにより数式をコピーしても、先頭セルは固定されたまま最終セルのみが拡張されるため、累計を求めることができます。

▼ 図4-4　絶対参照を使って累計を求める

絶対参照を使って計算しよう

絶対参照を利用して、月ごとの返品率を求めてみましょう。月ごとの返品数を全体の返品数で割って求めます。

▼ 図5-1　返品率を求める

1. 返品率を求めたいセルE4を選択する

2. 「=」を入力し、1月の返品数が入力されているセルD4をクリックする

3. 「/」を入力し、返品数の合計が入力されているセルD10をクリックして[F4]キーを押す

4. [Enter]キーを押して数式を確定する

残りの月の返品率もオートフィル機能を利用して、数式をコピーします。

コピーした数式を確認すると、セルD10で割り算されていることがわかります。

▼ 図5-2　数式をコピーする

さらに絶対参照を利用して、返品数の累計を求めてみましょう。

▼ 図5-3　絶対参照を利用して、累計を求める

1. 累計を求めたいセルF4を選択する

2. [オートSUM]ボタンをクリックする

3. セルD4をクリックし、「:」を入力する

4. SUM関数の引数が「D4:D4」となるので、一方を「$D$4」と絶対参照にする

5. [Enter]キーで数式を確定する

残りのセルに数式をコピーし、返品数の累計を求めましょう。

コピーした数式を確認すると、先頭セルからセル範囲が拡張されていることが確認できます。▼ 図5-4　累計を求める