BYSPT: 使用标准贯入试验估算土体的物理力学参数值

'Input DataDepth = Sheet1.Range(columnA).Value  'SPT Test DepthN60 = Sheet1.Range(columnB).Value    'Field SPT N-ValueUnitWeight = Sheet1.Range("E28").ValueSoil_Name = Sheet1.Range(columnC).Value

'Effective StressEffectiveStress = Depth * (UnitWeight - 9.81)Sheet1.Range(columnD).Value = EffectiveStress

'Overburden Correctiont2 = EffectiveStress / 95.76obc = Sheet1.Range("B28").Value    Select Case obc            Case "Liao and Whitman(1986)"               cn = Math.Sqr(1 / t2)            Case "Peck et al(1974)"                cn = 0.77 * Application.WorksheetFunction.Log10(20 / t2)            Case "Seed et al.(1975)"                cn = 1 - 1.25 * Application.WorksheetFunction.Log10(t2)            Case "Skempton(1986)"                cn = 2 / (1   t2)            Case "Youd et al. (2001)"                cn = Application.Min(2.2 / (1.2   t2), 1.7)                If cn > 1.7 Then                   cn = 1.7                  ElseIf cn < 0.4 Then                         cn = 0.4                End If            Case ""                cn = 1    End Select    N160 = N60 * cn

'Identification of Consistency or compactnessIf Soil_Name = "Clay" Then    Select Case N60            Case 0 To 2               Sheet1.Range(columnE).Value = "Very Soft"               Sheet1.Range(columnE).Comment.Text "Very soft:  N60<2, Core(height twice diameter) sags under its own weigth while  standing on end.;  sequeezes between fingers when fist closed."            Case 2 To 4                Sheet1.Range(columnE).Value = "Soft"                Sheet1.Range(columnE).Comment.Text "Soft: N60=2~4, Easily molded by fingers."            Case 4 To 8                Sheet1.Range(columnE).Value = "Firm"                Sheet1.Range(columnE).Comment.Text " Firm: N60=4~8, Molded by strong pressure of fingers."            Case 8 To 16                Sheet1.Range(columnE).Value = "Stiff"                Sheet1.Range(columnE).Comment.Text "Stiff: N60=8~16, Imprinted very sligthly by finger pressure."            Case 16 To 32                Sheet1.Range(columnE).Value = "Very Stiff"                Sheet1.Range(columnE).Comment.Text "Very Stiff: N60=16~32, Cannot be imprinted with finger pressure; can be penetrated with a pencil."            Case Is > 32                Sheet1.Range(columnE).Value = "Hard"                Sheet1.Range(columnE).Comment.Text "Hard: N60>32, Imprinted only slightly by pencil point."        End Select

 ElseIf Soil_Name = "Sand" Then 'Sheet1.Range(columnC).Value = "Sand"Select Case N60            Case 0 To 4               Sheet1.Range(columnE).Value = "Very Loose"               Sheet1.Range(columnE).Comment.Text "Very soft:  N60<2, Core(height twice diameter) sags under its own weigth while  standing on end.;  sequeezes between fingers when fist closed."            Case 4 To 10                Sheet1.Range(columnE).Value = "Loose"                Sheet1.Range(columnE).Comment.Text "Soft: N60=2~4, Easily molded by fingers."            Case 10 To 30                Sheet1.Range(columnE).Value = "Medium Dense"                Sheet1.Range(columnE).Comment.Text " Firm: N60=4~8, Molded by strong pressure of fingers."            Case 30 To 50                Sheet1.Range(columnE).Value = "Dense"                Sheet1.Range(columnE).Comment.Text "Stiff: N60=8~16, Imprinted very sligthly by finger pressure."            Case Is > 50                Sheet1.Range(columnE).Value = "Very Dense"                Sheet1.Range(columnE).Comment.Text "Very Stiff: N60=16~32, Cannot be imprinted with finger pressure; can be penetrated with a pencil."            End SelectEnd If  

'Undrained Shear Strength       If Soil_Name = "Clay" Then        Sheet1.Range(columnF).Value = 95.76 * N60 / 16        ElseIf Soil_Name = "Sand" Then        Sheet1.Range(columnF).Value = "N/A"       End If

'Elastic Modulus        Sheet1.Range("R2").Value = 0.5 * (N60   15)        Sheet1.Range("R3").Value = 100 * 5 * N60 / 1000        Sheet1.Range("R4").Value = 100 * 10 * N60 / 1000        Sheet1.Range("R5").Value = 100 * 15 * N60 / 1000        Sheet1.Range("R6").Value = 400 * N160 / 1000        Sheet1.Range("R7").Value = 700 * N160 / 1000        Sheet1.Range("R8").Value = 1000 * N160 / 1000        Sheet1.Range("R9").Value = 1200 * N160 / 1000        Sheet1.Range("R10").Value = N160   1        Sheet1.Range(columnG).Value = Application.WorksheetFunction.Average(Range("R2:R10"))        Sheet1.Range(columnG).Comment.Text "Max: " & FormatNumber(Application.WorksheetFunction.Max(Range("R2:R10")), 2) _                                         & "   Min: " & FormatNumber(Application.WorksheetFunction.Min(Range("R2:R10")), 2) _                                         & "   Median: " & FormatNumber(Application.WorksheetFunction.Median(Range("R2:R10")), 2)

'Skin Friction   Sheet1.Range(columnH).Value = N60 * 95.76 / (50 * 3)

'Relative Density 'Terzaghi and Peck(1948) and Meyerhof(1956), Das(2006)        Select Case N60            Case 0 To 4                Sheet1.Range("Q2").Value = (15 / 4) * N60                Sheet1.Range("Q3").Value = (20 / 4) * N60                Sheet1.Range("Q4").Value = (15 / 4) * N60            Case 4 To 10                Sheet1.Range("Q2").Value = (20 / 6) * (N60 - 4)   15                Sheet1.Range("Q3").Value = (20 / 6) * (N60 - 4)   20                Sheet1.Range("Q4").Value = (35 / 6) * (N60 - 4)   15            Case 10 To 30                Sheet1.Range("Q2").Value = (3 / 2) * (N60 - 10)   35                Sheet1.Range("Q3").Value = (N60 - 10)   30                Sheet1.Range("Q4").Value = (N60 - 10)   50            Case 30 To 50                Sheet1.Range("Q2").Value = (N60 - 30)   65                Sheet1.Range("Q3").Value = (N60 - 30)   60                Sheet1.Range("Q4").Value = (15 / 20) * (N60 - 30)   70            Case Is > 50                Sheet1.Range("Q2").Value = (15 / 50) * (N60 - 50)   85                Sheet1.Range("Q3").Value = (20 / 50) * (N60 - 50)   80                Sheet1.Range("Q4").Value = (15 / 50) * (N60 - 50)   85        End Select 'Das(2004)        Select Case N160            Case 0 To 5                Sheet1.Range("Q5").Value = N160            Case 5 To 10                Sheet1.Range("Q5").Value = 5 * (N160 - 5)   5            Case 10 To 30                Sheet1.Range("Q5").Value = (3 / 2) * (N160 - 10)   30            Case 30 To 50                Sheet1.Range("Q5").Value = (35 / 20) * (N160 - 30)   60        End Select 'Skempton(1986); Mayne(2001)        Sheet1.Range("Q6").Value = Application.Min(0.92 * 100 * Math.Sqr(N160 / 60), 100) 'Coarse sands        Sheet1.Range("Q7").Value = Application.Min(1.08 * 100 * Math.Sqr(N160 / 60), 100) 'Fine sands        Sheet1.Range("Q8").Value = Application.Min(100 * Math.Sqr(N160 / 60), 100) 'Original formula        Sheet1.Range(columnI).Value = Application.WorksheetFunction.Average(Range("Q2:Q8"))

'Calculation of Friction Angle        Sheet1.Range("S2").Value = 0.28 * N60   27.4        Sheet1.Range("S3").Value = 53.881 - 27.6034 * Math.Exp(-0.0147 * N60)        Sheet1.Range("S4").Value = 0.9 * 10 * Application.WorksheetFunction.Log10(N60)   27        Sheet1.Range("S5").Value = 15   Math.Sqr(20 * N60) 'fine sand layer        Select Case N60            Case 0 To 4                Sheet1.Range("S6").Value = (5 / 4) * N60   23                'Sheet1.Range("I8").Comment.Text "Very Loose"            Case 4 To 10            '    'Sheet1.Range("I8").Comment.Text "Loose"                Sheet1.Range("S6").Value = (8 / 6) * (N60 - 4)   27            Case 10 To 30             '   'Sheet1.Range("I8").Comment.Text "Medium Dense"                Sheet1.Range("S6").Value = 0.5 * (N60 - 10)   30            Case 30 To 50             '   'Sheet1.Range("I8").Comment.Text "Dense"                Sheet1.Range("S6").Value = 0.5 * (N60 - 30)   35            Case Is > 50             '   'Sheet1.Range("I8").Comment.Text "Very Dense"                Sheet1.Range("S6").Value = (N60 - 50)   41        End Select        Sheet1.Range("S7").Value = 27.1   0.3 * N160 - 0.00054 * N160 * N160 'Peck        Sheet1.Range("S8").Value = Math.Sqr(15.4 * N160)   20 'after Hatanaka and Uchida (1996) and Mayne(2001)
        'GridFrictionAngle(9, 2).CellValue = 28   15 * 0.01 * GridDr(1, 2).CellValue        'GridFrictionAngle(10, 2).CellValue = 28   15 * 0.01 * GridDr(2, 2).CellValue
        tempVariable1 = 12.2   20.3 * (EffectiveStress / 100)        tempVariable2 = Application.WorksheetFunction.Power(N60 / tempVariable1, 0.34)        radians = Math.Tan(tempVariable2)        Sheet1.Range("S9").Value = 0.9 * radians * (180 / Application.WorksheetFunction.Pi) 'DeMello (1971),Schmertmann (1975),and Mayne (2001)        Sheet1.Range(columnJ).Value = Application.WorksheetFunction.Median(Range("S2:S9"))        Sheet1.Range(columnJ).Comment.Text "Max: " & FormatNumber(Application.WorksheetFunction.Max(Range("S2:S9")), 2) _                                         & "   Min: " & FormatNumber(Application.WorksheetFunction.Min(Range("S2:S9")), 2) _                                         & "   Average: " & FormatNumber(Application.WorksheetFunction.Average(Range("S2:S9")), 2)

'OCR        If Soil_Name = "Clay" Then        E_Stress = 1000 * N60 / EffectiveStress        Sheet1.Range(columnK).Value = 0.193 * Application.WorksheetFunction.Power(E_Stress, 0.6897)        OCR = Sheet1.Range(columnK).Value        Select Case OCR            Case 1               Sheet1.Range(columnK).Comment.Text "Normally consolidated"            Case 1 To 8               Sheet1.Range(columnK).Comment.Text "Lightly over consolidated"
            Case Is > 8               Sheet1.Range(columnK).Comment.Text "Heavily over consolidated"        End Select        ElseIf Soil_Name = "Sand" Then        Sheet1.Range(columnK).Value = "N/A"       End If

'Wave Velocity        Sheet1.Range(columnM) = 0.3048 * 294.619 * Application.WorksheetFunction.Power(N60, 0.341)        'Shear Modulus        Sheet1.Range(columnN) = 0.001 * Sheet1.Range(columnM).Value * Sheet1.Range(columnM).Value * UnitWeight / 9.81

       'Tip Bearing Capacity        Sheet1.Range(columnL) = 3 * 95.76 * N60 / 16

Next k'Copyright & Version HistoryActiveWorkbook.Author = "GeotechWu"Sheet1.Range("J27").Value = "BYSPT    V1.0.009"Sheet1.Range("J28").Value = "GeotechWu" & "   " & DateSheet1.Range("J28").Comment.Text "Last modified by " & ActiveWorkbook.AuthorApplication.ScreenUpdating = TrueEnd Sub