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

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

Чтобы подсчитать только уникальные значения на основе заданных критериев в другом столбце, вы можете применить формулу массива, основанную на функциях СУММ, ЧАСТОТА, ПОИСКПОЗ и СТРОКА. Это пошаговое руководство поможет вам справиться с самым нервным использованием формулы.


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

Как показано в приведенной ниже таблице продуктов, есть несколько дублирующихся продуктов, проданных в одном магазине в разные даты, теперь я хочу получить уникальное количество продуктов, проданных в магазине A, вы можете применить приведенную ниже формулу.

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

{=SUM(--(FREQUENCY(IF(range=criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.firstcell)+1)>0))}

аргументы

Диапазон: Диапазон ячеек содержит значение, соответствующее критериям;
Критерии: Критерии, по которым вы хотите подсчитывать уникальные значения;
Vals: Диапазон ячеек, из которых вы хотите подсчитать уникальные значения;
Валс. Первая ячейка: Первая ячейка диапазона, из которого вы хотите подсчитать уникальные значения.

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

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

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

2. Введите в нее приведенную ниже формулу и нажмите Ctrl + Shift + Enter клавиши одновременно, чтобы получить результат.

=SUM(--(FREQUENCY(IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)),ROW(D3:D16)-ROW(D3)+1)>0))

Заметки: В этой формуле E3: E16 - это диапазон, содержащий значение, соответствующее критериям, H3 - это критерии, D3: D16 - диапазон, содержащий уникальные значения, которые вы хотите подсчитать, а D3 - это первая ячейка D3: D16. Вы можете изменить их по своему усмотрению.

Как работает эта формула?

{=SUM(--(FREQUENCY(IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)),ROW(D3:D16)-ROW(D3)+1)>0))}

  • IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)):
1) E3: E16 = H3: Здесь проверяется, существует ли значение A в диапазоне E3: E16, и возвращает TRUE, если оно найдено, возвращает FALSE, если нет. Вы получите такой массив: {ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ;}.
2) МАТЧ (D3: D16, D3: D16,0): Функция ПОИСКПОЗ получает первое местоположение каждого элемента в диапазоне D3: D16 и возвращает массив, подобный этому {1; 2; 3; 2; 1; 1; 3; 2; 1; 1; 1; 2; 3; 2}.
  • IF({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;},{1;2;3;2;1;1;3;2;1;1;1;2;3;2}): Теперь для каждого ИСТИННОГО значения в массиве 1 мы получим соответствующую позицию в массиве 2, а для FALSE мы получим FALSE. Здесь вы получите новый массив как {1; FALSE; FALSE; 2; FALSE; FALSE; 3; FALSE; FALSE; 1; FALSE; FALSE; 3; FALSE}.
  • СТРОКА (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;FALSE;FALSE;2;FALSE;FALSE;3;FALSE;FALSE;1;FALSE;FALSE;3;FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14}): Здесь функция ЧАСТОТА возвращает частоту каждого числа в данном массиве: {2; 1; 2; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}.
  • =SUM(--({2;1;2;0;0;0;0;0;0;0;0;0;0;0}>0)):
1) {2;1;2;0;0;0;0;0;0;0;0;0;0;0}>0: Каждое число в массиве сравнивается с 0 и возвращает ИСТИНА, если больше 0, в противном случае возвращает ЛОЖЬ. И вы получите массив ИСТИНА ЛОЖЬ, подобный этому {ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ};
2) - {ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}: Эти два знака минус преобразуют «ИСТИНА» в 1 и «ЛОЖЬ» в 0. Здесь вы получите новый массив как {1; 1; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0 ; 0; 0}.
3) SUM{1;1;1;0;0;0;0;0;0;0;0;0;0;0}: Функция СУММ суммирует все числа в массиве и возвращает окончательный результат как 3.

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

Функция СУММ в Excel
Функция СУММ в Excel складывает значения

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

Функция ЕСЛИ в Excel
Функция Excel ЕСЛИ выполняет простой логический тест, который зависит от результата сравнения, и возвращает одно значение, если результат ИСТИНА, или другое значение, если результат ЛОЖЬ.

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

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


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

Подсчитать количество видимых строк в отфильтрованном списке
В этом руководстве объясняется, как подсчитать количество видимых строк в отфильтрованном списке в 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   Характеристики
Предлагаемые места