[[20170509102249]] 『複数条件で最大値を表示』(K) ページの最後に飛ぶ

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

 

『複数条件で最大値を表示』(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

(洋平太)さん、(笑)さん ありがとうございます。
PCを使える環境になく御礼が遅くなってしまいました。

まだ試せてないのですが早々に試してみます。
(K) 2017/05/12(金) 10:20


コメント返信:

[ 一覧(最新更新順) ]


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