网站地图 加入收藏 设为首页 原创论文网,覆盖经济,法律,医学,建筑,艺术等800余专业,提供60万篇论文资料免费参考

浅析钢企财务报表中EXCEL函数的应用

作者:原创论文网 时间:2017-04-22 14:25 加入收藏
  在日常财务软件的报表系统中, 经常使用EXCEL报表进行成本归集与分配,需要运用大量的EXCEL函数来定义公式,以提高工作效率。 以下就工作中的一些体会,重点介绍两个EXCEL函数使用技巧。
  
  一、使用Index()函数、row()函数(或者column()函数)嵌套将横向排列转换成纵向
  
  表数据操作中会经常有这种需求,行标题与纵向排列的列标题进行位置的互换,我们把这个操作称为“行列标题转换”,那么怎样使原来横向排列的行标题转换为纵向排列的列标题呢? 这里要用到Index()函数、row()函数的嵌套,举例如下:
  
  如图1左所示,将光标移至想得到列标题为“电”的单元格上,写入以下公式:“=index( 1: 1,row(a3))”,敲回车完成编辑。 然后将此单元格公式向下拖动或复制后向下粘贴,即可将行标题转换成列标题。 转换后如图1所示。
  
  以上转换公式可以灵活运用,比如每隔4列取一个行标题然后转换成连续纵向排列的列标题, 这时就要对上述公式进行一下变通,见表1.
  
  如果要将作为行标题的钢材品种转换到另一张表中作为列标题进行下一步整理, 左边第一个品种处于第3列,从左边第一个品种开始,每向右移动4个单元格光标就落在下一个品种所在单元格上, 也就是品种与品种之间的步长是四个单元格。 在转换时就需要用到“3”、“4”,公式如下:=INDEX(′源文件′! 1: 1,3+((ROW(A1)-1)*4)),编辑完成后将此公式向下复制粘贴即可,见表2所示。
  
  那么同理,使用index()函数与column()函数嵌套也可以实现列标题到行标题的转换。 假如需要列转行的列标题第一项所在单元格为A3, 则此嵌套函数简单表述为:“=index(源数据表! A: A,column(C1))”,向右拖动鼠标或者复制粘贴公式即可。
  
  二、用rank()函数对关键字列排序,使用vlookup()函数和row()函数嵌套对源数据按某列数值大小进行排序
  

  对数据进行排序是比较常 用 的 表 格 操 作 之 一 , 在EXCEL表 格系统中 , 给出了排序的功能键, 选中需要排序的区域, 点击排序键即可达成排序。 但此操作因为只能在原表上操作, 所以它的缺点是打乱了原表的顺序,如果表中的数据引用自别的表格,数据源易发生变化,必须重新执行选中后点击“排序”的动作。 那么怎么才能在不更改原表结构与顺序基础上,另外增加一张按照我们的要求自动排序的表呢?
  
  这里介绍一种能随源数据的变化而重新自动排序的方法,即公式法,这种方法需要分两步:
  
  第一步, 在数据源表中增加一列,将需要排序的数据按从大到小的顺序编号为1、2、3、4……,这一操作用到的是有排名次功能的rank()函数,详解如下例图2所示。
  
  上图中A列作为I列(单位边际列)从i7:
  
  i135(C135是本表最后一个品种,故选)区域数据进行了编号(从大到小),此时在A7单元格中输入 “=if (i7=”“,”“,rank(i7, i 7: i 135,0)+countif( i 7: i 135,i7)-1)”, 然后向下复制到A135.
  
  第二步,用vlookup()、row()函 数嵌套定义公式进行排序。
  
  在另一张工作表中对源工作表中已经标示好的次序号进行从1到N的挑选, 以此达到自动排序的实际效果,见图3.
  
  在B7单元格中输入“=iferror(vlookup(row()-6,数据源表! a 7: b135,2,false),”“)”,这里的效果是将源表中A列标示为“1”的单元格后的产品品种精确挑 选 到B7单 元 格 中来。 然后向下复制粘贴 到 B8 -B135 单 元格。 这样就达到了源工作表中的数据发生变化时,此处的B列排序会自动发生变化。 当然,C列以右的数据就可以将排序后的品种列作为索引从别的相关表中引用数据了。
  
  这里以两个例子演示了EXCEL中怎么完成列标题与行标题转换位置和用公式法进行数据的自动排序。 第一个操作技巧在于使用了索引函数的定位功能和行或者 列 标 题 的 规 律 性 排列, 用一个事例讨论了怎么把行标题转换成列标题的方法,同理,也可以 将 列 标 题 用 讲 到 的index () 函 数 和 column ()函数的嵌套, 将列标题转换为行标题。
  
  现在将这两个函数嵌套的原理分析一下,index()函数的使用方法如下:
  
  语法:INDEX(array,row_num,column_num)返回数组中指定的单元格 或 单 元 格 数 组 的 数值 . INDEX (reference,row_num,column_num,area_num) 返 回引用中指定单元格或单元格区域的引用。
  
  参数:Array为单元格区 域 或 数 组 常 数 ;Row_num为数组中某行的行序号,函数从该行返回数值。如果省略row_num,则 必 须 有column_num;Column_num 是 数 组 中某列的列序号, 函数从该列返回数值。 如果省略column_num, 则必须有row_num.Reference是对一个或多个单元格区域的引用,如果为引用输入一个不连续的选定区域,必须用()。 Area_num是选择引用中的一个区域,并返回该区域中row_num和column_num的交叉区域。
  
  选中或输入的第一个区域序号为1,第二个为2,以此类推。 如果省略area_num,则INDEX函数使用区域1.
  
  例中使用公式“=index($1:$1,row(a3))”所表达的意思可以描述为“返回本表第一行第三个单元格中的内容”,将此公式向下拖拉复制粘贴后,随着公式中相对引用“a3”变化成a4“、”a5“、”a6“……公式所表达的意义成为”返回本表第一行第四个单元格中的内容“、”返回本表第一行第五个单元格中的内容“、”返回本表第一行第六个单元格中的内容“…… 这样行标题到列标题的转换完成。 同理列标题转为行标题公式”=index($A:$A,column(C1))(行标题第一项位于A3单元格时)“ 向右拖拉复制粘贴即可。
  
  第二例中公式使用了vlookup()函数、row()函数的 嵌 套 . 那 么 首 先 看 vlookup () 的 使 用 规 则 :
  
  VLOOKUP ( lookupvalue , table_array , colindexnum ,[rangelookup])。 文字表述就是VLOOKUP(查找值,查找范围,返回值所在列数,精确匹配或者近似匹配)在财务工作中,几乎都使用精确匹配,该函数的匹配样式参数一定要选择为false(也可以写为”0“)。
  
  否则返回值会是一个近似值,往往不是财务人员的常用选项。
  
  在例中使用的公式=iferror(vlookup(row()-6,数据源表!$a$7:$b$135,2,false),”“)的意义是,如果在数据源表中区域a7:a135中能找到”row()-6“这个值,就返回数据源表中找到的这个值后边对应的b7:b135中的值,也即对应的”品种名称“,如果找不到”row()-6“这个值,就返回一个空值。 而对于”row()-6“,因为输入公式的单元格所处位置为A7,所以”row()-6“就是7-6,也就是”1“,随着公式向下拖拉复制粘贴,”row()-6“以 ”1“为步长增加成 ”2“、”3“、”4“…… , 这样也就把数据源表中A列数值表示次序的值后边一列(B列)的品种按毛利以从大到小的顺序挑选了出来。
  
  在实际工作中, 这两个技巧的使用非常省心省力地完成了每月的人工转换、人工排序工作,一旦公式定义完成,既准确无误,又省时美观,大大提高了我们的工作效率和工作质量。 需要特别指出的是,vlookup()函数、hlookup()函数是财务人员应该精于使用 的 函 数 , 另 外 还 有 一 些 非 常 有 用 的 函 数 如sumproduct()函数(本文未涉及),运用得好,可以大大节省人工, 成倍减少工作量, 并且运用vlookup(Hlookup)函数的精确查找功能,还能提高精确性。希望在以后的工作中能被大家借鉴使用, 以达到事半功倍的工作效率。
上一篇:事业部门预算管理现状及其社会功能提升
下一篇:规范农村集体财务管理的策略
重要提示:转载本站信息须注明来源:原创论文网,具体权责及声明请参阅网站声明。
阅读提示:请自行判断信息的真实性及观点的正误,本站概不负责。
别人都分享了,你还在等什么?赶快分享吧!
更多
浅析钢企财务报表中EXCEL函数的应用相关文章
我们的服务
联系我们
热门推荐
热门推荐
快速导航: