跳到主要内容

如何根据Excel中的条件提取唯一值?

假设您有一个左侧的数据范围,您只想根据A列的特定标准仅列出B列的唯一名称即可获得结果,如下面的屏幕截图所示。 您如何快速,轻松地在Excel中处理此任务?

根据条件使用数组公式提取唯一值

使用数组公式基于多个条件提取唯一值

从具有有用功能的单元格列表中提取唯一值

 

根据条件使用数组公式提取唯一值

要解决此工作,您可以应用一个复杂的数组公式,请执行以下操作:

1。 在要列出提取结果的空白单元格中输入以下公式,在此示例中,我将其放在单元格E2中,然后按 Shift + Ctrl + 输入 键以获取第一个唯一值。

=IFERROR(INDEX($B$2:$B$15, MATCH(0, IF($D$2=$A$2:$A$15, COUNTIF($E$1:$E1, $B$2:$B$15), ""), 0)),"")

2。 然后,将填充手柄向下拖动到单元格,直到显示空白单元格,现在已列出了基于特定条件的所有唯一值,请参见屏幕截图:

请注意: 在上面的公式中: B2:B15 是列范围包含您要从中提取的唯一值, A2:A15 该列包含您基于的条件, D2 指示您要基于的列出唯一值的条件,并且 E1 是您输入的公式上方的单元格。

使用数组公式基于多个条件提取唯一值

如果要基于两个条件提取唯一值,这是另一个可以帮助您的数组公式,请执行以下操作:

1。 在要列出唯一值的空白单元格中输入以下公式,在此示例中,我将其放在单元格G2中,然后按 Shift + Ctrl + 输入 键以获取第一个唯一值。

=IFERROR(INDEX($C$2:$C$15,MATCH(0,COUNTIF(G1:$G$1,$C$2:$C$15)+IF($A$2:$A$15<>$E$2,1,0)+IF($B$2:$B$15<>$F$2,1,0),0)),"")

2。 然后,将填充手柄向下拖动到单元格,直到显示空白单元格,现在已列出了基于特定两个条件的所有唯一值,请参见屏幕截图:

请注意: 在上面的公式中: C2:C15 是列范围包含您要从中提取的唯一值, A2:A15 E2 是您要根据其提取唯一值的条件的第一个范围, B2:B15F2 是您要根据其提取唯一值的条件的第二个范围,并且 G1 是您输入的公式上方的单元格。

从具有有用功能的单元格列表中提取唯一值

有时,您只想从单元格列表中提取唯一值,在这里,我将推荐一个有用的工具-Kutools for Excel,其 提取具有唯一值的单元格(包括第一个重复项) 实用程序,您可以快速提取唯一值。

请注意:申请这个 提取具有唯一值的单元格(包括第一个重复项),首先,您应该下载 Kutools for Excel,然后快速轻松地应用该功能。

安装后 Kutools for Excel,请这样做:

1。 单击要在其中输出结果的单元格。 (备注:不要单击第一行中的单元格。)

2。 然后点击 库工具 > 公式助手 > 公式助手,请参见屏幕截图:

3。 在 公式助手 对话框,请执行以下操作:

  • 选择 文本 选项从 公式 Type 下拉列表;
  • 然后选择 提取具有唯一值的单元格(包括第一个重复项) 来自 选择一个草 列表框;
  • 在右边 参数输入 部分中,选择要提取唯一值的单元格列表。

4。 然后点击 Ok 按钮,第一个结果显示在单元格中,然后选择该单元格并将填充手柄拖到要列出所有唯一值的单元格上,直到显示空白单元格为止,请参见屏幕截图:

立即免费下载Kutools for Excel!


更多相关文章:

  • 计算列表中唯一值和不同值的数量
  • 假设您的值列表很长,其中包含一些重复项,现在,您要计算唯一值(仅一次出现在列表中的值)或不同值(列表中所有不同值的数量)的数量值+第一个重复值),如左图所示。 本文,我将讨论如何在Excel中处理此工作。
  • 根据Excel中的条件求和唯一值
  • 例如,我现在有一系列数据,其中包含“名称”和“订单”列,以根据“名称”列仅对“订单”列中的唯一值求和,如下图所示。 如何在Excel中快速轻松地解决此任务?
  • 连接Excel中的唯一值
  • 如果我有一长串的值填充了一些重复数据,那么现在,我只想查找唯一值,然后将它们串联到单个单元格中。 如何在Excel中快速轻松地解决此问题?

最佳办公生产力工具

🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述


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

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (40)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thank you so much. This was very very helpful. You Rock!!
This comment was minimized by the moderator on the site
hi everyone..
i have problem..
i got blank result even i press ctrl shift enter together..
This comment was minimized by the moderator on the site
Hi all, Can some help me to get all unique values on one single cell
This comment was minimized by the moderator on the site
Hi, this worked well! Although it takes Excel sooooo long to calculate. Just dragging down 15 cells in a column takes about 15min to calculate... if not longer. Is this normal? If this becomes dynamic it will take a hell of alot of computing time.
This comment was minimized by the moderator on the site
Hello. This is really helpful, however, what If I want a formula that lists the unique values based on multiple criteria. eg. I have a data set which has the following data in a table (after each hyphen is a new column but same row):

Company A - £200 - £100
Company A - £300 - £200
Company B - £300 - £200
Company C - £600 - £200
Company B - £100 - £300
Company D - £0 - £600
Company A - £700 - £100

I want a new data table in a new tab which groups the duplicate values without using an array formula. currently I'm grouping using a pivot table and pasting to my new data table. It's a long process but array formulas make my spreadsheet really slow.

Company A - £1200 - £400
Company B - £400 - £500
Company C - £600 - £200
Company D - £0 - £600

Thanks,
K
This comment was minimized by the moderator on the site
Hello, K,
For solving your problem, I can recommend our useful tool- Kutools for Excel, with its Advanced Combine Rows feature, you can deal with this job quickly. Firstly, you should copy and paste your data into a new worksheet, and then apply htis feature as below screenhsot shown.
You can know more about this feature from: https://www.extendoffice.com/product/kutools-for-excel/excel-combine-duplicate-rows.html
Please download Kutools for Excel and install it, then apply this feature. Full feature free trial 30-day, please try.
This comment was minimized by the moderator on the site
Hi! the formula works really well. I would like to add another criterion, i mean, get the unique answers but using two criteria
This comment was minimized by the moderator on the site
Hi, Giancarlo,
to extract unique values based on multiple criteria, any of the below formula can help you: (after pasting the formula, please press Ctrl + Shift + Enter keys together.)
=IFERROR(INDEX($C$2:$C$11, MATCH(0, COUNTIF(G1:$G$1, $C$2:$C$11)+IF($A$2:$A$11<>$E$2, 1, 0)+IF($B$2:$B$11<>$F$2, 1, 0), 0)), "")
=INDEX($C$2:$C$11, MATCH(0, IF(($A$2:$A$11=$E$2)*($B$2:$B$11=$F$2), COUNTIF($G$1:$G1, $C$2:$C$11), ""), 0))
Please try, hope it can help you!
This comment was minimized by the moderator on the site
Hi. I am using the two conditions formula =IFERROR(INDEX($C$2:$C$11, MATCH(0, COUNTIF(G1:$G$1, $C$2:$C$11)+IF($A$2:$A$11<>$E$2, 1, 0)+IF($B$2:$B$11<>$F$2, 1, 0), 0)), "") to extract a unique list and it works great, but I am struggle to add the SMALL function to get the list sorted as well in ascending order. Are you able to help?
This comment was minimized by the moderator on the site
Is there a way to make this work while ALLOWING for duplicate values? For instance, I want all instances of Lucy to be listed in the results.
This comment was minimized by the moderator on the site
Hello, Konstantin,
To extract all corresponding values including the duplicates based on a specific cell criteria, the following array formula can help you, see screenshot:
=IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

After inserting the formula, please press Shift + Ctrl + Enter keys together to get the correct result, and then drag the fill handle down to get all values.
Hope this can help you, thank you!
This comment was minimized by the moderator on the site
This has worked great for me with a specific lookup value. However, if I wanted to use a wildcard to look up partial values, how would I do that? For example, if I wanted to lookup all the names associated with KT?

I am using this function to look up cells that contain multiple text. For example if each product also had a sub-product within the same cell but I was only looking for names associated with the sub-product "elf".

KTE - elf
KTE- ball
KTE - piano
KTO - elf
KTO- ball
KTO - piano
This comment was minimized by the moderator on the site
For me the formula does not work. I press ctrl shift enter and i still get an error N/A. I would like to add that i prpared exaclty the same data as in tutorial. What is the reason it does not work?
This comment was minimized by the moderator on the site
How would I get this formula to return each of the duplicates instead of one of each of the names? For instance, in the example above, how would I get the results column (B:B) to return Lucy, Ruby, Anny, Jose, Lucy, Anny, Tom? I'm using this as a budget tool pulling to specific account summaries from a general ledger. However, several of the amounts and transaction descriptions are duplicates in the general ledger. Once the first of the duplicated values is pulled, no more of them get pulled.
This comment was minimized by the moderator on the site
Hi, Joe,
To extract all corresponding values based on a specific cell criteria, the following array formula can help you, see screenshot:
=IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

After inserting the formula, please press Shift + Ctrl + Enter keys together to get the correct result, and then drag the fill handle down to get all values.
Hope this can help you, thank you!
This comment was minimized by the moderator on the site
Last Question: If I want the results column to return all values not associated with KTE or KTO (so, D:D would be Tom, Nocol, Lily, Angelina, Genna), how would I do that?
This comment was minimized by the moderator on the site
Ok, so it works in the master workbook. There is one exception that I haven't been able to determine the cause of: If the array (in my case, the general ledger that I had beginning in row 3) does not begin in Row 1, the returned values are incorrect. What causes this problem, and which term in the formula fixes it? Thanks again for your help with this!
This comment was minimized by the moderator on the site
So far so good. I'm able to duplicate the results in the test sheet, make changes to the array, and then correct the formula to account for the changes I've made. I plan to move this into the master sheet today and see how it works. Thanks for the help!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations