Excel（エクセル）活用コミュの複数条件のある表の作成方法を教えて下さい。

エクセル２００７で請求金額一覧表を作らねばなりません。

A列→氏名、B列→金額、C列→会費、D列→振込み手数料、E列→合計金額。

A列の氏名が別表のリスト（Seet２）にある時のみC列の会費が発生します。

会費はB列の金額が5万円未満なら0円。
5万円以上10万円未満なら1000円。
10万円以上20万円未満なら1500円
20万円以上30万円未満ならB列の金額の10％。
30万円以上50万円未満なら4000円。
50万円以上なら5000円。

D列の振込み手数料は同行同支店、同行他支店、他行のときで、
更にB+Cの振込み金額によりそれぞれ3段階に分かれた金額となります。

IF関数でと思いやってみたのですがうまく行きません。
どうか良い方法を教えて下さい。

よろしくお願い致します。

コメント(17)

最初
全て
最新の40件

[1] mixiユーザー 06月04日 18:31

「上手くいきません」とありますが、どのような式を書いた結果、どうなったんでしょう？
質問に関する状況説明中で、このような曖昧な書き方は何のヒントにもならず、好ましくないと思いませんか？？

[2] mixiユーザー 06月04日 19:06

>D列の振込み手数料は同行同支店、同行他支店、他行のときで、
>更にB+Cの振込み金額によりそれぞれ3段階に分かれた金額となります。
これもどのような金額なのか不明ですね

会費はIF関数をネストして、しっかり
・A列の氏名が別表のリストにある場合、会費発生、そうでない場合、0
・B列の金額が5 万円未満　　　　　　　 =>　　 0円
　　　　　　　　　5 万円以上10万円未満 => 1000円
　　　　　　　　　10万円以上20万円未満 => 1500円
　　　　　　　　　20万円以上30万円未満 =>(B列の金額の10%)円
　　　　　　　　　30万円以上50万円未満 => 4000円
　　　　　　　　　50万円以上 => 5000円
という条件を書けば問題ないと思いますが
どのような式を書かれているのか不明なので・・・

[3] mixiユーザー 06月04日 19:20

Minonさん、
申し訳ございません。
質問の仕方が良くなかったです。

私の書いた式は・・・

=IF(F2="A",IF(B2>=500000,5000,IF(B2>=300000,4000,IF(B2>200000,B2*0.015,IF(B2>=100000,1500,IF(B2>=50000,1000,""))))))

私の知識ではSeet２の表にある氏名と同じものだけを抽出して
という事は出来なかったので印刷設定外のF列に数字の入るものだけを
Aと記入してこの記入が在る物のみ数字が入るようにしました。

ただこれだとF列に”A"が未記入で７５０００と金額が入るような時には
FALSEと記入されてしまいます。

また出来ればF列に私が記入した”A"は、この方法ではなく
Seet2の表と同じ氏名なら…と出来ればと思い質問させて頂きました。

D列の振込み金額も同じで私のレベルでは同行同支店→A、同行他支店→B,
他行→Cという欄をG列に作って記入し、
更にIF関数で金額によってわけるという
式かと思いますがもっと良い方法があるのではと思いましたので同じく
質問させて頂きました。

エクセルのHPなどもみて調べてるのですが良い方法が分かりません。
どうぞ宜しくお願い致します。

[4] mixiユーザー 06月04日 19:31

>ただこれだとF列に”A"が未記入で７５０００と金額が入るような時には
>FALSEと記入されてしまいます。
条件を満たさなかった(偽の)場合の処理がないからでしょう

>Seet2の表と同じ氏名なら
COUNTIF関数やMATCH関数などで、リストに存在するか確かめれば良いのでは？

[5] mixiユーザー 06月04日 19:47

マリ男さん、
お手数をおかけして申し訳ございません。
質問の仕方が不十分な事、反省していますl。

私の計算式はコメント３のとおりです。

また振込み手数料の事ですが
振込み手数料は同行の場合3万円以上は50円。
1万円以上3万円未満は20円。
1万円未満は0円。

同行他支店の場合、
3万円以上は400円。
1万円以上3万円未満は100円。
1万円未満は0円。

他行の場合　
3万円以上は700円。
1万円以上3万円未満は500円。
1万円未満は300円です。

それぞれの方々（A列）が振り込む銀行は決まっているので
後は振込み金額によって手数料を分ければよいのですが
この方々が毎回いるとは限らないのです。

出来れば別表を作って処理出来ればと思うのですが無理でしょうか?
もし良い方法がありましたら教えて下さい。
どうぞ宜しくお願い致します。

[6] mixiユーザー 06月04日 20:06

>出来れば別表を作って処理出来ればと思うのですが無理でしょうか?
基準となる金額と手数料を表にして、VLOOKUP関数を使うとかでしょうか？

[7] mixiユーザー 06月04日 20:14

>同行同支店、同行他支店、他行
はどのように判断するのでしょうかね？

[8] mixiユーザー 06月04日 20:16

マリ男さん、有り難うございます。

COUNTIF関数は条件にあう物を数えるだけの関数と思っていました。
早速調べて見ます。

VLOOKUP関数はきっとIF関数と組み合わせる形ですよね。
こちらもちょっと頑張って調べて見ます。

[9] mixiユーザー 06月04日 20:22

>COUNTIF関数は条件にあう物を数えるだけの関数
その通りですね、範囲内のセルのうち、条件に合うセルの個数を数えます
ですから、リストを範囲とし、氏名を条件とすると
リストに存在しない氏名が条件として入った場合、0になりますから・・・

[10] mixiユーザー 06月04日 20:29

マリ男さん、
同行同支店、同行他支店、他行は、
すでに誰がどの銀行かを書いた表があります。

例えば
佐藤さん・・・他行
山田さん・・・同行他支店　と言った表です。

ただ毎月同じ方に請求が発生するのならば
氏名別に表を作成して、後は金額に応じてその人の銀行に合った
割合をIF関数で処理すればよいので楽なのですが
そうではないので苦労しています。

もう少し頑張ってみます。

[13] mixiユーザー 06月05日 14:56

皆様、有り難うございます。
昨日よりずっとVOOKUP、COUNTIF,IF関数などを調べてるのですが
私のレベルでは理解不能です。

VLOOKUP関数の場合は「Seet2の氏名と一致した時、○列の数字を記入せよ」
だと分かるのですが「…氏名と一致した時、Seet1のB列の数字の値によって適応している数字を記入、または計算せよ」と言うのがどのように結びつけたらよいかが分かりません。

みしゃとさんの計算式もそれぞれの金額に応じての数字を抽出するのは分かるのですが、その前に「もしリストに名前があった場合は」と言う所をどう処理してよいか分かりません。

やはり初心者にこの処理は無理なのかも知れないと感じ始めています。
色々と有り難うございました。

[14] mixiユーザー 06月05日 17:22

>「もしリストに名前があった場合は」
という条件式が分からないということでしたら
前にもあったように、COUNTIF関数やMATCH関数で判断できるはずですが・・・
もしくは、VLOOKUP関数でもリストに名前があるかないか判断はできるはずですよ

COUNTIF関数なら返る数値、MATCH関数やVLOOKUP関数(検索の型がFALSEの場合)
ならエラーになるのかならないのかを見れば、リストに存在する名前なのか
判断はつくはずです

それをIF関数で存在する場合の処理と存在しない場合の処理を分岐させれば
出来るように思いますがね・・・

[17] mixiユーザー 06月05日 20:24

マリ男さん、みしゃとさん、
2日間も有り難うございます。

少し先が見えてきたように思えます。

まずは会費はSheet2のリストからVLOOKUPで
Sheet1の使用していない列にリストにあるものだけに「１」を記入。
次にSheet1の会費列にIF関数を使って「１」とあるときは・・・
として処理します。

同じように手数料も細かい表を作って
Sheet1で反映させるような方法をやってみます。

Sheet1に何も記入せず関数が作れるかを悩んでいたので
例えばSheet2のリストがA１からA100まで会員の氏名があったとして
VLOOKUPでも
=IF(VLOOKUP$A$2:$A$100 ・・・これでは絶対違うと思うし、
そのあとの金額によってのIF関数と同組み合わせて良いかが分からず
悩んでいました。

Sheet１に会員かどうかを入れる欄を作れば解消です。
=VLOOKUP(A2,Sheet2!A3:C7,3,1)　
そしてその列をEとすれば
=IF(E2=1,IF(B2>=500000,5000,IF(B2>=300000,4000,IF(B2>=200000,B2*0.1,IF(B2>=100000,1500,IF(B2>=50000,1000,0))))),0)
とすれば出来ました。

後はVLOOKUPの時にリストにないものに
#N/Aがでてるのでこれを修正すれば大丈夫と思います。

これはよそのHPで見た記憶がありますので
あとでもう一度調べて見ます。

色々と有り難うございました。
まだまだ勉強をしないとだめだと痛感しています。

ログインすると、残り4件のコメントが見れるよ