最近では専門家ではなくてもデータ分析ができたほうがいいという話をよく聞きます。そういう話の中で利用されているソフトはだいたい専用ソフトかエクセルといった感じでしょうか。
ただ数は少ないですがSQLを使おうというのもあるようです。実際SQLは非常に便利でエクセルより多少敷居は高いですが、少し使えればエクセルでは面倒な処理も簡単にできたりします。経験上、ワークシート関数、VBA、SQLを使い分ければほとんどの処理ができると思います。
ですが、いざSQLを使おうとするとどのソフトを使ったらいいのか迷うと思います。一般的にはAccess、SQL Server、MySQL(MariaDB)、SQLiteあたりがお勧めされていますが、もしPCにAccessが入っているならAccessが一番手軽に始められるのではないかと思います。今回はSQLiteを採用していますが、私も最初はAccessを使っていました。
中小企業で働いているとデータ分析やITの専門家がいない場合も多いので、自分たちで少しでもデータ分析ができると役に立つことが多いです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 |
Sub auto_open() Application.CommandBars("cell").Reset With Application.CommandBars("cell").Controls.Add .OnAction = "CreateTable" .Caption = "テーブル作成" End With With Application.CommandBars("cell").Controls.Add .OnAction = "QuerySelect" .Caption = "SQL実行" End With End Sub Sub auto_close() On Error Resume Next For i = 1 To 2 Application.CommandBars("cell").Controls("テーブル作成").Delete Application.CommandBars("cell").Controls("SQL実行").Delete Next i End Sub Function GetCon() GetCon = "DRIVER=SQLite3 ODBC Driver; DataBase=" & ThisWorkbook.Path & "\dat" End Function Function GetType(s) '型はSQLite以外の互換性のため Select Case s Case "s" tmp = "varchar(255)" Case "i" tmp = "bigint" Case "m" tmp = "money" Case "d" tmp = "datetime" End Select GetType = tmp End Function Sub CreateTable() Set cn = CreateObject("adodb.connection") cn.Open GetCon tbl = InputBox("テーブル名") If tbl = "" Then Exit Sub 'テーブル削除 On Error Resume Next cn.Execute "drop table " & tbl 'テーブル作成 On Error GoTo 0 fld = "" For c = Selection(1).Column To Selection(Selection.Count).Column tmp = GetType(Right(ActiveSheet.Cells(Selection(1).Row, c).Value, 1)) fld = fld & ActiveSheet.Cells(Selection(1).Row, c).Value & " " & tmp & "," Next c fld = Left(fld, Len(fld) - 1) q = "create table " & tbl & " (id integer primary key," & fld & ")" cn.Execute q: Debug.Print q 'データ登録 fld = "" For c = Selection(1).Column To Selection(Selection.Count).Column fld = fld & ActiveSheet.Cells(Selection(1).Row, c).Value & "," Next c fld = Left(fld, Len(fld) - 1) For r = (Selection(1).Row) + 1 To Selection(Selection.Count).Row For cc = Selection(1).Column To Selection(Selection.Count).Column rec = rec & "'" & ActiveSheet.Cells(r, cc).Value & "'" & "," Next cc rec = Left(rec, Len(rec) - 1) q = "insert into " & tbl & " (" & fld & ") values (" & rec & ")" cn.Execute q: Debug.Print q rec = "" Next r If cn.State = 1 Then cn.Close Set cn = Nothing MsgBox "done" End Sub Sub ExecuteQuery(q) Set cn = CreateObject("adodb.connection") Set rn = CreateObject("adodb.recordset") cn.Open GetCon rn.Open q, cn 'フィールド作成 i = 0 For c = Selection(1).Column To (Selection(1).Column + rn.Fields.Count) - 1 Cells(Selection(Selection.Count).Row + 1, c).Value = rn.Fields(i).Name i = i + 1 Next c 'データ読込 Cells(Selection(Selection.Count).Row + 2, Selection(1).Column).CopyFromRecordset rn If rn.State = 1 Then rn.Close Set rn = Nothing If cn.State = 1 Then cn.Close Set cn = Nothing End Sub Sub ExecuteNonQuery(q) Set cn = CreateObject("adodb.connection") cn.Open GetCon cn.Execute q If cn.State = 1 Then cn.Close Set cn = Nothing End Sub Sub QuerySelect() If Selection.Rows.Count = 1 Then q = ActiveCell.Value Else For r = 1 To Selection.Rows.Count q = q & " " & Selection(r) Next r End If If Left(q, 1) = "s" Then ExecuteQuery (q) Else ExecuteNonQuery (q) End If End Sub |
追記
ちょっと原因は分からないけど、SQLite標準のカラムのデータ型(textやinteger)でテーブルを作成するとCopyFromRecordsetで取得できなくなる。
varchar(255)をカラムのデータ型に指定しているとCopyFromRecordsetで問題なく取得できる。
CopyFromRecordsetはテーブル作成時の型の影響を受けているらしい。とりあえずCopyFromRecordsetを使わないパターン
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 |
Sub auto_open() Application.CommandBars("cell").Reset With Application.CommandBars("cell").Controls.Add .OnAction = "CreateTable" .Caption = "テーブル作成" End With With Application.CommandBars("cell").Controls.Add .OnAction = "QuerySelect" .Caption = "SQL実行" End With End Sub Sub auto_close() On Error Resume Next For i = 1 To 2 Application.CommandBars("cell").Controls("テーブル作成").Delete Application.CommandBars("cell").Controls("SQL実行").Delete Next i End Sub Function GetCon() 'GetCon = "DRIVER=SQLite3 ODBC Driver; DataBase=" & ThisWorkbook.Path & "\dat" GetCon = "DRIVER=SQLite3 ODBC Driver; DataBase=" & Sheets("設定").Cells(1, 2).Value End Function Function GetType(s) Select Case s Case "s" tmp = "text" Case "i" tmp = "integer" End Select GetType = tmp End Function Sub CreateTable() Set cn = CreateObject("adodb.connection") cn.Open GetCon tbl = InputBox("テーブル名") If tbl = "" Then Exit Sub 'テーブル削除 On Error Resume Next cn.Execute "drop table " & tbl 'テーブル作成 On Error GoTo 0 Set topLeft = Selection(1) Set bottomRight = Selection(Selection.Count) head = "" For c = topLeft.Column To bottomRight.Column tmp = GetType(Right(ActiveSheet.Cells(topLeft.Row, c).Value, 1)) head = head & ActiveSheet.Cells(topLeft.Row, c).Value & " " & tmp & "," Next c head = Left(head, Len(head) - 1) q = "create table " & tbl & " (id integer primary key," & head & ")" cn.Execute q 'データ登録 head = "" For c = topLeft.Column To bottomRight.Column head = head & ActiveSheet.Cells(topLeft.Row, c).Value & "," Next c head = Left(head, Len(head) - 1) For r = topLeft.Row + 1 To bottomRight.Row For c = topLeft.Column To bottomRight.Column body = body & "'" & ActiveSheet.Cells(r, c).Value & "'" & "," Next c body = Left(body, Len(body) - 1) q = "insert into " & tbl & " (" & head & ") values (" & body & ")" cn.Execute q body = "" Next r If cn.State = 1 Then cn.Close Set cn = Nothing MsgBox "done" End Sub Sub ExecuteQuery(q) Set cn = CreateObject("adodb.connection") Set rn = CreateObject("adodb.recordset") cn.Open GetCon rn.Open q, cn Set topLeft = Selection(1) Set bottomRight = Selection(Selection.Count) '見出 i = 0 For c = topLeft.Column To (topLeft.Column + rn.Fields.Count) - 1 ActiveSheet.Cells(bottomRight.Row + 1, c).Value = rn.Fields(i).Name i = i + 1 Next c '本体 r = 2 Do Until rn.EOF i = 0 For c = topLeft.Column To (topLeft.Column + rn.Fields.Count) - 1 ActiveSheet.Cells(bottomRight.Row + r, c).Value = rn.Fields(i) i = i + 1 Next c rn.MoveNext r = r + 1 Loop If rn.State = 1 Then rn.Close Set rn = Nothing If cn.State = 1 Then cn.Close Set cn = Nothing End Sub Sub ExecuteNonQuery(q) Set cn = CreateObject("adodb.connection") cn.Open GetCon cn.Execute q If cn.State = 1 Then cn.Close Set cn = Nothing End Sub Sub QuerySelect() If Selection.Rows.Count = 1 Then q = ActiveCell.Value Else For r = 1 To Selection.Rows.Count q = q & " " & Selection(r) Next r End If If Left(q, 1) = "s" Then ExecuteQuery (q) Else ExecuteNonQuery (q) End If End Sub |