给定A列中的值，在EXCEL中找到B列中的最小值/最大值 [英] Given value in column A, find min/max in column B in EXCEL

问题描述

我有一列公交车站 ID 号(A 列)和 B 列是不同的时间，其中每个值是一个人上/下公交车的时间.为了知道公交车停了多长时间，我想找到给定A的某个值的B列中最大值/最小值之间的差异.

I've got a column of bus stop ID numbers (Column A) and Column B are various timings of which each value is the time a person boards/alights the bus. In order to know how long the bus is stopped, I want to find the difference between the max/min in Column B given a certain value of A.

84009   9:17:38 AM
84009   9:17:40 AM
84009   9:17:41 AM
84009   9:17:43 AM
84009   9:17:50 AM
84009   9:17:55 AM
84029   9:22:47 AM
84029   9:22:47 AM
84029   9:22:49 AM
84029   9:22:50 AM
84019   9:23:49 AM
83049   9:28:19 AM
82069   9:32:32 AM
82069   9:32:49 AM
82049   9:33:58 AM
82049   9:33:58 AM
82049   9:33:59 AM
82049   9:34:02 AM
82049   9:34:10 AM
82029   9:35:13 AM

所以前 7 行应该打印 00:00:17，接下来的 4 行是 00:00:04 等等，因为它们具有相同的 A 值.这可能吗?感觉它越来越近但又不完全.太感谢了.任何帮助将不胜感激

So the first 7 rows should print 00:00:17, the next 4 is 00:00:04 etc. since they are of the same A value. Is this possible? Feel like its getting close but not quite. Thanks a million. Any help would be greatly appreciated

推荐答案

使用这个:

=MAX(IF(A1:A6=1889,B1:B6))-MIN(IF(A1:A6=1889,B1:B6))

然后按 CTRL+SHIFT+ENTER 进行评估.

and press CTRL+SHIFT+ENTER to evaluate it.

测试工作簿

这篇关于给定A列中的值，在EXCEL中找到B列中的最小值/最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！