我有一个参加我一个人的人的一个相当有趣的问题枢轴桌网络研讨会。他们想知道他们是否可以拥有文本价值价值枢轴桌的区域?
这通常是我们通过各种不同聚合方法总结字段的区域,例如采取总和,平均,最小,最大值或标准偏差。
但事情是,这些聚合方法需要数字数据!
是否有任何方法总结基于文本的数据,该数据将返回文本的结果?
答案是肯定的,但我们需要使用数据模型和DAX公式来执行此操作。传统的枢轴表没有此功能。
此外,我们需要成为Excel 2013(或更高版本)和Office 365的PC用户。抱歉,但Mac版本中没有这些现代功能。
目录
视频教程
问题
在这里,我们有一个学生列表,以及他们注册的课程。当学生在多个课程中注册时,学生可以拥有多行数据。
我们可以用枢轴表总结这些数据,以便我们只显示每个学生一次,然后显示逗号分隔的课程列表?
插入枢轴表
首先,我们需要插入枢轴表。这是以通常的方式完成的。
在数据内选择一个单元格➜转到插入选项卡➜然后按下数据透视表按钮。
为了使用DAX公式,我们需要选择将其添加到数据模型中选项。
添加措施
使用传统的枢轴表,我们不需要定义任何计算。它们来预定义,基本和数量,计数,平均,最小,最大,标准偏差和方差计算。
通过数据模型,我们可以使用DAX公式访问可能的计算的全新世界。这些是通过添加创建的措施。我们可以在任何可以想象的任何计算中创建。
要添加一个度量,请选择枢轴表➜右键点击在数据表中找到的数据表数据透视表窗户➜选择添加措施从菜单。
这将打开措施对话框,我们可以创建DAX公式。
concatenatex功能
在“尺寸”对话框中:
- 我们需要选择表格要附加我们的措施,请给出措施名称和描述。
- 我们可以使用该探索可用的DAX公式插入功能菜单并检查我们写的任何公式的有效性。
- 我们可以在中写下我们的公式DAX公式编辑器。
- 我们可以分配格式化衡量。
要创建将文本聚合到逗号分隔列表中的措施,我们将使用concatenatex.DAX功能。
= concatenatex(StudentData,[课程],“,”)
我们需要将上述公式写入DAX公式编辑器然后我们可以通过按下来创建新的度量行按钮。
这将采取课程场面studentdata.表并将其值与逗号和空格字符一起连接为分隔符。
使用我们的新措施
这项新措施将出现在其中数据透视表与所有其他字段的窗口,我们可以像任何其他字段一样使用这种新措施。
可以轻松地从数据字段中识别措施FX.来自测量名称的图标。
我们可以单击并拖动课程列表衡量价值我们的枢轴桌区域,这将产生逗号分隔的学生课程清单。
结论
长期以来,枢轴表已经是Excel的一个功能,他们可以做很多很大的有用计算。
虽然它们是有限的,特别是在显示文本值时价值区域。
使用数据模型,我们获得许多新的计算选项,常规枢轴表刚刚没有,包括连接文本值以显示价值区域。这绝对是一个值得探索的功能,当常规枢轴表无法削减它时。
你好约翰,
这是非常有帮助的!我有些麻烦将我的新度量字段添加到值区域。出于某种原因,它没有填充。我试图为非常大的数据表(数万行)这样做。使用它与非常大的数据来源时无法处理它吗?其他措施(参考不同的字段)工作好。我尝试了在较小的100行数据样本上进行了这种特殊的措施,似乎工作。任何想法问题是什么以及如何解决它?
谢谢!
是的,X DAX函数迭代整个数据集,因此资源密集。
此外,单元格的限制为32k个字符,因此结果可能被截断。
如果源表最多约3,000行,我只能将度量添加到数据透视图值部分。在我的情况下,源数据是32,000行。因此,要接近我正在寻找的结果,我将源数据拆分为3,000行最大的新标准表,然后从各个标准表中创建各个枢零设备。然后我将个别的“琶音”粘贴到一个标准表中。
32k角色是我的问题。但是,它是因为小计,总计细胞可能超过限制。所以,我只是在我的数据透视表中切断了小计和辫子,现在它有效。我从中学到了:https://sfmagazine.com/post-entry/july-2018-excel-reporting-text-in-a-pivot-table/
YEP,每个单元只有32k个字符。
我喜欢这个新的发现,它为我的日常生活增加了一个全新的生产力水平......我用这个国家的主要表,以及客户所选择的各种配置选项。客户在哪个国家拥有多个实体的地方,我可能有几行为同一个国家,但是枢轴的结果自然复制如预期。这很好,在哪里有不同的选项,但是当该国每个实体正在设置相同时,有没有办法消除这些重复?例如:
而不是“德国”SSO,SSO,SSO,SSO |间接,间接,间接,间接,“
我想看看“德国|SSO |间接“
我希望太多了吗?
谢谢!
您可以尝试类似的内容来获取唯一物品:
= concatenatex(值(StudentData [课程]),StudentData [课程],“,”)
值函数从列中返回唯一项目。
omg omg omg omg omg!我一直在滋补这个星期,...... omg ......它有效!非常感谢你..我不能谢谢你!谢谢!
我们可以休息一下吗?通过使用任何分隔符?让我们只是说我们正在尝试通过添加一行中断来分隔细胞文本。
是的,char(10)作为分隔符将执行诀窍。
在使用CONCATENATEX函数时,如何使用枢轴表结果计算Excel计算文本单元格。我只想计算数字字段。
我不认为你可以。如果它们处于同一列中,则数据将全部是文本类型。数据类型只能分配给列,而不是列中的单个行。