[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『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
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です。
(正)
ご丁寧なご教示ありがとうございます。
>データは何行くらいあるんですか?
2001/6/25〜2016/10/14で、現在で3783行です(土日、祝日、その他休業日は除外しています)
これからも増えていきますが、私の年齢78歳から考えても6000行もあれば十分です。(苦笑)
>1年ごとの行数は決まっていますか?
決まっていませんが、大体(土日、祝日、その他休業日は除外して)1年間で245日前後(1〜3日)
だと思います。
>年毎の行数が、必ず250日とか決まってるなら、作業列を使って比較的簡単に出来ますが、決まってないならちょっとややこしくなります。
1年間を245日と決めて、スタートを2002/1/1からとした場合の数式を教えてください。
>ご提示の例でも日数は決まってなさそうなので、D〜I列を作業列に使います。
上記のご教示をこれから勉強させていただきますが、取り急ぎお礼まで。
(正)
ありがとうございました。
皆さんのご教示をこれから勉強させていただきます。
今後とも宜しくお願いします。
(正)
(正) 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
何度もありがとうございます。
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
すでに回答があるけど考えたので回答。
=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
早速ありがとうございます。
> 年末
> =and(year(a1)<>year(a2),month(a1)<>month(a2))
> 月末
> =and(year(a1)=year(a2),month(a1)<>month(a2))
上記の数式で検証させて頂きました。出来たのですが、月初め、年初めに塗りつぶしになるのですが、
データのある月末、年末を塗りつぶしにできないでしょうか。
宜しくお願いします。
(正) 2016/10/17(月) 16:49
こんばんわ。
>月初め、年初めに塗りつぶしになる
「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) 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
通勤途中のお忙しい中をご教示下され申し訳なく感謝いたします。
お陰様で希望通りのものが出来上がりました。
長々と大変お世話になりました。
今後とも宜しくご指導お願いします。
(正) 2016/10/18(火) 11:17
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.