I've got a data set in an Excel Pivot table:
我在Excel Pivot表中有一个数据集:
ID Owner Member
身份所有者会员
1 Bob kelly jeff Bob
1 Bob kelly jeff Bob
2 Chris Jim Sally
2 Chris Jim Sally
sorry about the formatting, I bolded the owner
抱歉格式化,我加粗了主人
I want to do a filter where I can see only the IDs where the Owner is not in the Member list for each ID. so for the example, I only want to see ID 2 where Chris is not a member
我想做一个过滤器,我只能看到所有者不在每个ID的成员列表中的ID。所以对于这个例子,我只想看到Chris不是会员的ID 2
Anyone know if I can do this in a pivot?
任何人都知道我是否可以在枢轴中做到这一点?
1 个解决方案
#1
0
Insert a new column A; this will be your filter column.
插入新列A;这将是您的过滤器列。
In cell A2, and dragged down, type:
在单元格A2中,并向下拖动,键入:
=Iserror(search(B2,C2&D2&E2))
This searches for B2 within all of cells C2, D2, and E2 combined. If you ever have more than 3 names, you will need to also &F2 etc.
这将在所有单元格C2,D2和E2中搜索B2。如果你有超过3个名字,你还需要和F2等。
If it searches for B2 within the other cells and finds a match, it will return the letter where the word in B2 first appears. Otherwise it returns an error. So if there is an Error, ISERROR will return TRUE. Then just filter on column A for any TRUE rows.
如果它在其他单元格中搜索B2并找到匹配项,它将返回首先出现B2中的单词的字母。否则返回错误。因此,如果出现错误,ISERROR将返回TRUE。然后只需在A列上过滤任何TRUE行。
EDIT
Alternative method, especially if there are often a changing number of people in the group.
替代方法,特别是如果组中的人数经常变化。
Insert a new row to the left of A, as above. In A2, and dragged down, type:
如上所示,在A的左侧插入一个新行。在A2中,并向下拖动,键入:
=iserror(Match(B2,C2:Z2,0))
This works exactly as above, except that instead of searching for a word WITHIN a single string of text (like search), Match searches to see if any a given cell exactly matches any other cell in a range. This will only work if formatting of names is the same throughout.
这完全如上所述,除了不是在单个文本字符串中搜索单词(如搜索),匹配搜索以查看给定单元格是否与范围中的任何其他单元格完全匹配。这仅在整个名称格式相同时才有效。
Let me know if this doesn't quite suit your needs.
如果这不太适合您的需求,请告诉我。
#1
0
Insert a new column A; this will be your filter column.
插入新列A;这将是您的过滤器列。
In cell A2, and dragged down, type:
在单元格A2中,并向下拖动,键入:
=Iserror(search(B2,C2&D2&E2))
This searches for B2 within all of cells C2, D2, and E2 combined. If you ever have more than 3 names, you will need to also &F2 etc.
这将在所有单元格C2,D2和E2中搜索B2。如果你有超过3个名字,你还需要和F2等。
If it searches for B2 within the other cells and finds a match, it will return the letter where the word in B2 first appears. Otherwise it returns an error. So if there is an Error, ISERROR will return TRUE. Then just filter on column A for any TRUE rows.
如果它在其他单元格中搜索B2并找到匹配项,它将返回首先出现B2中的单词的字母。否则返回错误。因此,如果出现错误,ISERROR将返回TRUE。然后只需在A列上过滤任何TRUE行。
EDIT
Alternative method, especially if there are often a changing number of people in the group.
替代方法,特别是如果组中的人数经常变化。
Insert a new row to the left of A, as above. In A2, and dragged down, type:
如上所示,在A的左侧插入一个新行。在A2中,并向下拖动,键入:
=iserror(Match(B2,C2:Z2,0))
This works exactly as above, except that instead of searching for a word WITHIN a single string of text (like search), Match searches to see if any a given cell exactly matches any other cell in a range. This will only work if formatting of names is the same throughout.
这完全如上所述,除了不是在单个文本字符串中搜索单词(如搜索),匹配搜索以查看给定单元格是否与范围中的任何其他单元格完全匹配。这仅在整个名称格式相同时才有效。
Let me know if this doesn't quite suit your needs.
如果这不太适合您的需求,请告诉我。