计算机职称考试 excel上机考试常见题型及解答.doc

1.2条件格式：选定需要的全部数据单元格 格式-条件格式 （例：使表格中大于某个值的数据变为红色、加粗）1.2查找替换：选中需要替换（例：M-男、F-女）全部单元格 编辑-查找（替换）-替换1.2条件函数例题：根据评价标准，显示出各部门业绩及评价结果点击函数按钮，找到IF函数，选定C3，（从最大的数值开始！）在函数对话框第一条里输入B3>=400000，第二条里输入”很好”(英文状态双引号)光标放在第三条输入框内，单击左上角IF，输入第二层函数参数 在新的函数对话框第一条里输入B3>=300000，第二条里输入”好”光标放在第三条输入框内，单击左上角IF，输入第三层函数参数在新的函数对话框第一条里输入B3>=200000，第二条里输入”一般”光标放在第三条输入框内，单击左上角IF，输入第四层函数参数在新的函数对话框第一条里输入B3<200000，第二条里输入”差”光标放在第三条输入框内，最后单击确定，其他部门的拖动复制即可2.1时间价值函数：（1）分别计算每年偿还的本金和利息（PPMT定期偿还、固定利率下，给定期次内某项投资回报或偿还贷款的本金部分）（IPMT定期偿还、固定利率下，给定期次内某项投资回报或偿还贷款的利息部分）例：某人购买商品房，现贷款180000元，复利年利率6%。如果要在未来5年内等额偿还贷款本金及利息（每年年末支付），每年需要偿还多少钱？每年的还款中本金和利息各是多少？ 单击目标单元格-单击函数按钮-财务函数（PPMT）本金（PPMT）=PPMT（各期利率，期次，总贷款期，贷款额） 单击目标单元格-单击财务按钮-财务函数（IPMT）利息（IPMT）=IPMT（各期利率，期次，总贷款期，贷款额）（2）年金函数（PMT）：贷款金额200000，贷款利率5%，年限5年。利用年金函数计算每年应偿还多少钱。本息偿还额（PMT）=PMT（各期利率，总贷款期，贷款额）2.2投资决策函数例：投资评价期间A项目B项目C项目0-10000-20000-300001100001500020000280001000015000350001000010000净 现 值内含报酬率要求：计算各项目的净现值（假定利率为10%）和内含报酬率（请将公式定义在上表中,保留2位小数）。单击目标单元格-单击函数按钮-财务函数（净现值NPV）净现值（NPV）=NPV（贴现率，各期收入）+前期支出单击目标单元格-单击财务按钮-财务函数（内含报酬率IRR）内含报酬率（IRR）=IRR（各期支出和收入）2.3折旧函数：（1）固定余额递减法（DB）计算各年折旧、累计折旧 单击目标单元格-单击函数按钮-财务函数DB 各年折旧（DB）=DB（原值，预计残值，预计使用年限，期次，第一年月份数默认为12） 当期累计折旧=本期折旧+上期累计折旧 （2）年数总和法（SYD）计算各年折旧、累计折旧 单击目标单元格-单击函数按钮-财务函数（SYD） 各年折旧（SYD）=SYD（原值，估计残值，预计使用年限，期次） 当期累计折旧=本期折旧+上期累计折旧 （3）直线法（SLN）计算各年折旧、累计折旧 单击目标单元格-单击函数按钮-财务函数（SLN），每年折旧金额相等 各年折旧（SLN）=SLN（原值，估计残值，预计使用年限） 当期累计折旧=本期折旧+上期累计折旧（4）双倍余额递减法（DDB） 各年折旧（DDB）=DDB（原值，估计残值，预计使用年限，余额递减速率默认为2）（5）特定或部分时期的折旧额（VDB）折旧（VDB）=VDB（原值，残值，使用期限，折算开始期次，折算结束期次，余额抵减速率，逻辑值）逻辑值是指折旧额超过用余额递减法计算的水品是，是否转换为直线折旧法3.1排序选中目标单元格区域-数据-排序3.2高级筛选：在列表下方建立筛选条件区域，列出条件（注意） 数据-筛选-高级筛选，方式-将筛选结果复制到其他位置 列表区域-选择给出的表格内容的区域 条件区域-选择自己建立的区域 复制到-选择筛选结果要复制到的区域3.3分类汇总选中目标单元格区域-数据-分类汇总3.4数据透视表：选定全部数据（包括表头文字） 数据-数据透视表和数据透视图-向导1 待分析数据源类型（MS Excel），创建的报表类型（数据透视表），下一步向导2键入或选定数据源区域，下一步向导3透视表显示位置 新建工作表or现有工作表（在当前表选定输出位置）完成 将字段拖动到透视表上，注意：行字段位置竖着的，列字段位置横着的4.1单变量求解（利润）：例题：利用单变量求解计算，当销量为多少时，利润能达到15000先在目标单元格（B6）定义公式=B3*B5-B4*B5工具-单变量求解目标单元格：B6 目标值：15000 可变单元格：B5（所要求的销量）4.1单变量求解（时间）例题：计算当购买一辆汽车时，如果月还款1539.29元，则贷款额度为多少？（贷款额度用负整数表示）先在目标单元格（C8）定义公式年金公式PMT：各期利率C7/12（月利率） 总贷款期C6 贷款额C5工具-单变量求解待添加的隐藏文字内容3目标单元格：C8 目标值：1539.29 可变单元格：C5（所要求的贷款额度）4.2模拟运算表（单变量）例题先在指定单元格（D4）定义计算公式（年金函数PMT）选定包括定义单元格在内的数据列区域（D4：E14）数据-模拟运算表 引用行单元格 选定行所要引用内容的单元格（B4年限）引用列单元格 选定列所要引用内容的单元格（B5利率）以上在期限不变（10年），利率变化条件下，求月还款金额，称为单变量模拟运算表4.2模拟运算表（双变量）例题先在指定单元格（A4）定义计算公式（年金函数PMT） 选定包括定义单元格在内的表格区域（A4：F9） 数据-模拟运算表 引用行单元格 选定行所要引用内容的单元格（B2利率） 引用列单元格 选定列所要引用内容的单元格（B3年限）以上在年限和利率都在变化条件下，求年还款金额，称为双变量模拟运算表4.3规划求解例：已知某公司甲、乙、丙三种产品的单位利润和三个车间的工时限额，求生产甲、乙、丙各多少可使企业利润最大？1.输入计算公式1） 计算利润小计，B8=B6*B7，C8、D8同理2） 计算实际工时，E2=$B$7*B2+$C$7*C2+$D$7*D2，E3、E4同理3） 计算总利润，B9=B8+C8+D82.进行规划求解1）工具-规划求解，目标单元格B9（总利润最大值），选中最大值按钮 可变单元格B7:D7(甲、乙、丙各自最佳产量)2）添加，约束条件“实际工时”小于等于“最高工时”3）求解，选中“保存规划求解结果”选项，确定4.4方案管理器 量本利分析项目数值单价100变动成本30固定成本3000保本点销量盈亏平衡点 工具-方案-添加 选择可变单元格，确定，输入变量值全部完成后点击摘要，选择输出地址-确定指定名称：插入-名称-指定-选最左列-确定斜线表头：格式-单元格-边框 数字-文本 先把文字全输入，然后Alt+Enter强制换行，一行变两行 对齐-水平居中、垂直居中 再用空格调整两行互换：选中一行 鼠标放在该行上沿，光标呈方向十字 按住Shift拖动鼠标，当左边数字呈X:X时放手输出结果用负数表示：右键设置单元格-数值型-选带负号的示例设置（没有的）自动填充项：工具-选项-自定义序列-新序列等差数列：在两个单元格输入两个数，同时选中两个单元格，填充柄下拉等比数列：编辑-填充-序列，选等比数列，输入步长数据求和SUMIF函数