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

Большие листы данных могут быть пугающими, поэтому в Microsoft Excel есть несколько встроенных функций, которые помогают повысить эффективность работы. Возможно, вам придется посмотреть, сколько уникальных значений содержит лист, но это непросто, и вы не хотите подсчитывать значения вручную, так как это занимает слишком много времени.

К счастью, в Excel есть несколько методов подсчета уникальных значений в файле. Вы можете сделать это за считанные минуты и найти то, что ищете. Подробности читайте далее.

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

У вас есть доступ ко многим способам подсчета общего количества уникальных значений в Excel. В зависимости от вашей версии вы можете сделать это без использования формул. Это шаги для Excel 365 и Excel 2021.

1. Откройте файл Excel.
   
2. Нажмите на пустую ячейку.
   
3. Напротив fx введите “=UNIQUE (XX:XX)«
   
4. Теперь у вас будет количество уникальных значений из вашего диапазона ввода.

< р>XX в приведенной выше команде обозначает любые другие ячейки, из которых вы хотите, чтобы функция извлекала уникальные значения. Пожалуйста, не вводите XX в текстовое поле функции. Вам нужно будет ввести от первой до последней ячейки, и появится число.

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

Есть еще один способ подсчета уникальных значений, особенно если вы имеете дело только с одним столбцом. Вот как это работает.

1. Откройте любой файл Excel.
   
2. Нажмите на пустую ячейку.
   
3. Вставьте эту формулу в ячейку: =СУММПРОИЗВ(AX:AX,AX:AX)
   
4. Нажмите Enter, чтобы запустить формулу.
   
5. Теперь ячейка будет иметь число, показывающее, сколько уникальных значений в документе.

Вы также можете использовать другую формулу для задания.

1. Откройте файл Excel.
   
2. Нажмите на пустую ячейку.
   
3. Вставьте эту формулу в ячейку: =СУММ(ЕСЛИ( ЧАСТОТА(ЕСЛИ(ДЛСТР(BX:BX)>0,ПОИСКПОЗ(BX:BX,BX:BX,0),»»)), ЕСЛИ(ДЛСТР(BX:BX)>0,ПОИСКПОЗ(BX:BX,BX) :BX,0),»»))>0,1))
   
4. Нажмите сочетание клавиш Control + Shift + Enter, чтобы запустить формулу.
   
5. Теперь вы увидите количество уникальных значений в файле Excel.

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

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

Если длинные формулы слишком сложны, воспользуйтесь “Расширенным фильтром”

1. Откройте файл Excel.
   
2. Нажмите “Данные”
   
3. Перейдите к “Дополнительно” в разделе “Фильтр”
   
4. Выберите “Копировать в другое место” в появившемся окне” в разделе “Действие”
   
5. В диапазоне списка нажмите на значке справа и выберите данные диапазона для фильтрации.
   
6. Для “Копировать в” выберите ячейку для размещения отфильтрованных уникальных значений.
   
7. Проверьте “ Только уникальные записи.”
   
8. Выберите “ОК” и нажмите на другую пустую ячейку.
   
   
9. Введите “=ROWS(XX:XX)” и нажмите клавишу Enter.
   
10. Количество уникальных значений теперь будет отображаться для вас.
    

Есть много разных способов проверить номер уникальных значений. Вы также можете использовать Kutools. Чтобы начать, выполните следующие действия.

1. Установите Kutools for Excel.
   
2. Откройте Microsoft Excel.
   < img src="/wp-content/uploads/2022/05/04bdb05c31d0e58a21ae76a56bb2f5dd.png" />
3. Выберите диапазон данных для анализа.
   
4. Нажмите значок Kutools и выберите «Выбрать».
   
5. Выберите “Выбрать дубликат & Уникальные ячейки”
   

Часть 2

1. В этом диалоговом окне нажмите “Только уникальные значения”
   
2. Выберите “Каждый ряд”
   
3. Выберите два цвета в разделе “Обработка результатов” чтобы выделить параметры.
   
4. Нажмите “ОК”
   
5. Отклонить оповещение.
   
6. Теперь вы увидите выделенные все уникальные значения.
   

Этот метод не ’ не покажет вам, сколько их, поэтому вам придется считать вручную. Однако его относительно проще использовать.

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

Мы использовали некоторые формулы в предыдущие разделы, но вы можете использовать и многие другие.

Функция СЧЁТЕСЛИ

Функция СЧЁТЕСЛИ работает с числами и словами. Вот инструкции по его использованию:

1. Откройте файл Excel.
   
2. Определите, какие ячейки необходимо учитывать.
   
3. В пустом поле введите следующее: =СУММ(1/СЧЁТЕСЛИ(XX:XX,XX:XX))
   
4. Завершите процесс, нажав сочетание клавиш Ctrl + Shift + Enter.
   

Как и в приведенных выше формулах, XX обозначает ячейки-заполнители. Вам потребуется ввести фактический диапазон ячеек вручную.

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

СУММПРОИЗВ и СЧЁТЕСЛИ

Еще один простой способ их подсчета – использование комбинации формул СУММПРОИЗВ и СЧЁТЕСЛИ. .

1. Откройте Excel и выберите файл.
   
2. Проверьте, какие ячейки вы хотите подсчитать.
   < /li>
3. Введите эту формулу в пустую ячейку: =СУММПРОИЗВ(1/СЧЁТЕСЛИ(XX:XX, XX:XX))
   
4. Нажмите Ctrl + Shift + Enter.
   

Не рекомендуется использовать эту формулу, если в группе данных есть пустые ячейки. Вы получите “деление на ноль” ошибка, если вы это сделаете. Формула генерирует ноль для пустых ячеек, а поскольку деление на ноль невозможно, вы получите ошибку.

Вариант, который игнорирует пустые ячейки, выглядит следующим образом:

=СУММПРОИЗВ(((XX:XX<>»»)/СЧЁТЕСЛИ(XX:XX , XX:XX &»»)))

Этот вариант игнорирует все пустые ячейки , что позволяет находить уникальные значения в файле.

СУММ, ЧАСТОТА и ПОИСКПОЗ

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

=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(ХХ:ХХ<>»», ПОИСКПОЗ(ХХ:ХХ,ХХ:ХХ,0)),СТРОКА(ХХ:ХХ)-СТРОКА(перваяячейка)+1),1))

“firstcell” будет первой ячейкой, содержащей данные. Эта формула использует функцию ЧАСТОТА и является формулой массива. Поэтому фигурные скобки появятся в Excel при выполнении формулы.

Несмотря на то, что формула сложнее, будьте уверены, что она работает очень хорошо.

Как подсчитать уникальные Значения в сводной таблице

Сводная таблица привносит глоток свежего воздуха в Microsoft Excel, поскольку это мощный инструмент для использования пользователями. Он также может позволить вам получить уникальные значения и количество уникальных значений для файла.

Часть 1

1. Нажмите на любую пустую ячейку в файле Excel.
   
2. Перейти в “Вставить.”
   
3. Выберите “Сводная таблица”
   
4. В диалоговом окне установите флажок “Добавьте эти данные в модель данных”
   
5. В Таблица/диапазон, вставьте нужные данные диапазона.
   
6. Нажмите “ОК»
   

Часть 2

1. Нажмите маленькую стрелку на сводной таблице и перетащите ее в области полей.
   
2. Выберите “Настройки поля значений”
   
3. Прокрутите вниз и найдите раздел “Число уникальных”
   
4. Нажмите “ОК” для подтверждения.
   
5. Теперь у вас будут все уникальные значения и их различные подсчеты.

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

Power Pivot

Power Pivot — один из самых эффективных способов получения уникальных значений. Возможно, вам придется включить его, если вы используете Excel 2010, но в других версиях этой проблемы нет. Вот как начать работу:

Часть 1

1. Нажмите кнопку “Питание Поворот” вкладка
   
2. Перейдите к “Модель данных” и нажмите “Управление”
   
3. Откроется новое окно. открытым. Нажмите “Главная страница”
   
4. Выберите “Получить внешний Данные.”
   
5. Выберите “Из других источников”
   
6. Найдите файл “Excel File” вариант.
   
7. Нажмите “Далее”
   
8. Переименуйте соединение (при желании) и нажмите “Обзор&rdquo. ;
   
9. Найдите файл Excel, который хотите импортировать.
   
10. Нажмите “Использовать первую строку как заголовки столбцов” и перейдите к следующему разделу.
    

Часть 2

1. Завершите импорт файла в модель данных и нажмите “Готово”
   
2. После завершения операции импорта закройте мастер импорта.
   
3. Теперь информация будет в Power Pivot.
4. Нажмите “Сводная таблица” и выберите “Сводная таблица”
   
5. Разверните таблицу в всплывающее окно.
   
6. Поместите уникальные значения в строки, а число уникальных значений — в другой столбец.
   
7. Создайте сводную таблицу.
   

Часть 3

1. Перейдите на вкладку Power Pivot и выберите “Measures”
   
2. Создайте новую меру.
   
3. В “Формула” введите: “=DISTINCTCOUNT”
   
4. Продолжите, добавив квадратную скобку в конец формулы. Выберите “UniqueValue[name]))
   
5. Формула теперь будет выглядеть примерно так: =DISTINCTCOUNT(Sheetname[Name ]))
   
6. Измените категорию на “ Числа.”
   
7. Поменяйте местами “Формат” на “Целое число”
   
8. Выберите “ОК”
   
9. Теперь вы можете увидеть все уникальные значения и количество различных элементов.
   

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

Если у вас есть несколько критериев, по-прежнему можно узнать, сколько уникальных значений существует. Для этого задания можно использовать формулу СЧЁТЕСЛИ.

1. Найдите критерии, которые вы хотите проверить.
   
2. Нажмите на пустую ячейку.
   
3. Введите “=COUNTIFS(”) и продолжите с диапазоном, который хотите проверить.
   
4. Введите тест по критериям.
   
5. Добавьте второй диапазон, который может быть таким же, как предыдущий, или новым.
   
6. Пройти тест.
   
7. При необходимости повторите.
8. Заканчивайте “)” и нажмите клавишу Enter.

Ваша формула должна выглядеть примерно так:

=СЧЁТЕСЛИМН(XX:XX,*>* &XX,XX:XX,*<&XX,XX:XX,*<&XX)

Эта формула удобна и работает с любым количеством критериев. . Таким образом, вы можете сделать это столько, сколько требуется для работы.

Интересные числа

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

Есть ли у вас любимый метод? Какие еще способы вы знаете, которых здесь нет? Дайте нам знать в разделе комментариев ниже.