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

Вход

Регистрация

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

 

= Мир MS Excel/Сравнение значений в ячейках и выделение их - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Сравнение значений в ячейках и выделение их
rever27 Дата: Вторник, 02.06.2015, 17:44 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 141
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Здравствуйте.

Подскажите, нужно выполнить сравнение данных в таблице и при нахождении схожих(примерно равных) результатов в колонках Q, R, U выделить обе строчки одним цветом
Пример привел в строках 44 и 45, где в колонке Q разница должна быть не более 15, в R - не более 2 (для каждой колонки свое настраиваемое значение)

К сообщению приложен файл: 3428829.xlsx (66.6 Kb)
 
Ответить
СообщениеЗдравствуйте.

Подскажите, нужно выполнить сравнение данных в таблице и при нахождении схожих(примерно равных) результатов в колонках Q, R, U выделить обе строчки одним цветом
Пример привел в строках 44 и 45, где в колонке Q разница должна быть не более 15, в R - не более 2 (для каждой колонки свое настраиваемое значение)


Автор - rever27
Дата добавления - 02.06.2015 в 17:44
KSV Дата: Вторник, 02.06.2015, 18:21 | Сообщение № 2
Группа: Друзья
Ранг: Ветеран
Сообщений: 770
Репутация: 255 ±
Замечаний: 0% ±

Excel 2013
см. вложенный файл
К сообщению приложен файл: 3428829.xlsm (69.3 Kb)


KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333
 
Ответить
Сообщениесм. вложенный файл

Автор - KSV
Дата добавления - 02.06.2015 в 18:21
rever27 Дата: Вторник, 02.06.2015, 19:39 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 141
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Вместо или И поставил.
Но, как я понимаю, проверка идет только на те строки, что рядом друг с другом
Как сверить все данные в таблице? Предварительной сортировкой по каждой из колонок?

Еще есть условие на сравнение числа в составе текста. Его делать через Split?
К сообщению приложен файл: 7045634.xlsm (64.7 Kb)
 
Ответить
СообщениеВместо или И поставил.
Но, как я понимаю, проверка идет только на те строки, что рядом друг с другом
Как сверить все данные в таблице? Предварительной сортировкой по каждой из колонок?

Еще есть условие на сравнение числа в составе текста. Его делать через Split?

Автор - rever27
Дата добавления - 02.06.2015 в 19:39
KSV Дата: Среда, 03.06.2015, 10:24 | Сообщение № 4
Группа: Друзья
Ранг: Ветеран
Сообщений: 770
Репутация: 255 ±
Замечаний: 0% ±

Excel 2013
На самом деле, вариантов может быть много...
1. решение "в лоб" (самое не оптимальное) - для сравнения каждого значения использовать вложенный цикл, в котором сравнивать его со всеми другими значениями, кроме себя. (плюсы - легко реализовать, ничего не нужно думать/придумывать, минусы - огромное кол-во проходов! т.е., если у вас 1000 записей в таблице, то кол-во проходов будет = 1000 * 1000)
2. (подходит, если нужно найти совпадение ПО ОДНОМУ из ваших условий, т.е. ИЛИ, а для И (т.е. при совпадении только ВСЕХ условий) уже не подойдет) - перед сравнением сортировать по одному из столбцов и проводить сравнение по одному условию, касающемуся сравнению данных по этому отсортированному столбцу. и так для каждого условия. (т.е. кол-во проходов будет = 1000 * 3 (кол-во условий) + еще 3 относительно медленных операции - сортировки.
3. (как вариант) если данные в столбцах, по которым нужно сравнивать, получаются путем вычислений из какого-то одного столбца, то имеет смысл сравнивать именно по нему, а не по его производным.

[p.s.]Но в любом случае, сначала считывать все в массив, а потом в цикле сравнивать элементы массива, а не у каждой ячейки читать свойство .Value, что в разы медленнее...

Вместо или И поставил.

вот это не понял

Еще есть условие на сравнение числа в составе текста. Его делать через Split?

можно и через сплит, а можно искать позицию "=" через InstrRev("StopLoss=25","="), не знаю что быстрее - нужно проверять[/p.s.]


KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333


Сообщение отредактировал KSV - Среда, 03.06.2015, 10:27
 
Ответить
СообщениеНа самом деле, вариантов может быть много...
1. решение "в лоб" (самое не оптимальное) - для сравнения каждого значения использовать вложенный цикл, в котором сравнивать его со всеми другими значениями, кроме себя. (плюсы - легко реализовать, ничего не нужно думать/придумывать, минусы - огромное кол-во проходов! т.е., если у вас 1000 записей в таблице, то кол-во проходов будет = 1000 * 1000)
2. (подходит, если нужно найти совпадение ПО ОДНОМУ из ваших условий, т.е. ИЛИ, а для И (т.е. при совпадении только ВСЕХ условий) уже не подойдет) - перед сравнением сортировать по одному из столбцов и проводить сравнение по одному условию, касающемуся сравнению данных по этому отсортированному столбцу. и так для каждого условия. (т.е. кол-во проходов будет = 1000 * 3 (кол-во условий) + еще 3 относительно медленных операции - сортировки.
3. (как вариант) если данные в столбцах, по которым нужно сравнивать, получаются путем вычислений из какого-то одного столбца, то имеет смысл сравнивать именно по нему, а не по его производным.

[p.s.]Но в любом случае, сначала считывать все в массив, а потом в цикле сравнивать элементы массива, а не у каждой ячейки читать свойство .Value, что в разы медленнее...

Вместо или И поставил.

вот это не понял

Еще есть условие на сравнение числа в составе текста. Его делать через Split?

можно и через сплит, а можно искать позицию "=" через InstrRev("StopLoss=25","="), не знаю что быстрее - нужно проверять[/p.s.]

Автор - KSV
Дата добавления - 03.06.2015 в 10:24
rever27 Дата: Среда, 03.06.2015, 14:26 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 141
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Третий вариант отпадает, потому что все данные, как есть, берутся из другой программы.
Совпадения нужны по всем сразу условиям, для этого у них и задается числовой диапазон разницы значений.

Попытался сделать с сортировкой(последовательно по 3м столбцам), но так и не понял, почему при второй сортировке он не перекрашивает цвет уже совпавших строчек.
Так же проблема этого второго варианта в том, что после выделения строк, происходил дальнейшая сортировка ,после которой не понять, какие значения с какими совпали до того. Тут нужно либо каждой паре(или более) совпавших строк давать свой индивидуальный цвет, либо после выделения отсортировывать их по цвету в вверх/вниз таблицы и более не учитывать при дальнейшей сортировке (первый был бы более наглядный).
Строк в сумме не более 2000. Вам виднее, сколько времени на сортировку такого количества понадобится, возможно первый вариант и выход.

А по поводу Split, как не пытался делать по вашему предыдущему решению, так и не получилось включить его в макрос (
К сообщению приложен файл: 0058992.xlsm (72.6 Kb)
 
Ответить
СообщениеТретий вариант отпадает, потому что все данные, как есть, берутся из другой программы.
Совпадения нужны по всем сразу условиям, для этого у них и задается числовой диапазон разницы значений.

Попытался сделать с сортировкой(последовательно по 3м столбцам), но так и не понял, почему при второй сортировке он не перекрашивает цвет уже совпавших строчек.
Так же проблема этого второго варианта в том, что после выделения строк, происходил дальнейшая сортировка ,после которой не понять, какие значения с какими совпали до того. Тут нужно либо каждой паре(или более) совпавших строк давать свой индивидуальный цвет, либо после выделения отсортировывать их по цвету в вверх/вниз таблицы и более не учитывать при дальнейшей сортировке (первый был бы более наглядный).
Строк в сумме не более 2000. Вам виднее, сколько времени на сортировку такого количества понадобится, возможно первый вариант и выход.

А по поводу Split, как не пытался делать по вашему предыдущему решению, так и не получилось включить его в макрос (

Автор - rever27
Дата добавления - 03.06.2015 в 14:26
rever27 Дата: Пятница, 05.06.2015, 09:39 | Сообщение № 6
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 141
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
KSV, у вас будет решение этой проблемы? ^_^
 
Ответить
СообщениеKSV, у вас будет решение этой проблемы? ^_^

Автор - rever27
Дата добавления - 05.06.2015 в 09:39
KSV Дата: Пятница, 05.06.2015, 14:23 | Сообщение № 7
Группа: Друзья
Ранг: Ветеран
Сообщений: 770
Репутация: 255 ±
Замечаний: 0% ±

Excel 2013
проблема этого второго варианта в том

Я же сразу написал:
2. (подходит, если нужно найти совпадение ПО ОДНОМУ из ваших условий, т.е. ИЛИ, а для И (т.е. при совпадении только ВСЕХ условий) уже не подойдет)

вернее, можно сделать по 2-му варианту и для И, но заморочнее...

возможно первый вариант и выход

Я думал вы уже сделали по первому варианту, и не парюсь :) (т.к. для 2000 строк и первый должен отработать относительно быстро)

у вас будет решение

как вариант (см. вложенный файл)
К сообщению приложен файл: 3725195.xlsm (68.8 Kb)


KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333


Сообщение отредактировал KSV - Пятница, 05.06.2015, 14:24
 
Ответить
Сообщение
проблема этого второго варианта в том

Я же сразу написал:
2. (подходит, если нужно найти совпадение ПО ОДНОМУ из ваших условий, т.е. ИЛИ, а для И (т.е. при совпадении только ВСЕХ условий) уже не подойдет)

вернее, можно сделать по 2-му варианту и для И, но заморочнее...

возможно первый вариант и выход

Я думал вы уже сделали по первому варианту, и не парюсь :) (т.к. для 2000 строк и первый должен отработать относительно быстро)

у вас будет решение

как вариант (см. вложенный файл)

Автор - KSV
Дата добавления - 05.06.2015 в 14:23
rever27 Дата: Пятница, 05.06.2015, 19:15 | Сообщение № 8
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 141
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
KSV, Я бы с радостью сделал сам, но не понимаю и половины вашего код.
Так и не смог прикрутить к нему этот фильтр не с 40, а с текущей активной ячейки.

Но это не важно. Большая проблема в том, что значений очень много и все они одного цвета, и не поймешь, какие строки с какими схожи.
Возможно ли делать выделение каждой последующей проходки рандомным цветом? или сортировать полученные значения по цвету и по порядку?
 
Ответить
СообщениеKSV, Я бы с радостью сделал сам, но не понимаю и половины вашего код.
Так и не смог прикрутить к нему этот фильтр не с 40, а с текущей активной ячейки.

Но это не важно. Большая проблема в том, что значений очень много и все они одного цвета, и не поймешь, какие строки с какими схожи.
Возможно ли делать выделение каждой последующей проходки рандомным цветом? или сортировать полученные значения по цвету и по порядку?

Автор - rever27
Дата добавления - 05.06.2015 в 19:15
KSV Дата: Пятница, 05.06.2015, 22:25 | Сообщение № 9
Группа: Друзья
Ранг: Ветеран
Сообщений: 770
Репутация: 255 ±
Замечаний: 0% ±

Excel 2013
выделение каждой последующей проходки рандомным цветом

Вы думаете, рандомные цвета не повторяются? :)

не понимаю и половины вашего код

каждую строчку прокомментировал.
К сообщению приложен файл: 5872009.xlsm (70.3 Kb)


KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333
 
Ответить
Сообщение
выделение каждой последующей проходки рандомным цветом

Вы думаете, рандомные цвета не повторяются? :)

не понимаю и половины вашего код

каждую строчку прокомментировал.

Автор - KSV
Дата добавления - 05.06.2015 в 22:25
KSV Дата: Пятница, 05.06.2015, 22:40 | Сообщение № 10
Группа: Друзья
Ранг: Ветеран
Сообщений: 770
Репутация: 255 ±
Замечаний: 0% ±

Excel 2013
Так и не смог прикрутить к нему этот фильтр не с 40, а с текущей активной ячейки.

переделал, как вы хотите.
(процедуру очистки тоже переделал, чтоб начинала от текущей ячейки)
К сообщению приложен файл: 5635806.xlsm (70.7 Kb)


KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333
 
Ответить
Сообщение
Так и не смог прикрутить к нему этот фильтр не с 40, а с текущей активной ячейки.

переделал, как вы хотите.
(процедуру очистки тоже переделал, чтоб начинала от текущей ячейки)

Автор - KSV
Дата добавления - 05.06.2015 в 22:40
rever27 Дата: Суббота, 06.06.2015, 00:06 | Сообщение № 11
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 141
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
KSV, Стало куда лучше! Спасибо ^_^
Я еще использую для ускорения работы вот эти выключатели.
[vba]
Код

  Application.ScreenUpdating = False
Application.EnableEvents = False
[/vba]

Цвет поставил
[vba]
Код
Interior.Color = 13995347
[/vba]
Он более плавно меняется, при e = e + 1000

Но, видно идеала все равно не добьюсь ))
Т.к. выделение одним цветом идет только 2х строчек - i,n. Если У меня 10 строчек, которые все схожи между собой(с минимальной разницей), то будет все равно 5 разных цветов.
У других 10 строчек, схожих другими значениями тоже будут иметь 5 разных цветов. А суть всего этого в том, чтобы найти эти 20(10+10) строчек, увидеть, что они идентичны, и руками уже удалить 9 из 10 (к примеру)

Кстати, подскажите, как вы определяете нулевые, не пустые ячейки на листе?
 
Ответить
СообщениеKSV, Стало куда лучше! Спасибо ^_^
Я еще использую для ускорения работы вот эти выключатели.
[vba]
Код

  Application.ScreenUpdating = False
Application.EnableEvents = False
[/vba]

Цвет поставил
[vba]
Код
Interior.Color = 13995347
[/vba]
Он более плавно меняется, при e = e + 1000

Но, видно идеала все равно не добьюсь ))
Т.к. выделение одним цветом идет только 2х строчек - i,n. Если У меня 10 строчек, которые все схожи между собой(с минимальной разницей), то будет все равно 5 разных цветов.
У других 10 строчек, схожих другими значениями тоже будут иметь 5 разных цветов. А суть всего этого в том, чтобы найти эти 20(10+10) строчек, увидеть, что они идентичны, и руками уже удалить 9 из 10 (к примеру)

Кстати, подскажите, как вы определяете нулевые, не пустые ячейки на листе?

Автор - rever27
Дата добавления - 06.06.2015 в 00:06
KSV Дата: Суббота, 06.06.2015, 01:27 | Сообщение № 12
Группа: Друзья
Ранг: Ветеран
Сообщений: 770
Репутация: 255 ±
Замечаний: 0% ±

Excel 2013
Но, видно идеала все равно не добьюсь ))

Добьетесь, если разберетесь, чего именно хотите в конечном итоге и составите четкий алгоритм, как к этому прийти.

Т.к. выделение одним цветом идет только 2х строчек - i,n.

Вы, видимо, плохо прочли комментарии или не поняли их (может, я плохо объяснил)...
i - индекс контролируемой строки, значения которой мы сравниваем со значениями других строк.
n - индекс строки, с которой сравниваем.
теперь посмотрите внимательно код - мы НЕ выходим из внутреннего цикла, после совпадения условий, а только окрашиваем найденную строку, И ПРОДОЛЖАЕМ искать другие строки, совпадающие по условию. А значит, если совпадающих по условиям строк будет 10, то во внутреннем цикле мы 9 раз попадем на окрашивание строк (при 9-ти разных значениях n), при одном и том же значении i.
Т.е., все именно так, как вы и хотели ранее.

Если У меня 10 строчек, которые все схожи между собой(с минимальной разницей), то будет все равно 5 разных цветов

Так может происходить из-за того, что ...
Лучше "на пальцах" и с одним условием, чтоб было понятнее...
Условие - разница между значениями не более 10.
Имеем 3 строки со значениями:
1 - 15
2 - 25
3 - 35
Теперь смотрите: на первом шаге i=1, n=2 - их значения попадают под условия и мы окрашиваем обе эти строки в красный цвет, на втором шаге i=1, n=3 - их значения не попадают под условия и мы ничего не делаем, на третьем шаге i=2, n=3 - их значения снова попадают под условия и мы окрашиваем обе эти строки в зеленый цвет. Что вы увидите в итоге?
1 - 15 - красная
2 - 25 - зеленая
3 - 35 - зеленая
и вот вам строки (1 и 2), попадающие под условие, но имеющие разный цвет!
Можно добавить еще и проверку цвета заливки и, при совпадении условий, окрашивать только "белые" ячейки (т.е., окрашивания в зеленый цвет на шаге 3 не произойдет), но тогда вы скажите, почему значения в строках 2 и 3 попадают под условия, но имеют разный цвет?!
Поэтому и нужен четкий алгоритм, и никто лучше вас не знает как должно быть "правильнее"...


KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333
 
Ответить
Сообщение
Но, видно идеала все равно не добьюсь ))

Добьетесь, если разберетесь, чего именно хотите в конечном итоге и составите четкий алгоритм, как к этому прийти.

Т.к. выделение одним цветом идет только 2х строчек - i,n.

Вы, видимо, плохо прочли комментарии или не поняли их (может, я плохо объяснил)...
i - индекс контролируемой строки, значения которой мы сравниваем со значениями других строк.
n - индекс строки, с которой сравниваем.
теперь посмотрите внимательно код - мы НЕ выходим из внутреннего цикла, после совпадения условий, а только окрашиваем найденную строку, И ПРОДОЛЖАЕМ искать другие строки, совпадающие по условию. А значит, если совпадающих по условиям строк будет 10, то во внутреннем цикле мы 9 раз попадем на окрашивание строк (при 9-ти разных значениях n), при одном и том же значении i.
Т.е., все именно так, как вы и хотели ранее.

Если У меня 10 строчек, которые все схожи между собой(с минимальной разницей), то будет все равно 5 разных цветов

Так может происходить из-за того, что ...
Лучше "на пальцах" и с одним условием, чтоб было понятнее...
Условие - разница между значениями не более 10.
Имеем 3 строки со значениями:
1 - 15
2 - 25
3 - 35
Теперь смотрите: на первом шаге i=1, n=2 - их значения попадают под условия и мы окрашиваем обе эти строки в красный цвет, на втором шаге i=1, n=3 - их значения не попадают под условия и мы ничего не делаем, на третьем шаге i=2, n=3 - их значения снова попадают под условия и мы окрашиваем обе эти строки в зеленый цвет. Что вы увидите в итоге?
1 - 15 - красная
2 - 25 - зеленая
3 - 35 - зеленая
и вот вам строки (1 и 2), попадающие под условие, но имеющие разный цвет!
Можно добавить еще и проверку цвета заливки и, при совпадении условий, окрашивать только "белые" ячейки (т.е., окрашивания в зеленый цвет на шаге 3 не произойдет), но тогда вы скажите, почему значения в строках 2 и 3 попадают под условия, но имеют разный цвет?!
Поэтому и нужен четкий алгоритм, и никто лучше вас не знает как должно быть "правильнее"...

Автор - KSV
Дата добавления - 06.06.2015 в 01:27
rever27 Дата: Суббота, 06.06.2015, 14:08 | Сообщение № 13
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 141
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Вы очень подробно объясняете. Наверное проблема в моем изложении ситуации.
Попробую еще раз )
Есть таблица с данными из вне. Они приходят постоянно и их нужно фильтровать, отбирая из 1000-2000 лучшие варианты, сводя таблицу до 10-50 строк.
Фильтрацию я делаю другими макросами на удаления значений по критерию. Но остаются именно те значения, которые не имеют общего, четко выраженного сходства, лишь небольшое различие в цифрах. Их то мы с Вами и пытаемся выделить одним цветом, чтобы потом можно было глазами сравнить их и принять решение по удалению дубликатов, оставив 1 строку одного цвета.

Давайте рассмотрим по примерам.
3 строки. Все значения одинаковые, кроме колонки Q. Должны быть все одного цвета, т.к. общая разница между ними не превышает 30.
Но они разного, потому что после окраски их красного цвета, i перешла на 41 строку и окрасила ниже оставшиеся зеленым. Наверное, нужно исключать из дальнейшего окраса значения, которые уже не белые. Тогда при условии разницы в 30 ситуация будет такая
1 - 397.68 - красная
2 - 377.68 - красная
3 - 367.68 - красная
4 - 357.68 - белая
А потом уже, когда я удалю руками, допустим 1 и 3 вариант, у меня останется 2 значения, которые после повторного включения макроса благополучно покрасятся красным
2 - 377.68 - красная
4 - 357.68 - красная

Так же вопрос, как можно сделать после нахождения полученных значений сортировку всех полученных результатов по цвету?

И третий вопрос, как можно вынести числовые значения в отдельную переменную. Допустим, чтобы в самом верху макросы было а=30, в=20 и т.п. Изменяя их менялись бы параметры фильтра.
И четвертый ) Возможно ли осуществлять перед фильтр поиск текстового значения. Допустим, если в нашей ячейке нет слова StopLoss, то мы и не фильтруем.
К сообщению приложен файл: 6838638.xlsm (74.0 Kb)


Сообщение отредактировал rever27 - Суббота, 06.06.2015, 16:29
 
Ответить
СообщениеВы очень подробно объясняете. Наверное проблема в моем изложении ситуации.
Попробую еще раз )
Есть таблица с данными из вне. Они приходят постоянно и их нужно фильтровать, отбирая из 1000-2000 лучшие варианты, сводя таблицу до 10-50 строк.
Фильтрацию я делаю другими макросами на удаления значений по критерию. Но остаются именно те значения, которые не имеют общего, четко выраженного сходства, лишь небольшое различие в цифрах. Их то мы с Вами и пытаемся выделить одним цветом, чтобы потом можно было глазами сравнить их и принять решение по удалению дубликатов, оставив 1 строку одного цвета.

Давайте рассмотрим по примерам.
3 строки. Все значения одинаковые, кроме колонки Q. Должны быть все одного цвета, т.к. общая разница между ними не превышает 30.
Но они разного, потому что после окраски их красного цвета, i перешла на 41 строку и окрасила ниже оставшиеся зеленым. Наверное, нужно исключать из дальнейшего окраса значения, которые уже не белые. Тогда при условии разницы в 30 ситуация будет такая
1 - 397.68 - красная
2 - 377.68 - красная
3 - 367.68 - красная
4 - 357.68 - белая
А потом уже, когда я удалю руками, допустим 1 и 3 вариант, у меня останется 2 значения, которые после повторного включения макроса благополучно покрасятся красным
2 - 377.68 - красная
4 - 357.68 - красная

Так же вопрос, как можно сделать после нахождения полученных значений сортировку всех полученных результатов по цвету?

И третий вопрос, как можно вынести числовые значения в отдельную переменную. Допустим, чтобы в самом верху макросы было а=30, в=20 и т.п. Изменяя их менялись бы параметры фильтра.
И четвертый ) Возможно ли осуществлять перед фильтр поиск текстового значения. Допустим, если в нашей ячейке нет слова StopLoss, то мы и не фильтруем.

Автор - rever27
Дата добавления - 06.06.2015 в 14:08
KSV Дата: Суббота, 06.06.2015, 16:46 | Сообщение № 14
Группа: Друзья
Ранг: Ветеран
Сообщений: 770
Репутация: 255 ±
Замечаний: 0% ±

Excel 2013
Наверное, нужно исключать из дальнейшего окраса значения, которые уже не белые.

[vba]
Код
' поменяйте вот эту часть кода:
                 If (Abs(v(i, 1) - v(n, 1)) <= 30) And (Abs(v(i, 2) - v(n, 2)) <= 2) And (Abs(v(i, 5) - v(n, 5)) <= 30) And (Abs(a - b) <= 5) Then _
                     .Rows(i).Interior.Color = c: _
                     .Rows(n).Interior.Color = c: _
                     .Rows(i).Font.Bold = True: _
                     .Rows(n).Font.Bold = True: _
                     f = 1

' на эту:
                 If (Abs(v(i, 1) - v(n, 1)) <= 30) And (Abs(v(i, 2) - v(n, 2)) <= 2) And (Abs(v(i, 5) - v(n, 5)) <= 30) And (Abs(a - b) <= 5) Then _
                     If .Cells(n, 1).Interior.Pattern = xlNone Then _
                         .Rows(i).Interior.Color = c: _
                         .Rows(n).Interior.Color = c: _
                         .Rows(i).Font.Bold = True: _
                         .Rows(n).Font.Bold = True: _
                         f = 1
[/vba]
которые после повторного включения макроса благополучно покрасятся красным

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


KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333
 
Ответить
Сообщение
Наверное, нужно исключать из дальнейшего окраса значения, которые уже не белые.

[vba]
Код
' поменяйте вот эту часть кода:
                 If (Abs(v(i, 1) - v(n, 1)) <= 30) And (Abs(v(i, 2) - v(n, 2)) <= 2) And (Abs(v(i, 5) - v(n, 5)) <= 30) And (Abs(a - b) <= 5) Then _
                     .Rows(i).Interior.Color = c: _
                     .Rows(n).Interior.Color = c: _
                     .Rows(i).Font.Bold = True: _
                     .Rows(n).Font.Bold = True: _
                     f = 1

' на эту:
                 If (Abs(v(i, 1) - v(n, 1)) <= 30) And (Abs(v(i, 2) - v(n, 2)) <= 2) And (Abs(v(i, 5) - v(n, 5)) <= 30) And (Abs(a - b) <= 5) Then _
                     If .Cells(n, 1).Interior.Pattern = xlNone Then _
                         .Rows(i).Interior.Color = c: _
                         .Rows(n).Interior.Color = c: _
                         .Rows(i).Font.Bold = True: _
                         .Rows(n).Font.Bold = True: _
                         f = 1
[/vba]
которые после повторного включения макроса благополучно покрасятся красным

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

Автор - KSV
Дата добавления - 06.06.2015 в 16:46
KSV Дата: Суббота, 06.06.2015, 17:16 | Сообщение № 15
Группа: Друзья
Ранг: Ветеран
Сообщений: 770
Репутация: 255 ±
Замечаний: 0% ±

Excel 2013
не, нужно также проверять заливку ячеек и строки с индексом i (исправленный пример во вложенном файле)

Так же вопрос, как можно сделать после нахождения полученных значений сортировку всех полученных результатов по цвету?

см. в файле функцию SortByColor()
К сообщению приложен файл: 6266481.xlsm (72.4 Kb)


KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333


Сообщение отредактировал KSV - Суббота, 06.06.2015, 18:23
 
Ответить
Сообщениене, нужно также проверять заливку ячеек и строки с индексом i (исправленный пример во вложенном файле)

Так же вопрос, как можно сделать после нахождения полученных значений сортировку всех полученных результатов по цвету?

см. в файле функцию SortByColor()

Автор - KSV
Дата добавления - 06.06.2015 в 17:16
KSV Дата: Суббота, 06.06.2015, 22:07 | Сообщение № 16
Группа: Друзья
Ранг: Ветеран
Сообщений: 770
Репутация: 255 ±
Замечаний: 0% ±

Excel 2013
как можно вынести числовые значения в отдельную переменную

если в нашей ячейке нет слова StopLoss, то мы и не фильтруем

так?
К сообщению приложен файл: 4538447.xlsm (73.6 Kb)


KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333
 
Ответить
Сообщение
как можно вынести числовые значения в отдельную переменную

если в нашей ячейке нет слова StopLoss, то мы и не фильтруем

так?

Автор - KSV
Дата добавления - 06.06.2015 в 22:07
rever27 Дата: Понедельник, 08.06.2015, 00:41 | Сообщение № 17
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 141
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
KSV, Весь день сидел за этим кодом, в массивах так до конца и не разобрался.
Решил немного изменить внешний вид фильтрации.
От чего возникло еще пару вопросов, если не против ))
1) Как можно закрепить кнопку непосредственно в ячейку?
2) мы с Вами рассматривали пример фильтра по 4 колонкам. Можно ли сделать фильтр по всем заполненным колонкам, где значения в строке 8 = ""?
3) Так и не понял, как правильно сделать Сообщение, о том, что "совпадений не найдено"
4) Надеюсь, мой "код" не сильно напугает ))))

Редактирование:
Изменил немного код, сделал более рабочим
К сообщению приложен файл: 8234763.xlsm (54.8 Kb)


Сообщение отредактировал rever27 - Понедельник, 08.06.2015, 12:04
 
Ответить
СообщениеKSV, Весь день сидел за этим кодом, в массивах так до конца и не разобрался.
Решил немного изменить внешний вид фильтрации.
От чего возникло еще пару вопросов, если не против ))
1) Как можно закрепить кнопку непосредственно в ячейку?
2) мы с Вами рассматривали пример фильтра по 4 колонкам. Можно ли сделать фильтр по всем заполненным колонкам, где значения в строке 8 = ""?
3) Так и не понял, как правильно сделать Сообщение, о том, что "совпадений не найдено"
4) Надеюсь, мой "код" не сильно напугает ))))

Редактирование:
Изменил немного код, сделал более рабочим

Автор - rever27
Дата добавления - 08.06.2015 в 00:41
KSV Дата: Суббота, 13.06.2015, 14:46 | Сообщение № 18
Группа: Друзья
Ранг: Ветеран
Сообщений: 770
Репутация: 255 ±
Замечаний: 0% ±

Excel 2013
1)
[vba]
Код
        With Range("B8:I8")
               With ActiveSheet.Buttons.Add(.Left, .Top, .Width, .Height)
                   .OnAction = "b_SimilarFilterClear"
                   .Characters.Text = "Clear Color Filter"
                   .Font.Bold = True
               End With
           End With
[/vba]
2) да :) (добавьте еще условия, аналогично имеющимся)

3) меняете местами строки
[vba]
Код
                j = j + 1: _
                   If Cells(n, 17).Interior.Pattern = xlNone Then _
[/vba]
и после цикла пишите
[vba]
Код
    If j = 0 Then MsgBox "Позиций не найдено", vbInformation, "Фильтр по схожим значениям"
[/vba]
и верните на место сброс "флага" перед циклом
[vba]
Код
        f = 0
           For n = i + 1 To TheEnd
[/vba]
4) видел и страшнее :)


KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333


Сообщение отредактировал KSV - Суббота, 13.06.2015, 14:52
 
Ответить
Сообщение1)
[vba]
Код
        With Range("B8:I8")
               With ActiveSheet.Buttons.Add(.Left, .Top, .Width, .Height)
                   .OnAction = "b_SimilarFilterClear"
                   .Characters.Text = "Clear Color Filter"
                   .Font.Bold = True
               End With
           End With
[/vba]
2) да :) (добавьте еще условия, аналогично имеющимся)

3) меняете местами строки
[vba]
Код
                j = j + 1: _
                   If Cells(n, 17).Interior.Pattern = xlNone Then _
[/vba]
и после цикла пишите
[vba]
Код
    If j = 0 Then MsgBox "Позиций не найдено", vbInformation, "Фильтр по схожим значениям"
[/vba]
и верните на место сброс "флага" перед циклом
[vba]
Код
        f = 0
           For n = i + 1 To TheEnd
[/vba]
4) видел и страшнее :)

Автор - KSV
Дата добавления - 13.06.2015 в 14:46
rever27 Дата: Суббота, 27.06.2015, 12:16 | Сообщение № 19
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 141
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Ув. KSV, Как я не крутил этот макрос, но скорость выполнения так и осталась жутко медленной. (от 2000 строк более 2х минут)
Подскажи, может быть вы знаете варианты ускорения?
Мне в голову пришла идея ввести еще один цикл сортировки по колонкам, только не знаю, как реализовать (
Т.е. у нас есть условие:
[vba]
Код
If (Abs(Cells(i, 17) - Cells(n, 17)) <= x1) _
                 And (Abs(Cells(i, 18) - Cells(n, 18)) <= x2) _
                 And (Abs(Cells(i, 21) - Cells(n, 21)) <= x3) _
                 And (Abs(Val(a) - Val(b)) <= x5) _
                 And (Abs(Val(c) - Val(d)) <= x5) Then
[/vba]
Если перед ним сделать вначале сортировку по колонке 17 и сравнить Abs(Cells(i, 17) - Cells(n, 17)) <= x1, когда доходим до строки, где оно не выполняется - завершаем циклы i и n, далее сортируем по колонке 18, и так же проверяем условие Abs(Cells(i, 18) - Cells(n, 18)) <= x2, ну и до конца.
Тем самым мы останавливаем кучу лишних проверок, но добавляем еще 1 цикл.
Что думаете?
 
Ответить
Сообщение Ув. KSV, Как я не крутил этот макрос, но скорость выполнения так и осталась жутко медленной. (от 2000 строк более 2х минут)
Подскажи, может быть вы знаете варианты ускорения?
Мне в голову пришла идея ввести еще один цикл сортировки по колонкам, только не знаю, как реализовать (
Т.е. у нас есть условие:
[vba]
Код
If (Abs(Cells(i, 17) - Cells(n, 17)) <= x1) _
                 And (Abs(Cells(i, 18) - Cells(n, 18)) <= x2) _
                 And (Abs(Cells(i, 21) - Cells(n, 21)) <= x3) _
                 And (Abs(Val(a) - Val(b)) <= x5) _
                 And (Abs(Val(c) - Val(d)) <= x5) Then
[/vba]
Если перед ним сделать вначале сортировку по колонке 17 и сравнить Abs(Cells(i, 17) - Cells(n, 17)) <= x1, когда доходим до строки, где оно не выполняется - завершаем циклы i и n, далее сортируем по колонке 18, и так же проверяем условие Abs(Cells(i, 18) - Cells(n, 18)) <= x2, ну и до конца.
Тем самым мы останавливаем кучу лишних проверок, но добавляем еще 1 цикл.
Что думаете?

Автор - rever27
Дата добавления - 27.06.2015 в 12:16
KSV Дата: Суббота, 27.06.2015, 16:38 | Сообщение № 20
Группа: Друзья
Ранг: Ветеран
Сообщений: 770
Репутация: 255 ±
Замечаний: 0% ±

Excel 2013
Ускорить можно несколькими способами или даже совокупностью этих нескольких способов, но я практически уверен, что для ваших 2 тыс. строк (всего лишь) будет достаточно и самого простого способа, который я вам предлагал, начиная с моего самого первого ответа в этой теме (посмотрите любой из моих вложенных файлов, например здесь, в процедуре Sample1, к тому же там каждая строка прокомментирована) - там весь нужный диапазон сначала считывается в массив, а потом в цикле сравниваются уже значения из этого массива, а не обращается к каждой ячейке листа отдельно, как у вас. Это увеличивает скорость в разы, если не в десятки раз (я вам об этом уже недавно писал в одной из ваших тем). А если вы "теряетесь" в смещениях в массиве - сделайте проще! Считайте в массив не только нужный диапазон, а ВЕСЬ ваш рабочий диапазон (например, A1:AX2000), понятно, что при этом вы будете нерационально использовать память, т.к в массиве будет много лишнего (шапка таблицы и другие неиспользующиеся данные), но тогда вы можете не вникать в смещения и в цикле к элементам массива обращаться по тем же адресам, что и к ячейкам листа, т.е., элемент v(1,1) будет содержать значение ячейки Cells(1,1), т.е. ячейки A1, а элемент v(7,2) - значение ячейки Cells(7,2), т.е. ячейки B7, и т.д., и из-за того, что работа с массивом ведется в памяти, скорость выполнения увеличится в несколько раз.
А в цикле потом ВЕЗДЕ (а не только в строке сравнения), где запрашиваются значения ячеек, просто замените Cells на v:
[vba]
Код
If (Abs(v(i, 17) - v(n, 17)) <= x1) _
                  And (Abs(v(i, 18) - v(n, 18)) <= x2) _
                  And (Abs(v(i, 21) - v(n, 21)) <= x3) _
                  And (Abs(Val(a) - Val(b)) <= x5) _
                  And (Abs(Val(c) - Val(d)) <= x5) Then
[/vba]
KSV, Весь день сидел за этим кодом, в массивах так до конца и не разобрался.
Решил немного изменить внешний вид фильтрации.

заодно, это поможет вам разобраться с массивами


KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333


Сообщение отредактировал KSV - Суббота, 27.06.2015, 16:42
 
Ответить
СообщениеУскорить можно несколькими способами или даже совокупностью этих нескольких способов, но я практически уверен, что для ваших 2 тыс. строк (всего лишь) будет достаточно и самого простого способа, который я вам предлагал, начиная с моего самого первого ответа в этой теме (посмотрите любой из моих вложенных файлов, например здесь, в процедуре Sample1, к тому же там каждая строка прокомментирована) - там весь нужный диапазон сначала считывается в массив, а потом в цикле сравниваются уже значения из этого массива, а не обращается к каждой ячейке листа отдельно, как у вас. Это увеличивает скорость в разы, если не в десятки раз (я вам об этом уже недавно писал в одной из ваших тем). А если вы "теряетесь" в смещениях в массиве - сделайте проще! Считайте в массив не только нужный диапазон, а ВЕСЬ ваш рабочий диапазон (например, A1:AX2000), понятно, что при этом вы будете нерационально использовать память, т.к в массиве будет много лишнего (шапка таблицы и другие неиспользующиеся данные), но тогда вы можете не вникать в смещения и в цикле к элементам массива обращаться по тем же адресам, что и к ячейкам листа, т.е., элемент v(1,1) будет содержать значение ячейки Cells(1,1), т.е. ячейки A1, а элемент v(7,2) - значение ячейки Cells(7,2), т.е. ячейки B7, и т.д., и из-за того, что работа с массивом ведется в памяти, скорость выполнения увеличится в несколько раз.
А в цикле потом ВЕЗДЕ (а не только в строке сравнения), где запрашиваются значения ячеек, просто замените Cells на v:
[vba]
Код
If (Abs(v(i, 17) - v(n, 17)) <= x1) _
                  And (Abs(v(i, 18) - v(n, 18)) <= x2) _
                  And (Abs(v(i, 21) - v(n, 21)) <= x3) _
                  And (Abs(Val(a) - Val(b)) <= x5) _
                  And (Abs(Val(c) - Val(d)) <= x5) Then
[/vba]
KSV, Весь день сидел за этим кодом, в массивах так до конца и не разобрался.
Решил немного изменить внешний вид фильтрации.

заодно, это поможет вам разобраться с массивами

Автор - KSV
Дата добавления - 27.06.2015 в 16:38
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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