EXCEL関数で数量別単価表から該当する単価を表示させる方法が知りたい

質問

Sheet1に下記のような数量別単価表があります。

品番 数量(個) 単価(円)
001 100-199 500
001 200-299 450
001 300-399 420
001 400-499 360
001 500-599 300
001 600-　　　　 220

002　 300-599　　　90
002　 600-999　　　88
002　 1000-1499　　85
002　 1500-1799　　81
002　 1800-1999　　80
002　 2000-　　　　　79
・・・以下品番500程度まであり、それぞれ数量と単価の設定が変わってきます。この表を元にSheet2のセルに品番と数量を入力すれば、自動的に条件にあう単価を表示させる方法がわからず困っております。お知恵をお貸し下さい。
※品番001の数量「600-」の表記は600個以上はすべて単価220円という意味です。

回答No.9

Wendy02 · ベストアンサー

#8のWendy02です。

一応、確認を待たずに、ユーザー定義関数のVLOOKUPに似たものを作って完成しています。ただ、少し大きめなのと、最近、私のミスが多くなってきておりますので、動作試験をしばらく繰り返します。
今の段階では、まったく問題は出ていません。イレギュラーなパターンを探して、試しています。

#←ここが問題*
という問題は、こちらで解決させてしまいました。

#変更されたデータと、そうでないベタ・データでは、段違いにベタのほうが難しいです。

これは無視してください。

#今のデータは、以下のようなベタで打ち出したような状態になっているのですか？

なお、ベタのデータが、どのぐらいあるのかは、まだ気になるところです。たぶん、1万行程度は、まったく問題がないと思います。一応、品番は、並べ替えて、同じものが並んでいるという条件です。

なるべく見切り早いうちに、掲示します。お使いになるかは別問題ですが。

お礼

お返事が遅くなり申し訳ございませんでした。
せっかく「ユーザー定義関数のVLOOKUPに似たものを作って完成」
までしていただいて申し訳ございませんが、検索していましたら
別の方法が載っておりました。
・数量の最小と最大をB列とC列にわける（単価はD列）
・Sheet2の単価を求める数式を
=SUM(IF((Sheet1!$A$2:$A$99=A2)*(Sheet1!$B$2:$B$14<=B2)*(Sheet1!$C$2:$C$99>=B2),Sheet1!$D$2:$D$99))
　として配列数式にする

この方法で万事うまくいきました。
お手数をお掛けして申し訳ございませんでした。
ありがとうございました。

回答No.8

Wendy02

こんにちは。

>単価がでずに、＃REF!となります。なぜでしょうか？

INDIRECT の中の記述がおかしいのでしょうね。

VLOOKUP(B2,INDIRECT("_"&A2),2)

登録した名前と記述とかあっていないとか？

>全商品で約500品
>これはやはり全品とも品番で名前登録するしかないでしょうか？

元の質問の
>以下品番500程度まであり

というのを私は、読み落としていました。私の方法ではお勧めできません。グループとしては少数に限ります。

名前登録では、メモリ配分が悪くなります。それは、OSとExcelのバージョンに依存しますが、直接、VLOOKUPに影響が出るかと思います。

全面的に、設計じたいを替えたほうがよいです。ちまちまと、規定の関数でやって追いつかないように思います。もう、マクロ処理しかないと私は思います。今、いくつか案は持っています。

それでもよいのでしたら、私のほうで進めます。
それと、以下の確認が必要です。

今のデータは、以下のようなベタで打ち出したような状態になっているのですか？
確実になっているかどうか、それによっても話が違っています。

品番 数量 単価
A222 1-99 1110
A222 100-199 1000
A222 200-299 930
A222 300-499 890
A222 500-799 880
A222 800-900 850　←ここが問題*
A223 1-999 78
A223 1000-2999 74
A223 3000-4999 70
A223 5000-9999 67
A223 10000-19999 63
A223 20000-20001 59　　←ここが問題*
A224 1-299 300　　　　←ここが問題*

データの認識としては、その示した部分が、それ以上は、値段が変わらないのなら、

A222 800-　 |850　
A223 20000- | 59　
A224 1-　　 |300　

（見にくいので、「|」区切り線を入れましたが、つまり、数量は、1- 上限がない、というようになっていることです)

という規則性がほしいですが、500ともなると、それ自体をマクロ処理しないといけないようです。
変更されたデータと、そうでないベタ・データでは、段違いにベタのほうが難しいです。

一応、よかったら確認のレスをつけてください。

回答No.7

Wendy02

#6 の Wendy02です。
読み落としました。

>品番001の数量「600-」の表記は600個以上はすべて単価220円という意味です。

だったら、上限を超えたときの処理はいりませんね。

A222　　　901　　　xxx　削除
A223　　20002　　　xxx　削除
A224　　 300　 　　xxx ←300(円)に換える。

A224 は、数式的には、ダブってしまっていますが、一応、目安のために、そのままで良いと思います。

_A222, _A223 の名前の登録の参照範囲は、狭めてください。

当然、エラー処理は、VlookUp が、IsError にならない限りは、必要ありません。

=IF(ISERROR(C2),"",B2*C2)

ぐらいでよいでしょうね。

回答No.6

Wendy02

こんばんは。

このように書き換えたほうが見やすいですね。
xxx は、単に上限を超えたときのエラー値を出すためのダミーです。

　A　　　　B　　　　C
品番　　　数量　　　単価
A222　　　　0　　　　 0
　　　　　　1　　　1110
　　　　　100　　　1000
　　　　　200　　　930
　　　　　300　　　890
　　　　　500　　　880
　　　　　900　　　850
　　　　　901　　　xxx

品番　　　数量　　　単価
A223　　　0　　　　 0
　　　　　1　　　　 78
　　　　1000　　　 74
　　　　3000　　　 70
　　　　5000　　　 67
　　　　10000　　　63
　　　　20000　　　59
　　　　20002　　　xxx

品番　　　数量　　　単価
A224　　　0　　　　　0
　　　　　1　　　　300
　　　　 300　 　　xxx

数が増えたりするいけないので、本当は、それぞれの商品群の間は空けておいたほうが良いかもしれませんね。

次に、品番で名前登録します。しかし、A222 とかいうのは、セル名と同じになりますが、そのまま登録しようとすると、デフォルトで、「_ （アンターバー)」が付けられます。

[挿入]－[名前]－[定義] で、
それぞれを登録していきます。

名前　　　　　参照範囲
_A222　　　　Sheet1!$B$2:$C$9

_A223　　　　Sheet1!$B$12:$C$19

_A224　　　　Sheet1!$B$22:$C$24

と、品名の部分は入れないで、数量と単価の部分だけを参照範囲に入れます。

後は、Sheet2のシートに、このように入れれば、

A　　　B　　　C　　　　　　　D
品名　数量　　単価　　　　　
A222　 150　　=VLOOKUP(B2,INDIRECT("_"&A2),2)

単価は出てきます。D列の計算のところで、文字(xxx)が返った時のエラー処理をしてください。上限が超えております。例：=If(IsText(C2),"",B2*C2)

補足

ごめんなさい、実際に記載いただきましたとおりにやってみましたが、
単価がでずに、＃REF!となります。なぜでしょうか？
また、全商品で約500品あるのですが、これはやはり全品とも品番で名前登録するしかないでしょうか？

回答No.5

jakotama92

ＶＬＯＯＫＵＰを使った表にしたいんです。

（シート１）
　　　　 Ａ　　 　 Ｂ 　　　 Ｃ　　　 　 Ｄ　　　 　　Ｅ
１行目　品番　　数量　　単価　　計算式１　計算式２
２行目 A222 　 100-199 　500
３行目 A222 　 200-299 　 450
４行目 A222 　 300-399 　420
５行目

（計算式１）=VALUE(RIGHT(A2,3)&LEFT(B2,find("-",B2,1),-1))
（計算式２）=C2

計算式１は品番がＡ○○○しかないとして計算式を作っていますので、
他にもある場合はＡ２の値を拾う計算式を考えてください。

（シート２）
　　　　　　Ａ　　　　　 Ｂ　　　　　 Ｃ　 　　　　Ｄ
１行目　品番入力　 数量入力　 計算式３　 計算式４

（計算式３）=VALUE((RIGHT(A1,3)&B1))
（計算式４）=VLOOKUP(C1,Sheet1!$D:$E,2,TRUE)

計算式３は計算式２と同様に、品番がＡ○○○しか無いとして作っています。　　　　　　　　　　　以上

お礼

・数量の最小と最大をB列とC列にわける（単価はD列）
・Sheet2の単価を求める数式を
=SUM(IF((Sheet1!$A$2:$A$99=A2)*(Sheet1!$B$2:$B$14<=B2)*(Sheet1!$C$2:$C$99>=B2),Sheet1!$D$2:$D$99))
　として配列数式にする

この方法で万事うまくいきました。
お手数をお掛けして申し訳ございませんでした。
ありがとうございました。

補足

実際にやってみましたが、Sheet1のD2セルに計算式1を貼り付けようとした所、「この関数に対して、多すぎる引数が入力されています。」とエラー表示されてしまいます。
また、品番はご指摘の通り、A000～Z000まで使用しております。
>Ａ２の値を拾う計算式を考えてください。
こちらは具体的にはどのようにすればいいでしょうか？
初心者で申し訳ございませんが、よろしくお願いいたします。

回答No.4

noname#204879

参考にならないかも知れないが、私なら次のようにします。(フォントサイズを小さくして、画面をいっぱいに広げてご覧下さい)

Sheet1
　 A　　　　B　　 C　　 D　　E　　F　　G　　H　　I　　 J　　 K　　　L　　　M　　　N
1　品番↓　　 1　 100　200　300　500　800　901　1000　3000　5000　10000　20000　20002
2　A222　　1110　1000　930　890　880　850　　X　　 X　　 X　　 X　　　X　　　X　　　X
3　A223　　　78　　78　 78　 78　 78　 78　 78　　74　　70　　67　　 63　　 59　　　X
4　A224　　 300　 300　300　　X　　X　　X　　X　　 X　　 X　　 X　　　X　　　X　　　X
5　…

Sheet1 の「数量別単価表」を上のように書き替えます。ただし、データは[No.2回答に対する補足]に示されたものを使用しています。

Sheet2
　　 A　　 B　　　C
1　 品番　数量　 単価
2　 A222　　 23　1110
3　 A222　　123　1000
4　 A222　　234　 930
5　 A222　　345　 890
6　 A222　　567　 880
7　 A222　　890　 850
8　 A222　　900　 850
9　 A222　 1234　　 X
10　A223　　567　　78
11　A223　 2345　　74
12　A223　 3456　　70
13　A223　 7890　　67
14　A223　12345　　63
15　A223　20001　　59
16　A223　23456　　 X
17　A224　　123　 300
18　A224　　300　　 X

C2: =HLOOKUP(B2,Sheet1!B$1:N$500,MATCH(A2,Sheet1!A:A,0))

お礼

ありがとうございます。
商品数量が約500品あって、その1品ごとに数量の設定（何個から何個までがいくら）が、全くバラバラなのです。
数量欄を全品共通にしてしまうと、Sheet1の表作成に膨大な時間がかかってしまうかも。。

回答No.3

zap35

＃０１です。
すみません。数量の桁は最大５でしたね。以下の式に置き換えて下さい。なお「2000-　」の最大数は入れなくても計算します（式で使っていません）

=INDEX(Sheet1!C1:C13,MATCH(A1&TEXT(B1,"00000"),Sheet1!A1:A13 &TEXT(LEFT(Sheet1!B1:B13,FIND("-",Sheet1!B1:B13)-1),"00000"),1))

お礼

やってみたのですが、やはりエラーになってしまいました。
・数量の最小と最大をB列とC列にわける（単価はD列）
・Sheet2の単価を求める数式を
=SUM(IF((Sheet1!$A$2:$A$99=A2)*(Sheet1!$B$2:$B$14<=B2)*(Sheet1!$C$2:$C$99>=B2),Sheet1!$D$2:$D$99))
　として配列数式にする

この方法で万事うまくいきました。
お手数をお掛けして申し訳ございませんでした。
ありがとうございました。

回答No.2

zap35

＃０１です
＞実際には品番はA205のように、ローマ字1文字＋3桁の数字としており
＞数量は1桁から最大5桁まで商品ごとにあります

式は以下のようになると思います。Shift＋Ctrl＋Enterで確定です。

=INDEX(Sheet1!C1:C13,MATCH(A1&TEXT(B1,"0000"),Sheet1!A1:A13 &TEXT(LEFT(Sheet1!B1:B13,FIND("-",Sheet1!B1:B13)-1),"0000"),1))

注意事項：各品目の数量は必ず「1-xxx」のように数値は１から始まっていないと、前の品目の単価を引っぱってくることがあります。それをご了承ください
「2000-　　」のように上限値が空白なのでそれを関数で処理するにはつらいのです。

本当であればマクロで処理するのがベストだったかもしれません

お礼

ANo.3に同じです。

補足

品番 数量 単価
A222 1-99 1110
A222 100-199 1000
A222 200-299 930
A222 300-499 890
A222 500-799 880
A222 800-900 850
A223 1-999 78
A223 1000-2999 74
A223 3000-4999 70
A223 5000-9999 67
A223 10000-19999 63
A223 20000-20001 59
A224 1-299 300
・・・・・・・・・・・・
という具合に数量を1からはじめ、かつ数量に上限を設けてやってみましたが、一度最後の行の数値を検索してしまうと、品番を入力しなおしても、数量を入力しなおしても、最後の行の単価が表示されっぱなしになります。
この数式は仕入れの発注書に単価を明記する際に、利用する予定です。
なので、発注のたびに品番・数量を変更して入力すれば、単価が自動的に表示される仕組みを作りたかったのです。
やはり関数では限界でしょうか？

回答No.1

zap35

表が品番、数量で並び替えされている前提で、かつ数量は最大４桁とするとき、

Sheet2のA1に品番（001でも1でも可）、B2に数量を入力して、C1に以下の式をペーストしShift＋Ctrl＋Enterで確定（配列数式にします）

=INDEX(Sheet1!C1:C13,MATCH(TEXT(A1,"000")&TEXT(B1,"0000"),TEXT(Sheet1!A1:A13,"000") &TEXT(LEFT(Sheet1!B1:B13,FIND("-",Sheet1!B1:B13)-1),"0000"),1))

ただし品番001で数量<99の場合はエラーとなります。また品番003以降があると最後の行のデータを検索します。ですからダミーで品番003、数量0-1、単価0の行を入れて下さい。

お礼

ANo.3に同じです。

補足

早速ありがとうございます。
教えて頂いたとおりに試してみましたら無事出来ました。
ただ実際には品番はA205のように、ローマ字1文字＋3桁の数字としており、このような品番が約500アイテム分ございます。
また数量は1桁から最大5桁まで商品ごとにあります。単価は2桁～4桁までです。
以上の条件でも作動させるには、教えて頂いた数式のどの部分を修正すればいいでしょうか？
度々すみませんがよろしくお願い申し上げます。