「Excel・VBA」sheet1にあるリスト通りに、別シートの請求書内容を置き換えたい

質問

こんばんは！
明日仕事で使う為に、昨晩から色々試してるのですが
知恵をかして頂ければと思いますm(__)m

エクセルのブックに
sheet1と複数の請求書のsheetか存在します。

sheet1は請求書に必要な情報が入ってます。（データベースのようなものです）

　会社名(1)　　商品名(1)　商品名(2)　商品金額(1)　商品金額(2)
1　○○商事　りんご　　　みかん　　　　200　　　　100
2　○○会社　りんご　　　みかん　　　　250　　　　150

請求書は、取引先独自の請求書が100種類くらいあります。
なので、書式は様々です。
しかし、記載する内容は同じものなので、
例えば、請求書に　会社名(1)と入っていたら（○○商事）　　
　　　　　　　　　商品コード(1)と入っていたら（りんご）
　　　　　　　　　商品金額(1)と入っていたら（200）などと
置換していく方法はないでしょうか？
商品の種類も3種類しかないので、右に項目を作ってます。
ただ請求書の種類が多いもので・・・

途中まで数式を組んだりマクロの記録で試したのですが
VLOOKUPの数式が残ってしまったり、
次の会社に進んでくれなかったりで
ＶＢＡの知識が乏しく調べてもわかりませんでした。

私もまだ調べながら作りますので
もしおわかりになる方いらっしゃいましたらよろしくお願いいたします。

回答No.4

imogasi · ベストアンサー

普通、今までの質問は、請求書の様式が１種類がほとんどだった。
Googleで「imogasi 請求書」で照会のこと。過去回答が出ると思う。
ーー
基本的にこうなると（種類が多いと）、VBAでプログラムを組まないと自動化できないでしょう。
ーー
関数によるとしても、会社名にしても各社入れる場所が違うので、
１００種類あれば１００通りｘ項目数のセルに式を埋め込まないといけないだろう。ただコピー貼り付けは可能と思うが。
ーー
そのときSheet1における請求先当社のデータの有る行番号を入れるセルを作るのはどうかな。
例
Sheet1
ーーーー　A列　　　B列
第2行目　東商事 名古屋市
Sheet2
Å1に2(東商事を示す）
B3は住所として　=INDEX(Sheet1!$A$1:$D$300,2,2)
C4は会社名として　=INDEX(Sheet1!$A$1:$A$300,A1,1)
式はほとんど似ているでしょう。第３引数だけ変わる。理屈上そうなります。
B3,C4の場所（セル）は、各会社の様式により決まる。
ーー
Sheet2以下の各社の請求書様式のある、シートのA1
にSheet1のデータの行番号を入れていく。
（作業的には大変だが）
それで内容的にはSheet1のデータが、各シートに収まるはず。
ーー
印刷
そこで（Sheet1以外の）シートを全て選択（Shiftを押しつつ）して１度に印刷できるから、全社分を印刷（各々２枚とかもできると思う）する。
ーー
問題はA1セルに毎月（毎締め日）行番号を書き改めないといけない（可能性が大）。
Sheet１の行が、ほとんど上位（固定得意先）は固定ならやややりやすいが。
が問題だが、VBAなら会社名から、その会社の請求書様式のあるシート名を探せるのだが、VBAを勉強してからのお楽しみかな。
レイアウトまでVBAで毎月作成しなおしは、プログラムが複雑になる。
フォント罫線なども各社再現は複雑。シートは１回人手で作って毎回使いまわしがよかろう。

回答No.3

hallo-2007

>sheet1は請求書に必要な情報が入ってます。（データベースのようなものです）
この辺りからおかしくありませんか？
会社名(1)　　りんご　　　みかん
1　○○商事　　200　　　　100
2　○○会社　　250　　　　150
で十分なような気がしますが、
より良いデータベースであれば
　納品日　取引先名　　品名　数量　金額・・・
　7/8　　　○○商事　りんご　200
　7/8　　　○○商事　みかん　100
・・・と入力してみてください。
次に、記載する内容は同じものなので、　を集計するシート1枚作成します。
　取引先　　○○商事
　期間　　　7/1 ～　7/30
品名　　　数量　　金額
　りんご
　みかん
と準備して、条件にあった数量や金額が計算される関数を考えます。
次に請求書の様式にあわせた請求書のシートを100パターン作成します。
ＶＢＡを活用するならば、印刷ボタンを押すと、取引先に応じて必要な様式の請求書を自動で印刷する様なマクロを考えると良いでしょう。

回答No.2

shinkami

解決できましたか？
商品３種類を請求するパターンが１００種類もあるというのが理解できません。
請求書(sheet2)から請求情報(sheet1)を作成するほうが分かりやすいかも
その際、無効請求書に対応する行は空白になるように条件式「＝if(無効の論理式,"",sheet2のセル）
sheet1でフィルタ機能（データメニューからフィルター→オートフィルター）を使って空白行を表示しないとしておくとよいでしょう

回答No.1

turuzou

失礼します、参考までに。
＞（データベースのようなものです）
1行1データに変更すれば、VLOOKUP関数でも対応できると思います。
例えば、A列に社名コード、B列に重複の無い社名コード＆商品コード、C列に社名、D列に商品名、E列に金額にしたとして、この範囲に名前を定義(DB_1)して
=VLOOKUP(検索値,範囲,列番号,検索の型)　を↓
=VLOOKUP(社名コード＆商品コード,DB_1,列番号,FALSE)　さらに↓
=IF(COUNTIF(DB_1,社名コード＆商品コード),VLOOKUP(・・・,・・・,・・・,・・・),"")　と、すれば、不必要な数式も表示されません。