Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Как пронумеровать заново уникальные значения.
 
#1
29.12.2012 12:47:27
Добрый день, всех с наступающими праздниками и долгожданным отдыхом.
Не могу пронумеровать Уникальные значения, с нумерацией вида 1_2_3.
Есть данные вида
клиент 1 кл Андрей
клиент 1 кл Андрей
клиент 1 кл Ваня
клиент 1      кл      Миша
Тут есть и уникальные значения, и повторяющиеся. Необходимо пронумеровать уникальные значения по возрастания 1 2 3. Но эта нумерация каждый раз должна начинаться заново. Если-же значение повторяется, то надо возвратить уже присвоенный номер, или оставить ячейку пустой.
Через СЧЕТЕСЛИ смог сделать сквозную нумерацию 1 2 3 4 5 6 и т.д.
Через СУММПРОИЗВ(--(A2&B2&C2=$A$2:A2&$B$2:B2&$C$2:C2)) получается самый близкий результат. Но ошибка в следующем:
Клиент 1 кл Андрей 1 (правильно)
Клиент 1 кл Андрей 2 (неправильно, присваивает номер 2, а нужен 1)
Прошу Вашей помощи, сам решаю уже 1,5 дней.
 
 
 
#2
29.12.2012 13:12:59
Где-нибудь в сторонке по отдельности создайте три отсортированных уникальных списка по каждой из трех колонок. В каждом из них пронумеруйте элементы. И потом подтяните номера этих элементов к первоначальному списку и сделайте конкатенацию трёх значений в каждой строке.
 
 
 
#3
29.12.2012 13:48:42
Как-то так
 
 
 
#4
06.01.2013 23:14:49
Добрый день, спасибо за помощь.
To Gustav, спасибо за совет. Ваш способ, применим, когда мы окончательно закончили работу со списком, т.е. новых данных в него не добавляется. Когда-же систематически, происходит добавление данных, то такой способ, немного не «динамичен»
Михаил спасибо формула работает, но понять, как работает, с первого раза сложно, но очень интересно.
Попробую разложить по частям
СЧИТАТЬПУСТОТЫ с этим понятно. Если ячейки пустые, то «ничего не делаем», если не пустые то происходит проверка следующего логического условия.
ЕСЛИ(ПОИСКПОЗ(A2&B2&C2&D2;$A$1:A2&$B$1:B2&$C$1:C2&$D$1 :D 2;0)=ЧСТРОК($B$1:B2)
Здесь ПОИСКПОЗ ищет номер позиции в диапазоне, и возвращает числовое значение, ЧСТРОК также возвращает числовое значение, Соответственно сравнивается число от ПОИСПОЗ и число от ЧСТРОК.
Если сравниваемые числа равны то присваивается номер МАКС(ЕСЛИ($A$1:A1=A2;$F$1:F1))+1
Если числа не равны то значит номер уже есть и находит его ИНДЕКС($F$1:F2;ПОИСКПОЗ(A3&B3&C3&D3;$A$1:A3&$B$1:B3&$C$1:C3&$D$1 :D 3;0)) А вот тут непонятно, ведь с помощью ИНДЕКС задается диапазон столбца F, но в ПОИСКПОЗ столбец F вообще не участвует.
Вот вроде разложил по частям, после этого попробовал изменить условия задачи        

ID      Наименование       Контакт       Тип_Контакта       имя
1      клиент 1      111@gmail.com e-mail Андрей      
1      клиент 1      223344       телефон Андрей
1      клиент 1      222@gmail.com e-mail       Ваня
1      клиент 1      111@gmail.com e-mail       Миша

Здесь исключил пустые строки. Для поиска используются только столбцы ID, Наименование, Контакт, Тип Контакта, Столбец имя не используется. Хочу пронумеровать e-mail, т.е. если e-mail у ID уникальный то присваиваем ему номер (1,2,3), если номер уже присвоен, то возвращаем ранее присвоенный номер. Для этого в формуле изменил вот эту часть A2&B2&C2&"e-mail". Т.К пустот нет, то СЧИТАТЬПУСТОТЫ тоже исключил. Измененная формула имеет следующий вид:
{=ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ(A9&B9&C9&"e-mail";$A$1:A9&$B$1:B9&$C$1:C9&$D$1 :D 9;0)=ЧСТРОК($B$1:B9);МАКС(ЕСЛИ($A$1:A8=A9;$G$1:G 8) )+1;ИНДЕКС($G$1:G8;ПОИСКПОЗ(A9&B9&C9&"e-mail";$A$1:A9&$B$1:B9&$C$1:C9&$D$1 :D 9;0)));0)}
Вроде работает правильно, однако прошу специалистов проверить, все ли учел, есть ли скрытые недостатки формулы. Спасибо!
 
 
 
#5
06.01.2013 23:42:08
Почему бы не использовать UDF на словаре или коллекции (если строк не особенно много) или макрос?
 
 
 
#6
06.01.2013 23:53:21
Сам макросы и UDF еще не пишу, пытаюсь учится.
 
 
 
#7
06.01.2013 23:56:54
Я думаю UDF написать проще, чем такую формулу. Ну и макрос аналогичный аналогично.
Только я не вполне понял, по каким условиям нужно нумеровать?
 
 
 
#8
07.01.2013 00:11:26
Ну эту формулу первично сделал Михаил С, я уже переработал как смог.
Критерии такие:
Если у клиента 1 ("ID 1", у каждого клиента свой уникальный ID) есть e-mail, и он встречается первый раз, присваиваем ему номер 1, далее у клиента 1 есть второй e-mail то присваиваем номер 2, далее у клиента 1 повторяется e-mail, которому уже присвоили номер, то необходимо "вернуть" тот номер который присвоен ранее.
Далее у Клиента 2 нумерация повторяется 1-2-3 и т.д., то есть нумерация не сквозная 1,2,3,4,5,6, А у Каждого ID, каждый раз нумерация e-mail повторяется заново.
 
 
 
#9
07.01.2013 00:35:16
я хотел попробовать написать формулу без использования функции массива. Скажите, у Вас 2010 офис? Просто тогда можно использовать функцию ЕСЛИОШИБКА, что прилично укоротит формулу.
 
 
 
#10
07.01.2013 00:48:45
На одной работе 2007, на другой 2010, но насколько я знаю ЕСЛИОШИБКА, есть начиная с 2007?
 
 
 
#11
07.01.2013 01:04:54
Да, простите, просто я семёркой не пользовался.
Прочитав задание, подумал что речь об одном столбце, но посмотрев файл, понял, что Вам ещё принципиально значение клиент1 или клиент2.
Написал в столбце G формулу только для данных исключительно из D. Посмотрите, м.б. подойдёт что-нибудь.
Удачи!
 
 
 
#12
07.01.2013 01:09:20
Если вдруг заинтересует, то поправьте, пжл, кусок формулы
МАКС($E$2:E2)
на
МАКС($G$2:G2)
 
 
 
#13
07.01.2013 01:24:13
Вариант UDF - словари в словаре.
 
 
 
#14
07.01.2013 02:00:17
Не массивная
Код
=ЕСЛИ(D2<>"e-mail";"";ЕСЛИ(ПОИСКПОЗ(C2;$C$2:C2;0)=СТРОКА(A1);СУММПРОИЗВ(($B$2:B2=B2)*($D$2:D2="e-mail"));ИНДЕКС($H$1:H1;ПОИСКПОЗ(C2;$C$1:C2;0))))
зы. Исходим из того, что у разных клиентов разные e-mail.
Изменено: Михаил С. - 30.12.2013 00:36:44
 
 
 
#15
07.01.2013 02:33:24
В моём варианте почта у клиентов может повторяться - у каждого ID свой словарь почт.
 
 
 
#16
07.01.2013 03:40:48
Нет, решение через СУМПРОИЗВ() некорректно... формула массива
Код
=ЕСЛИ(D2<>"e-mail";"";ЕСЛИ(ПОИСКПОЗ(C2;$C$2:C2;0)=СТРОКА(A1);МАКС(ЕСЛИ($A$1:A1=A2;$G$1:G1))+1;ИНДЕКС($H$1:H1;ПОИСКПОЗ(C2;$C$1:C2;0))))
 
 
 
#17
07.01.2013 04:00:25
Iba2004, конечно заинтересует, любой способ пойдет в копилку.
Михаил С и Hugo, по поводу может ли одна почта повторится у разных клиентов. Вопрос хороший, согласно алгоритму работы, то нет не может, но ведь возможна человеческая ошибка. Хотя может быть и не ошибка, например один клиент выступает под разной организационно правовой формой, и как ИП и как ООО, притом что почта у него одна, в общем еще раз проверю рабочие таблицы и напишу. Спасибо Вам.
 
 
 
#18
07.01.2013 04:05:28
Михаил С, но ведь, в тестовом примере нумерация совпала.

Да, некоретно, уже разоборался.
Изменено: Kladez - 30.12.2013 00:37:43 (Поспешил написать первое сообщение.)
 
 
 
#19
07.01.2013 04:08:47
Случайно.  Подставьте формулу с СУММПРИЗ() в последний пример...
 
 
 
#20
07.01.2013 04:13:26
Написал, точнее изменил пост выше, наверное одновременно писали.
 
 
 
#21
07.01.2013 04:36:46
Цитата
Kladez пишет:
например один клиент выступает под разной организационно правовой формой, и как ИП и как ООО, притом что почта у него одна
Поскольку все равно формула массива, то можно учесть и ИД:
Код
=ЕСЛИ(D2<>"e-mail";"";ЕСЛИ(ПОИСКПОЗ(C2&A2;$C$1:C2 & $A$1:A2;0)=СТРОКА(A2);МАКС(ЕСЛИ($A$1:A1=A2;$H$1:H1))+1;ИНДЕКС($H$1:H1;ПОИСКПОЗ(C2;$C$1:C2;0))))
 
 
 
#22
07.01.2013 10:11:15
Сколько всего строк в таблице? От этого тоже может зависеть выбор решения - бывает, что вариант формулами просто не может обработать большое количество (т.е. может минут за 10 например...)
 
 
 
#23
29.12.2013 12:55:12
HUGO, ответ затянулся почти на год, но именно теперь вопрос про количество строк стал актуален.
Сейчас в таблице около 600 строк вариант с формулами, и вариант с UDF, по скорости работают одинаково. По крайней мере глазу не заметна разница в скорости.
В ближайшее время возможно резкое увеличение строк, до 1,5 тысяч. Вопрос такой, при каком количестве строк, нужно с формул переходит на UDF или макросы?
 
 
 
#24
29.12.2013 13:31:40
Kladez, а вы на время работы макроса пересчёт формул отключаете? Или сравниваете время в двух независимых книгах (в одной - формулы, в другой - только макросы). А то ведь если всё в одной книге - то тут возможны такие коллизии...  :)
В любом случае, при увеличение количества исходных данных - макросы обычно быстрее. Особенно, если оптимизировать код или алгоритм, не говоря уже об изменении структуры данных.
 
 
 
#25
29.12.2013 13:48:12
Andre TM, пересчет не отключаю. Сравниваю в одной книге, время так-же специально не фиксирую, смотрю на "глазок" Спасибо за совет, теперь попробую в разных книгах.
 
 
 
#26
13.01.2014 00:58:31
Сделал два массивных варианта по спецзаказу. Раз сделал - пускай тут тоже полежат :)
 
 
 
#27
14.01.2014 22:34:53
Hugo, спасибо.
Не видел сообщение на форуме.
Вам на почту отписался.
 
 
 
#28
19.01.2014 16:27:15
Продолжение темы здесь
 
 
 
Страницы: 1
Наверх