Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Получить количество уникальных значений
 
#1
24.01.2013 10:28:34
Добрый день!

Подскажите пожалуйста, как справиться со следующим.
Нужно посчитать кол-во уникальных значений в пределах объединенного диапазона ячеек "Дима" и "Вася". Но без привязки к именам самих ячеек (они все для примера). Посчитать следуют уникальные значения столбца "E".
опция типа "Удалить дубликаты" (Office 2007-10) - не подойдет, ибо манипуляций с таблицей проводить не следует.
в приложении сама таблица и пример как должно быть в столбце "J"

можно VBA, можно посредством формул Excel.


с уважением
Изменено: caustic - 24.01.2013 10:30:13
caustic
 
 
 
#2
24.01.2013 10:39:02
в таблице объединенные ячейки разбить можно?
 
 
 
#3
24.01.2013 10:53:47
в первом и втором столбце - можно
остальные - желательно не трогать

хотя по большому счету - можно разбивать все колонки  :)
caustic
 
 
 
#4
24.01.2013 11:05:35
достаточно первые две разбить  :)
смотрите вариант.
 
 
 
#5
24.01.2013 11:11:32
мудрено так. но все работает.
а такой вопрос,

учитывая то, что я принял во внимание то, что вы размножили значения 1ого и 2ого столбца, данную формулу со списком и массивами можно считать универсальной?
т.к. помимо Димы и Васи в таблице могут быть Толи, Миши, Степаны и тд.?

в списках указать не огранченный диапазон а выбрать оба столбца: A:A и B:B
Изменено: caustic - 25.01.2013 00:04:11
caustic
 
 
 
#6
24.01.2013 11:14:13
Цитата
caustic пишет:
не знаю какой таблица будет в длину
Ищем, изучаем - имена, именованные диапазоны, динамические диапазоны; списки/таблицы... ;)
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#7
24.01.2013 11:15:42
Другой вариант - написать UDF
 
 
 
#8
24.01.2013 11:22:38
Цитата
caustic пишет:
можно ли списки сделать не с ограниченным диапазоном
можно. И правильно заполняем таблицу - все ячейки первых двух столбцов должны быть заполнены, если не хотите видеть надписи спрячьте их заливкой как у меня в примере.
UDF проще буде если кто предложит.
П.С. надеюсь у вас в первом столбце повторений не будет.
Изменено: V - 24.01.2013 11:24:10
 
 
 
#9
24.01.2013 12:36:33
В первом приближении (без аргументов. просто по фактической таблице. можно добавить аргументы для указания номеров колонок. например. Да и самого массива) до конца записей в столбце Е. Заполнять все ячейке в А,В необязательно - функция берёт верхнее. Повторения допускаются в любой комбинации - работает словарь он сам проверит
Код
Function Количество_уникальных()Dim R, C1, C2, C3, LrDim K1, K2, K3Dim DDim DRDim T, Tt, WDim M()Dim REZ()Set D = CreateObject("Scripting.Dictionary")R = 1Do While Cells(R, 5) <> EmptyK1 = IIf(Cells(R, 1) = Empty, K1, Cells(R, 1))K2 = IIf(Cells(R, 2) = Empty, K2, Cells(R, 2))K3 = Cells(R, 5).ValueT = K1 & ":" & K2Tt = T & ":" & K3If D.Exists(T) Then    Set W = D.Item(T)    If W.Exists(Tt) Then        W.Item(Tt) = W.Item(Tt) + 1    Else        W.Add Tt, 1    End IfElse    Set DR = CreateObject("Scripting.Dictionary")    DR.Add Tt, 1    D.Add T, DREnd IfR = R + 1LoopM = D.KeysReDim REZ(UBound(M), 2)For R = 0 To UBound(M)REZ(R, 0) = Split(M(R), ":")(0)REZ(R, 1) = Split(M(R), ":")(1)REZ(R, 2) = D.Item(M(R)).CountNext RКоличество_уникальных = REZEnd F unction

Вводить как формулу массива
Изменено: Александр Моторин - 24.01.2013 12:37:17
 
 
 
#10
24.01.2013 12:39:01
Примерно так
 
 
 
#11
24.01.2013 12:40:25
Ну и при желании можно в функции сделать, что бы не повторялись Васи, Димы, как в примере ТС
Изменено: Александр Моторин - 24.01.2013 12:40:48
 
 
 
#12
24.01.2013 13:31:33
Александр Моторин, спасибо, только я не совсем понял, как этот код вводить и куда?
в ячейку? как формулу массива? скопировал, нажал ctrl+shift+enter - ничего не произошло, скопировался только текст
caustic
 
 
 
#13
24.01.2013 13:32:51
по всей видимости - это макрос ) тогда причем тут ввод как формулы массива?. это где применяется?
и VBA не читает код в такой записи, все красным. расставить нужно правильно Enter'ы
Изменено: caustic - 24.01.2013 13:36:04
caustic
 
 
 
#14
24.01.2013 13:40:18
Цитата
V пишет:
достаточно первые две разбить
смотрите вариант.

внедрил ваш вариант в свой рабочий файл.. по всем Именам гигантской таблички высчитал правильно.
но нашелся один, который выводит кол-во уникальных значений 45. хотя воспользовавшись опцией "Удалить дубликаты" - остается 19 значений. что является правдой.
дотошно проверял наличие ошибок при внедрении. ничего не помогло.. 45 пишет.. причем только по одному только человеку (назовем его Олегом)... остальные считает отлично.

навскидку не подскажете может где то что-то нужно скорректировать? или есть какая то особенность при подсчтёте

такое ощущение, что он при проверке дублей (aaa,bbb,ccc, и тд) ваш способ действует так: берет ограниченную область и в ней ищет дубликаты. причем сначала эта область зависит от кол-ва строк с одним именем (Вася, Дима итд), а когда достигается некий предел, часть строчек выбрасывается из этой области, и вновь найденные дубликаты считаются уникальными значениями...  :)  но это только моя догадка

p.s. кол-во строчек у этого Олега - 997, в которых он нашел 45 уникальных строк, а на деле их только 19


Pp.Ss. дублирующие имена (Дима, Вася, Олег) встречаются. но я не думаю, что они на что-то должны влиять, ибо если есть дубли - они идут подряд (не в разнобой)
Изменено: caustic - 24.01.2013 13:58:08
caustic
 
 
 
#15
24.01.2013 14:14:47
Цитата
caustic пишет: но я не думаю, что они
Если не думать, то предполагать никогда не вредно... И чтобы свои мозги не пухли, да и чужые зря не пудрить - не мешает себя самого перепроверять... Вариант, по старттопу - сводная, однако... ;)
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#16
24.01.2013 14:27:49
так не скажу - нужно видеть ваш файл.
Александр Моторин нормально код вставить слабо или файл с кодом приложить.
Z а разные значения "должно получиться" и ваши результаты вас не настораживают?
Изменено: V - 24.01.2013 14:39:09
 
 
 
#17
24.01.2013 14:52:35
все таки где то мой косяк... внедрил таблицу в пример, который вы мне выслали, он показал 19 )
надо разбираться  :)  спасибо еще раз
caustic
 
 
 
#18
24.01.2013 14:58:24
У вас офис какой 2003 или 2007.
 
 
 
#19
24.01.2013 15:00:17
Код вставить не слобо. Вставил, проверил, работает. результаты не насторожили. А почему они должны были насторорожить? код выложил. Написал, что это UDF пользовательская функция.(значит должна быть вставлена в модуль и вызвана как обыкновенная функция. Расписал её достоинства и недостатки. Указал, что должна вводиться как формула массива(не пойму что за упоминание о макросе??). И файл свой сразу удалил - и так забито разным хламом
 
 
 
#20
24.01.2013 15:02:13
Цитата
caustic пишет:
и VBA не читает код в такой записи, все красным. расставить нужно правильно Enter'ы
Ну это претензии к сайту: я вставил код и пометил его как код
 
 
 
#21
24.01.2013 15:03:16
Цитата
V пишет: вас не настораживают?
Меня - нет.
Сводная - для автора, чтобы:
1 - он еще раз мог убедиться в том, что объединенные ячейки - очень большое зло... И, следовательно, имхо
2 - задумался: эачем в таких случаях, в погоне за мнимой красивостью, озадачивать и себя, и планетях подобными "хочухами"... ;)
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#22
24.01.2013 15:21:47
Восстанови код из форума
 
 
 
#23
24.01.2013 15:23:38
У меня Офис-2007.
объясните мне пожалуйста одну вещь. я когда нажимаю на "изменить" список 2 - он мне выделяет колонку E (как при копировании), хотя в формуле и близко нет упоминание этого столбца. причем если удаляешь перед колонкой E столбец, формула в ячейке выдает ошибку.

помимо 3х списков из Диспетчера имен и формулы в табличке:
=СУММ(--(ПОИСКПОЗ(список2;список2;0)=СТРОКА(список3)))

есть ли еще что-то что я упустил?
Изменено: caustic - 25.01.2013 00:06:34
caustic
 
 
 
#24
24.01.2013 15:23:56
Цитата
по всей видимости - это макрос ) тогда причем тут ввод как формулы массива?
А разве на картинке не видно что это не макрос, а именно формула массива?

Для справки:
Function это функция! Не макрос!
Изменено: Александр Моторин - 25.01.2013 00:06:55
 
 
 
#25
24.01.2013 15:36:39
=СУММ(--(ПОИСКПОЗ(ЕСЛИ(ИНДЕКС($G$2:$G$7;ПОИСКПОЗ("яя";$G$2:G2))&H2=ПРОСМОТР(СТРОКА($A$1:$A$40);ЕСЛИ($A$1:$A$40>0;СТРОКА($A$1:$A$40));$A$1:$A$40)&ПРОСМОТР(СТРОКА($A$1:$A$40);ЕСЛИ($B$1:$B$40>0;СТРОКА($A$1:$A$40));$B$1:$B$40);$E$1:$E$40);ЕСЛИ(ИНДЕКС($G$2:$G$7;ПОИСКПОЗ("яя";$G$2:G2))&H2=ПРОСМОТР(СТРОКА($A$1:$A$40);ЕСЛИ($A$1:$A$40>0;СТРОКА($A$1:$A$40));$A$1:$A$40)&ПРОСМОТР(СТРОКА($A$1:$A$40);ЕСЛИ($B$1:$B$40>0;СТРОКА($A$1:$A$40));$B$1:$B$40);$E$1:$E$40);)=СТРОКА($A$1:$A$40)))-1

Формула массива, вводится при помощи комбинации клавиш Ctrl+Shift+Enter.
Изменено: Владимир - 05.10.2014 09:30:08
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#26
24.01.2013 15:40:55
Цитата
caustic пишет:
есть ли еще что-то что я упустил?
Нет. Разберитесь как работает ф-цию СМЕЩ.
в формуле список2 есть число 3 оно и отвечает за переход со столбца "В" в столбец "Е".
Для проверки Заходите в диспетчер имен (Формулы-Диспетчер имен) выделяете первый список , ставите курсор в формулу списка внизу - на листе должен выделиться диапазон в столбце "В" с один и тем же именем, если в него входят другие имена значит у вас таблица не отсортирована (т.е. где то дальше есть такое же имя в несмежном месте) - итог будет неверный.
переходим на список2 - курсор в формулу - выделяется диапазон соответствующий имени и по размеру равный количеству например "1пошел" в столбце "Е" если так и есть все должно считаться правильно. Скорей всего в таблице помарки - лишние пробелы, английские буквы вместо русский и т.д.
Изменено: V - 24.01.2013 15:42:13
 
 
 
#27
24.01.2013 15:41:33
Владимир, спасибо! очень удобно, весь гемор по этой задачке уместился в одну строчку  :D  
хотя виснит при работе с большим кол-вом строк.. жалко
Изменено: caustic - 25.01.2013 00:07:31
caustic
 
 
 
#28
24.01.2013 16:40:52
Можно решить с доп.столбцами.
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
Страницы: 1
Читают тему
Наверх