Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Формула для вывода дубликатов и не дубликатов столбца
 
#1
31.10.2023 20:06:28

Доброго здоровья всем!

Просьба написать две формулы: для вывода дубликатов т.е значений которые встречаются 2 или более раз, и формула для вывода значений, которые встречаются 1 раз, желательно без использования новых функции, чаще пользуюсь старым Excel 2002.

И еще одна просьба вот прошу написать формулы, т.к. это у меня получается не очень. В принципе хотел бы сам лучше научиться писать, но как – то совсем плохо это получается. Макрос для меня проще. Обычно алгоритм и даже не один в голове появляется, а по формулам ничего. Ну первую функцию еще выберу, а дальше обычно ступор. Может посоветуете как научится, что где прочитать. Может у кого-то есть книга в электронном виде, прочитав которую будет лучше получаться. Наверняка те, кто хорошо пишет формулы, читали такую книгу. По макросам у меня есть пара книг ну и несколько справок, в которые иногда заглядываю, плюс есть несколько сайтов, где можно что - то посмотреть, а по формулам ничего нет.
 
 
 
#2
31.10.2023 20:21:13
2002 - это для Мака?
Вообще если знаете макросы - то тут проще свою UDF написать и не ломать голову, на древней версии...
Но как на Маке с этим в деталях не скажу, но коллекции работают.
Но тут проще юзать словарь, с чем на Маке проблема.
Изменено: Hugo - 31.10.2023 20:22:19
 
 
 
#3
31.10.2023 20:26:25
Hugo Здравствуйте Да  UDF сам могу сделать, но хотел просто формулу.
Цитата
2002 - это для Мака?
Нет Эксель 2002. У меня и Эксель 2010, но еще не совсем привык, иногда мне проще в 2002 к ленте не могу привыкнуть до конца.
 
 
 
#4
31.10.2023 20:44:06
Доброго. Смотрите файл.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#5
31.10.2023 20:52:31
Максим В. Здравствуйте. Я такую трехэтажную никогда бы не написал. Спасибо.
 
 
 
#6
31.10.2023 20:55:32
Ну и UDF, два варианта, раз уж написал...
Первый массивный на весь диапазон, второй для одной ячейки.
Можно использовать оба для обоих вариантов - последний аргумент указывает что выводить.
 
 
 
#7
31.10.2023 21:15:11
Hugo Спасибо. Сегодня уже поздно пора спать. Завтра разберусь с кодом, что там интересного есть.
 
 
 
#8
31.10.2023 21:39:06
Понизим этажность :-)
Автономно и массивно
Дубликаты
=IFERROR(INDEX($A$2:$A$16;MATCH(0;COUNTIF($G$1:G1;$A$2:$A$16)+ISNUMBER(MATCH($A$2:$A$16;IFERROR(INDEX(A:A;N(INDEX(SMALL(IF(COUNTIF($A$2:$A$16;$A$2:$A$16)=1;ROW($A$2:$A$16));TRANSPOSE(ROW($A$2:$A$16))-1);)));"");));));"")
Не дубликаты
=IFERROR(INDEX(A:A;SMALL(IF(COUNTIF($A$2:$A$16;$A$2:$A$16)=1;ROW($A$2:$A$16));ROWS($H$2:H2)));"")

А если использовтаь не дубликаты для дубликтов, то
дубликаты
=IFERROR(INDEX($A$2:$A$16;MATCH(0;COUNTIF($G$1:G1;$A$2:$A$16)+COUNTIF(H:H;$A$2:$A$16);));"")
По вопросам из тем форума, личку не читаю.
 
 
 
#9
31.10.2023 22:16:01
Я там в коде чуть лишнего понаписал (хотел это использовать, но не стал) - можно не собирать строку из индексов, а просто считать повторы, и основываясь на этом удалять с 1 или с >1.
Поправил в этом файле.
Изменено: Hugo - 31.10.2023 22:21:49
 
 
 
#10
01.11.2023 06:41:18

БМВ Доброе утро. Вы как всегда на высоте. Класс!!!

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

Спасибо
 
 
 
#11
01.11.2023 07:28:00
Евгений Смирнов, Не читал ни одной книги, возможно зря, ибо многолетний опыт который сейчас могу демонстрировать - накоплен реально за много лет, а возможно , если б прочел, то познал бы многое быстрее, но не факт. Все зависит от потребностей. А в целом, если присмотреться, то большинство решений основано на очень ограниченном количестве способов и трюков. Их сочетания дают нужный результат. При этом многие становятся не нужны с появлением новых функций.
Ну и конечно нужно иногда от обратного отойти. Иной раз посчитать сумму по условию сложнее чем получить тот же результат за счет разницы всего за минусом инверсивного количества, для которого условие проще. Это я как раз к этой теме отношу. перевернули последовательность получения результата с не дубликатов в дубликаты и все упростилось и там и там.
Сложная формула пишется "наизнанку", обраcтая слоями, Ну есть те, которые уже заведомо понятны и вложенность функций очевидна, а зачастую наоборот. Так что читать книги или просто практиковаться - это кому как лучше. Мне лучше практиковаться.
По вопросам из тем форума, личку не читаю.
 
 
 
#12
01.11.2023 08:56:45
БМВ  Появился ответ на второй вопрос. Вывод: Предложение дружить семьями отклонено. :D

В принципе я понимаю, что главное практика. Без практики теория это ни о чем. Вопрос только во времени которое понадобится для освоения знаний. Теория с практикой сокращает время. В предыдущей своей теме про расширенный фильтр файл, с которого начал изучение VBA. Тогда не было ни инета нормального ни сайтов, где можно было что – то посмотреть. Хорошо, что когда был в городе попалась книга Уокенбах_Excel2003.ПрофПрограммированиеVBA. Без нее ничего бы не получилось. На 10-15 строчек кода ушло наверно месяц свободного от работы времени, видимо и для формул тоже надо много времени потратить. Вот вы пишите, что большинство решений основано на очень ограниченном количестве способов и трюков. Жаль, что нет книги, где бы это было описано с конкретными примерами. Наверняка при помощи такой книги, время для изучения понадобиться гораздо меньше.

Благодарю за ответ.
 
 
 
#13
01.11.2023 09:13:39
И вам доброго Евгений Смирнов,
вариант
"Дубликаты" { }:
=ИНДЕКС(A$2:A$16;НАИМЕНЬШИЙ(ЕСЛИ((СЧЁТЕСЛИ(A$2:A$16;A$2:A$16)>1)*ПОИСКПОЗ(A$2:A$16;A$2:A$16;)=СТРОКА(A$2:A$16)-1;СТРОКА(A$2:A$16)-1);СТРОКА(A1)))
Если поменять ">1" на "=1" то получатся "НеДубликаты"
или
=ИНДЕКС(A$2:A$16;НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСКПОЗ(СТРОКА(A$2:A$16)-1;(СЧЁТЕСЛИ(A$2:A$16;A$2:A$16)>1)*ПОИСКПОЗ(A$2:A$16;A$2:A$16;); );"");СТРОКА(A1)))
Цитата
на вторую просьбу
F1 , "обрывочные знания" из интернета и его величество Эксперимент  - это я про себя   )
 
 
 
#14
01.11.2023 09:49:06

Павел \Ʌ/ И вам доброе утро.

Очень хороший вариант. Вы не вложили, в ЕСЛИОШИБКА, но это не проблема, кстати В 2002 функции ЕСЛИОШИБКА нет, там только ЕОШИБКА и ее применение в 2 раза увеличит кол-во символов. И еще наверно в сообщении надо было указать, что формулы массивные. А то кто-нибудь из начинающих будет говорить, что не работают.

Огромное спасибо за интересный вариант и полный ответ.
 
 
 
#15
01.11.2023 11:11:35

Hugo  Доброе утро.

Сначала разбирался с формулами, т.к. гораздо хуже их понимаю, чем VBA. Сразу не мог понять, зачем 2 варианта, пока не могу до конца осмыслить, но разберусь это не проблема (UDF совсем мало писал, в основном процедуры). У вас наверняка опыта больше в написании кода. Один небольшой вопрос у меня есть.

Вот объявили переменную t как String. В этой строке не может быть иногда ошибки или на скорости это как - то отражается. Или в VBA это не принципиально, он сам преобразует тип данных.

Код
t = r(i, 1)

или лучше написать так

Код
t = CStr(r(i, 1))

И еще от аргумента ind во 2 варианте невозможно избавиться?

Изменено: Евгений Смирнов - 01.11.2023 14:32:32
 
 
 
#16
02.11.2023 07:46:45
Цитата
Евгений Смирнов написал:
В 2002 функции ЕСЛИОШИБКА нет, там только ЕОШИБКА и ее применение в 2 раза увеличит кол-во символов.
В таких случаях можно отсекать не по ошибке а по заранее просчитанному количеству. То есть просчитать количество заранее и исходя из номера строки выводить их. То есть количество не дубликатов
=SUM(--(COUNTIF($A$2:$A$16;$A$2:$A$16)=1))
с дубликатами чуть сложенее
=SUM(--((COUNTIF($A$2:$A$16;$A$2:$A$16)>1)*(MATCH(A2:A16;A2:A16;)+1=ROW(A2:A16)))) но это короче чем всю формулу проверять на ошибку

=IF(ROWS($K$2:K2)>SUM(--(COUNTIF($A$2:$A$16;$A$2:$A$16)=1));"";INDEX(A:A;SMALL(IF(COUNTIF($A$2:$A$16;$A$2:$A$16)=1;ROW($A$2:$A$16));ROWS($H$2:H2))))
По вопросам из тем форума, личку не читаю.
 
 
 
#17
02.11.2023 08:32:44
БМВ
Цитата
В таких случаях можно отсекать не по ошибке а по заранее просчитанному количеству.
Понял спасибо за пояснения.
 
 
 
Страницы: 1
Наверх