在Excel中进行枢纽操作而不显示汇总，以显示文本，而不显示数字? [英] Pivot in Excel without aggregation, to show text, not numbers?

问题描述

假设我有一个这样的表:

Let's say I have a table like this:

Country Region  Mytext
USA     North   a
USA     South   b
Brasil  North   c
Brasil  South   d

如何在Excel中获得这样的数据透视?

How can I obtain a pivot like this in Excel?

Country  North         South
USA      a             b
Brasil   c             d

如果'mytext'是一个数字，我可以做一个数据透视表:由于每个国家和地区的组合只有一行，因此min = max = sum = avg，并且所有这些汇总函数实际上都将显示我想要的结果.

If 'mytext' were a number, I could do a pivot table: since there is only one row per combination of country and region, min = max = sum = avg and any of these aggregate functions would, in fact, show me the result I want.

但是，当我想要的字段为非数字时该怎么办?使用Python的pandas库，即使在非数字字段上也可以使用dataframe.pivot()方法，但是我还没有找到在Excel中执行相同操作的方法.我可以将一个数字关联到每一行，在Excel中进行枢轴显示以显示该数字，然后进行查找以获取关联的文本，但这似乎是一个相当复杂的过程，实际上应该更容易做到这一点！

But what when the field I want is non-numeric? With Python's pandas library, I can use the dataframe.pivot() method even on non-numerical fields, but I haven't found a way to do the same in Excel. I could associate a number to each row, do the pivot in Excel to show that number, and then do a lookup to get the associated text, but it seems a rather convoluted process for something which should really be easier!

推荐答案

，您可以使用 MS Power Query Add-in *.不使用vb​​a进行数据透视表很容易.它是免费的，并且对数据转换非常有效.

you can use the MS Power Query Add-in*. Pivoting tables is without vba right easy. It's free and very effective for data Transformation.

M代码

 let
    Source = Excel.CurrentWorkbook(){[Name="table1"]}[Content],
    #"Pivot column" = Table.Pivot(Source, List.Distinct(Source[Region]), "Region", "Mytext")
in
    #"Pivot column"

您的输出

´*，已完全作为

´* from MS Office 2016, it's fully integrated in Excel as Get & Transform function.

这篇关于在Excel中进行枢纽操作而不显示汇总，以显示文本，而不显示数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！