[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数条件で最大値を表示』(K)
複数条件にあてはまるセルに数字を返したいと思っています。
データは3ヶ月おきに更新されます。
出荷個数は初回からの合計しかありません。
初回年から3年以上経過しているデータもあります。
下記A〜Eが、200〜300行くらいあるデータベースです。
求めたい数字は、品名ごとの年間出荷個数です。
リンゴであれば2014年に28個、2015年に7個
みかんであれば2013年に10個、2014年に15個
という集計がしたいです。
例えば、下記表の結果で12月のところに年間の出荷個数を表示する。
など何か方法はあるでしょうか。
よろしくお願いします。
A B C D E F
年月 初回年 品名 総個数 合計出荷個数 結果
201406 2014 リンゴ 35 20
201409 2014 リンゴ 35 25
201412 2014 リンゴ 35 28 28
201503 2014 リンゴ 35 28
201506 2014 リンゴ 35 35 7
201309 2013 みかん 25 10
201312 2013 みかん 25 10 10
201403 2013 みかん 25 10
201406 2013 みかん 25 12
201409 2013 みかん 25 25 15
< 使用 Excel:Excel2010、使用 OS:Windows7 >
>データは3ヶ月おきに更新されます。
データは、品名順 > 年月順 に並んでいるんですか?
言い換えれば、みかんの下の方に、またリンゴが出てくることがあるんでしょうか?
(半平太) 2017/05/09(火) 11:35
リンゴ2やリンゴ3はありますが、リンゴは出てきません。
並び順は必ずしも例の順ではありません。
(K) 2017/05/09(火) 11:47
A列は単なる6桁の数字だとすると・・
F2セルに下式を入力して、下にコピー
=IF(C2="","",IF(OR(C2<>C3,AND(C2=C3,LEFT(A2,4)<>LEFT(A3,4))),E2-IFERROR(IF(LOOKUP(8^8,F$1:F1,C$1:C1)=C2,LOOKUP(8^8,F$1:F1),0),0),""))
(半平太) 2017/05/09(火) 13:15
>並び順は必ずしも例の順ではありません。
何が違うのかわかりませんけど、 A列の年月(見た目通りの6桁の数値)は品名ごとに昇順になっているのなら
F2 =IF(E2="","",IF(OR(D2=E2,RIGHT(A2,2)="12"),E2-SUMIF($C$1:C1,C2,$F$1:F1),""))
例示の表でしか考えてません。 (笑) 2017/05/09(火) 13:42
二点ほど解決策があれば教えてください。
○並び順
品名、年月等すべての項目で並べ替えされていない状態で同じ結果が求められる方法はありますか?
(品名も年月もばらばらの場合)
○年月の期間
期間が3年以上で総個数と合計出荷個数がイコールになる場合、
例えばみかんが201309から201503の期間にまたがった時、
201503の結果が正しくは9となる場合21となってしまい結果の合計が総戸数を上回ってしまいます。
A B C D E F
年月 初回年 品名 総個数 合計出荷個数 結果
201309 2013 みかん 25 10
201312 2013 みかん 25 10 10
201403 2013 みかん 25 10
201406 2013 みかん 25 12
201409 2013 みかん 25 13
201412 2013 みかん 25 14 4
201503 2013 みかん 25 20
201506 2013 みかん 25 25 21
上記二点を両方解決する方法はありますか?
よろしくお願いします。
(K) 2017/05/09(火) 14:39
>201503の結果が正しくは9となる場合21となってしまい
なぜ「201503」? なぜ正しくは「9」? 「201506」の結果が正しくは「11」の間違いであれば、 ↓ の式ならそうなります。 >F2 =IF(E2="","",IF(OR(D2=E2,RIGHT(A2,2)="12"),E2-SUMIF($C$1:C1,C2,$F$1:F1),""))
つまり、回答を試しもしていないということが たいへんよくわかりました。 (笑) 2017/05/09(火) 16:14
試したうえで書き込みしたつもりでしたが勘違いでした。
さらに、正しくは11の間違いでした。
申し訳ありません。
並び順が品名・年月、すべてがばらばらの時に同じ結果が出せる方法があるのでしょうか?
よろしくお願いします。
(K) 2017/05/09(火) 16:26
済みません。先の回答は間違っていました。 m(__)m
>(品名も年月もばらばらの場合)
・・と思ったら、仕様の変更ですか? マクロでやった方がいい様な気がしてきましたけど。
F2セルに
=IFERROR(IF(IF(A2="","",IF(AGGREGATE(14,6,A$2:A$500*(C$2:C$500=C2)*(LEFT(A$2:A$500,4)=LEFT(A2,4)),1)=A2,E2)),AGGREGATE(14,6,E$2:E$500*(LEFT(A$2:A$500,4)-LEFT(A2,4)<=0)*(C$2:C$500=C2),1)-IFERROR(AGGREGATE(14,6,E$2:E$500*(LEFT(A$2:A$500,4)-LEFT(A2,4)<=-1)*(C$2:C$500=C2),1),0),""),"")
下にコピー
<結果図> 行 ___A___ ___B___ ___C___ ___D___ ______E______ __F__ 1 年月 初回年 品名 総個数 合計出荷個数 結果 2 201506 2013 みかん 25 25 11 3 201503 2013 みかん 25 20 4 201406 2013 みかん 25 12 5 201506 2014 リンゴ 35 35 7 6 201309 2013 みかん 25 10 7 201503 2014 リンゴ 35 28 8 201412 2013 みかん 25 14 4 9 201409 2014 リンゴ 35 25 10 201403 2013 みかん 25 10 11 201412 2014 リンゴ 35 28 28 12 201409 2013 みかん 25 13 13 201406 2014 リンゴ 35 20 14 201312 2013 みかん 25 10 10 15
(半平太) 2017/05/09(火) 23:18
>並び順が品名・年月、すべてがばらばらの時に同じ結果が出せる方法
並べ替えれば済む話ですけど、できない理由があるんですかね?
一応
F2 =IF(E2="","",IF(OR(D2=E2,RIGHT(A2,2)="12"),E2-SUMIFS(E:E,A:A,(LEFT(A2,4)-1)*100+12,C:C,C2),""))
検証してません。間違ってるかも。 (笑) 2017/05/10(水) 01:00
まだ試せてないのですが早々に試してみます。
(K) 2017/05/12(金) 10:20
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.