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

 

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

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир 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
Здравствуйте.
Цитата zavokzalka, 03.08.2020 в 21:17, в сообщении № 1 ( писал(а)):
можно и подсветить
Тогда держите вариант через УФ
=СЧЁТЕСЛИ($A$2:$A$100;$A2)>1
Диапазон в формуле, если нужно, увеличьте.
К сообщению приложен файл: 3744172-1.xlsx (8.9 Kb)

Делай нормально и будет нормально!
 
Ответить
СообщениеЗдравствуйте.
Цитата zavokzalka, 03.08.2020 в 21:17, в сообщении № 1 ( писал(а)):
можно и подсветить
Тогда держите вариант через УФ
=СЧЁТЕСЛИ($A$2:$A$100;$A2)>1
Диапазон в формуле, если нужно, увеличьте.
Автор - Che79
Дата добавления - 03.08.2020 в 21:48
Светлый Дата: Понедельник, 03.08.2020, 23:10 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1869
Репутация: 536 ±
Замечаний: 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
Цитата Che79, 03.08.2020 в 21:48, в сообщении № 2 ( писал(а)):
Здравствуйте!
Может быть так?

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


Сообщение отредактировал zavokzalka - Вторник, 04.08.2020, 09:19
 
Ответить
Сообщение
Цитата Che79, 03.08.2020 в 21:48, в сообщении № 2 ( писал(а)):
Здравствуйте!
Может быть так?

немного не понял где применять данные формулы. для условного форматирования они не подходят.
пробовал в пустых ячейках - не выводит ничего, кроме 1-2 формулы, там пишет "ложь", в том числе на нужных вариантах.
Автор - zavokzalka
Дата добавления - 04.08.2020 в 09:13
Светлый Дата: Вторник, 04.08.2020, 10:32 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1869
Репутация: 536 ±
Замечаний: 0% ±

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

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

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

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

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеСветлый,
Цитата zavokzalka, 03.08.2020 в 21:17, в сообщении № 1 ( писал(а)):
Вводные данные: около 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
Цитата bmv98rus, 04.08.2020 в 10:48, в сообщении № 6 ( писал(а)):

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

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


Сообщение отредактировал zavokzalka - Вторник, 04.08.2020, 15:55
 
Ответить
Сообщение
Цитата bmv98rus, 04.08.2020 в 10:48, в сообщении № 6 ( писал(а)):

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

сводная не совсем то, мне не нужно считать кол-во приставок. а именно выделить телефоны, где две разных приставки, указанных моделей.
Выше есть решение, пока не могу раскатать на все данные.
Автор - zavokzalka
Дата добавления - 04.08.2020 в 13:16
bmv98rus Дата: Вторник, 04.08.2020, 13:34 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 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
Цитата bmv98rus, 04.08.2020 в 13:34, в сообщении № 8 ( писал(а)):
Может ли быть что у одного абонента несколько единиц одной модели?

да, может. собственно задача в том, чтобы найти тех у кого всего две указанные модели, по 1й каждого вида.
 
Ответить
Сообщение
Цитата bmv98rus, 04.08.2020 в 13:34, в сообщении № 8 ( писал(а)):
Может ли быть что у одного абонента несколько единиц одной модели?

да, может. собственно задача в том, чтобы найти тех у кого всего две указанные модели, по 1й каждого вида.
Автор - zavokzalka
Дата добавления - 04.08.2020 в 15:56
bmv98rus Дата: Вторник, 04.08.2020, 16:55 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата zavokzalka, 04.08.2020 в 15:56, в сообщении № 10 ( писал(а)):
да, может

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

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

Сообщение отредактировал bmv98rus - Вторник, 04.08.2020, 17:19
 
Ответить
Сообщение
Цитата zavokzalka, 04.08.2020 в 15:56, в сообщении № 10 ( писал(а)):
да, может

Ну тут или сводная, как промежуток, или PowerQuer
Сделайте пример чуть побольше
Автор - bmv98rus
Дата добавления - 04.08.2020 в 16:55
Светлый Дата: Среда, 05.08.2020, 21:17 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1869
Репутация: 536 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата zavokzalka, 04.08.2020 в 15:56, в сообщении № 9 ( писал(а)):
чтобы найти тех у кого всего две указанные модели, по 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
 
Ответить
Сообщение
Цитата zavokzalka, 04.08.2020 в 15:56, в сообщении № 9 ( писал(а)):
чтобы найти тех у кого всего две указанные модели, по 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3854
Репутация: 814 ±
Замечаний: 0% ±

365
Цитата zavokzalka, 04.08.2020 в 13:16, в сообщении № 7 ( писал(а)):
а именно выделить телефоны
на более миллионе строк? Что это даст? Что с этим выделением будете делать? Как обрабатывать? Фильтр ведь 10000 только, или уже есть версии где работает на миллионы строк?
Я бы сразу макросом ну или PQ выбирал такие строки в отдельный лист.
Но это не по профилю...

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD

Сообщение отредактировал Hugo - Среда, 05.08.2020, 21:57
 
Ответить
Сообщение
Цитата zavokzalka, 04.08.2020 в 13:16, в сообщении № 7 ( писал(а)):
а именно выделить телефоны
на более миллионе строк? Что это даст? Что с этим выделением будете делать? Как обрабатывать? Фильтр ведь 10000 только, или уже есть версии где работает на миллионы строк?
Я бы сразу макросом ну или PQ выбирал такие строки в отдельный лист.
Но это не по профилю...
Автор - Hugo
Дата добавления - 05.08.2020 в 21:57
bmv98rus Дата: Четверг, 06.08.2020, 08:28 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата Hugo, 05.08.2020 в 21:57, в сообщении № 12 ( писал(а)):
Фильтр ведь 10000

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

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Цитата Hugo, 05.08.2020 в 21:57, в сообщении № 12 ( писал(а)):
Фильтр ведь 10000

ну 2016 точно весь лист фильтрует, другое дело, нужно ли так - я об этом тоже писал чуть выше. Светлый, то просто мозг тренирует. Практическое применение на малом количестве - есть, а вот лимон строк- задумчивость будет неимеверной.
Автор - bmv98rus
Дата добавления - 06.08.2020 в 08:28
Светлый Дата: Четверг, 06.08.2020, 11:30 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1869
Репутация: 536 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата bmv98rus, 06.08.2020 в 08:28, в сообщении № 13 ( писал(а)):
Светлый, то просто мозг тренирует.
Точно. Чтобы деменции не было.
Создаём дополнительный столбец с формулой:
=(СУММПРОИЗВ(СЧЁТЕСЛИМН(A$1:A$99;A2;B$1:B$99;E$2:E$4)*{1:10:100})=11)*(ПОИСКПОЗ(A2;A:A;)=СТРОКА(A2))
и сортируем по нему. Тоже долго на большом массиве, но быстрее, чем предыдущий вариант.

Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Цитата bmv98rus, 06.08.2020 в 08:28, в сообщении № 13 ( писал(а)):
Светлый, то просто мозг тренирует.
Точно. Чтобы деменции не было.
Создаём дополнительный столбец с формулой:
=(СУММПРОИЗВ(СЧЁТЕСЛИМН(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
Поиск:

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