エクセルで時間指定した最大・最小を求めたい

今　エクセルで１日２４時間データでその日の最大・最小を求めています。配列数式を使っていますが①例えば平日は９時～１８時の最大・最小等、時間指定をしたいです。
②次に４月の出荷数が２２００付近のデータ（近似値）を５個探すといった数式
今　この二つが困っています。何方かご教授願います

No.5 回答者： yuji3690 回答日時： 2017/03/18 23:15

あ、すみません。

D列のデータ使うかと思って最初注意書きのような物書きましたが、使わなくてできたので読み飛ばしてください。

No.4 回答者： yuji3690 回答日時： 2017/03/18 23:13

データが多いと重くなってしまうので、極力配列を使わない方法でやりますね。

説明文長いので、入力する必要のある所は★をつけておきますね。

D列の時間についてはどのようなデータ形式で入力されているのでしょうか？
A「1H」等と文字式が入っている
B「1｣等と整数が入っていて、表示だけ「H」が加えられている
C「1:00」や「1:00:00」といった時間のデータが入っていて、表示上「H」となっている
どの場合でも計算できますが、簡単のためにBであるとして説明します。
D8で例えると、Aの場合は『D8』を『LEFT(D8,LEN(D8)-1)』とすることで、D8から右端の1文字を除いたデータになります。
Cの場合は『D8』を『HOUR(D8)』とすることで、時間の部分のみの数字を抽出することができます。
必要に応じて適応させてください。

①
平日とありますが、土日以外の祝日かどうかも判断するのでしょうか？
そうであるなら、祝日の一覧表が必要となります。
★仮にAA列に祝日一覧（祝日の日付）があるとします。
振り替えがある場合は、振り替え後の日付を一覧表に入力してください。
どこかの列に、その年月日が平日に当たるかどうかを表示する作業列があった方が便利ですね。
仮にL列にそれを表示するとします。
祝日かどうかも判断するなら、IFを2つ重ねて、WEEKDAYで曜日の判断をします。土日でなくて、更に祝日一覧とも一致しない場合に平日判定を、それ以外は休日判定を出します。
祝日は判断しなくて良いなら、IF1つとWEEKDAYだけで判断できますね。
★例：L8=IF(WEEKDAY(I8,2)<6,IF(COUNTIF(AA:AA,I8)=0,"平日","祝日"),"休日")
この式をI列のデータの数だけコピーしてください。
WEEKDAY(I8,2)はI8が月曜なら1～日曜なら7と表示するので、<6とすることで土日以外を条件とします。
なので『I8が土日なら"休日"、そうでなくてI8が祝日一覧になければ"平日"、祝日一覧にあれば"祝日"と表示する』という式になっています。
休日と祝日を分けた意味はありません。どちらも休日と表示しても問題ないです。
今後の式で平日かどうかを判断基準にするので、表示は休日でも祝日でも変わらないと考えます。

平日は9～18時ということですが、休日はどうするのでしょうか？
一応休日は時間指定なしの最大最小とした場合で考えてみます。休日は空白とするのであれば、変更はたやすいかと。
休日は休日で別の指定をする必要がある場合は、式を加工する必要があります。
9時～18時というのは式で直接入力しても良いですが、後で変更する場合のためにどこかに入力セルを作っておきましょう。
★仮にA1が開始時刻、A2が終了時刻、とします。D列同様数字のみが入っているものとします。
この時、MAXにしろMINにしろ、その範囲をどう指定するかというのが問題ですね。
J8,K8の例で説明すると、
I8の日付に関してのデータは、MATCH(I8,C:C,FALSE)とすることで、C列の中でI8と一致する一番上のセルのセル番号を入手することができます。
L8が平日でなければ、FまたはG列のこの行にあるセルが開始時刻のセルとなり、そのセルの23個下のセルが終了時刻のセルとなります。
L8が平日であれば、FまたはG列の、この行から「A1-1」だけ下のセルが開始時刻のセルとなり、「A2-1」だけ下のセルが終了時刻のセルとなります。
これを式で表せば、「開始時刻のセル：終了時刻のセル」という表示にできるので、INDIRECTを用いてこの文字列を参照セルの範囲として組み込むことができます。
★例：J8=MAX(INDIRECT("F"&MATCH(I8,C:C,FALSE)+IF(L8="平日",A$1-1,0)&":F"&MATCH(I8,C:C,FALSE)+IF(L8="平日",A$2-1,23)))
★例：K8=MIN(INDIRECT("G"&MATCH(I8,C:C,FALSE)+IF(L8="平日",A$1-1,0)&":G"&MATCH(I8,C:C,FALSE)+IF(L8="平日",A$2-1,23)))
これらの式をI列のデータの数だけコピーしてください。

おそらく求めた結果が表示されていると思います。
試してないので、問題があれば直しますので教えてください。
（できれば入力した式と、その結果表示されたものを記載してください）
②も同様です。

②
これは補助列を用いて、その数値との差がどれだけあるかを絶対値で表示させ、
指定期間内のそのデータで小さい順に5つ取り出せばよいということです。
出荷数というのは、合計（E列＝1時間毎のMAXとMINの合計）のことでよろしいでしょうか？
であれば、H列に差を表示させるとして（邪魔であれば他のどこの列でもよいです）
★仮にA3に抽出したいデータの値（この場合2200）を入力するとして、
★仮にA4に抽出したいデータの開始日（この場合17年4月1日）を入力するとして、
★仮にA5に抽出期間の最終日の翌日（この場合17年5月1日）を入力するとして、
（最終日の翌日としたのは、1ヶ月毎であるなら末尾で変化するより翌日の1日で統一した方が分かり易いと判断した為です。1日からの1ヶ月固定であるならば、A5=DATE(YEAR(A4),MONTH(A4)+1,1)とすればA4に応じて自動入力されます）
★例：H8=IF(E8>A$3,E8-A$3,A$3-E8)
これをE列のデータの数だけコピーしてください。
これでE列のデータをA3と比較し、大きい方から小さい方を引いた値が表示されます。
抽出データをB1~B5に表示させるとします。
（ROW関数：セルの行番号を取得する関数。を使いますので、ROWの()の中は必要（＝抽出データを表示するセル）に応じて修正してください）
★例：B1=SMALL(INDIRECT("H"&MATCH(A$4,C:C,FALSE)&":H"&MATCH(A$5,C:C,FALSE)-1),ROW()-ROW(B$1)+1)
B1をB2～B5にコピーしてください。
これでH列の「A4に一致する日付の一番上の行～A5に一致する一番上の行の1つ上の行」で「ROW()-ROW(B$1)+1」番目に小さい数値を表示します。
ROW()-ROW(B$1)+1というのは、そのセルが「B1を1行目として」何行目にあたるかを計算する式です。
表示するセルの先頭行が分かっていれば、ROW()-○と○に入る数字を固定させればいいのですが、不明である為-ROW(B$1)+1という式にしています。B$1という所を表示セルの先頭セルの番地に変えてください。行番号の前の$を付け忘れないように。

No.3 回答者： WindFaller 回答日時： 2017/03/18 21:06

こんばんは。

まず、位置の確認をしておきます。4/1の始まりは、
[C8] ここが起点です。

①
私の場合は、補助列を作りました。
邪魔でしたら、どこか別の所にドラッグで移動すれば、数式は壊れずに済みます。

M8～下（M727)にコピー
=TEXT(C8,"mmdd")&REPT("0",3-LEN(D8))&D8

今は、見えやすいように、I1:K1

I1　　　　　　J1　　　　　K1
区間/時間　　08H　　18H

I8
4/1 から、4/30 (シリアル値に限ります）


J8
=MAX(OFFSET($E$7,MATCH(TEXT(I8,"mmdd")&$J$1,$M$8:$M$1000,0),,MATCH(TEXT(I8,"mmdd")&$K$1,$M$8:$M$1000,0)-MATCH(TEXT(I8,"mmdd")&$J$1,$M$8:$M$1000,0)+1))

K8
=MIN(OFFSET($E$7,MATCH(TEXT(I8,"mmdd")&$J$1,$M$8:$M$1000),,MATCH(TEXT(I8,"mmdd")&$K$1,$M$8:$M$1000,0)-MATCH(TEXT(I8,"mmdd")&$J$1,$M$8:$M$1000,0)+1))

これで、4/30分まで、ドラッグ・コピーすれば、時間内の最大値と最小値は出せます。

平日以外は時間帯が変わる場合は、

=MAX(OFFSET($E$7,MATCH(TEXT(I15,"mmdd")&$J$1,$M$8:$M$1000,0),,MATCH(TEXT(I15,"mmdd")&$K$1,$M$8:$M$1000,0)-MATCH(TEXT(I15,"mmdd")&$J$1,$M$8:$M$1000,0)+1))

&$K$1　と &$J$1
をそれぞれ、&"08H" &"016H" と手書きで変えてあげてもよいと思います。
数式でも可能ですが、極端に長くなる可能性があります。


②
　　I　　　　 J
2 中心値　2200 ←手書き
3 抽出　5　　　 ←手書き
4 順位[　12]　　 ←計算

j4
=COUNTIF($E$8:$E$727,"<="&$J$2)

K2 ～K6
=SMALL($E$8:$E$727,INT($J$3/2)+ROW(A1)-1)
　　・
　　・
　　・

No.2 回答者： mike_g 回答日時： 2017/03/18 19:56

添付図右表で 17/4/1 の最大値が 2570 に見えるのは何故？

「アレは単なる例として入れただけで実はデタラメ」なんて仰らないでぇ～！

No.1 回答者： 20170228 回答日時： 2017/03/18 19:41

D列は「9:00:00」と入力しているのを書式設定で「9H」にしているということで良いでしょうか？

事前に別のセルに9H(9:00:00)と18H(18:00:00)を入力したセルを用意（下図L列）
（指定する時間を変更したければここを変更すれば可）

①平日だったら9Hから18Hの中での最大値、土日だったら24H全体の中での最大値
上記条件で。
データを作るのが面倒だったので範囲は省略してます。
列全体を指定すると重くなると思うので範囲は任意に修正してください。

=IF(WEEKDAY($I8,3)<5,MAX(IF($C$8:$C$19=$I8,$E$8:$E$19)),MAX(IF(($C$8:$C$19=$I8)*($D$8:$D$19>=$L$8)*($D$8:$D$19<=$L$9),$E$8:$E$19)))

ctrl+shift+enterで確定して{}つけます。
MAXのところをMINに変更すれば最小値も出ます。

②の近似値は指定した範囲を超えませんか？超えても可？

この回答へのお礼

②近似値は前後ですので超えても可です

皆さんいろいろありがとうです
まだ会社に行っていないので今度いろいろ試してみます

お礼日時：2017/03/19 10:40