前回に引き続き、いつの間にかシリーズ化しているExcel入社テストの実践編第5回目です。
前回までは以下からどうぞ。
さて、Excel関数好きならたまらないlookup関数の登場です。
LOOKUP関数は難しい？
問題のファイルを真似て作ってみました。必要な方はこちらからダウンロードしてください。
解答のファイルを真似て作ってみました。必要な方はこちらからダウンロードしてください。
「lookup関数」と言えば、上級関数の先鋒でした。
その昔、少しExcel操作に慣れてきた頃に、「少し上級者向けの関数を覚えてみよー！」という流れで登場していた1段上の関数という存在だった気がします。
「lookup」に難しいイメージがあるのは、「必要とする場面の見極めが難しいから」ではないでしょうか。
”足し算した～い”と言う要望は明確で、「SUM」がすぐ出てきますが、”とある表から欲しいデータを取りた～い”と言う要望はどこか不明瞭だし、これだけだと色々な関数が思い浮かびます。
例え利用方法や式が分かっていても、普段の業務中に「ここはlookup関数だろっ！」というベストなタイミングを見つけられるかどうかなのです。
一応、「VLOOKUP」関数のイメージ図です。
Excelの利用が急務なのに、イマイチ掴めない方は・・・
とにかく慣れるしかないです。
なお、今回の問題は「lookup」関数を使わせるがためにこういう問題形式になっていますけど、Top20まで並べ替えるんだったらもっと早い方法がありますよね(笑)
「VLOOKUP」関数の使い方は、アップした回答例を見て頂くとして、このページでは、回答には載っていない別のやり方を見てみることにします。
これが実務なら関数は使うな！
さて、先程少しお伝えした通り、実務でこの問題のような環境に遭遇した場合、もちろん「VLOOKUP」関数、究極を言えば「RANK」関数すら使う必要がないんですよね。
そのやり方を見てみましょう。
RANK関数を使うなら
「RANK」関数で値の順位を求めた後に、「セルF4」をクリックします。
表の角のセルを選択するだけで、表のエリアを認識してくれます。
リボンの「データ」タブから「昇順」ボタンをクリックします。
表の一番左列を基準に数値の小さい順から並び変わりました。
20番目以降も表示されますが、これでTOP20のデータは取得できます。
最初の状態から一瞬で
「RANK」関数で順位となる数字を出す前にTOP20を取得してみます。
「セルG4」をクリックします。
※「セルF4」でも大丈夫ですが、今回はG列からの表なのであえて「G4」を選択しています。
リボンの「データ」タブから「並べ替え」をクリックします。
「並べ替え」ウィンドウで、上の図のように選択し、「OK」ボタンをクリックします。
同じように表のデータが並び変わりTOP20のデータは、行番号から5行目～24行目の部分となります。
元の表の順番を残しておく
元の表の順番を維持しつつ、TOP20を表示させるとします。
つまり、問題の回答の通りに「VLOOKUP」関数を使った形で終えるなら、以下のやり方がいいかもしれません。
メニューバーの「データ」で並べ替えた表のデータから20行をコピーします。
メニューバーの「ホーム」で「クリップボードツールバー」を表示しておきます(クリップボードツールバーがタスクバーに表示されておくようにします)。
「元に戻す」で表が最初の状態になるまで、操作を戻します。
貼り付けたい表の左上角をクリックし(この場合セルC5)、クリップボードツールバーに保存しておいたデータを貼り付けます。
クリップボードツールバーを表示させることがミソです。
表示させなければ、コピーしたデータは「元に戻す」を繰り返し最初の段階に戻った時には、消去されています。
一応お断りしておくと、20行ぐらいのデータ量なら関数を使わなくてもいいのではということです。
これが、「結果をTOP100まで表示させる」とか、「元の表データがしょっちゅう変わって作業を定型化したい」と言う時には”関数で自動表示させよう”という選択もありです。
まぁ、今回はあくまでもテストであり、問題文にしっかりと「関数だけを使用して抜き出して」と書いてあるので、関数を使わない方法は【こんなやり方もある】という事で見ておいて下さい。
ここはCELL関数でしょう
さて、今回のお話の最後です。
「VLOOKUP」関数で、TOP20の「名称」と「値」を求める時の関数式に注目してください。
「名称」を求める時の関数です。
3番目の引数に「表の何列目のデータから探すのか」を指定するのですが、
これはもちろん表の2列目から探すので、「2」と入力しています。
同様に「値」を求める時は、「3」を指定しています。
この3番目の引数は実数値なので、「名称」を求めた後にオートフィルで「値」にコピーしても同じ関数がコピーされるだけです。
(下の画像を参照)
ということは、「セルC5」に入力した関数の3番目の引数に「セル番地」が入るように仕向けたらいいわけです。
そして、その「セル番地」が「2」という答えを返してくれるような関数なり数式なりを使ってあげればいいというわけです。
そういった場合に便利な関数が「CELL」関数です。
指定したセルの様々な情報を返してくれるとっても便利な関数となります。
【CELL("COL",C4)】とすると、結果として「セルC4」の列番号を数値で返してくれます。
つまり、「3」という数値を取得できます。
それに1を引いてあげると、「2」と言う数値を取得できます。
つまり、【CELL("COL",C4)-1】という関数式を「VLOOKUP」関数の3番目の引数に指定してあげればよいのです。
そして、「セルC5」をオートフィルして「セルD5」にコピーしてみましょう。
今度は、【CELL("COL",D4)-1】と参照先が「D4」に変わるので、返ってくる結果は「3」になります。
あとは、2列まとめて選択し、オートフィルで20行分をコピーします。
(下の画像は、最後10行までのコピーで終わっています)
最後にまとめ
問題文には、「効率よく」とは書いていませんからね(笑)
最後のCELL関数で効率化しなくてもいい、ということなんでしょう。
でも、このCELL関数は、どんな場面でも使える便利な関数です。
今回は、列番号を求めましたが、他にも引数に指定した条件で色々な値を取得できます。
是非、実務でもどんどん活用してみてください。
LOOKUP関数は、入力したデータに対して、「自動化させる」目的で使用することの多い関数です。
マクロくらいまでの少しシステマチックなExcelファイルを作ろうと思ったら、このLOOKUP関数も併用して使われる事が多いと思います。
統計データから何かを探る必要が出てきた折には、是非、今日見たこの辺の関数を思い出してくださいね。
長くなったので、続きはまた次回
