Excel在财务中的应用 课件 项目五 进销存管理

项目五进销存业务管理任务一

进销存业务建账目录01

产品明细代码编写

02

产品数据规范化处理任务一

进销存业务建账

03

产品代码自动填充●填充柄

(FillHandle)●函

数

：TEXT,&,VLOOKUP,INDIRECT●数据验证

(DataValidation)●产品明细代码(唯一标识)●规范化数据(准确性)●自动填充(高效率)●基础文件：01货品代码表.xlsx●

关键原则：数据无重复、无遗漏前期准备

(Preparation)核心目标

(Objectives)核心工具

(Toolkit)销售(销)采购(进)入库(存)进销存建账核心认知(Consolas)拼音首字母(Consolas)3位数字(Category

Code)(Product

Code)(Sequence)Formula

Logic产品代码类别代码明细序号类别代码焙烤食品PK谷类GL肉罐头RGT水果和蔬菜罐头GSGT汤T调味品TWP饮料YL注意：以产品首字母作为产品代码表5-1产品类别与代码一、产品明细代码编写—代码逻辑D2

十

"000"

001规范序号格式使用填充柄生成连续序号1,2,3...Step

2:

格式转换Step

1:

填充序号原数值

强制3位文本格式=TEXT(D2,"000")ConsolasDCB品質控制(质量核查)

唯一性

格式统一

若出现####,请调整列宽PK001001(Cell

G2)(Cell

E2)(Cell

B2)PK生成唯一产品代码

(Generating

Unique

ProductCodes)为什么需要规范化?防手动录入错误(“培烤”vs“焙烤”)

。

实现“选填而非手填”二、产品数据规范化处理创建自定义名称选中‘类别’列→Alt+D+L→设置来源为'=类别'数据验证结果：下拉列表显示已定义名称的类别水果和蔬菜罐头汤焙烤食品谷类调味品饮料类别肉罐头设置一级数据验证●'INDIRECT`:

将文本转换为引用公式解析(FormulaAnatomy)数据验证(DataValidation)Visual

Linkage

Diagram饮料

ControlsCategory可乐Name茶设置二级联动验证三、产品代码自动填充—构建辅助列

类别

名称

唯一关键词VLOOKUP需要唯一查找值

(Unique

LookupValue)Corporate

Technical

Workshop任务二

采购入库成本表目录01

数据验证设置02

条件格式设置任务二

采购入库成本表消除浪费|降低成本|提升效率通过数据集成优化研发、采购、物流全流程。核心目标

(Roadmap)配套文件：'02采购入库统计表.xlsx',

'销售统计表.xlsx'

党的二十届三中全会强调：“高质量发展是首要任务”多维分析

(Intelligence)熟练运用数据透视表/图进行多维度采购分析掌握采购入库成本表的规范制作与数据验证异常监控

(Control)数据规范

(Defense)数据清洗

(Hygiene)学会用条件格式标识异常成本数据完成销售数据清洗与基础整理战略背景政策导向企业价值建立数据防火墙：基础数据验证菜单路径：数据(Data)→

数据验证(Data

Validation)防止人工录入错误，确保源头数据准确●

允许：序列来源：=公司名称操作对象：“公司名称”与“类别”列操作指南核心设置：

或=类别智能联动逻辑：INDIRECT

函数应用二级下拉菜单：

“名称”列表随“类别”变化=VLOOKUP(E2,

产品!$C$2:$D$46

,

2,自动化数据集成：辅助列与代码查询查找值(辅助列)

返回第2列(代码)构建辅助列

(Unique

Key)

VLOOKUP

自动获取代码数据源区域

精确匹配谷类

葡萄干辅助列谷类葡萄干类别名称"谷类"+"葡萄干"="谷类葡萄干"C2

D2=C2&D2E2=VLOOKUP(F2,产品!$D$2:$H$46,3,0)成本数据自动获取与一致性校验·

利用产品代码(Unique

Code)

确保价格对应准确。结果：下拉菜单选择录入，公式自动计算，确保数据一致性。:获取参考成本(ReferenceCost)1单位成本参考成本验证27.608.000.05322.8024.000.05430.4032.000.0558.608.00-0.07634.6032.00-0.08737.8036.00-0.05829.4028.00-0.05(参考成本-

单位成本)

÷单位成本>

4%业务价值：

快速定位成本异常项，辅助成本控制与审计。管理理念：例外管理异常监控逻辑：定义风险阈值=(I2-H2)/H2步骤1:添加验证列

(J

列)成本变动率阈值新建规则最终效果业务价值：快速定位成本异常项，辅助成本控制与审计。设置格式视觉化预警：条件格式实操Thisslidevisually

instructs

howtoturnthemath

intoa

redflag.TechnicalSwiss

ModernWorkshop8

质(B).SSTN任务三

采购分析表采购分析引擎：数据透视表构建将海量明细转化为商业智能

维度一：供应商与产品采购金额分析从宏观总览到微观明细(Drill-down)维度二：采购资金的时间分布趋势维度三：单位成本波动监控关键设置：从“求和”到“平均值”单位成本波动趋势图任务四

销售数据和销售分析目录01

销售表制作02

单位成本计算任务四

销售数据和销售分析03

毛利率计算04

毛利率分析销售表制作销售管理台账毛利分析·记录订单信息·支撑成本/毛利分析·

衔接库存管理数据清洗

→参数设置

→超级表转换核心作用制作流程涉及工作表：销售管理台账、参数原始订单库存表·1.清除格式：

选中全表

(Ctrl+A

)

→清除格式

(Alt+E+A+F

)·2.调整布局：自动调整行高/

列宽·3.冻结窗格：选中C2→

视图

→冻结窗格·4.自定义排序：

主要关键字[订单日期]

,次要关键字[公司名称]数据清洗(格式与视图)销售表制作：数据清洗(内容规范化)·

日期列设为[日期格式]·金额列设为[货币格式]提示：通配符*代表任意多个字符5.

批量替换符号7.设置数据类型6.规范公司名称销售表制作：新建参数表销售表制作：删除重复值销售表制作：数据验证超级表优势·

自动扩展：新增数据自动继承格式·筛选便捷：自带筛选按钮·格式统一：视觉清晰销售表制作：转换超级表¥产品采购入库

数据透视表

平均单位成本单位成本计算——数据透视表实操毛利=

(单价-单位成本)

×数量毛利率=毛利

÷销售收入毛利率计算保留两位小数查找匹配成本处理除零错误VLOOKUPIFERRORROUNDA

O

→0

0是数值。两者含义不同""是文本无内关键提示：函数嵌套与数据精度防止显示#DIV/O!

错误。双引号""代表空文本。单元格格式只改变'看'的样子，ROUND函数改变'算'的数值。将

VLOOKUP

结果直接作为ROUND的参数，简化步骤。函数嵌套

(Nesting)显示vs

实际空值vs

0IFERROR

毛利率分析——维度与方法识别高盈利(明星产品)与低盈利(问题产品),优化策略。方法：

数据透视表(PivotTable)2.

产品维度

(Product

Level)1.单订单维度(Order

Level)方法：排序(Sorting)两个分析维度分析目的=SUMIFS(采购入库!Quantity,采购入库!Code,CurrentRow!Code)期初库存

本期采购

本期销售

期末库存

库存表制作—

进销存逻辑在“进销存数据”表中，使用SUMIFS

汇总采购数量，再利用加减逻辑计算期末数。SUMIFS

函数解析采购表的[产品代码]列求和区域

←当前行的[产品代码]采购表的[数量]列操作步骤条件区域条件●

PivotTable计算平均成本●

表格形式布局●

VLOOKUP

精确查找●ROUND+IFERROR

嵌套●

库存守恒公式

Q&A●

快捷键清洗

(Ctrl+E)●

超级表与参数验证课程总结：知识点回顾谜速：成本透视●

SUMIFS多条件求和进销存逻辑销售表规范实操答疑与互动核心函数关键连接点：产品代码

(Product

Code)—

唯一识别ID进销存业务核心逻辑：三大表格的联动整合期初、采购、销售数据，反映库存结余产品库存表记录数量、单价、金额记录数量、单价、金额提供本期采购数据提供本