Private Sub Workbook_Open()’ブックを開いた時のイベントプロシージャ。
Worksheets("鉄道").Visible = True’鉄道シートを再表示。
Worksheets("鉄道").Unprotect’鉄道シートの保護を解除。
Worksheets("鉄道").Range("D1").Formula = "=COUNTA(B2:B65536)"’鉄道シートのD1セルに件数を数える「COUNTA関数」を記入。
ActiveSheet.Protect’鉄道シートを保護。
Worksheets("鉄道").Visible = False’鉄道シートを非表示。
Worksheets("駅").Select’駅シートを選択。
ActiveSheet.Unprotect’駅シートの保護を解除。
Worksheets("駅").Range("M1").Formula = "=COUNTA(B10001:B20000)"’駅シートのM1セルに件数を数える「COUNTA関数」を記入。
ActiveSheet.Protect’鉄道シートを保護。
End Sub
------------------------------------------------------------------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)’シート上でダブルクリックしたときのイベントプロシージャ。
UserForm1.Show’ユーザーフォームを表示。
End Sub
------------------------------------------------------------------------------------
Private Sub CommandButton1_Click()’閉じるボタンをクリックした時のイベントプロシージャ。
UserForm1.Hide’ユーザーフォームを非表示。
Range("A1").Select’A１セルを選択。
End Sub
------------------------------------------------------------------------------------
Private Sub UserForm_Initialize()’ユーザーフォームを開いたときのイベントプロシージャ
Dim 最終行 As Integer’変数を宣言。
Dim 表示範囲 As String’変数を宣言。
最終行 = Worksheets("鉄道").Range("D1").Value’変数「最終行」に鉄道シートのD1セルの値を代入。
表示範囲 = "鉄道!B2:B" & 最終行’変数「表示範囲」に「鉄道!B2:B" & 最終行」を代入。
UserForm1.ComboBox1.RowSource = 表示範囲’コンボボックス１のRowSourseに変数「表示範囲」を代入。
End Sub
------------------------------------------------------------------------------------
Private Sub ComboBox1_Change()’コンボボックス１を変更した際のイベントプロシージャ。
Dim 条件 As String’変数を宣言。
条件 = UserForm1.ComboBox1.Text ’変数「条件」へコンボボックス１のテキストを代入。
Application.ScreenUpdating = False’画面を停止。
Worksheets("駅").Select’駅シートを選択。
ActiveSheet.Unprotect’駅シートの保護を解除。
Worksheets("駅").Range("M1").Formula = "=COUNTA(B10001:B20000)"’駅シートのM1セルに件数を数える「COUNTA関数」を記入。
Range("A10000:Z20000").Select’セル範囲を選択。
Selection.ClearContents’選択範囲をクリア。
Range("A1").Select’A１セルを選択。
Selection.AutoFilter’オートフィルターをかける。
Selection.AutoFilter Field:=5, Criteria1:=条件’変数「条件」で第五列を検索。
Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Select’抽出範囲のみを選択。
Selection.Copy’選択範囲をコピー。
Range("A10000").Select’セルを選択。
ActiveSheet.Paste’貼り付け。
ActiveSheet.Protect’駅シートを保護。
Application.CutCopyMode = False’コピーモード解除。
Application.ScreenUpdating = True’画面停止を解除。
End Sub
------------------------------------------------------------------------------------
Private Sub ComboBox2_Enter()’コンボボックス２に入った際のイベントプロシージャ。
Dim 最終行 As Integer’変数を宣言。
Dim 表示範囲 As String’変数を宣言。
最終行 = Worksheets("駅").Range("M1").Value’変数「最終行」に駅シートのM1セルの値を代入。
表示範囲 = "駅!B10001:B" & 最終行 + 10001’変数「表示範囲」に「駅!B10001:B" & 最終行 + 10001」を代入。
UserForm1.ComboBox2.RowSource = 表示範囲’コンボボックス１のRowSourseに変数「表示範囲」を代入。
End Sub
------------------------------------------------------------------------------------
| 上記二つのデータベースを元に作成した「全国駅名一覧フォーム」と「銀行支店名一覧フォーム」はこちらからサンプルファイルをダウンロードすることが出来ます。 |
Ｅｘｃｅｌの場合、アドインが公開されてはいるものの、フォームのテキストボックスでAccess並にこれを利用することは難しいようです。そこで今回は郵政公社のダウンロードデータを利用して、私が「顧客管理名簿」の中で実際に使用している住所変換マクロをご紹介します。Microsoftの郵便番号は更新が不定期（ってゆか、数か月に一度）であるのに対し、郵政公社はさすがに毎月更新しています。
Private Sub CommandButton1_Click()’検索ボタンを押す。
Dim 検索値 As String’変数を宣言。
Dim 検索セル As Range’変数を宣言。
Dim 検索セル2 As Range’変数を宣言。
検索値 = TextBox1.Text’変数「検索値」にTextBox1のテキストを代入。
Application.ScreenUpdating = False’画面を停止。
Worksheets("東日本").Visible = True’東日本シートを再表示。
Worksheets("西日本").Visible = True’西日本シートを再表示。
Worksheets("東日本").Select’東日本シートを選択（省略可）。
With Worksheets("東日本")’
Set 検索セル = .Range("A1:A65536").Find(検索値)’Range("A1:A65536")の該当検索値をオブジェクト変数「検索セル」に代入。
If 検索セル Is Nothing Then’検索セルに該当がない場合。
Worksheets("西日本").Select’西日本シートを選択（省略不可）。
With Worksheets("西日本")’
Set 検索セル2 = Worksheets("西日本").Range("A1:A65536").Find(検索値)’Range("A1:A65536")の該当検索値をオブジェクト変数
’「検索セル2」に代入。
If 検索セル2 Is Nothing Then’検索セル2に該当がない場合。
Label1.Caption = "検索値は存在しません。"’ラベルにメッセージを表示。
Else’検索セル2に該当があったら。
TextBox2.Text = Cells(検索セル2.Row, 2)’該当した検索セル2の2列目をTextBox2に表示。
Label1.Caption = ""’ラベルにメッセージを表示しない。
End If’
End With’
Else’
TextBox2.Text = Worksheets("東日本").Cells(検索セル.Row, 2)’該当した検索セルの2列目をTextBox2に表示。
Label1.Caption = ""’ラベルにメッセージを表示しない。
End If’
End With’
Worksheets("東日本").Visible = False’東日本シートを非表示。
Worksheets("西日本").Visible = False’西日本シートを非表示。
Application.ScreenUpdating = True’画面停止を解除。
End Sub’
------------------------------------------------------------------------------------
サンプルファイルはこちらからダウンロードすることが出来ます。
その列が完了すると右側の列に移ります。なお次項の第四回 データを複合条件で抽出するは同じデータを使って作りました。これらをご覧になれば、
ExcelにおいてもAccessに負けないデータベースプログラムが構築できることがお分かりになると思います。
Private Sub ボタン2_Click()’ボタン2をクリックしたときのイベントプロシージャ。
Unload UserForm9’ UserForm9を初期化（空白）にする。
UserForm9.次へ.Enabled = False’ボタン「次へ」を使用不可に。
UserForm9.Label1.Caption = "検索値を入れて検索ボタンを押してください。"’Label1のキャプションを変える。
UserForm9.Show vbModeless’シート変更可能な状態でUserForm9を表示。
End Sub
------------------------------------------------------------------------------------
Private Sub 検索_Click()’「検索」ボタンをクリックしたときのイベントプロシージャ。
Dim 検索値 As String’変数を宣言。
On Error GoTo errhandler’この行より下でエラーが起きたら場合はerrhandler:の行へ跳ぶ。
検索値 = TextBox1.Value’変数「検索値」にTextBox1の値を代入。
If 検索値 = "" Then’検索値 が空欄の場合。
MsgBox "検索する語句を入れてください。"’メッセージを表示。
TextBox1.SetFocus’TextBox1にカーソルを移動。
Else
Cells.Find(What:=検索値, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False).Activate
’シート上の全セルを対象に、アクティブセルの次から、
’列方向に検索、その際に大文字小文字・全角半角は区別しない。
検索.Enabled = False’ボタン「検索」を使用不可に。
次へ.Enabled = True’ボタン「次へ」を使用可に。
Label1.Caption = "次をさがすには「次へ」ボタンを押してください。"’Label1のメッセージを変える。
errhandler:’
Select Case Err.Number’エラーコードが次のケースのとき。
Case 91’エラーナンバー91=検索値が存在しない場合。
MsgBox "その検索値は存在しません。"’メッセージを表示する。
End Select’
End If
End Sub
------------------------------------------------------------------------------------
Private Sub 次へ_Click()’「次へ」ボタンをクリックしたときのイベントプロシージャ。
Cells.FindNext(After:=ActiveCell).Activate’次を検索。
End Sub
------------------------------------------------------------------------------------
Private Sub 閉じる_Click()’「閉じる」ボタンをクリックしたときのイベントプロシージャ。
UserForm9.Hide’UserForm9を非表示に。
End Sub
そんなわけでサンプルファイルでは変数を用いてます。でも変数って使い過ぎると訳が分からなくなりますよね。何でもかんでも変数にするのも関心しないし、
特にσ(^_^・・・アルファベットの変数って嫌いだなぁ(笑)。
Private Sub Workbook_Open()’ブックを開いた時のイベントプロシージャ。
ActiveSheet.Unprotect’顧客名簿シートの保護を解除。
Worksheets("顧客名簿").Range("R1").Formula = "=SUBTOTAL(3,A3:A65536)"’顧客名簿シートのR1セルに、「SUBTOTAL関数」を
’引数に「3」を選んで記入（抽出後の件数が表示される）。
ActiveSheet.Protect’顧客名簿シートを保護。
End Sub
Sub ボタン3_Click()’ボタン3をクリックしたときのイベントプロシージャ。
Unload UserForm1’UserForm1を初期化します。
UserForm1.Show vbModeless’シート変更可能な状態でUserForm1を表示。
End Sub
Private Sub 検索_Click()’「検索」ボタンをクリックしたときのイベントプロシージャ。
If ComboBox1.Value <> "" And ComboBox2.Value <> "" And ComboBox3.Value <> "" Then’ComboBox1,2,3共空白でない場合。
ActiveSheet.Unprotect’シートの保護を解除。
ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=3, Criteria1:=ComboBox1.Text’ComboBox1のtext条件でField3を抽出。
ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=4, Criteria1:=ComboBox2.Text’ComboBox2のtext条件でField4を抽出。
ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=5, Criteria1:=ComboBox3.Text’ComboBox3のtext条件でField5を抽出。
Label4.Caption = Range("R1").Value & "件"’Label4.CaptionにＲ１セルの値と文字列”件”を代入。
ActiveSheet.Protect’シートを保護。
ElseIf ComboBox1.Value <> "" And ComboBox2.Value <> "" And ComboBox3.Value = "" Then’ComboBox1,2が空白でなく3が空白の場合。
ActiveSheet.Unprotect’シートの保護を解除。
ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=3, Criteria1:=ComboBox1.Text’ComboBox1のtext条件でField3を抽出。
ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=4, Criteria1:=ComboBox2.Text’ComboBox2のtext条件でField4を抽出。
Label4.Caption = Range("R1").Value & "件"’Label4.CaptionにＲ１セルの値と文字列”件”を代入。
ActiveSheet.Protect’シートを保護。
ElseIf ComboBox1.Value <> "" And ComboBox2.Value = "" And ComboBox3.Value = "" Then’ComboBox1が空白でなく2,3が空白の場合。
ActiveSheet.Unprotect’シートの保護を解除。
ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=3, Criteria1:=ComboBox1.Text’ComboBox1のtext条件でField3を抽出。
Label4.Caption = Range("R1").Value & "件"’Label4.CaptionにＲ１セルの値と文字列”件”を代入。
ActiveSheet.Protect’シートを保護。
ElseIf ComboBox1.Value = "" And ComboBox2.Value = "" And ComboBox3.Value = "" Then’ComboBox1,2,3が空白の場合。
Label4.Caption = "条件が選択されていません。" ’Label4.Captionにメッセージを表示。
ComboBox1.SetFocus’ComboBox1にカーソルを跳ばす。
ElseIf ComboBox1.Value = "" And ComboBox2.Value <> "" And ComboBox3.Value <> "" Then
ActiveSheet.Unprotect’シートの保護を解除。
ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=4, Criteria1:=ComboBox2.Text’ComboBox2のtext条件でField4を抽出。
ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=5, Criteria1:=ComboBox3.Text’ComboBox3のtext条件でField5を抽出。
ActiveSheet.Protect’シートを保護。
ElseIf ComboBox1.Value = "" And ComboBox2.Value = "" And ComboBox3.Value <> "" Then’ComboBox1,2が空白で3が空白でない場合。
ActiveSheet.Unprotect’シートの保護を解除。
ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=5, Criteria1:=ComboBox3.Text’ComboBox3のtext条件でField5を抽出。
Label4.Caption = Range("R1").Value & "件"’Label4.CaptionにＲ１セルの値と文字列”件”を代入。
ActiveSheet.Protect’シートを保護。
ElseIf ComboBox1.Value <> "" And ComboBox2.Value = "" And ComboBox3.Value <> "" Then’ComboBox1,3が空白でなく2が空白の場合。
ActiveSheet.Unprotect’シートの保護を解除。
ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=3, Criteria1:=ComboBox1.Text’ComboBox1のtext条件でField3を抽出。
ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=5, Criteria1:=ComboBox3.Text’ComboBox3のtext条件でField5を抽出。
Label4.Caption = Range("R1").Value & "件"’Label4.CaptionにＲ１セルの値と文字列”件”を代入。
ActiveSheet.Protect’シートを保護。
End If
End Sub
------------------------------------------------------------------------------------
Private Sub 閉じる_Click()
ActiveSheet.Unprotect’シートの保護を解除。
ActiveSheet.Range("A1").Select’Ａ１セルを選択。
Selection.AutoFilter’フィルター状態を解除
ActiveSheet.Protect’シートを保護。
Unload UserForm1’UserForm1を初期化。
UserForm1.Hide’UserForm1を非表示に。
End Sub
例）左がデータベースとしては悪い例、右が良い例
|
皆さんの中で、実際にAccessを使ってテーブル設計をなさった経験のある方も、いらっしゃると思います。
テーブル設計を始めて、まず最初に突き当たるのが、この「数値型か？テキスト型か？」という問題です。もう少し具体的に言いますと、それはレコードのＩＤ、すなわちキーコードの「型」のことです。テーブルのフィールドの中で、例えば人の名前は「テキスト型」でなければならないし、商品の値段は「数値型」でなければならない、ということは、誰しも予想することができます。しかしキーコードはどうでしょうか？
データベースのレコードは「一意性」を持つ、すなわち「他に同じ物がない」状態でなくてはならない、ということをお聞きになったことがあると思います。例えば、たまたま同じ住所にお住まいの「山田太郎」さんという人が二人いた場合、名前と住所では別人であることの「判別」は不可能です。でも、この二人に別々のコードが付けられていれば「別人」であることを認識出来るわけです。逆に、このお二人が、実は同一人物であって、担当者がたまたま誤って別のコードをつけてしまった、などということも、もちろんありえます。その場合は、その他のフィールドデータ、例えば「電話番号」「家族構成」「勤め先」などから類推して、あるいはご本人に確認するなどして、同一であることが確定したならば、片方のデータを「削除」すべきでしょう。
話を本題に戻したいと思います。それではキーコードは、数値型にしたらよいのか？テキスト型にしたらよいのか？というおはなしですが、結論から言うと「どちらもありうる」ということになります。それどころか、ひとつのデータベースの中で、その両方を「使い分ける」ことさえ可能です。ただし、そのようにどちらもありうる中で、なお私の意見を申し上げるならば「数値型になさい」ということになるかと思います。
これから、この数値型・テキスト型のデータとしての違いを少し説明しますが、難しい内容は一切省きます。その「現象面」から、二つの型の違い、のみを見ていきたいと思います。その前提として、キーコードの「最大唯一の目的」が、レコードに「一意性を持たせること」である、ということをまずご念頭においていただきたいと思います。極論すれば、キーコードの持つ意味は、これ以外ないといって過言ではありません。では数値型から見ていくことにいたしましょう。
＜数値型＞
基本的には「１」から順番にコードを振っていくことになります。「２」「３」「４」・・・とキーコードを振り分けていくことになります。
ここで皆さんの中にハタと気がつかれる方が、いらっしゃるとおもいます。「１」～「３６０」というように「桁数」がばらばらになる、のではないかというふうに。そうなのです、基本的に数値型では、このような表示になってしまいます。そして誰しも思うのは「一覧表」等のレポートに印刷した場合の「見栄え」の問題です。表の見栄えを良くするためには、桁数が揃っていたほうが良いと、誰しも思うことでしょう。でもご安心ください、解決方法はあります。テープル、フォーム等の書式プロパティを「００００００」とすることによって「１」を「０００００１」と６桁で表示することができるのです。ですから「桁数」表示は、あとからいくらでも増やすことが出来ます。この点については問題ありませんので、ご安心いただきたいと思います。
また、テーブルやフォームのプロパティを変えずとも、コードの桁数を統一する方法は、実は他にもあります。キーコードそのものを、最初から６桁にするのです。コードの初期値を「１００００１」とすれば「１００００２」「１００００３」・・・と増やしていって、最終的には「９９９９９９」までは、６桁のコードを確保できます。データの最大予想数にあわせて（例えば顧客コードの場合）１００万代から始める、というようにするわけです。
データの数というものは、その種類によって自ずから「最高数値」の予想はつくものです。例えば社員コードの数は、どんな大会社であっても、せいぜい多くて５桁もあればよいでしょう。小さな会社であれば２桁で済む場合だってあります。それに対して、顧客コードはどうでしょうか？６桁で足りなくなる可能性は、ゼロではありませんね。顧客コードはなるべくコードの桁数を、多めにとっておいたほうが「無難」です。商品コードなども「多品種少量」業種の場合、桁数を多めににとっておくべきでしょう。逆に部署コードなどはせいぜい４桁もあれば足りるでしょう。
＜テキスト型＞
さて、テキスト型です。テキスト型の利点（同時に欠点でもあるのですが）は「文字列や記号を併用できる」ということにあります。たった今「利点」と申し上げましたが、実のところ、私には欠点のほうがはるかに多いと思われます。
実際問題、文字列キーコードが要求される最大の理由は、カスタマーが「それまでコードに文字列を用いていたから」ということが挙げられます。つまり先方が、システム構築にあたって、従来の業務フローを「見直す」ことなく、そのまま「踏襲」することを主張なさり、そこを押し切られるような場合です。笑い話のような事実なのですが、ある会社では、社員番号の最初に「役職」を表すアルファベットを付けていた。どういうことかといえば、昇進するたびに「社員コード」が変わる（笑）。これはもう、メチャクチャです。
唯一、それもありかな？と、私が思うのは「商品コード」の場合です。各社が取り扱う商品の「シリアル番号（ないしは型式）」というのは、言うまでもなく、既に「一意性」をもたせているわけです。そしてそういった会社では、シリアル番号を、識別の基準として普通に用いているわけです。そうなってくると、シリアル番号そのものをキーコードにしてもよいかな？という考えも、頭を過ることになります。ただし、その条件としては、シリアル番号の管理そのものが、よほど厳重になされている必要があります。重複は許されません。
シリアル番号には、アルファベット、ハイフン、かぎかっこ等々、ありとあらゆる「文字列」が使用されます。そうなると、キーコードはテキスト型である必要が生じます。
こう申し上げると、私が商品コードにはシリアル番号を用いることを積極的に「容認」しているではないか、と思われるかも知れませんが、そうではありません。基本はあくまでも、シリアル番号とは別に、キーコードをつけて、それを「数値型になさい」というのが私の考えです。
データベースの目的のうち重要なもののひとつに、いろいろな条件でデータを抽出したり、並べ替えたりというものがあります。その条件となりうるフィールドは、ぜひともキーコード以外とすべきです。キーコードに「一意性」以外の「意味」「属性」を持たせることは、絶対にしてはなりません。そのためにはキーコードは、基本的に「数値型」にすべきなのです。
最後に「蛇足」になりますが、文字列を並べ替えたときには、説明のできないことが起きることがあります。次の図は、文字列のキーコードを試しに「昇順」に並べ替えたところです。
 |
これを見ると「辞書」的な意味での「並び」の優先順位は「①記号→②数字→③文字」の順番であるように見えます。ところがもしそうだとすると、私は最後の二つは並びが「逆」のような気がするのですが、如何でしょうか？このことが何を意味するかといえば、本当に厳密な「昇順」「降順」を表示するためには、キーコードに文字列を使用することによってではなく、フィールドに「フリガナ」を設けるのが最良である、ということになります。何を申し上げたいかといえば、上記の商品コードです。やはり商品テーブルを作る場合「商品コード」「シリアル番号」「商品名」「商品名のフリガナ」というのが、理想的ではないでしょうか。