我可以将关系数据导入 Excel 数据透视表吗
我有一张表（以葡萄酒为例），列出了我酒窖中的每瓶葡萄酒、购买时间、支付金额等。
有一个专栏用逗号分隔的标签描述了葡萄酒，例如“果味” ， 白色的”。
我已经根据该数据创建了一个数据透视表，并将描述作为过滤器列。但是我无法通过“白色”过滤它。我必须找到所有包含“白色”的描述，例如“干，白”，“白，脆”等。
由于具有 RDBMS 背景，我的自然倾向是将标签放在自己的表中，并针对酒行进行键控，这样每行酒有零个或多个标签行。
我到底怎样才能用它来过滤酒行呢？
I have a sheet (let's go with wines as an example) that lists every bottle of wine in my cellar, when I bought it, how much I paid etc.
There's a column that describes the wine in comma-separated tags such as "Fruity, White".
I've created a pivot table from that data, with the description as a filter column. However I can't filter it by "White". I have to find every description that contains "White" such as "Dry, White", "White, Crisp" etc.
Being from an RDBMS background, my natural inclination is to put the tags in their own table keyed against the wine row so there's zero-or-more tag rows per wine row.
How, how on earth can I use that to filter the wine rows?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
是的，您可以在 Excel 中执行此操作，并且描述字段可以保留为“干、白”等，因为您不需要拆分逗号分隔值。
假设表源包含一个描述文本列、一个值数字列和一个购买年份数字列。
您的数据透视表设置有以下
字段：描述、价值和购买年份。
列标签：购买年份
行标签上有一个下拉标签过滤器 - 单击此选项，应该有一个选项选择标签过滤器。选择此项，然后选择包含。您可以输入“白色”，这将选择包含白色的所有描述，例如“干，白”，“白，脆”。过滤器包括？表示单个字符，* 表示任意一系列字符。
对于“开头为”和“结尾为”以及否定，有类似的标签过滤器。
我在 Excel 2007 中尝试过此操作，它也应该在 2003 年中工作。我认为在 Excel 2003 中，您甚至可以组合过滤器，例如包含“白色”且不包含“干”，但在 2007 年我找不到执行此操作的方法。
Yes you can do it within Excel and the description fields can remain as "Dry, White" etc as you do not need to split the comma separated values.
Lets say the Table source comprises a text column for Description, a number column for Value and a number column for Year Bought.
Your pivot is setup with the the following
Fields: Description, Value and Year Bought.
Column labels: Year Bought
There is a drop down label filter on the row labels - click on this and there should be an option to select Label Filters. Select this and then select Contains. You can enter say "White" which will select all your descriptions that contain white e.g. "Dry, White", "White, Crisp". The filter includes ? to represent a single character and * to represent any series of characters.
There are similar label filters for "begins with" and "ends with" as well as there negation.
I tried this in Excel 2007 and it should also work in 2003. I think in Excel 2003 you could even combine the filters e.g. contains "White" and does not contain "Dry" but in 2007 I could not find a way of doing this.
如果我说的是显而易见的事情，请原谅我，但您在这里遇到问题的原因是描述列不在 1NF 中，并且 Excel 数据透视表界面不够灵活，无法允许基于模式的搜索。
最简单的选择是将 CSV 规范化为一系列列，每一列代表一个属性 - 一列表示葡萄酒颜色，一列表示甜度，一列表示原产国等 - 并在多个列中应用过滤器。但是，如果（正如您对问题的评论所暗示的那样）葡萄酒是您真正问题的隐喻，那么您可能没有机会重新审视源数据的设计。
另一种可能性可能是使用宏（或数据库查询 - 从您的问题中我不清楚您是否已经实现了标签系统）来根据您的标签值预先过滤数据透视表源表上的输入数据想要搜索，然后根据该数据重新刷新数据透视表。
第三种可能性是这个问题中使用的VBA，看起来像它将自定义过滤数据透视表的可见行。
Forgive me if I'm stating the obvious, but the reason you're having problems here is that the description column is not in 1NF, and the Excel pivot interface isn't flexible enough to allow pattern-based searching.
The simplest option will be to normalise the CSV into a series of columns, each of which represents a single attribute - one column for wine colour, one for sweetness, one for country of origin and so on - and apply the filter across multiple columns. However, if (as your comment on the question suggests) wine is a metaphor for your real problem, you may not have the luxury of revisiting the design of the source data.
Another possibility might be to use a macro (or a database query - I'm not clear from your question whether you have implemented the tag system already) to pre-filter the input data on the pivot table's source sheet based on the tag values you want to search for, then re-refresh the pivot table based on that data.
A third possibility is the VBA used in this question, which looks like it will custom-filter the pivot table's visible rows.
=IF(ISERR(FIND("WHITE",UPPER(B5))),0,1)
创建一个额外列并添加公式。这有两个技巧。一种是使用 upper - 在描述列中搜索 WHITE，以克服 excel find 区分大小写的事实。第二个是，如果字符串不存在，它会返回一个值错误 - 因此 iserr 将允许您捕获该错误，并在此示例中返回 0（如果不存在）或 1（如果存在）。您可以用白色和空白代替 1 和 0。
=IF(ISERR(FIND("WHITE",UPPER(B5))),0,1)
create an extra column and add a formula. There are 2 tricks to this. One is to search for WHITE in the description column using upper - to beat the fact that excel find is case sensitive. Two is that it returns a value error if the string does not exist - so iserr will allow you to trap that and return in this example 0 if it doesn't or 1 if it does. You could substitute white and blank for 1 and 0.
您可以编写一个脚本来循环数据并为描述列中的每个逗号分隔项添加新行。这将使数据透视表能够更好地进行过滤。
you could write a script that loops through the data and adds new lines for each comma separated item in the description column. This would allow the pivot table to filter better.