2016年11月23日

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

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

ASIN(arcsin / アークサイン)はサイン(正弦)の逆関数で、辺の比から角度(ラジアン)を求めることが出来ます。
逆関数と言うのは、y=xの直線に対して対象の関係の関数のことです。
20161123_00.jpg
ページの最後にsinとarcsinのグラフを描いてみました。

では、サインの値からASINで角度(ラジアン)を求めてみましょう。
20161123_01.JPG

「数式」「数学/三角」から「ASIN」を選択します。
20161123_02.JPG

数値:数値または数値が入力されているセルを指定します。
20161123_03.JPG

「OK」で角度が求められました。
20161123_04.JPG

コサインを求めた角度“ラジアン”と値が一致していますね。これが逆関数です。
では次にグラフを描いてみましょう。
まず、sin、asinで計算表をつくります。
20161123_05.JPG

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



関連ページ
数式パレットを表示させてみた
関数一覧(カテゴリ別)
関数一覧(名前別)
エラー値について(#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか)
ラベル:三角関数 ASIN
posted by haku1569 at 19:48| Excelの基本 | このブログの読者になる | 更新情報をチェックする

2016年03月27日

Tabキーでセルを横に移動させてみた / Excel

セルに値を入力したら次に入力するセルに移動するわけだけど、さて皆さんはどうしてますか?

矢印キーで移動させたり、「Enter」キーを押した時に移動方向(右か下)を予め設定しておいたり。。。いろいろ方法はありますが、、
いわゆる、表(テーブル)に入力する時は「Tab」キーを使って横(右)方向に移動させるととても便利です!!

例えば、こんな表で左上のセルがアクティブ(選択状態)になっています。
ここで、「Tab」キーを押すと右のセルがアクティブになります。
さらに「Tab」キーを押すと右のセルがアクティブになって、ここで「Enter」キーを押すと、最初に「Tab」キーを押したセルの1行下のセルに移動します。
20160327_01.JPG

さらに表をテーブルに設定していると「Tab」キーを押すだけで次の行に移動できるようになります。
20160327_02.JPG



関連ページ
posted by haku1569 at 17:08| Excelの基本 | このブログの読者になる | 更新情報をチェックする

2015年04月05日

シートの書式を一括でクリア(削除・解除)してみた / Excel

ワークシートに設定されている罫線や、フォント、文字の色、セルの背景色等の様々な書式を一括でクリア(解除)します。

こんな表があります。
罫線が設定され、行見出しには背景色が設定されています。オートフィルタも設定されています。
20150405_01.JPG

マウスカーソルを左上の角に置くと十字に代わります。
そこでクリックするとワークシート全体が選択されます。
20150405_02.JPG

メニューの「ホーム」、「編集」、「クリア」から「書式のクリア」を選択します。
20150405_03.JPG

すると、設定されていた全ての書式、おまけにオートフィルタの設定もクリア(解除)されました。
20150405_04.JPG

posted by haku1569 at 16:15| Excelの基本 | このブログの読者になる | 更新情報をチェックする

2014年07月27日

NORMDIST関数で正規分布の確率密度・累積分布を求めてみた/Excel/統計関数

Excelの関数で正規分布の確率密度・累積分布を求めます。
NORMDIST(Normal cumulative distribution)関数を使用します。
20140727_00_normdist関数イラスト.jpg

正規分布の基本的なことはまず、こちらをごらん下さい。。
んで、このような実際にデータ取りをした検査データがあります。基本的にこのデータは正規分布であると仮定します。
これから、平均値と標準偏差を求めて、それを元にNORMDIST関数で確率密度を求め正規分布のグラフを画いてみます。
また、累積分布を求めればデータの範囲にどのくらいの割合が含まれるか、含まれないかが推定できます。
20140727_01.JPG

平均値は“AVERAGE関数”、標準偏差は“STDEV関数”を使って求めます。
それから、-3σ(平均値から標準偏差の3倍を引いた値)、-2σ、-1σ、1σ、2σ、3σ(平均値に標準偏差の3倍を足した値)も予め計算しておきました。
20140727_02.JPG

確率密度関数(正規分布)は平均値と標準偏差だけで求められますので、その時の“x値”(データ)は適当に-4σから4σぐらいの値を予め表に入れておきます。
そして、予め計算しておいた-3σから3σの値も表に入れ込んでおきます。
201407027_03.JPG

「数式」「その他の関数」「統計」から「NORMDIST」を選択します。
20140727_04.JPG

X:x値(データ)を指定します
平均:予め求めておいた平均値のセルを絶対位置($マーク付き)で指定します。
標準偏差:予め求めておいた標準偏差のセルを絶対位置($マーク付き)で指定します。
関数形式:ここで、“FALSE”にすると確率密度が求められます。
20140727_05.JPG

隣のセルには累積分布を求めます。
同様にNORMDIST関数で、“関数形式”は“TRUE”にします。
20140727_06.JPG

で、それを全ての行にコピーします。
20140727_08.JPG
累積分布を見ると、-3σの時に“0.135%”になっています。これは、正規分布の解説にあった、「正規分布の範囲と推定」の値と一致していることが分かります。
この累積分布の値を使うことでデータの範囲の中にどれくらいの割合が含まれるかが推定できます。
例えば、-2σと3σのデータの範囲には“99.865%-2.275%=97.1%”のデータが含まれることが推定できます。

“x値(データ)”とNORMDISTの“確率密度”で散布図のグラフを作ると、平均値“53.10”、標準偏差“3.788”の正規分布のグラフが出来上がります。
20140727_09.JPG

では、おまけに、正規分布の解説に出てきた確率密度関数の式で、手計算してみましょう。
20150101.jpg

式の中のかたまりを予め計算しておきます。
自然対数は“EXP関数”を使用しました。
20140727_10.JPG

すると、NOMDIST関数で求めた確率密度と一致していることが分かりましたね。。。
20140727_11.JPG

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

関連ページ
正規分布の解説
AVERAGE関数で平均値を求める
STDEV関数で標準偏差を求める
関数一覧(カテゴリ別)
関数一覧(名前別)
エラー値について(#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!)
posted by haku1569 at 21:46| Excelの基本 | このブログの読者になる | 更新情報をチェックする

2014年04月06日

Excelでデータの作り方

今更なんだけど、Excelで集計表を作る時のデータの作り方について。。。
特にピボットテーブルを使う時のデータソース(元データ)を作る時はこの方法で作らないと、ピボットテーブルが上手く作れなくなってしまします。

特にExcelを使いこなしていない人に多いのが、始めから見やすい表を作りがちになってしまうことがあります。
勿論、エクセルにはその様な作表をサポートするテンプレートやデザインが用意されていて、それはそれで事足りてしまったりもするんだけど、、、
データ集計や、ピボットテーブルをつかった集計、分析を行おうとするんならそれなりのデータの作り方をした方が後々データの活用の幅が広がって来るんです。

例えば、日々の商品の売り上げを集計して表にまとめていく時、始めからこんな表を作っていってしまう人がいる。
2014040601.jpg
一見分かりやすくまとめられているけど、始めからこんな形でデータを作っていってしまうと、少なくとも2つの問題がでてくる。
●4月に売れたりんごの売上の合計を計算したい時に売上データが1行おきになっているから、範囲指定で合計を表示させたり、SUM関数がつかえない。
●りんごが売れた順にデータを並び変えようとしても、日付の欄がセルの結合を行っているんでデータの並び変えが出来ない。
後は、4月にみかんの売れた日数が集計できないとか、他にも色々ありそうだ。。。

だったら、どんな集計が良いかというと、
2014040602.jpg
こんな表になる。集計表と言うより、集計表を作るためのデータベースと言ったところだろか。
但し、こういう風に作っておくと、オートフィルタでデータの並び替えや、抽出も出来るし、勿論ピボットテーブルも作成可能になるのだ。
肝は、「1件のデータ(1件の出荷)は1行に記入する」と言うこと。1件のデータとは何かと言うのを明確にしておく必要がありそうだ。
それから、フィールド(列の項目)から、計算できる項目は省くと言うこと。
例えば、“単価”と“数量”がフィールドに設定してあれば“金額”は不要。なぜなら“単価x数量”で求めることができるから、ピボットテーブルの集計フィールドの追加で求めることができるからだ。
つまり、“単価”・“数量”と“金額”はだぶったフィールド(項目)になるということなのです。。。
データソースは“Simple is best”なのです。

ただ、この集計も一つ抜けているのがある、それは其々の1件、1件のデータを特定できるものが無いということ。
2014040603.jpg
この様に左端にIDと言う列をつくって重複しない記号を入力しておくことも肝だ。
なんでこれが必要かと言うと、データの件数を求める場合に重複が無く漏れ(空白)も無いデータを作っておくと何かと便利な時があります。このIDの数を数えればデータの件数が求めたりすることが出来ます。(データベースではこれを“ユニークなナンバー”等と言ったりします)
で、こんな集計表があれば、ピボットテーブルで次のようなクロス集計も可能になるんです。
2014040604.jpg
ピボットテーブルの集計結果だけでは表の体裁が良くないので、最終的にはここからコピーして体裁を整える必要があるけど、データを活用する(どう活用するか)と言う目的のためには、この様なデータの作り方が必要になってくるんです。


関連ページ
ピボットテーブルを作ってみた
posted by haku1569 at 15:36| Comment(2) | Excelの基本 | このブログの読者になる | 更新情報をチェックする