用 pandas 自动算科目余额的同比与环比变动（附完整代码）

{"type":"doc","content":[{"type":"paragraph","attrs":{"id":"6bfdcd5e-6567-4927-81da-1c6c38db3ec4","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"接手一家企业账务，先看科目余额表本期 vs 上期变动。哪个科目涨了 300%、哪个暴跌 90%，往往是审计重点。手工算几十个科目烦且易错，本文用 pandas 一键算变动率、自动标出异常波动。"}]},{"type":"paragraph","attrs":{"id":"3673a541-7744-4f77-a959-626a605e06ac","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"一、数据假设"}]},{"type":"paragraph","attrs":{"id":"0b06d41e-ee86-4a07-845d-9cda241974ab","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"trial_balance.xlsx 至少包含这些列（代码中用英文列名，便于直接运行）："}]},{"type":"bulletList","attrs":{"id":"979d4395-ba49-4dbb-be9e-9e9e11165b6d","isHoverDragHandle":false},"content":[{"type":"listItem","attrs":{"id":"47e4573c-c1ad-41ba-b1bc-f3601e981102"},"content":[{"type":"paragraph","attrs":{"id":"3ce1eec2-da73-4d3e-bbf9-13f92346a9c6","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"account 科目名称"}]}]},{"type":"listItem","attrs":{"id":"50acf71d-8dbe-44fd-aa31-25bcfce3de66"},"content":[{"type":"paragraph","attrs":{"id":"c873ad91-ea8c-413c-b037-4512580920d1","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"prior 期初/上期余额"}]}]},{"type":"listItem","attrs":{"id":"a1f6bbf9-d333-4fbc-b057-85fa0e03ca8c"},"content":[{"type":"paragraph","attrs":{"id":"db2f6949-e769-4b05-86d7-ef39455639f4","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"current 期末/本期余额"}]}]}]},{"type":"paragraph","attrs":{"id":"485a362d-c1f0-40f6-9f36-10f2f58f50b5","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"（实战把路径和列名换成你自己的即可）"}]},{"type":"paragraph","attrs":{"id":"a7917e60-7e02-422d-93bc-858f0ff11866","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"二、核心思路"}]},{"type":"orderedList","attrs":{"id":"0f3b7c8f-44d9-4f90-830b-48822ce6eae7","start":1,"isHoverDragHandle":false},"content":[{"type":"listItem","attrs":{"id":"bc960d82-2e1e-4732-9fac-b9699e5cd3a6"},"content":[{"type":"paragraph","attrs":{"id":"743eb37b-834d-4893-916b-f86a5180434a","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"变动额 = 本期 - 上期；"}]}]},{"type":"listItem","attrs":{"id":"bf0aed75-8a5c-4601-997a-5b96709ef8d9"},"content":[{"type":"paragraph","attrs":{"id":"d44bc338-4bf7-4965-a4c8-d54c0418b932","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"变动率 = 变动额 / 上期绝对值（上期为零时单列\"新增科目\"）；"}]}]},{"type":"listItem","attrs":{"id":"f99bc672-4fbe-4adc-9cee-876ec4aa2816"},"content":[{"type":"paragraph","attrs":{"id":"cd7c1fa6-32c6-4912-bb39-ffaa158aa9c4","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"变动率超过阈值（如 ±50%）或余额由正转负标记异常。"}]}]}]},{"type":"paragraph","attrs":{"id":"1796c7f5-0235-4fd5-93f1-2267d244c1df","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"三、完整代码（自包含，可直接跑）"}]},{"type":"codeBlock","attrs":{"id":"78568fc9-20e7-4d9d-882c-7398e5e2f93a","language":"javascript","theme":"atom-one-dark","runtimes":0,"isHoverDragHandle":false,"key":"","languageByAi":"javascript"},"content":[{"type":"text","text":"import pandas as pd\n\n# read your trial balance, e.g. df = pd.read_excel(\"trial_balance.xlsx\")\ndf = pd.DataFrame({\n \"account\": [\"Cash\", \"AR\", \"Inventory\", \"FixedAssets\", \"Revenue\"],\n \"prior\": [100000, 200000, 150000, 500000, 0],\n \"current\": [95000, 800000, 120000, 480000, 1200000],\n})\n\ndf[\"delta\"] = (df[\"current\"] - df[\"prior\"]).round(2)\n\ndef pct(row):\n if row[\"prior\"] == 0:\n return float(\"inf\") if row[\"current\"] != 0 else 0.0\n return (row[\"current\"] - row[\"prior\"]) / abs(row[\"prior\"])\n\ndf[\"pct\"] = df.apply(pct, axis=1).round(4)\n\nTHRESH = 0.5\ndf[\"flag\"] = df[\"pct\"].abs() > THRESH\n\nprint(df.sort_values(\"pct\", ascending=False))\nprint(\"\\nflagged accounts:\")\nprint(df[df[\"flag\"]][[\"account\", \"prior\", \"current\", \"pct\"]])\n"}]},{"type":"paragraph","attrs":{"id":"e484d914-dba8-427a-862e-40a0baef77dc","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"四、几个实战坑"}]},{"type":"bulletList","attrs":{"id":"cb682518-95b1-427f-b3dd-55059d04492c","isHoverDragHandle":false},"content":[{"type":"listItem","attrs":{"id":"56dc0546-c139-499b-ba60-a8568fd9436c"},"content":[{"type":"paragraph","attrs":{"id":"ef223ec6-2ea7-46a8-be9e-abd7c2949cd7","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"上期为零的科目（如新设收入科目）变动率无意义，要单列\"新增\"标签，别误报 1000%；"}]}]},{"type":"listItem","attrs":{"id":"09406204-8de8-4af0-847b-a46932229a8b"},"content":[{"type":"paragraph","attrs":{"id":"80378f87-63c1-46fd-bc0e-e59b173d826a","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"余额由正转负（如存货变负，可能记账错）要单独标记；"}]}]},{"type":"listItem","attrs":{"id":"40a64e71-24c8-482e-9f5d-b67d96079e91"},"content":[{"type":"paragraph","attrs":{"id":"2ee1a230-0323-49f1-86a3-7aa27c3a84dd","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"阈值分科目性质设：资产类小幅波动正常，损益类剧烈变动反而常见；"}]}]},{"type":"listItem","attrs":{"id":"6e18f751-798d-43ff-81a2-de5f7e249e03"},"content":[{"type":"paragraph","attrs":{"id":"333835fa-4566-4fae-98f6-68edbe7aa3a9","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"集团合并口径下，先抵消内部往来再算变动；"}]}]},{"type":"listItem","attrs":{"id":"a483b2d1-6e50-4149-b817-a3db19c5eb2d"},"content":[{"type":"paragraph","attrs":{"id":"3f5a3b3c-85d5-4a86-89a5-a3d189bbfa6e","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"读 csv 注意指定编码为 gbk（Windows 中文环境），xlsx 一般无此问题。"}]}]}]},{"type":"paragraph","attrs":{"id":"ea01afe1-5cc4-4972-92ed-3b243e7923ad","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"五、边界"}]},{"type":"paragraph","attrs":{"id":"bd0de9fd-2f77-4c84-93bf-00959b8286aa","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"脚本只负责算变动、标异常，是否错报、怎么查，注册会计师定。客户数据本地跑，别贴通用云端大模型。"}]},{"type":"paragraph","attrs":{"id":"c07070b2-8305-4934-9b66-6d9897583de2","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"我是会计师事务所的一名注册会计师，做过财报审计、经济责任审计、专项审计、尽职调查、内控评价各类项目，从手写底稿到 AI 辅助都经历过。如果你也在财务 / 审计岗位想提效，欢迎关注、交流实战。"}]},{"type":"paragraph","attrs":{"id":"987f7cbe-ea8b-4f37-acd8-b0667d7be68e","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"感谢你看到这里。如果这段代码对你有用，欢迎点赞 + 收藏 + 关注——我是会计师事务所的一名注册会计师，会继续分享审计提效与 Python 实战，咱们下篇见。"}]}]}