Excel. Как из списка получить только уникальные строки и подсчитать количество повторений?

Предположим у вас есть столбец с фамилиями. Примерно такой, см. столбец А:

СЧУТЕСЛИ

Разумеется, в реальности тут будут тысячи строк. Причем, списка уникальных строк (всех встречающихся фамилий) у вас тоже нет. Но нужно его получить и заодно подсчитать количество повторов, в общем, получить табличку в столбцах F и G. Как это сделать?

По сути работа разбивается на две задачи, порядок выполнения этих задач может быть произвольным.

Решение одной строкой: Фильтр уникальных значений + СЧЕТЕСЛИ.

  1. Вначале составляем список уникальных имен. Потом подсчитываем количество совпадений.

Копируем куда-нибудь все из столбца А, например в столбец F (затем мы удалим оттуда лишние строки). Выделяем данные, выбираем в меню: Данные – Удалить дубликаты.

СЧЕТЕСЛИ

Теперь считаем количество совпадений с помощью функции СЧЕТЕСЛИ (COUNTIF для английской версии)

=СЧЁТЕСЛИ (где нужно искать; что нужно найти)

В нашем случае диапазон A$1:A$25, а фамилию лучше не писать в кавычках, а указать ячейку нового списка, т.е. F1. Сразу поставьте знак $, чтобы диапазон данных не «съезжал» при растягивании формулы на все значения:

=СЧЁТЕСЛИ(A$1:A$25;F1)

Результат:

СЧЕТЕСЛИ

  1. Как ни странно, можно и наоборот: вначале считаем количество повторяющихся фамилий, потом фильтруем их.

Механически мне второй вариант показался даже быстрее первого. Сразу пишем нужную нам формулу со СЧЕТЕСЛИ, растягиваем ее на все строки со значениями. Фамилии и результаты подсчета повторяются, это неважно:

СЧЕТЕСЛИ

Удалять дубликаты в оригинального столбца нельзя, тогда обновятся и результаты подсчета. Поэтому выделяем снова колонку А, выбираем в меню: Данные – Сортировка и фильтр – Дополнительно. В меню ставим галочку «Только уникальные записи».

СЧЕТЕСЛИ

Конкретно в нашем примере Excel ругнется всплывающим окошком, так как у нас нет заголовка таблицы, жмем там ОК. Если заголовок у колонка А есть, то проблем нет вообще. Результат:

СЧЕТЕСЛИ

Удалите лишнюю строку (в нашем случае там два Андреева).

СЧЕТЕСЛИ

Share Button

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.