excel学习文档格式.docx

excel学习文档格式.docx

《excel学习文档格式.docx》由会员分享，可在线阅读，更多相关《excel学习文档格式.docx（19页珍藏版）》请在冰豆网上搜索。

调出SUM函数。

6.Ctrl+Enter:

选中多个单元格时，此操作可以实现全部填充。

7.选定左上角单元格，按下Shift，同时选中右下角单元格可以全部选定。

8.选定一列，光标放至左侧，按下按下Shift，同时拖动，可使列位置互换。

9.F4：

实现绝对引用与相对引用之间的切换，例：

$H$4转为H$4。

10.F3：

调出名称表。

11.F5：

调出定位表。

若表中有若干空白单元格，此时Ctrl+A选定表格区域，F5调出定位表，在“定位条件”中选定“空值”，此时在光标定位的单元格中输入公式，使其等于上一个单元格，Ctrl+Enter即可实现所有空白区域全部填充。

12.定义和管理名称：

选定区域，在名称框处输入名称，Enter即可为此区域命名。

二、智能匹配的运用：

1.插入一列时，出现智能匹配图标，可以选择“与左/右边格式相同”。

2.粘贴时“保留源文件宽”。

三、excel选项的运用：

1.常用—编辑自定义列表—导入—添加：

此时输入第一个名字，向下或向右拖动，可输入整个序列。

（自定义序列）

2.自定义—不在功能区的命令—添加（朗读单元格）：

可实现单元格朗读功能。

四、单元格设置超链接

选中单元格，右键选中“超链接”，在“请键入单元格引用”中输入单元格区域，即可实现单元格与明细区域的链接。

五、条件格式设置：

1.查找和选择—条件格式：

可以查找到有条件格式的单元格。

2.条件格式—清除规则：

可以取消设定格式。

3.条件格式—管理规则—编辑规则：

可以修改规则。

选中“仅显示图标”可以将数字结果隐藏。

4.条件格式—新建规则—使用公式确定要设置格式的单元格：

实现自定义条件格式规则。

六、超大表格的打印问题

页面布局—页面设置—工作表：

设置打印标题及打印顺序。

单元2：

创建图表，体验数据分析效果

一、收入与成本双轴分析图

1.选中图表—布局—置所选内容格式

2.布局—图例—在底部显示图例：

指标名称在图表下方。

3.布局—图表标题—图表上方：

4.设计—另存为模板：

选中作图数据区域，插入—他图表—有图表类型—模板，即可引用此模板。

二、用条形图反应任务完成进度情况

1.选中数据区域—插入—条形图

2.选中条形图—设计—切换行/列

3.选中条形图—布局—设置所选内容格式—系列选项

此时将分类间距调至大间距型，同时将系统绘制在次坐标轴。

三、创建气泡图产品价格和销量关系

1.选中数据区域—插入—其他图表—气泡图

2.选中气泡图—设计—选择数据—编辑

3.选中气泡图—布局—设置所选内容格式（将产品A、B气泡大小缩放为90）

4.选中气泡图—布局—设置所选内容格式（垂直轴）—坐标轴选项

将最小值固定为5即可。

四、制作下拉菜单式成本费用结构图

1.Excel选项—常用—在功能区显示开发工具选项卡

2.开发工具—插入—表单控件—组合框（窗体控件）

3.Ctrl+1（或者点击右键）—设置控件格式—控制

注：

数据源区域只能识别纵向序列，无法识别横向序列。

4.选中空单元格—公式—查找与引用—INDEX

5.选定2001年数据—插入—饼图

6.选中饼图—设计—选择数据：

选择INDEX函数定义的动态数据。

右键点击组合框，将其拖入图表中即可。

五、收入与成本滚动分析图

1.开发工具—插入—表单控件—滚动条（窗体控件）

2.Ctrl+1（或者点击右键）—设置控件格式—控制

3.选中一空单元格—公式—查找与引用：

输入INDEX（B3:

N3,,$K$10）&

"

销售分析图"

或者OFFSET（$A$3,,$K$10）&

。

4.公式—定义名称—引用位置=OFFSET（收入与成本滚动分析图!

$B$5:

$B$8,,收入与成本滚动分析图!

$K$10），创建新名称“数据”。

5.选中1月数据—插入—饼图

6.选中饼图—设计—选择数据：

图表数据区域F3选择“数据”，即可使饼图动态化。

7.选中标题，输入“=收入与成本滚动分析图!

$I$10”

单元3：

数据的统计与分析

一、高级筛选

1.选中数据区域—数据—筛选—高级

此时，选中“将筛选结果复制到其他位置”，在“选择不重复的记录”前打勾。

条件区域，同一行的条件是“和”的关系，不同行条件是“或”的关系。

例如：

2.模糊查找，查找同一姓氏的人。

二、数据有效性

数据—数据有效性—设置—允许（序列）

首先需要对序列进行命名，在数据有效条件中，“来源”引入INDIRECT函数，将字符串翻译为名称，同时在“提供下拉箭头”处打勾。

三、数据透视表

1.公式—定义名称—新建名称（数据透视表）

在编辑名称对话框中，“引用位置”处引用OFFSET函数，编辑OFFSET（Sheet1!

$A$1,,,COUNTA（Sheet1!

$B:

$B）,COUNTA（Sheet1!

$1:

$1））创建新名称。

2.插入—数据透视表：

出现“创建数据透视表”对话框，在“表/区域”处F3选择粘贴名称“数据透视表”，选择“新工作表”，即可创建一个数据透视表。

3.相关操作

（1）选中数据透视表—选项—字段列表

此操作可以选择出现或者隐藏“数据透视表字段列表”。

（2）光标放至单元格左侧，出现，此时可以选中多行相关数据。

（3）选中数据透视表—选项—选择—整个数据透视表

复制，粘贴，出现智能匹配图标，选择“值和数字格式”，即可使用透视表中的数据。

（4）选中行标签（年份）—选项—将所选内容分组：

此时可以按照季度进行分组，选中“取消组合”即可恢复。

（5）选中数据透视表—设计—分类汇总—在组的顶部显示所有分类汇总

（6）选中数据透视表右键—数据透视表选项—数据

在“打开文件时刷新数据”前打勾。

（7）选中数据透视表—选项—选项—显示报表筛选页

此时选中“工作地区”，可以为每一个地区建立一个报表。

单元4：

用数学算法与函数建立管理模型

一、混合引用（行固定、列固定相结合）

二、数学和三角函数

SUMIF函数应用（公式—数学和三角函数—SUMIF）

SUMIF（range,criteria,[sum_range]）

SUMIF函数语法具有以下参数：

range 

必需：

用于条件计算的单元格区域。

criteria 

用于确定对哪些单元格求和的条件。

sum_range 

可选：

要求和的实际单元格（如果要对未在range参数中指定的单元格求和）。

如果省略sum_range参数，Excel会对在范围参数中指定的单元格（即应用条件的单元格）求和。

三、逻辑函数

1.用IF、AND与OR函数统计促销结果（公式—逻辑）

2.IFERROR函数：

IFERROR（HLOOKUP（B$14,$A$1:

$G$8,$A$20+1,0）,"

）

四、统计函数

COUNTIF函数应用（公式—其他函数—统计）

五、查找与引用函数：

1.VLOOKUP函数：

只能在指定区域的最左列里找。

搜索表区域首列满足条件的元素，确定待搜索单元格在区域中的行序号，再进一步返回选定单元格的值。

Range—lookup:

填0时，为精确匹配；

不填，为模糊匹配。

2.HLOOKUP函数：

只能在指定区域的最顶行里找。

搜索表区域首行满足条件的元素，确定待搜索单元格在区域中的列序号，再进一步返回选定单元格的值。

3.MACH函数：

返回符合特定值特定顺序的项在数组中的相对位置。

4.INDEX函数：

在给定的单元格区域中，返回特定行列交叉处单元格的值及引用。

六、文本函数（公式—文本）

1.FIND函数：

返回一个字符串在另一个字符串中出现的起始位置。

2.LEFT函数：

从左边返回指定个数的字符。

3.LEN函数：

返回文本字符串中的字符个数。

4.SUBTITUTE函数：

将字符串中的部分字符以新字符串替换。

5.HIGHT函数：

从最右边返回指定个数的字符。

单元5：

使用宏和VisualBasic扩展Excel功能

1.开发工具—宏安全性—启用所有宏

2.开发工具—录制宏—快捷键Ctrlshifty—停止录制

3.开发工具—宏—执行

或者使用快捷键Ctrlshifty直接执行宏。