搜索
第6篇物业篇项目16制作小区业主信息管理表6.1项目17制作小区物业费用管理表6.2项目18制作小区车位管理表6.36.1项目16制作小区业主信息管理表6.1.1项目背景小区业主信息资料管理是小区物业管理部门的一项重要工作。科学、有效地管理业主信息，不仅能提高日常工作效率，同时还能提高小区物业管理水平。本项目以“制作小区业主信息管理表”为例，介绍Excel制作业主信息管理表的方法。6.1.2项目效果图6-1业主信息管理表图6-2小区房屋情况透视表6.1.3知识与技能●工作簿的创建●工作表重命名●数据的输入●数据有效性设置●函数MID的使用●公式的使用●数据透视表6.1.4解决方案任务1创建工作簿，重命名工作表（1）启动Excel2003，新建一个空白工作簿。（2）将创建的工作薄以“小区业主信息管理表”为名保存。（3）将“小区业主信息管理表”中的Sheet1工作表重命名为“业主基本信息”。任务2创建“小区业主信息管理表”的基本框架（1）在“业主基本信息”表中输入工作表标题。（2）输入表格标题字段。任务3输入“物业编号”（1）选中A3单元格。（2）输入物业编号“KY1001”。（3）用鼠标拖动其填充句柄至A34单元格，输入“KY1001”—“KY1032”的物业编号。任务4输入“房号”、“楼宇名称”和“楼层”（1）输入“房号”。（2）输入“楼宇名称”。（3）输入“楼层”。任务5输入“房屋类型”（1）为“房屋类型”设置有效数据序列。（2）输入“房屋类型”。任务6输入其他业主信息（1）录入“业主姓名”和“购房合同号”数据。（2）用值列表的形式录入“配套设施”的数据。（3）录入“房屋状态”、“建筑面积”和“使用面积”数据。任务7计算“公用面积”这里，公用面积=建筑面积-使用面积。任务8格式化“业主信息管理表”（1）将表格标题“小区业主信息管理表”合并及居中，并设置字体格式。（2）选中A2:M34单元格区域，自动套用格式“序列2”。在此基础上，为表格添加外粗内细的边框，并将该区域设置为居中对齐。（3）页面设置。任务9制作小区房屋情况透视表（1）选中“业主基本信息”工作表。（2）选中该工作表中的任一非空单元格。（3）选择【数据】菜单中的【数据透视表和数据透视图】命令，打开【数据透视表和数据透视图向导】对话框，按向导提示进行操作。（4）设置页字段布局。将“数据透视表字段列表”中的字段“配套设施”拖拽到区域“请将页字段拖至此处”。（5）设置行字段布局。将“数据透视表字段列表”中的字段“房屋类型”拖拽到区域“将行字段拖至此处”。（6）设置数据项字段布局。将“数据透视表字段列表”中的字段“建筑面积”、“使用面积”和“房号”拖拽到区域“请将数据项拖至此处”。（7）将生成的数据透视表工作表重命名为“小区房屋情况透视表”。6.1.5项目小结本项目通过制作“小区业主信息管理表”，主要介绍了工作簿的创建、工作表重命名、数据的输入、数据有效性设置以及使用MID函数提取字符串信息、利用公式进行统计等操作。在此基础上，通过数据透视表对业主和房屋信息进行汇总和分析，以便科学有效地对小区物业进行管理。6.1.6拓展项目1.制作小区房屋分布比例图。2.制作小区房屋入住状态数据透视表。3.制作小区房屋入住比例图。6.2项目17制作小区物业费用管理表6.2.1项目背景小区物业费用管理中涉及大量的、动态的、错综复杂的数据和信息，对物业费用进行及时、准确地分析和处理，可以最大限度地降低处理信息的劳动强度，使物业公司的管理手段和管理水平产生质的飞跃。本项目以“制作小区物业费用管理表”为例，介绍Excel制作物业费用管理表的方法。6.2.2项目效果图6-3小区物业费用明细表图6-4物业收费明细清单效果图6.2.3知识与技能●工作簿的创建●工作表重命名●工作簿间数据的引用●公式的使用●函数IF、VLOOKUP、SUM和ROUND的使用●单元格格式的设置6.2.4解决方案任务1创建工作簿，重命名工作表（1）启动Excel2003，新建一个空白工作簿。（2）将创建的工作薄以“小区物业费用管理表”为名保存。（3）将“小区物业费用管理表”工作簿中的Sheet1工作表重命名为“物业费用明细表”。任务2创建“物业费用明细表”的基本框架（1）在“物业费用明细表”中输入工作表标题。（2）输入表格标题字段。任务3输入“小区业主收费明细表”的基础数据（1）输入“物业编号”。（2）使用函数“VLOOKUP”输入“业主姓名”。（3）使用VLOOKUP函数，用“业主基本信息”工作表中的“建筑面积”，填充“管理费”一栏的“面积”数据。（4）输入“水”、“电”、“气”和“光纤费”的基础数据。任务4统计“小区业主收费明细表”中的各项费用（1）统计“水”费。（2）统计“电”费。（3）统计“气”费。（4）统计“管理费”。这里，管理费按照房屋的类型进行收取，普通住宅为1.2元/M2，商用为1.5/M2。任务5制作“物业收费明细清单”框架（1）将Sheet2工作表重命名为“物业收费清单”。（2）制作“物业收费明细清单”框架。任务6查询业主“物业收费明细清单”数据这里，我们希望通过输入“物业编号”数据，能动态地在“物业收费明细清单”中显示相应业主的收费明细数据。（1）查询“业主姓名”。（2）查询明细清单中“收费项目”为“水”的各项数据。（3）查询明细清单中“收费项目”为“电”、“气”、“管理费”和“光纤费”的各项数据。任务7完成“物业收费明细清单”其他数据的处理（1）输入“通知日期”的数据。（2）输入“缴费期限”的数据。这里，设定缴费期限为通知日期下达后一周之内。（3）计算“应缴费用合计”数据。（4）输入“应缴金额（大写）”数据。（5）设置表格边框，隐藏网格线。6.2.5项目小结本项目通过制作“小区物业费用管理表”，主要介绍了工作簿的创建、工作表重命名、工作簿间数据的引用等操作。在此基础上，通过IF、VLOOKUP、SUM和ROUND函数进行数据的汇总和统计。此外，进一步借助VLOOKUP函数实现小区业主物业费用的动态查询和显示，做到了小区物业费用管理的科学性和准确性。6.2.6拓展项目1.制作“小区物业缴费催款单”。2.查找出水、电、气费用均在40元以上的住户资料。3.为查看方便，设置冻结窗格。将C4单元格设置为冻结点。6.3项目18制作小区车位管理表6.3.1项目背景随着经济的发展，在小区中，停车难的问题日益突出。为了解决业主的停车问题，需要对小区内的车位进行合理规划、有效地管理。本项目以“制作小区车位管理表”为例，介绍Excel制作小区车位管理表的方法。6.3.2项目效果图6-4小区车位管理表6.3.3知识与技能●工作簿的创建●复制工作表●工作表重命名●数据有效性设置●函数IF、INDEX和MATCH的使用●数据筛选●数据透视图6.3.4解决方案任务1创建工作簿，重命名工作表（1）启动Excel2003，新建一个空白工作簿。（2）将创建的工作薄以“小区车位管理表”为名保存。（3）将“小区物业费用管理表”工作簿中的Sheet1工作表重命名为“车位管理表”。任务2复制工作表（1）打开“小区业主信息管理表”工作簿。（2）选中“业主基本信息”工作表。（3）将选定的工作表“业主基本信息”工作表复制到“小区车位管理表”工作簿中。任务3创建“车位管理表”的基本框架（1）在“车位管理表”中输入工作表标题。（2）输入表格标题字段。任务4输入“小区车位管理表”基础数据（1）输入“车位号”。（2）输入“业主姓名”、“车牌号”和“租用日期”数据。（3）输入“房号”。这里，我们使用MATCH和INDEX函数来自动输入房号。①选中C3单元格。②选择【插入】菜单中的【函数】命令，打开【函数参数】对话框，从“选择函数”列表中选择“INDEX”函数，打开【选定参数】对话框。③从INDEX“参数”列表中选择参数“array,row_num,column_num”。④然后单击【确定】按钮，打开【函数参数】对话框，设置参数“Array”。⑤将光标置于参数“Row_num”中，然后单击编辑栏左侧的函数列表，从列表中选择“MATCH”函数。即将函数“MATCH”嵌套在参数“Row_num”中。此时，将打开MATCH函数参数对话框。⑥设置函数MATCH的参数。⑦单击【确定】按钮，填充出B3单元格业主的房号。⑧选中C3单元格，拖动填充句柄至C22单元格，将公式复制到C4:C22单元格区域中，可自动输入所有业主的“房号”。任务5输入车位“类别”数据（1）为“类别”设置有效数据序列。（2）输入“类别”。任务6输入“单价”数据这里，“单价”可采用自动输入。设定车位类别为“租用”的，每月“单价”为300元，若为“自备”的，收取每月30元的管理费用。任务7输入各季度车位费并计算“合计”金额。（1）输入各季度车位费。（2）计算车位费“合计”。任务8查看车牌号为“川A”的车辆数据信息（1）复制“车位管理表”工作表，并将复制的工作表重命名为“川A车辆数据信息”。（2）选中“川A车辆数据信息”工作表。（3）筛选车牌号中“始于”“川A”的数据任务9制作“小区不同类别车位数据透视图”（1）选中“车位管理表”工作表。（2）选择【数据】菜单中的【数据透视表和数据透视图】命令，打开【数据透视表和数据透视图】向导对话框，按向导提示进行操作。（3）将“类别”拖作为“行”字段，将“车牌号”作为“数据”项字段处。（4）修改数据透视图。①将数据透视图类型修改为“三位饼图”②修改图表标题为“小区不同类别车位占比图”并设置格式。③为图表添加数据标志“百分比”。④修改图标绘图区格式，如数据点图案颜色、绘图区填充色等。⑤将数据透视图工作表重命名为“小区不同类别车位占比图”。6.3.5项目小结本项目通过制作“小区物业费用管理表”，主要介绍了工作簿的创建、复制工作表、工作表重命名、数据有效性设置、利用函数IF、INDEX和MATCH实现数据的自动输入。在此基础上，通过数据筛选、数据透视图等对小区车位管理数据进行分析，使小区车位管理做到合理、科学。6.3.6拓展项目1.汇总统计各类车位的费用。2.筛选出小区非“川A”的车辆信息。3.制作小区业主的车位费比较图。