[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『請求コードごとの請求書作成方法について』（たはたは）
会社の請求書発行部門で業務をしている者です。
社内のシステム変更が行われることになり、色々請求書発行方法も変わるので、エクセルで作成出来ないか検討しています。
今困っているのが、請求コードごとの請求書作成方法について、です。
１件のお客様、納品場所ごとに請求書を分けてほしいとご要望の為、納品場所ごとに請求コードを登録しています。
請求コードごとに、品名、規格、数量、単価、金額、備考、月の合計納品金額
を毎月発行しています。
今までは上記の請求書発行を自動で出来るシステムがあり、ボタンひとつ押せば請求コードごとの請求書が発行出来ていました（請求コードは全部で１５個ほど
）
それが会社の経費削減でシステムが使えなくなり、代わりの方法を模索しています。
月の納品履歴の基データはＣＳＶで出せるのですが、請求コードを軸にピポットテーブルのように「請求コードごとに、品名、規格、数量、単価、金額、備考、月の合計納品金額」を集計して、
請求コードごとに請求書を、出来たら差し込み印刷のように瞬時に出来る方法を探していました。
自分なりにネットで探しましたが、
・ピポットテーブルの「レポートフィルターページの表示」で請求先コードごとに抽出する→これだと用意した請求書フォーマットでなく新規のエクセルページに反映してしまう。結果を請求書フォーマットにコピペすればいいのかもしれませんが時間がかかる。。
・FILTER関数を用いて、請求書フォーマットに請求コードを入力して1枚ずつ印刷する
https://www.youtube.com/watch?v=UZ9_CxNwDs0
上記のyoutubeを参考にと思いましたが、出来たら1枚ずつ印刷でなく、一度印刷ボタンを押したらすべての請求コードごとに一斉印刷できる仕組みが良い
上記の方法が可能になる対応策を、ご教示いただけますと幸いです。
宜しくお願いいたします。
＜ 使用 Excel：Excel2021、使用 OS：Windows11 ＞
たたき台としては、こんな感じでしょうか 請求書シートの請求コードを入力するセルには、名前定義（"請求コード"）
Sub test()
Dim t As ListObject
Dim wsPrint As Worksheet
Dim e
Set t = Worksheets("Data").ListObjects(1)
Set wsPrint = Worksheets("請求書")
For Each e In WorksheetFunction.Unique(Range(t.Name & "[請求コード]"))
wsPrint.Range("請求コード").Value = e
wsPrint.PrintOut preview:=True
Next
End Sub （マナ） 2023/10/23(月) 00:03:02
Option Explicit
Sub main()
Dim kAry(), dAry()
zwSDelete
srccsv_dummydata_make
file_read kAry, dAry
printws_pageset
mprint kAry, dAry
zwSDelete
Erase kAry, dAry
End Sub
Private Sub zwSDelete()
Dim i As Long
If Not Evaluate("=ISREF(Sheet1!A1)") Then Sheets.Add.Name = "Sheet1"
Application.DisplayAlerts = False
For i = Worksheets.Count To 1 Step -1
If Worksheets(i).Name <> "Sheet1" Then
Worksheets(i).Delete
End If
Next
Application.DisplayAlerts = True
End Sub
Private Sub mprint(k(), w())
Dim i&, j&, a&, n&, y&, x&, mi(), old, inv_flg As Boolean
Dim g#, ag#, p&, cnt&, s&, acnt&
mi = Application.Index(w, 1, 0)
With Worksheets("請求書")
.Activate
.UsedRange.Clear
.Cells(1, 4) = "請 求 書"
old = k(0)
y = 5: x = 1: p = 1
For i = 0 To UBound(k)
If k(i) <> old Then
y = 5
If .Rows(y).Cells(1) <> "" Then
.Cells(2, 9) = p
With .UsedRange.SpecialCells(xlLastCell)
.Offset(2, 0) = g
.Offset(2, -1) = "総合計"
End With
.UsedRange.Columns.AutoFit
.PrintPreview
Intersect(.UsedRange, .Range(.Rows(y), .Rows(.Rows.Count))).Clear
End If
cnt = 0
p = 1
g = 0
End If
For j = 2 To UBound(w, 1)
If k(i) = w(j, 4) Then
cnt = cnt + 1
acnt = acnt + 1
If cnt = 1 Then
.Cells(2, 9) = p
.Cells(2, 2) = w(j, 3) & " 様"
.Cells(2, 5) = w(j, 5) & " 御中"
.Cells(4, 1).Resize(, UBound(mi)) = mi
'w → 1,6-11
End If
.Cells(y, x) = w(j, 1)
g = g + w(j, 10)
ag = ag + w(j, 10)
For n = 1 To 11
.Cells(y, x + n - 1) = w(j, n)
Next
y = y + 1
If y > 22 Then
s = j + 1
For a = s To UBound(w, 1)
If k(i) = w(a, 4) Then
inv_flg = True
Exit For
End If
Next
If Not inv_flg Then
With .UsedRange.SpecialCells(xlLastCell)
.Offset(2, 0) = g
.Offset(2, -1) = "総合計"
End With
End If
inv_flg = False
.Cells(2, 9) = p
p = p + 1
.UsedRange.Columns.AutoFit
.PrintPreview
y = 5
Intersect(.UsedRange, .Range(.Rows(y), .Rows(.Rows.Count))).Clear
End If
old = w(j, 4)
End If
Next
Next
.UsedRange.Clear
End With
Erase mi
MsgBox "総額 = " & Format(ag, "#,#") & Chr(13) & "処理件数 = " & acnt
End Sub
Private Sub printws_pageset()
'11*25
If Not Evaluate("=ISREF(請求書!A1)") Then Sheets.Add.Name = "請求書"
With Worksheets("請求書").PageSetup
.PaperSize = xlPaperA4
.Orientation = xlLandscape
.PrintArea = "A1:K24"
.Zoom = False
.FitToPagesTall = False
.FitToPagesWide = 1
End With
End Sub
Sub file_read(tkey() As Variant, w() As Variant)
Dim i&, j&, ps$, fnm$, buf$, mi$, v, idx()
Dim zA
ps = ThisWorkbook.Path & "\"
fnm = "DummyForTest.csv"
Open ps & fnm For Input As #1
'見出し1件、読み飛ばし
Line Input #1, mi
v = Split(mi, ",")
ReDim Preserve idx(i)
idx(i) = v
i = i + 1
Erase v
Do
If EOF(1) Then Exit Do
Line Input #1, buf
If CDbl(CDate(Left(buf, 10))) >= DateSerial(2023, 9, 1) And _
CDbl(CDate(Left(buf, 10))) <= DateSerial(2023, 9, 30) Then
v = Split(buf, ",")
ReDim Preserve idx(i)
idx(i) = v
Erase v
i = i + 1
If i Mod 1280 = 0 Then DoEvents
End If
Loop
Close
Kill ps & fnm
w = Application.Index(idx, 0, 0)
Set zA = CreateObject("System.Collections.ArrayList")
For i = 1 To UBound(w, 1)
If Not zA.Contains(w(i, 4)) Then zA.Add w(i, 4)
Next
zA.Sort
tkey = zA.toarray()
Erase idx
zA.Clear
If Not Evaluate("=ISREF(data!A1)") Then Sheets.Add.Name = "data"
With Worksheets("data")
.UsedRange.Clear
.Cells(1).Resize(UBound(w, 1), UBound(w, 2)) = w
End With
End Sub
Private Sub srccsv_dummydata_make()
Rnd -7
Dim x&, iMax&, i&, j&, mi(), idx(), w(), zd, tx$, ps$, fnm$
Dim sm(), tm(), rn&
mi = Array("日付", "得意先コード", "得意先名", "請求コード", "請求先名", _
"品名", "規格", "数量", "単価", "金額", "備考")
iMax = 2000
x = Int(iMax / 30) + 1
zd = DateSerial(2023, 8, 21)
ps = ThisWorkbook.Path & "\"
fnm = "DummyForTest.csv"
Open ps & fnm For Output As #1
Print #1, Join(mi, ",")
ReDim sm(1 To 25, 1 To 4)
sm(1, 1) = "商品コード": sm(1, 2) = "商品名": sm(1, 3) = "単価": sm(1, 4) = "規格"
sm(2, 1) = "A10001": sm(2, 2) = "かけうどん": sm(2, 3) = "300": sm(2, 4) = "和食"
sm(3, 1) = "A10002": sm(3, 2) = "ハイカラうどん": sm(3, 3) = "500": sm(3, 4) = "和食"
sm(4, 1) = "A10003": sm(4, 2) = "冷やしうどん": sm(4, 3) = "550": sm(4, 4) = "和食"
sm(5, 1) = "A10004": sm(5, 2) = "かけそば": sm(5, 3) = "350": sm(5, 4) = "和食"
sm(6, 1) = "A10005": sm(6, 2) = "ハイカラそば": sm(6, 3) = "450": sm(6, 4) = "和食"
sm(7, 1) = "10006": sm(7, 2) = "ザルそば": sm(7, 3) = "900": sm(7, 4) = "和食"
sm(8, 1) = "A10007": sm(8, 2) = "玉子丼": sm(8, 3) = "500": sm(8, 4) = "和食"
sm(9, 1) = "A10008": sm(9, 2) = "天丼": sm(9, 3) = "1000": sm(9, 4) = "和食"
sm(10, 1) = "A10009": sm(10, 2) = "木の葉丼": sm(10, 3) = "700": sm(10, 4) = "和食"
sm(11, 1) = "A10010": sm(11, 2) = "おにぎり定食": sm(11, 3) = "600": sm(11, 4) = "和食"
sm(12, 1) = "A10011": sm(12, 2) = "ラーメン": sm(12, 3) = "500": sm(12, 4) = "中華"
sm(13, 1) = "A10012": sm(13, 2) = "ラーメン定食": sm(13, 3) = "800": sm(13, 4) = "中華"
sm(14, 1) = "A10013": sm(14, 2) = "味噌ラーメン": sm(14, 3) = "600": sm(14, 4) = "中華"
sm(15, 1) = "A10014": sm(15, 2) = "餃子": sm(15, 3) = "300": sm(15, 4) = "中華"
sm(16, 1) = "A10015": sm(16, 2) = "カレーライス": sm(16, 3) = "500": sm(16, 4) = "洋食"
sm(17, 1) = "A10016": sm(17, 2) = "カツカレー": sm(17, 3) = "800": sm(17, 4) = "洋食"
sm(18, 1) = "A10017": sm(18, 2) = "ハンバーグ": sm(18, 3) = "600": sm(18, 4) = "洋食"
sm(19, 1) = "A10018": sm(19, 2) = "ハムエッグ": sm(19, 3) = "450": sm(19, 4) = "洋食"
sm(20, 1) = "A10019": sm(20, 2) = "日替わり定食": sm(20, 3) = "500": sm(20, 4) = "D"
sm(21, 1) = "A10020": sm(21, 2) = "洋食定食": sm(21, 3) = "900": sm(21, 4) = "洋食"
sm(22, 1) = "A10021": sm(22, 2) = "中華定食": sm(22, 3) = "800": sm(22, 4) = "中華"
sm(23, 1) = "A10022": sm(23, 2) = "和定食": sm(23, 3) = "1000": sm(23, 4) = "和食"
sm(24, 1) = "A10023": sm(24, 2) = "サンマ定食": sm(24, 3) = "600": sm(24, 4) = "和食"
sm(25, 1) = "A10024": sm(25, 2) = "出汁まき定食": sm(25, 3) = "500": sm(25, 4) = "和食"
'*
ReDim tm(1 To 20, 1 To 4)
tm(1, 1) = "顧客コード": tm(1, 2) = "顧客名": tm(1, 3) = "請求コード": tm(1, 4) = "請求先名"
tm(2, 1) = "10001": tm(2, 2) = "Ｘ病院": tm(2, 3) = "50001": tm(2, 4) = "x精神科"
tm(3, 1) = "10001": tm(3, 2) = "Ｘ病院": tm(3, 3) = "50002": tm(3, 4) = "x外科"
tm(4, 1) = "10002": tm(4, 2) = "八百屋": tm(4, 3) = "50003": tm(4, 4) = "しぶやスーパ"
tm(5, 1) = "10002": tm(5, 2) = "八百屋": tm(5, 3) = "50004": tm(5, 4) = "メグロや"
tm(6, 1) = "10002": tm(6, 2) = "八百屋": tm(6, 3) = "50005": tm(6, 4) = "歌舞伎店"
tm(7, 1) = "10002": tm(7, 2) = "八百屋": tm(7, 3) = "50006": tm(7, 4) = "本所支店"
tm(8, 1) = "10003": tm(8, 2) = "床屋": tm(8, 3) = "50007": tm(8, 4) = "床屋"
tm(9, 1) = "10004": tm(9, 2) = "寝具屋": tm(9, 3) = "50008": tm(9, 4) = "ふとんや"
tm(10, 1) = "10004": tm(10, 2) = "寝具屋": tm(10, 3) = "50009": tm(10, 4) = "べっど"
tm(11, 1) = "10004": tm(11, 2) = "寝具屋": tm(11, 3) = "50010": tm(11, 4) = "まくら"
tm(12, 1) = "10004": tm(12, 2) = "寝具屋": tm(12, 3) = "50011": tm(12, 4) = "もうふ"
tm(13, 1) = "10005": tm(13, 2) = "電気屋本社": tm(13, 3) = "50012": tm(13, 4) = "第1支社"
tm(14, 1) = "10005": tm(14, 2) = "電気屋本社": tm(14, 3) = "50013": tm(14, 4) = "第2支社"
tm(15, 1) = "10005": tm(15, 2) = "電気屋本社": tm(15, 3) = "50014": tm(15, 4) = "第3支社"
tm(16, 1) = "10005": tm(16, 2) = "電気屋本社": tm(16, 3) = "50015": tm(16, 4) = "第4支社"
tm(17, 1) = "10005": tm(17, 2) = "電気屋本社": tm(17, 3) = "50016": tm(17, 4) = "第5支社"
tm(18, 1) = "10005": tm(18, 2) = "電気屋本社": tm(18, 3) = "50017": tm(18, 4) = "第6支社"
tm(19, 1) = "10005": tm(19, 2) = "電気屋本社": tm(19, 3) = "50018": tm(19, 4) = "第7支社"
tm(20, 1) = "10005": tm(20, 2) = "電気屋本社": tm(20, 3) = "50019": tm(20, 4) = "第8支社"
Do
For j = 1 To Int((x - 1 + 1) * Rnd + 1)
i = i + 1
If i Mod 1280 = 0 Then DoEvents
If i > iMax Then Exit Do
ReDim w(1 To UBound(mi) + 1)
rn = Int((UBound(tm) - 2 + 1) * Rnd + 2)
w(1) = zd
w(2) = tm(rn, 1)
w(3) = tm(rn, 2)
w(4) = tm(rn, 3)
w(5) = tm(rn, 4)
rn = Int((UBound(sm) - 2 + 1) * Rnd + 2)
w(6) = sm(rn, 2)
w(7) = sm(rn, 4)
w(8) = CStr(sm(rn, 3))
w(9) = CStr(Int((10 - 1 + 1) * Rnd + 1))
w(10) = CStr(CLng(w(8)) * CLng(w(9)))
w(11) = Empty
Print #1, Join(w, ",")
Next
zd = zd + 1
Loop
Close
Erase mi, idx, w, sm, tm
End Sub
（隠居Z） 2023/11/12(日) 08:40:07
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.