11彭 颖ppt-excel高级财会运用-彭颖-0

1、 EXCEL高级财会应用2016级MPAcc姓名：彭颖学号:220160909870目录一、公司付款单模版二、收入对比动态图三、考勤表模版设置四、购房决策五、个人所得税模版六、数组公式和VLOOKUP函数结合3一、公司付款单模版公司付款单模版1. 付款单位设置来源处可以直接输入文本， 也可以先在表格中输入文本， 再引用。 相同办法设置 收款单位和付款内容。 设置序列，数据数据有效性序列4公司付款单模版2. 设置大小写金额：小写金额在哪个单元，大写金额设置就相应引用哪个单元格，本例中，小写金额在I10单元格，大写金额处输入公式=IF(ISTEXT(I10),人民币： &TEXT(INT(I10)

2、,dbnum2)&元&IF(INT(I10*10)-INT(I10)*10=0,TEXT(INT(I10*10)-INT(I10)*10,dbnum2)&角)&IF(INT(I10*100)-INT(I10*10)*10=0,整,TEXT(INT(I10*100)-INT(I10*10)*10,dbnum2)&分)。3. 根据需求与喜好设置其他格式。6小结付款单设置其实是小写金额转换成大写金额的应用， 主要运用数据有效性及IF、INT、ISTEXT、TEXT、dbnum2通用格式设置的运用，在打印时注意人民币货币符号与小写金额之间的空格，要进行调整以避免出现空格。7二、收入对比动态图8收入对比

3、动态图1. 进行数据整理分为两个数据源计划收入和实际收入添加辅助列引用序列下拉菜单。12创建窗体控件点击开发工具插入选项按钮（窗体控件）。（2007版）点击选项 自定义功能 不常在功能区。（2010版）点击公式名称管理器。建立名为引用序列下拉菜单的名称，输入公式=IF(Sheet1!$A$10=1,Sheet1!$B$20:$B$25,Sheet1!$A$20:$A$24)插入组合框（窗体控件）设置控件格式。数 据 源 区 域 =sheet1！ 引 用 序 列 下 拉 菜 单 。sheet1！为固定形式引用下拉菜单为定义的名称。这时达到的效果是=IF(Sheet1!$A$10=1,Sheet1

4、!$B$20:$B$25,Sheet1!$A$20:$A$2 4)点击按月份查询的选项控件按钮(即A10=1)组合框控件就显示月份点击分公司查询（A10=2）组合框控件显示分公司。定义名称创建动态数据源X=IF(Sheet1!$A$10=1,Sheet1!$A$20:$A$24,Sheet1!$B$ 20:$B$25)表示当点击按月份查询的选项控件X轴显示月份，否则是分公司。13收入对比动态图继续定义名称计划收入=IF(Sheet1!$A$10=1,OFFSET(Sheet1!$A$13,1,Sheet1!$C$10-1,5,1),OFFSET(Sheet1!$A$13,Sheet1!$C$1

5、0,1,6)实际收入=IF(Sheet1!$A$10=1,OFFSET(Sheet1!$H$13,1,Sheet1!$C$1 0-1,5,1),OFFSET(Sheet1!$H$13,Sheet1!$C$10,1,6)17利用定义名称创建动态图表点击图表工具设计选择数据。（2007版）（2010版）注意此时并未引用任何数据，是一张空白图表轴标签区域=sheet1！X轴sheet1！为固定形式X轴为定 义的名称动态的月份或者公司名数据。系列值=sheet1！计划收入sheet1！为固定形式计划收入为定义的名称是动态的计划收入数据。图表的美化添加标题和数据来源根据表格需要，添加标题，标题尽量简洁，

6、且能够反映表格内容，突出你想表达的观点。收入对比动态图小结收入对比动态的设置主要注意窗体控件的应用，另外， 插入图表时设置数据也需注意，还有名称定义的运用， 有多种方法，且一定不能误，否则后面将不能进行。18三、考勤表模版的设置19考勤表模版的设置1. 制作表格，制作时间窗体控件，以方便选择时间，设置结果如下：2. 在D3单元格输入公式=IF(DATE($V$2,$Z$2,COLUMN(A1)EOMONTH(DATE($V$2,$Z$2,1),0),COLUMN(A1)，向右自动填充，也可输入数字1. 向右自动填充，但需注意29、30、31三个日期需根据年月进行公 式设置。有两个办法解决，一是

7、输入上述公式，二是在30日处输入=IF($Z$2)2,30,)，31日处输入=IF($Z$2=2,IF(AND($Z$2)=7,MOD($Z$2,2)0),31,IF(AND($Z$2)7,MOD($Z$2,2)=0),31,)20考勤表模版的设置3. 自动设置星期，在D4单元格处输入公式=WEEKDAY($V$2&- &$Z$2&-&D$3)，向右填充至28日，在AF4处输入公式=IF(AF3=,WEEKDAY($V$2&-&$Z$2&-&AF$3)，向右填充至31日，即可出现对应星期，且自动辨别闰年闰月。254. 设置条件格式，对周 周日进行颜色标注。开始条件格式新建规则设置两项规则后，呈

8、现的结果如下：5. 对D5:AH12进行序列填充，分别根据喜好输入各种考勤符 号，序列填充前面已经运用，此处不再赘述。6. 对 AI5:AR5 分 别 输 入 公 式 ： AI5=IF(COUNTIF(D5:AH6,)/2=0,COUNTIF(D5:AH6,)/2)；AJ5=IF(COUNTIF(D5:AH6,)/2=0,COUNTIF(D5:AH6,)/2)；AK5=IF(COUNTIF(D5:AH6,)/2=0,COUNTIF(D5:AH6,)/2)；AL5=IF(COUNTIF(D5:AH6,)/2=0,COUNTIF(D5:AH6,)/2)；AM5=IF(COUNTIF(D5:AH6,

9、)/2=0,COUNTIF(D5:AH6,)/2)；AN5=IF(COUNTIF(D5:AH6,)/2=0,COUNTIF(D5:AH6,)/2)；AO5=IF(COUNTIF(D5:AH6,)/2=0,COUNTIF(D5:AH6,)/2)；AP5=IF(COUNTIF(D5:AH6,)/2=0,COUNTIF(D5:AH6,)/2)；AQ5=IF(COUNTIF(D5:AH6,)/2=0,COUNTIF(D5:AH6,)/2)；AR5=IF(COUNTIF(D5:AH6,)/2=0,COUNTIF(D5:AH6,)/2)。向下填充。呈现的结果如下：小结考勤表的设置主要注意29、30、31这

10、三天的日期设置，要区分闰年闰月，嵌套公式较多，星期的设置也需注意，另外想设置周日周六两天自动填充公休符号， 未找到方法。26四、 购房决策27购房决策1. 新建表购房决策，在B2单元格输入房价600000（设置为行变量）， 在B3单元格输入利率，案例中输入的是月利率，B4单元格输入按揭 月份数。2. 在C5：I6区域输入不价，在B7：B11输入不同按揭年数的月份数。3. 在B6单元格建立公式：=PMT(B3,B4,-B2)，此处之所以B2前加负 号是方便理解，因为PMT公式计算出来的值是负数。回车确认。4. 选取区域B6：I11建立模拟运算表。5. 分别指定行变量和列变量。根据需求设置条件格式

11、，5000以上显示为红色，3000-5000为橙色，3000以下为绿色。28购房决策30在房屋总价行输入总价即可根据个人需求来决定选择价格， 假如一个预算每月还款3000以下，则绿色表示可以考虑，5000以上不考虑，则红色表示不考虑，3000-5000可以商量， 橙色表示可商量小结购房决策主要运用了模拟运算的方法，比较简单，结合PMT函数的运用，PMT函数本来计算出来值是负数，为方便理解，我稍作处理，计算出来的值便为正数。31五、个人所得税模版及打印工资表32个人所得税模版1. 制作工资表格，为方便起见，只保留比较重要的的项目，首先是工龄计算，在F6输入公式=IF(E6=,IF(YEAR($F

12、$2)- YEAR(E6)=0,IF(IF(MONTH($F$2)=MONTH(E6),YEAR($F$ 2)-YEAR(E6),YEAR($F$2)-YEAR(E6)-1)=0,(IF(MONTH($F$2)=MONTH(E6),YEAR($F$2)-YEAR(E6),YEAR($F$2)-YEAR(E6)-1)，依次向下填充。即可计算出工龄；342. 应税所得BG单元格输入=IF(AU6-AH6- SUM(BA6:BD6)-BF6)0,0,(AU6-AH6-SUM(BA6:BD6)-BF6)，向下填充，计算应税所得；3. 税率BH单元格处输入=IF(BG6=1500,BG6=4500,BG

13、6=9000,BG6=35000,BG6=5500 0,BG6=80000,45%,0)，向下填充，得出相应工资所匹配的税率；个人所得税模版4. 速算扣除数BI单元格输入=IF(BG7=1500,BG7=4500,BG7=9000,BG7=35000,BG7=55000,BG7=80000,13505,0)，向下填充，得出速算扣除数；5. 计算个税，在BE单元格处输入=ROUND(IF(BG6=1500,BG6=4500,BG6=9000,BG6=35000,BG6=55000,BG6=80000,BG6*0.45-13505,0),2)，用来计算个税。35个人所得税模版36打印工资条打印工资

14、条，要求每个人的工资条都有表头： 方法一：1. 第一步：在工资细目的右侧两列中，交叉输入任意数字（主要是为了后面的“空位”空值，所以数字可任意输），然后选中交叉的四个单元格，双击右下角的“填充柄”，使这种格式一直填充至工资表的结束行。432. 第二步：执行“开始”“查 找”“定位条件”命令，在打开的“定位”对话框中单击“定位条件”按钮，在打开的“定位条件” 对话框中，选择“空值”，然后单击“确定”按钮。3. 第三步：执行“插入”“行”命令，这时便会从第2个人开始， 每一行的前面插入了一个空行。4. 第四步：序号填充筛选姓名处选空白定位条件可见单元格复制表头粘贴，这时工资条就算制作完成了。但此方

15、法只适合工资条中每一项目都有数字才能粘贴，下面一种方法则不需。方法二：1. 右键填充，选序列，步长值2，如右：2. 复制表头，粘贴,24行：3. 将序号填充数字， 以2开始，步长为2：4. 按序号排序：但此种方法如果表头有合并单元格则不适应，需要运用宏进行运算，第三种方法如果有合并单元格也可以，且比较简单。方法三：1. 复制表头，在B9单元格输入公式=INDEX(A:A,ROW(A28)/4)，向右填充；此处需注意是ROW里的数字，为什么是A28，这需要根据第一个人所在行 确定ROW里面的行数。2. 选定四行向下复制即可得到每个人的工资条。小结个人所得税的计算有多种方法，此处只列举一种方法，

16、此例基本包括了公司所有情况，合格比较复杂多重嵌 套需注意调整格式。打印表的设置列举了三种方法，尤其是第三种方法看似简单，实际在设置ROW的时候需特别注意。44六、数组公式与VLOOKUP函数结合运用45数组公式与VLOOKUP函数结合运用1. 直接运用VLOOKUP函数查找引用对B14单元格，在C14单元格中输入:=VLOOKUP(B14,B3:O11,14,FALSE)2. 本例中我们只用到了姓名列和年收入列，那么，查找区域是不是可 以缩小一下，只要B列和O列呢，对B15单元格，在C15单元格中输入完整公式 : (注意这是数组公式， 输入后按Ctrl+Shift+Enter )=VLOOKUP(B15,IF(TRUE,FALSE,B3:B11,O3:O11),2,FALSE)473. 要是原表中没有年收入怎么办呢，没关系，不是有每个月的数据吗，相加就好了，那么，怎么一步完成：对B16单元格，在C16单元格中输入公式 : (注意这是数组公式，输入后按 Ctrl+Shift+Enter )=SUM(VLOOKU