So I'm doing an analysis of values in a database and I figured the simplest way to do this is to use a pivot table. The problem I have is that some of the categories have multiple values in them. To show what I mean, I've included a sample of my excel file in this link: Excel Sample.
所以我正在对数据库中的值进行分析，我认为最简单的方法是使用数据透视表。我遇到的问题是某些类别中有多个值。为了说明我的意思，我在此链接中包含了我的Excel文件样本：Excel示例。
What I want to be able to do with my pivot is to, for example, filter Exposure Class by "N" and have all three entries with "N" show up. Is it possible to do this? I do have to note that this analysis doesn't have to be done in a pivot but the pivot does make the rest of the analysis a lot easier.
我希望能够用我的支点做的是，例如，按“N”过滤Exposure Class，并显示所有三个带有“N”的条目。是否有可能做到这一点？我必须注意，这种分析不必在枢轴中完成，但是枢轴确实使分析的其余部分更容易。
1 个解决方案
#1
3
Short answer: No -- each Excel cell contains a single value
简答：否 - 每个Excel单元格包含一个值
Long answer: Yes, of course! That single value can be a delimited string which splits into multiple fields, each of which corresponds to a value. Also, you can smuggle information into a cell using comments, formatting, etc. -- although such information isn't easy to manipulate without VBA.
答案很长：当然可以！该单个值可以是分隔的字符串，其分成多个字段，每个字段对应一个值。此外，您可以使用注释，格式等将信息走私到单元格中 - 尽管这些信息在没有VBA的情况下不易操作。
If the data in the cell looks something like "C2, S1, N" you can test if something like "S2" appears in it. Say that the string is in A1. Then the following formula returns TRUE or FALSE depending in whether or not S2 appears in it:
如果单元格中的数据看起来像“C2，S1，N”，您可以测试其中是否出现类似“S2”的内容。假设字符串在A1中。然后，以下公式将返回TRUE或FALSE，具体取决于S2是否出现在其中：
=IF(IFERROR(SEARCH("S2",A1),0) > 0, TRUE, FALSE)
(SEARCH isn't case-sensitive. Use FIND if you want a case-sensitive search). I don't have a lot of experience with pivot tables but I know that it is possible to filter a pivot table by a formula. You should be able to pull up all records in which e.g. "S2" appears in it.
（SEARCH不区分大小写。如果要进行区分大小写的搜索，请使用FIND）。我对数据透视表没有很多经验，但我知道可以通过公式过滤数据透视表。您应该能够提取所有记录，例如， “S2”出现在其中。
#1
3
Short answer: No -- each Excel cell contains a single value
简答：否 - 每个Excel单元格包含一个值
Long answer: Yes, of course! That single value can be a delimited string which splits into multiple fields, each of which corresponds to a value. Also, you can smuggle information into a cell using comments, formatting, etc. -- although such information isn't easy to manipulate without VBA.
答案很长：当然可以！该单个值可以是分隔的字符串，其分成多个字段，每个字段对应一个值。此外，您可以使用注释，格式等将信息走私到单元格中 - 尽管这些信息在没有VBA的情况下不易操作。
If the data in the cell looks something like "C2, S1, N" you can test if something like "S2" appears in it. Say that the string is in A1. Then the following formula returns TRUE or FALSE depending in whether or not S2 appears in it:
如果单元格中的数据看起来像“C2，S1，N”，您可以测试其中是否出现类似“S2”的内容。假设字符串在A1中。然后，以下公式将返回TRUE或FALSE，具体取决于S2是否出现在其中：
=IF(IFERROR(SEARCH("S2",A1),0) > 0, TRUE, FALSE)
(SEARCH isn't case-sensitive. Use FIND if you want a case-sensitive search). I don't have a lot of experience with pivot tables but I know that it is possible to filter a pivot table by a formula. You should be able to pull up all records in which e.g. "S2" appears in it.
（SEARCH不区分大小写。如果要进行区分大小写的搜索，请使用FIND）。我对数据透视表没有很多经验，但我知道可以通过公式过滤数据透视表。您应该能够提取所有记录，例如， “S2”出现在其中。