Excelで条件付き平均値、中央値、最頻値算出

質問

・『A列 顧客名』『B列 出荷日』『C列 返却日』『D列 個数』となるリストを作成
・G列の対応したセルに『G２セル 顧客名』『G3セル 出荷日』『G4セル 返却日』を入力すると対応する『D列 個数』の『G5セル 中央値』『G6セル 最頻値』『G7セル 平均値』『G8セル 最大値』『G9セル 最小値』を算出
上記をできるようにしたいです。

まずは検索内容を『検索顧客名 A』『検索出荷日 2018/1/1』『検索返却日 2020/4/20』とした場合の中央値の計算式をいかにしたのですが

(1) =MEDIAN(IF(B3:B21>=G3,IF(B3:B21>=G3,IF(A3:A21=G2,D3:D21,""),""),""))

この場合の結果が『４』となりましたが、確認のため別数式で

(2) =MEDIAN(D3,D6,D7,D8,D13,D18,D20)

とした場合の結果が『６』となりました。
おそらく(1)の数式に問題があるかと思うのですが原因を特定できませんでした。
皆さんに理由と正しい数式をご教授いただきたいです。
また、同様に不具合があった為、最頻値、平均値、最大値、最小値も教えていただきたいと思います。
よろしくお願いいたします。

回答No.2

bunjii · ベストアンサー

(1)の数式を下記のように修正してください。
=MEDIAN(IF(B3:B21>=G3,IF(B3:B21>=G3,IF(A3:A21=G2,D3:D21,""),""),""))
　　　　　　　↓
=MEDIAN(IF(B3:B21>=G3,IF(B3:B21>=G3,IF(A3:A21=G2,IF(ISBLANK(D3:D21),"",D3:D21),""),""),""))

(1)の数式ではD列のブランクセルを0として計算していますので不都合であれば0を""に置き換えます。

他の計算では平均値を同様に考えてください。
中央値、最頻値、最大値、最小値には影響が無いと思います。

お礼

的確な回答ありがとうございました。

回答者様達へは業務上詳細を説明できない為、質問内容は実際の業務と異なる名称のリストでしたので意図が理解しがたい点もあったと思います。

『答えが異なる理由』と『(1)式の修正点』を回答していただき、業務上求めている計算結果が明確となりましたのでベストアンサーとさせていただきました。
ありがとうございました。

回答No.4

masnoske

空白セルの処理方法の問題でしょう．
空白はデータがない（データに含まない）のか，個数 0 なのか（データに含む）．

回答No.3

msMike

考へた式が正答を出すかだうか検討する爲に、貴方が期待する、範囲 G5:G9 の値を教へておいて下さい。
質問者は、回答者の爲に此の位の協力はして欲しい！

回答No.1

f272

(1)は空白のセルは0と解釈していて，(2)は空白のセルを無視しているのです。
あなたはどうしたいのですか？