1.Excel表间取数
企业全面预算的最大特点是以销定产、以产定耗、以耗定购,所以生产预算必须根据销售预算编制。Excel提供的表间取数方法非常适合这种数据之间的链接计算,当数据源变动时,引用这些数据的其他工作表也会随之变动,不必逐一修改重算。引用表间数据可用单元名称引用,也可用单元坐标引用,即甲工作表中需引用乙工作表的数据时,应在甲工作表的单元格键入“=乙工作表!XX”,其中表名后要加英文(半角)的“!”,XX表示要引用的单元格或单元区域。
2.保护Excel工作表
编制的预算表需要在企业的各个部门之间传递、执行,为了防止无权限者修改数据,可以进行工作表的保护。默认情况下,保护工作表后不能对各单元格进行编辑(即需要锁定单元格),但可以选择单元格或单元区域,选定后工作表的编辑框中也会显示单元格的数值或公式。若不愿在编辑框中显示数值或公式,则应进行单元格内容的隐藏;单元格隐藏是指隐藏数值、公式等在编辑框中的显示,工作表中各单元格本身及其计算结果是不会被隐藏的。
某公司2019年12月中旬估计本年第4季度产品销量为830万台,预测2020年各季度预计销售量如工作任务6-4。该公司每季末的库存为当季销量的5%~20%。请编制该公司2020年的生产预算。
在Excel中设计生产预算模型与滚动条,如图6-12所示。
图6-12 Excel动态生产预算(Excel 2013)
(1)录入A1至A6、D1、B2至F2单元格或单元区域的文字(不要录入B3至F3单元区域的数字);合并A1至C1单元区域;设置字体字号等。
(2)设计滚动条及代码取值。在E1单元格拖动一个滚动条控件;控件格式设置的最小值、最大值、步长、页步长、单元格链接分别为“5、20、1、2、$E$1”,勾选“三维阴影”选项。
代码取值公式为F1=E1/100,将代码居中隐匿于滚动条之后。(www.xing528.com)
(3)表间取数。由于生产预算与“销售预算”表(图6-11)中的预计销量是一致的,所以应进行表间取数。方法是:选定生产预算工作表的B3单元格,键入“=销售预算!C3”;自动填充C3至E3单元区域公式。
(4)计算库存。季末库存量为本季销量乘以变动的百分比(即F1单元格),并用四舍五入(ROUND)保留两位小数,所以第1季末库存计算公式为B4=ROUND(B3∗$F$1,2);自动填充其他3季末的库存公式;全年的期末库存为第4季末的,所以应为F4=E4。
本季初即为上季末的库存量,所以第2季初库存即为第1季末库存,第2季初库存公式为C5=B4;然后自动填充第3、第4季初库存;第1季初库存为上年第4季销量的百分比,所以公式为B5=ROUND(830∗F1,2);全年期初库存即为第1季初库存,所以F5=B5。
(5)计算生产量。本期生产量应为本期销量加期末库存减期初库存,所以第1季生产量公式为B6=B3+B4-B5;自动填充其他各季生产量。
(6)保护工作表。保护除E1单元格滚动条以外的单元格不被修改的方法如下:
a.保护所有单元格。右击工作表左上角的全选按钮,选择进入“单元格格式”对话框,如图6-13(a)所示;在“保护”卡片中勾选“锁定”和“隐藏”复选框,单击“确定”按钮。
图6-13 单元格保护(a)与工作表保护(b)
b.取消不能保护的单元格。由于代码值所在的单元格被锁定后滚动条将无法使用,所以应取消锁定。方法是,选定E1单元格,右击并进入“单元格格式”对话框;在“保护”卡片中取消“锁定”复选框(可以不取消“隐藏”),单击“确定”按钮。
c.保护工作表。在Excel 2003及其以前版本中选择“工具/保护/保护工作表”命令,在Excel 2007—Excel 2019中选择“审阅/保护工作表”命令,进入“保护工作表”对话框,如图6-13(b)所示。在此键入密码(若不使用密码,则不必键入);若允许其他人员对工作表的内容进行部分操作,则勾选其下部的相应复选框(本例取默认设置);单击“确定”按钮。
d.按此法保护工作表后,由于E1单元格没有锁定,所以单击滚动条的左右箭头,F1、B4至F6单元区域的数值也会随之变化。
按上述方法保护后,任何单元均可单击选定,但工作表上部的编辑框中不会显示其数值、公式等,因为进行了单元格内容的“隐藏”。除E1单元格外,均不能编辑(因为进行了“锁定”),当双击这些单元格时,将会弹出不能编辑的提示对话框。
(1)若要取消保护,在Excel 2003及其以前版本中应选择“工具/保护/撤消工作表保护”,在Excel 2007—Excel 2019中应选择“审阅/更改/撤消工作表保护”,或“Office按钮/准备/保护工作簿/保护当前工作表”“文件/信息/保护工作簿/保护当前工作表”等相关命令,再键入相应的密码即可(原没设置密码的不必键入)。
(2)进行工作表的保护后,单元格锁定、隐藏的功能才能发挥出来,也就是说,单独对单元格进行保护(锁定、隐藏)是不起作用的。
免责声明:以上内容源自网络,版权归原作者所有,如有侵犯您的原创版权请告知,我们将尽快删除相关内容。