Excelを使う人なら、ほとんど誰でも、大学時代から使い慣れた関数を持っているはずです。それは、新しい関数を覚える必要がないほど使い勝手が良いからです。だからこそ、2026年になっても、全く新しいファイルの中にVLOOKUPやCONCATENATEが見つかるのです。私もその気持ちはよく分かります。というのも、今でもVLOOKUPを使うことに抵抗があるからです。 SUBTOTAL を放棄して AGGREGATE を使用する 長い間応援して頂きました。
しかし、Excelは進化を遂げており、何年も前に学んだ数式の多くは、今では本来あるべき姿よりも複雑になり、堅牢性が低下し、メンテナンスが難しくなっています。数式が当時と変わらず、スプレッドシートが重く感じられる場合は、アップデートが必要なサインかもしれません。
クイックリンク
XLOOKUPはVLOOKUPとHLOOKUPを完全に置き換えます
あらゆる方向に機能する単一の関数
VLOOKUPとHLOOKUPは、Excelにおける検索関数の先駆けと言えるでしょう。使いやすく、馴染み深く、そして広く使われています。しかし、問題は、スプレッドシートが大きくなるにつれて、これらの関数が柔軟性に欠け、ますます使いにくくなることです。どちらの関数もコード化された列番号や行番号に依存しているため、表の途中に列を挿入するといった単純な操作でさえ、結果が崩れてしまう可能性があります。
=VLOOKUP(検索値, 配列, 列番号, [範囲検索]) =HLOOKUP(検索値, 配列, 行番号, [範囲検索])
さらに、VLOOKUPはキー列の左側を検索できないため、基本的な検索を実行するだけでもデータの順序を変更したり、補助列を追加したりする必要があります。XLOOKUPはこれらすべてを一度に解決します。大きな表を指定して列や行を数える代わりに、Excelに検索する場所と返してほしい結果を正確に指示します。
= XLOOKUP（lookup_value、lookup_array、return_array、[if_not_found]、[match_mode]、[search_mode]）
このアプローチにより、誰かが列を追加したり、テーブル内のフィールドを並べ替えたりしても、数式が壊れることはありません。XLOOKUP は左から右、右から左、上から下へと処理されるため、データが従来の整然とした参照テーブルに配置されていない場合でも、より柔軟に使用できます。
もう一つの重要なアップグレードは、XLOOKUPにif_not_found引数が追加されたことです。これにより、見苦しい#N/Aメッセージを回避するためにIFERRORを使用する必要がなくなりました。次の2つの構文がその違いを示しています。
=IFERROR(VLOOKUP(B2, C2:E7, 4, TRUE)" ") =XLOOKUP(B2, C2:E7, D2:D7, "見つかりません")
最初の数式は、範囲C2:E7の1列目でB2を検索し、4列目を返します。一方、2番目の数式は範囲C2:E7でB2を検索し、D2:D7から一致する値を返します。XLOOKUPはデフォルトで完全一致を使用するため、並べ替えられていないリストから誤って近似値を取得するリスクが軽減されます。また、IFERRORにすべての検索を含める必要がなく、欠損値も処理されます。
XMATCHはMATCHのより効率的なバージョンです
マッチタイプと検索トレンドを正確に制御
おそらく、あなたはまだMATCHを筋肉の記憶のために使っているのでしょう。MATCHは、レンジ内で価値の高いポジションを見つけるのに非常に優れており、特に次のような場合に優れています。 MATCHはINDEXと組み合わせられる問題は、MATCH関数が誤用されやすいことです。正確な一致タイプを指定し忘れると、Excelは実際にはデータに当てはまらない並べ替えの仮定に基づいて、最も近い結果を表示してしまう可能性があります。
=MATCH(検索値, 検索配列, [一致タイプ])
この種のエラーは一見妥当な結果を生成することが多いため、検出と修正が困難な場合があります。XMATCHを使用すると、数式はデフォルトで完全一致となり、ほとんどのケースでほとんどの人が望む結果が得られます。
=XMATCH(検索値, 検索配列, [一致モード], [検索モード])
書いている場合 =MATCH(25, A1:A3, 0) 習慣的に切り替えて、 =XMATCH(25, A1:A3, 1) この改善点をぜひご活用ください。XMATCHでは、より安全なデフォルト設定に加え、検索方向も制御できます。リストの先頭だけでなく末尾からも検索できるため、補助列を追加せずに最後のエントリや値の最後の出現箇所を検索したい場合に特に便利です。
=XMATCH(E3, C3:C100, 0, -1)
もちろん、MATCH はまだ使用可能ですが、この時点では、より効率的なオプションがすぐ隣にあるのに、単純に機能するものを選択するようなものです。
動的な FILTER、UNIQUE、SUM の各数式が SUMPRODUCT の画期的な機能に取って代わります。
読みやすく、修正しやすく、長期にわたって維持しやすい
SUMPRODUCTを使用すると、複数の条件による計算、加重合計、行列ベースのロジックを1つの数式で処理できます。この柔軟性こそが、長年にわたり人気の代替ソリューションとなっている理由です。欠点は、SUMPRODUCTの数式はコードのように見えることが多く、読みにくく、デバッグが難しく、長期にわたるメンテナンスが困難になることです。しかし、SUMPRODUCTは Excel の動的配列のネイティブサポート以前 SUMPRODUCT を使用していた多くの作業では、このような難解でわかりにくい数式は必要なくなりまし た。
最新のExcelでは動的配列が導入され、SUMなどの標準関数で配列の計算を直接処理できるようになりました。2つの方法を並べて比較すると、新しいアプローチの方が明確で理解しやすいことがよくあります。
| 計算 | SUMPRODUCT | 和 |
|---|---|---|
| 単純な加算 | =SUMPRODUCT($A$20:$A$50) | =SUM($A$20:$A$50) |
| 古典的な加重組み合わせ | =SUMPRODUCT(A2A5, C2:C5) | =SUM(A2:A5 * C2:C5) |
| 複数基準の集約 | =SUMPRODUCT((A2:A9=”East”)*(B2:B9=”Cherries”)*C2:C9) | =SUM((A2:A9=”East”)*(B2:B9=”Cherries”)*C2:C9) |
さらに、FILTERやUNIQUEなどの新しい関数を使えば、長くて読みにくい1つの数式を作成する代わりに、問題をより小さく読みやすいステップに分割できます。関心のあるデータをフィルタリングし、必要に応じて一意の値を抽出し、結果を要約することができます。ロジックは同じままですが、数式の目的は、後で読んだりメンテナンスしたりする人にとってはるかに明確になります。
たとえば、列 B に売上リストがあり、列 A に商品名があり、「リンゴ」などの特定の商品の売上を収集したい場合は、次の 2 つの数式のいずれかを記述できます。
=SUMPRODUCT((A2:A10="Apples")*(B2:B10)) =SUM(FILTER(B2:B10, A2:A10="Apples", 0))
SUMPRODUCT版では、Excelは「Apples」の有無を確認するためのTRUE値とFALSE値の配列を作成し、それらを1と0に変換して売上高と掛け合わせます。「Apple」以外の値は0に変換され、最終的な合計から除外されます。2つ目の数式では、FILTER関数は単に「Apples」を含む売上高を返し、そのリストにSUM関数を追加します。結果は同じですが、2つ目のアプローチでは、数式の目的が一目で理解しやすくなります。
SUMPRODUCT関数は、実験時に配列1と配列2の構造を簡単に変更できるため、便利だと感じるかもしれません。しかし、数十万行、あるいは数百万行にも及ぶ大規模なデータセットの場合、より入手しやすい最新の代替関数に比べて大幅に遅くなる可能性があります。パフォーマンスの向上に加え、新しい関数はより簡潔な数式を提供するため、校正が容易になり、次にスプレッドシートを開く人にとっても違和感が少なくなります。
IFSとSWITCHはネストされたIF形式を排除します
無限の括弧のない、よりクリーンなロジック
技術的には、ネストされたIF文は機能しますが、すぐに悪夢のような状況に陥ります。特に自分で記述していない場合は、読みにくかったり保守が困難になったりします。IFSでは、IF文の中にIF文をネストするのではなく、条件と結果を順番に並べることで、式がよりシンプルなロジックのように見えます。「もしこれならあれ、もしこれが他の何かなら他の何か」といった具合です。SWITCHはさらに一歩進んで、単一の値を複数の固定確率と比較します。より簡潔で読みやすく、デフォルトオプションも用意されているため、最後に追加のエラー処理を追加する必要がありません。
=IF(テスト1, 結果1, IF(テスト2, 結果2, IF(テスト3, 結果3, ...))) =IFS(論理テスト1, 真の場合の値1, 論理テスト2, 真の場合の値2, ...) =SWITCH(式, 値1, 結果1, 値2, 結果2, ... [デフォルト])
たとえば、結果にスコアを割り当てる場合は、ネストされた IF ステートメントを使用できますが、IFS では同じロジックをより明確に表現できます。
=IF(A1>90, “A”, IF(A1>80, “B”, IF(A1>70, “C”, “F”))) =IFS(A1>90, “A”, A1>80, “B”, A1>70, “C”, TRUE, “F”)
IFS 式の末尾に TRUE を指定すると、グローバル条件として機能し、先行する条件がいずれも満たされない場合に Excel に何を返すかを指定します。この場合、70 未満の結果は、別の IF ネストを必要とせずに「F」に切り捨てられます。SWITCH 式は、セクションに数値コードを割り当てるなど、若干異なるパターンを処理し、末尾にデフォルト引数を指定するため、TRUE を返す必要はありません。
=SWITCH(A1, 101, “営業”, 102, “マーケティング”, “その他”)
セルA1に101が含まれている場合、Excelは「売上」という値を返します。102が含まれている場合、Excelは「マーケティング」という値を返します。それ以外の値が含まれている場合、Excelは「その他」という値を返します。ケースリストが増えても、このロジックは明確です。
TEXTJOINとCONCATはCONCATENATEを完全に置き換えます
個々のセルだけでなく、範囲を結合します。
CONCATENATE はもはや博物館の展示品と化しています。多くのスプレッドシートではまだ見かけますが、正式に置き換えられました。それにはちゃんとした理由があります。各セルを手動で参照する必要があるため、全範囲を対象とする処理には適しておらず、区切り文字という概念も組み込まれていません。
最新の代替手段である CONCAT は、ほぼ同じように動作しますが、完全なドメイン参照のサポートが追加されています。
=CONCAT(テキスト1, ...)
各セルを手動で選択する代わりに、次のような数式を入力して、残りの処理を Excel に任せることができます。
=CONCAT(B2:C8)
カンマやスペースなどの区切り文字を指定して、Excel に空のセルを無視するように指示する場合、TEXTJOIN は最適なツールです。
=TEXTJOIN(区切り文字, 空文字を無視, テキスト1, ...) =TEXTJOIN(", ", TRUE, B2:C8)最初の引数は区切り文字で、任意の値を指定できます。2番目の引数は、空白セルをスキップするかどうかをExcelに指示します。これは、読みやすいリストを作成したり、SQLクエリを作成したり、列やデータ範囲からリッチテキストをコンパイルしたりする場合などに特に便利です。
古いExcelの習慣を捨てる時が来た
幸いなことに、これらのアップグレードではExcelを一から学び直す必要はありません。ほとんどの場合、新しい関数は置き換えられる関数よりも簡単です。ただ、慣れるのに少し時間がかかるだけです。
今週、習慣を一つだけ変えるのであれば、まずは最もよく使う関数から始めて、それを最新の同等の関数に置き換えてみましょう。そして、ワークブックの一部をこれらの5つの置き換えを使って書き直してみて、数式がどれだけシンプルになるかを確認してみてください。
