定時で上がろう! Excel関数の底力 第31回 「マイナスの数値」を関数で丸めるときの注意点

今回は、数値データを「5単位」とか「10単位」とかに丸める方法を紹介していこう。ただし、単純に「FLOORやCEILINGで数値を丸める」という話ではない。今回の連載は「マイナスの数値をどのように丸めるか？」がポイントとなる。こういったケースにも対応できるように「FLOOR.MATH」や「CEILING.MATH」といった関数の使い方も覚えておこう。
数値を丸める関数（FLOOR、CEILING）

Excelには、数値を丸めるときに利用できる関数として「FLOOR」や「CEILING」といった関数が用意されている。ただし、数値データにマイナスの値が含まれている場合は、思い通りの結果を得られないケースもある。このような場合に備えて、「FLOOR.MATH」や「CEILING.MATH」といった関数の使い方も覚えておく必要がある。

まずは、一般的な数値の丸め方について解説していこう。以下の図は、「ある商品の実売価格」をA～Tの20店舗で調査したものだ。ただし、この表を漠然と眺めていても傾向はつかめない。

このように乱雑な数値データを扱うときは、各データを一定の範囲にグループ分けするのが一つの効果的な手法といえる。たとえば、2,400円台、2,500円台、2,600円台、・・・という具合に「100円単位」のグループに分類するとデータを処理しやすくなる。

このような場合に活用できるのが「関数FLOOR」や「関数CEILING」だ。関数FLOORは「切り捨て」、関数CEILINGは「切り上げ」により、「指定した単位」に数値を丸めてくれる関数となる。

◆関数FLOORの書式
　=FLOOR(数値, 基準値)

◆関数CEILINGの書式
　=CEILING(数値, 基準値)

具体的な例で見ていこう。たとえば、先ほどの価格調査のデータを100円単位に丸めたいときは、第2引数に「100」を指定すればよい。今回は、関数FLOORで100未満を「切り捨て」した数値に丸めてみよう。

入力した関数をオートフィルでコピーすると、以下の図のような結果が得られる。それぞれの価格データを「2,400円台」、「2,500円台」、「2,600円台」、・・・という具合にグループ分けできているのを確認できるだろう。

あとは、「xx円台」のデータがそれぞれ何個あるかをカウントするだけ。この処理を行うために、以下の図のような表を作成する。

続いて、関数COUNTIFで「xx円台」のデータがそれぞれ何個あるかをカウントしていく。今回は関数をオートフィルでコピーできるように、第1引数（セル範囲）を絶対参照で指定した。第2引数（条件）には「2,400」などの数値をセル参照で指定すればよい。こちらは普通に相対参照で指定する。

関数COUNTIFをオートフィルでコピーすると、「xx円台」の数値データが何個あるかを集計した表が完成する。検算用に関数SUMで「合計」も求めておこう。

この表をもとにグラフを作成すると、データの分布状況を分かりやすく示すことができる。今回の例では、以下の図のようなグラフが作成された。

この結果をみると、2,500～2,600円台の価格で商品を販売している店舗が多いことがわかる。続いて、2,700円台、2,800円台という価格の頻度が高くなっている。

このように関数FLOORで数値を丸めると、乱雑なデータをグループ分けして、それぞれの頻度を示すグラフ（ヒストグラム）を作成できるようになる。Excelには、ヒストグラムを手軽に作成する機能も用意されているが、それを関数で処理する方法として覚えておいても損はないだろう。

なお、「関数FLOOR」と「関数CEILING」については、本連載の第12回でも活用例を紹介している。気になる方はあわせて参照しておくとよいだろう。こちらの連載では「時間」を10分単位や30分単位に丸める方法を紹介している。
マイナスの数値を丸めたときの懸念事項

続いては、丸める数値に「マイナスの値」が含まれる場合について検討していこう。以下の図は、前回と同じ調査を1ヶ月後にも実施し、実売価格の増減率についてまとめたものだ。こちらは、実売価格が「値下がり傾向にあるのか？」、それとも「値上がり傾向にあるのか？」を調べるためのデータとなる。

ただし、「増減率」の数値は乱雑な値になっているため、漠然と表を眺めていても傾向はつかめない。そこで「2%単位」（0.02単位）に数値を丸めてみよう。今回は、関数CEILINGを使って「切り上げ」により数値を丸めてみる。

この関数をオートフィルでコピーすると、以下の図のような結果が得られる。一見すると「2%単位」にデータをグループ分けできているように見えるが、これは正しい分類の仕方といえるだろうか？　増減率が「プラス2％以内」と「マイナス2％以内」のデータに注目してみよう。

今回の例では関数CEILINGにより2％単位に「切り上げ」しているため、増減率が「プラス2％以内」のデータは「2％」として処理されることになる。一方、「マイナス2％以内」は「0％」として処理されることになる。

同じ「2％以内」の変動なのに、一方は「2％」、もう一方は「0％」として処理する、というのは変な話ではないだろうか？

こういった増減率を調べるときは、「0を基準にどれくらい離れているか？」で判断するのが基本だ。よって、単純な「切り捨て」や「切り上げ」によりグループ化するのは理想的な処理方法といえない。0を基点にグループ化を行うべきだ。
「関数FLOOR.MATH」と「関数CEILING.MATH」の活用

このように0を基点に「切り捨て」や「切り上げ」を行いたいときは、「FLOOR.MATH」や「CEILING.MATH」といった関数を使用する必要がある。これらの関数は、第3引数（モード）で「切り捨て」や「切り上げ」の処理方法を指定できるようになっている。

まずは、「切り捨て」する場合の関数FLOOR.MATHについて書式を紹介していこう。

◆関数FLOOR.MATHの書式
　=FLOOR.MATH(数値, [基準値], [モード])

モード（第3引数）には「0」または「1」の数値を指定する。モードに「0」を指定した場合は、関数FLOORと同じ処理になる。一方、モードに「1」を指定すると、「0に近づくように」に数値が丸められる仕様になっている。

以下に、簡単な例を示しておこう。切り捨てる前の数値データが「正の値」や0（ゼロ）であった場合は、どの関数（モード）を使用しても同じ結果になる。一方、「負の値」であった場合は、「関数FLOOR.MATHのモード1」だけ異なる結果になる。この場合は「0に近づくように」に数値が丸められている。

「切り上げ」を行う関数CEILING.MATHも基本的な考え方は同じだ。

◆関数CEILING.MATHの書式
　=CEILING.MATH(数値, [基準値], [モード])

こちらもモード（第3引数）に「0」または「1」の数値を指定する。モードに「0」を指定した場合は、関数CEILINGと同じ処理になる。一方、モードに「1」を指定すると、「0から遠ざかるように」に数値が丸められる。

先ほどと同様に、簡単な例を紹介しておこう。「関数CEILING.MATHのモード1」だけ異なる結果になっていることを確認できるだろう。

いずれの関数も「正の値」を丸める場合は同じ結果になる。差が生じるのは「負の値」を丸めるときだ。

これまでの話をまとめておこう。それぞれの関数における“数値データの丸め方”は、以下のようになる。

◆FLOOR、FLOOR.MATH（モード0）
　「小さい数値」に切り捨てて丸める

◆CEILING、CEILING.MATH（モード0）
　「大きい数値」に切り上げて丸める

◆FLOOR.MATH（モード1）
　「0に近い数値」に丸める

◆CEILING.MATH（モード1）
　「0から遠い数値」に丸める
プラス・マイナスが混在する数値を丸めて処理する

話を元に戻して、実売価格の「増減率」をデータ処理していこう。0を基点に数値データを丸めるときは、「関数CEILING.MATHのモード1」を利用するとよい。今回の例では「2％単位」に数値を丸めるので、第2引数に「0.02」、第3引数に「1」を指定する。

この関数をオートフィルでコピーすると、以下の図のような結果が得られる。

変動なしの0%は「0％」、プラス2％以内のデータは「2％」、マイナス2％以内のデータは「-2％」という具合に、0を基点に数値データが丸められているのを確認できるだろう。変動率が2％より大きい場合も同様に、2％刻みで分類されていくことになる。

以降の作業は、先ほど解説した手順と同じだ。それぞれのグループに「データが何個あるか？」を関数COUNTIFでカウントしていく。

今回は、増減率がマイナスの場合（値下げ）、0の場合（価格変動なし）、プラスの場合（値上げ）について合計も算出してみた。

この結果を見ると、20店舗中の中で「値下げ」した店舗は12店、「変動なし」は4店、「値上げ」したのは4店であることを確認できる。全体的に見て「値下げ」の傾向が強いといえるだろう。

「頻度」のデータをもとにグラフを作成すると、より状況が鮮明になる。

最も頻度が多いのは、6％程度の「値下げ」を行った店舗だ。0％（価格変動なし）の店舗もそれなりにあるが、全体的に見ると「値下げ」している店舗が多い。その一方で「値上げ」した店舗も少しだけ存在している。

このようにデータ分布の傾向を把握したいときに、「関数FLOOR」や「関数CEILING」が役に立つ。0を基点に増減の分布を調べたいときは「関数FLOOR.MATH」や「関数CEILING.MATH」の使い方も覚えておく必要があるだろう。乱雑なデータを統計処理する方法の一つとして参考にして頂ければ幸いだ。