プレス技術研究所VBA講座

上図のセルF3に、「=SUMPRODUCT(($D$3:$D$14=D3)＊($E$3:$E$14＞E3))+1」と
入力すると、「4」と表示された。
これは、D列とE列の男女の点数表から、D3セルが「男」だから、男だけの点数の順位が
4位だと判定したものである。
男女混載の表の中から、男女別に得点順位を付けるには、まず、同じ性別であり指定した点数
より大きい点数と言う、AND条件式を作成する。この時、すべての値を使った数式が必要なた
めに、配列が扱えるSUMPRODUCT関数を使う。
第1引数の「配列1」に「(性別のセル範囲=性別のセル)＊(点数セル範囲＞点数セル)」という
条件式を作成する。これにより、同じ性別内で指定した点数より大きい点数の個数が求められる。
順位は、この個数より「1」多くするれば求められるので、SUMPRODUCT関数で求めた個数に
「+1」とする数式を作成する。これをコピーすれば、男女別の順位が求められる。
F3セルの順位は、E3が「70」なので、「男」の中で「70」より大きいのは3人いるので「+1」
で「4」位になる。

上図のセルD15に、「MID("ABCDE",RANK(C15,$C$15:$C$19),1)」と入力すると「E」と
表示された。これは、売上高ランクをアルファベット文字で表示したい場合の数式である。
順位が1位なら「A」、2位なら「B」のように、指定した文字を付ける。
これには、RANK関数で求めたC列の中の順位が「1」なら、「ABCDE」の、左から1文字目の
「A」が取り出されるように、MID関数で数式を作成する。
MID関数の引数は、「=MID(文字列,開始位置,文字数)」の「文字列」にダブルクオーテイション
で囲んだ「"ABCDE"」、「開始位置」にRANK関数で求められた順位が「1」なら「ABCDE」の
1文字目から「文字数」の「1」文字分の「A」、順位が「2」なら「ABCDE」2文字目から
「文字数」の「1」文字分の「B」が取り出される。

上図のセルD3に、「=LOOKUP(PERCENTRANK($C$3:$C$12,C3),$G$3:$G$5,$H$3:$H$5)」
と入力すると、「C」と評価が表示された。
評価のランクは、G3からH5までの評価基準によって決められている。
C列の売上高の上位30％がAランク、下位30％がCランク、間がBランクである。

セルE3を見てみると、「=PERCENTRANK($C$3:$C$12,C3)」と書かれており、「11％」に
なっている。それを引数にして、LOOKUP関数が売上高「下位30％」と判定して評価「C」と
したのである。評価用の表をLOOKUP関数が参照することによって結果が得られる。
LOOKUP関数の第1引数「検査値」にPERCENTRANK関数を使い、第2引数「検査範囲」に
評価表の割合(%)のセル範囲を、第3引数の「対応範囲」に評価(A～C)のセル範囲を指定する。

上図のセルP3に「=RANK(O3,($O$3:$O$8,$O11:$O$16))」と入力すると、「9」と表示
された。これは、O3セルの「７0」点が上下表の「点数」列での順位を表したものである。

上図の様に、もう1つ表を増やしても複数範囲の順位は計算できる。
「=RANK(O19,($O$3:$O$8,$O$11:$O$16,$O$19:$O$24))」

上図のセルF17に「=RANK(E17,$E$17:$E$22)」と入力すると、支店の売上順位が
表示される。
支店別の売上効率を、売上高に対す人員の数とみる場合、
「1人当たりの売上」売上高÷人員をE列に計算しておいて、その数値を基にRANK関数で
順位を求める。

上図のセルF18に、「=SMALL($C$17:$C$20,E18)」と入力すると、C列の売上高の下から
2位に売上金額が表示された。
SMALL関数は、データの中で1番小さい値、2番目に小さい値と言う様に、順位を指定して値
を求める関数である。引数に「配列」と「順位」を指定すると、小さい方から値を求めること
ができる。

上図のセルF18に、「=LARGE($C$17:$C$20,E18)」と入力すると、C列の売上高の2位に
売上金額が表示された。
LARGE関数は、引数の「順位」に指定した「何番目」にある値が求められる。
例えば、「順位」に「2」を指定すると、「配列」に指定した範囲にあるデータの中から
2番目に大きい値が求められる。尚、E列のセルは数値だけで「位」は[書式設定]で表示して
いる。

上図のセルO13に「=SMALL(O3:O12,COUNTIF(O3:O12,0)+1)」と入力すると、「57」と
表示された。これは、O列の中から「0」を除いた最小値を求めたものである。
SMALL関数とCOUNTIF関数を組み合わせて使用することにより、値範囲の中にある「0」の
個数を求め、その数「+1番目」の数値を求めるものである。
SMALL関数は範囲の中で、小さい方から数えた「順位」の値を返す関数で、COUNTIF関数で
数えた0の個数に「+1」を足した数値を、SMALL関数の「順位」に指定する。
これにより、例えば値の中に0が2つあるとすると、「2+1=3」番目が「順位」に指定される。
結果、0を除いた最小値が求められることになる。つまり、0の値が複数あっても常に0を除く
最小値が求められる。

上図のセルE12に、「=MAX(C12＊0.2,20000)」と入力すると「20,000」と表示された。
これは、住宅手当として家賃の20％を支給するが、最低でも2万円を支給すると言う場合に、
支給額を算出する計算式である。

上図は、最大50,000円と決められた、通勤費の申請額と支給額を計算したものである。
セルE3に「=MIN(SUM(C3:D3),50000)」と入力すると、「50,000」と表示された。
これは、支給最大額が50,000円と決められているので、それ以上超えた申請には最大
支給額が支給されることを示している。
「=MIN(SUM(申請費用),支給最大値)」で「範囲の合計」と「支給最大値」のいずれか
「小さい方」が選択される。

上図のG3セルに、「=MODE(C3:C9)」と入力すると「6」と表示された。
これは、C列のデータの中に一番頻繁に出てくる数値を拾い出したものである。
尚、同じ頻繁に出てくる数値が複数ある場合は、セル範囲で先に出てくる値が
最頻値となる。

上図の様に、数値を並べた時に真ん中にある値(中央値)を求めるには、MEDIAN関数を使用する。
MEDIAN関数の引数は「=MEDIAN(求めるセル範囲)」で、データが偶数個の場合は、真ん中に
ある値2つの算術平均が求められる。

上図のセルG19に、「{=AVERAGE(IF((C19:C30="火")+(C19:C30="金"),E19:E30,""))}」
と入力すると、「1,950」と表示された。これは、C列の曜日から「火曜日」と「金曜日」の
売上高合計の平均を算出したものである。
AVERAGEIFS関数では、複数の条件を満たす平均が求められるが、AND条件でのみ求められる。
「火曜日」又は「金曜日」の売上平均を求めると言った、OR条件で平均を求めるには、
AVERAGE関数とIF関数を使用する。「A又はBである場合」のようなOR条件を満たす場合は、
平均する値の範囲を返す数式をIF関数で作成する。その返されたセル範囲を基にAVERAGE関数
で平均を求める仕組みである。但し、すべてのセル範囲を指定して配列として扱う為、配列数式
で求める必要がある。配列数式ではOR関数が使えない。
そこで、IF関数の「論理式」で指定するOR条件式は、条件ごとに「( )」で囲み、「+」の演算子
でつないで指定する。尚、AND条件式では「＊」の演算子でつないで指定する。

上図のセルC12に、「{=AVERAGE(IF(MOD(ROW(C4:C11),2)=0,C4:C11,""))}」と入力する
と、「33,795,000」と表示された。これは、C4からC11までのデータの、売上金額だけの平均を
算出したものである。すなわち、4行目から下に一行おきにデータを抽出して、平均を算出した
ものである。MOD関数とROW関数の使用で、条件式を作成することができる。
偶数行を条件にするのなら、「2で除算した余りが0」、奇数行を条件にするのなら、「2で除算
した余りが1」の場合と言う条件を作成する。この余りを求めるのがMOD関数である。
条件式をIF関数の「論理式」に使い、「真の場合」に平均するセル範囲、「偽の場合」は何も
しない「""」を指定する。数式全体をAVERAGE関数の引数に使うと偶数行、奇数行の平均が
求められる。但し、すべてのセル範囲を指定して配列として扱うために、配列数式で求める必要
がある。数式を入力したら、数式バーのカーソルが後部にあるときに、[Ctrl]+[Shift]キーを押し
ながら、[Enter]キーを押すと「{ }」が入力される。

上図のセルG4に、「AVERAGEIFS(E4:E15,C4:C15,"火",D4:D15,"神奈川")」と入力すると
「2,150」と表示された。これは、支店名「神奈川」の「火曜日」の売上の平均を算出したも
のである。
AVERAGEIFS関数は、「AVERAGEIFS(平均対象範囲,条件範囲1,条件1,条件範囲2,条件2)」と
引数を指定すると、「引数の「条件1」と「条件2」の両方を満たす値の平均が求められる。
ここでは、5行目と10行目だけなので、その合計を2で割った値が平均値になる。

上図のセルE4に、「AVERAGEIF(C4:C15,”>＝”＆PERCENTILE(C4:C15,0.9),C4:C15)」と
入力すると「3,550」と表示された。
これは、C列の売上高の上位10％だけの値の平均を算出したものである。
手計算の確認で、上位10％はC14とＣ１５セルなので、合計して2で割って算出した数値と
同じ値であるのとが分かる。
PERCENTILE関数は、「配列」に指定した値を下から数えて、百分率で「率」に位置する値
を返すので、下位から90％の位置にある値を求めることができる。その値「以上」という
「条件」としてAVERAGEIF関数に指定すれば、上位10％の平均が求められる。
但し、AVERAGEIF関数の「条件」に、関数で求めた値「以上」など、関数と比較演算子を
一緒に使った条件を指定する場合は、演算子を「""」(ダブルクオーテイション)で囲み、関数
と「＆」でつないで使用する。

上記のセルJ27に、「=AVERAGEIF(J17:J26,"＜＞0",J17:J26)」と入力すると「73.22」
と表示された。
これは、点数列から「0点」を除いた9個の合計を「9」で割ったものである。
AVERAGEIF関数は、引数の「条件」に「0以外」と言う条件を「"＜＞0"」の様に「"」
(ダブルクオーテイション)で囲んで指定する。0ではないと言う条件には「0より大きい」と
言う意味の「"＞0"」、1以上という意味の「"＞=1"」などの指定方法がある。

上図は、セルJ28に「AVERAGE(J17:J26)」と入力されたもので「65.90」と表示されている。
これは、0を含む10個の点数合計を、10で割ったものである。AVERAGEIF関数との違いである。

上記は、点数の中から男性、女性の平均を別々に算出したものである。
AVERAGE関数は値の平均を求めるが、試験結果から男性だけの点数平均を求めたり、
日々の売上から特定の曜日や、支店の平均を求めたりといった、特定の値だけの平均を
求めたい時もある。そこで、指定した「条件」に該当する値のみの平均を求めるときに
は、AVERAGEIF関数を使う。
J27セルに、「AVERAGEIF($I$17:$I$26,I27,$J$17:$J$26)」と入力したら「74.20」
と表示された。
引数に、「範囲」I17～I26セルの性別の範囲を指定し、「条件」にI27の求めたい性別を
選択、「平均対象範囲」にJ17～J26の点数範囲を指定する。
J27セルをJ28セルにコピーすれば、女性の平均点も求められる。
このように、2つの条件を同時に求めるには、引数の「条件」に直接入力せず、条件が
入力されたセルを指定することで、男女二つの平均が求められる。

上記は、過去6年間の売り上げの伸び率の平均を求めたものである。
GEOMEAN関数は、「相乗平均(幾何平均)」を求める関数である。
相乗平均とは、すべての値を乗算して、その個数のべき乗根で求める平均である。
売り上げの伸び率の平均を求める場合に使用する。
GEOMEAN関数を使って伸び率の平均を求めるには、下図のようにあらかじめ年毎に
「今年度／前年度」の数式で「前年度比」を求めておく。
セルD4に、「=C4/C3」と入力し、それをD8セルまでコピーする。

その前年比を基に、セルD9に「GEOMEAN(D4:D8)」と引数にD4:D8を指定する。
GEOMEAN関数の引数は、「=GEOMEAN(数値1)」である。

上記は、上限値の100点と下限値の20点を、除いた平均点を算出したものである。
TRIMMEAN関数の引数は、全体の値から指定した割合のデータを除いて平均を求める
関数で、「割合」に「0.2」を指定すると、上位の値から10％、下位の値から10％の、
上下合わせて20％のデータを除いた値で平均を求める。

AVERAGE関数は平均を求める範囲にある、文字列や空白セルは計算対象に含まれない。
だから、上記の場合は7つのデータの平均点である。
AVERAGE関数は、すべての値の和をその個数で割って求める平均で、「相加平均」
又は、「算術平均」と呼ばれる。