Excel库存周转率与呆滞物料分析模型

Excel库存周转率与呆滞物料分析模型盘活积压库存，释放被占用的资金·从数据到决策的完整工具致每一位对着满仓库存发愁的管理者，仓库里有这么一批物料：账面上它们值不少钱，但实际上已经好几个月没动过了。占着库位、占用资金，每次盘点都在，每次都用不上。你想知道到底哪些物料是“死库存”，占总库存的百分之多少；你也想知道每个月库存转了几圈，和上个月比是快了还是慢了。这份指南就是为你准备的。你只需要维护三张基础数据表——期初库存、入库记录、出库记录。剩下的所有分析——周转率、库龄、呆滞标识、ABC分类、超储短缺预警、动态图表——全部由公式自动完成。你不用再每个月手工拉表、肉眼判断哪个物料该处理了，这套模型会主动告诉你。目录模型总览：从基础数据到决策建议库存基础数据：三张源表的设计库存汇总表：期初、入库、出库、期末自动汇总库存周转率计算（月度/年化）库龄计算与呆滞物料自动标识物料ABC分类（金额累计占比）安全库存与再订货点建议超储与短缺红绿灯预警动态图表展示趋势生成呆滞处理建议清单导出给采购与销售部门第1章模型总览：从基础数据到决策建议整套模型由以下工作表组成，数据流向清晰：三张源表（你每月更新）

├──期初库存表

├──入库明细表

└──出库明细表

↓

库存汇总表（SUMIFS自动汇总）

↓

├──周转率分析表（月度/年化）

├──库龄与呆滞分析表（自动标红黄）

├──ABC分类表（帕累托分析）

└──超储短缺预警表（红绿灯）

↓

动态仪表盘（图表+切片器）

↓

呆滞处理建议清单（可导出）核心原则：你只维护三张源表。其他所有分析表都由公式从源表自动抓取数据。下个月把新数据粘贴进源表，刷新，整个模型的分析结果全部更新。第2章库存基础数据：三张源表的设计三张源表是所有分析的基础，结构要规范。2.1期初库存表记录每个月初各物料的初始库存数量。每个月一条记录。表名设为OpeningStock。列标题输入方式说明A月份手动格式2026-06B物料编码手动唯一标识，文本格式C物料名称手动D规格型号手动E单位下拉个/箱/kg/米F仓库下拉如：原料仓/成品仓G库位手动如A-01-03H期初数量手动I单价手动用于计算金额2.2入库明细表记录每一次入库操作。表名设为StockIn。列标题输入方式A日期手动，格式2026-06-03B入库单号手动C物料编码手动（扫码枪）D入库数量手动E仓库下拉F月份=TEXT(A2,"YYYY-MM")（公式自动生成）2.3出库明细表记录每一次出库操作。表名设为StockOut。列标题输入方式A日期手动B出库单号手动C物料编码手动（扫码枪）D出库数量手动E出库类型下拉：销售出库/生产领料/样品/报废F仓库下拉G月份=TEXT(A2,"YYYY-MM")2.4物料档案表（主数据）这张表是物料的“身份证”，表名设为MaterialMaster。列标题输入方式A物料编码手动，唯一B物料名称手动C规格型号手动D单位下拉E默认仓库下拉F安全库存手动设定G再订货点手动设定H标准单价手动所有源表都转为表格（Ctrl+T）并命名。这样后续SUMIFS可以直接引用表格列名，新增数据自动扩展。第3章库存汇总表：期初、入库、出库、期末自动汇总新建“库存汇总”工作表。这张表从三张源表自动汇总每个物料每月的进销存。3.1表格结构列标题公式/来源A月份2026-06（或引用参数表）B物料编码从物料档案表引用C物料名称VLOOKUP从物料档案表取D期初数量从期初库存表取（或等于上月期末）E本期入库SUMIFS从入库明细表汇总F本期出库SUMIFS从出库明细表汇总G期末数量=D2+E2-F2H单价VLOOKUP从物料档案表取I期末金额=G2*H23.2核心汇总公式本期入库（E列）：=SUMIFS(StockIn[入库数量],StockIn[物料编码],[@物料编码],StockIn[月份],[@月份])本期出库（F列）：=SUMIFS(StockOut[出库数量],StockOut[物料编码],[@物料编码],StockOut[月份],[@月份])期初数量（D列）：如果上月有期末数据，直接等于上月期末数量。如果是首月，从期初库存表取：=SUMIFS(OpeningStock[期初数量],OpeningStock[物料编码],[@物料编码],OpeningStock[月份],[@月份])3.3连锁月份处理如果要分析多个月的数据，库存汇总表需要包含每个月每个物料的记录。建议做法：将物料档案表中的所有物料复制多份（对应每个分析月份），或者在PowerQuery中创建物料×月份的笛卡尔积表，再用SUMIFS填充进销存数据。第4章库存周转率计算（月度/年化）新建“周转率分析”工作表。周转率衡量库存的流转速度，是库存管理最核心的指标。4.1关键公式月度周转次数：=本期出库金额合计/平均库存金额其中：本期出库金额合计=SUM(库存汇总[本期出库]×单价)平均库存金额=(期初库存金额+期末库存金额)/2在库存汇总表中新增辅助列，或直接在周转率分析表中汇总计算：月出库成本（按物料汇总）：=SUMIFS(StockOut[出库数量],StockOut[物料编码],物料编码,StockOut[月份],月份)*单价月度周转次数：=月出库成本/((期初数量*单价+期末数量*单价)/2)月度周转天数：=30/月度周转次数4.2年化周转率年化周转次数：=月度周转次数×12或者用过去12个月的总出库成本除以12个月的平均库存金额。4.3周转率趋势表月份出库金额平均库存金额月度周转次数月度周转天数2026-01500,0001,200,0000.4271天2026-02480,0001,150,0000.4272天2026-03620,0001,100,0000.5653天管理层看什么：月度周转天数趋势。天数在下降=库存转得更快=资金效率在提升。天数在上升=库存积压加剧，需要关注。第5章库龄计算与呆滞物料自动标识新建“库龄与呆滞分析”工作表。这是直接指导库存清理行动的工具。5.1库龄计算库龄=某批物料从入库至今未被出库消耗的天数。对于期末库存中的数量，需要追溯到它来自哪批入库——这需要“先进先出”逻辑。简化处理方式：方法一（推荐）：最后出库日期法在库存汇总表旁边新增一列“最后出库日期”：=MAXIFS(StockOut[日期],StockOut[物料编码],[@物料编码])库龄（天数）：=IF([@最后出库日期]=0,TODAY()-最早入库日期,TODAY()-[@最后出库日期])如果该物料从未出库过，库龄从最早入库日期算到今天；如果有出库记录，库龄从最后一次出库算到今天。最早入库日期：=MINIFS(StockIn[日期],StockIn[物料编码],[@物料编码])5.2呆滞物料自动标识在库龄列旁边新增“呆滞状态”列：=IFS([@库龄]>=180,"严重呆滞（>180天）",[@库龄]>=90,"呆滞（90-180天）",[@库龄]>=60,"关注（60-90天）",TRUE,"正常")5.3条件格式：让呆滞无所遁形选中“呆滞状态”列→条件格式：包含“严重呆滞”→填充深红色，字体白色加粗包含“呆滞”→填充黄色包含“关注”→填充浅蓝5.4呆滞物料汇总指标在呆滞分析表顶部用公式计算：指标公式物料总数=COUNTA(物料编码列)呆滞物料数（>90天）=COUNTIF(呆滞状态列,"*呆滞*")呆滞金额=SUMIFS(期末金额列,库龄列,">=90")呆滞金额占比=呆滞金额/SUM(期末金额列)当呆滞金额占比超过15%时，用大红字显示警告。第6章物料ABC分类（金额累计占比）新建“ABC分类”工作表。ABC分类基于帕累托法则：少数物料占用了大部分库存资金。6.1数据准备从库存汇总表提取：物料编码、物料名称、期末金额。6.2分类步骤第1步：按期末金额降序排列。金额最高的物料排第一。第2步：计算累计金额占比。列标题公式A物料编码B物料名称C期末金额D金额占比=C2/SUM(C:C)E累计占比=SUM(D$2:D2)——注意引用范围，向下拖动时累计范围扩展FABC分类见下方公式第3步：ABC分类公式。=IF(E2<=70%,"A",IF(E2<=90%,"B","C"))A类（累计占比前70%）：金额高，品类少。需要重点管理、每日关注。B类（累计占比70%-90%）：金额中等，常规管理。C类（累计占比90%-100%）：金额低，品类多。简化管理，可以适当放大安全库存、减少订货频次。6.3条件格式美化A类行填充浅红色，B类浅黄色，C类浅绿色。ABC三个类别的物料一目了然。第7章安全库存与再订货点建议新建“库存预警”工作表。7.1关键参数参数说明设定方式安全库存防止断货的缓冲量物料档案表中手动设定，或按公式计算再订货点低于此数量触发采购=安全库存+日均出库量×采购提前期最高库存防止过量采购=再订货点+经济订货批量安全库存的简易计算公式：=日均出库量×最大波动天数其中：日均出库量=过去90天总出库量/90最大波动天数=根据历史数据中出货量的波动范围设定，一般取5-10天7.2红绿灯预警在库存预警表中新增“预警状态”列：=IFS([@期末数量]<=[@安全库存],"🔴短缺",[@期末数量]<=[@再订货点],"🟡低库存",[@期末数量]>=[@最高库存],"🔵超储",TRUE,"🟢正常")条件格式：包含“短缺”时整行红色，包含“低库存”时整行黄色，包含“超储”时整行蓝色。7.3自动生成补货建议筛选预警状态为“短缺”或“低库存”的记录→新增列“建议补货量”：=MAX(0,[@再订货点]-[@期末数量]+[@安全库存])这张表可以直接导出给采购部门，作为采购计划的参考依据。第8章超储与短缺红绿灯预警除了基于安全库存的预警，还需要关注超储——库存过高同样占用资金。8.1超储判断标准判断维度条件预警按周转天数库存可售天数>180天🔵超储按金额上限单一物料库存金额>月均出库金额×6🔵超储按库龄库龄>180天且库存>0🔴严重呆滞8.2综合预警表整合第5章呆滞分析和第7章安全库存预警，生成一张综合预警清单：物料编码物料名称期末库存库存天数库龄呆滞状态安全库存综合预警M001电阻50000250180呆滞10000🔴超储+呆滞M002电容500310正常2000🔴短缺综合预警公式：=IF(AND(呆滞状态<>"正常",库存天数>180),"🔴超储+呆滞",

IF(库存天数>180,"🔵超储",

IF(期末库存<=安全库存,"🔴短缺",

IF(期末库存<=再订货点,"🟡低库存","🟢正常"))))第9章动态图表展示趋势新建“仪表盘”工作表，用于管理层一页看懂库存健康度。9.1关键指标卡片（顶部）指标公式库存总金额=SUM(库存汇总[期末金额])月度周转天数引用第4章呆滞金额占比引用第5章短缺物料数=COUNTIF(预警状态列,"*短缺*")超储物料数=COUNTIF(预警状态列,"*超储*")9.2图表设计图表一：月度周转天数趋势图（折线图）X轴：月份Y轴：周转天数添加一条参考线（目标周转天数），用辅助列实现图表二：ABC分类占比图（饼图或环形图）数据：A类金额、B类金额、C类金额环形图更直观，内环显示占比数字图表三：呆滞金额月度趋势图（面积图或柱状图）X轴：月份Y轴：呆滞金额用面积图的累积感展示呆滞问题的严重程度变化图表四：库存结构树状图（Treemap）按物料类别或仓库的面积占比展示库存结构一眼看出哪个仓库、哪类物料占用最多资金9.3按月/按仓库切片器基于库存汇总表插入切片器：月份切片器：切换查看不同月份的库存数据仓库切片器：筛选不同仓库的库存选中切片器后，仪表盘上所有图表和指标同步刷新。第10章生成呆滞处理建议清单新建“呆滞处理建议”工作表。这是从“发现问题”到“给出方案”的关键一步。10.1呆滞物料分类处理呆滞类型判定条件建议处理方式可退回供应商采购协议中有退货条款，且库龄<1年联系供应商办理退货或换货可折价销售通用规格，市场仍有需求发起促销，打折清理可改制利用通过简单加工可替代其他物料联系生产部门评估改制成本报废处理技术淘汰/过期/锈蚀提请报废审批流程10.2处理建议清单字段列来源物料编码库龄与呆滞分析表物料名称库龄与呆滞分析表当前库存库存汇总表库存金额库存汇总表库龄（天）库龄与呆滞分析表呆滞级别严重呆滞/呆滞/关注最后出库日期库龄与呆滞分析表ABC分类ABC分类表建议处理方式根据规则公式判断或手动选择责任人手动分配预期处理完成日手动设定10.3呆滞处理跟踪每个月更新一次呆滞处理建议清单，对比上月清单：哪些物料已经被处理掉了（从清单中消失）哪些物料库龄在继续恶化（如从90天变成120天）新增了哪些呆滞物料给管理层看的总结指标：本月新增呆滞金额、本月处理呆滞金额、净增呆滞金额。第11章导出给采购与销售部门11.1导出给采购：补货建议表从第7-8章的预警表筛选出“短缺”和“低库存”的物料，生成补货建议表：物料编码物料名称当前库存安全库存再订货点建议补货量建议下单日期M002电容500200030002500立