MS Office高级应用与设计课件 第10章 数据分析与管理

第三篇Excel2016应用第10章

数据分析与管理01知识目标清晰阐述数据排序、筛选、分类汇总和数据透视表的核心作用与适用场景；深入理解数据清单的概念，明确其结构规范与普通数据表的本质区别，建立规范的数据使用意识。02技能目标高效排序与精准筛选：熟练运用单/多条件排序，掌握自动与高级筛选操作，快速检索关键信息。深度汇总与多维分析：灵活创建分类汇总并分级展示，利用数据透视表对海量数据进行动态拆解与深度挖掘。03素质目标在实操中培养基于数据的逻辑判断能力，学会用数据说话、用逻辑决策；建立初步的计算思维与数据分析思维，能够将复杂问题拆解为可量化、可分析的数学模型。核心宗旨：不仅掌握Excel操作技法，更要学会用数据思维解决实际业务问题，实现从“会用工具”到“善用数据”的进阶。本章学习目标数据排序1Excel的数据排序功能可以根据数字、文字、日期等多种规则，快速将杂乱的原始信息整理成结构清晰、逻辑连贯的有序列表，是数据处理中最基础也最实用的核心技能之一。提升可读性让原本杂乱无章的数据表结构更加清晰，信息层级分明，查看和检索数据时一目了然，降低信息获取成本。发现数据规律通过有序排列，能直观地发现数据间的大小关系、变化趋势以及异常值，帮助我们挖掘数据背后隐藏的关联与差异。夯实分析基础排序是分类汇总、数据筛选等高级分析的前置步骤，规范的数据顺序能让后续复杂的数据处理流程更加轻松高效。思考时刻：为什么在进行复杂的数据分析项目前，数据排序通常被视为必须要做的第一步？（1）数据排序——让数据井然有序Excel数据清单示例：结构化的旅游物品清单表格，清晰展示了表头与数据记录的对应关系。核心定义数据清单是Excel工作表中一组相关联数据的集合，它借鉴了数据库的管理理念，将数据按行和列组织，是进行高效数据管理、排序、筛选与分析的基础结构。关键结构第一行是标题行（表头），包含各列的字段名称；下方是数据区域，每一行代表一条“记录”，每一列代表一个“字段”，行列对应关系明确。规范要求需保证同一列数据类型与格式完全一致；数据清单中禁止出现空白行或空白列，避免干扰Excel数据分析工具的识别与运算。（2）数据清单数据清单(List)：规范的核心【定义与结构】强调数据规范性，是严格的二维表结构，包含标题行和数据行，每一行代表一条完整的、相互关联的记录，结构类似数据库表。【目的与操作】专为数据管理与分析而生，能直接高效地进行排序、筛选、分类汇总及数据透视表操作，是Excel数据分析功能的基础载体。数据表(Table)：广义的载体【定义与结构】即Excel工作表，是更广义的概念。结构极其灵活，可包含任意形式的单元格组合，数据间不一定具备强关联性，用途也更为多样。【目的与操作】用于数据展示、计算、绘图、文字说明等全场景。若需使用高级分析功能，必须先将其整理成符合规范的数据清单结构。核心结论：所有数据清单都是数据表，但并非所有数据表都能被视为规范的数据清单。为了高效使用Excel的数据分析功能，首要任务是将数据整理成规范的数据清单结构。（2）数据清单记录单将表格数据转化为表单窗口，使单列长数据的管理更直观，有效避免了在密集单元格中操作的误触与混乱。核心价值：以表单形式管理数据清单，提供简洁窗口，支持数据的录入、修改、查询和删除，是处理列数较多的长数据清单的高效工具。快速开启：三步直达记录单窗口01选中数据清单中的任意一个单元格作为起点。02-03在右上角“请告诉我”搜索栏输入“记录单”，按下回车键即可打开操作窗口。新建与浏览：点击“新建”录入，用“上下条”切换查看。编辑与删除：定位记录后直接修改，或点“删除”移除。精准筛选：点击“条件”按钮输入筛选规则，快速定位符合特定要求的目标记录。（3）记录单的使用数字(Numbers)按数值大小进行升序（1,2,3...）或降序（10,9,8...）排列，遵循数学数值的比较逻辑。字母(Letters)严格按照英文字母表的顺序（A-Z或Z-A）排列，默认不区分大小写，可在排序选项中调整规则。中文文本(ChineseText)默认按拼音首字母（A-Z）排序；也可在“排序选项”中切换为按汉字笔画数量由少到多（或反之）排序。日期和时间(Dates&Times)按时间的先后顺序排列，支持从早到晚的升序，或从晚到早的降序，精确到具体的时分秒。逻辑值与错误值逻辑值中FALSE永远排在TRUE之前；所有错误值（如#N/A、#VALUE!）的优先级均相同，会被归为一类排列。空白单元格与核心注意空白单元格总是排在排序结果的最后；排序操作仅调整数据行的位置，不会修改、删除或添加单元格内的原始内容。核心原则：Excel会自动识别数据类型并应用对应规则，理解这些规则是精准预测和控制排序结果的关键。（4）单条件排序应用场景：在处理学生成绩表或销售数据表时，如需快速查看排名情况，可直接选中“总分”列的任意单元格，执行降序排列操作。定位目标列单击需要进行排序的“总分”列中的任意一个单元格，让Excel自动识别排序的基准数据列，为后续排序做准备。方法一：通过“开始”选项卡操作切换到“开始”选项卡，在“编辑”功能组中，点击“排序和筛选”下拉按钮，在弹出的菜单中根据需求选择“升序”或“降序”，即可完成数据排列。方法二：通过“数据”选项卡操作切换到“数据”选项卡，在“排序和筛选”功能组中，可直接看到“升序”和“降序”按钮，无需展开菜单，点击对应按钮即可快速完成单列数据的排序操作。（4）单条件排序核心概念解析多条件排序指依据多个关键字段对数据进行层级化排列。当主关键字无法区分数据项（如多名学生总分一致）时，系统会自动启用预设的次要关键字继续排序，以此类推，构建完整的排序逻辑链。综合评价排序场景在“综合评价”表中，排序优先级为：首先按“总分”降序；若总分相同，按“创新能力”降序；若创新能力也相同，则按“团队合作”得分降序排列，确保排名公平合理。核心技术优势突破单条件排序的局限性，通过多维度字段的综合考量，极大提升了数据排列的精确性与合理性。尤其在处理复杂、高基数的数据集时，能有效规避并列排名的歧义问题。总结：多条件排序通过“主关键字→次要关键字”的层级逻辑，为数据处理提供了更精细、更符合实际业务需求的解决方案。（5）多条件排序01.启动排序功能先选中数据清单中的任意一个单元格，再切换到“数据”选项卡，点击“排序”按钮，打开自定义排序对话框。02.设置多级排序条件首要关键字选“总分”并设为“降序”；点击“添加条件”，依次设置次要关键字为“创新能力”和“团队合作”，均选择“降序”排列，实现按权重依次筛选。03.确认并应用排序务必勾选对话框右上角的“数据包含标题”，防止表头被误排序；最后点击“确定”，完成多条件数据重排。图示为Excel“排序”对话框界面，通过“添加条件”可设置无限级别的排序规则，能精准满足复杂的数据分析需求，是处理多维度数据的高效工具。（5）多条件排序数据筛选2数据筛选是一种高效的数据检索手段，它能让Excel只显示符合特定条件的记录，同时自动隐藏不符合条件的记录，帮助用户在海量信息中精准定位目标数据，是处理大数据集的核心基础技能。快速聚焦目标从杂乱的海量数据中快速缩小范围，跳过无关信息，直接锁定并展示我们真正需要关注的关键记录。条件组合灵活不仅支持单一字段的简单筛选，还能通过与、或等逻辑构建复杂的多条件组合，满足多样化的查询需求。安全隔离数据筛选仅改变数据的显示状态，不会删除或修改原始数据源，确保数据完整性，随时可恢复查看全部内容。自动筛选：Excel最常用的基础功能，适用于简单的、基于单列或少量条件的筛选需求，操作便捷，一键启用，适合日常快速查看数据。高级筛选：针对复杂的多字段、多逻辑组合条件设计，支持将筛选结果复制到新位置，还能使用公式作为条件，功能更强大。（1）数据筛选——大海捞针的利器应用场景：在“产品销售统计表”中，快速筛选出“1月”销售量大于30的数据，精准定位目标信息。01.启动筛选功能切换到“数据”选项卡，点击“筛选”按钮，此时数据表的各列标题旁会自动出现下拉筛选箭头。02.设置筛选条件点击“1月”列标题旁的下拉箭头，在菜单中依次选择“数字筛选”，然后点击“大于”选项。03.输入目标数值在弹出的“自定义自动筛选方式”对话框中，在“大于”右侧的输入框内填入筛选阈值“30”。04.确认执行筛选点击对话框中的“确定”按钮，Excel将自动隐藏不符合条件的行，仅展示“1月”销量大于30的记录。图示：Excel“自定义自动筛选方式”对话框，设置数值筛选条件。（2）自动筛选清除筛选结果（显示全部数据）方法一（清除所有列）：在“数据”选项卡的“排序和筛选”组中，点击“清除”按钮，可一次性移除当前表格中所有列上设置的筛选条件，展示完整数据源。方法二（清除某一列）：打开特定列标题的筛选下拉列表，选择“从‘X列’中清除筛选”选项，仅取消该列的筛选条件，不影响其他列。退出筛选状态（移除筛选器箭头）核心操作：再次单击“数据”选项卡中的“筛选”按钮，即可关闭筛选功能，表格列标题旁的筛选器箭头会全部消失，恢复到普通表格状态。快速切换快捷键：Ctrl+Shift+L要点总结：“清除筛选”是保留筛选器但展示全量数据；“退出筛选”是彻底关闭筛选功能，移除所有列标题的筛选箭头。（2）自动筛选01.准备条件区域按照既定规则，在数据表外的空白区域设置好筛选条件，确保字段名与数据区域完全一致。02.启动高级筛选功能切换到Excel的“数据”选项卡，在“排序和筛选”组中，点击“高级”按钮，打开高级筛选对话框。03-04.配置参数并完成筛选依次指定“列表区域”和“条件区域”，推荐选择“将筛选结果复制到其他位置”并指定空白单元格，最后点击“确定”即可生成筛选结果。（3）高级筛选在“高级筛选”对话框中，需指定“列表区域”和“条件区域”。条件区域的设置格式直接决定了筛选结果的逻辑关系。前提关键：在进行高级筛选前，必须在工作表空白区域单独设置一个“条件区域”。该区域的第一行必须是待筛选数据的列标题（字段名），与数据区域标题完全一致。“与”关系(AND)多个条件需同时满足。将条件写在同一行。例如：筛选“农产品且销售额>5000”的记录。“或”关系(OR)满足任意一个条件即可。将条件写在不同行。例如：筛选“农产品或肉类”的记录。复杂组合示例：筛选“农产品且销售额>5000”或“肉类且销售额>2000”。设置时，将每组“与”条件写在一行，组间用换行实现“或”逻辑，形成多行多列的条件矩阵。（3）高级筛选数据分类汇总3核心概念：分组与计算将数据按照特定字段进行分组，对每组数据执行求和、计数、平均值等汇总计算，从而将杂乱的明细数据转化为清晰的统计结果。关键前提：必先排序创建分类汇总前，必须先对分类字段进行排序。这能确保相同类别的记录集中排列，是Excel正确进行分组计算的必要条件。典型场景：业绩统计分析在“销售业绩明细表”中，按“销售产品”字段分类，快速统计出每一类产品的“销售额”总计值，直观掌握各类产品的贡献度。标准操作流程预览1.对分类字段（如“销售产品”）进行排序；2.执行“分类汇总”命令；3.设置分类字段、汇总方式（如求和）及汇总项（如销售额），完成统计。（1）数据分类汇总——化繁为简的归纳工具图示为Excel“分类汇总”对话框，关键参数包括分类字段、汇总方式与选定汇总项，是实现数据自动统计的核心入口。01.排序预处理(前提)单击“销售产品”列任意单元格，执行升序或降序排序，确保同类数据连续排列，为分类汇总奠定基础。02.启动分类汇总功能切换到Excel顶部的“数据”选项卡，在“分级显示”功能组中，找到并点击“分类汇总”按钮，打开设置对话框。03.配置核心汇总参数分类字段选“销售产品”，汇总方式选“求和”，并在“选定汇总项”中勾选“销售额”，精准定义统计规则。04.确认生成汇总表参数设置无误后，点击“确定”按钮。Excel将自动按产品分类计算销售额合计，并生成分级显示的汇总报表。（2）创建分类汇总创建分类汇总后，Excel会在表格左侧自动生成层级按钮，通过点击不同数字，可快速切换数据的概括或明细视图。分级视图控制(1,2,3)1级显示总计，2级显示分类小计与总计，3级显示完整明细。通过数字切换，可从宏观到微观逐层洞察数据全貌。明细折叠与展开(+/-)点击“-”折叠当前类别的明细数据，聚焦汇总结果；点击“+”展开明细，查看具体构成。灵活控制，让数据展示更聚焦。高效应用：日常分析中，点击数字“2”可快速隐藏繁杂明细，只查看各类别的销售小计，大幅提升数据审阅效率。（3）分级显示结果在处理分类汇总数据时，若仅需提取各分类的小计结果，直接复制会连带隐藏的明细数据。通过以下步骤可快速获取纯净的汇总信息。“定位条件”对话框：精准筛选可见单元格01.分级显示，隐藏明细利用表格左侧的分级显示符号（如点击数字“2”），快速隐藏所有明细数据行，仅保留分类汇总的小计/总计行，聚焦核心结果。02.定位“可见单元格”选中汇总数据区域，在“开始”选项卡中打开“查找和选择”，点击“定位条件”，在对话框内勾选“可见单元格”并确认，仅选中当前显示的汇总行。03.复制粘贴，获取纯净结果使用快捷键Ctrl+C复制已选中的可见单元格，再用Ctrl+V将其粘贴到目标位置，即可得到不含隐藏明细、仅包含分类汇总数据的纯净表格。（4）复制分类汇总结果数据透视表的创建4数据透视表是一种交互式的报表，能够从源数据中快速汇总大量信息并深入分析。它允许用户通过简单的拖放操作，从不同角度灵活查看和组织数据，是Excel中处理复杂数据分析任务的核心利器。极致灵活性无需编写复杂公式，仅通过拖拽字段，即可快速切换行、列、值，改变分析维度。动态交互性支持即时排序、筛选和钻取数据，动态调整分析结果，让数据探索过程直观高效。强大计算力内置求和、计数、平均值、最大值等多种汇总函数，一键完成对数据的复杂运算。卓越高效性针对海量数据进行预处理和缓存，在处理大数据集时，分析性能远超传统分类汇总。适用场景：非常适合对数据进行快速汇总、多维度交叉分析与呈现。无论是财务报表分析、销售数据统计，还是市场调研结果梳理，数据透视表都能提供清晰、动态的视角，是数据分析师必备的核心技能。（1）数据透视表——最强大的数据分析工具分析场景：需要快速汇总各产品在不同销售地点的销售额总和，利用数据透视表可高效完成多维度的数据聚合与分析。01.选定数据源区域在Excel工作表中，选中用于创建透视表的完整数据清单区域，确保数据包含清晰的表头，无空行或空列干扰。02.启动数据透视表创建切换到Excel的“插入”选项卡，在“表格”组中点击“数据透视表”按钮，打开创建向导对话框。03.确认数据源与放置位置核对对话框中自动填充的数据源区域是否正确，并选择将透视表放置在“新工作表”中，保持数据整洁分离。核心逻辑：拖拽布局通过右侧“数据透视表字段”窗格，将字段拖入不同区域，即可自动生成多维分析报表，无需编写复杂公式。行区域：产品拖拽“销售产品”字段，作为统计的行维度。列区域：地点拖拽“销售地点”字段，作为统计的列维度。值区域：销售额拖拽“销售额”字段，自动计算求和汇总。（2）创建数据透视表图示为Excel数据透视表的筛选与排序对话框，通过可视化的交互界面，可快速对数据进行多维的排序与筛选操作，提升分析效率。01.灵活排序：按需重排数据展示顺序按标签排序：点击行/列标签下拉箭头，一键选择“升序”或“降序”排列维度字段。按值排序：右键数据单元格（如销售额），指定排序依据与方式，让数据按核心指标有序呈现。02.精准筛选：聚焦关键数据维度报表筛选器：将字段（如“销售日期”）拖至“筛选器”区域，生成全局筛选控件，快速切换展示范围。字段筛选器：利用行列标签自带的下拉筛选功能，直接对维度内容进行包含、排除或高级条件筛选。（3）数据透视表分析：排序与筛选动态联动，交互分析数据透视图会随数据透视表的筛选、排序等操作实时动态更新，无需手动调整，让数据分析过程更直观、更具交互性。数据透视图是数据透视表的可视化延伸，它完美融合了透视表强大的多维分析能力与图表直观的视觉呈现效果，将复杂的表格数据转化为更易理解的图形信息。01.选定数据单击数据透视表内的任意单元格，让系统识别数据源范围。02.插入图表切换到“插入”选项卡，点击“数据透视图”按钮，启动创建向导。03.选择类型在弹出的对话框中，根据分析需求选择合适的图表类型，如柱状图、饼图等。04.完成创建点击“确定”，即可生成与原透视表完全联动的数据透视图，实现可视化分析。（4）建立数据透视图核心概念数据合并计算是将分散在不同工作表、不同工作簿中的数据源，依据指定的规则进行整合与汇总的过程，是处理多源数据的高效手段。典型应用场景企业分季度统计销售业绩时，可将一季度、二季度的销售数据表（可能位于不同工作表）按“产品名称”为关键字进行合并求和，快速得到上半年的总销售额。01.指定结果位置在工作表中选择一个空白单元格，作为合并计算结果展示的起始位置，确保该区域有足够的空白空间。02.启动合并功能切换到Excel的“数据”选项卡，在“数据工具”组中找到并点击“合并计算”按钮，打开参数设置对话框。03.配置关键参数函数选“求和”，添加所有需合并的区域；关键是勾选“首行”和“最左列”，确保Excel按标签正确匹配数据。04.生成合并结果确认参数无误后点击“确定”，Excel将自动在指定位置生成汇总数据，清晰展示多源数据的合并计算结果。“合并计算”对话框中，正确配置函数与引用位置，