dhenkanマクロ修正しました

以前、「データベース変換～dhenkanマクロの使い方」で、VBコードを貼り付けて、マクロ登録すると、簡単にデータベースに変換できる解説をしました。

ただ、マクロを実行後、データ部分に設定した数式や関数までクリアされてしまうという意見が寄せられました。（ありがとうございます。）

ということで、数式や関数が入力されたセルはクリアしないようにVBコードを修正しました。


Sub dhenkan()
'
' データベース変換
'
Dim nyu As Integer, db As Integer

nyu = Worksheets("入力").Range("a1").CurrentRegion.Rows.Count
db = Worksheets("データ").Range("a1 ").CurrentRegion.Rows.Count

For Count = 1 To nyu

Worksheets("データ").Cells(db + 1, Count) = Worksheets("入力").Cells(Count, 2)

Next

For Count = 1 To nyu

If Not Worksheets("入力").Cells(Count, 2).HasFormula Then

Worksheets("入力").Cells(Count, 2).ClearContents
End If

Next

Range("b1").Select

End Sub


「データベース変換①～コードを貼り付ける」を参照して上記のコードを貼り付けし直してください。

少しでもお仕事のお役に立てたらうれしいです。

灯油定期配送管理①～日付算出

この地域では灯油の定期配送というのがよくあります。

冬になるとどうしても灯油を使う量が多くなるので、いちいち注文しなくても灯油を定期的に配送してくれるシステムです。

自分ならどのように管理するだろうか。ということで、Excelでこのような表を作成してみました。



顧客IDや地区コードなど必要なフィールドを作成します。

普段はオートフィルターなどを使い、地区コードや配送予定日で抽出したデータで、その日巡回するお客様を特定します。

作った初期段階では、配送予定日や前回配送日、給油量は、手入力する必要があります。



給油業務が終了したら、本日の給油したお客さまのデータに配送日と給油量を入力します。



入力すると次回の配送日がサイクル（日数）と入力した配送日データから算出されます。同時に今回入力したデータが履歴として残るように、前回配送データ算出に準備されます。



実際に配送日算出セル「G4」に数式を入力していきます。

配送日入力のデータにサイクル日数をプラスした日付が次回の配送予定日になります。そして配送がない場合は、配送予定に変化がないので、配送予定の値がそのまま表示されるようにします。

※表の範囲は「A3」から「L15」までです。




=IF(E4="",H4,D4+E4)

「配送日入力」セル「E4」が空白だったら、「配送予定」セル「H4」を表示し、入力されたら、「サイクル」セル「D4」と「配送日入力」セル「E4」をプラスした日付を表示する。という意味になります。

コピーしましょう。



この時点では、入力がないので、配送予定の日付がそのまま表示されます。

次に履歴用に用意した前回配送の日付を算出する数式を入力します。

入力したの日付と給油数が履歴となります。入力がない場合は前回配送のデータがそのまま表示されるようにします。

セル「I4」を選択し、数式を入力します。


=IF(E4="",K4,E4)

配送日入力セル「E4」が空白だったら、前回配送セル「K4」を表示し、入力されたら、配送日入力セル「E4」を表示する。という意味になります。

となりの前給油算出にも数式を入力しましょう。



=IF(F4="",L4,F4)

給油入力用のセル「F4」が空白だったら、前回給油セル「L4」を表示し、入力されたら給油入力セル「F4」を表示する。という意味になります。

コピーしましょう。



現時点では、まだ日付、給油量ともに入力されていなので算出フィールドと配送予定、前回配送ともに同じ値です。



変化を確かめましょう。

3/18に給油したお客様のデータに日付と給油量を入力します。

配送日入力と給油量を入力してみましょう。



配送日算出は入力日付にサイクルの日数をプラスした日付になりました。前算出フィールドはともに入力した日付および給油量になってます。また、入力していないところは変化ありません。

ただしく数式が入力されました。

あとは、算出された範囲をそれぞれコピーし、貼り付けて、入力したエリアをクリアすれば、明日からの作業準備が整います。



このコピーと貼り付け作業、さらに入力した範囲のクリアをマクロの記録を使って自動化します。

灯油定期配送管理②～マクロの記録・・・・・へつづく

灯油定期配送管理②～マクロの記録

灯油定期配送管理①～日付算出からのつづき

灯油定期配送の表にその日の給油したお客様データに給油日と給油量を入力すると、次回の配送予定日と、入力したデータを前回配送履歴として残す数式を入力しました。



今度は次の日からの配送データを抽出及び入力する準備をしなくてはなりません。

したがって算出したデータを正式なデータとして、「配送日算出」は「配送予定」「へ、「前配送算出」と「前給油算出」は「前回配送」と「前回給油」へそれぞれコピーして貼り付けます。

さらに、「配送日入力」と「給油」は次の日のためにクリアしておきます。



この作業をマクロの記録を使って自動化します。（画像はOffice2007での説明になります。）

まずは、「開発」タブの「コード」グループから、マクロの記録をクリックします。

※Office2003は「ツール」→「マクロ」→「新しいマクロの記録」



ダイアログボックスが表示されます。

マクロ名は「更新」にし、ショートカットキーを英数小文字で「k」と入力し「OK」をクリックします。
※マクロ名は任意でかまいません。ショートカットキーは一応「更新」なので「k」にしただけです。



これにより、マクロを実行するとき、Ctrlキーを押しながら、「k」を押すだけで簡単に実行できます。

最初に前配送算出データ範囲を選択し、コピーをします。（右クリックメニューからコピー）



前回配送のセルを選択します。コピーした範囲は数式が入力されているので、貼り付けるときは右クリックメニューから「形式を選択して貼り付け」を選択します。



ダイアログボックスの貼り付けから「値」を選択して「OK」をクリックします。



値のみのコピーがされました。



コピー範囲の解除をしておきましょう。キーボードの「ESC」キーを押します。点滅部分が消えるはずです。



次に配送日算出範囲も同様にコピーし「値」のみの貼り付けを行います。

このように貼り付けされます。「ESC」キーを押しておきましょう。



最後に入力範囲をクリアします。範囲を選択し、「Delete」キーを押します。



そして、一番最初のデータの先頭にカーソルを置きます。



「記録終了」ボタンをクリックして、マクロの記録終了です。



これで記録した時点から記録終了までの一連の流れが自動で行うことができるようになりました。

算出範囲のデータをそれぞれコピーして貼り付け、入力範囲をクリアする。たったこれだけですが、毎回手動でやるとなると、これが手間なんですね。

表は、算出されたデータがコピーされ、入力範囲もクリアされた状態になりました。



それでは、正常に動くか試したいのですがその前に一つ作業をしましょう。

算出エリアは次回の配送と履歴を表示するための、一時待機場所のようなものなので、随時表示しておく必要はありません。非表示にする作業をしましょう。

算出エリアの列番を選択し、右クリックメニューから「非表示」を選択します。あくまでも非表示にするだけです。削除ではありません。（配送日算出、前配送算出、前給油算出を非表示にしましょう。）



非常にシンプルで見やすくなると思います。

それでは、3/20に給油してきたお客様に配送日と給油量を入力をしてみます。



Ctrlキーを押しながら「k」キーを押します。



配送予定日、前回配送データともに更新されました。

これで明日からの作業ができますね。

今回は少ないデータで解説しましたが、本来は数千件以上あると思います。配送データを抽出するときは、地区や顧客IDを並び替えたり、オートフィルター機能などを使用し効率のよい作業をしましょう。

また、新規のお客様の追加なども考えられますので、マクロの記録時、今回のように、データが入力された範囲のみコピーするのではなく、かなり多めに（現顧客数の倍ぐらい）選択しておくと安心して追加できます。（VBコードを修正できる方は必要ありません。）

参考にしてみてください。

データベース変換①～コードを貼り付ける

データベースを作成する作業で一番大変なのが、それこそデータを入力する作業です。

毎日コツコツできれば一番いいのですが、そうもいかず、あとでまとめて・・・なんてのも多いですよね。

データを入力するとき、大抵の場合、まずは「ID」や「氏名」などのフィールド名を一番上の行に作り、その下の行にデータを入力していきます。

最初はいいんですが、データが多くなってくると入力しずらいですよね。

Accessなんかだと、フォームというオブジェクトがあるので、入力用の画面を簡単に作成できて、一件、一件確認しながら入力できます。

効率も上がるし間違いも少なくなります。

Excelにもフォームという機能はありますが、カーソルの移動や、関数や入力規則が入ったセルの内容が反映されないなど、扱いにくい点が多々あります。

なんとか、ExcelでもAccessのように一件ずつ入力したデータを簡単にデータベースに変換できないかと思い、VBでこんなコードを作成しました。

Sub dhenkan()
'
' データベース変換
'
Dim nyu As Integer, db As Integer

nyu = Worksheets("入力").Range("a1").CurrentRegion.Rows.Count
db = Worksheets("データ").Range("a1 ").CurrentRegion.Rows.Count

For Count = 1 To nyu

Worksheets("データ").Cells(db + 1, Count) = Worksheets("入力").Cells(Count, 2)

Next

For Count = 1 To nyu

If Not Worksheets("入力").Cells(Count, 2).HasFormula Then

Worksheets("入力").Cells(Count, 2).ClearContents
End If

Next

Range("b1").Select

End Sub

VBを知らない方は、なんのことか分からないと思いますが、簡単に解説すると、「入力」というシートに入力したデータを「データ」というシートに書き込むという作業をしています。

このコードをVBのEditor画面に貼り付けることで、データ入力が簡単にできるようになります。

今回はコードを貼り付ける作業を解説します。

Office2007での解説になりますが、Office2003でももちろん使えます。

①まず、このブログ上記のVBコード「Sub dhenkan()　～　End Sub」までをコピーします。

②Excelを起動します。

「開発」タブをクリックし、「Visual Basic」ボタンを選択します。（Office2003ではAltキーを押しながらF11を押す）

※図はクリックすると大きく表示されます。


※「開発」タブがない場合はオフィスボタンから「Excelのオプション」を選択。


「開発」タブをリボンに表示するにチェックを付けます。



③Editorの画面が開きます。

「挿入」メニューから「標準モジュール」を選択します。



④コードウィンドウ内で右クリックし、貼り付けます。



⑤Editor画面を閉じて「マクロ」ボタンを選択します。



⑥ダイアログボックスに「dhenkan」とうマクロが追加されていればOKです。



確認できたら閉じておきます。

ブックに任意の名前を付けて保存しておきましょう。（Office2007の場合、ファイルの種類を「マクロ有効ブック」にして保存すること）

次はこのマクロについての使い方を解説します。

データベース変換②～dhenkanマクロの使い方

データベース変換②～dhenkanマクロの使い方

データベース変換①～コードを貼り付ける～のつづき

前回、下記のVBコードをExcelのVBEditorに貼り付ける作業をしました。

Sub dhenkan()
'
' データベース変換
'
Dim nyu As Integer, db As Integer

nyu = Worksheets("入力").Range("a1").CurrentRegion.Rows.Count
db = Worksheets("データ").Range("a1 ").CurrentRegion.Rows.Count

For Count = 1 To nyu

Worksheets("データ").Cells(db + 1, Count) = Worksheets("入力").Cells(Count, 2)

Next

For Count = 1 To nyu

If Not Worksheets("入力").Cells(Count, 2).HasFormula Then

Worksheets("入力").Cells(Count, 2).ClearContents
End If

Next

Range("b1").Select

End Sub

マクロに「dhenkan」というマクロ名が追加されているのを確認しました。

今回この「dhenkan」マクロの使い方を解説します。

VBを貼り付けて保存したExcelブックを開きます。（セキュリティの警告のオプションから「コンテンツを有効にする」を選択してください）
※Office2003の場合は、マクロを有効にする確認が表示されると思いますので、有効にするをクリックします。

決まり事がいくつかあります。

その一つはシート名です。

データを入力するシートのシート名は「入力」とし、データが更新される側のシートは「データ」とします。

シート名をこのように変更します。



「入力」シートを表示して、必要な項目を入力します。必ずセル「A1」から下方向へ入力してください。

一応ベタな例で取引先の情報を入力します。文字の大きさや行列の幅は任意で調整してください。



入力し終えたら、「データ」シートにこの項目をフィールドとしてコピーして貼り付けます。

項目の範囲を選択し、コピーします。



「データ」シートに切り替えて、セル「A1」を選択します。

縦に入力した項目を横に変換したいので、「形式を選択して貼り付け」を選択し「行列を入れ替える」にチェックをし、「OK」をクリックします。








データ更新用のフィールドが完成しました。列幅を調整しましょう。

「入力」シートに切り替えます。

次に「dhenkan」マクロを実行するための作業を行います。

Excelで用意されているフォーム用のコントロールがあるのですが、今回は図形を使ってマクロを実行させます。その方が少しでも楽しくなるでしょう。

「挿入」タブから図形を選択し、任意の図形を選択します。（直線とか矢印以外がいいと思います。）

※Office2003では図形描画ツールバーから選択してください。



私は、楕円を選択しました。入力した項目の少し下にドラックして形を整えます。



お好みにより、3D効果を設定したり、塗りつぶしを設定してください。

図形にテキストを挿入します。右クリックし、メニューから「テキストの編集」を選択し「更新」と入力します。（なんでもいいです。決まりはありません。）

文字の大きさを調整しましょう。



最後にこの図形にマクロを登録します。右クリックし、メニューから「マクロの登録」をクリックします。



「マクロの登録」ダイアログボックスが表示されます。「dhenkan」を選択し、「OK」をクリックします。




いったん、任意のセルをクリックし、図形にマウスポインタを当ててみると人差し指マークになると思います。

※図形を移動するときは、右クリックすると図形を選択できる状態になります。

これで、準備は整いました。

データを入力してみましょう。項目名に沿ってセル「B1」から下方向へ入力します。

入力できたら、図形をクリック！



入力したデータが消えます。どこへ行ったのでしょう。

「データ」シートに切り替えます。



フィールド名の下にデータが更新されてますね。

「入力」シートに切り替えて、もう一件入力して図形をクリックしましょう。



「データ」シートを確認してみます。



ちゃんと追加されていますね。


たわいもないVBコードですが、入力の手助けぐらいにはなると思います。

まとめです。

①「入力」「データ」シートを作成、またはシート名を変更する。
②「入力」シートのセル「A1」から下方向へ入力項目を設定する。（基本的に項目数の制限はありません）
③設定した項目と同一のフィールド名を「データ」シートへ作成する。
④「入力」シートに好きな図形を作成し、「dhenkan」マクロを登録する。
⑤データは「入力」シートのセル「B1」から下方向へ入力する。

以上の決まり事を守れば正しく処理されるはずです。

いろいろ応用もできます。

例えば、月ごとにデータを管理したいという場合は、その月のデータを入力し終えたら、「データ」シートのシート名をその月名に変更し、新たに追加したシートの名前を「データ」と変更すれば、どんどんシートを増やせます。

また、「入力」シートの入力項目も増やしたり、減らしたり、または、まったく新しく設定したりもできます。その際、必ず「データ」シートも修正するのを忘れずに。

新しいブックに作成するときは、「データベース変換①～コードを貼り付ける」の作業から行ってください。

とにかく「入力」と「データ」というシート名にしか、反応しないプログラムですから、その関係性を利用していろいろ応用してみてください。