Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Функция подсчёта уникальных серийных номеров.
 
#1
13.01.2013 03:23:02
Всем планетянам  planetaexcel.ru доброго времени суток :)
Пожалуйста, помогите найти функцию для подсчёта кол-ва уникальных серийных номеров
в итоговой ячейке С12 столбца "С" (С1:С11) ???
Пример прилагаю.
 
 
 
#2
13.01.2013 03:25:51
Формула массива:
{=СУММ(1/СЧЁТЕСЛИ(Таблица1[Серийный номер изделия];Таблица1[Серийный номер изделия]))}
 
 
 
#3
13.01.2013 03:38:23
vikttur,Попробовал подставить формулу в ячейку С12 не работает(((...
Очень хочется именно готовую функцию найти и подставить её в строку итогов.
Вариант с формулами не желателен, хотя к примеру такая формула идеально работает:
=СУММ(ЕСЛИ(ЧАСТОТА(ПОИСКПОЗ(C2:C11;C2:C11;0);ПОИСКПОЗ(C2:C11;C2:C11;0))>0;1))
Повторюсь.. ОООчень хочу именно встроенную функцию, а не формулу...
Надеюсь в excel 2010 есть такая функция...
Изменено: Dmitrij V - 13.01.2013 04:06:40
 
 
 
#4
13.01.2013 08:20:02
Цитата
Dmitrij V пишет:
Надеюсь в excel 2010 есть такая функция...
Надежда умириает последней... в 2010 - нет, однако... Однако, в сводных 2013 можно... ;)
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#5
13.01.2013 10:51:13
Можно свою функцию на VBA написать вместо недостающей стандартной. Например так:
Код
Public Function CountUnique(Rng As Range) As Long

    Dim myCell As Range
    Dim UniqueVals As New Collection
    Application.Volatile
    On Error Resume Next

    For Each myCell In Rng
        If Not IsEmpty(myCell) Then UniqueVals.Add myCell.Value, CStr(myCell.Value)
    Next myCell
    On Error GoTo 0
    CountUnique = UniqueVals.Count
End Function
 
 
 
#6
13.01.2013 12:21:13
Николай Павлов, а Volatile в такой функции зачем?  :oops:  
вариант со словарём
Код
Public Function CountUnique&(Rng As Range)
  Dim myCell As Range, dict As Object
  Set dict = CreateObject("scripting.dictionary")
  For Each myCell In Rng: dict.item(CStr(myCell.Value))=0: Next
  CountUnique = dict.Count
End F unction
Изменено: ikki - 13.01.2013 14:10:52
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#7
13.01.2013 12:46:18
Да не нужна, конечно. Это я кусок из своего старого проекта выдрал "не глядя" :)
 
 
 
#8
13.01.2013 13:02:44
off
а почему в коде Николая нет глюков движка?
ни квадратика перед Value, ни пробела в End Function?
быть может, есть какой-нибудь секрет?
я уж, увидев этот пост, подумал было, что глюк побеждён  :)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#9
13.01.2013 13:04:33
Раз нет такой функции в 2010, значит будем работать с формулами  ;)  
В принципе устраивает устраивал вариант:
=СУММ(ЕСЛИ(ЧАСТОТА(ПОИСКПОЗ(C2:C11;C2:C11;0);ПОИСКПОЗ(C2:C11;C2:C11;0))>0;1))
но!!! заметил очень важную и неприятную особенность (точнее "подводный камень" ;)
Если к примеру фильтром из столбца С убрать с.н. 11142,
Тогда excel 2010 в итоговой ячейке (с нашей формулой) С12 показывает не верное значение кол-ва с.н.
вместо 5 показывает 6.
Возможно так и должно быть, т.к. фильтр всего лишь визуально скрывает с.н. 11142 из столбца С,
и формула все равно видит и подсчитывает скрытый фильтром с.н. 11142 из столбца С.
В моём примере это категорически не допустимо!!!
Т.к. очень важно именно фильтром отсортировать изделия по названию в столбце А и
фильтром отсортировать с.н. в столбце С !!!
И при этом результат в строке итогов (ячейка с формулой С12) должен отображаться правильно!!!
Вопрос: как это сделать???
Может саму структуру таблицы (точнее б.д.) изменить, или формулу подправить???
Или в 2010 фильтр так работает, что не возможно решить мою задачу??? (Надеюсь это не так)...  :)  
Сводную таблицу с отчётом я тоже делаю на втором листе этой же книги.
Но о сводной мы поговорим позже, после того, как с основным вопросом поста разберёмся  8)
Изменено: Dmitrij V - 13.01.2013 13:06:25
 
 
 
#10
13.01.2013 13:06:24
У меня тоже бывает (иногда) без квадратика.
 
 
 
#11
13.01.2013 13:39:12
Цитата
vikttur пишет: Формула массива
vikttur,
Формула массива заработала (не знал, что она вводится через Ctrl+Shift+Enter и после этого автоматически заключается в фигурные скобки {})
Не знаю, что почему и как, но данная формула почему то не правильно подсчитывает уникальные серийные номера в столбце D.
Данная формула одинаковые с.н. считает как уникальные, а это не гуд  :oops:  
Прикрепляю пример (Лист2 ячейка D12).
Пожалуйста, подправьте формулу массива, чтобы она правильно подсчитывала уникальные с.н.
В столбце D вместо не верного результата 6 должен быть верный результат 3
 
 
 
#12
13.01.2013 13:57:49
Посмотрел - верный результат как раз 6. Вы ведь по первой таблице считаете!
 
 
 
#13
13.01.2013 13:58:11
у вас формула не соответствует таблице.
Код
=СУММ(1/СЧЁТЕСЛИ([Серийный номер];[Серийный номер]))
 
 
 
#14
13.01.2013 14:06:45
Цитата
ikki пишет:
а почему в коде Николая нет глюков движка?

Вставлял в режиме bbcode между тегами [CODE] простым копипастом.
 
 
 
#15
13.01.2013 14:53:46
Братья, все три формулы идеально, корректно и правильно подсчитывают уникальные с.н. в столбце D
№1. =СУММ(ЕСЛИ(ЧАСТОТА(ПОИСКПОЗ(C2:C11;C2:C11;0);ПОИСКПОЗ(C2:C11;C2:C11;0))>0;1))
№2. =СУММПРОИЗВ(1/СЧЁТЕСЛИ(D2:D11;D2:D11))
№3. {=СУММ(1/СЧЁТЕСЛИ([Серийный номер];[Серийный номер]))}

Проблема и с уть вопроса в том, что если фильтром мы убираем к примеру один (любой) с.н. из столбца D,
тогда любая из всех 3ёх формул отображает не правильный результат!!!
На прикреплённом примере в рабочей книге ТРИ разных листа со всеми ТРЕМЯ разными формулами,
и на всех 3ёх листах если фильтром убрать один из 4ёх с.н. (11142)
11142 -убрать фильтром
11143 -оставить
11144 -оставить
11145 -оставить
тогда все три разные формулы отображают НЕ верный результат 4 вместо верного результата 3.
Цитата
Dmitrij V пишет:
Возможно так и должно быть, т.к. фильтр всего лишь визуально скрывает с.н. 11142 из столбца D,
и формула все равно видит и подсчитывает скрытый фильтром с.н. 11142 из столбца D.
В моём примере это категорически не допустимо!!!
Т.к. очень важно именно фильтром отсортировать изделия по названию в столбце B и
фильтром отсортировать с.н. в столбце D !!!
И  при этом результат в строке итогов (ячейка с формулой D12) должен отображаться правильно!!!
Вопрос: как это сделать???
Может саму структуру таблицы (точнее б.д.) изменить, или формулу подправить???
Или в 2010 фильтр так работает, что не возможно решить мою задачу???
Вопрос: помогите, пожалуйста решить эту проблему.
Нужно, чтобы результат после фильтра отображался ПРАВИЛЬНО!!!
Т.е. 3, а не 4 !!!
 
 
 
#16
13.01.2013 15:19:27
Цитата
Dmitrij V пишет:
Нужно, чтобы результат после фильтра отображался ПРАВИЛЬНО!!!
Вам не кажется, что если бы такое было возможно, то давным-давно кто-нибудь да воплотил это в жизнь?!.
Вы считаете правильным то, что вам необходимо получить, и "давите" на планетян -"Доворачивайте!!!"... Хотя, по умолчанию, на форумах никто никому ничего не должен...
Может вам пора подумать о повороте в решении вопроса через последовательные этапы?..
Вы упомянули о сводной - предостерегу: с ней (если не 2013) с уникальными будет о-о-чень большая заморочка... Дежурный вариант - сводная по сводной... Удачи!..
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#17
13.01.2013 15:56:29
Ну UDF то можно повернуть как угодно, например так доработать уже выше написанное ikki :

Код
Public Function CountUnique(Rng As Range)
  Dim myCell As Range, dict As Object
  Set dict = CreateObject("scripting.dictionary")
  For Each myCell In Rng
  If myCell.Rows.Height Then dict.Item(CStr(myCell.Value)) = 0&
  Next
  CountUnique = dict.Count
End F unction
Изменено: Hugo - 13.01.2013 16:00:09
 
 
 
#18
13.01.2013 16:32:45
Можно еще дописать перебор ячеек только в пределах использованного диапазона, чтобы функция могла эффективно работать с целыми строками и столбцами:
Код
Public Function CountUnique(Rng As Range)
  Dim myCell As Range, r As Range
  Set r = Intersect(Rng, Rng.Worksheet.UsedRange)
  If r Is Nothing Then Exit Function
  With CreateObject("scripting.dictionary")
    For Each myCell In r
      If myCell.Rows.Height Then .Item(CStr(myCell.Value)) = 0&
    Next
    CountUnique = .Count
  End With
End F unction

Например, посчитать уникальные в ст. А и С:
Код
=CountUnique((A:A;C:C))
Изменено: Казанский - 13.01.2013 16:38:56
 
 
 
#19
13.01.2013 17:06:48
Цитата
Dmitrij V пишет:
Нужно, чтобы результат после фильтра отображался ПРАВИЛЬНО!!!
Код
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($D$1;СТРОКА($D$2:$D$11)-1;))*(ПОИСКПОЗ($D$2:$D$11;$D$2:$D$11;0)=(СТРОКА($D$2:$D$11)-1)))
или массив
Код
=СУММ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($D$1;СТРОКА($D$2:$D$11)-1;))*(ПОИСКПОЗ($D$2:$D$11;$D$2:$D$11;0)=(СТРОКА($D$2:$D$11)-1)))
 
 
 
#20
13.01.2013 17:14:53
Зы. Если в столбце возможны пустые ячейки, то
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($D$1;СТРОКА($D$2:$D$11)-1;))*(ПОИСКПОЗ($D$2:$D$11&"";$D$2:$D$11&"";0)=(СТРОКА($D$2:$D$11)-1)))
или массив
Код
=СУММ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($D$1;СТРОКА($D$2:$D$11)-1;))*(ПОИСКПОЗ($D$2:$D$11&"";$D$2:$D$11&"";0)=(СТРОКА($D$2:$D$11)-1)))
 
 
 
#21
13.01.2013 18:07:41
Цитата
Михаил С. пишет:
Если в столбце возможны пустые ячейки, то
Михаил С., Вы очень правильно и верно это подметили!!!
Именно так было есть и будет, и пустые ячейки в столбце с.н. будут, т.к. не всегда с.н. есть.
Михаил С., Вам отдельная благодарность, земной поклон, мегареспект и безграничная человеческая благодарность за гениальное, великолепное, изящное, простое и идеальное решение достаточно сложного, и как многие люди думали (в том числе и я) НЕ выполнимого задания!!!
Честно скажу, надежда у меня была минимальная на то, что в табличном редакторе (точнее в Excel 2010) вообще такое возможно!!!
В 2010 получить в ячейках итоговой строки б.д. ПРАВИЛЬНЫЕ значения уникальных данных столбца ПОСЛЕ ФИЛЬТРА...
От шока, восторга и экстаза долго в себя придти не смогу...

:!: Админам и модераторам имхо пример Ув. Михаил С., обязательно в копилку полезных идей добавить нужно!!! :!:
 
 
 
Страницы: 1
Наверх