Google Sheetsの別のシートからVLOOKUPする方法
Google SheetsのVlookup（vertical lookup）の可能性を最大限に引き出すには、それを使用してさまざまなスプレッドシートやワークブック間でデー
Vlookup関数は、数時間の作業をほんの数秒に変えることができる非常に強力な機能です。 この関数は単一シートのデータ分析に便利ですが、異なるスプレッドシート間でデータを呼び出すことで、Vlookupの使いやすさをさらに向上させることができま
この関数を使用すると、大量のデータから特定の値を迅速かつ効率的に検索して取得することができます。
複数のスプレッドシートで使用できるため、データをきれいに保つことが容易になり、シート間に変更があると自動的に更新されます。
この記事はカバーしています:
Vlookup式
Vlookup構文は非常に簡単ですが、非常に複雑な程度に使用できます。
名前の「垂直」が示すように、Vlookupは、確立された範囲の左端の列で行の一致を検索し、インデックスで識別されるセルの値を返します。
列を検索し、一致する行、通常は別の列から値を返します。Vlookupの最大の弱点の1つは、右側の列で一致を検索できず、列の結果を左側に戻すことができないことです。 言い換えれば、Vlookupは右を見るか右折することしかできません。
これは、部分的に分解すると式がどのように見えるかです:
=Vlookup(search_key,range,index,is_sorted)
各コンポーネントを個別に見てみましょう:
- =Vlookup（）：これはパラメータのない関数自体です。 これは、Googleシートに行動するように指示するものです。
- search_key:このパラメータは、一致させる情報を定義します。 これは、名前、数値、ブール値、または別のものである可能性があります。 定義した静的な値であるか、セルに格納されている相対値である可能性があります。 これを「A2」に設定すると、セル「A2」の値が検索されます。 “True”に設定すると、テキスト文字列”true”と一致します。”
- : これは、vlookup関数に、一致を探す場所と、返すはずの値がある範囲を指示します。 列Bの値の一致を検索していて、列CまたはDの値を返す場合は、範囲をB：Dに設定します。
- index：このパラメータは、返す列の値をVlookupに指示します。 インデックスは、シートではなく、範囲に対する相対的なものです。 したがって、列のような文字の代わりに数字が通常定義されています。 検索が列Bで一致し、列Cから値を返す場合、インデックス値は2です。 列Bの一致から列Dを返す場合、インデックス値は3です。
- is_sorted:このパラメータの名前は、”false”に設定されている場合は完全一致を返し、”true”に設定されている場合は最も近い一致を返すほど明確ではありません。”デフォルトではTrueが設定されていますが、ほとんどの用途ではfalseが推奨されます。
さて、それをすべてまとめて見てみましょう:
=Vlookup(A2,A2:B5,2,false)
同じブックと別のブックでVLOOKUPを使用する
ほとんどの場合、同じシートのGoogleシートでVLOOKUP式を使用しますが、同じブック内の2つのシート間、または別のブック間でVLOOKUPするために使用する必要がある場合があります。
たとえば、参照データが別のシートまたは別のブックにある間に、ワークシート内の特定の項目からデータを取得することができます。
Vlookup関数を使用して別のシートからデータを取り込む方法は、同じワークブックまたは別のワークブックで作業している場合とは多少異なります。
同じワークブックVlookupの式は次のようになります:
=vlookup(search_key,{sheet name}!{cell range},index,is_sorted)
“があることに注意してください！”シート名とセル範囲の間。 また、同じワークブックVlookupの範囲の周りに引用符はありません。
私たちのテストでは、”Called”という名前のシートで範囲をA3：B6に設定したいと思います。「列Aで一致するものを検索し、列Bの値を返しています。
以下はこれを行う式です:
=Vlookup(A2,Called!A2:B5,2,false)
上記の数式は、現在のシートの”Called”という名前のシートから2番目の列から値を取得します。
さて、別のGoogle Sheetsワークブックから値を取得する必要がある例を見てみましょう
=vlookup(search_key,importrange("{sheetsURL}","{sheet name}!{cell range}"),index,is_sorted)
このバージョンでは、”importrange()”という新しいコマンドを使用します。
このコマンドの構文では、データのインポート元のブックのURLを定義し、特定のシートを定義し、範囲を設定するように求められます。
式は次のように分解されます:
Importrange("{sheetsURL}","{sheet name}!{cel range}")
- {sheetsURL}：引用符で、アクセスするGoogle SheetsファイルのURLを追加します。 例: “https://docs.google.com/spreadsheets/d/1AJcuVkYvdiW0NAlfuI”
- {シート名}!{cel range}：これを同じワークブックメソッドで構成したのと同じ方法で設定します。 ただし、今回はシート名とセル範囲の周りに引用符があることに注意してください。
今、付加価値とすべての値が追加されてそれを見てください :
=Vlookup(A2,importrange("https://docs.google.com/spreadsheets/d/18nsDPJ","Called!A2:B5"),2,false)
繰り返しますが、クロスシートVlookup参照で設定する必要がある3つの重要な値があります:
- ブックURL{sheetsURL}
- シートページ{シート名}
- セル範囲{セル範囲}:
Vlookupプロセスを実行する
他のシートやブックを参照するときにVLOOKUPがどのように機能するかのプロセスを理解しましょう。
同じワークブック内の別のワークシートからのVLOOKUP
のは、Vlookupがどのように動作するかを紹介するために、製品の在庫の簡単なケースを使用してみましょう。
この例では、ガジェット、ギズモ、Thingamabobs、ウィジェットの四つの製品ラインアップがあります。 現在、我々は在庫の任意の防犯機器を持っていません。 私たちの最初のシート、”アクティブ”は私たちにすべての製品のリストを与え、製品が在庫があるかどうかを教えてくれます:
のデータセットには、在庫がある各製品の数を一覧表示する”Called”という名前の2番目のシートもあります。
ここで、”アクティブ”タブに在庫のある製品の数を表示したいとしましょうが、スプレッドシートページの更新一番上のセルにVlookup式を入力します—私たちの場合は、別のシートを参照するために
=Vlookup(A2,Called!A2:B5,2,false)in cell C2.
今、私たちの”アクティブな”ページは、”呼び出された”ページから値を引っ張っています。 Vlookupを使用する利点の1つは、両方のスプレッドシートに異なる順序でリストされている場合でも、製品が一致することです。
参照されているスプレッドシートから製品が欠落している場合にもスローされません。
別のワークブックの別のワークシートからのVLOOKUP
別のワークブックからデータを参照するプロセスはもう少し複雑です。
前の例と同じガジェット、ギズモ、Thingamabobs、ウィジェットの製品ラインナップを使用します。
今回は、別のワークブックの”Outside”というシートに同じ情報を入れたいと思います。
他のブックの「呼び出された」シートから情報を取り込む必要があります:
ここで、”Outside”タブに在庫のある製品の数を表示したいとしますが、元のワークブックの”Called”スプレッドシートページの更新につ
一番上のセルにVlookup式を入力します。
=Vlookup(A2,importrange("https://docs.google.com/spreadsheets/d/18nsDPJ-","Called!A2:B5"),2,false)in cell C2.
のVlookupへのImportrangeこれで、2番目のワークブックは最初のワークブックの在庫数を参照しています:
から取得するために使用されます。
スプレッドシート内の情報の一部を取り込んで、別のスプレッドシートで簡単に分析できるようにするのにも非常に便利です。 Vlookupコマンドを使用すると、必要な情報だけを簡単に取得できます。
VLOOKUPを使用して別のシート/ワークブックを参照するときのヒント
式で別のシートまたはワークブックを参照するときに注意するヒントをいくつか示します。
範囲について具体的に
Vlookup機能は非常にパフォーマンスが高く、ブックのパフォーマンスがクロールされる可能性があります。
参照する範囲を特定することで、パフォーマンスの低下を避けることができます。”A”のように列全体を呼び出す代わりに
- :B”は、”A1：B1000″のような特定の開始セルと終了セルを参照します。 これにより、同じ量の情報を取り込むためにGoogle Sheetsが必要とする作業の量が削減されます。列Aで情報を検索し、結果を列Dに戻す場合は、「A1：F1000」の代わりに「A1：D1000」のような参照を使用します。 使用されていない場合は、範囲内の列EとFを参照する必要はありません。
これは、異なるブック間で情報を呼び出す場合に特に重要です。 クロスブックVlookupを実行している場合、2つの間でデータを転送するにはインターネット帯域幅が必要です。
不必要な呼び出しを防ぐために条件文を使用する
クロスシートVlookupでスローダウンを防ぐもう一つの方法は、条件文を使用してGoogleシートがVlookupを実行すたとえば、vlookup呼び出しを使用しているシートに、実行する必要がないことを示す情報がある場合は、それを使用してください。
この製品の例では、”アクティブ”シートに製品が在庫されているかどうかが表示されます。 製品が在庫切れであることがわかっているため、在庫数を定義するためにVlookupを使用する必要はありません。
このためには、”=if()”関数を使用します。 この関数は、与えられた条件がtrueかfalseかを尋ね、それぞれの場合に異なる何かを行います。 構文は次のようになります:
=if(logical_expression, value_if_true, value_if_false)
単純なユースケースでは、セルA1の値がセルB1の値よりも大きいかどうかを判断するために使用できます。 したがって、式A1>B1は次のようになります:
=if(A1>B1, "A1 is greater", "B1 is greater")
数式は、A1が大きい数値の場合は”A1is greater”、B1が大きい数値の場合は”B1is greater”というテキストを返します。製品ワークシートの場合、「在庫あり」の値が「いいえ」の場合、Vlookupを実行したくありません。 だから我々はこのような私たちのif文を設定します:
- logical_expression：B2=”YES”—セルB2のデータが”YES”の場合、これは”真の場合の値”を実行します。
- Value_If_True:Vlookup(A2,Called!A2:B5,2,false)–論理式がtrueを返す場合、これはVlookupを実行します。
- : “out of stock”-セルB2の値が”YES”以外の場合、これはテキスト”out of stock”を返します。”
我々はそれをすべて一緒に置く場合、それはこのように見える出てくる:
=IF(B2="YES",Vlookup(A2,Called!A2:B5,2,false),"out of stock")
セルC3が値”0″を返す代わりに”在庫切れ”と表示されるようになりました。 この場合、データを戻す必要がないため、Vlookupの実行を避けました。
結果を計算するためにGoogleシートに詳細な情報を入力している間は、プログラムの作業が少なくなります。単一のVlookupよりも多くの「ifチェック」を実行する作業がはるかに少なくなります。 この手法を使用すると、Googleスプレッドシートを高速化し、パフォーマンスを向上させるのに役立ちます。
権限があることを確認してください
明白なセキュリティ上の理由から、Googleスプレッドシートでは、権限がない限り、別のブックからデータを取得できません。
Vlookupであるブックを別のブックから参照するには、両方の作成者であるか、両方を使用する権限を持っている必要があります。 アカウントまたは共有URLを使用して、承認されたユーザーとして追加できます。
Vlookupを使用して、異なるシートやブック間で情報を参照することは、あなたの処分で持っている信じられないほど強力なツールです。
このコマンドは、参照されているすべてのシートで元のシートに加えられた変更を反映するため、クロスシートの使用に特に役立ちます。