用Excel制作商品销售清单

用用 Excel 制作商品销售清单制作商品销售清单 Excel 高级应用高级应用 作者作者 许兴权许兴权 也许有人认为也许有人认为 Excel 就是做电子表格罢了 没什么太强大的功能 读者如果也是这种看法的话 就是做电子表格罢了 没什么太强大的功能 读者如果也是这种看法的话 那就请您试试本附件中的那就请您试试本附件中的 excel 文档吧 文档吧 图图 1 是附件中是附件中 Excel 文档刚打开时的状态 文档刚打开时的状态 图图 1 图图 2 是销售三条商品记录所开具的销售单 在销售清单数据录入过程中 是销售三条商品记录所开具的销售单 在销售清单数据录入过程中 品名品名 是由是由 大类大类 小类小类 品名品名 三级下拉列表选定的 这有利于商店对商品名称的规范化管理 销售清单中的三级下拉列表选定的 这有利于商店对商品名称的规范化管理 销售清单中的 数量数量 单价单价 规格规格 1 规格规格 2 是手动输入的数据 是每条销售记录是手动输入的数据 是每条销售记录 金额金额 的计算依据 的计算依据 其中其中 规格规格 1 与与 规格规格 2 可以放空 当可以放空 当 规格规格 1 规格规格 2 为空时 将自动不参与为空时 将自动不参与 金额金额 计计 算 算 单位单位 规格规格 1 单位单位 规格规格 2 单位单位 不但可以使用下拉列表框的形式选择 而且可以直接不但可以使用下拉列表框的形式选择 而且可以直接 使用手工输入 使用手工输入 图图 2 图图 2 中有三条销售记录 第一条记录直接按盒销售 金额由两个数据相乘获得 为中有三条销售记录 第一条记录直接按盒销售 金额由两个数据相乘获得 为 15 2 8 42 元 第二条记录按件销售 每件中有元 第二条记录按件销售 每件中有 48 盒的牛奶 因此金额由三个数据相乘获得 为盒的牛奶 因此金额由三个数据相乘获得 为 8 48 2 8 1075 20 元 而第三条记录则按每件中有元 而第三条记录则按每件中有 6 包 每包中有包 每包中有 8 盒的方式销售 因此金额由四盒的方式销售 因此金额由四 个数据相乘获得 为个数据相乘获得 为 8 6 8 2 8 1075 2 元 本表格支持对商品按不同种方式销售 销售金额将根据元 本表格支持对商品按不同种方式销售 销售金额将根据 输入的数据自动计算 这样的设计基本能满足大部分的销售情况的要求 输入的数据自动计算 这样的设计基本能满足大部分的销售情况的要求 如果您对它感兴趣 您可以试着用它 并将它存储为如果您对它感兴趣 您可以试着用它 并将它存储为 Excel 模板 方便调用 或许将会改变您模板 方便调用 或许将会改变您 的看法哦 可您要知道 做出这样的表格模板仅用到的看法哦 可您要知道 做出这样的表格模板仅用到 Excel 的常用功能和不多的函数而已 的常用功能和不多的函数而已 VBA 还还 没派上用场呢 没派上用场呢 本文仅介绍该文档的制作过程 方便读者能跟据自己的要求修改文档 能把文档转变为自己能本文仅介绍该文档的制作过程 方便读者能跟据自己的要求修改文档 能把文档转变为自己能 用的东西 只要有时间用的东西 只要有时间 包学包会包学包会 哦 哦 下面将按步骤介绍文档的设计制作过程 下面将按步骤介绍文档的设计制作过程 步骤一步骤一 打开打开 Excel 把 把 sheet1 改名为改名为 销售单销售单 把 把 sheet2 改名为改名为 品名品名 删除 删除 sheet3 在 在 品名品名 工作表中输入商品的大类 小类 和品名 如图工作表中输入商品的大类 小类 和品名 如图 3 和图和图 4 所示 本文的实例大类设计为所示 本文的实例大类设计为 食品食品 百货百货 五金五金 三种 食品有三种 食品有 蛋糕蛋糕 牛奶牛奶 大米大米 三种小类 百货设计有三种小类 百货设计有 丝织品丝织品 洗洗 涤品涤品 纸类纸类 三种小类 五金设计有三种小类 五金设计有 刀具刀具 螺丝螺丝 门锁门锁 三种小类 读者在设计自己的表三种小类 读者在设计自己的表 格时 可以自己定义大类 小类 本文实例中各小类中都包含着一些具体的商品 也就是具体的品格时 可以自己定义大类 小类 本文实例中各小类中都包含着一些具体的商品 也就是具体的品 名 品名就是所销售的各种商品的规范化的名称 名 品名就是所销售的各种商品的规范化的名称 图图 3 图图 4 选定如图选定如图 5 所示内容 单击菜单所示内容 单击菜单 插入插入 名称名称 指定指定 在弹出的对话框中按如图 在弹出的对话框中按如图 6 设设 置 定义置 定义 食品食品 百货百货 五金五金 名称 为备后期引用 定义了名称 为备后期引用 定义了 食品食品 名称后 今后在本工作名称后 今后在本工作 薄引用的时候 就指向薄引用的时候 就指向 蛋糕蛋糕 牛奶牛奶 大米大米 同理 百货名称被引用时 就指向 同理 百货名称被引用时 就指向 丝织品丝织品 洗涤品洗涤品 纸类纸类 五金名称被引用时 就指向 五金名称被引用时 就指向 刀具刀具 螺丝螺丝 门锁门锁 图图 5 图图 6 选定如图选定如图 7 所示内容 单击菜单所示内容 单击菜单 插入插入 名称名称 指定指定 在弹出的对话框中按如图 在弹出的对话框中按如图 8 设设 置 就定义了置 就定义了 蛋糕蛋糕 牛奶牛奶 大米大米 丝织品丝织品 洗涤品洗涤品 纸类纸类 刀具刀具 螺丝螺丝 和和 门门 锁锁 名称 方便后期对名称的引用 名称 方便后期对名称的引用 图图 7 图图 8 通过上述的名称设定 本工作薄已有如图通过上述的名称设定 本工作薄已有如图 9 中所示的名称 单击列表中的名称 再单击引用位中所示的名称 单击列表中的名称 再单击引用位 置 可以适当修改所定义名称所引用的位置置 可以适当修改所定义名称所引用的位置 数据 当然不改也可以 下拉框会出现空行等现象数据 当然不改也可以 下拉框会出现空行等现象 图图 9 这样也就为这样也就为 销售单销售单 工表作的工表作的 大类大类 小类小类 品名品名 的下拉列表框做好了准备工作 的下拉列表框做好了准备工作 步骤二步骤二 编辑编辑 销售单销售单 工作表 输入如图工作表 输入如图 10 的数据 按图的数据 按图 10 设置表格的格式 选定隐藏不使用的行设置表格的格式 选定隐藏不使用的行 和列 把和列 把 销售单销售单 工作表编辑为如图工作表编辑为如图 10 的效果 的效果 图图 10 在在 销售单销售单 工作表中选定工作表中选定 A4 A13 单击 单击 数据数据 菜单菜单 有效性有效性 分别按图 分别按图 11 12 设置填设置填 写写 设置设置 出错警告出错警告 选项卡的相应项 其中选项卡的相应项 其中 设置设置 选项卡中选项卡中 来源来源 填写的内容 食品填写的内容 食品 百货百货 五金 五金 各大类名称在该位置列表 并用半角的 各大类名称在该位置列表 并用半角的 隔开 隔开 图图 11 图图 12 选定选定 B4 B13 单击 单击 数据数据 菜单菜单 有效性有效性 分别按图 分别按图 13 14 设置填写设置填写 设置设置 出错警告出错警告 选项卡的相应项 其中选项卡的相应项 其中 设置设置 选项卡中选项卡中 来源来源 填写的内容填写的内容 INDIRECT A4 INDIRECT A4 表示该单元格下拉列表的内容来自以左边所选大类名称的引用表示该单元格下拉列表的内容来自以左边所选大类名称的引用 这个已经在这个已经在 品名品名 工作表中定义过了 这里直接引用工作表中定义过了 这里直接引用 图图 13 图图 14 上述步骤设定好了 单击上述步骤设定好了 单击 确定确定 命令按钮时 会弹出警告框如图命令按钮时 会弹出警告框如图 15 所示 这是因为左边单元所示 这是因为左边单元 格的大类为空 没有选定内容所导致的 没有关系 可以忽略 直接选择格的大类为空 没有选定内容所导致的 没有关系 可以忽略 直接选择 是是 就可以 千万不要就可以 千万不要 选择选择 否否 否则就做不出 否则就做不出 小类小类 的下拉列表 的下拉列表 图图 15 一样的方法 先选定一样的方法 先选定 C4 C13 再按如图 再按如图 16 17 设置品名下拉列表 设置品名下拉列表 图图 16 图图 17 数量数量 的数据有效性如图的数据有效性如图 18 19 所示所示 图图 18 图图 19 单位单位 规格规格 1 单位单位 规格规格 2 单位单位 的数据有效性如图的数据有效性如图 20 21 所示 所示 图图 20 图图 21 数量数量 单价单价 规格规格 1 规格规格 2 根据实际情况设置如图 根据实际情况设置如图 22 23 所示 所示 图图 22 图图 23 通过以上的设置 销售清单的设置就差通过以上的设置 销售清单的设置就差 金额金额 和和 合计合计 的还没计算 的还没计算 步骤三步骤三 设置设置 K4 单元格单元格 金额金额 的运算公式 在金额的计算中的运算公式 在金额的计算中 G4 规格规格 1 I4 规格规格 2 参加计算时 先判参加计算时 先判 断它是否为空 若为空则默认以断它是否为空 若为空则默认以 1 参与乘法计算 非空则以实际数据参与计算 如果整个乘法公式参与乘法计算 非空则以实际数据参与计算 如果整个乘法公式 的积为的积为 0 则 则 K4 为空 否则显示计算结果 所以在公式中使用二层的为空 否则显示计算结果 所以在公式中使用二层的 if 嵌套 外层主要作用是修嵌套 外层主要作用是修 饰饰 K4 单元格 以防止计算结果为单元格 以防止计算结果为 0 时 在时 在 K4 单元格中显示单元格中显示 0 内层的 内层的 if 的作用是判断的作用是判断 规格规格 1 规格规格 2 是否为空 是否为空 K4 的计算表达式 的计算表达式 IF D4 F4 IF G4 1 G4 IF I4 1 I4 0 D4 F4 IF G4 1 G4 IF I4 1 I4 K4 编辑好了后 拖动填充句柄到编辑好了后 拖动填充句柄到 K13 完成 完成 金额金额 的计算 并把的计算 并把 金额金额 列定义为列定义为 2 位小数位小数 右对齐的显示方式 右对齐的显示方式 合计合计 单元格的内容是单元格的内容是 IF SUM K4 K13 0 SUM K4 K13 使用 使用 if 的作用是当合计为的作用是当合计为 0 时 隐藏时 隐藏 0 步骤四步骤四 选定选定 A4 J13 和和 J2 设置 设置 单元格格式单元格格式 中的中的 保护保护 选项卡 去掉选项卡 去掉 锁定锁定 选项 如图选项 如图 24 所所 示 示 图图 24 选择选择 工具工具 菜单菜单 保护工作表保护工作表 密码 密码 123 保护 保护 销售单销售单 工作表工作表 单击单击 品名品名 工作表 选择工作表 选择 工具工具 菜单菜单 保护工作表保护工作表 密码 密码 123 选择选择 工具工具 菜单菜单 保护工作薄保护工作薄 密码 密码 456 保护整个工作薄的格式 保护整个工作薄的格式 选择选择 文件文件 菜单的菜单的 另存为另存为 选择保存位置 输入文件名 选择文档类型 完成保存 至此 选择保存位置 输入文件名 选择文档类型 完成保存 至此 大功告成 大功告成 小结 上述小结 上述 Excel 文档的制作的主要重点是名称的定义 三级下拉列表的设计 数据有效性的文档的制作的主要重点是名称的定义 三级下拉列表的设计 数据有效性的 设计 设计 indirect 与与 if 函数的应用 难点是名称引用的理解和数据有效性的灵活运用 通过上文的学函数的应用 难点是名称引用的理解和数据有效性