ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¦h±ø¥ó§PÂ_½s»s¦WºÙ »Ý­n¥Î¤°»ò¨ç¼Æ

[µo°Ý] ¦h±ø¥ó§PÂ_½s»s¦WºÙ »Ý­n¥Î¤°»ò¨ç¼Æ

[attach]22719[/attach]

·Q½Ð±Ð¦U¦ì°ª¤â ¦pªþ¹Ï
­Y ¶µ¥ØA ¼e¬°750 ªø¬°70 «h¦bIÄæ¦Û°Ê½s»sW1_L2
½Ð°Ý³o¼Ëªº¦h±ø¥ó§PÂ_¸Ó«ç»ò¼g¦¨¤½¦¡©O¡H

RE: ¦h±ø¥ó§PÂ_½s»s¦WºÙ »Ý­n¥Î¤°»ò¨ç¼Æ

©êºp¹Ï¤ù¨S¤W¶Ç¦n

TOP

¦^´_ 2# mina324


¥i§_¤W¶ÇÀɮסH
¼g¤½¦¡¤]¶·½d¨Ò¬°°Ñ·Ó¡A¤£­nÅý¸ÑÃDªÌ¦A¦hªá®É¶¡¥h«Ø¥ß¸ê®Æ¡I

TOP

http://blog.xuite.net/hcm19522/twblog/362889454

TOP

¥i¥H¥ÎVBA¶Ü?
¸Õ¸Õ¬Ý:
  1. Public arW, arL
  2. '¨ú±o寛«×»Pªø«×¬É­­ªº°}¦C, ¨ÑMatch¥Î
  3. Sub init()
  4.     ReDim arW(3) As Integer
  5.     ReDim arL(3, 3) As Integer
  6.     Dim W1 As Integer, L1 As Integer
  7.     arW(0) = Split(Cells(3, 3), "~")(1)    '寛«×ªº¤W­­
  8.     For W1 = 0 To 2
  9.         arW(W1 + 1) = Split(Cells(W1 * 3 + 3, 3), "~")(0) '寛«×«ö­°¾­±Æ
  10.         For L1 = 0 To 2
  11.             arL(W1, L1) = Split(Cells(W1 * 3 + L1 + 3, 5), "~")(0)   'ªø«×«ö¤É¾­±Æ
  12.         Next
  13.         arL(W1, L1) = Split(Cells(W1 * 3 + L1 + 2, 5), "~")(1)     'ªø«×ªº¤W­­
  14.     Next
  15. End Sub

  16. Private Sub CommandButton1_Click()
  17.     Dim I As Integer, J As Integer, arL2(3) As Integer
  18.     Dim MHW, MHL, IDW As String, IDL As String
  19.     init
  20.     For I = 4 To [G4].End(xlDown).Row
  21.         MHW = Application.Match(Cells(I, 8), arW, -1)
  22.         IDW = Application.Index([B1:B11], MHW * 3, 1)
  23.         For J = 0 To 3
  24.             arL2(J) = arL(MHW - 1, J)    '±N2ºû°}¦CÂର1ºû°}¦C
  25.         Next
  26.         MHL = Application.Match(Cells(I, 9), arL2, 1)
  27.         'ªø«×¥N¸¹¤À[D3:D5,D6:D9,D10:D12]¤T°Ï
  28.         IDL = Application.Index([D3:D5,D6:D8,D9:D11], MHL, 1, MHW)
  29.         Cells(I, 10) = IDW & "_" & IDL
  30.     Next
  31. End Sub
½Æ»s¥N½X
test.gif

TOP

¦^´_ 2# mina324


¼e«×­è¦n400,700ºâ­þ­Ó°Ï¶¡? ¶W¹L1000©O?
ªø«×¦P¼Ë°ÝÃD~~

TOP

J5=LOOKUP(H5,{0;400;700},{"W3";"W2";"W1"})&"_"&LOOKUP(I5,CHOOSE(RIGHT(LOOKUP(H5,{0;400;700},{"W3";"W2";"W1"})),{0;50;100},{0;30;50},{0;20;50}),{"L1";"L2";"L3"})

TOP

¦^´_ 3# ­ã´£³¡ªL


    ­ã´£³¡ªLª©¥D©êºp¡A§Ú¬Oªì¾ÇªÌ¡A¤½¦¡ÁÙ¤£°÷¼ô±x¡A¨S¯àªþ¤WÀɮסA
    ¤U¦¸§Ú·|¦hª`·N¨Ã§V¤O¾Ç²ßªº¡C  sorry

TOP

hcm19522¡BANGELA ÁÂÁ§A­ÌÀ°§Ú¸Ñ¨M°ÝÃD¡C
yen956  VBA§Ú¥¿¦b¾Ç²ß¡AÁÂÁ§A¯S¦aÀ°§Ú¼g¤Fµ{¦¡¡A§Ú·|¬ã¨s¬Ý¬Ýªº¡C

TOP

        ÀR«ä¦Û¦b : ¨Ã«D¦³¿ú¾{¬O§Ö¼Ö¡A°Ý¤ßµL·\¤ß³Ì¦w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD