前回、企業が入社テストとして出題した「Excel問題」のその4をやってみました。
今回は、みんな大好き「LOOKUP関数」となります。
それでは一緒にやっていきましょう。
問題5を確認
出題されていたエクセルの画面と同じものを作成し、以下からダウンロードできるようにしてあります。
今回は33件のデータがある右の表から、最初に「RANK関数」を使って値が大きい順にランク付けをします。
そして「TOP20」の名称とデータを抜き出すために、左側の表に「LOOKUP関数」を使ってみたいと思います。
今回のポイントは以下のようになります。
- 値の大きい順に関数でランキングを付ける
- 表から該当データを抜き出すために関数のみを使う
- 黄色い枠だけを変更できる
それでは見ていきましょう。
RANK関数を使う
まずはH列の「値」が大きい順にランキングを付与してみます。
ランキングを図る関数は「RANK関数」を使います。
まずは「セルF5」に「RANK関数」をセットします。
「数値」の引数には、範囲の中で順位を知りたい値を指定します。
この場合は「セルH5」になります。
ランキングを図るデータは、「セルH5」から「セルH37」まであります。
「参照」の引数には、その範囲を指定します。
ただし、後で「セルF5」のデータを「セルF37」までオートフィルしますので、データの範囲である5行目から37行目までを絶対参照にしておきましょう。
記述した「RANK関数」は以下のようになります。
RANK(H5,H$5:H$37)
「セルH5」から「セルH37」までの範囲で、「セルH5」の141というのは、33位であると分かりました。
では「セルF37」までオートフィルします。
データの「値」列を基に順位が分かりました。
それでは、この表から上位20番目までのデータを左側の表に書き出してみましょう。
VLOOKUP関数を使う
「VLOOKUP関数」は、表の行から特定の列の値を調査する関数です。
| 列番号：1 | 列番号：2 | 列番号：3 |
|---|---|---|
| 1 | 不動産 | 不動産セクターの株式やETFを保有している |
| 2 | 株式 | 日本国内の株式だけを保有している |
| 3 | 投資信託 | インデックスファンドだけを持っている |
| 4 | 不動産 | 現物不動産を所有している |
| 5 | 株式 | 日本及び米国の株式を保有している |
例えば上のようなマスター表があって、僕と弟と父と母の投資商品保有状況について以下のような表を持っていました。
| 僕 | 1 |
| 弟 | 3 |
| 父 | 5 |
| 母 | 2 |
では、僕はどんな投資商品を持っていると思いますか？
僕は「1」を持っています。
そしてマスター表を見ると、「1」(列番号1)というのは「不動産」(列番号2)であり、不動産セクターの株式やETFを保有している(列番号3)と分かります。
僕のデータである「1」がマスター表の「1」と同じなわけです。
そして、マスター表「1」の列番号2や列番号3を見れば、僕がどのような投資商品を持っているかが分かるのです。
つまり普段管理するデータは、「僕が1である」というデータだけで済むわけですね。
その後もしデータが増えても「姉が3である」、「おじいちゃんが5である」のようにデータを管理しやすくできるのです。
この「1」というデータから、マスター表の「1」がどのようなデータかを調べる時に「VLOOKUP関数」を使います。
データの管理で行列が入れ替われば、「HLOOKUP関数」を使います。
調査対象が列(Vertical)か、行(Holizon)かの違いだけです。
それでは上位20位までの値を調べてみましょう。
「VLOOKUP関数」で調査するイメージを以下で確認しておきます。
実際の「VLOOKUP関数」の引数は以下のように指定します。
ランキング1位の名称である「セルC5」に「VLOOKUP関数」を指定したら、まずはC列だけオートフィルをする予定で参照方法も合わせて考えます。
Check!
なぜD列の「値」もオートフィルを考えないのかは後述します。
VLOOKUP(B5,F$5:H$37,2,0)
最初の引数である「検索値」には、「セルB5」の1を指定します。
次の引数の「範囲」には、右側の表全体なので「セルF5からセルH37」を指定します。
この時、まずはC列の名称だけをオートフィルする前提なので、行が固定されるように行番号に「$」を付けて絶対参照にしています。
次の引数の「列番号」は取得したいデータ列となります。
ここはランキングが1位の「名称」を取得したいので、「2」を指定します。
最後の引数は、「0」を指定すると検索方法が「完全一致」となります。
検索値と完全に一致する値を検索する場合は、必ず0(もしくはFALSE)を指定します。
結果として、「りょ」が表示されました。
右側の表でランクが1のデータを確認してみましょう。
残りの行はオートフィルで埋めます。
一応確認しておくと、ランキング9位の名称は「ひゃ」となっていて、合っているのが分かります。
値(D列)へのオートフィルをする前に
さて、本来は一気に「値」(D列)もオートフィルでデータを表示させたいところでしたが、一旦それを抜きにして名称(C列)だけを考えて「VLOOKUP関数」を設定しました。
実は先ほどの「VLOOKUP関数」の「列番号」に指定した「2」という数字は、オートフィルをしても「2のまま」になります。
なぜならオートフィルで参照範囲が動くのは、「セル番地」だからです。
例えば「セルB2」から「セルD2」に、以下のような関数に使うための数値を入力していたとします。
引数の「列番号」には実数の「2」ではなく、「C2」というセル番号を指定したとします。
その後「セルC5」の名称をオートフィルで「セルD5」にコピーすると・・・
これであれば、オートフィルをした時に「列番号」のセル参照も動くので、名称の時は「2(セルC2)」、値の時は「3(セルD2)」とオートフィルも機能します。
しかし実数の「2」という数字を指定しまうとオートフィルが機能しなくなるのです。
そしてこの入社テストの決まり事として、「黄色いセル以外は変更してはならない」というのがありましたね。
つまり先ほど「セルB2」から「セルD2」まで作業用として勝手にセルに入力したような行為は、NG(ご法度)なのです。
そのため名称は列番号「2」、値は列番号「3」というこの数値たちを何か数式で取得できないだろうか、と考えるしかありません。
そこでもう一つ使いたい関数が「COLUMN関数」となります。
COLUMN関数で該当セルの列番号を取得
「COLUMN関数」は、該当セルの列番号を取得する関数です。
例として「セルC2」に、「COLUMN関数」がどのような結果を表示するかを確認してみます。
引数として参照するセル番地を指定します。
C列だったらどのセルでもいいのですけど、ここではタイトルの「名称」が書かれている「セルC4」を指定してみました。
すると結果は、3が返ります。
シートのA列が1、B列が2、C列が3と言う形で列番号を数値として取得できるのです。
そしてこれを利用すると、「VLOOKUP関数」の引数「列番号」に指定したい番号を「COLUMN関数から1を引いた値」にすれば、数式による「2」という数値
が取得できるわけですね。
それでは、TOP20の「名称」と「値」をオートフィルで一気に取得するために、「セルC5(名称のランキング1位)」の「VLOOKUP関数」を書き直してみましょう。
絶対参照の範囲も変わりますので注意して見てください。
ちなみに、「セルC2」は「COLUMN関数」の動作確認で使っただけなので、このセルの関数は削除して綺麗にしておいてください。
では順番に見ていきます。
最初に「検索値」は、列番号を固定しました。
「名称(C列)」から「値(D列)」にオートフィルする時でも、検索値はB列のままなので、列を絶対参照に設定します。
次の「範囲」は、行も列も固定にしました。
先ほどのように、下方向だけのオートフィルなら行番号だけを絶対参照にしておくだけでも大丈夫でした。
しかし、下方向にも横方向にもオートフィルするのであれば、表全体を行列で絶対参照にしておく必要があります。
次の列番号は先ほどお話した通り、「COLUMN関数」を使います。
参照するセル番地はC列であればどこでもいいのですが、ここではタイトル名の「セルC4」を指定しました。
下方向にオートフィルしてもC列であればどこでもいいので、一緒に動いても良かったのですが、常にタイトル行の「セルC4」を参照させたいため、あえて行は絶対参照としています。
横にオートフィルした時には、「COLUMN関数」の引数は「セルD$4」となります。
そこから1引いた数値、つまり「3」が「VLOOKUP関数」の引数「列番号」となります。
それでは全部をオートフィルで埋めてみましょう。
黄色いセルはすべて埋まりました。
このようなランキング形式の表から上位〇名のデータを取得するような場合は、「LOOKUP関数」が効率的だというのが良く分かりますね。
「LOOKUP関数」は他にも、住所録や顧客表などのデータ一覧から目的の情報を探す場合にも適しています。
機能がさらに増えた「XLOOKUP関数」は、また別の機会に記事にしたいと思います。
問題5まとめ
今回は3つの関数が出てきましたね。
それではおさらいをしておきましょう。
- 「RANK関数」で目的のデータの順位付けができるか
- 「VLOOKUP関数」でデータの取得、セルの参照が正しく使えるか
- 列番号を数値で取得したい時に「COLUMN関数」を使えるか
- 「VLOOKUP関数」の引数に別の関数などを用いて、オートフィルによる効率的な作業ができるか
段々と複雑になってきましたね。
セル参照や条件分岐のために、「値を別の関数から取得する」という方法は「VLOOKUP関数」に限らず結構あります。
是非色々な関数を使いながら試してみてください。
次回は問題6に進んでいきます。