Excel関数で違った答えになってしまいます

質問

Excel関数で違った答えになってしまいます

Excelで商品の管理をしているのですが、
関数を入れても答えが違ってしまいます。

[Sheet1]・・・コード入力シート
(A1)(A2)・・・商品コード
(B1)(B2)・・・数量

[Sheet2]・・・データベースシート
(A列)・・・商品コード

とあり、データベースの価格を
数量により変化させます。

例えば
商品コード（as400）の価格を
数量100個以上なら10円、100個未満だと15円
としたいのです。

作成した関数は
IF(OR(AND('Sheet1'$A$1=A1,'Sheet1'$B$1>=100),AND('Sheet1'$A$2=A1,'Sheet1'$B$2>=100)),10,15)
としました。

ただこれだと、[Sheet1]の1行目は正しく表示されるのですが
2行目になると、数量を1個にしても価格が10円になってしまいます。

どのようにすれば良いか悩んでいます。
よろしくお願いします。

回答No.1

mt2008 · ベストアンサー

具体例が無いので誤解しているかもしれませんが、添付の図の様にSheet2のB列に単価を表示したいと言う事ですかね？

添付の図の場合、Sheet2のB1に↓と入れて下にオートフィルしています。

=IF(COUNTIF(Sheet1!A:A,A1)=0,15,10+5*(VLOOKUP(A1,Sheet1!A:B,2,FALSE)<100))

回答No.8

tom04

こんばんは！
横からお邪魔します。

色々良い回答は出ていますので、参考程度で目を通してみてください。
余計なお世話かもしれませんが、商品によって単価が変わる境界数に違いがあるのではないかと思います。

それに対応するための方法です。
↓の画像の右側がSheet2になります。こちらに表を作っておきます。
画像では勝手に商品コードを入れ、
as400は100個が境界・BB250は50個が境界・CD100は50個おきの境界としてみました。
実際必要な表は薄い水色部分だけですが、判りやすくするために
B列（未満）を表示しています。

そして、Sheet1のC2セルに
=IF(COUNTBLANK(A2:B2)>0,"",INDEX(Sheet2!$C$8:$E$11,MATCH(B2,Sheet2!$A$8:$A$11,1),MATCH(A2,Sheet2!$C$7:$E$7,0)))
という数式を入れ、オートフィルで下へコピーすると
数量によって単価が変化します。

以上、参考になれば良いのですが
的外れなら読み流してくださいね。m(__)m

お礼

>余計なお世話かもしれませんが、商品によって単価が変わる境界数に違いがあるのではないかと思います。

その通りです。
商品によって基準となる「単価」「単価が変わる境界数」がいろいろあります。
最初はINDEXやMATCHを使用する事も考えたのですが、
あまりにも数が多く、一覧表の作成だけで膨大な数になってしまいます。
だから数式で対応出来ればと思い、試行錯誤していました。

言葉足らずを補って頂きまして、ありがとうございました。

回答No.7

mt2008

ANo.1です。

> VLOOKUPを使うとなれば、必ずA列からの記述が必要ですよね？
> 例えばSheet1の商品コード入力がE列の5行目と6行目にあり
> 数量入力がF列の5行目と6行目となっていた場合、
> （A列や1行目には他の記述がある）
> どうすれば良いでしょうか

そんな事はありません。
Vlookupの2つ目の引数はセル範囲を指定します。私が提示したサンプルがA列B列全てを範囲としているに過ぎません。
Sheet1!A:B　を　Sheet1!E5:F6　に変えれば済む話です。

お礼

どうもヘルプの記述の
「左端の値」
というのを勘違いしていたみたいです。

本当に目から鱗の使い方をありがとうございました。

補足

数式を入力してみましたが、やはりダメでした。

[データベース設定Sheet2]
500個以上・・・1,110円
500個未満・・・1,140円

[入力Sheet1]
H10・・・商品コード1
H12・・・1（1つめの数量）
H14・・・商品コード2
H16・・・1（2つめの数量）

[計算結果Sheet3]
商品コード1：1個：1,140円
商品コード2：1個：1,110円

となってしまいます。
何故でしょうか？

回答No.6

esupuresso

＞例えば商品コード（as400）の価格を数量100個以上なら10円、100個未満だと15円としたいのです。
要するに数量に対して10円にするか15円にするかを設定したいのでしょうか？

だとするなら単純に【D2】セルを使って「=IF(C2>=100,"10円",IF(C2<=100,"15円",""))」という式になりますねぇ・・・？
Sheet1のA列「商品コード」の返し方は名前を定義しての方法でしょうか？
Sheet2のデータからですと「商品コード」しか入力されていないようですが・・・？

取り合えず単純な数式ですので間違っていましたらそのまま通過してください　＾＾；

お礼

他の方の回答にも返事したのですが、

シート1はあくまでも入力用
シート1で入力した値を基に
シート2のデータベースから値を抽出し
別シート（例えばシート3）に結果を出力し印刷

という流れで運用しています。

また商品や価格は変動しますので、上記数式では対応できません。
あくまでもシート2の価格列の数字を変更したいのです。

回答No.5

mt2008

ANo.1です。

> =IF(COUNTIF(Sheet1!A:A,A1)=0,15,10+5*(VLOOKUP(A1,Sheet1!A:B,2,FALSE)<100))
> の関数の後半
> 10+5*(VLOOKUP(A1,Sheet1!A:B,2,FALSE)<100)
> の部分の説明をして頂けないでしょうか？
> 前半部分の意味と、VLOOKUPの意味はわかるのですが・・・

説明不足でしたね。失礼しました。
論理式の結果のTRUE/FALSEがセル式では1/0として扱える事を利用しています。

まず、VLOOKUP(A1,Sheet1!A:B,2,FALSE)　で、商品コードに対応した数量を得ています。
この数量が100未満（＜100　が成立）なら　5*(VLOOKUP(A1,Sheet1!A:B,2,FALSE)<100)
の結果は5、100以上（＜100が不成立）なら0になります。
この結果に10を足して15あるいは10にしています。

お礼

なるほど、すごく良くわかりました。
ご親切にありがとうございました。

出来ればもう一つ、
VLOOKUPを使うとなれば、必ずA列からの記述が必要ですよね？
例えばSheet1の商品コード入力がE列の5行目と6行目にあり
数量入力がF列の5行目と6行目となっていた場合、
（A列や1行目には他の記述がある）
どうすれば良いでしょうか？

あつかましいお願いですが、どうぞよろしくご教授ください。

回答No.4

KURUMITO

データベースとなるシート2を整備することが必要でしょう。
Ａ列には商品コードを1行おきに入力します。
例えばas400の名前をＡ2セルに入力し、Ｂ1セルには0と、Ｃ1セルには100と入力します。また、Ｂ2セルには1個から99個までのときの価格で15と入力します。Ｃ2セルには100個以上のときの価格で20と入力します。
このように2行を使って1行目のＢ列から右の列には数量による価格が変わるその数量を、2行目には商品コードと数量が変わった時の価格を入力します。数量による価格の変動は仮にＥ列まで使用して設定することにします。
二つ目の商品コードについても3行目と4行目を使って入力します。
以下同様です。
なお、数量による価格の変動が無い場合には1商品当たり2行は使いますが数量を各行は空欄のままにします。

そこでシート1についてですがＡ列には商品コードをＢ列には数量を入力するとして、その時の価格をＣ列に表示させるとしたらＣ1セルには次の式を入力して下方にオートフィルドラッグコピーします。

=IF(OR(A1="",B1=""),"",IF(INDEX(Sheet2!A:E,MATCH(A1,Sheet2!A:A,0)-1,3)="",INDEX(Sheet2!A:E,MATCH(A1,Sheet2!A:A,0),2),INDEX(Sheet2!A:E,MATCH($A1,Sheet2!A:A,0),MATCH($B1,INDIRECT("Sheet2!A"&MATCH($A1,Sheet2!A:A,0)-1&":E"&MATCH($A1,Sheet2!A:A,0)-1),1))))

これで商品コードあたりで、数量当たりで、その価格を自由に設定でき、シート1ではそれが反映された形で価格が表示されます。

補足

Sheet2はすでにデータベースとしてほぼ完成しており、
価格列を増やすとその他の列に使用している関数も全て見直しとなります。

現在実務運用しながらなので、できればデータベースの価格列以外を触らずに解決したいと考えております。

ただMATCHとINDEXは忘れていました。
何か利用できるか考えてみます。

回答No.3

mt2008

No1です。
画像が上手く添付できなかったので再チャレンジ

お礼

すみません、
なぜか回答2が2つあり、補足がずれてしまいました。
本当に申し訳ございませんでした。

ひとつお伺いしたいのですが、
=IF(COUNTIF(Sheet1!A:A,A1)=0,15,10+5*(VLOOKUP(A1,Sheet1!A:B,2,FALSE)<100))
の関数の後半
10+5*(VLOOKUP(A1,Sheet1!A:B,2,FALSE)<100)
の部分の説明をして頂けないでしょうか？
前半部分の意味と、VLOOKUPの意味はわかるのですが・・・

よろしくお願いします。

補足

Sheet1はコード入力用のシートですので、行数は増えません。
Sheet2はデータベースとしてのシートです。

Sheet1で入力したコード及び数量を基に
Sheet2より「商品コード」「商品名」「数量」「価格」等を抽出
別シートにその内容を反映させて印刷する。

といった手順で使用しています。

書かれている
「シート1のA1がシート2のA1と一致していてシート1のB1が100以上、
もしくはシート1のA2がシート2のA1と一致していてシート1のB2が100以上の場合が10」
となるはずが、
「シート1のA2がシート2のA1と一致していてシート1のB2が100以下の場合でも10」
となってしまうのです。

書き忘れていたのですが、最初私が書いた関数はSheet2の価格の列に書いている関数です。

回答No.2

ann_dv

全て絶対参照で指定されていますので、当然の事ながら他のセルへその関数をコピペしても常にその結果はシート1のA1がシート2のA1と一致していてシート1のB1が100以上、もしくはシート1のA2がシート2のA1と一致していてシート1のB2が100以上の場合が10となります。

ご質問の内容からは、各シートのA列がどのような並びになっているのか判断できません。
もしもシート1とシート2が全く同一の並びになっているようであれば、ANDやORを使うまでも無く、
=IF((Sheet1!B1>=100),10,15)

をシート2のB1に登録した上でB列を下へコピペしてゆけばよろしいかと思いますが、シート1と2ではA列の並びが全く異なっているようであれば、vlookup関数等を使用する必要があると思います。

参考URL：

http://www11.plala.or.jp/koma_Excel/contents1/mame1006/mame100601.html

補足

すみません、
ANo.2が2つあり、mt2008様に返信してしまいました。
申し訳ございませんでした。

「シート1のA1がシート2のA1と一致していてシート1のB1が100以上、
もしくはシート1のA2がシート2のA1と一致していてシート1のB2が100以上の場合が10」
となるはずが、
「シート1のA2がシート2のA1と一致していてシート1のB2が100以下の場合でも10」
となってしまうのです。

よろしくお願いします。