DDE¤À®É°O¿ý¦p¦ó¥Îvba§ä¥X¹ïÀ³ªº¸¹½X

corcovado886

¤§«e¦bExcel ¤@¯ë°Ïµo°Ý¡A¤w¸g¥i¥H¦b¤À®É°O¿ýªºÀx¦s®æ¡A§ä¥X³Ì¤j¤p­Èªº¹ïÀ³¸¹½X
Z11=HLOOKUP(Y11,CHOOSE({1;2},OFFSET(A$10,ROW(A1),,,22),A$10:V$10),2,)

¦ý¦bvba¸Ì¡A¥u¯à§ä¥X1~22ªº±Æ§Ç
    Mx = Application.Max(Sht1.Range("a" & xRow & ":v" & xRow))
   Ans1 = Sht1.Range("a" & xRow & ":v" & xRow).Find(Mx, , , xlWhole).Address
   Sht1.Range("Y" & xRow).Value = Range(Ans1).Column
   Mi = Application.Min(Sht1.Range("a" & xRow & ":v" & xRow))
   Ans2 = Sht1.Range("a" & xRow & ":v" & xRow).Find(Mi, , , xlWhole).Address
   Sht1.Range("Z" & xRow).Value = Range(Ans2).Column

¦³¨S¦³¿ìªk¥Îvba¼g¥X¹ïÀ³ªº¸¹½X©O¡H³Ì¦n¬OÅã¥Ü¦b©T©wªºÄæ¦ì¸Ì¡A
½Ð¦U¦ì°ª¤âÀ°¦£¤@¤U¡A·PÁ¤j®a¡I DDE_VBA(max_min).zip (33.58 KB)

¤U¸ü¦¸¼Æ: 8

2015-11-7 11:54

­ã´£³¡ªL

Y11:
=IF(COUNT(A11:V11),INDEX(A$10:V$10,MATCH(MAX(A11:V11),A11:V11,)),"")

Z11:
=IF(COUNT(A11:V11),INDEX(A$10:V$10,MATCH(MIN(A11:V11),A11:V11,)),"")

VBA:¿ý»s¤½¦¡
With Sht1.Range("Y" & xRow)
¡@¡@.FormulaR1C1 = "=IF(COUNT(RC[-24]:RC[-3]),INDEX(R10C[-24]:R10C[-3],MATCH(MAX(RC[-24]:RC[-3]),RC[-24]:RC[-3],)),"""")"
¡@¡@.Value = .Value
End With
With Sht1.Range("Z" & xRow)
¡@¡@.FormulaR1C1 = "=IF(COUNT(RC[-25]:RC[-4]),INDEX(R10C[-25]:R10C[-4],MATCH(MIN(RC[-25]:RC[-4]),RC[-25]:RC[-4],)),"""")"
¡@¡@.Value = .Value
End With

©ÎªÌ:
With Sht1.Range("Y" & xRow)
¡@¡@.Formula = Replace("=IF(COUNT(A11:V11),INDEX(A10:V10,MATCH(MAX(A11:V11),A11:V11,)),"""")", 11, xRow)
¡@¡@.Value = .Value
End With
With Sht1.Range("Z" & xRow)
¡@¡@.Formula = Replace("=IF(COUNT(A11:V11),INDEX(A10:V10,MATCH(MIN(A11:V11),A11:V11,)),"""")", 11, xRow)
¡@¡@.Value = .Value
End With

corcovado886

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

·PÁ¤j®vªº¦^µª¡A«á¨Ó§Ú¦Û¤v¤]§ä¨ì¤èªk¤F¡C
   Mx = Application.Max(Sht1.Range("a" & xRow & ":v" & xRow)) '¨C¤@¦C§ä¥X³Ì¤j­È
   Ans1 = Sht1.Range("a" & xRow & ":v" & xRow).Find(Mx, , , xlWhole).Column '¨C¤@¦C§ä¥X³Ì¤j­ÈªºÀx¦s®æ¬O¦b²Ä´XÄæ
   Sht1.Range("Y" & xRow).Value = Range("A10:V10").Cells(Ans1) 'YÄæ¦ì©¹¤U°O¿ý³Ì¤j­È©Ò¹ïÀ³ªº¸¹½X
   
   Mi = Application.Min(Sht1.Range("a" & xRow & ":v" & xRow))
   Ans2 = Sht1.Range("a" & xRow & ":v" & xRow).Find(Mi, , , xlWhole).Column
   Sht1.Range("Z" & xRow).Value = Range("A10:V10").Cells(Ans2)

¦pªG¤À®É°O¿ý¸ê®Æ¡A¥ý­pºâ«e¤­¦Cªº»ù®t¡A¦A§ä¥X³Ì¤j¡B³Ì¤p­È¡A¤£ª¾¹D­n«ç»ò­×§ï¡H
»Ý­n¥Î¨ì°}¦C¹Bºâ¶Ü¡H ¯u¤£¦n·N«ä¡A°ÝÃD¦³ÂI¦h¡A³oÀ³¸Ó¬O³Ì«á¤@­Ó°ÝÃD¤F¡A¦A¦¸·PÁ¡I
DDE_VBA_­pºâ»ù®t¦A§ä¥X³Ì¤j¤p­Èªº¹ïÀ³¸¹½X.zip (25 KB)

¤U¸ü¦¸¼Æ: 11

2015-11-8 12:07

­ã´£³¡ªL

AA11:
=IF(AND(COUNT(A11:V11),ROW(A1)>5),LOOKUP(1,0/((A11:V11-A6:V6)=MAX(A11:V11-A6:V6)),A$10:V$10),"")

AB11:
=IF(AND(COUNT(A11:V11),ROW(A1)>5),LOOKUP(1,0/((A11:V11-A6:V6)=MIN(A11:V11-A6:V6)),A$10:V$10),"")

corcovado886

¦^´_ 4# ­ã´£³¡ªL
·PÁ²a¤j³o»ò§Ö´N¦^ÂСA³o´X¤Ñ·¥¤O·Q¦Û¦æ³B²z¤@¨Ç°ÝÃD¡A¦ý¯uªº¦³ÂIµL§U¡C
¨ä¹ê¤]¤£¬O·s°ÝÃD¡A¦]¬°DDE¸ê®Æ±`±`¨S¦³¦¨¥æ¶q¡A¨Ï±oÀx¦s®æ²£¥Í #N/Aªº±¡ªp¡C

¬d¤Fºô¸ô¸ê®Æ¡AÀ³¸Ó¥i¥H¥Î IFNA(...©Î IF(ISNA...¨Ó¸Ñ¨M¡A
¤£¹L LOOKUP(1,0/((A11:V11-A6:V6)=MAX(A11:V11-A6:V6)) ³o­Ó¤½¦¡¹ê¦b¤Ó²`¶ø¤F¡A
¹Á¸Õ¤F«Ü¦h¦ì¸m¡A´¡¤JIFNA ³£µLªk¦¨¥\¡A¤£ª¾¹D¦³¨S¦³¸Ñ¨Mªº¿ìªk¡H

­ã´£³¡ªL

¦^´_ 5# corcovado886


If Second(Time) Mod 3 = 0 Then
  If Application.Count(Sht1.[A2:V2]) = 22 Then
     '~¦Û°Ê°O¿ýµ{¦¡~
  End If
End If

DDE¤½¦¡¦³¿ù»~, ´N¤£¯à¶i¦æ°O¿ý~~