Excel количество значений по условию в сводной таблице - Word и Excel

На чтение 8 мин. Просмотров 45.5k.

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

Обладая базовым пониманием, вы можете выполнить свою работу за несколько секунд.

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

Одной из таких задач является подсчет разных значений в сводной таблице.

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

Прежде чем приступить к работе, важно понять разницу между «Подсчетом разных значений» и «подсчетом уникальным значений»

Содержание

Разные значения против уникальных значений
Подсчет разных значений в сводной таблице Excel
Добавление вспомогательного столбца в набор данных
Недостатки использования вспомогательного столбца
Добавить данные в модель данных и суммировать, используя «Число различных элементов»
Что если вы хотите посчитать уникальные значения (а не разные значения)?

Разные значения против уникальных значений

Кажется, что это одно и то же, но это не так.

Ниже приведен пример со списком имен, в столбцах отдельно выделены уникальные и разные имена.

Разница между уникальным и разными значениями

Уникальные значения / имена — это те, которые встречаются только один раз. Это означает, что все имена, которые повторяются и имеют дубликаты, не являются уникальными. Уникальные имена перечислены в столбце D вышеупомянутого набора данных.

Разными значениями / именами являются те, которые встречаются хотя бы один раз в наборе данных. Поэтому, если имя появляется три раза, оно все равно считается разным значением. Такой список можно получить путем удаления повторяющихся значений / имен и сохранения всех разных значений. Разные имена перечислены в столбце C приведенного выше набора данных.

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

Подсчет разных значений в сводной таблице Excel

Предположим, у вас есть данные о продажах:

Нажмите здесь, чтобы загрузить файл примера и делать все вместе со мной:

С этим набором данных вам нужно найти ответ на следующие вопросы:

Сколько сотрудников в каждом регионе (а это не что иное, как количество разных сотрудников в каждом регионе)?
Сколько сотрудников продали принтер в 2019 году?

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

Если вы используете Excel 2013 или более поздние версии, в сводной таблице есть встроенная функция, которая быстро подсчитывает количество.

А если вы используете Excel 2010 или ранние версии, вам придется изменить исходные данные, добавив вспомогательный столбец.

В этой статье рассматриваются следующие методы:

Добавление вспомогательного столбца в исходный набор данных для подсчета разных значений (работает во всех версиях).
Добавление данных в модель данных и использование параметра «Число различных элементов» (доступно в Excel 2013 и последующих версиях).

Существует третий метод, он называет метод сводной таблицы в сводной таблице.

Давайте начнем!

Добавление вспомогательного столбца в набор данных

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

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

Хотя это простой обходной путь, у него есть некоторые недостатки (которые будут рассмотрены далее).

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

Предположим, у меня есть набор данных, как показано ниже:

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

= ЕСЛИ (СЧЁТЕСЛИМН ($C$2:C2; C2; $B$2:B2; B2) > 1;0;1)

Приведенная выше формула использует функцию СЧЁТЕСЛИМН для подсчета количества раз, когда имя появляется в данном регионе. Также обратите внимание на диапазоны критериев: $C$2:C2 и $B$2:B2. Это означает, что они продолжают расширяться, когда вы идете вниз по столбцу.

Например, в ячейке F2 диапазон критериев составляет $C$2:C2 и $B$2:B2, а в ячейке F3 эти диапазоны расширяются до $C$3:C3 и $B$3:B3.

Это гарантирует, что функция СЧЁТЕСЛИМН считает первый экземпляр имени как 1, второй экземпляр имени как 2 и так далее.

Поскольку мы хотим получить только разные имена, используется функция ЕСЛИ, которая возвращает 1, когда имя появляется для региона в первый раз, и возвращает 0, когда оно появляется снова. Это гарантирует, что учитываются только разные имена, а не повторы.

Ниже показано, как будет выглядеть таблица, когда вы добавите вспомогательный столбец.

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

Ниже приведены шаги, как сделать это:

Выберите любую ячейку в таблице.
Нажмите вкладку «Вставка».

Нажмите на кнопку Сводная таблица.

В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно (и включает вспомогательный столбец), и выбран «На новый лист» в качестве места размещения.

Нажмите ОК.

Вышеуказанные шаги вставят новый лист со сводной таблицей.

Перетащите поле «Регион» в область «Строки» и поле «Помощник» в область «Значения».

Вы получите вот такую сводную таблицу:

Теперь вы можете изменить заголовок столбца с «Сумма по полю Помощник» на «Количество сотрудников».

Недостатки использования вспомогательного столбца

Хотя этот метод довольно прост, я должен выделить несколько недостатков, связанных с изменением исходных данных в сводной таблице:

Источник данных со вспомогательным столбцом не такой динамичный, как сводная таблица. Если изменится поставленная задача, вам придется вернуться к исходным данным и изменить формулу вспомогательного столбца (или добавить новый вспомогательный столбец).
Поскольку вы добавляете больше данных в источник сводной таблицы (который также добавляется в сводный кэш), это может привести к увеличению размера файла Excel.
Так как мы используем формулу Excel, это может замедлить работу вашей книги Excel, если в данных тысячи строк.

Добавить данные в модель данных и суммировать, используя «Число различных элементов»

В сводную таблицу добавлены новые функции в Excel 2013, которые позволяют получать количество различных значений.

В случае, если вы используете предыдущую версию, вы не сможете использовать этот метод (используйте метод, описанный выше).

Напомню, что у нас есть таблица данных, и мы хотим получить количество разных сотрудников в каждом регионе.

Ниже приведены шаги для получения количества разных сотрудников в сводной таблице:

Выберите любую ячейку в таблице.
Нажмите вкладку «Вставка».

Нажмите на кнопку Сводная таблица.

В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно и выбран новый рабочий лист.
Установите флажок «Добавить эти данные в модель данных».

Диалоговое окно Создание сводной таблицы

Нажмите ОК.

Приведенные выше шаги вставят новый лист с новой сводной таблицей.

Перетащите регион в область «Строки» и «Сотрудник» в область «Значения». Вы получите такую сводную таблицу:

В этой сводной таблице приводится общее количество сотрудников в каждом регионе (а не количество разных).

Чтобы получить подсчет разных значений в сводной таблице, выполните следующие действия:

Щелкните правой кнопкой мыши по любой ячейке в «Число элементов в столбце Сотрудник»
Нажмите на «Параметры полей значений».

В диалоговом окне «Параметры поля значений» выберите «Число различных элементов» в качестве операции (вам может потребоваться прокрутить список вниз, чтобы найти его).

Нажмите ОК.

Обратите внимание, что название столбца изменится с «Число элементов в столбце Сотрудник» на «Число разных элементов в столбце Сотрудник». Вы можете изменить его.

Некоторые вещи, которые нужно знать, добавляя свои данные в модель данных:

Если вы сохраните свои данные в модели данных, а затем откроете в более старой версии Excel, появится предупреждение: «Некоторые функции сводной таблицы не будут сохранены».
Когда вы добавляете свои данные в модель данных и создаете сводную таблицу, в ней не отображаются параметры добавления вычисляемых полей и вычисляемых столбцов.

Что если вы хотите посчитать уникальные значения (а не разные значения)?

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

Помните — уникальные значения и разные значения не одно и то же. Нажмите здесь, чтобы узнать разницу.

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

В таких случаях вам нужно создать один или несколько вспомогательных столбцов.

Вот формула для этого случая:

= ЕСЛИ (ЕСЛИ (СЧЁТЕСЛИМН ($C$2:$C$1001; С2; $B$2:$B$1001; В2) / СЧЁТЕСЛИ ($C$2:$C$1001; С2) <1;0;1); ЕСЛИ (СЧЁТЕСЛИ ($С2:С$22; С2) > 1;0;1);0)

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

Если имя встречается в нескольких регионах, формула возвращает 0, в противном случае возвращает единицу.

Формула также проверяет, повторяется ли имя в том же регионе или нет. Если имя повторяется, только первый экземпляр имени возвращает значение 1, а все остальные экземпляры возвращают 0.

Это может показаться немного сложным, но это опять-таки зависит от того, чего вы пытаетесь достичь.

Таким образом, если вы хотите подсчитать уникальные значения в сводной таблице, используйте вспомогательные столбцы, а если вы хотите подсчитать различные значения, вы можете использовать встроенную функцию (в Excel 2013 и более поздних версиях) или использовать вспомогательный столбец.

Источник

Здравствуйте! Есть выгрузка по продажам, сделал 2 варианта сводной таблицы, необходимо посчитать количество продаж по столбцу «назначение», т.е по тарифу 1, тарифу 2,тарифу 3. Нюанс в том, что нужно учитывать столбец «принятые платежи» в котором есть нули и отрицательные значения, платеж отклонен или возврат соответственно. В первой сводной посчитал исходя из условия, что тариф 1 больше 500 но меньше 2000 , тариф 2 от 2000 до 4000 и тариф 3 больше 5000. Все хорошо, но если фильтровать в сводной по датам, то эти значения меняться не будут. Во второй сводной этой проблемы нет, но считает она по столбцу «назначение», значения столбца «принятые платежи» естественно не учитываются. Как решить эту проблему?

Прикрепленные файлы

ОТЧЕТ2.xlsx (72.47 КБ)

Источник

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

Другой способ вычислений — использовать меры в Power Pivot, которые вы создаете с помощью формулы выражений анализа данных (DAX). Дополнительные сведения см. в разделе Создание меры в Power Pivot.

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

Для вычисления значений в сводной таблице можно использовать любые из описанных ниже методов.

Функции сведения в полях значений. В сводной таблице в области значений отображаются сводные данные, вычисленные на основе исходных данных. Рассмотрим пример с такими исходными данными:

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

В сводной таблице поле столбца Месяц содержит элементы Март и Апрель. Поле строки Регион содержит элементы Север, Юг, Восток и Запад. Значение на пересечении столбца Апрель и строки Север — это общая выручка от продаж, определенная по исходным данным, для которых столбец Месяц содержит значение Апрель, а столбец Регион — значение Север.
В сводной диаграмме поле Регион может представлять собой поле категорий, в котором элементы Север, Юг, Восток и Запад отображаются как категории. Поле Месяц поле может быть полем рядов, в котором элементы Март, Апрель и Май отображаются как ряды, представленные в легенде. Поле значений с именем Сумма продаж может содержать маркеры данных, которые представляют общую выручку в каждом регионе за каждый месяц. Например, один маркер данных может представлять (своим положением на вертикальной оси, т. е. оси значений) сумму продаж за месяц Апрель в регионе Север.

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

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

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

Для настраиваемых вычислений в полях значений доступны перечисленные ниже функции.

Функция	Результат
Без вычислений	Значение, введенное в данное поле.
% от общей суммы	Значения в процентах от общей суммы всех значений или точек данных в отчете.
% от суммы по столбцу	Все значения в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду.
% от суммы по строке	Значение в каждой строке или категории в процентах от итогового значения по этой строке или категории.
Доля	Значения в процентах от значения базового элемента в соответствующем базовом поле.
% от суммы по родительской строке	Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента по строкам).
% от суммы по родительскому столбцу	Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента по столбцам).
% от родительской суммы	Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента в выбранном базовом поле).
Отличие	Значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле.
Приведенное отличие	Значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле.
С нарастающим итогом в поле	Значение в виде нарастающего итога для последовательных элементов в базовом поле.
% от суммы с нарастающим итогом в поле	Значение в виде нарастающего итога в процентах для последовательных элементов в базовом поле.
Сортировка от минимального к максимальному	Ранг выбранных значений в определенном поле с учетом того, что наименьшему из них присваивается значение 1, а остальным — значения более высокого ранга соответственно.
Сортировка от максимального к минимальному	Ранг выбранных значений в определенном поле с учетом того, что наибольшему значению в поле присваивается значение 1, а каждому меньшему значению — более высокий ранг.
Индекс	Рассчитывает значения следующим образом: ((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)).

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

Доступность вычислений и параметров в отчете зависит от того, получены ли исходные данные из базы данных OLAP.

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

Кроме того, если сервер OLAP предоставляет вычисляемые поля, называемые «вычисляемыми элементами», вы увидите их в списке полей сводной таблицы. Вы также увидите все вычисляемые поля и вычисляемые объекты, созданные с помощью макросов, которые написаны на языке Visual Basic для приложений (VBA) и хранятся в книге, но не сможете их изменить. Если вам нужны дополнительные типы вычислений, обратитесь к администратору базы данных OLAP.

Если исходные данные получены из базы данных OLAP, то при вычислении промежуточных и общих итогов можно включить или исключить значения для скрытых элементов.
Вычисления на основе исходных данных не из базы данных OLAP. В сводных таблицах, основанных на внешних данных других типов или на данных листа Excel, для вычисления полей значений, содержащих числовые данные, используется функция «Сумма», а для вычисления полей данных, содержащих текст, — функция «Количество». Для дальнейшего анализа и обработки своих данных вы можете выбрать другие функции сведения, например «Среднее», «Максимум» или «Минимум». Кроме того, можно создавать собственные формулы, в которых используются элементы отчета или другие данные листа. Для этого нужно создать вычисляемое поле или вычисляемый объект в поле.

Формулы можно создавать только в отчетах, которые основаны на исходных данных, полученных не из источника данных OLAP. В отчетах, основанных на базе данных OLAP, формулы не поддерживаются. При использовании формул в сводных таблицах нужно учитывать описанные ниже правила синтаксиса и поведения формул.

Элементы формулы сводной таблицы. В формулах, которые создаются для вычисляемых полей и вычисляемых объектов, можно использовать операторы и выражения, как и в других формулах на листе. Также можно использовать константы и ссылаться на данные из отчета, но не допускается использование ссылок на ячейки и определенных имен. Невозможно использовать функции листа, для которых нужны аргументы в виде ссылок на ячейки или определенных имен, а также формулы массива.
Имена полей и элементов. В Excel имена полей и элементов используются для идентификации этих элементов отчета в формулах. В приведенном ниже примере для данных в диапазоне C3:C9 используется имя поля Молоко. Для вычисляемого объекта в поле Тип, оценивающего объем продаж нового продукта на основе данных о продажах молочных продуктов, можно использовать формулу =Молоко * 115%.

Примечание: На сводной диаграмме имена полей отображаются в списке полей сводной таблицы, а имена элементов можно просмотреть в каждом раскрывающемся списке полей. Не следует путать эти имена с теми, которые отображаются в подсказках к диаграммам и соответствуют именам рядов и точек данных.
Формулы работают с итоговыми суммами, а не с отдельными записями. Формула для вычисляемого поля оперирует суммой исходных данных для каждого используемого поля. Например, формула вычисляемого поля =Продажи * 1,2 умножает сумму продаж для каждого типа и региона на 1,2, а не умножает каждое отдельное значение продаж на 1,2 с последующим суммированием полученных величин.

Формулы для вычисляемых объектов оперируют отдельными записями. Например, формула вычисляемого объекта =Молоко * 115% умножает каждое отдельное значение продаж молочных продуктов на 115 %, после чего полученные величины суммируются в области «Значения».
Пробелы, цифры и символы в именах. В имени, которое содержит два или несколько полей, их порядок не имеет значения. В примере выше ячейки C6:D6 могут называться ‘Апрель Север’ или ‘Север Апрель’. Имена, которые состоят из нескольких слов либо содержат цифры или символы, нужно заключать в одинарные кавычки.
Итоги. Формулы не могут ссылаться на итоговые значения (в примере выше — это Сумма за март, Сумма за апрель и Общий итог).
Имена полей в ссылках на элементы. Вы можете включить имя поля в ссылку на элемент. Имя элемента должно быть заключено в квадратные скобки, например: Регион[Север]. Используйте этот формат, чтобы избежать ошибок #ИМЯ?, которые возникают, если два элемента в двух разных полях отчета имеют одинаковые имена. Например, если в отчете есть два элемента с именем «Мясо» в полях «Тип» и «Категория», можно избежать появления ошибок #ИМЯ?, ссылаясь на эти элементы следующим образом: Тип[Мясо] и Категория[Мясо].
Ссылки на элементы по позиции. Вы можете сослаться на элемент, указав его позицию в отчете (с учетом того, какие элементы фактически отображаются и как они отсортированы в настоящий момент). Тип[1] — это Молоко, а Тип[2] — Морепродукты. Когда позиции элементов изменятся, например, если какие-то из них будут скрыты или снова отображены, такая ссылка, возможно, будет указывать на другой элемент. Скрытые элементы не учитываются в этом индексе.

Для ссылки на элементы можно использовать относительные позиции. Они определяются относительно вычисляемого объекта, содержащего формулу. Если текущим регионом является Юг, то Регион[-1] — это Север. Если текущим регионом является Север, то Регион[+1] — это Юг. Например, для вычисляемого объекта можно использовать формулу =Регион[-1] * 3%. Например, для вычисляемого объекта можно использовать формулу =Регион[-1] * 3%. Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!.

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

Например, на этой сводной диаграмме представлены данные о продажах для каждого продавца по регионам:

Чтобы посмотреть, как будут выглядеть объемы продаж, если увеличатся на 10 %, можно создать вычисляемое поле в связанной сводной таблице и воспользоваться формулой =Продажи * 110%.

Результат сразу отображается на сводной диаграмме, как показано на этом рисунке:

Чтобы отобразить отдельный маркер данных для продаж в регионе «Север» за вычетом транспортных расходов, которые составляют 8 %, можно создать в поле «Регион» вычисляемый объект с такой формулой: =Север – (Север * 8%).

Диаграмма будет выглядеть следующим образом:

Однако вычисляемый объект, созданный в поле «Продавец», будет отображаться как ряд, представленный в легенде, и появится на диаграмме в виде точки данных в каждой категории.

Важно: Создать формулу в сводной таблице, подключенной к источнику данных OLAP, невозможно.

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

В случае вычисляемых объектов в отдельных ячейках можно вводить разные формулы. Например, если вычисляемый объект с именем ЯблокиОбласть содержит формулу =Яблоки * 0,25 для всех месяцев, то для июля, августа и сентября ее можно заменить формулой =Яблоки * 0,5.

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

Добавление вычисляемого поля

Щелкните сводную таблицу.

Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемое поле.
В поле Имя введите имя для поля.
В поле Формула введите формулу для поля.

Чтобы использовать в формуле данные из другого поля, щелкните его в списке Поля и нажмите кнопку Добавить поле. Например, чтобы вычислить величину комиссионных, составляющую 15 %, для каждого значения в поле «Продажи», введите формулу =Продажи * 15%.
Нажмите кнопку Добавить.

Добавление вычисляемого объекта в поле

Щелкните сводную таблицу.

Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
Если элементы в поле сгруппированы, на вкладке Анализ в группе Группировать выберите команду Разгруппировать.
Щелкните поле, в которое нужно добавить вычисляемый объект.
На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемый объект.
В поле Имя введите имя вычисляемого объекта.
В поле Формула введите формулу для этого объекта.

Чтобы использовать в формуле данные из имеющегося элемента, щелкните его в списке Элементы и нажмите кнопку Добавить элемент (он должен находиться в том же поле, что и вычисляемый объект).
Нажмите кнопку Добавить.

Ввод разных формул для вычисляемых объектов в отдельных ячейках

Щелкните ячейку, для которой нужно изменить формулу.

Чтобы изменить формулу для нескольких ячеек, нажмите клавишу CTRL и, удерживая ее, выделите остальные ячейки.
В строке формул внесите изменения в формулу.

Изменение порядка вычислений для нескольких вычисляемых объектов или формул

Щелкните сводную таблицу.

Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Порядок вычислений.
Щелкните одну из формул и нажмите кнопку Вверх или Вниз.
Повторите эти действия для других формул, чтобы расположить их в нужном порядке.

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

Щелкните сводную таблицу.

Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вывести формулы.

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

В случае вычисляемых объектов можно изменять отдельные формулы для конкретных ячеек одного объекта. Например, если вычисляемый объект с именем ЯблокиРасчет содержит формулу =Яблоки * 0,25 для всех месяцев, то для июля, августа и сентября ее можно заменить формулой =Яблоки * 0,5.

Определение принадлежности формулы к вычисляемому полю или вычисляемому объекту

Щелкните сводную таблицу.

Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вывести формулы.
Найдите в списке формулу, которую нужно изменить. Она может находиться в разделе «Вычисляемое поле» или «Вычисляемый объект».

Если для одного вычисляемого объекта задано несколько формул, то формулой по умолчанию, введенной при его создании, является та, для которой в столбце B указано его имя. Для остальных формул в столбце B указывается не только имя самого вычисляемого объекта, но и имена элементов, на пересечении которых используется формула.

Предположим, что есть формула по умолчанию для вычисляемого объекта с именем МойЭлемент, а также другая формула для этого объекта с именем МойЭлемент Январь Продажи. В сводной таблице вы увидите эту формулу в ячейке «Продажи» для строки «МойЭлемент» и столбца «Январь».
Чтобы внести изменения, воспользуйтесь одним из описанных ниже способов.

Редактирование формулы вычисляемого поля

Щелкните сводную таблицу.

Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемое поле.
В списке Имя выберите вычисляемое поле, для которого нужно изменить формулу.
В поле Формула измените формулу.
Нажмите кнопку Изменить.

Редактирование одной формулы для вычисляемого объекта

Щелкните поле, содержащее вычисляемый объект.
На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемый объект.
В поле Имя выберите вычисляемый объект.
В поле Формула измените формулу.
Нажмите кнопку Изменить.

Редактирование отдельной формулы для конкретной ячейки вычисляемого объекта

Щелкните ячейку, для которой нужно изменить формулу.

Чтобы изменить формулу для нескольких ячеек, нажмите клавишу CTRL и, удерживая ее, выделите остальные ячейки.
В строке формул внесите изменения в формулу.

Совет: Если есть несколько вычисляемых объектов или формул, можно настроить порядок вычислений. Дополнительные сведения см. в разделе Изменение порядка вычислений для нескольких вычисляемых объектов или формул.

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

Определите, к чему относится формула: к вычисляемому полю или вычисляемому объекту.

Вычисляемые поля отображаются в списке полей сводной таблицы. Вычисляемые объекты отображаются в виде элементов в других полях.
Выполните одно из указанных ниже действий.
- Если нужно удалить вычисляемое поле, щелкните в любом месте сводной таблицы.
- Если нужно удалить вычисляемый объект, в сводной таблице щелкните поле, которое его содержит.
  
  Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемое поле или Вычисляемый объект.
В поле Имя выберите поле или элемент, который нужно удалить.
Нажмите кнопку Удалить.

Для сведения данных в сводной таблице в Excel в Интернете можно использовать такие функции, как СУММ, СЧЁТ и СРЗНАЧ. По умолчанию для чисел в полях значений используется функция СУММ. Вы можете просматривать и редактировать сводную таблицу на основе источника данных OLAP, но не можете создать ее в Excel для Интернета.

Ниже описано, как выбрать другие функции сведения данных.

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

Выберите нужную функцию суммирования и нажмите OK.

Примечание: Функции сведения недоступны в сводных таблицах на базе источников данных OLAP.

Функция сведения данных	Вычисляемое значение
Сумма	Сумма значений. Используется по умолчанию для полей с числовыми значениями.
СЧЁТ	Количество заполненных полей. Функция сведения данных СЧЁТ работает так же, как СЧЁТЗ. СЧЁТ по умолчанию используется для пустых полей и полей с нечисловыми значениями.
Среднее	Среднее арифметическое.
Максимум	Наибольшее значение.
Минимум	Наименьшее значение.
Произведение	Произведение значений.
Количество чисел	Количество значений, содержащих числа (отличается от функции СЧЁТ, в которой учитываются заполненные поля).
Смещенное отклонение	Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
СТАНДОТКЛОНП	Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные.
ДИСП	Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Несмещенная дисперсия	Дисперсия генеральной совокупности, которая содержит все сводимые данные.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

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

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

Значения 5, 6, 7 и 6, результатом являются три уникальных значения : 5, 6 и 7.

Значения «Брэнли», «Дойл», «Дойл», «Дойл» — это два уникальных значения: «Андрей» и «Дойл».

Существует несколько способов подсчета уникальных значений среди дубликатов.

В диалоговом окне Расширенный фильтр можно извлечь уникальные значения из столбца данных и ввести их в новое место. Затем с помощью функции ЧСТРОК можно подсчитать количество элементов в новом диапазоне.

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

Убедитесь, что диапазон ячеек имеет заголовок столбца.

На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

Появится диалоговое окно Расширенный фильтр.

Нажмите кнопку Копировать в другое место.

В поле Копировать в введите ссылку на ячейку.

Вы также можете нажать кнопку Свернуть , чтобы временно скрыть диалоговое окно, выбрать ячейку на этом сайте и нажать кнопку Развернуть .

Выберите поле Уникальные записи и нажмите кнопку ОК.

Уникальные значения из выбранного диапазона копируется в новое место, начиная с ячейки, указанной в поле Копировать в.

В пустой ячейке под последней ячейкой диапазона введите функцию СТРОКИ. Используйте диапазон уникальных значений, скопированные в качестве аргумента, исключая заголовок столбца. Например, если диапазон уникальных значений — B2:B45, введите =СТРОКИ(B2:B45).

Для этой задачи используйте сочетание функций ЕСЛИ,СУММ,ЧАСТОТА,НАЙТИИ LEN:

Назначьте значение 1 каждому из истинных условий с помощью функции ЕСЛИ.

Сложить итог с помощью функции СУММ.

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

Возвращает положение текстового значения в диапазоне с помощью функции MATCH. Возвращаемая величина затем используется в качестве аргумента функции ЧАСТОТА для оценки соответствующих текстовых значений.

Находите пустые ячейки с помощью функции LEN. Пустые ячейки имеют длину 0.

Формулы, приведенные в этом примере, должны быть введены как формулы массива. Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

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

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

Функция ПОИСК ПОИСК ПО ищет указанный элемент в диапазоне ячеек, а затем возвращает его относительную позицию в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, формула =MATCH(25;A1:A3;0) возвращает число 2, поскольку 25 является вторым элементом в диапазоне.

Функция LEN возвращает количество символов в текстовой строке.

Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом другой функции. Например, СУММ(A1:A5) суммирует все числа, содержащиеся в ячейках A1–A5.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Сводная таблица для отбора Уникальных значений из списка EXCEL

Для отбора уникальных значений можно использовать формулы , расширенный фильтр или можно воспользоваться меню Данные/ Работа с данными/ Удалить дубликаты . В этой статье используем Сводные таблицы .

Пусть в столбце B имеется список с повторяющимися значениями, например список с названиями компаний (см. файл примера ). Столбец А содержит номера позиций уникальных значений.

Для наглядности уникальные значения в исходном списке выделены цветом с помощью Условного форматирования .

Используем сводную таблицу для создания списка уникальных значений. Для этого выделите столбец В таблицы с заголовком (т.е. столбец Исходный список) и во вкладке Вставка , в группе Таблицы нажмите кнопку Сводная таблица .

Примечание : Выделять столбец требуется для того, чтобы сводная таблица содержала только одно поле (столбец В ). В противном случае сводная таблица будет содержать 2 поля. Это не повлияет на вычисления, но для наглядности пока не будем включать в сводную таблицу столбец А .

Поле Сводной таблицы Исходный список перетащите в область Названия строк.

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

Сортировка как в источнике данных

Чтобы сохранить сортировку как в исходной таблице нам потребуется создать дополнительный столбец в источнике данных сводной таблицы. Для этого в столбце А введите формулу

Эта формула пронумерует все первые повторы значений, остальные строки будут содержать значение Пустой текст «».

Теперь создадим другую сводную таблицу. Для этого нужно выделить любую ячейку в диапазоне таблице ( А7:В22 ). В этой таблице будет 2 поля.

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

Подсчет различных значений в сводной таблице Excel (простое пошаговое руководство)

Сводные таблицы Excel великолепны (я знаю, что упоминаю об этом каждый раз, когда пишу о сводных таблицах, но это правда).

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

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

И одна из таких вещей — подсчет различных значений в сводной таблице.

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

Но прежде чем я перейду к подсчету различных значений, важно понять разницу между «уникальным счетом» и «уникальным счетом».

Отличный счет против уникального счетчика

Хотя это может показаться одним и тем же, это не.

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

Уникальные значения / имена те, которые возникают только один раз. Это означает, что все повторяющиеся и повторяющиеся имена не уникальны. Уникальные имена перечислены в столбце C в приведенном выше наборе данных.

Отличные ценности / имена те, которые встречаются в наборе данных хотя бы один раз. Таким образом, если имя встречается три раза, оно все равно считается одним отдельным именем. Этого можно достичь, удалив повторяющиеся значения / имена и сохранив все разные. Отличительные имена перечислены в столбце B приведенного выше набора данных.

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

Подсчет различных значений в сводной таблице Excel

Предположим, у вас есть данные о продажах, как показано ниже:

Щелкните здесь, чтобы загрузить файл с примером, и следуйте инструкциям.

Предположим, вы хотите найти ответ на следующие вопросы:

Сколько торговых представителей работает в каждом регионе (это не что иное, как отдельное количество торговых представителей в каждом регионе)?
Сколько торговых представителей продали принтер в2021-2022 гг.?

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

Если вы используете Excel 2013 или более поздние версии, в сводную таблицу есть встроенная функция, которая быстро дает вам точный счет. И если вы используете Excel 2010 или более ранние версии, вам придется изменить исходные данные, добавив вспомогательный столбец.

В этом руководстве рассматриваются следующие два метода:

Добавление вспомогательного столбца в исходный набор данных для подсчета уникальных значений (работает во всех версиях).
Добавление данных в модель данных и использование опции Distinct Count (доступно в Excel 2013 и более поздних версиях).

В этой статье Роджер показывает третий метод (который он называет методом Pivot the Pivot Table).

Добавление вспомогательного столбца в набор данных

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

Хотя это простой обходной путь, у этого метода есть некоторые недостатки (которые будут рассмотрены позже в этом руководстве).

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

Предположим, у меня есть набор данных, как показано ниже:

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

В приведенной выше формуле используется функция СЧЁТЕСЛИМН, чтобы подсчитать, сколько раз имя появляется в данном регионе. Также обратите внимание, что диапазон критериев — $ C $ 2: C2 и $ B $ 2: B2. Это означает, что он продолжает расширяться по мере того, как вы спускаетесь по столбцу.

Например, в ячейке E2 диапазоны критериев: $ C $ 2: C2 и $ B $ 2: B2, а в ячейке E3 эти диапазоны расширяются до $ C $ 2: C3 и $ B $ 2: B3.

Поскольку мы хотим получить только отдельные имена, используется функция ЕСЛИ, которая возвращает 1, когда имя появляется для региона в первый раз, и возвращает 0, когда оно появляется снова. Это гарантирует, что учитываются только отдельные имена, а не повторения.

Ниже показано, как будет выглядеть ваш набор данных после добавления вспомогательного столбца.

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

Ниже приведены шаги для этого:

Выберите любую ячейку в наборе данных.
Щелкните вкладку «Вставить».
Нажмите на сводную таблицу (или воспользуйтесь сочетанием клавиш — ALT + N + V)
В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно (и включают вспомогательный столбец) и выбран параметр «Новый рабочий лист».
Щелкните ОК.

Вышеупомянутые шаги позволят вставить новый лист со сводной таблицей.

Перетащите поле «Регион» в область «Строки» и поле «D Count» в область «Значения».

Вы получите сводную таблицу, как показано ниже:

Теперь вы можете изменить заголовок столбца с «Sum of D count» на «Sales Rep».

Недостатки использования вспомогательной колонки:

Источник данных со вспомогательным столбцом не такой динамичный, как сводная таблица. Хотя с помощью сводной таблицы вы можете разрезать данные как угодно, когда вы используете вспомогательный столбец, вы теряете часть этой способности. Допустим, вы добавили вспомогательный столбец, чтобы получить количество отдельных торговых представителей в каждом регионе. Теперь, что, если вы также хотите получить отчетливое количество торговых представителей, продающих принтеры. Вам нужно будет вернуться к исходным данным и изменить формулу вспомогательного столбца (или добавить новый вспомогательный столбец).
Поскольку вы добавляете больше данных в источник сводной таблицы (которая также добавляется в сводный кеш), это может привести к увеличению размера файла Excel.
Поскольку мы используем формулу Excel, это может замедлить работу вашей книги Excel, если у вас есть тысячи строк данных.

Добавить данные в модель данных и подвести итоги, используя определенное количество

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

Если вы используете предыдущую версию, вы не сможете использовать этот метод (следует также попробовать добавить вспомогательный столбец, как показано в методе выше этого).

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

Ниже приведены шаги, чтобы получить отчетливое значение счетчика в сводной таблице:

Выберите любую ячейку в наборе данных.
Щелкните вкладку Вставка.
Нажмите на сводную таблицу (или воспользуйтесь сочетанием клавиш — ALT + N + V)
В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно и «Новый рабочий лист» выбран.
Установите флажок «Добавить эти данные в модель данных».
Щелкните ОК.

Вышеупомянутые шаги позволят вставить новый лист с новой сводной таблицей.

Перетащите область в область «Строки» и «Торговый представитель» в область «Значения». Вы получите сводную таблицу, как показано ниже:

В приведенной выше сводной таблице указано общее количество торговых представителей в каждом регионе (а не отдельное количество).

Чтобы получить точное количество в сводной таблице, выполните следующие действия:

Щелкните правой кнопкой мыши любую ячейку в столбце «Количество торговых представителей».
Нажмите на Настройки поля значений.
В диалоговом окне «Параметры поля значения» выберите «Distinct Count» в качестве типа расчета (возможно, вам придется прокрутить список вниз, чтобы найти его).
Щелкните ОК.

Вы заметите, что название столбца изменится с «Количество торговых представителей» на «Отличное количество торговых представителей». Вы можете изменить его на все, что захотите.

Некоторые вещи, которые вы знаете, добавляя данные в модель данных:

Если вы сохраните данные в модели данных, а затем откроете их в более старой версии Excel, появится предупреждение: «Некоторые функции сводной таблицы не будут сохранены». Вы можете не увидеть отдельное количество (и модель данных) при открытии в более старой версии, которая его не поддерживает.
Когда вы добавляете данные в модель данных и составляете сводную таблицу, в ней не будут отображаться параметры для добавления вычисляемых полей и вычисляемых столбцов.

Щелкните здесь, чтобы загрузить файл примера

Что делать, если вы хотите подсчитывать уникальные значения (а не отдельные значения)?

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

Помните: уникальные значения и разные значения — это не одно и то же. Щелкните здесь, чтобы узнать разницу.

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

В таких случаях вам нужно создать один из нескольких вспомогательных столбцов.

В этом случае поможет следующая формула:

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

Если имя встречается более чем в одном регионе, возвращается 0, иначе возвращается единица.

Это может показаться немного сложным, но это опять же зависит от того, чего вы пытаетесь достичь.

Итак, если вы хотите подсчитать уникальные значения в сводной таблице, используйте вспомогательные столбцы, а если вы хотите подсчитать отдельные значения, вы можете использовать встроенные функции (в Excel 2013 и выше) или можете использовать вспомогательный столбец.

Щелкните здесь, чтобы загрузить файл примера

Вам также могут понравиться следующие руководства по сводным таблицам:

Источник

17 авг. 2022 г.
читать 2 мин

Часто вам может понадобиться вычислить сумму и количество одного и того же поля в сводной таблице в Excel.

Это легко сделать, дважды перетащив одно и то же поле в поле « Значения » при создании сводной таблицы.

В следующем примере показано, как именно это сделать.

Пример: вычисление суммы и количества одного и того же поля в сводной таблице Excel

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

Теперь предположим, что мы вставляем следующую сводную таблицу, чтобы обобщить сумму продаж по продуктам:

Теперь предположим, что мы также хотели бы суммировать количество продаж для каждого продукта.

Для этого мы можем просто снова перетащить значение « Продажи » на панели « Поля сводной таблицы » в поле « Значения »:

Затем щелкните стрелку раскрывающегося списка рядом с Sum of Sales2 и нажмите « Настройки поля значения »:

В появившемся новом окне нажмите « Подсчет », а затем нажмите « ОК »:

В сводную таблицу будет добавлено новое поле, показывающее количество продаж:

Не стесняйтесь щелкнуть имя нового поля и изменить его на «Количество продаж»:

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

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:

Excel: как отфильтровать 10 лучших значений в сводной таблице
Excel: как сортировать сводную таблицу по общей сумме
Excel: как рассчитать разницу между двумя сводными таблицами

Написано

Замечательно! Вы успешно подписались.

Добро пожаловать обратно! Вы успешно вошли

Вы успешно подписались на кодкамп.

Срок действия вашей ссылки истек.

Ура! Проверьте свою электронную почту на наличие волшебной ссылки для входа.

Успех! Ваша платежная информация обновлена.

Ваша платежная информация не была обновлена.

Источник

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

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

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

И вот о чем мы сейчас поговорим:

Как посчитать уникальные значения в столбце.
Считаем уникальные текстовые значения.
Подсчет уникальных чисел.
Как посчитать уникальные с учётом регистра.
Формулы для подсчета различных значений.
Как не учитывать пустые ячейки?
Сколько встречается различных чисел?
Считаем различные текстовые значения.
Как сосчитать различные текстовые значения с учетом условий?
Считаем количество различных чисел с ограничениями.
Как учесть регистр при подсчёте?
Как посчитать уникальные строки?
Используем сводную таблицу.

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

Уникальные значения – те, которые появляются в списке только один раз.
Различные – это все, которые имеются в списке без учета повторов, то есть уникальные плюс первое вхождение повторяющихся.

Следующий рисунок иллюстрирует эту разницу:

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

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

Считаем уникальные значения в столбце.

Предположим, у вас есть столбец с именами на листе Excel, и вам нужно подсчитать, сколько там есть неповторяющихся. Самое простое решение состоит в том, чтобы использовать функцию СУММ в сочетании с ЕСЛИ и СЧЁТЕСЛИ :

=СУММ(ЕСЛИ(СЧЁТЕСЛИ(диапазон ; диапазон ) = 1,1,0))

Примечание. Это формула массива, поэтому обязательно нажмите Ctrl + Shift + Enter, чтобы корректно ввести её. Как только вы это сделаете, Excel автоматически заключит всё выражение в {фигурные скобки}, как показано на скриншоте ниже. Ни в коем случае нельзя вводить фигурные скобки вручную, это не сработает.

В этом примере мы считаем уникальные имена в диапазоне A2: A10, поэтому наше выражение выглядит так:

{=СУММ(ЕСЛИ(СЧЁТЕСЛИ(A2:A10;A2:A10)=1;1;0))}

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

Далее в этом руководстве мы обсудим несколько других подходов для подсчета уникальных значений разных типов. И поскольку в основном они являются вариациями этой базовой формулы, имеет смысл подробно рассмотреть её. Если вы поймете, как это работает, то сможете настроить ее для своих данных. Если кого-то не интересуют технические подробности, вы можете сразу перейти к следующему примеру.

Как работает формула подсчета уникальных значений?

Как видите, здесь используются 3 разные функции – СУММ, ЕСЛИ и СЧЁТЕСЛИ. Посмотрим, что делает каждая из них:

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

В этом примере СЧЁТЕСЛИ(A2:A10;A2:A10)возвращает массив {3:2:2:1:1:2:3:2:3}.

Функция ЕСЛИ оценивает каждый элемент в этом массиве, сохраняет все единицы (то есть, уникальные) и заменяет все остальные цифры нулями.

Итак, функция ЕСЛИ(СЧЁТЕСЛИ(A2:A10;A2:A10)=1;1;0) преобразуется в ЕСЛИ({3:2:2:1:1:2:3:2:3}) = 1,1,0).

И далее она превращается в массив чисел {0:0:0:1:1:0:0:0:0}. Здесь 1 означает уникальное значение, а 0 – появляющееся более 1 раза.

Наконец, функция СУММ складывает числа в этом итоговом массиве и выводит общее количество уникальных значений. Что нам и нужно.

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

Если ваш список содержит как числа так и текст, и вы хотите посчитать только уникальные текстовые строки, добавьте функцию ЕТЕКСТ() в формулу массива, описанную выше:

{=СУММ(ЕСЛИ(ЕТЕКСТ(A2:A10)*СЧЁТЕСЛИ(A2:A10;A2:A10)=1;1;0))}

Функция ЕТЕКСТ возвращает ИСТИНА, если исследуемое содержимое ячейки является текстом, и ЛОЖЬ в противоположном случае. Поскольку звездочка (*) в формулах массива работает как оператор И, то функция ЕСЛИ возвращает 1, только если рассматриваемое одновременно текстовое и уникальное, в противном случае получаем 0. И после того, как функция СУММ сложит все числа, вы получите количество уникальных текстовых значений в указанном диапазоне.

Не забывайте нажимать Ctrl + Shift + Enter, чтобы правильно ввести формулу массива, и вы получите результат, подобный этому:

Рис3

Как вы можете видеть на скриншоте выше, мы получили общее количество уникальных текстовых значений, исключая пустые ячейки, числа, логические выражения ИСТИНА и ЛОЖЬ, а также ошибки.

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

Чтобы посчитать уникальные числа в списке данных, используйте формулу массива точно так же, как мы только что делали при подсчете текстовых данных. Отличие заключается в том, что вы используете ЕЧИСЛО вместо ЕТЕКСТ:

{=СУММ(ЕСЛИ(ЕЧИСЛО(A2:A10)*СЧЁТЕСЛИ(A2:A10;A2:A10)=1;1;0))}

Пример и результат вы видите на скриншоте чуть выше.

Уникальные значения с учетом регистра.

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

{=ЕСЛИ(СУММ((—СОВПАД($A$2:$A$10;A2)))=1;»Уникальный»;»Дубль»)}

А затем используйте простую функцию СЧЁТЕСЛИ для подсчета уникальных значений:

=СЧЁТЕСЛИ(B2:B10; «Уникальный»)

А теперь посмотрим, как можно посчитать количество значений, которые появляются хотя бы один раз, то есть так называемых различных значений.

Подсчет различных значений.

Используйте следующую универсальное выражение:

{=СУММ(1 / СЧЁТЕСЛИ( диапазон ; диапазон ))}

Помните, что это формула массива, поэтому вам следует нажать Ctrl + Shift + Enter, вместо обычного Enter.

Кроме того, вы можете использовать функцию СУММПРОИЗВ и записать формулу обычным способом:

=СУММПРОИЗВ(1 / СЧЁТЕСЛИ( диапазон ; диапазон ))

Например, чтобы сосчитать различные значения в диапазоне A2: A10, вы можете использовать выражение:

{=СУММ(1/СЧЁТЕСЛИ(A2:A10;A2:A10))}

или же

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A10;A2:A10))

Этот способ подходит не только для подсчета в столбце, но и для диапазона данных. К примеру, у нас под имена отведено две колонки. Тогда делаем так:

{=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:B10;A2:B10))}

Этот метод подходит для текста, чисел, дат.

Единственное ограничение – диапазон должен быть непрерывным и не содержать пустых ячеек и ошибок.

Если в вашем диапазоне данных есть пустые ячейки, то можно изменить:

{=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A10; A2:A10&»»))}

Тогда в расчёт попадёт и будет засчитана и пустая ячейка.

Как это работает?

Как вы уже знаете, мы используем функцию СЧЁТЕСЛИ, чтобы узнать, сколько раз каждый отдельный элемент встречается в указанном диапазоне. В приведенном выше примере, результат работы функции СЧЕТЕСЛИ представляет собой числовой массив: {3:2:2:1:3:2:1:2:3}.

После этого выполняется ряд операций деления, где единица делится на каждую цифру из этого массива. Это превращает все неуникальные значения в дробные числа, соответствующие количеству повторов. Например, если число или текст появляется в списке 2 раза, в массиве создаются 2 элемента равные 0,5 (1/2 = 0,5). А если появляется 3 раза, в массиве создаются 3 элемента 0,333333.

В нашем примере результатом вычисления выражения 1/СЧЁТЕСЛИ(A2:A10;A2:A10) является массив {0.333333333333333:0.5:0.5:1:0.333333333333333:0.5:1:0.5:0.333333333333333}.

Пока не слишком понятно? Это потому, что мы еще не применили функцию СУММ / СУММПРОИЗВ. Когда одна из этих функций складывает числа в массиве, сумма всех дробных чисел для каждого отдельного элемента всегда дает 1, независимо от того, сколько раз он появлялся. И поскольку все уникальные элементы отображаются в массиве как единицы (1/1 = 1), окончательный результат представляет собой общее количество всех встречающихся значений.

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

Помните, что все приведенные ниже выражения являются формулами массива и требуют нажатия Ctrl + Shift + Enter.

Подсчет различных значений без учета пустых ячеек

Если столбец, в котором вы хотите совершить подсчет, может содержать пустые ячейки, вам следует в уже знакомую нам формулу массива добавить функцию ЕСЛИ. Она будет проверять ячейки на наличие пустот (основная формула Excel, описанная выше, в этом случае вернет ошибку #ДЕЛ/0):

=СУММ(ЕСЛИ( диапазон <> «»; 1 / СЧЁТЕСЛИ( диапазон ; диапазон ); 0))

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

Используем:

{=СУММ(ЕСЛИ(A2:A10<>»»;1/СЧЁТЕСЛИ(A2:A10; A2:A10); 0))}

Как видите, наш список состоит из трёх имён.

Подсчет различных чисел.

Чтобы посчитать различные числовые значения (числа, даты и время), используйте функцию ЕЧИСЛО:

= СУММ(ЕСЛИ(ЕЧИСЛО( диапазон ); 1 / СЧЁТЕСЛИ( диапазон ; диапазон ); «»))

Считаем, сколько имеется различных чисел в диапазоне A2: A10:

{=СУММ(ЕСЛИ(ЕЧИСЛО(A2:A10);1/СЧЁТЕСЛИ(A2:A10; A2:A10);»»))}

Результат вы можете посмотреть ниже.

Это достаточно простое и элегантное решение, но работает оно гораздо медленнее, чем выражения, которые используют функцию ЧАСТОТА для подсчета уникальных значений. Если у вас большие наборы данных, то целесообразно переключиться на формулу, основанную на расчёте частот.

И вот еще один способ подсчета чисел:

=СУММ(—(ЧАСТОТА(диапазон; диапазон)>0))

Применительно к примеру ниже:

=СУММ(—(ЧАСТОТА(A2:A10; A2:A10)>0))

Как видите, здесь игнорируются записи, в которых имеются буквы.

Пошагово разберём, как это работает.

Функция ЧАСТОТА возвращает массив цифр, которые соответствуют интервалам, заданным имеющимися числами. В этом случае мы сравниваем один и тот же набор чисел для массива данных и для массива интервалов.

Результатом является то, что ЧАСТОТА() возвращает массив, который представляет собой счетчик для каждого числового значения в массиве данных.

Это работает, потому что ЧАСТОТА() возвращает ноль для любых чисел, которые ранее уже появились в списке. Ноль возвращается и для текстовых данных. Поэтому полученный массив выглядит следующим образом:

{3:0:0:2:0:0}

Как видите, обрабатываются только числа. Ячейки A7:A10 игнорируются, потому что там текст. А функция ЧАСТОТА() работает только с числами.

Теперь каждое из этих чисел проверяем на условие «больше нуля».

Получаем:

{ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ}

Теперь превращаем ИСТИНА и ЛОЖЬ в 1 и 0 соответственно. Делаем это при помощи двойного отрицания. Проще говоря, это двойной минус, который не меняет величину числа, но позволяет получить реальные числа, когда это вообще возможно:

{1:0:0:1:0:0}

А теперь функция СУММ складывает всё и получаем результат: 2.

Различные текстовые значения.

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

Как вы можете легко догадаться, мы просто добавим функцию ЕТЕКСТ и проверку условия:

=СУММ(ЕСЛИ(ЕТЕКСТ( диапазон ); 1 / СЧЁТЕСЛИ( диапазон ; диапазон ); «»))

Количество индивидуальных символьных значений посчитаем так:

{=СУММ(ЕСЛИ(ЕТЕКСТ(A2:A10);1/СЧЁТЕСЛИ(A2:A10; A2:A10);»»))}

Не забываем, что это формула массива.

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

В общем виде это выглядит так:

=СУММПРОИЗВ(—(ЧАСТОТА(ПОИСКПОЗ (диапазон; диапазон;0); СТРОКА (диапазон)- СТРОКА (диапазон_первая_ячейка)+1)>0))

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

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

{=СУММПРОИЗВ(— (ЧАСТОТА(ПОИСКПОЗ(A2:A10; A2:A10;0); СТРОКА(A2:A10) -СТРОКА(A2) +1)> 0))}

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

Если какая-либо из ячеек в диапазоне пустая, вам необходимо использовать более сложную формулу массива, которая включает в себя функцию ЕСЛИ:

{= СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(данные <> «»;ПОИСКПОЗ(данные; данные; 0));СТРОКА(данные) -СТРОКА(данные_первая_ячейка) +1); 1))}

Примечание: поскольку логическая проверка в операторе ЕСЛИ содержит массив, то наше выражение сразу становится формулой массива, которая требует ввода через Ctrl+Shift+Enter. Поэтому же СУММПРОИЗВ была заменена на СУММ.
Применительно к нашему примеру это выглядит так:

{=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(A2:A10 <> «»;ПОИСКПОЗ(A2:A10; A2:A10; 0));СТРОКА(A2:A10) -СТРОКА(A2) +1); 1))}

Теперь «сломать» этот расчет может только наличие ячеек с ошибками в исследуемом диапазоне.

Различные текстовые значения с условием.

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

Чтобы решить эту проблему, вам может помочь этот вариант:

{=СУММПРОИЗВ((($A$2:$A$18=E2)) / СЧЁТЕСЛИМН($A$2:$A$18;$A$2:$A$18&»»; $B$2:$B$18;$B$2:$B$18&»»))}

Введите это в пустую ячейку, куда вы хотите поместить результат, F2, например. А затем нажмите Shift + Ctrl + Enter вместе, чтобы получить правильный результат.

Поясним: здесь A2:A18 это список покупателей, с учётом которого вы ограничиваете область расчётов, B2: B18 — перечень товаров, в котором вы хотите посчитать уникальные значения, Е2 содержит критерий, на основании которого подсчет ограничивается только конкретным покупателем.

Второй способ.

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

{=СУММ(—(ЧАСТОТА(ЕСЛИ(критерий; ПОИСКПОЗ(диапазон; диапазон;0)); СТРОКА(диапазон) -СТРОКА(диапазон_первая_ячейкаl)+1)>0))}

Применительно к нашему примеру:

{=СУММ(—(ЧАСТОТА(ЕСЛИ(A2:A10 = E2; ПОИСКПОЗ(B2:B10; B2:B10;0)); СТРОКА(B2:B10) — СТРОКА(B2)+1) > 0))}

С учетом ограничений ЕСЛИ() функция ПОИСКПОЗ определяет порядковый номер только для строк, которые соответствуют критериям.

Если какая-либо из ячеек в диапазоне критериев пустая, вам необходимо скорректировать расчёт, добавив дополнительно ЕСЛИ для обработки пустых ячеек. Иначе они будут переданы в функцию ПОИСКПОЗ, которая в ответ сгенерирует сообщение об ошибке.

Вот что получилось после корректировки:

{=СУММ(— (ЧАСТОТА(ЕСЛИ(B2:B10 <> «»; ЕСЛИ(A2:A10 = E2; ПОИСКПОЗ(B2:B10; B2:B10;0))); СТРОКА(B2:B10) -СТРОКА(B2) +1)> 0))}

То есть все действия и расчёты мы производим, если в столбце B нам встретилась непустая ячейка: ЕСЛИ(B2:B10 <> «»….

Если у вас есть два критерия, вы можете расширить логику формулы путем добавления другого вложенного ЕСЛИ.

Поясним. Определим, сколько наименований товара находилось в первой партии первого покупателя.

Критерии запишем в G2 и G3.

В общем виде это выглядит так:

{=СУММ(—(ЧАСТОТА(ЕСЛИ(критерий1; ЕСЛИ(критерий2; ПОИСКПОЗ (диапазон; диапазон;0))); СТРОКА (диапазон) — СТРОКА (диапазон_первая_позиция) +1)> 0))}

Подставляем сюда реальные данные и получаем результат:

{=СУММ(—(ЧАСТОТА(ЕСЛИ(A2:A10=G2; ЕСЛИ(C2:C10=G3;ПОИСКПОЗ(B2:B10;B2:B10;0)));СТРОКА(B2:B10)-СТРОКА(B2)+1)>0))}

В первой партии 2 наименования товара, хотя и 3 позиции.

Различные числа с условием.

Если вам нужно пересчитать уникальные (с учётом первого вхождения) числа в диапазоне с учетом каких-то ограничений, можно использовать формулу, основанную на СУММ и ЧАСТОТА, и вместе с этим применять критерии.

{=СУММ(— (ЧАСТОТА(ЕСЛИ(критерий; диапазон); диапазон)> 0))}

Предположим, у нас есть перечень табельных номеров и количество отработанных часов по дням. Нужно сосчитать, сколько человек хотя бы раз отработали менее чем по 8 часов, то есть неполную смену.

Вот наша формула массива:

{=СУММ(— (ЧАСТОТА(ЕСЛИ(B2:B10 < 8; A2:A10); A2:A10)> 0))}

Как видите, таких случаев 3, но связаны они с двумя работниками.

Различные значения с учетом регистра.

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

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

{=ЕСЛИ(СУММ((—СОВПАД($A$2:$A2;$A2)))=1;»Уникальный»;»»)}

Как вы помните, все формулы массива в Excel требуют нажатия Ctrl + Shift + Enter.

После того, как это выражение будет записано, вы можете посчитать «различные» значения с помощью обычной функции СЧЁТЕСЛИ, например:

=СЧЁТЕСЛИ(B2:B10; «Уникальный»)

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

{=СУММ(ЕСЛИОШИБКА(1/ЕСЛИ($A$2:$A$10<>»»; ЧАСТОТА(ЕСЛИ(СОВПАД($A$2:$A$10; ТРАНСП($A$2:$A$10)); ПОИСКПОЗ(СТРОКА($A$2:$A$10); СТРОКА($A$2:$A$10)); «»); ПОИСКПОЗ(СТРОКА($A$2:$A$10); СТРОКА($A$2:$A$10))); 0); 0))}

Как видите, обе формулы дают одинаковые результаты.

Подсчет уникальных строк в таблице.

Подсчет уникальных / различных строк в Excel сродни пересчёту уникальных и различных значений. С той лишь разницей, что вы используете функцию СЧЁТЕСЛИМН вместо СЧЁТЕСЛИ, что позволяет вам указать сразу несколько столбцов для проверки уникальности.

Например, чтобы подсчитать уникальные строки на основе столбцов A (Имя) и B (Фамилия), используйте один из следующих вариантов:

Для уникальных строк:

{=СУММ(ЕСЛИ(СЧЁТЕСЛИМН(A3:A11;A3:A11; B3:B11;B3:B11)=1;1;0))}

Для различных строк:

{=СУММ(1/СЧЁТЕСЛИМН(A3:A11;A3:A11;B3:B11;B3:B11))}

Естественно, вы не ограничены только двумя столбцами. Функция СЧЁТЕСЛИМН может обрабатывать до 127 пар диапазон / критерий.

Как можно использовать сводную таблицу.

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

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

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

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

Когда откроется сводная таблица, расположите области строк, столбцов и значений так, как вам нужно. Если у вас нет большого опыта работы со сводными таблицами Excel, могут оказаться полезными следующие подробные рекомендации: Создание сводной таблицы в Excel.
Переместите поле, количество уникальных элементов которого вы хотите вычислить ( поле « Товар» в этом примере), в область « Значения» , щелкните его и выберите «Параметры значения поля…» из раскрывающегося меню.
Откроется диалоговое окно , прокрутите вниз до операции «Число разных элементов» , которая является самым последним пунктом в списке, выберите ее и нажмите OK .

Вы также можете дать собственное имя своему счетчику, если хотите.

Готово! Вновь созданная сводная таблица будет отображать количество различных товаров, как показано на самом первом скриншоте в этом разделе.

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

Благодарю вас за чтение и надеюсь увидеть вас снова. Пожалуйста, не переключайтесь!

Как найти и выделить уникальные значения в столбце — В статье описаны наиболее эффективные способы поиска, фильтрации и выделения уникальных значений в Excel. Ранее мы рассмотрели различные способы подсчета уникальных значений в Excel. Но иногда вам может понадобиться только просмотреть уникальные…

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

Как выделить цветом повторяющиеся значения в Excel? — В этом руководстве вы узнаете, как отображать дубликаты в Excel. Мы рассмотрим различные методы затенения дублирующих ячеек, целых строк или последовательных повторений с использованием условного форматирования. Ранее мы исследовали различные…

Как посчитать количество повторяющихся значений в Excel? — Зачем считать дубликаты? Мы можем получить ответ на множество интересных вопросов. К примеру, сколько клиентов сделало покупки, сколько менеджеров занималось продажей, сколько раз работали с определённым поставщиком и т.д. Если…

Как убрать повторяющиеся значения в Excel? — В этом руководстве объясняется, как удалять повторяющиеся значения в Excel. Вы изучите несколько различных методов поиска и удаления дубликатов, избавитесь от дублирующих строк, обнаружите точные повторы и частичные совпадения. Хотя…

Источник

Подсчёт количествa уникальных значений в сводной таблице

GLR

Дата: Среда, 08.01.2020, 05:13 |
Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

Добрый день и с праздниками.
Прошу помощь в решение ситуации необходимо подсчитать количество уникальных значений в сводной таблице по
столбцу №1, столбцы № 2 и №3 выступают условием (строки и столбцы в сводной), пример во вложение.

К сообщению приложен файл:

3174401.xlsx
(73.8 Kb)

Ответить

Xenus91

Дата: Среда, 08.01.2020, 05:53 |
Сообщение № 2

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 129

Репутация:
12
±

Замечаний:
0% ±

Excel 2016

GLR,
Доброе утро!
Добавляем Table4 в модель данных>строим сводную по созданной модели
в параметрах полей значений выбираем «Число разных элементов»

Сообщение отредактировал Xenus91 — Среда, 08.01.2020, 05:54

Ответить

GLR

Дата: Среда, 08.01.2020, 06:05 |
Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

Добрый день, Xenus91.
Провидите ликбез касательно Table4 в модель данных, у меня отсутствует в параметрах «Число разных элементов».

Ответить

Xenus91

Дата: Среда, 08.01.2020, 06:31 |
Сообщение № 4

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 129

Репутация:
12
±

Замечаний:
0% ±

Excel 2016

GLR, попробуйте по скриншотам

Сообщение отредактировал Xenus91 — Среда, 08.01.2020, 06:33

Ответить

GLR

Дата: Среда, 08.01.2020, 06:43 |
Сообщение № 5

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация:
0
±

Замечаний:
0% ±

Excel 2016

Xenus91, огромное спасибо!!!
Все работает.

Ответить

Источник

Подсчет Уникальных ТЕКСТовых значений в MS EXCEL

Смотрите также CreateObject(«Scripting.Dictionary») — 1

Так что, формулисты, что она невыдаёт #ЗНАЧ! iCell.Value, Trim(iCell.Value) уникальные представители. СделатьСЦЕПИТЬ (CONCATENATE)HugoGuest издания сводная считает: =СУММ (1/МУМНОЖ (—(A1:A5&B1:B5=ТРАНСП совокупности, где выборка статья была вам Пустые ячейки иметь текстовое значение, хотя

Задача

Произведем подсчет уникальных текстовыхMyArray = [a1:a12000]Cells(i + 1, бросайте своё тормозное слишком велика (ну,Михаил С.Next

 это можно в

: Вот может и: Ещё раз задача кол-во записей (A1:A5&B1:B5));{1:1:1:1:1})) является подмножеством всей полезна. Просим вас длину 0 (количество

и нулевой длины. значений в диапазонеFor Each a 2) = aNew(i)

занятие и пишите

по крайней мере: массивСЧЁТ_РАЗНЫХ = .Count

 любой пустой ячейкеТогда дальнейшая задача будет мой файлик с (см фаил)Файл удален

Формула массива, ввод генеральной совокупности. уделить пару секунд символов).СОВЕТ ячеек. In MyArrayNext макросы и UDF!!!

не выше, чем=СУММ(1/(ЕСЛИ(СЧЁТЕСЛИ(A4:P16;A4:P16);СЧЁТЕСЛИ(A4:P16;A4:P16);1)))-СЧЁТЕСЛИ(A4:P16;»»)

End With соседних столбцов, введя сводиться к поиску «излишествами» как разна листе 1- велик размер. Ctrl+Shift+EnterСТАНДОТКЛОНП и сообщить, помоглаСкопируйте таблицу в ячейку: Как подсчитать уникальныеСначала поясним, что значитD.Add CStr(a), aCells(2, 5) =Казанский обращение к ячейкеЕсли пустые не

End Function туда вот такую дубликатов уже в и пригодился. таблица, на основании [Модераторы]В формуле массивСтандартное отклонение генеральной совокупности, ли она вам, A1 на пустом числовые значения показано

 подсчет уникальных значений.Next a Timer — Start: Интересно! А функция диапазона). Поэтому, наверное, считаем за уникальные

Alex_ST формулу с известной одном столбце.Serge которой строится своднаяФас единиц длиной в

excel2.ru

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

 которая содержит все с помощью кнопок листе в Excel в одноименной статье Пусть имеется массивNewMyArray = D.ItemsCells(2, 6) = листа как таковая? выигрыш в скоростиAlex_ST: К стати, kim, функцией ВПР (VLOOKUP) иЧуть более сложный способ,: Думаю можно обойтись таблица лист 7: Берётся автофильтр вбивается количество строк, и сводимые данные. внизу страницы. Для для работы с Подсчет уникальных числовых текстовых значений {«а»,»b»,

For i = UBound(aNew) + 1Serge при использовании массива: Михаил, не посоветуете, скопировав ее вниз чем первые два, без VBA.Сводная таблица считает издание оттуда копируется если строк много,ДИСП удобства также приводим примерами формул, использующих значений.»а»,»а»,»а»

0 To UBound(NewMyArray)[CalcTime] = Timer: Ага, а потом

вместо прямого обращениятак может бытьа как в
на весь столбец: но зато -
Вопрос только в только количество записей, расширенным фильром уникальные например 99, тоОценка дисперсии генеральной совокупности, ссылку на оригинал функции.СОВЕТ}. При подсчете уникальныхCells(i + 1, — Start выясняется что расчётный к ячейкам будет
и будет работать формуле =СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16)) обойти=ЕСЛИ(МАКС(NameCount) динамический, т.е. с том насколько таблицы а надо, чтобы
и считается строчки единицы устанешь писать. где выборка является (на английском языке).Данные

Пример

: Как подсчитать уникальные игнорируются все повторы, 2) = NewMyArray(i)End Sub файл не работал незначительным…


(честно говоря, даже	ошибку #ДЕЛ/0! если
или в английской версии	автоматическим пересчетом, т.е.
большие?..	считала кол-во уникальных
Ad	Выход массив единиц
	подмножеством всей генеральной
Для сведения данных в	Данные
числовые значения с	т.е. значения выделенные
Next	Sub btnCollections1_Click()
у начальника на	слэн
не проверял), но	в диапазоне попадается
Excel:	если список редактируется
Начиная с 2007-й версии	записей: т.е. в сводной делать формулой совокупности. сводной таблице можно
986	дополнительными условиями (критериями)жирнымCells(5, 5) =Dim NewMyArray(), MyArray деловой презентации потому: гораздо быстрее
в вашем примере	хотя бы одна=IF(MAX(NameCount) или в него функция удаления дубликатовВ данном примере это сделать никак

=СУММ (1/МУМНОЖ (—(A1:A5&B1:B5=ТРАНСПДИСПР

использовать такие функции,Руслан показано в статье Подсчет. Соответственно, подсчитываются остальные Timer — StartDim Col As что макросы отключеныКазанский теряется вся простота
пустая ячейка?Эта формула проходит сверху дописываются еще элементы, является стандартной - у Ва-банка 3 нельзя? (A1:A5&B1:B5));СТРОКА (A1:ИНДЕКС (A:A;СЧЁТЗДисперсия генеральной совокупности, которая как СУММ, СЧЁТДмитрий Уникальных ЧИСЛОвых значений

support.office.com

Сведение данных в сводной таблице

 значения, т.е. «а»Cells(5, 6) = New Collection были.: Алексей, а протестируй и элегантность предложеннойkim вниз по столбцу то они автоматически найти ее можно уникальных рекламодателя, УAd (A:A)))^0)) содержит все сводимые и СРЗНАЧ.563 в MS EXCEL. и «b». Ответ UBound(NewMyArray) + 1Start! = TimerИ стоя на

разные методы и kim формулы…: =СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16&»»))-1NameCount проверяются на уникальность

на вкладке Телесемь 5 (См: Просто в данномAd данные.По умолчанию для значений67

Примечание: очевиден: количество уникальных[CalcTime] = TimerOn Error Resume бирже труда макрописец отпишись?Ну, разве можно

Alex_STи выводит все

 и отбираются. ВДанные — Удаление дубликатов таблицу на листе примере два издания,: При формировании сводной

Число различных объектов	в числовых полях 789
Мы стараемся как	значений равно 2. — Start Next упрямо повторяет себе:Для тестов создай сравнить по трудоёмкости: Спасибо. позиции списка с предыдущих способах при (Data — Remove 1){/post}{/quote} а реали их таблицы Количество уникальных значений. Эта сводной таблицы используется235 можно оперативнее обеспечиватьПроизведем подсчет числа уникальныхEnd SubMyArray = [a1:a12000] «А у меня массив из миллиона написания и возможномуА почему -1
номерами в отдельную	изменении исходного списка Duplicates)фаил забыл прикрепить больше сотни можетв левом столбце функция сведения работает функция СУММ. НижеРуслан
вас актуальными справочными	текстовых значений в
Sub filter()	For Each a
считает-то всё-равно быстрее»…	случайных чисел в
при этом количеству	в конце формулы
таблицу:	нужно будет заново:Guest быть. И по
отображаються сотрудники(фамилии сортируются	только при использовании описано, как выбратьДмитрий
материалами на вашем	диапазонеStart! = Timer In MyArrayБрр.
диапазоне 1…200 000.	ошибок формулы: вдруг появилось?karl311
запускать	В открывшемся окне нужно: кто точно знает? каждому автофильтром? и не повторяются), модели данных в

support.office.com

Excel: Как посчитать количество уникальных записей?

другие функции сведения689 языке. Эта страницаA7:A15Range(«A1:A12000»).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Worksheets(1).Range(«B1»),

Col.Add a, CStr(a)слэнGuest
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16))
Уникальных значений раньше: Здравствуйте.
Расширенный фильтр с помощью флажковможет такое своднаяGuest а в правом
Excel.
данных.789
переведена автоматически, поэтому(см. файл примера). Unique:=True
Next a
: и еще можно: да, коллекции медленнееи формулу массива было 48, а
Нужна небольшая Вашаили жать на задать те столбцы, или нет?: Да а ещё (там где суммируется,Помогите написать формулу, котораяВ сводной таблице щелкнитеДмитрий ее текст может Диапазон может содержать[CalcTime] = Timer’Если работа с немножко причесать.. :)

 чем массив, но={СУММ(1/(ЕСЛИ(СЧЁТЕСЛИ(A4:P16;A4:P16);СЧЁТЕСЛИ(A4:P16;A4:P16);1)))-СЧЁТЕСЛИ(A4:P16;»»)} теперь вдруг получилось
помощь. Уже не кнопку по которым необходимоIgor67 можно просмотром и
находится среднее и.т.п) высчитывала бы по правой кнопкой мыши
143 содержать неточности и пустые ячейки. — Start коллекцией далее неприемлема,например, зачем в скорость выявления уникальностиКазанский

 47. первый день ломаюУдаление дубликатов.
обеспечивать уникальность. Т.е.: Просто сводной, вероятно
анализом каждой ячейки. кол-во проданных товарных двум столбцам кол-во то поле, которое56 грамматические ошибки. ДляРешениеCells(6, 5) =
то перебросить её коллекцию передавать значение, ключей очень высока

подсчёт количества УНИКАЛЬНЫХ значение в Сводной таблице

: Вкладывать worksheetfunction’ы такДа и мой голову над этой
Итак, снова имеем список если включить все не получится. МожноGuest наименований. При этом уникальных записей. Пример нужно изменить, и237 нас важно, чтобыЗапишем формулу =СУММПРОИЗВ((A7:A15<>»»)/СЧЁТЕСЛИ(A7:A15;A7:A15)) Timer — Start в массив если нужно только (подразумеваю, что ключи нельзя. макрос говорит, что задачкой. Нужно подсчитать беспорядочно повторяющихся элементов.

 флажки, то будут попробовать создать доп: Хорошо, если в если сотрудник по
на рисунке. Спасибо выберите пункт67

 эта статья былаЕсли в диапазоне кромеEnd Sub
’ReDim NewMyArray(1 To посчитать? там хранятся упорядоченно)
Function СЧЁТ_РАЗНЫХ_2(Диапазон As их всё-таки 48… количество уникальных значений Например, такой: удалены только полностью столбцы в 1 сводной это не многу раз продавалSupreme council
Итоги по235 вам полезна. Просим текстовых значений содержатсяФайл не даю,

 Col.Count)передавайте только ключ — в этом Range) As Longkim в массиве иПервая задача — пронумеровать совпадающие строки. Если построчно создать уникальные делается, то как одни и теже

: 1. Вычисление количества.Формула вас уделить пару также и числа,

 сильно секретный :)i = 1слэн и выигрыш. Еще’=СУММПРОИЗВ(1/СЧЁТЕСЛИ(Диапазон;Диапазон)): Алексей, поищите огрехи
 частоту их появления, всех уникальных представителей включить только флажок

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

Описание (результат) секунд и сообщить, то формула подсчитаетКазанский

For Each a: или пересчет был быстрее конструкция dictionarys..СЧЁТ_РАЗНЫХ_2 = Evaluate(«SumProduct(1/CountIf(« в макросе :) т.е. значение «7» списка, дав каждому

 заказчик, то останется по счетесли() проставить чтобы чтобы значения будут считаться, но

=SUM(IF(FREQUENCY(A2:A10;A2:A10)>0;1)) данных.=СУММ(ЕСЛИ(ЧАСТОТА(A2:A10,A2:A10)>0,1)) помогла ли она и их. Чтобы: У меня ввод In Col отключен :) и удобнее. Но & Диапазон.Address &Дело в том, столько то раз,
 свой номер (столбец только по одной 1 или 0.

 поля первого уровня мне надо чтобы=СУММ (ЕСЛИ (ЧАСТОТАПримечание:

Подсчет количества уникальных числовых вам, с помощью

 игнорировать числовые значения формулы в ячейку’ NewMyArray(i) =
Serge требует подключения доп

«,» & Диапазон.Address что в таком значение «12» столько А на рисунке). строке для каждогоИли посчитать результат

 были в каждой подсчёт шёл только (A2:A10;A2:A10)>0;1)) Функции сведения недоступны в значений в диапазоне кнопок внизу страницы. нужно записать формулу =СУММПРОИЗВ(ЕТЕКСТ(A7:A15)/СЧЁТЕСЛИ(A7:A15;A7:A15)) листа 14,5 с,

 a: Не согласен. Для библиотеки.
& «))») виде, формула интерпретирует то раз и Для этого вставляем
заказчика и т.д. сводной другой формулой. ячейке? см фаил по УНИКАЛЬНЫМ неповторяемым2. Вычисление количества

сводных таблицах на A2:A10 без учета Для удобства такжеЕсли в список постоянно «СЧЁТ_РАЗНЫХ_3» 28,9 с.Cells(i, 2) =

 того что быAlex_STEnd Function
пусто как еще т.д. в ячейку А2Если у вас Excel
Примеры в файле. т.е. как на наименованиям. Как это уникальных числовых и базе источников данных

пустых ячеек и приводим ссылку на добавляются значения, то Ровно в 2 a пересчёт отключить надо

: По просьбам слушателей

слэн одно уникальное, вот
Заранее благодарен. и копируем затем

 2003 или старше,Igor67 базе одной сводной сделать ? текстовых значений (не OLAP. текстовых значений (4) оригинал (на английском можно создать Динамический
раза. Интересно, почему?i = i + 1
лесть в настройки,

 во время обеденного: а не надо мы его иkim вниз до упора то для удаления
: Подумал, а ведь сделать другую
Ad работает, если есть

Функция сведения данных=СУММ(ЕСЛИ(ЧАСТОТА(ПОИСКПОЗ(B2:B10,B2:B10,0),ПОИСКПОЗ(B2:B10,B2:B10,0))>0,1))

 языке) . именованный диапазон Исходный_список.слэнNext а это мало перерыва (раньше работа этой «элегантности» :)

 отнимаем. Для более: Для этого нужна следующую формулу: дубликатов и вытаскивания можно искать неФайл удален: Неужели никто не

пустые ячейки)Для расчета

Подсчет количества уникальных текстовыхПредположим, что вы хотите=СУММПРОИЗВ((Исходный_список<>»»)/СЧЁТЕСЛИ(Исходный_список;Исходный_список))

: не помню сCells(3, 5) = кто делает (я мешала) сделал сравнение
в vba эффективнее простого визуального восприятия функция СЧЁТЕСЛИ()- масса=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;»») из списка уникальных пусто, что реализуется- велик размер.
знает как это

=SUM(IF(FREQUENCY(MATCH(B2:B10;B2:B10;0);MATCH(B2:B10;B2:B10;0))>0;1)): и числовых значений

узнать, сколько уникальныхТеперь при добавлении новых

 кем мы оттачивали Timer — Start вообще ещё не разных методов подсчёта будет простым перебором,

 уменьшил проверяемый диапазон. примеров на форуме.В английской версии это
(неповторяющихся) элементов можно через массив, а [Модераторы]

planetaexcel.ru

Извлечение уникальных элементов из диапазона

Способ 1. Штатная функция в Excel 2007

делается?=СУММ (ЕСЛИ (ЧАСТОТАСУММ в диапазоне B2:B10, текстовых и числовых значений они будут скорость этого алгоритмаCells(3, 6) = встречал таких сотрудников

числа уникальных значений только к вашемуслэнДля подсчета уникальных: будет: использовать ИтогGuestПодсчет кол-ва уникальных (ПОИСКПОЗ (B2:B10;B2:B10;0);ПОИСКПОЗ (B2:B10;B2:B10;0))>0;1))Сумма значений. Используется по который не должен значений в диапазоне учитываться формулой.

Способ 2. Расширенный фильтр

— точно с Col.Count кто знает как в диапазоне. алгоритму еще добавить: да, но если =СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16))=IF(COUNTIF(B$1:B2;B2)=1;MAX(A$1:A1)+1;»»)Расширенный фильтр (Advanced Filter) тогда формула подсчета: ну кто-нибуть что-нибуть

— по моему3. Вычисление количества умолчанию для полей содержать пустые ячейки

есть. Например, еслиПримечание ZVI, а вот[CalcTime] = Timer это делается). АКонечно, создавать «массив считываение диапазона в

пустых ячеек все

HugoЭта формула проверяет сколькоиз меню (вкладки) будет выглядеть
занет как сделать задача весьма распространённая уникальных значений (универсальная с числовыми значениями. (7) столбец содержит текст
: Уникальные значения в файле примера выделены еще учавствовал или — Start вот уровень безопасности из миллиона случайных массив..

же нет? :)

: СЧЁТЕСЛИ раз текущее наименованиеДанные (Data)=ЕСЛИ( И(A5<>»»;B5<>»»); СЧЁТЗ(СМЕЩ(B5;0;0;ПОИСКПОЗ(«*Итог»;A5:A500;0);1)); выше описанные задачи?mazayZR формула) Если поле является=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),»»),ЕСЛИ(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),»»))>0,1)) значения с помощью Условного форматирования (см.

degassad или Anik..End Sub АЙтишники всегда ставят чисел в диапазоне

Способ 3. Выборка уникальных записей формулой

а в этойAlex_STAlex_ST уже встречалось в. «»)XD: экий вы, батенька,=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0;MATCH(A2:A10;A2:A10;0);»»);IF(LEN(A2:A10)>0;MATCH(A2:A10;A2:A10;0);»»))>0;1)) пустым или содержитПодсчет количества уникальных текстовыхРуслан «,» Сергей «,» статью Выделение уникальных значений но там былоSub btnCollections2_Click() высокий и тут 1…200 000″ я элегантной формуле перебор: Макрос считает правильно.

: kim, списке (считая сПредположим, что у нас

Василий: нетерпеливый!=СУММ (ЕСЛИ (ЧАСТОТА нечисловые значения (текст, и числовых значений Сергей, Сергей в MS EXCEL). чуточку точнее вDim NewMyArray(), MyArray

 уже необходимы знания

не стал, т.к. происходит не один

 Проверялось не раз

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16))- это здОрово! начала), и если имеется вот такой: Игорь, спасибо!!!Guestвы попали на (ЕСЛИ (ДЛСТР (A2:A10)>0;ПОИСКПОЗ даты или логические в диапазоне A2:A10, имеется два уникальных

Часто вместо формулы =СУММПРОИЗВ((A7:A15<>»»)/СЧЁТЕСЛИ(A7:A15;A7:A15)) используют концеDim Col As что бы их времени жалко, но раз, а по (в том числеЭлементарно просто и это количество =1, список беспорядочно повторяющихсяInter_E: дак а делается-то обеденный перерыв и (A2:A10;A2:A10;0);»»);ЕСЛИ (ДЛСТР (A2:A10)>0;ПОИСКПОЗ величины), при его без учета пустых значения — Руслан  более простую формулуAlex_ST New Collection

(макросы) включить. с меньшим размером количеству элементов -
даже и ручным элегантно. т.е. элемент встретился

названий компаний:: Добрый день! У это как? послеобеденный сон. (A2:A10;A2:A10;0);»»))>0;1)) переносе в область ячеек и текстовых и Сергей. Если =СУММПРОИЗВ(1/СЧЁТЕСЛИ(A7:A15;A7:A15)). Разница между: По совету слэн’aStart! = TimerТак что это попробовал. для каждого из пересчётом).

Не встречал такого

 первый раз -Выбираем в меню

 меня 350000 строк,

неужели только си как выПоследнюю формулу нужно значений списка полей значений (6) диапазон содержит числа формулами состоит в

planetaexcel.ru

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

 попробовал ещё подсократитьOn Error Resume

не одно иТак вот, при элементов массива происходитА вот усовершенствованная раньше, поэтому написАл дает ему последовательноДанные — Фильтр - и данные не помощью макросов ? верно подметили, задача вводить как формулу сводной таблицы дляПримечания:

5, 6, 7, 6

 том, что вторая время выполнения СЧЁТ_РАЗНЫХ_2. Next тоже…
обработке массива из перебор всех «остальных

 вами формула даже UDF:

 возрастающий номер. Расширенный фильтр
по алфавиту, и
Порылся в форуме, весьма распространенная и
массива, т. е. него используется функция

, уникальные значения — формула учитыват значения
Попытался сначала заменить
MyArray = [a1:a12000]Hugo
10 000 цифр
элементов», сравнение с в исходном примере
Function СЧЁТ_РАЗНЫХ(Диапазон As
Для упрощения адресации дадим(Data — Filter - формула кажется не
неоднократно видел подобного
не найти ее нажать не просто
СЧЁТ.В формулах в этом 5, 6 и Пустой текст («»),
в цикле в
For Each a
: У меня есть от 0 до
условием и увеличение топик-стартера (т.е. без
Range) As Long нашим диапазонам (например,
Advanced Filter)
пересчитает, как быть? рода вопросы, оставшиеся
в поиске (так Enter, а CtrlПосле переноса поля в
примере должны быть
7.
а первая их
ключе коллекции CStr(tmpArr(i,

 In MyArray такие цифры при 100 на моём
индекса, если условие пустых ячеек в’————————————————————————————— исходя из того,.Inter_E

 без ответов.. Хотя удобно расположенного возле

 + Shift + область значений можно
введены как формулыЧтобы подсчитать только уникальные игнорирует.
j)) на tmpArr(i,Col.Add a, CStr(a) копировании 3000 уникальных не шустром рабочем
выполнено. диапазоне) даёт 47,’ Procedure :

 что в спискеПолучаем окно:: Добрый день! У
писалось выше, что кнопки «создать тему»), Enter. После этого изменить функцию сведения массива. Выделите все значения, используйте следующиеПриведем пример, когда это j):Next a

 из 11000 макросами: компе получилось следующее:я уж не когда реально и

 СЧЁТ_РАЗНЫХ может быть доВ нем: меня 350000 строк, задача распространённая весьма странно и
в строке формул на СУММ; при ячейки, содержащие формулы, функции: бывает важно.If tmpArr(i, j)’Если работа сDegassad 4.1711251. Мой макрос говорю о последующем моим макросом и

’ Author : 100 элементов) имена.Выделяем наш список компаний
и данные неЛузер™ уму не растяжимо. мы увидим, что этом для всех нажмите клавишу F2Используйте функции Если дляПусть дана таблица продаж <> «» Then
коллекцией далее неприемлема,For Each 1.625004
с прямым считыванием
делении и опять вашей же исходной
’ Topic_HEADER :
Это можно сделать
в по алфавиту, и
: Поскольку я так
может потыкаете в

формула взята в

 пустых полей и и нажмите клавиши
присвоения значения 1
товаров (см. рисунок .Add tmpArr(i, j),

 то перебросить еёFor i 2.422002
значений из диапазона суммировании.. формулой там 48 Функция СЧЁТ_РАЗНЫХ (UDF) в новых версияхИсходный диапазон (List Range) формула кажется не и не понял
поиск? а еще фигурные скобки ({}), нечисловых значений в Ctrl + Shift каждому истинному условию.
ниже, столбцы А
tmpArr(i, j)
в массив

Dictionary 1.530991 и добавлением вМихаил С.

уникальных значений…’ Topic_URL :
Excel на вкладке
. пересчитает, как быть?{/post}{/quote} задачи, описанной в можно потыкать в
это признак того,

 сводной таблице будет + Ввод.Используется функция сумм для

и В). Сзаработало. Время уменьшилосьReDim NewMyArray(1 ToAdvFilter 0.280985355 коллекцию: ну может вы

Alex_ST’ Post_Author :Формулы — Диспетчер именСтавим переключатель в положениеИмел ввиду базу первом посте темы, «приемы» и в что введенная формула задано значение 0,Чтобы просмотреть процесс вычисления сложения уникальных значений. помощью формулы =ЕСЛИ(МЕСЯЦ(B26)=1;A26;»»)

почти вдвое : Col.Count)Сами коды:СЧЁТ_РАЗНЫХ_1 — 0,44

 и правы;: К стати, знатоки Alex_ST & The_Prist
(Formulas — Name
Скопировать результат в другое

 для сводной таблицы то просто размножаю
«учебник»… массива. чтобы их можно функции по шагам,Функция частота позволяет выполнить определяются товары, которые стало 0,047 сек.For i =Sub btnDeggasad_Click() сек.=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16&»»))-ИЛИ(A4:P16=»»)

VBA, помогите, плиз… & Лузер™ manager) место (Copy toInter_E то что скопированоGuestПолосатый жираф алик было суммировать. выделите ячейку с подсчет количества уникальных были проданы вНО возникли сомнения, 1 To Col.CountDim aOld, aNew,2. Мой доработанныйтоже массив

Что-то у меня’ Post_URL :

или в старых another location): Имел ввиду базу без макроса.
: А если он: Напиши в C1СЧЁТ формулой, а затем

 значений. Эта функция январе. Если товар а С ЛЮБЫМИ’ NewMyArray(i) = i As Long, макрос с предварительнымAlex_ST не получается сделать’ DateTime : версиях — черези указываем пустую для сводной таблицы{/post}{/quote}Guest

 продал 2 пылесоса =A1&B1Количество заполненных полей. Функция на вкладке игнорирует текстовые и продан не в ЛИ ТИПАМИ ДАННЫХ Col(i)
str As String копированием из диапазона: Не понял, зачем? на основе этой 06.04.10, 12:00 меню ячейку.Уже голова разболелась…
: Ещё раз задача и две кофемолкиСкопируй её до сведения данных СЧЁТФормулы нулевые значения. Первое январе, то формула
это будет корректноCells(i, 2) =Start! = Timer в массив иБыстрее работать что
классной формулы UDF’ Purpose :
Вставка — Имя -Включаем (самое главное!) флажок (больше идеи нет, (см фаил) — в одной С5. Чтобы не работает так же,
в группе вхождение конкретное значение
возвращает значение Пустой работать? Ведь об Col(i)
aOld = [a1:a12000] добавлением в коллекцию

ли будет если (ну, чтобы потом возвращает число уникальных ПрисвоитьТолько уникальные записи и формулы захлебывают

на листе 1 ячейке обе продажи видеть формулы, можно как СЧЁТЗ. СЧЁТ

Зависимости формул она возвращает число текст. Пользователь решает

 ошибке работы макросаNextstr = «» уже из этого я сначала скопирую формулы писАть было значений в указанном(Insert — Name -
(Uniqe records only) Exel) таблица, на основании по разным позициям? выбрать в формате
по умолчанию используется

нажмите кнопку равно количество вхождений подсчитать количество уникальных

никак не узнаешь,Cells(4, 5) =For i = массива

диапазон во временный

 проще и не диапазоне Define)

и жмемКажется эту проблему, которой строится сводная Это как? Вы белый ШРИФТ. для пустых полейВычисление формулы значения. Для каждого товаров в январе т.к. включен обработчик Timer — Start 1 To UBound(aOld)СЧЁТ_РАЗНЫХ_2 — 0,094 массив, а потом указывать два раза’ Notes ::
ОК т.е «Проблема Уникальных таблица лист 7

 бы файл приложилиА в В6 и полей с. случая того же

(их всего 3:
ошибок
Cells(4, 6) =
If InStr(1, str,
сек.

буду брать по

диапазон)
’—————————————————————————————диапазону номеров (A1:A100) -.
Записей в Сводной
Сводная таблица считает
с примером. Никто
напиши =СУММ (1/СЧЁТЕСЛИ нечисловыми значениями.
Примечание: значения после первого Товар1, Товар2 иЯ с коллекциями Col.Count aOld(i, 1)) =
3. Макрос, реализующий
одному элементу массиваДаже без игнорированияDim iCell As

имяПолучите список без дубликатов: Таблицы» можно решить
только количество записей, гадать не будет.
(C1:C5;C1:C5))
Среднее Мы стараемся как можно
эта функция возвращает Товар3).

знаком мало, поэтому[CalcTime] = Timer
0 Then str

на VBA функцию
и пытаться добавить
пустых ячеек… Range
NameCount
Если требуется искать дубликаты эффективно для большых
а надо, чтобы
L&MПробелы перед скобками
Среднее арифметическое.
оперативнее обеспечивать вас
значение 0.Формула =СУММПРОИЗВ((A7:A15<>»»)/СЧЁТЕСЛИ(A7:A15;A7:A15)) вернет правильный результат 3, и сомневаюсь… — Start
= str & листа =СУММПРОИЗВ(1/СЧЁТЕСЛИ(Диапазон;Диапазон))
его в коллекцию
Пытаюсь сделать так:Set Диапазон =
всему списку с номерами не по одному,
баз только с считала кол-во уникальных
: Мазай, азачем в
удалить из формулы.
МАКС актуальными справочными материалами
Функция ПОИСКПОЗ используется для а формула =СУММПРОИЗВ(1/СЧЁТЕСЛИ(A7:A15;A7:A15)) вернет 4,
Попытки вместо значенияEnd Sub
aOld(i, 1) &

СЧЁТ_РАЗНЫХ_3 — 38,22
(метод проверки уникальности
Function СЧЁТ_РАЗНЫХ_2(Диапазон As Intersect(Диапазон.Parent.UsedRange, Диапазон)
(A1:B100) — имя
а по нескольким помошью VBA.
записей
Поиск? Жители Форума Вводить, как формулу
Наибольшее значение.
на вашем языке.
возврата позиции текстового т.к. в «пустых» добавлять только ключ:Sub btnDictionary_Click()
«|» сек.
по Уокенбаху)? Range) As Long
On Error ResumeNameList
столбцам, то можноКак вы думаете?…
В данном примере
поищут и дадут массива. Как было
МИН Эта страница переведена
значения в диапазоне. ячейках

If tmpArr(i, j)

Dim NewMyArray, MyArray,
Next iДа… Гуру форума,
Я, к стати,
’=СУММПРОИЗВ(1/СЧЁТЕСЛИ(Диапазон;Диапазон)) Next
Теперь осталось выбрать из предварительно склеить их
Михаил С.
у Ва-банка 3 ссылку.
указано выше -
Наименьшее значение.
автоматически, поэтому ее
Функция частота используетС31:С34
<> «» Then D
aNew = Split(str,
конечно, обычно правы, искал где-нибудь данные
With Application.WorksheetFunctionWith New Collection
списка в один, сделав,
: посмотрите вот эту

уникальных рекламодателя, У
Ad
в конце вводаПРОИЗВЕД
текст может содержать результирующее значение рассчитывается
на самом деле .Add «», tmpArr(i,
Start! = Timer

«|») ‘ последнее но чтобы разница

 о скорости выполненияСЧЁТ_РАЗНЫХ_2 = .SumProduct(1For Each iCellNameList своего рода, составной тему Телесемь 5 (См

: вот пример надо нажать не Enter,Произведение значений. неточности и грамматические соответствующих текстовых значений. содержатся 4 значения j)On Error Resume значение пустое во времени выполнения операций с коллекциями,

 / .CountIf(Диапазон, Диапазон)) In Диапазонвсе элементы имеющие ключ с помощью
она пока еще таблицу на листе посчитать кол-во УНИКАЛЬНЫХ а сочетание Ctrl+Shift+Enter.Смещенное отклонение
ошибки. Для насФункция LEN используется для «», которые воспринимаютсяк ускорению не
NextFor i = достигала более 400
но не нашел…End WithIf iCell.Value <> номер — это функции рядом. 1) рекламодателей у каждогоАлексей матевосов (alexm)
Оценка стандартного отклонения генеральной важно, чтобы эта поиска пустых ячеек.

ей как некое привели.
Set D = 0 To UBound(aNew) раз…!!! Не ожидал. Что-то мне подсказывает,
End Function «» Then .Add

planetaexcel.ru

и будут наши

Источник

Разные значения против уникальных значений

Подсчет разных значений в сводной таблице Excel

Добавление вспомогательного столбца в набор данных

Недостатки использования вспомогательного столбца

Добавить данные в модель данных и суммировать, используя «Число различных элементов»

Что если вы хотите посчитать уникальные значения (а не разные значения)?

Дополнительные сведения

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

Дополнительные сведения

Сводная таблица для отбора Уникальных значений из списка EXCEL

Сортировка как в источнике данных

Подсчет различных значений в сводной таблице Excel (простое пошаговое руководство)

Отличный счет против уникального счетчика

Подсчет различных значений в сводной таблице Excel

Добавление вспомогательного столбца в набор данных

Недостатки использования вспомогательной колонки:

Добавить данные в модель данных и подвести итоги, используя определенное количество

Что делать, если вы хотите подсчитывать уникальные значения (а не отдельные значения)?

Пример: вычисление суммы и количества одного и того же поля в сводной таблице Excel

Дополнительные ресурсы

Считаем уникальные значения в столбце.

Как работает формула подсчета уникальных значений?

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

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

Уникальные значения с учетом регистра.

Подсчет различных значений.

Как это работает?

Подсчет различных значений без учета пустых ячеек

Подсчет различных чисел.

Различные текстовые значения.

Различные текстовые значения с условием.

Различные числа с условием.

Различные значения с учетом регистра.

Подсчет уникальных строк в таблице.

Как можно использовать сводную таблицу.

Подсчет Уникальных ТЕКСТовых значений в MS EXCEL

Задача

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

Пример

Сведение данных в сводной таблице

Excel: Как посчитать количество уникальных записей?

подсчёт количества УНИКАЛЬНЫХ значение в Сводной таблице

Извлечение уникальных элементов из диапазона

Способ 1. Штатная функция в Excel 2007

Способ 2. Расширенный фильтр

Способ 3. Выборка уникальных записей формулой

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

А вот еще интересные статьи: