Excel如何用index函数自动排序数据




会计工作实务上,排序是很常用工具。无论是审计查账或者公司财务分析,我们强调重大性原则,交易金额越大的交易越值得重视,金额太小的可以忽略。除了金额之外,也经常就日期作排序,例如逾期帐款,我们希望将逾期越久的排在越上面,因为这是必须重点追踪的异常项目。针对这个需求,会计人应该很习惯使用Excel的排序功能,以下要介绍的,是如何用index函数达到自动排序:

一、应收帐款明细表,有个字段是标明是否逾期,后面两栏是逾期天数和逾期金额。

后面两栏是逾期天数和逾期金额

二、在后面新增一栏,输入:「=COUNTIF($E$2:E7,E7)」这个公式能找出有相同逾期天数的帐款。并无逾期的,显示为零,60天都有三笔帐款逾期,依序显示为「1」、「2」、「3」。

=COUNTIF($E$2:E7,E7)

三、输入另一个公式:「{=LARGE(IF($D$2:$D$9=”Y”,$E$2:$E$9),ROW()-1)}」LARGE函数能找出某个范围第几大的值,参数一是范围,这里弄了一个IF数组函数,「$D$2:$D$9」有标注「Y」,取「$E$2:$E$9」相对的值,所以LARGE的范围便是「30,30,60,60,60,90」。参数二表示第几大,这个使用:「ROW()-1」,配合一开始为第二列拉下来,刚好是从1递增的数列(1,2,3,……)。公式结果如图所示,有一点必须特别说明,因为这是数组公式,记得最后在数据编辑列输入组合键:「Crtl+Shift+Enter」。

{=LARGE(IF($D$2:$D$9="Y",$E$2:$E$9),ROW()-1)}

四、接下来公式较为复杂:「=SUMPRODUCT(($E$2:$E$9=H4)*($G$2:$G$9=COUNTIF($H$2:H4,H4))*ROW($E$2:$E$9))」。可以将Sumproduct函数视为多条件的Vlookup,以I4为例,条件一是E栏中等于H4,E4、E6、E7都合乎资格,条件二是取出现次数相同的帐款,COUNTIF($H$2:H4,H4)的值是2,所以是取G2到G9中等于2的单元格,条件一和条件二合起来,便是E6单元格,而Sumproduct要取的是ROW($E$2:$E$9),也就是列数,因此I4的计算结果是「6」,表示E6位于第六列的意思。

=SUMPRODUCT(($E$2:$E$9=H4)*($G$2:$G$9=COUNTIF($H$2:H4,H4))*ROW($E$2:$E$9))

五、得到了依照条件排序的列号,再使用Index函数抓取数据:「=IFERROR(INDIRECT(”B”&$J2),””)」。顾名思义,Index是以间接方式连结单元格,这里的「”B”」,表示引用B栏,「&」后面的「$J2」,表示要引用B栏中的第几列,「$」是把字段固定位,不会随着单元格拖曳而变动,「IFERROR(……,””)」是指计算出现错误时显示空白,例如K8和K9,把公式往下拉的结果如图所示。

=IFERROR(INDIRECT("B"&$J2),"")

六、以上个步骤为基础,继续开展下去,便可以将原来的数据复制过来,呈现出来的报表,已经依照逾期天数排序。

呈现出来的报表,已经依照逾期天数排序

熟悉Excel操作的人,应该都知道系统默认的排序功能非常好用,这篇文章花了很多心思设置函数,达到了只是相同效果。这样做的缺点是一开始架公式比较麻烦,但优点是一旦架好了,可以重复利用,往后只要有新的资料,直接把内容贴值进去,马上就会得到排序好的报表,所以特别适用于例行性的报表,例如这篇文章使用的逾期应收范例。


有系統、全面性的學習Excel職場應用,:會計人的Excel小教室PressPlay頻道


當前文章分類:
Excel