エクセルのデーターを反映させるには？

質問

先日も同じような質問をしたのですが、もう少し分かりやすく説明して質問させていただきます。

エクセルで日程表から請求書へデーターを反映させたいのですが・・・
日付　　○○さん　　　△△さん　　□□さん
10/1　　A店　500　　B店　500　　C店　1000
10/2　　C店　500　　A店　500　　A店　500
10/8　　A店　200　　C店　300　　B店　1000
10/9　　B店　300　　A店　200　　C店　500
とあります。この日程表から人ごと、場所ごとの請求書を作成します。
ので、上記の場合全部で9枚の請求書が必要です。例えば○○さんのA店への場合
10/1　　500
10/8　　200
合計　　700円
という請求書です。これを日程表から反映させて作成したいのです。
どのような方法でできるのでしょうか？

回答No.4

BLUEPIXY · ベストアンサー

一応作ってみました。
ＶＢＥで標準モジュールに貼り付けて下さい。
表の形がイメージしにくいかもしれませんが
イメージは、＃１のイメージです。
基本的には、場所が違う場合も、「場所」の部分だけを変更すれば動くように作ったつもりです。
セルの書式は適宜設定して下さい。
わからないところがあれば聞いて下さい。
プルダウンリストの作り方や
ボタンを作ってマクロを割り当てるのはわかりますか？

Public Sub 請求書作成()
Const 日付 = 0, 金額 = 1
Dim 日程表 As Range, 請求書 As Range
Dim 担当者, 店名, 集計月
Dim 担当者列
Dim 日程表検査位置, 請求書転記位置
Dim 合計

Set 日程表 = Range("日程表!A2") '日程表シートのデータ（項目名を除いた）部分の左上のセル
Set 請求書 = Range("請求書!C6") '請求書シートの項目名部分の左上のセル回りに１つ以上のセルの空きがあること
'日程表が月ごとになっている場合は必要ない
'月ごとに作成する場合は、月末の請求書の作成処理が終わったら、コピーしてシート名の日程表を１０月などに変更する
'年ごとになっている場合は、検査月をApplication.WorksheetFunction.month(請求書.Offset(日程表検査位置, 日付).Value)のようにして調べる
集計月 = Range("請求書!B1").Value
店名 = Range("請求書!D1").Value 'プルダウンリストで指定した内容のセルを読み込む
担当者 = Range("請求書!F1").Value 'プルダウンリストで指定した内容のセルを読み込む
担当者列 = Application.WorksheetFunction.Match(担当者, Range("日程表!B1:F1"), 0) '"日程表!B1:F1"は、担当者の名前の範囲

請求書.Activate
請求書.CurrentRegion.ClearContents '作業領域の消去
日程表検査位置 = 0: 請求書転記位置 = 0: 合計 = 0
請求書.Offset(請求書転記位置, 日付).Value = "日付": 請求書.Offset(請求書転記位置, 金額).Value = "金額" '項目名の作成
請求書転記位置 = 請求書転記位置 + 1 '項目名を書いたので１つ下げる
Do While (日程表.Offset(日程表検査位置, 日付).Value <> "") '日程表の日付があるデータを検索する日付の並びに空白はないこと
If 日程表.Offset(日程表検査位置, 担当者列).Value = 店名 And 日程表.Offset(日程表検査位置, 担当者列 + 1).Value <> 0 Then '日程表に金額が入っている時転記する
請求書.Offset(請求書転記位置, 日付).Value = 日程表.Offset(日程表検査位置, 日付).Value
請求書.Offset(請求書転記位置, 金額).Value = 日程表.Offset(日程表検査位置, 担当者列 + 1).Value
合計 = 合計 + 請求書.Offset(請求書転記位置, 金額).Value
請求書転記位置 = 請求書転記位置 + 1 '１つ書いたので１つ下げる
End If
日程表検査位置 = 日程表検査位置 + 1 '検査する場所を１つ下げる
Loop
請求書.Offset(請求書転記位置, 日付).Value = "合計": 請求書.Offset(請求書転記位置, 金額).Value = 合計 & "円" '合計の作成
End Sub

回答No.3

terakura

考える前にお伺いしたいのですが、事例がＡ店からＣ店となっていますが、店の名前というのはあらかじめ分かっていますか？増える可能性はありますか？
それから、元のデータは日付順に並んでいるのですか？
処理の方法はＶＢＡを使うとうまくできると思います。補足していただいたら、考えてみようと思います。

補足

ご回答ありがとうございます。
店名はわかっていて20位はあり、これからも増える可能性はあります。
元のデーターは質問で書いた日程表なのですが、表自体は日付順に並んでいるというか・・・

土曜　・・・・・・
日曜　・・・・・・　
平日　・・・・・・　
土曜　・・・・・・
日曜　・・・・・・
平日　・・・・・・
祝日　・・・・・・
土曜　・・・・・・

というように土曜と日曜の間に平日の分を1行挟んでいます。
日程なので入力するのは日付順ではなく、スケジュールのわかった所からばらばらに入力していきます。
もちろんスケジュールがなければ空白のままもあります。

反映した請求書は日付順に並んでいなくても構いません。
日程表に入力してすぐに反映というのではなく、1か月分まとめて月末に反映させても構いません。
VBAで作成できるというのはわかりましたがなにせ初心者なので、勉強してやってみようとは思うのですがまず何をどうしてどのようにすればいいのかがさっぱりわからなくてお手上げ状態です(T_T)
ご回答いただければとても嬉しいです。
なにとぞよろしくお願いいたします。

回答No.2

moon_piyo

こんにちは
昔，似たようなことをmatch関数とoffset関数を使って行いました
match関数で "A店" の位置の検索を行うのですが，そのときに，
「直前の検索でみつけた "A店" の真下のセルから検索する」という作戦でいきます

○○さんの店名がB列に格納されて，見出しが1行目にあるとします
データは50件を想定します

まず検索開始行を決めます(初回0)
その位置から検索を開始し，何番目に"A店"が出現するか，をMATCHで取得します
次の検索を行う位置は 検索開始行 = 前回の検索開始位置 + 前回のMATCH関数の戻り値，とします

1回目の検索
検索開始: 0
発見位置: match("A店", offset(B1,検索開始(0),0,50-検索開始,1)) .... 2
日付 : OFFSET(B1,検索開始{0}+発見位置{2},-1,1,1) {10/1}
金額 : OFFSET(B1,検索開始{0}+発見位置{2},1,1,1) {500}

2回目の検索
検索開始: 前回の検索開始{0}+ 前回の発見位置{2} .... 2
発見位置: match("A店", offset(B1,検索開始{2},0,50-検索開始(2),1)) .... 2
日付 : OFFSET(B1,検索開始{2}+発見位置{2},-1,1,1) {10/8}
金額 : OFFSET(B1,検索開始{2}+発見位置{2},1,1,1) {200}

3回目の検索
検索開始: 前回の検索開始{2} + 前回のOFFSET{2} .... 4
発見位置: match("A店", offset(B1,検索開始{4},0,50-検索開始{4},1)) ... #N/A

※ {}内の数は実際の値をあらわします

また
合計行のところは OFFSETの第2パラメータが #N/Aかどうか，第2パラメータの一つ上の値が#N/Aかどうかをチェックすれば実現できると思います

回答No.1

BLUEPIXY

やはり、マクロを使うのが一番思ったものができるとは思いますが、日程表に入力即反映とはいかない（マクロの実行という手順が必要）

参照と関数でやる場合、間を詰めるというのが難しいです。間がスカスカになってもいいなら、準備が結構面倒かもしれませんが、日程表が即反映できます。

フィルタオプションを使ってやる場合、
日程表の形式を、変更しないといけません。
データの抽出はできますが、合計は別に付け足す必要があります。

なんにしても、日程表、請求書と密接に関連した処理が必要なので、簡単には答えにくいです。

アイデアとしては、
請求書シートの上部に、
人名のプルダウンリスト，請求先のプルダウンリスト、実行ボタンなどを用意してボタンを押すと、
その下の作業領域がクリアされ
マクロで該当する条件のデータを転記する。
マクロで印刷範囲を設定して、印刷。
てな感じですか。
条件を調べるとか転記するというのは、結構簡単なので、それほど難しくはありません。

補足

ご回答ありがとうございます。
そうなんです、理想としては、
請求書の上部にそれぞれのプルダウンリストを用意して、それぞれ指定すると、それぞれの請求書が出てきてそこだけを印刷するって感じなんです！
日程表に入力して即反映しなくても構いません。1か月分まとめてでもいいんです。それと、請求書は間が空かず1行ずつ詰めたいのです。
なにかよい方法があればご回答のほどよろしくお願いいたします。