vba 基于excel中的第二列创建子列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/790323/
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
Creating a sublist based on a second column in excel
提问by Scott Chamberlain
I have two columns, the first column will have the name of a object, the second is who it belongs to. I want a new sheet for each person to list what they had assigned to them. here is a example:
我有两列，第一列将有一个对象的名称，第二列是它属于谁。我希望每个人都有一张新表，列出他们分配给他们的内容。这是一个例子：
dog F
cat F
bell S
whistle
bird F
So Fred has a dog, cat, and a bird; Scott has a bell; and no one has a whistle on their page. Now doing a simple IF() i can get it to look like this for Fred's page
所以弗雷德有一只狗、一只猫和一只鸟；斯科特有一个铃铛；没有人在他们的页面上吹口哨。现在做一个简单的 IF() 我可以让它在 Fred 的页面上看起来像这样
TOP OF ROW
dog
cat
bird
And Scott's page will look like
斯科特的页面看起来像
TOP OF ROW
bell
however I want Fred's to look like
但是我希望弗雷德看起来像
TOP OF ROW
dog
cat
bird
and Scott to be the same.
和斯科特是一样的。
My current train of thought is to use =VLOOKUP($C$1,Items!A2:C1000,3)in a hidden column in D to tell me which row my data is in, (where Column C on Items is a hidden column with the row number of the row and C1 is the search parameter (S or F)), then =IFERROR(CELL("contents",INDIRECT(ADDRESS($D2,2,1,TRUE,"Items"))),""), however I other than changing my row index of my search array to 1+ the last found item (which i have not figured out how to do) I can not figure out how to continue searching for the next item.
I know C++ and C# but never have coded in VBA before and I rely heavily on the MSDN and to my knowelge there is no MSDN section dedicated to the Excel API.
我目前的思路是=VLOOKUP($C$1,Items!A2:C1000,3)在 D 中的一个隐藏列中使用来告诉我我的数据在哪一行（其中 Items 上的 C 列是一个隐藏列，其中包含该行的行号，C1 是搜索参数（S 或F))，然后=IFERROR(CELL("contents",INDIRECT(ADDRESS($D2,2,1,TRUE,"Items"))),"")，但是我除了将搜索数组的行索引更改为 1+ 最后找到的项目（我还没有想出该怎么做）之外，我不知道如何继续搜索下一个项目。我知道 C++ 和 C#，但以前从未在 VBA 中编码过，我非常依赖 MSDN，据我所知，没有专用于 Excel API 的 MSDN 部分。
回答by Henrik K
One way of achieving the list that you are looking for without any VBA code is to make use of the advanced filter.
在没有任何 VBA 代码的情况下获得您正在寻找的列表的一种方法是使用高级过滤器。
On sheet 1 the input list is entered as follows
在工作表 1 上，输入列表输入如下
on sheet2 enter the filter criteria (this criteria means contains F in Owned by column)
在 sheet2 上输入筛选条件（此条件表示在 Ownered by 列中包含 F）
and finally on sheet3, call the advanced filter function like this
最后在sheet3上，像这样调用高级过滤器功能
make sure to select copy to another location
确保选择复制到另一个位置
select the sheet1 input list as the list range
选择 sheet1 输入列表作为列表范围
select the sheet2 filter criteria as the criteria range
选择 sheet2 过滤条件作为条件范围
and select somewhere in sheet3 as the output range (the copy to entry)
并选择 sheet3 中的某处作为输出范围（复制到条目）
If you prefer to go down the route of excel VBA programming a good first step is to try out the macro recorder in excel (tools - macros - record macro)
如果你更喜欢走 excel VBA 编程的路线，一个好的第一步是在 excel 中试用宏记录器（工具 - 宏 - 记录宏）
good luck!
祝你好运！
回答by Alex Andronov
There is a good way of doing this with functions in excel.
有一个很好的方法可以使用 excel 中的函数来做到这一点。
Essentially you need to create a running countif
基本上你需要创建一个正在运行的 countif
So in C2 you would have =COUNTIF($B$2:$B2,"F")Obviously the "F" could also be a reference to another cell. If you fill this formula down the range it will expand the range. Eg. in C3 it will say =COUNTIF($B$2:$B3,"F")
因此，在 C2 中，=COUNTIF($B$2:$B2,"F")显然“F”也可以是对另一个单元格的引用。如果您在范围内填充此公式，它将扩大范围。例如。在 C3 中它会说=COUNTIF($B$2:$B3,"F")
This will give you a running total in column C in your example this would mean:
在您的示例中，这将为您提供 C 列中的运行总数，这意味着：
dog F 1
cat F 2
bell S 2
whistle 2
bird F 3
狗 F 1
猫 F 2
铃 S 2
哨子2
鸟 F 3
The fact that you have 3 2s doesn't matter because a vlookup will always match to the first match it finds.
您有 3 个 2 的事实并不重要，因为 vlookup 将始终与它找到的第一个匹配项匹配。
This technique has a lot of different applications. And depending on the data you may find it easier to put this on the left of the data so the VLOOKUP will be easier.
这种技术有很多不同的应用。根据数据，您可能会发现将其放在数据左侧更容易，因此 VLOOKUP 会更容易。
