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

1.2 条件格式：选定需要的全部数据单元格 格式条件格式 （例：使表格中大于某个值的数据变为红色、加粗） 1.2 查找替换：选中需要替换（例：m男、f女）全部单元格 编辑查找（替换）替换 1.2 条件函数 例题：根据评价标准，显示出各部门业绩及评价结果 点击函数按钮，找到 if 函数，选定 c3， （从最大的数值开始！ ！ ！ ） 在函数对话框第一条里输入 b3=400000，第二条里输入”很好”(英文状态双引号) 光标放在第三条输入框内，单击左上角 if，输入第二层函数参数 在新的函数对话框第一条里输入 b3=300000，第二条里输入”好” 光标放在第三条输入框内，单击左上角 if，输入第三层函数参数 在新的函数对话框第一条里输入 b3=200000，第二条里输入”一般” 光标放在第三条输入框内，单击左上角 if，输入第四层函数参数 在新的函数对话框第一条里输入 b3200000，第二条里输入”差” 光标放在第三条输入框内，最后单击确定，其他部门的拖动复制即可 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 -30000 1 10000 15000 20000 2 8000 10000 15000 3 5000 10000 10000 净 现 值 内含报酬率 要求：计算各项目的净现值（假定利率为 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*b5b4*b5 工具单变量求解 目标单元格：b6 目标值：15000 可变单元格：b5（所要求的销量） 4.1 单变量求解（时间） 例题：计算当购买一辆汽车时，如果月还款 1539.29 元，则贷款额度为多少？（贷款额度用 负整数表示） 先在目标单元格（c8）定义公式 年金公式 pmt：各期利率 c7/12（月利率） 总贷款期 c6 贷款额 c5 工具单变量求解 目标单元格：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+d8 2.进行规划求解 1）工具规划求解，目标单元格 b9（总利润最大值） ，选中最大值按钮 可变单元格 b7:d7(甲、乙、丙各自最佳产量) 2）添加，约束条件“实际工时”小于等于“最高工时” 3）求解，选中“保存规划求解结果”选项，确定 4.4 方案管理器 量本利分析 项目 数值 单价 100 变动成本 30 固定成本 3000 保本点销量 盈亏平衡点 工具方案添加 选择可变单元格，确定，输入变量值 全部完成后点击摘要，选择输出地址确定 指定名称：插入名称指定选最左列确定 斜线表头：格式单元格边框 数字文本 先把文字全输入，然后 alt+enter 强制换行，一行变两行 对齐水平居中、垂直居中 再用空格调整 两行互换：选中一行 鼠标放在该行上