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


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

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

count-unique1.png

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

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

Способ 1. Формула массива

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

count-unique2.png

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

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

count-unique4.png

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

count-unique5.png

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

count-unique3.png

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

=SUM(IF(ISEMPTY(A2:A10);0;1/COUNTIF(A2:A10;A2:A10)))

Ну, и наконец, если не охота заморачиваться с сочетанием клавиш Ctrl+Shift+Enter, то можно заменить функцию СУММ на аналогичную по смыслу (в данном случае) функцию СУММПРОИЗВ (SUMPRODUCT), которая понимает формулы массива "из коробки", т.е. при вводе обычным образом с помощью только Enter:

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

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

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

Способ 2. Удаление дубликатов

Это хоть и рукопашный, но вполне рабочий вариант, если нужно быстро и единоразово оценить количество уникальных значений. Выделив исходные данные, идём на вкладку Данные - Удалить дубликаты (Data - Remove Duplicates). В открывшемся окне помечаем флажком столбцы, по которым нужно удалить повторы и после нажатия на ОК получаем сообщение с количеством оставшихся уникальных значений:


Просто, но минус в том, что при изменении исходных данных придётся повторять весь процесс.

Способ 3. Сводная таблица

Если построить по исходной таблице сводную, то можно поместить поле, по которому мы хотим подсчитать количество дубликатов, в область строк или столбцов и затем простой функцией СЧЁТЗ (COUNTA) посчитать количество значений в полученном списке (вычтя 2 лишних ячейки на заголовок и итоги):

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

Ещё красивее будет, если построить не простую сводную, а сводную по Модели Данных, включив в окне построения сводной соответствующий флажок через Вставка - Сводная таблица - Добавить эти данные в модель данных (Insert - Pivot Table - Add this data to Data Model). В этом случае можно закинуть искомый столбец уже в область значений и переключить функцию расчёта поля в подсчёт количества уникальных, щёлкнув по полю правой кнопкой мыши и выбрав команду Итоги по - Количество различных значений (Summarize Values By - Count Distinct):

Количество уникальных в сводной по Модели Данных

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

Способ 4. Новая функция УНИК

В последних версиях, а именно начиная с Excel 2021 и в Excel 365, появилась поддержка динамических массивов и несколько новых мощных функций для работы с ними. Одна из этих функций УНИК (UNIQUE) - выводит массив уникальных значений из исходного списка. Если завернуть её снаружи в функцию подсчёта количества заполненных ячеек СЧЁТЗ (COUNTA), то получим простой и быстрый способ расчёта количества уникальных для таблицы любого размера:

Новая функция УНИК

Способ 5. Макрофункция на VBA

Если последней версии Excel у вас пока нет, а функцию УНИК иметь очень хочется, то вполне можно написать её аналог на VBA. Для этого выберите на вкладке Разработчик (Developer) команду Visual Basic или нажмите сочетание клавиш Alt+F11, чтобы открыть окно редактора макросов, вставьте туда новый модуль через меню Insert - Module и введите в него код следующей функции:

Function DistinctCount(dataRange As Range)

    Dim coll As New Collection
    Dim cell As Range
    
    'если возникла ошибка при добавлении в коллекцию (т.е. добавляем дубль), то идём дальше
    On Error Resume Next
    
    'перебираем ячейки в исходном диапазоне
    For Each cell In dataRange
        'если ячейка не пустая, то пытаемся добавить её в коллекцию
        If Not IsEmpty(cell) Then coll.Add cell.Value, CStr(cell.Value)
    Next cell

    DistinctCount = coll.Count

End Function

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

Использовать созданную функцию очень легко - она ничем не отличается от любой другой встроенной функции Excel:

Макрофункция на VBA для подсчета количества уникальных значений

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

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

 



MCH
09.10.2013 12:54:52
Вариант 2, для 2-го способа, немассивно:
=СУММПРОИЗВ(СЧЁТЕСЛИ(A2:A10;A2:A10)^(2*ЕПУСТО(A2:A10)-1))
09.10.2013 13:41:15
Класс! Отличный способ!
24.06.2015 10:13:35
Доброго времени суток, могли бы вы, для новичка, разобрать данную формулу, начиная со знака крышки.
=СУММПРОИЗВ(СЧЁТЕСЛИ(A2:A10;A2:A10)^(2*ЕПУСТО(A2:A10)-1))
11.02.2019 10:14:58
Спасибо, Ваш пример =СУММПРОИЗВ... работает даже при вводе вручную в пустую ячейку
11.10.2013 20:12:03
Для полного диапазона:

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

Для диапазона с пустыми ячейками:

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A10;A2:A10&""))-1
30.06.2014 12:35:06
Неудобство в том, что диапазон должен обязательно содержать пустые значения, чтобы "-1" было в тему... Всё-таки метод от MCH наиболее универсален, не говоря уже о красоте.
14.01.2014 14:25:30
Всем доброго времени суток! Можно ли усовершенствовать формулу так, чтобы считать количество уникальных элементов в отфильтрованных строках (при использовании автофильтра)? Что-то вроде ПромежуточныхИтогов, где будут считаться количество уникальных.
16.08.2014 10:17:20
Не уверен, что это можно реализовать формулой. Я бы писал макрофункцию на VBA в таком случае.
01.10.2014 18:33:03
Я сделал так.
1) В отдельной колонке вывел результат 1/Число вхождений, как на последней картинке в Способ 1
2) На эту колонку наложил функцию =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;колонка)
В результате, когда применяю фильтр, получаю суммарное количество уникальных значений.

Недостаток один - если фильтр наложить по другим полям, получим неверное отображение количества уникальных значений.
01.07.2016 16:33:18
Огромное спасибо. Ваш способ именно то, что мне пригодилось!
13.08.2014 22:30:35
Как посчитать уникальных значений из списка деталей по длине и ширине,когда длина и ширина в разных ячейках.
16.08.2014 10:16:07
Склеить длину и ширину в один столбец с помощью функции СЦЕПИТЬ (CONCATENATE) и дальше подсчитать уникальные, как описано в статье выше.
24.09.2014 22:52:00
А мне еще нужно посчитать кол-во  уникальных значений не просто в одном столбце, а ещё по двум критериям в таблице. Это реально? Можно ли как-то соединить с СЧЁТЕСЛИМН?
27.09.2014 09:37:25
См. выше. Я бы склеил все критерии в отдельный столбец с помощью функции СЦЕПИТЬ и искал уникальные потом по этой колонке. Хотя не видя примера детально ответить сложно.
07.11.2014 13:06:35
Буквально вчера бился с этой задачей, но вроде бы победил :) Допустим, что в указанном примере в столбце "B" стоит требуемый нам дополнительный параметр, например "цвет". Тогда формула будет выглядеть следующим образом:

=СУММ(ЕСЛИОШИБКА(1/СЧЁТЕСЛИМН(A2:A10;A2:A10;B2:B10;"Красный");0))
12.01.2021 15:28:52
У меня данная формула выдает неправильный результат.
Вот что нашел на соседнем сайте. Эта формула у меня работет.

=СУММПРОИЗВ((A13:A21=A7)/СЧЁТЕСЛИМН(B13:B21;B13:B21;A13:A21;A13:A21))
15.01.2015 13:31:59
подсчет по двум критериям в таблице можно посмотреть в последнем файле этой темы: http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=8&TID=30488&MID=265827#message265827
08.10.2014 12:38:37
А мне еще нужно посчитать кол-во уникальных значений не просто в одном столбце, а ещё по двум критериям в таблице. Это реально? Можно ли как-то соединить с СЧЁТЕСЛИМН?
 

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

Например Таблица содержит наименования продуктов и даты их выпуска )) Подскажите.. )8)
27.10.2014 20:27:24
Добрый день. А есть ли возможность вставить подсчет уникальных данных в сводную таблицу? Например в вычисляемое поле?
08.11.2014 16:11:26
Вычисляемые поля в сводных таблицах весьма ограничены по возможностям - формулу массива или сложную функцию туда точно не вставить. В новом Excel 2013 подсчет уникальных значений добавлен в стандартный набор вычислений по полю, кстати.
Здравствуйте! Просьба помочь с решением такой задачи: нужно по каждой дате подсчитать количество машин (по гос. номеру). №№ ТТН пренебречь.
Дат в 2014 году было много, машин - тоже.....
Заранее спасибо!
Гос.№ авто№ ТТНДата
Т 240РВ403826/1201.03.2014
Т 240РВ403826/1201.03.2014
Т 240РВ403826/1201.03.2014
С 215ВН403825/1404.03.2014
С 215ВН403825/1404.03.2014
С 215ВН403825/1404.03.2014
С 215ВН403825/1404.03.2014
С 215ВН403825/1404.03.2014
С 215ВН403825/1404.03.2014
С 215ВН403825/1404.03.2014
С 215ВН403825/1404.03.2014
С 215ВН403825/1404.03.2014
С 215ВН403825/1404.03.2014
Т 118ЕЕ14/2-206.03.2014
Т 118ЕЕ14/2-206.03.2014
14.02.2015 11:30:28
Андрей, если у вас Excel 2013, то можно использовать сводную таблицу - она в 2013 версии умеет подсчитывать количество уникальных элементов.
Если версия более древняя, то прогнать список сначала через удаление дубликатов (Данные - Удалить дубликаты) с галочками Гос.№ и Дата, а потом построить сводную (Даты закинуть в строки, а Гос№ в значения).
Николай, спасибо за ответ!
Остался момент: в сводной таблице не получается найти кнопку "Уникальные значения". У меня 2013 эксель.
16.02.2015 14:26:45
Андрей, когда строите сводную, то обязательно установите флажок Добавить в модель данных.
Потом в сводной щелкаете правой по полю значений - Параметры поля - Операции - Число различных элементов.
Флажок ставлю. А "Кол-ва уникальных" не вижу в настройках поля данных....
16.02.2015 19:08:42
Оно там на вкладке Операции - правильно называется Число различных элементов (Distinct Values)
Гос. № машины заменил на число - и заработало! а смешанный формат Гос.№  не дает появиться пункту "Число различных элементов". Есть рецепт как решить эту проблему? В смысле - без выборки 3 цифр гос. номера. В случае их уникальности...
21.08.2023 12:21:56
Николай ,благодарю Вас ,помогло с решением моей задачи!!!
либо....формула?

Гос.№ автоДатаКол-во датКол-во машин
Т 240РВ01.03.20141
Т 240РВ01.03.2014
Т 240РВ01.03.2014
Т 240РВ01.03.20141
С 215ВН04.03.20142
С 215ВН04.03.2014
С 215ВН04.03.2014
С 215ВН04.03.2014
С 2155ВН04.03.2014
С 215ВН04.03.2014
С 215ВН04.03.2014
С 215ВН04.03.2014
С 215ВН04.03.2014
С 215ВН04.03.20141
Т 118ЕЕ06.03.20143
Т 118ЕЕ06.03.2014
Т 118ЕЕ06.03.2014
О 754ТМ06.03.2014
О 754ТМ06.03.2014
О 754ТМ06.03.2014
О 754ТМ06.03.20141
В 093ЕЕ07.03.20144
В 093ЕЕ07.03.2014
В 093ЕЕ07.03.2014
В 093ЕЕ07.03.2014
В 093ЕЕ07.03.2014
В 093ЕЕ07.03.2014
В 093ЕЕ07.03.2014
В 093ЕЕ07.03.2014
В 093ЕЕ07.03.2014
В 093ЕЕ07.03.20141
{=ЕСЛИ(G4<>"";СЧЁТЕСЛИ(ИНДЕКС(A1:A$2;ПОИСКПОЗ("ххх";G1:G$2)):A1;"<>"&A1:A$2);"";)}
Кол-во дат считает верно, а кол-во машин в день - неверно...


{=ЕСЛИ(G4<>"";СЧЁТЕСЛИ(ИНДЕКС(A1:A$2;ПОИСКПОЗ("ххх";G1:G$2)):A1;"<>"&A1:A$2);"")}
Прошу помощи!!!!!!!!!!!!!!!!!!!!!!!!
Камрады! Хелп!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Андрей, я бы сделала так: делаем из этой таблицы сводную. В строки ставим поле "Дата" - получаем список всех возможных дат, копируем его на отдельный лист. Добавляем в сводной в строки поле "Гос.№ авто" , обязательно в "макете отчета" - "Повторять все подписи элементов" (работает с 2010-го). И с помощью СЧЁТЕСЛИ подтягиваем к списку дат количество машин. Это вариант без дополнительных условий. Если нужно учесть еще критерии (например, количество машин с отгрузкой не менее определенной суммы) - СЧЁТЕСЛИМН меня всегда выручает.
29.11.2015 16:02:11
Здравствуйте.

Подскажите пожалуйста, как решить задачку. У меня к сожалению не получается самостоятельно.
Есть несколько строк из 10 чисел.
Нужно посчитать сколько раз одновременно в строку входят 2 или 3 других числа.

Заранее спасибо.
03.12.2015 19:05:29
Здравствуйте, подскажите, есть ли какие-либо ограничения по кол-ву строк или версии Офиса?
У меня на 35 тыс. строк (Офис 2013) не  работает, выдает "0". При этом, ограничив в этом же массиве до 1500 строк, считает корректно :(
Ребят, а как просчитать значения которые повторяются, чтобы указывалось не общее количество, а порядочное, нп:

Имя          Подсчет
Груша             1
Груша             2
Груша             3
Груша             4

Вместо такого, что выдает формула "счетесли"

Имя          Подсчет
Груша             4
Груша             4
Груша             4
Груша             4
15.07.2016 21:55:31
Нужно у аргумета "диапазон" функции СЧЁТЕСЛИ строку нижней границу указать без доллара. И будет тебе счастье…
15.07.2016 22:06:36
Есть ещё один замечательный способ, как можно посчитать уникальные значения. С помощью функции ЧАСТОТА:

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

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


{=СУММ(--(ЧАСТОТА(ЕСЛИ(B2:B10=E1;ПОИСКПОЗ(A2:A10;A2:A10;0));СТРОКА(A2:A10)-СТРОКА(B2)+1)>0))}
(в ячейке E1 указываем условие с названием магазина)
Max
27.10.2021 14:04:02
К сожалению,
  • Функция ЧАСТОТА пропускает пустые ячейки и текст.
16.01.2023 07:12:04
Добрый день. Замечательная формула. А как в ней можно изменить Условие (в вашем случае название магазина). А у меня товар с разным заполнением номенклатуры но есть одинаковое слово (например: шоколад Аленка, шоколад клубничный, горький шоколад с апельсином...). обычно я выношу в отдельную ячейку *шоколад* и ссылаюсь на эту ячейку, как на условие и формула считает. А в данной формуле как это можно реализовать? Каталог товара большой и мне необходимо посчитать по каждому магазину, по каждому виду товара, в какое количество чеков этот товар попал. И возможно ли в формулу добавить еще один критерий? условие-Магазин, условие-группа товаров, счет - количество чеков в которые эта группа товаров попала (причем если в один чек попали товары из одинаковой группы, то считал бы как один) в конкретном магазине.    
16.01.2023 08:09:09
а возможно ли реализовать через суммеслимн, но в диапазон суммирования ка-то поставить =сумм(1/счетесли(диапазон; диапазон)
16.01.2023 23:58:43
Чтобы сравнить с нестрогим совпадением текста, вместо "B2:B10=E1" вы можете написать "ЕСЛИОШИБКА(ПОИСК(E1;B2:B10);0)>0".

Чтобы добавить ещё один критерий, напишите условия в скобках, разделённые знаком звёздочки: (условие1)*(условие2).
Например, так: "(ЕСЛИОШИБКА(ПОИСК(E1;B2:B10);0)>0) * (ЕСЛИОШИБКА(ПОИСК(E2;C2:C10);0)>0)"
Так же можно обработать ошибку, возникающую при пустых значениях в искомом поле - добавив условие …*(A2:A10<>"")
17.01.2023 02:51:05
Спасибо большое. Все получилось
19.07.2016 19:59:16
В продолжение темы, ещё один вариант подсчёта уникальных значений.

Сначала сортируем диапазон A1:A10 по возрастанию (или по убыванию, не важно). Потом вводим такую формулу массива:
{=СУММ(ЕСЛИ(A2:A10<>A1:A9;1;0))}

В этом варианте преимущество в том, что расчёт происходит очень быстро, даже если в исходнике много тысяч строк.
19.07.2016 20:49:02
С пропуском пустых ячеек и в немассивной форме:
=СУММПРОИЗВ((A2:A10<>A1:A9)*(A2:A10<>"")
29.10.2016 19:54:17
Хотел задать вопрос, но сам разобрался. Всем удачи!
05.12.2017 16:41:10
Всем привет! Подскажите как посчитать уникальные(повторяющиеся) значения в столбце таблицы, при использовании промежуточных итогов?
15.02.2020 03:30:27
Здравствуйте, уважаемые коллеги, помогите пожалуйста написать формулу для подсчёта числа уникальных названий городов с ненулевой транспортной активностью по каждому округу из табицы:

Округ отпр.Город отпр.Округ приб.Город приб.Число рейсов
ЦентральныйМосква< --- >ЦентральныйРязань0
ЦентральныйМосква< --- >ЦентральныйТверь0
ЦентральныйМосква< --- >ЦентральныйБалашиха1
ЦентральныйМосква< --- >ЮжныйКраснодар3
ЦентральныйМосква< --- >УральскийТюмень1
ЦентральныйРязань< --- >СибирскийНовосибирск22
СибирскийНовосибирск< --- >ЮжныйКраснодар1
УральскийТюмень< --- >СибирскийНовосибирск12
УральскийТюмень< --- >ЮжныйКраснодар2
УральскийТюмень< --- >ЮжныйСочи11
СибирскийНовосибирск< --- >ЦентральныйТверь2
ЮжныйКраснодар< --- >ЦентральныйРязань11

Должно получиться что-то типа такого:

ОкругЧисло узлов
Центральный5
Сибирский1
Уральский1
Южный2
Какие-то города могут быть только в части "отправление", какие-то - в "прибытии", а какие-то в обоих частях, и у некоторых могут быть нулевое число рейсов (если у найденного города везде ноль в числе рейсов - то он не считается, а если хоть в одном месте не ноль - то считается).

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


P.S.: Мысль посетила - может натолкнёт на идею: можно обнулять результаты счёта города до тех пор, пока не найден город с ненулевым числом рейсов. Для этого можно перемножать логическую 1 ("город найден";) на 1-1*(0 если есть хоть один ненулевой рейс, иначе 1).
Тогда если все рейсы нулевые, то найденный город помножится на 1-1*1 = 0 и не посчитается. Иначе помножится на 1-1*0 = 1.
27.10.2020 13:34:31
Нашел еще одно решение через функцию сведения данных "Число различных объектов", которая появляется только при использовании модели данных в Excel Сведение данных в сводной таблице
10.01.2021 09:49:02
Помогла решить задучу вот эта формула

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

но не до конца. В моей таблице содержаться пустые ячейки.
Подскажите как можно это реализовать?
Спасибо заранее
10.01.2021 11:28:22
вопрос закрыт
Не подтягивает числовое значение в желтую ячейку. С форматами играться пробовал - не помогло. Использовал формулу
=((СУММПРОИЗВ(СЧЁТЕСЛИ(B6:B35;B6:B35)^(2*ЕПУСТО(B6:B35)-1)))+0)

Далее не работает формула со ссылкой на эту ячейку
=(ВПР(B5;$D$5:$E$19;2;0))
Как можно исправить ситуацию?
18.11.2021 13:13:24
15.04.2022 10:48:40
Добрый день!
Прошу помощи.
Моя формула выглядит так:
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(B2:B200;B2:B200&""))-ЕСЛИ(СЧЁТЕСЛИ(B2:B200;"*доп*");СЧЁТЕСЛИ(B2:B200;"*доп*");0)-1
Цель: подсчитать в списке кол-во уникальных значений, за вычетом значений с припиской "доп".
Проблема: формула уменьшает значение на "1" если приписка "доп" дублируется 2 и более раз, то есть если в столбце есть "доп123" и "доп1234" хочу чтобы вычитала 2, а если в столбце "доп123" и "доп123" хочу чтобы вычитала 1.
712307
724490
663597
725282
724126
726106
доп681529
2доп681529
В данном случае решил проблему изменением дубля, но хотелось бы поправить на уровне формулы.
Заранее спасибо!
15.04.2022 11:06:16
Вижу это так:
(общее количество значений-дубли-доп)+дубли доп
(общее количество значений-доп)-дубли кроме доп
Это реально?
23.05.2022 11:20:37
Спасибо, формула для столбцов без пустых ячеек работает. Буду сейчас пробовать с пустыми. Удачи мне)
02.07.2022 20:04:46
Коллеги, а как быть, если в диапазоне больше пустых значенений, чем остальных и в ответе выйдет пустое значение?
Как прописать формулу, чтобы игнорировались пустые ячейки?
03.01.2023 14:23:59
Доброго времени.
Прошу помощи. У меня есть огромная таблица. Мне нужно посчитать количество уникальных значений за определенную дату.
Формулы из статьи применить не смог. Удалять дубли стандартно нельзя.
13202131.12.2022Не получена
13202202.01.2023Аннулирована
13202302.01.2023Не получена
13202402.01.2023Не получена
13202502.01.2023Не получена
13202602.01.2023Не получена
13202702.01.2023Не получена
13202802.01.2023Не получена
13202902.01.2023Не получена
13203002.01.2023Не получена
13203102.01.2023Не получена
13203202.01.2023Не получена
13203302.01.2023Не получена
13203402.01.2023Не получена
13203502.01.2023Не получена
13203502.01.2023Не получена
13203602.01.2023Не получена
13203602.01.2023Не получена
13203602.01.2023Не получена
13203602.01.2023Не получена
13203702.01.2023Не получена
13203702.01.2023Не получена
13203802.01.2023Не получена
13203802.01.2023Не получена
13203802.01.2023Не получена
В формуле должен быть критерий/условие типа СЕГОДНЯ()-1.
02.02.2023 19:54:51
ABCD
113202131.12.2022Не получена132022
213202202.01.2023Аннулирована132023
313202302.01.2023Не получена132024
413202402.01.2023Не получена132025
513202502.01.2023Не получена132026
613202602.01.2023Не получена132027
713202702.01.2023Не получена132028
813202802.01.2023Не получена132029
913202902.01.2023Не получена132030
1013203002.01.2023Не получена132031
1113203102.01.2023Не получена132032
1213203202.01.2023Не получена132033
1313203302.01.2023Не получена132034
1413203402.01.2023Не получена132036
1513203506.01.2023Не получена132037
1613203502.01.2023Не получена132038
1713203602.01.2023Не получена
1813203602.01.2023Не получена
1913203602.01.2023Не получена
2013203602.01.2023Не получена
2113203702.01.2023Не получена
2213203702.01.2023Не получена
2313203802.01.2023Не получена
2413203802.01.2023Не получена
2513203802.01.2023Не получена
Если даты будут последовательны (т.е. таблица отсортирована по датам) то можно вот так вывести в 1 столбец все уникальные значения по дате (02.01.2023 которая в ячейке - $F$3)

Формула в ячейке D1

=ЕСЛИОШИБКА(ИНДЕКС($A$1:$A$100;АГРЕГАТ(15;6;(СТРОКА($A$1:$A$100)-СТРОКА($A$1)+1)/((ПОИСКПОЗ($A$1:$A$100;$A$1:$A$100;0)=СТРОКА($A$1:$A$100)-СТРОКА($A$1)+1)*(=ЕСЛИОШИБКА(ИНДЕКС($A$1:$A$100;АГРЕГАТ(15;6;(СТРОКА($A$1:$A$100)-СТРОКА($A$1)+1)/((ПОИСКПОЗ($A$1:$A$100;$A$1:$A$100;0)=СТРОКА($A$1:$A$100)-СТРОКА($A$1)+1)*($F$3=B$1:B$100)); ЧСТРОК($A$1:$A2)));"")=B$1:B$100)); ЧСТРОК($A$1:$A2)));"")
[CODE][/CODE]
18.02.2023 20:34:46
Подсчет уникальных в сводной отличная идея, но к сожалению она работает только когда Вы добавляете таблицу в Модель данных и строите сводную по модели. Минусом этого способа является то, что в сводной отключается возможность "ручной" группировки строк.
     Для решения этой проблемы я создал дополнительный столбец в таблице и присвоил каждому уникальному значению 1, а остальным дублям 0. Таким образом при суммировании в сводной мы сможем увидеть только сумму уникальных. Ответ был кстати получен с помощью chatgpt. Сам я до него не додумался и на форумах ответ не нашел. А эта нейросетка сгенерила ответ за 2 мин.

=ЕСЛИ(ЕСЛИ(СЧЁТЕСЛИ($A$2:A2;A2)>1;0;A2)=0;0;1)

В итоге можно построить сводную таблицу и сделать сумму по новому столбцу. Сумма будет считать только количество уникальных.  
27.02.2023 08:54:50
Применил "Способ 1. Если нет пустых ячеек" точь в точь, но не работает. Почему не знаете?
28.06.2023 09:38:12
Еще один с пустыми ячейками:

{=СЧЁТ(ЕСЛИ(ПОИСКПОЗ(A2:A10;A2:A10)>0;1;0))}
22.11.2023 18:01:18
Добрый день!
Прошу помощи знатоков! Может через VBA как-то можно это сделать.
Имеется - файл Эксель, 98 тысяч строк на каждом листе, а листов не более 20 шт.
Весит этот файл порядка 0,5 ГБ. Автовычисление выключено.
Задача: на каждом листе удалить повторяющиеся строки. Вручную не вариант, так как повторяющихся строк 106 шт из 98к строк. Необходимо чтобы из этих 106 осталось 53, и остальные неповторяющиеся значения.
Счетесли не работает, за 20 минут 0%
Подсвечивание дубликатов не работает - файл вылетает.
Сводная таблица не вариант.
22.02.2024 10:55:28
Добрый день!
Нужна помощь знатоков:
Можно ли эту формулу обработать через Power Query?
Потому что, другие данные уже обрабатываются через него и в полученной таблице если проставляешь дальше массив =ЕСЛИ(ЕПУСТО([НомерИнтернетЗаказа] );0;1/СЧЁТЕСЛИ([НомерИнтернетЗаказа];[НомерИнтернетЗаказа] ) (так как имеются дубли заказов). При количестве строк под 80 тыс, Эксель и весь комп зависает на 20-60 мин только из-за одного этого столбца, а без него обрабатывается и обновляет данные за считанные секунды
Столбец необходим для дальнейшего вывода в отчет, создавать сводную не вариант, так как требуется настраиваемый через формулы отчет
22.02.2024 16:01:33
+ консолидация (помню раньше на эту же тему эту же функцию вспоминал :D)
26.03.2024 15:28:51
Подскажите, можно ли использовать созданную макрофункцию на VBA при работе со сводными таблицами? Подсчет числа уникальных значений зачастую нужен именно на уровне группировки.
16.10.2024 13:35:18
Уважаемый Николай Владимирович!
Приведите пожалуйста, данные (в сек/миллисек), сколько времени заняло отработка массива 100к каждым из 5-ти способов.
Заранее благодарю, "Pavel A."
25.11.2024 15:05:16
IDСтатусДатаЧас
100true15.10.202412
10116.10.202412
105true16.10.202412
105true16.10.202412
105true16.10.202412
105false16.10.202413
100true22.11.202413
10122.11.202413
10222.11.202413
Добрый день! Есть таблица с указанными полями, интересует количество уникальных строк со статусом true за месяц. Нужна формула в одной ячейке посчитать кол-во уникальных строк таблицы со статусом true, например, в октябре. Ответ формулы если за октябрь был бы 2. Т.е. строка
100true15.10.202412
и строка
105true16.10.202412
26.11.2024 16:56:31
Если у вас версия Excel 2021 или новее, то потребуется функция ФИЛЬТР, чтобы отобрать строки с true, а затем функция УНИК, чтобы оставить в отфильтрованном только дубликаты.
27.11.2024 13:11:32
=СУММ(--(ДЛСТР(УНИК(ФИЛЬТР(массив;(Дата=01.10.2024)*(Статус="true");"")))>0))/2

Таким образом находит только за дату 01.10.2024, а как найти в диапазоне дат при помощи ФИЛЬТР, что бы вместо Дата=01.10.2024 использовались условия Дата>=01.10.2024 И Дата<01.11.2024?
Наверх