Excel。LARGE関数は指定した○番目に大きい値を算出します【LARGE】

Excel。LARGE関数は指定した○番目に大きい値を算出します

＜関数辞典：LARGE関数＞

LARGE関数

読み方： ラージ　　

分類： 統計 

LARGE(配列,順位)

指定した○番目に大きい値を算出します

Excel。上位1位～3位までのデータがわかりやすいように、行全体を塗りつぶすには【TOP3】

Excel。上位1位～3位までのデータがわかりやすいように、行全体を塗りつぶすには

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

似ているデータが多い時に上位1位～3位までのデータを把握したいとします。

そこで、上位1位～3位のデータならば、行全体を塗りつぶすことで、把握することにしました。

では、どのようにしたらいいのでしょうか。

上位1位～3位ということで、条件によって塗り分けるので、条件付き書式をつかうわけです。

あとは、どのような条件にすればいいのかという点です。

上位1位～3位を算出するには、LARGE関数をつかうことで判断することができます。

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

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

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

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

条件式を設定しますので、ルールの種類を「数式を使用して、書式設定するセルを決定」を選択します。

条件式のボックスに数式を設定します。

=$C2>=LARGE($C$2:$C$11,3)

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

OKボタンをクリックすれば、条件付き書式の設定は終了します。

これにより、上位1位～3位のデータの行全体を塗りつぶすことができます。

では、設定した条件式を確認します。

=$C2>=LARGE($C$2:$C$11,3)

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

これにより、行全体を塗りつぶすことができます。

LARGE($C$2:$C$11,3)

LARGE関数をつかうことで、2番目の引数で「3」と設定すれば、最初に設定した引数の範囲ないで、上位3番目の値を算出することができます。

上位3番目の数値が算出できたら、その値以上ならば、1位～3位ということがわかります。

そのために、LARGE関数をつかった条件式を設定したというわけです。

あと、$C$2:$C$11　というように、忘れずに、絶対参照を設定しましょう。

Excel。TOP1位～3位までのデータなら、行全体を塗りつぶししたい【TOP3】

Excel。TOP1位～3位までのデータなら、行全体を塗りつぶししたい

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

大量なデータからTOP1位～3位がわかるように、行全体を塗りつぶしたい場合、どのようにしたらいいのでしょうか。

このような場合は、条件付き書式をつかうのですが、条件付き書式で設定されている上位10項目を使用すると、セルは塗りつぶすことはできても、行全体に塗りつぶすことはできません。

そのため、条件式を設定する必要があります。

条件ですが、TOP3の売上金額がわかれば、それ以上の売上金額ならば、TOP1～3ということがわかります。

TOP3の値を算出にはLARGE関数をつかうことで、対応することができます。

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

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

ルールの種類を「数式を使用して、書式設定するセルを決定」をクリックします。

条件式のボックスに、

=$C2>=large($C$2:$C$16,3)

と設定します。

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

このように、TOP1～TOP3のデータならば、行全体を塗りつぶすことができました。

また同じ数値でもきちんと、塗りつぶされていることがわかります。

Excel。平均値だけでなく上位3位までの平均値を算出するにはどうしたらいいの。【TOP3】

Excel。平均値だけでなく上位3位までの平均値を算出するにはどうしたらいいの。

＜AVERAGEIF+LARGE関数＞

上位グループと下位グループに分かれているようなデータの場合、単純な平均値だけではなく、上位グループの平均値も合わせて算出したい場合があります。

そこで、今回は、上位3位までの平均値を算出する方法をご紹介します。

次のデータを用意しました。

A1:C10にデータがあります。

C2:C10に数値のデータがあります。この平均値を算出したのが、F1です。

F1の数式は、

=AVERAGE(C2:C10)

AVERAGE関数では、全体の平均値を算出することはできても、条件をつけた平均値を算出することができません。

そこで、上位3位という条件を含めることができる。

つまり単一条件の平均値を算出することができる、AVERAGEIF関数を使う必要があります。

F3に上位3位までの平均値を算出していきますので、次の数式を設定しました。

=AVERAGEIF(C2:C10,">="&LARGE(C2:C10,3))

これで、上位3位までの平均値を算出することができました。

AVERAGEIF関数の引数の説明をします。

最初の引数は、「範囲」です。

この範囲とは、2つ目の引数の条件が含まれている範囲のことです。

よって、C2:C10と設定します。

2つ目の引数の条件には、上位3位と意味する数式を設定する必要があります。

「上位」という順位に該当する値を算出（抽出）する場合には、LARGE関数を使います。

ただし、3位の値ではなく、1～3位までの数値である必要がありますので、比較演算子をつかいます。

そこで、

「”>=”」と比較演算子とLARGE関数を「&（アンパサンド）」で結合します。

3つ目の引数の、平均対象範囲は、1つ目の引数の範囲と同じなので、省略できますので、省略しました。

これで、上位3位までの平均値を算出することができます。

なお、ステップを踏んで算出すると次のようになります。

D列には、LARGE関数をつかって、3位の数値。246を表示してあります。

D2の数式は、

=LARGE($C$2:$C$10,3)

D10までオートフィルで数式をコピーします。

E2には、

=IF(C2>=D2,C2,"")

という数式をコピーします。

これで、上位3位までの数値だけにすることができます。この3件の平均値を算出するというわけです。

E11に設定した数式は

=AVERAGEIF(E2:E10,">0")

E11の結果と先程紹介した

=AVERAGEIF(C2:C10,">="&LARGE(C2:C10,3))

と同じになっていることが確認できます。

Excel。上位3位までのデータがわかるように、行全体を塗りつぶしたい【higher rank】

Excel。上位3位までのデータがわかるように、行全体を塗りつぶしたい

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

データ量が多い場合など、該当するデータがどこにあるのか、わかりやすくするために、セルを塗りつぶすだけよりも、行（レコード）全体を塗りつぶすほうが、より一層わかりやすくなります。

次の表のようにするにはどうしたらいいのでしょうか。

今回は、売上高が上位3位に該当する場合、行全体を塗りつぶすようにしてあります。

このように、条件がついた塗りつぶしを行いたい時には、「条件付き書式」をつかうわけですが、ポイントになるのが、条件をどのように設定したらいいのかということですね。

該当するセルだけを塗りつぶすだけならば、ホームタブの「条件付き書式」にある「上位/下位ルール」の「上位10項目」を使用すれば設定することができます。

しかしながら、行全体を塗りつぶしたいわけです。

そのため、条件式をつくってあげる必要があります。

上位3位ということは、第3位の売上高以上ならば、該当するといえるわけです。

第3位の値を算出するには、LARGE関数を使えば算出することができますね。

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

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

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

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

=$C2>=LARGE($C$2:$C$11,3)

あとは、書式を設定したらOKボタンをクリックして完成です。

設定した数式ですが、

LARGE($C$2:$C$11,3)で、第3位の売上高の数値を算出できます。

それ以上ならば、1位～3位であることがわかります。

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

Excel。上位3位まで平均を楽に算出するには、どうしたらいいの。【higher rank】

Excel。上位3位まで平均を楽に算出するには、どうしたらいいの。

＜AVERAGEIF＋LARGE関数＞

全体の平均を算出するだけならば、オートSUMボタンに含まれている、AVERAGE関数の「平均」をつかえば、すぐに算出することができます。

ただ、そのうち、上位3位までとか、限定した範囲の平均を算出する場合、どのようにしたら、楽に、手早く算出することができるのでしょうか。

次のデータを用意しました。

E1には、データ全体の平均がすでに算出されています。

E1の数式は、

=AVERAGE(B2:B11)

上位3位までの平均ということは、どうやって、上位3位までなのかを判断させる必要があります。

上位3位までのような算出をおこなうには、「LARGE関数」をつかうことで、対応できます。

そして、上位3位までだったら、平均を算出する対象ということで、条件付きの平均で、算出する必要があります。

単一条件の平均を算出するには「AVERAGEIF関数」をつかうと解決できます。

このようなことから、今回は、AVERAGEIF関数とLARGE関数のネストで対応することができそうです。

E2に数式を設定します。

=AVERAGEIF(B2:B11,">="&LARGE(B2:B11,3),B2:B11)

これで、上位3位までの平均を算出することができました。

それでは、ダイアログボックスを表示して、数式を確認しておきましょう。

最初の範囲と、平均対象範囲は、説明は不要だと思いますので、条件について確認します。

上位3位までということで、1位2位3位を算出しようと考えてしまいがちですが、1位などのランキングを算出するのは、LARGE関数ではなくて、RANK.EQ関数です。

上位3位という意味は、上位3位の数値以上なわけですね。

そのため、LARGE関数をつかって上位3位を算出した値以上という条件にすればいいわけです。

また、比較演算子は、「&（アンパサンド）」で結合する必要があります。

「">=LARGE(B2:B11,3)”」と「”（ダブルコーテーション）」で全体を囲いがちですが、これでは、文字になってしまうので、条件が合致しませんので、比較演算子を使用する時は注意する必要があります。

今回のケースは、「上位」でしたが「下位」を条件に使う場合には、「SMALL関数」をつかうことで対応することができます。

単純な平均だけではなくて、他の関数と組み合わせることで、必要とする平均を求めることができるかもしれませんので、色々試してみるといいかもしれませんね。

Excel。上位3位までのデータがわかりやすいように、行全体を塗りつぶしたい【line fill】

Excel。上位3位までのデータがわかりやすいように、行全体を塗りつぶしたい

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

上位1位から3位までのデータを視覚的にわかりやすくした表を作りたいとします。

フィールド数（見出し列数）が多い場合には、データのセルだけを塗りつぶすよりも、レコード（行）全体を対象に塗りつぶしをしたほうが、いいと思うわけですね。

次のような表を作りたいわけです。

手早く設定するには、当然、目視で確認するわけではなく、条件付き書式をつかうわけです。

問題になるポイントが、どのような数式を設定すればいいのかということですね。

条件付き書式に用意させているものは、対象になるセルにしか、書式を反映することができないので、行全体と考えた時点で、条件付き書式の条件は数式を使わないといけないわけですね。

上位3位ということなので、RANK.EQ関数で順位を算出してもいいのですが、一度順位を算出した列をつくらないといけないので、効率的にちょっと、オススメできません。

そこで、上位3位の数値を算出させて、算出結果より以上の数値ならば、上位3位に入ることがわかります。

上位何番目というのを算出する時につかうのが「LARGE関数」です。

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

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

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

=$B2>=LARGE($B$2:$B$16,3)

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

これで、上位1位～3位のデータを行全体で塗りつぶすことができます。

設定した数式を説明しておきましょう。

=$B2>=LARGE($B$2:$B$16,3)

「$B2」と列側だけに絶対参照にする「複合参照」にすることで、行全体を塗りつぶすことができます。

あとは、LARGE関数の引数ですが、最初の引数は、配列なので範囲のことです。

絶対参照を忘れないように設定します。

そして、何位という順位を次に設定します。

上位3位のデータを算出したいので、「3」と設定すればいいわけですね。

このように、条件付き書式の条件に数式をうまく取り入れることで、様々な表現を帳票に組み込むことができます。

今回のように、色々試してみると、よりよい帳票や資料が作れるかもしれませんね。

Excel。条件付き書式で、上位3件を行全体で塗りつぶしたいけど、どうしたらいいの。【TOP3】

Excel。条件付き書式で、上位3件を行全体で塗りつぶしたいけど、どうしたらいいの。

＜条件付き書式+LARGE関数＞

条件付き書式をつかうことで、視覚的に資料をわかりやすくすることができます。

最初から用意されているものでも十分ですが、行全体にも反映させたい場合は、アレンジしないとできません。

今回は、上位3件のデータの行全体を塗りつぶししたい場合でみてみましょう。

セルのみに条件付き書式を設定したいならば、ホームタブの条件付き書式にある「上位/下位ルール」の上位10項目をつかって、3項目にすればいいわけですが、行全体になると、対応することができません。

行全体とか列全体という場合には、数式を使った条件設定で対応することになります。

やりたいことは、上位3件。

つまり、1位～3位のデータがわかればいいわけです。

そこで、使う関数は、LARGE関数です。

 

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

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

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

=$F2>=LARGE($F$2:$F$6,3)

あとは、書式ボタンをクリックして、セルの色を設定したら、OKボタンをクリックして設定完了です。

これで、行全体に塗りつぶしを行うことができました。

数式も確認しておきましょう。

「$F2」のように、列固定の複合参照にすることで、行全体を対象とすることができます。

「LARGE($F$2:$F$6,3)」で、上位3番目の値を算出できます。

その値以上ならば、上位1位～3位に該当します。

逆に、下位の場合には、「SMALL関数」をつかえば、同じように設定することができます。

ところで、なぜ、LARGE関数をつかうのかというと、MAX関数は、最上位のデータしか判別できません。

最上位（1位）以外の上位の値を算出するには、LARGE関数を使う必要があります。

RANK.EQ関数という方法もありますが、一度順位を算出させたあと、3以下のデータなのか判断させる数式で条件を設定する必要があるので、LARGE関数よりも、作業量が増えてしまいます。

手早く行う点からみても、LARGE関数をつかうといいですね。

Excel関数辞典 VOL.45。LARGE関数～LENB関数【dictionary】

Excel関数辞典　VOL.45。LARGE関数～LENB関数

＜Excel関数＞

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

LARGE関数

ラージ

指定した○番目に大きい値を算出します。

LARGE(配列,順位)

LCM関数

エルシーエム

整数の最小公倍数を算出します。

LCM(数値1,[数値2])

LEFT関数

レフト

文字列の左端から文字を取り出せます。

LEFT(文字列,[文字数])

LEFTB関数

レフトビー

文字列の左端から指定バイト数の文字を返す

LEFTB(文字列,[バイト数])

LEN関数

レン

文字列の文字数を返す

LEN(文字列)

LENB関数

レンビー

文字列のバイト数を返す

LENB(文字列)

Excel。表からTOP3のデータを抽出したいけどどうしたらいいの？

Excel。表からTOP3のデータを抽出したいけどどうしたらいいの？

＜INDEX&MATCH&LARGE関数＞

TOP3の値なんて、
LARGE関数を使えば簡単に算出できるでしょう？と思われるでしょうが、
今回は、次の表からTOP3のデータを抽出したいというのがリクエストなのです。

左側のA2:C12までの表があります。
このデータの中から、E:Gにある、
別表にTOP3までのデータを抽出したいということなのです。

オートフィルターを使ってもいいのですが、
今回は、関数を使って抽出を行ってみたいと思います。

【LARGE関数だけでは難しい】

まず、1位だけならば、MAX関数を使うところですが、
2位・3位はMAX関数ではダメなので、登場するのがLARGE関数ですね。

しかし、このLARGE関数では、売上高のTOP3を算出することが出来ても、
店舗名を抽出することは出来ません。

仮に、LARGE関数で算出した数値を使ってVLOOKUP関数で
店舗名を算出しようとしても、売上高の左側なので、それも無理ですし、
数値が重複していた場合も考慮する必要がありますので、
LARGE関数単独では難しいようです。

そこで、このようなケースでは、INDEX関数が重宝します。

【INDEX関数とMATCH関数のコンビネーション】

INDEX関数は、
どこにデータがあるのかを教えてあげればいい関数なので、
それぞれ、1位・2位・3位の数値がデータの何件目にあるのかを教えてあげれば
データを抽出することが出来ます。

では、どのようにしたら、1位～3位までのデータがそれぞれ、
どこにあるのかを求めるには、MATCH関数を使えば求めることが出来ます。

そこで、今回は、まずはMATCH関数を使って別の列にデータを算出して、
それからINDEX関数を使っていく方法をとります。

E3をクリックして、MATCH関数ダイアログボックスを表示します。

検索値には、LARGE関数をネストしますので、
LARGE関数ダイアログボックスを表示します。

配列には、$C$3:$C$12
順位には、1という数値がある、F3
そして、数式バーを使って、MATCH関数ダイアログボックスに戻りましょう。

検索範囲には、売上高の$C$3:$C$12
照合の種類は、完全一致なので、0
そして、OKボタンをクリックします。

7と算出されました。この表の7件目のデータが1位ということがわかりました。

確かに、秋葉原の480が1位ですね。

では、E5まで数式をコピーしておきましょう。

では、この数値を使って、G3に店舗名を抽出していきます。

そこでINDEX関数を使いますので、
INDEX関数ダイアログボックスを表示します。

INDEX関数は、2種類の引数を持っている関数なのですが、
今回は、『配列・行番号・列番号』を使っていきますので、
選択してOKボタンをクリックします。

配列には、B$3:B$12　複合参照にしてあるのは、
完成した数式をオートフィルでコピーするためです。
行番号は、$E3
列番号は、0
あとは、OKボタンをクリックします。
そして、数式をオートフィルを使ってコピーします。

G3の数式は、
=INDEX(B$3:B$12,$E3,0)

これで、抽出することが出来ましたね。

このように、
INDEX&MATCH&LARGE関数のコンビネーションで抽出することが出来ましたね。

ついでに、数式を一発で算出しようとした場合は、
G3の数式は次のようになります。
=INDEX(B$3:B$12,MATCH(LARGE($C$3:$C$12,$F3),$C$3:$C$12,0),0)

やっぱり結構長い感じになりましたので、
慣れるまでは無理をせずに、
しっかり算出・抽出することを考えていきましょう。

Excel。RANK関数ほど有名じゃないけど、順位の関数も便利です。

Excel。RANK関数ほど有名じゃないけど、
順位の関数も便利です。

LARGE関数・SMALL関数

研修やらなんやらで、結構ご紹介することがある、

RANK関数。

RANK関数は、順番を付ける関数なのですが、あくまでも順番を付ける”だけ”の関数。

たとえば、データが膨大になると、
どれが、1位はどれか、2位はどれだ、と探すのが大変。
並び替えをすれば、見つかりますが、最初から、1位・2位を見つけるだけなのに、
RANK関数をつかって、並び替えてとか、
オートフィルターを使って、抽出するのも、工程数がかかるわけですね。

たしかに、最大・最小を見つけるだけなら、
MAX関数とMIN関数で用は足りますが、
2位以降～ブービーまでは対応できない。

そこで、紹介するのが、

LARGE関数・SMALL関数。

この関数を使えば、
ダイレクトに、どのデータが何位なのかを見つけることが出来ます。
下記のデータを使って、ご紹介。

まずは、

LARGE関数。

1位をMAX関数ではなく、LARGE関数で算出してみます。
F4をクリックして、LARGE関数のダイアログボックスを表示させたら、

配列は、データを選択して、順位は1位の1。これでOK。

今度は、

SMALL関数。

作り方はLARGE関数と同じですね。

順位に2をいれることによって、ブービーを求めることができます。
なお、同じ値の場合は、同じ順位で、それ以降の順位が繰り下がっていきます。