问题描述
我是excel新手，想知道excel是否内置了执行以下操作的功能.
I am an excel newb, wondering if excel has built in functionality to do the following.
打开此数据:
Name | Activity | Option
Bob | Monday Activities | Golf
Bob | Tuesday Activities| Kayak
Marge | Monday Activities | Spa
John | Tuesday Activities| Soccer
Liz | Tuesday Activities| Spa
进入:
Name | Monday Activities | Tuesday Activities
Bob | Golf | Kayak
Marge | Spa |
John | | Soccer
Liz | | Spa
推荐答案
数据透视表允许您操纵和显示数字数据，而不是诸如"kayak"之类的字符串，因此，尽管可以非常轻松地创建数据透视表，但只能计数/总和等，例如皮划艇，足球等实例，则不显示这些值.
Pivot tables allow you to manipulate and display numerical data, not strings such as "kayak", so while you can create a pivot table very easily, it will only count/sum etc. the instances of kayak, soccer etc., not display the values.
您可以重新格式化数据以产生一些内容，从而使您近似所需的输出:
You could reformat the data to produce something that gives you an approximation of your desired output:
Name Activity Golf Kayak Soccer Spa
Bob Monday 1
Bob Tuesday 1
Marge Monday 1
John Tuesday 1
Liz Tuesday 1(以上可能无法在此处正确显示，但如果使用选择性粘贴">文本"粘贴到Excel中，则将正确显示)
(above may not display here correctly, but will show correctly if pasted into Excel using Paste Special>Text)
但是，即使那样，您仍然必须结合使用= GETPIVOTDATA和其他公式才能获得令人满意的结果.由于很可能将错误引入数据中(拼写，尾随空格等)，因此这可能会变得非常混乱.考虑到这将要求您无论如何都要重新格式化数据(如果您这样做，则可以按照自己的意愿格式化它)，我想这可能对您没有多大用处.
However, even then you would still have to use a combination of =GETPIVOTDATA and other formulae to achieve a satisfactory result. This could get quite messy due to the very real possibility of errors being introduced into the data (spelling, trailing spaces etc.). Considering this would require you to reformat the data anyway (and if you are doing this you may as well just format it as you desire), I'm guessing this may not be much use to you.
实现所需目标所需的VBA并非易事，这在很大程度上取决于数据的确切布局以及哪些信息是已知的或可以从其他地方提取的，例如活动，名称.如果数据集不是很大，那么重新格式化的工作可能会比尝试编程解决方案少.
The VBA required to achieve what you want is non-trivial and would depend greatly on the exact layout of your data and what information is already known or could be pulled from elsewhere, e.g. the activities, names. If the dataset is not very large then the effort in reformatting would likely be less than attempting a programmatic solution.
这篇关于Excel枢轴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！