資料ダウンロード
SEO無料相談
今回はいつもの記事とは毛色を変えて、普段の業務に使えそうなテクニック系のものにしてみました。
題して「誰も教えてくれないExcel関数の便利な使い方（第1回）」
この記事はExcelの関数に多少は慣れている人が次のワンステップを踏み出せるようになることを目的としています。
その為、一つひとつの関数の使い方などの話は省かせていただきます。
予めご了承くださいませ。
＼「SEO」で1位の弊社が研究し尽くしたSEO×AI活用ノウハウを無料配布！／
「自分でSEOの疑問を調べるは大変」
という方はウィルゲートにご相談ください。
「自社サイトにはどんなSEO改善をすれば良いのか？」「アルゴリズム変動で影響を受けたサイトを強化するにはどうしたら良いのか？」など、まだ具体的にサービス導入を検討していない方もお気軽にご相談ください。
VLOOKUP関数は使うな
すいません。いきなりVLOOKUP関数を全否定しました。
VLOOKUP関数はとても便利で使いやすい関数なのですが、いかんせんテンプレートとして使いにくいので個人的にはあまりおすすめしない関数でもあります。
どういうことか、簡単に説明します。
例えば、あなたは日々商品の値段を調査する必要があるとします。
取り扱っている商品の値段の一覧は、管理部門が日々マスタテーブルに入力していきます。
そして、VLOOKUP関数を使えば下図のようにID部分で値段や商品名を引っ張ることができます。
とても便利ですね。
しかしVLOOKUP関数には2つの落とし穴が存在します。
まず一つは、検索引数とするID部分が引っぱってきたいデータより左側にある場合しか使えないということです。
つまり下記のようにID部分が値段より右に存在する場合には使えません。
今回の例ではあまりそういったことは無いでしょうが、例えば値段から商品名を引っぱりたくなった場合ではVLOOKUPが使えないということになります。
また、どこかのタイミングでマスタテーブルの列順を入れ替えたいといったニーズもあるでしょう。
そうしたときに読み込みテーブルを新たに作り直さないといけなくなってしまいます。
そしてもう一つは、途中でマスタテーブルの列を増やす必要があった場合に、関数が壊れてしまうということです。
下図のようにマスタテーブルに産地の情報も入力しなければならなくなった場合、読み込み側のテーブルは意図しないデータを取ってきてしまうことになります。
これは、VLOOKUP関数の3つ目の引数で、範囲の3列目のデータを取ってくると直接指定してしまっていることに起因します。
マスタテーブルでは3列目は「値段」でなく「産地」になってしまったためにこのような問題が起こったのです。
複数の関数を組み合わせることで解決する
このようにVLOOKUP関数を単一で使っていると、マスタデータの変更に柔軟に対応できません。
日々複数の人が使うテンプレートファイルにするには、都度の修正が必要になってしまいあまり向いていないのです。
できるだけ普遍的に使い続けるには、複数の関数を組み合わせることで解決します。
今回のようなVLOOKUP関数を置き換える場合には、次の関数たちを使います。
- INDEX関数
- MATCH関数
- COLMUN関数
先に正解を見せると、こんな感じになります。
=INDEX(テーブル1,MATCH([@発注ID],テーブル1[ID],0),COLUMN(テーブル1[値段])-COLUMN(テーブル1)+1)
この関数の組み合わせで大切な部分は
「COLUMN(テーブル1[値段])-COLUMN(テーブル1)+1」
この部分です。
この部分を数字で記述してしまうと、VLOOKUP関数の2つ目の落とし穴と同じことが起きてしまいます。
また、なぜINDEX関数の第三引数に設置したCOLUMN関数を引いたり1を足したりしているのかというと、これはCOLUMN関数が列番号を絶対参照してしまうからです。
もしマスタテーブルの場所がずれた場合、データ読み込みができなくなったり、意図しないデータを取ってきてしまいます。
その為、目的の列数からテーブルの始点列数を引いてを足すことにより、指定した参照範囲に対して相対的な列番号を指定しているのです。
この関数の組み合わせであれば、マスタテーブルの列の増減と、テーブル自体の移動に対応することができるのです。
＼「SEO」で1位の弊社が研究し尽くしたSEO×AI活用ノウハウを無料配布！／
まとめと次回予告
一時的なデータの取得であれば、このような面倒くさい関数の組み合わせをする必要は全くありません。
素直にVLOOKUP関数を使ったほうが圧倒的に早いです。
しかし、毎日のようにデータ集計を行う必要があり、マスタデータの形式がある程度変更される可能性があるなら、このように関数の組み合わせを工夫することで予期せぬデータの取得エラーを最小限に留めることができます。
今回は第1回ということで、INDEX関数とMATCH関数の組み合わせという「それ教えてもらったことあるよ」と言われそうなものを題材にしてしまいました。
第2回の掲載で書こうと思っている「配列数式」の布石のためという言い訳をお許し下さい。
次回は検索引数が255文字より大きくなってしまった場合のデータの引用について、今回のINDEX関数とMATCH関数の応用事例を紹介したいと思います。
また、「エクセルでこんな場合に困っている」というようなことがあれば、はてブのコメントに記載いただければ今後の記事のネタになるかもしれないです。
SEOに関して、何から着手すべきかわからない、なかなか成果がでないなどのお悩みがございましたら SEO無料相談も受け付けておりますので、お気軽にお問合せください。
▼この記事を読んだあなたにおすすめ! コンテンツマップの作成方法と使えるツールまとめ【コンテンツマーケティング基礎編】
