Excel：从一行中查找唯一值并为其创建一列？

我觉得有一种方法可以用 TXTSPLIT 或 regex 之类的东西来做到这一点，但我只有 2013 年，显然你这样做了，所以这些不是一个选择。

这只是我头脑中的一个思考过程。我首先将第二列复制到您计划放置表格的工作表中。然后我将通过选择您的数据，然后在您的功能区中选择“文本到列”来开始该工作表。

然后你应该在这个屏幕上结束

选择“分隔”单选按钮，然后按Next。

Make sure you select Comma as your delimiter, or whatever matches what is separating your words. You may want to select "Treat consecutive delimiters as one" if you have gaps in you data such as Apple,,Brains, Beaver Tails. Make sure you do not have space selected as a delimiter incase you have multi word data between your delimeters.

Your data should then be split into multiple columns with each word in its own cell.

I would then select each individual column then choose the remove duplicates button. I tried doing multiple columns at once but it did not remove duplicates in all columns when I did that.

So select your column(s)

Then select remove duplicates from the ribbon

I selected continue with selection as I only wanted to deal with one column at a time.

I left the "My data has headers" unchecked and hit ok

And voila we have a unique list in the column

Repeat this for all columns. Then move your columns into one column and repeat the duplicate process. You could have done this step first but it may have been longer columns to deal with moving around. In the end you should wind up with one column of unique words.

Here all the columns are unique within the column but not the table.

So the columns get stacked into 1

And after running remove duplicates on the single column the following unique list is obtained.

Now you can take that data and transpose it to become your column headers and with a minor tweak you can make your table look like:

So now that we have our table setup, I would use the following formula in B2 of the table and copy down and right. Note the $ to lock the row or column reference.

=IF(ISNUMBER(FIND(B$1,$B12)),"yes","no")
'FIND is case sensitive

Edit

=IF(ISNUMBER(SEARCH(B$1,$B12)),"yes","no")
'SEARCH is NOT case sensitive

In the case of this formula, B$1 is your header row, and $B12 is the first row of column two with data.

Results look like the following based on the date being layed out in B12:b15

Formulaic alternative

place the following in the third column copy right and down as far as needed.

=TRIM(MID(SUBSTITUTE(","&$B2&REPT(",",6),",",REPT(" ",255)),COLUMNS($B2:B2)*255,255))

I personally would copy it over all the way to column z or so first, then down your 10K rows.

Now for the part that will bog your system down. THIS WILL BE A HUGE AMOUNT OF CALCULATIONS FOR EACH CELL THE FORMULA WINDS UP IN. It will be generating the unique list. Use the following in an unused column starting in the second row. convert the range G12:J15 to match B2:Z10000 or whatever range you wound up with. Change $L$11:L11 to empty space above where you want the first unique name to appear. So if you wanted to start your list in AA2 change it to $AA$1:AA1.

=INDIRECT(TEXT(MIN(IF(($G$12:$J$15<>"")*(COUNTIF($L$11:L11,$G$12:$J$15)=0),ROW($G$12:$J$15)*100+COLUMN($G$12:$J$15),7^8)),"R0C00"),)&""

这是一个数组公式或 CSE。这意味着你将需要使用CONTROL+ SHIFT+ENTER完成公式时，不只是ENTER。当您在公式栏中的公式周围看到 {} 时，您就会知道您做对了。这些不能手动添加。仅根据需要复制列表。当没有更多唯一条目时，它将生成空白条目，但它仍在执行大量计算。

从那里您可以使用以下公式生成标题行，假设您在 Sheet1 上完成了上述所有生成。在您想要第一个标题的位置使用以下公式并正确复制：

=INDEX(Sheet1!AA:AA,COLUMN(B1))

然后您可以像手动方法的最后一步中使用的公式一样生成是/否表。