Note: The other languages of the website are Google-translated. Back to English
English English

Подсчет уникальных значений в диапазоне в Excel

В этом руководстве объясняется, как подсчитывать только уникальные значения среди дубликатов в списке в Excel с указанными формулами.


Как подсчитать уникальные значения в одном столбце в Excel?

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

Общие формулы

=SUMPRODUCT(--(FREQUENCY(MATCH(range,range,0),ROW(range)-ROW(range.firstcell)+1)>0))

=SUMPRODUCT(1/COUNTIF(range,range))

аргументы

Диапазон: Диапазон ячеек, в котором вы хотите подсчитывать только уникальные значения;
Range.firstcell: Первая ячейка диапазона.

Как пользоваться этими формулами?

1. Выберите пустую ячейку для вывода результата.

2. Введите одну из формул ниже в выбранную ячейку и нажмите клавишу Enter .

=SUMPRODUCT(--(FREQUENCY(MATCH(D3:D16,D3:D16,0),ROW(D3:D16)-ROW(D3)+1)>0))

=SUMPRODUCT(1/COUNTIF(D3:D16,D3:D16))

Ноты:

1) В этих формулах D3: D16 - это диапазон ячеек, в котором вы хотите подсчитывать только уникальные значения, а D3 - это первая ячейка диапазона. Вы можете изменить их по своему усмотрению.
2) Если в указанном диапазоне существуют пустые ячейки, первая формула вернет ошибку # N / A, а вторая формула вернет ошибку # DIV / 0.

Пояснение к формулам

=SUMPRODUCT(--(FREQUENCY(MATCH(D3:D16,D3:D16,0),ROW(D3:D16)-ROW(D3)+1)>0))

  • МАТЧ (D3: D16, D3: D16,0): Функция ПОИСКПОЗ получает позицию каждого элемента в диапазоне D3: D16, если значения появляются более одного раза в диапазоне данных, она вернет ту же позицию, чтобы получить такой массив {1; 2; 3; 2; 1 ; 1; 3; 2; 1; 1; 1; 2; 3; 2}.
  • СТРОКА (D3: D16) - СТРОКА (D3) +1: Здесь функция СТРОКА возвращает номер строки ссылки D3: D16 и D3, и вы получите {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16} - {3} +1.
  • Каждое число в массиве вычитает число 3, затем добавляет 1 и, наконец, возвращает {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14}.
  • FREQUENCY({1;2;3;2;1;1;3;2;1;1;1;2;3;2},{1;2;3;4;5;6;7;8;9;10;11;12;13;14}): Функция частоты вычисляет, как часто каждое число встречается в массиве данных, и возвращает массив чисел, подобных этому {6; 5; 3; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0 }.
  • SUMPRODUCT(--{6;5;3;0;0;0;0;0;0;0;0;0;0;0}>0):
{6;5;3;0;0;0;0;0;0;0;0;0;0;0}>0: Каждое число в массиве сравнивается с 0 и возвращает ИСТИНА, если больше 0, в противном случае возвращает ЛОЖЬ. И вы получите массив ИСТИНА ЛОЖЬ, подобный этому {ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ};
- {ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}: Эти два знака минус преобразуют «ИСТИНА» в 1 и «ЛОЖЬ» в 0. Здесь вы получите новый массив как {1; 1; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0 ; 0; 0}.
SUMPRODUCT({1;1;1;0;0;0;0;0;0;0;0;0;0;0}): Функция СУММПРОИЗВ суммирует все числа в массиве и возвращает окончательный результат как 3.

=SUMPRODUCT(1/COUNTIF(D3:D16,D3:D16))

  • СЧЁТЕСЛИ (D3: D16; D3: D16): Функция СЧЁТЕСЛИ подсчитывает, как часто каждое значение встречается в диапазоне D3: D16, используя все те же значения в качестве критериев. Он возвращает массив, подобный этому {6; 5; 3; 5; 6; 6; 3; 5; 6; 6; 6; 5; 3; 5}, что означает, что портативный компьютер появляется 6 раз, проектор появляется 5 раз и дисплей появляется 3 раза.
  • 1/{6;5;3;5;6;6;3;5;6;6;6;5;3;5}: Каждое число в массиве делится на 1 и возвращает новый массив как {0.166666666666667; 0.2; 0.333333333333333; 0.2; 0.166666666666667; 0.166666666666667; 0.2;
    0.333333333333333;0.166666666666667;0.166666666666667;0.166666666666667;0.333333333333333;0.2;
    0.333333333333333;}.
  • SUMPRODUCT({0.166666666666667;0.2;0.333333333333333;0.2;0.166666666666667;0.166666666666667;
    0.2;0.333333333333333;0.166666666666667;0.166666666666667;0.166666666666667;0.333333333333333;0.2;
    0.333333333333333;})
    : Затем функция СУММПРОИЗВ суммирует все числа в массиве и возвращает окончательный результат как 3.

Связанные функции

Функция СУММПРОИЗВ в Excel
Функцию СУММПРОИЗВ в Excel можно использовать для умножения двух или более столбцов или массивов, а затем получения суммы произведений.

ЧАСТОТА в Excel
Функция ЧАСТОТА Excel вычисляет, как часто значения встречаются в диапазоне значений, а затем возвращает вертикальный массив чисел.

Функция ПОИСКПОЗ в Excel
Функция ПОИСКПОЗ в Excel ищет определенное значение в диапазоне ячеек и возвращает относительное положение этого значения.

Функция СТРОКА в Excel
Функция Excel ROW возвращает номер строки ссылки.

Функция СЧЁТЕСЛИ в Excel
Функция Excel СЧЁТЕСЛИ используется для подсчета количества ячеек, соответствующих критерию.


Родственные формулы

Подсчитать количество видимых строк в отфильтрованном списке
В этом руководстве объясняется, как подсчитать количество видимых строк в отфильтрованном списке в Excel с помощью функции ПРОМЕЖУТОЧНЫЙ ИТОГ.

Подсчет уникальных значений с критериями
Это пошаговое руководство помогает подсчитывать только уникальные значения на основе заданных критериев в другом столбце.

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

Используйте СЧЁТЕСЛИ для несмежного диапазона
В этом пошаговом руководстве показано, как использовать функцию countif для несмежного диапазона в Excel.


Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.

  • Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
  • Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
  • Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
  • 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Лента Excel (с установленным Kutools for Excel)

Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Сортировать комментарии по
Комментарии (0)
Оценок пока нет. Оцените первым!
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места