Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Сравнить значения в дублях - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сравнить значения в дублях (Формулы/Formulas)
Сравнить значения в дублях
zavokzalka Дата: Понедельник, 03.08.2020, 21:17 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Здравствуйте! Прошу помощи в решении задачи.
Вводные данные: около 1млн строк. 2 столбца.
В первом слолбике - телефон, во втором модель приставки.
На одном телефоне, построчно может быть от 1 до 3 приставок в различных комбинациях моделей (все одинаковые, все разные, две одной модели, 1 другой и т.п.)
Моделей приставок может быть три.
Мне нужно:
1. из столбца 1 найти дубли телефонов, т.е. чтобы строк телефонов с одним номером было больше 1 (до трех можеть быть, т.е. искать 2 или 3 дубля одного телефона;
2. по этому списку найти, чтобы было 2 модели на одном телефон (две указанных модели из трех всего возможных моделей).

Во вложении примерно набросал что хочется видеть.
Желтым виделил что нужно найти.
Список желательно сделать отдельным.
Но можно и подсветить каким-либо образом.
К сообщению приложен файл: 3744172.xlsx (8.5 Kb)
 
Ответить
СообщениеЗдравствуйте! Прошу помощи в решении задачи.
Вводные данные: около 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
Диапазон в формуле, если нужно, увеличьте.
К сообщению приложен файл: 3744172-1.xlsx (8.9 Kb)


Делай нормально и будет нормально!
 
Ответить
СообщениеЗдравствуйте.
можно и подсветить
Тогда держите вариант через УФ
Код
=СЧЁТЕСЛИ($A$2:$A$100;$A2)>1
Диапазон в формуле, если нужно, увеличьте.

Автор - Che79
Дата добавления - 03.08.2020 в 21:48
Светлый Дата: Понедельник, 03.08.2020, 23:10 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 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));"")


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Вторник, 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 формулы, там пишет "ложь", в том числе на нужных вариантах.


Сообщение отредактировал zavokzalka - Вторник, 04.08.2020, 09:19
 
Ответить
Сообщение
Здравствуйте!
Может быть так?

немного не понял где применять данные формулы. для условного форматирования они не подходят.
пробовал в пустых ячейках - не выводит ничего, кроме 1-2 формулы, там пишет "ложь", в том числе на нужных вариантах.

Автор - zavokzalka
Дата добавления - 04.08.2020 в 09:13
Светлый Дата: Вторник, 04.08.2020, 10:32 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
В УФ работает.
К сообщению приложен файл: 6422619.xlsx (10.2 Kb)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеВ УФ работает.

Автор - Светлый
Дата добавления - 04.08.2020 в 10:32
bmv98rus Дата: Вторник, 04.08.2020, 10:48 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Светлый,
Вводные данные: около 1млн строк. 2 столбца.
Цитата Светлый, 03.08.2020 в 23:10, в сообщении № 3 ()
Может быть так?
:D

Делайте сводную, там и фильтр можно на приставку поставить и сразу количество посчитать.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеСветлый,
Вводные данные: около 1млн строк. 2 столбца.
Цитата Светлый, 03.08.2020 в 23:10, в сообщении № 3 ()
Может быть так?
:D

Делайте сводную, там и фильтр можно на приставку поставить и сразу количество посчитать.

Автор - bmv98rus
Дата добавления - 04.08.2020 в 10:48
zavokzalka Дата: Вторник, 04.08.2020, 13:16 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019

Делайте сводную, там и фильтр можно на приставку поставить и сразу количество посчитать.

сводная не совсем то, мне не нужно считать кол-во приставок. а именно выделить телефоны, где две разных приставки, указанных моделей.
Выше есть решение, пока не могу раскатать на все данные.


Сообщение отредактировал zavokzalka - Вторник, 04.08.2020, 15:55
 
Ответить
Сообщение

Делайте сводную, там и фильтр можно на приставку поставить и сразу количество посчитать.

сводная не совсем то, мне не нужно считать кол-во приставок. а именно выделить телефоны, где две разных приставки, указанных моделей.
Выше есть решение, пока не могу раскатать на все данные.

Автор - zavokzalka
Дата добавления - 04.08.2020 в 13:16
bmv98rus Дата: Вторник, 04.08.2020, 13:34 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Может ли быть что у одного абонента несколько единиц одной модели? Если нет, то сводная элементарно решает проблему.
К сообщению приложен файл: example1802.xlsx (12.2 Kb)


Замечательный Временно просто медведь , процентов на 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
Дата добавления - 04.08.2020 в 15:56
bmv98rus Дата: Вторник, 04.08.2020, 16:55 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
да, может

Ну тут или сводная, как промежуток, или PowerQuer
Сделайте пример чуть побольше


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Вторник, 04.08.2020, 17:19
 
Ответить
Сообщение
да, может

Ну тут или сводная, как промежуток, или PowerQuer
Сделайте пример чуть побольше

Автор - bmv98rus
Дата добавления - 04.08.2020 в 16:55
Светлый Дата: Среда, 05.08.2020, 21:17 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 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.
К сообщению приложен файл: 3744172-2.xlsx (10.3 Kb)


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Среда, 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3250
Репутация: 707 ±
Замечаний: 0% ±

2019
а именно выделить телефоны
на более миллионе строк? Что это даст? Что с этим выделением будете делать? Как обрабатывать? Фильтр ведь 10000 только, или уже есть версии где работает на миллионы строк?
Я бы сразу макросом ну или PQ выбирал такие строки в отдельный лист.
Но это не по профилю...


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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Фильтр ведь 10000

ну 2016 точно весь лист фильтрует, другое дело, нужно ли так - я об этом тоже писал чуть выше. Светлый, то просто мозг тренирует. Практическое применение на малом количестве - есть, а вот лимон строк- задумчивость будет неимеверной.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Фильтр ведь 10000

ну 2016 точно весь лист фильтрует, другое дело, нужно ли так - я об этом тоже писал чуть выше. Светлый, то просто мозг тренирует. Практическое применение на малом количестве - есть, а вот лимон строк- задумчивость будет неимеверной.

Автор - bmv98rus
Дата добавления - 06.08.2020 в 08:28
Светлый Дата: Четверг, 06.08.2020, 11:30 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 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))
и сортируем по нему. Тоже долго на большом массиве, но быстрее, чем предыдущий вариант.

Автор - Светлый
Дата добавления - 06.08.2020 в 11:30
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сравнить значения в дублях (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!