Excel: количество вхождений в таблицу

Question

На листе Excel у меня есть прямоугольная область (C3:G7 на изображении ниже), которая содержит имена людей (например, Пол, Джон, Фрэнк и т.д.)  я бы хотел

1. Создайте уникальный (лишенный дублирования) линейный список имен (в столбце B ; B9:B20 на изображении ниже, но количество строк зависит от количества уникальных имен во входном диапазоне).
2. В столбце C (C9:C20) подсчитайте, сколько раз каждое имя появляется на входе.  (Это легко сделать с помощью COUNTIF .)

3. Сортировать список вывода (B9:C20) по количеству (по убыванию).

   

Повторим: строки от 2 до 7 являются входными данными (B3:B7 можно игнорировать), а строки с 9 по 20 - выходные данные.  Я застрял на первом шаге (создание дублированного списка имен), и я мог бы также использовать некоторую помощь на третьем шаге (сортировка вывода).

Answer 1

Функция COUNTIF в Excel подсчитывает количество ячеек в диапазоне, которые соответствуют предоставленным критериям

range - диапазон ячеек для подсчета в примере от C3 до G7 этот диапазон должен быть заблокирован (т. е. строка и столбец с предшествующим знаком доллара), поэтому при перетаскивании ячейки вниз диапазон остается прежним.

критерии - критерии, которые контролируют, какие ячейки должны быть подсчитаны. здесь считая "Крис", который находится в ячейке B9.

поэтому формула, необходимая в ячейке C9

=COUNTIF($C$3:$G$7,B9)

Выберите ячейку C9, затем наведите курсор мыши на нижнюю правую часть ячейки C9, пока не появится перекрестие, затем дважды щелкните мышью, чтобы заполнить формулу до ячейки C20.

Более подробную информацию об использовании countif можно найти здесь https://support.office.com/en-us/article/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34

РЕДАКТИРОВАТЬ, чтобы учесть отредактированный вопрос

Для сортировки уникальных имен вам понадобятся вспомогательные столбцы

В ячейку J3 добавьте следующий код (обратите внимание, как указано в фигурных скобках, он вводится как формула массива, т.е. используйте Ctrl+Shift+Enter), а затем перетащите вниз, чтобы заполнить список уникальных имен.

{=INDIRECT(TEXT(MIN(IF(($C$3:$G$7<>"")*(COUNTIF($J$2:J2,$C$3:$G$7)=0),ROW($3:$7)*100+COLUMN($C:$G),7^8)),"R0C00"),)&""}

Примените счетчик в K3, как и раньше, с тем же диапазоном, но на этот раз ссылаясь на смежную уникальную ссылку на ячейку имени J3, затем перетащите вниз.

=COUNTIF($C$3:$G$7,J3)

Теперь оцените уникальные имена на основе их столбца подсчета помощников, используя следующую формулу в I3, и перетащите вниз

=RANK(K3,$K$3:$K$14,0)+COUNTIF($K3:K$14,K3)-1

Добавьте ранговые номера позиций от 1 до 12 в A10-A21, затем используйте VLOOKUP, чтобы сопоставить номер позиции с ранжированием в столбце помощника и вернуть соответственно "Имя" и Счет.

для B10 введите

=VLOOKUP($A10,$I$3:$K$14,2,FALSE)

и С10

=VLOOKUP($A10,$I$3:$K$14,3,FALSE)

Выделите B10 и C10, затем перетащите вниз, чтобы закончить.