Excel講座

数学関数

本章で用いる実習用ファイル

SUMやAVERAGEと同類の、しかし少しマイナーな関数群の紹介。一見あまり縁がなさそうに見える関数もちょっとしたところで便利に使えたりする。特にINT関数、MOD関数は必須。

絶対値:ABS(数値)

ABS関数は数値の絶対値(負の値の時は符号が反転して正の値になる)を返す関数である。

IF(数値>0, 数値, -1*数値)と同じ。

ABC
1数値絶対値
210=ABS(A2)
310.510.5
4-10.510.5
5

(正負)符号:SIGN(数値)

SIGN関数は数値の符号を返す(正:1, 負:-1)関数である。

IF(数値>0, 1, IF(数値 = 0, 0, -1))と同じ。

ABC
1数値正負
210=SIGN(A2)
310.51
4-10.5-1
5
ABS関数とSIGN関数の組み合わせ例
ABC
1数値数値
210=SIGN(A2)*ABS(A2)
310.510.5
4-10.5-10.5
5

元に戻るだけだから、さしあたりは意味が無い…が

平方根:SQRT(数値)

SQRT関数は数値の平方根を返す関数である。

ABC
1数値平方根
22=SQRT(A2)
331.732050808
4-5#NUM!
5

数値を丸める:ROUND(数値,桁数), ROUNDUP(数値,桁数), ROUNDDOWN(数値,桁数)

  • ROUND関数は数値を桁数に四捨五入する。

  • ROUNDUP関数は数値を桁数に切り上げる。

  • ROUND関数は数値を桁数に切り捨てる。

桁数意味例
……
2小数点第2位123.45
1小数点第1位123.4
0整数に丸める123
-110の位120
-2100の位100
……
ABCDE
11234.567
2
3丸める桁数四捨五入切り捨て切り上げ
4整数=ROUND(A1,0)=ROUNDDOWN(A1,0)=ROUNDUP(A1,0)
5小数点第1位=ROUND(A1,1)=ROUNDDOWN(A1,1)=ROUNDUP(A1,1)
610の位=ROUND(A1,-1)=ROUNDDOWN(A1,-1)=ROUNDUP(A1,-1)
7
ABCDE
11234.567
2
3丸める桁数四捨五入切り捨て切り上げ
4整数123512341235
5小数点第1位1234.61234.51234.6
610の位123012301240
7

整数に丸める:INT(数値),TRUNC(数値)

INT関数・TRUNC関数は数値を整数にする。小数点以下は切り捨てる。

INT関数
数値が負の値のときにはその数値を超えない整数にする。
TRUNC関数
整数部を切り捨てる
ABCD
1数値INTTRUNC
22.2=INT(A2)=TRUNC(A2)
31.111
40.600
5-0.7-10
6-1.4-2-1
7-2.2-3-2
8
INT関数は1方向(無限大方向)

-3           -2           -1           0           1           2           3 

        -2.2        -1.4      -0.7          0.6      1.1          2.2

TRUNC関数は0を中心(ROUNDDOWN関数と同じ)

-3           -2           -1           0           1           2           3 

        -2.2        -1.4      -0.7          0.6      1.1          2.2

ROUND関数とINT関数

INT関数と簡単な四則演算でROUND-関数と同等のことができる。

ABCDE
11234.567
2
3丸める桁数四捨五入切り捨て切り上げ
4整数=INT(A1+0.5)=INT(A1)=-INT(-A1)
5小数点第1位=INT(A1*10+0.5)/10=INT(A1*10)/10=-INT(-A1*10)/10
610の位=INT(A1/10+0.5)*10=INT(A1/10)*10=-INT(-A1/10)*10
7
ABCDE
11234.567
2
3丸める桁数四捨五入切り捨て切り上げ
4整数123512341235
5小数点第1位1234.61234.51234.6
610の位123012301240
7

負の値のときはROUND関数とは同じ結果にはならない。

ABCDE
1-1234.567
2
3使用関数四捨五入切り捨て切り上げ
4INT=INT(A1+0.5)=INT(A1)=-INT(-A1)
5ROUND=ROUND(A1,0)=ROUNDDOWN(A1,0)=ROUNDUP(A1,0)
6
ABCDE
1-1234.567
2
3使用関数四捨五入切り捨て切り上げ
4INT-1235-1235-1234
5ROUND-1235-1234-1235
6

課題:INTとROUND(xlsx形式)

ROUND-関数を用いず、負の値のときにROUND-関数と同じ結果になるようにINT関数・ABS関数・SIGN関数を用いて式を立てよ。

ABCDE
1-1234.567
2
3使用関数四捨五入切り捨て切り上げ
4INT(0基準)-1235-1234-1235
5

課題:購入品(xlsx形式)

下表を完成させよ。

ABCDEFGH
1購入品目本体価格消費税支払額ポイント税率
2ThinkPaa T1516799910%
3IdeaVision M1454999549960490550
4Type-Cドック29799297932770300
5VT510 Tower147590147591623401480
6IMAGEFORCE19880198821860200
7Logic MX30014999149916490150
8合計4787404360
9
支払額
10円未満は切り捨て
ポイント
本体価格に対して1%で1ポイント。ただし10ポイント未満は切り上げ

除算した余り:MOD(数値, 除数)

MOD関数は数値を除数で割った余りを返す関数である。

より正確には 「数値ー商(INT(数値/除数))×除数」がMOD関数の戻り値「余り」である。

ABCDE
1数値除数商余り
252=INT(A2/B2)=MOD(A2,B2)
32000年9月3日752531
45.51.431.3
5-52-31
6

課題:閏年(xlsx形式)

表に西暦年を入力したら閏年判定をするように式を立てよ。

セルA2に入力された値(西暦)が閏年ならセルB2に「閏年」と表示し、閏年以外または入力前は空白のままにせよ。

閏年の定義
4で割りきれる年は閏年、ただし100で割り切れても400で割りきれない年は平年
閏年判定
ABC
1西暦判定
22024
3

順位:RANK.EQ(数値, 範囲, 順序), 順位:RANK.AVG(数値, 範囲, 順序)

RANK.EQは範囲の中から数値の順位を返す関数である。

  • 順序が0の時は降順
  • 順序が1の時は昇順

同順のときには順位の最上位の数値(整数)を返す。

RANK.EQ
ABCD
1点数順位
2山岸90=RANK.EQ(B2,$B$2:$B$10,0)
3小片952
4新沼787
5谷本689
6岸本971
7浅倉952
8小野835
9小野田728
10秋山796
11

RANK.AVGは範囲の中から数値の順位を返す関数である。

  • 順序が0の時は降順
  • 順序が1の時は昇順

同順のときには順位の平均を返す。

RANK.AVG
ABCD
1点数順位
2山岸90=RANK.AVG(B2,$B$2:$B$10,0)
3小片952.5
4新沼787
5谷本689
6岸本971
7浅倉952.5
8小野835
9小野田728
10秋山796
11

課題:スポーツテスト(xlsx形式)

スポーツテストの結果から、両競技の順位を付けたい。好成績のものが上位になるようにし、同順はその中の最上位の順位にせよ。

ABCDEF
1幅跳び順位50m走順位
2山岸3.677.5
3小片4.1227.03
4新沼3.8758.06
5谷本2.6998.58
6岸本3.5676.91
7浅倉4.0337.34
8小野4.3116.91
9小野田3.9448.37
10秋山2.96810.79
11

課題:自然数反転(xlsx形式)

セルA2に9桁までの自然数を入力すると、その桁を反転させた自然数を生成したい。表では最終的にはC列に目的の自然数が生成する。この表を完成させよ。

ABCDE
110で割る余りD列に余りを1桁目に加える10を掛ける
212345
3
4
5
6
7
8
9
10
11
12
完成したときA2に「12345」を入力した場合
ABCDE
110で割る余りD列に余りを1桁目に加える10を掛ける
212345
312345550
4123454540
51235435430
612543254320
70154321
800
900
1000
1100
12

C7に目的の自然数が生成された。