VBAと関数の無料講座

上図は、2つの作業所での「生産性」を比較したものである。
単純に「平均」を比較すると「A作業所」の方が多いが、偶然の可能性もある。
そこで、「t検定」を利用する。
「t検定」をするには、あらかじめ2つの数値群が「等分散」になっているのかを
検証する必要がある。これを行えるのが「F検定」で、FTEST関数で計算できる。
上図のセルJ3に、「=IF(FTEST(D4:D25,H4:H23)＞5%,"等分散","不等分散")」
と入力すると、「等分散」と表示された。
一般的にFTESTの結果が「5％」より大きい時に「等分散」とみなすことができ
る。
尚、通常のt検定は、「等分散」を仮定しています。「等分散」でない場合は他の
方法が必要になります。

上図のセルJ6に
「=IF(TTEST(D4:D25,H4:H23,2,2)＞5%,"平均差はない",平均差がある")」と
入力すると、「平均差がある」と表示された。
「t検定」は、TTEST関数で計算でき、第3引数に「両側確立」を求めるものとし
て「2」、第4引数には先に「F検定」で求めた「等分散」として「2」を指定する。
結果が「5％」より大きければ差は認められない。
上図の場合は、「A作業所」の方が優れていると認められる。
TTEST関数で「等分散」の「両側確立」として求めている。
一般的にTTESTの結果が「5％」より大きければ、２つの数値群に「平均差はない」
とみなす。
「平均差がある」と「t検定」の結果が出た場合は、「平均値」が高い方の値を生産性
の指標として見ることが一般的です。
尚、「平均差はない」と出た場合は、二つの生産性は同等であると考えられる。

上図のセルF3に、「=GROWTH(C3:C7,B3:B7,E3)」と入力すると、「事業年度6」
の会員数予測が表示された。
GROWTH関数は、独立変数(x)と従属変数(y)による「y=a＊B^x」として指数曲線
を求めて、(x)の値に対する従属変数(y)の値を予測できる。
上図のような、急激に増加しているデータを、予測したい場合の書式が、
「=GROWTH(y範囲[,x範囲][,xの値][,係数])」のようになり、この書式に当てはめ
て引数を指定する。
尚、例では事業年度に「1」や「2」を指定しているが、これを「2007」や「2008」
の様に指定すると、指数関数として成立しなくなる。

事業年度5までをグラフにしてみると、上図のようになる。

上図の様に、グラフのデータ線を選択して右クリックすると、[近似曲線の
追加]が出るのでクリックする。

上図のように、[指数近似]を選択すると、近似曲線で予測が表示される。

上図の様に、表の「事業年度6」に予測値を入力するとグラフに書き込まれた。

上図のセルG13に、「=STEYX(C3:C16,D3:D16)」と入力すると、「標準誤差」
が求められる。「売場面積」と「売上高」の様に、関連性がありそうなデータから
「y=ax+b」のような回帰直線を予想して導いた際、この回帰直線上の理論上の値
との間に差が生じる。STEYX関数を用いると、この差を「標準誤差」として求める
事ができる。これにより、予測値との誤差の程度を測ることができる。
STEYX関数の書式は、「STEYX(y範囲,x範囲)」と指定する。

「売場面積」と「売上高」の点と回帰直線に注目してみると、±標準誤差の
範囲にほとんどのデータに当てはまる事が分かる。

散布図の作成は、上図の様にセルC2～D16までを選択して、[挿入]-[グラフ]-
[その他の折れ線グラフ]をクリックする。

上図のように、[散布図]を選択して[OK]をクリックする。

図のように散布図が表示されるので、グラフ内で右クリックし[データ選択]を
クリックする。

上図の様に、「売場面積」と「売上高」の軸方向が、逆になっているので修正
をする。

[行列の切り替え]をクリックすると上図の様に、横軸に「売上高」が入るので
選択して[編集]をクリックする。

[系列の編集]が出るので、[系列]をすべて入力し、[OK]をクリックする。

[データソースの選択]に戻るので[売上高]の下の[系列3]から下のチェックを
すべて外す。[OK]をクリックする。

グラフ範囲を選択し、右側に現れた「+」をクリックする。

グラフ要素が表示されるので、[近似曲線]－[線形予測]をクリックすると
回帰直線が表示できた。

上図は、売上高と売場面積の2つのデータの、相関関係を調べたものである。
セルＧ2に「=CORREL(C3:C16,D3:D16)」と入力すると、「0.794」と
表示された。
CORREL関数の書式は、「=CORREL(y範囲,ｘ範囲)」となっており、独立変数
の「x範囲」とそれに関連があると予想できる、従属変数の「y範囲」の相関係数
を求めることができる。

結果の相関係数を、相関判定表に照合してみると、相関関係が「強い」と表示さ
れた。

分布の偏り具合を示す「歪度」はSKEW関数で求めることができる。
上図のセルF15に、「=SKEW(C15:C23)」と入力すると、「0.0350」と
表示された。SKEW関数の結果が「0」の場合、左右対称の正規分布と同等で
あることが分かり、歪度が「0」より大きければ左に偏った分布、歪度が「0」
より小さければ、右に偏った分布であることが分かる。
上図の場合は、どちらも左に偏っていることが分かる。

上図は、分布の尖度を求めたものである。
セルF3に、「=KURT(C3:C11)」と入力すると、結果が「－0.9362」と表示さ
れた。KURT関数の結果が「0」の場合、分布の山が正規分布と同等であることが
分かり、尖度が「0」より大きければ分布の山がとがっていて、尖度が「0」より
小さければ、分布の山がなだらかであることが分かる。
上記の場合は、どちらも負の値なので、山がなだらかなのが分かる。

上図の様に、FREQUENCY関数は度数分布を求める関数として用意されている。
「=FREQUENCY(データ範囲,区間範囲)」のような書式で、「区間範囲」には
各階層の条件値を入力した範囲を指定する。
区間範囲の最後を空白にしておくと、それ以上の値と言うことになる。
上図のセルJ3には、「{=FREQUENCY(E3:E12,I3:I10)}」と入力されている。

入力方法は、セルJ3～J10までを選択し、「=FREQUENCY(E3:E12,I3:I10)」
と入力して、[Ctrl]+[Shift]+[Enter]を同時に押して入力確定をする。
すると各階層の上限値に合わせたデータ件数が求められる。配列数式なので、
行の挿入などで、階級を増やすことはできない。

度数分布を求めるには基本的に、階級ごとの「下限値以上」「上限値未満」の
2条件を指定して、条件に合致する件数を求めればよい。
上図の様に、SUMPRODUCT関数を使用して求めることができる。
セルJ3に、「=SUMPRODUCT(($E$3:$E$12＞=G3)＊($E$3:$E$12＜=I3))」
と入力すると、「0～30,000」までの金額の人数を求めることができる。
SUMPRODUCT関数で、2条件に合致するデータ件数を求めるには、
「=SUMPRODUCT((条件1)＊(条件2))」のような構造になる。
上図では、セルE3～E12に対して「(金額＞=下限値)＊(金額＜=上限値)」の
ような条件を指定している。下方にコピーすると各階層の件数を求められる。

配列数式の仕組みは、上図のように「下限値以上」と「上限値以下」の条件を
満たす場合は、「TRUE×TRUE」で「1」求められ、それらを合計すると件数
が算出できる。

上図の様に、前期と今期の売上実績表を作成する。
「今期累計」は、セルE3に、「=SUM($D$3:D3)」入力して、それを下方に
コピーすると計算できる。

「移動合計」は、セルF3に「=SUM($C$3:$C$14)－SUM($C$3:C3)+E3」と
入力して、下方にコピーすると完成する。

上図の様に、列Bを選択して、[Ctrl]を押しながらセルD2からF14までを
選択する。

その選択状態で上図の様に、[挿入]－[折れ線グラフ]－[マーカー付き折れ線
グラフ]を選択するとグラフが表示される。

上図の様に、表の「今期累計」「移動合計」の文字が「折り返して表示」に
なっているのを横一列に変更すると、グラフの凡例文字の間隔が整って見易
くなる。

上図の様に、グラフタイトルにカーソルを入れて文字を「売上分析」に修正する。
グラフの完成である。
完成したZチャートは、「移動合計」が右上がりになっている場合、上昇傾向で
あるとみることができ、右下がりなら下降傾向であるとなり、横にまっすぐな
ら横ばいと言うことになる。

毎月の売上に対して、単純に「当月売上÷前月売上」として「前月比」を
計算しても、売上は月によってバラツキがある為、売上が上昇傾向にある
のか、下降傾向にあるのか分かりにくい。

上図のセルE5に、「=AVERAGE(D3:D5)」と入力し下方にコピーすると、
3ケ月の移動平均が計算できる。このようにすることで、個々のバラツキを
抑えた前月比の平均を見ることができる。

複数文字の出現回数を求めるには、COUNTIF関数を使用して求める。
上図のセルK28に「=COUNTIF($E$28:$E$37,J28)」と入力すると、E列の
中の「数学」の出現回数が「5」と表示された。これを下方にコピーしてK30
まで求める。

上図のセルH28に、
「=INDEX($J$28:$J$30,MATCH(LARGE($K$28:$K$30,ROW(A1)),
　$K$28:$K$30,0))」と入力すると、「数学」と表示された。
これは、セルK28～K30までの出現回数を基に「希望科目」を算出したものである。
これは、セルK28～K30で求めた人数から、LARGE関数で順位の大きい値を取り
出している。
ここでは、「ROW(A1)」としているので「1番人気」と言うことになり、
LARGE関数で求めた1番大きい値「５」となり、この「5」をMATCH関数で完全
一致「1行目」と求め、INDEX関数でセルJ28～J30の1行目の「数学」が取り出
される。
この式を、下にコピーすると「ROW(A2)」となり人気番号が繰り上がり「2」と
なるので、2番人気、3番人気の値と言うことになる。
LARGE関数の引数は、「=LARGE(範囲,順位)」である。
INDEX関数の引数は、「=INDEX(範囲,行位置,列位置)」であり、ここでは列位置
は「0」となっている。

上図の様に、アルファベット文字などの最頻値を求めるには、MODE関数だけでは
求められない。MODE関数は数値の最頻値を求められるが、文字の最頻値を求める
ことは出来ない。
しかし、1文字ならCODE関数で文字コードとして数値化できるので、MODE関数で
文字コードの最頻値を調べることができる。この結果得られた文字コードをCHAR
関数で文字にすれば目的を果たせる。
セルH16に、「=MODE(CODE(E16:E25))」と入力すると、「67」となり、
「=CHAR(MODE(CODE(E16:E25)))」と入力すると、「C」と表示された。
これが基本形になる。
セルH17に、「{＝CHAR(MODE(IF(E16:E25＜＞H16,CODE(E16:E25),"")))}」
と入力すると、「A」と表示された。
これは、IF関数でE16～E25とすでに求めた最頻値のH16との文字を比べて、異な
っている場合のみCODE関数でE16～E25セルの文字を返す。
こうして最頻値を除いた残りの文字コードの中から最頻値を求め、それをCHAR関数
で文字に戻す。
アルファベット文字に限らず、どんな文字でも1文字なら対応可能である。
H18セルに、
「{=CHAR(MODE(IF((E16:E25＜＞H16)＊(E16:E25＜＞H17),CODE(E16:E25),
　"")))}」と入力すると、「B」が表示される。
「{=CHAR」で文字コードの文字を取り出す。
「(MODE」で最頻値を求める。
「IF((E16:E25＜＞H16)＊(E16:E25＜＞H17),CODE(E16:E25),"")))}」でH16と
　H17の文字を除いた、文字群の文字コードの配列を作る。

上図は、希望の多い商品を順に3位まで求めたものである。
セルH3に、「=MODE(E3:E12)」と入力すると、「2」と表示された。
MODE関数は、「最頻値」を求める関数である。

上図は、2番人気の商品を抽出したものである。
セルH4に「=MODE(IF(E3:E12＜＞H3,E3:E12,""))」と入力して、
[Ctrl]+[Shift]+[Enter]キーを押すと
「{=MODE(IF(E3:E12＜＞H3,E3:E12,""))}」と配列数式として入力され、
結果「1」と表示された。
これは、配列数式を用いて、IF関数でE3～E12セルとすでに求めた最頻値の
H3セルの値と比べて、異なっている場合にのみE3～E12セルの値を返すように
している。こうすることで、最頻値を取り除いた数値群ができるので、それら
に対してMODE関数を用いて、2番目に多い数値が求められる。
但し、配列数式にしないと正しい回答は得られない。

上図は、3番人気を求めたものである。
セルH5に、「=MODE(IF((E3:E12＜＞H3)＊(E3:E12＜＞H4),E3:E12,""))」と
入力して、[Ctrl]+[Shift]+[Enter]キーを押すと、
「{=MODE(IF((E3:E12＜＞H3)＊(E3:E12＜＞H4),E3:E12,""))}」と配列数式
として入力され、結果「4」と表示された。
これも2番人気と同じように、最頻値と2番目に多い数値を取り除いた数値群から
最頻値を取り出して、3番目に多い数値を求めている。
尚、「(E３:E12＜＞H3)＊(H3:H12＜＞H4)」とすることで、AND条件の指定に
することができる。

上図のセルE23に、「=STANDARDIZE(C23,C$28,C$29)＊10+50」と入力する
と「偏差値」が求められる。その式をすべてのセルにコピーすると、すべての
「偏差値」が求められる。
STNDARDIZE関数の書式は、「=STANDARDIZE(x,平均点,標準偏差)」となって
いて、「偏差値」を求めるには、「STNDARDIZE×10+50」として計算できる。

上図の様に、まず平均を求める。
セルH3に「=AVERAGE(IF($D$3:$D$12=G3,$E$3:$E$12,""))と入力し、
[Ctrl]+[Shift]+[Enter]キーで入力確定すると、配列数式として入力される。
数式が「{ }」で囲まれて男女別の平均が求められる。

上図のセルI3に「=STDEV.P(IF($D$3:$D$12=G3,$E$3:$E$12,""))」と入力
して、[Ctrl]+[Shift]+[Enter]キーを押すと、数式が「{ }」で囲まれて男女別の
標準偏差が求められる。

上図は、配列数式の仕組みを示したものである。
STDEV.P関数の引数にあたる「IF($D$3:$D$12=G3,$E$3:$E$12,"")」は内部的
に、「IF(D3=G3,E3,"")」のように1セルずつ検証される。
IF関数で男女を判断し、「男」を集計する場合は、「男」の行は数値、異なる行は
「" "」が返る。これらをSTDEV.P関数で計算する際、文字列の「""」は無視され
るので、「男」の行の数値だけの標準偏差を求めることができる。

上図の例では、平均点はどちらも「47」で同じであるが、各点数のバラツキ度合い
が異なる。このような場合に用いるのが「分散」である。分散が大きいほどバラ
ツキが大きいことが分かる。
セルC9に「=VARP(C3:C7)」と入力すると、「1526」と「分散」が求められた。
尚、セルC8には、「=AVERAGE(C3:C7)」と平均を求める式が入っている。