2014年11月23日

端数処理で値が変わる!?(表示桁数で計算する) /Excel

Excelで集計をしていて、少数点以下の桁数が多いと見難いから、セルの書式設定で表示形式を変えて小数点以下を表示させないようにしたり、よくあるこだけど、その合計を計算すると、Excelで計算した結果と、表示されているセルの値を電卓などで手計算すると合わないことがあります。
何でだ!?

こんな、業務日報があって、作業時間が“分”で表示されています。作業者の時間単価を60で割って分単価を求めて、業務時間と分単価を掛けてその業務コストを集計しています。
分単価が割切れない数値になっているため、業務コストも小数点以下が続く値があります。
一番右の列は、業務コストを“ROUND関数”で小数点以下で四捨五入して整数に丸めています。
20141123_201.JPG

ここで、一番下の行の合計を見ると、小数点以下が表示されている“業務コスト”の合計は、“46,547.33”、四捨五入した方は“46,545”と“1.7円”ほど異なっている。。
ちなみに、四捨五入したセルはこの様な式が入力されている。
20141123_202.JPG

小数点以下が表示されている“業務コスト”のセルを「セルの書式設定」で「通貨」に設定します。これで、小数点以下で四捨五入されて整数で表示されます。
20141123_203.JPG

これで、“業務コスト”のセルも隣の“四捨五入”のセルも同じ値が表示されました。
がー、合計は変わりなく、依然として“1.7円”ほどの違いがあります。
20141123_204.JPG
こうなると、表示されているセルの値を電卓で足していっても、集計された合計値とは合わなくなってしまいます。
これは、書式設定で小数点以下は四捨五入されて整数での表示になっていますが、セルの中身は変わっていないからです。

表示桁数で計算する
エクセルではこういった場合の誤差を無くすためにセルの「表示桁数で計算する」という機能があります。
左上のExcelのボタンを押します。
20141123_205.JPG

そこから「Excelのオプション」を選択します。
20141123_206.JPG

「詳細設定」から「次のブックを計算するとき」で「表示桁数で計算する」にチェックを入れます。
20141123_207.JPG

すると、こんなメッセージが表示されますが「OK」して続行します。
20141123_208.JPG

すると、合計金額が「四捨五入」の合計と一致しました。
20141123_209.JPG

“分単価”も、小数点以下が続いているので、見易いように小数点以下 2桁で丸めてみます。
20141123_210.JPG

すると、合計金額も四捨五入で切上げられたため、“46,545”だったのが“46,558”に“13円”増加しました。
20141123_211.JPG

と、このように、セルの書式設定によって見かけ上の数値の小数点以下は自由に丸めることが出来ますが、実際の計算はセルの中の値で行われるため、書式設定による表示の影響は受けません。
また、「表示桁数で計算する」は非常に便利な機能にも思いますが、セルの書式設定によって計算結果が変わってしまうのは人為的なミスが起きる可能性があると思います。
と言うことで、数値を丸める場合は見せかけに頼ることなく、ハッキリとセルの中身の値そのものを丸めてしまいましょう!



バックナンバー


posted by haku1569 at 23:02| Excelデータ分析 | このブログの読者になる | 更新情報をチェックする

2014年09月13日

パレート図を画いてみた(パレート分析)/データ分析/Excel

第1弾!ABC分析(パレート分析)について分かったところで!?
第2弾はExcelでパレート図を画いてみましょう。(グラフを作ります)
20140906_00パレート図.jpg

食料品を扱う問屋(卸売業者)の受注データから、2013年の1年間に受注した商品(アイテム)と受注金額(値)でパレート図を画いてみます。
元データはこんなデータです。
20140913_01.JPG
受注データは1日毎に受注の有った、得意先や商品名、数量等のデータになっています。
なので、同じ日の中に異なる得意先から同じ商品を受注することもあり、商品は重複したデータになっています。

パレート図に使う元データは重複の無い商品(アイテム)と金額(値)のデータが必要になりますから、この受注データからそのようなデータをまず集計する必要があります。
方法は、いろいろあると思いますが、簡単な方法は“ピボットテーブル”による集計です。その方法は第3弾!“ピボットテーブルでパレート図を画いてみた”で説明しますね。
んで、商品と金額のデータはこのようになります。
20140913_02.JPG
例えば、家計簿のデータだったら、1年間か、1ヶ月など期間を区切って日々のデータから買物をした品名等の支出の項目(アイテム)と金額のデータを集計しておく必要があります。

ちなみに、商品(アイテム)の数は119種類あります。
これを、金額の大きい順(降順)に並べ替えておきます。
それから、金額の一番下に合計金額を“SUM関数”を使って求めておきます。2013年の受注金額の合計は\10,561,900でした。

まず最初に“累積金額”を計算していきます。
一番最初の“讃岐めん”の累積金額は“金額”(\740,000)をそのまま持ってきて、2番目の“北海道昆布”から下は一つ上のセルの累積金額に金額を足していきます。
“北海道昆布”の累積金額は“\740,000+\528,000=\1,268,000”になります。
20140913_03.JPG

この式を一番下までコピーします。
すると、一番下の累積金額は予め計算しておいた合計金額を同じになります。
20140913_04.JPG

次に累積構成比を計算します。
これは、合計金額に対する累積金額の割合(構成比率)のことです。
“累積金額”を“合計金額”で割ります。
セルにはこんな式を入力します。
20140913_05.JPG
ここで、“合計金額”のセルを“$C$123”と“$”マークを付けているのは、このセルを一番下までコピーする時に“合計金額”のセルを絶対位置にさせておくために付けます。

一番下まで、コピーして表示形式を“%”にしておきます。
ちなみに、一番下の“ローカロリー牛乳”の累積金額は合計金額と同じですから、累積構成比は100%になります。
20140913_06.JPG

さ、これでパレート図(グラフ)を画く元データは完了です。
このデータからだけでも、累積構成比が80%の時のアイテム数がいくつあるかが分かりますから、総アイテム数119個の20%になっているかどうか、つまりパレートの法則(80-20の法則)に則っているかどうかを確認することができますね。

では、グラフを画いていきましょう。
ここからは、グラフ作成の説明ですよ。。

まず、グラフにするデータを範囲選択します。
パレート図はアイテムの金額(棒グラフ)と累積構成比(折れ線グラフ)の2つのデータで、“累積金額”はグラフのデータに不要なので、まず最初にセル“B3〜C122”を範囲選択したあと「Ctrl」キーを押して、次にセル“E3〜E122”を範囲選択します。
すると、選択範囲がグレーになります。
20140913_07.JPG

次に、「挿入」、「グラフ」、「縦棒」から「集合縦棒」を選択します。
20140913_08.JPG

すると、サクッとグラフができました。
累積構成比は“1”以下の値ですから、この縦軸“\0〜\800,00”では下の方にへばりついてほとんど見えませんね。。。
20140913_09.JPG

次に、累積構成比を“第2軸”にして%表示とします。
グラフの中でクリックしておいて「グラフツール」、「書式」、「現在の選択範囲」から「系列“累積構成比”」を選択します。
20140913_10.JPG

そして「選択対象の書式設定」を選択します。
20140913_11.JPG

「データ系列の書式設定」画面がひらきます。
ここで、「使用する軸」を「第2軸」を選択します。
20140913_12.JPG

「閉じる」を押すと“累積構成比”が赤い棒グラフで出現しました!
右側に第2軸が現れれて“0%〜120%”になっています。
これを、折れ線グラフにしなくちゃならないので、赤の棒グラフの上をクリックします。
すると所々に○が表示されて選択されたことが分かります。
その上で右くりし、「系列グラフの種類の変更」を選択します。
20140913_13.JPG

「グラフの種類の変更」画面が開きます。
「折れ線」を選択します。
20140913_14.JPG

「OK」を選択します。
んで、ついでにグラフの右側に表示されていた“凡例”も消しちゃいましょう。
「グラフツール」、「レイアウト」、「ラベル」、「凡例」から「なし」を選択します。
20140913_15.JPG

すると、このようにほぼパレート図ができました。
20140913_16.JPG

で、正しいパレート図というのは各アイテムの金額の棒グラフの構成比も読み取れるように金額の縦軸の最大値を合計金額“\10,561,900”に固定して、第2軸は最大値を“100%”に固定するのです。
そうすると、累積構成比の折れ線グラフは“讃岐めん”の棒グラフの頭から伸びる形になります。
ですが、この場合ハッキリ言って一番金額が大きい“讃岐めん”でも構成比は“7%”しかないので、そうしてしまうと棒グラフが低くなりすぎてみにくくなってしまうため、敢えて行いません。
ですが、このままの状態では主軸と第2軸の目盛の位置が合っていないので、それを合わせるために修正しておきます。

まず、主軸の上でクリックすると主軸が四角で囲まれます。
そこで、右くりして「軸の書式設定」を選択します。
20140913_17.JPG

「軸の書式設定」画面が開きます。
「軸のオプション」から「最大値」を「固定」にして“100000”と入力します。
20140913_18.JPG

次に同じように第2軸も「軸の書式設定」を選択し
20140913_19.JPG

「最大値」を“1”(100%)に「固定」します。
20140913_20.JPG

するとこのように主軸と第2軸の目盛があったパレート図が完成しました。
20140913_21.JPG

これで、ABC分析をしてみると
70%までを“ランクA”、そこから90%までを“ランクB”、その後を“ランクC”にしてみると
20140913_22.JPG
こんな感じで、“80-20の法則”にはなってなくて“なだらか形”になっているのが分かります。
20140906_00パレート図の形.jpg
ABC分析からすると、受注金額に大きな影響を与える商品はなくリスクは小さいと言えますが、売れ筋商品が無く受注金額を伸ばすための注力すべき商品がハッキリしていない。ということが言えます。。。
サンプルが良くなかったですね。。。反省。。


関連ページ
第1弾!“ABC分析(パレート分析)について
第2弾!“ピボットテーブルでパレート図を画いてみた
posted by haku1569 at 19:26| Excelデータ分析 | このブログの読者になる | 更新情報をチェックする

2014年05月05日

データ分析ツールでヒストグラムを作ってみた/Excel/データ分析

Excelのデータ分析ツールを使ってヒストグラムを作ってみましょう。
データ分析ツールを使用するにはあらかじめアドインでインストールする必要があります。
こちらをご覧ください。

まず、こんな製品の検査データがあります。
20140505-001.JPG

次に、ヒストグラムで設定するデータ区間をあらかじめ作っておきます。
この検査データは最小値が40.39、最大値が60.63なので、“40”から“61”まで“1”飛びで連続データを作っておきます。“1”飛びが区間(階級)になります。
20140505-11.JPG

「データ」から「データ分析」を選択します。
20140505-12.JPG

「分析ツール」の画面が開きます。「ヒストグラム」を選択します。
20140505-13.JPG

「ヒストグラム」画面が開きます。
入力範囲:検査データの入力範囲を指定します
データ区間:データ区間の入力範囲を指定します
出力先:検査データが入力されているシートにヒストグラムを作成(出力)させる場合は、その位置(セル)を指定します
グラフ作成にチェックをいれます
20140505-14.JPG

「OK」で度数分布表とヒストグラムが指定した位置(左上)に作成できました。
20140505-15.JPG

ピボットテーブルでも同様のヒストグラムが作成できます。
区間を“2”に変更したりするにはデータ分析ツールはいちいちデータの区間を作り変えておかなければなりませんが、ピボットテーブルでは比較的簡単に変更できます。

関連ページ
アドインでデータ分析ツールをメニューに追加する
ピボットテーブルでヒストグラムを作ってみた
posted by haku1569 at 17:18| Excelデータ分析 | このブログの読者になる | 更新情報をチェックする

2014年04月26日

重複の無いデータを抽出する/Excel/データ

重複するデータがある表から重複の無いデータを抽出してみましょう。

この受注データには、得意先名が重複して存在しています。
ここから、重複の無い得意先名を抽出してみましょう。
20140426-41.JPG

「データ」の「並べ替えとフィルタ」から「詳細設定」を選択します。
20140426-42.JPG

「フィルタオプションの設定」画面が開きます。
「リスト範囲」に得意先名のJ列をクリックして選択します(ここでは、勢いでI列も範囲指定しちゃってますが。。)
「重複するレコードは無視する」にチェックを入れて「OK」します。
20140426-43.JPG

すると、抽出の無い得意先名が抽出されました。
20140426-44.JPG

関連ページ
重複データを削除する
posted by haku1569 at 16:33| Comment(0) | Excelデータ分析 | このブログの読者になる | 更新情報をチェックする

重複データを削除する/Excel/データ

重複しているデータを削除して、重複しないデータだけを取り出してみましょう。

この受注データには、得意先名が重複して存在しています。
ここから、得意先名の一覧を取り出すため重複しているデータを削除してみましょう。
20140426_31.JPG

「データ」の「データツール」から「重複の削除」を選択します。
20140426_32.JPG

「重複の削除」が開きます。ここで、「得意先名」を選択して「OK」します。
20140426_33.JPG

すると、削除する件数が表示されます。「OK」します。
20140426_34.JPG

すると、得意先名が重複の無いデータが作成されました。
20140426_35.JPG

但し、ここで注意しなければならないのは、この方法は重複データは削除されてしまいますので、ファイルしてしまうと復元できません!

関連ページ
重複の無いデータを抽出する
posted by haku1569 at 15:51| Comment(0) | Excelデータ分析 | このブログの読者になる | 更新情報をチェックする

2009年09月15日

ソルバーを使った最小二乗法/Excel/データ分析

最小二乗法(Least squares)とは、実際の測定で得られたデータ等を一次関数や二次関数、対数曲線などの関数を用いて近似する際、実際のデータとの差の二乗(残差の二乗)の総和が最小になる様に関数の係数を想定する方法です。手計算で行うと偏微分方程式等を解かなければなりませんが、Excelの"ソルバー"を用いると瞬時に繰り返し計算を行って係数の推定が行えます。

直線で近似する場合
まずサンプルの元データを用意します。
20140426-701.jpg

この様なX,Yのデータから最小二乗法を用いて直線で近似してみます。解りやすいようにグラフでも表示してみます。
このデータは y=xのデータ(直線)から、データを適当にばらつかせたものです。
では直線の式 f(x)=ax+b の式を当てはめてみます。元々y=x ですから、a=1、b=0としてみます。
20140426-702.jpg

列Cに f(x)=ax+b (a=1,B=0) のデータを計算させています。計数aの値はC28、bの値はC29に入力し、f(x) の計算はそれらを引用して計算させます。(ソルバーではこの部分を最適化させます)
20140426-703.jpg

そして赤線でグラフを書いています。また列Dには(f(x)-y)^2のデータつまり残差の二乗を計算させています。(具体的には列Cの値から列Bの値を引いてから二乗しています)
20140426-704.jpg

そして、27行目にその総和(合計)を算出しています。
20140426-705.jpg

元々このデータからばらつかせたものですからこの赤い直線で十分近似しているようにもみえますが、残差の総和は"124.86"あります。
では早速、ソルバーを使って最小二乗法を実行してみましょう。

ソルバーの実行
まず、メニューの"データ"から"ソルバー"を選択します。ソルバーが表示されない場合は"オプション"の"アドイン"から、ソルバーを設定してください。
20140426-706.jpg

するとこのようなパラメータ設定画面が開きます。
ここでは、"目的セル"と"目標値"、"変化させるセル"を設定します。
20140426-707.jpg

まず、"目的セル"は残差の総和のセルにします。
20140426-708.jpg

変化させるセルは、a,bの値のセルにします。
20140426-709.jpg

目標値を最小値(つまり残差の総和を小さくする)にして実行します。
20140426-710.jpg

すると、自動的にa,bの値が変わり、グラフも書き替りました。
20140426-711.jpg
ソルバー実行後の画像aが0.905965、bが0.382456になり残差の総和は"113.906"となりました。

残差を更に小さくする
残差を小さくするとはどういうグラフになるでしょうか?
それは青いばらついている点それぞれに赤線が近づく曲線のグラフになります。つまりうねった曲線になります。
一般的に近似式の次数を大きくすると残差は少なくなります。先ほどのデータを3次式で近似してみます。
近似式は f(x)=ax^3+bx^2+cx+d とし、ソルバーを実行します。
20140426-712.jpg

すると、このように赤いグラフが多少うねっています。残差の総和は"113.8683"となり、直線で近似した場合より小さくなりました。
この近似曲線は元のデータを近似してると言えるかどうか?単に残差を小さくするためにばらつきを拾っているとも言えます。
データを分析するために最小二乗法を使って近似式を推定する場合は、対象のデータがどのようなものか、何のために近似するかなどの条件によって適切な式f(x)を想定する必要があります。近似式f(x)の設定が間違っているとソルバーを使っても適切な近似は不可能です。
例えば、f(x)=a/x+b にしてしまうと。。。
こんなことになってしまいます。
20140426-713.jpg
posted by haku1569 at 23:56| Comment(0) | Excelデータ分析 | このブログの読者になる | 更新情報をチェックする

アドインでデータ分析ツールをメニューに追加する/Excel/データ分析

エクセルで色々なデータ分析を行う「データ分析」メニューはアドインでインストールするようになっているため、メニューに表示されない場合は改めてインストールする必要があります。

Excel のメニューのデータを見るとツールバーの分析のところには“ソルバー”しか表示されていません。
ここに“データ分析”を表示させます。
20140436-71.JPG

まずExcelのOfficeボタンを押して“Excelのオプション”をクリックします。
20140436-72.JPG

“Excelのオプション”画面が開きます。“アドイン”メニューから“分析ツール”を選択し、“設定”をクリックします。
20140436-73.JPG

“アドイン”画面が開きます。“分析ツール”にチェックしてOKします。
20140436-74.JPG

インストールの確認画面が開きます。“はい”を押します。
20140436-75.JPG

インストールが開始します。少し時間がかかります。
20140436-76.JPG

これで、完了。
ツールバーに“データ分析”が表示されました。
20140436-77.JPG
posted by haku1569 at 21:20| Comment(0) | Excelデータ分析 | このブログの読者になる | 更新情報をチェックする

2009年06月28日

データ分析で基本統計量を求めてみた/Excel/データ分析

データの平均値、標準偏差等の統計値を関数を用いずに「データ分析」で「基本統計量」として一覧表で表示させることができます。
データ分析を使用するにはあらかじめアドインで「分析ツール」をインストールする必要があります。
こちらをご覧ください。


こんな検査データの表(テーブル)があります。
この検査データの「平均値」「中央値」「最頻値」「標準偏差」等の基本統計量を表示してみます。
20140508-01.JPG

「データ」から「データ分析」を選択します。
20140508-02.JPG

「分析ツール」の画面が開きます。「基本統計量」を選択します。
20140508-03.JPG

「基本統計量」の画面が開きます。
入力範囲:検査データの入力範囲を指定します
出力先:同じシート内に表示させる場合はそのセル位置を指定します
統計情報にチェックをいれます
20140508-04.JPG

「OK」で基本統計量が指定した位置に一覧表示されました。
20140508-05.JPG

ここで一言、これらの基本統計量はデータの分布状況によってそれらの見方(評価)が違ってきます。
平均値が同じだからと言って、2つのデータの集団が同じだとは言えない場合があります。
データの分布状況を表すのは一般的に度数分布図(ヒストグラム)が使われ、Excelでは「データ分析」や「ピボットテーブル」で簡単に作成することができます。
ちなみに、この検査データのヒストグラムをデータ分析ピボットテーブルで作成してあるので、参考に見といて下さい。

では次に、この基本統計量を関数で求めてみましょう。
元データが頻繁に更新される場合は、予め関数でセルに式を入れておけば元データの更新に応じて自動的に基本統計量も更新させることができます。

関連ページ
データ分析の基本統計量を関数で求めてみた
データ分析でヒストグラムを作ってみた
ピボットテーブルでヒストグラムを作ってみた
アドインでデータ分析ツールをメニューに追加する
posted by haku1569 at 18:32| Comment(0) | Excelデータ分析 | このブログの読者になる | 更新情報をチェックする

この広告は180日以上新しい記事の投稿がないブログに表示されております。