@ 平均値を表示するセルにセルポインタを合わせる
A 関数を入力して、[リターン(Enter)]キーを押して入力を決定する
=AVERAGE(D6:G6)を入力する
@ 平均値が表示される
EX4D300Z 作例1-4-A
EX4D301Z
EX4D302Z
注意
データ(点数)を入力していないときは、平均値を表示するセルにエラー(#DIV/0!)が表示されます。これは、データが入力されていないので計算できない、を知らせるエラー表示です。データを入力すると消えます。このエラー表示は、ゼロ表示を消す方法(後で解説)を利用して消すことができます。
2.少数点以下の桁数
表示される値の小数点以下を変更するとき、変更するセルにセルポインタを合わせて、ツールバーの[小数点表示桁上げ][小数点表示桁下げ]ボタンをクリックして調整します。1回クリックすると1桁変化します。なお、桁下げすると、はみ出した桁で四捨五入された値で表示されます。
@ 表示桁を変更するセルにセルポインタを合わせる
A[小数点表示桁下げ]ボタンをクリックする
@ 小数点以下の表示桁が減り、四捨五入された値が表示される
EX4D303Z EX4D304Z
3.数式を複写する
平均値を求めるすべてのセルに数式を入力しなくても、入力した平均値を求める数式を下へ続くセルに複写して集計表を完成させることができます。1つ1つ入力するより、手間が省け入力ミスも防ぐことができます。
@複写元のセルにセルポインタを合わせる
Aセルの角に表示されている■にマウスポインタを合わせて、ドラッグする
マウスポインタが+字に変わる位置に合わせてドラッグします。
Bドラッグで複写範囲まで枠を伸ばして、ドロップする
C数式が複写されて、平均値が表示される
D矢印キーを押す、または、反転表示以外の空白セルをクリックして反転表示を解除する
小数点の桁数も同じ桁数で複写されます。また、罫線も同時に複写されるので、場合により罫線の引き直しが必要になります。
EX4D305Z EX4D306Z
EX4D307Z
4.空白セルの対応
セル範囲に数値以外(空白セルや文字が入力されているセル)のセルを含むとき、そのセルは平均値の計算から除外されます。ゼロを入力すると、そのセルは数値と見なされるので計算に含まれます。
@この平均値は、国語と算数の2科目の平均値が計算されています。
空白の理科と社会は計算から除外されています。
Aこの平均値は、理科と社会の2科目の平均値が計算されています。
欠席の文字が入力されている国語と算数は計算から除外されています。
Bこの平均値は、4科目の平均値が計算されています。
ゼロが入力されたセルは計算に含まれます。
EX4D308Z
5.科目の平均値
科目ごとの平均値を求めるAVERAGE関数をカーソルキー(矢印キー)の操作で入力する手順を解説しておきます。セル番地をキーボードから入力するより簡単で、セル範囲を視覚的に判断できるので入力ミスも少なくなります。国語を例にすると、入力内容は次のようになります。この数式のセル番地の部分をカーソルキーの操作で入力します。
=AVERAGE(D6:D20)
@科目の平均点を表示するセルにセルポインタを合わて、関数の前部を入力する
=AVERAGE( を入力する
A[↑]キーを押して点線枠をセルD6に移動する
@ 数式バーにセル番地が入力される =AVERAGE(D6
EX4D309Z
Bコロン(:)を入力してから[↓]キーを押して点線枠をD20へ拡げる
数式バーの表示内容 =AVERAGE(D6:D20
C最後の括弧 )を入力して[リターン(Ebter)]キーを押す
D数式が入力され結果が表示される
値は小数点以下の桁数が自動的に決定された桁数で表示されます。[小数点以下桁下げ]や[小数点以下桁上げ]ボタンを使って目的に添った桁数に調整してください。
EX4D311Z
EX4D312Z
EX4D313Z
●数式を横へ複写する
前の3で操作したように、こんどは、入力した数式を右へ複写して科目ごとの平均値を完成させます。
@ 複写元のセルにセルポインタを合わせる
A セルの右下端の■にマウスポインタ(+字型)を合わせてドラッグする
B 複写範囲の最後のセルまでドラッグしてドロップする
@ 反転表示の状態で数式が複写され結果が表示される
C 空白セルをクリックして反転表示を解除する
EX4D314Z
EX4D315Z
EX4D316Z
ポイント[カーソルキーでセル位置を指定できないとき]
[f・2]キーを押すと指定できるようになります。数式を入力しているとき、[↑]などカーソルキーを押しても点線枠が表示されずに、入力中のセル内でカーソル(縦棒)が左右に移動することがあります。このときは、セル内で移動したカーソルをセル番地を入力する位置へ戻して、[f・2]キーを押してください。カーソルキーで点線枠が表示される状態にもどります。
【操作のまとめ】
●空白や文字が入力されているセルは、平均値の計算から除外される
●複写を利用して入力の手間を省くようにする。
●データを入力していないときは、エラー(#DIV/0!)が表示される
■データを選択した平均値(男女別の平均点)
成績表に並べた生徒の一覧から男子だけの平均点、女子だけの平均点、などある条件に合うデータだけを抜き出して平均を求めることができます。成績表以外では、「合計を求める」で解説した、期間の合計、を、期間の平均、と置き換えることが考えられます。
1.男子の科目別平均点
ある条件を元に平均値を求めるには、DAVERAGE関数を使います。先の合計で使ったDSUM関数と同じようにデータの範囲と抽出条件を指定します。
=DAVERAGE(データ範囲,平均を求める列,抽出条件)
@ A B
@男女を条件にするときは、性別の項目を含めたセル範囲を指定する
ここではセル範囲B5:G20
A項目名または範囲の先頭を1とした列番号を指定する
国語の場合、"国 語"または3を指定する
B条件を入力したセル範囲を指定する
データ範囲に入力した同じ項目名と条件(男)を入力したセル範囲を指定する
男のとき、L23:L24 女のとき、M23:M24
作例 1-4-B
EX4D318Z
2.関数を入力する
男子の国語を例に、関数(数式)を入力してみます。完成式は以下のようになるので、キーボードから直接入力してもかまいません。以下の手順は、セル番地やセル範囲の入力をカーソルキー(矢印キー)で操作しています。
=DAVERAGE(B5:G20,”国 語”,L23:L24)
r操作
@ 数式を入力するセルにセルポインタを合わせる
◆ 数式を「=DAVERAGE(」までを入力する
◆ カーソルキーを操作してデータ範囲の先頭セル(B5)に点線枠を合わる
◆ コロン(:)を入力する
◆ ここまでの数式バーの表示内容 =DAVERAGE(B5:
A カーソルキーを操作してデータ範囲の終点セル(G20)に点線枠を合わせる
◆ コンマ(,)を入力する
@ 数式バーの表示内容「=DAVERAGE(B5:G20,」
◆ 平均を求める項目名を入力してコンマ(,)を入力する
A 数式バーの表示内容「=DAVERAGE(B5:G20,”国 語”,」
EX4D266Z
EX4D267Z
◆ カーソルキーを操作して条件範囲を指定する
◆ セルポインタ(点線枠)をセルL23に合わせてコロン(:)を入力する
◆ セルポインタ(点線枠)をセルL24に合わせる
B 数式バーの表示内容「=DAVERAGE(B5:G20,”国 語”,L23:L24」
◆ 括弧「)」を入力する
◆[リターン]キーを押して入力を決定する
C 結果が表示される。
表示された値の小数点以下の桁数を調整して国語の平均点を完了します。
EX4D268Z
EX4D270Z
操作の途中で入力する項目名は半角のダブルクォーテイションで囲みます。また、一覧表に入力している項目名と同じ項目名で指定しなければなりません。文字間にスペースを入力していれば、そのスペースも半角と全角を取り違えないように、正しく入力することが必要です。
.数式の複写
男子平均点の国語に入力したDAVERAGE関数を算数から社会までに複写しても正しく計算されません。その理由は、データ範囲と抽出条件に入力したセル範囲が変化することと、平均を求める項目列が違っているからです。たとえば、算数の位置へ複写すると次のようになってしまいます。
コピー元(D23)
入力内容 =DAVERAGE(B5:G20,”国 語”,L23:L24)
コピー先(E23)
入力内容 =DAVERAGE(C5:H20,”国 語”,M23:M24)
↑ ↑ ↑
変化する 変化しない 変化する
算数の正しい内容(E23)
入力内容 =DAVERAGE(B5:G20,”算 数”,L23:L24)
↑ ↑ ↑
変化しない 変化する 変化しない
セル番地を変化しないようにするには、$を付けて(絶対番地にして)以下のように書き換えればいいのですが、項目名はセル番地でないので変化させることはできません。
コピー元(D23)
入力内容 =DAVERAGE($B$5:$G$20,”国 語”,$L$23:$L$24)
複写で入力の手間を省くには、複写元を上のように書き換えて、右へ続くセル(算数から社会)に複写して、それぞれの項目名を書き換えることになります。
<注意>
DAVERAGE関数の平均を求める列に入力する項目名は、データ範囲に入力されている項目名と同じ項目(文字)を入力することが必要です。文字間のスペースの数、全角半角を取り違えて入力するとエラー「VALUE!」が表示され正しく計算されません。
EX4D269Z
4.女子の平均点
抽出条件のセル範囲が違うだけで、その他の部分は男子の平均点と同じになります。3の操作を参考に入力して、完成させます。
@ 各セルに数式を入力して平均値を求める
EX4D271Z
ヒント[データの入力範囲を明確にする]
完成した集計表にデータを入力するとき、数式を入力しているセルに間違えてデータを入力すると、数式が壊れて(消えて)しまいます。データを入力するセル範囲に色を付けるなどの処理を施して誤入力を防ぐようにします。
ヒント[範囲名]
データ範囲や抽出条件のセル範囲に名前を付けて、その名前をDAVERAGE関数に使うこともできる。
【操作のまとめ】
●空白や文字が入力されているセルは、平均値の計算から除外される
●入力したDAVERAGE関数を複写するときは、項目名などの指定を書き換えることが必要になる
■最大値/最小値を求める
あるデータ範囲の中で値の最大値や最小値を求めるときは、MAX関数やMIN関数を使います。科目別などに分類したデータだけで求めるときは、データベース関数のDMAX関数やDMIN関数(「科目別の最大値と最小値」で解説)を使います。
1.関数の使い方
値の最大最小を求めるMAX関数やMIN関数の書き方は次のようになります。データベース関数のDMAX関数やDMIN関数はDSUM関数と同じように抽出列や抽出条件を加えた書き方になります。
=MAX(データ範囲) データ範囲の中で一番大きな値を求める
=MIN(データ範囲) データ範囲の中で一番小さな値を求める
=DMAX(データ範囲,値を求める列,抽出条件)
=DMIN(データ範囲,値を求める列,抽出条件)
データ範囲に空白セルがあると、そのセルは無視され、0として扱われることはありません。
2.他のシートのデータ
次のような集計表(@)があるとき、金銭出納帳(累計表)の情報をシート2(Sheet2)に作成してみましょう。繰越額から残金までは、参照式とSUM関数を使っています。シート1に入力されている金銭出納帳のデータをシート2で使うので、セル番地の指定にシート名を付け加えることが必要になります。
@金銭出納帳をシート1に作成する
A金銭出納帳の集計データをシート2に作る
EX4D342Z EX4D343Z
作例 1-4-C
●シート名をマウス操作で入力する
繰越額から残金までは参照式とSUM関数を使いますが、引数に入力するセル番地やセル範囲にシート名を付け足すことが必要になります。
セルD4に入力する数式
=Sheet1!G4
↑ ↑
シート名 シート名とセル番地を接続する記号(!)
数式をキーボードから入力してもいいのですが、ここで、マウスによる入力方法を解説しておくことにします。マウスで他のシートのセル番地またはセル範囲を入力するときは、マウスの右ボタンと左ボタンを使い分ける操作になるので、クリックやドラッグするときのボタンを間違わないようにしてください。
●セル番地を入力する
繰越額を表示するセルD4に入力する数式(参照式)を入力してみましょう。以下の手順で操作します。
@数式を入力するセルにセルポインタを合わせて=を入力する
シート2のセルD4に合わせます。
AマウスポインタをSheet1に合わせて右クリックする
参照先のシートラベルを右クリックします。
画面はシート1に変わります。
B参照先のセルを左クリックする
セルG4にマウスポインタを合わせて左クリックする
通常のセル選択と同じ操作です
@ 数式バーにセル番地が入力される
CマウスポインタをSheet2に合わせて右クリックする
画面はシート2に戻ります。
D[リターン]キーを押して入力を決定する
A シート1に入力している繰越額が参照(表示)される
EX4D344Z EX4D345Z
EX4D346Z EX4D347Z
●セル範囲を入力する
SUM関数などのセル範囲を入力するときも、セル番地を入力する手順と同じです。シートラベルを右クリックして画面を切り替えたとき、マウスの左ボタンでドラッグアンドドロップで範囲を選択します。
@ 計算式の最初の部分を入力する
=SUM( を入力する
A シートタブをマウス右ボタンでクリックして切り替える
B セル範囲を指定する
範囲をドラッグアンドドロップで指定する
@ セル範囲が入力される
C マウスポインタをSheet2タブに合わせ、右クリックする
画面はシート2に切り替わる
D 最後の括弧「)」を入力して[リターン]キーを押す
◆ 計算結果が表示される(図EX4D352Z参考)
EX4D348Z EX4D349Z
EX4D351Z
残りの収支合計と残金を求める数式を入力して、ここまでを完成させます。
セルD6の入力内容
=SUM(Sheet1!F6:F18)
セルD7の入力内容
=D4+D5−D6
EX4D352Z
【操作のまとめ】
●指定したデータ範囲内に入ってる空白は無視される
●他のシートのデータを利用するときは、シート名を付けたセル番地やセル範囲で指定する
●マウスでセル番地やセル範囲を入力いるときは、右ボタンでシートの切り替え操作を行う
■科目別の最大値/最小値
集計表や累計表などから科目別の最大値/最小値を求めるときは、データベース関数のDMAX関数DMIN関数を使います。DSUM関数と同じように抽出条件(科目など)を指定して値を求めます。
1.関数の使い方
関数の書き方は以下のとうりです。最大/最小どちらも同じ書き方になります。
最大値を求める
=DMAX(データ範囲,抽出列,抽出条件)
↑ ↑ ↑
@ A B
最小値を求める
=DMIN(データ範囲,抽出列,抽出条件)
↑ ↑ ↑
@ A B
@集計表や累計表などのセル範囲を指定する
最大値または最小値を求めるデータ列と条件が入力されている列(ここでは仕入科目)が入っている必要がある。ここでは、日付、仕入科目、支出金額の3列をデータ範囲にしています。
@−1 日付の列番は1になる
@−2 仕入科目の列番は2になる
@−3 支出金額の列番は3になる
A最大値や最小値を求めるデータの列番号を指定する
ここでは、支出金額を示す3を指定する。データ範囲の先頭を1とした列番号で指定します。
B抽出条件を入力しているセル範囲を指定する
ここでは、F5:F6
ポイント[抽出列]
日付の列を含めないデータ範囲を指定したときは、抽出列は2になります。
EX4D334Z
作例 1-4-D
2.抽出条件
セル範囲F5:F6に入力する抽出条件は、データ範囲に入力されている項目名と同じ項目名にする必要があるので、項目名を参照式で入力します。同じ項目名を正しく入力してもいいのですが、参照式を使う方が入力の手間を省くことができ、入力ミスも防げます。
@ここに参照式を入力する
=C5 を入力する
AセルC5に入力されている項目名が表示される
中央表示にするときは、このセルにセルポインタを合わせ[中央揃え]ボタンをクリックする
Bここに検索する項目名を入力する
ここでは、野菜、を入力しています。
EX4D335Z EX4D336Z EX4D337Z
3.最大値と最小値
データ範囲と抽出条件が揃ったところで、求めた値を表示するセルに関数を入力します。それぞれの関数は以下のようになります。
最大値 =DMAX(B5:D19,3,F5:F6)
↑ ↑ ↑
データ範囲 最大を求める列 抽出条件を入力したセル範囲
最小値 =DMIN(B5:D19,3,F5:F6)
↑ ↑ ↑
データ範囲 最小を求める列 抽出条件を入力したセル範囲
r操作●最大値
@最大値を表示するセルI6に数式「=DMAX(B5:D19,3,F5:F6)」を入力する
@野菜の最大支出金額が表示される
r操作●最小値
A最小値を表示するセルI7に数式「=DMIN(B5:D19,3,F5:F6)」を入力する
A野菜の最小支出金額が表示される
EX4D338Z
EX4D339Z
EX4D340Z
EX4D341Z
ポイント[科目を変更する]
抽出条件に入力した科目(セルF6に入力した野菜)を他の科目に書き換えると、その科目の最大値と最小値を求めることができます。
【操作のまとめ】
●他のセルに入力されている内容と同じ内容を使うときは、すでに入力されているセルを参照式で参照する
●データ範囲の先頭列が1になるので、データ範囲の取り方(列数)により求める列番号(抽出列の番号)が変わる
■金銭出納帳に科目別集計を付ける(科目指定を簡単にする)
金銭出納帳などの累計形式の集計表に、収入や支出の総合計、科目別の合計や最大金額などの表示を付け足してみましょう。科目別の集計はデータベース関数を使うことになりますが、ここでは、抽出条件に少し手を加え、科目の指定を簡単にする方法を紹介します。
1.金銭出納帳の情報
まずはじめに、金銭出納帳(累計表)全体の情報として、合計や収入支出の最大最小金額などを表示することにします。金銭出納帳がシート1に入力されているものとして、情報表示はシート2を使うことにします。それぞそれの数式の詳細は、他で解説している該当項目を参考にしてください。入力する数式は次のようになります。項目名や罫線は、画面図と同じような内容で入力または線引きしてください。
@金銭出納帳がシート1に入力されている
この表は「金銭出納帳を作る」で作成しています
Aシート2セルD4に参照式を入力する
=Sheet1!G4 を入力する
Bシート2セルD5に収入の合計式を入力する
=SUM(Sheet1!E6:E18) を入力する
Cシート2セルD6に支出の合計式を入力する
=SUM(Sheet1!F6:F18) を入力する
Dシート2セルD7に残金の計算式を入力する
=D4+D5−D6
Eシート2セルD8に収入の最大額を求める数式を入力する
=MAX(Sheet1!E6:E18) を入力する
Fシート2セルD9に収入の最小額を求める数式を入力する
=MIN(Sheet1!E6:E18) を入力する
Gシート2セルD10に収入の最大額を求める数式を入力する
=MAX(Sheet1!F6:F18) を入力する
Hシート2セルD11に収入の最小額を求める数式を入力する
=MIN(Sheet1!F6:F18) を入力する
EX4D397Z EX4D398Z
2.科目別の情報
科目別の合計や最大/最小額を求めるには、データベース関数を使うことになります。操作をはじめる前に、データ範囲の指定をやりやすくするためにシート1に入力しているデータ範囲(金銭出納帳)に範囲名を付けておきます。
●範囲名を付ける
データ範囲を選択(反転表示)にして、メニューバー[挿入(I)]e[名前(N)]e[定義(D)s]で設定操作を行います。詳細は、「データ範囲に範囲名を使う」で解説しているので参考にしてください。
@ データ範囲を反転表示にする、 項目名を含めた範囲を指定します
A メニュー[挿入(I)]e[名前(N)]e[定義(D)s]を選択する
@ 名前の定義ダイアログボックスが開く
B 名前「金銭出納帳」を入力する
C[OK]をクリックする
EX4D399Z
EX4D400Z EX4D401Z
●科目別の合計と最大/最小値
データベース関数のDSUM関数、DMAX関数、DMIN関数、を使い、以下のように情報表示を完成させます。関数の詳細は、それぞれの関数を解説している項を参考にしてください。入力する関数(数式)は以下のとうりです。データ範囲に付けた名前の入力は[f・3]キーを押して表示される名前の一覧から選択入力します。
r操作
@抽出条件のテーブルを作る
抽出条件を入力したセル範囲のことを条件テーブルと呼びます
Aそれぞれの値を求める数式を入力する
セルD18に =DSUM(金銭出納帳,5,D15:D16) を入力する
セルD19に =DMAX(金銭出納帳,5,D15:D16) を入力する
セルD20に =DMIN(金銭出納帳,5,D15:D16) を入力する
EX4D402Z
3.科目を番号で入力する
以上の操作で科目別の情報を見ることができるようになりましたが、科目を変更するとき、セルD16に入力することになります。操作に少し慣れるとこの入力もやっかいな作業になり、もっと簡単な操作にできないか、と考えるのは誰しも同じではないでしょうか。そこで、科目名の入力を番号でできるようにしてみましょう。ある値(データ)を番号で入力するには、「品番で品名を自動入力する」で使ったVLOOKUP関数でできます。VLOOKUP関数は、番号とデータ(ここでは科目名)の対応表(これもデータが並んでいるのでテーブルという)が必要なので、作成します。
●科目番号のテーブル
科目名に付ける番号を適当に決め、その番号と科目名の対応表を作ります。
使用している科目名を縦に並べたスタイルの一覧表を作成します。科目番号は任意の番号を付けることができますが、入力しやすい番号を使うようにしましょう。また、上から下へ順番に大きい番号(昇順)を付けます。図のように作成すれば、テーブルの範囲はG15:H20になります。VLOOKUP関数で使用するテーブルの範囲に項目名(セルG14とH14)を含める必要はありません。
@このようなテーブルを作成する
EX4D403Z
●科目名の自動入力
テーブルができあがれば、次にVLOOKUP関数を目的のセルに入力して自動入力(番号で科目名を自動入力する)を完成させます。関数は以下のようになります。VLOOKUP関数の詳細は「品番で品名を自動入力する」をご覧ください。
@番号を入力する場所を作る
番号の入力する位置を明確にするため、罫線や項目を入力する
AセルD16に数式「=VLOOKUP(C16,G15:H20,2,FALSE)」を入力する
数式の入力を[リターン]キーを押して決定すると、エラーが表示されます。このエラーは科目名に付けた番号をセルC16にまだ入力していないからです。次の操作で番号を入力するとエラー表示が消え、科目名が表示されます。
EX4D404Z
EX4D405Z
●科目を指定する
科目番号を入力するセルに番号を入力すれば、データベース関数の抽出条件に科目名が表示され、合計や最大値などが表示されます。
@ 科目番号を入力していないとエラー(#N/A)が表示されます。
@ 抽出条件がエラーなので計算結果が0になっている
A 科目番号を入力する
科目名が表示され、合計などの値が表示される
EX4D406Z EX4D407Z
ポイント[エラー表示を消す]
科目番号が入力されていなときのエラー表示は、IF関数を使って消すことができます。セルD16に入力する数式を次のように変更します。
=IF(C16=””,””,VLOOKUP(C16,G15:H20,2,FALSE))
エラー表示を消すと、合計などは科目を指定しない状態の値が表示されます。この表示も消す場合は、同じようにIF関数でC16の入力内容をチェックして表示するようにします。
セルD18 =IF(C16=””,””,DSUM(金銭出納帳,5,D15:D16))
セルD19 =IF(C16=””,””,DMAX(金銭出納帳,5,D15:D16))
セルD20 =IF(C16=””,””,DMIN(金銭出納帳,5,D15:D16))
【操作のまとめ】
●抽出条件の入力内容はVLOOKUP関数を使って番号入力で指定することができる
●抽出条件を空白にすると、データ範囲すべてが計算の対象になる