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

Вход

Регистрация

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

 

= Мир MS Excel/Сравнить два столбца и отметить уникальные значения в каждом - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Сравнить два столбца и отметить уникальные значения в каждом (Макросы/Sub)
Сравнить два столбца и отметить уникальные значения в каждом
iddqd Дата: Четверг, 17.03.2016, 11:26 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Здравствуйте. Давно читаю ваш форум, много полезного почерпнул. Спасибо. Но сейчас без совета гуру не обойтись.
Есть таблица с данными из 6 столбцов.
Нужно раскидать из двух столбцов значения в итоговую таблицу из 4 столбцов, а часть данных нужно удалить, учитывая заданные условия.
Одним из условий является уникальность данных в каждом из столбцов.
Поэтому я помещаю в соседний дополнительный столбец метку "1 - уникальное" и "2 - неуникальное" для каждого из двух столбцов.
Затем проверяю все имеющиеся условия и раскидываю данные из этих двух столбцов в итоговую таблицу.
Макрос написал, он работает как надо. Но на большом количестве строк (от 10000) работает очень долго.
Всё дело в способе определения уникальности значений.
Я сравниваю каждое значение из одного столбца с каждым значением из другого столбца. И наоборот.
Поэтому столбцы с метками заполняются очень долго.
Использовать ВПР и растянуть до конца значений гораздо быстрее.
На вашем форуме я нашёл как можно вставить и протянуть формулу - не помогло: фиксированный диапазон просмотра в ВПР нужно задавать

переменными, т.к. число обрабатываемых строк всегда разное и отличается в обоих столбцах.

Я вставляю формулу так:

Поиск уникальных значений в 1 столбце в сравнении с 6 столбцом, метку поместить в 3 столбец
[vba]
Код
Range("C2").Select
N = Range("C2").End(xlDown).Row
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-2],Range(Cells(2, 6), Cells(2, N)),1,FALSE)),1,2)"
[/vba]
где Range(Cells(2, 6), Cells(2, N)) фиксированный диапазон просмотра из другого столбца
N - число строк столбца со значениями (пустых ячеек нет)
протягиваю так:
[vba]
Код
Range("C2").AutoFill Destination:=Range("C2:C" & N)
[/vba]
Протянутая формула всегда возвращет значение "1" потому что "RC[-2]" при преобразовано в "A2", а "Range(Cells(2, 6), Cells(2, N))" в "$F$2:$F$115" не преобразовывается

Подскажите, пожалуйста третий путь для написания быстрого макроса.
[moder]Обтягивайте коды кнопкой #, а не спойлером.
Поправила на первый раз[/moder]


Привет всем участникам Броуновского движения!

Сообщение отредактировал Manyasha - Четверг, 17.03.2016, 12:15
 
Ответить
СообщениеЗдравствуйте. Давно читаю ваш форум, много полезного почерпнул. Спасибо. Но сейчас без совета гуру не обойтись.
Есть таблица с данными из 6 столбцов.
Нужно раскидать из двух столбцов значения в итоговую таблицу из 4 столбцов, а часть данных нужно удалить, учитывая заданные условия.
Одним из условий является уникальность данных в каждом из столбцов.
Поэтому я помещаю в соседний дополнительный столбец метку "1 - уникальное" и "2 - неуникальное" для каждого из двух столбцов.
Затем проверяю все имеющиеся условия и раскидываю данные из этих двух столбцов в итоговую таблицу.
Макрос написал, он работает как надо. Но на большом количестве строк (от 10000) работает очень долго.
Всё дело в способе определения уникальности значений.
Я сравниваю каждое значение из одного столбца с каждым значением из другого столбца. И наоборот.
Поэтому столбцы с метками заполняются очень долго.
Использовать ВПР и растянуть до конца значений гораздо быстрее.
На вашем форуме я нашёл как можно вставить и протянуть формулу - не помогло: фиксированный диапазон просмотра в ВПР нужно задавать

переменными, т.к. число обрабатываемых строк всегда разное и отличается в обоих столбцах.

Я вставляю формулу так:

Поиск уникальных значений в 1 столбце в сравнении с 6 столбцом, метку поместить в 3 столбец
[vba]
Код
Range("C2").Select
N = Range("C2").End(xlDown).Row
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-2],Range(Cells(2, 6), Cells(2, N)),1,FALSE)),1,2)"
[/vba]
где Range(Cells(2, 6), Cells(2, N)) фиксированный диапазон просмотра из другого столбца
N - число строк столбца со значениями (пустых ячеек нет)
протягиваю так:
[vba]
Код
Range("C2").AutoFill Destination:=Range("C2:C" & N)
[/vba]
Протянутая формула всегда возвращет значение "1" потому что "RC[-2]" при преобразовано в "A2", а "Range(Cells(2, 6), Cells(2, N))" в "$F$2:$F$115" не преобразовывается

Подскажите, пожалуйста третий путь для написания быстрого макроса.
[moder]Обтягивайте коды кнопкой #, а не спойлером.
Поправила на первый раз[/moder]

Автор - iddqd
Дата добавления - 17.03.2016 в 11:26
Manyasha Дата: Четверг, 17.03.2016, 11:33 | Сообщение № 2
Группа: Модераторы
Ранг: Старожил
Сообщений: 1586
Репутация: 662 ±
Замечаний: 0% ±

Excel 2007, 2010
iddqd, здравствуйте, можно использовать словари. И работайте не с листом. Запомните все данные в массивы и обрабатывайте их вместо ячеек листа.

Если нужны более подробные ответы, показывайте файл с макросом.


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804
 
Ответить
Сообщениеiddqd, здравствуйте, можно использовать словари. И работайте не с листом. Запомните все данные в массивы и обрабатывайте их вместо ячеек листа.

Если нужны более подробные ответы, показывайте файл с макросом.

Автор - Manyasha
Дата добавления - 17.03.2016 в 11:33
SLAVICK Дата: Четверг, 17.03.2016, 11:54 | Сообщение № 3
Группа: Модераторы
Ранг: Старожил
Сообщений: 1836
Репутация: 613 ±
Замечаний: 0% ±

2007,2010,2013,2016
Почитайте эту тему.
Там я писал такой макрос.


Иногда все проще чем кажется с первого взгляда.
 
Ответить
СообщениеПочитайте эту тему.
Там я писал такой макрос.

Автор - SLAVICK
Дата добавления - 17.03.2016 в 11:54
iddqd Дата: Четверг, 17.03.2016, 12:00 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
SLAVICK, спасибо, изучаю. Осталось понять как вместо копирования ставить метку в соседнем столбце


Привет всем участникам Броуновского движения!
 
Ответить
СообщениеSLAVICK, спасибо, изучаю. Осталось понять как вместо копирования ставить метку в соседнем столбце

Автор - iddqd
Дата добавления - 17.03.2016 в 12:00
_Boroda_ Дата: Четверг, 17.03.2016, 12:02 | Сообщение № 5
Группа: Модераторы
Ранг: Экселист
Сообщений: 9362
Репутация: 3936 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
: фиксированный диапазон просмотра в ВПР нужно задавать переменными,
Смотрите небольшой примерчик, как это можно сделать. Не для этого случая, а так, на будущее.
[vba]
Код
    r1_ = Range("A" & Rows.Count).End(xlUp).Row 'Кол-во строк в столбце А, например, 20
    r2_ = Range("F" & Rows.Count).End(xlUp).Row 'Кол-во строк в столбце F, например, 10
    'вставляем в G2:G10 формулу =--ЕЧИСЛО(ПОИСКПОЗ(F1;$A$1:$A$20;0)), дающую 1 в случае
    'нахождения значения F1 в диапазоне А1:А20
    Range("G2:G" & r2_).FormulaR1C1 = "=--ISNUMBER(MATCH(RC[-1],R1C1:R" & r1_ & "C1,0))"
[/vba]


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
: фиксированный диапазон просмотра в ВПР нужно задавать переменными,
Смотрите небольшой примерчик, как это можно сделать. Не для этого случая, а так, на будущее.
[vba]
Код
    r1_ = Range("A" & Rows.Count).End(xlUp).Row 'Кол-во строк в столбце А, например, 20
    r2_ = Range("F" & Rows.Count).End(xlUp).Row 'Кол-во строк в столбце F, например, 10
    'вставляем в G2:G10 формулу =--ЕЧИСЛО(ПОИСКПОЗ(F1;$A$1:$A$20;0)), дающую 1 в случае
    'нахождения значения F1 в диапазоне А1:А20
    Range("G2:G" & r2_).FormulaR1C1 = "=--ISNUMBER(MATCH(RC[-1],R1C1:R" & r1_ & "C1,0))"
[/vba]

Автор - _Boroda_
Дата добавления - 17.03.2016 в 12:02
iddqd Дата: Четверг, 17.03.2016, 14:17 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
=--ISNUMBER


А зачем два минуса? Без них ЛОЖЬ/ИСТИНА, с одним -1


Привет всем участникам Броуновского движения!
 
Ответить
Сообщение
=--ISNUMBER


А зачем два минуса? Без них ЛОЖЬ/ИСТИНА, с одним -1

Автор - iddqd
Дата добавления - 17.03.2016 в 14:17
_Boroda_ Дата: Четверг, 17.03.2016, 14:31 | Сообщение № 7
Группа: Модераторы
Ранг: Экселист
Сообщений: 9362
Репутация: 3936 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
читайте про минусы http://www.excelworld.ru/publ/hacks/tricks/17-1-0-9
Почему я сделал именно 0 и 1? А потому, что так удобно считать количество неуникальных - просто выделить столбец с формулой и посмотреть вниз в строку состояния - там (если Вы не меняли ее стандартное обгалочивание) покажет сумму выделенного (она же является и количеством, у нас же при неуникальности единица).


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщениечитайте про минусы http://www.excelworld.ru/publ/hacks/tricks/17-1-0-9
Почему я сделал именно 0 и 1? А потому, что так удобно считать количество неуникальных - просто выделить столбец с формулой и посмотреть вниз в строку состояния - там (если Вы не меняли ее стандартное обгалочивание) покажет сумму выделенного (она же является и количеством, у нас же при неуникальности единица).

Автор - _Boroda_
Дата добавления - 17.03.2016 в 14:31
iddqd Дата: Четверг, 17.03.2016, 22:45 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Смотрите небольшой примерчик, как это можно сделать.


Спасибо, всё работает. Но тормозит, похоже, из-за постоянного пересчёта листа

Для дальнейшей обработки я вставляю потом в эти столбцы значения:

[vba]
Код

Columns("C:C").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
[/vba]

Пересчёт происходит после каждого добавления формулы? Если да, то можно его отключить как обновление экрана?


Привет всем участникам Броуновского движения!

Сообщение отредактировал iddqd - Четверг, 17.03.2016, 22:46
 
Ответить
Сообщение
Смотрите небольшой примерчик, как это можно сделать.


Спасибо, всё работает. Но тормозит, похоже, из-за постоянного пересчёта листа

Для дальнейшей обработки я вставляю потом в эти столбцы значения:

[vba]
Код

Columns("C:C").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
[/vba]

Пересчёт происходит после каждого добавления формулы? Если да, то можно его отключить как обновление экрана?

Автор - iddqd
Дата добавления - 17.03.2016 в 22:45
_Boroda_ Дата: Четверг, 17.03.2016, 22:53 | Сообщение № 9
Группа: Модераторы
Ранг: Экселист
Сообщений: 9362
Репутация: 3936 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Конечно[vba]
Код
    Application.Calculation = xlCalculationManual
[/vba]
И Ваш код можно переписать без Селектов (в большинстве случаев они не нужны и, более того, тормозят)
[vba]
Код
Columns("C:C").Copy
Columns("C:C").PasteSpecial Paste:=xlPasteValues
Columns("E:E").Copy
Columns("E:E").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
[/vba]


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеКонечно[vba]
Код
    Application.Calculation = xlCalculationManual
[/vba]
И Ваш код можно переписать без Селектов (в большинстве случаев они не нужны и, более того, тормозят)
[vba]
Код
Columns("C:C").Copy
Columns("C:C").PasteSpecial Paste:=xlPasteValues
Columns("E:E").Copy
Columns("E:E").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
[/vba]

Автор - _Boroda_
Дата добавления - 17.03.2016 в 22:53
iddqd Дата: Четверг, 17.03.2016, 23:21 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
И Ваш код можно переписать без Селектов (в большинстве случаев они не нужны и, более того, тормозят)

Спасибо. (Вы спите вообще?) :)

Это не код, а запись действий в макрос :) Я ж новичок - макросы пишу, когда сильно припрёт.

А включать пересчёт после вставки всех формул так?

[vba]
Код
Application.Calculation = xlCalculationAuto
[/vba]

Или значение возвращается сразу после вставки формул? Я ж потом эти значения использую для проверки условий.

____________________

Всё равно тормозит :( Эх, надо словари изучать... как их к моему случаю прикрутить?


Привет всем участникам Броуновского движения!

Сообщение отредактировал iddqd - Четверг, 17.03.2016, 23:25
 
Ответить
Сообщение
И Ваш код можно переписать без Селектов (в большинстве случаев они не нужны и, более того, тормозят)

Спасибо. (Вы спите вообще?) :)

Это не код, а запись действий в макрос :) Я ж новичок - макросы пишу, когда сильно припрёт.

А включать пересчёт после вставки всех формул так?

[vba]
Код
Application.Calculation = xlCalculationAuto
[/vba]

Или значение возвращается сразу после вставки формул? Я ж потом эти значения использую для проверки условий.

____________________

Всё равно тормозит :( Эх, надо словари изучать... как их к моему случаю прикрутить?

Автор - iddqd
Дата добавления - 17.03.2016 в 23:21
_Boroda_ Дата: Четверг, 17.03.2016, 23:29 | Сообщение № 11
Группа: Модераторы
Ранг: Экселист
Сообщений: 9362
Репутация: 3936 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Пожалуйста. (Дык рано еще).
Я догадался. Только после автозаписи макросы лучше вручную допиливать.
Да, так.
Не, не возвращается. Включать нужно. Или однократно пересчитывать лист
[vba]
Код
Application.Calculate
[/vba]Но потом все равно лучше включить, чтобы не забыть.
-----------------
Без Вашего файла (или его куска) помочь сложно. Но по-любому проверять задвоения ПОИСКПОЗом (или ВПРом) - вряд ли лучший выход.
А про словари - не гоните лошадей. Какие словари, если Вы еще с Селектами не разобрались?


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПожалуйста. (Дык рано еще).
Я догадался. Только после автозаписи макросы лучше вручную допиливать.
Да, так.
Не, не возвращается. Включать нужно. Или однократно пересчитывать лист
[vba]
Код
Application.Calculate
[/vba]Но потом все равно лучше включить, чтобы не забыть.
-----------------
Без Вашего файла (или его куска) помочь сложно. Но по-любому проверять задвоения ПОИСКПОЗом (или ВПРом) - вряд ли лучший выход.
А про словари - не гоните лошадей. Какие словари, если Вы еще с Селектами не разобрались?

Автор - _Boroda_
Дата добавления - 17.03.2016 в 23:29
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Сравнить два столбца и отметить уникальные значения в каждом (Макросы/Sub)
Страница 1 из 11
Поиск:

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