Excel 関数でよくある 9 つの間違いとその修正方法

 

Excel にはさまざまな強力な関数が用意されていますが、それらを定期的に使用している場合でも、最も一般的な間違いは細かい点から発生します。たとえば、間違った一致モードを使用したり、値の代わりに書式設定されたテキストを比較したり、セルの書式設定によって基礎となる数値も四捨五入されると想定したりすることです。

このチュートリアルでは、頻繁に誤って使用される 9 つの一般的な Excel 関数について説明し、それらを修正する方法を示します。

1. VLOOKUP – あらゆる用途で VLOOKUP を使用するのはやめましょう

間違った方法: 多くのユーザーは、他の関数の方が適切な場合でも、すべてを VLOOKUP に依存しています。 VLOOKUP は右側のみを検索し、近似一致するには並べ替えられたデータを必要とし、列がソース データに挿入されるときに中断される可能性があります。デフォルトでは、VLOOKUP は近似一致 (TRUE) を使用します。これには最初の列がソートされている必要があり、データが適切にソートされていない場合は、多くの場合間違った項目が返されます。

修正: Microsoft 365 では完全一致の XLOOKUP を使用するか、古いバージョンの Excel を使用している場合は INDEX+MATCH を使用します。

柔軟性を高めるには INDEX/MATCH を使用します:

• セルを選択し、次の数式を挿入して特定の商品の価格を調べます。

=INDEX(G2:G101, MATCH(L3, E2:E101, 0))

これは、INDEX 内の任意の戻り列を選択することで「左検索」も処理します。

Excel 365 ユーザーの場合は、XLOOKUP を検討してください:

• セルを選択し、次の数式を挿入します。

=XLOOKUP(L3, E2:E101, G2:G101, "Not found", 0)

0 は完全一致を強制し、4 番目の引数はわかりやすい「見つかりません」メッセージを表示します。

• どの方向（左または右）でも機能します。
• 列の挿入時に中断されません。
• 大規模なデータセットの場合はより効率的です。
• ロジックの流れがより明確になります。

2. SUMIF/SUMIFS – 基準の書き方が間違っている

間違った方法: 演算子を連結したり、criteria_range 引数と sum_range 引数を混同したり、A:A のような列全体参照を使用したりする代わりに、演算子を条件文字列に直接書き込むと (例:プレーン テキストとして「>=2025-03-01」)、計算が遅くなる可能性があります。

修正: 範囲を揃えて、連結によって演算子の基準を構築します。堅牢な日付フィルタには DATE/EOMONTH を使用します。

• 実際のデータを含む特定の範囲を使用する方が高速で正確なので

=SUMIF(D2:D101,"East",H2:H101)

• 西部地域の 2025 年 3 月の総売上高:

=SUMIFS(
H2:H101,
D2:D101, "West",
A2:A101, ">=" & DATE(2025,3,1),
A2:A101, "<=" & EOMONTH(DATE(2025,3,1),0)
)

これにより、ロケールの予期せぬ問題やテキストの日付の問題が回避されます。 sum_range と各 criteria_range が同じサイズであることを常に確認してください。

プロのヒント: 自動範囲拡張には Excel テーブルと構造化参照を使用します。

3. IF ステートメント – ネストされた悪夢

間違った方法: ブール関数を不必要に TRUE と比較する (例:=IF(AND(E4=”Y”,F4=”Y”)=TRUE, …))、または単純なカテゴリ マップに対して深くネストされた IF ステートメントを作成する。これは読み取りと保守が困難です。

修正: AND/OR 関数がブール値を直接返すようにします。複数の条件の場合は、IFS、CHOOSE/MATCH、または SWITCH を使用すると、よりクリーンで管理しやすい数式が得られます。

• クリーンなブール値テスト:

=IF(AND(D2="East", F2>=3), "Bulk East", "Other")

• IFS を使用した複数条件スコア:

=IFS(F2:F101>=5, "High", F2:F101>=2, "Medium", TRUE, "Low")

• CHOOSE/MATCH を備えたきちんとしたカテゴリ マップ（例:J2 の成績を GPA ポイントに変換）:

=CHOOSE(MATCH(J2, {"A","B","C","D","F"}, 0), 4,3,2,1,0)

これは、深い IF ピラミッドよりも単純で、エラーが発生しにくくなります。

4.連結 – 時代遅れのアプローチ

間違った方法: 複雑なテキストを結合するために、古い CONCATENATE 関数を依然として使用したり、複数のアンパサンドを連鎖したりすることは、煩雑でエラーが発生しやすい可能性があります。

修正: 最新バージョンの Excel では TEXTJOIN を使用すると、指定された区切り文字を使用して複数の値を効率的に結合できます。

• 複数の値には区切り文字を使用して TEXTJOIN を使用します。

=IF(L2="","", TEXTJOIN(", ", TRUE, FILTER(E$2:E$101, C$2:C$101=L2)))

単純な連結には &演算子を使用しますが、TEXTJOIN は次のような場合に役立ちます。

• 空白セルを自動的にスキップします。
• 全体で同じ区切り文字を使用します。
• セル範囲を結合します。

5. COUNTIF – 複数の基準による効率の無視

間違った方法: リテラル演算子を連結する代わりに COUNTIF をリテラル演算子とともに使用したり、大文字と小文字を区別する結果を誤って期待したり、ワイルドカードを引用符で囲む必要があることを忘れたりします。もう 1 つのよくある間違いは、複数の基準に対してより効率的な COUNTIFS を使用せずに、複数の COUNTIF 関数を合計することです。

修正: 演算子を正しく連結してください。大文字と小文字を区別したり、より複雑な「含む」ロジックが必要な場合は、SUMPRODUCT または FILTER に切り替えてください。

• US-E で始まるコードを数える:

=COUNTIF(J2:J101, "US-E*")

• 商品に「電話」という単語が含まれる注文をカウントします (大文字と小文字は区別されません):

=SUMPRODUCT(--ISNUMBER(SEARCH("phone", E2:E101)))

大文字と小文字を区別する「含む」が必要な場合は、SEARCH を FIND に置き換えます。

6. ROUND – 間違ったタイミングでの丸め

間違った方法: セルを小数点以下 2 桁に書式設定すると、計算の基になる値も丸められると仮定します。書式設定では外観が変更されるだけで、保存されている値は変更されないため、合計の不一致が生じる可能性があります。

修正: ビジネス ルールで必要なステップで丸めます。増分には ROUND、ROUNDUP、ROUNDDOWN、または MROUND を使用します。

• 四捨五入された行の金額:

=ROUND(F2:F101*G2:G101, 2)

• 最も近い 0.05 に四捨五入します (現金価格の場合に一般的):

重要な原則: 表示用に結果を丸めますが、特に必要でない限り、中間計算の精度は維持されます。

7.計算に使用されるテキストと日付の書式設定

間違った方法: 表示目的で値をテキストに変換し、そのテキストベースの結果を数学的計算に使用したり、書式設定された日付文字列を実際の日付値と比較したりする。これには、多くの場合、日付をテキストとして扱い、専用の日付関数の代わりに複雑なテキスト操作を使用することが含まれます。

修正: 生の数値に対して計算を実行します。 TEXT 関数は、グラフのタイトルやレポートのラベルなど、プレゼンテーションの最終段階でのみ使用してください。

• 実際の日付値を含む適切な日付関数を使用する:

=YEAR(A1)
=MONTH(A1)
=DAY(A1)

• 数値を損なうことなく月と合計を表示するダッシュボードのタイトル:

="March " & YEAR(DATE(2025,3,1)) & " Sales: " &
TEXT(SUMIFS(H$2:H$101, A$2:A$101, ">="&DATE(2025,3,1), A$2:A$101, "<="&EOMONTH(DATE(2025,3,1),0)),"$#,##0")

• 日付を使用した堅牢な月フィルター (テキストは不要):

=SUMIFS(H$2:H$101, D$2:D$101, "East",
A$2:A$101, ">="&DATE(2025,3,1),
A$2:A$101, "<="&EOMONTH(DATE(2025,3,1),0))

8. SUMPRODUCT – 強制を忘れた場合、または FILTER がより明確な場合

間違った方法: ブール値 (TRUE/FALSE) 配列を数値 (1/0) に強制変換するのを忘れたり、サイズが一致しない配列を構築したりします。逆に、最新の Excel では単純な SUM(FILTER(…)) の組み合わせを使用した方が読みやすい場合は、複雑な SUMPRODUCT 式を使用します。

修正: 二重単項を使用するか、1 を乗算して TRUE/FALSE を 1/0 に強制します。 Microsoft 365 では、複数基準の合計には透過的な SUM および FILTER パターンを推奨します。

• 東部地域の総売上高、「電話」を含む商品、およびレガシー対応の数量が 3 つ以上:

=SUMPRODUCT((D$2:D$101="East") * ISNUMBER(SEARCH("phone", E$2:E$101)) * (F$2:F$101>=3) * H$2:H$101)

• 動的配列 (365/2021) と同じロジック:

=SUM(FILTER(H$2:H$101, (D$2:D$101="East")*(ISNUMBER(SEARCH("phone", E$2:E$101)))*(F$2:F$101>=3)) )

FILTER を使用すると、条件が 1/0 ゲートとして乗算され、結果が読み取れる状態になります。

9. IFERROR をブランケットバンドエイドとして使用

間違った方法: すべてのエラーを抑制するために、大規模で複雑な数式を IFERROR(…,””) でラップします。これは、範囲名のスペルミスや本物の #DIV/0 などの実際の問題を隠す可能性があるため危険です。エラー、または注意すべきその他の論理的欠陥。

修正: 予期される特定のエラーのみをキャッチします。値が見つからない場合は検索関数に IFNA を使用し、計算を実行する前に IF ステートメントを使用して空白セルを処理します。

• ルックアップキーが空白の場合は空白を表示します。それ以外の場合は、本当に見つからない場合にのみ、わかりやすい「見つかりません」メッセージを表示します。

=IF(L2="","", IFNA(XLOOKUP(L2, E$2:E$101, G$2:G$101),"No match"))

空白:

一致しません:

• 変換対象がある場合にのみ、テキストを数値に変換します。

=IF(A2="", "", VALUE(A2))

これにより、正当なゼロが保持され、無関係なエラーがマスクされることが回避されます。

ベスト プラクティスの概要

• 適切なツールを選択します: より良い代替手段が存在する場合は、使い慣れた関数をデフォルトにしないでください。
• 範囲は具体的に指定してください: 必要な場合を除き、列全体の参照は避けてください。
• 保守性について考える: 他の人（将来のあなたを含む）が理解できる式を書く
• 適切なデータ型を使用する: 日付は日付として、数値は数値として扱います。
• エッジケースをテストする: 空白セル、エラー、予期しないデータで何が起こるかを検討してください。
• Excel テーブルを活用する: ダイナミック レンジには構造化参照を使用します。
• 最新の状態を維持する: 複雑な従来の数式を置き換えることができる Excel 365 の新しい関数を学習します。

結論

これらのよくある間違いを回避することで、より効率的で読みやすく、信頼性の高い Excel スプレッドシートを作成できます。重要な点は、ジョブに適切な関数を選択すること、基準と範囲を正確に指定すること、データ型を分離すること、つまり数値の計算を実行し、書式設定関数を最終的なプレゼンテーションにのみ使用することです。古いチュートリアルから学んだ古い習慣を打ち破ると、数式がエラーになりにくくなるだけでなく、作業の保守と理解が容易になります。

ソリューション付きの高度な Excel 演習を無料で入手しましょう!