Category Archives: Excel VBA

厚生労働省 一般職業紹介状況(職業安定業務統計) 有効求人倍率データの整形

Sub JobsToApplicantsRatio()
'配列宣言
Dim Year As Integer
Dim Month As Integer
Dim SheetNumber As Integer
Dim StartYear As Integer
Dim EndYear As Integer
Dim DateColumn As Integer
Dim StartRow As Integer
Dim LastRow As Integer
Dim JanCol As Integer
Dim DecCol As Integer
Dim StartMonth As Integer
Dim EndMonth As Integer
Dim OutputRow As Integer
Dim OutputDataRow As Integer
Dim MonthColumn As Integer
'変数初期値(季節調整値.実数を利用する場合は適宜変更)
SheetNumber = ActiveSheet.Index
StartYear = 1963
EndYear = 2014
JanCol = 25
DecCol = 36
StartMonth = 1
EndMonth = 10
StartRow = 5
LastRow = 56
DateColumn = 41
'日付列作成
Columns(DateColumn).Select
Selection.NumberFormatLocal = "yyyy/m/d"
OutputRow = StartRow
For Year = StartYear To EndYear - 1
  For Month = 1 To 12
    Worksheets(SheetNumber).Cells(OutputRow, DateColumn) = Year & "/" & Month & "/1"
    OutputRow = OutputRow + 1
  Next
Next
For Month = 1 To EndMonth
  Worksheets(SheetNumber).Cells(OutputRow, DateColumn) = EndYear & "/" & Month & "/1"
  OutputRow = OutputRow + 1
Next
'データ列作成
OutputRow = StartRow
For OutputDataRow = StartRow To LastRow
  For MonthColumn = JanCol To DecCol
    Worksheets(SheetNumber).Cells(OutputRow, DateColumn + 1) = Worksheets(SheetNumber).Cells(OutputDataRow, MonthColumn)
    OutputRow = OutputRow + 1
  Next
Next
'列名称
Worksheets(SheetNumber).Cells(StartRow - 1, DateColumn) = "date"
Worksheets(SheetNumber).Cells(StartRow - 1, DateColumn) = "日付"
Select Case SheetNumber
 Case 1
  Worksheets(SheetNumber).Cells(StartRow - 1, DateColumn + 1) = "Active job openings-to-applicants ratio:Seasonally adjusted:Excluding new school graduates:Including part-timers"
  Worksheets(SheetNumber).Cells(StartRow - 1, DateColumn + 1) = "有効求人倍率.季節調整値.除新卒.含パートタイム"
 Case 2
  Worksheets(SheetNumber).Cells(StartRow - 1, DateColumn + 1) = "Active job openings-to-applicants ratio:Seasonally adjusted:Excluding new school graduates and part-timers"
  Worksheets(SheetNumber).Cells(StartRow - 1, DateColumn + 1) = "有効求人倍率.季節調整値.除新卒及びパートタイム"
 Case 3
  Worksheets(SheetNumber).Cells(StartRow - 1, DateColumn + 1) = "Active job openings-to-applicants ratio:Seasonally adjusted:Part-timers"
  Worksheets(SheetNumber).Cells(StartRow - 1, DateColumn + 1) = "有効求人倍率.季節調整値.パートタイム"
End Select
End Sub