Как мне создать три верхних значения из столбцов таблицы данных в Excel?
У меня есть большой лист данных в Excel (лист1) со столбцами C, D, E и F, содержащими данные о времени (12:00), местоположении (Нью-Йорк), фамилии (Смит) и имени (Джон) соответственно. Пример данных:
На отдельном листе (лист2) я хотел бы создать таблицу трех верхних значений в этих столбцах, то есть в таблице показаны 3 наиболее распространенных значения, которые появляются в каждом из вышеуказанных столбцов, а именно:
Обратите внимание, что столбцы обрабатываются совершенно отдельно, поэтому «время», отображаемое рядом с 1, должно быть временем, которое чаще всего встречается в столбце времени на листе 1, а «Местоположение», отображаемое рядом с 1, должно быть наиболее частым местоположением, и поэтому на.
Я использую приведенную ниже формулу, которая успешно генерирует наибольшее значение столбца:
=index(sheet1!C2:C999,match(max(countif (sheet1!C2:C999,sheet1!C2:C999)),countif (sheet1!C2:C999,sheet1!C2:C999),0))
Как вместо этого создать топ-3? Обратите внимание: хотя я использую Excel 365, мне бы хотелось, чтобы формула могла работать и со старыми версиями Excel.
Спасибо заранее!
Извиняюсь - я имею в виду три наиболее часто встречающихся значения в столбцах. Например, если Нью-Йорк встречается 69 раз, Лондон 65 раз и Париж 62 раза, результатом будет Нью-Йорк, Лондон и Париж (например) в ячейках A2, A3 и A4 на листе 2.
Пожалуйста, опубликуйте несколько примеров данных в своем сообщении в виде текстовой таблицы, чтобы мы могли их скопировать. Укажите свою версию Excel. Это Excel-365?
Если ваша формула возвращает наиболее часто встречающееся значение в столбце, вы, вероятно, ищете «Три наиболее часто встречающихся значения в столбце».
Скажем, у вас большая таблица данных: как обрабатывать одинаковые частоты?
Вероятно, дубликат нового вопроса связан с более старым вопросом с множеством связанных вопросов (некоторые закрыты как дубликаты).
Ответы 2
В Листе2 используйте следующую формулу:
=LET(loc,Sheet1!C2:C13,TAKE(SORT(HSTACK(UNIQUE(loc),COUNTIFS(loc,UNIQUE(loc))),2,-1),3,1))
Если вы все равно планируете использовать O365, вы можете рассказать об этом за один раз:
=LET(data,A2:D7,MAKEARRAY(3,COLUMNS(data),LAMBDA(r,c,LET(i,INDEX(data,,c),x,XMATCH(i,i),f,FREQUENCY(x,x),INDEX(SORTBY(i,DROP(f,-1),-1),r)))))
Если вы собираетесь использовать более старую версию Excel (как указано в комментариях), вы можете использовать следующее, перетаскиваемое вправо и вниз:
=INDEX(A$2:A$999,MATCH(LARGE(FREQUENCY(MATCH(A$2:A$999,A$2:A$999,),MATCH(A$2:A$999,A$2:A$999,)),ROW(A1)),FREQUENCY(MATCH(A$2:A$999,A$2:A$999,),MATCH(A$2:A$999,A$2:A$999,)),0))
Что вы подразумеваете под тремя главными ценностями? Вы имеете в виду первые 3 ряда? Если да, то попробуйте
=TAKE(C2:F999,3).