Excel和Google Sheets公式、模板与脚本

表格结构 — "发票"工作表

列	表头	填写方式
A	发票号	手动
B	客户	手动（从"客户"工作表下拉选择）
C	开票日期	手动
D	金额	手动
E	账期（天）	公式
F	到期日	公式
G	付款日期	手动（付款前留空）
H	状态	公式
I	逾期天数	公式

公式（节选，10 个中的 6 个）

E2 — 从客户列表读取账期 =IFERROR(VLOOKUP(B2, '客户'!A:C, 3, FALSE), 30) 在"客户"工作表中查找该客户的账期；如果找不到该客户，默认 30 天。

F2 — 到期日 =IF(C2="", "", C2 + E2) 开票日期加上账期。空行保持为空。

H2 — 状态 =IF(C2="", "", IF(G2<>"", "已付款", IF(TODAY()>F2, "逾期", "未到期"))) 填了付款日期即为"已付款"；否则过了到期日为"逾期"，没过则为"未到期"。

I2 — 逾期天数 =IF(H2="逾期", TODAY() - F2, "")

'看板'!B2 — 未收款总额 =SUMIFS('发票'!D:D, '发票'!H:H, "<>已付款", '发票'!C:C, "<>")

'看板'!A5 — 各客户欠款（一个公式生成整张表） =QUERY('发票'!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 行，然后向下拖到第 500 行
• 两个看板公式各粘贴一次即可，之后自动更新
• 对 H2:H500 设置条件格式：文本为"逾期"→ 浅红色填充

已考虑的特殊情况

• **部分付款的发票：**新增一行填写剩余金额，编号如 INV-041b
• **客户改名：**只在"客户"工作表中修改；下拉菜单会让各行保持一致
• **用 Excel 而不是 Google 表格：**除 QUERY 外所有公式都能用；改用对 B、D、H 列做数据透视表

验算示例

发票 INV-038，客户北岸口腔诊所，9 月 1 日开票，账期 14 天 → 9 月 15 日到期。今天是 10 月 20 日，未付款 → 逾期，35 天。