[[20161015201235]] 『1年間毎の最大値、最小値を横に表示したい』(正) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『1年間毎の最大値、最小値を横に表示したい』(正)

毎度お世話になります。
A列A2から下に15年間昇順で日付が入っています。
B列B2から下に15年間データが入っています。
1年間毎のデータの値の最大値、最小値をそれぞれ、その横のC列に表示したい。
最大値、最小値ともに同じ値がある場合は最初の値を表示する。
C列C2以下に入れる数式を教えてください。

例=17日間です

 日付     値 最大値、最小値
2016/9/16 839
2016/9/20 827 827
2016/9/21 850
2016/9/23 862
2016/9/26 842
2016/9/27 870
2016/9/28 857
2016/9/29 906
2016/9/30 909
2016/10/3 899
2016/10/4 913
2016/10/5 919
2016/10/6 950
2016/10/7 966
2016/10/11 997 997
2016/10/12 992
2016/10/13 984

宜しくお願いします。

(正)

< 使用 Excel:Excel2013、使用 OS:Windows10 >


 こういうので、だしたら良いんじゃないですか?
 =LARGE(B2:B18,1)
 =SMALL(B2:B18,1)
 BJ

1年間とは?暦年ですか?念のため。

BJさん、お尋ねします。
HNにカッコがついたり、既定の日付形式で表示されるのは、
何か貴兄の美学に反するんでしょうか。
できれば、普通の形式を使って貰えないですか?
(γ) 2016/10/16(日) 00:05


 こんばんわ。

 関数よりマクロ向きの要件ですねぇ。。。

 例では2016年のデータしか無いですけど、年毎にそれぞれで最大値と最小値を表示させるんですよね。
 それが15年分、、、
 データは何行くらいあるんですか?

 1年ごとの行数は決まっていますか?
 年毎の行数が、必ず250日とか決まってるなら、作業列を使って比較的簡単に出来ますが、決まってないならちょっとややこしくなります。

 ご提示の例でも日数は決まってなさそうなので、D〜I列を作業列に使います。
 上限を50年分か1万行までにしています。
 2万行だと再計算で結構な時間固まると思います。
 年単位は1月1日〜12月31日にしてます。
 4月始まりとかならD列の式がもう少しややこしくなります。

 C2 =IF(COUNT(A2:B2)=2,IF(COUNTIF(C$1:C1,B2)=0,IF(OR(E2=0,F2=0),B2,""),""),"")
 D2 =YEAR(A2)
 E2 =COUNTIFS(OFFSET(D$1,VLOOKUP(D2,G$2:I$52,2),0,VLOOKUP(D2,G$2:I$52,3),1),D2,OFFSET(B$1,VLOOKUP(D2,G$2:I$52,2),0,VLOOKUP(D2,G$2:I$52,3),1),">"&B2)
 F2 =COUNTIFS(OFFSET(D$1,VLOOKUP(D2,G$2:I$52,2),0,VLOOKUP(D2,G$2:I$52,3),1),D2,OFFSET(B$1,VLOOKUP(D2,G$2:I$52,2),0,VLOOKUP(D2,G$2:I$52,3),1),"<"&B2)
 それぞれ1万行までオートフィル 

 G2 =YEAR(MIN(A:A))
 G3 =G2+1
 H2 =IFERROR(MATCH(G2,D$2:D$10000,0),0)
 I2 =COUNTIF(D$2:D$10000,G2)
 それぞれ50行目までオートフィル

(sy) 2016/10/16(日) 00:24


私なら結果を早く出すことを重視します。
年を表す列を追加することも厭わない。
ピボットで年ごとの最大最小値を求めます。
フィルタオプションで抽出して、
複数回出現をチェック。
何度もすることでも無かろうから、
速度重視です。
他人に尋ねるのが最速とは思いたくない。

(γ) 2016/10/16(日) 00:49


 BJ さん

ご教示ありがとうございます。

(正)


(γ) さん

ご教示ありがとうございます。

>1年間とは?暦年ですか?念のため。

説明が足りなくて済みませんでした。
1年間とは各年度の1/1〜12/31です。

(正)


(sy)さん

ご丁寧なご教示ありがとうございます。

>データは何行くらいあるんですか?

2001/6/25〜2016/10/14で、現在で3783行です(土日、祝日、その他休業日は除外しています)
これからも増えていきますが、私の年齢78歳から考えても6000行もあれば十分です。(苦笑)

>1年ごとの行数は決まっていますか?

決まっていませんが、大体(土日、祝日、その他休業日は除外して)1年間で245日前後(1〜3日)
だと思います。

>年毎の行数が、必ず250日とか決まってるなら、作業列を使って比較的簡単に出来ますが、決まってないならちょっとややこしくなります。

1年間を245日と決めて、スタートを2002/1/1からとした場合の数式を教えてください。

>ご提示の例でも日数は決まってなさそうなので、D〜I列を作業列に使います。

上記のご教示をこれから勉強させていただきますが、取り急ぎお礼まで。

 (正)


BJ さん 、(γ)さん 、(sy)さん の皆様へ

ありがとうございました。
皆さんのご教示をこれから勉強させていただきます。
今後とも宜しくお願いします。

 (正)

(正) 2016/10/16(日) 09:04


 こんにちわ。

 >1年間を245日と決めて、スタートを2002/1/1からとした場合の数式を教えてください。 

 1年間の日数が決まっているなら作業列が無くても1万行でもサクサク動くので、以下の数式で行けます。
 スタート日は意識しなくてもA2セルの日付がスタート日になります。
 日付は245日必ず必要です。B列のデータは必要ない日付の行では空欄にして下さい。

 C2 =IF(COUNT(A2:B2)=2,IF(IFERROR(COUNTIF(OFFSET(C1,DATE(YEAR(A2),1,1)-A2+1,0,A2-DATE(YEAR(A2),1,1),1),B2),0)=0,IF(OR(COUNTIFS(OFFSET(B$2:B$246,INT((ROW()-2)/245)*245+1,0),">"&B2)=0,COUNTIFS(OFFSET(B$2:B$246,INT((ROW()-2)/245)*245+1,0),"<"&B2)=0),B2,""),""),"")

 因みに1年365日(閏年は366日)全て日付があるなら以下です。
 こちらもB列のデータは必要ない日付の行では空欄にして下さい。

 C2 =IF(COUNT(A2:B2)=2,IF(IFERROR(COUNTIF(OFFSET(C1,DATE(YEAR(A2),1,1)-A2+1,0,A2-DATE(YEAR(A2),1,1),1),B2),0)=0,IF(OR(COUNTIFS(OFFSET(B2,DATE(YEAR(A2),1,1)-A2,0,365+(DATE(YEAR(A2),2,29)<>DATE(YEAR(A2),3,1)),1),">"&B2)=0,COUNTIFS(OFFSET(B2,DATE(YEAR(A2),1,1)-A2,0,365+(DATE(YEAR(A2),2,29)<>DATE(YEAR(A2),3,1)),1),"<"&B2)=0),B2,""),""),"")

 ちょっと間違えました。修正(9:44)

(sy) 2016/10/16(日) 09:28


 すいません。

 初めの行数不確定の作業列を使う方のC列の式も間違ってました。
 以下でお願いします。

 C2 =IF(COUNT(A2:B2)=2,IF(IFERROR(COUNTIF(OFFSET(C1,VLOOKUP(D2,$G$2:$H$50,2,0)-ROW()+2,0,ROW()-VLOOKUP(D2,$G$2:$H$50,2,0)-1,1),B2),0)=0,IF(OR(E2=0,F2=0),B2,""),""),"")

(sy) 2016/10/16(日) 09:56


 行数不定の数式ですけど、こっちの方が計算効率が良かったです。
 元の数式でも1万行くらいなら普通に使う分には大丈夫ですが、シート全体を再計算した時などは結構な時間固まります。
 こちらならシート全体を再計算しても一瞬です。

 C2 =IF(COUNT(A2:B2)=2,IF(IFERROR(COUNTIF(OFFSET(C1,VLOOKUP(D2,E$2:F$50,2,0)-ROW()+2,0,ROW()-VLOOKUP(D2,E$2:F$50,2,0)-1,1),B2),0)=0,IF(OR(COUNTIFS(E$2:E$50,D2,H$2:H$50,B2),COUNTIFS(E$2:E$50,D2,I$2:I$50,B2)),B2,""),""),"")
 D2 =YEAR(A2)
 1万行までオートフィル

 E2 =YEAR(MIN(A:A))
 E3 =E2+1
 F2 =MATCH(E2,D$2:D$10000,0)
 G2 =COUNTIF(D$2:D$10000,E2)
 H2 =MAX(OFFSET(B$1,F2,0,G2,1))
 I2 =MIN(OFFSET(B$1,F2,0,G2,1))
 50行迄オートフィル

(sy) 2016/10/16(日) 21:18


(sy) さん

何度もありがとうございます。

2016/10/16(日) 00:24 でご教示頂いた数式検証させて頂きました。
希望通りの結果でした。

また(sy) 2016/10/16(日) 21:18 の方も検証させていただきました。
同じく希望通りの結果でした。こちらの方が各作業列数式の意味が分かり易く勉強になります。
こちらを利用させていただきます。

負んぶに抱っこで厚かましいのですが、もう一件教えていただけませんか。
日付のセルを年末以外の月末はピンクの塗りつぶしに、年末の月末だけは赤の塗りつぶしにしたいのです。
条件付き書式設定でどのように設定すればよろしいのでしょうか?
エクセル2013です。

2015/11/11
2015/11/12
2015/11/13
2015/11/16
2015/11/17
2015/11/18
2015/11/19
2015/11/20
2015/11/24
2015/11/25
2015/11/26
2015/11/27
2015/11/30 ←このセルはピンクの塗りつぶし
2015/12/1
2015/12/2
2015/12/3
2015/12/4
2015/12/7
2015/12/8
2015/12/9
2015/12/10
2015/12/11
2015/12/14
2015/12/15
2015/12/16
2015/12/17
2015/12/18
2015/12/21
2015/12/22
2015/12/24
2015/12/25
2015/12/28
2015/12/29
2015/12/30 ←このセルは赤の塗りつぶしに
2016/1/4
2016/1/5
2016/1/6
2016/1/7
2016/1/8

宜しくお願いします。

  (正)

(正) 2016/10/17(月) 08:37


1つ目のルール:=AND((EOMONTH($A2,0)=$A2),MONTH($A2)=12) で赤。
2つ目のルール:=(EOMONTH($A2,0)=$A2) でピンク。
(???) 2016/10/17(月) 10:21

あ、月末が休日で、一覧に無い、なんて事がありますかね?(サンプル日付が正にそうか…)
会社の休日まで考慮する必要があるならば、WORKDAY.INTL関数を併用してみてください。
(???) 2016/10/17(月) 10:30

 すでに回答があるけど考えたので回答。

 =A2=EOMONTH(A2,0) ピンク
 =AND(A2<>EOMONTH(A2,0),MONTH(A2)<>MONTH(A3)) 赤
(bi) 2016/10/17(月) 10:30

 こんにちわ。

 a列全体を選択して、条件付書式の数式で

 年末
 =year(a1)<>year(a2)

 月末
 =month(a1)<>month(a2)

 年末条件が上にくるようにして下さい。

 もしくは以下だと順番はどうでも良いです。

 年末
 =and(year(a1)<>year(a2),month(a1)<>month(a2))

 月末
 =and(year(a1)=year(a2),month(a1)<>month(a2))

(sy) 2016/10/17(月) 12:35


 今日はPCを触れ無いので確認してませんが、
 もしも最終行が色付になるようでしたら、
 a2<>""
 を式に追加して下さい。

(sy) 2016/10/17(月) 12:49


(sy) さん

早速ありがとうございます。

> 年末
> =and(year(a1)<>year(a2),month(a1)<>month(a2))

> 月末
> =and(year(a1)=year(a2),month(a1)<>month(a2))

上記の数式で検証させて頂きました。出来たのですが、月初め、年初めに塗りつぶしになるのですが、
データのある月末、年末を塗りつぶしにできないでしょうか。

宜しくお願いします。

(正) 2016/10/17(月) 16:49


2行目からデータなので、「=YEAR($A2)<>YEAR($A3)」「=MONTH($A2)<>MONTH($A3)」のように、2行目と3行目を比較しましょう。
(なお、年だけ、月だけで判断しても良いと思う)
(???) 2016/10/17(月) 17:19

 こんばんわ。

 >月初め、年初めに塗りつぶしになる

 「a列全体を選択して」が抜けてますよ。

 A2からの範囲にするなら、???さんのおっしゃるように、A2とA3を比較して下さい。

 後、初めに浮かんだ式から勘違いして、別の式をUPしてました。
 12月から1月は減るので、お互いの条件どうし干渉しないので、以下で行けます。

 年末
 =YEAR(A1)<YEAR(A2)

 月末
 =MONTH(A1)<MONTH(A2)

 優先順位はどっちが上でも構いません。

 こちらもA2からの範囲にするなら、A2とA3を比較して下さい。

(sy) 2016/10/17(月) 21:27


(sy) さん

ありがとうございます。
希望通りに出来ました。

厚かましくもう一つお願いです。

>(sy) 2016/10/16(日) 21:18

上記の時にご教示下さった数式を少し変更して年間ではなく、月間で最大値、最小値を出したく、
いろいろ試してみましたが上手くいきません。
どこをどのように変更すれば出来ますか? 教えて頂けませんか。

宜しくお願いします。
(正) 2016/10/17(月) 23:15


(???) さん

ご教示ありがとうございます。勉強させていただきます。

(正) 2016/10/17(月) 23:20


 おはようございます。
 もう通勤途中なので検証は出来ませんが、
 多分下記の修正で出来ると思います。

 d2 =eomonth(a2,0)

 e2 =eomonth(min(a:a),0)
 e3 =eomonth(e2,1)

 に、それぞれ変更して下さい。
 オートフィルの範囲も500行まで増やして、

 c列のvlookupとcountifsの範囲も500まで増やして下さい。

(sy) 2016/10/18(火) 08:07


(sy)さん

通勤途中のお忙しい中をご教示下され申し訳なく感謝いたします。
お陰様で希望通りのものが出来上がりました。
長々と大変お世話になりました。
今後とも宜しくご指導お願いします。

(正) 2016/10/18(火) 11:17


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.