表格表姐是如何炼成的EP1

OFFICE系列课程Episode2企管与人力资源中心企人工程师王子雷认识一下Office大家族Access数据库管理工具Word文字处理、编辑Excel数据计算、分析PPT幻灯片演示、宣传OneNote笔记本备忘录Outlook邮件和日程安排Project项目计划、管理Visio流程、结构等图表Sharepoint互交同步工具Infopath交互图表Publisher专业小型出版物表哥表姐是如何炼成的王子雷第3页第4页认识一下Office大家族65%48%6%表哥表姐是如何炼成的王子雷第5页Excel的系统概念不假时日练习，你能做出下面这样的表表哥表姐是如何炼成的王子雷第6页Excel的系统概念员工休假源数据表汇总统计表参数表表哥表姐是如何炼成的王子雷第7页Excel的系统概念源数据表汇总统计表参数表输入预先设置调用转换输出表哥表姐是如何炼成的王子雷第8页Excel的系统概念源数据库记录你的头像、等级等信息参数库记录所有的用户账号和密码信息用户界面根据参数库的登录信息，调用源数据库中的用户信息后自动生成。表哥表姐是如何炼成的王子雷第9页Excel的系统概念参数表即保存配置参数的表，供源数据表和统计汇总表调用，属于基础数据。类似于在编程中所说的常量、常数。通常来说，参数是不会经常变化的数据，甚至一次设置后就永远都不会再变。参数往往会被汇总统计表调用数次。表哥表姐是如何炼成的王子雷第10页Excel的系统概念源数据表即“天下第一表”，数据操作表，相当于系统的录入界面。便于数据录入、数据计算和统计。源数据表是为了让计算机读懂的数据表。表哥表姐是如何炼成的王子雷第11页Excel的系统概念汇总统计表是我们使用Excel的最终目的，得到我们需要的信息。汇总统计表是为了让人阅读，要符合人类阅读的习惯。由于经常涉及数据更新，根本无需我们手动操作就可以自动更新数据。表哥表姐是如何炼成的王子雷第12页Excel的系统概念例子某制造企业需要每天统计消耗原料以及材料成本，同时对每日库存进行统计追踪，对低于库存预警的物料提出采购预警，并自动生成采购计划。表哥表姐是如何炼成的王子雷第13页Excel的系统概念Step1：明确需求明确需要的最终结果，往往就是汇总统计表需要来做的。1、统计每日材料消耗；2、汇总每日消耗成本；3、对低于库存物料进行预警；4、自动生成建议采购计划。表哥表姐是如何炼成的王子雷第14页Excel的系统概念Step2：明确数据身份明确都需要哪些数据，这些数据的来源、是干吗用的，有什么特点，应放到哪个表里需要哪些数据：1、各种物料的参数（名称、规格、单价等）；2、每日各种物料的消耗量；3、各种物料的原始库存量；4、各种物料的建议库存量；5、每日消耗成本；6、建议采购物料数据的来源：1、物料基本说明（一次性输入不变）；2、每日库管输入；3、库管一次性输入；4、库管一次性输入；5、自动计算得分；6、自动计算得出。数据的位置：1、参数表；2、源数据表；3、参数表；4、参数表；5、统计汇总表；6、统计汇总表。表哥表姐是如何炼成的王子雷第15页Excel的系统概念Step3：设计表格样式明确了哪个表里有哪些数据，我们就可以开始设计表格样式了，可以先从源数据表入手、然后是参数表，最后再来设计统计汇总表表哥表姐是如何炼成的王子雷第16页Excel的系统概念Step3.1设计源数据表列编号列标题、字段单元格、数据列行号行表哥表姐是如何炼成的王子雷第17页Excel的系统概念Step3.1设计源数据表表哥表姐是如何炼成的王子雷第18页Excel的系统概念源数据表一维数据一个标题行字段分类清晰数据属性完整数据连续无合并单元格无合并计行无分割行/列数据区域中无空白单元格禁用短语或句子表哥表姐是如何炼成的王子雷第19页Excel的系统概念Step3.2设计参数表表哥表姐是如何炼成的王子雷第20页Excel的系统概念Step3.3设计汇总统计表表哥表姐是如何炼成的王子雷第21页Excel的系统概念Step4.1数据联接参数表和源数据表全选参数后，CTRL+SHIFT+F3，进行批量定义，选择首列表哥表姐是如何炼成的王子雷第22页Excel的系统概念Step4.1数据联接参数表和源数据表在名称管理器中，我们已经看到定义的名称了表哥表姐是如何炼成的王子雷第23页Excel的系统概念Step4.1数据联接参数表和源数据表在名称单元格内设置“数据有效性”，选择序列，来源为“=名称”表哥表姐是如何炼成的王子雷第24页Excel的系统概念Step4.1数据联接参数表和源数据表在规格单元格内设置“数据有效性”，选择序列，来源为“=Indirect（A2）”表哥表姐是如何炼成的王子雷第25页Excel的系统概念Step4.1数据联接参数表和源数据表在规格单元格内就会自动显示我们所需要的规格信息了表哥表姐是如何炼成的王子雷第26页Excel的系统概念Tips：名称“名称”在Excel中的作用类似于自定义常量。用定义的名称能帮助简化公式编辑和搜索定位数据单元格区域。表哥表姐是如何炼成的王子雷第27页Excel的系统概念Tips：名称定位：单击“名称框”右侧的下拉按钮，在列表中单击已经定义的名称，系统则自动迅速地定位到名称列。表哥表姐是如何炼成的王子雷第28页Excel的系统概念Tips：名称公式：名称可以直接参与公式之中，降低公式复杂度和错误风险。表哥表姐是如何炼成的王子雷第29页Excel的系统概念Tips：数据有效性设置单元格只能输入我们想要的数据内容或格式。是一种提供数据标准化的有效手段表哥表姐是如何炼成的王子雷第30页Excel的系统概念Tips：Indirect函数返回引用内容表哥表姐是如何炼成的王子雷第31页Excel的系统概念Tips：Indirect函数返回引用内容，将参数中的文本变为单元格或名称引用。例子中A1=“B2”；B2=123；B3=indirect（A1）=indirect（“B2”）=B2=123表哥表姐是如何炼成的王子雷第32页Excel的系统概念Step4.1数据联接参数表和源数据表使用Vlookup函数搜索查找联接数据。表哥表姐是如何炼成的王子雷第33页Excel的系统概念Tips：Vlookup函数在表格间建立联接最常用的函数。Lookup_value：需要检索比较的值；Table_array：进行首列对比检索的区域；Col_index_num：检索后右移的列数；Range_lookup：检索参数，一般设为0；表哥表姐是如何炼成的王子雷第34页Excel的系统概念Tips：Vlookup函数系统将在Table_array的范围里，也就是A1：E10的范围的首列里（A1：A10）对比查找Lookup_value中的值（也就是A2，电池），查到之后，将A1：E10区域中电池所在的那一行的第Col_index_num列（也就是第三列，单位那一列）的值返回并显示，得到了我们想要的“个”数值。表哥表姐是如何炼成的王子雷第35页Excel的系统概念Tips：数据错误问题任何情况下都要避免在表格中出现#N/A或#DIV/0等数据错误显示。可以通过IF函数嵌套解决。思路：如果关键参数为空，则目标直接显示为空；参数不为空后，再按公式计算。E.G：=IF(A3="","",VLOOKUP(A3,'物料参数表1'!A2:E11,3,0))表哥表姐是如何炼成的王子雷第36页Excel的系统概念Tips：引用的问题Excel里对位置的引用包括绝对引用和相对引用：B2、E4、Y18都是相对引用，相对引用在自动填充过程中会自动位移；$B$2为绝对引用，”$”为绝对引用标志，绝对引用不会自动位移；$B2，只锁定为B列，不锁定行；B$2，只锁定为第2行，不锁定列；$B$2,列与行都锁定。表哥表姐是如何炼成的王子雷第37页Excel的系统概念Tips：条件格式根据数据内容自动变化自身格式的一种实现方法。条件格式里也可以使用公式。表哥表姐是如何炼成的王子雷第38页Excel的系统概念Tips：条件格式当D列数据为”出库”时，E列自动变为红色字体。条件格式的关键在于，当条件中的公式返回结果为“肯定”或“1”的时候，条件成立，按预设格式执行。表哥表姐是如何炼成的王子雷第39页Excel的系统概念Tips：日期格式在需要日期的时候，需要正确填写日期的格式。首先将单元格设置为日期格式，再按YYYY-MM-DD的格式输入日期。表哥表姐是如何炼成的王子雷第40页Excel的系统概念Step4.2数据联接汇总统计表和源数据表利用sumif函数和辅助列链接表哥表姐是如何炼成的王子雷第41页Excel的系统概念Tips：SUMIF函数检索在Range范围内符合Criteria条件的项，并计算这些项Sum_range列的值和例子中：在源数据表的B列中，寻找值为’B5’即7号的数值，然后将所有B列为7号的值的E列进行相加。表哥表姐是如何炼成的王子雷第42页Excel的系统概念Tips：辅助列有时候为了计算方便，会设立辅助列，辅助列的作用是在表格中为了计算方便而进行数据预处理。在正式表格中，辅助列都被隐藏，但是它依然可以有效。比如本例中，辅助列将出库的数量信息自动转变为负数，以便后续计算表哥表姐是如何炼成的王子雷第43页Excel的系统概念Tips：辅助列利用辅助列协助条件搜索利用“&”自动合并为字符串表哥表姐是如何炼成的王子雷第44页Excel的系统概念Tips：辅助列配合辅助列，实现了SUMIF公式对物料规格、出/入库分类和日期的分类筛选表哥表姐是如何炼成的王子雷第45页Excel的系统概念Step4.3数据联接汇总统计表和参数表利用Vlookup函数调用物料单价，然后与出库数量相乘得到每日成本消耗表哥表姐是如何炼成的王子雷第46页Excel的系统概念Step4.4数据联接汇总统计表、参数表和源数据表利用源数据表辅助列计算各物料的最新库存变化，然后与参数表原始库存计算，得到最新库存数据。表哥表姐是如何炼成的王子雷第47页Excel的系统概念Step4.4数据联接汇总统计表、参数表和源数据表思路：如果当前库存大于预警库存，显示0，否则显示预警库存-当前库存量。=IF(F4>SUMIF('物料参数表1'!B$2:B$10,汇总统计表!B4,'物料参数表1'!E$2:E$10),0,SUMIF('物料参数表1'!B$2:B$10,汇总统计表!B4,'物料参数表1'!E$2:E$10)-汇总统计表!F4)表哥表姐是如何