☰

使用逗号分隔单元格的数据透视表

tag-icon • tag-icon microsoft-excel pivot-table
使用逗号分隔单元格的数据透视表

我需要模拟逗号分隔单元格的数据透视表，并输出到逗号分隔单元格中，按国家/地区分组。我有大约 100 行此类数据，因此无法转换为列或行。

数据

Col A | Col B

国家1、国家1、国家3、国家4、国家4、国家4、国家8|数字1、数字2、数字3、数字4、数字5、数字6、数字7

需要的输出

国家1,国家3,国家4,国家8|(数字1+数字2),数字3,(数字4+数字5+数字6),数字7

我对数据透视表还不熟悉，任何宏或 VBA 函数都可以。有什么想法吗？

答案1

以下公式将在 Excel 2007 和 2010 中起作用，假设国家单元格为 A1，值单元格为 B1，且两个单元格均包含 7 个值，并以 6 个逗号分隔：

国家：

    =LEFT(A1,SEARCH(",",A1)-1)&","&LEFT(RIGHT(A1,LEN(A1)-SEARCH(",",A1,SEARCH(",",A1)+1)),SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1)+1)+1)-SEARCH(",",A1,SEARCH(",",A1)+1)-1)&","&LEFT(RIGHT(A1,LEN(A1)-SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1)+1)+1)),SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1)+1)+1)+1)-SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1)+1)+1)-1)&","&RIGHT(A1,LEN(A1)-SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1,SEARCH(",",A1)+1)+1)+1)+1)+1))

值：

     =LEFT(B1,SEARCH(",",B1)-1)+LEFT(RIGHT(B1,LEN(B1)-SEARCH(",",B1)),SEARCH(",",B1,SEARCH(",",B1)+1)-SEARCH(",",B1)-1)&","&LEFT(RIGHT(B1,LEN(B1)-SEARCH(",",B1,SEARCH(",",B1)+1)),SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)-SEARCH(",",B1,SEARCH(",",B1)+1)-1)&","&LEFT(RIGHT(B1,LEN(B1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)),SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)-1)+LEFT(RIGHT(B1,LEN(B1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)),SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)+1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)-1)+LEFT(RIGHT(B1,LEN(B1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)+1)),SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)+1)+1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)+1)-1)&"," &RIGHT(B1,LEN(B1)-SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1,SEARCH(",",B1)+1)+1)+1)+1)+1))

这也可以用 VBA 来实现。如果您使用的是旧版本的 Excel，请告诉我，我可以发布。

相关内容