财务常用excel技巧及公式汇总.xls

目目录录 应应避避免免的的现现象象 打打印印设设置置技技巧巧 四四舍舍五五入入 填填充充单单元元格格 绝绝对对引引用用 提提取取字字符符 合合并并 改改变变负负号号 单单元元格格个个数数 IFIF公公式式一一 IFIF公公式式二二 数数据据有有效效性性 符符合合条条件件求求和和 符符合合多多条条件件求求和和 筛筛选选求求和和 同同颜颜色色单单元元格格求求和和 V VLOOKUPLOOKUP函函数数 求求内内含含报报酬酬率率 求求复复利利年年金金现现值值、复复利利年年金金终终值值、年年金金 使使符符条条件件单单元元格格变变色色 excelexcel动动态态图图表表制制作作 数数据据透透视视表表运运用用 实实用用快快捷捷键键 同同列列数数据据空空单单元元格格覆覆盖盖 点点击击进进入入相相应应工工作作表表 番番外外篇篇（excelexcel在在wordword运运用用） 返回目录 excelexcel应应避避免免现现象象 我我们们在在向向其其他他人人报报送送相相应应表表格格时时，一一定定要要保保证证excelexcel表表的的整整洁洁美美观观。要要避避免免以以下下现现象象： 1、对excel表整行（列）标注颜色或设置边框实线。影响表格美观；打印时需设置起止 页，否则会连续打印。 2、打印设置： （1）不设置打印页边距； （2）单元格文字内容未完全显示； （3）边框线未设置。 3、格式不统一： （1）对齐不一致，垂直有的靠下，有的居中（excel快速调节）； （2）字体、数字格式不一致； （3）行高不统一。 4、未删除不相关的批注、说明等内容。 5、表头涉及主体名称、日期等未修改。 请请相相信信：你你的的每每一一个个表表格格报报 送送对对象象都都是是最最严严重重的的强强迫迫症症 患患者者，都都是是处处女女座座！ 返回目录 打印设置技巧 1、打印标题行设置 选择：页面布局（页面设置-工作表）打印标题顶端标题行，点击右边的红色箭头，用鼠标选 中标题行（可以是一行也可以是多行）。 2、设置打印区域 （1）选中打印区域，选择：页面布局（页面设置-工作表）打印区域设置打印区域 （2）若要取消打印区域，选择：页面布局（页面设置-工作表）打印区域设置打印区域 3、单色打印设置（不打印单元格背景颜色、彩色字体） 选择：打印预览页面设置工作表，勾选【单色打印】 4、错误单元格打印显示 选择：打印预览页面设置工作表，下拉选定【错误单元格打印为】；可以将公式返回错误的显 示值在打印时不显示，或显示为“”，如下方单元格（公式=2/0)。 #DIV/0! 5、格式怎么设都有问题 选择：先清除全部格式，重新设置。 请请相相信信：你你的的每每一一个个表表格格报报 送送对对象象都都是是最最严严重重的的强强迫迫症症 患患者者，都都是是处处女女座座！ 返回目录 ROUNDROUND公公式式：四四舍舍五五入入，解解决决一一分分钱钱差差异异 我们在用excel在计算时，往往要保留两位小数，我们一般的处理方式是改变单元格的性质，以数字的形式反映，但是在计算式往往会出现尾差错误，即我们常说的差一分钱。 我们解决这样的问题时，需要使用round公式，即round（要计算的公式，要保留的小数位数） 姓名加班天数工资基数加班工资计算基数 加班工资 张三91,000.0021.78413.2231 李四51,500.0021.78344.3526 王五21,200.0021.78110.1928 赵六71,800.0021.78578.5124 合计235,500.001,446.2810 通过round公式处理，一分钱尾差的事情已经得到解决。 补充：其他与小数点相关函数 值结果公式 3.5263.52=TRUNC(B23,2) 3.5263.52 =ROUNDDOWN(B25,2) 3.5253 =INT(B26) 加班工资加班工资 413.22413.00 344.35344.00 110.19110.00 578.51579.00 1,446.271,446.00 点点击击可可查查看看公公式式 我们在用excel在计算时，往往要保留两位小数，我们一般的处理方式是改变单元格的性质，以数字的形式反映，但是在计算式往往会出现尾差错误，即我们常说的差一分钱。 通过round公式处理，一分钱尾差的事情已经得到解决。 返回目录 填填充充单单元元格格的的技技巧巧 当要复制的单元格很多，而且要复制的单元格连续，我们一般处理是要复制的源单元格右下角出现黑“+”时进行下拉处理。 如： 1 1 1 1 1 1 1 1 1 1 若想仅填充内容或仅填充格式，填充完成后，下拉右下方的正方形标志（如下图），选择“仅填充格式”或“不带格式填充”即可。 已经填充源的单元格，选中其中某个单元格，双击该单元格右下方的黑色小方块，可实现快速填充（仅填充该单元格下方的单元格）。 小窍门：要改变复制的数字形式（数值或序列）时，按住ctrl再下拉或双击，会改变填充的形式。 待解决问题：如何能不带格式填充序列。欢迎知道的童鞋交流指导 当要复制的单元格很多，而且要复制的单元格连续，我们一般处理是要复制的源单元格右下角出现黑“+”时进行下拉处理。 若想仅填充内容或仅填充格式，填充完成后，下拉右下方的正方形标志（如下图），选择“仅填充格式”或“不带格式填充”即可。 已经填充源的单元格，选中其中某个单元格，双击该单元格右下方的黑色小方块，可实现快速填充（仅填充该单元格下方的单元格）。 返回目录 绝绝对对引引用用 我们在用excel在计算时，往往想保持一个值不变，其他变量变动，这时就需要绝对引用。即用$符号将所绝对引用的单元格的列夹住（快捷键为F4）。 如：计算各部分占全部的比例 项目用量所咱比例 丹参107.4% 黄芪2014.8% 木耳1511.1% 黄瓜籽107.4% 骨碎补3022.2% 淫羊藿5037.0% 合计135100.0% 提示：绝对引用$C$12、$C12 限制行引用C$12（不限制列） 限制行引用$C12（不限制列） 输入公式状态下，光标停留在“A4”,按F4键，可快速转为“$A$4” 应用： 在某些情况下应删除绝对引用的符号，如跨表格取数填充。 本例中每个药材的用量是变量，合计用 用量是不变量，所以在D5单元格中输入 入公式=C5/$C$12,然后再向下拖或复 复制即能达到计算效果。 0.07 0.22 我们在用excel在计算时，往往想保持一个值不变，其他变量变动，这时就需要绝对引用。即用$符号将所绝对引用的单元格的列夹住（快捷键为F4）。 输入公式状态下，光标停留在“A4”,按F4键，可快速转为“$A$4” 本例中每个药材的用量是变量，合计用 用量是不变量，所以在D5单元格中输入 入公式=C5/$C$12,然后再向下拖或复 复制即能达到计算效果。 返回目录 提提取取字字符符 在工作中，我们往往需要一些单元格中的部分字符，这时就需要公式来解决。 如：left（）、right（）、mid（）公式 目目标标公公式式 辽宁大政医药进出口有限公司LEFT(B7,3) 辽宁大政医药进出口有限公司RIGHT(B8,3) 辽宁大政医药进出口有限公司MID(B9,3,3) mid与find配合使用 摘摘要要目目标标值值所所在在位位置置 收款孟庆 解俊华地翡翠蓝湾（朗园）一期11#楼250318 收款徐祖芳华地翡翠蓝湾（朗园）一期11#楼110115 收款胡美凤华地翡翠蓝湾（朗园）一期11#楼160415 收款刘积红华地翡翠蓝湾（朗园）一期11#楼150115 收款韦兵华地翡翠蓝湾（朗园）一期11#楼230314 收款李志诚 黄自秀华地翡翠蓝湾（朗园）一期10#200119 收款胡峰华地翡翠蓝湾（朗园）一期11#楼200314 收款王建 丁琼林华地翡翠蓝湾（朗园）一期11#楼220218 收款殷清 童培培华地翡翠蓝湾（朗园）一期11#楼170418 说说明明：将将findfind位位置置目目标标值值改改为为“期期”或或“一一期期”，如如何何提提取取字字符符。 提示：提取可与分列、替换组合使用（替换可替换空格） 提取公式不能用于提取日期格式（此时用分列） 用公式拆分日期年 2012年12月13日2012 2015年3月9日2015 公公式式计计算算结结果果说说明明 辽宁大取目标的左起三个字符 限公司取目标的右起三个字符 大政医取目标第三个字符起的三个字符 提提取取公公式式提提取取结结果果说说明明 一期11#楼2503一期11#楼2503 在公式里，一个汉字 、一个数字或符号都 为一个字符 一期11#楼1101 MID(B16,FIND(）,B16)+1,10) MID(B17,FIND(）,B17)+1,10) MID(B18,FIND(）,B18)+1,10) MID(B19,FIND(）,B19)+1,10) MID(B20,FIND(）,B20)+1,10) MID(B21,FIND(）,B21)+1,10) MID(B22,FIND(）,B22)+1,10) 月日 1213 39 在工作中，我们往往需要一些单元格中的部分字符，这时就需要公式来解决。 如：left（）、right（）、mid（）公式 说说明明：将将findfind位位置置目目标标值值改改为为“期期”或或“一一期期”，如如何何提提取取字字符符。 返回目录 合合并并单单元元格格字字符符 在工作中，我们往往需要将一些单元格的内容合并，而重新输入会很麻烦，这时就需要公式来解决。 1、用“&”符号来解决 目目标标公公式式结结果果 华地恒润注册资本100万华华地地恒恒润润注注册册资资本本100100万万 华地恒基注册资本500万华华地地恒恒基基注注册册资资本本500500万万 在“华地恒润”后面加“房地产有限公司” 华地恒润房地产有限公司注册资本100万 如果合并内容中包含日期，需要套一个text公式。 王大锤2015年12月15日王大锤42353 王大锤2015年12月15日 王大锤2015-12-15 王大锤2015/12/15 2、用CONCATENATE公式 华地恒润注册资本100万CONCATENATE(B7,C7,D7) 华地恒润注册资本100万元CONCATENATE(B7,C7,D7,元) 公公式式 B7&C7&D7 B8&C8&D8 B7&房地产有限公司&C7&D7 B15&C15 B15&TEXT(C15,e年m月d日)TEXT公式可以改变日期显示格式，有时比先自定义单元格再直接复制方便！ B15&TEXT(C15,e-m-d) B15&TEXT(C15,e/m/d) 在工作中，我们往往需要将一些单元格的内容合并，而重新输入会很麻烦，这时就需要公式来解决。 1、用“&”符号来解决 CONCATENATE(B7,C7,D7,元) 返回目录 改改变变负负号号 工作中很可能要将单元格中的数字变换，这样就需要选择性粘贴 1、如将下列单元格的负数变成正数 -12.00 -11.00 -10.00 -9.00 -8.00用鼠标选上全部单元格点击任一个空白处按鼠标右键 -7.00 -6.00 -5.00 -4.00 -3.00 -2.00 说明：此种方法会节约大量的转换时间，不用每个单元格去修改 大家可以想想其他的运算“加”、“乘”、“除”都有何妙用？ 2、在设置数值格式为会计专用格式时，负号左顶格如何取消？ 控制面板区域和语言选项自定义 24.00 22.00 20.00 点击选择性粘贴点击“数值”和“减”18.00 16.00 点击确定，全部变为正数14.00 12.00 再粘贴回原处10.00 8.00 6.00 4.00 “货币”栏下货币负数格式选下图列示格式 待待粘粘贴贴单单元元格格需需 需需为为空空或或数数字字为为0 0 说明：此种方法会节约大量的转换时间，不用每个单元格去修改 大家可以想想其他的运算“加”、“乘”、“除”都有何妙用？ 返回目录 符符合合条条件件单单元元格格个个数数 在工作中，有时我们需要查找特定区域的一定条件的数值单元格个数，这样就需要公式来解决 如：查找下列区域的大于324的数值单元格个数，过去我们一般用筛选来解决，那样的话比较耽误时间，现在用公式countif来解决 材料名称 数量 AA324 AB648.5 BB648.5 BC324 BD324 CA648.5 CC324 DG648.5 FG324 FY648.5输入公式=countif（选择查找的区域C6:C24 FX648.5 GA324 GB648.5 GC324 GD648.5 GF324 GG324说明：此种方法对查找一定数值单元格数量会节约大量的时间，但此公式只限于数值型单元格 GH648.5另外，除大于外，也可以等于=、小于324 输入括号完毕 运算结果 10 在工作中，有时我们需要查找特定区域的一定条件的数值单元格个数，这样就需要公式来解决 如：查找下列区域的大于324的数值单元格个数，过去我们一般用筛选来解决，那样的话比较耽误时间，现在用公式countif来解决 说明：此种方法对查找一定数值单元格数量会节约大量的时间，但此公式只限于数值型单元格 另外，除大于外，也可以等于=、小于D7,超出,否) 行行政政190.00200.00 生生产产298.00300.00 部部门门 预预算算费费 用用 实实际际费费 用用 超超降降比比 要求：部门费用超预算的且超过10%显示超支；超预算5%（含）至10%（含）显示降低；超预算5%以内显示考核；小于预算显示奖励 财财务务120.00100.00-20.00% 公式：=IF(E170.1,超支,IF(E17=0.05,降低,考核) 行行政政190.00200.005.00% 公式：=IF(E170.1,超支,IF(E17=0.05,降低,IF(E17=0,考核,奖励) 生生产产298.00300.000.67% 营营销销200.00245.0018.37% 提示：IF公式多层嵌套注意顺延一定的顺序，从左到右，从大到小（或相反），嵌套不超4层为宜 计算结果 超出 否 否 公式公式 计算结果计算结果 奖励奖励 降低降低 考核考核 超支超支 点点击击可可查查看看公公式式 在excel公式中，个人觉得用处最大的公式还是，很多人知道这个公式，但是能用好它的却很少；IF公式可以配合很多公式一起使用，有意想不到的效果。 说明：=IF(aa，bb，cc)，aa为要满足的条件、bb为满足条件时显示的值、cc为不满足条件时显示的值 要求：部门费用超预算的且超过10%显示超支；超预算5%（含）至10%（含）显示降低；超预算5%以内显示考核；小于预算显示奖励 公式：=IF(E170.1,超支,IF(E17=0.05,降低,考核) 公式：=IF(E170.1,超支,IF(E17=0.05,降低,IF(E17=0,考核,奖励) 提示：IF公式多层嵌套注意顺延一定的顺序，从左到右，从大到小（或相反），嵌套不超4层为宜 返回目录 IFIF公公式式应应用用（二二） 1、判断成绩 大于90优秀 大于90优秀80到90良好 60到90合格60到80合格 小于60不及格小于60不及格 89合格良好 88合格良好 76合格合格 35不合格不合格 95优秀优秀 78合格合格 2、判断账龄 判断账龄 DATEDIF函数 2013/12/12014/2/62 2013/6/12014/2/68 2012/5/72014/2/620 2009/7/82014/2/654 2007/9/102014/2/676 2010/10/12014/2/640 3、IF+AND（限制条件较多时使用） ABC 616366A、B、C都大于等于60，发放证书 918858 665561 658169 IF函数lookup函数 6个月以下6个月以下IF(F20=24,2年以上,IF(F20=18,1.5年以上,IF(F20=12,1年以上,IF(F20=6,6个月以上,IF(F20=24,2年以上,IF(F20=18,1.5年以上,IF(F20=12,1年以上,IF(F20=6,6个月以上,IF(F206,6个月以下) lookup(F22,0,6,12,18,24,6个月以内,6个月以上,1年以上,1.5年以上,2年以上) 返回目录 数数据据有有效效性性 工作中，我们做好格式提供给他人使用，但是又怕他人不按规定要求填列单元格，这时就需要使用有效性来限制输入 说明：数据菜单有效性 例子：在给定单元格中输入四位整数，否则显示“输入错误，请输入四位整数”。 数据菜单有效性设置允许文本长度 项目 丹参 13黄芪 木耳 黄瓜籽 骨碎补 大家可以通过此例子选择其他选项，得到其他想要的结果。淫羊藿 数据等于长度 4 出错警告 输入相关信息 工作中，我们做好格式提供给他人使用，但是又怕他人不按规定要求填列单元格，这时就需要使用有效性来限制输入 返回目录 符符合合条条件件求求和和 实际工作中对符合条件的求和，我们大家很多人用“sumif”这个公式 说明：sumif（aa，bb，cc），aa为要满足条件的区域、bb为满足的条件、cc为满足条件时求和的区域 例子：部部门门实实际际费费用用要求：分别按部门汇总费用 财财务务120.00公式：=SUMIF(B6:B17,财务,C6:C17)或=SUMIF(B6:B17,B6,C6:C17) 行行政政190.00 生生产产298.00 财财务务120.00 行行政政190.00 生生产产298.00 财财务务120.00 行行政政190.00 生生产产298.00 财财务务120.00 行行政政190.00 生生产产298.00 科目简单加总 合并抵消 抵消合并数 此公式多用于手工合并报表借贷 其他应收款60000400064000 应收账款5000500010000 其他应付款80000400084000 预收账款9000500014000 部门计算结果 财务480 行政760 生产1192 合并抵消分录 序号科目借方贷方备注 1其他应收款4000 调整其他应收负数 1其他应付款4000 2应收账款5000 调整应收负数 2预收账款5000 点点击击可可查查看看公公式式 说明：sumif（aa，bb，cc），aa为要满足条件的区域、bb为满足的条件、cc为满足条件时求和的区域 公式：=SUMIF(B6:B17,财务,C6:C17)或=SUMIF(B6:B17,B6,C6:C17) 返回目录 符符合合多多条条件件求求和和 实际工作中对符合条件的求和，我们大家很多人用“sumif”这个公式，但是要选择多个条件的时候，就要用公式“sumproduct” 说明：sumproduct（(aa)*(bb)*(cc)），aa为要满足的条件、bb为满足的条件、cc为满足的条件 例子：部部门门月月份份实实际际费费用用要求：分别按部门汇总一月份费用 财财务务一一月月120.00公式：=SUMPRODUCT(（B6:B17=财务)*(C6:C17=一月)*(d6:d17) 行行政政一一月月190.00 生生产产一一月月298.00 财财务务一一月月120.00 行行政政二二月月190.00 生生产产二二月月298.00 财财务务三三月月120.00 行行政政三三月月190.00 生生产产三三月月298.00 财财务务一一月月120.00 行行政政二二月月190.00 生生产产二二月月298.00 生生产产一一月月255.00 部门计算结果 财务360 行政190 生产553 点点击击可可查查看看公公式式 实际工作中对符合条件的求和，我们大家很多人用“sumif”这个公式，但是要选择多个条件的时候，就要用公式“sumproduct” 说明：sumproduct（(aa)*(bb)*(cc)），aa为要满足的条件、bb为满足的条件、cc为满足的条件 要求：分别按部门汇总一月份费用 公式：=SUMPRODUCT(（B6:B17=财务)*(C6:C17=一月)*(d6:d17) 返回目录 筛筛选选求求和和 工作中处理大量数据时会使用筛选功能，筛选出的数据要求和的话，一般都是用鼠标拖并单元格的求和方式，这时可以用subtotal公式来达到目标。 如：=SUBTOTAL(9,区域)，9是代码，表示求和 例如：一月部部门门实实际际费费用用 一月行行政政190.00 一月生生产产298.00 一月行行政政190.00 一月小计678.00 小计用subtotal公式 二月生生产产298.00 二月行行政政190.00 二月生生产产298.00 二月行行政政190.00 二月生生产产298.00 二月小计1,274.00 三月行行政政190.00 三月生生产产298.00 三月行行政政190.00 三月生生产产298.00 三月行行政政190.00 三月生生产产298.00 三月小计1464 四月生生产产500 总计3,916.00 点点击击可可查查看看公公式式 工作中处理大量数据时会使用筛选功能，筛选出的数据要求和的话，一般都是用鼠标拖并单元格的求和方式，这时可以用subtotal公式来达到目标。 返回目录 同同颜颜色色单单元元格格求求和和 2016年 凭证号 月日 0114记账-0017 0120记账-0030 0120记账-0030 0129记账-0060 0130记账-0072 0131记账-0074 0131记账-0079 0219记账-0030 0224记账-0045 0226记账-0051 0229记账-0053 0229记账-0058 0229记账-0058 0229记账-0061 0229记账-0061 0229记账-0061 0229记账-0061 0229记账-0061 0229记账-0062 0229记账-0062 0229记账-0065 0302记账-0002 0305记账-0006 0315记账-0037 0325记账-0066 0325记账-0066 0325记账-0066 0325记账-0066 0325记账-0066 0325记账-0066 0325记账-0066 0330记账-0079 0331记账-0080 0331记账-0084 0331记账-0095 0411记账-0021 0421记账-0051 0421记账-0051 0421记账-0051 0421记账-0051 0422记账-0058 0429记账-0096 0429记账-0097 0430记账-0104 0508记账-0014 0511记账-0019 0523记账-0038 0523记账-0038 0523记账-0038 0523记账-0038 0523记账-0045 0524记账-0047 0527记账-0054 0531记账-0064 0531记账-0075 0607记账-0016 0620记账-0037 0621记账-0067 0621记账-0067 0621记账-0067 0621记账-0067 0623记账-0044 0627记账-0052 0630记账-0079 0708记账-0014 0708记账-0015 0721记账-0055 0721记账-0055 0721记账-0055 0727记账-0073 0731记账-0084 0731记账-0084 0731记账-0091 0812记账-0017 0817记账-0025 0817记账-0026 0825记账-0054 0825记账-0054 0825记账-0054 0825记账-0054 0827记账-0058 0831记账-0075 1、点工具栏（或按CTRL+F键）【查找和选择】 2、点【选项】 3、点【格式】 4、填充，选择对应颜色，比如是黄色 5、在查找界面按Shift键选中全部选中颜色单元格（重要！） 6、点公式栏下【定义名称】，比如定义为“黄色单元” 7、输入公式=SUM（黄色单元） 摘要对方科目 结转租金收入（D区商-103、103上）2015.07.25-2016.03.24 快天下餐饮预收账款 收委托贷款利息01.20银行存款 收委托贷款利息01.20银行存款 结转租金收入（C区商-117、117上） 合肥本中小美银行存款/预收账款 收委贷利息01.29 东莞银行银行存款 收委贷利息01.21 徽商安庆西银行存款 收入结转本年利润 本月合计 本年累计 结转张锐租商铺租金02.19 银行存款/预收账款/财务 费用 结转王晓光租商铺租金02.24银行存款/预收账款 结转刘杨租商铺租金02.29 银行存款/预收账款/财务 费用 结转王玉林租商铺租金02.29 银行存款/预收账款/财务 费用 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款/其他应付款 aaaaaa银行存款/其他应付款 收入结转本年利润 本月合计 本年累计 结转沈茂勇租商铺租金03.02银行存款/预收账款 结转吉的堡幼儿园租金（2016.01.01-2016.06.30）03.05银行存款/预收账款 结转徐洁租商铺租金03.15 银行存款/预收账款/财务 费用 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 结转租户租商铺租金03.30 银行存款/库存现金/预收 账款/财务费用 同同颜颜色色单单元元格格求求和和 结转租户租商铺租金03.31银行存款/预收账款 收委托贷款利息03.21 天津海贸银行存款 收入结转本年利润 本月合计 本年累计 结转商户租商铺租金04.11 安徽味王餐饮银行存款/预收账款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 结转租金收入（C区商-117、117上） 合肥本中小美银行存款/预收账款 结转租金收入04.29 安徽哈哈乐餐饮预收账款 收入结转本年利润 本月合计 本年累计 结转张锐租商铺租金05.05银行存款/预收账款 结转刘杨租商铺租金05.11 银行存款/财务费用/预收 账款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 结转张洪兰租商铺租金05.23 银行存款/预收账款/财务 费用 结转王晓光租商铺租金05.20银行存款/预收账款 还贷收息05.25银行存款/其他应收款 结转租户商铺租金05.30 银行存款/库存现金/财务 费用/预收账款 收入结转本年利润 本月合计 本年累计 结转沈茂勇租商铺租金06.07银行存款/预收账款 结转商户租商铺租金06.20 安徽味王餐饮银行存款/预收账款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 结转租户租商铺租金06.24 银行存款/预收账款 收入结转本年利润 本月合计 本年累计 结转租金收入07.07 银行存款/预收账款 结转租金收入07.07 银行存款/预收账款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 结转租金收入07.07 预收账款 aaaaaa银行存款 aaaaaa银行存款 收入结转本年利润 本月合计 本年累计 因商铺出售，故退商铺租金给各业主08.12其他业务成本/其他应付款 收吉的堡幼儿园租金银行存款/预收账款 结转张锐商铺租金预收账款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 aaaaaa银行存款 结转张洪兰租商铺租金 银行存款/预收账款/财务 费用 收入结转本年利润 本月合计 本年累计 1、点工具栏（或按CTRL+F键）【查找和选择】 4、填充，选择对应颜色，比如是黄色 5、在查找界面按Shift键选中全部选中颜色单元格（重要！） 6、点公式栏下【定义名称】，比如定义为“黄色单元” 7、输入公式=SUM（黄色单元） 借方 贷方方向 余额 本币本币 30959.00 贷30959.00 723333.33 贷754292.33 191666.67 贷945959.00 15835.00 贷961794.00 186666.67 贷1148460.67 240000.00 贷1388460.67 1388460.67平 1388460.671388460.67 平 1388460.671388460.67 平 27050.00 贷27050.00 23996.00 贷51046.00 33087.00 贷84133.00 24930.00 贷109063.00 341757.01 贷450820.01 1708.78 贷452528.79 310000.00 贷762528.79 52.75 贷762581.54 168000.00 贷930581.54 99947.25 贷1030528.79 300000.00 贷1330528.79 15000.00 贷1345528.79 45000.00 贷1390528.79 1390528.79平 1390528.791390528.79 平 2778989.462778989.46 平 25276.00 贷25276.00 215000.00 贷240276.00 19746.51 贷260022.51 290000.00 贷550022.51 120431.66 贷670454.17 968.40 贷671422.57 269698.27 贷941120.84 666666.67 贷1607787.51 405000.00 贷2012787.51 14568.34 贷2027355.85 24882.00 贷2052237.85 30959.00 贷2083196.85 180362.67 贷2263559.52 2263559.52平 2263559.522263559.52 平 5042548.985042548.98 平 73502.00 贷73502.00 310000.00 贷383502.00 600000.00 贷983502.00 289333.34 贷1272835.34 2066666.67 贷3339502.01 182478.89 贷3521980.90 15835.00 贷3537815.90 27875.00 贷3565690.90 3565690.90平 3565690.903565690.90 平 8608239.888608239.88 平 28404.00 贷28404.00 34741.00 贷63145.00 223333.33 贷286478.33 600000.00 贷886478.33 280000.00 贷1166478.33 2000000.00 贷3166478.33 35017.00 贷3201495.33 23996.00 贷3225491.33 26666.67 贷3252158.00 20734.00 贷3272892.00 3272892.00平 3272892.003272892.00 平 11881131.8811881131.88 平 26539.00 贷26539.00 75912.00 贷102451.00 620000.00 贷722451.00 886.14 贷723337.14 288447.20 贷1011784.34 2066666.67 贷3078451.01 274248.08 贷3352699.09 31991.00 贷3384690.09 3384690.09平 3384690.093384690.09 平 15265821.9715265821.97 平 24882.00 贷24882.00 1200.00 贷26082.00 600000.00 贷626082.00 280000.00 贷906082.00 2000000.00 贷2906082.00 17513.00 贷2923595.00 119222.11 贷3042817.11 298.06 贷3043115.17 3043115.17平 3043115.173043115.17 平 18308937.1418308937.14 平 -44162.00 借44162.00 215000.00 贷170838.00 28404.00 贷199242.00 446.97 贷199688.97 465000.00 贷664688.97 154553.03 贷819242.00 2066666.67 贷2885908.67 35017.00 贷2920925.67 2920925.67平 2920925.672920925.67 平 21229862.8121229862.81 平 黄黄色色单单元元格格合合计计1 11 14 44 41 12 20 0. .5 51 1 蓝蓝色色单单元元格格合合计计5 51 13 30 00 00 0 返回目录 VLOOKUPVLOOKUP函函数数 在工作中有时需要比对数据，或将数据重新排序，这时可以使用VLOOKUP函数 名称金额名称金额 A110120A110120 B110221A090216 V010525 C050626 C060827 A090216 D050418 利用vlookup跨表格比对数据后面与数据透视表一同介绍。 返回目录 求求内内含含报报酬酬率率（内内部部收收益益率率） 在做投资测算时会经常需要测算内部收益率，这时需要使用IRR函数 投资某项目现金流如下单位：万元 项目0123456 现金流出：初始购建-1000 大修理-300 产生现金流入260280290290260240 合计-1000260280-10290260240 内部收益率8.464% 以上表示1-6年的现金流按8.464%复利折现等于初始投资1000万元 返回目录 求求复复利利年年金金现现值值、复复利利年年金金终终值值、年年金金 复复利利现现值值+ +年年金金现现值值（一一般般债债权权人人测测算算用用，但但公公式式设设计计站站在在债债务务人人角角度度考考虑虑） 折现率（利率）8% 期数(年 )3 计息（支付）间隔期期(月 )3 定期支付额25000 期值0 支付日期期末 贷款金额为￥264,383.53 复复利利终终值值+ +年年金金终终值值 初期存款800000 年利率4.50% 每月存款12000 存款时间（月）30 存款日期期末 注意隐含假设：存款每月计息（若计息间隔期不是1个月，以上公式不可用） 三年后可拿到存款（终值）￥1,275,347.26 提示：倘若不存在每月存款额，可以直接用次方计算，“”表示次方 895069.45 求求年年金金 贷款本金280000 月利率0.55% 贷款时间（年）15 支付方式每月末付 等额本息每月偿还￥2,454.52 最后一期支付额若不等于25000的情况下不为0，比如最后一期支付15000，则期值为10000；最后一期支付35000，则期值为-10000 期末付，公式中输入0；期初付，公式中输入1（看第一次支付时点） 期末付，公式中输入0；期初付，公式中输入1（看第一次存款时点） 期末付，公式中输入0；期初付，公式中输入1（看第一次支付时点） 求求复复利利年年金金现现值值、复复利利年年金金终终值值、年年金金 复复利利现现值值+ +年年金金现现值值（一一般般债债权权人人测测算算用用，但但公公式式设设计计站站在在债债务务人人角角度度考考虑虑） 注意隐含假设：存款每月计息（若计息间隔期不是1个月，以上公式不可用） 提示：倘若不存在每月存款额，可以直接用次方计算，“”表示次方 返回目录 工作中需要对很多的数据进行核对，核对中如果查找某些核对结果不符时，容易产生视觉疲劳，这时用条件格式的话就很好的解决此问题 说明：格式菜单条件格式新建规则选择规则范围、条件、及复核条件单元的显示方式 快速找出以下序列中大于数值25的单元格 12151836 13282735 15252532 18232631 26252329 11142224 9232225 修改规则： 条件格式管理规则编辑规则 快速找出以下序列中包含“利息”的单元格 收委托贷款利息01.20 收委托贷款利息01.20 结转租金收入（C区商-117、117上） 收委贷利息01.29 东莞银行 收委贷利息01.21 徽商安庆西 收入结转 本月合计 本年累计 结转张锐租商铺租金02.19 结转王晓光租商铺租金02.24 结转刘杨租商铺租金02.29 结转王玉林租商铺租金02.29 一一般般选选择择“只只为为包包含含 以以下下内内容容的的单单元元格格设设 置置格格式式” 工作中需要对很多的数据进行核对，核对中如果查找某些核对结果不符时，容易产生视觉疲劳，这时用条件格式的话就很好的解决此问题 选择规则范围、条件、及复核条件单元的显示方式 快速找出以下序列中包含“利息”的单元格 返回目录 excelexcel动动态态图图表表制制作作 TRUE TRUE项目ABCDEF TRUE费用5512522222378104 收入12406801200668980325 利润902400325455587136 1、生成柱形图2、插入复选框（开发工具-插入-表单控件），菜单栏若未显示开发工具：excel选项-勾选【在功能区显示“开发工具”选项卡】 3、右击复选框，选择设置控件格式，设置如下图 勾勾选选图图中中复复选选框框，显显示示对对应应数数据据 4、复制原来的数据表，并将表格数据修改为公式表示，=IF($A$3=TRUE,C5,0) 5、对收入、利润做以上操作 项目ABCDEF6、修改柱形图数据源即可：鼠标右击柱形图，选择数据，如下图操作 费用5512522222378104 收入12406801200668980325 利润902400325455587136 损损益益对对比比图图 ABCDEF 0 200 400 600 800 1000 1200 1400 费用 收入 利润 费用 收入利润 2、插入复选框（开发工具-插入-表单控件），菜单栏若未显示开发工具：excel选项-勾选【在功能区显示“开发工具”选项卡】 3、右击复选框，选择设置控件格