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

or

如何统计Excel中列中的重复值?

如果工作表中的数据列表具有唯一值和重复值,并且您不仅要计算重复值的频率,还希望知道重复值发生的顺序。 在Excel中,COUNTIF函数可以帮助您计算重复值。

在Excel中计算重复的频率

在Excel中计算重复项的发生顺序

使用Kutools for Excel计算并选择列中的所有重复值

使用Kutools for Excel对列中每个副本的出现次数进行计数

使用Kutools for Excel轻松统计和选择列中的所有重复值

由Kutools为Excel提供。 全功能免费试用30天!
广告选择次数重复

Office选项卡在Office中启用选项卡式编辑和浏览,使您的工作更轻松......
Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%
  • 重用任何东西: 将最常用或最复杂的公式,图表和其他任何内容添加到您的收藏夹中,并在将来快速重复使用它们。
  • 超过20文本功能: 从文本字符串中提取数字; 提取或删除部分文本; 将数字和货币转换为英语单词...
  • 合并工具:多个工作簿和表格合二为一; 合并多个单元格/行/列而不丢失数据; 合并重复行和总和...
  • 拆分工具:根据价值将数据拆分为多个表格; 一个工作簿到多个Excel,PDF或CSV文件; 一列到多列......
  • 粘贴跳过 隐藏/过滤行; 数和总和 按背景颜色; 创建邮件列表和 通过Cell的价值发送电子邮件...
  • 超级过滤器: 创建高级过滤方案并应用于任何工作表; 排序 按周,日,频率等; 筛选 通过大胆,公式,评论......
  • 超过300强大的功能; 与Office 2007-2019和365一起使用; 支持所有语言; 在您的企业或组织中轻松部署。

箭头蓝色右泡 在Excel中计算重复的频率

在Excel中,可以使用COUNTIF函数来计算重复项。

选择与列表中第一个数据相邻的空白单元格,然后键入此公式 = COUNTIF($ A $ 2:$ A $ 9,A2) (范围 $ A $ 2:$ A $ 9 表示数据的列表, A2 站在您想要计数频率的小区,您可以根据需要更改它们),然后按 输入,然后拖动填充手柄来填充所需的列。 看截图:

小技巧:如果要计算整个列中的重复项,请使用此公式 = COUNTIF(A:A,A2)A栏 表示数据的列, A2 站在你想要计数频率的细胞,你可以根据需要改变它们)。


箭头蓝色右泡 在Excel中计算重复项的发生顺序

但是,如果要计算重复项的出现顺序,则可以使用以下公式。

选择与列表中第一个数据相邻的空白单元格,然后键入此公式 = COUNTIF($ A $ 2:$ A2,A2) (范围 $ A $ 2:$ A2 表示数据的列表, A2 站在您要计数的单元格,您可以根据需要更改它们),然后按 输入,然后拖动填充手柄来填充所需的列。 看截图:


箭头蓝色右泡 使用Kutools for Excel计算并选择列中的所有重复项

有时您可能需要计算并选择指定列中的所有重复项。 用Kutools for Excel可以轻松完成 选择重复和唯一单元格 效用。

1。 选择您将计数所有重复的列或列表,然后单击 Kutools > 选择 > 选择重复和唯一单元格.

2。 在打开选择重复和唯一单元格对话框中,选中 重复(除1st之外) 选项或 所有重复(包括1st之一) 根据需要选择,然后单击 Ok 按钮。

然后你会看到一个对话框出来,显示有多少重复选择,同时在指定的列中选择重复。

请注意: 如果你想统计所有重复包括第一个,你需要检查 所有重复(包括1st之一) 选择重复和唯一单元格对话框中的选项。

3。 点击 OK 按钮。

Kutools for Excel -包括多个用于Excel的300便捷工具。 全功能免费试用30天,无需信用卡! 现在加入


箭头蓝色右泡 使用Kutools for Excel对列中每个副本的出现次数进行计数

Kutools为Excel的 先进的组合行 实用程序可以帮助Excel用户批量统计列中每个项目(本例中的Fruited Column)的出现次数,然后轻松删除基于此列(水果列)的重复行,如下所示:

1。 选择包含您计算每个重复项的列的表格,然后单击 Kutools > 内容 > 先进的组合行.

2。 在“高级组合行”中,选择您要对每个副本进行计数的列,然后单击 首要的关键,接下来选择你要把计数结果放入的列,然后单击 计算 > 计数,然后单击 OK 按钮。 看截图:

现在已经统计了指定列中每个副本的出现次数。 看截图:

Kutools for Excel -包括多个用于Excel的300便捷工具。 全功能免费试用30天,无需信用卡! 现在加入


箭头蓝色右泡演示:通过Kutools for Excel统计Excel中列中的重复值

在这个视频中, Kutools Kutools Plus 选项卡添加 Kutools for Excel。 如果需要,请点击 30天免费试用,不受限制!

箭头蓝色右泡相关文章:

在Excel中计算合并的单元格

在Excel中计算空白单元格或非空白单元格的范围


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

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能30天免费试用。
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.
    Mel Scott · 3 months ago
    Thanks so much, saved me hours!
  • To post as a guest, your comment is unpublished.
    patel akshay · 5 months ago
    i need this
    There are 24 students.how many groups are there to take 10 student in 1 group
    example:
    1,2,3,4,5 take 1 group in 3 number

    ans={1,2,3} {2,3,4} {3,4,5} {4,5,1} {5,1,2,}

    how to do this excel will create this file
    • To post as a guest, your comment is unpublished.
      kellytte · 2 months ago
      Hi patel akshay,
      You can use the COMBIN function in Excel directly.
      =COMBIN(24,10)
  • To post as a guest, your comment is unpublished.
    Jothibasu · 9 months ago
    Thank You very much. It's very useful.
  • To post as a guest, your comment is unpublished.
    BillBrewster · 10 months ago
    I must be stupid as the KuTools solution is not working for me. I have copied the example with Fruits column (which I put 3 Apple entries in), made a column next to it which is blank called Count column. KuTools -> Content -> Advanced Combine Rows. Select Fruit column as primary key, Count column as Calculate -> Count. Hit OK. Nothing. No change to the sheet. Help please?
    • To post as a guest, your comment is unpublished.
      kellytte · 8 months ago
      Hi BillBrewster,
      Would you do me a favor and send me a screenshot about the Advanced Combine Rows dialog? Just like the screenshot as below. Thanks in advance!
  • To post as a guest, your comment is unpublished.
    Muhammad Husnain · 1 years ago
    Dear, I am working on the attached Screenshot and Excel File. I need to calculate the Values in Column "G" i.e. to Count unique text values based on multiple (Two) criteria, but criteria are in NUMBERS. Further, Its is big sheet, therefore I want to use the cell reference in range and in criteria. I am very confused. I want to count the column C i.e. Degree based on the Criteria E and F. That is,I need to set formula in G2, such that, Look E2 in column A and Look F2 in column B and count the unique text values in Column C. Hope it is clear.

    I am very thankful to you please


    Details:
    I have a data of more than 1000 companies with different year. As in column A, I have companies and B shows the year for which these companies have data. For example, First company have data for year 2011, 2012, 2013 and 2015. For company 2, I have data for year 2014 and 2011 and so on. Since majority of companies have data from 2011 to 2015, therefore I select this range in column F for each company. Now, I want to count the unique Degrees for cell G2, if there is 389841988 (Company ID for First company) in A and there is 2011 in Column B. Now I need to set formula in G2,in a way, If I will drag the formula G2 in cell G3, then It should give me the value by looking such as if there is 389841988 (Company ID for First company) in A and there is 2012 in Column B, the based on these two criteria, it should count the unique text in C2 and so on.
    Look on the second sheet named Example, I did the same for INDEX, MATCH function and as well for SUMIFS function; in C2 I have the array formula INDEX($F:$H,MATCH(1,(A2=$F:$F)*(B2=$G:$G),0),3) and in D2 I have SUMIFS(I:I,F:F,A2,G:G,B2). These help me to do what I want with two criteria (To match and also to sum) and also I can drag these for rest of cell. I am looking something like this, or anything that I can drag to count unique degrees. Thank you very much. I am waiting.

    Plz help. I am very thankful to for this kindness
  • To post as a guest, your comment is unpublished.
    Muhammad Husnain · 1 years ago
    Dear, I got the way to upload the screenshot. Kindly consider the attached one,
    Thank you very much
    • To post as a guest, your comment is unpublished.
      Muhammad Husnain · 1 years ago
      Dear, I am working on the attached Screenshot and Excel File. I need to calculate the Values in Column "G" i.e. to Count unique text values based on multiple (Two) criteria, but criteria are in NUMBERS. Further, Its is big sheet, therefore I want to use the cell reference in range and in criteria. I am very confused. I want to count the column C i.e. Degree based on the Criteria E and F. That is,I need to set formula in G2, such that, Look E2 in column A and Look F2 in column B and count the unique text values in Column C. Hope it is clear.

      I am very thankful to you please


      Details:
      I have a data of more than 1000 companies with different year. As in column A, I have companies and B shows the year for which these companies have data. For example, First company have data for year 2011, 2012, 2013 and 2015. For company 2, I have data for year 2014 and 2011 and so on. Since majority of companies have data from 2011 to 2015, therefore I select this range in column F for each company. Now, I want to count the unique Degrees for cell G2, if there is 389841988 (Company ID for First company) in A and there is 2011 in Column B. Now I need to set formula in G2,in a way, If I will drag the formula G2 in cell G3, then It should give me the value by looking such as if there is 389841988 (Company ID for First company) in A and there is 2012 in Column B, the based on these two criteria, it should count the unique text in C2 and so on.
      Look on the second sheet named Example, I did the same for INDEX, MATCH function and as well for SUMIFS function; in C2 I have the array formula INDEX($F:$H,MATCH(1,(A2=$F:$F)*(B2=$G:$G),0),3) and in D2 I have SUMIFS(I:I,F:F,A2,G:G,B2). These help me to do what I want with two criteria (To match and also to sum) and also I can drag these for rest of cell. I am looking something like this, or anything that I can drag to count unique degrees. Thank you very much. I am waiting.

      Plz help. I am very thankful to for this kindness
    • To post as a guest, your comment is unpublished.
      Muhammad Husnain · 1 years ago
      The screenshot is not showing in the post. I don't know way.
  • To post as a guest, your comment is unpublished.
    Muhammad Husnain · 1 years ago
    Dear, I am working on the attached Sheet. I need to calculate the Values in Column "G" i.e. to Count unique text values based on multiple (Two) criteria, but criteria are in NUMBERS not in TEXT. Further, Its is big sheet, therefore I want to use the cell reference in range and in criteria. I am very confused. I want to count the column C i.e. Degree based on the Criteria E and F. That is, Look E2 in column A and Look F2 in column B and count the unique text values in Column C. Hope it is clear.
    Plz help. Thank you
    • To post as a guest, your comment is unpublished.
      Tang Kelly · 1 years ago
      Hi,
      Could you upload a screenshot about your problem? A picture may help us understand you problem much clear. Thank you!
      • To post as a guest, your comment is unpublished.
        Husnain · 1 years ago
        thank you very much.
        I am trying to upload the Screenshot, but I don't know how I can upload it in the comment section.


        CompanyID* YEAR Degree CompanyID* YEAR Count Unique Degree
        389841988 2015 PhD 389841988 2011 1
        389841988 2015 Master 389841988 2012 2
        389841988 2011 Matric 389841988 2013 1
        389841988 2012 PhD 389841988 2014 0
        389841988 2012 PhD 389841988 2015 2
        389841988 2012 Matric 23819116896 2011 1
        389841988 2013 Matric 23819116896 2012 0
        23819116896 2014 Master 23819116896 2013 0
        23819116896 2014 Master 23819116896 2014 1
        23819116896 2011 Master 23819116896 2015 0
        168402710018 2011 Master 168402710018 2011 1
        168402710018 2014 PhD 168402710018 2012 0
        168402710018 2014 PhD 168402710018 2013 0
        168402710018 2014 1
        168402710018 2015 0
        • To post as a guest, your comment is unpublished.
          deepak · 1 years ago
          Row Number B column c column D column E column F column G column

          Forumula: =C1&D1&E1&F1&G1

          13 168402710018 2014 PhD 1.68403E+11 2012 0 2014PhD16840271001820120
          14 168402710018 2014 PhD 1.68403E+11 2012 0 2014PhD16840271001820120


          Result: Row number 13 and 14 are same.
  • To post as a guest, your comment is unpublished.
    rafiq · 1 years ago
    how can we count duplicate values in excel row
    • To post as a guest, your comment is unpublished.
      Tang Kelly · 1 years ago
      Maybe you can copy the row to a column by the Transpose feature firstly?
  • To post as a guest, your comment is unpublished.
    Vishvas · 2 years ago
    Can't we get count of duplicate values with the help of count if function if yes how ? please advise
    as this is a interview question asked to me.
    • To post as a guest, your comment is unpublished.
      deepak · 1 years ago
      Vishvas use countif formula



      Forumula:=COUNTIF($A$2:$A$10,A1)
      row no A column Counts
      2 a 2
      3 b 2
      4 b 2
      5 c 1
      6 d 3
      7 d 3
      8 d 3
      9 a 2
      10 e 1
  • To post as a guest, your comment is unpublished.
    HARVIND · 2 years ago
    Hi , in below table some are appearing more than once, i need to catch them with number of appearance along with the series. like, B25 = 2. please help

    B25
    B17
    B9
    B15
    B1
    -
    B6
    B25
    B4
    B8
    B4
    B3
    B21
    B7
    B18
    B20
    B5
    B22
    B16
    B14
  • To post as a guest, your comment is unpublished.
    Bernadette Wright · 2 years ago
    Super helpful -- thank you!!
  • To post as a guest, your comment is unpublished.
    Unni · 3 years ago
    Hi,

    Please help me to solve the below problem

    =COUNTIFS('Weld Map'!$O$6:$O$7105,"="&F2,'Weld Map'!$V$6:$V$7105,"=Rej*",'Weld Map'!$AL$6:$AL$7105,"=REJ*")

    I need to count the text "REJ" from columns "V" and "AL" under the criteria of a period between F1 and F2
  • To post as a guest, your comment is unpublished.
    Unni · 3 years ago
    Hi,

    Please help me to solve the below problem

    =COUNTIFS('Weld Map'!$O$6:$O$7105,"="&F2,'Weld Map'!$V$6:$V$7105,"=Rej*",'Weld Map'!$AL$6:$AL$7105,"=REJ*")

    I need to count the text "REJ" from columns "V" and "AL" under the criteria of a period between F1 and F2

    Thanks and regards
    • To post as a guest, your comment is unpublished.
      aNKIT · 3 years ago
      use this instead of yours

      =SUM(COUNTIFS(J1:J196,"agree",A1:A196,"yes"),COUNTIFS(J1:J196,"agree",A1:A196,"no"))
  • To post as a guest, your comment is unpublished.
    Rhytha · 3 years ago
    I appreciate for the Solution provided. It is very helpful.
  • To post as a guest, your comment is unpublished.
    Najam Ul Hassan · 3 years ago
    very nice formula for counting of duplicate. It is very helpful
    Thanks extend office team
  • To post as a guest, your comment is unpublished.
    Stefan · 3 years ago
    Thank you SO much for this post its exactly what I needed!

    Please could you tell me why your formula "=COUNTIF($A$2:$A2,A2)" didn't work as expected in my worksheet until I amended it to "=COUNTIF($A2:$A$2;A2)" ?
    i.e. switching the absolute references around

    Thanks in advance
  • To post as a guest, your comment is unpublished.
    Khan · 3 years ago
    Please help to resolve this issue

    site ID Supplier Line
    12 abc good
    12 VV good
    12 TT good

    site ID Supplier Line
    12 abc good

    Required Supplier Name - formula required to show "Multiple Suppliers" as against same site iD and line there are 3 different suppliers.

    Please help to resolve this issue.
  • To post as a guest, your comment is unpublished.
    mohammad · 4 years ago
    many many thanks :-)
  • To post as a guest, your comment is unpublished.
    Ash · 4 years ago
    Let say I have different number of PO's in column A but some numbers are the same how can I count the total number of PO without including the duplicate number?
    • To post as a guest, your comment is unpublished.
      Rupesh Brahme · 2 years ago
      use formula =SUMPRODUCT(1/COUNTIF(A1:A1483, A1:A1483&""))
      =1/sumproduct(1/countif(range, criteria))
      :-)
  • To post as a guest, your comment is unpublished.
    Fujilives · 4 years ago
    =COUNTIF($A$1:$A1,A1)

    This method for finding duplicates is amazing, because it allows you to do a simple filter on the column (just deselect 0 and 1) to show all 'duplicate entries' instead of 'entries that have duplicates'. What I mean by this, is I can then select ALL visible after the filter, and delete the entire rows, and be left with only a single entry of a row containing that item.

    For MANY projects, this is a fantastic way to filter things down quickly.
  • To post as a guest, your comment is unpublished.
    Brice · 4 years ago
    If you want to get a sum of duplicate values in a column(without counting the first one), try:
    =IF(COUNTIF($A$1:$A1,A1)-1>=1,1,0)

    For example, let's say that you have a same value 5 times. It will count 1 for each of the 4 duplicate values. Then, you just have to get a sum.
  • To post as a guest, your comment is unpublished.
    Amol · 4 years ago
    suppose there is a column which contains values as GR1, GR2, GR3 and so on..... but some also getting repeated again. how can i get the final count of the item. Like if it reaches to GR29, the the value should show as 29 in the formula cell
  • To post as a guest, your comment is unpublished.
    Harrison · 4 years ago
    I am trying to label an individual data point as "1"...and if it has a duplicate, it will label the duplicates as "0"...but it would still label at least one of the data points as "1". Example, I could have one PO number on a truck, or multiple.

    Thanks
  • To post as a guest, your comment is unpublished.
    NAVEEN · 4 years ago
    i had query regarding for eg: 1st sheet of work book 1st is column is with data received fruits, 2nd column is fruits names, 3rd column is for normal defects, 4th column is for Major Defects, 5th column is for Critical defects

    2nd sheet for Normal Defects,
    3rd sheet for Major Defects,
    4th sheet for Critical Defects,
    My query is when we are updating these above sheet it total count should be reflected in 1st by individual fruits and for individual defects.

    Regards,
    Naveen kumar
  • To post as a guest, your comment is unpublished.
    NAVEEN · 4 years ago
    hI

    In sheet1 we have three columns, 1st columns "fruits names" 2nd column Date of received, Name of supplies only to supplies and in 3rd 4th 5th columns are Normal defect, Major defects and Critical defects,all these in 1st sheet.

    in 2nd sheet, 3rd sheet, 4th sheet, saparetly post all of Normal defects in 2nd sheet, Major defects in 3rd sheet, critical defects in 4th sheet. when we are updating these sheet it automatically should update 1st individual in normal, Major, Critical.

    Thanks
    Naveen
  • To post as a guest, your comment is unpublished.
    Ajeet singh · 4 years ago
    Impotent work if duplicate value.
  • To post as a guest, your comment is unpublished.
    Gaurav Pahuja · 5 years ago
    easy to use and helpful..:-p
  • To post as a guest, your comment is unpublished.
    munish · 5 years ago
    easy and helpful in large working
  • To post as a guest, your comment is unpublished.
    Zana · 5 years ago
    Awesome, it's easy and useful. Thaks
  • To post as a guest, your comment is unpublished.
    Priyanka · 5 years ago
    Is there any other function or way to calculate the same..?? Because countif() slows down the functioning of the sheet. Please suggest.
    • To post as a guest, your comment is unpublished.
      Ankit · 4 years ago
      contact me @@ if u want stop duplicacy!! * conditional formating duplicate
    • To post as a guest, your comment is unpublished.
      Amol Chopade · 5 years ago
      More use of functions and formulas make worksheet slower. There is no method to solve it even i suggest you to use special paste option after using formulas and functions. It will solve your problem. Just use it "Alt+s+e+v". :-)
  • To post as a guest, your comment is unpublished.
    Adnan Khan · 5 years ago
    easy and good one :D