Excel解答Word格式.docx
《Excel解答Word格式.docx》由会员分享,可在线阅读,更多相关《Excel解答Word格式.docx(19页珍藏版)》请在冰豆网上搜索。
b.
在I列增加一列“数量”,并求出其数量(=每盒数量*采购盒数);
将鼠标定位在G24,插入数据库函数DSUM(求满足给定条件的数据库中记录的字段(列)数据的和),“=DSUM(A1:
I17,I1,J7:
L8)”,各区域含义同上。
3.某公司对各个部门员工吸烟情况进行统计,作为人力资源搭配的一个数据依据。
对于调查对象,只能回答Y(吸烟)或者N(不吸烟)。
根据调查情况,制做出Sheet3。
请使用函数,统计符合以下条件的数值。
a.统计未登记的部门个数;
b.统计在登记的部门中,吸烟的部门个数。
a.将鼠标定位在B14,插入函数COUNTBLANK,“=COUNTBLANK(B2:
E11)”
b.将鼠标定位在B15,插入函数COUNTIF,“=COUNTIF(B2:
E11,"
y"
)”
4.使用函数,对Sheet3中的B21单元格中的内容进行判断,判断其是否为文本,如果是,结果为“TRUE”;
如果不是,结果为“FALSE”,并将结果保存在Sheet3中的B22单元格当中。
将鼠标定位在B22,插入函数ISTEXT(检测一个值是否为文本,返回TRUE或FALSE),“=ISTEXT(B21)”。
5.将Sheet1复制到Sheet4中,对Sheet4进行高级筛选,要求:
a.筛选条件:
“产品为白炽灯,商标为上海”,并将结果保存;
b.将结果保存在Sheet4中。
将Sheet1复制到Sheet4。
建立条件区域:
产品
商标
白炽灯
上海
执行“插入”|“高级筛选”,选择列表区域和条件区域,确定即可。
6.根据Sheet1的结果,在Sheet5中创建一张数据透视表,要求:
a.显示不同商标的不同产品的采购数量;
b.行区域设置为“产品”;
c.列区域设置为“商标”;
d.计数项为“采购盒数”。
执行“数据”|“数据透视表和数据透视图”,按图2.1所示选择“数据源类型”和“报表类型”。
图2.1选择“数据源类型”和“报表类型”
单击“下一步”,选定区域,如图2.2所示。
图2.2选定数据源区域
单击“下一步”,选择数据透视表的显示位置。
在Sheet5中单击A18单元格。
图2.3选择数据透视表的显示位置
单击“完成”,即出现如图2.4所示的空的数据库透视表及相关工具。
图2.4空的数据库透视表及相关工具
将图2.4中“数据透视表字段列表”中的“产品”拖到“行字段”,将“商标”拖到“列字段”,将“采购盒数”拖到“数据项”。
最后的数据透视表效果如图2.5所示。
图2.5数据透视表效果图
E2-房产销售表
1.利用公式,计算Sheet1中的房价总额。
房价总额的计算公式为:
“面积*单价”
将鼠标定位在I3,输入公式“=F3*G3”,自动填充。
2.使用数组公式,计算Sheet1中的契税总额。
契税总额的计算公式为:
“契税*房价总额”
将鼠标定位在J3,输入公式“=I3*J3”,并以“Ctrl+Shift+Enter”结束该公式的输入,自动填充。
3.使用函数,根据Sheet1中的结果,统计每个销售人员的销售总额,将结果保存在Sheet2中的相应的单元格中。
将鼠标定位在Sheet2的B2,插入函数“=SUMIF(Sheet1!
K3:
K26,"
B2"
Sheet1!
I3:
I26)”,自动填充。
4.使用RANK函数,根据Sheet2的结果,对每个销售人员的销售情况进行排序,并将结果保存在“排名”列当中。
将鼠标定位在Sheet2的B3,插入函数“=RANK(B2,$B$2:
$B$6,0)”,自动填充。
5.将Sheet1复制到Sheet3中,并对Sheet3进行高级筛选,要求:
a.筛选条件为:
“户型”为两室一厅,“房价总额”>
1000000;
b.将结果保存在Sheet3中。
将Sheet1复制到Sheet3。
户型
房价总额
两室一厅
>
1000000
6.根据Sheet1的结果,创建一张数据透视图Chart1,要求:
a.显示每个销售人员销售房屋所缴纳契税总额;
b.行区域设置为“销售人员”;
c.计数项设置为契税总额;
d.将对应的数据透视表保存在Sheet4中。
执行“数据”|“数据透视表和数据透视图”,按图2.6所示选择“数据源类型”和“报表类型”。
图2.6选择“数据源类型”和“报表类型”
单击“下一步”,选定区域,如图2.7所示。
图2.7选定数据源区域
单击Sheet4工作表标签选择数据透视表的显示位置。
图2.8选择数据透视表的显示位置
单击“完成”,即出现如图2.9所示的空的数据库透视表及相关工具
图2.9空的数据库透视表及相关工具
将图2.9中“数据透视表字段列表”中的“销售人员”拖到“分类字段”,将“契税总额”拖到“数据项”。
最后的数据透视图效果如图2.10所示。
(注:
数据透视图在Chart1生成的同时在Sheet5中生成了数据透视表)
图2.10数据透视图效果图
E3-电话号码
1.使用时间函数,对Sheet1中用户的年龄进行计算。
要求:
计算用户的年龄,并将其计算结果填充到“年龄”列当中。
将鼠标定位在D2,插入函数“=YEAR(NOW())-YEAR(C2)”,自动填充即可。
2.使用REPLACE函数,对Sheet1中用户的电话号码进行升级。
对“原电话号码”列中的电话号码进行升级。
升级方法是在区号(0571)后面加上“8”,并将其计算结果保存在“升级电话号码”列的相应单元格中。
将鼠标定位在G2,插入文本函数REPLACE(将一个字符串中的部分字符用另一个字符串替换),“=REPLACE(F2,5,0,8)”(其中,F2是Old_text,要进行字符替换的文本;
5是Start_num,要替换为new_text的字符在old_text中的位置;
0是Num_chars,要从old_text中替换的字符个数,8是New_text,用来对old_text中指定字符串进行替换的字符串),自动填充即可。
3.使用逻辑函数,判断Sheet1中的“大于等于40岁的男性”,将结果保存在Sheet1中的“是否>
=40男性”。
将鼠标定位在H2,插入条件函数IF,“=IF(AND(D2>
=40,B2="
男"
),"
是"
"
否"
)”,自动填充即可。
4.对Sheet1中的数据,根据以下条件,利用函数进行统计:
a.统计性别为“男”的用户人数,将结果填入Sheet2的B2单元格中;
b.统计年龄为“>
40”岁的用户人数,将结果填入Sheet2的B3单元格中。
a.将鼠标定位在Sheet2的B2,插入函数COUNTIF(计算某个区域中满足给定的单元格数目),“=COUNTIF(Sheet1!
B2:
B37,"
)”。
(其中,“Sheet1!
B37”是Range,要计算其中非空单元格数目的区域;
“男”是Criteria,以数字、表达式或文本形式定义的条件。
b.将鼠标定位在Sheet2的B3,插入函数“=COUNTIF(Sheet1!
D2:
D37,"
40"
5.将Sheet1复制到Sheet3,并对Sheet3进行高级筛选。
a.筛选条件为:
“性别”-女、“所在区域”-西湖区;
b.将筛选结果保存在Sheet3中。
性别
所在区域
女
西湖区
6.根据Sheet1的结果,创建一数据透视图Chart1,要求:
a.显示每个区域所拥有的用户数量;
b.x坐标设置为“所在区域”;
c.计数项为“所在区域”;
d.将对应的数据透视表保存在Sheet4中。
(参见E2-6)
E4-气温
1.使用IF函数,对Sheet1中的“温度较高的城市”列进行。
将鼠标定位在D2,插入IF函数,“=IF(B2>
C2,"
杭州"
上海"
)”,自动填充。
2.使用数组公式,对Sheet1中的相差温度值(杭州相对于上海的温差)进行填充。
将鼠标定位在E2,输入公式“=B2-C2”,并以“Ctrl+Shift+Enter”结束该公式的输入,自动填充。
3.利用函数,根据Sheet1中的结果,符合以下条件的进行统计。
a.杭州这半个月以来的最高温度和最低温度;
b.上海这半个月以来的最高温度和最低温度。
a.将鼠标定位在C18,插入函数MAX,“=MAX(B2:
B16)”;
将鼠标定位在C19,插入函数MIN,“=MIN(B2:
B16)”。
b.将鼠标定位在C20,插入函数MAX,“=MAX(C2:
C16)”;
将鼠标定位在C21,插入函数MIN,“=MIN(C2:
C16)“。
4.将Sheet1复制到Sheet2中,在Sheet2中,重新编辑数组公式,将Sheet2中的“相差的温度值”中的数值取其绝对值(均为正数)。
将Sheet1复制到Sheet2,将鼠标定位在Sheet2的E2,插入函数ABS,“=ABS(B12-C12)”,自动填充。
5.将Sheet2复制到Sheet3中,并对Sheet3进行高级筛选,要求:
筛选条件:
“杭州平均气温”>
=20,“上海平均气温”<
20。
将Sheet2复制到Sheet3。
杭州平均气温
上海平均气温
=20
<
20
6.根据Sheet1中的结果,在Sheet4中创建一张数据透视表,要求:
a.显示杭州气温高于上海气温的天数和上海气温高于杭州气温的天数;
b.行区域设置为“温度较高的城市”;
c.计数项设置为温度较高的城市。
(参见E1-6)
E5-员工情况
1.使用REPLACE函数,对Sheet1中的员工代码进行升