Excel运用总结.docx

Excel运用总结.docx

《Excel运用总结.docx》由会员分享，可在线阅读，更多相关《Excel运用总结.docx（15页珍藏版）》请在冰豆网上搜索。

Excel运用总结

Excel

1-1

在「销售详情」工作表上的单元格A3：

A21中填入「一月」。

不要更改单元格的格式

在A3输入一月-下拉-不带格式填充

1-2

修改工作簿计算选项，以便包含公式的单元格在计算结果发生改变的时候，只有通过手动重算或者保存工作簿才显示该变化

文件-选项-公式-计算选项

1-3

在「产品信息」工作表上，将单元格E3：

E22中数据的格式修改为「美元」，但不指定特定语言或地区，「美元」符号应在金额之前，不要创建自定义格式

选中区域-右击-设置单元格格式-货币符号

1-4

在「产品信息」工作表的单元格I5中使用函数添加当前日期和时间

=NOW（）

1-5

在「全年销售情况」工作表上添加图表，售出数量显示为面积图，海外笔记显示在次坐标轴上，图表类型为折线图

选中区域-插入-图表-下角标-所有图表-组合

2-1

在「批次」工作表上格式化列C和列D，使得其中的数值显示为4位小数，将格式应用到现有的行和新行

选中列-右击-设置单元格格式-数值-小数位数

2-2

在「质量评级」工作表上，如果已经测试的所有周的平均质量评级小于6，使用「水平条纹」的图案样式填充单元格A3：

A7，图案颜色为「深红」

选中列-开始-样式-条件格式-新建规则-使用公式确定要设置格式的单元格-输入公式（注意手动输入）-格式-图案颜色-图案样式

2-3

在「原材料信息」工作表上，从A1单元格开始，使用查询从素材文件夹中的「原材料信息.xlsx」工作簿加载数据。

仅包含「原料」、「产地」和「特级原材料公斤数」列

点击单元格-数据-获取和转换-新建查询-从文件-从工作簿-导入-编辑-删除不需要的列-关闭并上载至…-现有工作表

2-4

在「批次」工作表中，使用Excel预测功能计算批次20171的「所需比重」，使得「最终酒精百分比」的值为20%

点击G2单元格-数据-预测-模拟分析-单变量求解-目标单元格酒精百分比-目标值0.2-可变单元格所需比重

2-5

在「质量评级」工作表上，将表格名称从「表1」更改为「质量评级」

单击表格随意一处-设计-属性-表名称

2-6

在「质量评级」工作表上，为图表添加线性趋势线，预测批次20171直到第12周的质量

找到20171坐标点对应的表格区域，只选择到第8周-设计-图表布局-添加图表元素-趋势线-线性预测-右击预测线-设置趋势线格式-趋势预测-向前4周期

3-1

在「谷草转氨酶结果」工作表上修改图表，使其显示组2每位成员的逐月数据

点击图表-筛选组-点击图表右下角+直至不能再点-右击-显示字段列表-勾选月，取消勾选日期

3-2

在「每组平均值」工作表上添加切片器，以便用户能够对「数据透视表」进行交互式筛选，只显示特定的「谷丙转氨酶」值所对应的数据

选中区域-插入-筛选器-切片器-勾选谷丙转氨酶

3-3

在「每组平均值」工作表上删除「数据透视表」的总计行

设计-布局-总计-对行和列禁用

3-4

在「数据」工作表上创建「组合框」控件并链接到单元格F2，该控件须显示「医生」工作表列A中的三个名字

开发工具-控件-插入-组合框-右击组合框-设置控件格式-数据源区域-医生-单元格链接-主治医生-下拉显示项数3

3-5

在「数据」工作表上，对列D应用条件格式规则，在值大于或等于80时显示红色圆圈，在值大于等于40但小于80时显示黄色圆圈，在值小于40时显示绿色圆圈。

应用格式到列D中的新行和现有行

选中列-开始-样式-条件格式-新建规则-基于各自值设置所有单元格的格式-格式样式-图标集-反转图表次序-最后注意检查图标是否和题目要求一致

4-1

在「费用计算」工作表上的单元格B6中添加公式，使用INDEX函数检索「基本费用」和「每位成员的额外费用」值，并计算「折扣前每月费用」，计算公式为：

「基本费用」+（投保人数-1）*「每位成员的额外费用」

=INDEX（费用!

B2:

B5,费用计算!

B3）+（费用计算!

B2-1）*INDEX（费用!

C2:

C5,费用计算!

B3）

4-2

保护工作簿，以便用户无法添加、删除或修改工作表，除非输入密码「123456」

审阅-更改-保护工作簿

4-3

仅启用数字签名的宏

开发工具-代码-宏安全性-禁用无数字签署的所有宏

4-4

在「费用计算」工作表上，将单元格B6的值添加到监视窗口中

公式-公式审核-监视窗口

4-5

在「费用计算」工作表上，将单元格B4命名为「折扣百分比」，在工作簿范围创建名称

公式-定义的名称-定义名称

5-1

在「用户信息」工作表上的列F中使用公式，在用户超出使用限额时显示TRUE，否则显示FALSE。

要求公式必须使用AND和OR函数

=OR（AND（D2="四星级",E2>四星级）,AND（D2="三星级",E2>三星级））

5-2

在「用户信息」工作表上的单元格K7中使用平均的数计算「华北」地区的「五星级」用户平均使用情况

=AVERAGEIFS（E:

E,B:

B,"华北",D:

D,"五星级"）

5-3

在「用户信息」工作表的列G中插入公式，如果用户为五星级或四星级，并且处于西北地区时显示「折扣1」，否则显示「折扣2」

=IF（OR（AND（D2="四星级",B2="西北"）,AND（D2="五星级",B2="西北"））,"折扣1","折扣2"）

5-4

「数据透视表」已添加到数据模型。

在「地区」工作表上的单元格F4中使用GETPIVOTDATA计算「华南」地区的「四星级」用户流量使用量

=华南地区四星级单元格

5-5

在「地区」工作表上创建「三维簇状柱形图」「数据透视图」，显示每个地区的「五星级」和「四星级」用户流量使用量

单击数据透视图任意单元格-分析-工具-数据透视图-等级-点击图表右下角+直至不能再点

6-1

在「艺术班项目」工作表上设置列B的格式，使该列中所有的时间值都显示为「hAM/PM」，不要显示分钟

选中区域-右击-设置单元格格式-自定义-类型手动输入

6-2

在「艺术班项目」工作表的列H中插入OR函数，如果课程签到人数超过任意课程的平均签到人数，或者旁听人数超过2人，则显示为TRUE，否则显示为FALSE

=OR（F2>AVERAGE（F:

F）,G2>2）

6-3

在「艺术班项目」工作表的列C中添加公式，根据同一行列A中的日期对应的星期数值来显示1至7的数字。

星期日以数字7表示，星期一以数字1表示

=WEEKDAY（A2,2）

6-4

在「教师打卡」工作表中添加切片器，以便用户能够仅显示特定时间提供的课程数据。

时间值应精确到小时和分钟

单击数据透视表任意单元格-分析-筛选-插入切片器-勾选具体时间

6-5

在新的工作表上创建「带数据标记的折线图」「数据透视图」，显示每个课程的最大课程签到人数和最大旁听客户人数

选中区域-插入-数据透视表-新工作表-勾选课程、课程签到人数、旁听客户-值字段设置-最大值-单击数据透视表任意单元格-分析-工具-数据透视图

7-1

在「赞助商基金」工作表的单元格I2中添加公式，使用「汇总」行中的结构化引用计算所有赞助商为每部电影提供的平均赞助

=AVERAGE（表1[[#汇总],[电影1]:

[电影5]]）

7-2

修改Excel选项，防止在更改数据时自动重新计算公式数值。

在保存工作簿时要重新计算「公式」的值

文件-选项-公式-计算选项-手动重算-保存工作簿前重新计算

7-3

在「赞助商基金」工作表的单元格A2：

A11中添加条件格式规则，对赞助基金超过￥12,000,000的所有赞助商名字应用RGB「130」「200」「85」填充颜色

选中赞助商名字区域-开始-样式-条件格式-新建规则-使用公式确定要设置格式的单元格-手动输入公式=SUM（B2:

F2）>12000000-格式-字体-颜色

7-4

在「2017票房」工作表上使用「票房情况」名称，将图表作为模板保存到文档文件夹

右击图表-另存为模板

7-5

除非输入密码「12345678」，否则阻止其他用户修改「票房」工作表中的数据。

用户可以选择和格式化单元格、列以及行，而不必输入密码

进入工作表-审阅-保护工作表-勾选设置单元格格式、设置列格式、设置行格式

8-1

在「贸易清单」工作表上使用格式「14demarzode2012」，将列A格式化为「西班牙（墨西哥）」日期

选中列-右击-设置单元格格式-日期-区域设置-类型

8-2

在错误检查规则中启用标记列内不一致公式的选项

选项-公式-错误检查规则-勾选表中不一致的计算列公式

8-3

在「商贸清单」工作表上创建图表，在横轴上显示「货物」。

「进货总额」显示为「簇状柱形图」。

「提升」显示为「折线图」，且添加次坐标

选中相应列-插入-图表下角标-所有图表-组合

8-4

在「分类」工作表上首先按进货地点分组数据，然后按货物分组数据，最后按进货日期分组数据

单击数据透视表任意单元格-拖拽调整行

8-5

「分类」工作表上以表格形式显示数据，并在每个项目后插入一个空白行

单击数据透视表任意单元格-设计-布局-报表布局-以表格形式显示-空行-在每个项目后插入空行

9-1

通过添加浅绿左端双边框来修改「主要标题」样式

开始-样式-右击主要标题-修改-格式-边框

9-2

在「2017贸易往来」工作表的单元格M4使用条件求和函数计算销售额达标的北京总贸易金额

=SUMIFS（D4:

D33,C4:

C33,"北京*",F4:

F33,TRUE）

9-3

在「2017贸易往来」工作表上的列H中使用VLOOKUP函数从「酬金费率」表检索每位企业家的抽筋费率。

不要更改列中的任何值

=VLOOKUP（G4,酬金额!

$A$4:

$B$7,2,TRUE）

9-4

在「2017贸易往来」工作表上将单元格区域A4：

B33命名为「企业家」。

名称创建在工作簿范围内

选中区域-公式-定义的名称-定义名称

9-5

修改工作簿中的名称「税率」，使其仅包含单元格区域E4：

E33

公式-定义的名称-名称管理器-编辑

10-1

在「金额计算」工作表的单元格C10中添加公式来计算每月付款金额，假定付款日期为月初。

从本金中减去「首付」金额

=PMT（C4/12,C8*12,A8-C6,0,1）

10-2

在「销售情况」工作表的列H中添加公式，如果「库存」数量超过了「上个月销量」的5倍或者超过「本月销售」4倍时，显示「是」，否则显示「否」

=IF（OR（F2>E2*5,F2>D2*4）,"是","否"）

10-3

在「销售情况」工作表上，对数据所在的行应用格式，如果「本月销售」超过「库存」的40%，则粗体显示文字，同时将文本颜色更改为RGB「10」「196」「90」

选中区域-开始-样式-条件格式-新建规则-使用公式确定要设置格式的单元格

10-4

在「金额计算」工作表上为单元格C8添加数据验证，以便在用户输入小于1或大于3的值，或输入包含小数位的数字时显示「停止」的出错警告，标题为「错误」，错误信息为「1到3」

选中单元格-数据-数据工具-数据验证-设置-允许整数-最小值1-最大值3-出错警告-样式-停止-标题错误-错误信息1到3

10-5

在「销售表格」工作表上修改图表，以便按照推出时间分组显示项链类型

单击数据透视表任意单元格-拖拽行类型调整

12-1

在「每个系的捐书者人数」工作表上使用「捐书者列表」工作表上的数据创建「数据透视图」来显示年均捐书量。

在水平轴上显示每年各系捐书的情况

选中区域-插入-图表-数据透视图

12-2

在新工作表上使用「捐献记录」工作表上的数据创建「数据透视表」，显示每个系的捐书量

选中区域-插入-表格-数据透视表

12-3

在「图书馆活动」工作表上修改表的名称为「活动得分」

设计-属性-表名称

12-4

在「图书馆活动」工作表的列E中添加公式，使用AND函数在捐赠者参加所有3个活动都得分时显示TRUE，否则显示FALSE

=AND（[@漂流书库],[@悦读协会],[@电影放映]）

12-5

将主题颜色修改为「紫罗兰」，然后将主题保存到默认位置，名称为「图书捐赠」

页面布局-主题-颜色-主题-保存当前主题

13-1

在「销售额」工作表上修改单元格区域D3：

D34的「条件格式」规则，使用内置规则将该列中所有高于平均值的数值的字体颜色更改为红色

选中区域-开始-样式-条件格式-新建规则-仅对高于或低于平均值的数值设置格式

13-2

在「销售额」工作表上的单元格K8中使用公式，计算八月销量超过3000的「棒冰」种数

=COUNTIFS（C3:

C34,"棒冰",E3:

E34,">3000"）

13-3

在「比较」工作表的列D中添加一列内容，显示每种类型的八月最低销量

再次拖拽八月到值区域-值汇总依据-最小值

13-4

在「比较」工作表上添加在「名称」行到每种在「类型」下方

拖拽名称到行区域

13-5

在「每季度销售额」工作表上，为单元格区域B3：

C7设置密码保护。

命名该区域为在「每季度销售数据」。

使用「123456」作为区域保护密码。

保护工作表，密码在「123456」

选择区域-审阅-更改-允许用户编辑区域-新建-标题-保护工作表

14-1

在「功能区」显示「开发工具」选项卡

右击选项卡-自定义功能区

14-2

在「晚会节目」工作表的单元格A3中使用单个函数显示在「工作人员名单」工作表上分配了「声乐导演」角色的人名

=VLOOKUP（"声乐导演",工作人员名单!

A2:

B8,2,FALSE）

14-3

在「工作人员工资」工作表上追踪在公式中直接或间接引用了单元格C2值的所有单元格

单击C2-公式-公式审核-追踪从属单元格-点击至不再反应为止

14-4

在「晚会彩排成本」工作表上添加「移动平均」趋势线到图表

设计-图表布局-添加图表元素-趋势线

14-5

在「晚会彩排成本」工作表上，将单元格区域A2：

B15命名为「晚会彩排成本」。

范围在工作簿内

选定区域-公式-定义的名称-定义名称

17-1

在「销售额数据透视表」工作表中，将「类型」的字段作为筛选器添加到「数据透视表」

分析-显示-字段列表-拖拽类型到筛选器

17-2

在「销售额」工作表上，将L列的日期更改为「西班牙（墨西哥）」，格式为「14demarzode2012」，将B列数值改为「$西班牙语（墨西哥）」

选中区域-右击-设置单元格格式-日期-区域-类型

17-3

在「销售额」工作表中的单元格C35中计算所有单价低于$10、七月销售额超过2000的七月销售额的总量

=SUMIFS（D2:

D33,B2:

B33,"<10",D2:

D33,">2000"）

17-4

删除引用单元格区域「销售额!

$E$2:

$E$33」的名称「八月销售额」

公式-定义的名称-名称管理器-删除

17-5

在「销售额数据透视表」工作表上添加显示「数据透视表」中的数据「三维堆积条形图」

单击数据透视表任意单元格-插入-图表-数据透视图

18-1

在「动物生产」工作表中的单元格B2添加使用单一函数的公式，对「动物生产」工作表的单元格A2中列出的家庭，在「代号查询」工作表中寻找对应的代号

=VLOOKUP（A2,代号查询!

A2:

B26,2,FALSE）

18-2

防止其他用户添加、删除或者修改工作表，使用密码「123456」保护工作簿

审阅-更改-保护工作簿

18-3

创建新的自定义颜色，修改「着色2」选项为RGB「236」「168」「60」，将自定义颜色命名为「黄色列表」

页面布局-主题-颜色-自定义颜色

18-4

在「动物生产」工作表的单元格F2中创建公式来显示春季和秋季是否都有动物出生，还是仅出生在春季。

春秋两个季节都有出生的，显示「两季」，如果仅有春季出生，显示「仅春季」

=IF（AND（D2,E2）,"两季","仅春季"）

18-5

在「动物生产」工作表的列H中创建使用日期和时间函数的公式，显示是在多少年前办理的养殖执照

=YEAR（TODAY（））-G2（注意单元格格式）

19-1

修改Excel选项，防止更改数据时自动重新计算公式数值。

保存工作簿时也不要重新计算公式数值

文件-选项-公式-计算选项-手动重算-取消勾选保存工作簿前重新计算

19-2

根据「订购」工作表上的单元格B2：

H10，在「合作商成本」工作表单元格A3中创建「数据透视表」的数据，显示美家合作商的总成本平均值，每行显示一家合作商

选中区域-插入-数据透视表-行合作商-值总成本-值汇总依据平均数

19-3

在「利润数据透视图」工作表上更改「数据透视表」的设置，使得在每次打开文件时都刷新「数据透视表」的数据

单击数据透视表任意单元格-分析-数据透视表-选型-选项-数据-勾选打开文件时刷新数据

19-4

将「数据」工作表上B3：

D6的单元格添加到「监视窗口」

选中区域-公式-公式审核-监视窗口

19-5

在「促销摊位销售」工作表上的单元格H7中添加使用单个逻辑函数的公式，在每个人都至少售出一份冰工厂时显示TRUE，在有任何人没有售出冰工厂时显示FALSE

=AND（H3,H4,H5,H6）

20-1

使用Excel中的「填充序列」选项，在单元格B1：

D1中填充。

不要更改单元格的格式

不带格式填充

20-2

删除「第2年」工作表的所有条件格式

开始-样式-条件格式-管理规则-显示其格式规则当前工作表-删除规则

20-3

在「图表」工作表上添加切片器，用于筛选「支出/收入」列

单击数据透视表任意单元格-插入-筛选器-切片器

20-4

在「图表」工作表上，将「数据透视图」另存为文档中名为「Park」的模板

右击-另存为模板

20-5

在功能区显示「开发工具」选项卡

右击选项卡-自定义功能区-勾选开发工具