Excel。SMALL関数は指定した○番目に小さい値を算出します。【small】

Excel。SMALL関数は指定した○番目に小さい値を算出します。

＜関数辞典：SMALL関数＞

SMALL関数

読み方： スモール　　

分類： 統計 

SMALL(配列,順位)

指定した○番目に小さい値を算出します　

Excel。オートフィルターをつかわずに、該当するデータを別のセルに表示したい【Extract】

Excel。オートフィルターをつかわずに、該当するデータを別のセルに表示したい

＜IF＋ROW関数　IFERROR＋INDEX＋SMALL＋ROW＋COLUMN関数＞

データベースや表から、該当する条件のデータを抽出して、別のセルに表示したい場合、オートフィルターでデータを抽出して、コピーをする。

この作業でもいいのですが、抽出条件が変わるとなると、オートフィルターで抽出してコピーするという作業が面倒になってきます。

そこで、数式だけで、対応することもできます。

A1:C8にデータがあって、G1のクラス名に合致するデータを、F4を起点として抽出表示したいわけです。

G1を「B」にしたら、クラスBのメンバーが抽出され表示するというものです。

最初は準備として、D2に次の数式を設定し、D8までオートフィルで数式をコピーします。

=IF(B2=$G$1,ROW()-1,"")

B列のデータと、G1のデータが合致したら、行番号-1を算出するという数式ですね。

ROW関数は行番号を算出します。

「-1」するのは、あとでINDEX関数をつかうのですが、座標がわかりやすくするために、「1」からの値にしたいからです。

今回は、データの1件目が2行目にありますので、「-1」すれば、「1」から表示することができます。

F4にメインとなる数式を設定します。

設定したら、オートフィルで数式をコピーします。

=IFERROR(INDEX($A$2:$C$8,SMALL($D$2:$D$8,ROW(A1)),COLUMN(A1)),"")

長い数式ですが、これだけで、該当するデータを抽出し表示することができます。

F4:H8まで数式を設定してあります。

それでは、数式を確認していきます。

最初のIFERROR関数ですが、データの件数が少ない場合、「#NUM!」というエラーが表示されてしまうので、その防止のためにIFERROR関数をつかっています。

INDEX関数は、設定した範囲の中から、指定した行番号と列番号のデータを抽出するという関数です。

最初の引数は、「配列」です。

「$A$2:$C$8」と設定します。

オートフィルで数式をコピーしますので、絶対参照を忘れないようにしましょう。

2番目の引数は、「行番号」です。

SMALL($D$2:$D$8,ROW(A1))

先程、IF関数で算出した結果を、SMALL関数を使い、1番小さい値を抽出させます。

1番小さい値、次に2番目の小さい値というようにしたいので、SMALL関数をつかい、さらに、ROW関数でA1を指定します。

オートフィルで数式をコピーすると、A1がA2とかわり、2番目・3番目に小さい値のある場所を見つけてくれるという仕組みです。

最後の引数は、「列番号」です。

COLUMN(A1)

こちらも、先程と同じ仕組みで、オートフィルで数式をコピーするときに、座標がズレるようにしています。

このように、INDEX関数をつかうことで、データを抽出することもできます。

Excelの様々な関数の読み方や引数などを紹介。今回は、SLN関数～SORTBY関数です。【dictionary】

Excelの様々な関数の読み方や引数などを紹介。今回は、SLN関数～SORTBY関数です。

＜Excel関数辞典：VOL.73＞

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

SLN関数

読み方： エスエルエヌ

読み方： ストレートライン

分類： 財務 

SLN(取得価額,残存価額,耐用年数)

減価償却費を定額表で算出します　

Straight LiNe depreciationの略

SLOPE関数

読み方： スロープ　　

分類： 統計 

SLOPE(既知のy,既知のx)

回帰直線の傾きを算出する　

SMALL関数

読み方： スモール　　

分類： 統計 

SMALL(配列,順位)

指定した○番目に小さい値を算出します　

SORT関数

読み方： ソート　　

分類： 検索/行列 

SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])

範囲または配列を並べ替えます　

SORTBY関数

読み方： ソートバイ　　

分類： 検索/行列 

SORTBY(配列,基準配列1,[並べ替え順序1],…)

範囲または配列を、対応する範囲または配列の値に基づいて並べ替えます　

Excel。結合したセルの値をまとめた一覧表を作りたいけど、コピペじゃ大変なんです。【Merged cells】

Excel。結合したセルの値をまとめた一覧表を作りたいけど、コピペじゃ大変なんです。

＜ROW関数・IFERROR+INDEX+SMALL関数＞

セルの結合は設定するのが簡単なのですが、結合したセルの値を別のセルにコピーするとか、一覧表を作る場合、意外と大変というか、困ってしまうことがあります。

例えば、次の表のような処理をする場合で、説明していきます。

A列には、セル結合された担当者名が入力されています。

入力規則のリストなどで使うために、D列のように担当者一覧を作りたいとします。

今回は、4名なので、コピー&ペーストでも、どうにかなりますが、件数が多かったらコピー&ペーストでは大変です。

D2に「=A2」というセル参照を設定して、オートフィルで数式をコピーしても、できませんよね。

Excel VBAでマクロをつくってもいいのですが、マクロを作成すること自体も面倒です。

今回のような、結合セルの値から、一覧表をつくるには、ちょっとした関数を組み合わせれば、可能になります。

では、早速作っていきましょう。

C2に、次の数式を設定して、オートフィルで数式をコピーします。

=IF(A2<>"",ROW(A1),"")

このように、算出されます。

数式の説明をします。

縦方向にセルが結合されている場合、結合されている一番上のセルに値が設定されいます。

値が入っているか、どうかを判断したいので、IF関数を使います。

論理式は、「A2<>""」。

A2が空白ではなかったらと、問い合わせます。

真の場合は、「ROW(A1)」。

ROW関数で行番号をつかって、数値を算出させます。

義の場合は、空白　とします。

これによって、値があるセルの場所がわかりました。

続いて、D列にまとめる作業を行います。

D2に次の数式を設定します。

=IFERROR(INDEX($A$2:$A$10,SMALL($C$2:$C$10,ROW(A1))),"")

数式を説明していきます。

IFERROR関数を使うのは、このあと、オートフィルで数式をコピーすると、抽出データがないセルに、「#NUM!」というエラーが表示されるので、その防止のために使います。

INDEX関数をつかうことで、行番号と列番号が交差したデータを抽出することができます。

INDEX関数は、INDEX（参照,行番号,列番号）という引数をもっています。

参照は、担当者名を算出したいわけですから、$A$2:$A$10。

オートフィルで数式をコピーしますので、絶対参照を設定します。

行番号は、

SMALL($C$2:$C$10,ROW(A1))

SMALL関数は、その数値の順位の数値を算出します。

このために、先程のIF関数で算出したわけです。

SMALL関数は、SMALL（配列,順位）という引数をもっています。

配列は、IF関数で算出した範囲の、$C$2:$C$10

絶対参照も忘れずに設定します。

順位は、ROW(A1)　A1を指定することで、「1」という数値を算出することができます。

オートフィルで数式をコピーすると、A2・A3とかわることで「2」「3」とスライドすることができます。

列番号は、今回複数列ではないので、省略します。

すると、このような結果になりました。

このように、セル結合した値でも一覧表を作ることができました。

最後に、C列の数値を表示してあると、カッコ悪いので、非表示にしますので、C2:C10を範囲選択して、セルの書式設定ダイアログボックスを表示します。

表示形式の「ユーザー定義」にして、種類に「;;;」（セミコロン×3）と設定します。

これで、文字を非表示することができました。

セルの結合は、見た目としては、便利なのですが、そのあとに、何かしたい場合は、うまくいかないことがありますので、見た目を重視する必要がない場合は、データベースとして表を作る方がいいかと思います。

Excel。XLOOKUP関数をつかうと、楽に、上位3位の点数に該当する氏名を抽出できます。【XLOOKUP】

Excel。XLOOKUP関数をつかうと、楽に、上位3位の点数に該当する氏名を抽出できます。

＜SMALL+ROW関数&XLOOKUP関数＞

最高値を算出するには、MAX関数を使いますし、2位や3位など指定の順位を算出するには、LARGE関数をつかいます。

ただ、困ったことに、数値そのものしか算出することができません。

どういうことかというと、次の表があります。

 

AB列の表は、ある競技の時間の表です。

上位3位までのタイムのいい人を抽出したいわけです。

作業としては、最初にD列を算出します。

数値が小さい上位3名までなので、SMALL関数を使えばOKです。

D2の数式を確認してみましょう。

=SMALL($B$2:$B$11,ROW(A1))

引数のROW(A1)は何を意味似ているのかというと、ROW(A1)としないで、1位を算出したいので、「1」と設定したら、オートフィルで数式をコピーしても、「1」のままなので、「2」「3」と修正しないといけません。

ROW関数をつかって、数式を修正する手間を省いています。

SMALL関数を使うことで、上位1～3位までのデータを算出することはできるのですが、そのデータの他のフィールドを抽出することは、SMALL関数ではできません。

垂直の表になっているので、VLOOKUP関数を使えば、抽出することができるのではと考えるかもしれませんが、残念ながら、今回のような表からでは、VLOOKUP関数で、別のフィールドのデータを抽出することはできません。

なぜならば、VLOOKUP関数は、抽出する範囲の一番左側が、検索値でなければなりません。

つまり、今回は、参加者・時間という並びになっていますので、VLOOKUP関数が使えないわけです。

時間・参加者ならばVLOOKUP関数が使えます。

このような表に対応するには、INDEX+MATCH関数をつかうことで、抽出することはできるのですが、なかなか面倒な関数の組み合わせです。

ところが、近年追加された「XLOOKUP関数」を使うと、とても簡単に問題を解決してデータを抽出することができます。

E2をクリックして、XLOOKUP関数をつかった数式を作っていきます。

なお、XLOOKUP関数ですが、XLOOKUP関数ダイアログボックスで数式をつくると、どのように引数を設定したらわかりにくいので、出来ることならば、手入力で数式をつくるほうがいいように思えます。

なので、今回は、手入力で、XLOOKUP関数を作っていきます。

最初は、検索値ですが、これは、D2の時間が該当しますので、「D2」と入力したいところですが、Office365のExcelやXLOOKUP関数が使えるようになったExcel2019には、「スピル機能」があるので、「D2:D4」と入力します。

検索範囲ですが、検索値が所属しているフィールド（列）ですから、B2:B11。

オートフィルで数式をコピーするのではなく、「スピル機能」によって数式がコピー（スピル）されますので、絶対参照は不要です。

戻り範囲ですが、これは、抽出したいものですから、参加者の列ですね。

A2:A11と入力します。

ここも、絶対参照は不要です。

見つからない場合は、空白にしたいので「””（ダブルコーテーション×2）」

一致モードは、完全一致なので「0」

検索モードは、データの上部から下部へと検索しますので「1」

Enterキーで数式を確定すると、スピル機能によって、数式が設定されます。

E2の数式は、

=XLOOKUP(D2:D4,B2:B11,A2:A11,"",0,1)

です。

このように、XLOOKUP関数は、VLOOKUP関数よりも、融通がききやすいので、使ってみると便利です。

なお、「スピル機能」がよくわからない場合は、旧来通り、絶対参照をつかった数式をつくり、オートフィルで数式をコピーしてもOKですよ。

その場合の数式は、

=XLOOKUP(D2,$B$2:$B$11,$A$2:$A$11,"",0,1)

です。

新しく加わった機能や関数を、取り入れてみると、改善できるものとかあるかもしれませんね。

Excel。オートフィルターを使わずに関数で該当データを抽出するには？【Extraction】

Excel。オートフィルターを使わずに関数で該当データを抽出するには？

＜IFERROR+INDEX+SMALL+ROW関数、IF+AND+YEAR+MONTH関数＞

売上データや納品データなど大きなデータがあって、そこから、請求書などのデータを抽出したい時に、オートフィルターなどを使わずに、数式（関数）で直接データを抽出することができれば、オートフィルターなどを使わない分、作業効率が改善されます。

では、どのように数式を作成したらいいのでしょうか？

次のような表（購入リストという名前のシート）と

転記したいシート（店舗別という名前のシート）を用意しています。

転記したいシートのB1には、店舗名。D1には、年。

そして、F1には、月を入力して、そのデータに合致したものを、表から抽出するということをします。

表にオートフィルターを設定して、抽出条件を加味して、そのデータをコピー&ペーストするという作業で問題はないのですが、例えば、店舗が10店舗とかあると、いちいち抽出していると面倒なので、店舗ごとのシートを作れば、年とか月とかを変更するだけで済むようになるわけです。

【合致するものがあるのかを確認する】

転記したいシートのD列を使って、まずは、表と合致するデータがあるのかどうかを算出させます。

D4をクリックして、次の数式を設定します。

=IF(AND(購入リスト!B2=店舗別!$B$1,YEAR(購入リスト!A2)=$D$1,MONTH(購入リスト!A2)=$F$1),ROW(A1),"")

少し長い数式なので、説明をしていきます。

購入リストシートのB2が、抽出したい店舗名と同じで、年月も同じなのかをAND関数を使って確認しています。
IF関数を使って、真の場合は、この後の使う関数のために、昇順の連番がほしいので、ROW(A1)としています。合致しなければ、空白という数式です。

あとは、オートフィルで適度に数式をコピーしておきます。

【合致したデータを詰めて表示したい】

A4に次の数式を設定します。
=IFERROR(INDEX(購入リスト!$A$2:$D$10,SMALL($D$4:$D$12,ROW(A1)),1),"")
D列のデータがないとエラーが表示されるので、IFERROR関数でエラーを表示させないようにしています。

そして、ポイントになる関数。それが、『INDEX関数』です。

INDEX関数は、指定された列と行が交差するデータを表示することができる関数です。

今回は、交差するセル情報を表示させたいので、INDEX関数の引数は、【配列・行番号・列番号】を使います。

配列は、購入リストシートの$A$2:$D$10。

行番号は、先程算出してあるD列を使うのですが、D4に1と算出しているので、最初は1を使うのですが、2件の場合は、D6の3を使うことで、引数の行番号には3を使いたいわけです。

となると、順々に数値を変更させたいのですが、連番とは限りませんので、SMALL関数を使い、さらにROW関数を使うことで、1番目に小さい数値、2番目に小さい数値と引数に設定することができます。

列番号には、1列目のデータなので、1。

先程の行番号は、SMALL関数とROW関数を使うことで、自動的に引数の数値を変えることができたのですが、この列番号だけは、何列目のデータを抽出していいのか、判断が難しいので、それぞれの列で、修正する必要があります。

つまり、
日付のA4の数式は、
=IFERROR(INDEX(購入リスト!$A$2:$D$10,SMALL($D$4:$D$12,ROW(A1)),1),"")

商品名のB4の数式は、
=IFERROR(INDEX(購入リスト!$A$2:$D$10,SMALL($D$4:$D$12,ROW(B1)),3),"")

購入金額のC4の数式は、
=IFERROR(INDEX(購入リスト!$A$2:$D$10,SMALL($D$4:$D$12,ROW(C1)),4),"")

となっています。

それでは、オートフィルで数式をコピーしてみましょう。

該当するデータを詰めて表示することができましたね。

あと作業用で使ったD列の数値が見えているとカッコ悪いですね。
当然、数式を削除するわけにはいきませんね。

そこで、D4以降には、次のような表示形式を設定します。

セルの書式設定ダイアログボックスを表示して、分類の「ユーザー定義」に、;;;（セミコロン×3）と設定して、OKボタンをクリックしましょう。

:::（セミコロン×3）を設定すると、文字を非表示にできます。

このような数式を使うことで、抽出業務も改善できるかもしれませんね。

Excel。0（ゼロ）を除いた最小値の算出方法がMINIFSで楽勝になった【Exclude zero】

Excel。0（ゼロ）を除いた最小値の算出方法がMINIFSで楽勝になった

＜MINIFS関数　SMALL+COUNTIF関数＞

次のような表があります。

B列のデータから0を除いた最小値を算出しようとすると、
思っている以上に面倒というか簡単に算出することが意外とできません。

最小値を算出するには、おなじみのMIN関数を使うことを考えますが、
当然0が含まれていれば、0が算出されてしまいます。

つまり、「0（ゼロ）を除いて」という条件を
MIN関数では付けることができません。

また、SMALL関数を使えば算出できそうな感じもしますが、
これもプラスしたアイディアが必要になります。

LARGE関数ならば、1とすれば最大値を算出することが可能ですが、
0（ゼロ）を除いた最小値が何番目にあるのかがわからないと、
SMALL関数のみでは算出することができません。

そこで考え方としては、0（ゼロ）の件数を数えて、
その数をデータ件数から減算すれば、
0（ゼロ）を除いた最小値の場所を求めることができますので、
SMALL関数で算出することができます。

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

配列には、B2:B11
順位には、COUNTIF関数をネストしますので、カーソルを表示して、
COUNTIF関数ダイアログボックスを表示します。

範囲には、B2:B11
検索条件には0
としてOKボタンをクリックします。

ただし、数式としては作成途中です。

E2の数式は、
=SMALL(B2:B11,COUNTIF(B2:B11,0))

となっていますが、この次の順位が0（ゼロ）を除いた場所にあたりますので、
+1を追加しますので、数式を次のように修正します。

=SMALL(B2:B11,COUNTIF(B2:B11,0)+1)

これで、0（ゼロ）を除いた最小値を算出することができました。

ちなみに、次のような数式でも算出することができます。

=LARGE(B2:B11,COUNTIF(B2:B11,">0"))

最小値というコトバから逆転の発想で、0（ゼロ）より大きい件数を算出して、
大きい順でその算出された数値のところが、
0（ゼロ）を除いた最小値ということになります。

”+1”をしなくていいメリットはありますが、わかりにくいかもしれません。

ところが、Office365で追加されたMINIFS関数が登場して、
数式を作るのが簡単になりました。

【Office365では、MINIFS関数】

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

このMINIFS関数ですが、引数の作り込みが、イマイチで、なぜか、
最小値なのに、最大値と表示されています。

最大値（最小値）には、B2:B11
条件範囲1には、B2:B11
条件1には、”>0”
比較演算子を引数内で使うときには、
ダブルコーテーションを忘れないようにしましょう。

では、OKボタンをクリックします。

このように簡単に算出することができました。

そりゃそうですね。
MIN関数に条件検索ができるようになった、
MINIFS関数なら0（ゼロ）以外という条件を付けるだけですからね。

なお、E3の数式は、
=MINIFS(B2:B11,B2:B11,">0")

ということで、Office365ならば、MINIFS関数をはじめ、
プラスされた関数を使うことで、
今まで面倒だったものが改善できるかもしれませんね。

Excel。最小値はMIN関数。だけどデータ内に0（ゼロ）があってうまくいきません。

Excel。最小値はMIN関数。だけどデータ内に0（ゼロ）があってうまくいきません。

＜SMALL関数+COUNTIF関数＞

ちょっとした質問をいただきまして、実験データなんだそうですが、

そのデータから最小値を求めたいけど、うまくいきません。だそうでして。

次の表のようなものらしいのですが。

 

B列の実施ポイントから、最小値をD3に算出したいということですから、

別にMIN関数で求められるはず…ではないのです。

一応、やってみましょう。D3をクリックして、

オートSUMボタンの▼をクリックして、最小値を選択して、B3:B12を範囲選択してみましょう。

D3の数式は、

=MIN(B3:B12)

 

結果は0（ゼロ）が算出されましたが、リクエストは、0（ゼロ）を除いた最小値ということ。

つまり、MIN関数では当然ですが、0（ゼロ）が最小値なので、

0（ゼロ）を算出してしまうわけです。

本当なら、この手のデータは0（ゼロ）を入力しないで、空白にしておく、

 

あるいは、-（ハイフン）のような文字をいれておく。

 

このようにすれば、問題は解決するのですが、0（ゼロ）はすでに入力されている。

この状態で、0(ゼロ)を除いた最小値を求めたいというのが、今回のリクエストなんですね。

なので、ちょっと、ややこしい。MIN関数では算出することが出来ないのです。

そこで、どうやって、0（ゼロ）を除くのか？というと、

SMALL関数とCOUNTIF関数を使うと算出することが出来るのです。

SMALL関数は、指定したワーストのデータを求めることができる関数ですね。

そこで、0（ゼロ）の総数を数えて、

その数にプラス1した数値のデータを持ってくることが出来れば、

それが0（ゼロ）を除いた最小値というわけです。

なので、0（ゼロ）の数を数えるために、COUNTIF関数を使うわけです。

では、やってみましょう。

D3にSMALL関数ダイアログボックスを表示しましょう。

 

配列には、B3:B12

順位には、COUNTIF関数をネストしますので、

COUNTIF関数ダイアログボックスを表示させるようにしましょう。

名前ボックスの▼からネストする関数を選択していきます。

 

範囲には、B3:B12

検索条件には、0（ゼロ）

を入力して、SMALL関数ダイアログボックスにもどりましょう。

数式バーのSMALLという文字をクリックすると、戻れますね。

 

そして、OKボタンをクリックしましょう。

ただ、まだ数式は作成途中です。

今のところまでの数式は、

=SMALL(B3:B12,COUNTIF(B3:B12,0))

となっていますので、COUNTIF関数の結果に+1（プラス1）するように数式を修正します。

最終的に数式は、

=SMALL(B3:B12,COUNTIF(B3:B12,0)+1)

とします。

これで、算出することができましたね。

 

このように、どうしてもデータ上0（ゼロ）が入っている場合に最小値を求めたい場合は、

MIN関数は使えませんので、ご注意ください。

Excel。MONTH。誕生月の方を抽出したリストを作りたいけどどうしたらいい？というご質問

Excel。誕生月の方を抽出したリストを作りたいけどどうしたらいい？というご質問

MONTH・ROW・COUNT・SMALL・INDEX関数

登録されている一覧から誕生月ごとに何人いるのか？という算出方法をご紹介しましたが、
今回は、その該当月の方を抽出したいのだけど、どうしたらいい？と、ご質問がありましたので、
今回は、それを紹介していきます。

難しそうには感じませんが、結構ややこしくて、面倒なんですね。
例えば10月の方を抽出したい場合、

考え方として、IF関数を使ってみたら、

E4には、

=IF(MONTH(C4)=$E$1,B4,"")

という数式が設定されています。
この数式では、E列に10月生まれの方の氏名が表示されるだけですね。

これでは、イマイチですね。

次にテーブルにしてみたら、どうでしょうか？

テーブルに変換して、月を10月で抽出してみます。

なお、このD4には、

=MONTH(C4)

という月を算出する関数が設定されています。

これで、10月の方が抽出されましたので、OKではありますが、
元の一覧が消えてしまっていまして、リクエストとして、元の表は見えるようにしてほしいという、
欲張りなものでした。

ということで、今回は、関数を使って抽出していくことにしましょう。

完成はこんな感じです。

では、まずは、抽出から作成していきましょう。

この抽出は、最終的にこの列を使用して、INDEX関数を使って、該当の方を抽出させていきます。

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

論理式には、MONTH(C4)=$G$1　と入力します。

これは、誕生月が10月と合致するかどうか？とします。

真の場合には、ROW()-3　と入力します。
このROW関数は、その行番号を算出してくれます。
今回の一覧表は、3行目に見出しがあるので、
篠崎さんは1行目（1レコード）にならないといけませんので、-3（マイナス3）する必要があります。

偽の場合には、”” 空白　と入力します。

OKボタンをクリックして、オートフィルハンドルで数式をコピーしましょう。

続いてこの抽出で表示された数値だけを別表に集めていきます。

ここで登場するのはSMALL関数です。

SMALL関数は、データの中から指定された順に小さい数値をもってきます。

それでは、H4をクリックして、IF関数ダイアログボックスを表示します。

論理式には、抽出された件数よりも大きければ空白を入力するための数式が入りますので、COUNT($D$4:$D$21)<F4　と入力します。

真の場合は、””　空白を入力します。

偽の場合は、SMALL関数を挿入しますので、
SMALL関数ダイアログボックスをネストしていきます。

配列には、抽出のD列。$D$4:$D$21　と入力します。

順位ですが、これは、丁度NoのF列に1～連番ではいっていますので、
これを使いますので、F4　と入力します。

OKボタンをクリックして、オートフィルハンドルで数式をコピーします。

H列に数値が集められましたね。SMALL関数って結構使える関数なんですよ。
そして、いよいよ、氏名を抽出させます。
IF関数ダイアログボックスを表示します。

論理式には、H4=””　先程のH列が空白なら空白そうでなければINDEX関数をネストします。

真の場合は、””　空白を入力します。
偽の場合には、INDEX関数をネストしますので、INDEX関数ダイアログボックスを表しましょう。

その際に、引数の選択ダイアログボックスが表示されてきますので、
【配列,行番号,列番号】を選択しましょう。

配列には、$B$4:$B$21　と入力します。氏名のデータですね。
行番号は、H4　と入力します。この数値のデータをもってきます。
列番号は、0　と入力します。省略もできます。
あとは、OKボタンをクリックして、数式をコピーしましょう。

これで完成ですね。

しかし、H列など作業だけで算出させたのが見えていると見栄えが良くないので、
次回は、見栄えを良くする方法アレコレをご紹介していきます。

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