[µo°Ý] ¤½¦¡Â²¤Æ©ÎªÌ¨Ï¥Î¥¨¶°

adam2010

½Ð±Ð¦U¦ì¥ý¶i¬O§_¦³§ó²¤Æªº¤½¦¡¥i¥H¨ú±o¸ê®Æ
¤½¦¡Â²¤Æ 20230929.rar (107.82 KB)

¤U¸ü¦¸¼Æ: 3

2023-9-29 01:55

WIPªºDÄæ¦ì¤½¦¡·½¦Û©ó»¡©ú¤u§@ªíªº¨}²v

¤U¸ü (103.98 KB)

2023-9-29 01:55

®Ú¾Ú C-E (³Ñ¾l¤u§@¯¸ÂI)µ¹¤©¤£¦Pªº¨}²v¡A¼Æ¶q=­ì©l¼Æ¶q*¨}²v(¥|±Ë¤­¤J¨ú¾ã¼Æ)
§PÂ_¨}²vªº¶¶§Ç
1.­YAÄæ¦ì=¯S®í®Æ¸¹®Æ¸¹(AAA001~7)¡÷Vlookup»¡©ú¤u§@ªíªº T~Z (¨ú²Ä5~11Äæ)
2.¦pªG²£«~½s¸¹²Ä7½X=W¡÷Vlookup»¡©ú¤u§@ªíªº AAÄæ (¨ú²Ä12Äæ)
3.¦pªG§å¸¹²Ä1½X=8¡÷Vlookup»¡©ú¤u§@ªíªº RÄæ (¨ú²Ä3Äæ)
4.³Ñ¾l¡÷Vlookup»¡©ú¤u§@ªíªº QÄæ (¨ú²Ä2Äæ)
¥Ñ©ó¯S®í®Æ¸¹¥i¯àÁÙ·|¦A¼W¥[¡A©Ò¥H·Q¸ß°Ý¬O§_¦³§óºë²¥B¦³¼u©Êªº¤½¦¡¥i¥H¨ú¥N¡A©ÎªÌ¬O»Ý­n¨Ï¥Î¥¨¶°

¤U¸ü (254.37 KB)

2023-9-29 01:55

Andy2483

¦^´_ 1# adam2010


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò
¤½¦¡Â²¤Æ 20231002.zip (115.5 KB)

¤U¸ü¦¸¼Æ: 1

2023-10-2 09:58

°õ¦æµ²ªG:

¤U¸ü (219.64 KB)

2023-10-2 09:58

Option Explicit
Function F20231002_1(ByVal Va$)
Application.Volatile
Evaluate "TEST()"
F20231002_1 = Va
End Function
Sub TEST()
Dim Brr, Crr, Z, i&, j%, V%, V7%
Set Z = CreateObject("Scripting.Dictionary")
Brr = Range(Sheets("»¡©ú").Cells(ActiveSheet.UsedRange.Rows.Count, "A"), [»¡©ú!IV1].End(xlToLeft))
For j = 1 To UBound(Brr, 2)
   If Trim(Brr(1, j)) <> "" Then Z("/" & Trim(Brr(1, j)) & "/") = j
Next
Crr = Range([WIP!O1], [WIP!A65536].End(3))
For i = 2 To UBound(Crr)
   V = Val(Crr(i, 15)): V7 = Val(Crr(i, 7)): Crr(i - 1, 1) = ""
   If Z.Exists("/" & Crr(i, 1) & "/") <> Empty Then
      Crr(i - 1, 1) = Round(Brr(V7 + 2, Z("/" & Crr(i, 1) & "/")) * V, 0)
      ElseIf Right(Left(Crr(i, 1), 7), 1) = "W" Then
         Crr(i - 1, 1) = Round(Brr(V7 + 2, Z("/W/")) * V, 0)
      Else
         Crr(i - 1, 1) = Round(Brr(V7 + 2, Z("/" & Split(Crr(i, 2), "X")(0) & """/")) * V, 0)
   End If
Next
[WIP!D2].Resize(UBound(Crr) - 1, 1) = Crr
Set Z = Nothing: Erase Brr, Crr
End Sub

Andy2483

¦^´_ 2# Andy2483
¦^´_ 1# adam2010


    ­×¥¿¦Û¤vªº²Ê¤ß¤j·N,½Ð¥H¦p¤U½d¨Ò´ú¸Õ
¤½¦¡Â²¤Æ 20231002-1.zip (115.58 KB)

¤U¸ü¦¸¼Æ: 2

2023-10-2 10:33

Function F20231002_1(ByVal Va$)
Application.Volatile
Evaluate "TEST()"
F20231002_1 = Va
End Function

Sub TEST()
Dim Brr, Crr, Z, i&, j%, V%, V7%
Set Z = CreateObject("Scripting.Dictionary")
With Sheets("»¡©ú"): Brr = Range(.Cells(.UsedRange.Rows.Count, "A"), .[IV1].End(xlToLeft)): End With
For j = 1 To UBound(Brr, 2)
   If Trim(Brr(1, j)) <> "" Then Z("/" & Trim(Brr(1, j)) & "/") = j
Next
Crr = Range([WIP!O1], [WIP!A65536].End(3))
For i = 2 To UBound(Crr)
   V = Val(Crr(i, 15)): V7 = Val(Crr(i, 7)): Crr(i - 1, 1) = ""
   If Z.Exists("/" & Crr(i, 1) & "/") <> Empty Then
      Crr(i - 1, 1) = Round(Brr(V7 + 2, Z("/" & Crr(i, 1) & "/")) * V, 0)
      ElseIf Right(Left(Crr(i, 1), 7), 1) = "W" Then
         Crr(i - 1, 1) = Round(Brr(V7 + 2, Z("/W/")) * V, 0)
      Else
         Crr(i - 1, 1) = Round(Brr(V7 + 2, Z("/" & Split(Crr(i, 2), "X")(0) & """/")) * V, 0)
   End If
Next
[WIP!D2].Resize(UBound(Crr) - 1, 1) = Crr
Set Z = Nothing: Erase Brr, Crr
End Sub

adam2010

¦^´_ 3# Andy2483
·PÁÂAndy¤j¥X¤â¬Û§U¡A¯u¬O¤Ó¯«³t¤F¥B¥i°ÊºA§ó·s¡A¤£¹L´ú¸Õ«á¦b§PÂ_¶¶§Ç¤W¦³ÂI¤p°ÝÃD¡A
¤½¦¡Â²¤Æ 20231002-3.rar (115.09 KB)

¤U¸ü¦¸¼Æ: 3

2023-10-2 22:27

¹Á¸Õ±N³Ñ¾l21¯¸ªº¨}²v§@¤@­Ó°Ï§O¨ÓÅçÃÒ(¶¶«K¼W¥[®Æ¸¹)

¤U¸ü (42.08 KB)

2023-10-2 22:27

µo²{§PÂ_¶¶§Ç¦³ÂI°ÝÃD¡A¥¿½T¬O1.¯S®í®Æ¸¹ ¡÷ 2.®Æ¸¹²Ä7½X=W ¡÷ 3.§å¦¸²Ä1½X=8 ¨ä¾l¤~¬O¨úQÄæ

¤U¸ü (145.57 KB)

2023-10-2 22:27

Andy2483

¦^´_ 4# adam2010


    ÁÂÁ«e½ú¦^´_
¥H¤U¬O §PÂ_¶¶§Ç 1.¯S®í®Æ¸¹«e6½X ¡÷ 2.®Æ¸¹²Ä7½X=W ¡÷ 3.§å¦¸½X(6 §ä 6"Äæ ,8 §ä 8"Äæ ,12 §ä 12"Äæ )

Option Explicit
Function F20231002_1(ByVal Va$)
Application.Volatile
Evaluate "TEST()"
F20231002_1 = Va
End Function

Sub TEST()
Dim Brr, Crr, Z, i&, j%, V%, V7%
Set Z = CreateObject("Scripting.Dictionary")
With Sheets("»¡©ú"): Brr = Range(.Cells(.UsedRange.Rows.Count, "A"), .[IV1].End(xlToLeft)): End With
For j = 1 To UBound(Brr, 2)
   If Left(Trim(Brr(1, j)), 6) <> "" Then Z(Left(Trim(Brr(1, j)), 6)) = j
Next
Crr = Range([WIP!O1], [WIP!A65536].End(3))
For i = 2 To UBound(Crr)
   V = Val(Crr(i, 15)): V7 = Val(Crr(i, 7)): Crr(i - 1, 1) = ""
   If Z.Exists(Left(Trim(Crr(i, 1)), 6)) <> Empty Then
      Crr(i - 1, 1) = Round(Brr(V7 + 2, Z(Left(Trim(Crr(i, 1)), 6))) * V, 0)
      ElseIf Right(Left(Crr(i, 1), 7), 1) = "W" Then
         Crr(i - 1, 1) = Round(Brr(V7 + 2, Z("W")) * V, 0)
      Else
         Crr(i - 1, 1) = Round(Brr(V7 + 2, Z(Trim(Split(Crr(i, 2), "X")(0) & """"))) * V, 0)
   End If
Next
[WIP!D2].Resize(UBound(Crr) - 1, 1) = Crr
Set Z = Nothing: Erase Brr, Crr
End Sub

adam2010

¦^´_ 5# Andy2483

·PÁÂAndy¤j¨³³t¦^ÂСA¸g´ú¸Õ§¹¥þ²Å¦X»Ý¨D¡A¤Ó·PÁ¤F
¦]¬°¤]¦³¦bµ{¦¡°Ï¸ß°Ý¦¹°ÝÃD¡A¬O§_¥i±NAndy¤jªº¸Ñªk¤À¨É¹L¥hµ¹¨ä¥L¤H°Ñ¦Ò

adam2010

¦^´_ 5# Andy2483
Sorry~Andy¤j¡AÀ³¸Ó¬O§Ú»s§@ªºSample¸ê®ÆÀɮפ£°÷§¹¾ã¡A¦b®M¥Î®É­p¸ê®Æ®É¥X²{°ÝÃD

¤U¸ü (58.14 KB)

2023-10-4 19:42

°ÝÃD¦b©ó  3.§å¦¸½X(6 §ä 6"Äæ ,8 §ä 8"Äæ ,12 §ä 12"Äæ )¡AAndy¤j¬O¨ú²Ä2½X X «eªº¼Æ¦r·í¦T§O¥h§ä¹ïÀ³ªº¨}²v¡A
¦ý¹ê»Ú¸ê®Æ12¤o¬O¥ÎC¥Nªí¥B§å¦¸²Ä2½X¤£¤@©w¬O X

¤U¸ü (38 KB)

2023-10-4 19:49

¤£ª¾¨ì³Ì«á¤@­Ó§PÂ_¦¡¸Ó¦p¦ó­×§ï
            Crr(i - 1, 1) = Round(Brr(V7 + 2, Z(Trim(Split(Crr(i, 2), "X")(0) & """"))) * V, 0)
    ¤½¦¡Â²¤Æ 20231002-4.rar (113.36 KB)

¤U¸ü¦¸¼Æ: 4

2023-10-4 19:52

adam2010

¦^´_ 7# adam2010

±N»¡©ú¼ÐÃD§ï¬°

¤U¸ü (5.13 KB)

2023-10-4 22:19

µM«á±N³Ì«á¤@­Ó§PÂ_¦¡§ï¬°¨ú²Ä1½X«á´ú¸Õ¦¨¥\¤F¡A¤£¹LÁÙ¬O«Ü·PÁÂAndy¤j¤§«eªº¨ó§U¡A²×©ó§¹¦¨
  Else
            Crr(i - 1, 1) = Round(Brr(V7 + 2, Z(Left(Crr(i, 2), 1))) * V, 0)
         
   End If

Andy2483

¦^´_ 5# Andy2483


    ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú,¥H¤U¬O½Æ²ßªº¤ß±oµù¸Ñ

Sub TEST()
Dim Brr, Crr, Z, i&, j%, V%, V7%
'¡ô«Å§iÅܼÆ:(Brr,Crr,Z)¬O³q¥Î«¬ÅܼÆ,i¬Oªø¾ã¼Æ,(j,V,V7)¬Oµu¾ã¼Æ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZ³o³q¥Î«¬ÅܼƬO ¦r¨å
With Sheets("»¡©ú"): Brr = Range(.Cells(.UsedRange.Rows.Count, "A"), .[IV1].End(xlToLeft)): End With
'¡ôÃö©ó "»¡©ú"¤u§@ªíªºµ{§Ç:
'¥OBrr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C,¥H²Ä1¦C³Ì¥ªÃ䦳¤º®eÀx¦s®æ¨ì AÄæ¤w¨Ï¥Î¦C(³Ì¤j¦C¸¹)Àx¦s®æ,
'¥H³o½d³òÀx¦s®æ­È±a¤JBrr°}¦C¤¤
For j = 1 To UBound(Brr, 2)
'¡ô³]¶¶°j°é!¥OjÅܼƱq1 ¨ìBrr°}¦C³Ì¤j¯Á¤ÞÄ渹
   If Left(Trim(Brr(1, j)), 6) <> "" Then Z(Left(Trim(Brr(1, j)), 6)) = j
   '¡ô¦pªG²Ä1¦Cj°j°éÄæBrr°}¦C­È¥h°£ÀY§ÀªÅ¥Õ¦r¤¸«á ¨ú¥ª°¼6¦r¤¸ªº·s¦r¦ê¤£¬OªÅ¦r¤¸!
   '´N¥O¥H ²Ä1¦Cj°j°éÄæBrr°}¦C­È¥h°£ÀY§ÀªÅ¥Õ¦r¤¸«á ¨ú¥ª°¼6¦r¤¸ªº·s¦r¦ê·íkey,
   'jÅܼƷíitem¯Ç¤JZ¦r¨å¤¤
Next
Crr = Range([WIP!O1], [WIP!A65536].End(3))
'¡ô¥OCrr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C,¥H[O1]Àx¦s®æ¨ì AÄæ³Ì«á¦³¤º®eÀx¦s®æ,
'¥H³o½d³òÀx¦s®æ­È±a¤JCrr°}¦C¤¤
For i = 2 To UBound(Crr)
'¡ô³]¶¶°j°é!¥OiÅܼƱq2 ¨ìBrr°}¦C³Ì¤j¯Á¤Þ¦C¸¹
   V = Val(Crr(i, 15)): V7 = Val(Crr(i, 7)): Crr(i - 1, 1) = ""
   '¡ô¥OV³oµu¾ã¼ÆÅܼƬO i°j°é¦C15ÄæCrr°}¦C­ÈÂà¤Æªº¼Æ­È
   '¡ô¥OV7³oµu¾ã¼ÆÅܼƬO i°j°é¦C7ÄæCrr°}¦C­ÈÂà¤Æªº¼Æ­È
   '¡ô¥O(i-1)°j°é¦C1ÄæCrr°}¦C­È¬OªÅ¦r¤¸
   If Z.Exists(Left(Trim(Crr(i, 1)), 6)) <> Empty Then
   '¡ô¦pªG¥H²Ä1¦Cj°j°éÄæCrr°}¦C­È¥h°£ÀY§ÀªÅ¥Õ¦r¤¸«á ¨ú¥ª°¼6¦r¤¸ªº·s¦r¦ê(¥H¤UºÙ²£«~½s¸¹),
   '­Y¥H²£«~½s¸¹¬d¨ìZ¦r¨å¦³¦¹key!
      Crr(i - 1, 1) = Round(Brr(V7 + 2, Z(Left(Trim(Crr(i, 1)), 6))) * V, 0)
      '¡ô¥O(i-1)¦C1ÄæCrr°}¦C­È¬O (V7ÅܼÆ+2¦C,²£«~½s¸¹¬d¦r¨å¦^¶Çitem­ÈÄæ)Brr°}¦C­È,
      '¦A­¼¥HVÅܼƫá,¸Ó­È¤p¼Æ1¦ì¥|±Ë¤­¤J¨ì¾ã¼Æ
      ElseIf Right(Left(Trim(Crr(i, 1)), 7), 1) = "W" Then
      '¡ô§_«h¦pªG¥H ²Ä1¦Cj°j°éÄæCrr°}¦C­È¥h°£ÀY§ÀªÅ¥Õ¦r¤¸«á ¨ú²Ä7¦r¤¸¬O"W"!
         Crr(i - 1, 1) = Round(Brr(V7 + 2, Z("W")) * V, 0)
        '¡ô¥O(i-1)¦C1ÄæCrr°}¦C­È¬O (V7ÅܼÆ+2¦C,"W"¬d¦r¨å¦^¶Çitem­ÈÄæ)Brr°}¦C­È,
        '¦A­¼¥HVÅܼƫá,¸Ó­È¤p¼Æ1¦ì¥|±Ë¤­¤J¨ì¾ã¼Æ
      Else
         Crr(i - 1, 1) = Round(Brr(V7 + 2, Z(Trim(Split(Crr(i, 2), "X")(0) & """"))) * V, 0)
         '¡ô¥O(i-1)¦C1ÄæCrr°}¦C­È¬O (V7ÅܼÆ+2¦C,§å¸¹²Ä1³W®æ½X¬d¦r¨å¦^¶Çitem­ÈÄæ)Brr°}¦C­È,
         '¦A­¼¥HVÅܼƫá,¸Ó­È¤p¼Æ1¦ì¥|±Ë¤­¤J¨ì¾ã¼Æ
         'µù §å¸¹²Ä1³W®æ½X: i°j°é¦C²Ä2ÄæCrr°}¦C­È,¥H"X"¤À³Î¦¨ªº¤@ºû°}¦Cªº²Ä0¯Á¤Þ¸¹°}¦C­È ³s±µ "²Å¸¹
   End If
Next
[WIP!D2].Resize(UBound(Crr) - 1, 1) = Crr
'¡ô¥O"WIP"¤u§@ªí[D2]¦V¤UÂX®iCrrÁa¦V³Ì¤j¯Á¤Þ¦C¸¹-1¦C,¦¹½d³òÀx¦s®æ­È¥HCrr°}¦C­È±a¤J
Set Z = Nothing: Erase Brr, Crr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub

­ã´£³¡ªL

«ö­ì¤½¦¡§ï¤U//
=ROUND(VLOOKUP(G2,»¡©ú!$P:$AC,LOOKUP(99,CHOOSE({1,2,3,4},2,3/(LEFT(B2)="8"),12/(MID(A2,7,1)="W"),MATCH(A2,»¡©ú!$T$1:$Z$1,)+4)),)*O2,)