用 pandas 自动生成银行余额调节表（附完整代码）

{"type":"doc","content":[{"type":"paragraph","attrs":{"id":"82d4cd8f-4a75-4184-a840-ac30a936c7a6","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"企业账面银行存款余额和银行对账单余额常对不上，差异来自未达账项：企业已记银行未记、银行已记企业未记。手工编调节表容易漏。本文用 pandas 把两边流水按金额比对，自动标出未达项，并验证调节后余额是否相等。"}]},{"type":"paragraph","attrs":{"id":"f5e005e3-90bc-4384-8007-e291416368db","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"一、数据假设"}]},{"type":"paragraph","attrs":{"id":"94095446-af54-4f8c-8ec1-c2d8de46457e","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"book.xlsx（企业账面）与 bank.xlsx（银行对账单）至少包含这些列（代码中用英文列名，便于直接运行）："}]},{"type":"bulletList","attrs":{"id":"e25dd656-9c8f-44b6-b7a1-8de98a4a91f1","isHoverDragHandle":false},"content":[{"type":"listItem","attrs":{"id":"d7394461-bb3d-4b95-b0b8-7607735b29b1"},"content":[{"type":"paragraph","attrs":{"id":"dd562d95-bbb5-4222-aae9-be857b02a602","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"date 日期"}]}]},{"type":"listItem","attrs":{"id":"1465a8e7-2d60-4be1-aee3-34e60d90ad79"},"content":[{"type":"paragraph","attrs":{"id":"7635a4fa-463d-4ce3-b863-17e1ea56c339","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"desc 摘要"}]}]},{"type":"listItem","attrs":{"id":"4703a7cc-978b-462e-afcd-8744a16302ed"},"content":[{"type":"paragraph","attrs":{"id":"6dc61d0c-082d-48b0-a6df-06718145b75b","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"amount 金额（收正付负）"}]}]}]},{"type":"paragraph","attrs":{"id":"7fc18e9f-0093-4a91-bd8d-bde8f075d362","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"（实战把路径和列名换成你自己的即可）"}]},{"type":"paragraph","attrs":{"id":"ab8fd8a4-bb6c-432d-895e-8b92776191b4","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"二、核心思路"}]},{"type":"orderedList","attrs":{"id":"bf12fd18-8afd-41bf-9ba8-f41d7769ab38","start":1,"isHoverDragHandle":false},"content":[{"type":"listItem","attrs":{"id":"61e62a8f-e493-43bd-8b37-425433497373"},"content":[{"type":"paragraph","attrs":{"id":"c8f05577-15fb-4374-8fb5-5db898d3275b","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"找出\"账面有、对账单无\"的未达项（企业已记银行未记）；"}]}]},{"type":"listItem","attrs":{"id":"1b01bcb2-c872-4f41-8598-e2042adff1eb"},"content":[{"type":"paragraph","attrs":{"id":"b2290f54-bbf8-4d52-873c-8fed7aa7cb66","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"找出\"对账单有、账面无\"的未达项（银行已记企业未记）；"}]}]},{"type":"listItem","attrs":{"id":"b1951fb8-9f11-4e07-b837-c5adeff900d2"},"content":[{"type":"paragraph","attrs":{"id":"600734e1-62c6-4f2c-91f9-e0f4e103918c","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"以账面余额为基础，加银行已记企业未记项、减企业已记银行未记项，得到调节后余额；"}]}]},{"type":"listItem","attrs":{"id":"8a5dada1-02a1-43c0-9e96-87b442bd0468"},"content":[{"type":"paragraph","attrs":{"id":"922a3109-dd8b-478e-9e72-1d8f8032f009","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"与银行对账单余额比对，相等即通过。"}]}]}]},{"type":"paragraph","attrs":{"id":"b47c33aa-a72d-4c4b-9d16-cdac386fb430","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"三、完整代码（自包含，可直接跑）"}]},{"type":"codeBlock","attrs":{"id":"7a49433b-25fc-4f8f-9f04-94f3a5e61c7f","language":"javascript","theme":"atom-one-dark","runtimes":0,"isHoverDragHandle":false,"key":"","languageByAi":"javascript"},"content":[{"type":"text","text":"import pandas as pd\n\nbook_bal = 100000.0\nbank_bal = 103000.0\n\nbook = pd.DataFrame({\n \"date\": [\"2026-03-28\", \"2026-03-30\"],\n \"desc\": [\"recv A\", \"pay B\"],\n \"amount\": [5000.0, -2000.0],\n})\nbank = pd.DataFrame({\n \"date\": [\"2026-03-29\"],\n \"desc\": [\"recv A\"],\n \"amount\": [5000.0],\n})\n\n# book items not in bank yet (enterprise recorded, bank not)\nunmatched_book = book[~book[\"amount\"].isin(bank[\"amount\"])]\n# bank items not in book yet (bank recorded, enterprise not)\nunmatched_bank = bank[~bank[\"amount\"].isin(book[\"amount\"])]\n\nadj_bal = book_bal + unmatched_bank[\"amount\"].sum() - unmatched_book[\"amount\"].sum()\n\nprint(\"unmatched book side:\\n\", unmatched_book)\nprint(\"unmatched bank side:\\n\", unmatched_bank)\nprint(\"adjusted book balance:\", adj_bal)\nprint(\"bank statement balance:\", bank_bal)\nprint(\"reconciled:\", abs(adj_bal - bank_bal) < 0.01)\n"}]},{"type":"paragraph","attrs":{"id":"2a1cf40a-2e3e-4808-84aa-f2cf5310bd42","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"四、几个实战坑"}]},{"type":"bulletList","attrs":{"id":"ca739ace-69db-4885-84ca-7ef17cd6cc54","isHoverDragHandle":false},"content":[{"type":"listItem","attrs":{"id":"f67eff90-a541-457d-9d62-b837ed918378"},"content":[{"type":"paragraph","attrs":{"id":"2586698b-a785-4c7c-8398-bbe47ac1c83c","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"仅凭金额匹配会误判（同金额多笔），实战加日期容差或凭证号辅助键；"}]}]},{"type":"listItem","attrs":{"id":"a364c3cd-6e9e-44dd-aa8d-acd47d5f9b0f"},"content":[{"type":"paragraph","attrs":{"id":"a8598aeb-2476-48bc-9665-6694f17b5b06","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"借方贷方方向两边可能相反，统一成\"收正付负\"再比；"}]}]},{"type":"listItem","attrs":{"id":"1c7ac6d8-3de7-4da1-aa19-ec03810868c7"},"content":[{"type":"paragraph","attrs":{"id":"aee6d5e5-4e3d-4f04-a4a8-7638cb4c6a03","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"多账户要按账号分组合并，不能跨账户混比；"}]}]},{"type":"listItem","attrs":{"id":"90229ae6-576d-4d4e-b05b-4c3041741aef"},"content":[{"type":"paragraph","attrs":{"id":"5f697901-9521-42b5-b33f-b266707b93af","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"调节后仍有差异说明有记账错误，需逐笔查；"}]}]},{"type":"listItem","attrs":{"id":"e542081d-d052-4997-ab7c-aefc5b38650d"},"content":[{"type":"paragraph","attrs":{"id":"b6bf03b6-3d2a-4158-aa00-6c6bc0050b79","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"读 csv 注意指定编码为 gbk（Windows 中文环境），xlsx 一般无此问题。"}]}]}]},{"type":"paragraph","attrs":{"id":"7aebf8a7-7406-438c-803d-c8c09aea7248","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"五、边界"}]},{"type":"paragraph","attrs":{"id":"1a2ac4de-dc3b-496f-843f-7d58741f768d","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"脚本只负责比对和标注未达项，差异原因和账务调整由你判。客户数据本地跑，别贴通用云端大模型。"}]},{"type":"paragraph","attrs":{"id":"7c895d4f-4078-4817-8564-73d1e56e03bf","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"我是会计师事务所的一名注册会计师，做过财报审计、经济责任审计、专项审计、尽职调查、内控评价各类项目，从手写底稿到 AI 辅助都经历过。如果你也在财务 / 审计岗位想提效，欢迎关注、交流实战。"}]},{"type":"paragraph","attrs":{"id":"06191ce8-e2eb-41cd-99d7-f1e8bc568c9d","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"感谢你看到这里。如果这段代码对你有用，欢迎点赞 + 收藏 + 关注——我是会计师事务所的一名注册会计师，会继续分享审计提效与 Python 实战，咱们下篇见。"}]}]}