提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

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

假设你在工作表范围内有一个值列表,现在你想知道有多少个唯一值。 如果你逐一识别和计数,它会浪费很多次。 所以在这里我会向您介绍一些快速处理这个问题的技巧。

用公式计算唯一值的数量

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

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


一次选择/突出显示重复或唯一的值:

您订购的 Kutools for Excel选择重复和唯一单元格,您可以根据需要快速选择或突出显示范围内的重复值或唯一值。

doc select unique-1-1

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


用公式计算唯一值的数量

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

DOC计数器唯一values1

你可以使用下面的公式,请这样做:

1。 输入这个公式 =SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10)) 进入空白单元格。

DOC计数器唯一values1

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

DOC计数器唯一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计数器唯一values1

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

DOC计数器唯一values1

备注:

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

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

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

1。 点击 数据 功能区中的选项卡,然后单击 高级 命令,看截图:

DOC计数器唯一values1

2。 和在 高级筛选 对话框:

DOC计数器唯一values1
  • (1。)选择 复制到另一个位置 选项下 操作;
  • (2。)在 列表范围 部分,点击 DOC-button1 按钮选择要使用的范围数据;
  • (3。)在 复制到 部分,点击 DOC-button1 按钮来指定一个单元,你想把过滤的唯一值放入;
  • (4。)最后,检查 唯一的记录 选项。

3。 完成设置后,单击 OK,独特的价值已被列在一个新的列,请参阅截图:

DOC计数器唯一values1

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

DOC计数器唯一values9 -2 DOC计数器唯一values10

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

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

Kutools for Excel : 与超过300方便的Excel加载项,在60天免费试用没有限制.

安装后 Kutools for Excel请按以下步骤操作:

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

2。 然后点击 Kutools > 选择 > 选择重复和唯一单元格,看截图:

DOC-数器唯一values11-11

3。 在 选择重复和唯一单元格 对话框中选择 唯一的值只 or 所有唯一(包括1st副本) 如您所愿,同时,您还可以通过检查来突出显示唯一值 填充背景颜色 or 填充字体颜色 在下面 处理结果 部分,看截图:

DOC-数器唯一values12-12

4。 (1。)如果您选择 唯一的值只,只有唯一的值会被统计和选择一次。 看截图:

DOC-数器唯一values13-13

(2。)如果您选择 所有唯一(包括1st副本),包括第一个副本的所有唯一值将被计数和选择,如下面的截图所示:

DOC-数器唯一values14-14

点击了解更多关于此选择重复和唯一单元格功能。

立即下载并免费试用Kutools for Excel!


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

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


相关文章:

如何在Excel中筛选或选择唯一记录?


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

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

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
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 · 3 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 · 3 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 · 3 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 · 4 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 · 4 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 · 4 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 · 4 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 · 4 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 · 4 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 · 5 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 · 5 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 · 5 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 · 5 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 · 5 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 · 5 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 · 5 years ago
    [b]Works fine, Thanks a lot![/b]
    :lol:
  • To post as a guest, your comment is unpublished.
    brian moffatt · 5 years ago
    Awesome, I tried many formulas and nothing worked. This worked on the first attempt, just changed teh cell range.
    Thanks! :roll: