端数処理の関数を使い
希望の単位で切り上げたり、特定行に色をつける



数値の端数の処理をする関数

 数値の端数を処理する関数のなかには、計算結果を希望の桁数で算出する(ROUND関数など数値を丸める)関数だけではなく、希望の単位で算出する関数も用意されています。それが、今回ご紹介する、CEILING(シーリング)関数、FLOOR(フロア)関数、MROUND(エムラウンド)関数です。これらはすべて、「=(数値,基準値または倍数)」という書式からなります。

 希望の単位を基準値または倍数に指定することにより、CEILING関数は数値を基準値の倍数で切り上げ、FLOOR関数は切り捨て、MROUND関数は四捨五入して算出してくれるのです。


今回の関数




数学/三角関数
 エムオーディー 
 割り算の余りを求める
            
書 式 =MOD(数値,除数)
MOD関数は割算の余りを求めることができる。ここでは、「条件付き書式」のなかで使うことによって、5行ごとに色をつけてみた。何行かごとに色をつけることは表を見やすくする方法として有効なので習得しよう。


数学/三角関数
 シーリング 
 希望の基準値で切り上げる
 
書 式 =CEILING(数値,基準値または倍数)
シーリングとは「天井」のこと。指定された単位の上限まで数値を切り上げるときに使う。ここでは、G6からG9、G11からG14、G16からG19の各範囲の平均値を求め、その値を5000円単位で切り上げている。


数学/三角関数
 フロア 
 希望の基準値で切り捨てる
 
書 式 =FLOOR(数値,基準値または倍数)
フロアとは「床」のこと。指定された単位の下限まで数値を切り下げる。ここではCEILINGと同様、各商品の売上高の平均値を求め、その額を5000円単位で切り下げている。MROUNDを使うと希望の単位に四捨五入できる。



CEILING、FLOOR、MROUNDを使い分ける

CEILING(天井)ならば指定された単位の上限に切り上げ、FLOOR(床)ならば下限まで切り下げる。MROUNDは天井か床か近いほうの単位に数値を丸めることができる。たとえば、\187,569を\5,000単位で表示させる場合、CEILINGなら\190,000、FLOORなら\185,000、MROUNDでは\190,000となる。3つの関数を使い分けられるようになろう。

\190,000
\187,569
*たとえば基準値を「5000」と設定。\185,000から\190,000の間の数値は、CEILINGならすべて\190,000に、FLOORならすべて\185,000となる。MROUNDは四捨五入。\190,000か\185,000か近いほうにそろえられる。
\185,000


端数の処理に強くなろう

 では、この基準値または倍数を指定することで希望の単位で切り上げ、切り下げ、四捨五入する関数とは、どんなときに、どんな使い方をすれば有効なのでしょうか?これらの関数は、数値を何円単位でそろえたい、何分単位でそろえたいといったときに使います。たとえば、今月の売上額から来月の目標売上高を提示したいといった場合は、数値を細かく表示する必要はありません。そこで今月の実績額を5000円単位で切り上げて表示させるといったことが、端数を処理する関数で求めることができます。たとえば11万4450円を5000円単位で切り上げると、11万5000円となります。また、時給の計算などで、15分単位で切り下げて計算したいときにも有効です。労働時間が5時間12分ならば切り下げられ5時間、5時間17分ならば5時間15分とすることができます。

 「基準値を設定し希望の単位に切り上げたり、切り捨てる関数」といっても理解しづらいかもしれませんが、実際に利用例を見ると、いろいろな応用が利き、実際に適用できる機会も多いことがわかると思います。まずは、基準値で切り上げるCEILINGを例に、これらの関数の利用法を習得してみましょう。


数値を希望の単位で算出する

 数値を希望の単位で算出する方法をみてみましょう。希望の単位で切り上げるCEILING関数は切り下げるFLOOR関数や四捨五入するMROUND関数と書式が共通します。ここでは関数を組み合わせる「ネスト」も習得しましょう。


数値を5000円単位で切り上げる

▼目標売上高を、今月の平均値異常に設定する。ここでは今月の平均を5000円単位で切り上げて、来月の目標売上高とした。(1)目標売上高を設定したいセルG24を選択し、(2)[関数貼り付け]ボタンを押し、「関数の貼り付け」画面から(3)[数学/三角]、(4)[CEILING]を選択。(5)[OK]を押す。




▼平均を求めるにはAVERAGE関数でも設定できるが、ここではSUBTOTAL関数を利用する。(6)「数値」ボックスにカーソルを置いたまま、(7)「関数ボックス」横の[▼]ボタンを押し、(8)[その他の関数]を選択する。




▼「関数の貼り付け」画面が表示される。「関数の分類」で(9)[数学/三角]を選び、「関数名」で(10)[SUBTOTAL]関数を選択。(11)[OK]ボタンを押す。




▼SUBTOTAL関数の関数パレット画面が表示。(12)「集計方法」で「1」と設定(ここで、小計値を求めたい場合は「9」と設定)。(13)「範囲1」、(14)「範囲2」、(15)「範囲3」には各範囲を選択。ここでは[OK]を押さない。(16)数式バーの[CEILING]の部分をクリック。CEILING関数の数式パレットに戻る。




▼「基準値ボックス」に(17)「5000」と入力、(18)[OK]ボタンをクリック。5000円単位で数値を切り上げることができる。このように関数のなかに関数を設定して使うことをネストという。



G24のセルに入った式は
=CEILING(SUBTOTAL(1,G6:G9,G11:G14,G16:G19),5000)


知ッ得
関数の中に関数を入れ込むことを「ネスト(入れ子)」という。1つの関数にネストできる関数は7つまで。また、関数をネストしたあとは、数式バーを使うと簡単に元の数式パレットに戻ることができる。その際は、数式バーの数式のなかで、戻りたい数式の関数名の一部をクリックすると関数パレットが表示される。


「基準値」の設定方法を理解する

 希望の単位で切り上げる関数CEILING、切り下げるFLOOR、四捨五入するMROUNDはいずれも引数が(数値,基準値)となります。「数値」とは端数を処理する対象となる数値のことで、数値が入ったセルを通常指定します。「基準値」には、切り上げや切捨ての基準となる単位数を入れます。たとえば前ページで設定したように、5000円単位にしたい場合は「5000」と入れます。この入れた数値の倍数が数値を丸める基準となり、「5000」とすれば、次は「10000」「15000」・・・と5000円単位で丸める基準値が設定されるのです。ですから、基準値を「12」とすればその倍数の24、36、48・・・が単位となり、たとえば、「14」を基準値「12」でCEILINGすると「24」となり、FLOORすると「12」となります。実践してみてください。


希望の単位で切り捨てや四捨五入する 

次に希望の単位で数値を切り下げるFLOOR関数と希望の単位で四捨五入するMROUND関数の設定方法をみておきましょう。書式は、希望の単位で切り上げるCEILINGの「=(数値,基準値または倍数)」と同様です。


5000円単位で切り捨てるには

▼CEILINGでは希望の単位で切り上げたが、切り下げるにはFLOORを使う。ここでは売上の平均値を5000円単位で切り下げる。(1)答えを求めたいセルを選択、ツールバーの(2)[関数貼り付け]ボタンをクリックする。


▼書式はCEILINGと同じ。(3)「数値」には前ページと同様の手順でSUBTOTALを使いG6からG9、G11からG14、G16からG19範囲の平均値を求め、(4)「基準値」に「5000」と設定し、(5)[OK]を押す。



G25のセルに入った式は
=FLOOR(SUBTOTAL(1,G6:G9,G11:G14,G16:G19),5000)


5000円単位で四捨五入するには

▼四捨五入の場合はMROUND関数を使う。書式はCEILINGと同様。なお基準値とはその倍数を単位として設定するもの。たとえば基準値を「12」とすれば、ダース単位で数値を丸めることができるなど応用が利くので活用したい。



入った式は
=MROUND(SUBTOTAL(1,G6:G9,G11:G14,G16:G19),5000)


時刻を10分単位で切り上げてみよう

労働時間を求める場合などでは時間をある時間単位で切り上げることが多い。ここでは始業時間を午前9時とし、9時前に到着した場合は、9時始業となるようにした。また遅刻の場合は、10分単位で切り上げるようにし、たとえば、9時5分に到着した場合は9時10分からの始業となるようにした。式としては(1)D6のセルに(2)のような式が入っている。なお「TIME(0,10,0)」の部分は「"0:10"」としても答えが求められる。


知ッ得
MROUND関数はアドイン関数なので登録しなければ利用することができない。アドイン関数とは、関数の貼り付けダイアログのなかで初期設定では表示されない関数のこと。利用するには「分析ツール」というアドインを登録する。[ツール]→[アドイン]を選択、[分析ツール]にチェックを入れ、[OK]ボタンをクリックする。


割算の余りを求める関数

 端数を処理する関数には、ほかに割算の整数部を求めるQUOTIENT(クオーシェント)関数や割算の余りを求めるMOD関数などがあります。ここではMOD関数の利用例として、求めた余りを条件付き書式と組み合わせることで、特定の行ごとに色をつける設定を紹介しましょう。大量の数値が並ぶデータなどは1行ごとに色をつけることで見やすい表にできます。この方法を利用し、上の例のように5行ごとに色をつけるように設定したり、MOD関数で除数を2と設定することで偶数行に色をつけることができます。


関数で決まった行に色をつける 

端数を処理する関数には、ほかに割算の余りを求めるMOD関数などがあります。この関数を使った意外な利用例があるので紹介しましょう。ここでは、何行かごとに色をつけ、表を見やするする方法を紹介します。


割算の余りを利用して行に色をつける

▼MOD関数と「条件付き書式」を組み合わせることで、決めた行に色をつけることができる。ここでは各種類別に売上高の承継を求めている行が5行ごとなので、5で割った余りが0になる行に色をつけるように設定。まず(1)書式を設定したい範囲E6からG20を選択し、(2)[書式]メニューから(3)[条件付き書式]を選択する。




▼「条件」で(4)を押し、[数式が]にする。ここに「5で割った余りを求め、余りが0である場合」という条件式(5)「=MOD(ROUW(E6:E20),5)=0」を入れる。ROW関数は選択行が何行目かを求める関数。(6)[書式]を押し、選択行に入れる色を設定し、(7)[OK]ボタンをクリックする。


条件を削除するには

▼設定した条件付き書式を削除したい場合は、範囲選択し、[書式]→[条件付き書式]の(8)[削除]ボタンをクリック。(9)1つ目の条件なので[条件1]にチェックをいれ(10)[OK]ボタンをクリック、もう一度[OK]ボタンをクリックする。



オートフォーマットで簡単に色をつける

▼MOD関数を使い偶数行に色をつける場合は、除数を2にする。1行ごとに色をつける場合は、範囲を指定し、[書式]→[オートフォーマット]から(1)でサンプルを探し、(2)[リスト1]を選択し、[OK]を押す。フォントの色をそのままにしたい場合は、(3)オプションボタンをクリック、(4)[フォント]のチェックをはずす。




今回の表示形式
ある値以上の数字を赤色に変える

数値は表示形式を変更するだけで色をつけることもできる。ここでは、目標売上高の19万以上の値はフォントの色を赤に、それ以外は黒に設定。目的のセルで右クリック→[セルの書式設定]で(1)[書式表示]を選び、(2)[ユーザー定義]条件も[ ]で囲む。条件の区切りはセミコロン「:」を使う。