haku1569　Excel でらくらく データ分析！

Excelの“CEILING関数”で指定した値の倍数で切り上げを行います。
“Ceiling”（シーリング）は“天井”、“上限”って意味。“シーリングライト”の“シーリング”ですね。
端数処理の中には“天井関数”という、簡単にいうと実数を整数に切り上げをする関数があります。Excelの“CEILING関数”の倍数を“1”にした関数のようなものですね。

倍数を求める値を“必要数”、倍数を“購入単位”としていくつ購入すればいいかを、“CEILING関数”で求めてみましょう。購入数は必要数より少なくなったらまずいので、切上げ処理をする“CEILING関数”を使います。
購入単位というのは1箱の入り数。購入は箱単位でしか買えません。
ちなみに、この表は「テーブル」の設定をしています。


「数式」「数学/三角」から「CEILING」を選択します。
(Excel2013では“CEILING,MATH”になります )


数値：“必要数”が入力されているセルを指定します
倍数：“購入単位”が入力されているセルを指定します
（テーブルに設定されているのでセルの名前がフィールド名の最後の方になって分かりずらいです）


「OK」で購入単位の倍数の内一番近い値で切り上げられました。


ちなみに、12個単位は“ダース”っていいますよね。知らない？
箱入り鉛筆の本数とか。。。ゴルフボールとか？
12って数字は、約数（割切れる数）が1、2、3、4、6、12と6種類ありますが、10は1、2、5、10の4種類しかありません。
何がいいかって、箱詰めする時2x6とか3x4とか自由度がありますよね。

応用例
時間を10分単位で丸めたり、切上げ、切捨てをしてみた

関連ページ
関数一覧（カテゴリ別）
関数一覧（名前別）

Excelの関数で相関係数を求めます。
CORREL関数を使用します。
“CORREL”というのは“Correlation coefficient”（コリレーション コエフィシエント）「相関係数」の略です。

さて前回、共分散の求め方を紹介しましたが、次は相関係数です。
相関係数は共分散から簡単に求められます。
相関関係（相関係数・共分散）の解説はこちらをご覧ください。

では、早速。
「数式」「その他の関数」「統計」から「CORREL」を選択します。


「関数の引数」画面が開きます。
配列1：xの値が入力されているセル範囲を指定します。
配列2：y1の値が入力されているセル範囲を指定します。


セルの式はこうなります。

y2、y3についても同様に求めましょう。

すると相関係数が求められました。


y1、y2、y3共にプラス（正の値）で右肩上がりの相関グラフになっているのが分かります。
んで、
y2＞0.7で、強い相関
0.4＜y1＜0.7で中間的な相関
y3＜0.2で相関が無い
ということが分かります。

では、相関係数を求める数式をつかって計算で求めてみましょう。
解説にもありますが、数式はこうなります。


まず、x、y1、y2、y3の標準偏差を“STDEVP関数（母集団の標準偏差）”で求めておきます。


で、共分散をxの標準偏差、yの標準偏差で割ります。


と、CORREL関数で求めた相関係数と一致しました。


このように、相関係数を求めれば、相関グラフを画かなくても、グラフが右肩上がりか、下がりか、分布のバラつきによる相関の程度がどの程度かを掴むことができます。。。
っと、早合点（はやがてん）してはいけませんよ。
実はそこには、相関係数を見誤るデータの分布が隠されてしまう場合があります。
なので、この関数を使って相関係数を求める時も、相関グラフ（散布図）を必ず画いて確認する必要があります。

バックナンバー
COVAR関数で共分散を求めてみた

関連ページ
相関関係についてまとめてみた
相関分析で気をつけたいこと（散布図を画いて確認しよう！）
関数一覧（カテゴリ別）
関数一覧（名前別）
エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか）

Excelの関数で共分散を求めます。
COVAR関数を使用します。
“COVAR”というのは“Covariance”（コヴァリアンス）「共分散」の略です。

共分散は相関関係の相関係数を求めるために使用されます。
相関関係（相関係数・共分散）の解説はこちらをご覧ください。

このような相関グラフ（散布図）があります。（データはサンプルです）

y2（赤○）はばらつきが少なく右肩上がり、
y1（青△）はばらつきのある右肩上がり
y3（緑□）はばらつきが大きく、グラフの傾向もよくわかりませんね。。

グラフの元データはこんな感じです。
テーブルの設定をしています。


では、早速。
「数式」「その他の関数」「統計」から「COVAR」を選択します。


「関数の引数」画面が開きます。
配列1：xの値が入力されているセル範囲を指定します。
配列2：y1の値が入力されているセル範囲を指定します。


セルの式はこうなります。


同じように、y2、y3の共分散も求めましょう。

と、このように共分散を求めることができました。
値の大小はあるものの、全てプラス（正の値）になっているの殆ど傾向が見えなかったy3も含めて全て右肩上がりのグラフであることがわかります。

では、共分散を求める数式をつかって計算で求めてみましょう。
解説にもありますが、数式はこうなります。


まず、x、y1、y2、y3の平均を“AVERAGE関数”で求めておきます。
それぞれのデータの平均との差（ここでは“平均差”と命名）を求めます。


次に、xの平均差と、yのの平均差の積（ここでは“平均差積”と命名）を求めます。


共分散は平均差積の平均なのでここも“AVERAGE関数”で平均をもとめます。

と、“COVAR関数”で求めた共分散と一致しました。

で次は、共分散から相関係数を求めてみましょう。。


関連ページ
相関関係（相関係数・共分散）
関数一覧（カテゴリ別）
関数一覧（名前別）
エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか）

Excelの関数で指定した行番号のセルのデータを求めます。
INDEX関数を使用します。
INDEX関数は、行番号と列番号で指定したセルの値を求めますが、今回は1列のデータ範囲から行番号だけで指定したセルの値を求めてみます。

例えば、こんなデータが31行に入力されているデータがあります。
ここで、指定した行番号（“�ａh）の“来店者数”の値を求めてみましょう。


「数式」「検索/行列」から「INDEX」を選択します。


「引数の選択」画面が開きます。
「配列,行番号,列番号」を選択します。


「関数の引数」画面が開きます。
配列：“来店者数”が入力されているセル範囲を指定します。
行番号：求める行番号を入力したセルを指定します。
列番号：ここでは「配列」で指定した範囲は1列だけなので“1”を入力します。


こんな感じの入力になります。


と、行番号を“30”と入力すると、��30の来店者数“83”、行番号を“31”と入力すると��31の来店者数“67”が表示されました。

行番号に“31”を入力



関連ページ
OFFSET関数で関数の引数の範囲を設定してみた
INDIRECT関数でセルの位置や範囲をテキストで参照させてみた

関数一覧（カテゴリ別）
関数一覧（名前別）

Excelの関数で数値の順位を求めます。
RANK関数を使用します。

こんな、営業マン毎の受注金額データがあります。
この金額の順位を求めみます。下位からの順位も求められます(^_^;)


「数式」「その他の関数」「統計」から「RANK」を選択します。


数値：順位を求める数値かセルを指定します
参照：順位を求める全体の範囲を指定します
　　　ここでは、全てのセルに式をコピーするので、
　　　この範囲は“$”マークを付けて絶対参照にしておきます。
順序：空白か“0”の場合は値の大きい方からの順位
　　　それ以外の数値の指定すると値の小さい方からの順位


セルの中の式はこのようになります。


これをコピーするとこうなりました。
田中君がナンバー1ですね。


次に、“順序”に“1”を指定して値の小さい方からの順位、下位からの順位を求めてみます。


すると、、山本君がワースト1というのが一目瞭然に。。。(T T)


単に順位を見るだけなら、値を“昇順”、“降順”で並び替えれば済む話ですが、“氏名”を並べ変えたくないような場合や、順位を数値で表示させたい場合はこの関数が便利ですね。。

んでここで、“LARGE関数”と“SMALL”関数を使うと、指定した順位の値を取り出すことが出来ます。
“LARGE関数”で上位から3番目の順位の値を求めます。


“SMALL関数”で下位から3番目の順位の値を求めます。


と、“RANK関数”で順位付けした上位、下位3位の値が取り出されました。。


それから、ここでは説明のために「数式」の「関数ライブラリ」から関数を選択しましたが、あらかじめ関数が分かっている場合は式を入力するセルに直接“=RANK()”と入力します。
“=R”と入力すると推定機能が働いて“R”で始まる関数が表示されるのでそこから選択することができます。

関連ページ
LARGE関数で上位から指定した順番目の値を求める
SMALL関数で下位から指定した順番目の値を求める
関数一覧（カテゴリ別）
関数一覧（名前別）

Excelの関数でセルが空白（ブランク）かどうか調べてみます。
ISBLANK関数を使用します。

“IS”から始まる関数は“IS関数”と呼ばれています。
これは、対象のセルを調べて“TRUE”か“FALSE”を表示します。


こんな、表があって、データが未入力の空白セルがあります。


「数式」「その他の関数「情報」から「ISBLANK」を選択します。


テストの対象：空白かどうかを調べるセルを指定します。


こんな式が入力されました。


全てにセルにコピーするとこんなふうになりました。


空白セルは“TRUE”と表示されています。

んで、“IF関数”と組み合わせて、“TRUE”だったら“空白セルです”と表示させ、そうでない時は空白を表示させてみると、、


こんな感じになりました。



それから、ここでは説明のために「数式」の「関数ライブラリ」から関数を選択しましたが、あらかじめ関数が分かっている場合は式を入力するセルに直接“=ISBLANK()”と入力します。
“=I”と入力すると推定機能が働いて“I”で始まる関数が表示されるのでそこ から選択することができます。


関連ページ
関数一覧（カテゴリ別）
関数一覧（名前別）
エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか）

Excelの関数で値が奇数か偶数かを求めます。
ISODD関数、またはISEVEN関数を使用します。

奇数は英語で“odd number”と言って、2で割り切れない整数のこと。
偶数は英語で“even number”と言って、2で割り切れる整数のことを言います。
Excelでは、
ISODD関数で奇数だったら“TRUE”、偶数だったら“FALSE”を表示し、
ISEVEN関数で奇数だったら“FALSE”、偶数だったら“TRUE”を表示します。


最初に“0”〜“10”を“ISODD関数”で奇数か偶数か求めてみます。


「数式」「その他の関数」「情報」から「ISODD」を選択します。


数値：調べる数値か数値を入力したセルを指定します。


こんな式が入力されました。


全てにセルにコピーするとこんなふうになりました。


次に、隣に“ISEVEN関数”で偶数かどうかを調べてみましょう。
「数式」「その他の関数」「情報」から「ISEVEN」を選択します。


ISODDと同じように数値を指定し、こんな式が入力されます。


これをコピーすると、“ISODD”の結果とは逆に“TRUE”と“FALSE”が表示されました。

んで、奇数と偶数の定義が「2で割り切れるかどうか」ということであれば、実際に2で割った時に割り切れる（余りが“0”）か、割り切れない（余りが“1”）かを求めれば良いということですよね。
ほんじゃ、MOD関数を使って2で割った時の余りを求めてみましょう。

「数式」「数学/三角」から「MOD」を選択します。


数値：調べる数値か数値を入力したセルを指定します。
除数：割る数“2”を指定します


こんな式が入力されます。


これをコピーすると、

となって、割り切れない（余りが“1”）が奇数になっているのが分かります。

それから、奇数、偶数とは“整数”であることが前提なのですが、、
小数だったらどうなるんでしょーか！？
ということで、数値に小数を入れてみた。

と、このように“１”未満は偶数になった。
実はこれ“ISODD関数”、“ISEVEN関数”では小数点以下は全て切り下げられて処理されるようになっているからなんです。
つまり、“１”未満の小数は全て“0”とみなされてしまうのです。。



それから、ここでは説明のために「数式」の「関数ライブラリ」から関数を選択しましたが、あらかじめ関数が分かっている場合は式を入力するセルに直接“=IS***()”と入力します。
“=I”と入力すると推定機能が働いて“I”で始まる関数が表示されるのでそこ から選択することができます。


関連ページ
MOD関数で割り算の余り（剰余）を求めてみた
関数一覧（カテゴリ別）関数一覧（名前別）エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか）

Excelの関数で割り算の余り（剰余）を求めます。
MOD関数を使用します。

割り算のことを数学では“除法”といいます。割られる数を“被除数”割る数を“除数
”、結果は“商”、整数の時の割り切れない余りを“剰余”と言います。
ちのみに、余りの“剰余”のことを英語では“remainder”というのですが、Excelの“MOD”はプログラミング演算子“modulo”（モジュロ）の略です。modulo演算子とは、まんま剰余を求める演算子なんです。。


最初にこんなテーブル（表）を作っておきます。
縦は“被除数”（割られる数）、横は“除数”（割る数）です。


「数式」「数学/三角」から「MOD」を選択します。


数値：割られる数“被除数”を指定します。
ここでは、後からこのテーブル全てのセルにコピーするので、列“B”は絶対参照の“$”マークを付けておきます。
除数：割る数“除数”を指定します。
今度は、“4”行目に絶対参照の“$”マークを付けておきます。


こんな式が入力されました。


全てにセルにコピーするとこんなふうになりました。


よくながめてると、面白いですね。
“2”で割ると、奇数は“1”、偶数が“0”になってます。
これって、奇数と偶数を識別できますよね。“3”で割った剰余をさらに“2”で割ってみると、2つおきに“1”が登場しそうです。色々な場面で応用できそうな予感。。。


それから、ここでは説明のために「数式」の「関数ライブラリ」から関数を選択しましたが、あらかじめ関数が分かっている場合は式を入力するセルに直接“=MODM()”と入力します。
“=M”と入力すると推定機能が働いて“M”で始まる関数が表示されるのでそこ から選択することができます。


関数の応用
・箱に何個入るか（箱詰め数）計算してみた


関連ページ
関数一覧（カテゴリ別）
関数一覧（名前別）
エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか）
続きを読む

Excelの関数で行番号を求めます。
ROW関数を使用します。

行番号を求めることで、表に連番を振ったりすることができます。
行番号に基づいているので、表の順序が入れ替わってもいつも連番が崩れることがありません。他にもいろいろ使い方はあるようです。


ではさっそく。。。
50個の検査データがあります。それのデータ�ｂ�ROW関数で入力してみましょう。


「数式」「検索/行列」から「ROW」を選択します。


参照：行番号を求めたいセルを指定します。
何も指定しないと、式が入力されている行番号になります。
ここでは、指定を省略します。


すると、行番号“3”が表示されました。


でも、ここは“1”から始めたいので、“-2”します。


んで、これをコピーするとこうなります。“1”〜“50”の連番が振れました。


試しに、データを「昇順」で並べ替えしてみます。


でも、データ�ｂﾍ変わりません。


今度は、「降順」で並べ変えてみても


データ�ｂﾍ変わりません


途中の行を削除してみても


データ�ｂﾍ49までになったけど、連番は崩れてません。


また、「参照」に範囲を指定してその範囲の行番号を求めることもできます。
まず、「参照」に指定する範囲分を表示させる範囲を指定します。


そこに「数式」「検索/行列」から「ROW」を選択し、
「参照」に5行目〜9行目の範囲をしてします。


そこで、「Ctrl」＋「Shift」＋「OK」を押します。
すると、“5”〜“9”が表示されました。


さて、この“ROW関数”の応用はいろいろとありそうです。。。

それから、ここでは説明のために「数式」の「関数ライブラリ」から関数を選択しましたが、あらかじめ関数が分かっている場合は式を入力するセルに直接“=ROW()”と入力します。
“=R”と入力すると推定機能が働いて“R”で始まる関数が表示されるのでそこから選択することができます。

関連ページ
COLUMN（COLUMNS）関数で列番号を求めてみた

関数一覧（カテゴリ別）
関数一覧（名前別）
エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか）

Excelの関数で最大値、最小値から指定の範囲を除いたトリム平均を求めてみます。
TRIMMEAN関数を使用します。
Microsoftによると、これを“中間項平均”と呼ぶようです。。

トリム関数についてはコチラの解説をご覧ください。
TRIMMEAN関数を使うと最大値と最小値から一定の範囲のデータを除いた平均値を求めることができます。


ではさっそく。。。
ある検査データがあります。データの数は50個、試しに、平均値、最小値と最大値をAVERAGE関数、MIN関数、MAX関数で求めてみます


「数式」、「その他の関数」、「統計」から「TRIMMEAN」を選択します。


配列：平均を求めるデータの範囲を指定します。
割合：最大値、最小値から取り除くデータの範囲を指定します。
ここでは“0.04”を指定しています。
データの数は50個なので、（50x0.04）÷2=“1”　つまり最大値から1つ、最小値から1つ合計2つのデータを除くことになります。つまり、“最小値”と“最大値”を除くことになります。
もし、ここで“0.08”と指定すると（50x0.08）÷2=“2” で、最大値から2つ、最小値から2つの合計4つのデータを除きます。
“0.06”を指定すると（50x0.06）÷2=“1.5”
“0.05”を指定すると（50x0.05）÷2=“1.25”
といずれも、少数が付きます。その場合は切り捨てて“1”にして最小値と最大値が除かれます。


「OK」すると、最小値と最大値を除いた48個のデータの平均値が“52.74”と求められました。


試しに、元データから最小値と最大値を除いた平均値を実際に求めてみましょう。
まず、元データに「フィルター」をかけて「昇順」で並べ替えます。


最小値が一番上、最大値が一番下になるので、それを除いた範囲を“AVERAGE関数”で平均を求めます。


すると、“52.74”となり、“TRIMMEAN関数”で求めた値と一致しました。


この様に、最小値、最大値から一定の範囲のデータを除くことができますが、最小値からだけ、最大値からだけ除くことはできません。必ず最小値、最大値、両方から一定の範囲になってしましいます。このへんをよく理解して使いましょう！



それから、ここでは説明のために「数式」の「関数ライブラリ」から関数を選択しましたが、あらかじめ関数が分かっている場合は式を入力するセルに直接“=TRIMMEAN()”と入力します。
“=T”と入力すると推定機能が働いて“T”で始まる関数が表示されるのでそこから選択することができます。

関連ページ
異常値を除外するトリム平均とは？
関数一覧（カテゴリ別）
関数一覧（名前別）
エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか）

データ分析の解説/メニュー