今回の目的
ズラーと数百、数千行が並ぶリストがあって、行ごとに「VLOOKUP」関数を作っているんですけど・・・コピペするのも面倒だし、後々編集することを考えると管理が大変だ〜！！汗
kabuさん、VLOOKUP関数を列全体に自動で反映できるような方法はないですか？
「ARRAYFORMULA」関数使えば、列全体に一括で自動反映できますよ。
① VLOOKUPを列全体に自動で反映する数式
② よく使う数式を「名前付き関数」にする方法
③ 数式の解説
今回はこれを話しましょう！
ということで、
「VLOOKUP関数を列範囲に自動反映してくれる関数を「名前付き関数」＝一つの関数にする」ことを目的に解説を始めていきます。
※結論としては、上部動画の挙動をする関数が作れます。
VLOOKUPを列全体に反映する数式
例えば、このスクリーンショットのような、「A:C」列にマスターのデータがあるとします。
「E」列に任意のテキストを入れると、E列テキストと一致した「C」列のデータが「F」列に出力させたいというイメージですね。
私は、F3セルにこんな数式を入れて、それをF4、F5・・・と、必要な行までコピペしているんですけど・・・
=VLOOKUP(E3,B$3:C$12,2,FALSE)ただ、これだとE列の「会社名」を追加した時に、またF列に数式をコピペしないといけないのが面倒！
それに、もしF列のどこか一部のセルの数式を途中でいじったら、列全部に反映するの忘れちゃいそうで！泣
そうですねー。
それではやはり「ARRAYFORMULA」関数を使いましょう。
F3セルに入れる数式はこんな感じ。
=ARRAYFORMULA(IFERROR(VLOOKUP(E3:E,B3:C12,2,FALSE),""))各関数の詳しい説明はこちらで解説するとして、
要すると、「VLOOKUP」関数で検索する範囲を指定し、それを「ARRAYFORMULA」関数で「E」列全体に反映しています。
「IFERROR」関数で、エラー（E列の空白セル）を非表示にしている。って感じですね。
この数式・・・
というか、この列全体にVLOOKUP関数を反映するっていうの、
（僕は）よく使うので「名前付き関数」にも登録しておきましょう。
「名前付き関数」・・・ですか？
「名前付き関数」について
ChatGPTの説明では・・・
スプレッドシートの「名前付き関数」とは、特定の計算式や値に名前を付け、スプレッドシート内で簡単に再利用できるようにする機能です。この機能を使用することで、複雑な計算式を繰り返し入力する必要がなくなり、スプレッドシートの可読性やメンテナンス性が向上します。
名前付き関数を使用すると、式や関数内で直接セル参照を書く代わりに、定義した名前を使用してその値や計算結果を参照できるようになります。これにより、式がより理解しやすくなり、誤りが発生する可能性も低減します。
ChatGPT4
要するに、数式を「名前付き関数」に登録することで、同じ数式を使いまわせたり、数式を見やすくすることができるってことですね。
「名前付き関数」を作成するメリット ・同じ数式を使いまわせる（スプレッドシートを跨いで） ・数式が短くなるので見やすい
「名前付き関数」を作成する際のデメリット ・設定が面倒で、説明書きがわかりづらい ・セルや列、行などの範囲を置き換える必要があるため、数式のチェックがやりづらい
「名前付き関数」を作るにあたってメリットとデメリットはありますが、
上でも書いたように数式がシンプルに見やすくなるので管理は非常にしやすいですね。
先ほど紹介した数式が、最終的にはこんな感じに短縮できます。
//通常の数式
=ARRAYFORMULA(IFERROR(VLOOKUP(E3:E,B3:C12,2,FALSE),""))//「名前付き関数」で作成した数式
=AVLOOKUP(E3:E,B3:C12,2)
数式がこんなに短くなるんですね！！
これなら今後も管理しやすいかも☆*+.
また、「名前付き関数」については以下の記事でも詳しく解説しています。
よろしければ覗いてみてください。
VLOOKUP関数を自動で列全体に反映する「名前付き関数」の作成方法
そんじゃ「名前付き関数」を作っていきますか・・・
今回は、この数式を
=ARRAYFORMULA(IFERROR(VLOOKUP(E3:E,B3:C12,2,FALSE),""))「名前付き関数」に登録することで、
シンプルな数式に出来るようにしたいと思います。
=AVLOOKUP(E3:E,B3:C12,2)
上部「メニュー」の「データ」から「名前付き関数」を選択します。
画面右に「名前付き関数」のメニューが表示されるので、「新しい関数を追加」を選択します。
入力項目に以下を入れていきます。
- 関数名：「AVLOOKUP」（ARRAYFORMULAの「A」＋「VLOOKUP」として名前付けていますが、自由に入力して問題ありません。）
- 関数の説明：「列範囲に対して、VLOOKUP関数を自動適用します。」
（自分や使用する方が後で見てわかる内容にします。） - 引数のプレースホルダ：下の「数式の定義」で指定している引数を入力します。
「キーワード列」、「検索範囲」、「出力列」それぞれを入力し、エンターキーを押すと登録されます。 - 数式の定義：「=ARRAYFORMULA(IFERROR(VLOOKUP(キーワード列,検索範囲,出力列,FALSE),””))」
「次へ」を押します。
初めて「名前付き関数」を作成する方は、これが何を示しているのかよくわからないかと思いますが、、とりあえずこのまま入力してみてください（後で編集できますし）。
- 引数の説明：「検索するキーワード列を指定します。」
- 引数の例：「A1:A」
- 引数の説明：「キーワードで検索を行う範囲を指定します。」
- 引数の例：「B1:D」
- 引数の説明：「範囲内で出力を行いたい列番号を指定します。」
- 引数の例：「3」
右下「作成」を押します（スクリーンショットでは「更新」になっていますが、初めて作る場合は「作成」になります。）
作成した「名前付き関数」を試してみる
教えてもらった通りに設定しましたが・・・
正直、何が何だかわかりませんでした・・・
そうですよね。ま、とりあえず動かしてみましょう
上手く動くか、実際に試してみます。
「=AVL...」と入れれば、先ほど登録した「AVLOOKUP」関数がアシストされます。
え、やったー！！
よく分からなかったけど、自分で作成した関数が出てくるの嬉しい！！
続いて「キーワード列」として、「E3:E5」までを選択します。
ここがVLOOKUPの検索キーに当たる部分ですが、作った関数では列範囲を一気に指定できます。
「検索範囲」には、マスターデータの「B3:C12」を選択します。
「C」列を出力したいので、「出力列」には「2」を入れます。
とじかっこをしてエンターを押せば・・・
無事、「F」列に値が出力されました。
どうですかね？
ちゃんとできましたかね？
なるほど！
先ほど設定した「キーワード列」や「検索範囲」、「出力列」などはこうやって出てくるんですね！
「説明」もちゃんと書いておけば、後で使う時とか、他の人が使う時にわかりやすい ♪
もしエラーが出てしまった場合は、最初に登録した「数式の定義」を見直してみてください
VLOOKUP関数を自動で列全体に反映する数式、使用した関数の解説
VLOOKUP関数を自動で列全体に反映する数式の解説
今回作成した数式の中身を見ていきましょう。
数式はこんな感じ。
=ARRAYFORMULA(IFERROR(VLOOKUP(E3:E,B3:C12,2,FALSE),""))VLOOKUP関数は分かるですが、
他の関数と混ざってどうなっているのかがよく分かりません汗
これなら分かるんじゃないですかね？
=IFERROR(VLOOKUP(E3,B3:C12,2,FALSE),"")VLOOKUP関数で検索した値が「エラー」だったら、「””」を出力ってことです。
確かに、それなら分かります！！
VLOOKUPの「検索キー（この場合「E3」）」は1つの値・セルしか指定できませんが、
ARRAYFORMULA関数で、この検索キーを「1つのセル」→「配列」にすることが出来ます。
ARRAYFORMULAの処理はこんなイメージですね。
=ARRAYFORMULA(
IFERROR(VLOOKUP(E3,B3:C12,2,FALSE),"")
IFERROR(VLOOKUP(E4,B3:C12,2,FALSE),"")
IFERROR(VLOOKUP(E5,B3:C12,2,FALSE),"")
IFERROR(VLOOKUP(E6,B3:C12,2,FALSE),"")
IFERROR(VLOOKUP(E7,B3:C12,2,FALSE),"")
IFERROR(VLOOKUP(E8,B3:C12,2,FALSE),"")
・
・
・
IFERROR(VLOOKUP(E,B3:C12,2,FALSE),"") //E列の終わりまで処理をおこなう
)なるほど！
すごく便利な関数ですね！
中身の解説はこんなところですが、
次の項で各関数についてAiに説明してもらった文章も載せておきますね。
VLOOKUP関数
概要
VLOOKUP関数は、表の縦方向を検索し、指定した値と同じ行にある別の列の値を取得する関数です。
引数
- 検索値: 検索対象となる値
- 検索範囲: 検索を行う表
- 列番号: 取得したい値がある列番号
- 検索方法: 完全一致/近似一致
例
=VLOOKUP(A1, B:D, 3, FALSE)- A1セルに検索値
- B:Dセルが検索範囲
- 3列目の値を取得
- 完全一致で検索
メリット
- 表から特定の情報を簡単に抽出できる
- 大量のデータ分析に有効
デメリット
- 検索範囲は昇順に並べ替える必要がある
- 完全一致/近似一致の判定方法を理解する必要がある
ARRAYFORMULA関数
概要
ARRAYFORMULA関数は、配列に対して数式を適用し、結果を配列として返す関数です。
特徴
- 複数のセルに個別に数式を入力する必要がない
- 数式の入力を簡潔化
- 計算処理を効率化
例
=ARRAYFORMULA(A1:A10*B1:B10)- A1:A10とB1:B10の各要素を乗算
- 結果はA1:A10に出力
メリット
- 繰り返し処理を省略できる
- コードの簡潔化
- エラーの削減
デメリット
- 処理速度が遅くなる場合がある
- 数式の理解が難しい場合がある
IFERROR関数
概要
IFERROR関数は、数式がエラーを返した場合に、代替値を返す関数です。
引数
- 数式: エラーの可能性がある数式
- 代替値: エラー発生時に返す値
例
=IFERROR(A1/B1, "エラー")- A1/B1がエラーの場合、”エラー”と表示
- エラー以外の場合、A1/B1の結果を表示
メリット
- エラー処理を簡単に記述できる
- 表の見やすさを向上
- ユーザーエラーを防ぐ
デメリット
- エラーの種類を特定できない
- 複雑なエラー処理には不向き
まとめ
今回は
① VLOOKUPを自動で列全体に反映する数式
② よく使う数式を「名前付き関数」にする方法
③ 数式の解説
について話しました。
「名前付き関数」の設定は、最初はよく分からなかったけど、
登録してみると「ここに反映される文章か〜！」と分かったので、
まずは一度登録して、後で確認するってのもアリだと思いました！
今回の数式は、特にARRAYFORMULA関数がミソになるので、
それをよくよく理解して使うと、色んなことに活かせると思いますよ。
コメント