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

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

複数の条件がある場合、Excelで中央値を計算するにはどうすればよいですか?

Author Sun Last modified

Excelでのデータセットの中央値の計算は、データ分析やレポート作成において頻繁に必要とされる操作です。単純な範囲の中央値を見つけることは標準的なExcel関数を使用して迅速に行えますが、特定の複数の基準を満たすデータからのみ中央値が必要になる状況もよくあります。例えば、大量のデータの中で特定の日付における特定の製品の中央販売額を見つける場合などです。このような複雑で条件付きの操作を従来の関数だけで処理することは困難です。このチュートリアルでは、Excelで複数の条件を持つ中央値を計算するためのさまざまな実用的なソリューションを紹介し、数式ベースのアプローチと高度なニーズに対応するVBAを使用した自動化の両方を探ります。


複数の条件を満たす場合の中央値を計算

以下のようにデータ範囲があると仮定し、あなたのタスクは2つの基準を満たす中央値を決定することです。例えば、列Aに「a」の値があり、列Cに「1月2日」の日付がある場合の列Bの中央値を決定します。このシナリオは特に、営業報告書、クラス試験結果、およびその他のビジネスまたは学術データ分析で複数のカテゴリ別にフィルタリングする必要がある場合によく見られます。

a screenshot of the original data

明確にするために、次のようにワークシートを準備しましょう: Excelシートで、下の画像と同様のレイアウトで条件を入力してください。ここで、列Eには列Aの基準がリストされ、列F以降の行1には列Cの日付基準が表示されます。

a screenshot of typing new required data

複数の基準を満たす中央値を計算するには、MEDIANIF関数を利用して条件に基づいてフィルタリングされた値のリストを作成する配列数式を使用できます。以下の手順で行います:

1. 中央値の結果を表示させたいセルF2をクリックし、次の数式を入力します:

=MEDIAN(IF($A$2:$A$12=$E2,IF($C$2:$C$12=F$1,$B$2:$B$12)))

この数式は、各列について、列Aの値がE2の条件と一致し、列Cの値がF1のヘッダーと一致しているかどうかを確認します。両方の条件が満たされている場合、中央値の計算のために列Bの値を集計します。

2. 数式を入力後、配列数式であるため、EnterではなくCtrl + Shift + Enterを押します。 Excelは数式を波括弧 { } で囲んで配列数式であることを示します。

3. F2の右下隅にある塗りつぶしハンドルをドラッグして、異なる条件下で中央値が必要な他の関連セルに数式をコピーします。以下をご参照ください:

a screenshot of using the formula

パラメーターの説明と使用のヒント: この数式では、$A$2:$A$12は最初の条件(例えば商品名)を含む範囲、$C$2:$C$12は2番目の条件(例えば日付)の範囲、$B$2:$B$12は中央値を求めたい数値が含まれる範囲です。自分のワークシートに合わせてこれらの範囲を調整してください。数式をコピーする際に範囲がずれないように絶対参照($記号)を使用してください。

注意点: 両方の条件を満たす値がない場合、数式は#NUM!エラーを返します。混乱を避けるために、数式をIFERRORでネストして空白またはカスタムメッセージを返すことができます:

=IFERROR(MEDIAN(IF($A$2:$A$12=$E2,IF($C$2:$C$12=F$1,$B$2:$B$12))),"No match")

中央値の列に空のセルや非数値文字が含まれていないか確認してください。これにより結果に影響を与える可能性があります。

この数式ベースのアプローチは、比較的簡単な条件(通常は2つか3つの基準まで)がある場合に適しています。設定が簡単でプログラミングスキルは必要ありません。しかし、動的条件や大規模なデータセットでの複雑なフィルタリングの場合、配列数式の維持や編集が煩雑になることがあります。


VBAコード - 複数の条件で中央値を計算

多くの条件がある場合、大規模なデータセット、または基準自体が頻繁に変わる場合などの条件付き中央値の計算を自動化する必要がある場合は、VBAソリューションが実用的な代替手段を提供できます。VBAを使用すると、任意の数の条件に基づいて中央値を計算する再利用可能なマクロを作成できます。VBAベースのソリューションは、繰り返しの分析を合理化したり、レポートやダッシュボード用のカスタムExcelプロセスを開発したい場合に特に役立ちます。

条件付き中央値計算にVBAを使用するには、次の手順に従ってください:

1. 開発ツール > Visual Basic をクリックします。新しいMicrosoft Visual Basic for Applicationsウィンドウが開きます。挿入 > モジュールをクリックし、次のコードをモジュールに貼り付けます:

Sub ConditionalMedian()
    Dim DataRange As Range
    Dim CriteriaRange1 As Range
    Dim CriteriaRange2 As Range
    Dim OutputRange As Range
    Dim Criteria1 As Variant
    Dim Criteria2 As Variant
    Dim TempArr() As Double
    Dim i As Long
    Dim j As Long
    Dim count As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set DataRange = Application.InputBox("Select the range containing median values (e.g., B2:B12):", xTitleId, "", Type:=8)
    Set CriteriaRange1 = Application.InputBox("Select the first criteria range (e.g., A2:A12):", xTitleId, "", Type:=8)
    Criteria1 = Application.InputBox("Enter the first criteria value (e.g., a):", xTitleId, "", Type:=2)
    Set CriteriaRange2 = Application.InputBox("Select the second criteria range (e.g., C2:C12):", xTitleId, "", Type:=8)
    Criteria2 = Application.InputBox("Enter the second criteria value (e.g.,2-Jan):", xTitleId, "", Type:=2)
    Set OutputRange = Application.InputBox("Select the cell to output the result:", xTitleId, "", Type:=8)
    
    count = 0
    For i = 1 To DataRange.Rows.count
        If StrComp(CStr(CriteriaRange1.Cells(i, 1).Value), CStr(Criteria1), vbTextCompare) = 0 And _
           CStr(CriteriaRange2.Cells(i, 1).Value) = CStr(Criteria2) Then
            ReDim Preserve TempArr(count)
            TempArr(count) = DataRange.Cells(i, 1).Value
            count = count + 1
        End If
    Next i
    
    If count = 0 Then
        OutputRange.Value = "No match"
    Else
        Call QuickSort(TempArr, LBound(TempArr), UBound(TempArr))
        If count Mod 2 = 1 Then
            OutputRange.Value = TempArr(count \ 2)
        Else
            OutputRange.Value = (TempArr(count \ 2) + TempArr(count \ 2 - 1)) / 2
        End If
    End If
End Sub

Sub QuickSort(arr() As Double, first As Long, last As Long)
    Dim i As Long
    Dim j As Long
    Dim pivot As Double
    Dim temp As Double
    
    i = first
    j = last
    pivot = arr((first + last) \ 2)
    
    Do While i <= j
        Do While arr(i) < pivot
            i = i + 1
        Loop
        
        Do While arr(j) > pivot
            j = j - 1
        Loop
        
        If i <= j Then
            temp = arr(i)
            arr(i) = arr(j)
            arr(j) = temp
            i = i + 1
            j = j - 1
        End If
    Loop
    
    If first < j Then
        QuickSort arr, first, j
    End If
    
    If i < last Then
        QuickSort arr, i, last
    End If
End Sub

2. クリックしてください Run button ボタン(またはF5キーを押す)を実行します。必要な範囲を選択し、条件を入力するよう求められます。プロンプトが完了すると、すべての条件を満たす中央値の結果が指定したターゲットセルに出力されます。

このマクロを使用すると、実行するたびに値の範囲、条件範囲、条件値、および結果を出力する場所を柔軟に選択できます。また、必要に応じてさらに多くの条件を含めるようにコードを簡単に適応させることも可能です。

ヒントとトラブルシューティング: VBAソリューションを使用する際は、選択したすべての範囲の長さが等しいこと、および基準が正しいデータ型とフォーマット(例:テキスト対日付)に一致していることを確認してください。条件を満たす値がない場合、出力には「一致なし」が表示されます。最良の安定性を得るために、マクロを実行する前にワークブックを保存し、プロンプトが表示されたら常にマクロを有効にしてください。このVBAソリューションは、マクロセキュリティ設定に精通しているユーザー向けであり、自動化されたExcelワークフローでの使用に適しています。

まとめると、VBAアプローチは、数式のみでは面倒または難しい複雑な中央値計算を自動化します。これは特に、変動する条件、頻繁な再計算、および大規模なデータセットを扱う場合に適しています。


関連記事:


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

🤖 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日間フル機能お試し —— 登録やクレジットカード不要
  • コストパフォーマンス最適 —— 個別購入よりお得