[[20150402143932]]　『日付を見てセットするには』（さとこ）　ページの最後に飛ぶ

『日付を見てセットするには』（さとこ）

Sheet1に下記のような数値情報を入力した場合について質問させていただきます。
YYYYMMより変更したいという情報をsheet1にセットしました。
00001の場合、201506から新単価400円を。それまでは300円の単価で計算したい。
　　A　　　B　　　　　C　　　D　　　　E
1 コード　商品名　　単価　新単価　　変更日
2 00001　A商品　　　300　　400　　　201506
3 00002　B商品　　　100　　
4 00003　C商品　　　400　　500　　　201508
5 00004　D商品　　　200

		：		
sheet2に月別に数量があります。
数量
コード	4月	5月	6月	7月	8月	9月	・・・	3月
00001	10	10	10	31	40	10	・・・	11
00002	10	25	10	10	10	20	・・・	13
00003	40	55	40	44	30	50	・・・	54
00004	20	20	30	20	10	25	・・・	20

＜結果＞
sheet3にsheet1の単価とsheet2の数量を見て、単価*数量の値をセットするには、どうしたらいいでしょうか？
コード 4月 5月 6月 7月 8月 9月・・・ 3月
00001 3000 3000 4000 12400 16000 4000 ・・・ 4400
00002 1000 2500 1000 1000 1000 2000 ・・・ 1300

EXCEL関数でできますか？
xlsmでも構いません。
宜しくお願いします。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 単価も月ごとの表にして、計算は当月の価格と数量を掛けるようにはできないでしょうか。

（Mook） 2015/04/02(木) 15:41

単価も月別にした方が、単純計算できて良いですねぇ。現状だと、以下。
（SHeet3!B1:M1は、日付型で2015/4/1のように入力されており、表示形式で「m"月"」にしている事前提）

=Sheet2!B2*VLOOKUP($A2,Sheet1!$A:$E,IF(AND(VLOOKUP($A2,Sheet1!$A:$E,4,FALSE)<>"",VALUE(VLOOKUP($A2,Sheet1!$A:$E,5,FALSE))<=VALUE(TEXT(B$1,"YYYYMM"))),4,3),FALSE)
（???） 2015/04/02(木) 16:16

 マクロ処理の一例です。
 Sheet1、Sheet2の日付はともに日付型で入力されていて表示書式で、アップされたイメージになっているという前提。
 (関数処理にせよ、マクロ処理にせよ、新単価表は月別にしておけば、なんということはないのですがね）

 Sub Test()
    Dim vntQ As Variant
    Dim vntP As Variant
    Dim c As Range
    Dim z As Variant
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim i As Long
    Dim j As Long
    Dim applyP As Long

    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")

    vntQ = sh2.Range("A1").CurrentRegion.Value
    ReDim vntP(1 To UBound(vntQ, 1), 1 To UBound(vntQ, 2))
    '各月単価のセット
    For i = 2 To UBound(vntQ, 1)
        z = Application.Match(vntQ(i, 1), sh1.Range("A1", sh1.Range("A" & Rows.Count).End(xlUp)), 0)
        If IsNumeric(z) Then
            Set c = sh1.Range("A" & z)
            For j = 2 To UBound(vntQ, 2)
                applyP = c.Offset(, 2).Value
                If Not IsEmpty(c.Offset(, 3)) Then
                    If Format(vntQ(1, j), "yyyymm") >= Format(c.Offset(, 4), "yyyymm") Then applyP = c.Offset(, 3).Value
                End If
                vntP(i, j) = applyP
            Next
        End If
    Next

    '金額計算
    For i = 2 To UBound(vntQ, 1)
        For j = 2 To UBound(vntQ, 2)
            vntQ(i, j) = vntQ(i, j) * vntP(i, j)
        Next
    Next

    With Sheets("Sheet3")
        .UsedRange.ClearContents
        .Range("A1").Resize(UBound(vntQ, 1), UBound(vntQ, 2)).Value = vntQ
        .Select
    End With

 End Sub

（β） 2015/04/02(木) 16:29

皆様、お手数をおかけして申し訳ありません。
ありがとうございます。下記はどのような意味でしょうか？
=Sheet2!B2*VLOOKUP($A2,Sheet1!$A:$E,IF(AND(VLOOKUP($A2,Sheet1!$A:$E,4,FALSE)<>"",VALUE(VLOOKUP($A2,Sheet1!$A:$E,5,FALSE))<=VALUE(TEXT(B$1,"YYYYMM"))),4,3),FALSE)

（さとこ） 2015/04/02(木) 17:15

 関数処理が妥当だと思いますが、アップしたコードで無駄なことをしているところがありましたので。

 Sub Test2()
    Dim vntQ As Variant
    Dim vntA As Variant
    Dim c As Range
    Dim z As Variant
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim i As Long
    Dim j As Long
    Dim applyP As Long

    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")

    vntQ = sh2.Range("A1").CurrentRegion.Value
    vntA = vntQ
    '各月の単価から金額計算
    For i = 2 To UBound(vntQ, 1)
        z = Application.Match(vntQ(i, 1), sh1.Range("A1", sh1.Range("A" & Rows.Count).End(xlUp)), 0)
        For j = 2 To UBound(vntA, 2)
            If IsNumeric(z) Then
                Set c = sh1.Range("A" & z)
                applyP = c.Offset(, 2).Value
                If Not IsEmpty(c.Offset(, 3)) Then
                    If Format(vntA(1, j), "yyyymm") >= Format(c.Offset(, 4), "yyyymm") Then applyP = c.Offset(, 3).Value
                End If
            Else
                applyP = 0
            End If
            vntA(i, j) = vntQ(i, j) * applyP
        Next
    Next

    With Sheets("Sheet3")
        .UsedRange.ClearContents
        .Range("A1").Resize(UBound(vntA, 1), UBound(vntA, 2)).Value = vntA
        .Select
    End With

 End Sub

（β） 2015/04/02(木) 17:55

数量：=Sheet2!B2
旧単価：=VLOOKUP($A2,Sheet1!$A:$E,3,FALSE)
新単価：=VLOOKUP($A2,Sheet1!$A:$E,4,FALSE)
変更日：=VALUE(VLOOKUP($A2,Sheet1!$A:$E,5,FALSE))
当月：=VALUE(TEXT(B$1,"YYYYMM"))

単価が書いてある情報で、新単価の更新分しか記入されていないので、ここが空欄なら旧単価を使用、というIf文が入っており、面倒になってます。
つまり、変更日<=当月かつ、新単価が空欄でなければ新単価、そうでなければ旧単価を使用する、という計算にしています。
（Sheet1の3列目なら旧単価、4列目なら新単価なので、3にするか4にするかを判定しています）

面倒でしょ？だから単価も同じ月別の表にすれば？、と言ってます。その方が今後の変更時、自力解決が楽ですよ。
（???） 2015/04/03(金) 09:07

もう1点。変更日の値と、Sheet3の1行目の月は、どちらも日付型とし、表示方法だけ変えてあれば少し簡単になりますね。
（Sheet3は日付型にするよう書きましたが、変更日はスラッシュがないので、ただの数字なんです）
（???） 2015/04/03(金) 09:16

コメント返信：

[ 一覧(最新更新順) ]

[[20150402143932]] 『日付を見てセットするには』（さとこ） ページの最後に飛ぶ

[[20150402143932]]　『日付を見てセットするには』（さとこ）　ページの最後に飛ぶ