计算基础与智能 4

电子表格处理软件Execl大学计算机与人工智能第5章AI+Execl本章将系统介绍Excel在数据处理与分析中的核心功能与应用方法，同时讲解如何借助大模型技术辅助表格处理与分析，帮助学习者掌握从基础操作到智能化应用的综合实践能力。目录5.1Excel基本操作

5.2公式与函数5.3Excel图表5.4Excel的数据管理

5.1Excel基本操作制作表格：操作步骤1）启动Excel应用程序，新建一个Excel文件，并保存在自己的文件夹中。2）填充数据3）格式设置设置行高设置数字格式设置单元格格式设置表格边框4）通过AI工具对Excel表格的数据进行分析5）设置条件格式5.1.1各种类型数据的输入1.文本输入•默认左对齐，单元格最多显示1024个字符•数字作为文本输入时，以'（单引号）开头，如学号'0116001‘2.数字输入•默认右对齐•负数：输入-5或(5)•分数：先输入0和空格，如03/43.日期与时间输入•日期用/或-分隔，如2026/8/7•时间用:分隔，如14:30•快捷键：Ctrl+;输入当前日期，Ctrl+Shift+;输入当前时间5.1.2数据的自动输入1.使用自动填充柄2．使用“填充”命令单击“开始”→“编辑”→“填充”命令按钮的下拉菜单，从中选择“序列”命令，将出现“序列”对话框。在该对话框中，可指定序列产生的方式、类型等。5.1.3工作表的基本操作1．选定工作表单击标签选定单个工作表。

相邻多个：先单击首个标签，按住Shift再单击最后一个工作标签。

不相邻多个：按住Ctrl，

依次单击所需工作标签。2.插入工作表：选定某工作表，新的工作表将会插入到当前活动工作表的前面。有如下两种方法。开始

→‘单元格’组

→‘插入’下拉

→‘插入工作表’右键单击当前工作表标签

→

选择‘插入’5.1.3工作表的基本操作3．移动或复制工作表选定一个工作表，选择“开始”→“单元格”组，单击“格式”按钮，然后选择“移动或复制工作表”，弹出如图所示的对话框，选择要移动或复制的位置。如果是复制工作表，则还需勾选“建立副本”复选框，单击“确定”按钮后即可完成工作表的移动或复制操作。5.1.3工作表的基本操作4．删除工作表选定要删除的工作表，选择“开始”→“单元格”组，单击“删除”按钮，从下拉菜单中选择“删除工作表”。或者选定要删除的工作表，单击鼠标右键，在弹出的菜单中选择“删除”。5．重命名工作表双击工作表标签，输入新的名字按回车键即可，或者选定要重命名的工作表标签，单击鼠标右键，在弹出的菜单中选择“重命名”，输入新的名字。5.1.4编辑数据1．单元格的选定、插入与删除选定单元格：鼠标指针移至单元格

→单击左键，该单元格即被选定为当前单元格；单击左上角的单元格

→拖动至右下角单元格，即选中连续的单元格区域。

不连续单元格区域：先选第一区域，按住‘Ctrl’再选其他区域。插入单元格：

路径：‘开始’→‘单元格’→‘插入’→‘插入单元格’→

选择插入方式

→

确定。删除单元格：路径：‘开始’→‘单元格’→‘删除’→‘删除单元格’→

确认删除选项

→

确定。整个工作表的选择5.1.4编辑数据2．数据的移动与复制（1）移动（复制）整个单元格区域

单击源单元格

→‘开始’→‘剪贴板’→‘剪切’（移动）或’复制’。定位到目标单元格

→‘开始’→‘剪贴板’→‘粘贴’，如图所示。5.1.4编辑数据（2）选择性地复制单元格选择“开始”→“剪贴板”→“选择性粘贴”选项，可以粘贴内容，格式或者公式等，如图所示。5.1.5格式化工作表Excel提供了十分丰富的格式化命令，用来设定数字的格式、文本的对齐方式、字形字体的设置、边框底纹颜色的设定等。在开始选项卡，点击对话框启动器，可以打开“设置单元格格式”对话框进行设置。5.1.6AI辅助生成表格【例5-4】某公司销售部门提供了本月各区域的销售数据，以文字形式呈现。请借助AI工具将以下文字描述快速转换为规范的Excel表格，便于后续数据统计与分析。1）在AI工具中输入提示词2）AI生成的表格如图所示，可以直接复制到Execl进行下一步编辑。5.2公式与函数5.2.1公式公式是以“=”开头的表达式。其中，表达式是由运算符、常量、函数、单元格地址等组成，不能包含空格。使用公式可以对单元格中的数据进行加、减、乘、除、乘方、统计等计算，也可以用公式对文本进行操作和比较。1.公式的输入选中单元格，在单元格或编辑栏中输入以“=”开头的公式，按`Enter`或单击“输入”按钮确认，单元格显示计算结果。5.2.1公式2.公式中的运算符在公式中，每个参与运算的数据值或单元格地址等都是由运算符来连接的,常见运算符及功能如下：5.2.2单元格的引用单元格的引用是指对单元格的地址的引用，分为相对引用、绝对引用和混合引用。相对引用相对引用是指公式中的单元格地址随着公式单元格位置的改变而改变，直接用列标和行号表示，如“C5”、“F7”或“D5:F6”。绝对引用绝对引用指公式中的单元格地址不随着公式位置的改变而发生改变。使用绝对引用的方法是在行号和列标前面加上“$”符号，如“$A$1”、“$B$2”。5.2.2单元格的引用混合引用混合引用是指在同一个单元格中，既含有相对引用又有绝对引用。混合引用是在行号或列标前面加上“$”符号，如“$C5”或“C$5”。4．跨工作表的单元格地址引用公式中可能会用到另一个工作表的单元格中的数据，可通过如下形式引用：工作表名！单元格地址，如公式“=(B2+C2+D2)*Sheet2!A5”，其中“Sheet2!A5”表示工作表Sheet2中的A5单元格地址。5.2.3函数与AI智能辅助Excel提供了多种类型的函数，包括数学与三角函数、财务函数、日期与时间函数、统计函数、数据库函数等，利用这些函数，可以实现数学计算、逻辑计算和财务计算等。1．函数的形式函数由三部分组成，包括函数名、参数和括号。参数可以是数字、文本、逻辑值或单元格地址，也可以是公式或其他函数，当函数的参数为其他函数时称为嵌套。一般形式为：函数名(参数1,参数2,…,参数n)5.2.3函数与AI智能辅助2．常用函数（1）SUM函数（求和函数）

格式：SUM（数据1，数据2，…，数据n）

SUM函数是Excel中最常用的函数，可以计算指定参数列出的数据总和，常用于计算总成绩、总产量、总人数等。（2）AVERAGE函数（求平均值函数）

格式：AVERAGE（数据1，数据2，…，数据n）

AVERAGE函数用来对数据进行平均值计算，常用于计算平均成绩、平均收入、平均销售值等。常用函数（3）COUNT函数（计数函数）

格式：COUNT（数据1，数据2，…，数据n）

COUNT函数用来计算指定参数列表中含数值型数据的参数个数，常用于统计指定单元格区域中含数值单元的个数。注意：COUNT函数只统计数值型数据，文本、逻辑值、错误信息、空单元格不统计。（4）MAX函数

格式：MAX（数据1，数据2，…，数据n）

MAX函数用来求出指定数据或单元格区域中最大的数值。常用函数（5）MIN函数

格式：MIN（数据1，数据2，…，数据n）

MIN函数用来求出指定数据或单元格区域中最小的数值。（6）IF函数

格式：IF（指定条件，条件成立时的值，条件不成立时的值）

IF函数用来根据所选单元格的值是否满足条件来得到相应的值。（7）SUMIF函数（有条件求和函数）

格式：SUMIF（指定的条件区域，指定条件，求和区域）SUMIF函数常用于对指定单元格区域进行有条件的求和。常用函数（8）COUNTIF函数（有条件计数函数）

格式：COUNTIF（指定的条件区域，指定条件）

COUNTIF函数用于对指定的单元格区域进行有条件的计数。（9）RANK函数

格式：RANK（指定单元格，指定单元格所处序列，降序/升序）RANK函数用来返回某数值在一系列数值中的排列名次。（10）VLOOKUP函数（查询函数）

格式：VLOOKUP（查找值，查找区域，返回值在查找区域的列数，匹配方式）

VLOOKUP函数是按列查找，最终返回该列所需查询列序所对应的值。5.2.3函数与AI智能辅助借助AI工具，通过自然语言描述需求即可直接生成函数公式、并提供参数解释与错误诊断，降低了使用的难度。在使用AI辅助时，一个重要的技巧是上传Excel文件。AI可以直接读取表格的结构、列名及数据内容，因此提示词的撰写无需再详细描述数据位置或行列对应关系，只需聚焦于计算目标和特殊条件即可，表述更加简洁高效。5.3Excel图表制作基本图表步骤：1）选择“插入”选项卡，在“图表”组中单击“插入柱形图或条形图”，在弹出的下拉框中选自己需要的图，Excel将自动生成如图所示的图表。图5-37“图表”组的各选项按钮5.3Excel图表2）坐标轴标题设置：选择“图表工具”→“设计”→“图表布局”组，单击“添加图表元素”。选择“坐标轴标题”，在图表中分别添加“主要横坐标轴标题”选择“纵坐标轴标题”，单击鼠标右键，选择“设置坐标轴标题格式”→“标题选项”，将“对齐方式”中的文字方向设置为“竖排”。3）单击图表上方的“图表标题”，并修改为“各类型图书销售量比较”；单击图表下方的“图例”，单击鼠标右键，选择“设置图例格式”→“图例选项”，将图例位置设置为“靠右”。5.3Excel图表4）选中纵坐标轴，单击鼠标右键，选择“设置坐标轴格式”→“坐标轴选项”，将边界的最小值设置为0，最大值设置为100，间隔单位最大设为10。5.3Excel图表5）图书销售预测。利用Excel2016的“预测工作表”功能，基于历史数据智能预测下一年四个季度的销售情况。预测工作表要求数据为两列格式，因此在图书销售统计表.xlsx的sheet2中准备以下数据，如图所示。5.3Excel图表选中数据区域A1:B5，单击菜单栏“数据”→“预测工作表”，在弹出的“创建预测工作表”对话框中填写参数，“预测结束”设置为8（预测到第8季度），“置信区间”保持默认95%，单击创建按钮，在新工作表中生成预测结果，如图所示：5.3.1图表的基本概念1．图表类型常用的有柱形图、折线图、饼图、条形图、面积图等。选中需要制作图表的单元格区域，选择“插入”→“推荐的图表”→“插入图表”，“所有图表”栏中很多表格以供选择。5.3.1图表的基本概念图表的构成：图表主要由图表标题、绘图区、数据系列、图例、坐标轴以及坐标轴标题等组成，如图所示。图表纵坐标纵坐标轴横坐标横坐标轴图数据系绘图图表标题轴标题5.3.2图表的创建1）选定需要制作图表的数据区域，从“插入”→“图表”组中选择所需创建的图表的类型，然后单击要使用的图表子类型，Excel将自动生成相应的图表。2）单击图表，功能区会出现“图表工具”选项卡，利用其下的“设计”和“格式”选项卡可以完成图表图形颜色、图表位置、图表标题、图例位置等的设计和布局以及颜色的填充等格式设计，如图所示。5.3.3图表的编辑与修改1.“图表工具|设计”选项卡当选定图表后，选择“图表工具|设计”选项卡，如图所示。在“图表布局”组中，可以添加图表元素，可以选择所需要的布局方式进行快速布局；在“图表样式”组中，可以选择适合表现的样式等；在“数据”组中，可以“切换行/列”的数据，“选择数据”的更改；在“类型”组中，可以更改图表的类型；在“移动图表”组中，可以选择放置图表的位置。5.3.3图表的编辑与修改2．“图表工具|格式”选项卡“格式”选项卡主要用来格式化绘图区，如图所示。绘图区的图案随图表类型不同而不同，包括线条颜色、粗细、线条样式、区域填充颜色、填充效果等。不仅对整个绘图区进行设置，还可以对绘图区的某一个元素（如饼图中的一块饼）进行单独的设置。对于图表中的一些元素，例如图表标题、坐标轴等，也可以对它们进行格式设置。5.3.3图表的编辑与修改3．用“快捷菜单”进行编辑、修改选中需要修改的地方，然后单击鼠标右键，在弹出的快捷菜单中选择需要的命令。例如：要修改“绘图区”的格式，只需选定绘图区后单击鼠标右键，弹出快捷菜单，选择“设置绘图区格式”命令，随即打开了“设置绘图区格式”对话框，如图所示，可以对绘图区进行相应设置。5.3.4AI辅助图表设计AI辅助图表设计通过AI工具可以快速获得专业的图表选型建议。具体做法是：将Excel数据文件作为附件上传至AI对话窗口，并清晰描述分析目标：例如：“我有一份2024年各产品季度销售额数据，包含‘家电’‘数码’‘服饰’三个产品线，需要分析各产品线的销售趋势、年度总销售额对比以及全年销售占比，请推荐最合适的图表类型并说明理由。”注意：AI的推荐是基于常见数据分析规则生成的，最终选择仍应结合具体场景进行判断。同时，由于AI幻觉的原因，需要对推荐结果进行审核。5.4Excel的数据管理创建一个Excel工作簿文件，表格数据如图所示。将此表格分别复制到Sheet2、Sheet3、Sheet4中，并完成如下操作：1）使用Sheet2中的表格，以“成绩”为关键字，以“递减”（从高到低）方式排序。2）使用Sheet3中的表格，筛选出“成绩”大于等于90分的女学生，筛选结果放在该表格的下方。3）使用Sheet4中的表格，要求按系部对“年龄“和”成绩“的平均值分类汇总。操作步骤1．排序操作使用Sheet2中的表格，选中要进行排序“成绩”字段列的任意单元格（如F2），选择“数据”→“排序和筛选”组，单击“降序”按钮，即按照“成绩”字段从高到低排序，如图所示。操作步骤2．筛选操作使用Sheet3中的表格。1）确定条件区域，可以在数据表之外的任意单元格区域确定。本例选择H3:I4单元格范围，并输入条件。2）选择“数据”→“排序和筛选”组，单击“高级”按钮，打开“高级筛选”对话框，如图所示。2．筛选操作3）在对话框中选择“将筛选结果复制到其他位置”方式。4）在“列表区域”中输入整个数据表的单元格范围“A1:F11”。5）在“条件区域”中输入已经建立条件的单元格范围“H3:I4”。6）在“复制到”中输入筛选后结果存放的区域“A15”（注：筛选结果存放的区域，选择时可以只选择生成表格的左上角单元格）。7）单击“确定”按钮，得到筛选的结果。操作步骤3．分类汇总操作使用Sheet4中的表格。1）首先按照分类字段排序，即按照“系部”字段排序。选中“系部”列的任意一个单元格（如C2），选择“数据”→“排序和筛选”组，单击“升序”或“降序”。2）选择“数据”→“分级显示”组，单击“分类汇总”按钮，打开“分类汇总”对话框，如图所示。3．分类汇总操作3）在“分类汇总”对话框中，“分类字段”选择“系部”，“汇总方式”选择“平均值”，“选定汇总项”为“年龄”和“成绩”。4）选中“汇总结果显示在数据下方”复选框。5）单击“确定”按钮即可得到分类汇总的结果，如图所示。3．分类汇总操作6）若单击数据表格左侧的“隐藏分类汇总明细”按钮，将得到如图所示的表格。5.4.1数据排序1．简单排序简单排序是指在工作表中以一列单元格中的数据为依据，对工作表中的所有数据进行排序。首先鼠标单击该列数据中的任一单元格，然后选择“数据”→“排序和筛选”组，单击“升序”或“降序”按钮2．多重排序如果排序的字段中有相同数据时，简单的排序方法便不能满足要求，此时可对多列数据进行设置。即某列数据中有相同值时，可根据另一列的数据进行排序。1）鼠标单击单元格区域“A1:F11”中的任一单元格。2）选择“数据”→“排序和筛选”组，单击“排序”按钮，即打开“排序”对话框5.4.1数据排序【例