vba 垂直和水平过滤的最佳方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16822916/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to filter both Vertically and Horizontally?
提问by Batman
I have a large data set which contains users and the groups they belong too (they can be in many groups). I currently have 265 users and 94 groups. I'd like to find the best way to display this information. Right now I have a very large spreedsheet that shows me this information, but I can only filter the groups (column) to find out which users are in that group. Ideally, It would be great to also be able to filter the users to find all the groups on user belongs too.
我有一个大数据集，其中包含用户和他们所属的组（他们可以在许多组中）。我目前有 265 个用户和 94 个组。我想找到显示此信息的最佳方式。现在我有一个非常大的电子表格，可以向我显示这些信息，但我只能过滤组（列）以找出该组中的哪些用户。理想情况下，如果还能够过滤用户以查找用户所属的所有组，那就太好了。
Here's a demo of what my sheet looks like:
这是我的工作表外观的演示：
Transposing won't work with 265 users and I'd rather not have two spreadsheets with the same information. Any ideas? Thanks.
移调不适用于 265 个用户，我宁愿没有两个具有相同信息的电子表格。有任何想法吗？谢谢。
回答by Brian Camire
Store your data in one table with two columns, "User Name" and "Group Name", and one record for each group that each user belongs to. For example, the first few rows might be:
将您的数据存储在一个包含“用户名”和“组名”两列的表中，每个用户所属的每个组都有一条记录。例如，前几行可能是：
User Name Group Name
User 1 Group 1
User 1 Group 5
User 2 Group 5
User 2 Group 6
You can filter this table on "User Name" to see what groups a specific user belongs to, or on "Group Name" to see what users belong to a specific group.
您可以按“用户名”过滤此表以查看特定用户属于哪些组，或按“组名”过滤以查看哪些用户属于特定组。
If you want to see your table in a "matrix" form, create a pivot table based on the table.
如果您想以“矩阵”形式查看您的表格，请根据该表格创建一个数据透视表。
As a one-time exercise to convert your data from its current format to the new format, you might:
作为将数据从当前格式转换为新格式的一次性练习，您可以：
- Create a new worksheet.
- In the new worksheet, enter the following column headings in the first row of columns A through F, respectively: Cell Number, Row Number, Column Number, Cell Value, User Name, and Group Name.
- In the new worksheet, enter the following formulas:
=ROW()-1in cell A2,=INT((A2-1)/94)+1in cell B2,=MOD(A2-1,94)+1in cell C2,=INDEX('Your Existing Data'!$E$2:$CT$266,B2,C2)in cell D2,=INDEX('Your Existing Data'!$A$2:$A$266,B2,1)in cell E2, and=INDEX('Your Existing Data'!$E$1:$CT$1,1,C2)in cell F2. This assumes that your existing data is in a worksheet named "Your Existing Data" and laid out as shown in the image referenced in your comment. - Copy the formulas down to row 24911 (265 * 94 + 1).
- Copy the formulas and paste over them with their values.
- Use a filter to delete the rows that don't have an X in the Cell Valuecolumn.
- Delete columns A through D.
- 创建一个新的工作表。
- 在新工作表中，分别在 A 到 F 列的第一行中输入以下列标题： 单元格编号、行编号、列编号、单元格值、用户名和组名称。
- 在新工作表中，输入以下公式：
=ROW()-1在单元格 A2、=INT((A2-1)/94)+1单元格 B2、=MOD(A2-1,94)+1单元格 C2、=INDEX('Your Existing Data'!$E$2:$CT$266,B2,C2)单元格 D2、=INDEX('Your Existing Data'!$A$2:$A$266,B2,1)单元格 E2 和=INDEX('Your Existing Data'!$E$1:$CT$1,1,C2)单元格 F2 中。这假设您的现有数据位于名为“您的现有数据”的工作表中，并按照您的评论中引用的图像所示进行布局。 - 将公式向下复制到第 24911 行 (265 * 94 + 1)。
- 复制公式并粘贴它们的值。
- 使用过滤器删除“单元格值”列中没有 X 的行。
- 删除 A 到 D 列。
If you want, you can add also formulas to the new worksheet to look up the first name, last name, and e-mail from your existing data based on the full name.
如果需要，您还可以向新工作表添加公式，以根据全名从现有数据中查找名字、姓氏和电子邮件。
