Как подсчитать различные и уникальные значения в Excel таблицах
В этом руководстве вы узнаете, как подсчитывать уникальные значения в Excel с помощью формул и как получить автоматическое подсчет различных значений в сводной таблице. Мы также обсудим ряд примеров формул для подсчета уникальных имен, текстов, чисел, чувствительных к регистру уникальных значений и многое другое.
При работе с большим набором данных в Excel часто может потребоваться знать, сколько существует повторяющихся и уникальных значений. И иногда, вы можете считать только различные (разные) значения.
- Уникальные значения-это значения, которые появляются в списке только один раз.
- Различные значения-это все разные значения в списке, т. е. уникальные значения плюс 1 st вхождений повторяющихся значений.
А теперь давайте посмотрим, как вы можете рассчитывать уникальные и отличные значения в Excel, используя формулы и функции сводной таблицы. В этой статье мы затронем следующие темы:
Вот общая задача, которую все пользователи Excel должны выполнять один раз в то время. У вас есть список данных, и вам нужно узнать количество уникальных значений в этом списке. Как ты это делаешь? Проще, чем вы можете подумать ниже вы найдете несколько формул для подсчета уникальных значений различных типов.
Понятно, что лично вас (и многих других) интересует тайное знание (ТЗ) о Windows, которое я доставляю время от времени. Но должен ли я его доставлять в каждой записи? Нет, конечно.
Сводные таблицы в Google Spreadsheets | Ренат Шагабутдинов
- Функция COUNTIF подсчитывает, сколько раз каждое отдельное значение появляется в указанном диапазоне. В этом примере COUNTIF(A2:A10,A2:A10) возвращает массив .
- Функция IF оценивает каждое значение в массиве, возвращаемом COUNTIF, сохраняет все 1 (уникальные значения) и заменяет все другие значения нулями. Таким образом, функция IF(COUNTIF(A2:A10,A2:A10)=1,1,0)становится IF(1;2;2;1;2;2;2;1;2) = 1,1,0,тем, что превращается в массив , где 1-уникальное значение, а 0-повторяющееся значение.
- Наконец, функция SUM суммирует значения в массиве, возвращаемом IF, и выводит общее количество уникальных значений, что именно то, что мы хотели.
Евгений, спасибо, что читаете мой блог (в т.ч. предыдущие записи — там много интересного:) и поддерживаете идею этой записи. И я очень рад, что она подвигла вас на отправку первого (судя по имени/e-mail) комментария!
17 ответов
вставьте 3-й столбец и в ячейку C2 вставить эту формулу
и скопируйте его. Теперь создайте свой pivot на основе 1-го и 3-го столбцов. Смотрите снимок
UPDATE: теперь вы можете сделать это автоматически с помощью Excel 2013. Я создал это как новый ответ, потому что мой предыдущий ответ фактически решает немного другую проблему.
Если у вас такая версия, то выберите данные для создания сводной таблицы при создании таблицы, убедитесь, что опция ‘флажок добавить эти данные в модель данных (см. ниже).
затем, когда откроется сводная таблица, создайте строки, столбцы и значения обычно. Затем щелкните поле, которое требуется вычислить, и измените параметры значения поля:
наконец, прокрутите вниз до самого последнего варианта и выберите ‘Distinct Count.’
Это должно обновить значения сводной таблицы, чтобы показать данные, которые вы ищете.
Я хотел бы добавить дополнительный параметр в микс, который не требует формулы, но может быть полезен, если вам нужно подсчитать уникальные значения в наборе в двух разных столбцах. Используя оригинальный пример, у меня не было:
Я нашел лучший способ получить мои данные в этом формате, а затем иметь возможность манипулировать им дальше было использовать следующее:
как только вы выберете «запуск total in», выберите заголовок для вторичного набора данных (в этом случае это будет заголовок или заголовок столбца набора данных, который включает 123, 456 и 567). Это даст вам максимальное значение с общим количеством элементов в этом наборе в вашем основном наборе данных.
затем я скопировал эти данные, вставил их как значения, а затем поместил их в другую сводную таблицу, чтобы управлять ею более легкий.
FYI, у меня было около четверти миллиона строк данных, поэтому это работало намного лучше, чем некоторые из подходов формулы, особенно те, которые пытаются сравнить через два столбца/набора данных, потому что он продолжал сбой приложения.
возможность выполнения «отдельного подсчета» является частью Excel 2013, но не включается автоматически.
Excel как изменить сводную таблицу — Офис Ассист
Приведенная выше функция говорит о том, что D2: D7 содержит счета для Батурина менее чем на $9000, а сумма должна отобразить сумму записей, в которых оно соблюдается. Формула найдет ячейку C6, которая соответствует условию, и отобразит 1.
Вычисляемые поля сводной таблицы
Задайте понятное имя, и запишите формулу, используя любые функции (имейте в виду, что вычисляемые поля не работают с текстом). В качестве примера умножим курс на 1000 и вычтем 13 процентов (=Курс*1000*0,87). Назовем поле «ЗП», добавим в область значений и в качестве операции применим максимум. Посмотрите новый вид отчета:
Можно заметить, что результат по новому полю возвращает «странный» результат. Это происходит от того, что предварительно по значению столбца «курс» производится суммирование, а только потом полученный результат используется для вычисления. Если добавить в область названия строк дату, то расчет вернет требуемый результат:
Недостатки умных таблиц Excel
- Названия строк — отображает значения поля в виде названия строк. По сути данная область ничего не меняет, если назначить ей все имеющиеся поля, то получим исходную таблицу;
- Названия столбцов — значения полей отображаются в виде названий столбцов. Действие напоминает обычное транспонирование;
- Значения — со значениями столбцов, помещенными в эту область, производятся вычисления. При этом одно поле может быть назначено ей несколько раз;
- Фильтр отчета — сюда помещаются поля, значения которых служат для фильтрации строк исходных данных, которые сводная таблица должна проигнорировать.
В следующих примерах функции ЕСЛИ и СУММ используются вместе. Функция ЕСЛИ сначала проверяет значения в определенных ячейках, а затем, если возвращается значение ИСТИНА, функция СУММ складывает значения, удовлетворяющие условию.
