人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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のセルに品番と数量を入力すれば、自動的に条件にあう単価を表示させる方法がわからず困っております。
この数式は仕入れの発注書に単価を明記する際に、利用する予定です。
なので、発注のたびに品番・数量を変更して入力すれば、単価が自動的に表示される仕組みを作りたいのです。
どなたかお知恵をお貸し下さい。





●質問者: apple8
●カテゴリ:ビジネス・経営 コンピュータ
✍キーワード:Excel アイテム セル 入力 自動
○ 状態 :終了
└ 回答数 : 6/6件

▽最新の回答へ

1 ● きゃづみぃ
●18ポイント

このような仕様だと 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

◎質問者からの返答

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


2 ● takemori
●50ポイント

ツール→マクロ→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かな?

◎質問者からの返答

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

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


3 ● kaiton
●100ポイント

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

(品番の桁数は全部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)

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

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

要点は、

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

◎質問者からの返答

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


4 ● gillsderais
●200ポイント ベストアンサー

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

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で「列の挿入」、「区切り位置指定ウィザード」をしなくてすむかもしれません。

◎質問者からの返答

gillsderais様

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

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

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

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


5 ● llusall
●18ポイント

ユーザ定義関数+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 にデータベースを作成

ABCD
1品番数量FROM数量TO単価
2A22250991110
3A2221001991000
4A222200299930

・・・以下、省略



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

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


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

◎質問者からの返答

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

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


1-5件表示/6件
4.前の5件|次5件6.
関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ