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

Excelの関数で度数分布を求めます。
FREQUENCY関数を使用します。

度数分布はヒストグラム（度数分布図）を作るのに利用されます。ヒストグラムによってデータの分布の状態を見ることができ、分布が正規分布に近い状態になっているかどうか、（ピークが大きく左右にずれていないかどうか、2つピークが出ていないか、かけ離れたところにデータがないか）、データの代表値として平均値を使っても良いかどうかなど、データ分析においてはまず分布の形を見ることがとても重要なことで、最も基本的な分析の一つになります。


Excelでは、この度数分布を求める方法は他に、データ分析ツールの「ヒストグラム」や、ピボットテーブルでグループ化をつかって度数分布表を作ることができますが、FREQUENCY関数の良いところはデータ区間の変更が簡単にできるということです。（後でやってみます）

ではさっそく。。。
ある検査データがあります。データの数は50個、予め、最小値と最大値をMIN関数、MAX関数で求めておきます。
これは、度数分布の区間を設定するときに参考にするためです。


まず、データの区間を設定します。
これは、その区間に入るデータの個数（度数）を数えるための上下限の範囲をその間隔（等間隔）の設定になります。
間隔を広くするとたくさんのデータが入り、狭くすると入るデータは少なくなるので、この間隔の大きさによって、ヒストグラムの形は変わってきます。
どのくらいの間隔が妥当かは試行錯誤が必要な場合もあります。
ここでは“40”から“61”まで“1”間隔で設定しています。
次に、、、ここが、普通の関数と違うところなんですが、関数を入力する範囲を予め範囲指定しておきます。


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


データ配列：検査データが入力されている範囲を指定します
区間配列：データ区間を入力したセルの範囲より一つ下のセルまで指定します。
図−4

そしてこれまた、普通の関数と違うところが、ここで「Ctrl」＋「Shift」＋「OK」で結果を求めます。


と、このように指定したデータ区間に含まれるデータの個数が求まりました。
ここで、“40”というのは40以下、“41”というのは40を超えて41以下という意味になります。

参考に、データ分析ツールやピボットテーブルで作った度数分布表も並べてみました。


で、この度数分布表からグラフを作成すると、、
ヒストグラム（度数分布図）の出来上がりです。


では、FREQUENCY関数の良いところ、データ区間の変更を実際にやってみましょう。
データ区間の間隔を“2”にして書き換えると、度数がそれに応じて変わりました。


実は、データ分析ツールやピボットテーブルを使った度数分布表ではこうはいかないんですよ。
んで、データ区間を“2”にしたヒストグラムはこうなります。

ちょっと粗過ぎでしょうか。。。

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

関連ページ
データ分析ツールでヒストグラムを作る
ピボットテーブルを使ってヒストグラムを作る
関数一覧（カテゴリ別）
関数一覧（名前別）
エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか）

Excelの関数で2つ値が同じな時“1”を、異なっている時は“0”を表示させます。
DELTA（デルタ）関数を使用します。


例えば、同じ検査データが2人の入力者によって“データ入力1”と“データ入力2”の2つのデータが入力されています。2つの入力データが合っていれば正しいと判断し（2人とも間違って同じ値を入力したかもしれませんが。。。）合っていなければどちらかが間違っているため、再確認をする必要が出てきます。


「数式」「その他の関数」「エンジニアリング」から「DELTA」を選択します。


数値1：1つの数値か数値が入力されているセルを指定します。
数値2：もう１つの数値か数値が入力されているセルを指定します


このような入力になります。


�ｂPのデータは2つのデータが同じなため“1”が表示されました。


これを全てのデータにコピーして、最後のセルにSUM関数でDELTA関数の結果を合計してみます。


すると、2つのデータが合っていてDELTA関数で“1”が表示された合計は“46”と求められました。データの件数は50件なので、4件はどちらかが間違っているため、再確認がひつようです。


このようにDELTA関数の応用としては、データ入力の正誤チェックや、テストの採点（答えと回答が同じ件数を数える）等が考えられます。
但し、それをExcelで実施するためにはその方法をよく検討しないと、かえって手間がかかってしまうかもしれません。


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

Excelの関数で値が指定した値（しきい値）以上の時“1”を表示させます。
GESTEP（ジーイーステップ）関数を使用します。
英語では “Greater than or Equal to STEP”
“Greater than or equal”とは“以上”と言う意味です。


しきい値（閾値）という言葉がでてくるのですが、別に意識する必要は無くて、単純に指定した値と比較して以上だったら、“1”、そうでなかったら（未満だったら）“0”を表示する関数です。
なので、その結果を合計すれば、指定した値以上のデータの個数を求めることができます。
IF関数でも同じことができますが、件数を数えたりする場合は“1”を表示してくれるGESTEP関数の方が便利ですね。

例えば、検査データの集計表があって、値が“50以上”かどうかを調べてその件数を求めてみましょう。


「数式」「その他の関数」「エンジニアリング」から「GESTEP」を選択します。


数値：判定する数値または数値が入力されているセルを指定します
しきい値：以上かどうかを判定させる数値か、数値が入力されているセルを指定します。
ここでは、“50”はセル“B54”に入力されているので、絶対参照で「F4」キーを押して“$”マークを付けてあげます。


で、このセルの式をデータ��50まで貼り付けます。


すると、検査データの値が50以上のデータに“1”が表示され、最後のSUM関数の合計値が“39”となりました。


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

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

Excelの関数で関数の引数の範囲を設定してみます。
OFFSET関数を使用します。


例えば、こんなシートがあって黄色の範囲（“1”が入力されている範囲）をOFFSET関数で設定して、合計をSUM関数で求めてみます。


まず、普通にSUM関数で合計を求めてみます。
SUM関数では、合計を求める対象の範囲を引数としてカッコの中に設定します。


と、合計は一目瞭然 “6”になりました。


次に、範囲をOFFSET関数で設定します。
予め、SUM関数の引数をセルに入力しておきます。
行数：基準セルから設定範囲までの行数。基準セルはここでは“1行目”で、範囲は“3行目”からなので、“4行-1行”で“3”になります。
列数：これも“行数”と同じ考え方で“C列-A列”で“2”
高さ：範囲の行数なので“3”
幅：範囲の列数なので“2”


まず、“=SUM()”と入力します。


カッコの中にカーソルを置いて、「数式」「検索/行列」から「OFFSET」を選択します。（SUM関数の中にOFFSET関数をネスト（入れ子）させます）


「関数の引数」画面が開きます。
参照：基準のセルを設定します。ここでは“A1”です。
行数：さっき予め入力したセルを指定します。
列数：どうじょー（同上）
高さ：〃（同じ）
幅：ちょんちょん（〃）


と、“6”になりました。


んで、次に応用例です。あまり良い例ではないかもですが、、
こんな50件のデータがあって、��1から、指定した数だけの合計を求めてみます。


参照：基準セル位置を��1のデータが入力されているセル“C4”を指定します。
行数：基準セルから開始する範囲なので入力は省略します。
列数：基準セルの列の範囲なので入力は省略します。
高さ：合計する範囲の行数“10”を入力してあるセル“B54”を指定します。
幅：C列だけの範囲なので入力は省略します。


セルの入力内容はこうなります。


このOFFSET関数をSUMの中に入れます。


と、��1から��10までの合計が求められました。


例えば、��11までの合計にしたければ、“行数”にしたセルを“11”にするだけで、��1から��11までの合計に変わります。


んで、応用例その2
“B54”のセルに“COUNT関数”でデータの件数を求めておけば、


“50”件の合計が求められました。


と言うわけで、SUM関数にネストさせていろいろ説明しましたが、範囲を引数にする全ての関数に応用ができまので、きっと画期的な応用ができるハズっ！です。。

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


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

Excelのエンジニアリング関数でいろいろな単位の変換をしてみます。
CONVERT関数を使用します。

長さや、重さとか、いろいろな（物理量の）単位がありますが、それらを換算計算をしなくても一発で単位変換が行えます。
技術者必須の関数です！？
予め用意されている単位から変換が可能です。


例として、大気（身の回りの空気の圧力）の圧力単位の変換を行ってみます。
大気圧=1atm（アトム）を “mmHg”（ミリメートルHg）、“Pa”（パスカル）に変換してみます。


「数式」「その他の関数」「エンジニアリング」から「CONVERT」を選択します。


「関数の引数」画面が開きます。
数値：変換する値または値が入力されているセルを指定します（ここでは“1”のセル）
変換前単位：変換前の単位または単位が入力されているセルを指定します（ここでは“atm”のセル）
変換後単位：変換後の単位または単位が入力されているセルを指定します（ここでは“mmHg”のセル）


「OK」で“atm”から“mmHg”への変換ができました。
同様にパスカル“Pa”に変換します。


一般的に大気圧の場合はキロパスカル“kPa”が用いられます。
CONVERT関数では単位の接頭辞（キロ：1000とか、ミリ：1/1000とか）も予め用意されている記号を単位の前に付けると変換可能です。
変換後単位を“kPa”にして変換すると、パスカルの1/1000の値で変換できました。


用意されている単位と接頭辞の一覧です。

重さ
グラム：　　　　　　"g"
スラグ：　　　　　　"sg"
ポンド(常衡)：　　　"lbm"
U (原子質量単位)：　"u"
オンス (常衡)：　　"ozm"

長さ
メートル：　　　　"m"
法定マイル：　　　"mi"
海里：　　　　　　"Nmi"
インチ：　　　　　"in"
フィート：　　　　"ft"
ヤード：　　　　　"yd"
オングストローム： "ang"
パイカ：　　　　　"pica"

時刻
年：　"yr"
日：　"day"
時：　"hr"
分：　"mn"
秒：　"sec"

圧力
パスカル：　　　"Pa" (または "p")
気圧：　　　　　"atm" (または "at")
ミリメートルHg： "mmHg"

力
ニュートン：　　　"N"
ダイン：　　　　　"dyn" (または "dy")
ポンド フォース： "lbf"

エネルギー
ジュール：　　　　　　　　　　"J"
エルグ：　　　　　　　　　　　"e"
カロリー (物理化学的熱量)：　　"c"
カロリー (生理学的代謝熱量)： "cal"
電子ボルト：　　　　　　　　　"eV" (または "ev")
馬力時：　　　　　　　　　　　"HPh" (または "hh")
ワット時：　　　　　　　　　　"Wh" (または "wh")
フィートポンド：　　　　　　　"flb"
BTU (英国熱量単位)：　　　　　"BTU" (または "btu")

出力
馬力：　　"HP" (または "h")
ワット：　"W" (または "w")

磁力
テスラ：　"T"
ガウス：　"ga"

温度
摂氏：　　　"C" (または "cel")
華氏：　　　"F" (または "fah")
絶対温度：　"K" (または "kel")

容積
ティースプーン：　　"tsp"
テーブルスプーン：　"tbs"
オンス：　　　　　　"oz"
カップ：　　　　　　"cup"
パイント (米)：　　"pt" (または "us_pt")
パイント (英)：　　"uk_pt"
クォート：　　　　　"qt"
ガロン：　　　　　　"gal"

接頭辞
exa　　　1E+18 "E"
peta　　 1E+15 "P"
tera　　 1E+12 "T"
giga　　 1E+09 "G"
mega　　 1E+06 "M"
kilo　　 1E+03 "k"
hecto　　1E+02 "h"
dekao　　1E+01 "e"
deci　　 1E-01 "d"
centi　　1E-02 "c"
milli　　1E-03 "m"
micro　　1E-06 "u"
nano　　 1E-09 "n"
pico　　 1E-12 "p"
femto　　1E-15 "f"
atto　　 1E-18 "a"

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

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

Excelの関数で単位が“度（°）”の角度からラジアン（弧度）を求めてみます。
RADIANS関数を使用します。

ラジアン（rad / radian）は弧度と言い、円弧の長さと円の半径の比で表わされます。
なので、単位は無次元になり角度に半径を掛けると円弧の長さが求められるため、特に数学においては、“度（°）”を用いるより一般的に使用されます。
Excelの三角関数でも角度は全てラジアンを用います。


では、早速“度（°）”の角度からラジアンを求めてみましょう。


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


角度：“度（°）”単位の角度または角度が入力されているセルを指定します


「OK」でラジアンが求められました。


関数を用いずに計算でも求めてみましょう。
ラジアン＝PI()x角度/180で求められます。


と、関数で求めた結果と一致しました。あたりまえ〜。。。


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

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

Excelの関数で掛け算の積を求めます。
PRODUCT関数を使用します。

PRODUCTとは掛け算の積のことをいいます。掛け算は数学用語で“乗法（multiplication）”といいます。
例えば“5x2=10”の場合、“5”を被乗数（multiplicand）、“2”を乗数（multiplier）、結果の“10”を積（product）といいます。
PRODUCT関数は指定した複数の値の積を求めます。足し算のSUM関数の掛け算版みたいな感じです。。

式で描くとこういうことになります。


では、使用例です。
商品毎の受注データがあって、標準価格に対し商品区分別に仕切り率があり、飲料についてはキャンペーン特価の仕切り率が設定してあります。そして、受注数量があります。
この商品毎の受注金額をPRODUCT関数を使ってもとめましょう。


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


数値：掛け算をする数値範囲を指定します


「OK」で金額が求められました。


これを他のレコードのコピーすると受注金額が求められました。


それから、ここでは説明のために「数式」の「関数ライブラリ」から関数を選択しましたが、あらかじめ関数が分かっている場合は式を

入力するセルに直接“=PRODUCT()”と入力します。
“=P”と入力すると推定機能が働いて“P”で始まる関数が表示されるのでそこから選択することができます。

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

Excelの関数で三角形の辺の比率から角度を求めます。
ACOS関数を使用します。

ACOS（arccos / アークコサイン）はコサイン（余弦）の逆関数で、辺の比から角度（ラジアン）を求めることが出来ます。

ページの最後にcosとarccosのグラフを描いてみたんで、お楽しみ。。

では、コサインの値からACOSで角度（ラジアン）を求めてみましょう。


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


数値：数値または数値が入力されているセルを指定します


「OK」で角度が求められました。


コサインを求めた角度“ラジアン”と値が一致していますね。これが逆関数です。
ある関数と逆関数のグラフは y=x の直線に対して対象となります。
実際にグラフを描いてみましょう。
まず、cos、acosで計算表をつくります。


んで、グラフにすると、

y=x に対象になっているのが分かります（？）よね！

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

関連ページ
角度（°）からラジアンを求める / RADIANS
関数一覧（カテゴリ別）
関数一覧（名前別）

Excelの関数で数値の絶対値を求めます。
ABS関数を使用します。

絶対値（absolute value）というのは数値（実数/全ての普通の数字）から＋、−の符号を除いた数の大きさ（0からの距離）を表すものです。
難しく書くとaの絶対値はこうなります。。。


雑談はさておいて、、
“5”と“-5”の絶対値を求めてみましょう。


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


数値：求める数値または数値が入力されているセルを指定します


「OK」で絶対値が求められました。


ちなみに、最初に出てきた難しい計算式で求めると。。。


なんか、顔文字みたいな式ですが、ちゃんと絶対値が求められます。


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

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

Excelの関数で指定したセル範囲から条件表に入力した条件を満足するデータの合計を求めます。
DSUM関数を使用します。

5月度の営業担当者毎の受注データがあります。
このデータ（データベース）から集計条件表の “在籍支社”、“部署名” を満足する
“金額” の合計を求めてみましょう。


「数式」「関数の挿入」を選択します。


「関数の挿入」画面が開きます。
「関数の分類」から「データベース」、「関数名」から「DSUM」を選択し「OK」します。


「関数の引数」画面が開きます。
データベース：受注データの入力範囲をフィールド名（項目）から指定します
フィールド：合計させるフィールドを指定します。
　　　　　　ここでは「金額」なので、指定したデータベース範囲の左から6番目の列に
　　　　　　なるので列番号の“6”または“金額”（ダブルクォーテーション付きで）
　　　　　　と入力します。
条件：集計条件表の範囲をフィールド名から指定します。


「OK」します。まだ、条件表は空白になっていますので合計金額は全ての合計金額が表示されます。


では、“東京本社”の受注金額の合計を求めてみます。
条件表の在籍支社に“東京本社”と入力します。


次に、“東京本社”の“第二営業”の受注金額は


集計条件を2行に増やしてみます。
在籍支社に“東京本社”と“大阪支社”を入力すると、東京支社と大阪支社の受注金額の合計が表示されます。


1行目の在籍支社に“東京本社”、2行目の部署名に“営業一”を入力すると東京本社全体と全国の営業一の受注金額の合計が表示されます。


このように条件表は横方向がAND（かつ）、縦方向がOR（または）の条件になります。

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

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