Inageのエクセル小技教室
第一回 全国駅名一覧フォーム、銀行支店名一覧フォーム
第二回 郵便番号からの住所呼び出しフォーム
第三回 キーワードを次々に検索する
第四回 データを複合条件で抽出する

★Inageからエクセラー・ビジネスマンへの一口アドバイス★

データベースよもやま話
(1)数値型か?テキスト型か?
(2)DateDiff関数
☆☆☆エクセル工房へ☆☆☆☆☆
☆☆☆
エクセル・リンクページへ ☆☆☆

エクセル工房のソフトについて
☆☆☆☆よくある質問コーナー☆☆☆☆
Excel工房 Inage
第一回 全国駅名一覧フォーム、銀行支店名一覧フォーム

エクセルVBAの解説ホームページは諸先輩たちのすばらしい仕事がすでにたくさん存在しています。そんな中であまり紹介されていないもので便利な技を紹介していこうと考えました。
Accessでは難なく表現できることがExcelでは複雑な処理となる、あるいはExcelでは簡単に出来ることがAccessでは出来ない。そんな経験に思い当たる方がいらっしゃるのではないでしょうか。表計算ソフトとデータベースソフトの違いを実感するのはそういったときです。
第一回目はExcelを使った「Accessまがいの検索フォーム」。実は同様の処理はAccessにおいてはそれほどむずかしいことではありません。それを今回はExcelを使ってやってみようというわけです。ユーザーフォーム上に二つのコンボボックス①②があります。①のコンボボックスを開くと全国の鉄道路線が表示され、②のコンボボックスを開くとその路線の全駅が表示される、そのようなマクロを書いてみようと思います。シートは「鉄道」と「駅」の2枚用意します。使用するデータはスナフキんさんがWeb上で公開なさっている「全国駅名一覧」です。
日本全国駅名一覧 http://www5a.biglobe.ne.jp/~harako/data/station.htm
同様にDoratheraさんがWebで公開なさっている「全国統一銀行コード」も利用させていただきました。
全国統一金融機関コード http://aporia.toypark.in/modules/d3downloads/index.php?cid=1
貴重なデータを提供してくださっているスナフキんさんとDoratheraさんにこの場を借りて心よりお礼申し上げます。
ブックを開くとアイウエオ順に並んだ「駅」シートが表示されます。
シートの上でダブルクリックすると「検索」フォームが表示されます。
「▼」ボタンを押すと・・・。
ドロップダウンリストが現れます。
「総武本線」を選んでクリックします。
「総武本線」と入力されました。
同様に「駅」のドロップダウンリストを表示させると・・・。
総武線の駅のみが表示されています。
「千葉」を入力しました。
フォームを閉じるとシートはオートフィルター画面になっています。
シートの10000行目にフィルターにかけられた総武線の駅名がコピーされています。
ComboBox1に表示される鉄道シート。
1.ワークブックのプロシージャ
------------------------------------------------------------------------------------
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
------------------------------------------------------------------------------------
2.駅シートのプロシージャ
------------------------------------------------------------------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)’シート上でダブルクリックしたときのイベントプロシージャ。

   UserForm1.Show’ユーザーフォームを表示。

End Sub
------------------------------------------------------------------------------------
3.Userform1のプロシージャ
------------------------------------------------------------------------------------
Private Sub CommandButton1_Click()’閉じるボタンをクリックした時のイベントプロシージャ。

   UserForm1.Hide’ユーザーフォームを非表示。
   Range("A1").Select’A1セルを選択。

End Sub
------------------------------------------------------------------------------------
Private Sub UserForm_Initialize()’ユーザーフォームを開いたときのイベントプロシージャ

Dim 最終行 As Integer’変数を宣言。
Dim 表示範囲 As String’変数を宣言。

   最終行 = Worksheets("鉄道").Range("D1").Value’変数「最終行」に鉄道シートのD1セルの値を代入。
   表示範囲 = "鉄道!B2:B" & 最終行’変数「表示範囲」に「鉄道!B2:B" & 最終行」を代入。
   UserForm1.ComboBox1.RowSource = 表示範囲’コンボボックス1のRowSourseに変数「表示範囲」を代入。

End Sub
------------------------------------------------------------------------------------
Private Sub ComboBox1_Change()’コンボボックス1を変更した際のイベントプロシージャ。

Dim 条件 As String’変数を宣言。

   条件 = UserForm1.ComboBox1.Text ’変数「条件」へコンボボックス1のテキストを代入。
   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’A1セルを選択。
   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()’コンボボックス2に入った際のイベントプロシージャ。

Dim 最終行 As Integer’変数を宣言。
Dim 表示範囲 As String’変数を宣言。

   最終行 = Worksheets("駅").Range("M1").Value’変数「最終行」に駅シートのM1セルの値を代入。
   表示範囲 = "駅!B10001:B" & 最終行 + 10001’変数「表示範囲」に「駅!B10001:B" & 最終行 + 10001」を代入。
   UserForm1.ComboBox2.RowSource = 表示範囲’コンボボックス1のRowSourseに変数「表示範囲」を代入。

End Sub
------------------------------------------------------------------------------------
上記二つのデータベースを元に作成した「全国駅名一覧フォーム」と「銀行支店名一覧フォーム」はこちらからサンプルファイルをダウンロードすることが出来ます。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
第二回 郵便番号からの住所呼び出しフォーム
このページのトップへ
Microsoft社は、「郵便番号辞書」のアドインをWeb上で提供しております。それをダウンロードすると、例えばAccessのテーブルでは郵便番号を入力してEnterを押せば、別のフィールドに住所が自動入力されます。リレーショナルデータベース・ソフトであるAccessでは、同様の結果がフォーム上でも実現できます。しかし
Excelの場合、アドインが公開されてはいるものの、フォームのテキストボックスでAccess並にこれを利用することは難しいようです。そこで今回は郵政公社のダウンロードデータを利用して、私が「顧客管理名簿」の中で実際に使用している住所変換マクロをご紹介します。Microsoftの郵便番号は更新が不定期(ってゆか、数か月に一度)であるのに対し、郵政公社はさすがに毎月更新しています。
郵便番号ダウンロード http://www.post.japanpost.jp/zipcode/
尚、私のサイトでは「顧客管理名簿」のユーザーのために、コピーしてそのまま使えるよう郵政公社の最新データを加工したものを、適宜用意いたしております。以下の場所に置いてありますのでご自由にダウンロードしてください。今回のサンプルでは、全国の郵便番号データを2枚のシートに持たせますので、かなりの「チカラわざ」に属するマクロになっている、ということをご理解ください。しかし、顧客名簿などの帳票においては大いに力を発揮しますので、是非ともお使いになることをお勧めします。
http://members.jcom.home.ne.jp/t.quantz/soft.files/kokyakukanri/jusyo.lzh
シートの構成は「検索」「東日本」「西日本」の三つです。全国の郵便番号を1枚のシートに持たせることは無理なので東日本と西日本に分けました。ちなみに、Excel2007では1枚に持つことが可能です。Userformを作り、その上にTextBox2個とCommandButton2個、それとLabelをひとつ配置します。
シートに配置した住所検索ボタンを押すとUserform1が表示されます。
TextBox1に郵便番号を入力し、検索ボタンを押すと。
TextBox2に、途中までの住所が表示されます。
該当する検索値がない場合。
Userform1のプロシージャ
------------------------------------------------------------------------------------
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に負けないデータベースプログラムが構築できることがお分かりになると思います。
ブックを開くと「連続検索」ボタンと「絞り込み抽出」ボタンがあります。
「連続検索」ボタンで「曖昧連続検索」フォームを表示させます。
「鈴木」で「検索」ボタンを押すと最初の「鈴木」へ跳びます。
「次へ」ボタンで次の「鈴木」へ跳びます。
1.シートのイベントプロシージャ
------------------------------------------------------------------------------------
Private Sub ボタン2_Click()’ボタン2をクリックしたときのイベントプロシージャ。

   Unload UserForm9’ UserForm9を初期化(空白)にする。
   UserForm9.次へ.Enabled = False’ボタン「次へ」を使用不可に。
   UserForm9.Label1.Caption = "検索値を入れて検索ボタンを押してください。"’Label1のキャプションを変える。
   UserForm9.Show vbModeless’シート変更可能な状態でUserForm9を表示。

End Sub
------------------------------------------------------------------------------------
2.Userform9のプロシージャ

------------------------------------------------------------------------------------                     
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
------------------------------------------------------------------------------------
サンプルファイルはこちらからダウンロードすることが出来ます。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
第四回 データを複合条件で抽出する
このページのトップへ
今回は、ひとつのユーザーフォーム上で複数の条件で「絞り込み検索」を行います。もうここまで来ればExcelがAccessと何ら変わらないデータベース機能を持っていると実感できるはずです。なお3.Userform1のプロシージャで、ComboBox1から3の値は変数にすることも可能です。ってゆか、ふつうはそうするかもしれませんね( ^ ^ ゞ。
そんなわけでサンプルファイルでは変数を用いてます。でも変数って使い過ぎると訳が分からなくなりますよね。何でもかんでも変数にするのも関心しないし、
特にσ(^_^・・・アルファベットの変数って嫌いだなぁ(笑)。
「絞り込み抽出」ボタンを押すと「絞り込み抽出」フォームが表示されます。
「男」「東京都」「会社員」の条件で「検索」ボタンを押してみます。
データが抽出され、フォームには件数が表示されます。
1.ワークブックのプロシージャ
------------------------------------------------------------------------------------
Private Sub Workbook_Open()’ブックを開いた時のイベントプロシージャ。

   ActiveSheet.Unprotect’顧客名簿シートの保護を解除。
   Worksheets("顧客名簿").Range("R1").Formula = "=SUBTOTAL(3,A3:A65536)"’顧客名簿シートのR1セルに、「SUBTOTAL関数」を
   ’引数に「3」を選んで記入(抽出後の件数が表示される)。

   ActiveSheet.Protect’顧客名簿シートを保護。

End Sub
------------------------------------------------------------------------------------
2.シートのイベントプロシージャ
------------------------------------------------------------------------------------
Sub ボタン3_Click()’ボタン3をクリックしたときのイベントプロシージャ。

   Unload UserForm1’UserForm1を初期化します。
   UserForm1.Show vbModeless’シート変更可能な状態でUserForm1を表示。

End Sub
------------------------------------------------------------------------------------
3.Userform1のプロシージャ
------------------------------------------------------------------------------------
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にR1セルの値と文字列”件”を代入。
      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にR1セルの値と文字列”件”を代入。
      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にR1セルの値と文字列”件”を代入。
      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にR1セルの値と文字列”件”を代入。
      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にR1セルの値と文字列”件”を代入。
      ActiveSheet.Protect’シートを保護。

   End If

End Sub
------------------------------------------------------------------------------------
Private Sub 閉じる_Click()

   ActiveSheet.Unprotect’シートの保護を解除。
   ActiveSheet.Range("A1").Select’A1セルを選択。
   Selection.AutoFilter’フィルター状態を解除
   ActiveSheet.Protect’シートを保護。
   Unload UserForm1’UserForm1を初期化。
   UserForm1.Hide’UserForm1を非表示に。

End Sub
------------------------------------------------------------------------------------
サンプルファイルはこちらからダウンロードすることが出来ます。


★Inageからエクセラー・ビジネスマンへの一口アドバイス★
このページのトップへ

①ActiveXコントロールでインターフェイスを整えよう。
自分だけが使うファイルであるならば、それほど気にすることはないのかも知れません。しかしそのファイルがもっぱら他人が使うものだとしたら、ぜひとも考慮したいのが、このActiveXコントロールの活用です。画面をあちこちスクロールしながらの直接入力、入力の手順を覚えるのだけで四苦八苦する、等というのは論外です。どこに何を入力するのかが一目でわかるようにActiveXコントロールフォームを利用します。必要に応じてコンボボックス等も使い、入力し易さを徹底的に追及しましょう。

②二次利用可能なシート設計を心がけよう。
たとえばデータベースの場合を考えてみます。データベースである以上「抽出」「検索」「並べ替え」等が容易にできるシートでなければいけません。また時にはグラフの作成を上司に頼まれるかも知れません。それらに可能な限り応えられるシート構成にすることを心がけましょう。そこではAccessのテーブルの考え方が参考になります。Excelは相当こなせる人でもデータベースソフト経験のない人が、うっかり犯すミスに次のようなものがあります。それは行に追加しなくてはならないデータを列に、つまり横に並べてしまうことです。フィールドという考え方をエクセルに応用した場合、データは行に追加していきます。これは③で説明する「柔軟性」にも密接にかかわってくる問題です(例参照)。

③変更に柔軟性を持たせよう。
よくある例は、関数・マクロを使ってガチガチに作ってあるファイル。高度なマクロを駆使して、大変良くできているのに肝心のシートに柔軟性がない。たとえば入力項目を簡単に増やすことができない。増やすとマクロを大々的に書き変えなくてはならない。これはかなりのエクセル熟達者であっても犯しやすい最も「ポピュラーな」失敗と言えるでしょう。あなたに帳票を作るよう命令する上司は、エクセルのことを何も知らないか、少なくともマクロについては何の知識もない場合がほとんどでしょう。そんな上司が次々と注文を出してくるわけです。「此処に項目をまうひとつ付け足して呉れたまへ」「此処はもつとかういふ具合にならぬかね?」「君是はグラフにはならぬものであらうか?」などと旧仮名遣いで命令してくるわけです。そんなとき、あなたが作っていたマクロが「ガチガチ」だったら、さあ大変です。あそこを直せばこっちが不具合、それを直すと今度は別のところが不具合を起こす・・・。経験ありませんか?ひとつ具体例を挙げましょう。「列の挿入」です。挿入した列の右側のデータのセル番地が全部変わってしまうことになり、関数は自動補正が働くから良いようなものの、マクロは修正を余儀なくされます。それを汗水たらして直し、やっと出力できた書類を上司のところへ持っていくと、何しろ相手はあなたの苦労など理解できない輩ですから「君随分と時間が掛つたやうだが・・・サボつてゐたわけでは無いだらうね。」などとのたまう。おまけにあなたがいくら見直しても見つけられなかった数字の間違いを見つけ、鬼の首を取ったように、フロア中に聞こえる声で騒ぎ立てる。いやはやもうたいへんな騒ぎ・・・で?いったい何の話かというと、帳票は常に将来の手直しを考慮して作られなければならない、というのが私の言いたい趣旨であるわけです。

 例)左がデータベースとしては悪い例、右が良い例

データベースよもやま話
このページのトップへ
(1)数値型か?テキスト型か?

皆さんの中で、実際にAccessを使ってテーブル設計をなさった経験のある方も、いらっしゃると思います。

テーブル設計を始めて、まず最初に突き当たるのが、この「数値型か?テキスト型か?」という問題です。もう少し具体的に言いますと、それはレコードのID、すなわちキーコードの「型」のことです。テーブルのフィールドの中で、例えば人の名前は「テキスト型」でなければならないし、商品の値段は「数値型」でなければならない、ということは、誰しも予想することができます。しかしキーコードはどうでしょうか?

データベースのレコードは「一意性」を持つ、すなわち「他に同じ物がない」状態でなくてはならない、ということをお聞きになったことがあると思います。例えば、たまたま同じ住所にお住まいの「山田太郎」さんという人が二人いた場合、名前と住所では別人であることの「判別」は不可能です。でも、この二人に別々のコードが付けられていれば「別人」であることを認識出来るわけです。逆に、このお二人が、実は同一人物であって、担当者がたまたま誤って別のコードをつけてしまった、などということも、もちろんありえます。その場合は、その他のフィールドデータ、例えば「電話番号」「家族構成」「勤め先」などから類推して、あるいはご本人に確認するなどして、同一であることが確定したならば、片方のデータを「削除」すべきでしょう。

話を本題に戻したいと思います。それではキーコードは、数値型にしたらよいのか?テキスト型にしたらよいのか?というおはなしですが、結論から言うと「どちらもありうる」ということになります。それどころか、ひとつのデータベースの中で、その両方を「使い分ける」ことさえ可能です。ただし、そのようにどちらもありうる中で、なお私の意見を申し上げるならば「数値型になさい」ということになるかと思います。

これから、この数値型・テキスト型のデータとしての違いを少し説明しますが、難しい内容は一切省きます。その「現象面」から、二つの型の違い、のみを見ていきたいと思います。その前提として、キーコードの「最大唯一の目的」が、レコードに「一意性を持たせること」である、ということをまずご念頭においていただきたいと思います。極論すれば、キーコードの持つ意味は、これ以外ないといって過言ではありません。では数値型から見ていくことにいたしましょう。

<数値型>

基本的には「1」から順番にコードを振っていくことになります。「2」「3」「4」・・・とキーコードを振り分けていくことになります。

ここで皆さんの中にハタと気がつかれる方が、いらっしゃるとおもいます。「1」~「360」というように「桁数」がばらばらになる、のではないかというふうに。そうなのです、基本的に数値型では、このような表示になってしまいます。そして誰しも思うのは「一覧表」等のレポートに印刷した場合の「見栄え」の問題です。表の見栄えを良くするためには、桁数が揃っていたほうが良いと、誰しも思うことでしょう。でもご安心ください、解決方法はあります。テープル、フォーム等の書式プロパティを「000000」とすることによって「1」を「000001」と6桁で表示することができるのです。ですから「桁数」表示は、あとからいくらでも増やすことが出来ます。この点については問題ありませんので、ご安心いただきたいと思います。

また、テーブルやフォームのプロパティを変えずとも、コードの桁数を統一する方法は、実は他にもあります。キーコードそのものを、最初から6桁にするのです。コードの初期値を「100001」とすれば「100002」「100003」・・・と増やしていって、最終的には「999999」までは、6桁のコードを確保できます。データの最大予想数にあわせて(例えば顧客コードの場合)100万代から始める、というようにするわけです。

データの数というものは、その種類によって自ずから「最高数値」の予想はつくものです。例えば社員コードの数は、どんな大会社であっても、せいぜい多くて5桁もあればよいでしょう。小さな会社であれば2桁で済む場合だってあります。それに対して、顧客コードはどうでしょうか?6桁で足りなくなる可能性は、ゼロではありませんね。顧客コードはなるべくコードの桁数を、多めにとっておいたほうが「無難」です。商品コードなども「多品種少量」業種の場合、桁数を多めににとっておくべきでしょう。逆に部署コードなどはせいぜい4桁もあれば足りるでしょう。

<テキスト型>

さて、テキスト型です。テキスト型の利点(同時に欠点でもあるのですが)は「文字列や記号を併用できる」ということにあります。たった今「利点」と申し上げましたが、実のところ、私には欠点のほうがはるかに多いと思われます。

実際問題、文字列キーコードが要求される最大の理由は、カスタマーが「それまでコードに文字列を用いていたから」ということが挙げられます。つまり先方が、システム構築にあたって、従来の業務フローを「見直す」ことなく、そのまま「踏襲」することを主張なさり、そこを押し切られるような場合です。笑い話のような事実なのですが、ある会社では、社員番号の最初に「役職」を表すアルファベットを付けていた。どういうことかといえば、昇進するたびに「社員コード」が変わる(笑)。これはもう、メチャクチャです。

唯一、それもありかな?と、私が思うのは「商品コード」の場合です。各社が取り扱う商品の「シリアル番号(ないしは型式)」というのは、言うまでもなく、既に「一意性」をもたせているわけです。そしてそういった会社では、シリアル番号を、識別の基準として普通に用いているわけです。そうなってくると、シリアル番号そのものをキーコードにしてもよいかな?という考えも、頭を過ることになります。ただし、その条件としては、シリアル番号の管理そのものが、よほど厳重になされている必要があります。重複は許されません。

シリアル番号には、アルファベット、ハイフン、かぎかっこ等々、ありとあらゆる「文字列」が使用されます。そうなると、キーコードはテキスト型である必要が生じます。

こう申し上げると、私が商品コードにはシリアル番号を用いることを積極的に「容認」しているではないか、と思われるかも知れませんが、そうではありません。基本はあくまでも、シリアル番号とは別に、キーコードをつけて、それを「数値型になさい」というのが私の考えです。

データベースの目的のうち重要なもののひとつに、いろいろな条件でデータを抽出したり、並べ替えたりというものがあります。その条件となりうるフィールドは、ぜひともキーコード以外とすべきです。キーコードに「一意性」以外の「意味」「属性」を持たせることは、絶対にしてはなりません。そのためにはキーコードは、基本的に「数値型」にすべきなのです。

最後に「蛇足」になりますが、文字列を並べ替えたときには、説明のできないことが起きることがあります。次の図は、文字列のキーコードを試しに「昇順」に並べ替えたところです。


これを見ると「辞書」的な意味での「並び」の優先順位は「①記号→②数字→③文字」の順番であるように見えます。ところがもしそうだとすると、私は最後の二つは並びが「逆」のような気がするのですが、如何でしょうか?このことが何を意味するかといえば、本当に厳密な「昇順」「降順」を表示するためには、キーコードに文字列を使用することによってではなく、フィールドに「フリガナ」を設けるのが最良である、ということになります。何を申し上げたいかといえば、上記の商品コードです。やはり商品テーブルを作る場合「商品コード」「シリアル番号」「商品名」「商品名のフリガナ」というのが、理想的ではないでしょうか。
このページのトップへ
(2)DateDiff関数
アクセスは、色々と便利な関数を用意してくれています。しかし、ときどきあれっ?と思うような関数に出会うことがあります。今日はその中の一つ「DateDiff」を取り上げてみようと思います。「DateDiff」関数は、二つの日付の間の差、すなわち年数・月数・日数を計算してくれます。以下の例ではそれぞれのTextBoxには次のような関数が入っています。日付の入ったTextBoxは、それぞれ「開始日」「終了日」です。
日数=DateDiff("d",[開始日],[終了日])
週数=DateDiff("w",[開始日],[終了日])
月数=DateDiff("m",[開始日],[終了日])
年数=DateDiff("yyyy",[開始日],[終了日])
開始日、終了日をそれぞれ「2014/01/01」「2014/01/07」と入れてみました。
開始日、終了日をそれぞれ「2014/01/01」「2014/01/31」と入れてみました。
予想通り、週数も切り捨てされています。
開始日、終了日をそれぞれ「2014/01/01」「2014/12/31」と入れてみました。
ここでハタと、疑問がよぎりますね。月数と年数はこれでいいのか?と。しかし「片端入れ」では、確かにこのようになります。
ところがです。試しに「2014/01/02」「2015/01/01」としてみました。
するとどうしたことでしょう?日数は364日と変わらないにもかかわらず、月数と年数がひとつづつ増えていますね。これも何かおかしいような気もしますが「片端入れ」では、やはりこのようになるのです。理論的には正しくても、何だかしっくり来ないですよね。
そこで、これを「両端入れ」になるように、次のような式にしてみました。
日数=DateDiff("d",[開始日],[終了日])+1
週数=DateDiff("w",[開始日],[終了日])+1
月数=DateDiff("m",[開始日],[終了日])+1
年数=DateDiff("yyyy",[開始日],[終了日])+1
「2014/01/01」「2014/01/07」と入れてみました。
「両端入れ」ではこのようになります。
「2014/01/01」「2014/01/31」と入れてみました。
「両端入れ」ではこのようになります。
「2014/01/01」「2014/12/31」と入れてみました。
「両端入れ」ではこのようになります。
さて、それでは「2014/01/02」「2015/01/01」としてみました。
これも何だか変なような気がしますが「両端入れ」のルールには適っています。
つまりこれは、日本語の「足掛け」という数え方にあたります。
日数が一年以内でも月数や年数はこのようになるのです。ここで疑問に思うのは、月数と年数です。つまり、週が7日であるのは普遍的です。しかし、月数は30日である場合と、31に日である場合がありますね。更に2月は28日の場合と、4年に一度29日の場合があるわけです。同様に一年間の日数も4年に一度366日になります。一体どの日数を使って計算しているのでしょうか?
皆さんもうお分かりですね。Accessは、まさにそのとき時の「実際の日数」を元にしているのです。PC上では199/01/01以降、未来永劫に渡るすべての日付に一意的なシリアル値が当てられています。Accessは、そのシリアル値を即座に読み取って、左のような計算を実行しているわけです。
サンプルファイルはこちらからダウンロードすることが出来ます。