Сравнить значения в дублях
zavokzalka
Дата: Понедельник, 03.08.2020, 21:17 |
Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация:
0
±
Замечаний:
0% ±
Excel 2019
Здравствуйте! Прошу помощи в решении задачи. Вводные данные: около 1млн строк. 2 столбца. В первом слолбике - телефон, во втором модель приставки. На одном телефоне, построчно может быть от 1 до 3 приставок в различных комбинациях моделей (все одинаковые, все разные, две одной модели, 1 другой и т.п.) Моделей приставок может быть три. Мне нужно: 1. из столбца 1 найти дубли телефонов, т.е. чтобы строк телефонов с одним номером было больше 1 (до трех можеть быть, т.е. искать 2 или 3 дубля одного телефона; 2. по этому списку найти, чтобы было 2 модели на одном телефон (две указанных модели из трех всего возможных моделей). Во вложении примерно набросал что хочется видеть. Желтым виделил что нужно найти. Список желательно сделать отдельным. Но можно и подсветить каким-либо образом.
Здравствуйте! Прошу помощи в решении задачи. Вводные данные: около 1млн строк. 2 столбца. В первом слолбике - телефон, во втором модель приставки. На одном телефоне, построчно может быть от 1 до 3 приставок в различных комбинациях моделей (все одинаковые, все разные, две одной модели, 1 другой и т.п.) Моделей приставок может быть три. Мне нужно: 1. из столбца 1 найти дубли телефонов, т.е. чтобы строк телефонов с одним номером было больше 1 (до трех можеть быть, т.е. искать 2 или 3 дубля одного телефона; 2. по этому списку найти, чтобы было 2 модели на одном телефон (две указанных модели из трех всего возможных моделей). Во вложении примерно набросал что хочется видеть. Желтым виделил что нужно найти. Список желательно сделать отдельным. Но можно и подсветить каким-либо образом. zavokzalka
Ответить
Сообщение Здравствуйте! Прошу помощи в решении задачи. Вводные данные: около 1млн строк. 2 столбца. В первом слолбике - телефон, во втором модель приставки. На одном телефоне, построчно может быть от 1 до 3 приставок в различных комбинациях моделей (все одинаковые, все разные, две одной модели, 1 другой и т.п.) Моделей приставок может быть три. Мне нужно: 1. из столбца 1 найти дубли телефонов, т.е. чтобы строк телефонов с одним номером было больше 1 (до трех можеть быть, т.е. искать 2 или 3 дубля одного телефона; 2. по этому списку найти, чтобы было 2 модели на одном телефон (две указанных модели из трех всего возможных моделей). Во вложении примерно набросал что хочется видеть. Желтым виделил что нужно найти. Список желательно сделать отдельным. Но можно и подсветить каким-либо образом. Автор - zavokzalka Дата добавления - 03.08.2020 в 21:17
Che79
Дата: Понедельник, 03.08.2020, 21:48 |
Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация:
306
±
Замечаний:
0% ±
2013 Win, 365 Mac
Здравствуйте. Тогда держите вариант через УФ Код
=СЧЁТЕСЛИ($A$2:$A$100;$A2)>1
Диапазон в формуле, если нужно, увеличьте.
Здравствуйте. Тогда держите вариант через УФ Код
=СЧЁТЕСЛИ($A$2:$A$100;$A2)>1
Диапазон в формуле, если нужно, увеличьте. Che79
Делай нормально и будет нормально!
Ответить
Сообщение Здравствуйте. Тогда держите вариант через УФ Код
=СЧЁТЕСЛИ($A$2:$A$100;$A2)>1
Диапазон в формуле, если нужно, увеличьте. Автор - Che79 Дата добавления - 03.08.2020 в 21:48
Светлый
Дата: Понедельник, 03.08.2020, 23:10 |
Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация:
510
±
Замечаний:
0% ±
Excel 2013, 2016
Здравствуйте! Может быть так?Код
=СУММПРОИЗВ(ЗНАК(СЧЁТЕСЛИМН(A$2:A$99;A2;B$2:B$99;E$7:E$8))*{10:1})=11
Или массивная формула:Код
=СЧЁТ(1/СЧЁТЕСЛИМН(A$2:A$99;A2;B$2:B$99;E$7:E$8))=2
*А такая массивная формула выдаст сортированный список удовлетворяющих условиям телефонов:Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ((Ч(ИНДЕКС(МУМНОЖ(ЗНАК(СЧЁТЕСЛИМН(A$1:A$99;A$1:A$99;B$1:B$99;ТРАНСП(E$7:E$8)));{10:1});))=11)*ЕЧИСЛО(1/(ПОИСКПОЗ(A$1:A$99;A$1:A$99;)=СТРОКА(A$1:A$99)));A$1:A$99);СТРОКА(A1));"")
**Немного проще:Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ((МУМНОЖ(ЗНАК(СЧЁТЕСЛИМН(A$1:A$99;A$1:A$99;B$1:B$99;ТРАНСП(E$7:E$8)));{10:1})=11)*ЕЧИСЛО(1/(ПОИСКПОЗ(A$1:A$99;A$1:A$99;)=СТРОКА(A$1:A$99)));A$1:A$99);СТРОКА(A1));"")
Здравствуйте! Может быть так?Код
=СУММПРОИЗВ(ЗНАК(СЧЁТЕСЛИМН(A$2:A$99;A2;B$2:B$99;E$7:E$8))*{10:1})=11
Или массивная формула:Код
=СЧЁТ(1/СЧЁТЕСЛИМН(A$2:A$99;A2;B$2:B$99;E$7:E$8))=2
*А такая массивная формула выдаст сортированный список удовлетворяющих условиям телефонов:Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ((Ч(ИНДЕКС(МУМНОЖ(ЗНАК(СЧЁТЕСЛИМН(A$1:A$99;A$1:A$99;B$1:B$99;ТРАНСП(E$7:E$8)));{10:1});))=11)*ЕЧИСЛО(1/(ПОИСКПОЗ(A$1:A$99;A$1:A$99;)=СТРОКА(A$1:A$99)));A$1:A$99);СТРОКА(A1));"")
**Немного проще:Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ((МУМНОЖ(ЗНАК(СЧЁТЕСЛИМН(A$1:A$99;A$1:A$99;B$1:B$99;ТРАНСП(E$7:E$8)));{10:1})=11)*ЕЧИСЛО(1/(ПОИСКПОЗ(A$1:A$99;A$1:A$99;)=СТРОКА(A$1:A$99)));A$1:A$99);СТРОКА(A1));"")
Светлый
Программировать проще, чем писать стихи.
Сообщение отредактировал Светлый - Вторник, 04.08.2020, 00:03
Ответить
Сообщение Здравствуйте! Может быть так?Код
=СУММПРОИЗВ(ЗНАК(СЧЁТЕСЛИМН(A$2:A$99;A2;B$2:B$99;E$7:E$8))*{10:1})=11
Или массивная формула:Код
=СЧЁТ(1/СЧЁТЕСЛИМН(A$2:A$99;A2;B$2:B$99;E$7:E$8))=2
*А такая массивная формула выдаст сортированный список удовлетворяющих условиям телефонов:Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ((Ч(ИНДЕКС(МУМНОЖ(ЗНАК(СЧЁТЕСЛИМН(A$1:A$99;A$1:A$99;B$1:B$99;ТРАНСП(E$7:E$8)));{10:1});))=11)*ЕЧИСЛО(1/(ПОИСКПОЗ(A$1:A$99;A$1:A$99;)=СТРОКА(A$1:A$99)));A$1:A$99);СТРОКА(A1));"")
**Немного проще:Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ((МУМНОЖ(ЗНАК(СЧЁТЕСЛИМН(A$1:A$99;A$1:A$99;B$1:B$99;ТРАНСП(E$7:E$8)));{10:1})=11)*ЕЧИСЛО(1/(ПОИСКПОЗ(A$1:A$99;A$1:A$99;)=СТРОКА(A$1:A$99)));A$1:A$99);СТРОКА(A1));"")
Автор - Светлый Дата добавления - 03.08.2020 в 23:10
zavokzalka
Дата: Вторник, 04.08.2020, 09:13 |
Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация:
0
±
Замечаний:
0% ±
Excel 2019
Здравствуйте! Может быть так?
немного не понял где применять данные формулы. для условного форматирования они не подходят. пробовал в пустых ячейках - не выводит ничего, кроме 1-2 формулы, там пишет "ложь", в том числе на нужных вариантах.
Здравствуйте! Может быть так?
немного не понял где применять данные формулы. для условного форматирования они не подходят. пробовал в пустых ячейках - не выводит ничего, кроме 1-2 формулы, там пишет "ложь", в том числе на нужных вариантах.zavokzalka
Сообщение отредактировал zavokzalka - Вторник, 04.08.2020, 09:19
Ответить
Сообщение Здравствуйте! Может быть так?
немного не понял где применять данные формулы. для условного форматирования они не подходят. пробовал в пустых ячейках - не выводит ничего, кроме 1-2 формулы, там пишет "ложь", в том числе на нужных вариантах.Автор - zavokzalka Дата добавления - 04.08.2020 в 09:13
Светлый
Дата: Вторник, 04.08.2020, 10:32 |
Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация:
510
±
Замечаний:
0% ±
Excel 2013, 2016
В УФ работает.
Программировать проще, чем писать стихи.
Ответить
Сообщение В УФ работает. Автор - Светлый Дата добавления - 04.08.2020 в 10:32
bmv98rus
Дата: Вторник, 04.08.2020, 10:48 |
Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация:
768
±
Замечаний:
0% ±
Excel 2013/2016
Светлый , Вводные данные: около 1млн строк. 2 столбца.
Делайте сводную, там и фильтр можно на приставку поставить и сразу количество посчитать.
Светлый , Вводные данные: около 1млн строк. 2 столбца.
Делайте сводную, там и фильтр можно на приставку поставить и сразу количество посчитать.bmv98rus
Замечательный Временно просто медведь , процентов на 20 .
Ответить
Сообщение Светлый , Вводные данные: около 1млн строк. 2 столбца.
Делайте сводную, там и фильтр можно на приставку поставить и сразу количество посчитать.Автор - bmv98rus Дата добавления - 04.08.2020 в 10:48
zavokzalka
Дата: Вторник, 04.08.2020, 13:16 |
Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация:
0
±
Замечаний:
0% ±
Excel 2019
Делайте сводную, там и фильтр можно на приставку поставить и сразу количество посчитать.
сводная не совсем то, мне не нужно считать кол-во приставок. а именно выделить телефоны, где две разных приставки, указанных моделей. Выше есть решение, пока не могу раскатать на все данные.
Делайте сводную, там и фильтр можно на приставку поставить и сразу количество посчитать.
сводная не совсем то, мне не нужно считать кол-во приставок. а именно выделить телефоны, где две разных приставки, указанных моделей. Выше есть решение, пока не могу раскатать на все данные.zavokzalka
Сообщение отредактировал zavokzalka - Вторник, 04.08.2020, 15:55
Ответить
Сообщение Делайте сводную, там и фильтр можно на приставку поставить и сразу количество посчитать.
сводная не совсем то, мне не нужно считать кол-во приставок. а именно выделить телефоны, где две разных приставки, указанных моделей. Выше есть решение, пока не могу раскатать на все данные.Автор - zavokzalka Дата добавления - 04.08.2020 в 13:16
bmv98rus
Дата: Вторник, 04.08.2020, 13:34 |
Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация:
768
±
Замечаний:
0% ±
Excel 2013/2016
Может ли быть что у одного абонента несколько единиц одной модели? Если нет, то сводная элементарно решает проблему.
Может ли быть что у одного абонента несколько единиц одной модели? Если нет, то сводная элементарно решает проблему. bmv98rus
Замечательный Временно просто медведь , процентов на 20 .
Сообщение отредактировал bmv98rus - Вторник, 04.08.2020, 13:37
Ответить
Сообщение Может ли быть что у одного абонента несколько единиц одной модели? Если нет, то сводная элементарно решает проблему. Автор - bmv98rus Дата добавления - 04.08.2020 в 13:34
zavokzalka
Дата: Вторник, 04.08.2020, 15:56 |
Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация:
0
±
Замечаний:
0% ±
Excel 2019
Может ли быть что у одного абонента несколько единиц одной модели?
да, может. собственно задача в том, чтобы найти тех у кого всего две указанные модели, по 1й каждого вида.
Может ли быть что у одного абонента несколько единиц одной модели?
да, может. собственно задача в том, чтобы найти тех у кого всего две указанные модели, по 1й каждого вида.zavokzalka
Ответить
Сообщение Может ли быть что у одного абонента несколько единиц одной модели?
да, может. собственно задача в том, чтобы найти тех у кого всего две указанные модели, по 1й каждого вида.Автор - zavokzalka Дата добавления - 04.08.2020 в 15:56
bmv98rus
Дата: Вторник, 04.08.2020, 16:55 |
Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация:
768
±
Замечаний:
0% ±
Excel 2013/2016
Ну тут или сводная, как промежуток, или PowerQuer Сделайте пример чуть побольше
Ну тут или сводная, как промежуток, или PowerQuer Сделайте пример чуть побольшеbmv98rus
Замечательный Временно просто медведь , процентов на 20 .
Сообщение отредактировал bmv98rus - Вторник, 04.08.2020, 17:19
Ответить
Сообщение Ну тут или сводная, как промежуток, или PowerQuer Сделайте пример чуть побольшеАвтор - bmv98rus Дата добавления - 04.08.2020 в 16:55
Светлый
Дата: Среда, 05.08.2020, 21:17 |
Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация:
510
±
Замечаний:
0% ±
Excel 2013, 2016
чтобы найти тех у кого всего две указанные модели, по 1й каждого вида
Тогда формулы будут немного другими:Код
=СУММПРОИЗВ(СЧЁТЕСЛИМН(A$2:A$99;A2;B$2:B$99;E$2:E$4)*{1:10:100})=11
Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ((МУМНОЖ(СЧЁТЕСЛИМН(A$1:A$99;A$1:A$99;B$1:B$99;ТРАНСП(E$2:E$4));{1:10:100})=11)*ЕЧИСЛО(1/(ПОИСКПОЗ(A$1:A$99;A$1:A$99;)=СТРОКА(A$1:A$99)));A$1:A$99);СТРОКА(A1));"")
*Если надо другие модели вместо 11 можно подставить 101 или 110.
чтобы найти тех у кого всего две указанные модели, по 1й каждого вида
Тогда формулы будут немного другими:Код
=СУММПРОИЗВ(СЧЁТЕСЛИМН(A$2:A$99;A2;B$2:B$99;E$2:E$4)*{1:10:100})=11
Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ((МУМНОЖ(СЧЁТЕСЛИМН(A$1:A$99;A$1:A$99;B$1:B$99;ТРАНСП(E$2:E$4));{1:10:100})=11)*ЕЧИСЛО(1/(ПОИСКПОЗ(A$1:A$99;A$1:A$99;)=СТРОКА(A$1:A$99)));A$1:A$99);СТРОКА(A1));"")
*Если надо другие модели вместо 11 можно подставить 101 или 110.Светлый
Программировать проще, чем писать стихи.
Сообщение отредактировал Светлый - Среда, 05.08.2020, 21:18
Ответить
Сообщение чтобы найти тех у кого всего две указанные модели, по 1й каждого вида
Тогда формулы будут немного другими:Код
=СУММПРОИЗВ(СЧЁТЕСЛИМН(A$2:A$99;A2;B$2:B$99;E$2:E$4)*{1:10:100})=11
Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ((МУМНОЖ(СЧЁТЕСЛИМН(A$1:A$99;A$1:A$99;B$1:B$99;ТРАНСП(E$2:E$4));{1:10:100})=11)*ЕЧИСЛО(1/(ПОИСКПОЗ(A$1:A$99;A$1:A$99;)=СТРОКА(A$1:A$99)));A$1:A$99);СТРОКА(A1));"")
*Если надо другие модели вместо 11 можно подставить 101 или 110.Автор - Светлый Дата добавления - 05.08.2020 в 21:17
Hugo
Дата: Среда, 05.08.2020, 21:57 |
Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3252
Репутация:
707
±
Замечаний:
0% ±
2019
а именно выделить телефоны
на более миллионе строк? Что это даст? Что с этим выделением будете делать? Как обрабатывать? Фильтр ведь 10000 только, или уже есть версии где работает на миллионы строк? Я бы сразу макросом ну или PQ выбирал такие строки в отдельный лист. Но это не по профилю...
а именно выделить телефоны
на более миллионе строк? Что это даст? Что с этим выделением будете делать? Как обрабатывать? Фильтр ведь 10000 только, или уже есть версии где работает на миллионы строк? Я бы сразу макросом ну или PQ выбирал такие строки в отдельный лист. Но это не по профилю...Hugo
excel@nxt.ru webmoney: E265281470651 Z422237915069
Сообщение отредактировал Hugo - Среда, 05.08.2020, 21:57
Ответить
Сообщение а именно выделить телефоны
на более миллионе строк? Что это даст? Что с этим выделением будете делать? Как обрабатывать? Фильтр ведь 10000 только, или уже есть версии где работает на миллионы строк? Я бы сразу макросом ну или PQ выбирал такие строки в отдельный лист. Но это не по профилю...Автор - Hugo Дата добавления - 05.08.2020 в 21:57
bmv98rus
Дата: Четверг, 06.08.2020, 08:28 |
Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация:
768
±
Замечаний:
0% ±
Excel 2013/2016
ну 2016 точно весь лист фильтрует, другое дело, нужно ли так - я об этом тоже писал чуть выше. Светлый , то просто мозг тренирует. Практическое применение на малом количестве - есть, а вот лимон строк- задумчивость будет неимеверной.
ну 2016 точно весь лист фильтрует, другое дело, нужно ли так - я об этом тоже писал чуть выше. Светлый , то просто мозг тренирует. Практическое применение на малом количестве - есть, а вот лимон строк- задумчивость будет неимеверной.bmv98rus
Замечательный Временно просто медведь , процентов на 20 .
Ответить
Сообщение ну 2016 точно весь лист фильтрует, другое дело, нужно ли так - я об этом тоже писал чуть выше. Светлый , то просто мозг тренирует. Практическое применение на малом количестве - есть, а вот лимон строк- задумчивость будет неимеверной.Автор - bmv98rus Дата добавления - 06.08.2020 в 08:28
Светлый
Дата: Четверг, 06.08.2020, 11:30 |
Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация:
510
±
Замечаний:
0% ±
Excel 2013, 2016
Светлый, то просто мозг тренирует.
Точно. Чтобы деменции не было. Создаём дополнительный столбец с формулой:Код
=(СУММПРОИЗВ(СЧЁТЕСЛИМН(A$1:A$99;A2;B$1:B$99;E$2:E$4)*{1:10:100})=11)*(ПОИСКПОЗ(A2;A:A;)=СТРОКА(A2))
и сортируем по нему. Тоже долго на большом массиве, но быстрее, чем предыдущий вариант.
Светлый, то просто мозг тренирует.
Точно. Чтобы деменции не было. Создаём дополнительный столбец с формулой:Код
=(СУММПРОИЗВ(СЧЁТЕСЛИМН(A$1:A$99;A2;B$1:B$99;E$2:E$4)*{1:10:100})=11)*(ПОИСКПОЗ(A2;A:A;)=СТРОКА(A2))
и сортируем по нему. Тоже долго на большом массиве, но быстрее, чем предыдущий вариант.Светлый
Программировать проще, чем писать стихи.
Ответить
Сообщение Светлый, то просто мозг тренирует.
Точно. Чтобы деменции не было. Создаём дополнительный столбец с формулой:Код
=(СУММПРОИЗВ(СЧЁТЕСЛИМН(A$1:A$99;A2;B$1:B$99;E$2:E$4)*{1:10:100})=11)*(ПОИСКПОЗ(A2;A:A;)=СТРОКА(A2))
и сортируем по нему. Тоже долго на большом массиве, но быстрее, чем предыдущий вариант.Автор - Светлый Дата добавления - 06.08.2020 в 11:30