Excel。最大値のデータのある行全体を塗りつぶしたい。【MAX】

Excel。最大値のデータのある行全体を塗りつぶしたい。

＜条件付き書式＋MAX関数＞

最大値のデータを見つけるには、条件付き書式にある、「上位10項目」をつかうことで手早く書式を設定することができます。

ただ、該当するデータのみなので、大きな表だった場合、どのデータなのかわかりにくいことがあります。

そこで、行全体を塗りつぶすことで、わかりやすくすることができます。

今回用意したのが、次の表です。

D列の販売金額の最大値の行全体を塗りつぶしたいわけです。

そこで、ホームタブの条件付き書式にある「新しいルール」をつかって設定をします。

A2:D8を範囲選択します。

ホームタブの条件付き書式にある「新しいルール」を選択します。

新しいルール書式設定ダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」を選択して、条件のボックスに数式を設定します。

=MAX($D$2:$D$8)=$D2

あとは、書式ボタンをクリックして、塗りつぶしたい色を設定したら、OKボタンをクリックします。

これで、最大値のデータ。行全体を塗りつぶすことができました。

では、設定した条件式を確認してみましょう。

=MAX($D$2:$D$8)=$D2

最大値を算出する必要がありますので、MAX関数を使います。

その値が、D2と等しいのかを確認します。

条件に合致すれば、1。

合致しなければ0を返します。

Excelでは、1=TRUEなので、条件を満たしたということですから、書式が反映されるというわけです。

なお、$D2という列固定の複合参照にすることで、行全体を対象にすることができます。

Excel。MAX関数は最大値を算出します。【MAX】

Excel。MAX関数は最大値を算出します。

＜関数辞典：MAX関数＞

MAX関数

読み方： マックス　　

分類： 統計 

MAX(数値1,[数値2],…)

最大値を算出します

Excel。データ間の差が一番大きな値の行全体を塗りつぶすには、どうするの。【Data Differences】

Excel。データ間の差が一番大きな値の行全体を塗りつぶすには、どうするの。

＜条件付き書式＋MAX+ABS関数＞

前年度と今年度の差がどのぐらいなのか、わかりやすい資料をつくりたい。

条件によって行全体を塗りつぶしたいので、条件付き書式をつかいます。

では、条件式をどのようにしたらいいのでしょうか。 

D列の差というのは、プラスであろうが、マイナスであろうが、前年度と今年度の値が離れているものなので、単純に減算した結果というわけにはいきません。

=D2-C2

の結果だけで、条件付き書式の条件式では、対応することができません。

プラスとマイナスに関係なく、数値としての差を条件にしたいので、絶対値をつかえばいいわけです。

絶対値を求めるには、ABS関数をつかえばいいわけです。

では、条件付き書式を設定していきます。

A2:D6を範囲選択します。

ホームタブの条件付き書式にある、新しいルール　をクリックします。

新しい書式ルールダイアログボックスが表示されます。

数式を使用して、書式設定するセルを決定　をクリックします。

条件式のボックスに

=max(abs($D$2:$D$6))=$D2

と数式を設定します。

あとは、書式ボタンをクリックして、塗りつぶしたい色を設定します。

これで、一番大きな差の行全体を塗りつぶすことができました。

設定した条件式を確認していきます。

=MAX(ABS($D$2:$D$6))=$D2

MAX関数は最大値を求める関数です。

MAX関数の引数にABS関数をつかっています。

このABS関数は、絶対値を求めることができる関数です。

ABS関数をつかって、D2:D6の絶対値を求めます。

MAX関数で絶対値にした、最大値を求めます。

その結果とD列のデータが合致していれば、差が一番大きいデータということがわかります。

また$D2と列固定の複合参照にすることで、行全体を塗りつぶしの対象にすることができます。

Excel。最大値と最小値を除いた平均値をLET関数で算出してみる【Excluding】

Excel。最大値と最小値を除いた平均値をLET関数で算出してみる

＜SUM+MAX+MIN+COUNT関数・LET関数＞

最大値と最小値を除いた平均値は、AVERAGE関数では算出することができません。

AVERAGE関数をつかわないで、平均値を算出するには、範囲の総和を範囲の総和した件数で除算すれば、算出することができます。

C2:C6まで、売上金額が入力されています。

この範囲の最大値と最小値を除いた平均値を算出するために、D8に数式を設定しました。

=(SUM(C2:C6)-MAX(C2:C6)-MIN(C2:C6))/(COUNT(C2:C6)-2)

長いですが、シンプルな関数しかつかっていません。

だけど、C2:C6という範囲選択が何度も登場してきます。

数式を説明すると、

SUM(C2:C6)-MAX(C2:C6)-MIN(C2:C6)

SUM関数で、合計を算出します。

その値から、MAX関数で最大値をMIN関数で最小値を減算します。

この値を

COUNT(C2:C6)-2　の算出結果で除算します。

COUNT関数で数値の件数を算出することができます。

「-2」するのは、最大値と最小値のデータを除いた件数にする必要があるからです。

この数式で算出して、何ら問題はありません。

今回は、この数式をLET関数で、算出してみます。

LET関数は、数式内に「名前の定義」というか、「変数」を設定することができる関数です。

ただ、どのように使うのか、わかりにくい関数といわれています。

そこで、比較的わかりやすい　範囲選択　を名前に設定してみます。

D9にLET関数をつかって数式をつくります。

=LET(rg,C2:C6,(SUM(rg)-MAX(rg)-MIN(rg))/(COUNT(rg)-2))

数式の文字数としては、差はあまりありませんが、C2:C6を何度もつかわないので、可読性は改善されています。

では、この数式の説明をします。

LET関数の最初の引数は、名前。

プログラムでいうところに変数。あるいは、名前の定義です。

今回は、範囲選択なので、Excel VBAでお馴染みのRangeの「rg」としました。

2つ目の引数は、式。

式となっていますが、数式でもいいし、範囲でも指定できます。

繰り返し設定するところを設定するといいので、「C2:C6」としました。

この名前と式を複数設定することも出来ます。

3つ目は、計算式。

SUM(rg)-MAX(rg)-MIN(rg))/(COUNT(rg)-2

C2:C6だったところが、rgという名前を設定しましたので、rgにすることで、C2:C6と入力しなくてもいいわけです。

このように、LET関数をつかうと、複雑な数式の時に、可読性の改善と、数式もコンパクトにすることができるかもしれません。

Excel。データ内の差である範囲（レンジ）を手早く算出するにはどうするの。【range】

Excel。データ内の差である範囲（レンジ）を手早く算出するにはどうするの。

＜MAX・MIN関数＞

データ内にある年齢。

例えば、年齢差を知りたい場合、どのようにしたら、算出することができるのでしょうか？

範囲を一発で算出する関数はありません。

そこで、データ内の最大値から最小値を減算することで、算出することが可能です。

A2:A11にデータが用意されていますので、今回は、A2:A11のデータをつかって、範囲であるレンジを算出します。

D1には、

=MAX(A2:A11)-MIN(A2:A11)

これで、データ内の差である範囲（レンジ）を算出することができました。

平均値・最大値・最小値に中央値だけでなく、範囲も算出することで、どのようなデータなのかが少し見えてきます。

Excel。最大値がある行全体を手早く塗りつぶすには、どうしたらいいの。【conditional formatting】

Excel。最大値がある行全体を手早く塗りつぶすには、どうしたらいいの。

＜条件付き書式＋MAX関数＞

ホームタブにある、条件付き書式「上位/下位ルール」の「上位」をつかうことで、最大値のデータがあるセルに、塗りつぶすなど、書式を設定することができます。

では、セルではなく、データ全体。

つまり、行全体を塗りつぶすには、どのようにしたら、いいのでしょうか。

行全体を対象にするには、条件を数式で作る必要があります。

A2:F10を範囲選択します。

ホームタブの条件付き書式にある「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」を選択して、ルールを設定していきます。

「次の数式を満たす場合に値を書式設定」のボックスに、次の数式を設定します。

=MAX($F$2:$F$10)=$F2

あとは、書式ボタンをクリックして、塗りつぶす色を設定したら、OKボタンをクリックします。

これで、最大値のデータがある行全体を塗りつぶすことができました。

Excel。季節変動や採点競技でお馴染みのトリム平均を算出したい【trimmed average】

Excel。季節変動や採点競技でお馴染みのトリム平均を算出したい

＜SUM＋MAX＋MIN＋COUNT関数＞

データ内から最高点と最低点を除いた平均値であるトリム平均値を算出するには、単純にAVERAGE関数では算出することはできません。

理由は簡単で、最大値と最小値を除くことができないからです。

そこで、データの総和と、その総和したデータの個数で除算した数値が平均値です。

そのデータの総和から最大値と最小値を減算してあげたものを、データの個数から「-2」した値を除算すれば、トリム平均値を算出することができるというわけです。

次のデータを使って確認しておきましょう。

E1には、AVERAGE関数の数式を設定してあります。

=AVERAGE(B2:B9)

E2には、次の数式を設定することで、トリム平均値を算出しています。

=(SUM(B2:B9)-MAX(B2:B9)-MIN(B2:B9))/COUNT(B2:B9)-2

関数が多く登場してますが、いたってシンプルです。

SUM関数は総和を算出しています。

そこ値からMAX関数の最大値とMIN関数の最小値を減算します。

その値を、COUNT関数をつかって総和した件数から最大値と最小値の2件を減算した値で除算したのが、E2の算出結果である。

トリム平均値ということになります。

トリム平均値は、外れ値を除いた平均値を算出したいときにも使用する数式です。

Excel。分析ツールの基本統計量を関数で算出してみよう。【Basic statistics】

Excel。分析ツールの基本統計量を関数で算出してみよう。

＜AVERAGE・STDEV.S・SQRT・COUNT・MEDIAN・MODE.SNGL・VAR.S・KURT・SKEW・MAX・MIN・SUM関数＞

アドインでデータ分析にある基本統計量をつかうことで、データのステータスを手早く確認することができます。

G列とH列に表示されているのが、C列のListeningの基本統計量です。

H列の基本統計量の値は、関数でも算出できるので、確認しておきましょう。

H2の平均は、AVERAGE関数です。

=AVERAGE(C2:C11)

標準誤差は、標準偏差をデータの件数の平方根で除算した値です。

=STDEV.S(C2:C11)/SQRT(COUNT(C2:C11))

で算出することができます。

STDEV.S関数は、「S」なので、数値をサンプルとした標準偏差を算出する関数です。

SQRT関数は、平方根を算出する関数です。

COUNT関数は、数値の件数を算出関数です。

中央値は、MEDIAN関数で算出できます。

=MEDIAN(C2:C11)

最頻値は、MODE.SNGL関数で算出できます。

最頻値は、データ内で一番多く登場した数値のことですね。

=MODE.SNGL(C2:C11)

標準偏差は、先程紹介した、STDEV.S関数で算出します。

=STDEV.S(C2:C11)

標準偏差は、データが平均値からどのぐらい外れているか（散っているのか）を表します。

分散は、VAR.S関数で算出します。

=VAR.S(C2:C11)

尖度（せんど）は、KURT関数で算出できます。

=KURT(C2:C11)

尖度は、正規分布を元に上下にどの程度偏っているかを表す値です。

歪度（わいど）は、SKEW関数で算出できます。

=SKEW(C2:C11)

歪度は、正規分布を元に左右にどの程度偏っているかを表す値です。

範囲は、最大値と最小値の範囲（レンジ）です。

最大値から最小値を減算すれば算出できます。

=MAX(C2:C11)-MIN(C2:C11)

最小は、MIN関数ですね。

=MIN(C2:C11)

最大は、MAX関数ですね。

=MAX(C2:C11)

合計は、SUM関数ですね。

=SUM(C2:C11)

最後は、データの個数なので、COUNT関数です。

=COUNT(C2:C11)

このように、基本統計量は様々な関数をつかって算出しています。

算出する項目が多いため、面倒なので、分析ツールをつかえるようならば、つかったほうが、楽なような気がします。

Excel。データ間の差が一番大きなデータの行全体を塗りつぶしたい【Numerical difference】

Excel。データ間の差が一番大きなデータの行全体を塗りつぶしたい

＜条件付き書式＋MAX+ABS関数＞

データ間の差が、最大のものを確認したい場合、目視で行うとしたら、容易ではありません。

そこで、該当するデータの行全体を塗りつぶすことで把握するにはどのようにしたらいいのでしょうか。

使用するデータのE2には、

=C2-D2

という数式が設定されていて、オートフィルで数式をコピーしています。

データ全体を塗りつぶすということですから、「条件付き書式」をつかっていきます。

A2:E11を範囲選択して、ホームタブにある「条件付き書式」の「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」を選択します。

「次の数式を満たす場合に値を書式設定」のボックスに、次の数式を設定します。

=MAX(ABS($E$2:$E$11))=$E2

あとは、書式ボタンをクリックして、塗りつぶす色を設定して、OKボタンをクリックすれば完成です。

それでは、設定した数式を確認しておきましょう。

「差」から考えていきます。

当然のことながら、前回の数値よりも今回のほうが大きければ差は「プラス」になり、小さければ「マイナス」になります。

記号を除いた「値」にすれば、値の高低を確認することができます。

この記号を取り除いた値。

すなわち「絶対値」を算出する必要があります。

Excelで絶対値を算出するには「ABS関数」をつかうことで算出することができます。

その結果を該当する範囲内の「最大値」を求めたいので、MAX関数と組み合わせてあげればいいわけです。

そして、算出された差の最大値がE2と合致するのかどうかを判断させたいので

「=$E2」をつけています。

なお、列を固定した複合参照にすることで、行全体を塗りつぶすことができます。

Excel。算出結果が負ならゼロに置換するなら、MAX関数を使う方法もあるんです。【Replace minus with zero】

Excel。算出結果が負ならゼロに置換するなら、MAX関数を使う方法もあるんです。

＜MAX関数＞

最大値を算出する時に使用するのがMAX関数ですが、最大値を算出するだけにつかうのはもったいないわけです。

次のようなケースでもMAX関数を使うと、簡単に結果を算出することができます。

この表についてと、やりたいことを説明していきます。

B3の在庫数は、B1の在庫数からB2の消費予定数を減算した結果です。

800から1000を減算しますので、結果は、「-200」と算出されます。

しかし、実際の在庫で「マイナス」というのはありえません。

そこで、算出結果が負数になったらば、「0（ゼロ）」と置換したいわけです。

それが、D列です。

では、どのような計算式をつくればいいのでしょうか？

マイナスならば、「0（ゼロ）」そうでなければ、減算というように考えるはずです。

このような考え方だと、使うのは、IF関数で算出できそうです。

もし、D3にIF関数で数式をつくるとしたら、

=IF(D1-D2<0,0,D1-D2)

という数式を設定するわけです。

これで、問題はありませんが、「D1-D2」という減算する計算式を繰り返し作る必要があるわけです。

簡単な数式なので、困ることはありませんが、長い計算式だったときは、二度同じ計算式をつくるのは、ちょっと面倒ですし、ミスをする可能性も高くなります。

これに比べて、MAX関数をつかった場合、もっとシンプルな数式で、同じ結果を求めることができます。

今回D3にMAX関数をつかって設定した数式は、

=MAX(D1-D2,0)

とても、シンプルな数式ですね。

しかし、これで、先程のIF関数と同じように結果を求めることができます。

D1-D2の減算結果が、100だったら、100と0を比べて、MAX。

つまり、大きい方を算出しますので、100を返します。

D1-D2の減算結果が、-200だったら、-200と0を比べて、MAX。

つまり、大きい方を算出しますので、0を返すというわけです。

IF関数でも、問題ありませんが、可読性とか、修正しやすいなどを考えると、シンプルな数式で同じ結果を算出できるようでしたら、シンプルな数式を使ってみるというのも悪くないかもしれません。

日頃、使用している表で使っている関数の数式も、もしかしたら、別の関数を使用することでシンプルな数式にすることができるかもしれません。

何かの機会に見直してみるというのもいいかもしれませんね。

Excel関数辞典 VOL.48。MATCH関数～MDURATION関数【dictionary】

Excel関数辞典　VOL.48。MATCH関数～MDURATION関数

＜Excel関数＞

今回は、LOGINV関数～LOWER関数までをご紹介しております。

MATCH関数

マッチ

値を検索してその相対位置を算出

MATCH(検査値,検査範囲,[照合の種類])

MAX関数

マックス

最大値を算出

MAX(数値1,[数値2],…)

MAXA関数

マックスエー

数値・文字列・論理値を含む最大値を算出

MAXA(数値1,[数値2],…)

MAXIFS関数

マックスイフズ

条件により指定した範囲内の最大値を算出

MAXIFS(最大範囲,条件範囲1,条件1,…)

MDETERM関数

マトリック ディターミナント

配列の行列式を算出

MDETERM(配列)

MDURATION関数

エムデュレーション

額面価格を $100 と仮定して、証券に対する修正マコーレー デュレーションを返します。

Modified DURATION

MDURATION(受渡日,満期日,利率,利回り,頻度,基準)

Excel。最大値のデータが複数あっても、行全体で塗りつぶしをしたい【Multiple maximums】

Excel。最大値のデータが複数あっても、行全体で塗りつぶしをしたい

＜条件付き書式＋MAX関数＞

大量のデータがある場合、どこにあるのかわかりやすくしたいので、該当するデータの行全体に対して塗りつぶしを設定したいのですが、どのようにしたらいいのでしょうか？

次のデータがあります。

B列のデータから最大値のデータのセルだけを塗りつぶすならば、条件付き書式のボタンだけで簡単に設定することができます。

B2:B11を範囲選択して、ホームタブの「条件付き書式」にある「上位/下位ルール」の「上位10項目」をクリックします。

上位10項目ダイアログボックスが表示されます。

最大値が対象なので、「1」として、書式を選択したら、OKボタンをクリックします。

複数データがあった場合でも、最大値のデータであれば、書式を反省させた状態にすることができます。

しかしながら、これは、あくまでも該当の「セル」に反映されるだけです。

やりたいのは、行全体を塗りつぶしたいわけです。

条件付き書式に用意されているリボンのメニューだと、セルが対象になっているだけで、行全体が対象になっていません。

このような場合は、条件付き書式の設定に数式を使うことで対応することができます。

では、改めて、A2:B11を範囲選択して、ホームタブの「条件付き書式」にある「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

ルールの種類を「数式を使用して、書式設定するセルを決定」にして、次の数式を満たす場合に値を書式設定のボックスに、次の数式を設定します。

=$B2=max($B2:$B11)

数式の説明はあとにまわします。

書式ボタンをクリックして、塗りつぶしを設定しましょう。

あとはOKボタンをクリックします。

このように、最大値のデータの行全体に塗りつぶしを設定することができました。

では設定した数式を確認していきましょう。

=$B2=max($B2:$B11)

セル番地のすべてが列固定の複合参照にします。

列固定の複合参照にすることで、行全体が設定対象になります。

条件付き書式をつかって行全体を塗りつぶししたいということでしたら、行固定の複合参照と覚えておくといいですね。

B2の値が、B2:B11の中でのMAX（最大値）の値と同じだったらば、条件付き書式が反省されるという意味の数式です。

これで、大きなデータであっても、瞬時にデータ内の最大値を含むデータであることが一目でわかるようになります。

条件付き書式は、数式を使ったルールをつかうことで、視覚的にわかりやすい資料をつくることができますので、色々試してみるといいかもしれませんね。

Excel。最高値を検索できても、その行にある他のデータを抽出するには？【MAX】

Excel。最高値を検索できても、その行にある他のデータを抽出するには？

＜MAX・OFFSET+MATCH関数＞

データの中から、最大値や最小値などは、MAX関数やMIN関数を使うことで、簡単に抽出することができますが、では、その該当したデータのレコードはどうやったら、抽出することができるのでしょうか？

例えば、次の表。

C列の金額の中で最大値を算出するのはMAX関数を使うので、なんら問題はありませんが、では、その日付はいつなのか？

条件付き書式をつかって行（レコード）を塗りつぶすように設定してもいいのですが、ただ単に日付が知りたい場合どうしたらいいのか？というのが今回のテーマなのです。

まずは、F1にMAX関数を使って、最大値を算出してみます。

F1の数式は、
=MAX(C2:C16)
さて、474は、3月4日に発生しているのですが、どうやったらいいのでしょうか？

検索させるとしたら、最初に、474という最大値は、何行目にあるのか算出させる。

そして、474というセルの1つめの左側に日付のデータがあるということですよね。

なので、まず何行目にあるのか確認するにはどうしたらいいのでしょうか？
そこで登場するのが、MATCH関数です。

G1にMATCH関数の結果を表示してみましょう。
G1をクリックして、MATCH関数ダイアログボックスを表示します。

検索値には、最大値の結果が算出されている、F1
検索範囲は、金額なので、C2:C16
照合の種類ですが、完全一致なので”0（ゼロ）”。

ちなみに、”1”だと、以下。”-1”だと、以上となっています。

G1の数式は、
=MATCH(F1,C2:C16,0)

結果は、4と表示されました。4件目に該当するデータがあることが算出されたわけです。

その該当するセルの1つ左側のセルを見つけるために、今度は、OFFSET関数をつかうことで、算出することができます。

では、F2をクリックして、OFFSET関数ダイアログボックスを表示しましょう。

参照は、C1。見出しじゃないかと思うかもしれませんが、OFFSET関数は、参照である起点を０（ゼロ）としています。

つまり、データの先頭であるC2を参照（起点）として、4つ下方向のセルだと、C6を指してしまいますので、C1の見出し行を参照（起点）とする必要があります。

行数は、G1に先ほどMATCH関数で算出しましたので、G1を設定します。

列数は、1つ左側なので、-1(マイナス1)と設定して、OKボタンをクリックしましょう。

F2の数式は、
=OFFSET(C1,G1,-1)
算出結果が、シリアル値で表示されてきますので、表示形式を日付にするといいでしょう。

結果は、このようになりましたね。

このように、OFFSET関数とMATCH関数を合わせて使うことで、該当するセルの行（レコード）からデータを抽出することが可能になりますよ。

ちなみに、OFFSET関数とMATCH関数をネストにすると、
=OFFSET(C1,MATCH(F1,C2:C16,0),-1)

さらに、OFFSET関数とMATCH関数とMAX関数をネストすると、
=OFFSET(C1,MATCH(MAX(C2:C16),C2:C16,0),-1)

確かに、1つの数式で算出することができますが、理解するのに時間がかかりますので、実務では、ほどほどがいいように思えますね。

Excel。事務職のデータ分析27。最初は数値データの全体像を把握しましょう。【Numeric data】

Excel。事務職のデータ分析27。最初は数値データの全体像を把握しましょう。

＜MAX・MIN・AVERAGE・TRIMMEAN・MEDIAN・MODE.SNGL・COUNT・MODE.MULT＞

色々な統計分析についての書籍があって読むけど、
最初に何をしたらいいのかが、よくわからないと耳にしますので、
あまりExcelを使っていないけど、どのような数値を会議資料として、
分析用の資料として用意すればいいのかを改めて、簡単に説明していきましょう。

【全体像の特徴を知る】

数値データではわからないので、
全体像の特徴をはあくすることから始めるといいですね。

簡単に算出できるものが多いので、重要性を感じないかもしれませんが、
大切な数値だったりします。

次のデータがあります。

単に数値が並んでいるだけですが、最小値と最高値を知るところから始めます。

当然、MAX関数とMIN関数を使うわけですが、
この2つの項目を知るだけでも、重要です。

日々の日報だったら、なぜこの日だけ売上高がよくないのか？
ということがわかるわけですよね。

改善点を見つけることに繋がるかもしれませんよね。

なので、単純であっても、重要です。

E1の最大値の数式は、
=MAX(B2:B26)
E2の最小値の数式は、
=MIN(B2:B26)

続いて、算出したのは、平均値です。

E4の平均値の数式は、
=AVERAGE(B2:B26)
この平均値ですが、ちょっと難しくいうと、
算術平均とか相加平均といったりします。

相加とは、すべての数値の合計をその数値の個数で除算したもののことですね。

ところが、おおむね平均値に数値があるわけではなく、
異常時というケースが含まれていることが多いので、
それを取り除いた平均値も算出したほうがいいわけですね。

例えば、急にブームになった食品とかがあって、
瞬間的にバカ売れしたりすると、平均値に影響を与えてしまうからです。

では、Excelで異常値を取り除いて算出する場合には、
TRIMMEAN関数を使います。

E5の数式は、
=TRIMMEAN(B2:B26,0.1)
割合を0.1=10%としましたが、
これは、上限0.05%・下限0.05%を意味しますので、注意が必要です。

平均値では、把握できないケースがあります。

そこで、全体の数値を並べてみて、
その中心の値を求める中央値というものを算出することも多くあります。

登場する関数は、MEDIAN関数です。

E7の数式は、
=MEDIAN(B2:B26)
金額が全体平均よりも小さいので、
販売金額は、安いほうに比重が大きいようですね。

【データ量が多くなると最頻値がほしくなる】

大量のデータから特徴を見つけ出すのに、
一番多く登場している数値を知ることができれば、
その数値に何かしらの理由。

すなわち購入しやすい価格なのか？などが見えてきます。

データの中で登場回数を算出するために使用する関数は、
MODE.SNGL関数です。

登場する回数が多い数値は、3200だそうですが、
よくみると、29600もデータが2つありますので、最頻値のはずですよね。

このMODE.SNGL関数。欠点があって、
データの最初に登場するほうを算出しちゃうのです。

これでは、会議の資料としては使えません。

そこで、同率一位が、いくつのあるのかを算出して、
該当する数値も算出するといいですよね。

では、同率一位がいくつあるのかを算出してきます。

E10の数式は、
=COUNT(MODE.MULT(B2:B26))

いくつのあるかということなので、COUNT関数。

そして、複数の最頻値を算出することができる、
MODE.MULT関数をネストすると算出することができます。

算出された結果は2なので、2件あるということがわかりました。

E11に、MODE.MULT関数ですが、
{}がついていますが、配列関数にしています。

そこで、この数式の作り方ですが、
最初に、E11:E12を範囲選択します。

先ほどの同率一位の数が5だったら、5つのセルを範囲選択します。

今回は、2と算出されたので、2つのセルを範囲選択しているわけです。

=MODE.MULT(B2:B26)

と入力したら、Enterキーを押さずに、
Ctrl+Shift+Enterキーで配列関数にして完成です。

これで、2つの最頻値を算出することができました。

まずは、このような数値を用意するだけでも
日ごろの資料がグレードアップします。

Excel。確定申告の医療費などでお馴染みのマイナスなら0を選択する方法【Tax return】

Excel。確定申告の医療費などでお馴染みのマイナスなら0を選択する方法

【MAX関数】

よく、確定申告の医療費などで、
数値を算出した結果が0（ゼロ）より小さい場合は、
そのマイナス値ではなくて、0（ゼロ）にするというものがあります。

では、次の表を見てみましょう。

このような場合、IF関数を使って算出することが多いかと思います。

例えば、B9にIF関数を使って、数式を作るとしたら、

論理式には、B7-B8<0
真の場合には、0
偽の場合には、B7-B8

として、OKボタンをクリックして確認してみると、
B9は0になっているのが確認できます。

数式は、
=IF(B7-B8<0,0,B7-B8)

確かに、IF関数を使った方法で算出できるので、全く問題はないのですが、
論理式や、偽の場合の数式がもっと、高度で煩雑だった場合、
2回同じ数式を作るというのは、面倒ですし、
修正する場合でも修正個所が2つ発生する可能性もありますね。

そこで、IF関数でもいいのですが、
もっと簡単な関数で代用することが可能なので、
その方法をご紹介していきましょう。

登場するのは、【MAX関数】

どうしてもMAX関数は最大値というイメージがありますのでMAX関数？
と思うかもしれませんね。

今回のケースは、算出結果が、
負の場合は、マイナス数値と0（ゼロ）を比べて0（ゼロ）を選択します。

算出結果が、正の場合は、0（ゼロ）と正の数を比べて、
正の数を選択するという動きになればいいわけです。

すなわち、0（ゼロ）と比べてあげて、
数値の高いほうを採用すればいいわけです。

では、B9をクリックして、MAX関数ダイアログボックスを表示しましょう。

数値1には、B7-B8
数値2には、0
OKボタンをクリックしましょう。

B9の数式は、=MAX(B7-B8,0)となっていますね。

では、結果はどうなっているか確認してみましょう。

B7-B8なので、-5000と算出結果が負のため、0と比べて大きいほう、
すなわち、0を表示していますね。

このMAX関数を使った数式ですが、通常MAX関数を使うときは、
オートSUMボタンの▼をクリックして、
その中にある、最大値を使います。

その場合範囲選択をして、
その中での最大値を算出するということが多いかと思いますが、
MAX関数ダイアログボックスを表示するとわかるのですが、
数値1、数値2となっていて、
それぞれの引数を設定することで、比較して、
そのうちの最大値を算出することが出来ることがわかります。

では、B7を30000と入力してみて結果を確認してみましょう。

このケースは、B7-B8で算出される値は、100000なので、
その値と0を比較して最大値を算出しますので、
100000を算出してくれるわけです。

今回のように、IF関数に頼りがちになりますが、
別の関数や別のExcelの機能を使うことで、
わかりやすくなるケースもありますので、
いろいろ試してみると意外な方法が見つかるかもしれませんね。

Excel。事務職のデータ分析その3。求めるのは簡単だけど『範囲』は大切【RANGE】

Excel。事務職のデータ分析その3。求めるのは簡単だけど『範囲』は大切

＜MAX関数・MIN関数＞

様々な分析手法はわからなくても、一般職や事務職でも、
会議資料の一つとして、データ分析用の資料作りをすることもあるようです。

そこで、事務職のデータ分析と題して、ご紹介していきたいと思っております。

【RANGE(レンジ)について】

前回は、【中央値・最頻値】をご紹介しましたが、3回目は、【範囲】。
つまり『RANGE(レンジ)』についてです。

RANGEとは、データが分布してある【範囲】のことです。

この範囲は、そのデータの「振り幅」を表しています。

例えば、毎年なぜこの時期の売上は低いのに、
この時期は高いなど対応策を考えるときに必要になったりもします。

なんか、小難しく書いてしまいましたが、
単純に最大値と最小値を求めて、
その差が【範囲】ということになりますので、
算出そのものは、難易度は高くありません。

では、次のデータのRANGE（範囲）を求めてみましょう。

C列の年齢を降順で並び替えをしてもいいのですが、
データはそのままで、F2にRANGEを算出していきましょう。

RANGEの公式は、最大値-最小値=RANGEですから、
F2をクリックして、MAX関数ダイアログボックスを表示しましょう。

数値1は、C3:C22
OKボタンをクリックします。

ここまでの数式は、
=MAX(C3:C22)

そして、最小値を減算しますので、
-「マイナス」を入力して、次は、MIN関数ダイアログボックスを表示します。

数値1は、C3:C22
OKボタンをクリックします。

ここまでの数式は、=MAX(C3:C22)-MIN(C3:C22)
F2には、51と算出されました。

よって、このデータの振り幅は、51。

つまり年齢差は51歳あるということになります。

数式自体は、関数の初歩の初歩なので、なんら難しくはありませんが、
平均値同様に、このような基本的な関数を使って算出したデータが、
分析するための資料の一つになって行きます。
基本は大事。
簡単な関数をバカにしてはいけないということなのでしょうね。

意外かもしれませんが、このRANGE。

ビジネスシーンにでは、様々なデータの範囲をあらかじめ把握することが出来るので、
その対応策を検討することが可能になります。

このことは、とても重要なコトだといわれております。

ここまで、
平均値を算出するAVERAGE関数
中央値を算出するMEDIAN関数
最頻値を算出するMODE.SNGL関数

そして、
範囲（RANGE）を算出する、MAX関数-MIN関数

と簡単な関数ですが、資料作成には欠かせない関数ですので、
改めてご紹介させていただきました。

次回は、【標準偏差】の算出方法をご紹介していきます。