在没有聚合的情况下在 Excel 中透视以显示文本而不是数字? [英] Pivot in Excel without aggregation, to show text, not numbers?

问题描述

假设我有一张这样的桌子:

国家地区Mytext美国北部美国南部 b巴西北部 c巴西南部

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

国家南北美国 a b巴西

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

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

解决方案

你可以使用

´* 来自 MS Office 2016，它以 获取 &变换函数.

Let's say I have a table like this:

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

How can I obtain a pivot like this in Excel?

Country  North         South
USA      a             b
Brasil   c             d

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.

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!

解决方案

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.

the M-Code

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

your Output

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

这篇关于在没有聚合的情况下在 Excel 中透视以显示文本而不是数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！