[[20151002150837]] 『小さい順で、いくつかの和をとる』(ビギナー) ページの最後に飛ぶ

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

 

『小さい順で、いくつかの和をとる』(ビギナー)

    G H I
101 5 7 
102 7 4
103 2 6
104 8 4
105
106

上のようにG101〜H104セルまで値が入っています。

G101セルの値が5 というのは、G1〜G25セルまでに1か2が入力されている個数が5個あるからです。
また、D1〜D25セルまでには、10や20などの値が入力されています。
同様にG26〜G50までの入力個数がG102に、
   G51〜G75までの入力個数がG103、
   G76〜G100までの入力個数がG104に反映されています。

そこでやりたいことは、G101〜G104までの最小数をチェックすると2なので、
D1*G1、D2*G2、…のうち小さい順に2つめまでの和を出したいということです。
それを26〜50、51〜75,76〜100行それぞれの範囲で行い、この4つの合計を出したいということです。
そして、H列なら小さい順に4つまでの和ということになります。
単純に範囲全部で掛け合わせて和を求めるのならSUMPRODUCT(D1:D25,G1:G25)かと思ったのですが、よくわかりませんでした。
どうか、よろしくお願いいたします。

< 使用 Excel:Excel2003、使用 OS:Windows8 >


 質問(めんどくさいんでA:B列で5行ごとで例っす)

	A	B	C	D	E	F
1	10	1	10	←		3
2	20	2	40	←		5
3	30	2	60			4
4	40					4
5	50					2
6	10	1	10	←		
7	20	1	20	←		220
8	30	1	30			
9	40	2	80			
10	50	1	50			
11	10	2	20	←		
12	20	1	20	←		
13	30	3	90			
14	40	1	40			
15	50	2	100			
16	10	1	10	←		
17	20	2	40	←		
18	30	2	60			
19	40	1	40			
20	50					
21	10	2	20	←		
22	20					
23	30	1	30	←		
24	40					
25	50					

 F列みると「2」が最小値。

 んで各グループ(質問は25行ごとだけど例は5行ごと) A*Bの結果を小さい順に2個とる。

 最後に合計。(例の結果では「220」)

 ってこと?
 
(GobGob) 2015/10/02(金) 16:08

=SUMPRODUCT(((D1:D25)*(G1:G25) <= SMALL((D1:D25)*(G1:G25),MIN(G101:G104)))*(G1:G25)*(D1:D25))

(mm) 2015/10/02(金) 17:13


 たとえば小さいほうから三つ目までという場合、個数が三つなのか数値の種類が三種類なのか?

 例
 D列とG列をかけた結果が10,20,20,30の場合。

 個数が三個   :10+20+20の50
 数値が三種類(A):10+20+20+30の80
 数値が三種類(B):10+20+30の60

(ねむねむ) 2015/10/02(金) 17:32


GobGobさん、ねむねむさん、質問がわかりにくくてすいません。
GobGobさんが具体例を書いてくださいましたが、D列の←のついている部分の和をとりたいのです。
ただ、私の表の中には、C、D列はありません。

数値の個数なので、10、20、20、30、30、40、…なら、
2個なら10+20=30
3個なr10+20+20=50
です。

mmさんの式は、結果が0となってしまいました。

すいません、よろしくお願いします

(ビギナー) 2015/10/03(土) 05:09

	A	B	C	D	E	F
1	10	1	10	3	3	10
2	20	2	40	4		
3	30	2	60	5		
4	40		 0	1		
5	50		 0	1		
6	10	1	10	1	5	30
7	20	1	20	2		
8	30	1	30	3		
9	40	2	80	5		
10	50	1	50	4		
11	10	2	20	1	5	40
12	20	1	20	1		
13	30	3	90	4		
14	40	1	40	3	
15	50	2	100	5		
16	10	1	10	2	5	10
17	20	2	40	3		
18	30	2	60	5		
19	40	1	40	3		
20	50		 0	1		
21	10	2	20	4	2	0
22	20		 0	1		
23	30	1	30	5		
24	40		 0	1		
25	50		 0	1
26                                      2       90

としたときに、元々は、A列とB列だけで結果を出そうとしたのです。
しかしできないので、D列に、
=rank(c$1:c$5,1)をD1〜D5に入力しました、(以下、同様)
E列には、E1、E6、…に
=countif(B1:B5,1)+COUNTIF(B1:B5,2)
といった感じで入力し、
E26に
=min(E1:E25)
と入力しました。
そこでF1には
=SUMIF(D1:D5,"<="&E26,C1:C5)
と入力しました。

結果は出ますが、シートがかなり大きくなってしまったのが困りものです。
一応、報告しますと同時に、
もっとコンパクトなもの(マクロ以外で)があったら、お教えください。
よろしくお願いします。


 コンパクトかどうかは疑問だが。

 =SUM(SMALL(IF((D1:D25<>"")*(G1:G25<>""),D1:D25*G1:G25,""),ROW(INDIRECT("1:"&MIN(G101:G104)))))
 で、1行目から25行目で小さい順からの計が出るので

 =SUM(SMALL(IF((D1:D25<>"")*(G1:G25<>""),D1:D25*G1:G25,""),ROW(INDIRECT("1:"&MIN(G101:G104)))),
  SMALL(IF((D26:D50<>"")*(G26:G50<>""),D26:D50*G26:G50,""),ROW(INDIRECT("1:"&MIN(G101:G104)))),
  SMALL(IF((D51:D75<>"")*(G51:G75<>""),D51:D75*G51:G75,""),ROW(INDIRECT("1:"&MIN(G101:G104)))),
  SMALL(IF((D76:D100<>"")*(G76:G100<>""),D76:D100*G76:G100,""),ROW(INDIRECT("1:"&MIN(G101:G104)))))
 ではどうか?

 なお、式の入力後、確定時にShiftキーとCtrlキーを押しながらEnterキーで確定してくれ。
 確定後、式が{}で囲まれればOK。

 あと、返信は編集からではなく、下にある「コメント」から行ってくれ。
(ねむねむ) 2015/10/05(月) 09:42

コメント返信:

[ 一覧(最新更新順) ]


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