计算EXCEL 2010中一列中的唯一值或带有100万行的R中的唯一值(count the unique values in one column in EXCEL 2010 or R with 1 million rows)

 搜索论坛后，我找不到这个问题的好方法。 如果我错过了，请告诉我。 
 
 我需要在EXCEL 2010中的一列中计算唯一值。 
 
 该工作表有100万行和10列。 所有单元格值都是字符串或数字。 
 
 我在Excel的一列中使用Count唯一值的解决方案 
 
 =SUMPRODUCT((A2:A1000000<>"")/COUNTIF(A2:A100000,A2:A1000000&""))
 
 但是，它的运行时间很长，以至于EXCEL几乎被冻结了。 并且，它在Win 7中生成25个进程。 
 
 有更有效的方法吗？ 
 
 此外，在列中，所有值都具有格式 
 
  AX_Y

  here, A is a character, X is an integer, Y is an integer from 1 to 10. 

  For example, A5389579_10
 
 我需要在（包括）低调之后切断部分。 例如， 
 
  A5389579
 
 这就是我需要计算为一列中所有单元格中的唯一值。 
 
  For example, A5389579_10
               A1543848_6
               A5389579_8
 
 这里，在删除下划线后的部分后，唯一值为2。 
 
 如何在EXCEL VBA和R中完成（如果没有EXCEL的有效解决方案）？ 

After searching the forum, I did not find a good solution for this question. If I missed it, please tell me. 
 
I need to count the unique values in one column in EXCEL 2010. 
 
The worksheet has 1 million rows and 10 columns. All cell values are string or numbers. 
 
I used the solution at Count unique values in a column in Excel
 
 =SUMPRODUCT((A2:A1000000<>"")/COUNTIF(A2:A100000,A2:A1000000&""))
 
But, it runs so long time that the EXCEL is almost frozen. And, it generates 25 processes in Win 7.
 
Are there more efficient ways to do it? 
 
Also, in the column, all values have for format of 
 
  AX_Y

  here, A is a character, X is an integer, Y is an integer from 1 to 10. 

  For example, A5389579_10
 
I need to cut off the part after (including) undersocre. for the example, 
 
  A5389579
 
This is what I need to count as unique values in all cells in one column.
 
  For example, A5389579_10
               A1543848_6
               A5389579_8
 
Here, the unique value has 2 after removing the part after underscore. 
 
How to do it in EXCEL VBA and R (if no efficient solution for EXCEL)?

原文：https://stackoverflow.com/questions/23035511