データ内の情報を検索するには、次のような LOOKUP 関数を使用します。
これらの関数を実行する際は時間がかかることがあるため、この記事では LOOKUP のパフォーマンスを最適化してコンピュータの消費電力をおさえる方法について説明します。
LOOKUP 関数で範囲を並べ替えるより効率的に並べ替えるには、SORT 関数の代わりに [範囲を並べ替え] を使用します。
- セルまたは列を選択します。
- 上部のタスクバーで、[データ]
[範囲を並べ替え] をクリックします。
- いずれかを選択します。
- [左端の列] を基準に昇順で範囲を並べ替え。
- [左端の列] を基準に降順で範囲を並べ替え。
- 範囲の並べ替え詳細オプション: 複数の列を階層的な順序で一度に並べ替えます。
[範囲を並べ替え] をクリックします。ヒント: [範囲を並べ替え] では、最初にデータを並べ替えてから、並べ替えたデータを VLOOKUP 関数にフィードします。
禁止事項:
重要: 次の例は、SORT 関数を使用しない方法を示しています。
=VLOOKUP([検索キー], SORT(A1:B10, 1), 2)
この例では、SORT は VLOOKUP 関数でネストされています。並べ替え済みの範囲のデータが変更されるたびに、SORT 関数がすべてのデータに対して不要な再調査を行い、SORT 関数を新しく実行します。
ヒント: SORT はできるだけデータセットに 1 回だけ適用します。必要に応じて参照できます。
計算を高速化するには、セット全体で重複データを削除します。
- 対象となる列と行をすべて選択します。
- 上部のタスクバーで [データ]
- 分析する列を選択します。
- [重複を削除] をクリックします。
ヒント: 列や行で値の重複が多いデータの場合、計算速度が低下する可能性があります。
LOOKUP 関数:
- 重複をうまく検出できません
- 一致しないすべての重複値を含むデータセット全体を検索します
コンピュータ上で LOOKUP 計算をローカルで実行するには、まずデータをスプレッドシートにインポートします。
- IMPORTRANGE を使用して、LOOKUP のあるスプレッドシートの空の範囲にデータを取り込みます。IMPORTRANGE の詳細
- インポートしたデータを、範囲として LOOKUP 関数内で参照します。
禁止事項:
重要: 次の例は、IMPORTRANGE を使用しない方法を示しています。
=VLOOKUP(検索キー, IMPORTRANGE(スプレッドシートの URL, 範囲の文字列), 番号, [並べ替え済み])
この例では、IMPORTRANGE は LOOKUP 関数でネストされています。LOOKUP が実行されるたびに、まず IMPORTRANGE を実行してデータを取得し、次に、取得したデータに対して LOOKUP 関数を実行します。
ヒント: LOOKUP 関数では、関数をネストしないようにします。ネストすると、LOOKUP 関数が実行されるたびに、内部関数が LOOKUP 内で計算を追加で行います。
スプレッドシートの計算速度を上げるには、IF ステートメントを使用して、N/A、#ERROR、REF#、空のセルなどの繰り返しの値をスキップします。
禁止事項:
重要: 次の例は、VLOOKUP を使用しない方法を示しています。
上記の例では、VLOOKUP を使用して、列 A のリストの果物の価格を検索できます。ただし、この果物のリストには空のセルも複数あります。
Google スプレッドシートでは、B3、B7、B9 の VLOOKUP 検索が空白に対するものでも、列 A のすべての参照元検索キーについて B2～B10 の計算を実行します。これらの検索では、有効な結果が返されません。
対処方法:
IF 関数を使用して、目的の条件に基づいてこれらの検索キーをスキップし、果物リストのデータが空白でない場合にのみ VLOOKUP を実行します。
この IF 関数は Google スプレッドシートに、検索キーが空白でない場合は数式を実行し、空白の場合は数式 を計算せずに N/A を出力するように指示します。
一般にどの数式も使うことができ、スキップする値が空白である必要はありません。この技術により意味のない値がある場合に余計な計算を避けることができるため、時間を節約できます。
Google スプレッドシートは、IF 関数で値を明示的に呼び出さない限り、値が意味を持つかどうかを判断できません。
=IF(A2 <> スキップする値, 数式, "N/A")
ヒント: 上記の IF 関数は Google スプレッドシートに、A2 がスキップする値と等しくない場合は数式を実行し、A2 がスキップする値と等しい場合は数式を実行せず、代わりに N/A を出力するように指示します。
VLOOKUP はやや早いですが、INDEX 関数と MATCH 関数を使用すると柔軟性が生まれ、LOOKUP のワークフローをより細かく分割できます。こうすることで、前回の結果を再利用して時間を節約できます。
禁止事項:
たとえば、VLOOKUP がこの表の「Apple」の価格と数量の両方を検索するには、2 つの VLOOKUP 数式を個別に使用する必要があります。
=VLOOKUP("Apple", $A$1:$C$4, 2, FALSE)
この VLOOKUP は、内部で 2 つのステップを実行します。
- 「Apple」の場所を探します。
- 2 列目に移動して「Apple」の価格を検索します。
=VLOOKUP("Apple", $A$1:$C$4, 3, FALSE)
この VLOOKUP は、内部で 2 つのステップを実行します。
- 「Apple」の場所を探します。
- 3 列目に移動して「Apple」の数量を検索します。
上記の 2 つの数式における最初のステップは同じで、リスト内で「Apple」の場所を探します。ただし、VLOOKUP は 1 つの関数で 1 つの出力を持つため、上記の数式の 2 つ目のステップを一緒に実行することはできません。
そのため、同じアイテムに含まれる複数の異なる情報を見つけるために VLOOKUP を実行する際は、最初のステップを 2 回実行する必要があります。この場合は計算時間が長くなります。
対処方法:
MATCH と INDEX を組み合わせて使用すると、時間を節約できます。この方法ではステップ 1 とステップ 2 が分離され、必要に応じてステップ 1 を再利用できます。
1. MATCH 関数を使って「Apple」の場所を探します。
=MATCH("Apple", $A$2:$A$4, FALSE)
「Apple」は範囲内の 1 番目にあるため、この数式では「1」が出力されます。
2. 「Apple」の 2 列目の価格を INDEX で検索します。
=INDEX($A$2:$C$4, MATCH 式を含むセル, 2)
この数式では「$1」が出力されます。
「Apple」の数を検索するには、ステップ 1 を再利用し、MATCH 関数でセルを参照すると、その部分を再計算する必要がなくなります。
3. 「Apple」の 3 列目の数量を INDEX で検索します。
=INDEX($A$2:$C$4, MATCH 式を含むセル, 3)
この例では、INDEX と MATCH により、ステップ 1 を 1 回とステップ 2 を 2 回、合計 3 つの計算ステップで実行します。代わりに VLOOKUP 関数を使用する場合は、2 つの VLOOKUP 関数と 4 つのステップが使用されます。このため、時間がかかりコンピューティング リソースの使用量が増えます。
このように効率的なため、使える VLOOKUP 関数が多くなります。時間を節約するために MATCH の結果を再利用できるケースは他にもたくさんあります。