切り上げ、切り捨て、四捨五入

四捨五入

四捨五入はRound関数を使います。

=ROUND([セル名], [桁数])

桁数は小数点以下第何位で表示かを指定します。0を指定すると、整数での四捨五入になります。1を指定すると、小数点第2位で四捨五入を行い、小数第1位の値で表示されます。

切り捨て

切り捨てはRounddown関数を使います。

＝ROUNDDOWN([セル名], [桁数])

切り捨てになるだけで、使い方はRound関数に準じます。

切り上げ

切り上げはRoundup関数を使います。

=ROUNDUP([セル名], [桁数])

切り捨てになるだけで、使い方はRound関数に準じます。

INT関数

入力の仕方は

=INT([セル名])

または

＝INT([数値])

正の値の場合は、小数点を切り捨て、負の値の場合は小数点を切り上げます。小数点を切り捨てるだけのことがを何の役に立つのかと思うかもしれません。

例えば

15	10
23	20
38	30
32	30
45	40
53	50

というように、何十代で揃えたいとき、

=INT([セル名] / 10) * 10

と入力すれば実現できます。

資料作り以外にも

計算が必要になったとき、机の引出からわざわざ電卓を取り出して計算していないでしょうか。

提出資料用だけではもったいない

メモ用としても活用できます。電卓で計算した場合、計算の履歴は残りません。しかし、Excelではセルに数値を入力していき、合計の機能を使用すれば、計算ができ、かつそれぞれの値も明確になります。更に数値をちょっと変えて計算し直したい場合も対応できます。

数値からランク付けの方法2

Excelである数値が入力されており、その数値の範囲ごとにABCとランクをつけたいときの方法です。
例えば0～100までの値があるとき
80以上　　　　A
70以上79以下　B
60以上69以下　C
59以下　　　　D
というようにしたい場合の方法です。

数件のデータであれば、直接目で見てA、Bと手入力していけば済みますが、多数のデータが存在するとき都度手入力していては面倒になります。

以前、数値からランク付けの方法で、IF式を使った方法を紹介しましたが、今回はIF式を使わずに行ってみたいと思います。

IF式の欠点

IF式は条件を自由に設定できる利点はあるのですが、真のときの値と偽のときの値と分岐してしまうため、どうしても複雑になってしまう欠点があります。

数値を4種類に集約

IF式を使用しないで表示するには、0～100までの数値を4つの数値のどれかになるように変換してしまうことを考えます。

80以上　　　　3
70以上79以下　2
60以上69以下　1
59以下　　　　0

この4種類の数値で3→A、2→B、1→C、0→Dと変換することを目指します。

INT関数

INT関数は正の値のとき、小数点以下を切り捨てる関数です。

=INT(数値)

と入力します。元の数値を60で割った値にINT関数を適用すると、60以上は1にそれ未満は0となります。入力方法は以下のようにします。これで59以下は0に60以上は1に区別できました。

=INT([セルの値]/60)

次に70以上を2にする必要があります。元の数値を70で割った値にINT関数を適用すると70以上は1にそれ未満は0となります。これを先の値に加えます。

=INT([セルの値]/60)＋INT([セルの値]/70)

これで70以上は2になりました。さらに80以上を同様に加えます。

=INT([セルの値]/60)＋INT([セルの値]/70)＋INT([セルの値]/80)

これで80以上は3となり、4種類の数値に変換できました。

VLOOKUP関数の威力

いよいよ、ランクの表示をします。まずは邪魔にならないセルに以下のように表1を用意します。図ではE1セルからF4セルまで入力されています。

表1

3	a
2	b
1	c
0	d

図ではB列に点数の数値ランクが入力されています。C列に

＝VLOOKUP(B6, $E$1:$F$4, 2, 0)

と入力します。B6は検証するセルでどのセルに入っている値を変換するかという値です。$E$1:$F$4は参照範囲で、先に入力した表1のように入力した部分をドラッグして「F4」キーを押すと、範囲が指定されます。「F4」キーを押すのは「絶対参照」といって、入力後のセルをコピーした際に範囲がずれないようにする意味があります。3番目の「2」は表1の2列目を表示させるという意味で、最後の「0」は深く考えずにおまじないで0を入れると思ってください。

これでランク付けができるようになります。

数値からランク付けの方法

Excelである数値が入力されており、その数値の範囲ごとにABCとランクをつけたいときの方法です。
例えば
例えば0～100までの値があるとき

80以上　　　　A

70以上79以下　B
60以上69以下　C
59以下　　　　D
というようにしたい場合の方法です。

数件のデータであれば、直接目で見てA、Bと手入力していけば済みますが、多数のデータが存在するとき都度手入力していては面倒になります。

IF式による条件設定

条件を指定して、それに合うときと合わないときで表示を変えたいとき、Excelでは「IF式」という関数があります。IF式は

=IF([条件],[真のときの値],[偽のときの値])

という入力方法です。条件が成り立つときは「真のときの値」が表示され、成り立たないとき「偽のときの値」が表示されます。この部分の値に文字を表示させるときは、ダブルクォーテーション「"」で囲みます。「A」と表示させたいときは"A"と打ち込みます。

条件の書き方としては、「80以上」の場合、単純に

[セル名]>=80

となります。したがって、80以上でAと表示し、そうでないときBと表示させたいならば、

=IF([セル名]>=80, "A", "B")

と入力すれば良いです。

「70以上79以下」これは、「70以上かつ79以下」という意味なので、「かつ」という条件を示す必要があります。これは「AND」という式を使います。

AND([セル名]>=70, [セル名]<=79)

と入力します。

IF式の入れ子は複雑になる

最初の例を表示させるためには、これを繰り返せば良いのですが、

＝IF([80以上], "A", IF([70以上79以下]), "B", IF([60以上69以下], "C", IF([59以下], "D", ""))))

のように入力していくと(条件は簡略化して表示してある）、式が複雑になってしまい、後々見たとき、どのように書き込んだのかわからなくなってしまいます。IF式はあまり入れ子にしない方が得策です。

そこで、4つの条件をすべてバラバラにし、それぞれの条件に合わない場合の表示を、空の文字列「""」とします。

1) IF(A1>=80,"A","")
2) IF(AND(A1>=70, A1<=79),"B","")
3) IF(AND(A1>=60, A1<=69),"C","")
4) IF(A1<=59,"D","")

という1)～4)の式4つを用意します。1)では80以上では「A」と表示されますが、それ以外では空の文字になります。同様に2)では70以上79以下で「B」とそれ以外では空の文字に3)以下同様。

文字をつなぎ合わせて表示

文字をつなぎ合わせる場合、「&」という記号を使います。例えば、別々のセルに「X」、「Y」、「Z」と表示されていて、これをくっつけたいとします。

＝[セル名1]&[セル名2]&[セル名3]

と入力します。例えば更に後ろに「W」を直接つなぎたいときは、

＝[セル名1]&[セル名2]&[セル名3]&"W"

と入力すれば良いです。

ランク付けの表示から離れてしまったように感じますが、この方法が実は生きてきます。つまり、「A」と表示させたい場合、「A」以外の条件は満たさないので全て空""となります。「"A"&""&""&""」を表示させるとやはり「A」となります。つまり、先の1)～4)の条件式を「&」でつないでやれば、AからDのどれか1文字だけが表示されるようになります。

= IF(A1>=80,"A","") & IF(AND(A1>=70, A1<=79),"B","") & IF(AND(A1>=60, A1<=69),"C","") & IF(A1<=59,"D","")

というように入力すれば、ランクがきれいに表示されます。

Excelで入力した数値を0.5刻みに直す

よく計算式を使って出てきた結果を1、1.5、2、2.5・・・と0.5刻みで表現したいことがあると思います。

例えば

1.2333→1.5

1.7867→2

というように。数件くらいであれば、目で見て直せば済むことですが、数十件、百件とデータがあればこれだけでも相当時間がかかってしまいます。うまく関数を使って効率化を図っていくことを考えます。

四捨五入はRound関数

Excelでは数値の丸めにはRound関数というのもありますが、これをこのまま使っても、指定した桁の次の桁で四捨五入（RoundUpは切り上げ、RoundDownでは切り捨て）するので、0.5刻みとはなりません。

Round関数をそのまま使うと、
1.2333→1
1.7867→2
となってしまいます。

ではどうするか？

掛けて割る

思いきって、2を掛けます。そして2を掛けた値で丸め（切り上げなら、RoundUp、切り捨てならRoundDown、四捨五入ならRound）を0桁（第二引数(コンマの後ろに入れる数)に指定、０を指定すると小数第一位で処理される）で行います。その値を、更に2で割ります。丸めを行った時点で値が整数になるので、２で割った値は必ず整数か○.5のいずれかになります。Excelに書く場合、

=Roundup([セル名]*2, 0)/2

と打ち込みます。

図　セルA1の値を0.5刻みにする例

というように記述します。

Excelの操作が面倒？

業務でExcelを使って資料を作成したりといったことは、皆さんも経験あることと思います。

Excelには様々な関数が存在しており、それをうまく活用することでかなりの効率化が図れます。ただ、関数という言葉から敬遠する人も多いと思います。

このブログではあまりアカデミックにならないよう、手間がかかる入力をちょっとした工夫をすることで、ちょっとだけ入力の手間を省くアイデアを紹介していけたらと思います。

Excelの機能は使い方次第

Excelを使っていて、同じことを何度も入力したり、手入力は避けようと数式で入れたは良いけれど、式が複雑になってしまって混乱したりといったことはよくあると思います。

Excelの関数は便利ですが、入力の仕方によっては複雑になってしまい、かえって非効率になってしまいます。

私が今までデスクワークで使ってきた手法が少しでもお手伝いになればと思い、ブログに残すことにしました。

一つ一つはたいした工夫ではないものであっても、組み合わせて使うことで結構便利に、効率化が図れるものです。

マクロは使わない前提

マクロをご存じない方のために。Excelにはマクロという機能があります。同じ処理を何度も繰り返す場合、その操作を記録しておき、その記録を実行すれば、一度の操作で処理を繰り返し使えます。また、データの処理は書式の設定などをVBAという、ある種のプログラミングコードを書いて記録しておき、それを実行する方法もあります。

マクロは非常に便利で、効率化が図れますが、このブログでは取り上げないことにします。

使い方より

入力した値を0.5刻みに直す
数値からランク付けの方法
数値からランク付けの方法2
資料作り以外にも

関数より

INT関数
切り上げ、切り捨て、四捨五入