最全+Excel操作步骤讲义.docx
《最全+Excel操作步骤讲义.docx》由会员分享，可在线阅读，更多相关《最全+Excel操作步骤讲义.docx（37页珍藏版）》请在冰豆网上搜索。
最全+Excel操作步骤讲义
Excel操作步骤讲义
0101公差、等比数列、单元格设置操作
Sheet1：
要求:
（请严格按照要求在指定的区域中进行操作，否则答案无效！
！
！
）
1用序列在A3：
A12中填入甲、乙……、癸。
在A3单元格中输入汉字“甲”，选中A3单元格后，利用填充柄下拉至A12。
2B3：
B12中填入从50开始、等差为5的序列。
在B3单元格中输入50，点击整列→编辑→填充→序列→等差序列→步长值（5）
3C3：
C12中填入从50开始、等比为1.05的序列，结果保留2位小数。
在C3单元格中输入50，点击整列→编辑→填充→序列→等比序列→步长值（1.05）
选中C3：
C12区域→右键→设置单元格格式→数值→小数位数
（2）
4D3：
D12区域定义公式，计算合计，保留2位小数。
选中D3→插入→函数sum（或直接点击fx→sum）→相对引用→区域选中B2:
C2→确定
将D3下拉至D12，选中D3：
D12区域→右键→设置单元格格式→数值→小数位数
（2）
5B13：
D13区域定义公式，计算合计，保留2位小数。
选中B13→插入→函数sum（或直接点击fx→sum）→相对引用→区域选中B3:
B12→确定
将B13右拉至D13，选中B13：
D13区域→右键→设置单元格格式→数值→小数位数
（2）
6B14：
D14区域定义公式，计算平均值，保留2位小数
选中B14→插入→函数average（或直接点击fx→average）→相对引用→区域选中B3:
B12→确定
将B14右拉至D14，选中B14：
D14区域→右键→设置单元格格式→数值→小数位数
（2）
7用条件格式将D3：
D12区域，小于150的销售额用红色，非斜体字体显示。
选中D3：
D12区域，格式→条件格式→小于<→150→格式设置（颜色：
红色，常规）→确定→确定
Sheet2：
要求:
（请严格按照要求在指定的区域中进行操作，否则答案无效！
！
！
）
1用序列在A2：
A11中填入代理1、代理2……、代理10。
在A2单元格中输入“代理1”，选中A2单元格后，利用填充柄下拉至A11。
2G2：
G11中定义公式，计算出每个代理商的销售额，保留2位小数。
在G2中输入“=E2*F2”（E2、F2可直接点击单元格，*要按shift8）点击回车既出结果。
将G2下拉至G11，选中G2：
G11区域→右键→设置单元格格式→数值→小数位数
（2）
3G12单元格中计算出销售额合计
选中G12单元格插入→函数sum（或直接点击fx→sum）→相对引用→区域选中G2：
G11→确定
4G13中计算出销售额的平均值。
选中G13单元格插入→函数average（或直接点击fx→average）→相对引用→区域选中G2：
G11→确定
5G14单元格中计算出COLOR300型打印机销售额合计：
6选中G14单元格插入→函数sumif→“选型号一列D2：
D11”（引用）→选中color300型（哪个单元格都可以）→选中销售额所有单元格G2：
G11→回车答案7946
7用条件格式将小于10000的销售额用红色显示。
（G2：
G11）
选中全部销售额单元格→格式→条件格式→小于<→1000→格式设置（颜色：
红色）→确定→确定
Sheet3：
要求:
（请严格按照要求在指定的区域中进行操作，否则答案无效！
！
！
）
1题格式：
字体：
隶书；字号：
20，加粗；底纹：
图案中的“50%灰色”；字体颜色：
红色；合并及居中（A1：
F1）。
选中A1：
F1→右键→设置单元格格式→字体（隶书，20号，加粗）→图案→图案→50%灰色→字体（红色）→对齐→合并单元格，水平：
居中→确定
2标题格式：
字体：
宋体；字号：
14；字体颜色：
绿色；文字居中。
选中A2：
F2→右键→设置单元格格式→字体（宋体；14号；绿色）→对齐→水平：
居中→确定
3A2：
F10区域加上“所有框线”；底纹：
淡蓝
选中A2：
F10→右键→设置单元格格式→边框（外边框、内部）→图案→淡蓝色→确定
4用函数功能计算合计、平均值、最大值、最小值。
选中B7插入→函数sum（或直接点击fx→sum））→相对引用→区域选中B3：
B6→确定。
将B7向右填充至F7
平均值average、最大值MAX、最小值MIN,步骤同上。
Sheet4：
1B1：
D1合并居中，设置内容为黑体、加粗、倾斜、14磅。
选中B1：
D1→右键→设置单元格格式→对齐→合并单元格，水平：
居中→字体（黑体、加粗、倾斜、14磅）→确定
2表格第二列（“行业名称”这一列）中的文字水平、垂直居中。
选中第二列→右键→设置单元格格式→对齐→垂直：
居中
3“就业增长量”设为2位小数，并将260以上的数据设置为加粗、红色。
选中B4：
B9→右键→设置单元格格式→数值→小数位数
（2）。
选中B4：
B9→格式→条件格式→大于>→260→格式设置（加粗、红色）→确定→确定。
4表格线改为蓝色实线。
选中B3：
D9右键→设置单元格格式→边框→先选颜色（蓝色）后画外边框、内部线。
5“就业增长量”一列添加底纹；图案中的“25%灰”。
选中D3：
D9→右键→设置单元格格式→图案→图案→25%灰色→确定。
Sheet5：
1除C列（“零售额百分比（%）”所在列）。
选中C列→右键→删除。
2公式计算出“增长率”，并填入相应的单元格中（增长率=（本月零售额-上月零售额）/上月零售额），计算结果用百分比表示，并保留2位小数。
点击E3→输入=（B3-D3）/D3回车。
将E3下拉填充至E17。
选中E3：
E17右键→设置单元格格式→数值→百分比（小数位数2）→确定。
3“本月销售额”为主要关键字按降序，对表格数据进行排序。
点击“本月销售额”所在列任一单元格→点击降序快捷键
4数字区域A2：
D2设置蓝色底纹，并将其中的数据垂直居中显示。
选中A2：
D2→右键→设置单元格格式→图案→蓝色→对齐→垂直居中→确定。
0102
Sheet1：
要求:
（请严格按照要求在原表上进行操作，否则答案视为无效！
！
！
）
⑴标题：
行高20，黑体，16号,合并及居中，垂直居中，白色底纹，红色字体。
⑵表头行：
行高20，楷体、12号，水平居中，垂直居中。
⑶表格内容：
宋体，10号字，非斜体，第1列居中，其它列居右。
⑷数字：
会计专用格式，货币符号：
￥中文（中国），2位小数点。
⑸为标题增加批注“依据2002年资料统计”。
6取消工作表的网格线，并为表格（标题除外）加上表格线（细线）。
7据表格数据创建一个柱形图（第1子图表类型），放于表格右侧，图表标题为“销售统计图”。
（1）略
（2）选中第二行→右键→行高（20）确定，其它略。
（3）、（4）略。
根据题目要求在右键→设置单元格格式→在六个选项卡里设置。
（5）选中标题→右键→插入批注（输入“依据2002年资料统计”）出现红色小三角
（6）工具→选项→视图→网络线（不挑勾）→确定。
（7）插入→图表（或图表向导快捷键）→下一步→数据区域（单选表格）→下一步（图表标题）→作为其中对象插入→完成。
Sheet2：
要求:
（请严格按照要求在原表上进行操作，否则答案视为无效！
！
！
）
（1）标题：
行高20，黑体，16号，合并及居中，垂直居中，白色底纹，红色字体。
（2）表头行：
行高20，楷体、12号，水平居中，垂直居中。
（3）表格内容：
宋体，10号字，非斜体，第1列居中，其它列居右。
（4）字：
会计专用格式，不带人民币符号，2位小数点。
（5）为标题增加批注“依据2002年资料统计”。
（6）消工作表的网格线，并按样文为“A2：
D6”区域加上表格线。
（7）据A2：
A6,C2：
C6数据区域创建一个三维柱形图，放于表格右侧，图表标题为“冰箱销售统计图”。
图表标题为红字，并加双下划线。
1-6略
7.图表向导→下一步→柱形图→三维柱形图（最下方）→下一步→（系列产生在列）→数据区域：
销售地区一列（A2：
A6）和（ctrl）冰箱一列（C2：
C6）→下一步（图表标题改为“冰箱销售统计图”）→作为其中对象插入→完成。
将图表移至表格右侧→点击图表标题→右键→图表标题格式（红字，双下划线）→确定
Sheet3：
要求:
（请严格按照要求在原表上进行操作，否则答案视为无效！
！
！
）
⑴计算营收总计及各公司市场份额。
⑵第一行格式：
黑体、16号、跨列居中、紫色底纹、黄色字体。
⑶列标题格式：
行高30，黄色底纹，文字居中（水平和垂直）。
⑷表格中各列文字（A3:
B13）居中，数据（C3:
E13）靠右，天蓝色底纹（A3:
E13）。
（5）市场份额”和“增长率”两列数据使用百分比样式。
（6）“营收”列数据使用会计专用格式，不带人民币符号。
（7）“名次”单元格增加附注“依据2002年资料统计”。
（8）用“公司”和“市场份额”两列数据（B2：
B12，E2：
E12）创建一个三维饼图，要求将占市场份额最大的饼块分离。
1.选中C13单元格→fx→sum（C3：
C12），选中E3单元格输入公式=C3/C13→F4（绝对引用）→回车。
相对引用：
随单元格变化，绝对引用：
不随单元格变化而变化
2-7略.8：
饼块分离→左击黑点圈中学要分离的饼块。
Sheet4：
1、将“设备”一行移到“通讯费”一行之前。
选中设备一行→右键→剪切→选通讯量一行→右键→插入已剪切的单元格。
2、标题文字的格式：
黑体、18号字、加粗、合并及居中、深兰色底纹、白色字体。
3、各数据单元格数字格式为会计专用（C4:
F10），保留2位小数，左对齐，其它各单元格居中（A2:
F3,A4:
B10）。
4、将数据为-400的单元格设置底纹为红色,字体为白色，粗体。
5、为-400单元格加上附注“超支”。
6、利用“调配拨款”一列（E2:
E9）的数据制作三维饼图。
（标题为“2001年调配拨款”）
（将四小块分离出来）
2-6略
答题要求：
请在原表上进行操作（画图同）
Sheet5：
要求:
（请严格按照要求在原表上进行操作，否则答案视为无效！
！
！
）
1、将“部门”一列移到“员工编号”一列之前。
选中B2：
B20→右键→剪切。
点击A2→右键→插入已剪切单元格
2、清除表格中员工编号为S23的一行的内容。
3、标题文字格式：
隶书、20号字、合并及居中。
4、第二行黄底红字、宋体、字形：
加粗。
5、为部门一列中所有的“市场部”填加灰色（50%）底纹（A6:
A7,A12:
A13,A19）。
选中部门所有单元格→格式→条件格式→等于→选市场部→格式→底纹灰色（50%）→确定
6、按样文为A2：
G20区域设置边框。
先选线条类型和颜色，再选外边框和内部框线。
7、为“员工编号”一列中的K12单元格加上附注“优秀员工”。
8、使用“工资”（G2:
G20）数据创建一个三维饼图，图表标题为“工资比例图”。
0103筛选、高级筛选
Sheet1：
要求：
（请在原表上进行操作，否则答案无效！
！
！
）
1请利用自动筛选功能将北京地区的销售记录筛选出来。
点击任意一个内容为“北京”的单元格→自动筛选
或选“地区”单元格→数据→筛选→自动筛选→北京
2在筛选的基础上，对北京的销售记录按商品名称进行升序排序。
点击任意商品单元格→升序。
或数据→排序→主要关键字“商品”→升序→确定。
3排序后，按照商品字段对销售额进行汇总，汇总方式为求和。
所有内容非斜体。
选中A2：
D12→或数据→排序→确定
选中B3：
B12或数据→分类汇总（或直接点击分类汇总快捷键）→分类字段（商品）→汇总方式（求和）→选中汇总项（销售额）→确定。
Sheet2：
（1）请利用高级筛选功能将北京地区销售额大于80的记录筛选出来。
（2）请将筛选结果放在F10:
I17中，否则答案无效！
！
！
。
在F6：
G7单元格设置条件区域
地区
销售额
北京
>80
数据→筛选→高级筛选（或直接点击高级筛选快捷键）→将方式选为（将筛选结果复制到其他位置）→列表区域（A2：
D35）→条件区域（F6：
G7）→复制到F6：
G7→确定。
Sheet3：
（1）请用高级筛选功能将北京地区销售额大于100小于500的记录筛选出来。
（2）请将筛选结果放在F10:
I15中，否则答案无效！
！
！
。
在单元格F6：
H7区域内做如下条件区域
地区
销售额
销售额
北京
>100
<500
数据→筛选→高级筛选（或直接点击高级筛选快捷键）→将方式选为（将筛选结果复制到其他位置）→列表区域（A2：
D35）→条件区域（F6：
H7）→复制到F10：
I16→确定
Sheet4：
（1）请利用高级筛选功能将商品为洗衣机或品牌为海尔的记录筛选出来。
（2）请将筛选结果放在F10:
I25中，否则答案无效！
！
！
。
在单元格F6：
G8区域内做如下条件区域
商品
品牌
洗衣机
海尔
注意：
题目要求中出现“或”就错一格。
数据→筛选→高级筛选（或直接点击高级筛选快捷键）→将方式选为（将筛选结果复制到其他位置）→列表区域（A2：
D35）→条件区域（F6：
G8）→复制到F10：
I25→确定.
Sheet5：
（1）请用高级筛选将天津地区销售额大于300小于600的记录筛选出来。
（2）请将筛选结果放在F10:
I18中，否则答案无效！
！
！
。
略
2.条件格式、替换练习
（1）将标题“期末成绩表”改为“部分期末成绩表”
双击击标题单元格，将标题改为部分期末成绩表
（2）将表中的低于60的数据设置为“红色”
选中部门D5：
E14单元格→格式→条件格式→小于→60→格式→红色→确定
（3）用查找、替换的方法将性别中的“1”、“2”分别替换为“男”和“女”。
选中C5：
C14→编辑→替换→查找内容中输入“1”→替换为中输入“男”→全部替换
查找内容中输入“2”→替换为中输入“女”→全部替换
（4）用公式求出每一个同学的总分（总分=语文+数学+英语+理综）结果保留两位小数。
点击H5单元格→自动求和→将D5下拉至D14
3.图表练习
Sheet1
（1）删除“东南菱帅”所在行
选中第9行→右键→删除
（2）用公式计算出“1月-4月销量”，并填入相应的单元格中
在B3中输入=E3-C3-D3，将B3下拉至B8
（3）将工作表E1单元格插入批注为“上半年统计表”
点击E1→右键→插入批注→上半年统计表
（4）在工作表中以“品牌”和“累计销量”两列数据为区域（区域为A2：
A18和E2：
E18）,建立一个图表。
要求：
用簇状柱形图，系列产生在列，图表标题设为“2004年1月~6月国内部分汽车销量统计（单位：
辆）”，图标位置放在表格的右边，图表插入到当前工作表中。
图表向导→下一步→柱形图→簇状柱形图→下一步→（系列产生在列）→数据区域：
区域为A2：
A18和E2：
E18→下一步（图表标题改为“2004年1月~6月国内部分汽车销量统计（单位：
辆）”）→作为其中对象插入→完成。
将图表移至表格右侧。
Sheet2
1-4略，5.打印预览→页面设置→横向→纸张大小A4
6.图表向导→下一步→折线图→堆积数据点折现图→下一步→（系列产生在列）→数据区域：
区域为A1：
D13→下一步（图表标题改为“北京某年气温图”）→创建新图表→完成。
Sheet3
略
Sheet4：
略
4.排序、筛选练习
Sheet1：
1-3略4.点击姓名列任意单元格→升序排列（或数据→排序→主要关键字（姓名）→升序）
5.在G3：
H5单元格设置条件区域
计算机计算机
>90
<60
数据→筛选→高级筛选（或直接点击高级筛选快捷键）→将方式选为（将筛选结果复制到其他位置）→列表区域（A2：
E12）→条件区域（G3：
H5）→复制到A22→确定。
Sheet2：
1、3略，2数据→排序→主要关键字（金牌）→降序→次要关键字（银牌）→降序→确定
5、自动筛选练习
1.点击“一月”的任意单元格→数据→筛选→自动筛选
2.点击销售产品列任意单元格→升序排列
3.数据→分类汇总→分类字段（销售产品）→汇总方式（求和）→汇总项（销售额）
6.分类汇总练习
Sheet1
1.点击类别列任一单元格→升序排列
2.数据→分类汇总→分类字段（类别）→汇总方式（平均值）→汇总项（点击）→汇总结果显示在数据下方。
Sheet2略
Sheet3略
7.高级筛选练习
Sheet1
在D33：
E34单元格设置条件区域
类别点击
综合>500
数据→筛选→高级筛选（或直接点击高级筛选快捷键）→将方式选为（将筛选结果复制到其他位置）→列表区域（A1：
E31）→条件区域（D33：
E34）→复制到A36→确定。
Sheet2略
Sheet3略
8.数据透视表练习
Sheet1
（1）将鼠标点到有字的地方→数据→数字透视表和透视图→下一步→选定区域不动→下一步（现有工作表）、区域点击A18→下一步（点击左下角布局或版式）将分店托至列字段、商品名称拖至行字段、销售额拖至数据项→确定→完成。
（2）出现数据透视表→直选分店（将分店1、分店2对勾取消）→确定
Sheet2略
Sheet3略
⑴请为上表建立数据透视表。
将销售地区作为页字段，商品名称作为行字段，年份作为列字段，销售额作为数据项。
数据透视表放在本工作表内，起始位置为A22。
⑵将数据透视表设置为只显示北京2000年的各商品销售额信息。
9单变量求解练习
Sheet1
一般都先建立公式，再用单变量求解
（1）选中A1：
B1→右键→设置单元格格式→对齐→合并单元格，
→右键→插入批注（输入产品销售统计表）→字体→隶书、16号。
（2）选中B5→输入=（B2-B3）*B4→回车
（3）选中B4→工具→单变量求解→目标单元格B5→目标值15000→可变单元格B4→确定→确定。
Sheet2
注意：
第一问没让用单变量求解，第二问才用。
步骤1：
选中C6→插入→函数→财务函数→PMT（年金）→Rate（利率）选C5→Nper（贷款年限）选C4→PV（贷款总额）C3→确定，在C3中输入200000
步骤2：
选中C6→工具→单变量求解→目标单元格C6→目标值-35000→可变单元格C3→确定→确定。
Sheet3
步骤1、2略3.选中C8→插入→函数→财务函数→PMT（年金）→Rate（利率）选C7/12→Nper（贷款年限）选C6→PV（贷款总额）C5→确定
步骤4选中C8→工具→单变量求解→目标单元格C8→目标值1539.25→可变单元格C5→确定→确定。
Sheet4
（1）选中B5→输入公式→=（B1-B2）*B4-B3→回车
（2）选中B5→工具→单变量求解→目标单元格B5→目标值45000→可变单元格B4→确定→确定。
10模拟运算表练习
要求：
⑴请根据以上给出的贷款金额、贷款利率和贷款年限，利用年金函数计算每年应偿还多少钱（请将公式定义在A4中，在每年年末支付，贷款金额以正数表示，结果采用默认格式）。
⑵在第⑴题的基础上，利用模拟运算表计算在不同的利率（B4：
F4）和贷款年限（A5：
A9）下每年各应偿还多少钱（请在上表中进行操作，否则答案无效！
！
！
）。
（1）选中A4→插入→函数→财务函数→PMT（年金）→Rate（利率）选B2→Nper（贷款年限）选B3→PV（贷款总额）B1→确定.
（2）选中全表→数据→模拟运算表→输入引用行单元格B2（利率）、输入引用列单元格B3（年限）→确定。
11.公式结算、方案练习
（1）选中C7输入公式→=C6/（C4-C5）,选中C8输入公式→=（C4-C5）*C7-C6
（2）选中B4：
C8→插入→名称→指定→选择最左列→确定。
（3）选中C8→工具→方案→添加→方案名：
方案一，可变单元格C4确定，单价110→确定
添加→方案名：
方案二，可变单元格C4：
C5确定，单价120，变动成本30→确定
添加→方案名：
方案三，可变单元格C6确定，固定成本2500→确定
添加完成后点击摘要→结果类型：
方案摘要→结果单元格C7：
C8确定。
13条件判断练习
Sheet1
步骤1、2略3（用IF函数做出此功能）插入→函数→if→确定
Logical-test输入：
b3>5000.value-if-true输入if（b3>=10000，“微波炉”，“DVD”）
value-if-false输入收音机→确定。
最后函数：
=（IF（B3>5000，IF（B3>=10000,“微波炉”，“DVD”）,“收音机”）
Sheet2
步骤1、2、3略4,、插入→函数→if→第一行输入B4+C4-D4-E4>0、第二行输入节约、第三行输入超支。
选定G4单元格下拉至G16。
14、利润分配表练习略
15.制表练习
1-4略注：
表头设置：
选中A21：
A22右键→设置单元格格式→对齐→合并单元格→边框→然后单击从左上角至右下角的对角线按钮→确定→输入文字ALT+回车键换行，利用空格键调整文字位置。
5.月折旧额=固定资产原值*月折旧率
12.财务函数练习
Sheet1：
直线法1（SLN函数）
已知某固定资产原值为200000，预计残值为20000，预计使用年限为5年。
请按直线法计算各年折旧及累计折旧（将公式定义在下表中）。
点击D3单元格→插入→函数→函数类别选“财务”→SLN→Cost（原值）输入200000，Salvage（预计净残值）输入20000，Life（使用年限）输入5→确定。
因为直线折旧法下每年折旧额相等，所以复制D3结果至D5、D7、D9、D11。
将D3复制到D4利用求和函数D6=sum（D4：
D5）、D8=sum（D6：
D7）、D10=sum（D8：
D9）、D12=sum（D10：
D11）。
Sheet:
2：
直线法2（SLN函数）
已知：
固定资产原值
200000
预计残值
20000
预计使用年限
5
要求：
请按直线法计算各年折旧及累计折旧（将公式定义在下表中）。
点击B9单元格→插入→函数→函数类别选“财务”→SLN→Cost（原值）选中B2，Salvage（预计净残值）选中B3，Life（使用年限）选中B4→确定。
因为直线折旧法下每年折旧额相等，所以复制B9公式至B10--B13（公式中单元格变成绝对引用）、C9。
C10=sum（B9：
B10）、C11=sum（B9：
B11）、C12=sum（B9：
B12）、C13=sum（B9：
B13）。
Sheet3：
年数总和法1（SYD函数）
已知某固定资产原值为200000，预计残值为20000，预计使用年限为5年。
请按年数总和法计算各年折旧及累计折旧（将公式定义在下表中,结果格式默认）。
点击D3单元格→插入→函数→函数类别选“财务”→SYD→Cost（原值）输入200000，Salvage（预计净残值）输入20000，Life（使用年限）输入5,Period（进行折旧计算的期次即第几期）输入1→确定。
将D3复制到D5、D7、D9、D11中将公式中的Per分别改为2、3、4、5。
将D3复制到D4利用求和函数D6=sum（D4：
D5）、D8=sum（D6：
D7）、D10=sum（D8：
D9）、D12=sum（D10：
D11）。
Sheet4：
年数总和法2（SYD函数）
已知：
升级会员 