エクセル顧客管理 | 第6回.ここまでの復習 | Excelマクロを駆使したカスタマイズ可能なエクセル顧客管理、エクセルVBAの学習教材



最終更新日:2014-11-11

第6回.ここまでの復習


エクセルで顧客管理を作ります、


ここまで、ハイペースで来ましたので、一旦整理しながら復習します、


まず、エクセルのシートの確認です。


シート「顧客一覧」


シート「項目名」


シート「顧客一覧」の「顧客区分」に、「入力規則」の「リスト」を設定します。


名前定義

1.シート「項目名」を選択

2.Ctrl+F3を押して下さい。

  Ctrl+F3は名前定義の画面を表示するショートカットです。

  メニューからは、2003なら、「挿入」→「名前」→「定義」、

  2007以降なら、「数式」→「名前の管理」→「新規作成」

2.「名前」に、「顧客管理」と入力

3.2007以降の場合は、「範囲」は「ブック」のままにして下さい。

4.「参照範囲」に、「=OFFSET($A$2,0,0,COUNTA(項目名!$A:$A)-1,1)」と入力

5.「OK」


「入力規則」の「リスト」の設定

1.シート「顧客一覧」を選択

2.セルC4:C103までを選択。顧客区分の全範囲です。

3.2003なら、「データ」→「入力規則」、

  2007以降なら、「データ」→「データの入力規則」

4.「入力値の種類」で、「リスト」を選択」

5.「元の値」で、「顧客管理」と入力。F3で一覧から選択できます。

6.「OK」


この名前定義との使い方は絶対に覚えましょう。


他の関数と組み合わせることで、利用範囲の広いテクニックです。


シート「設定」


1.A1=「シート名」

2.A2=RIGHT(CELL("filename",顧客登録!A1),LEN(CELL("filename",顧客登録!A1))-FIND("]",CELL("filename",顧客登録!A1)))

3.=RIGHT(CELL("filename",顧客一覧!A1),LEN(CELL("filename",顧客一覧!A1))-FIND("]",CELL("filename",顧客一覧!A1)))

4.=RIGHT(CELL("filename",項目名!A1),LEN(CELL("filename",項目名!A1))-FIND("]",CELL("filename",項目名!A1)))

5.A2に「顧客登録シート名」と名前定義する。

6.A3に「顧客一覧シート名」と名前定義する。

7.A4に「項目名シート名」と名前定義する。


シート名取得の関数です。

ブック名なら、

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

「顧客一覧」と「顧客登録」の先頭セルに名前定義する。

1.シート「顧客一覧」を選択

2.B3に、「顧客一覧開始」と名前定義

3.シート「顧客登録」を選択

4.B3に、「顧客登録開始」と名前定義

シート名を変更しても、シート位置がずれても、マクロを修正しなくて済ませる為の準備です。


シート「顧客登録」


モジュール「Mod共通」

Option Explicit


Public sht顧客登録 As String '顧客登録のシート名
Public sht顧客一覧 As String '顧客一覧のシート名
Public sht項目名 As String '項目のシート名
Public pos顧客登録 As Range '顧客登録の先頭セルシート名
Public pos顧客一覧 As Range '顧客一覧の先頭セルシート名


Sub シート名取得()
  Worksheets("設定").Calculate '念の為、再計算しておく
  sht顧客登録 = Range("顧客登録シート名") '顧客登録のシート名を取得
  sht顧客一覧 = Range("顧客一覧シート名") '顧客一覧のシート名を取得
  sht項目名 = Range("項目名シート名") '項目名のシート名を取得

  Set pos顧客登録 = Range("顧客登録開始") '顧客登録の開始位置を取得
  Set pos顧客一覧 = Range("顧客一覧開始") '顧客一覧の開始位置を取得
End Sub


モジュール「Mod顧客登録」

Sub 顧客登録シート作成4()
  Dim r1 As Long, c1 As Long '顧客一覧の見出しの行,列位置
  Dim r2 As Long, c2 As Long '顧客登録の行,列位置
  Dim intW As Integer '列数計算用

  Call シート名取得 '各シート名をパブリック変数に設定

  r1 = pos顧客一覧.Row '顧客一覧の開始行位置を取得
  c1 = pos顧客一覧.Column '顧客一覧の開始列位置を取得

  r2 = pos顧客登録.Row '顧客登録の開始行位置を取得
  c2 = pos顧客登録.Column '顧客登録の開始列位置を取得

  With Worksheets(sht顧客登録) 'Worksheets(sht顧客登録)を省略可能とする
    .UsedRange.Clear '顧客登録の使用セルを全てクリア

    '顧客一覧の3行目を2列目から右に進み、空白セルになるまで繰り返す
    Do Until IsEmpty(Worksheets(sht顧客一覧).Cells(r1, c1))
      '顧客一覧の見出しを顧客登録にコピー
      Worksheets(sht顧客一覧).Cells(r1, c1).Copy .Cells(r2, c2)
      '顧客一覧のデータ部を顧客登録にコピーし、データは消去する
      Worksheets(sht顧客一覧).Cells(r1 + 1, c1).Copy Destination:=.Cells(r2, c2 + 1)
      .Cells(r2, c2 + 1).ClearContents
      '顧客一覧の列幅が、顧客登録の列幅の何個分かを計算
      intW = Round(Worksheets(sht顧客一覧).Columns(c1).Width / .Columns(c2 + 1).Width, 0)
      '上で計算した個数分のセルを結合する。

      .Range(.Cells(r2, c2 + 1), .Cells(r2, c2 + 1 + intW)).MergeCells = True
      '罫線を引く
      .Range(.Cells(r2, c2), .Cells(r2, c2 + 1 + intW)).Borders.LineStyle = xlContinuous
      c1 = c1 + 1 '顧客一覧の列を右に
      r2 = r2 + 2 '顧客登録の行を2つ下に
    Loop
  End With
End Sub


以上が作成したエクセルとマクロです。


一息いれて


出てきたエクセルの関数


OFFSET(基準, 行数, 列数, 高さ, 幅)

基準のセルから指定の行数と列数だけシフトした位置にある、高さと幅のセル範囲を返す関数です。


COUNTA(範囲)

指定範囲の、空白でないセルの個数を返します。


PHONETIC(範囲)

ふりがなの文字列を取り出します。


CELL(検査の種類,対象範囲)

対象範囲の右上隅にある書式、位置等を返します。

"filename"ですと、「フルパス[ブック名]シート名」が返ってきます。


RIGHT(文字列,文字数)

文字列の右端から指定した文字数だけ取り出す

LEFTなら左端からになります。


LEN(文字列)

文字列の文字数を返す



出てきたVBA

Dim r1 As Long

これは、変数の定義です、変数とは、データを一時的に保存しておく入れ物です。

この入れ物には、種類が色々あり、入れられる物が違ってきます。

身の回りで言えば、鍋、ザル、ダンボール等々だと考えて下さい。

水を入れるなら、上のなかなら、鍋しかないですよね。

つまり、入れるデータによって種類を決めます。

これをデータ型と言います。

そして、この変数定義は、「,」で区切ることで、1行に複数書くことが出来ます。


データ型一覧

Integer 整数型 2バイト -32,768 〜 32,767
Long 長整数型
Single 単精度浮動小数点数型
Double 倍精度浮動小数点数型
Currency 通貨型
String 文字列型
Date 日付型
Object オブジェクト型
Boolean ブール型
Variant バリアント型
それぞれ詳しい値の範囲等は省略します。

Integerの範囲だけ示したのは、現在のエクセルでは、行数には不足している事を理解して欲しいからです。

行数をいれるなら、Longが必要です。

これをいきなり全て覚えるのは無理でしょうから、

分からない時は、Variantを指定して下さい、これは何でも入れられる便利なものです。


r1 = 3

これは、r1という変数に3を入れることです。

r2を3に等しくすると考えればよいでしょう。


Worksheets("顧客登録").UsedRange.Clear

まず、Worksheets("顧客登録")は、シート「顧客登録」のことです。

これは、Sheets("顧客登録")でも同じです。

シートは、「ワークシート」や「グラフ」等があり、この種別のちがいであり、

Sheetsなら全てのシートを指定できます。

まあ、最初はどちらでも好みで良いと思います。

UsedRangeは、そのシートで使用したことのあるセル範囲を示します。

そして、その範囲を、Clearしています、全て消去されます、値も書式も消去されます。

また、範囲をシート全部にするなら、「.Cells.Clear」と指定します。


Do Until Worksheets("顧客一覧").Cells(r1, c1) = ""
Do Until 条件

・・・

LooP

で、条件を満たすまで、・・・を繰り返します。

Worksheets("顧客一覧").Cells(r1, c1) = ""になるまで、

つまり、空白セルになるまで以下の処理を繰り返すのです。

この場合の「=」は値の代入ではなく、等しいかの判定です。

Cells(行, 列)は、指定の行列の位置のセルを指します。

正しくは、Cells(行, 列).Valueなのですが、Valueは省略可能です。

Valueは値の入っているプロパティです。

さまりここでは、セルそのものではなく、Cells(r1, c1)のセルの値が空白かの判断をしています。

Cellsについては、もっと詳しく説明する必要がありますが、次回以降で。


別の書き方で、

Do While 条件

・・・

Loop

があります、これは、条件を満たしている間、処理を繰り返します。

Whileで、上記を書けば、

Do Until Worksheets("顧客一覧").Cells(r1, c1) <> ""となります。

「<>」は等しくないと言う意味です。


Worksheets("顧客登録").Cells(r2, c2) = Worksheets("顧客一覧").Cells(r1, c1)
これは、値の代入です、省略しないで書くと、

Worksheets("顧客登録").Cells(r2, c2).Value = Worksheets("顧客一覧").Cells(r1, c1).Value

になります、このValueは、セルが持つプロパティで、値がはいっています。

このValueは省略できるのです。

つまり、シート「顧客一覧」の該当セルの値を、シート「顧客登録」の該当セルに入れています。


c1 = c1 + 1

これは値の代入です。

c1に1を足して、元のc1に入れています。

つまり、c1を1増やしています。


With Worksheets("顧客登録")

With オブシェクト

・・・

End With

これは、オプジェクトに対し、再定義を省略できるようにします。

つまり、「・・・」の部分では、このオブジェクトの記述を省略できます。

はっきり言って、初心者には直ぐには理解しずらいと思います。

本来なら、もっと後で説明するところなのですが、

ブログへのアップを考えると、記述を短くした方が良いので、あえて先に出しました。

理屈ぬきで、オブジェクトを省略出来ると思って下さい。

この場合は、

Worksheets("顧客登録").○○○は、.○○○だけで良いということです。

ただし、先頭の「.」は絶対に必要です、決して忘れないで下さい。


Worksheets("顧客一覧").Cells(r1, c1).Copy .Cells(r2, c2)

これは、セルのコピー&ペーストです。

マクロの記録だと、

Sheets("顧客一覧").Select
range("B3").Select
Selection.Copy
Sheets("顧客登録").Select
range("B3").Select
ActiveSheet.Paste
こんなふうになると思います。

しかし、これでは記述が長すぎるし、処理スピードも遅くなります。

上の命令では、「コピー元 コピー先」として1行で記述できています。

コピー先の.Cells(r2, c2)の先頭の「.」は、先のWithで省略された部分です。

つまり、Worksheets("顧客登録").Cells(r2, c2)と同じ事です。

セルをコピーしていますので、文字・書式の全てがコピーされています。


.range(.Cells(r2, c2 + 1), .Cells(r2, c2 + 1 + intW)).MergeCells = True
上で計算して、列数分をセル結合しています。

.MergeCells = Trueで指定の範囲のセルが結合されます。


.range(.Cells(r2, c2), .Cells(r2, c2 + 1 + intW)).Borders.LineStyle = xlContinuous

Worksheets("顧客登録").range(.Cells(r2, c2), .Cells(r2, c2 + 1 + intW))のセルに対して、

上下左右に線をひいています。

xlContinuousは線種で、実線になります。

マクロの記録をしてみれば分かると思いますが、とんでもなく長い記述になります。

今回は簡単に実線を引く場合のやり方です。

罫線には色々種類や引き方があるので、必要になった時点で詳しく説明します。


.Cells(r2, c2 + 1).ClearContents
前回は、Worksheets("顧客登録").UsedRange.Clear

というのが出てきましたが、.Clearはデータだけでなく、書式も消去します。

.ClearContentsはデータのみクリアします。


Option Explicit

これは、変数の宣言を強制するオプションです。

VBAでは、変数の宣言を省略できますが、

変数の宣言は必ずするようにして下さい。

思わぬバグで行き詰らない為の予防策です。

「Mod顧客登録」の先頭にも入れておいて下さい。

「ツール」→「オプション」で「変数の宣言を強制する」にチェックを付けておけば、

モジュールの挿入時に、自動で付加されます。


Public sht顧客登録 As String

Publicは他のモジュールからも参照可能な変数の定義です。

同一モジュールのみ参照可能にするなら、Privateとします。

Stringは、文字列を入れる変数である事を意味します。


Public pos顧客登録 As Range

Rangeは、オブシェクトです。

値だけでなく、セルそのものを入れる事ができます。


Worksheets("設定").Calculate

自動計算が停止していたりして、シート名取得の関数が再計算されていないことを防ぐため、

「設定」を再計算しています。


sht顧客登録 = Range("顧客登録シート名")

Rangeは、前回出たCellsと似たもので、指定されたセル範囲を参照します。

今、良く見たら、前回もRangeを使ってました、説明もれです。

Range(セル1, セル2)は、セル1からセル2のセル範囲になります。

Range(名前定義)は、名前定義されているセルになります。

シート名が指定されていませんが、良いのでしょうか?

名前定義はブック共通なので、シートの指定が必要ありません。

もちろん指定しても結構です。

ただし、2007以降では、名前定義がシート単位で指定できますので、注意して下さい。

特別な理由がない限り、名前定義は「ブック」にします。

また、 As Rangeはデータ型です、ここのRangeはオブジェクトです、混同しないで下さい。


Set pos顧客登録 = Range("顧客登録開始")

pos顧客登録はRange型のオブジェクトです。

Range("顧客登録開始")は「顧客登録」の先頭セルです。

つまり、セルをオブジェクトに入れています。

このように、オブジェクトに入れる場合は、Setを先頭に付けます。

私でもたまに付け忘れますが、実行時にエラーになるので、直ぐに分かります。


Call シート名取得

他のモジュールを呼んでいます。

「シート名取得」というモジュールを実行しています。


r1 = pos顧客登録.Row

c1 = pos顧客登録.Column

pos顧客登録はRange型のオブジェクトであり、シートの使用先頭セルが格納されています。

Rowは、行番号を返します

Columnは、列番号を返します



結構いろいろ出てきていますね。


これからは、さらに複雑なマクロ作成になります。


まずは、基本を押さえて下さい。


次回は、さらにこれをプロっぽいプログラムにしていきます。


特に、Mod共通の使い方は、いかにも素人ぽいので(笑)


Public変数の使い方を説明するために、このような記述をしましたが、


Public変数など使用しないほうが、コードがスマートに見えますので。






同じテーマ「エクセル顧客管理」の記事

第7回.本格的なプログラムへ
第8回.顧客一覧より顧客データを取得
第9回.イベントを使ってマクロを起動させる
第10回.コーディングとデバッグ
第11回.顧客登録より顧客一覧へ更新
第12回.最終行の判定、Rangeオブジェクトと配列、高速化の為に
第13回.コントロールのボタンを配置

新着記事 ・・・新着記事一覧を見る

SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)

アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
7.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
8.ひらがな⇔カタカナの変換|エクセル基本操作
9.定数と型宣言文字(Const)|ExcelマクロVBA入門
10.CSVの読み込み方法|ExcelマクロVBAサンプル集



  • >
  • >
  • >
  • ここまでの復習

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。

    ↑ PAGE TOP