aoa三一宝典计算机二级考试Word文档格式.docx

1、1. AND（X条件 ， Y条件， Z条件，）都成立（TRUE），则最终成立。2. OR （X条件， Y条件， Z条件，）有一成立，则最终成立。3. NOT（条件） 反之！ （2）IF函数：IF（判别条件，条件成立（真）时取值，条件不成立（假）时取值）例：=IF（C5（成绩）AVERAGE（$C$2:$C$39）,D2AVERAGE（$D$2:$D$39）,E2AVERAGE（$E$2:$E$39）,TRUE,FALSE）4. 根据Sheet1中的结果，使用统计函数，统计“数学”考试成绩各个 分数段的同学人数，将统计结果保存到Sheet2中的相应位置。条件统计个数的函数COUNTIFCOUNT

2、IF（被统计个数的区域之绝对引用，条件）数学分数位于0到20分的人数：=COUNTIF（Sheet1!$D$2:$D$39,75，“数学”75，“英语”75，“总分”250； b. 将结果保存在Sheet3中。（1）将Sheet1复制到Sheet3中（2）在Sheet3的空白处，自己先建如下条件语文数学英语总分=75=250（3）选中Sheet3中的表格全部。（4）点数据筛选 高级筛选（5）如下图：（6）选中条件区域，成为如下图：确定！注：（a）无需考虑是否删除或移动筛选条件; （b）复制数据表后，粘贴时，数据表必须顶格放置。6. 根据Sheet1中的结果，在Sheet4中创建一张数据透视表，

3、要求： a. 显示是否三科均超过平均分的学生人数； b. 行区域设置为：“三科成绩是否均超过平均”； c. 计数项为三科成绩是否均超过平均。数据透视表： 用“数据透视表和数据透视图向导”选中Sheet1的整个表：数据数据透视表和数据透视图P.选择所创建的数据透视表的数据源类型（会自动出默认）。下一步选择数据源的区域，包括那张表sheet？（没有汇总过的）。“布局”设计将要生成的数据透视表的版式和选项。在以下的图中，按题目要求进行拖放（搞错可拖回去）（二）、服装采购（VLOOKUP函数）.xls1. 使用VLOOKUP函数，对Sheet1中的商品单价进行自动填充。 要求：根据“价格表”中的商品单

4、价，利用VLOOKUP函数，将其单价自动填充到采购表中的“单价”列中。（1）VLOOKUP叫引用函数；一个数组或表格table_array的最左列中含有特定值的字段，到另一数据表格、数组或数据库look_value去查找，找到同值时，把的第col_index_num列的对应值填到的某一指定单元格中。VLOOKUP（lookup_value，talbe_array，col_index_num，range_lookup） look_value：被查找的列的值， 如A11,A12,会相对下去table_array：引用的数据表格、数组或数据库，如$F$2:$G$4 绝对引用。col_index_nu

5、m：一个数字，代表要返回的值位于table_array中的第几列。rang_lookup：一个逻辑值，如果其值为TRUE或被省略，则符合多少算多少；如果该值为FALSE时，函数只会查找完全符合的数值，如果找不到，则返回错误值“N/A”。=VLOOKUP（A11,$F$2:$G$5,2,FALSE）Look表ABCD项目单价11衣服12裤子鞋子第2列table表FG1价格表2312048051502. 使用逻辑函数，对Sheet1中的商品折扣率进行自动填充。根据“折扣表”中的商品折扣率，利用相应的函数，将其折扣率自动填充到采购表中的“折扣“列中。=IF（B11=$A$6,$B$6,IF（B11=

6、$A$5,$B$5,IF（B11=$A$4,$B$4,$B$3）3. 利用公式，计算Sheet1中的“合计金额”。根据“采购数量”，“单价”和“折扣”，计算采购的“合计金额”。计算公式：单价*采购数量*（1-折扣）=D11*B11*（1-E11）4. 使用SUMIF函数，统计各种商品的采购总量和采购总金额，将结果保存在Sheet1中的“统计表”当中。（1）判断条件求和函数SUMIFSUMIF（被判断的区域，判断条件，要求和的列）=SUMIF（A$11:A$43,I12,B$11:B$43） =SUMIF（各种商品，衣服，采购数量）等等，等等！A$43,I12,F$11:F$43） =SUMIF

7、（各种商品，衣服，合） 等等，等等！统计表统计类别总采购量总采购金额28003054245. 对Sheet2中的“采购表”进行高级筛选。a. 筛选条件为：“采购数量”150，“折扣”0；采购数量折扣 b. 将筛选结果保存在Sheet2中。6. 根据Sheet1中的采购表，新建一个数据透视图Chart1，要求： a. 该图形显示每个采购时间点所采购的所有项目数量汇总情况； b. x坐标设置为“采购时间”； c. 求和项为采购数量； d. 将对应的数据透视表保存在Sheet3中。选：数据透视图（及数据透视表）（R） 下一步 重要： 对该题来说：出图后在Sheet3中把“项目”拖到“汇总” （三）、

8、书籍出版（闰年new）（数组公式if）.xls1. 使用数组公式，计算Sheet1中的订购金额，将结果保存到表中的 “金额”列当中。=H2:H51*G2:G512. 使用统计函数，对Sheet1中结果按以下条件进行统计，并将结果保存在Sheet1中的相应位置，要求：a. 统计出版社名称为“高等教育出版社”的书的种类数；=COUNTIF（D2:D51,高等教育出版社b. 统计订购数量大于110且小于850的书的种类数。=COUNTIF（G2:G51,110）-COUNTIF（G2:=8503. 使用函数计算，每个用户所订购图书所需支付的金额总数，将结果保存在Sheet1中的相应位置。SUMIF（

9、A2:A51,K8,I2:I51）等等，等等4. 使用函数，判断Sheet2中的年份是否为闰年，如果是，结果保存“闰年”，如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。 说明：闰年定义：能被400整除的年份，或者年数能被4整除而不能被100整除。取除法后得余数的函数：MOD（被除数，除数）方法:IF（ , 闰年 , 平年OR（ MOD（x，400）=0, ）AND（ MOD（x，4）=0 ,MOD（x，100）0 ）成为：=IF（OR（MOD（A2,400）=0,AND（MOD（A2,4）=0,MOD（A2,100）=500，且金额总数=30000”；订数金额=500=300

10、00b. 将结果保存在Sheet3中。6. 根据Sheet1中的结果，在Sheet4中新建一张数据透视表，要求： a. 显示每个客户在每个出版社所订的教材数目；“出版社”； c. 列区域设置为：“客户”；d. 计数项为订数。（四）、电话升级（时间函数）.xls1. 使用时间函数，对Sheet1中用户的年龄进行计算。计算用户的年龄，并将其计算结果填充到“年龄”列当中。（1）取年份函数：YEAR（date）,取出date的4位年份整数。（2）取当前日期函数：NOW（）=YEAR（NOW（）-YEAR（C2）2. 使用REPLACE函数，对Sheet1中用户的电话号码进行升级。对“原电话号码”列中的

11、电话号码进行升级。 升级方法是在区号（0571）后面加上“8”， 并将其计算结果保存在“升级电话号码”列的相应单元格中。（1）字符串替换函数：REPLACE功能：将某几位的文字以新的字符串替换。其替换功能与SUBSTITUTE函数大致类似 格式：REPLACE（old_text，start_num，num_chars，new_text）old_text：老的文本数据start_num：从第几个字符位置开始替换num_chars：共替换多少字符（1个汉字算2个）new_text：用来替换的新字符串 （2）连字符运算符：&（3）RIGHT（text,num_chars）根据所指定的字符数num_chars返回文本字符串text中右面最后一个或多个字符。=REPLACE（F2,1,4,057183. 使用逻辑函数，判断Sheet1中的“大于等于40岁的男性”，将结果保存在