メインコンテンツへスキップ

Kutools for Office — 1つのスイート、5つのツールで、もっと多くのことを成し遂げましょう。

Excelでゼロやエラーを無視して中央値を求める方法は？

Author Sun Last modified

Excelの多くのデータ分析作業において、正確に中央値を算出することは、データセットの中心的傾向を理解するために非常に重要です。しかし、場合によってはデータセットにゼロやエラー値（たとえば #DIV/0!, #N/Aなど）が含まれていることがあり、これが単純な中央値計算を妨げる原因となります。たとえば標準的な数式を使用すると、 =MEDIAN(range) ゼロも計算に含まれてしまい、範囲内に無効なセルがある場合はエラーが返され、誤った結果や計算失敗につながる可能性があります（以下例参照）。
A screenshot showing when calculating the median with zeros and errors included in the data range is needed

こうした問題に対応するために、ゼロやエラーを除外して中央値を計算するためのいくつかの解決法があります。これらの方法を使えば、分析が正確かつ信頼性の高いものとなります。調査データのクリーニングや財務レポート、あるいは科学的測定など、ゼロやエラーを除外すべき様々なケースに適しています。下記では、Excelで利用できるそれぞれの方法について、直接数式を使うものから高度な自動化テクニックまで、実践的な手順を丁寧にご紹介します。

中央値 ゼロを無視

中央値 エラーを無視

VBA：ゼロとエラーを無視して中央値（UDF）

Power Query：ゼロ／エラーを除外後に中央値を算出

arrow blue right bubble 中央値 ゼロを無視

もし中央値の計算でゼロ（例えば0で不足値を表している等）を含めたくない場合、配列数式を利用してゼロを除外できます。この方法はゼロがデータがないことを示すプレースホルダーとなっているデータセットに特に有効です。

中央値を表示したいセル（例：C2）を選択し、以下の数式を入力します。

=MEDIAN(IF(A2:A17<>0,A2:A17))

数式を入力後、単にEnterキーを押すのではなく、 Ctrl + Shift + Enter を同時に押して配列数式として確定します（数式バーで数式が波括弧で囲まれます）。これにより、 A2:A17 のうちゼロ以外の値のみが中央値計算の対象となります。スクリーンショット参照：
A screenshot showing how to apply the median formula in Excel while ignoring zeros

ヒント：

  • Excel365 または Excel2021以降を使用している場合は、動的配列に対応しているためEnterキーだけで十分です。
  • 範囲内にゼロ以外の数値が1つもない場合、この数式は#NUM!エラーを返しますのでご注意ください。
  • この方法はアンケート回答や支出報告、売上データなど、ゼロを分析から除外したい場合に最適です。

arrow blue right bubble 中央値 エラーを無視

#N/A、#DIV/0!、#VALUE!などのエラー値があると標準の中央値関数はエラーを返し、データ分析が中断されます。これらのエラーを除外して安全に中央値を計算するには、次の配列数式をご利用ください。

結果を表示したいお好きなセルを選んで、下記の数式を入力します：

=MEDIAN(IF(ISNUMBER(F2:F17),F2:F17))

数式を入力後、 Ctrl + Shift + Enter を押してください（Excel365/2021以降の場合は動的配列対応のためEnterのみでOKです）。この数式では F2:F17 のうち純粋な数値のみが考慮され、エラーセルは完全に無視されます。
A screenshot showing how to apply the median formula in Excel while ignoring errors

ヒントおよび注意事項：

  • すべてのセルがエラー値の場合、結果は#NUM!エラーとなるため、少なくとも1つの有効な数値が含まれていることをご確認ください。
  • 除外条件はネストして組み合わせることもできます（たとえばゼロとエラーの両方を除外する等）。
  • この数式はインポートデータや調査結果、計算が不完全な財務諸表の処理に特に役立ちます。

arrow blue right bubble VBA：ゼロとエラーを無視して中央値（UDF）

頻繁にゼロやエラーを無視して中央値を求める場合や、配列数式を手動で入力したくない場合は、カスタムのVBA関数（ユーザー定義関数／UDF）を利用できます。この方法なら、すべての除外条件を関数内で完結でき、標準関数と同様に使えるため、大規模や頻繁に更新されるデータセットにも適しています。

UDFの設定方法：

  1. Excelで[Developer]タブをクリックします。表示されていない場合は、[File > Options > Customize Ribbon]から有効化してください。
  2. [Visual Basic]をクリックしてVBAエディタを開きます。
  3. VBAエディタで[Insert > Module]を選択し、新しいモジュールを作成します。
  4. 以下のコードをモジュールに貼り付けてください。
Function MedianIgnoreZeroError(rng As Range) As Variant
    Dim cell As Range
    Dim tempList() As Double
    Dim count As Integer
    
    count = 0
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    For Each cell In rng
        If IsNumeric(cell.Value) Then
            If cell.Value <> 0 And Not IsError(cell.Value) Then
                count = count + 1
                ReDim Preserve tempList(1 To count)
                tempList(count) = cell.Value
            End If
        End If
    Next cell
    
    On Error GoTo 0
    
    If count = 0 Then
        MedianIgnoreZeroError = CVErr(xlErrNum)
    Else
        MedianIgnoreZeroError = Application.WorksheetFunction.Median(tempList)
    End If
End Function

UDFの利用方法：
Excelに戻り、任意のセルに以下の数式を入力します。 =MedianIgnoreZeroError(A2:A17) （ A2:A17 は計算対象の範囲に置き換えてください）。配列数式と異なり、Enterだけで完了し、 Ctrl + Shift + Enter.

  • の操作は必要ありません。この方法は非常に大きなデータセットにも有効で、配列数式のクセを避けやすく、コードを編集して他の除外条件を追加することもできます。
  • 範囲内がゼロまたはエラーのみの場合、結果は#NUM!になります。
  • #NAME?エラーが表示された場合、VBAマクロが正しくインストールされているか、Excelの設定でマクロが有効になっているかご確認ください。

arrow blue right bubble Power Query：ゼロ／エラーを除外後に中央値を算出

Power QueryはExcelにおける強力なインポート・変換・分析ツールです。中央値などの計算前に大規模なデータセットをクリーンアップしたい場合に特に有効です。Power Queryなら簡単にゼロ値やエラーをフィルターで除外でき、計算対象を数値のみに限定できます。元データが外部システムから定期的に更新・インポートされる場合にも最適です。

ゼロとエラーを無視して中央値を求めるためにPower Queryを使う手順：

  1. データ範囲内の任意のセルを選択し、[Data]タブで[From Table/Range]をクリックします。データがテーブル形式でない場合はExcelが自動でテーブル作成を促すので、[OK]を押します。
  2. Power Queryエディタウィンドウが開きます。対象列のドロップダウン矢印をクリックして0のチェックを外し、ゼロ値を除外します（エラーを除外する場合は列ヘッダーを右クリックして[Remove Errors]を選択してください）。
  3. フィルター後、[Home > Close & Load]をクリックして、クリーン化したデータをワークシートに戻します。
  4. その後、フィルター済みの列に対して標準の=MEDIAN()数式を適用してください。これで不要な項目を除いたデータに対してのみ中央値を算出できます。

この方法では元データに手を加えることなく、新規や更新データにも対応しやすく、定期報告や大規模・外部データ処理にも特に効果的です。Power Queryの処理フローは、ソースデータに変更があった際もワンクリックで更新でき、手作業とエラーリスクを最小限に抑えます。

  • Power QueryはExcel2016以降で標準搭載されています（またはExcel2010・2013用のアドインとして利用可能）。
  • 変換後のクリーンなデータを使って計算を行うことで、後続の分析の信頼性が向上します。

予想外の結果が出る場合は、Power Query側のフィルター工程やクリーンデータ内に有効な数値が残っているかを再確認しましょう。

まとめ：配列数式を直接使う方法、UDFで自動化する方法、Power Queryでワークフローの自動処理を活用する方法など、Excelにはゼロやエラーを無視して中央値を求めるための実用的な手段が複数用意されています。データセットの規模や更新頻度、作業フローに応じて最適な方法を選び、信頼性の高い分析結果を得てください。

最高のオフィス業務効率化ツール

🤖 Kutools AI Aide：データ分析を革新します。主な機能：Intelligent Execution｜コード生成｜カスタム数式の作成｜データの分析とグラフの生成｜Kutools Functionsの呼び出し……
人気の機能：重複の検索・ハイライト・重複をマーキング｜空白行を削除｜データを失わずに列またはセルを統合｜丸める……
スーパーLOOKUP：複数条件でのVLookup｜複数値でのVLookup｜複数シートの検索｜ファジーマッチ……
高度なドロップダウンリスト：ドロップダウンリストを素早く作成｜連動ドロップダウンリスト｜複数選択ドロップダウンリスト……
列マネージャー：指定した数の列を追加 ｜列の移動 ｜非表示列の表示/非表示の切替｜ 範囲＆列の比較……
注目の機能：グリッドフォーカス｜デザインビュー｜強化された数式バー｜ワークブック＆ワークシートの管理｜オートテキスト ライブラリ｜日付ピッカー｜データの統合 ｜セルの暗号化/復号化｜リストで電子メールを送信｜スーパーフィルター｜特殊フィルタ（太字/斜体/取り消し線などをフィルター）……
トップ15ツールセット：12 種類のテキストツール（テキストの追加、特定の文字を削除など）｜50種類以上のグラフ（ガントチャートなど）｜40種類以上の便利な数式（誕生日に基づいて年齢を計算するなど）｜19 種類の挿入ツール（QRコードの挿入、パスから画像の挿入など）｜12 種類の変換ツール（単語に変換する、通貨変換など）｜7種の統合＆分割ツール（高度な行のマージ、セルの分割など）｜… その他多数
Kutoolsはお好みの言語で利用可能 ― 英語、スペイン語、ドイツ語、フランス語、中国語、その他40以上の言語に対応！

Kutools for ExcelでExcelスキルを強化し、これまでにない効率を体感しましょう。 Kutools for Excelは300以上の高度な機能で生産性向上と保存時間を実現します。最も必要な機能はこちらをクリック...

Office TabでOfficeにタブインターフェースを追加し、作業をもっと簡単に

  • Word、Excel、PowerPointでタブによる編集・閲覧を実現。
  • 新しいウィンドウを開かず、同じウィンドウの新しいタブで複数のドキュメントを開いたり作成できます。
  • 生産性が50%向上し、毎日のマウスクリック数を何百回も削減！

全てのKutoolsアドインを一つのインストーラーで

Kutools for Officeスイートは、Excel、Word、Outlook、PowerPoint用アドインとOffice Tab Proをまとめて提供。Officeアプリを横断して働くチームに最適です。

Excel Word Outlook Tabs PowerPoint
  • オールインワンスイート — Excel、Word、Outlook、PowerPoint用アドインとOffice Tab Proが含まれます
  • 1つのインストーラー・1つのライセンス —— 数分でセットアップ完了（MSI対応）
  • 一括管理でより効率的 —— Officeアプリ間で快適な生産性を発揮
  • 30日間フル機能お試し —— 登録やクレジットカード不要
  • コストパフォーマンス最適 —— 個別購入よりお得