ML089

¦^´_ 1# m06o2
A1 ¿é¤J¼Æ­È
B1 ¬°ÅܼÆ(»Ý¨D)
C1 ¬°¥Ø¼Ð¤½¦¡¨DB1­È¬°¦h¤Ö®É­pºâ­È¬°0
C1 =A1-(0.9*0.49+9.36*LOG(b1+1)-0.2+LOG(1.5/2.7)/(0.4+1094/(b1+1)^5.19)+2.32*LOG(412000)-8.07)


ALT-F11
¿ï¾Ü Sheet1 ´¡¤J¿é¤JWorksheet_Change
·íA1§ïÅܭȮɡA±Ò°Ê ¥Ø¼Ð·j´M ¨ç¼Æ¡A§ä¥XB1­È¡C

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Range("C1").GoalSeek Goal:=0, ChangingCell:=Range("B1")
    End If
End Sub

20161209 ³W¹º¨D¸Ñ°ÝÃD.rar (9.47 KB)

¤U¸ü¦¸¼Æ: 6

2016-12-9 17:35

ML089

¦^´_ 6# m06o2

'©ñ¦b ThisWorkbook¡A¨C­ÓSheet³£¯à§@¥Î
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Range("C1").GoalSeek Goal:=0, ChangingCell:=Range("B1")
    End If
End Sub


'©ñ¦b¸ÓSheet¤¤¡A¥u¦³¸ÓSheet¤~¯à§@¥Î
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Range("C1").GoalSeek Goal:=0, ChangingCell:=Range("B1")
    End If
End Sub

ML089

¦^´_ 8# m06o2


EX:=((1.5*P3*Q3)*((1/(L3*(1+((U3/Q3)^2)*((N3/L3)^(2/3)))^(1/2)))+((1-(1/((1+((U3/Q3)^2))^(1/2))))/N3))-O3))
ªºÃö«Y¦¡¬O¬° 0 ¶Ü?

ML089

¦^´_ 11# m06o2

¬Ý¤£¤ÓÀ´
­Y¦³¤½¦¡´Nª½±µ´Nª½±µ¨D¸Ñ

ML089

J3 =(1.5*D3*E3)*((1/(C3*(1+((F3/E3)^2)*((B3/C3)^(2/3)))^(1/2)))+((1-(1/((1+((F3/E3)^2))^(1/2))))/B3))-H3
¤U©Ô

ºë«×§C³t«×§Ö
Sub ¥Ø¼Ð·j´M()
    [E3:E30] = 1
    For i = 3 To 30
        Range("J" & i).GoalSeek Goal:=0, ChangingCell:=Range("E" & i)
    Next
End Sub


¨D¸Ñºë«×¤ñ¸û°ª
'SolverSolve ¨ç¼Æ
'
'¶}©l°õ¦æ³W¹º¨D¸Ñ¸Ñ¨M¤è®×¡C¬Û·í©ó«ö¤@¤U [³W¹º¨D¸Ñ°Ñ¼Æ] ¹ï¸Ü¤è¶ô¤¤ªº [³W¹º¨D¸Ñ]¡C
'
'¨Ï¥Î¥»¨ç¼Æ¤§«e¡A¥²¶·«Ø¥ß¹ï³W¹º¨D¸Ñ¼W¯q¶°ªº°Ñ·Ó¡C¦b Visual Basic ½s¿è¾¹¤¤¡A±NµJÂI²¾¦Ü¬Y­Ó¼Ò²Õ¡A«ö¤@¤U [¤u¨ã] ¥\¯àªí¤Wªº [°Ñ·Ó]¡AµM«á¿ï¨ú [¥i¥Î°Ñ·Ó] ©³¤Uªº [Solver.xlam] ®Ö¨ú¤è¶ô¡C¦pªG [Solver.xlam] ¨S¦³¥X²{¦b [¥i¥Î°Ñ·Ó] ©³¤U¡A«ö¤@¤U [ÂsÄý]¡AµM«á¶}±Ò \office12\library\Solver ¤l¸ê®Æ§¨¤¤ªº Solver.xlam¡C
'
Sub ³W¹º¨D¸Ñ()
    [E3:E30] = 1
    For i = 3 To 30
        SolverOk SetCell:="$J$" & i, MaxMinVal:=3, ValueOf:="0", ByChange:="$E$" & i
        SolverSolve UserFinish:=True
        'SolverFinish KeepFinal:=1, ReportArray:=Array(1)    '1 ªí¥Ü«Ø¥ßµª®×³ø§i¡A2 ªí¥Ü«Ø¥ß±Ó·P«×³ø§i¡A¦Ó 3 ªí¥Ü«Ø¥ß­­¨î³ø§i
    Next
End Sub

­pºâ¬yµ{.rar (804.1 KB)

¤U¸ü¦¸¼Æ: 4

2016-12-14 22:59

ML089

¦^´_ 17# m06o2

¬Q¤Ñ§Ú¦³´ú¸Õ 99µ§ÁÙOK¡A
­n°õ¦æ1~2000µ§·|«Ü¤[§a?
±ß¤W§Ú¦A¸Õ¸Õ

¥Ø«e¨S¦³·Qªk¥i¥H¸Ñ¨M

ML089

¦^´_ 19# m06o2

[E3:E30] = 1
ªì©l­È¦³­×§ï¶Ü?