Как подсчитать количество уникальных значений в диапазоне на основе данных из другого столбца в Excel?
При работе с данными в Excel нередко возникает задача подсчёта количества уникальных значений в одном столбце, сгруппированных по значениям другого. Например, у вас есть данные в двух столбцах, и требуется определить количество уникальных имён в столбце B для каждого значения из столбца A — как показано на скриншоте слева. В этой статье вы найдёте подробное руководство по эффективному решению этой задачи, а также полезные советы по оптимизации, которые помогут повысить производительность и точность расчётов.
Подсчитать количество уникальных значений в диапазоне на основе другого столбца
Подсчитать количество уникальных значений в диапазоне на основе другого столбца с помощью формулы
Если вы предпочитаете использовать формулы, для подсчёта количества уникальных значений в диапазоне можно применить комбинацию функций SUMPRODUCT и COUNTIF.
1. Введите следующую формулу в пустую ячейку, куда вы хотите поместить результат, а затем перетащите маркер заполнения вниз, чтобы получить уникальные значения для соответствующих критериев. См. скриншот:
=SUMPRODUCT(($A$2:$A$18=D2)/COUNTIF($B$2:$B$18,$B$2:$B$18&,""))
- A2:A18=D3Эта часть проверяет, соответствует ли курс в столбце A значению ячейки D3, и возвращает массив значений ИСТИНА/ЛОЖЬ.
- COUNTIF(B2:B18,B2:B18&"")подсчитывает, сколько раз каждое имя студента встречается в столбце B.
- SUMPRODUCTЭта функция суммирует результаты деления, эффективно подсчитывая уникальные имена.
Подсчитать количество уникальных значений в диапазоне на основе другого столбца с помощью Kutools для Excel
Оптимизируйте свою работу в Анализе данных с помощью Kutools для Excel — мощной надстройки, которая упрощает выполнение сложных задач. Если вам нужно подсчитать количество уникальных значений в диапазоне на основе другого столбца, Kutools предлагает интуитивно понятное и эффективное решение.
После установки Kutools для Excel перейдите по пути «Kutools» > «Merge & Split» (Объединить и разделить) > «Расширенное объединение строк», чтобы открыть одноимённое диалоговое окно.
В диалоговом окне «Расширенное объединение строк» выполните следующие настройки:
- Щёлкните имя столбца, по которому нужно подсчитать уникальные значения. В данном случае я выбираю «Course» (Курс), а затем в раскрывающемся списке столбца «Operation» (Операция) указываю «Primary Key» (Первичный ключ).
- Затем выберите имя столбца, значения в котором необходимо подсчитать, и выберите «Count» (Подсчёт) из Раскрывающийся список в столбце «Operation» (Операция);
- Установите флажок «Удалить повторяющиеся значения», чтобы подсчитывать только уникальные значения;
- Наконец, нажмите кнопку «OK».
Результат: Kutools создаст таблицу с количеством уникальных значений на основе указанного вами столбца.
Подсчитать количество уникальных значений в диапазоне на основе другого столбца с помощью функций UNIQUE и FILTER
Excel 365 и Excel 2021 (и более поздние версии) предлагают мощные функции динамических массивов, такие как UNIQUE и FILTER, которые значительно упрощают подсчёт количества уникальных значений в диапазоне на основе данных из другого столбца.
Введите или скопируйте приведённую ниже формулу в пустую ячейку, чтобы разместить результат, а затем протяните её вниз для заполнения остальных ячеек. См. скриншот:
=IFERROR(ROWS(UNIQUE(FILTER($B$2:$B$18,$A$2:$A$18=D2))), 0)
- FILTER($B$2:$B$18, $A$2:$A$18=D2)фильтрует значения в столбце B, для которых соответствующее значение в столбце A совпадает со значением ячейки D2.
- UNIQUE(...): извлекает и удаляет дубликаты из отфильтрованного списка, оставляя только уникальные значения.
- ROWS(...)подсчитывает количество строк в списке уникальных значений, тем самым определяя число уникальных элементов.
- IFERROR(..., 0)Если возникает ошибка (например, в столбце A отсутствуют совпадающие значения), формула возвращает 0 вместо сообщения об ошибке.
В заключение, подсчёт количества уникальных значений в диапазоне на основе другого столбца в Excel можно выполнить разными способами — каждый из них подходит для определённой версии программы и предпочтений пользователя. Выбрав метод, который лучше всего соответствует вашей версии Excel и рабочему процессу, вы сможете эффективно управлять и анализировать данные с точностью и лёгкостью. Если вы хотите узнать ещё больше полезных советов и приёмов работы в Excel,на нашем сайте представлены тысячи обучающих материалов.
Связанные статьи:
Как подсчитать количество уникальных значений в заданном диапазоне Excel?
Как подсчитать количество уникальных значений в диапазоне отфильтрованного столбца в Excel?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек