Excelでマイナスの数値を部分一致で平均を出す方法
実務でデータ分析を行う際、特定の文字列が含まれるマイナスの数値だけを平均化したい場面は多いでしょう。手作業では時間がかかりますが、ExcelやGoogleスプレッドシートを使えば効率的に処理できます。この記事では、部分一致で指定した文字列を持つマイナスの数値のみから平均を出す方法を詳しく解説します。
📊 やり方の比較
| 手法 | メリット | デメリット | 推奨 |
|---|---|---|---|
| 数式(AVERAGE関数とSEARCH関数) | 自動更新される, 部分一致が可能 | 複雑な数式になる可能性がある, 古いExcelでは一部の機能が使えない | ◎ |
| マウス操作(フィルタリング) | 直感的で簡単, データを直接見ながら操作できる | 部分一致は難しい, 元データが変更される可能性がある | 〇 |
fx 使用する数式
Excel / SpreadSheet
=AVERAGE(IF(ISNUMBER(SEARCH("特定の文字列", A1:A10)) * (A1:A10 < 0), A1:A10))💡 仕組みの解説
【数式の仕組み】この数式は、データ範囲から部分一致するマイナスの値だけを抽出し、それらの平均を計算します。まず、SEARCH関数（またはREGEXMATCH）で指定した文字列が含まれているか確認し、次にそのセルがマイナスであることを確認します。これらの条件を満たす場合のみ、AVERAGE関数に値を渡します。元データは削除されず、別の場所に抽出された平均値が出力されます。
具体的な手順
1
SEARCH関数またはREGEXMATCH関数を使用し、指定した文字列と部分一致するセルを特定
2
ISNUMBER関数と論理積演算子（*）を使って、部分一致かつマイナスの値を持つセルだけを選択
3
AVERAGE関数に選択した範囲を渡し、平均を計算
よくある質問
Q Q1. VLOOKUP関数との違いは？
A.
VLOOKUP関数は完全一致を前提としており、部分一致には適していません。SEARCH関数やREGEXMATCH関数を使用することで部分一致が可能です。
Q Q2. スピルエラーとは何ですか？
A.
スピルエラー（#SPILL!）は、新しいExcelのダイナミック配列機能で発生するエラーメッセージです。数式が範囲を超えて結果を表示しようとした場合に表示されます。
Q Q3. スマホ版でも使える？
A.
はい、スマートフォン版のExcelやGoogleスプレッドシートでも同様の操作と数式を使用できます。ただし、画面サイズが小さいため入力が難しい場合があります。