Excel関数について

質問

先週末にも同様の質問をさせていただきましたが、よく分からないことがありますので、再度質問させてください。
下記のようなある表から、上位の号から並べ替え（昇順）、またその号に対応する月数についてを合計６０月となるまで積算させたいのです。
○号、月については、表示形式によりますので文字列入力としません。
○表内の空欄（ゼロ）のセルは計算しません。
○号数は１号から７号までとします。（０号はありません。）
○同一号数は同順位とし、それぞれ対応する月数は合算します。
○各号に対応する月数は積算していき、最終月数の上限を６０月とします。
○下表によると６号の合計月数は１１７月ですが、上位の号の月数の関係により４５月まで積算対象となります。
○仮に４号で６０月を超えている場合には、計算結果は「４号　６０月」となります。

　　　Ａ　　Ｂ　　Ｃ　・　・　Ｇ　　Ｈ　
１　７号　１２月　　　　　　４号　　３月
２　７号　２４月　　　　　　５号　１２月
３　６号　８４月　　　　　　６号　４５月
４　６号　３３月　　　⇒
５　５号　１２月　　　　　（合計は６０月）
６　４号　　３月
７　・　　　・
８　・　　　・
・　・　　　・
・　・　　　・

回答No.5

KURUMITO · ベストアンサー

解答Ｎｏ2です。
Ｇ列に入れる式は複雑になりますので、作業列を例えばＪ列に作って対応します。
Ｊ2セルには次の式を作って下方にオートフィルドラッグします。
=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,MAX(J$1:J1)+1,""))
その後にＧ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(OR(COUNIF(J:J,ROW(A1))=0,AND(ROW(A1)>1,SUM(H$1:H1)>=60)),"",INDIRECT("A"&MATCH(ROW(A1),J:J,0)))
Ｈ2セルへの入力の式は解答Ｎｏ2の通りです。

お礼

この度はご回答いただきありがとうございました。KURUMITO様にご教示いただきました内容がヒントとなりお蔭様でシートが完成するに至りました。心より感謝申し上げます。

回答No.6

cistronezk

＞先週末にも同様の質問をさせていただきましたが、よく分からないことがありますので、再度質問させてください。

前回たくさん回答がつきましたが、どこがわからないかをきちんと明記してください。

＞できればＧ列には１、２、３など順位を入力せずに、Ａ列とＢ列を入力すると、Ｇ列とＨ列が抽出・積算されるような方法があると助かるのですが。ぜひご教示ください。

作業列がないと非常に複雑になります。本当に理解したいのなら段階的な手順を踏むべきです。それとも単に望む答えを手に入れたいだけなのでしょうか？

お礼

お世話になります。ご指摘に感謝いたします。
いただいた回答を私なりに考え、シートの完成を目指し、お蔭様で完成させることができました。ありがとうございました。

回答No.4

mt2008

No.3です。
一部修正。
G2に入れる式は↓にしてください
=IF(SUM(H$1:H1)=60,"",IF(MAX(G$1:G1)=MAX(A:A),"",SMALL(A:A,COUNTIF(A:A,"<="&G1)+1)))

お礼

mt2008様ご回答ありがとうございます。
他の方の回答も参考にさせていただき、シートの完成を目指したいと思います。ご協力に感謝申し上げます。

回答No.3

mt2008

あまり綺麗ではありませんが、こんな感じでしょうか。

G1に　=MIN(A:A)
H1に　=IF(SUMIF(A:A,G1,B:B)>60,60,SUMIF(A:A,G1,B:B))

G2に↓
=IF(SUM(H$1:H1)=60,"",IF(MAX(G$1:G1)=MAX(A:A),"",SMALL($A$1:$A$14,COUNTIF($A$1:$A$14,"<="&G1)+1)))

H2に↓
=IF(G2="","",IF((SUM(H$1:H1)+SUMIF(A:A,G2,B:B))>60,60-SUM(H$1:H1),SUMIF(A:A,G2,B:B)))

G2:H2を下までコピー

回答No.2

KURUMITO

ごめんなさい。式を間違えていました。
=IF(G2="","",IF((SUMIF(A:A,G2,B:B)+SUM(G$1:G1))>60,60-SUM(H$1:H1),SUMIF(A:A,G2,B:B)))

回答No.1

KURUMITO

Ａ列に号がありＢ列に月があるとしてＧ列には1，2，3のように入力して号が、それぞれ2行目から、あるとしたらＨ2セルには次の式を入力して下方にオートフィルドラッグすればよいでしょう。
=IF(G2="","",IF((SUMIF(A:A,G1,B:B)+SUM(G$1:G1))>60,60-SUM(H$1:H1),SUMIF(A:A,G1,B:B)))

補足

早速のご回答ありがとうございます。
すみません。できればＧ列には１、２、３など順位を入力せずに、Ａ列とＢ列を入力すると、Ｇ列とＨ列が抽出・積算されるような方法があると助かるのですが。ぜひご教示ください。