◆Excel~関数 Vol.2~◆
第6回 Excel~関数 Vol.2~
1.関数を使って、合計や平均の求める方法
関数を用いることにより、複雑な計算が可能になる。
数式の文法(その2:関数)
関数は、 sum( ) のように関数名と小括弧で構成される。
関数名は大文字でも小文字でもよい。
関数の ( ) 内に記述する内容を「引数(ひきすう)」と言う。関数によって、引数の種類や数が決まっている。
複数の引数を指定する場合は、カンマ( , )で区切る。
連続したセル範囲を指定する場合は、A5:A10 のように始点のセルと終点のセルをコロン( : )で区切る。
セルまたはセル範囲の指定は、そのセルをクリックまたはドラッグで指定することもできる。
次のデータを例に、合計や平均を計算する。
A3セルにA1~E1の合計を求める。
A3セルに =sum(a1:e1) と記入する。半角ならば大文字でも小文字でもよい。 a1:e1 は「a1~e1の範囲」を表している。
注意
=sum(a1,d1) のようにすると、a1とd1の合計( =a1+d1 )になってしまう。
=sum(a1,b1,c1,d1,e1) とすることもできるが、冗長な表現はなるべく避ける。
B3セルにA1~E1の平均(算術平均)を求める。
B3セルに =average(a1:e1) と記入する。average は算術平均を求める関数である。
同様に、C3セルに中央値、D3セルに最小値、E3セルに最大値、F3セルにデータ数を求めよ。(次節参照)
この程度のデータ数ならば関数を用いなくても答が出せると思うだろう。 しかし、これが千件、1万件ともなれば目視で見つけることは無駄な作業と言える。 データ数が違っても関数の使い方は変わらないわけだから、関数を使う習慣を身につけることが大切である。
2.よく使う関数
関数
SQRT
SUM
AVERAGE
MAX
MIN
MEDIAN
STDEV
STDEVP
ROUND
ROUNDUP
ROUNDDOWN
INT
COUNT
COUNTA
COUNTIF
IF
RANK
機能
平方根を求める。
合計を求める。
平均(算術平均)を求める。空白セルは除外される。
最大値を求める。
最小値を求める。
中央値を求める。
標準偏差(n-1で割る方、標本調査で用いる)を求める。
標準偏差(nで割る方、全数調査で用いる)を求める。
指定桁で丸め処理(四捨五入、切り上げ、切り捨て)。
例:=ROUND(A1,0)
「セルの書式設定→表示形式→数値→小数点以下の桁数」と併用しないように注意すること。
丸め処理が重複して正しくない結果が出る可能性がある。
セルの書式設定で指定した場合は、表示上四捨五入されるだけであり、値は四捨五入されていない。 一方、ROUND関数を使うと、値そのものが四捨五入される。 つまり、この値を参照して計算すると、以降の計算結果が違ってくる。
整数化。もとの値を超えない最大の整数になる。負数のとき注意。
COUNT ()内に含まれる数値の個数を求める関数です。
(例1) =COUNT(1,5,29) シート全体の中で()内の数値をもつものの個数。
(例2) =COUNT(4/2,5*3,2^3) シート全体の中で()内それぞれの計算結果の数値をもつものの個数。
(例3) =COUNT(B1:D10) B1からB6の数値の個数。…文字が入力されているセルは数えられないことに注意。
COUNTA ()内に含まれるデータ(文字、数値)の個数を求める関数です。
(例4) =COUNTA(1,5,29) ()内のデータの個数。…どこも参照していないことに注意。(例1)と比較せよ。
(例5) =COUNTA(B1:D10) B1からB6のデータの個数。…数値も文字も対象となっている。(例3)と比較せよ。
※ COUNT関数とCOUNTA関数とを組み合わせることで文字の個数を求めることができます。
指定した条件を満たしたデータの数を数える。
(例1) =COUNTIF(A1:A10,"<=10") A1~A10の中で10以下の数値データをもつものの個数。
(例2) =COUNTIF(A1:A10,"<10") A1~A10の中で10未満の数値データをもつものの個数。
(例3) =COUNTIF(B1:D10,71) B1~D10の中で数値が71である数値データをもつものの個数。…単一の数字のときは等号を省略できる。
指定した条件の時だけ、特定の処理をする。
(例1) =IF(A1=0,A1+4,A1*2) A1が0ならば、A1+4を実行する。そうでなければ A1*2を実行する。
(例2) =IF(A1=0,"上","下") A1が0ならば、「上」を表示する。そうでなければ 「下」を表示する。
順位を求める。
3.問題
1. 「Excel~数式~」ファイルを開き、人口、世帯数、面積の各項目について、それぞれ、2番目に大きい数、2番目に小さい数を求めよ。
2. 次のデータは、ある高校の3教科の成績データである。 ( ※ 一部前回の授業で作成済み )
(1) データをExcelに入力しなさい。( 新しい Sheet に記入すること) (前回済み)
(2) それぞれの生徒の3教科の合計点数を求めよ。 (前回済み)
(3) この設問は、“if”関数を用いること。
(ⅰ) (2)の結果を用いて、240点以上なら“合格”、それ以外なら“保留”と評価1欄に記述せよ。 (前回済み)
(ⅱ) (2)の結果を用いて、240点以上なら“A”、それ以外200点以上なら“B”、それら以外なら“C”と評価2欄に記述せよ。 (新)
(4) 条件付き書式を用いて、先の (3) (ⅱ) の結果得られた”A”,”B”,”C”に応じて色を付けよ。 (挑戦問題)