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

Max Petruk

Гость

27.10.2009 16:49:48

Использую вот такую формулу:
=СУММПРОИЗВ((ЧАСТОТА(ПОИСКПОЗ(B7:B3000&"";B7:B3000&"";0);ПОИСКПОЗ(B7:B3000&"";B7:B3000&"";0))>0)*ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ(B7;СТРОКА(B7:B3001)-СТРОКА(B7);)))

То есть в диапазоне B7:B3000 считаю сколько уникальных значений имеется, не учитывая скрытые автофильтром строки.

Не могу понять почему часто показывает не правельный подсчет, как правило меньше на единицу при определенных условиях фильтрации. Или наглядный пример:
Если отфильтровать столбец H по значению TI-MPSUKR5509032503ETH-022 то подсчет уникальных значений в столбике В покажет число 66 на самом деле там их 69 и все уникальные. В тоже время в столбике F формула показивает значение 1, а там два уникальных значения.

Подскажите где ошибка как исправить?

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

post_71990.rar (60.78 КБ)

lapink2000

Пользователь

Сообщений: 2186 Регистрация: 01.01.2013

27.10.2009 17:34:57

В вашей формуле логическая ошибка: вы вычисляете уникальность и видимость независимо друг от друга, а затем накладываете результаты. Т.е. значение может впервые появиться в скрытой строке и повторяться в видимых строках но у вас оно незачтется.
Попробуйте такую формулу массива (ввод через {CTRL+SHIFT+ENTER}) заодно и ускоренную вдвое, что немаловажно, учитывае летучесть формул, их обилие, и размеры используемых диапазонов:

{=СУММ(--(ЧАСТОТА(ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ(B7;СТРОКА(B7:B3000)-СТРОКА(B7);))>0;ПОИСКПОЗ(B7:B3000;B7:B3000;0));СТРОКА(B7:B3000)-СТРОКА(B7)+1)>0))}

Guest

Гость

27.10.2009 17:55:11

{quote}{login=KL}{date=27.10.2009 05:34}{thema=}{post}В вашей формуле логическая ошибка: вы вычисляете уникальность и видимость независимо друг от друга, а затем накладываете результаты. Т.е. значение может впервые появиться в скрытой строке и повторяться в видимых строках но у вас оно незачтется.
Попробуйте такую формулу массива (ввод через {CTRL+SHIFT+ENTER}) заодно и ускоренную вдвое, что немаловажно, учитывае летучесть формул, их обилие, и размеры используемых диапазонов:

{=СУММ(--(ЧАСТОТА(ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ(B7;СТРОКА(B7:B3000)-СТРОКА(B7);))>0;ПОИСКПОЗ(B7:B3000;B7:B3000;0));СТРОКА(B7:B3000)-СТРОКА(B7)+1)>0))}{/post}{/quote}

KL, огромное Вам спасибо за такой точный ответ! Даже не ожидал - благодарю, удачи Вам! :)

Саша

Пользователь

Сообщений: 607 Регистрация: 16.01.2013

27.10.2009 18:07:52

С позволения KL, в его формуле не нужна первая проверка>0, т к промежуточные итоги(3;x) будут возвращать только 0 и 1 в любом случае и, соответственно, Если уже будет получать требуемые значения.

А вообще, очень красиво :)

Guest Гость	#5 29.10.2009 10:16:36 а вот так: Function pritun(ddd As Range) As Long i = 0 For Each s In ddd i = i + 1 nrow = s.Row If Rows(nrow).Hidden = False Then For j = 1 To i - 1 If s.Value = ddd(j) Then GoTo line Next j pritun = pritun + 1 End If line: Next s End Function

lapink2000 Пользователь Сообщений: 2186 Регистрация: 01.01.2013	#6 29.10.2009 13:33:33 Вы бы хоть попробовали свой код ;-) 1) он в примерно 50.000 раз медленнее формулы 2) и при этом считает неверно KL

Guest Гость	#7 29.10.2009 15:53:07 {quote}{login=KL}{date=29.10.2009 01:33}{thema=}{post}Вы бы хоть попробовали свой код ;-) 1) он в примерно 50.000 раз медленнее формулы 2) и при этом считает неверно{/post}{/quote} 1 я пробовал на 30 строках - было хорошо (и правильно) после Вашего поста попробовал на 6000 - стало очень плохо и неправильно 2 переписываю

Читают тему