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

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

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

Начиная с 2007-й версии функция удаления дубликатов является стандартной — найти ее можно на вкладке Данные — Удаление дубликатов (Data — Remove Duplicates):

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

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

Если у вас Excel 2003 или старше, то для удаления дубликатов и вытаскивания из списка уникальных (неповторяющихся) элементов можно использовать Расширенный фильтр (Advanced Filter) из меню (вкладки) Данные (Data).

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

Выбираем в меню Данные — Фильтр — Расширенный фильтр (Data — Filter — Advanced Filter). Получаем окно:

В нем:

• Выделяем наш список компаний в Исходный диапазон (List Range).
• Ставим переключатель в положение Скопировать результат в другое место (Copy to another location) и указываем пустую ячейку.
• Включаем (самое главное!) флажок Только уникальные записи(Uniqe records only) и жмем ОК.

Получите список без дубликатов:

Если требуется искать дубликаты не по одному, а по нескольким столбцам, то можно предварительно склеить их в один, сделав, своего рода, составной ключ с помощью функции СЦЕПИТЬ (CONCATENATE):

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

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

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

Итак, снова имеем список беспорядочно повторяющихся элементов. Например, такой:

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

=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;»»)

В английской версии это будет:

=IF(COUNTIF(B$1:B2;B2)=1;MAX(A$1:A1)+1;»»)

Эта формула проверяет сколько раз текущее наименование уже встречалось в списке (считая с начала), и если это количество =1, т.е. элемент встретился первый раз — дает ему последовательно возрастающий номер.

Для упрощения адресации дадим нашим диапазонам (например, исходя из того, что в списке может быть до 100 элементов) имена. Это можно сделать в новых версиях Excel на вкладке Формулы — Диспетчер имен (Formulas — Name manager) или в старых версиях — через меню Вставка — Имя — Присвоить (Insert — Name — Define):

• диапазону номеров (A1:A100) — имя NameCount
• всему списку с номерами (A1:B100) — имя NameList

Теперь осталось выбрать из списка NameList все элементы имеющие номер — это и будут наши уникальные представители. Сделать это можно в любой пустой ячейке соседних столбцов, введя туда вот такую формулу с известной функцией ВПР (VLOOKUP) и скопировав ее вниз на весь столбец:

=ЕСЛИ(МАКС(NameCount)<СТРОКА(1:1);»»;ВПР(СТРОКА(1:1);NameList;2))

или в английской версии Excel:

=IF(MAX(NameCount)

Эта формула проходит сверху вниз по столбцу NameCount и выводит все позиции списка с номерами в отдельную таблицу:

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

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

Хитрости »

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

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

Представим себе большой список различных наименований, ФИО, табельных номеров и т.п. А необходимо из этого списка оставить список все тех же наименований, но чтобы они не повторялись — т.е. удалить из этого списка все дублирующие записи. Как это иначе называют: создать список уникальных элементов, список неповторяющихся, без дубликатов. Для этого существует несколько способов: встроенными средствами 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
ссылки
статистика

Числовые значения

В диапазоне

= СУММ(—(ЧАСТОТА(данные; данные)>0))

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

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

ID номера работников появляются в диапазоне B5: B14. Для того, чтобы получить количество уникальных номеров, вы можете использовать следующую формулу:

= СУММ(— (ЧАСТОТА(B5:B14; B5:B14) > 0))

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

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

{3; 0; 0; 2; 0; 3; 0; 0; 2; 0; 0}

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

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

Теперь каждая ИСТИНА в списке представляет собой уникальный номер в списке, и нам просто нужно сложить Истинные значения с СУММ.

Однако СУММ не будет добавлять логические значения в массиве, так что мы должны сначала превратить значения в 1 или ноль. Это делается с помощью двойного минуса (двойной унарный). В результате массив только 1 или 0.

{1; 0; 0; 1; 0; 1; 0; 0; 1; 0; 0}

И, наконец, СУММ добавляет эти значения и рассчитывает общее число, которое в данном случае равно 4.

Примечание: вы можете также использовать СУММПРОИЗВ для сложения элементов в массиве.

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

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

С критериями

{=СУММ(—(ЧАСТОТА(ЕСЛИ(criteria;значения);значения)>0))}

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

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

В показанном примере, формула в G4 является:

{= СУММ(— (ЧАСТОТА(ЕСЛИ(B5:B14 = «A»; C5:C14); C5:C14)> 0))}

Примечание: это формула массива и должна быть введена с помощью сочетания клавиш Ctrl+Shift+Enter

Функция ЧАСТОТА рассчитывает массив значений, которые соответствуют массиву_интервалов. В этом случае мы подставлем «отфильтрованный» набор идентификаторов для массива данных, а также полный набор идентификаторов для массива_интервалов. Фильтрация осуществляется с помощью функции ЕСЛИ:

ЕСЛИ (B5:B14 = «А»; С5:С14)

Которая в примере рассчитывает это:

{81400; 81405; 81405; 82364; 82364; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}

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

{1; 2; 0; 2; 0; 0; 0; 0; 0; 0; 0}

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

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

Каждая ИСТИНА в списке представляет собой уникальный номер в списке, и нам просто нужно сложить Истинные значения с СУММ.

Однако СУММ не будет добавлять логические значения в массиве, так что мы должны сначала превратить значения в 1 или ноль. Это делается с помощью двойного минуса (двойной унарный). В результате массиве только 1 или 0.

{1; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0}

И, наконец, СУММ добавляет эти значения и рассчитывает общее число, которое в данном случае составляет 3.

Текстовые значения

В диапазоне

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

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

Кроме того, можно использовать СЧЁТЕСЛИ, как описано ниже.

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

Имена работников находятся в диапазоне B5:B14. Для того, чтобы получить количество уникальных имен, вы можете использовать следующую формулу:

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

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

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

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

{1; 1; 1; 4; 4; 6; 6; 6; 9; 9}

Аргумент, хранимый в массиве строится из этой части формулы:

СТРОКА(B5:B14) -СТРОКА(B5) +1

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

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10}

Функция ЧАСТОТА возвращает массив значений, которые соответствуют «хранимым». В этом случае мы подставляем массив, рассчитываемый ПОИСКПОЗ для массива данных, а также рассчитываемый массив построчного массива выше, как массив хранимых.

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

{3; 0; 0; 2; 0; 3; 0; 0; 2; 0; 0}

Далее, каждое из этих значений преобразуется в значение ИСТИНА или ЛОЖЬ, которые > 0, а затем в 1 или ноль с двойным отрицанием (двойной минус). Это делается потому что СУММПРОИЗВ нужны числовые значения, она не может работать непосредственно с текстом или логическими значениями.

Внутри функции СУММПРОИЗВ конечный массив выглядит следующим образом:

{1; 0; 0; 1; 0; 1; 0; 0; 1; 0; 0}

И, наконец, СУММПРОИЗВ просто добавляет эти значения и рассчитывает общее число, которое в данном случае равно 4.

Обработка пустых ячеек в диапазоне

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

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

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

Рассмотрим формулу изнутри, ЕСЛИ требуется, потому что ПОИСКПОЗ рассчитает # N / A, если значение совпадений содержит пустые значения. Тестируя для пустых значений с данными <> «», в том числе и ПОИСКПОЗ как значение, если оно истинно, то результирующий массив будет содержать номера в сочетании с ЛОЖЬ:

{1; 1; ЛОЖЬ; 4; 4; 6; 6; ЛОЖЬ; 9; 9}

которая подается ЧАСТОТА, что и массив данных. ЧАСТОТА будет рассчитывать массив вроде этого:

{2; 0; 0; 2; 0; 2; 0; 0; 2; 0; 0}

Элементы в этом массиве преобразуются либо в 1, или ЛОЖЬ с окончательным (внешним) ЕСЛИ заявлением. Результат выглядит следующим образом:

{1; ЛОЖЬ; ЛОЖЬ; 1; ЛОЖЬ; 1; ЛОЖЬ; ЛОЖЬ; 1; ЛОЖЬ; ЛОЖЬ}

СУММ затем складывает 1 и рассчитывает 4.

Эта формула из замечательной книги Майка Гивина по формулам массива.

Использование СЧЁТЕСЛИ вместо ЧАСТОТЫ для подсчета уникальных значений

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

С критериями

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

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

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

В показанном примере формула в G5 является:

= {СУММ(—(ЧАСТОТА(ЕСЛИ(C5:C11 = G4;СТРОКА(B5:B11; B5:B11;0));СТРОКА(B5:B11) — СТРОКА(B5)+1) > 0))}

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

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

С помощью ЕСЛИ ПОИСКПОЗ рассчитывает только для строк, которые соответствуют критериям.

В конце концов, массив позиций, генерируемых ПОИСКПОЗ подаются к частотному в аргументе массива данных.

Аргумент, хранимый в массиве, строится из этой части формулы:

СТРОКА(B5:B11) -СТРОКА(B5) +1

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

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10}

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

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

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

Наконец, СУММПРОИЗВ просто складывает эти значения и рассчитывает общее число.

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

Обработка пустых ячеек в диапазоне

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

{= СУММ(— (ЧАСТОТА(ЕСЛИ(B5:B11 <> «»; ЕСЛИ(C5:C11 = G4; ПОИСКПОЗ(B5:B11; B5:B11;0))); СТРОКА(B5:B11) -СТРОКА(B5) +1)> 0))}

С двумя критериями

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

= {СУММ(— (ЧАСТОТА(ЕСЛИ(c1; ЕСЛИ(c2; ПОИСКПОЗ (значения;значения;0))); СТРОКА (значения) — СТРОКА (значения.первая позиция) +1)> 0))}

Там, где c1 = критерий1, c2 = критерий2 и значения = диапазон значений.

Взято из замечательной книги Майка Гивина по формулам массива, Control-Shift-Enter.

В диапазоне с СЧЁТЕСЛИ

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(данные;данные))

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

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

{3; 3; 3; 2; 2; 3; 3; 3; 2; 2}.

После того, как СЧЁТЕСЛИ заканчивает, результаты используются в качестве делителя, 1 в качестве числителя. Значения, которые появляются в данных, после появляются в массиве как 1, но значения, которые появляются несколько раз будут отображаться как дробные значения, которые соответствуют множеству. (Т.е. значение, которое появляется в 5 раз, данные будут генерировать 5 элементов в массиве со значением 1/5 = 0,2).

Наконец, функция СУММПРОИЗВ суммирует все значения в массиве и рассчитывает результат.

Обработка пустых ячеек

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

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(данные; данные&»»))

Данные &»» выражение предотвращает нули массива, созданного СЧЁТЕСЛИ, когда имеются пустые ячейки данных. Она делает это путем обеспечения того, что критерии для пустой ячейки «», а не ноль. Это важно, так как ноль в делителе выбросит #ДЕЛ/0. Так что эта версия формулы не будет выдавать ошибку, когда есть пустые ячейки, но она будет включать в себя пустые ячейки в счете. Если вы хотите исключить пустые ячейки в счете, используйте:

=СУММПРОИЗВ((данные <> «»)/СЧЁТЕСЛИ(данные;данные и «»))

Низкая производительность?

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