【応用編その2】VLOOKUP関数の列番号をCOLUMN関数で取得する方法

こんにちは。

今回の記事では、

VLOOKUP関数の列番号をいちいち手入力するのが面倒くさい！

ということで、列番号をCOLUMN関数で取得する方法をご紹介してみたいと思います。

これまで同様、ダウンロードデータをご用意しています。過去記事と同じデータを使いますので、すでにダウンロードしている方はそちらをご利用ください。

まだダウンロードしていない方は、以下の内容をよくご確認のうえ、自己責任でダウンロードをお願いします。

COLUMN（カラム）関数とは

列番号を取得するために使う関数は

COLUMN（カラム）関数

と言います。

どういった関数かというと、

指定したセルがエクセルの左端（＝A列）から数えて何列目かを調べる

という働きをする関数です。

では早速、使い方を見てみましょう。ダウンロードデータの空いているセルを使って確認していきます。

セルK3を選択し、半角英数の入力状態で

＝col

と入力すると、COLUMN関数の候補が表示されます。

• キーボードの[tab]キーを押す
• 画面の青い部分をマウスでダブルクリックする

のどちらか好きなやり方でCOLUMN関数を確定させ、

• [fx]をマウスでクリックする
• キーボードで[Shift]＋[F3]を押す

のどちらかを使って［関数の引数］ボックスを表示しましょう。

COLUMN（カラム）関数の引数

［関数の引数］ボックスをよく見てみると、数式の結果に既に答えが表示されています。

このまま［OK］を押して式を確定すると、「11」という答えが返ってくるということです。まだ引数を設定していないのに、どうして答えが出ているのでしょうか。

実は［関数の引数］ボックスには、その関数の説明が表示されています。

説明を見てみると、

と記載がありますので、1つずつ見ていきましょう。

セルまたはセル範囲を指定した場合

1つ目は、

セルまたはセル範囲を指定した場合

ですが、セル範囲を指定してもあまり意味がなかったので、セルを指定した場合のみ解説していきます。

試しにセルA3を選択してみました。

すると、「数式の結果」部分に「1」と表示されました。これは、「このままOKすると1と表示されますよ」という意味です。

A列は一番左にある列なので、A列のどのセルを指定しても「1」という答えが返って来ます。

選択するセルをA4に変えてみても、同じ「1」という答えが返って来ます。

範囲を省略した場合

次に範囲を省略、つまりセルを指定せずにそのまま［OK］した場合の結果を見てみましょう。

［関数の引数］ボックスの参照部分が空白になっていて、どのセルも指定していない事が確認できます。にも関わらず、「数式の結果」には「11」と答えが返っています。

この「11」はどこから来たのかというと、COLUMN関数を入力している列（K列）から取得されました。

［関数の引数］ボックスの説明にも

• 範囲を省略するとCOLUMN関数が入力されているセルの列番号が返される

と記載があります。

COLUMN関数の式をコピーしてみると？

それではセル範囲を指定せず、このまま［OK］で式を確定させましょう。そして式をコピーするとどうなるのかを見てみたいと思います。

［OK］で式を確定させ、

結果を確認すると「11」と答えが返って来ました。K列なので「11」ですね。

ではこの式を、隣のL列にコピーしてみましょう。

結果を確認すると、L列の答えは「12」となっています。

この特徴を使って、VLOOKUP関数の列番号を自動的にズラそうというのが今回の目的です。ではさっそくVLOOKUP関数に応用してみましょう。

VLOOKUP関数を入力する

それでは第1四半期シートを開き、担当者欄にVLOOKUP関数を設定していきます。これまでの記事でやってきた方法で、［関数の引数］ボックスを表示しましょう。

引数を設定する

［関数の引数］ボックスを表示したら、

• 検索値
• 範囲

を設定していきます。

こちらも今まで同様、設定してください。

列番号欄へのCOLUMN関数の入力方法

次に設定する引数は列番号です。COLUMN関数を使って設定していきましょう。

［関数の引数］ボックスの列番号欄にカーソルを立て、［クリップボード］という文字の下にある「▼」をクリックして候補を表示させ、一番下の「その他の関数」を選択してください。

「その他の関数」を選択すると、下図のように「関数の挿入」ボックスが表示されます。

エクセルに登録されているすべての関数が、「関数名」というところにアルファベット順に並んで表示されています。

この中から使いたい関数（今回はCOLUMN関数）を探しましょう。

「関数の挿入」ボックスの簡単な使い方

Aから順番に並んでいるので、Cで始まるCOLUMNだったら、マウスをスクロールして探しても、そんなに時間は掛かりません。でも、例えばWから始まる関数を探したい、となった時、マウスのスクロールで探すと時間が掛かって仕方ありません。

そこで簡単な探し方をご紹介します。

「関数名」の下に関数が並んでいるところで、ABS以外の関数をどれでもいいのでマウスでクリックします。

そしてキーボードで「C」と入力すると、「C」から始まる関数に移動してくれます。

ここからマウスのスクロールで探したほうが、時間短縮になって便利です。

COLUMN関数を見つけたら、［OK］を押してください。

COLUMN関数の引数を入力するボックスが表示されます。

参照でどこのセルを設定すればいいか？

COLUMN関数でセル参照を省略すると、式を入力している列数が返ってくる、ということを最初に確認しました。

今、この式はD列に入力されていますので、このまま参照を設定せず［OK］を押すと「4」という数字が返って来ます。

ここで本来設定したい列番号をマスタで確認すると、

担当者を表示するために「2」と設定する必要がありますので、B列のセルをどれか一つ選択します。

セルB1を選択しても、

セルB15を選択しても結果は同じです。

COLUMN関数からVLOOKUP関数への戻り方

COLUMN関数の参照を設定したあと、つい［OK］ボタンを押したくなりますが、絶対に押さないでください。

このタイミングで［OK］を押してしまうと、そのまま式が確定し、VLOOKUP関数の最後の引数が未入力のままになってしまうからです。

数式バーのVLOOKUPという文字部分をマウスでクリックすると、VLOOKUP関数のボックスが再度表示されますので、

最後の引数「検索方法」の設定を行いましょう。

式をコピーして結果を見てみよう

［OK］を押して式を確定させたら、E列に式をコピーしてみましょう。

列番号が自動的に「3」に変わり、販売店が表示されていると思います。

D列とE列、2列を選択している状態でオートフィルを使うと、2列まとめて式のコピーができます。セルE4の右下角に「＋」マークを出し、ダブルクリックしてみましょう。

ダブルクリックすると、最終行まで一気に式がコピーされます。

第2四半期シートで復習してみよう

忘れないうちに、第2四半期シートで復習してみましょう。

COLUMN関数の出し方や、VLOOKUP関数への戻り方が間違いやすいポイントなので、ゆっくり確認しながらやってみてください。

解答例で式を確認しよう

練習ができたら、解答例で確認してみましょう。

解答例データのダウンロード

いつものように、解答例の入力されたデータを準備していますので、必要な方はご利用ください。「VLOOKUP応用②解答例」という名前のファイルがダウンロードフォルダに入ります。

今回の解答例は、

• №
• 販売単価
• 売上額

については未入力です。

解答例が必要な方は、以前の記事でご用意したダウンロードデータにてご確認ください。

解答例の確認

データのダウンロードに不安がある方や、ダウンロードできない状況の方向けに、式を記載しておきます。こちらも併せてご利用ください。

いつものように、各項目5つずつ記載しています。

まとめ

VLOOKUP関数のシリーズも3記事目となりましたが、少しは慣れてきましたか？

関数は難しいイメージがありますが、使い方さえ理解出来れば、あとはエクセルが全部やってくれますのですごく楽です。

ぜひ仕事やプライベートで活用してみてください。
それではまた。