お知恵をお貸し下さい。

質問

支払の金額によって、手形金額の振り分けを関数で算出したいと考えております。A1セルへ支払金額を手動で入力すると、B1セル～B4セルまで自動で振り分け表示されるようにしたい。

エクセル２００７を使用しております。

・A1セルへ、1以上～1,000,000以下を入力した場合
B1セルへの表示はA1そのままの金額（A1に123,456を入力したなら、B1に123,456）を表したい。
B2～B4セルには0ゼロを表示したい。

・A1セルへ、1,000,001以上～1,099,999以下を入力した場合
B1セル→1,000,000
B2セル→残額を表示
（A1に1,023,456を入力したなら、B１に1,000,000　B2に23,456）
B3～B4セルには0ゼロを表示

・A1セルへ、1,100,000以上～2,000,000以下を入力した場合
B1セル→A1そのままの金額
B2～B4セルには0ゼロを表示

・A1セルへ、2,000,001以上～2,099,999以下を入力した場合
B1セル→2,000,000
B2セル→残額を表示
B3～B4セルには0ゼロを表示

以下も同様に、2,000万まで表示させたい。

2,100,000-3,000,000 金額そのまま
3,000,001-4,000,000 300万と残額
4,000,001-4,099,999 300万と100万と残額
4,100,000-5,000,000 金額そのまま
5,000,001-6,000,000 500万と残額
6,000,001-6,099,999 500万と100万と残額
6,100,000-7,000,000 500万と残額
7,000,001-7,099,999 500万と200万と残額
7,100,000-8,000,000 500万と残額
8,000,001-9,000,000 500万と300万と残額
9,000,001-9,099,999 500万と300万と100万と残額
9,100,000-10,000,000 金額そのまま
10,000,001-11,000,000 1000万と残額
11,000,001-11,099,999 1000万と100万と残額
11,100,000-12,000,000 1000万と残額
12,000,001-12,099,999 1000万と200万と残額
12,100,000-13,000,000 1000万と残額
13,000,001-14,000,000 1000万と300万と残額
14,000,001-14,099,999 1000万と300万と100万と残額
14,100,000-15,000,000 1000万と残額
15,000,001-16,000,000 1000万と500万と残額
16,000,001-16,099,999 1000万と500万と100万と残額
16,100,000-17,000,000 1000万と500万と残額
17,000,001-17,099,999 1000万と500万と200万と残額
17,100,000-18,000,000 1000万と500万と残額
18,000,001-19,000,000 1000万と500万と300万と残額
19,000,001-19,099,999 1000万と500万と300万と100万と残額
19,100,000-20,000,000 金額そのまま

何卒宜しくお願い申し上げます。

回答No.1

kagakusuki · ベストアンサー

まず、適当なセル範囲（例えばSheet2のA列～F列）に次の様な表を作成して下さい。

　　　　　　 A列　　　　　　　　B列　　　　　　　　C列　　　　　　　　D列　　　　　E列　　　　F列
1行目　　　A1　　　　　　　　　B1　　　　　　　　 B2　　　　　　　　 B3　　　　　　 B4　　　　 B5
2行目　　　　0
3行目　　　　1　　　　　　金額そのまま
4行目　　1000001　　　　1000000　　　　　　　残額
5行目　　1100000　　　　金額そのまま
6行目　　2000001　　　　2000000　　　　　　　残額
7行目　　　2100000　　　金額そのまま
8行目　　3000001　　　　3000000　　　　　　　残額
9行目　　4000001　　　　3000000　　　　　　1000000　　　　　　残額
10行目　　4100000　　　金額そのまま
11行目　　5000001　　　　5000000　　　　　　残額
12行目　　6000001　　　　5000000　　　　　1000000　　　　　　残額
13行目　　6100000　　　　5000000　　　　　　残額
14行目　　7000001　　　　5000000　　　　　2000000　　　　　　残額
15行目　　7100000　　　　5000000　　　　　　残額
16行目　　8000001　　　　5000000　　　　　3000000　　　　　　残額
17行目　　9000001　　　　5000000　　　　　3000000　　　　　1000000　　　　　残額
18行目　　9100000　　　金額そのまま
19行目　　10000001　　　10000000　　　　　残額
20行目　　11000001　　　10000000　　　　1000000　　　　　　残額
21行目　　11100000　　　10000000　　　　　残額
22行目　　12000001　　　10000000　　　　2000000　　　　　　残額
23行目　　12100000　　　10000000　　　　　残額
24行目　　13000001　　　10000000　　　　3000000　　　　　　残額
25行目　　14000001　　　10000000　　　　3000000　　　　　1000000　　　　　残額
26行目　　14100000　　　10000000　　　　　残額
27行目　　15000001　　　10000000　　　　5000000　　　　　　残額
28行目　　16000001　　　10000000　　　　5000000　　　　　1000000　　　　　残額
29行目　　16100000　　　10000000　　　　5000000　　　　　　残額
30行目　　17000001　　　10000000　　　　5000000　　　　　2000000　　　　　残額
31行目　　17100000　　　10000000　　　　5000000　　　　　　残額
32行目　　18000001　　　10000000　　　　5000000　　　　　3000000　　　　　残額
33行目　　19000001　　　10000000　　　　5000000　　　　　3000000　　　　1000000　　　残額
34行目　　19100000　　金額そのまま

　次に、元のシートのB1セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER(A1),A1>=1),IF(ISNUMBER(VLOOKUP(A1,Sheet2!$A:$B,2)),VLOOKUP(A1,Sheet2!$A:$B,2),A1),"")

　次に、元のシートのB2セルに次の数式を入力して下さい。

=IF(ISNUMBER(B$1),IF(ISNUMBER(VLOOKUP(A$1,Sheet2!$A:$F,ROWS($2:2)+2)),VLOOKUP(A$1,Sheet2!$A:$F,ROWS($2:2)+2),A$1-SUM(B$1:B1)),"")

　次に、元のシートのB2セルをコピーして、B3～B5の範囲に貼り付けて下さい。

　以上です。

お礼

すっ・・すっごい！！！　感謝・感激です！！　めちゃめちゃうれしいわ～！！
自分がやりたかったことが完璧に反映されております。
この関数をじっくり熟慮・理解し、今後の仕事に活かしていけるよう勉強いたします。
本当に有難うございました！！！！！！！！

回答No.2

hitozaurus

これでどうでしょうか？
[B1] =IF(E1>0,F1,IF(E2>0,F2,IF(E3>0,F3,IF(E4>0,F4,A1))))
[B2] =IF(B1=A1,0,IF(E2>0,F2,IF(E3>0,F3,IF(E4>0,F4,G4))))
[B3] =IF(OR(B2=G4,B2=0),0,IF(E3>0,F3,IF(E4>0,F4,G4)))
[B4] =IF(OR(B3=G4,B3=0),0,IF(E4>0,F4,G4))
[B5] =IF(OR(B4=G4,B4=0),0,G4)

作業セル(E1:G4)
[E1] =QUOTIENT(A1,10000000) '
[E2] =QUOTIENT(G1,5000000)
[E3] =QUOTIENT(G2,3000000)
[E4] =QUOTIENT(G3,1000000)
[F1] =10000000*E1
[F2] =5000000*E2
[F3] =3000000*E3
[F4] =1000000*E4
[G1] =A1-F1
[G2] =G1-F2
[G3] =G2-F3
[G4] =G3-F4