Question

excel单元格A1,A2,A3是同样的内容如是姓名张三,但B1,B2,B3内容不同分别为语言，数学、...

如上图，A列为姓名，B列为科目

想得到图二的结果或图三的结果

我要怎麼做，用函数
数据还大不想用复制，选择性粘贴+转置





Answer 1

在原表第一行插入一行，张三那一列输入姓名，第二列输入科目，再插入一列数值，全部为1。用数据透析表，将姓名拉入行数据，科目拉入列数据，数值接入数据中，复制出来后再将数值为1的替换成列首就可以了

不知道是否帮到你

Answer 2

在E1输入数组公下拉，输入完成后不要直接回车，要按三键  CTRL+SHIFT+回车  结束

=INDEX(A:A,SMALL(IF(A$1:A$999>"",IF(MATCH(A$1:A$999,A$1:A$999,)=ROW($1:$999),ROW($1:$999),4^8)),ROW(A1)))&""

 

在F1输入数组公右拉再下拉，输入完成后不要直接回车，要按三键  CTRL+SHIFT+回车  结束

=INDEX($B:$B,SMALL(IF($A$1:$A$999=$E1,ROW($1:$999),4^8),COLUMN(A1)))&""

 

请点击这回答右下角绿色的“点击下载”按钮下载文件试用效果。

追问

我想在SHEET2中该怎麼做？非常感谢！

追答

在SHEET2   A1输入数组公下拉，输入完成后不要直接回车，要按三键  CTRL+SHIFT+回车  结束

=INDEX(Sheet1!A:A,SMALL(IF(Sheet1!A$1:A$999>"",IF(MATCH(Sheet1!A$1:A$999,Sheet1!A$1:A$999,)=ROW($1:$999),ROW($1:$999),4^8)),ROW(A1)))&""

 

在SHEET2 B1输入数组公右拉再下拉，输入完成后不要直接回车，要按三键  CTRL+SHIFT+回车  结束

=INDEX(Sheet1!$B:$B,SMALL(IF(Sheet1!$A$1:$A$999=$A1,ROW($1:$999),4^8),COLUMN(A1)))&""

 

修改好了，我把文件传上来，请下载试用效果，改为在SHEET2工作表中统计信息。

追问

请问4^8是什麼意思

追答

4^8就是4的8次方，等于65536，就是用INDEX函数读取这一行的内容，一般用于当没有符合条件时就让INDEX函数读取这一行内容，在数据量不多的表格中，这行一般都是使用不到，是空白的，就是让公式在没有符合条件的时候，让公式返回空白值。
4^8还有一个作用就是为了兼容EXCEL 2003，因为EXCEL 2003最大支持的行数就是65536行，如果把这个数定设计大于4^8在EXCEL 2003中运行，公式是会出错的。

Answer 3

　　用函数需要两个公式:

　　C2公式(黄色区域)

• =INDEX(A:A,MIN(IF(COUNTIF($C$1:C1,A1:A1000),4^8,ROW(1:1000))))&""

　　下拉

　　D2公式(绿色区域)

• =INDEX($B:$B,SMALL(IF($A$1:$A$1000=$C2,ROW($1:$1000),4^8),COLUMN(A1)))&""

　　右拉, 下拉

　　注: 两个都是数组公式, 粘贴完公式后, 按下F2, 再同时按 Ctrl+Shift+回车健 三键结束, 这是数组公式的输入方法, 没有这步, 结果不会正确.

　　参考下图:

　　

追问

我想在SHEET2中该怎麼做？非常感谢！

追答

　　原数据所在表名是Sheet1吗? 如果是的话, 继续看下面:

　　Sheet2!A2数组公式:

• =INDEX(Sheet1!A:A,MIN(IF(COUNTIF($C$1:C1,Sheet1!A1:A1000),4^8,ROW(1:1000))))&""

　　下拉

　　Sheet2!B2数组公式:

• =INDEX(Sheet1!$B:$B,SMALL(IF(Sheet1!$A$1:$A$1000=$C2,ROW($1:$1000),4^8),COLUMN(A1)))&""

　　右拉, 下拉

Answer 4

用数据透视表