Excel : Get the most frequent value for each group

问题:

I Have a table ( excel ) with two columns ( Time 'hh:mm:ss' , Value ) and i want to get most frequent value for each group of row.

for example i have

Time    | Value
4:35:49 | 122
4:35:49 | 122
4:35:50 | 121
4:35:50 | 121
4:35:50 | 111
4:35:51 | 122
4:35:51 | 111
4:35:51 | 111
4:35:51 | 132
4:35:51 | 132

And i want to get most frequent value of each Time

Time    | Value
4:35:49 | 122
4:35:50 | 121
4:35:51 | 132

Thanks in advance

UPDATE The first answer of @scott with helper column is the correct one

See the pic

回答1:

You could use a helper column:

First it will need a helper column so in C I put

=COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,B2)

Then in F2 I put the following Array Formula:

=INDEX($B$2:$B$11,MATCH(MAX(IF($A$2:$A$11=E2,IF($C$2:$C$11 = MAX(IF($A$2:$A$11=E2,$C$2:$C$11)),$B$2:$B$11))),$B$2:$B$11,0))

It is an array formula and must be confirmed with Ctrl-Shift-Enter. Then copied down.

I set it up like this:

回答2:

Here is one way to do this in MS Access:

select tv.*
from (select time, value, count(*) as cnt
      from t 
      group by time, value
     ) as tv
where exists (select 1
              from (select top 1 time, value, count(*) as cnt
                    from t as t2
                    where t.time = t2.time
                    group by time, value
                    order by count(*) desc, value desc
                  ) as x
              where x.time = tv.time and x.value = tv.value
             );

MS Access doesn't support features such as window functions or CTEs that make this type of query easier in other databases.

回答3:

Would that work? I haven't tried and got inspired here

;WITH t3 AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY time ORDER BY c DESC, value DESC) AS rn
   FROM (SELECT COUNT(*) AS c, time, value FROM t GROUP BY time, value) AS t2
)
SELECT *
FROM t3
WHERE rn = 1