2014年08月02日

FREQUENCY関数で度数分布を求めてみた/Excel/統計関数

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

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

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

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

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

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

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

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

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

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

で、この度数分布表からグラフを作成すると、、
ヒストグラム(度数分布図)の出来上がりです。
20140731_07.JPG

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

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

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

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


posted by haku1569 at 23:03| Excel関数 | このブログの読者になる | 更新情報をチェックする

2014年07月13日

DELTA関数で2つの値が同じかどうかを求めてみた/Excel/エンジニアリング関数

Excelの関数で2つ値が同じな時“1”を、異なっている時は“0”を表示させます。
DELTA(デルタ)関数を使用します。
20140713_00_DELTA関数イラスト.jpg

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

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

数値1:1つの数値か数値が入力されているセルを指定します。
数値2:もう1つの数値か数値が入力されているセルを指定します
20140713_03.JPG

このような入力になります。
20140713_04.JPG

bPのデータは2つのデータが同じなため“1”が表示されました。
20140713_05.JPG

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

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

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


関連ページ
関数一覧(カテゴリ別)
関数一覧(名前別)
エラー値について(#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか)
posted by haku1569 at 21:33| Excel関数 | このブログの読者になる | 更新情報をチェックする

2014年07月06日

GESTEP関数で値がしきい値以上かどうかを求めてみた/Excel/エンジニアリング関数

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

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

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

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

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

で、このセルの式をデータ50まで貼り付けます。
20150702_04.JPG

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

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

関連ページ
関数一覧(カテゴリ別)
関数一覧(名前別)
エラー値について(#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか)
posted by haku1569 at 14:34| Excel関数 | このブログの読者になる | 更新情報をチェックする

2014年06月22日

OFFSET関数で関数の引数の範囲を設定してみた/Excel/検索関数

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

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

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

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

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

まず、“=SUM()”と入力します。
20140618_005.JPG

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

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

と、“6”になりました。
20140618_008.JPG

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

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

セルの入力内容はこうなります。
20140618_011.JPG

このOFFSET関数をSUMの中に入れます。
20140618_012.JPG

と、1から10までの合計が求められました。
20140618_013.JPG

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

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

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

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

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


関連ページ
関数一覧(カテゴリ別)
関数一覧(名前別)
posted by haku1569 at 00:44| Excel関数 | このブログの読者になる | 更新情報をチェックする

2014年06月15日

CONVERT関数で単位を変換してみた/Excel/エンジニアリング関数

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

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

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

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

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

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

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

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

重さ
グラム:      "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”で始まる関数が表示されるのでそこから選択することができます。

関連ページ
関数一覧(カテゴリ別)
関数一覧(名前別)
posted by haku1569 at 16:21| Excel関数 | このブログの読者になる | 更新情報をチェックする

2014年06月07日

角度(°)からラジアンを求めてみた/Excel/関数

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

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

では、早速“度(°)”の角度からラジアンを求めてみましょう。
20140603-01.JPG

「数式」「数学/三角」から「RADIANS」を選択します。
20140603-02.JPG

角度:“度(°)”単位の角度または角度が入力されているセルを指定します
20140603-03.JPG

「OK」でラジアンが求められました。
20140603-04.JPG

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

と、関数で求めた結果と一致しました。あたりまえ〜。。。
20140603-06.JPG

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

関連ページ
関数一覧(カテゴリ別)
関数一覧(名前別)
posted by haku1569 at 17:28| Excel関数 | このブログの読者になる | 更新情報をチェックする

2014年05月25日

PRODUCT関数で(掛け算の)積を求めてみた/Excel/関数

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

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

式で描くとこういうことになります。
20140525-p31.jpg

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

「数式」「数学/三角」から「PRODUCT」を選択します。
20140525-32.JPG

数値:掛け算をする数値範囲を指定します
20140525-33.JPG

「OK」で金額が求められました。
20140525-34.JPG

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

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

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

関連ページ
関数一覧(カテゴリ別)
関数一覧(名前別)
posted by haku1569 at 23:58| Excel関数 | このブログの読者になる | 更新情報をチェックする

ACOS関数で三角形の辺の比率から角度を求めてみた(COSの逆関数)/Excel/関数

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

ACOS(arccos / アークコサイン)はコサイン(余弦)の逆関数で、辺の比から角度(ラジアン)を求めることが出来ます。
20140525-P01.jpg
ページの最後にcosとarccosのグラフを描いてみたんで、お楽しみ。。

では、コサインの値からACOSで角度(ラジアン)を求めてみましょう。
20140525-01.JPG

「数式」「数学/三角」から「ACOS」を選択します。
20140525-02.JPG

数値:数値または数値が入力されているセルを指定します
20140525-03.JPG

「OK」で角度が求められました。
20140525-04.JPG

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

んで、グラフにすると、
20140525-06.JPG
y=x に対象になっているのが分かります(?)よね!

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

関連ページ
角度(°)からラジアンを求める / RADIANS
関数一覧(カテゴリ別)
関数一覧(名前別)
posted by haku1569 at 15:41| Excel関数 | このブログの読者になる | 更新情報をチェックする

2014年05月24日

ABS関数で数値の絶対値を求めてみた/Excel/関数

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

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

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

「数式」「数学/三角」から「ABS」を選択します。
20140524-12.JPG

数値:求める数値または数値が入力されているセルを指定します
20140524-13.JPG

「OK」で絶対値が求められました。
20140524-14.JPG

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

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

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

関連ページ
関数一覧(カテゴリ別)
関数一覧(名前別)
posted by haku1569 at 19:48| Excel関数 | このブログの読者になる | 更新情報をチェックする

DSUM関数で条件表を満足するデータの合計を求めてみた/Excel/関数

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

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

「数式」「関数の挿入」を選択します。
20140524-02.JPG

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

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

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

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

次に、“東京本社”の“第二営業”の受注金額は
20140524-07.JPG

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

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

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

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

関連ページ
関数一覧(カテゴリ別)
関数一覧(名前別)
posted by haku1569 at 18:51| Excel関数 | このブログの読者になる | 更新情報をチェックする
×

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