すぐマク　YNxv925 Home | Search | Contents | Gallery | Introduction | Service | Support | What's New!
データベースの概念（リストの作り方・使い方）は？ Click Here!
Ｑuestion 20.3　ブックシート Previous Next
始めまして、私は1年前に始めてパソコンを知り、現在会社で役立ててます。特にエクセルは上達し、ちょっとしたプログラムも組めるようになり、私より若い部下から、プログラム作成の依頼があるほどです。さてご質問をさせていただきます。

私の会社では、担当一人当たり150人位の顧客を管理していますが、私の作成したものは、シートを150人分つくる邪道のつくり方です。ホームページにありました消費者金融用のようにしたいのですが、基本的な作成方法、特に概念が判りません。

一枚のシートで任意の顧客情報は出せるのですが、その後毎日150人の売掛残などの詳細を、DB式に縦に「Aさんいくら、Bさんいくら・・・・」と並べて、会社に提出しなくてはなりません。レポートの形式は統一されていないので、その点はあまり気にしなくても良いのですが、どう考えても150枚のシートからの参照しか、方法が見つかりません。HPに書いてあった通り、重くてとてもノートでは、処理しきれません。
VBAはあまり詳しくは無いのですが、努力して覚えようと思ってます。概念的な考え方を教えてください。
Ａnswer　　 Copyright (C) 1999.6.25 Yoshioh Nagai
スケールのでかい質問で「データベースの概念」ということですが、データベースの作り方と使い方を、説明すれば良いのでしょうか。「努力して覚えよう」と決意してみえるので、長くなりますが回答します。しっかり読んでください。
概念：　個々の事物から共通な性質や一般的性質を取り出してつくられた表象（現れた形・姿） 《Shogakukan国語大辞典より》
Excelで扱えるデータベースには、いろいろなフォーマットがあり、よく知られているものとして、AccessやdBASEのデータベースがあげられます。また企業内では、サーバーに貯えられているデータベースから、Excelワークシートに必要なデータを取り出して利用する方法が、急速に普及して来ているようです。

 以外に知られていないようですが、リスト形式のExcelワークシートは、データベースとして利用できます。 		school
リスト：
ワークシートの行の系列に見出しの付いたもので、請求書のデータベース、顧客の氏名と電話番号など、同じ項目のデータが入力されたもの。リストはデータベースとしても使用できます。その場合は、行がレコード、列がフィールドになります。

そして、ワークシートをリスト形式にしておくと、Excelの世界が一挙に広がります。その意味は、このケースをマスターし終わると、実感できると思いますが、今わかりたいなら一例として、「ワークシート関数（Dcount）をVBAの中で使うには」を、見てください。
「すぐに役立つエクセルVBAマクロ集」の各ページを見ていて気づかれているかと思いますが、私はワークシートをレイアウトする場合、原則としてA1セルから使います。左余白のためにA列を、上余白のために 1行目を空けておくという手法は、滅多に採りません。
データベースとして、いつでも利用可能なように、リスト形式で作成しています。余白やヘッダーは印刷のページ設定で、自在に調整できますから。
【 リストの作り方 】

普通にワークシートを作る場合にくらべて、それほど大袈裟なルールがある訳ではありません。次の 4つのポイントを守って作ります。
　　　　　1. 1行目を列見出し（項目名）にする （注）
　　　　　2. 列見出しはNullにしない、同じ名称は使わない
　　　　　3. A列の各行もデータとして使い、行見出しにしない
　　　　　4. A列のデータはNullにならないキー項目にする
　　（注）1～2行目を列見出しにした場合、どちらかの行の項目名にNullが混ざり易いので、2行にしない方がベター

リストの例示
 DBシート
←列見出し（項目名）
【 リストの使い方 】

リストの使い方には、いろいろな方法があります。ここでは、私がよく利用する機能のオートフィルタ、ピボットテーブル、豊富なセル範囲選択機能を活用したコピー・貼り付け、そして、並べ替えの中からいくつかを説明します。

1.入力方法
安全な方法： データベースにするリストとは別のワークシートに一旦入力して、内容確認後にコピー貼り付けする
　　　　　　　　 . . . 具体的な説明は、「消費者金融業のVBA」のページの入力方法を参考にしてください . . .
簡便な方法： リストへ直接入力する …… データ量が少ないか、簡単なデータの場合に適する

2.抽出方法
リストデータはオートフィルターで抽出できます。
上図のリストから担当者が「西田」のデータだけを抽出するマクロは、次のとおりです。なお、シート名は「DB」とします。 
'-----------------------------------------------------------------------------------------
Sub オートフィルタで西田だけを抽出する()
    Sheets("DB").Select
        Range("A2").Select
        Selection.AutoFilter Field:=2, Criteria1:="西田"
End Sub
'-----------------------------------------------------------------------------------------
3.コピー貼り付け方法
オートフィルターで抽出したら、可視セルを選択してコピーし、他のシート「抜出」に値だけを貼り付けます。
マクロは次のとおりです。 
'-----------------------------------------------------------------------------------------
Sub オートフィルタで抽出したデータをコピー貼り付けする()
    Sheets("DB").Select
        Selection.SpecialCells(xlVisible).Copy  '可視セルを選択してコピー
    Sheets("抜出").Select
        Range("A1").PasteSpecial Paste:=xlValues '値だけ貼り付け
End Sub
'-----------------------------------------------------------------------------------------
オートフィルターの抽出キーの指定は、この例のほかに複合条件にすることも可能です。
「Macro」の「コピー」のページに掲載されている「4)オートフィルターしてコピー」、 「FAQ」の「オートフィルタで抽出した結果を写すマクロが動かないが？」を参考にして、いろいろな使い方を研究してください。

4.集計方法
担当者別顧客別残高を、ピボットテーブルで集計して、結果を「ピボット」シートに格納します。マクロは次のとおりです。 
'-----------------------------------------------------------------------------------------
Sub 担当者別顧客別残高をピボットテーブルで集計する()
    Sheets("ピボット").Cells.Clear              'すべてクリア
    Sheets("DB").Select
        Range("A2").Select
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        "DB!R1C1:R6C6", TableDestination:="[YNxv925_DBlist.xls]ピボット!R1C1" _
        , TableName:="ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1"
    ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").AddFields RowFields:="顧客名", _
         ColumnFields:="担当者"
    ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("売掛金残高"). _
        Orientation = xlDataField
End Sub
'-----------------------------------------------------------------------------------------
このマクロで作ったピボットテーブル ビボット
質問にある「DB式に縦に、Aさんいくら、Bさんいくら・・・・と並べて」が、顧客名で並べるという意味でしたら、これを印刷すれば使えるかも知れませんね。

なお、マクロの5～11行目は自動記録しました。
実務ではDBシートの大きさが、その都度変わるでしょうから（特に縦方向の行数）、6行目の"DB!R1C1:R6C6"の記述を変数にした方が良いでしょう。 「FAQ」の「ピボットテーブルのソースデータの範囲を変数名で指定するには 」を参考にしてください。
そのページは、Macintoch用 Excel98のユーザーへの回答ですが、Excel95、98、2000にも共通です。

5.加工方法
ピボットテーブルからデータを取り出して、報告用のシートをマクロで作ってみましょうか。
tea time この後の説明は、ちょっと難しくなりますが、10回くらい読んで試してみれば、きっとマスターできるでしょう。
そして、この手法をマスターすればピボットテーブルだけでなく、いろいろなワークシートから自在にデータを取り出して、加工できるようになるでしょう。 でも "無理は禁物"ですから根気のない人は、ここで挫折してください。 しかし、この長大なページを、ここまで読み進んでこられた あなたは、決して根気がなくはないでしょう。トライしてみてください。

1) ヒボットテーブルの大きさを調べておきます。
'-----------------------------------------------------------------------------------------
Sub ヒボットテーブルの大きさを調べる()
    下端 = Sheets("ピボット").Range("A3").End(xlDown).Row       '下端検出
    右端 = Sheets("ピボット").Range("A3").End(xlToRight).Column '右端検出
End Sub
'-----------------------------------------------------------------------------------------
2) 報告書用シート（右図）を作成します。処理概要とマクロは下記のとおりです。

　① シートを初期化する
　② ピボットテーブルの3行目から順に下方向へ、総計の上の行
　　　までの各行ごとに、③④の処理を繰り返す
　③ 各行のB列から順に右方向へ、総計の左の列まで、④の処理を
　　　繰り返す 　④ ②と③で指定されるセルの金額がゼロでなけれ
　　　ば、報告書シートに新しい行を作って担当者名、顧客名、金額
　　　をセットする
　⑤ 印刷する 		報告書 
'-----------------------------------------------------------------------------------------
Sub ピボットテーブルから残高データを取り出しながら報告書を作成する()
    Sheets("報告書").Cells.Clear                    '報告書シートをクリアする
    Sheets("テンプレート").Range("A1:C2").Copy destination:= _
        Sheets("報告書").Range("A1")                'テンプレートから報告書シートへ様式写す
'
    Sheets("報告書").Select                         '報告書シートを選択する
        貼付行 = 2                                  '貼付行ポインタ：報告書シートの2行目
        For 横 = 2 To 右端 - 1                      'ピボットテーブルのB列～右端列-1
            For 縦 = 3 To 下端 - 1                  'ピボットテーブルの3行目～最下行-1
                If Sheets("ピボット").Cells(縦, 横) <> 0 Then '金額がゼロでなければ
                    Sheets("テンプレート").Range("A2:C2").Copy destination:= _
                       Sheets("報告書").Range(Cells(貼付行, 1), Cells(貼付行, 1)) '様式写す
                    Cells(貼付行, 1) = Sheets("ピボット").Cells(2, 横)  '担当者名
                    Cells(貼付行, 2) = Sheets("ピボット").Cells(縦, 1)  '顧客名
                    Cells(貼付行, 3) = Sheets("ピボット").Cells(縦, 横) '金額
                    貼付行 = 貼付行 + 1             '貼付行ポインタを1行あげる
                End If
            Next
        Next
End Sub
'-----------------------------------------------------------------------------------------
サンプルブックのダウンロードは ここをクリック　（YNxv925_DBlist.xls　45KB）
※ 一旦、ブックをハードディスクに保存し、開き直してから実行してください。
なお、ピボットテーブルの参考ページとして、「Macro」の「印刷」に「ＤＢから請求書を印刷」が、あります。
http://www.geocities.jp/happy_ngi/ Home | Contents | Gallery | Introduction | Service | Support | What's New!