Excel随心所欲 二维变一维

发布时间:2019-12-24 来源:网络


在办公过程中,经常会遇到多行多列的数据,因为工作需要,我们要将这种多行多列的数据转化为一列或一行。得高手指点,结合在工作中遇到的问题以及解决办法,分享出来和大家一起交流和学习。


如图1,是在某一个会议的座区图,领导要求将座区图中的名字汇总按拼音排成一列。



(图1)


一、将会场座区示意图稍微处理下,删除多余的行和列,变成多行多列的数据。(如图2)


(图2)


二、在空白单元格如H1单元格,输入以下数组公式:


=INDIRECT(TEXT(RIGHT(SMALL(COUNTIF($A$1:$G$4,"<"&$A$1:$G$4)*10^8+ROW($1:$4)*1000+COLUMN(A:G),ROW(1:1)),8),"R0C000"),0)

然后按CTRL+SHIFT+ENTER组合键,下拉,直至出现#NUM!的错误标识,如图3所示。



(图3)


公式解析:


1、公式中INDIRECT函数R1C1的引用模式;


2、用COUNTIF函数对A1:G4单元格区域中的各文本进行比大小(按拼音),分别得出各单元格文本在A1:G4单元格区域的大小(排名,以数字呈现);


3、用SMALL函数来提取第几小的“排名”;


4、在用COUNTIF得出的排名扩大100000000倍,然后再加单元格所在行号的1000倍,之后加上列号,后8位数为行号和列号的字符。如“阿明”在进行TEXT函数转化时前会得到如下“函数”:

=INDIRECT(TEXT(RIGHT(2003,8),"R0C000"),0)

其中,2003是“00002003”,由于2003前面4个0属于无意义的0,不予显示。


5、用INDIRECT函数来定位,即可以得到R2C3的单元格内容,即“阿明”的文本内容。


6、注意:对于本文的例子中,RIHGT函数中,提取长度可以不用为8,只要不小于4就可以。但如果需要处理文本所在行数大于10,就需要相应的变化,所以建议用8来提取长度。


7、如果有童鞋还不是很了解,可以通过【公式】选项卡里面的“公式求值”来进一步了解函数是怎么运行的。


三、如果要求用姓氏笔画来排名,可以用复制成文本来取消公式,然后用笔画排序来实现。如图4所示。

 

(图4)


四、如果需要排成一行,可以在排成一列之后,用选择性粘贴中的“转置”来实现,在此就不再演示。