Excel如何以Index函数查找传票摘要




有读者来信,提供我一连串像是传票摘要的资料,然后是一组客户名称,想要从摘要裡,把有包含的客户名称抓出来。我联想到常常有些时候,手上只有明细分类帐,系统没有合适报表,而我们又需要把一张张传票,依照某种方式分类彙总,像这种状况,以下的笨方法,也许可以考虑:

一、如图所示,读者案例:「★A栏位有出现D栏位的客户名,将客户名回传至B栏位」。

A栏位有出现D栏位的客户名,将客户名回传至B栏位

二、首先,我需要把「比对」那一栏垂直排列的客户名称,转换成水平排列,第一个想到的是「TRANSPOSE」函数,客户有六个,所以先选取「C1」到「H1」一列六个储存格,直接输入「=TRANSPOSE(A2:A7)」,由于是阵列资料,公式输完必须在公式栏先按住「Ctrl」及「Shift」,再按下「Enter」,结果如图所示。

=TRANSPOSE(A2:A7)

三、「TRANSPOSE」有个缺点,要先看垂直有多少家客户,然后选择多少个水平储存格范围,这个工作量,如果是十家以内还好,但是,一般正常公司,少说也有几十家客户,用「TRANSPOSE」阵列操作起来,非常麻烦。依照Excel惯用手法,最好是有个函数能一直拉下去的,例如:「INDEX」!

依照Excel惯用手法,最好是有个函数能一直拉下去的,例如:「INDEX」

四、输入公式:「=INDEX($A:$A,COLUMN(A1)+1)」,INDEX函数是像座标轴般,超连结引用其它储存格的内容,例如这裡,我引用范围是整个A栏,一开始「C1」我引用的是「A1」栏数(1)再加1,也就是第二列,A栏第二列:「大乐」。将公式往右拉,便把垂直的客户名称序列,水平往右拉下去。

=INDEX($A:$A,COLUMN(A1)+1)

五、接下来介绍「FIND」函数:「=FIND(C$1,$B2)」,意思是要在「B2」裡找出「C1」的起始位置,在「C2」这裡是找不到,所以显示「#VALUE!」,不过公式拉到了「E2」,在「B2」字串第五个字元开始,正是「大润发」,所以显示结果是「5」,如此,应能理解「FIND」函数的妙用了吧。

=FIND(C$1,$B2)

六、「=IF(IFERROR(FIND(C$1,$B2),0)>0,COLUMN(C2)-2,0)」,Excel翻译糕:摘要裡如果找不到客户名称,便显示「0」,找得到(起始字元>0)便显回本身储存格栏数减2,「C2」(栏数3)减2是1、「D2」(栏数4)灭2是2,因为「C2」和「D2」摘要裡没有相对应「C1」和「D1」的客户,所以显示「0」,到了「E2」,摘要裡有客户,因此显示「3」,「G4」显示「5」、「H3」显示「6」,其馀储存格显示「0」,都是同样道理。

=IF(IFERROR(FIND(C$1,$B2),0)>0,COLUMN(C2)-2,0)

七、仔细想想上一步骤造成的结果,每一列摘要如有出现客户名称,那一列客户所在栏位,便会显示客户序号,例如「E2」的「3」。依照如此规则,再下最后一个公式:「=INDEX($F$1:$K$1,SUM(F2:K2))」,可在函数视窗裡参考说明。

=INDEX($F$1:$K$1,SUM(F2:K2))

八、呼,好了,经过这麽一个实例,应该彻底瞭解「INDEX」函数了吧,妙用无穷呢!

经过这麽一个实例,应该彻底瞭解「INDEX」函数了吧


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


當前文章分類:
Excel