別表参照の関数

質問

IF関数についての質問です

A1を顧客名としB1を受注数量とし、単価を200円とします。

=B1*200

下に挙げる2つのパターンについて
別で作ったプライス表を参照し、計算する式を作りたいのですが
上手くできずに困っています…

パターン　１
　A1＝山田さま or 田中さま @150円
　A1＝鈴木さま @180円
　A1＝その他顧客 @200円

パターン　２
　A1＝山田さま or 田中さま @150円
　A1＝鈴木さま @180円
　A1＝その他顧客
　注文数100個以下 @200円
　注文数100個以上 @160円

VLOOKUP？を使用した表を初めて作ってみたいのですが
詳しく教えて頂けませんでしょうか。

お願い致します。

回答No.6

bunjii · ベストアンサー

>もしよければ別シートバージョンも教えて下さい。
回答に記載の数式では括弧の個数に誤りがありました。
C1=IF(COUNTIF(E:E,A1),VLOOKUP(A1,E:F,2),200))*B1
　　　　　　　　　↓
C1=IF(COUNTIF(E:E,A1),VLOOKUP(A1,E:F,2),200)*B1

別シートを参照するときは次のように修正してください。
=IF(COUNTIF(E:E,A1),VLOOKUP(A1,E:F,2),200)*B1
　　　　　　　　　↓
=IF(COUNTIF(Sheet2!E:E,A1),VLOOKUP(A1,Sheet2!E:F,2),200)*B1
但し、価格表がSheet2と言うシート名でE列に特定顧客名、F列に単価が入力されているとします。

>ここに登場する(A1,E:F,2)の「2」という数字はどういう意味の2ですか？
VLOOKUP関数は複数列を指定したとき「1番左側の列に検索対象のデータがある」と言う条件になっています。
検索対象の範囲に検索値が見付かった行の左から1番目は検索値と一致した値であり、左から2番目の列の値を返すときはVLOOKUP(A1,E:F,2)のように第3引数で2を指定します。

回答No.7

bunjii

他の回答者への補足からの憶測ですが、パターン2の場合は次のような数式で良いと思います。
=B1*VLOOKUP(IF(COUNTIF(Sheet2!A:A,A1),A1,"その他"),Sheet2!A:C,IF(B1<100,2,3),FALSE)
検索方法のFALSEは省略可能のようです。
COUNTIF関数でA1セルの顧客名がSheet2のA列に存在するときはVLOOKUPの検索値をA1とし、存在しないときは"その他"を検索値に設定します。
また、戻り値をB1が100未満のときSheet2のB列の値を返し、100以上のときC列の値を返すようにできます。
尚、A1セルが空欄のときの処理は省略していますので必要なら次のように修正してください。
=IF(A1="","",B1*VLOOKUP(IF(COUNTIF(Sheet2!A:A,A1),A1,"その他"),Sheet2!A:C,IF(B1<100,2,3),FALSE))

回答No.5

mshr1962

>単価=IFERROR(VLOOKUP(顧客,プライス表!$A:$B,2,FALSE),200)
> の、　　　　　　　　　　　　　　　　　　　　　　↑2
> という数字はどういう意味ですか？

この場合は、A:B列の2列目（B列）の内容を表示という意味です。
ここが1の場合はA列の内容なので、顧客のセルの内容と同じものが表示されます。

回答No.4

kagakusuki

今仮に

>A1を顧客名としB1を受注数量

というデータが入力されているシートがSheet1であるものとします。
　又、プライス表を作表するシートがSheet2であるものとします。

　まず、Sheet2に次の様な表を作成して下さい。

　　　　　A列　　　　B列
1行目　　 顧客 　　　単価
2行目　　<100　　　 200
3行目　　>=100　　 160
4行目　　山田さま　　150
5行目　　田中さま　　150
6行目　　鈴木さま　　180

　その上で、

>A1を顧客名としB1を受注数量

というデータに対する価格を表示させるセル(C1セル？)には次の様な関数を入力して下さい。

=IF(ISNUMBER($B1),$B1*VLOOKUP(IF(COUNTIF(Sheet2!$A:$A,$A1),$A1,IF($B1<100,"<",">=")&100),Sheet2!$A:$B,2,FALSE),"")

回答No.3

bunjii

>注文数100個未満　@200円
>注文数100個以上　@160円
>でお願い致します。
貼付画像のような表の構成で検証しました。

パターン　１
C1=IF(COUNTIF(E:E,A1),VLOOKUP(A1,E:F,2),200))*B1
IF関数で特定の顧客（山田、田中、鈴木）の単価の対応表に対してVLOOKUP関数で単価の抽出を行います。
特定の顧客に含まれない顧客のとき単価を200とします。

パターン　２
C2=IF(COUNTIF(E:E,A1),VLOOKUP(A1,E:F,2),VLOOKUP(B1,H:I,2))*B1
パターン　１で特定の顧客以外の顧客のときは数量を基準に単価を抽出します。
数量が一致しないときは数量一覧の中から参照の数値より小さい最大値が検出結果になります。

補足

どうもありがとうございます！
分かり易そうで、やってみようと挑戦したら、価格表を別シートで作っていたので
びっくりマークに混乱して、、断念してしまいました。
もしよければ別シートバージョンも教えて下さい。

ちなみに
C1=IF(COUNTIF(E:E,A1),VLOOKUP(A1,E:F,2),200))*B1
ここに登場する(A1,E:F,2)の「2」という数字はどういう意味の2ですか？
教えて下さい。

回答No.2

mshr1962

プライス表
A＿＿＿B
顧客名　単価
山田さま　150
田中さま　150
鈴木さま　180
と別表があるとして

パターン1
単価=IFERROR(VLOOKUP(顧客,プライス表!$A:$B,2,FALSE),200)
パターン2
単価=MIN(IFERROR(VLOOKUP(顧客,プライス表!$A:$B,2,FALSE),200),IF(注文数<100,200,160))

※パターン2が複雑なのは、顧客が鈴木さまで注文数100個以上の時に、金額の逆転現象が起きないようにするためです。

もしくは
プライス表
A＿＿＿B＿＿＿C
顧客名　単価1　単価2
山田さま　150　150
田中さま　150　150
鈴木さま　180　160
その他　　200　160
と別表があるとして

パターン2
単価=VLOOKUP(IF(COUNTIF(プライス表!$A,顧客),顧客,"その他"),プライス表!$A:$B,IF(注文数<100,2,3),FALSE)

補足

ありがとうございます！
ちなみに
単価=IFERROR(VLOOKUP(顧客,プライス表!$A:$B,2,FALSE),200)
の、　　　　　　　　　　　　　　　　　　　　　　↑2
という数字はどういう意味ですか？

回答No.1

bunjii

>注文数100個以下 @200円
>注文数100個以上 @160円
正確には矛盾します。
注文数100個のときは両方の条件がマッチします。

注文数100個未満　@200円
注文数100個以上　@160円
または
注文数100個以下　@200円
注文数101個以上　@160円
何方にするかを補足してください。

補足

すいません！

注文数100個未満　@200円
注文数100個以上　@160円
でお願い致します。
ありがとうございます。