Как в столбце посчитать количество значений которые не повторяются в excel

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

Постановка задачи

Есть диапазон с данными, в котором некоторые значения повторяются больше одного раза:

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

Рассмотрим несколько способов ее решения.

Способ 1. Если нет пустых ячеек

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

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

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

Потом вычисляются дроби 1/Число вхождений для каждого элемента и все они суммируются, что и даст нам количество уникальных элементов:

Способ 2. Если есть пустые ячейки

Если в диапазоне встречаются пустые ячейки, то придется немного усовершенствовать формулу, добавив проверку на пустые ячейки (иначе получим ошибку деления на 0 в дроби):

Вот и все дела.

Ссылки по теме

• Как извлечь из диапазона уникальные элементы и удалить дубликаты
• Как подсветить дубликаты в списке цветом
• Как сравнить два диапазона на наличие в них дубликатов
• Извлечение уникальных записей из таблицы по заданному столбцу с помощью надстройки PLEX

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

Если исходный список значений находится в диапазоне

А7:А16

(см.

файл примера

), то число

неповторяющихся

значений можно вычислить с помощью формулы

=СУММПРОИЗВ(—(СЧЁТЕСЛИ(A7:A16;A7:A16)=1))

Формула хороша тем, что производит подсчет и текстовых и числовых значений, кроме того, она игнорирует

пустые

ячейки.

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

Условного форматирования

(см. статью

Выделение неповторяющихся значений в MS EXCEL

).

СОВЕТ:

Решение обратной задачи (подсчет повторяющихся значений) можно найти в статье

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

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

Хитрости »

1 Май 2011              532124 просмотров

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

Представим себе большой список различных наименований, ФИО, табельных номеров и т.п. А необходимо из этого списка оставить список все тех же наименований, но чтобы они не повторялись — т.е. удалить из этого списка все дублирующие записи. Как это иначе называют: создать список уникальных элементов, список неповторяющихся, без дубликатов. Для этого существует несколько способов: встроенными средствами Excel, встроенными формулами и, наконец, при помощи кода Visual Basic for Application(VBA) и сводных таблиц. В этой статье рассмотрим каждый из вариантов.

• При помощи встроенных возможностей Excel 2007 и выше
• При помощи Расширенного фильтра
• При помощи формул
• При помощи кодов Visual Basic for Application(VBA) — макросы, включая универсальный код выборки из произвольного диапазона
• При помощи сводных таблиц

Как использовать данную команду. Выделяете столбец(или несколько) с теми данными, в которых надо удалить дублирующие записи. Идете на вкладку Данные (Data) —Удалить дубликаты (Remove Duplicates).

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

Появится окно с параметрами удаления дубликатов

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

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

В случае с Excel 2003 все посложнее. Там нет такого инструмента, как Удалить дубликаты. Но зато есть такой замечательный инструмент, как Расширенный фильтр. В 2003 этот инструмент можно найти в Данные —Фильтр —Расширенный фильтр. Прелесть этого метода в том, с его помощью можно не портить исходные данные, а создать список в другом диапазоне.

В 2007-2010 Excel, он тоже есть, но немного запрятан. Расположен на вкладке Данные (Data), группа Сортировка и фильтр (Sort & Filter) — Дополнительно (Advanced)

Как его использовать: запускаем указанный инструмент — появляется диалоговое окно:

• Обработка: Выбираем Скопировать результат в другое место (Copy to another location).
• Исходный диапазон (List range): Выбираем диапазон с данными(в нашем случае это А1:А51).
• Диапазон критериев (Criteria range): в данном случае оставляем пустым.
• Поместить результат в диапазон (Copy to): указываем первую ячейку для вывода данных — любую пустую(на картинке — E2).
• Ставим галочку Только уникальные записи (Unique records only).
• Жмем Ок.

Примечание: если вы хотите поместить результат на другой лист, то просто так указать другой лист не получится. Вы сможете указать ячейку на другом листе, но…Увы и ах…Excel выдаст сообщение, что не может скопировать данные на другие листы. Но и это можно обойти, причем довольно просто. Надо всего лишь запустить Расширенный фильтр с того листа, на который хотим поместить результат. А в качестве исходных данных выбираем данные с любого листа — это дозволено.

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

Для этого надо просто в пункте Обработка выбрать Фильтровать список на месте (Filter the list, in-place).

Способ 2: Формулы

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

А

(

А1:А51

, где

А1

— заголовок). Выводить список мы будем в столбец

С

, начиная с ячейки

С2

. Формула в

C2

будет следующая:

{=ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1))}
{=INDEX($A$2:$A$51;SMALL(IF(COUNTIF($C$1:C1;$A$2:$A$51)=0;ROW($A$1:$A$50));1))}
Детальный разбор работы данной формулы приведен в статье: Как просмотреть этапы вычисления формул

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

Ctrl

+

Shift

+

Enter

(при этом сами скобки вводить не надо — они появятся сами после ввода формулы тремя клавишами

Ctrl

+

Shift

+

Enter

). После того, как мы ввели эту формулу в

C2

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

#ЧИСЛО!(#NUM!)

— это значит все элементы отображены и ниже протягивать формулу нет смысла. Чтобы ошибку избежать и сделать формулу более универсальной(не протягивая каждый раз до появления ошибки) можно использовать нехитрую проверку:

для Excel 2007 и выше:
{=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1));»»)}
{=IFERROR(INDEX($A$2:$A$51;SMALL(IF(COUNTIF($C$1:C1;$A$2:$A$51)=0;ROW($A$1:$A$50));1));»»)}
для Excel 2003:
{=ЕСЛИ(ЕОШ(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1));»»;ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1)))}
{=IF(ISERR(SMALL(IF(COUNTIF($C$1:C1;$A$2:$A$51)=0;ROW($A$1:$A$50));1));»»;INDEX($A$2:$A$51;SMALL(IF(COUNTIF($C$1:C1;$A$2:$A$51)=0;ROW($A$1:$A$50));1)))}

Тогда вместо ошибки 

#ЧИСЛО!(#NUM!)

у вас будут пустые ячейки(не совсем пустые, конечно — с формулами :-)).
Чуть подробнее про отличия и нюансы формул ЕСЛИОШИБКА и ЕСЛИ(ЕОШ можно прочесть в этой статье: Как в ячейке с формулой вместо ошибки показать 0

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

УНИК(UNIQUE)

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

=УНИК($A$2:$A$51)
=UNIQUE($A$2:$A$51)

Что самое важное в данном случае — это функция динамического массива и вводить её надо только в одну ячейку C2, а результат она поместит сама в нужное количество ячеек.

Способ 3: код VBA

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

• Что такое макрос и где его искать? к статье приложен видеоурок
• Что такое модуль? Какие бывают модули? потребуется, чтобы понять куда вставлять приведенные ниже коды

Оба приведенных ниже кода следует помещать в стандартный модуль. Макросы должны быть разрешены.

Исходные данные оставим в том же порядке — список с данными расположен в столбце «А«(А1:А51, где А1 — заголовок). Только выводить список мы будем не в столбец С, а в столбец Е, начиная с ячейки Е2:

Sub Extract_Unique()
    Dim vItem, avArr, li As Long
    ReDim avArr(1 To Rows.Count, 1 To 1)
    With New Collection
        On Error Resume Next
        For Each vItem In Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value
            'Cells(Rows.Count, 1).End(xlUp) – определяет последнюю заполненную ячейку в столбце А
            .Add vItem, CStr(vItem)
            If Err = 0 Then
                li = li + 1: avArr(li, 1) = vItem
            Else: Err.Clear
            End If
        Next
    End With
    If li Then [E2].Resize(li).Value = avArr
End Sub

С помощью данного кода можно извлечь уникальные не только из одного столбца, но и из любого диапазона столбцов и строк. Если вместо строки
Range(«A2», Cells(Rows.Count, 1).End(xlUp)).Value
указать Selection.Value, то результатом работы кода будет список уникальных элементов из выделенного на активном листе диапазона. Только тогда неплохо бы и ячейку вывода значений изменить — вместо [E2] поставить ту, в которой данных нет.
Так же можно указать конкретный диапазон:

Или другой столбец:

Range("C2", Cells(Rows.Count, 3).End(xlUp)).Value

здесь отдельно стоит обратить внимание то, что в данном случае помимо изменения А2 на С2 изменилась и цифра 1 на 3. Это указание на номер столбца, в котором необходимо определить последнюю заполненную ячейку, чтобы код не просматривал лишние ячейки. Подробнее про это можно прочитать в статье: Как определить последнюю ячейку на листе через VBA?

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

Sub Extract_Unique()
    Dim x, avArr, li As Long
    Dim avVals
    Dim rVals As Range, rResultCell As Range
 
    On Error Resume Next
    'запрашиваем адрес ячеек для выбора уникальных значений
    Set rVals = Application.InputBox("Укажите диапазон ячеек для выборки уникальных значений", "Запрос данных", "A2:A51", Type:=8)
    If rVals Is Nothing Then 'если нажата кнопка Отмена
        Exit Sub
    End If
    'если указана только одна ячейка - нет смысла выбирать
    If rVals.Count = 1 Then
        MsgBox "Для отбора уникальных значений требуется указать более одной ячейки", vbInformation, "www.excel-vba.ru"
        Exit Sub
    End If
    'отсекаем пустые строки и столбцы вне рабочего диапазона
    Set rVals = Intersect(rVals, rVals.Parent.UsedRange)
    'если указаны только пустые ячейки вне рабочего диапазона
    If rVals Is Nothing Then
        MsgBox "Недостаточно данных для выбора значений", vbInformation, "www.excel-vba.ru"
        Exit Sub
    End If
    avVals = rVals.Value
    'запрашиваем ячейку для вывода результата
    Set rResultCell = Application.InputBox("Укажите ячейку для вставки отобранных уникальных значений", "Запрос данных", "E2", Type:=8)
    If rResultCell Is Nothing Then 'если нажата кнопка Отмена
        Exit Sub
    End If
    'определяем максимально возможную размерность массива для результата
    ReDim avArr(1 To Rows.Count, 1 To 1)
    'при помощи объекта Коллекции(Collection)
    'отбираем только уникальные записи,
    'т.к. Коллекции не могут содержать повторяющиеся значения
    With New Collection
        On Error Resume Next
        For Each x In avVals
            If Len(CStr(x)) Then 'пропускаем пустые ячейки
                .Add x, CStr(x) 'если добавляемый элемент уже есть в Коллекции - возникнет ошибка
                'если же ошибки нет - такое значение еще не внесено,
                'добавляем в результирующий массив
                If Err = 0 Then
                    li = li + 1
                    avArr(li, 1) = x
                Else
                    'обязательно очищаем объект Ошибки
                    Err.Clear
                End If
            End If
        Next
    End With
    'записываем результат на лист, начиная с указанной ячейки
    If li Then rResultCell.Cells(1, 1).Resize(li).Value = avArr
End Sub

Способ 4: Сводные таблицы

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

• Выделяем один или несколько столбцов в таблице, переходим на вкладку Вставка(Insert) -группа Таблица(Table) —Сводная таблица(PivotTable)
• В диалоговом окне Создание сводной таблицы(Create PivotTable) проверяем правильность выделения диапазона данных (или установить новый источник данных)
• указываем место размещения Сводной таблицы:
  • На новый лист (New Worksheet)
  • На существующий лист (Existing Worksheet)
• подтверждаем создание нажатием кнопки OK

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

• выделил диапазон A1:B51 на листе Извлечение по критерию
• вызвал меню вставки сводной таблицы: вкладка Вставка(Insert) -группа Таблица(Table) —Сводная таблица(PivotTable)
  выбрал вставить на новый лист(New Worksheet)
• назвал этот лист Уникальные сводной таблицей
• поле Данные поместил в область строк
• поле ФИО в область фильтра. Почему? Чтобы удобно было выбирать одно или несколько ФИО и в сводной отображался бы список уникальных месяцев только для выбранных фамилий
  

В чем неудобство работы со сводными в данном случае: при изменении в исходных данных сводную таблицу придется обновлять вручную: Выделить любую ячейку сводной таблицы -Правая кнопка мыши —Обновить(Refresh) или вкладка Данные(Data) —Обновить все(Refresh all) —Обновить(Refresh). А если исходные данные пополняются динамически и того хуже — надо будет заново указывать диапазон исходных данных. И еще один минус — данные внутри сводной таблицы нельзя менять. Поэтому если с полученным списком необходимо будет работать в дальнейшем, то после создания нужного списка при помощи сводной его надо скопировать и вставить на нужный лист.

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

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

Скачать пример:

  Tips_All_ExtractUnique.xls (108,0 KiB, 18 431 скачиваний)

Также см.:
Работа с дубликатами
Как подсчитать количество повторений
Общие сведения о сводных таблицах

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

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

​Смотрите также​2. Вычисление количества​: ,Sanja,я конечно ниче​ IsObject(Conditions(J)) Then rngCond​

1. ​Придумал вариант с​​Казанский​​Start! = Timer​aOld = [a1:a12000]​ макросы и UDF!!!​​: да, коллекции медленнее​​ алгоритму еще добавить​

   ​ уникальных значений...​
​For Each iCell​

   

2. ​ (иначе получим ошибку​ Ctrl + Shift​​ Функция частота использует​​Автор: Антон Андронов​Этот пример показывает, как​​ уникальных числовых и​​ не понял, ну​

   ​ = Conditions(J).Value If​
​ доп.столбцом, однако способ​

   

3. ​: У меня ввод​​On Error Resume​​str = «»​

   

​Казанский​​ чем массив, но​ считываение диапазона в​Alex_ST​ In Диапазон​ деления на 0​ + Ввод.​ результирующее значение рассчитывается​Примечание:​ создать формулу массива,​

​ текстовых значений (не​

• ​ штучка прикольная. это​ IsNumeric(rngCond(I, 1)) Then​​ будет неудобен при​​ формулы в ячейку​ Next​For i =​: Интересно! А функция​
• ​ скорость выявления уникальности​ массив..​: К стати, знатоки​If iCell.Value <>​ в дроби):​Чтобы просмотреть процесс вычисления​ соответствующих текстовых значений.​Мы стараемся как​ которая подсчитает уникальные​
• ​ работает, если есть​ наверное аналог счётеслимн?​
• ​ If Application.Evaluate(Replace(rngCond(I, 1),​ большом количества сетей,​ листа 14,5 с,​​MyArray = [a1:a12000]​​ 1 To UBound(aOld)​ листа как таковая?​

​ ключей очень высока​а в этой​
​ VBA, помогите, плиз…​
​ «» Then .Add​

​Вот и все дела.​

office-guru.ru

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

​ функции по шагам,​​Функция LEN используется для​ можно оперативнее обеспечивать​ значения.​ пустые ячейки)​Sanja​ «,», «.») &​ тогда придется для​ «СЧЁТ_РАЗНЫХ_3» 28,9 с.​For Each a​If InStr(1, str,​Serge​ (подразумеваю, что ключи​ элегантной формуле перебор​Что-то у меня​ iCell.Value, Trim(iCell.Value)​karl311​ выделите ячейку с​ поиска пустых ячеек.​ вас актуальными справочными​Воспользуемся функцией​=SUM(IF(FREQUENCY(MATCH(B2:B10;B2:B10;0);MATCH(B2:B10;B2:B10;0))>0;1))​

​: Можно и так​ Conditions(J + 1))​ каждого создавать доп.столбцы​ Ровно в 2​ In MyArray​ aOld(i, 1)) =​: Ага, а потом​​ там хранятся упорядоченно)​ происходит не один​​ не получается сделать​Next​: Здравствуйте.​ формулой, а затем​​ Пустые ячейки иметь​​ материалами на вашем​COUNTIF​=СУММ (ЕСЛИ (ЧАСТОТА​

​ сказать. Название говорит​ Then arrFlag(Int(J /​Заранее благодарю!​

• ​ раза. Интересно, почему?​Col.Add a, CStr(a)​ 0 Then str​

• ​ выясняется что расчётный​ — в этом​

• ​ раз, а по​ на основе этой​СЧЁТ_РАЗНЫХ = .Count​Нужна небольшая Ваша​ на вкладке​ длину 0 (количество​ языке. Эта страница​(СЧЁТЕСЛИ). Для примера​ (ПОИСКПОЗ (B2:B10;B2:B10;0);ПОИСКПОЗ (B2:B10;B2:B10;0))>0;1))​ само за себя​ 2)) = True​Dmitriy XM​слэн​

• ​Next a​ = str &​ файл не работал​ и выигрыш. Еще​ количеству элементов -​ классной формулы UDF​

• ​End With​ помощь. Уже не​Формулы​ символов).​ переведена автоматически, поэтому​

Пример

​ подсчитаем количество чисел​3. Вычисление количества​ СЧЕТ​ Else If rngCond(I,​:​: не помню с​

​ задачкой. Нужно подсчитать​​нажмите кнопку​

• ​ листе в Excel​ грамматические ошибки. Для​, используя следующую формулу:​=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))​ обычное СЧЕТЕСЛИМН подсчитывает​ arrFlag(Int(J / 2))​: Дмитрий, благодарю, что​ — точно с​ в массив​

• ​aNew = Split(str,​ были.​ библиотеки.​ элементов», сравнение с​ указывать два раза​​ не посоветуете,​​ количество уникальных значений​​Вычисление формулы​​ для работы с​​ нас важно, чтобы​​=COUNTIF(A1:A6,5)​

support.office.com

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

Постановка задачи

​=СУММ (ЕСЛИ (ЧАСТОТА​ количество УНИКАЛЬНЫХ значений​ = True End​ не остались равнодушны​

​ ZVI, а вот​ReDim NewMyArray(1 To​ «|») ‘ последнее​И стоя на​Alex_ST​ условием и увеличение​ диапазон)​а как в​

​ в массиве и​.​

Способ 1. Если нет пустых ячеек

​ примерами формул, использующих​ эта статья была​=СЧЁТЕСЛИ(A1:A6;5)​ (ЕСЛИ (ДЛСТР (A2:A10)>0;ПОИСКПОЗ​alex1210​ If Next If​

​ к проблеме! Однако​ еще учавствовал или​ Col.Count)​ значение пустое​ бирже труда макрописец​: По просьбам слушателей​

​ индекса, если условие​Даже без игнорирования​ формуле =СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16)) обойти​ частоту их появления,​Есть диапазон с данными,​ функции.​ вам полезна. Просим​​Чтобы подсчитать уникальные значения,​ ​ (A2:A10;A2:A10;0);»»);ЕСЛИ (ДЛСТР (A2:A10)>0;ПОИСКПОЗ​​: ААААААААААААААААААА, круто​ WorksheetFunction.And(arrFlag) = True​ Ваш вариант считает​ degassad или Anik..​

​For i =​​For i =​​ упрямо повторяет себе:​ во время обеденного​ выполнено.​ пустых ячеек…​ ошибку #ДЕЛ/0! если​

Способ 2. Если есть пустые ячейки

​ т.е. значение «7»​ в котором некоторые​Данные​ вас уделить пару​ добавим функцию​ (A2:A10;A2:A10;0);»»))>0;1))​Akropochev​ Then .Add CStr(cl(I,​

​ количество накладных сети​

planetaexcel.ru

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

​ Магнит с бонусом​​ чуточку точнее в​’ NewMyArray(i) =​ — 1​
​ считает-то всё-равно быстрее»…​ мешала) сделал сравнение​

​ говорю о последующем​​Function СЧЁТ_РАЗНЫХ_2(Диапазон As​

​ Col(i)​​Next​: и еще можно​
​ в диапазоне.​Михаил С.​With Application.WorksheetFunction​: =СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16&»»))-1​Заранее благодарен.​ в диапазоне. В​

​563​​ Для удобства также​

​.​​ Enter, а Ctrl​
​Олег55​ 1 Else Err.Clear​ количество уникальных значений​
​ попробовал ещё подсократить​Next​Cells(2, 5) =​ немножко причесать.. :)​
​Конечно, создавать «массив​: ну может вы​СЧЁТ_РАЗНЫХ_2 = .SumProduct(1​

​Alex_ST​​kim​ приведенном выше примере,​
​67​ приводим ссылку на​=SUM(1/COUNTIF(A1:A6,A1:A6))​ + Shift +​: , , большое​ End If End​ по столбцу ИД​ время выполнения СЧЁТ_РАЗНЫХ_2.​Cells(4, 5) =​

​ Timer — Start​​например, зачем в​ из миллиона случайных​ и правы;​

​ / .CountIf(Диапазон, Диапазон))​​: Спасибо.​: Для этого нужна​ как легко заметить,​789​ оригинал (на английском​
​=СУММ(1/СЧЁТЕСЛИ(A1:A6;A1:A6))​ Enter. После этого​ спасибо, что не​ If Next End​ точки для сети​Попытался сначала заменить​ Timer — Start​Cells(2, 6) =​ коллекцию передавать значение,​ чисел в диапазоне​=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16&»»))-ИЛИ(A4:P16=»»)​

​ мы увидим, что​​ от моей проблемы!​
​Jack Famous​
​ больше нуля. В​ ключе коллекции CStr(tmpArr(i,​

​ Col.Count​​[CalcTime] = Timer​
​ посчитать?​ не стал, т.к.​Alex_ST​выдаёт #ЗНАЧ!​ вдруг появилось?​Для подсчета уникальных:​ варианта.​Дмитрий​
​ узнать, сколько уникальных​.​ формула взята в​ Разобрал принципы работы​: Sanja, какая крутая​
​ данном примере таковых​
​ j)) на tmpArr(i,​
​[CalcTime] = Timer​

​ попробовал.​​ ли будет если​=СУММ(1/(ЕСЛИ(СЧЁТЕСЛИ(A4:P16;A4:P16);СЧЁТЕСЛИ(A4:P16;A4:P16);1)))-СЧЁТЕСЛИ(A4:P16;»»)​
​ теперь вдруг получилось​
​: СЧЁТЕСЛИ​

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

​Да и мой​​: kim,​ нет пустых ячеек,​56​
​ значения​ в фигурные скобки​Полосатый жираф алик​ от ситуации​

​ доброго времени, подскажите​​ с бонусом, так​ tmpArr(i, j)​ D​ New Collection​: Не согласен. Для​ 10 000 цифр​ массив, а потом​Alex_ST​ макрос говорит, что​=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16))- это здОрово!​ то можно использовать​237​

​ таблице (уникальных)?​​Jack Famous​: доп столбец один​

​НО возникли сомнения,​​Set D =​MyArray = [a1:a12000]​ а это мало​ компе получилось следующее:​ его в коллекцию​ (честно говоря, даже​ в макросе :)​
​ раньше, поэтому написАл​ как формулу массива,​Описание (результат)​ и Сергей. Если​ формулу.​
​ видеть формулы, можно​

​ (метод проверки уникальности​​ не проверял), но​Дело в том,​

​ UDF:​​ т.е. нажать после​=СУММ(ЕСЛИ(ЧАСТОТА(A2:A10,A2:A10)>0,1))​ диапазон содержит числа​Пояснение:​ выбрать в формате​: В таблице посчитать​Ну, как я​Sanja​ ЛИ ТИПАМИ ДАННЫХ​MyArray = [a1:a12000]​ In MyArray​ вообще ещё не​ с прямым считыванием​ по Уокенбаху)?​ в вашем примере​ что в таком​
​Function СЧЁТ_РАЗНЫХ(Диапазон As​ ввода формулы не​Подсчет количества уникальных числовых​

​ в массив и​​ по крайней мере​=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16))​: да, но если​’ Post_Author :​ помощью функции​ который не должен​

​ каждому истинному условию.​​ обозначает: три числа​ в конце ввода​=СУММПРОИЗВ(Ч(СЧЁТЕСЛИ(A1:J10;A1:J10)=1))​ЦитатаОлег55 написал: …количество​ должен иметь хотя-бы​Попытки вместо значения​Next​For Each a​Так что это​ добавлением в коллекцию​

planetaexcel.ru

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

​Alex_ST​​’ Post_URL :​. Если представить это​
​=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),»»),ЕСЛИ(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),»»))>0,1))​Функция частота позволяет выполнить​ «moon», одно число​Алексей матевосов (alexm)​ одинаковых можно посчитать,​ есть диапазон B2:B16​ состоять из одного​ <> «» Then​
​Cells(5, 6) =​ a​Hugo​СЧЁТ_РАЗНЫХ_2 — 0,094​ выигрыш в скорости​: Вкладывать worksheetfunction’ы так​
​: Макрос считает правильно.​

​’ DateTime :​​ в виде дополнительного​​Подсчет количества уникальных текстовых​

​ подсчет количества уникальных​​ 5, три числа​: =СУММ (1/МУМНОЖ (—(A1:A5&B1:B5=ТРАНСП​ например (36.46-3; 36.41-5;​Олег55​ столбца и иметь​ .Add «», tmpArr(i,​ UBound(NewMyArray) + 1​Cells(i, 2) =​: У меня есть​ сек.​ при использовании массива​ нельзя.​ Проверялось не раз​ 06.04.10, 12:00​ столбца, то выглядело​ и числовых значений​
​ значений. Эта функция​ 7, три числа​ (A1:A5&B1:B5));{1:1:1:1:1}))​ 36.29-2…)?​: От души большое​ равное кол-во строк​

​ j)​​[CalcTime] = Timer​ a​

​ такие цифры при​​3. Макрос, реализующий​ вместо прямого обращения​Function СЧЁТ_РАЗНЫХ_2(Диапазон As​ (в том числе​’ Purpose :​ бы оно так:​ в диапазоне A2:A10​ игнорирует текстовые и​ 7.​Формула массива, ввод​AlexM​ спасибо! оба варианта​ Dim cl() Dim​к ускорению не​ — Start​i = i + 1​ копировании 3000 уникальных​ на VBA функцию​ к ячейкам будет​ Range) As Long​ даже и ручным​ возвращает число уникальных​Потом вычисляются дроби​ без учета пустых​ нулевые значения. Первое​Все это сводится к​ Ctrl+Shift+Enter​: так?​ великолепные — и​ arrFlag() As Boolean​ привели.​End Sub​Next​ из 11000 макросами:​ листа =СУММПРОИЗВ(1/СЧЁТЕСЛИ(Диапазон;Диапазон))​ незначительным…​’=СУММПРОИЗВ(1/СЧЁТЕСЛИ(Диапазон;Диапазон))​ пересчётом).​ значений в указанном​1/Число вхождений​ ячеек и текстовых​ вхождение конкретное значение​ {1/3;1/1;1/1;1/1;1/3;1/3}.​В формуле массив​Помогите написать формулу, которая​ Сергея, и​ Dim I&, J&​Олег55​Sub filter()​Cells(3, 5) =​Degassad 4.171125​СЧЁТ_РАЗНЫХ_3 — 38,22​

​слэн​​СЧЁТ_РАЗНЫХ_2 = Evaluate(«SumProduct(1/CountIf(«​А вот усовершенствованная​​ диапазоне​

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

​Очень помогли!​​ Dim rngCond() On​
​: Доброе утро, уважаемые​Start! = Timer​​ Timer — Start​​For Each 1.625004​ сек.​: гораздо быстрее​ & Диапазон.Address &​

​ вами формула даже​​’ Notes :​ и все они​
​Примечания:​ равно количество вхождений​ в качестве аргумента​ количество строк, и​

​ двум столбцам кол-во​​Jack Famous​ Error Resume Next​ знатоки Excel!​Range(«A1:A12000»).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Worksheets(1).Range(«B1»),​
​Cells(3, 6) =​

​For i 2.422002​​Да… Гуру форума,​
​Казанский​ «,» & Диапазон.Address​ в исходном примере​’—————————————————————————————​ суммируются, что и​ ​

​ значения. Для каждого​​ для функции​ если строк много,​ уникальных записей. Пример​: Олег55, доброго дня!​

​ cl = rngU.Value​​Пожалуйста, помогите в​ Unique:=True​ Col.Count​Dictionary 1.530991​​ конечно, обычно правы,​​: Алексей, а протестируй​ & «))»)​ топик-стартера (т.е. без​

​Dim iCell As​​ даст нам количество​

​В формулах в этом​​ случая того же​
​SUM​ например 99, то​
​ на рисунке. Спасибо​

​Предлагаю вариант на​​ With CreateObject(«Scripting.Dictionary») For​ ячейке вычислить количество​[CalcTime] = Timer​[CalcTime] = Timer​AdvFilter 0.280985355​ но чтобы разница​ разные методы и​End Function​ пустых ячеек в​ Range​

planetaexcel.ru

Подсчет уникальных значений (Формулы/Formulas)

​ уникальных элементов:​​ примере должны быть​ значения после первого​(СУММ), давая результат​

​ единицы устанешь писать.​​Supreme council​ связях таблиц между​ I = 1​

​ точек сети Магнит,​​ — Start​

​ — Start​​Сами коды:​ во времени выполнения​ отпишись?​слэн​​ диапазоне) даёт 47,​

​Set Диапазон =​​Если в диапазоне встречаются​ введены как формулы​ эта функция возвращает​ 4.​

​ Выход массив единиц​​: 1. Вычисление количества​

excelworld.ru

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

​ собой. Использованы только​ To UBound(cl) ReDim​ по которым хотя​Cells(6, 5) =​End Sub​

​Sub btnDeggasad_Click()​​ достигала более 400​Для тестов создай​
​: а не надо​
​ когда реально и​ Intersect(Диапазон.Parent.UsedRange, Диапазон)​
​ пустые ячейки, то​ массива. Выделите все​ значение 0.​Урок подготовлен для Вас​ делать формулой​
​ уникальных числовых значений​
​ штатные функции Excel.​ arrFlag(Int(UBound(Conditions) / 2))​
​ бы в одной​ Timer — Start​Sub btnCollections2_Click()​
​Dim aOld, aNew,​
​ раз…!!! Не ожидал.​ массив из миллиона​ этой «элегантности» :)​ моим макросом и​
​On Error Resume​ придется немного усовершенствовать​ ячейки, содержащие формулы,​Функция ПОИСКПОЗ используется для​ командой сайта office-guru.ru​=СУММ (1/МУМНОЖ (—(A1:A5&B1:B5=ТРАНСП​=SUM(IF(FREQUENCY(A2:A10;A2:A10)>0;1))​ Результат на листе​ For J =​ товарной накладной начислен​End Sub​Dim NewMyArray(), MyArray​ i As Long,​Так что, формулисты,​

​ случайных чисел в​​в vba эффективнее​ вашей же исходной​
​ Next​ формулу, добавив проверку​ нажмите клавишу F2​ возврата позиции текстового​Источник: http://www.excel-easy.com/examples/count-unique-values.html​
​ (A1:A5&B1:B5));СТРОКА (A1:ИНДЕКС (A:A;СЧЁТЗ​=СУММ (ЕСЛИ (ЧАСТОТА​ «shop» 3ий столбец​
​ LBound(Conditions) To UBound(Conditions)​ бонус (т.е. бонус​Файл не даю,​Dim Col As​ str As String​ бросайте своё тормозное​ диапазоне 1…200 000.​ будет простым перебором,​

​ формулой там 48​​With New Collection​ на пустые ячейки​
​ и нажмите клавиши​ значения в диапазоне.​
​Перевела: Ольга Гелих​ (A:A)))^0))​ (A2:A10;A2:A10)>0;1))​alex1210​ Step 2 If​ >0).​ сильно секретный :)​ New Collection​
​Start! = Timer​ занятие и пишите​Guest​

​ только к вашему​