Note: The other languages of the website are Google-translated. Back to English
登陆  \/ 
x
or
x
点此注册  \/ 
x

or

如何计算Excel列中的唯一值或数字?

假设您在工作表的范围中有一个值列表,现在您想知道其中有多少个唯一值。 如果您一一识别并计算它们,将会浪费很多时间。 因此,在这里我将向您介绍一些解决该问题的快速技巧。

用公式计算唯一值的数量

使用高级过滤器计算唯一值的数量

使用Kutools for Excel计算唯一值的数量


用公式计算唯一值的数量

有一些公式可以帮助您快速计算范围内唯一值的数量。 例如,如果您的数据是数字,则您有一列范围在(A1:A10)的数字,请参见屏幕截图:

doc-count-unique-values1

您可以使用以下公式,具体操作如下:

1。 输入此公式 =SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10)) 变成一个空白单元格。

doc-count-unique-values1

2。 然后按 输入 键,并显示唯一值的编号。 看截图:

doc-count-unique-values1

窍门:1.这是另一个公式 =SUM(IF(FREQUENCY(A1:A10, A1:A10)>0,1)) 也可以帮助您。 请随意申请任何人。 以上两个公式都将计算唯一值,包括第一个重复值。

2。 如果您的工作表中有一定范围的文本,则上述公式将不起作用,您应该使用以下数组公式: =SUM(IF(FREQUENCY(IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""), IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""))>0,1))

doc-count-unique-values1

输入公式后,然后记得按 Shift + Ctrl + Enter 键,并且已显示唯一值的数量。

doc-count-unique-values1

备注:

  • 1.范围 A1:A10 在上述公式中,变量是可变的,您可以根据需要更改它们。
  • 2.此公式还计算包括第一个重复项在内的唯一值。
  • 3.此公式也适用于一系列文本和数字。

使用高级过滤器计算唯一值的数量

您还可以使用“高级筛选器”从一列数据中提取唯一值并将其粘贴到新位置。 然后使用ROWS函数来计算新范围内唯一值的数量。 具体步骤如下:

1。 点击 数据 功能区中的“选项卡”,然后单击“ 高级 命令,请参见屏幕截图:

doc-count-unique-values1

2。 和在 进阶筛选 对话框:

doc-count-unique-values1
  • (1.)选择 复制到另一个位置 选项下 操作;
  • (2.)在 清单范围 部分,点击 doc-button1 按钮选择您要使用的范围数据;
  • (3.)在 复制到 部分,点击 doc-button1 按钮,指定要放入过滤后的唯一值的单元格;
  • (4.)最后检查 仅唯一记录 选项。

3。 完成设置后,单击 OK,唯一值已在新列中列出,请参见屏幕截图:

doc-count-unique-values1

4。 然后在空白单元格中输入简短公式 = ROWS(E1:E8),然后按 输入 键盘上的键。 并显示唯一值的数量。

doc-count-unique-values9 -2 doc-count-unique-values10

使用Kutools for Excel计算唯一值的数量

 在这里,我将向您推荐一个有用的工具- Kutools for Excel,其 选择重复和唯一单元格 功能,您可以快速选择重复值或唯一值并计算它们的数量。

Kutools for Excel : 带有300多个便捷的Excel加载项,可以在30天内免费试用

安装后 Kutools for Excel,请执行以下步骤:

1. 选择要计算唯一值的数据范围。

2。 然后点击 Kutools > 选择 > 选择重复和唯一单元格,请参见屏幕截图:

doc-count-unique-values11-11

3。 在 选择重复和唯一单元格 对话框,选择 仅唯一值 or 所有唯一(包括第一个重复项) 根据需要,您还可以通过选中 填充背景色 or 填充字体颜色 在下面 处理结果 部分,请参见屏幕截图:

doc-count-unique-values12-12

4。 (1.)如果选择 仅唯一值,则将仅计算和选择唯一值。 看截图:

doc-count-unique-values13-13

(2.)如果选择 所有唯一(包括第一个重复项),包括以下重复项在内的所有唯一值(包括第一个重复项)都会被计数和选择,如下所示:

doc-count-unique-values14-14

单击以了解有关此“选择重复和唯一单元格”功能的更多信息。

立即下载和免费试用Excel的Kutools!


演示:使用Kutools for Excel计算唯一值的数量

Kutools for Excel:具有300多个方便的Excel加载项,可以在30天内免费试用,没有任何限制。 立即下载并免费试用!


相关文章:

如何在Excel中从选定范围过滤或选择唯一记录?


最佳办公效率工具

Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%

  • 重用: 快速插入 复杂的公式,图表 以及您以前使用过的任何东西; 加密单元 带密码 创建邮件列表 并发送电子邮件...
  • 超级公式栏 (轻松编辑多行文本和公式); 阅读版式 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不会丢失数据; 拆分单元格内容; 合并重复的行/列...防止细胞重复; 比较范围...
  • 选择重复或唯一 行; 选择空白行 (所有单元格都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择...
  • 确切的副本 多个单元格,无需更改公式参考; 自动创建参考 到多张纸; 插入项目符号,复选框等...
  • 提取文字,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级滤镜 (将过滤方案保存并应用于其他工作表); 高级排序 按月/周/日,频率及更多; 特殊过滤器 用粗体,斜体...
  • 结合工作簿和工作表; 根据关键列合并表; 将数据分割成多个工作表; 批量转换xls,xlsx和PDF...
  • 超过300种强大功能。 支持Office / Excel 2007-2019和365。支持所有语言。 在您的企业或组织中轻松部署。 完整功能30天免费试用。 60天退款保证。
kte选项卡201905

Office选项卡为Office带来了选项卡式界面,使您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
officetab底部
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    priya · 4 years ago
    tying to know the values of unique customer for different products, for ex: there are 100 customers, wanted to know out of those how many are unique customers who buy only those products.

    Likewise: total sales of products = 100
    apple = 40
    orange = 60

    how to find out customers who have brought only oranges and apples?
  • To post as a guest, your comment is unpublished.
    Alireza · 5 years ago
    in this formula,
    =SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))
    can i add another "if"? for example i want count the range of unique text in B2:B150 if C2:C150=1 ???
    can i?
  • To post as a guest, your comment is unpublished.
    Meags · 5 years ago
    How would you seperate unique values in a cell? ie. #2,#2,#3 I only want the #2 to appear once?
  • To post as a guest, your comment is unpublished.
    Saurabh · 5 years ago
    no these formulas are not sufficient, in actual my problem is little bit big, there are lots of column in my sheet, with the name of store id and store name and months in three column respectively and there is repetition of store name in every month a no of time, i have to calculate the unique no of the store name with respect to month. please suggest me the exact formula accordingly.
  • To post as a guest, your comment is unpublished.
    Wendy · 5 years ago
    Order Rep Status Product Order Value
    A1 John Close Apple $1
    A1 John Close Pear $1.50
    A2 John Open Orange $0.50
    A3 John Close Grape $3.00
    A2 John Open Apple $1
    A4 John Close Orange $0.50
    A5 Mary Close Apple $1
    A6 Peter Close Grape $3.00

    Appreciate your advice on this...
    I am trying to count the unique number of order # (first column), where the rep is John and the order status is closed.
    the result to achieve is 3 unique orders (A1, A3 and A4). But if i do a countifs function, it literally count the number of 'Close' where rep=John, and that comes back with 4.


    Is this possible to count unique values against multiple conditions of other columns?
  • To post as a guest, your comment is unpublished.
    Snoo · 5 years ago
    Anyone know how to make the =SUM(IF(FREQUENCY(A1:A10, A1:A10)>0,1)) formula work to count the number of unique fields for a series of intervals?
    For example, I want to be able to find out the number of unique entries for each day. My data is structures as ID numbers in Column A, Dates in column B. The data is sorted by the date column.

    Any help would be very very much appreciated!
  • To post as a guest, your comment is unpublished.
    John Murray · 5 years ago
    Your formula works a treat and allows me to see the number of unique clients that I have in total (i.e. from all my sales people combined). I am struggling however, to update the formula so that it would allow me to see the number of unique clients an individual sales person would have. Any ideas?
  • To post as a guest, your comment is unpublished.
    Afhar Siyam · 5 years ago
    =SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))when you are selecting the range make sure that the cells should not be blank.
  • To post as a guest, your comment is unpublished.
    Sam · 6 years ago
    Hi,

    Thank you for your help regarding the duplicates but I'd like to modify your formula =SUM(IF(FREQUENCY(A2:A800, A2:A800)>0,1)) and add this condition (assuming that all the cells are numbers) to count only between 402 to 460 and it goes on to 502 to 560; 602 to 660 (1 formula per range) would it be possible?

    Thank you very much!
  • To post as a guest, your comment is unpublished.
    Christopher · 6 years ago
    This is awesome. Follow-up question: how would you subtotal this if filters were applied?

    If I have:
    Apple
    Orange
    Grape
    Apple

    And filter out the range so only Apple is displayed, how would I get the total to display: "1"?

    Thanks!
    --
    Christopher
  • To post as a guest, your comment is unpublished.
    Jeswant Alexander · 6 years ago
    Your solution with SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10)) is awesome and save a lot of time. Can you please explain the logic as how it works. I'm very interested.
  • To post as a guest, your comment is unpublished.
    Jeswant Alexander · 6 years ago
    Your solution with =SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10)) is awesome and save a lot of time. To some extent I understand sumproduct and countif, but unable to catchup the logic has how this solution works. Can you please explain.
  • To post as a guest, your comment is unpublished.
    Jeswant Alexander · 6 years ago
    The solution with =SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10)) is awesome and saves a lot of time. I understand sumproduct and countif to an extent but unable to catchup the logic as how your solution formula works. Can you please explain me the logic.
  • To post as a guest, your comment is unpublished.
    Amit · 6 years ago
    To actually find the frequency distribution (no of repetition of unique values with in a range), You can use countif funcion. To the cell adjacent to the unique records found out from the original data range, just use funtion:
    = countif(Original Range, unique value)
    This will list out the frequency distributions!!!

    Cheers!
  • To post as a guest, your comment is unpublished.
    naveen · 6 years ago
    super boss... my friends wasted 2 hrs in searchig....u saved my time:-)
  • To post as a guest, your comment is unpublished.
    Fábio Makoto Kawanam · 6 years ago
    [b]Works fine, Thanks a lot![/b]
    :lol:
  • To post as a guest, your comment is unpublished.
    brian moffatt · 6 years ago
    Awesome, I tried many formulas and nothing worked. This worked on the first attempt, just changed teh cell range.
    Thanks! :roll: