Excel関数（抽出？）について

質問

早速ですが、Excelで次のような表があり、この中からA列のデータを昇順に抽出して、あわせて対応するB列のデータを積算するのですが、次の3点の条件を設けた場合を求める関数がよくわかりません。
(1)A列を抽出する際には0号はカウントしない。
(2)同じ号はあわせて抽出する。
(3)各号の合算した日数は、60日までとする。
具体的には、
　　A　　B　　　　　　　　F　　G
１　3号　 5日　　　　　　1号　10日
２　2号　10日　　　　　　2号　25日
３　4号　10日　　　　　　3号　20日
４　3号　15日　　⇒　　　4号　 5日　
５　2号　15日　　　　　 　計 60日
６　1号　10日
７　0号 5日
　　 計 70日
としたいのです。
どなたかご教示くださるようよろしくお願い申し上げます。

回答No.5

KURUMITO · ベストアンサー

初めにすべてのデータは2行目から下方にあるとします。
また、号、日などの記号はセルの書式設定の表示形式からユーザー定義を選び、?"日"　などの入力して表示させるとします。
Ｆ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(G2="","",ROW(A1)&"号")
Ｇ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(COUNTIF(A:A,ROW(A1))=0,"",IF(SUMIF(A:A,ROW(A1),B:B)+SUM(G$1:G1)<=60,SUMIF(A:A,ROW(A1),B:B),(SUMIF(A:A,ROW(A1),B:B)+SUM(G$1:G1))-60))

お礼

ご回答ありがとうございます。
当方の力量不足により、新たに質問させていただくことになりましたのでそちらへのご回答もよろしくお願い申し上げます。

回答No.4

xkuonx

下で回答しているものです。
式にミスがあったので、訂正致します。

▽F列のセル内容
※F3以降はF2の内容をフィルハンドルコピーしたものでOK
F1→=1
F2→=IF(OR(F1="計",F1=""),"",IF(SUM($G$1:G1)+SUMIF($A$1:$B$7,F1+1,$B$1:$B$7) > 60,"計",F1+1))
F3→=IF(OR(F2="計",F2=""),"",IF(SUM($G$1:G2)+SUMIF($A$1:$B$7,F2+1,$B$1:$B$7) > 60,"計",F2+1))
F4→=IF(OR(F3="計",F3=""),"",IF(SUM($G$1:G3)+SUMIF($A$1:$B$7,F3+1,$B$1:$B$7) > 60,"計",F3+1))
F5→=IF(OR(F4="計",F4=""),"",IF(SUM($G$1:G4)+SUMIF($A$1:$B$7,F4+1,$B$1:$B$7) > 60,"計",F4+1))

上記でお願い致します。

お礼

ご回答ありがとうございます。
当方の力量不足により、新たに質問させていただくことになりましたのでそちらへのご回答もよろしくお願い申し上げます。

回答No.3

xkuonx

まずはじめに、質問に書かれているG4セルですが「5日」ではなく「10日」ですよね？
それに伴いG5セルは「65日」となり、要件である「合算した日数は60日まで」に反するので、正しい出力内容は
F 　　G
1号　10日
2号　25日
3号　20日
計　55日
である事を前提とします。

更に前提としてA列、B列、F列はセルの書式設定を下記のようなユーザー定義にしてください。

▽A列
セルを右クリック→セルの書式設定→表示形式タブから「ユーザー定義」選択→「G/標準"号"」を設定
▽B列
同様に「G/標準"日"」を設定

▽F列
同様に「G/標準"日"」を設定

以上を前提として回答します。
関数を設定するのはF列、G列になります。

▽F列のセル内容
※F3以降はF2の内容をフィルハンドルコピーしたものでOK
F1→=1
F2→=IF(OR(F1="計",F1=""),"",IF(SUM($G$1:G1)+SUMIF($A$1:$B$7,2,$B$1:$B$7) > 60,"計",F1+1))
F3→=IF(OR(F2="計",F2=""),"",IF(SUM($G$1:G2)+SUMIF($A$1:$B$7,2,$B$1:$B$7) > 60,"計",F2+1))
F4→=IF(OR(F3="計",F3=""),"",IF(SUM($G$1:G3)+SUMIF($A$1:$B$7,2,$B$1:$B$7) > 60,"計",F3+1))
F5→=IF(OR(F4="計",F4=""),"",IF(SUM($G$1:G4)+SUMIF($A$1:$B$7,2,$B$1:$B$7) > 60,"計",F4+1))

▽G列のセル内容
※G3以降はG2の内容をフィルハンドルコピーしたものでOK
G1→=SUMIF($A$1:$B$7,F1,$B$1:$B$7)
G2→=IF(F2="","",IF(F2="計",SUM($G$1:G1),IF(SUM($G$1:G1)>60,SUM($G$1:G1),SUMIF($A$1:$B$7,F2,$B$1:$B$7))))
G3→=IF(F3="","",IF(F3="計",SUM($G$1:G2),IF(SUM($G$1:G2)>60,SUM($G$1:G2),SUMIF($A$1:$B$7,F3,$B$1:$B$7))))
G4→=IF(F4="","",IF(F4="計",SUM($G$1:G3),IF(SUM($G$1:G3)>60,SUM($G$1:G3),SUMIF($A$1:$B$7,F4,$B$1:$B$7))))
G5→=IF(F5="","",IF(F5="計",SUM($G$1:G4),IF(SUM($G$1:G4)>60,SUM($G$1:G4),SUMIF($A$1:$B$7,F5,$B$1:$B$7))))

上記でやりたい事はできると思われます。

お礼

ご回答ありがとうございます。
当方の力量不足により、新たに質問させていただくことになりましたのでそちらへのご回答もよろしくお願い申し上げます。

補足

すみません。
早速ご回答いただき恐縮ですが、こちらからの説明が足りなくて申し訳ありません。
次のとおり補足させてください。
(1)については、例示とは異なりますが、Ａ列に「1号」がない場合には「2号」から、「2号」がない場合には「3号」からというように、Ａ列を昇順に抽出していきます。
(3)についてですが、Ａ列の号を昇順で抽出していき、対応するＢ列を60日になるまで積算していきます。
よって、例示の場合、
A7は「0号」なので抽出せず、対応するB7の「5日」は積算しません。
F1にはA6の「1号」でG1にはB6の「10日」、
F2にはA2とA5の「2号」でG2にはB2とB5を合算した「25日」、
F3にはA1とA4の「3号」でG3にはB1とB4を合算した「20日」、
F4にはA3の「4号」でG4にはB3の「10日」ですが、
これまでのＧ列の合計「65日」となり、Ｇ列の合計は「60日」までですのでB3のうち「5日」だけ積算することにしたいのです。
説明が分かりにくくて申し訳ありません。

回答No.2

cistronezk

B列は数字（表示形式で”日”を表示）ですか、それとも文字列（”５日”と直打ち）ですか、どちらですか？

お礼

ご回答ありがとうございます。
当方の力量不足により、新たに質問させていただくことになりましたのでそちらへのご回答もよろしくお願い申し上げます。

補足

早速、お問い合わせいただきありがとうございます。
Ｂ列については、数字入力で表示形式で”日”を表示するものです。
また、(3)についてですが、Ａ列の号を昇順で抽出していき、対応するＢ列を60日になるまで積算していきます。
よって、例示の場合、
A7は「0号」なので抽出せず、対応するB7の「5日」は積算しません。
F1にはA6の「1号」でG1にはB6の「10日」、
F2にはA2とA5の「2号」でG2にはB2とB5を合算した「25日」、
F3にはA1とA4の「3号」でG3にはB1とB4を合算した「20日」、
F4にはA3の「4号」でG4にはB3の「10日」ですが、
これまでのＧ列の合計「65日」となり、Ｇ列の合計は「60日」までですのでB3のうち「5日」だけ積算することにしたいのです。
説明が分かりにくくて申し訳ありません。

回答No.1

Cupper

SUMIF関数で合計を求めればOK

　=SUMIF(検索範囲,検索条件,合計範囲)
　=SUMIF(A1:A7,F1,B1:B7)

このように使います
コピーして使うなら

　=SUMIF(A$1:A$7,F1,B$1:B$7)

これでOK
※ F列は自分で入力してください

お礼

ご回答ありがとうございます。
当方の力量不足により、新たに質問させていただくことになりましたのでそちらへのご回答もよろしくお願い申し上げます。