DDE¤À®É°O¿ý¦p¦ó¥Îvba§ä¥X¹ïÀ³ªº¸¹½X
- ©«¤l
- 13
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 18
- ÂI¦W
- 0
- §@·~¨t²Î
- WINDOWS 7
- ³nÅ骩¥»
- HOME
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2015-10-25
- ³Ì«áµn¿ý
- 2023-8-18
|
DDE¤À®É°O¿ý¦p¦ó¥Îvba§ä¥X¹ïÀ³ªº¸¹½X
¤§«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)
|
|
|
|
|
|
|
- ©«¤l
- 2831
- ¥DÃD
- 10
- ºëµØ
- 0
- ¿n¤À
- 2887
- ÂI¦W
- 0
- §@·~¨t²Î
- ¡e²¤¡f
- ³nÅ骩¥»
- ¡e²¤¡f
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¡e²¤¡f
- µù¥U®É¶¡
- 2013-5-13
- ³Ì«áµn¿ý
- 2024-11-4
|
¥»©«³Ì«á¥Ñ ã´£³¡ªL ©ó 2015-11-8 10:38 ½s¿è
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 |
|
|
|
|
|
|
- ©«¤l
- 13
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 18
- ÂI¦W
- 0
- §@·~¨t²Î
- WINDOWS 7
- ³nÅ骩¥»
- HOME
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2015-10-25
- ³Ì«áµn¿ý
- 2023-8-18
|
¦^´_ 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¤£ª¾¹Dn«ç»òקï¡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)
|
|
|
|
|
|
|
- ©«¤l
- 2831
- ¥DÃD
- 10
- ºëµØ
- 0
- ¿n¤À
- 2887
- ÂI¦W
- 0
- §@·~¨t²Î
- ¡e²¤¡f
- ³nÅ骩¥»
- ¡e²¤¡f
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¡e²¤¡f
- µù¥U®É¶¡
- 2013-5-13
- ³Ì«áµn¿ý
- 2024-11-4
|
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),"") |
|
|
|
|
|
|
- ©«¤l
- 13
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 18
- ÂI¦W
- 0
- §@·~¨t²Î
- WINDOWS 7
- ³nÅ骩¥»
- HOME
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2015-10-25
- ³Ì«áµn¿ý
- 2023-8-18
|
¦^´_ 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
- 2831
- ¥DÃD
- 10
- ºëµØ
- 0
- ¿n¤À
- 2887
- ÂI¦W
- 0
- §@·~¨t²Î
- ¡e²¤¡f
- ³nÅ骩¥»
- ¡e²¤¡f
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¡e²¤¡f
- µù¥U®É¶¡
- 2013-5-13
- ³Ì«áµn¿ý
- 2024-11-4
|
¦^´_ 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¿ý~~ |
|
|
|
|
|
|