エクセルで店別・月別に時間の中央値を取得したいです

シート名：時間計算
行　　　　Ａ列　　　Ｂ列　　　Ｃ列
1　　　　　店　　　日付　　　　時間
2　　　　　1　　　4月1日　　　1:00
3　　　　　3　　　4月1日　　　1:10
4　　　　　4　　　4月1日　　　0:30　
　　　　　 ：　　　　：　　　　　：
　　　　　 1　　　3月31日　　　0:50
　　　　　 3　　　3月31日　　　1:20
　　　　　 4　　　3月31日　　　1:45

というように、店別日別に時間を入力しているシートがあります

これをもとに、店別月別の中央値を表示させたいです
Ａ列　　　Ｂ列　　　Ｃ列　　　　Ｄ列　　　～　　　　
店　　　　4月 　　　5月　　　　6月
1 　　
3 　　
4 　　
※中央値を表示するシートは、時間計算シートでも別シートでもどちらでも
　結構です（上記例は別シートの場合になります）

MEDIAN関数とIF関数を使ってやってみたのですが、うまくできませんでした

どうぞよろしくお願いいたします

No.1 回答者： fujillin 回答日時： 2021/08/29 00:29

こんばんは

夜中なので、きちんと確認していませんけれど・・・

＞MEDIAN関数とIF関数を使ってやってみたのですが、
＞うまくできませんでした。
え～～っと。
二つの条件で絞って、中央値を出すってことですよね。
　=MEDIAN(IF((条件1)*(条件2), 時間のセル範囲))
として、「配列数式」として設定すれば計算できたりしませんか？

※ 条件の一方が、日付から月の照合なので、そのあたりが配列計算に対応しているかまでは確認していません。
単純な条件式であれば、計算可能なようです。

ご参考までに。

No.2 ベストアンサー 回答者： goomania 回答日時： 2021/08/29 01:49

ご質問者は

>中央値を表示するシートは、時間計算シートでも別シートでも
>どちらでも結構です
とおっしゃっているので、(「中央値」という名前の）別シートに表示することにします。
添付画像①をご覧ください。判り易くするため、データは1～3までの店舗の各月3日分のデータがあるものとします。
添付画像②をご覧ください。中央値シートの月が表示されている、B2～D2のセルには各月の月初の日付を西暦年月日で入力してあり、セルの書式設定のユーザー定義「m"月"」を用いて、「月」を表示しているものとします。
中央値シートのB3セルに
=MEDIAN(IF(時間計算!$A$2:$A$28=$A3,IF((時間計算!$B$2:$B$28>=B$2)*(時間計算!$B$2:$B$28<=EOMONTH(B$2,0)),時間計算!$C$2:$C$28,""),""))
という数式を記述し、配列数式として確定させてあります。
配列数式とは、上記数式を入力後、CTRL+SHIFT+ENTERで確定させた数式を言います。(バージョンが「365」の場合はENTERのみでもよい)
すると
{=MEDIAN(IF(時間計算!$A$2:$A$28=$A3,IF((時間計算!$B$2:$B$28>=B$2)*(時間計算!$B$2:$B$28<=EOMONTH(B$2,0)),時間計算!$C$2:$C$28,""),""))}
のように波括弧で括られた状態になります。
これを下方向、右方向へコピーすればよいことになります。
なお、データの範囲、中央値シートの表示位置に合わせて、上記数式のセル範囲を調整願います。

この回答へのお礼

遅い時間に画像や吹き出しまで作っていただいてありがとうございました
ものすごく分かり易くてうれしいです
おかげでやりたいことが完ぺきにできました
本当にありがとうございました

お礼日時：2021/08/29 09:32