计算机一级考试资料：Excel操作题

计算机一级考试资料：Excel操作题F00001 "请在Excel中对所给工作表完成以下操作:1.在Sheet1表格中，设置(A1:B1)单元格的标题字体“某电子商城全年销售产品统计”为黑体，字号为16磅，将累计销售额(B2)单元格填充黄色(第四行第三列色块)底纹，图案为12.5%灰色;2.将工作表Sheet1改名为“全年销售统计表”;3.在“全年销售统计表”中计算累计销售额，累计销售额等于上半年、下半年销售额之和(用求和函数计算);4.将累计销售额所在列(B3:B8)的数字格式设置为货币型(,)，3位小数;5.将累计销售额所在单元格的列宽设为最""file,189,,ks_direction,Excelst.xls," "107~B4~19.88~累计销售额列设为最适合列宽~10~~0~108~A2~16~行高设为16~10~~0~150~1~全年销售统计表~工作表改名~10~~0~100~B4~=SUM(上半年!C4,下半年!C4)~用Sum函数求累计销售额~20~~0~101~B4~,#,##0.000;,-#,##0.000~设置单元格数据类型为货币型，保留3位小数~10~~0~ 20~A3:B7-5~1~内边框~10~~0~20~A2:B8-3~1~外边框~10~~0~21~A3:B7-5~255~设置"F00002 "请在Excel中对所给工作表完成以下操作:1.将(A1:G1)单元格合并并居中，行高设为最适合行高，数据区域(A2:G10)设置为水平居中，垂直居中;2.计算职工的奖金/扣除列(使用IF函数，条件是:实际销售量>=12月份应销售量，条件满足的取值800，条件不满足的取值-300);3.计算月收入(月收入=基本工资+奖金/扣除)，将(G3:G10)单元格的数字格式设为货币(,)，不保留小数;4.将工作表Sheet1改名为“职工收入”;5.将12月份应销售量(D列)的列宽设置为15;6.为表格加单" "file,188,,ks_direction,Excelst.xls,""58~1~-4107~图例放置于底部~10~~0~100~F6~=IF(C6>=D6,800,-300)~计算奖金/扣除列~20~~0~100~G6~=E6+F6~月收入的计算~10~~0~20~A2:G10-3~1~单实线外边框~10~~0~150~1~职工收入~工作表的表名~10~~0~107~D5~15~D列列宽设为15~10~~0~101~G7~,#,##0;,-#,##0~数字格式设为货币不保留小数~10~~0~108~a1:G1~15.75~设为最适合行高~10~~0~102~"F00003 "请在Excel中对所给工作表完成以下操作:1.合并(A1:H1)单元格区域，文本字体设为楷体_GB2312，文本内容垂直居中对齐;2.将工作表表名改为“批发市场销售表”;3.分别使用函数计算下半年销售额合计和各类商品每月销售额最大值;4.选择猪肉、羊肉和牛肉三行(不包括下半年销售额合计)制作三维柱形图，图表的标题为“肉类销售表”，图例放在底部;5.为“各类商品每月销售最大值”一行中的(B10:G10)区域填充红色(第三行第一列色块)the direct punishment 0.5-10,000 yuan, responsible for thedirect responsibility for the accident and the responsibility of leadership, such as concerning administrative sanctions should be dealt with by the personnel Department of the company. (3) personal injury accident occurs, the direct punishment 500-1000, who is directly responsible for the accident responsibility, give notice of criticism and 50-100 economic sanctions against them. (4) to conceal the accident, reported without undue delay or false, to inform the administrative leadership of the criticism, resulting in serious consequences, the pursuit of leadership, along with 500-1000 punishment. (5) significant near miss should be attempted as the case of responsible for the accident and construction team injuries accident penalty provisions, mutatis mutandis. Eight, should perform in the construction standards and specifications, serial number a 1 GB3323-2005 steel fusion welded butt joints, welding engineering-Ray lighting and quality rating of 2 GB11345-89 steel welds manual methods of ultrasonic inspection and testing results for grade 3 GB50236-2002 industrial pipe welding engineering code for construction and acceptance of field equipment 4 HGJ222-92 technical specification for welding of aluminium and itsalloys 5 low temperature steel welding procedure 6 SH3525-2004 petrochemical JB/ T4708-2000 of welding procedure qualification forsteel pressure vessels 7 JB/4709-2000 8 JB4730-2005 pressure vessel 底纹，25%灰色图案;6.将A10单元格设置为自动换行;7." "file,195,,ks_direction,Excelst.xls,""103~A1:H1~-4108~A1:H1垂直居中对齐~10~~0~104~A1:H1~True~合并A1:H1~10~~0~57~1~=SERIES(批发市场销售表!$A$4,,批发市场销售表!$B$4:$G$4,1)~图表由猪肉、羊肉和牛肉三行构成~10~~0~11~A1:H1~楷体_GB2312~字体设为楷体_GB2312~10~~0~ 50~1~肉类销售表~图表的标题为“销售业绩表”~10~~0~58~1~-4107~图例放在底部~10~~0~51~1~-4100~图表的类型~10~~0"F00004 "请在Excel中对所给工作表完成以下操作:1.在A2单元格左边插入一个单元格，输入“2012年”，然后将A2与B2单元格旋转30度;2.将学号列数字类型设为文本型;3.利用求和函数计算每个同学的总分，将数据区域(A3:F16)按总分降序排序;4.在A17单元格添加文字“平均分”，合并(A17:E17)单元格，文字右对齐，在F17单元格利用函数计算总平均分，保留1位小数;5.将数据区域(A3:F17)加“田”字型表格边框，要求边框颜色为红色(第3行第1列色块)、线型为细实线。

" "file,191,,ks_direction,Excelst.xls,""10~A2~2012年~判断是否插入单元格,~10~~0~10~B2~1月14日~判断插入新单元格,原单元格右移~10~~0~10~A17~平均分~A17单元格添加文字“平均分”~10~~0~104~A17:E17~True~合并(A17:E17)单元格~10~~0~102~A17:E17~-4152~文字右对齐~10~~0~21~A17:F17-1~255~边框为红色~10~~0~100~F17~=AVERAGE(F4:F16)~计算总平均分~10~~0~101~F17~0.0_ ~保留1"F00005 "请在Excel中对所给工作表完成以下操作:1. 将标题行A1:G1合并居中,字体设为楷体_GB2312，24磅，倾斜;2. 为标题区域(A1:G1)设置黄色(颜色中第四行第三列色块)底纹，图案为细对角线条纹; 3. 利用平均函数求每个同学各门课的平均成绩，数字格式设为数值型，保留1位小数; 4. 利用函数求总评，(条件为:平均>=60，满足的为""合格""，不满足的为""不合格""); 5. 表格(A1:G8)加红色双细实线外边框;6. 选择姓名和平均两列制作三维柱形图。

""file,192,,ks_direction,Excelst.xls," "14~A1:G1~True~文字倾斜~10~~0~ 57~1~=SERIES(Sheet1!$F$2,Sheet1!$B$3:$B$8,Sheet1!$F$3:$F$8,1)~图表包括范围~10~~0~ 51~1~-4100~制作三维柱形图~10~~0~20~A2:G8-4~-4119~双线边框~10~~0~104~A1:G1~True~合并居中~10~~0~100~G6~=IF(F6>=60,""合格"",""不合格"")~利用if函数求出总评~20~~0~101~F6~0.0_ ~设置保留1位小数~1"F00006 "请在Excel中对所给工作表完成以下操作:1.将工作表Sheet1的名称修改为“成绩单”;2.将标题行(A1:F1)合并及居中，标题文本字号设为16磅，行高设为24; the direct punishment 0.5-10,000 yuan, responsible for the direct responsibility for the accident and the responsibility of leadership, such as concerning administrative sanctions should be dealt with by the personnel Department of the company. (3) personal injury accident occurs, the direct punishment 500-1000, who is directly responsible for the accident responsibility, give notice of criticism and 50-100 economic sanctions against them. (4) to conceal the accident, reported withoutundue delay or false, to inform the administrative leadership of the criticism, resulting in serious consequences, the pursuit of leadership, along with 500-1000 punishment. (5) significant near miss should be attempted as the case of responsible for the accident and construction team injuries accident penalty provisions, mutatis mutandis. Eight, should perform in the construction standards and specifications, serial number a 1 GB3323-2005 steel fusion welded butt joints, welding engineering-Ray lighting and quality rating of 2 GB11345-89 steel welds manual methods of ultrasonic inspection and testing results for grade 3 GB50236-2002 industrial pipe welding engineering code for construction and acceptance of field equipment 4 HGJ222-92 technical specificationfor welding of aluminium and its alloys 5 low temperature steel welding procedure 6 SH3525-2004 petrochemical JB/ T4708-2000 of welding procedure qualification for steel pressure vessels 7 JB/4709-2000 8JB4730-2005 pressure vessel3.用Sum函数计算所有学生个人总成绩，用Average函数在(B12:E12)单元格计算每门课平均分;4.将所有的数字单元格(B4:F12)数字格式设置为数值，无小数位;5.将(A3:F11)的成绩数据按个人总成绩降序排列;6.为表格加双线外边框和细实线内边框;7.选择(A3:F11)绘制数据点折线图，图表标题为“成绩分析表”""file,197,,ks_direction,Excelst.xls," "10~f9~288 ~按降序排列~10~~0~ 101~c7~#,##0_ ~设置B4:F12的数字格式~10~~0~57~1~=SERIES(成绩单!$B$3,成绩单!$A$4:$A$11,成绩单!$B$4:$B$11,1)~图表范围~10~~0~ 58~1~-4107~图例放置底部~10~~0~50~1~成绩分析表~图表标题为“成绩分析表”~10~~0~100~d12~=AVERAGE(D4:D11)~求平均值~10~~0~51~1~65~图表的类型为折线图~10~~0~100~f6~=SU"F00007 "请在Excel中对所给工作表完成以下操作:1. 为标题行(A1:D1)设置红色(颜色:第三行第一列)底纹，白色文字，字体为黑体;2. 使用公式计算总提成(总提成=数量*单价*销售提成比例，销售提成比例单元格的引用使用绝对引用);3. 将所有数据区域(A3:D9)按总提成降序排序;4. 将总提成区域(D4:D9)数字格式设置为数值，保留两位小数位，列宽设为最适合列宽;5. 为数据区域(A3:D9)设置双线外边框;6. 选择产品代码和总提成两列制作饼图，图表标题为“公司产品月销售提成”，图例放在右" "file,193,,ks_direction,Excelst.xls," "13~A1~16777215~字体颜色为白色~10~~0~30~A1~255~添加红色底纹~10~~0~11~A1~黑体~字体为黑体~10~~0~20~A3-1~-4119~设置表格外框线为双线~10~~0~107~D5~8.88~设为最适合列宽~10~~0~101~D6~0.00_ ~设置单元格的格式~10~~0~100~D7~=B7*C7*$D$2~计算总提成(使用绝对地址)~20~~0~ 10~D5~5385.60 ~排序判断1~10~~0~10~C7~13.4~排序判断2~10~~0"F00008 "请在Excel中对所给工作表完成以下操作:1.为标题行(A1:G1)设置红色(第三行第一列色块)底纹、白色文字;2.将区域(C3:G8)数字类型设置为数值，保留1位小数位;3.用函数计算各地区四个季度销售之和，放在合计列(G3:G8)区域;4.将数值区域(A2:G8)按合计降序排序;5.将工作表Sheet1改名为“分区销售情况表”;6.选择地区和合计两列制作饼图，图表标题为“分区销售统计”，图例放在底部;7.为表格加双线外边框和细实线内边框;8.将(A9:B9)单元格合并，用函数计算各季度""file,198,,ks_direction,Excelst.xls," "13~A1:G1~16777215~字体颜色为白色~10~~0~ 20~A3:G8-5~1~细实线内边框~10~~0~30~A1:G1~255~添加红色底纹~10~~0~the direct punishment 0.5-10,000 yuan, responsible for the direct responsibility for the accident and the responsibility of leadership, such as concerning administrative sanctions should be dealt with by the personnel Department of the company. (3) personal injury accident occurs, the direct punishment 500-1000, who is directly responsible for the accident responsibility, give notice of criticism and 50-100 economic sanctions against them. (4) to conceal the accident, reported without undue delay or false, to inform the administrative leadership of the criticism, resulting in serious consequences, the pursuit of leadership, along with 500-1000 punishment. (5) significant near miss should be attempted as the case of responsible for the accident and construction team injuries accident penalty provisions, mutatis mutandis. Eight,should perform in the construction standards and specifications, serial number a 1 GB3323-2005 steel fusion welded butt joints, welding engineering-Ray lighting and quality rating of 2 GB11345-89 steel welds manual methods of ultrasonic inspection and testing results for grade 3 GB50236-2002 industrial pipe welding engineering code for construction and acceptance of field equipment 4 HGJ222-92 technical specification for welding of aluminium and its alloys 5 low temperature steel welding procedure 6 SH3525-2004 petrochemical JB/ T4708-2000 of welding procedure qualification for steel pressure vessels 7 JB/4709-2000 8JB4730-2005 pressure vessel101~F6~0.0_ ~设置单元格的格式~10~~0~58~1~-4107~图例放在底部~10~~0~100~G7~=SUM(C7:F7)~计算总计~10~~0~10~D5~186.0 ~排序判断~10~~0~20~A2:G9-4~-4119~双线外边框~10~~0~100~D9~=AVERAGE(D3:D8)~计算平均值"F00009 "请在Excel中对所给工作表完成以下操作:1.设置标题行(A1:E1)合并及水平居中，并设置标题行为红色底纹(颜色中第三行第一列色块)和黄色文字;2.计算2011年销量(2011年销量=2010年销量*(1+增长比例));3.选择产品名称和2011年销量两列制作堆积柱形图;4.将当前工作表改名为“年销售情况表”;5.计算2011年销售额，已知2011年各产品的平均售价放置在Sheet2工作表内(2011年销售额=2011年销量*2011年平均售价);6.为表格(A2:E6)设置双线外边框和蓝色""file,196,,ks_direction,Excelst.xls," "104~A1:E1~True~单元格合并及居中~10~~0~ 13~A1:E1~65535~标题行字体为黄色~10~~0~30~A1:E1~255~标题行底纹为红色~10~~0~100~d4~=B4*(1+C4)~计算2011年销量~20~~0~101~E4~,#,##0.00;,-#,##0.00~格式设为货币，保留2位小数~10~~0~21~B3:C4-5~16711680~蓝色内边框~10~~0~20~B3:C4-5~1~细实线内边框~10~~0~20~A2:E6-3~-4119~双线外"中对所给工作表完成以下操作: F00010 "请在Excel1.将工作表Sheet1命名为“专业人数统计表”;2.在“专业人数统计表”中用公式计算专业人数(专业人数=班级数*每班人数);3.设置C8单元格的文本控制方式为“自动换行”;4.在D8单元格内用条件求和函数SUMIF计算所有专业人数超过 100人(包含100人)的专业人数之和;5.设置D8单元格的格式为水平居中对齐和垂直居中对齐;6.在“专业人数统计表”中(A1:D6)区域按专业人数降序排序;7.为表格加双实线外边框和细实线内边框;8.根据“专业”" "file,194,,ks_direction,Excelst.xls," "150~1~专业人数统计表~工作表的标题~10~~0~100~D4~=B4*C4~用公式计算专业人数~10~~0~106~C8~True~C8单元格自动换行~10~~0~20~A1:D6-4~-4119~双实线外边框~10~~0~100~D8~=SUMIF(D2:D6,"">=100"",D2:D6)~超过 100人的专业人数之和~20~~0~ 102~D8~-4108~D8单元格水平对齐~10~~0~103~D8~-4108~D8单元格垂直对齐~10~~0~10~d4~124~按专业人数降序排序"F00011 "请在Excel中对所给工作表完成以下操作:1. 将单元格区域(A1:F1)合并、居中，行高设为16磅，加红色(颜色中第三行第一列)底纹、12.5%的灰色图案;the direct punishment 0.5-10,000 yuan, responsible for the direct responsibility for the accident and the responsibility of leadership, such as concerning administrative sanctions should be dealt with by the personnel Department of the company. (3) personal injury accident occurs, the direct punishment 500-1000, who is directly responsible for the accident responsibility, give notice of criticism and 50-100 economic sanctions against them. (4) to conceal the accident, reported without undue delay or false, to inform the administrative leadership of the criticism, resulting in serious consequences, the pursuit of leadership, along with 500-1000 punishment. (5) significant near miss should be attempted as the case of responsible for the accident and construction team injuries accident penalty provisions, mutatis mutandis. Eight, should perform in the construction standards and specifications, serial number a 1 GB3323-2005 steel fusion welded butt joints, welding engineering-Ray lighting and quality rating of 2 GB11345-89 steel welds manual methods of ultrasonic inspection and testing results for grade 3 GB50236-2002 industrial pipe welding engineering code for constructionand acceptance of field equipment 4 HGJ222-92 technical specification for welding of aluminium and its alloys 5 low temperature steel welding procedure 6 SH3525-2004 petrochemical JB/ T4708-2000 of welding procedure qualification for steel pressure vessels 7 JB/4709-2000 8JB4730-2005 pressure vessel2. 将A9单元格文本控制方式设为自动换行;3. 利用平均值函数计算各类产品的平均值，数字格式设为货币(,)，保留两位小数;4. 将数据区域(A2:F9)按销售平均值(第9行)降序排序;5. 选择销售材料和销售平均值两行制作簇状柱形图，图表标题为“销售平均值”，图例设置在底部;6. 为表格(A2:F9)添加“田”字型细实线边框。