例の実走ログからトルク・出力を算出するマクロ。
はそ@GDB-C specCさんのマクロを参考にさせて頂き、私も、計測項目の増減や並びに影響されないマクロを構築してみた。
ただし、ロガーの定義ファイルは「
Logger XML UPDATE version 0.3.2b 12-03-07」を使ってロギングしたものに限ります。ログ項目名とか以前のバージョンから結構変わっているので。
VBA初心者なので、インチキ臭い部分もあるかと思いますが、とりあえず簡単に使える手順を整備手帳にアップしました。
整備手帳:
【ECU】実走ログからのトルク・出力計算
計測は、ギア3速で、エンスト寸前の低回転域からフルスロットルで行ってください。
マクロのエクスポートファイルをここからダウンロードして頂いても結構ですが、以下にもソースを。
Sub PowerCheck()
'
' 整列 Macro
' マクロ記録日 : 2008/1/23 ユーザー名 : mistbahn
'
' Keyboard Shortcut: Ctrl+l
'
'初期パラメータ------------------------------------------------------
' wegiht [lbs] (LGT 2006)
' ※ただし、3400[lbs]はBase Weightで、これにガソリン量等の補正が入る
Dim weight As Double
weight = 3400 + 360
' inertia calibration constant (LGT 2006)
Dim C1 As Double
C1 = 0.2
' aerodynamic calibration constant (LGT 2006)
Dim C2 As Double
C2 = 0.012
' Cd (LGT 2003)
Dim Cd As Double
Cd = 0.28
' Engine To Speed [rpm/mph] (LGT 2003)
Dim rpm_ratio As Double
rpm_ratio = 73
' 加速度Smoothingの行数(この値で結構カーブが変わる)
Dim smoothing_count As Integer
smoothing_count = 4
'初期パラメータここまで----------------------------------------------
Dim IdxName(100) As String
IdxName(0) = "Time"
IdxName(1) = "Stop Light Switch (On/Off)"
IdxName(2) = "Clutch Switch (On/Off)"
IdxName(3) = "Gear (Calculated)* (position)"
IdxName(4) = "Throttle Opening Angle (%)"
IdxName(5) = "Throttle Opening Angle Difference (%)"
IdxName(6) = "Idle Switch (On/Off)"
IdxName(7) = "Turbo Dynamics Integral Cumulative* (absolute %)"
IdxName(8) = "Primary Wastegate Duty Cycle (%)"
IdxName(9) = "Manifold Relative Pressure (Corrected) (bar)"
IdxName(10) = "Boost Error* (bar)"
IdxName(11) = "Mass Airflow (g/s)"
IdxName(12) = "Engine Load (Calculated) (g/rev)"
IdxName(13) = "Engine Speed (rpm)"
IdxName(14) = "Engine Speed Difference (rpm)"
IdxName(15) = "Engine Speed Acceleration (rpm/msec)"
IdxName(16) = "Engine Speed Acceleration Avr (rpm/sec)"
IdxName(17) = "A/F Sensor #1 (AFR)"
IdxName(18) = "Ignition Timing (Total) (degrees)"
IdxName(19) = "Feedback Knock Correction* (degrees)"
IdxName(20) = "IAM (Ignition Advance Multiplier)* (multiplier)"
IdxName(21) = "Vehicle Speed (kph)"
IdxName(22) = "Real Vehicle Speed (kph)"
IdxName(23) = "Real Vehicle Speed (mph)"
IdxName(24) = "Real Vehicle Speed Acceleration (kph/msec)"
IdxName(25) = "Rough Acceleration (Gs)"
IdxName(26) = "Smoothed Acceleration (Gs)"
IdxName(27) = "Torque (lbf・ft)"
IdxName(28) = "Torque (N・m)"
IdxName(29) = "Torque (kgf・m)"
IdxName(30) = "Power (kW)"
IdxName(31) = "Power (ps)"
IdxName(32) = "Fine Learning Knock Correction* (degrees)"
IdxName(33) = "Intake Air Temperature (C)"
IdxName(34) = "Coolant Temperature (C)"
Dim row As Integer
Dim col As Integer
Dim idx As Integer
Dim LogSheetName As String
LogSheetName = ActiveWorkbook.ActiveSheet.Name
Dim taiou(100) As Integer
' 配列対応比較
For idx = 0 To 100
taiou(idx) = 0
For col = 1 To 100
If Cells(1, col).Value = "" Then
Exit For
End If
If Cells(1, col).Value = IdxName(idx) Then
taiou(idx) = col
Exit For
End If
Next
Next
' ワークシートの追加と整列
ActiveWorkbook.Sheets.Add
Dim AddSheetName As String
AddSheetName = ActiveWorkbook.ActiveSheet.Name
For idx = 0 To 100
If taiou(idx) <> 0 Then ' 元ログに該当項目あり
ActiveWorkbook.Sheets(LogSheetName).Columns(taiou(idx)).Copy
ActiveWorkbook.Sheets(AddSheetName).Columns(idx + 1).Select
ActiveSheet.Paste
Else
' 元ログに該当項目がなくても列は作る
ActiveWorkbook.Sheets(AddSheetName).Cells(1, idx + 1).Value = IdxName(idx)
End If
Next
' 行数
Dim RecCount As Integer
' 先頭行の文字列を90度回転し、下詰めに
ActiveWorkbook.Sheets(AddSheetName).Rows("1:1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 90
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
' ウインドウ枠の固定
ActiveWorkbook.Sheets(AddSheetName).Rows("2:2").Select
ActiveWindow.FreezePanes = True
' 行数の確認
Dim i As Integer
i = 1
Do Until Cells(i, 1).Value = ""
i = i + 1
Loop
RecCount = i - 1
' EngineSpeedDifference値計算
For y = 3 To RecCount
Cells(y, 15).Value = Cells(y, 14).Value - Cells(y - 1, 14).Value
Next
' EngineSpeed加速度値計算
For y = 3 To RecCount
Cells(y, 16).Value = Cells(y, 15).Value / (Cells(y, 1).Value - Cells(y - 1, 1).Value)
Next
' Throttle変化量計算
For y = 3 To RecCount
Cells(y, 6).Value = Cells(y, 5).Value - Cells(y - 1, 5).Value
Next
' RealVehicleSpeed計算
For y = 2 To RecCount
Cells(y, 23).Value = Round(Cells(y, 22).Value * 0.979, 0)
Next
' RealVehicleSpeedAcceleration値計算
For y = 3 To RecCount
Cells(y, 25).Value = ((Cells(y, 23).Value - Cells(y - 1, 23).Value) / (Cells(y, 1).Value - Cells(y - 1, 1).Value))
Cells(y, 25).Value = Round(Cells(y, 25).Value, 4)
Next
' MPH、加速度計算
Dim kph As Integer
Dim mph As Integer
Dim gs As Double
Dim AccMph As Double
Dim round_gs As Double
For y = 3 To RecCount
' kph → mph補正
kph = Cells(y, 23)
mph = kph / 1.61
' Debug
Cells(y, 24).Value = mph
' 加速度 kph → mph → Gs補正(※45.54865424430はmph→Gs補正係数)
AccMph = Cells(y, 25).Value / 1.61
gs = AccMph * 45.5486542443
' Debug
Cells(y, 26).Value = gs
Next
' Smoothed Acceleration
For y = 3 To (RecCount - (smoothing_count - 1))
Cells(y, 27).Value = "=TREND(Z" & y & ":Z" & (y + (smoothing_count - 1)) & ",A" & y & ":A" & (y + (smoothing_count - 1)) & ",A" & y & ")"
Cells(y, 27).Value = Round(Cells(y, 27).Value, 5)
Next
' トルク計算
For y = 3 To RecCount
Cells(y, 28).Value = (C1 * weight * Cells(y, 27).Value + C2 * Cd * mph ^ 2) ' [lbf・ft]
Cells(y, 29).Value = Cells(y, 28).Value * 1.3558 ' [N・m]
Cells(y, 30).Value = Cells(y, 29).Value / 9.80665 ' [kgf・m]
Cells(y, 28).Value = Round(Cells(y, 28).Value, 2)
Cells(y, 29).Value = Round(Cells(y, 29).Value, 2)
Cells(y, 30).Value = Round(Cells(y, 30).Value, 2)
Next
' Power値計算
For y = 3 To RecCount
' Cells(y, 34).Value = Cells(y, 31).Value * Cells(y, 14).Value / 5252 [hp]
Cells(y, 31).Value = (Cells(y, 29).Value * Cells(y, 14).Value * 2 * 3.14) / 60000 ' [kW]
Cells(y, 32).Value = Cells(y, 31).Value / 0.7355 ' [ps]
Cells(y, 31).Value = Round(Cells(y, 31).Value, 1)
Cells(y, 32).Value = Round(Cells(y, 32).Value, 1)
Next
' 整列
Columns(1).ColumnWidth = 8 ' Time
Columns(2).ColumnWidth = 2.6 ' Stop Light
Columns(3).ColumnWidth = 2.6 ' Clutch
Columns(4).ColumnWidth = 2.63 ' Gear
Columns(5).ColumnWidth = 5.4 ' Throttle Opening Angle
Columns(6).ColumnWidth = 6.13 ' Throttle Opening Angle Difference
Columns(7).ColumnWidth = 2.6 ' Idle
Columns(8).ColumnWidth = 5.38 ' Turbo Dymanics
Columns(9).ColumnWidth = 5.2 ' Primary Wastegate Duty
Columns(10).ColumnWidth = 5.38 ' Manifold Relative Pressure
Columns(11).ColumnWidth = 6.2 ' Boost Error
Columns(12).ColumnWidth = 6.5 ' Mass Air Flow
Columns(13).ColumnWidth = 4.5 ' Engine Load
Columns(14).ColumnWidth = 4.88 ' Engine Speed
Columns(15).ColumnWidth = 4.88 ' Engine Speed Difference
Columns(18).ColumnWidth = 5.2 ' A/F
Columns(19).ColumnWidth = 4.25 ' Ignition Timing
Columns(20).ColumnWidth = 6.13 ' Feedback Learning Correction
Columns(21).ColumnWidth = 6.25 ' IAM
Columns(22).ColumnWidth = 4.25 ' Vehicle Speed
Columns(23).ColumnWidth = 4.25 ' Real Vehicle Speed
Columns(24).ColumnWidth = 4.25 ' MPH
Columns(25).ColumnWidth = 6.5 ' Real Vehicle Speed Acceleration
Columns(26).ColumnWidth = 8.38 ' Rough Acceleration
Columns(27).ColumnWidth = 7.38 ' Smoothed Acceleration
Columns(28).ColumnWidth = 6.38 ' Torque (lbf・ft)
Columns(29).ColumnWidth = 6.38 ' Torque (N・m)
Columns(30).ColumnWidth = 5.4 ' Torque (kgf・m)
Columns(31).ColumnWidth = 5.13 ' Power (kw)
Columns(32).ColumnWidth = 5.28 ' Power (ps)
Columns(33).ColumnWidth = 2.8 ' Fine Learning Correction
Columns(34).ColumnWidth = 3 ' Intake Air Temp
Columns(35).ColumnWidth = 3.8 ' Coolant Temp
' Brake Switchの色付け
Columns(2).Select
Selection.FormatConditions.Delete
' 0に等しいセルの文字はグレーに
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = 15
' 1に等しいセルの文字は紺にし、背景は水色に
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="1"
Selection.FormatConditions(2).Font.ColorIndex = 11
Selection.FormatConditions(2).Interior.ColorIndex = 24
' Clutch Switchの色付け
Columns(3).Select
Selection.FormatConditions.Delete
' 0に等しいセルの文字はグレーに
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = 15
' 1に等しいセルの文字は紺にし、背景は水色に
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="1"
Selection.FormatConditions(2).Font.ColorIndex = 11
Selection.FormatConditions(2).Interior.ColorIndex = 24
' Gear(3速)の色付け
Columns(4).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="3"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 10
End With
' Throttle Opening Angle 色付
Columns(5).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="100"
With Selection.FormatConditions(1).Font
.Bold = False
.Italic = False
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(1).Interior.ColorIndex = 8
' Throttle Opening Angle Difference色付
Columns(6).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = xlAutomatic
Selection.FormatConditions(1).Interior.ColorIndex = 15
' Boost追加色付
Columns(10).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = 48
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="0.9", Formula2:="1"
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = False
End With
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="1", Formula2:="1.5"
With Selection.FormatConditions(3).Font
.Bold = True
.Italic = False
.ColorIndex = 7
End With
' BoostError色付
Columns(11).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="-0.1", Formula2:="0.1"
Selection.FormatConditions(1).Font.ColorIndex = 5
' Engine Load色付
Columns(13).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="1", Formula2:="1.99"
Selection.FormatConditions(1).Font.ColorIndex = 46
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="2", Formula2:="2.99"
Selection.FormatConditions(2).Font.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="3", Formula2:="10"
With Selection.FormatConditions(3).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
' AFR色付
Columns(18).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="14.8", Formula2:="100"
Selection.FormatConditions(1).Font.ColorIndex = 46
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="11.6", Formula2:="0"
Selection.FormatConditions(2).Font.ColorIndex = 14
' Feedback Correction
Columns(20).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="0"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
Selection.FormatConditions(1).Interior.ColorIndex = 38
ActiveWindow.SmallScroll Down:=9
' IAM色付け
Columns(21).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="1"
Selection.FormatConditions(1).Font.ColorIndex = 7
' TurboDynamics色付
Columns(8).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = 15
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="10", Formula2:="20"
Selection.FormatConditions(2).Font.ColorIndex = 7
' Vehicle Speed色付
Columns(22).Select
Selection.FormatConditions.Delete
' 100~199[kph]を青色に
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="100", Formula2:="199"
With Selection.FormatConditions(1).Font
.Bold = False
.Italic = False
.ColorIndex = 5
End With
' 200~300[kph]を太ピンク字に
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="199", Formula2:="300"
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 7
End With
' Vehicle Speed色付
Columns(23).Select
Selection.FormatConditions.Delete
' 100~199[kph]を青色に
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="100", Formula2:="199"
With Selection.FormatConditions(1).Font
.Bold = False
.Italic = False
.ColorIndex = 5
End With
' 200~300[kph]を太ピンク字に
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="199", Formula2:="300"
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 7
End With
' Fine Correctionの色付け
Columns(33).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="0"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
Selection.FormatConditions(1).Interior.ColorIndex = 38
ActiveWindow.SmallScroll Down:=9
' CoolantTemp色付
Columns(35).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="0", Formula2:="79"
Selection.FormatConditions(1).Font.ColorIndex = 33
Selection.FormatConditions(1).Interior.Pattern = xlNone
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="100", Formula2:="110"
Selection.FormatConditions(2).Font.ColorIndex = 7
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="110", Formula2:="200"
Selection.FormatConditions(3).Font.ColorIndex = 3
' トルク色付け
Columns(30).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="30", Formula2:="100"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 5
End With
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="10", Formula2:="30"
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = xlAutomatic
End With
' Power色付け
Columns(32).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="260", Formula2:="400"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 5
End With
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="200", Formula2:="300"
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 14
End With
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="100", Formula2:="200"
With Selection.FormatConditions(3).Font
.Bold = True
.Italic = False
End With
End Sub
HTMLでは、半角スペースは複数並んでいてもひとつとして処理されるので、ブログにコピペするとインデントが消えて見づらいな。
●レガシィB4(BL5A)のECUチューン関連目次はこちら
●レガシィB4(BL5A) 関連目次はこちら