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


Sheet1に下記のような数量別単価表があります。
品番 数量(個) 単価(円)
A222 50-99 1110
A222 100-199 1000
A222 200-299 930
A222 300-499 890
A222 500-799 880
A222 800-900 850
A223 500-999 78
A223 1000-2999 74
A223 3000-4999 70
A223 5000-9999 67
A223 10000-19999 63
A223 20000-25000 59
A224 1-299 300



品番はA000~Z999までの組み合わせで、約500アイテムあります。
500アイテムごとに、数量の設定がバラバラです。
この表を元にSheet2のセルに品番と数量を入力すれば、自動的に条件にあう単価を表示させる方法がわからず困っております。
この数式は仕入れの発注書に単価を明記する際に、利用する予定です。
なので、発注のたびに品番・数量を変更して入力すれば、単価が自動的に表示される仕組みを作りたいのです。
どなたかお知恵をお貸し下さい。



回答の条件
  • 1人2回まで
  • 登録:2006/09/11 12:57:14
  • 終了:2006/09/11 21:40:57

ベストアンサー

id:gillsderais No.4

gillsderais回答回数50ベストアンサー獲得回数42006/09/11 13:50:30

ポイント200pt

私ならこうするというので書いてみます。

Sheet1

A B C

1 品番 数量   単価

2 A222 50-99 1110

3 A222 100-199 1000

99 A229 1-299 300

(最終行を99としましたが、お持ちのデータに合わせて下さい)

まずB列とC列に一列挿入します。そしてB列を選択し、

メニュー「データ」→「区切り位置指定ウィザード」で、B列を「-」を境にB列とC列に分けます。


Sheet1

A B C D

1 品番 最小 最大 単価

2 A222 50 99 1110

3 A222 100 199 1000

99 A229 1 299 300


つぎにsheet2でA2、B2に品番と数量を入力するとC2に単価が出るようにします。


Sheet2

A B C

1 品番 数量 単価

2 A225 178 <数式>


条件にあった単価を抜き出すために、<数式>に次の配列数式を入力してください。


=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))

(配列数式とするため、入力後はCtrl+Shiftを押しながらEnterで確定してください。)


配列数式については、http://t_shun.at.infoseek.co.jp/My_Page/Excel-REF/ref_page4-1.ht...を参照ください。


注意)

1.上記の配列数式は、条件にあったものの合計を計算するようになっていますので、Sheet1の単価表には品番と数量の組合せが重複しないようにしてください。

たとえば、

A222 50 99 1100

A222 70 150 1000 など

このときSheet2にA222を80個と入力すると単価は2100になってしまいます。

2.条件に合うものがないと単価は0になります。

3.数式を工夫すればSheet1で「列の挿入」、「区切り位置指定ウィザード」をしなくてすむかもしれません。

id:gni8

gillsderais様

ありがとうございます。お返事が遅くなりすみませんでした。

すばらしいです!なんの迷いも無く疑問も無く、

おっしゃる通りにやってみたら完璧にできました。

このやり方でさせていただきます。ありがとうございます。

2006/09/11 21:30:40

その他の回答(5件)

id:taknt No.1

きゃづみぃ回答回数13539ベストアンサー獲得回数11982006/09/11 13:39:01

ポイント18pt

このような仕様だと VBAを使わないと難しいかと思います。

Sheet2のところに 以下のVBAを貼り付ければいいですね。

ちなみに Sheet2は A列 品番 B列 数量 C列 単価という構成でやってます。

Sheet1も 順に A列 B列 C列という割当です。


Private Sub Worksheet_Change(ByVal Target As Range)

'範囲外は、何もしない

If Target.Column > 2 Then Exit Sub

'品番と数量が入っていないときは 何もしない

If Cells(Target.Row, 1).Value = "" Then Exit Sub

If Cells(Target.Row, 2).Value = "" Then Exit Sub

d = CLng(Cells(Target.Row, 2).Value)

For a = 2 To 65536

'何も入っていないセルがあったら、終了

If Worksheets("Sheet1").Range("Sheet1!A" & a) = "" Then Exit For

If Cells(Target.Row, 1).Value = Worksheets("Sheet1").Range("Sheet1!A" & a) Then

b = Worksheets("Sheet1").Range("Sheet1!B" & a)

c = InStr(b, "-")

d1 = CLng(Left(b, c - 1))

d2 = CLng(Right(b, Len(b) - c))

If d1 <= d And d <= d2 Then

Cells(Target.Row, 3).Value = Worksheets("Sheet1").Range("Sheet1!C" & a)

Exit For

End If

End If

Next a

End Sub

id:gni8

ありがとうございます。お返事が遅くなりすみませんでした。自分はVBAの知識が全く無くてわかりませんでした。すみません。

2006/09/11 21:31:35
id:takemori No.2

takemori回答回数29ベストアンサー獲得回数22006/09/11 13:49:05

ポイント50pt

ツール→マクロ→VisualBasicEditorを開きます

左の”プロジェクト”画面でVBAProjectを右クリック

挿入→標準モジュールをクリック

Module1が出来ると思うのでそれを開きます

そこに↓を貼り付け

Function MYFUNC(number As String, count As Integer)

Dim myArray As Variant

Dim i As Integer

i = 2

While Worksheets("Sheet1").Range("A" & i).Value <> ""

If number = Worksheets("Sheet1").Range("A" & i).Value Then

myArray = Split(Worksheets("Sheet1").Range("B" & i).Value, "-")

If myArray(0) <= count And myArray(1) >= count Then

MYFUNC = Worksheets("Sheet1").Range("C" & i).Value

Exit Function

End If

End If

i = i + 1

Wend

MYFUNC = "見つからなかった場合の数値、メッセージ"

End Function




するとMYFUNCというオリジナル関数が使えるようになります

Sheet2の単価を表示させたい部分を↓のようにする

=MYFUNC(品番のセルを指定,数値のセルを指定)

=MYFUNC(A2,B2)のような感じになります。

これでOKかな?

id:gni8

ありがとうございます。お返事が遅くなりすみませんでした。

VBAのやり方がまったくわからないのですが、とりあえずおっしゃるままにやってみたのですが、なぜかうまくいきませんでした。すみません。

2006/09/11 21:32:30
id:kaiton No.3

kaiton回答回数260ベストアンサー獲得回数342006/09/11 13:48:08

ポイント100pt

まず、以下の表のようにします。

(品番の桁数は全部4桁で同じを想定)

A B C D

A222 50 1110

A222 100 1000

A222 200 930

A222 300 890

A222 500 880

A222 800 850

A223 500 78

A223 1000 74

A223 3000 70

A223 5000 67

A223 10000 63

A223 20000 59

A224 1 300

必ず、品番順、単価順の昇順に並び替えします。

C2に =A2&"_"&TEXT(B2,"0000000") この式を下にコピー

A222_0000050 のようにC列に表示されると思います。

Sheet2のA,B列に品番、数量があるとすると、

Sheet2のC2に =A2&"_"&TEXT(B2,"0000000")

Sheet2のD2に =VLOOKUP(C2,Sheet1!C2:D100,2,TRUE)

これで、希望の単価が出ると思います。

適宜、検索用の列は非表示にするとデザイン上はきれいでしょう。

要点は、

  • 品番と単価を合わせた検索用の列を作ること
  • 単価の桁合わせのためにTEXT関数を使うこと
  • VLOOKUP関数の検索の方を true にした
  • 品番と単価を明確に分けるために間に_記号をつけた

http://www.eurus.dti.ne.jp/yoneyama/Excel/kansu/vlookup.htm

http://www.relief.jp/itnote/archives/000431.php

品番が少ない(数個)なら、以下の方法も参考になると思いますが、500アイテムあるので、無理ですね。

http://pc.nikkeibp.co.jp/pc21/tech/excel36/27/index.shtml

id:gni8

ありがとうございます。お返事が遅くなりすみませんでした。シンプルさで比較させていただき、回答者4様の案を採用させていただきました。すみません。

2006/09/11 21:33:52
id:gillsderais No.4

gillsderais回答回数50ベストアンサー獲得回数42006/09/11 13:50:30ここでベストアンサー

ポイント200pt

私ならこうするというので書いてみます。

Sheet1

A B C

1 品番 数量   単価

2 A222 50-99 1110

3 A222 100-199 1000

99 A229 1-299 300

(最終行を99としましたが、お持ちのデータに合わせて下さい)

まずB列とC列に一列挿入します。そしてB列を選択し、

メニュー「データ」→「区切り位置指定ウィザード」で、B列を「-」を境にB列とC列に分けます。


Sheet1

A B C D

1 品番 最小 最大 単価

2 A222 50 99 1110

3 A222 100 199 1000

99 A229 1 299 300


つぎにsheet2でA2、B2に品番と数量を入力するとC2に単価が出るようにします。


Sheet2

A B C

1 品番 数量 単価

2 A225 178 <数式>


条件にあった単価を抜き出すために、<数式>に次の配列数式を入力してください。


=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))

(配列数式とするため、入力後はCtrl+Shiftを押しながらEnterで確定してください。)


配列数式については、http://t_shun.at.infoseek.co.jp/My_Page/Excel-REF/ref_page4-1.ht...を参照ください。


注意)

1.上記の配列数式は、条件にあったものの合計を計算するようになっていますので、Sheet1の単価表には品番と数量の組合せが重複しないようにしてください。

たとえば、

A222 50 99 1100

A222 70 150 1000 など

このときSheet2にA222を80個と入力すると単価は2100になってしまいます。

2.条件に合うものがないと単価は0になります。

3.数式を工夫すればSheet1で「列の挿入」、「区切り位置指定ウィザード」をしなくてすむかもしれません。

id:gni8

gillsderais様

ありがとうございます。お返事が遅くなりすみませんでした。

すばらしいです!なんの迷いも無く疑問も無く、

おっしゃる通りにやってみたら完璧にできました。

このやり方でさせていただきます。ありがとうございます。

2006/09/11 21:30:40
id:llusall No.5

llusall回答回数505ベストアンサー獲得回数612006/09/11 16:40:19

ポイント18pt

ユーザ定義関数+ADO+ExcelODBCドライバ で実現できます。

=GetTanka(品番,数量)

という関数を作ってみます。


以下、サンプルです。

1.ユーザ定義関数の作成

VBAのエディタを起動し、標準モジュールを追加。

以下のコードを記述してください。

Public Function GetTanka(ByVal hinban, ByVal suryo) As Variant

        Const DB_SHEETNAME As String = "Sheet1" 'データベースのシート名

        Dim cn As ADODB.Connection

        Dim rs As ADODB.Recordset

        Dim xl_file As String

        Dim sql As String

        sql = "Select [単価] from [" & DB_SHEETNAME & "$] Where 品番 = '" & hinban & "'"

        sql = sql & " And 数量FROM <= " & suryo & " And " & suryo & " <= 数量TO"

        xl_file = ThisWorkbook.FullName

        Set cn = New ADODB.Connection

        cn.Provider = "MSDASQL"

        cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & xl_file & "; ReadOnly=False;"

        cn.Open

        Set rs = New ADODB.Recordset

        rs.Open sql, cn, adOpenStatic

        

        If rs.EOF Then

            GetTanka = "なし"

        Else

            GetTanka = rs(0).Value

        End If

        rs.Close

        cn.Close

End Function

※VBAのエディタのメニューの[ツール]-[参照設定]で、「Microsoft ActiveX Data Objects 2.x Liblary」がチェックされていない場合は、最新の「2.x」をチェックオンしてください。



2.Sheet1 にデータベースを作成

 
品番数量FROM数量TO単価
A22250991110
A2221001991000
A222200299930

・・・以下、省略



3.Sheet2 にユーザ定義関数を記述

 
品番数量単価
A22250=GetTanka(A2,B2)


4.C列には、「単価」が表示されます。

id:gni8

ありがとうございます。お返事が遅くなりすみませんでした。

VBAのところのやり方がわからずうまくいきませんでした。すみません。

2006/09/11 21:34:45
id:llusall No.6

llusall回答回数505ベストアンサー獲得回数612006/09/11 17:22:55

「配列数式」なんていうのがあるんですね。

初めて知りました。

勉強になります。

でも、難しくて見ても良く理解できませんでした。orz

必要に迫られたときに、良く読んでみようと思います。


>rikuzaiさん。

回答つけてみましょうよ。


私も少し様子を見ていたのですが、いろいろな方法があるようで、面白そうなので回答してみました。

id:gni8

5に同じです。

2006/09/11 21:36:05
  • id:taknt
    質問にあるような範囲指定は、Excelの関数では 対応していないと思いますよ。
  • id:kaiton
    2の回答の補足です。
    品番:A223で数量100だと、品番:A222の800-900の単価が検索されます。
    また、数量を超過した場合(例:品番:A222で1000)には最大のレンジの単価が検索されます。
    WinXp+Excel2000で検証しています。
  • id:kaiton
    2ではなく3の補足でした。m(__)m
    回答では、表の空白が詰められていますが、単価がD列としてその前に数式をいれてください。
    http://www.eurus.dti.ne.jp/yoneyama/Excel/kansu/vlookup.htm
    のVLOOKUP関数で「[B]検索値が数値範囲の場合(検索の型:TRUE)」に準じた方法を使っています。
    ただ、前の補足のような不具合があるので、数量:0~は単価:エラーのデータでも登録する。
    そして、上限もあるなら上限以上も単価:エラーとでもしておくと、数量の入力ミスを少しでも防げると思います。

    数量の範囲:50-99の列から50だけの列にするのは、後ろに1列追加し、数量の列を「データ」の「区切り位置」の「カンマやタブ..」でその他の"-"で区切れば500件ほどのデータがすぐに準備できます。

    回答になれていないので..何度もコメントするかもしれません m(__)m
  • id:gillsderais
    4の回答者です。
    表をスペースで作ったものですから、レイアウトがグチャグチャですね。一番左の1,2,・・・,99等の数字は行番号です(セルに入力する数値ではありません)。

    それにしても、わずか10分の間に4つの回答、うち3つは1分おき(たぶんみんな同時に回答している?)というのも驚きですが、その回答が全部違うことがさらに驚きましたw。

    ちょっと面白かったです。
  • id:takemori
    たしかにみんな違ってて面白いですね

    質問者の方、業務で使うのであれば自分の回答(2の回答)
    使ったほうが良いですよ

    Sheet1をそのまま使えるし<数式>が長くならず見やすいと思うので。。。
    発注書のデザイン変えたりするときも簡単だと思います。

    Sheet1の途中に空白行があったら処理がそこで止まります。
    対象のデータが複数行あったら一番上の行にある値が表示されます。
  • id:rikuzai
    ええと、既出の1~4案以外に関数(配列でなく)のみで算出する方法もあるのですが、
    今のところ質問者さんのコメントがつかないようなので様子見をしております。

    また、二つほど確認したいことがあります。

    1.数量別単価表内に記載されている最低数量を下回る、最大数量を上回っている場合の表示はどうしますか?
    →「数量が間違っています」などのエラーメッセージを出すとか、最低数量の時は一番上の単価、最大数量異以上は一番下の単価を出す、など。

    2.数量別単価表を多少加工することは可能ですか。
    (私が考えている数式は、最大数量のみを使用しますので、数量項目を最小と最大に分ける必要があります。回答者4の方も同じことをされてますね)

    以上です。
  • id:gillsderais
    これ、第三者の方が新たに回答で評価してくれると、さらにおもしろいかもw。

    >質問者の方、業務で使うのであれば自分の回答(2の回答)使ったほうが良いですよ

    第三者的に判断すると、私もtakemoriさんの回答が一番便利かなぁとおもいます。(Sheet1を変更しないというのはやっぱりポイント大きいかなぁ)
    仕組みのわかりやすさで言えばkaitonさんのかなぁ(関数がわかりやすいから)。

    あっ、ウチの回答の注意点としてもう一つありました。
    配列数式を入力したセルがあまりに多いとEXCELの性格上、計算にめちゃくちゃ時間を要します^^;
  • id:llusall
    質問者様、
    すみません。
    コメントを入れるはずが、2回目、回答してしまいました。
    2回目は開かないで頂けると幸いです。



    あぁ、はずかしい。


    取り消し方ってないですかね。


    すみませんでした。
  • id:llusall
    質問者様、
    取消す方法は無いそうです。
    http://q.hatena.ne.jp/1157963595


    2回目は開かないで頂けると幸いです。
    すみませんでした。
  • id:taknt
    残念ながら、一度 回答したものは、削除できません(今のところ)

    質問者が 回答を開かなくても、質問終了後は、誰でもその回答を
    オープンすることができます。

    誰かが興味本位で オープンすることは 避けられませんね。
  • id:gni8
    皆様
    質問者のgni8です。
    大変迅速な回答、コメントの数々ありがとうございます。業務に追われてチェックできませんでした。
    とりあえずひとつづつ試してみたいと思います。

    これはオンラインショップの受注発注業務に利用する予定もので、
    メール注文→データベース(レンタル)に取り込み→受注書と発注書を作成
    という流れのなのですが、発注書に原価を記載する際に、いちいち数量別原価表とにらめっこして、原単価を入力していました。
    これが非常に手間がかかるので、注文メールにお客様が入力した「品番」「数量」をそのまま活用して、発注書の原単価が自動的に記載される仕組みを作りたかったのです。

    とりあえずこれからひとつづつ試してみたいと思います。

  • id:kaiton
    3の回答者です。 もう見ないかもしれませんが..念のため

    4の方の配列数式は、すばらしいです。
    式の確定時にCtrl+Shift+Enterとして、式の前後に{}がつくと思うのですが、担当が変わったり・年数がたつとそのことを忘れます。
    なので、アイテムが増えて式を変えるときのことを考慮して、コメントなどでかかれることをお勧めします。
    私も、そのような質問を時々受けるので...

    配列数式は、こちらのサイトもわかりやすいです。
    http://pc.nikkeibp.co.jp/pc21/special/hr/index.shtml
  • id:gni8
    kaiton(3の回答者)様

    質問者です。最後までフォロー頂きありがとうございました。
    Ctrl+Shift+Enter、重要ですね。
    ぜひセルにコメントをつけておきます。

    ホントありがとうございました。
  • id:gillsderais
    takemoriさんの方法は無理でしたか。
    上でも書きましたが、takemoriさんやllusallさんのようにVBAでサクッと関数をかければ、それが一番理想だと思います(ちなみに私もVBAは使わない人間なので、takemoriさんのどこに問題があるのかわかりません)。

    llusallさんが、「必要に迫られたときに、良く読んでみようと思います」とおっしゃられていますが、たぶんVBAのできる方にとって配列数式は無用の長物だとおもいます。

    さて、最後にやっぱりに気になるのがrikuzaiさんの回答です。
    >関数(配列でなく)のみで算出する方法
    いまさら、ふと思ったんですが、もしかしてDatabase関数(DGETなど)を使うとか?

    現在試せる環境にないのなんともいえないんですが、kaitonさんのおっしゃられているCtrl+Shift+Enterに伴うミスも回避できるかもしれません。

    はてな初めて日が浅いのですが、今回はいろんな考え方あるということがわかって面白かったです(これがはてなの醍醐味?)。
  • id:llusall
    「回答者の要望を汲み取れず、外してしまう」これも「はてな」の難しいところですねw


    当初、私もデータベース関数の「DGET」や「DLOOKUP」あたりでできそうと踏んだのですが、困難でした。


    つい、VBAに逃げてしまいました(汗)


    >VBAのできる方にとって配列数式は無用の長物
    ワークシート関数のできる方にとってはVBAは・・・
    という事にもなりますよw
  • id:taknt
    提示された状態だと ワークシート関数は 無理でしょう。
    セルを分けるなどしないとダメです。

    それはそれで別のものと思われますが・・・。

    gni8さん、早くVBAが使えるようになったらいいですね。
  • id:rikuzai
    ええと、質問者さんは満足されているようなので数式はもう良いかと思いますが、
    気にしていただいている方もいるようなので。
    数式はmatchで該当行を検索・確定して、indirectで数値を返すものです。
    これを条件によってifとiserrorで処理分岐させます。
    ただ、条件をご回答いただけなかったので詰められず回答を遠慮させていただきました。

    VBAはカスタマイズがきいて便利なので、そりゃもうできるにこしたことはありません。自分だけしか使わないファイルならガンガン使ってます。
    ただ、私のように自分だけでなく、スキルがあまり高くない人にも使ってもらえるようにしないといけない部署では、
    マクロ自体を使えないようにしてしまったりしていちいち問合せしてくる人がいるので、
    できるだけ関数で処理するようにしています。

この質問への反応(ブックマークコメント)

トラックバック

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません