Python 多 Excel 工作簿批量合并与汇总

{"type":"doc","content":[{"type":"paragraph","attrs":{"id":"73f3ed59-9f54-47bf-81d5-74c080986608","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"问题"},{"type":"hardBreak","attrs":{"id":"7afda9b7-5431-47e9-a2a9-a5aad86f974b"}},{"type":"text","text":"分公司各自报一个 xlsx，科目编码相同，要把它们按“科目”纵向合并成一张全量底稿，或跨表按科目汇总金额。手工复制粘贴易错。用 pandas 批量读合并。"}]},{"type":"paragraph","attrs":{"id":"9ec8c8d7-fcd9-458f-b038-0a76e983f44c","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"示例代码"}]},{"type":"paragraph","attrs":{"id":"034749f3-95b7-4ac0-a8ec-5bb6df4d9f76","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","marks":[{"type":"textStyle","attrs":{"color":"","background":""}}],"text":"plaintext"}]},{"type":"codeBlock","attrs":{"id":"44047bb1-6dc8-4588-8cc1-fc89ccc6cf9c","language":"javascript","theme":"atom-one-dark","runtimes":0,"isHoverDragHandle":false,"key":"","languageByAi":"javascript"},"content":[{"type":"text","text":"import pandas as pd\nimport glob\nimport os\n\ndef merge_workbooks(folder, sheet_name=0):\n files = glob.glob(os.path.join(folder, \"*.xlsx\"))\n frames = []\n for f in files:\n df = pd.read_excel(f, sheet_name=sheet_name)\n df[\"source\"] = os.path.basename(f)\n frames.append(df)\n return pd.concat(frames, ignore_index=True)\n\ndef summarize_by_account(merged, code_col, amount_col):\n return merged.groupby(code_col, as_index=False)[amount_col].sum()\n\nmerged = merge_workbooks(\"./branches\")\nsummary = summarize_by_account(merged, \"科目编码\", \"本期发生额\")\nsummary.to_excel(\"consolidated.xlsx\", index=False)\n"}]},{"type":"paragraph","attrs":{"id":"cd595363-d127-45e4-92a3-7b03db903c42","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"关键点"}]},{"type":"bulletList","attrs":{"id":"22723422-71dc-400e-a56f-8713ab133fef","isHoverDragHandle":false},"content":[{"type":"listItem","attrs":{"id":"c30938d4-aa0b-4636-a947-01f674594e56"},"content":[{"type":"paragraph","attrs":{"id":"47691b32-dd3a-4087-9372-3f8eb22a0933","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"glob 取目录下全部 xlsx；pd.concat 纵向拼；加 source 列溯源，哪行来自哪家公司一目了然。"}]}]},{"type":"listItem","attrs":{"id":"1a38353b-1abe-4d77-98b1-9add6ed36d26"},"content":[{"type":"paragraph","attrs":{"id":"76e81b51-5aee-4987-9223-dd26ada33d57","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"汇总用 groupby 按科目编码求和；若各公司编码口径不一，先统一映射再汇总。"}]}]},{"type":"listItem","attrs":{"id":"adc84bce-e0a5-4b26-a515-9027b37b175d"},"content":[{"type":"paragraph","attrs":{"id":"c4740fb3-3814-4baa-8215-c872a957e085","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"大批量读用 openpyxl 引擎只读模式更快：pd.read_excel(f, engine=\"openpyxl\")。"}]}]}]},{"type":"paragraph","attrs":{"id":"c97fa0dd-e29b-4c1a-b501-dc4659e08334","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"小结"},{"type":"hardBreak","attrs":{"id":"8017917b-5327-4867-a104-547656f6346c"}},{"type":"text","text":"分公司数据合并 = 读全部 + 拼 + 分组汇总。pandas 三行核心代码替代一上午复制粘贴。"}]},{"type":"horizontalRule","attrs":{"id":"4c03293f-8370-47b2-b057-e155f701d83b","isHoverDragHandle":false}},{"type":"paragraph","attrs":{"id":"d6e262d8-42d5-482d-8de1-f76b9f21a6b3","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"你合并分公司数据最常卡在“编码口径不一致”还是“表头不同”？评论区说。"}]},{"type":"paragraph","attrs":{"id":"a5a0c4ac-5ebb-466f-877b-0d54295b5a6c","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"如果有用，点赞收藏关注，留下审计难题。下期预告：财务异常检测——统计离群点自动筛查。"}]},{"type":"paragraph","attrs":{"id":"aa7092d7-07d8-4008-8ba6-1257cf9420ae","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"会计师事务所的一名注册会计师"}]}]}