|
● 関数の使い方をマスターしよう!!
いよいよ、関数に入ってきました。
関数も、沢山の関数があります。
そのうちの、幾つかを紹介しましょう。
● 関数はなんなん!!
★関数ってなんだ？
関数って聞くと、中学で習った１次関数、2次関数を思い出してくれ？
数学の苦手な人にとっては、虫唾（むしず）がはしるかもしれんな。(T_T)
じゃが、ここは、ちょっと、我慢して。。。まぁ、聞け (^0_0^)
たとえばじゃ
1次関数 Y=５X＋１０ という式がある。
Y は エクセルの場合 おのおのセルだと思ってくだされ。
セル A1 を Y としよう。
” = ”はエクセルの場合も、数式には必ず必要なんじゃ。覚えておいて欲しい。
（「計算式を入れてみよう」でも説明したよね。ここは復習じゃ!!）
さて、その” = ”の入れ方は、
図のように
数式バーに ” = ”がついちょるじゃろーー。
そうじゃ、このボタンを押せば ” = ”が図のように挿入される!!ぞ。。
例えば セル A1 は Y じゃ、そして X は セル B1 とする
A1 = 5 * B1 + 10
という式がなりたつじゃろ。。。それを、そのまま数式バーに入れてみよう!!
数式バーに直接この式を入力してもよいが、セルを選択して式を作る方法を紹介する。
① まず Ａ１のセルを選択して、 = を挿入する。
② つぎに 5*と入れる。
（数式の結果バーはドラッグすれば自由に動くぞ!! ）
③ セル B1 を選択する。 どうじゃ、B1が自動的にはいったじゃろ!!\(^o^)\
④ あとは +10 を入力すれば、この式は、完成じゃ。
「OK」 を クリックすれば（あるいは「Enterキー」を押す）
セル A1 に ６０ という答えが 出たはずじゃ!!
Good!! 出来たよね。
B1にどんな数字を入力しても、セル A1 は答えを出してくれるじゃろ。。
さらにじゃ、Y = aX ＋ ｂ という式にして、a を セル C1 ｂに セル D1 と
わりあててやれば、1次関数のすべての式に対応できるわけじゃ。
中学生の1次関数の宿題の答え合わせは、これで完璧じゃ!!
うほぉ、ほぉ、ほぉ、、うれしいの\(^o^)\
つまりじゃ、エクセルはこういった式を沢山用意してくれておるわけじゃ。
その式のことを関数とよんでいるんだなぁ。
● 復習を兼ねて実践練習しよう!!
上のようなデータを作ってみた。（ｒｅｎｓｙｕ.xls ←でDLできるよ）
点数は、乱数（０～１）を使って、１００倍し、その整数が
５０以下なら＋５０となるようにすればいいですけれど、
結構めんどうですよね、
式にすれば、乱数はRAND（）を使う。
具体的には
=INT（RAND（）*100））
って書けばよい。「INTは整数にしなさい」という関数だ!!
そのセルを参照にして IF関数 っていうのを使うんだ。
このIF関数は
= IF（Ａの条件,Ｂ,Ｃ） って書き方をするんだけれど
意味は
「Ａの条件を満たせば、Ｂにしなさい、そうじゃなかったらＣにしなさい」
ということなんですな、
具体的には RAND（）の入ったセルを Ｒ３とすれば
= ＩＦ（Ｒ３<50,Ｒ３＋５０,Ｒ３）とすれば
Ｒ３が５０より小さい時、Ｒ３の値に５０を足しなさい、
５０より大きい時はそのままＲ３の値を使いなさいっていうことだな。
ところが、ギッチョンチョン
エクセルにはさらに便利なツール（関数）があるじゃあ～りませんか!!
それは、エクセルアドイン（ヘルプから アドイン で検索してください）から
分析ツールを入手するんです。（入手していなければ入手してみてはどうかな!!）
その中にRANDBETWEEN（）という関数があるんじゃな。
これをつかえば、整数で（９７、９５であるかどうか確認していません。スンマセン (~_~;) ）
たとえば５０から１００までの乱数が簡単にGETじゃ。
これをコピーして、数値だけを上の表にコピーすればＯＫだな。
（表の横に式が入れてあるので参照にしてくだされ）
他にも便利なツールがあるので、必要に応じて入手してみてはどうじゃ。
（ここでは、アドインについての詳細はカァーットじゃ、何故なら、話がながくなるからじゃ）
すんませーん、話がどんどんよこにそれてしまいまんな～。
さて、
次に、諸君、もちろん、オートサムは以前練習していてくれたことと
思うのじゃが、で、ここではカットするぞ 。練習のため１つだけ入れておくが
あとは自分で入れるのじゃ。
どうじゃ、できたか。。。上と同じ答えがでたらGOODじゃ。
コピーするときに、気をつけてくれ、貼り付けは、「数式だけ」にしておくのじゃ
（ほんとうは、体裁は最後にするのがベストだと思う。表の中身が完成までは
体裁は気にしない方がいいぞ。）
オートサムを忘れていたら、「計算式を入れてみよう」へ行って見てくれ>>> GO!!
要は、下の図の赤枠の左横の∑を使えばＯＫなんじゃが。。。。(~_~;)
赤枠の中のfxのアイコン、これがエクセルの関数じゃな。。。(*_*)
これについては、次で解説じゃ。
●横道ついでに、ここで「条件式書式」をマスターするかの。
表の中に青い、数字と赤い数字があるじゃろ。
これは学科の最高点と最低点じゃ。
こういう条件に書式をつけるには、
ファイルメニュー → 書式 → 条件付書式 をクリック
ここでも、関数が使っているんだ。
MAXは最大。MINは最小。ここでセルの位置を示すのに
あまり見られない＄マークがついているだろう。これは「絶対」
という意味なんだけど、中級編で説明しているので、＞＞＞GO!!
ここでは説明しないよ。
簡単にいうなら、行は絶対だから、そのセルをコピーしてどこに
貼り付けても行の位置はかわらない（この場合3:16だね）
だけど列は、コピーした位置の列に変化してくれるんだ。
Ｅの列に貼り付ければ E$3:E$16 となるわけです。
こうしておけば、１個の式をコピーして、（今回は、行で白と色付きで
２段になっているので先に書式で表を作った場合２個作ったほうが
コピーするときに楽ですヨ。。。だから体裁は最後だよね!!）
データ全域に貼り付けてしまえばいいわけだ。
★条件１ってかいてあるけど、条件は３まで追加できる。
真中下の「追加」で条件を増やせるんだ。
★左上部の「セルの値が」って書いてあるけど、他に「数式が」ってのがある。
数式の場合は TRUE（正）かFALSE（負）が答えとなるような式で
TRUE（正）の場合だけ書式を変えることができるんだ。
★「セルの値が」の場合「次の条件」ってのがあるんだけど
それは、自分の目で確かめてみてくれ。
今回は、「次の値に等しい」を選んでみた。
そして、次のBOXには、ここでは値ではなく、関数を入れてみた
式は最大関数MAXを使う。
= MAX（C$3:C$16）
これでC列の国語のデータ範囲の最大値の時に青色、太字に変わるということだ
次の条件で最小関数MINを使って色は赤色、太字にしてある。
書式は書式のボタンを押すと、上の図が表示されるので
書式を決定すればよい。
今回は、罫線とパターンはさわらない。
フォントだけの設定だ。
いわんこっちゃないが、
たとえば、エラーが出るような式があってエラー表示を
見せたくない場合、ISERROR（）という式があるので
これを使えば、エラーのときTRUEを返すので、たとえば
フォントの色をバックの色に合わせておけば表面上は
なにもないように、見せかけることができる。
白地であれば、フォントの色を白にしてやる。
なんてことをするわけじゃ!!
これもテクニックの一つだ!!
おおまか、理解できただろうか？？
少しずつ難しくなってきたけれど、細かい所も少しずつ
知っておくと、後でいろいろ応用できるので、是非
マスターして欲しいものじゃな。
でもあせらずじゃ!!
● 平均、最大、最小、ランク、標準偏差!!etc
◆今回の最後だな。ガンバ、ガンバ(^-^)/~~
さて、今回は、やり方を書いておくので、実際に自分でやってみてくれ。
そして、回答というか、できたファイルは、次回掲載するということにしよう!!
つまり、みんなの嫌いな宿題というやつじゃ。
さぁ、やっと" fx "の出番じゃな。
" fx "を早速、クリックしてみよう。
そうすると下の図の、「関数の貼り付け」が出てくるぞ。
みてもわかるように、関数がくさるほどあるな、
これを全部使いこなすのは、至難の業じゃ、
し、しかしじゃ、普段使うのは、そんなに多くを必要としないのも事実じゃ。
とりあえず、今回はこの中から、先ほどのデータを使って、
いろいろやってみようというわけじゃな!!
●まず、平均からはじめるぞ!!
テストの成績となると、平均をしりたいものじゃ、それから最高が何点で、
最高が何点、自分がクラスで何番目で、標準偏差はいくらになるのか。
あるいは、男女別にするとどうなるのかなぁ、なんてことを知りたいだろうと思う。
こういう時に、エクセルの関数が役に立つのである。（話がながいぞ～。。。(*_*) ）
ホイホイ、前置きは、さておき、さっそくはじめるとするか、
平均は AVERAGE() という関数を使う。
これは、分類の統計の初めのほうに入っている。
まず、セルD18に選択しておいて、
アイコンｆｘをクリック、統計をクリック
AVERAGEはすぐに出てくると思うので
これをクリック
そうすると次のように出てきたと思う
エクセルは自動的に上の範囲を選択しているが合計
も含んで範囲を選択している。範囲をマウスでしなおすか、
あるいはD17をD16と直接書き直してもいい。
正解は次のとおりだ。
=AVERAGE(D3:D17)
答えは 66 と出ているはずだ。
これを合計(列I）までコピーしちゃおう!!
これで平均はできあがり、
順次求めていこう
関数は統計に入っている
最大はMAX()
最小はMIN()
中央値はMEDIAN()
標準偏差はSTDEVP()
男の人数は数学/三角関数の
COUNTIF()を使います。
範囲を選択して男を検索値にすればいいのですが
文字だということで必ず " " をつけておいてくだされ
範囲は絶対値にしておいておくことだ
=COUNTIF($C$3:$C$16,"男")
女の人数はコピー、貼り付けをして、男を女に変えればＯＫでな。
男女別の平均だが、これは
SUMIF()を使う。これも数学／三角関数にあるんだな
範囲は各個人の合計までの絶対値をとっておくこと。
検索値は"男"だな。合計は国語であれば、
国語の範囲（これは行だけ絶対値にしておくのじゃぁ!!）
=SUMIF($C$3:$I$16,"男",D$3:D$16)
実際にはSUMIの関数を入力してから人数の絶対値で割れば
平均がでることは、わかるよね。。。。
（これがわからなければ、算数からお勉強!!お勉強するのじゃ!!） (^0_0^)
実際に入る式は次のとおり。
=SUMIF($C$3:$I$16,"男",D$3:D$16)/$C$24
国語
|
合計 |
917 |
|
平均 |
65.50 |
|
最大 |
94 |
|
最小 |
49 |
|
中央値 |
62 |
|
偏差値 |
13.57 |
|
|
| 男数 |
7 |
58.29 |
| 女数 |
7 |
72.71 |
最後に偏差値をいれてみるぞ!!
入れる場所はじゃ、各個人の科目の合計の横に偏差値を入れてみる。
偏差値の計算は =((個人の合計-平均値)*10/標準偏差)+50
で求めることができるんじゃ！！ホヒョー
実際には
=((I3-$I$18)*10/$I$22)+50
という式が入るのじゃが、参考にしてくれ!!
平均値と標準偏差には絶対値をいれておこう。
（Ｆ４を順次押せばかわるよ～ん!!）
こうすれば一括コピー、貼り付けができるからだ。
テストの偏差値の式としては以下のとおり、
５０を基準とした偏差値を求める
最終的には次のように求めてくだされ
今回、これにて終了。回答は次回掲載するので、
確かめたい方は ＤＬ してくだされ。
今回は盛りだくさんだったな!!、関数はたくさんあるので、
やっぱ、次回も、関数をしようと思う。
関数を多く知ることが、上達の秘訣でもあるからの～～、ガンバ、ガンバ\(^o^)\
それでは
次回も懲りずに頑張ってチャレンジ!!次回もあいましょう (^-^)/~~
ｂｙ たこちゅー
|
その4
第7弾
