こんにちは。
今回の記事では、
VLOOKUP関数の列番号をいちいち手入力するのが面倒くさい！
ということで、列番号をCOLUMN関数で取得する方法をご紹介してみたいと思います。
1列にだけVLOOKUP関数を設定するなら必要ありませんが、複数列に何度もVLOOKUP関数を設定する必要がある場合にとても便利な方法です。
1つ前の記事を読んでない、という方は先に読んでからのほうがこちらの内容を理解しやすいのでお勧めです。
これまで同様、ダウンロードデータをご用意しています。過去記事と同じデータを使いますので、すでにダウンロードしている方はそちらをご利用ください。
まだダウンロードしていない方は、以下の内容をよくご確認のうえ、自己責任でダウンロードをお願いします。
ダウンロードデータを利用される方は、下記「ダウンロード」をクリックしてご利用ください。「VLOOKUP練習問題①」というファイル名のエクセルデータが、各パソコンの[ダウンロード]フォルダに入ります。
エクセルデータを開いた際、「保護ビュー」が表示された場合は下の記事を参考にご対応ください。
※スマートフォンやタブレットからのダウンロードは操作保証致しかねますので、パソコンからご利用いただくようお願いします。
COLUMN（カラム）関数とは
列番号を取得するために使う関数は
COLUMN（カラム）関数
と言います。
カラムとは「列」という意味です。
「行」の場合はロウ（ROW）と言います。
どういった関数かというと、
指定したセルがエクセルの左端（＝A列）から数えて何列目かを調べる
という働きをする関数です。
では早速、使い方を見てみましょう。ダウンロードデータの空いているセルを使って確認していきます。
セルK3を選択し、半角英数の入力状態で
＝col
と入力すると、COLUMN関数の候補が表示されます。
- キーボードの[tab]キーを押す
- 画面の青い部分をマウスでダブルクリックする
のどちらか好きなやり方でCOLUMN関数を確定させ、
- [fx]をマウスでクリックする
- キーボードで[Shift]＋[F3]を押す
のどちらかを使って［関数の引数］ボックスを表示しましょう。
COLUMN（カラム）関数の引数
［関数の引数］ボックスをよく見てみると、数式の結果に既に答えが表示されています。
このまま［OK］を押して式を確定すると、「11」という答えが返ってくるということです。まだ引数を設定していないのに、どうして答えが出ているのでしょうか。
実は［関数の引数］ボックスには、その関数の説明が表示されています。
説明を見てみると、
参照には列番号を調べるセルまたはセル範囲を指定します。
範囲を省略すると、COLUMN関数が入力されているセルの列番号が返されます。
と記載がありますので、1つずつ見ていきましょう。
セルまたはセル範囲を指定した場合
1つ目は、
セルまたはセル範囲を指定した場合
ですが、セル範囲を指定してもあまり意味がなかったので、セルを指定した場合のみ解説していきます。
試しにセルA3を選択してみました。
すると、「数式の結果」部分に「1」と表示されました。これは、「このままOKすると1と表示されますよ」という意味です。
A列は一番左にある列なので、A列のどのセルを指定しても「1」という答えが返って来ます。
選択するセルをA4に変えてみても、同じ「1」という答えが返って来ます。
あくまでも何列目かを調べる関数なので、行は何行目でも関係ないという事になりますね。
範囲を省略した場合
次に範囲を省略、つまりセルを指定せずにそのまま［OK］した場合の結果を見てみましょう。
［関数の引数］ボックスの参照部分が空白になっていて、どのセルも指定していない事が確認できます。にも関わらず、「数式の結果」には「11」と答えが返っています。
この「11」はどこから来たのかというと、COLUMN関数を入力している列（K列）から取得されました。
［関数の引数］ボックスの説明にも
- 範囲を省略するとCOLUMN関数が入力されているセルの列番号が返される
と記載があります。
A・B・C・D…と順番に数えていくと、Kは11番目です。
COLUMN関数の式をコピーしてみると？
それではセル範囲を指定せず、このまま［OK］で式を確定させましょう。そして式をコピーするとどうなるのかを見てみたいと思います。
［OK］で式を確定させ、
結果を確認すると「11」と答えが返って来ました。K列なので「11」ですね。
ではこの式を、隣のL列にコピーしてみましょう。
結果を確認すると、L列の答えは「12」となっています。
A・B・C・D…と順番に数えていくと、Lは12番目ですよね。
この特徴を使って、VLOOKUP関数の列番号を自動的にズラそうというのが今回の目的です。ではさっそくVLOOKUP関数に応用してみましょう。
VLOOKUP関数を入力する
それでは第1四半期シートを開き、担当者欄にVLOOKUP関数を設定していきます。これまでの記事でやってきた方法で、［関数の引数］ボックスを表示しましょう。
普段自分がやっている方法がある、という方はその方法で構いません。
引数を設定する
［関数の引数］ボックスを表示したら、
- 検索値
- 範囲
を設定していきます。
こちらも今まで同様、設定してください。
今回も複数列に式のコピーをおこないますので、検索値は複合参照にしておいてください。
複合参照を知らない方、やり方を忘れてしまった方は前回の「VLOOKUP関数を複数列に使いまわしてみよう！」で確認しましょう。
列番号欄へのCOLUMN関数の入力方法
次に設定する引数は列番号です。COLUMN関数を使って設定していきましょう。
［関数の引数］ボックスの列番号欄にカーソルを立て、［クリップボード］という文字の下にある「▼」をクリックして候補を表示させ、一番下の「その他の関数」を選択してください。
「その他の関数」を選択すると、下図のように「関数の挿入」ボックスが表示されます。
エクセルに登録されているすべての関数が、「関数名」というところにアルファベット順に並んで表示されています。
この中から使いたい関数（今回はCOLUMN関数）を探しましょう。
「関数の挿入」ボックスの簡単な使い方
Aから順番に並んでいるので、Cで始まるCOLUMNだったら、マウスをスクロールして探しても、そんなに時間は掛かりません。でも、例えばWから始まる関数を探したい、となった時、マウスのスクロールで探すと時間が掛かって仕方ありません。
そこで簡単な探し方をご紹介します。
「関数名」の下に関数が並んでいるところで、ABS以外の関数をどれでもいいのでマウスでクリックします。
そしてキーボードで「C」と入力すると、「C」から始まる関数に移動してくれます。
ここからマウスのスクロールで探したほうが、時間短縮になって便利です。
ちなみに、関数名を一文字ではなく複数文字入力すると、より目的の関数に近づくことが出来ます。
※COLUMN関数の頭3文字「col」を素早く入力すると、一発でCOLUMN関数に移動できます。
かなり素早く入力しなければいけないので、慣れて来たら試してみてください。
COLUMN関数を見つけたら、［OK］を押してください。
COLUMN関数の引数を入力するボックスが表示されます。
参照でどこのセルを設定すればいいか？
COLUMN関数でセル参照を省略すると、式を入力している列数が返ってくる、ということを最初に確認しました。
今、この式はD列に入力されていますので、このまま参照を設定せず［OK］を押すと「4」という数字が返って来ます。
ここで本来設定したい列番号をマスタで確認すると、
担当者を表示するために「2」と設定する必要がありますので、B列のセルをどれか一つ選択します。
セルB1を選択しても、
セルB15を選択しても結果は同じです。
中途半端な位置のセルを選択すると、あとで式を見たときに混乱する恐れがあるので、B1、C1など、1行目で設定しておいたほうが分かりやすいかなと思います。
ということで、今回はセルB1で設定して先に進みます。
COLUMN関数からVLOOKUP関数への戻り方
COLUMN関数の参照を設定したあと、つい［OK］ボタンを押したくなりますが、絶対に押さないでください。
このタイミングで［OK］を押してしまうと、そのまま式が確定し、VLOOKUP関数の最後の引数が未入力のままになってしまうからです。
数式バーのVLOOKUPという文字部分をマウスでクリックすると、VLOOKUP関数のボックスが再度表示されますので、
最後の引数「検索方法」の設定を行いましょう。
式をコピーして結果を見てみよう
［OK］を押して式を確定させたら、E列に式をコピーしてみましょう。
列番号が自動的に「3」に変わり、販売店が表示されていると思います。
これはかなり便利ですね！
D列とE列、2列を選択している状態でオートフィルを使うと、2列まとめて式のコピーができます。セルE4の右下角に「＋」マークを出し、ダブルクリックしてみましょう。
ダブルクリックすると、最終行まで一気に式がコピーされます。
第2四半期シートで復習してみよう
忘れないうちに、第2四半期シートで復習してみましょう。
COLUMN関数の出し方や、VLOOKUP関数への戻り方が間違いやすいポイントなので、ゆっくり確認しながらやってみてください。
最初は間違えて当たり前です。間違えても気にせず、また最初からゆっくりやってみてくださいね。
解答例で式を確認しよう
練習ができたら、解答例で確認してみましょう。
解答例データのダウンロード
いつものように、解答例の入力されたデータを準備していますので、必要な方はご利用ください。「VLOOKUP応用②解答例」という名前のファイルがダウンロードフォルダに入ります。
今回の解答例は、
- №
- 販売単価
- 売上額
については未入力です。
解答例が必要な方は、以前の記事でご用意したダウンロードデータにてご確認ください。
解答例の確認
データのダウンロードに不安がある方や、ダウンロードできない状況の方向けに、式を記載しておきます。こちらも併せてご利用ください。
いつものように、各項目5つずつ記載しています。
担当者の式
セルD4：=VLOOKUP($C4,マスタ!$B$3:$D$12,COLUMN(B1),0)
セルD5：=VLOOKUP($C5,マスタ!$B$3:$D$12,COLUMN(B2),0)
セルD6：=VLOOKUP($C6,マスタ!$B$3:$D$12,COLUMN(B3),0)
セルD7：=VLOOKUP($C7,マスタ!$B$3:$D$12,COLUMN(B4),0)
セルD8：=VLOOKUP($C8,マスタ!$B$3:$D$12,COLUMN(B5),0)
販売店の式
セルE4：=VLOOKUP($C4,マスタ!$B$3:$D$12,COLUMN(C1),0)
セルE5：=VLOOKUP($C5,マスタ!$B$3:$D$12,COLUMN(C2),0)
セルE6：=VLOOKUP($C6,マスタ!$B$3:$D$12,COLUMN(C3),0)
セルE7：=VLOOKUP($C7,マスタ!$B$3:$D$12,COLUMN(C4),0)
セルE8：=VLOOKUP($C8,マスタ!$B$3:$D$12,COLUMN(C5),0)
まとめ
VLOOKUP関数のシリーズも3記事目となりましたが、少しは慣れてきましたか？
関数は難しいイメージがありますが、使い方さえ理解出来れば、あとはエクセルが全部やってくれますのですごく楽です。
ぜひ仕事やプライベートで活用してみてください。
それではまた。
