最全+Excel操作步骤讲义.docx
《最全+Excel操作步骤讲义.docx》由会员分享,可在线阅读,更多相关《最全+Excel操作步骤讲义.docx(37页珍藏版)》请在冰豆网上搜索。
![最全+Excel操作步骤讲义.docx](https://file1.bdocx.com/fileroot1/2023-2/26/8ad20dbb-fa81-4184-b66c-521add979dfe/8ad20dbb-fa81-4184-b66c-521add979dfe1.gif)
最全+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函数)
已知: