มาโคร Excel VBA สำหรับงานที่ทำซ้ำ

โครงสร้างชีต — แท็บ "Invoices"

คอลัมน์	หัวคอลัมน์	ใครกรอก
A	เลขที่ใบแจ้งหนี้	คุณ
B	ลูกค้า	คุณ (ดรอปดาวน์จากแท็บ "Clients")
C	วันที่ออก	คุณ
D	จำนวนเงิน	คุณ
E	เครดิตเทอม (วัน)	สูตร
F	วันครบกำหนด	สูตร
G	วันที่ได้รับชำระ	คุณ (เว้นว่างไว้จนกว่าจะได้เงิน)
H	สถานะ	สูตร
I	เกินกำหนด (วัน)	สูตร

สูตร (บางส่วน 6 จาก 10)

E2 — เครดิตเทอมจากรายชื่อลูกค้า =IFERROR(VLOOKUP(B2, Clients!A:C, 3, FALSE), 30) ดึงเครดิตเทอมของลูกค้าจากแท็บ "Clients" ถ้าไม่มีชื่อลูกค้าในแท็บ ใช้ 30 วัน

F2 — วันครบกำหนด =IF(C2="", "", C2 + E2) บวกเครดิตเทอมเข้ากับวันที่ออก แถวว่างจะยังว่างอยู่

H2 — สถานะ =IF(C2="", "", IF(G2<>"", "ชำระแล้ว", IF(TODAY()>F2, "เกินกำหนด", "รอชำระ"))) ถ้ากรอกวันที่ได้รับเงินแล้ว = ชำระแล้ว ถ้ายังและเลยวันครบกำหนด = เกินกำหนด นอกนั้น = รอชำระ

I2 — จำนวนวันที่เกินกำหนด =IF(H2="เกินกำหนด", TODAY() - F2, "")

Dashboard!B2 — ยอดค้างรับทั้งหมด =SUMIFS(Invoices!D:D, Invoices!H:H, "<>ชำระแล้ว", Invoices!C:C, "<>")

Dashboard!A5 — ยอดค้างแยกตามลูกค้า (สูตรเดียวได้ทั้งตาราง) =QUERY(Invoices!B:H, "select B, sum(D) where H <> 'ชำระแล้ว' and B is not null group by B order by sum(D) desc label sum(D) 'ยอดค้าง'", 1)

วางสูตรตรงไหน

• ใส่ E2, F2, H2 และ I2 ในแถวที่ 2 ของแท็บ "Invoices" แล้วลากลงถึงแถว 500
• วางสูตร Dashboard ทั้งสองครั้งเดียว มันจะอัปเดตเอง
• ตั้ง Conditional formatting ที่ H2:H500: ข้อความเป็น "เกินกำหนด" → พื้นสีแดงอ่อน

กรณีพิเศษที่รองรับแล้ว

• ชำระบางส่วน: เพิ่มแถวใหม่ด้วยยอดที่เหลือ และเลขที่ใหม่ เช่น INV-041b
• ลูกค้าเปลี่ยนชื่อ: แก้ที่แท็บ "Clients" ที่เดียว ดรอปดาวน์จะทำให้ทุกแถวตรงกัน
• ใช้ Excel แทน Sheets: สูตรทั้งหมดใช้ได้ ยกเว้น QUERY ให้ใช้ PivotTable กับคอลัมน์ B, D, H แทน

ตัวอย่างตรวจสอบ

ใบแจ้งหนี้ INV-038 ลูกค้า คลินิกทันตกรรมยิ้มสวย ออกวันที่ 1 ก.ย. เครดิต 14 วัน → ครบกำหนด 15 ก.ย. วันนี้ 20 ต.ค. ยังไม่จ่าย → เกินกำหนด 35 วัน