­ã´£³¡ªL

X5:
=IF(($U5+$V5)*($AO5+$AP5)=0,"",IF(($U6+$V6)*($AO6+$AP6),"    *",W5-LOOKUP(,0/(X$4:X4=""),W$4:W4)))

AJ5:
=IF(($C5+$D5)*($AE5+$AF5)=0,"",IF(($C6+$D6)*($AE6+$AF6),"    *",AG5-LOOKUP(,0/(AJ$4:AJ4=""),AG$4:AG4)))


­Y­nªº¬O³o­Ó®ÄªG¡A¨ä¥¦Ä椽¦¡¦Û¦æ¥h®M¡ã¡ã
³o¥ÎVBA¤Ï¦Ó¤ÓÁc½Æ¤F¡I

­ã´£³¡ªL

¦^´_ 4# donod

³o»ò¦h if , ¥Î + §Y¥i:(¥ô¤@¦¨¥ß)
=IF(AND($AO5<>0,$AP5<>0,$U5<>0,$V5<>0)+AND($AO5<>0,$AP5=0,$U5<>0,$V5<>0)+AND($AO5=0,$AP5<>0,$U5<>0,$V5=0.6216)+¡ã¡ã¡ã,"    *","")

¨ä¹êAND(($AO5+$AP5)<>0,($U5+$V5)<>0)¤w¥]§t¨ä¤¤´X­Ó±ø¥ó,
½Ð¦Û¦æ¦A¼f°u¦U±ø¥ó, ¯à¬Ù«h¬Ù¤§~~

­ã´£³¡ªL

¦^´_ 7# donod


=IF(AND($AO5<>0,$AP5<>0,$U5<>0,$V5<>0)+AND($AO5<>0,$AP5=0,$U5<>0,$V5<>0)+AND($AO5=0,$AP5<>0,$U5<>0,$V5=0.6216)+AND($AO5<>0,$AP5=0.6216,$U5=0,$V5<>0)+AND($AO5=0,$AP5<>0,$U5<>0,$V5=0)+AND($AO5<>0,$AP5=0,$U5=0,$V5<>0)+AND($AO5<>0,$AP5<>0,$U5<>0,$V5=0)+AND($AO5<>0,$AP5=0,$U5<>0,$V5=0)+AND($AO5=0,$AP5<>0,$U5=0,$V5<>0)=0,"",IF(AND($AO6<>0,$AP6<>0,$U6<>0,$V6<>0)+AND($AO6<>0,$AP6=0,$U6<>0,$V6<>0)+AND($AO6=0,$AP6<>0,$U6<>0,$V6=0.6216)+AND($AO6<>0,$AP6=0.6216,$U6=0,$V6<>0)+AND($AO6=0,$AP6<>0,$U6<>0,$V6=0)+AND($AO6<>0,$AP6=0,$U6=0,$V6<>0)+AND($AO6<>0,$AP6<>0,$U6<>0,$V6=0)+AND($AO6<>0,$AP6=0,$U6<>0,$V6=0)+AND($AO6=0,$AP6<>0,$U6=0,$V6<>0) ,"    *",W5-LOOKUP(,0/(X$4:X4=""),W$4:W4)))

ÂŦ⬰[·í¦C]ªº§PÂ_, ¬õ¦â¬°[¤U¤@¦C]ªº§PÂ_!

­ã´£³¡ªL

¦^´_ 9# donod

²¨©¿¤F¨ä¶¡¦³0­È,
LOOKUP(0,0/(Q$4:Q680="")¡@¹J¨ì¢¯§Y°±¤î¡A
§ï¬°¡G
LOOKUP(1,0/(Q$4:Q680="")

¥i¿ï¨ú¾ã±i¤u§@ªí¡A
±N¡@LOOKUP(,¡@¥þ³¡¨ú¥N¦¨¡@LOOKUP(1,

­ã´£³¡ªL

¦^´_ 12# donod

Sub TEST()
Dim R&, x&, y&, H, xR As Range
R = Cells(Rows.Count, 1).End(xlUp).Row ¡¦¥H¢ÏÄæÀË´ú³Ì«á¤@µ§¸ê®Æ¦C¸¹
If R < 5 Then Exit Sub
For x = Range("X1").Column To Range("AB1").Column¡@'X ~ AB Äæ
¡@¡@For y = 5 To R
¡@¡@¡@¡@Set xR = Cells(y, x)
¡@¡@¡@¡@If xR = "    *" Then
¡@¡@¡@¡@¡@¡@If H = "" Then H = Val(Range("W" & y - 1))
¡@¡@¡@¡@¡@¡@If xR(2, 1) = "" And H <> "" Then
¡@¡@¡@¡@¡@¡@¡@¡@xR = "=" & Val(Range("W" & y)) & "-" & H
¡@¡@¡@¡@¡@¡@End If
¡@¡@¡@¡@End If
¡@¡@¡@¡@If IsNumeric(xR) Then H = ""
¡@¡@Next y
Next x

'¨ä¥¦Äæ¦ì©¹¤U¥[¤J¡]³W«h¦p¤W¡A¥u¦³¬Û´î­ÈªºÄ椣¦P¡^¡@
End Sub

¢°¡D¥ý¥Î¤½¦¡²£¥ÍÀˬd­«ÂЪº"    *" °O¸¹
¢±¡D¦A¨Ï¥Îvba¶i¦æ¨â­È¬Û´î
¢²¡D¥H¤W¥u¥Ü¨Ò¢æ¡ã¢Ï¢ÐÄ檺°µªk¡A¨ä¥¦Äæ½Ð¦Û¦æ¥h®M¤J
¢³¡D¬Û´î­È¥H¤½¦¡ = ??? - ??? ªí²{¡A¤è«K°µ¤ñ¹ï¬O§_¥¿½T

­ã´£³¡ªL

¦^´_ 14# donod


µ¹­Ó°Ñ¦Ò¤è®×¡A¯à¥Î«h¥Î¡AÀ³¥i¨Æ¥b¥\­¿¡G
Xl0000082.rar (358.14 KB)

¤U¸ü¦¸¼Æ: 3

2015-11-7 12:24

¥u­n¦b¡e²Ä¥|¦C¡f³]¦n¤½¦¡¡A¤Î¼Ð°O¬Û´î­ÈªºÄæ¦ì¡Aµ{¦¡¥þ¦Û°Ê³B²z¡ã¡ã