Excelの計算式で困っています。

質問

お世話になります。
まず、セル　A1　B1 　C1　D1（下方に連続）が有ります。
A1には（D1・D2・N・J・Dr）の5種類の文字列が限定で入り、B1には一定範囲の文字列が入ります。
C1には数字が入り、D1にD1・D2・N・J・Drの4種類の文字列に対して、対応する値をC1とかけ算した結果を表示したいのです。
それだけなら、IF関数で出来るのですが、セルA1～下の位置に文字列"D1・D2・N"の3種類がB1との3カ所以上有った場合、掛け率が違うように計算したいのです。
”D1&○○○（○は同じ文字と考えてください）”や”D2&○○○（○は同じ文字と考えてください）”や”N&○○○（○は同じ文字と考えてください）”が3つ以上有った場合、セルXの数値を掛ける。そうでない場合は、D1やD2やNに対応したそれぞれの掛け率（別セル・絶対番地）を掛けるように。また、J・Drは単純に掛け率のセルと掛けるという次の計算式を作ってみました。

IF($A1="J",$C1*$BB$4,
IF($A1="Dr",$C11*$BB$5,
IF($A1="D1",$C11*$BB$1,
IF($A1="D2",IF((SUMIF($AU$11:$AU$110,$B1,$BP$11:$BP$110)>=3),$C1*$BB$6,
IF($A1="N",IF((SUMIF($AU$11:$AU$110,$B1,$BP$11:$BP$110)>=3),$C1*$BB$6,
$P11*$BB$3)))))))
※SUMIFの検索範囲はセルA1&セルB1を表示させて、いくつ出たかを表示させる別番地になります。
BB1～BB6は掛け率を表しています。（後で変更が合ったときに式を全部変えなくても済むように）

それで問題点は、セルA1の位置に文字列”D1"や"D2”を入れると、正しく計算できるのですが、セルA1の位置に文字列”N"や"J""Dr"を入れると、”FLASE”が表示され、正しい答えが表示されません。

それと、IF関数に対してカッコの数が多いように思うのですが、この式を入れると、削っても勝手にExcelの方で、足されてしまいます。
また、BB1～BB3及びBB6は%表示、BB4～BB5は整数というのも何か関係が有るのでしょうか？

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
|　　A　　　　　　|　　　　B　　　　　　|　　　　C　　　　|　　 D　　　　　　　　　|
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
|　　D1 　　　　　|　　　○○○　　　 |　10000　　　　|　　 　 　2000　　　　　|
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
|　　D2　　　　　|　　 　○○●　　　|　20000　 　　　|　　 　　3000　　　　　|
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
|　　N　　　　　　|　　　○○○　　　|　15000　　 　　|　　 　　　1750　　　　　|
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
|　　D2　　　　　|　　　○○○　　　|　12000　　　　 |　　　　　 1800　　　　　|
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
|　　J　　　　　 |　　　○○● 　　|　　　 　1　　　 　|　 300　　　　　|
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
上記の内容で、B列の"○○○"と”D1”または”D2”または”N”の組み合わせが3カ所以上有った場合だけBB6、それ以外は、それぞれの”D1”なら”D1”に対応する掛け率を表示させたいのです。

解りにくい説明で申し訳ないのですが、何かヒントだけでも御願いできればと思います。
よろしくお願いします。

回答No.4

Nouble

再度ヒントです
添付映像が見られれば良いのですが
念のためクラウドサーバーにも置いておきますね
http://sdrv.ms/13vrth9

如何でしょうか？
お役に立てていたならば幸いです。

お礼

お礼が遅くなりまして、申し訳ありません。皆様のご回答を参考に、VLOOKUPやSUMPRODACTなどを組み合わせて、目的を達成することができました。ありがとうございます。3人の方の回答を参考にさせていただきましたので、ベストアンサーはなしとさせていただきます。本当は皆様に差し上げたいのですが、システム上できないので、悪しからず、ご了承いただければと存じます。

回答No.3

keithin

説明がヌケてますが、３つ以上無かったらBB1,BB2,BB3でいいんですね。
それとご利用のエクセルのバージョンがご質問に書かれてませんので、どのバージョンでも使えるかわりに重たい数式を回答しておきます。
Ｄ１
=IF(A1="","",C1*(IF(SUMPRODUCT((($A$1:$A$100="D1")+($A$1:$A$100="D2")+($A$1:$A$100="N"))*($B$1:$B$100=B1))>=3,$BB$6,
IF(A1="J",BB$4)+IF(A1="Dr",BB$5)+IF(A1="D1",BB$1)+IF(A1="D2",BB$2)+IF(A1="N",BB$3))))

具体的にどんな大きさのシートにしてるのかの情報もありませんので、実地で試すときは、不用意にばかでっかいセル範囲で計算しようとしないよう、注意して工夫してください。

＃補足
ＢＡ１からＢＡ６にD1からJまで、ＢＢ１～ＢＢ６に対応する記号を記入しておくといった工夫をすることで、こんなにＩＦばっかりじゃなくVLOOKUP関数とか使ってもっとスマートな数式に出来ます。今回のご質問の主眼じゃないので説明は省きますが、別途練習してみて下さい。

お礼

お礼が遅くなりまして、申し訳ありません。皆様のご回答を参考に、VLOOKUPやSUMPRODACTなどを組み合わせて、目的を達成することができました。ありがとうございます。3人の方の回答を参考にさせていただきましたので、ベストアンサーはなしとさせていただきます。本当は皆様に差し上げたいのですが、システム上できないので、悪しからず、ご了承いただければと存じます。

補足

早速のご回答有り難うございます。Excelのバージョンを記入するのをすっかり忘れてました。Excel(Office)2003です。質問をした後の画面でSUMPRODUCT関数の事を知り、検索して使いかを調べてました。
一応、お教えいただいた物を当てはめで入れたところ、うまく動作しました。
焦ってたので、はしょって書きましたが、実際は下が224行迄あり、
横は同じパターンが8列あるので、8列分×224行を検索して、同じ組み合わせが3有れば、それぞれ、全ての所で掛け率が変わるようにしたいので、
お教えいただいた式を元に、とりあえず、SUMPRODUCT関数を追加して作ってみます。

>ＢＡ１からＢＡ６にD1からJまで、ＢＢ１～ＢＢ６に対応する記号を記入しておくとい>った工夫をすることで、こんなにＩＦばっかりじゃなくVLOOKUP関数とか使っても
>っとスマートな数式に出来ます。

"D1”とか"N”といった文字列に意味がありまして、紙ベースで他人が書いた物を入力しますので、記号化してしまうと、一目見てどれか解りにくくなったり、間違えやすくなったりするので、あえてそのまま使いたいと思っています。

あと、VLOOKUP関数の使い方も勉強してみます。

一つだけよく解らないのですが、最初の「=IF(A1="","",C1*(I・・・　」で空欄の文字列を2回繰り返していますがこれはどのような意味があるのでしょうか？

いろいろ甘えてすみません。

回答No.2

Nouble

説明が不適切ですよ
A列は4行しかないのですか？
そんなことはなく
それ以上ありますよね？
5種類だけのものが15行にわたって記載されれば
確率的にはどれも3回記載されているとの期待値が持てますよね？

b列はB1だけなのですか？
何故B1だけ参照すれば良いか説明と食い違ってますよ、

A列には限定で入るとのことですが、
5つの内の1つだけ限定で入るのですか？
それとも複数入るのですか？
最大幾つまではいるのですか？

後エクセルのバージョンによっても回答が変りますよ

IF($A1="J",$C1*$BB$4,
IF($A1="Dr",$C11*$BB$5,
IF($A1="D1",$C11*$BB$1,
IF($A1="D2",IF((SUMIF($AU$11:$AU$110,$B1,$BP$11:$BP$110)>=3),$C1*$BB$6,
IF($A1="N",IF((SUMIF($AU$11:$AU$110,$B1,$BP$11:$BP$110)>=3),$C1*$BB$6,
$P11*$BB$3)))))))

AU、BP、P、等といった列が何を任されているか
ご説明にはない気がしますが
何故説明頂けないのですか？

私のことを何も聞かず知れと、
貴方がいわれて困るように

伝わるようにご説明頂けないと
貴方の考えを私たちも知り得ないのです。
困ってしまうのです。　よ
ご理解くださいね。

貴方の意識の中で至極当然のことも、
他の方は知らなかったり、
無意識下でこなしているため
認知できないこともあるのですよ。
はしょらずに全て書いてくださいね。

ご連絡をお待ちしていますね。

ところヒントです。
リプレースすると当然ながらａがｒｒになったりしますよね
ａｂｃｄｅ
中のａをｒｒにリプレースしたら
ｒｒｂｃｄｅ
一文字増えますよね？
でもこの時セルには書き込む必要は無いですよ。
文字数が増えたと解れば良いだけですから。

次に
ｂｃｄｅｆ
を同様にリプレースしようとしても
ｂｃｄｅｆ
のままですよね？
当然文字数も増えません。

では
各々D1・D2・N・J・DrをRRR、RRR、RR、RR、RRRに
リプレースするという条件の下
以下のものを処理したら文字数はどうなるでしょうか？

NIJKDrFGHD1
IIFESMORKER
JOKEASRANGE
DJASCOOLGOD

参考URL：

http://excel.onushi.com/function/replace.htm

お礼

お礼が遅くなりまして、申し訳ありません。皆様のご回答を参考に、VLOOKUPやSUMPRODACTなどを組み合わせて、目的を達成することができました。ありがとうございます。3人の方の回答を参考にさせていただきましたので、ベストアンサーはなしとさせていただきます。本当は皆様に差し上げたいのですが、システム上できないので、悪しからず、ご了承いただければと存じます。

補足

早速のご回答、有り難うございます。Excel(Office)2003です。

>5つの内の1つだけ限定で入るのですか？→5種類の文字列が限定で1つだけ入ります。

>AU、BP、P、等といった列が何を任されているか→※SUMIFの検索範囲はセルA1&セルB1を表示させて、いくつ出たかを表示させる別番地になります。つまり、A列にはいるD1やD2等とB列に表示させる○○○を別番地で&を使って1セルに表示させて、SAMIFの検索対象にしようと考えていました。

回答No.1

mekuriya

何回読んでも何をやりたいのかさっぱり分かりませんが、そもそもIF関数を使うのが間違っています。関数が複雑になりすぎる時はたいてい使う関数が適切でないか、データ構造に無理があって例外処理が多すぎるかどちらかです。EXCELはなまじ高機能なだけに何でもできそうな気がしてしまうものですが、本質的には表計算ソフトに過ぎません。

まずお願いしたいのは質問者さんの立場を教えて下さいってことです。学者なのか事務要員なのか、業務なのか趣味なのか。そういう背景がわからないと、結局何をやろうとしているのか当方は見えて来ません。事務処理なのか経理処理なのか統計分析なのか。場合によっては、そもそもEXCELで実現しようとすることに無理がある場合もありますし、関数でやろうとすることに無理がある場合もあります。

何事もうまくいかない時は出発地点に戻って、０から方針を見直す必要があるのです。

補足

ご回答有り難うございます。事務で、歩合式の給与計算と利益計算の目的で作っています。あまり細かいことは書きたくないのですが、列Aは歩合の種類（掛け率）を選択します。B列は人名です。C列は獲得金額、D列は具体的な金額（計算結果）です。今までは、3本以上で掛け率が変わるようなシステムでは無かったので、問題なく動作していましたが、最近システムが変わって、色々調べて作っていましたが、どうしてもうまくいかず、皆様のお知恵を拝借している次第です。