エクセル(VBA)について質問です。


時間がある方でプログラムできる方おりましたらよろしくお願いいたします。

現在3つのデータ(長さ、重さ、厚さ)の値から商品レベルを48通りに分けるプログラムを作成したいと考えております。

長さ、重さ、厚さの条件については以下にあります。

★長さ、重さ、厚さに関する条件:http://oskuni7.sakura.ne.jp/hatena/question2/jouken.htm

★レベル分けの条件:http://oskuni7.sakura.ne.jp/hatena/question2/syouhin_level.htm

レベル分けを行いたいのは以下のデータになります。

★プログラムを実行するデータ:http://oskuni7.sakura.ne.jp/hatena/question2/syouhin.htm

列C、列D、列Eに入っているデータの条件から列Fにその条件にあった商品レベルを出力します。

お手数をおかけしますができるかたおりましたらよろしくお願いいたします。

また今後このような条件式を用いてプログラムをする場合、条件を増やしたい場合ここを変えるとやりやすくなる等ありましたらよろしくお願いいたします。


回答の条件
  • 1人2回まで
  • 登録:2008/12/05 16:43:18
  • 終了:2008/12/12 16:45:02

ベストアンサー

id:pahoo No.1

pahoo回答回数5960ベストアンサー獲得回数6332008/12/05 20:52:11

ポイント35pt

「長さ」「重さ」「厚さ」の3つのパラメータが、ご質問のリンク先にあったような文字列として確実に入っていれば、VBAを使うまでもなく、VLOOKUP関数を使って下記のようにして求めることができます。青字の部分は「式」として入力してください。


シート名[LEVEL]

比較用 商品レベル 長さ 重さ 厚さ
=C3&D2&E2 A1 60cmまで 100gまで 1cmまで
=C3&D3&E3 A2 60cmまで 150gまで 1cmまで
=C4&D4&E4 A3 60cmまで 250gまで 1cmまで

――以下省略――


シート名[ITEMS]

管理番号 長さ 重さ 厚さ 管理番号の商品レベル
A13 60cmまで 250gまで 3.5cmまで =VLOOKUP(B2&C2&D2,LEVEL!$A$2:$E$49,2,FALSE)
A13 60cmまで 250gまで 3.5cmまで =VLOOKUP(B3&C3&D3,LEVEL!$A$2:$E$49,2,FALSE)
A31 60cmまで 250gまで 3.5cmまで =VLOOKUP(B4&C4&D4,LEVEL!$A$2:$E$49,2,FALSE)

――以下省略――


ただし、この方法だと LEVEL にないパラメータの組み合わせ――たとえば「35cmまで」「110gまで」「2.3cmまで」などという商品データがあると、正しいレベルが判定できます。

その場合は、「長さ」「重さ」「厚さ」を数値として比較する必要があるのですが、そうなると、「まで」が「以下」なのか「未満」なのか、言葉の定義を明確化していかなければなりません。

id:aiomock

ご回答ありがとうございます。

作成してみたらできました。

ですが一部バグが発生いたします。

バグが発生したのは 

100cmまで 500gまで 3.5cm以上

100cmまで 1kgまで 3.5cm以上

の場合のVLOOKUP式を入れ込んだときなのですが

VLOOKUPの関数を入れ込んだ際に問題になる主な原因はどのようなものでしょうか?

他の物はまったく問題なく表示出力されていたので、なぜこの条件の場合だけ問題が起こったのかわからずじまいです。

VLOOKUPに詳しいかたおられましたらよろしくお願いいたします。

2008/12/05 22:05:02

その他の回答(1件)

id:pahoo No.1

pahoo回答回数5960ベストアンサー獲得回数6332008/12/05 20:52:11ここでベストアンサー

ポイント35pt

「長さ」「重さ」「厚さ」の3つのパラメータが、ご質問のリンク先にあったような文字列として確実に入っていれば、VBAを使うまでもなく、VLOOKUP関数を使って下記のようにして求めることができます。青字の部分は「式」として入力してください。


シート名[LEVEL]

比較用 商品レベル 長さ 重さ 厚さ
=C3&D2&E2 A1 60cmまで 100gまで 1cmまで
=C3&D3&E3 A2 60cmまで 150gまで 1cmまで
=C4&D4&E4 A3 60cmまで 250gまで 1cmまで

――以下省略――


シート名[ITEMS]

管理番号 長さ 重さ 厚さ 管理番号の商品レベル
A13 60cmまで 250gまで 3.5cmまで =VLOOKUP(B2&C2&D2,LEVEL!$A$2:$E$49,2,FALSE)
A13 60cmまで 250gまで 3.5cmまで =VLOOKUP(B3&C3&D3,LEVEL!$A$2:$E$49,2,FALSE)
A31 60cmまで 250gまで 3.5cmまで =VLOOKUP(B4&C4&D4,LEVEL!$A$2:$E$49,2,FALSE)

――以下省略――


ただし、この方法だと LEVEL にないパラメータの組み合わせ――たとえば「35cmまで」「110gまで」「2.3cmまで」などという商品データがあると、正しいレベルが判定できます。

その場合は、「長さ」「重さ」「厚さ」を数値として比較する必要があるのですが、そうなると、「まで」が「以下」なのか「未満」なのか、言葉の定義を明確化していかなければなりません。

id:aiomock

ご回答ありがとうございます。

作成してみたらできました。

ですが一部バグが発生いたします。

バグが発生したのは 

100cmまで 500gまで 3.5cm以上

100cmまで 1kgまで 3.5cm以上

の場合のVLOOKUP式を入れ込んだときなのですが

VLOOKUPの関数を入れ込んだ際に問題になる主な原因はどのようなものでしょうか?

他の物はまったく問題なく表示出力されていたので、なぜこの条件の場合だけ問題が起こったのかわからずじまいです。

VLOOKUPに詳しいかたおられましたらよろしくお願いいたします。

2008/12/05 22:05:02
id:SALINGER No.2

SALINGER回答回数3454ベストアンサー獲得回数9692008/12/06 13:17:23

ポイント35pt

既に解決してるようですが、面白そうな質問だったのでVBAでユーザー定義関数を使って回答してみます。

まず、「レベル分けの条件」の表の商品レベルの名前の付け方は規則性があるようですので、

これは、「長さ、重さ、厚さに関する条件」の表さえあれば後は自動で商品レベルの名前をつけられそうです。

また、「長さ、重さ、厚さに関する条件」の表に新しく項目を増やした場合に

「レベル分けの条件」の表を作り直すのも手間がいるので、5kg以上を増やしたり項目を増やしたら即座に反映されるようにしました。


まず、「長さ、重さ、厚さに関する条件」の表をシート名「項目」にして左上詰めで作ります。

(リンク先の表をExcelにコピペする場合は、「cm(小文字)」が「㎝(センチメートルの変換)」と混ざっているようなので気をつけてください)


次に、ユーザー定義関数を標準モジュールにコピペします。

Function Level(target1 As Range, target2 As Range, target3 As Range) As String
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim lastRow1 As Long
    Dim lastRow2 As Long
    Dim lastRow3 As Long
    With Worksheets("項目")
        lastRow1 = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = 2 To lastRow1
            If Trim(.Cells(i, 1).Value) = Trim(target1.Value) Then Exit For
        Next
        lastRow2 = .Cells(Rows.Count, 2).End(xlUp).Row
        For j = 2 To lastRow2
            If Trim(.Cells(j, 2).Value) = Trim(target2.Value) Then Exit For
        Next
        lastRow3 = .Cells(Rows.Count, 3).End(xlUp).Row
        For k = 2 To lastRow3
            If Trim(.Cells(k, 3).Value) = Trim(target3.Value) Then Exit For
        Next
    End With
    If i = lastRow1 + 1 Or j = lastRow2 + 1 Or k = lastRow3 + 1 Then
        Level = ""
    Else
        Level = Chr((lastRow3 - 1) * (i - 2) + k + 63) & j - 1
    End If
End Function

ユーザー定義関数とはなんぞやという場合はこちら

http://hp.vector.co.jp/authors/VA016119/hajimete/udf1.html


このユーザー定義関数は

商品レベル=Level(長さのセル,重さのセル,厚さのセル)

となっています。


最後に、管理番号の商品レベルの列に

=Level(B2,C2,D2)

のように数式を入れて、下にコピーすれば完成です。


注意)アルファベットは26文字なので、長さの項目の数と厚さの項目の数を掛けた値が26を超えると、記号が入ったりします。

id:aiomock

ご回答ありがとうございます。

2008/12/07 12:06:06
  • id:kn1967
    お望みのものであれば VBA を使わずに
    「レベル分けの条件」というシートを用意して
    VLOOKUPで検索するだけでも可能ですが、どうしても VBA?

    もちろんVBAでも、それほど難しいものではなく作成可能ですけど
    後々の事を考えるとシンプルで自分でも手直しできるほうが良いと思いますよ。
  • id:kn1967
    H1 から H6の長さは「100cmまで」じゃありませんか?
    (D1 から H6までとダブってます)

    将来の拡張を考えてVLOOKUPと書きましたが
    現状であれば別シートを使わずとも関係式を書く事でも対処できます。
  • id:aiomock
    kn1967 さん

    アドバイスありがとうございます。

    VLOOKUP機能でできるということを知りませんでした。

    VLOOKUP(参照するセル3つ、データの範囲、参照する列を3つ、FALSE)

    という式で対応できるものでしょうか?

    見たところVLOOKですと

    VLOOK(参照するセル1つ、データの範囲、参照する列1つ、FALSE)

    のような形式なのですが

    お手数をおかけしますが知っているかたおりましたらよろしくお願いいたします。

  • id:pahoo
    #1の回答に誤植がありました。訂正します。
    下から2行目の文末
    ×正しいレベルが判定できます。
    ○正しいレベル判定ができません。
  • id:pahoo
    > バグが発生したのは 

    レベルは
    100cmまで 500gまで 3.5cm以上 ⇒ H4
    100cmまで 1kgまで 3.5cm以上 ⇒ H5
    ですよね。こちらでは正常に判別できています。

    英数字が全角だったり、データの途中に空白が混じっているということはありませんか?
  • id:kn1967
    人が話しを進めているところを横取りしてまでポイント欲しいのでしょうか?
    それよりも自分の書き込みのフォローしていただけませんかねぇ・・・>pahoo氏

    question:1228427479
  • id:pahoo
    > aiomockさん
    質問者の方に不快な思いをさせてしまったようでしたら申し訳ありません。
    可能であれば、私はポイント不要で結構ですので、kn1967さんにポイントを送信してあげてください。
    他所でも申し上げていますが、私は回答するのを楽しみでやっておりますので、ポイントの多寡は気にしておりません。

    > kn1967さん
    本件質問と無関係な発言をされるのはいかがなものかと存じます。
  • id:kn1967
    質問と無関係な発言に関しては申し訳ありません。>aiomockさん

    ところで、どうして最初から回答ではなくコメント欄にて
    ワークシート関数(VLOOKUP)での対処法を提案したのかと申しますと
    ワークシート関数の場合はシート上で関係箇所に変更を加える度に
    再計算が行われてしまったりして動作が遅くなる可能性があるからです。
    昨今のパソコンは性能が上がっているのでサンプル程度の件数ならば
    気にする必要すらなかったりしますが、今後も使い続ける事を考えると
    その場合の考慮もしなければなりません。

    対して、VBAで対処した場合は
    (1)レベル欄をリアルタイムで変化させる
      この場合、ワークシート関数によるものよりも
      動作にかかる負担(時間)は大きくなりますが
      ワークシート関数では難しい複雑な条件でも対処できます。
    (2)マクロを起動した時だけレベル欄を再計算させる
      この場合、普段の動作に負担はありませんが表示されているものが
      最新でない場合もあるので元のデータが頻繁に変更される場合は
      間違ったデータを見せられる事になり注意が必要です。
      元のデータに対する変更があまり無いのであれば
      この方法が1番適しているでしょう。
    といったような特徴がそれぞれあるため
    もう少し状況把握が必要だったためです。
    (全パターン作成して説明まで入れるのは少々時間がかかりますからね
    回答を絞りたい訳です)

    こういった考慮をしていればVBAでの質問(というか依頼ですね)に対して
    いきなりVLOOKUPでの回答などは行うはずもないと思いますから
    aiomockさんには関係無い事柄ではありましたが苦言を呈した次第です。

    SALINGER氏からの回答がありますので
    多分VBAで(2)に該当するものが挙げられていると思います。
    ひとまず、引っ込みます。
  • id:SALINGER
    kn1967さんに見透かされたようです。VBAでユーザー定義関数を作ったので負荷の高い(1)の回答でした。
  • id:kn1967
    あらっ!? (1)?
    でもまぁ(2)との違いは微々たるものですから、どっちでもいいですよね。

    某氏の回答がネットで寄せ集めたようなコードが多いのに対して、
    SALINGERさんのコードはオリジナリティがあって綺麗でもありますので
    私も拝見したく「のっかりはてな」させていただきました。

    回答していない私は外野席のにぎやかしみたいになってるな(笑
  • id:kn1967
    アルファベットの部分を文字コードで持ってくるとは・・・。
    (動作確認はしてなかったりするけど)乗っかって正解だったと思ふ。

    配列を用いて
    長さ60cmまでだったらa,b,c,d/100cmまでだったらe,f,g,h
    厚さ1cmまでだったら1番目(aかe)/2cmまでだったら2番目(bかf)・・・
    重さ・・・
    なんて形でコード振ろうなんて考えてましたから・・・なんとも恥ずかしい。



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

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

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

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