论文部分内容阅读
店铺营业管理、家庭个人理财……利用Excel 2003便可制作出符合自己需要的管理数据系统，任意定制，快捷方便！
★创建超市管理工作簿，设计商品目录表
新建一个Excel工作簿。由于通常情况下一个超市的商品管理涉及商品目录、日常结算、库存管理以及利润测算等问题，因此这个工作簿采用了4个工作表，在商品目录的A3:D30单元格区域输入超市商品的编号、名称、单位以及销售价格等信息（见图1）。
可以采用下拉列表框来输入商品名称。首先对商品目录工作表中的商品编号列进行定义名称，执行“插入→名称→定义”命令，在名称文本框中输入“商品编号”，然后在下面的“引用位置”框中选择商品编号所在的单元格区域A3:A30即可，确定（见图2）。
图1
★设计日常结算表，实现高效结算
超市中最迫切需要解决的问题就是日常结算问题。我们可以充分利用Excel强大的函数功能来完美解决。切换到日常结算工作表，在第一行中，分别输入超市日常结算经常使用的项目，然后作以下处理。
第1步 可通过刚才设置的下拉框来选择商品名称。选择商品编号所在的A5单元格，执行“数据→有效性”命令，从“设置”选项卡的“有效性条件”下面的“允许”列表框中选择“序列”选项，在“来源”中输入公式“=商品编号”，然后选中“提供下拉箭头”复选项，确定（见图3）。
图2
第2步 在“产品名称和规格”下面的B5单元格内输入公式“=IF(ISERROR(VLOOKUP(A5,商品目录!A:D,2,FALSE)),"",VLOOKUP(A5,商品目录!A:D,2,FALSE))”。
VLOOKUP函数的作用是从“商品目录”工作表的A:D列中，查找与A5单元格相同的值，并返回到“商品目录”工作表中第2列中的值，即产品名称和规格，而IF、ISERROR函数则能够防止在B5单元格中出现错误值的作用（图4）。“单位”下的D5单元格和“售价”下的F5单元格输入公式相似。
图3
第3步 客户在购买商品时可能选择多种商品，而且存在找零问题，因此在“应收金额”下的J5单元格内输入公式“=IF(I5="","",SUMIF(C:C,C5,H:H))”，计算在C列中同一个客户的应收金额之和，然后在“应找零”下的K5单元格内输入公式“=IF(I5="","",I5-J5)”，用来计算应找回客户的金额。编辑完毕后，选择A5:AK单元格区域，然后拖动填充柄，将这些公式复制到下面的单元格区域内。
使用这张“日常结算”工作表时，只需要从A列的下拉列表框中，选择一种商品编号（如A003），然后输入客户编号、销售数量、折扣比例、客户付款，就会自动将商品名称、规格、单位、售价填入表中，快速准确无误得计算出实际销售金额、应收金额、找零了（见图5）。
图4
编辑库存利润表，充分掌握经营状况
超市的经营涉及方方面面，日常结算问题基本解决了，而商品库存控制、利润测算问题仍然无法自动计算，怎么办？
切换到“商品库存以及利润”工作表，将商品目录中的商品编号以及名称等复制过来，然后在列名行中，输入期初库存、本期采购、本期销售、期末库存、本期毛利、毛利率等项目。
图5
“本期销售-数量”下面的H5单元格内输入公式“=SUMIF(日常结算!$A$5:$A$24,B5,日常结算!$E$5:$E$24)”，“本期销售-金额”下面的H5单元格内输入公式“=SUMIF(日常结算!$A$5:$A$24,B5,日常结算!$H$5:$H$24)”，使用此公式来从日常结算工作表获得相关商品的销售数据。
为了计算期末库存，在“期末库存”下面的J5单元格内输入公式“=D5+F5-H5”，库存商品价格采用加权法计算，即在K5中输入公式“=(G5+E5)/(D5+F5)”，而库存金额为“=J5*K5”。计算本期毛利时，在M5单元格中输入公式“=I5-H5*(E5+G5)/(D5+F5)”，毛利率则使用公式“=IF(ISERROR(M5/I5),"",M5/I5)”即可解决（见图6）。
图6
识一点通：
这个实例综合运用了多种Excel技巧。比如制作“下拉列表”便于商品名称的输入，使用条件函数IF、逻辑函数VLOOKUP等，进行简单嵌套，方便地实现了销售中的“自动”显示结果。还使用了SUMIF等函数进行分析，通过条件格式和图表，巧妙直观地把数据展示出来，实时掌握盈亏动态，轻松做出经营决策。
图7
图8
三：搭建个人银行账户管理系统
现在很多朋友都有多个银行账户，利用前面的知识要点，就可以非常轻松地设计属于自己的银行管理系统，使得家庭理财水平提高到一个新的高度！
第1步 在Excel中新建一个名为“银行账户管理明细表”的工作簿，在其中创建3个工作表，分别为“银行账户余额表”、“交易明细表”、“基本资料”，其中在“基本资料”表中输入银行名称/账户号码以及交易摘要内容，同时按照前面的方法，将银行名称/账户号码、交易摘要内容所在的单元格区域分别定义为“银行”、“摘要”（见图8）。
图9
2步 在“交易明细表”中设计一个表格，包括交易日期、交易银行账户名称、摘要、借贷方金额等内容，分别选择交易银行账户名称、摘要所在的单元格区域，通过数据有效性工具，将其数据来源设置为“银行”、“摘要”，从而实行下拉框方式快速输入这些信息（见图9）。
第3步 切换到“银行账户余额表”，编辑一个“2007年度银行账户余额表”，包括银行账户名称、期初余额、本期借方累计、本期贷方累计、期末余额等内容，其中在“本期借方累计”下面的D5单元格中输入公式“=SUMIF(交易明细表!$C$4:$C$19,C5,交易明细表!$E$4:$E$19)”，“本期贷方累计”下面的E5单元格中输入公式“=SUMIF(交易明细表!$C$4:$C$19,C5,交易明细表!$F$4:$F$19)”，“期末余额”下面的F5单元格中输入公式“=C5+D5-E5”，并将这些公式复制到下面对应的单元格中，这样当我们在“交易明细表”输入日常银行交易明细时，就会自动汇总到“银行账户余额表”表中对应的银行账号中，从而及时掌握自己的个人资金状况了（见图10）。
火速链接：2006年第24期《条件函数三兄弟 为运算加条件解决实际问题》、2007年第1期《众里寻她只一步——数据查找与引用函数种种》、第2期《让数据活起来——图表创建经验谈》、第3期《彩色的数据会说话——Excel 2007“条件格式”最新实用技巧大放送》。
★创建超市管理工作簿，设计商品目录表
新建一个Excel工作簿。由于通常情况下一个超市的商品管理涉及商品目录、日常结算、库存管理以及利润测算等问题，因此这个工作簿采用了4个工作表，在商品目录的A3:D30单元格区域输入超市商品的编号、名称、单位以及销售价格等信息（见图1）。
可以采用下拉列表框来输入商品名称。首先对商品目录工作表中的商品编号列进行定义名称，执行“插入→名称→定义”命令，在名称文本框中输入“商品编号”，然后在下面的“引用位置”框中选择商品编号所在的单元格区域A3:A30即可，确定（见图2）。
图1
★设计日常结算表，实现高效结算
超市中最迫切需要解决的问题就是日常结算问题。我们可以充分利用Excel强大的函数功能来完美解决。切换到日常结算工作表，在第一行中，分别输入超市日常结算经常使用的项目，然后作以下处理。
第1步 可通过刚才设置的下拉框来选择商品名称。选择商品编号所在的A5单元格，执行“数据→有效性”命令，从“设置”选项卡的“有效性条件”下面的“允许”列表框中选择“序列”选项，在“来源”中输入公式“=商品编号”，然后选中“提供下拉箭头”复选项，确定（见图3）。
图2
第2步 在“产品名称和规格”下面的B5单元格内输入公式“=IF(ISERROR(VLOOKUP(A5,商品目录!A:D,2,FALSE)),"",VLOOKUP(A5,商品目录!A:D,2,FALSE))”。
VLOOKUP函数的作用是从“商品目录”工作表的A:D列中，查找与A5单元格相同的值，并返回到“商品目录”工作表中第2列中的值，即产品名称和规格，而IF、ISERROR函数则能够防止在B5单元格中出现错误值的作用（图4）。“单位”下的D5单元格和“售价”下的F5单元格输入公式相似。
图3
第3步 客户在购买商品时可能选择多种商品，而且存在找零问题，因此在“应收金额”下的J5单元格内输入公式“=IF(I5="","",SUMIF(C:C,C5,H:H))”，计算在C列中同一个客户的应收金额之和，然后在“应找零”下的K5单元格内输入公式“=IF(I5="","",I5-J5)”，用来计算应找回客户的金额。编辑完毕后，选择A5:AK单元格区域，然后拖动填充柄，将这些公式复制到下面的单元格区域内。
使用这张“日常结算”工作表时，只需要从A列的下拉列表框中，选择一种商品编号（如A003），然后输入客户编号、销售数量、折扣比例、客户付款，就会自动将商品名称、规格、单位、售价填入表中，快速准确无误得计算出实际销售金额、应收金额、找零了（见图5）。
图4
编辑库存利润表，充分掌握经营状况
超市的经营涉及方方面面，日常结算问题基本解决了，而商品库存控制、利润测算问题仍然无法自动计算，怎么办？
切换到“商品库存以及利润”工作表，将商品目录中的商品编号以及名称等复制过来，然后在列名行中，输入期初库存、本期采购、本期销售、期末库存、本期毛利、毛利率等项目。
图5
“本期销售-数量”下面的H5单元格内输入公式“=SUMIF(日常结算!$A$5:$A$24,B5,日常结算!$E$5:$E$24)”，“本期销售-金额”下面的H5单元格内输入公式“=SUMIF(日常结算!$A$5:$A$24,B5,日常结算!$H$5:$H$24)”，使用此公式来从日常结算工作表获得相关商品的销售数据。
为了计算期末库存，在“期末库存”下面的J5单元格内输入公式“=D5+F5-H5”，库存商品价格采用加权法计算，即在K5中输入公式“=(G5+E5)/(D5+F5)”，而库存金额为“=J5*K5”。计算本期毛利时，在M5单元格中输入公式“=I5-H5*(E5+G5)/(D5+F5)”，毛利率则使用公式“=IF(ISERROR(M5/I5),"",M5/I5)”即可解决（见图6）。
图6
识一点通：
这个实例综合运用了多种Excel技巧。比如制作“下拉列表”便于商品名称的输入，使用条件函数IF、逻辑函数VLOOKUP等，进行简单嵌套，方便地实现了销售中的“自动”显示结果。还使用了SUMIF等函数进行分析，通过条件格式和图表，巧妙直观地把数据展示出来，实时掌握盈亏动态，轻松做出经营决策。
图7
图8
三：搭建个人银行账户管理系统
现在很多朋友都有多个银行账户，利用前面的知识要点，就可以非常轻松地设计属于自己的银行管理系统，使得家庭理财水平提高到一个新的高度！
第1步 在Excel中新建一个名为“银行账户管理明细表”的工作簿，在其中创建3个工作表，分别为“银行账户余额表”、“交易明细表”、“基本资料”，其中在“基本资料”表中输入银行名称/账户号码以及交易摘要内容，同时按照前面的方法，将银行名称/账户号码、交易摘要内容所在的单元格区域分别定义为“银行”、“摘要”（见图8）。
图9
2步 在“交易明细表”中设计一个表格，包括交易日期、交易银行账户名称、摘要、借贷方金额等内容，分别选择交易银行账户名称、摘要所在的单元格区域，通过数据有效性工具，将其数据来源设置为“银行”、“摘要”，从而实行下拉框方式快速输入这些信息（见图9）。
第3步 切换到“银行账户余额表”，编辑一个“2007年度银行账户余额表”，包括银行账户名称、期初余额、本期借方累计、本期贷方累计、期末余额等内容，其中在“本期借方累计”下面的D5单元格中输入公式“=SUMIF(交易明细表!$C$4:$C$19,C5,交易明细表!$E$4:$E$19)”，“本期贷方累计”下面的E5单元格中输入公式“=SUMIF(交易明细表!$C$4:$C$19,C5,交易明细表!$F$4:$F$19)”，“期末余额”下面的F5单元格中输入公式“=C5+D5-E5”，并将这些公式复制到下面对应的单元格中，这样当我们在“交易明细表”输入日常银行交易明细时，就会自动汇总到“银行账户余额表”表中对应的银行账号中，从而及时掌握自己的个人资金状况了（见图10）。
火速链接：2006年第24期《条件函数三兄弟 为运算加条件解决实际问题》、2007年第1期《众里寻她只一步——数据查找与引用函数种种》、第2期《让数据活起来——图表创建经验谈》、第3期《彩色的数据会说话——Excel 2007“条件格式”最新实用技巧大放送》。