【Excel】SMALL/LARGE関数とVLOOKUP複数

  • SMALL/LARGE関数ってどんな関数?
  • SMALL/LARGE関数の使うタイミングってどういう時?

関数式自体の説明もそうですが、具体的な使用方法を使って見ていきましょう。

SMALL/LARGE関数の数式

検索と相性がいい

実際にVLOOKUP関数とIFERROR関数を組み合わせる

条件付き書式設定を使って視覚に訴えかけるのも相性がいいので組み合わせてつかっていくのもおすすめです。

【Excel】条件付き書式設定の使い方①
Excelで表や計算シートを作っても、いまいちよく伝わりづらいことはありませんか?ただつくるのではなく、データなどを持って客観的事実を「わかりやすく」伝えること=プレゼンが大事です。 資料として見える部分にも伝わりやすさがプレゼンには必要ですよね。 ...


SMALL/LARGE関数の数式

SMALL/LARGE関数は「範囲の中で○番目に小さい/大きい値」を返す数式です。

=SMALL(配列,順位) ※配列は範囲の意味
=LARGE(配列,順位) ※配列は範囲の意味

B3セルには=SMALL(A2:A6,B2)、B6セルには=LARGE(A2:A6,B5)と数式が入っています。使い方自体は難しいものではありません。


検索と相性がいい

使い方に関してはわかったのですが、実際にどんな関数の使い方がされているのかを検索してみたら、以下のようになってました。

ちなみにIF関数とVLOOKUP関数の検索を見てみると以下のようになります。

関数のなかでも格差社会があるみたいですね…
ちなみにこちらのサイトを使って検索しています。

関連キーワード取得ツール(仮名・β版)

話が逸れましたが、使用頻度が違うのか検索に対してのキーワード自体が少ないのが実態です。しかしSMALL/LARGE関数の使い勝手は決して劣るわけではないのでそれを確認してみましょう。


実際にVLOOKUP関数とIFERROR関数を組み合わせる

資材注文票を作成して、注文した商品だけ表示されるようにしていきます。

注文票では、

『棚250×900』
『棚350×900』
『棚350×750』

この3種類しか注文していません。この注文票を基に、注文した商品のみを表示するリストを作成していきます。

手順に沿って確認していきましょう。

【Excel】LOOKUP関数を使いこなす【VLOOKUP、HLOOKUP】
「Excelって数字がいっぱいで、必要な情報が探しづらい… 結局、データを一覧でまとめてみたけど自分で探す手間がかかるから時間短縮につながってない。」 これだと、Excelの機能が使いこなせているとは言えません。Excelはデータ集計・分析ソフトで...

手順1、注文した商品の商品コードだけを表示する

F2に=IF(E2=””,””,A2)と数式を入れて、注文した商品の商品コードだけ表示させます。今回はE2、E4、E8のみ注文されているので商品コードは『0001』『0003』『0007』しか表示されません。

手順2、VLOOKUP関数の検索値をSMALL関数にする

VLOOKUP関数は=VLOOKUP(検索値,検索範囲,列番号,FALSE)という数式で、検索値の部分をSMALL関数に置き換えます。

=VLOOKUP(SMALL(配列,順位),検索範囲,列番号,FALSE)とします。

SMALL関数の配列を手順1で作成した$F$2:$F$19にして、順位を$A2セルに設定します。(1~19で並んでいるので)

VLOOKUP関数の検索範囲は$A$2:$E$19、列番号は商品名を表示したいので3としてFALSEでVOLLKUP関数を終了します。

手順3、IFERROR関数で不必要なものを消す

手順1・2が終わるとG2セルが

=VLOOKUP(SMALL($F$2:$F$19,$A3),$A$2:$E$19,3,FALSE)

となります。そのままオートフィルをすると以下のようになってしまいます。

注文した商品は3つしかないので、SMALL関数の順位が3までしか反映されず4つ目以降がエラーになってしまっています。見栄えが良くないのでエラーが出る部分には空白となるようにしておきましょう。
VLOOKUP関数をIFERROR関数で以下のように囲います。

=IFERROR(VLOOKUP(SMALL($F$2:$F$19,$A2),$A$2:$E$19,2,FALSE),“”)

IFERROR(値,エラーの時の値)なので数式がエラーになった時には『””(空白)』とすることで必要な部分以外を空白にすることができます。

ここまで数式を入れたらオートフィルで引っ張ってあげれば完成です。

より実践的なものを作る場合はチェックボックスを使って作るという手もあります。

【Excel】☑ボックスの作り方と注意点【コピー出来るけど…】
アンケートや持ち物チェックでよく使われる☑(チェックボックス)ですが、Excelでも使うことができます。さらに数式で集計などにも使えて、☑自体も印刷を出来るのでプリントとして配布することもできます。使い方をマスターしてみまし...