提取与唯一对列ID对应的两个最小值 [英] Extracting the two smallest values corresponding to the unique-to-the-column ID

问题描述

我有两行，即： ID 和时间。我想提取与唯一列ID相对应的两个最小的时间值。

  ID时间
 27604 13:00 ：12 
 27604 13:00:32 
 27604 13:00:34 
 27604 13:00:38 
 27604 13:00:41 
 27604 13:00 ：47 
 27604 13:00:50 
 27605 13:00:20 
 27605 13:00:23 
 27605 13:00:39 
 27605 13:00 ：42 
 27605 13:00:45 
 27605 13:00:48 
 27605 13:00:54 
 27605 13:00:57 
 27605 13:01 ：00 
 27606 13:00:49 
 27606 13:00:52 
 27606 13:00:55 
 27606 13:01:01 
 27606 13:01 ：04 
 27606 13:01:07 
  

27604我只想提取13:00:12和13:00:32。

对于ID 27605，我想提取13:00:20和13:00：

对于ID 27606，我只想提取13:00:27和13:00:30。

我想提取所有这些值如下：

  27604 13：0 0:12 
 27604 13:00:32 
 27605 13:00:20 
 27605 13:00:23 
 27606 13:00:49 
 27606 13： 00:52 
  

解决方案

你可以实现

E4中的公式是

  = AGGREGATE（15，6，（B：B）/（A：A = D4），COUNTIF（D $ 4：D4 ，D4））
  

根据需要填写。由于我们使用SMALL子功能，所以我们可以通过增加 k 参数来轻松地获取第二，第三等比例，因为我已经使用了一个 COUNTIF函数实现渐进范围和浮动条件。

6 是用于忽略错误值的AGGREGATE参数。通过将时间除以列A是否是正确的ID，我们将生成＃DIV / 0！错误，我们不希望考虑将其忽略。

¹ _{AGGREGATE¹功能的已经与Excel 2010一起推出。以前的版本不可用。}

I have two rows, that is: ID and Time. I want to extract the two smallest Time value corresponding to the unique column ID.

ID  Time
27604   13:00:12
27604   13:00:32
27604   13:00:34
27604   13:00:38
27604   13:00:41
27604   13:00:47
27604   13:00:50
27605   13:00:20
27605   13:00:23
27605   13:00:39
27605   13:00:42
27605   13:00:45
27605   13:00:48
27605   13:00:54
27605   13:00:57
27605   13:01:00
27606   13:00:49
27606   13:00:52
27606   13:00:55
27606   13:01:01
27606   13:01:04
27606   13:01:07

For ID 27604 I want to extract 13:00:12 and 13:00:32 only.

For ID 27605 I want to extract 13:00:20 and 13:00:23 only.

For ID 27606 I want to extract 13:00:27 and 13:00:30 only.

I want to extract all this values as below:

27604   13:00:12
27604   13:00:32
27605   13:00:20
27605   13:00:23
27606   13:00:49
27606   13:00:52

解决方案

You can achieve the SMALL values (with criteria) easily with the AGGREGATE¹ function's SMALL sub-function (e.g. 15).

    

The formula in E4 is,

=AGGREGATE(15, 6, (B:B)/(A:A=D4), COUNTIF(D$4:D4, D4))

Fill down as necessary. Since we are using the SMALL sub-function, we can easily retrieve the second, third, etc. ratios by increasing the k parameter as I've done with a COUNTIF function implementing a progressive range and floating criteria.

The 6 is the AGGREGATE parameter for ignoring error values. By dividing the time by whether or not column A is the correct ID we are producing #DIV/0! errors for anything we do not want considered leaving them ignored.

¹_{The AGGREGATE¹ function's was introduced with Excel 2010. It is not available in previous versions.}

这篇关于提取与唯一对列ID对应的两个最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！