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

DDE°ÊºA¸ê®Æ¦p¦ó¨Ï¥ÎHLOOKUP

DDE°ÊºA¸ê®Æ¦p¦ó¨Ï¥ÎHLOOKUP

¦bDDE°ÊºA¸ê®Æ¸Ì¡A¨C¤@¬q®É¶¡ªº¤À®É°O¿ý¡A¤À§O­pºâ¥XMAX­È¡A
¦ý¦³¿ìªk¬d§ä©Ò¹ïÀ³ªºÀx¦s®æ¶Ü¡H

¸Õ¤FLOOKUP¡BOFFSET¡BMATCH¡A³£¨S¿ìªk¦¨¥\¡A
¦n¹³­n¯Â¼Æ¦r©Î¤å¦r¤~¯à¬d§ä¡A¸Ì­±¦³¼g¤½¦¡ªº¸Ü¡A¤£ª¾¹D¦³¨S¦³¿ìªk....




DDE_LOOKUP.zip (67.71 KB)

¦^´_ 1# corcovado886


    ª½±µ¼g¦bvba¸Ì­±¥i¥H¶Ü¡H
  1. Sub ¦Û°Ê°O¿ý()
  2. If uMode = 0 Then Exit Sub
  3. If Time > TimeValue(EndTime) Then '¦¬½L®É¶¡¥H«á¤£°õ¦æ
  4.     Sht1.[W4] = "¤w¹L¦¬½L"
  5.     uMode = 0
  6.     Exit Sub
  7. End If
  8. Sht1.Range("W2") = Time '·í«e®É¶¡¡]®É¶¡½Xªí¡^
  9. '----------------------------------------------------------
  10. '¨C¢°¤ÀÄÁ°O¿ý¡A¢´¤ÀÄÁ«h§ï¬° Mod 5
  11. If Second(Time) Mod 3 = 0 Then
  12.    xRow = Sht1.Range("A65536").End(xlUp).Row + 1
  13.    If xRow < 11 Then xRow = 11
  14.    Sht1.Range("A" & xRow & ":V" & xRow).Value = Sht1.Range("A2:V2").Value
  15.    Sht1.Range("W" & xRow).Value = Time
  16.    Mx = Application.Max(Sht1.Range("a" & xRow & ":v" & xRow))
  17.    Sht1.Range("Y" & xRow).Value = Mx
  18.    Ans = Sht1.Range("a" & xRow & ":v" & xRow).Find(Mx, , , xlWhole).Address
  19.    Sht1.Range("Z" & xRow).Value = Range(Ans).Column
  20.    '------------------------------------------------
  21.    If ActiveSheet.Name = Sht1.Name And xRow > 20 Then
  22.       ActiveWindow.ScrollRow = xRow - 12 'Åý³Ì·s¸ê®Æ«O«ù¦b¥i¨£µøµ¡¤¤
  23.    End If
  24.    ThisWorkbook.Save   '¦sÀÉ
  25. End If
  26. Application.OnTime Now + TimeValue("00:00:01"), "¦Û°Ê°O¿ý"   '¨C¤@¬í»¼°j¤@¦¸
  27. End Sub
½Æ»s¥N½X

TOP

Z11:
=IF(COUNT(A11:V11),MATCH(Y11,A11:V11,),"")

TOP

µw­n¥ÎHLOOKUP  °Ñ¦Ò
Z11=HLOOKUP(Y11,CHOOSE({1;2},OFFSET(A$10,ROW(A1),,,22),A$10:V$10),2,)

TOP

¦^´_ 4# hcm19522
·PÁ¤j®aªº¯d¨¥¡Aª©¤W¦n¦hexcel¤j®v ¡I
¦n¹³hcm19522 ¤j¤jªº¤~¬O¥¿¸Ñ¡A¦]¬°A10:V10 ¤£¤@©w¬O·Ó»¼¼W¶¶§Ç±Æ¦C¡C

¦pªG­n¦bAA11Àx¦s®æ¥[¤WMin­È©O¡H
¦]¬°¤½¦¡¦³ÂI½ÆÂø¡A¤£ª¾¹D«ç»ò±N¤U¦CªºMax­È§ï¦¨³Ì¤p­È
=HLOOKUP(Y11,CHOOSE({1;2},OFFSET(A$10,ROW(A1),,,22),A$10:V$10),2,)

TOP

¦^´_ 5# corcovado886
  «á¨Ó¦Û¤v¶Ã¸Õ¸Ñ¨M¤F¡A¦bAAÄæ¥t¥~­pºâMin­È¡A
µM«á±N¦P¼Ëªº¤½¦¡½Æ»s¨ìABÄæ¡A©¹¤U¤@©Ô´N­pºâ¥X¹ïÀ³­È¤F¡C

¦ý³o¬O¹w¥ý¦b¨C¤@¦C¼g¦n¤½¦¡¡A¦³¨S¦³¿ìªk©T©wÅã¥Ü¦b¤@­ÓÄæ¦ì©O¡H (¨Ò¦p Àx¦s®æY9)

TOP

        ÀR«ä¦Û¦b : ­ì½Ì§O¤H´N¬Oµ½«Ý¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD