Страницы: 1
RSS
Подсветить определенные дубликаты
 
Здравствуйте.  Подскажите люди добрые.
Мне нужно выделить заливкой дубликаты в заданном диапазоне. Но не все, а конкретные. Например цифра 12 при наличии дубликата - одна заливка, для 3 - другая. Возможно нужен макрос. Как это должно выглядеть, видно в файле примера. Сравнение должно выполнятся по колонкам, т.е. не всю таблицу сравнивать, а для определенного диапазона. А именно B2:B9 - отдельно от C2:C9, D2:D9 и т.д.
Изменено: Алексей П - 21.03.2018 06:08:48
 
вариант УФ
Лень двигатель прогресса, доказано!!!
 
Спасибо. Я возможно плохо объяснил условие. В каждой колонке должны искаться дубли значений. Для примера взяты 3 и 12. Именно они и обрабатываются. Т.е. если значений "3" в колонке "B" (B2:B9) больше одного, то дубликаты окрашивается например оранжевым. Тоже самое для 12, только другим цветом. Для остальных колонок эти условия должны повторяться, как и для "B", но сравнение не должно идти между колонками. В Вашем примере работает не так. У Вас окрашивается даже одна ячейка, имеющая нужное значение. Это не верно...  
 
другой вариант красятся только дубли при тех же условиях
Лень двигатель прогресса, доказано!!!
 
Да, круто. Спасибо! Для "B" и "E" все работает как надо. И откуда только про такие навороченные формулы узнаете, в учебниках по экселю? Вопрос, можно ли условное форматирование перетащить по колонкам, как обычную формулу? Колонок много, для каждого задавать отдельно довольно трудоемкая задача...
 
Простите за глупый вопрос выше, элементарная вещь оказывается (распространение формата на другие ячейки).
Изменено: Алексей П - 21.03.2018 07:43:11
 
копируете ячейку B2 выделяете диапазон хоть 100 столбцов на 100 строк  спец вставка форматы ОК ( но вначале в правилах в УФ в СЧЁТЕСЛИ(B$2:B$9;B2) меняете диапазон просмотра с B$9 на B$100 или на то кол-во строк на которое собираетесь распространять УФ)
Лень двигатель прогресса, доказано!!!
 
Цитата
Алексей П написал:
И откуда только про такие навороченные формулы узнаете
отсюда с форума
Лень двигатель прогресса, доказано!!!
 
Спасибо за помощь!
 
Еще вопрос, если можно. =СУММПРОИЗВ((СЧЁТЕСЛИ(B$2:B$9;B2)>1)*(B2=$E$13)
За что в данном случае отвечает B2 (выделил красным цветом)?

А, это образец, с которого брать значения... сори.
Изменено: Алексей П - 21.03.2018 08:17:56
 
берем значение из В2 и считаем в диапазоне эта часть формулы которая выявляет дубликаты в столбце
Лень двигатель прогресса, доказано!!!
 
Т.е. B2 это (B2=$E$13). Так? B2 это условное обозначение или координаты ячейки? Видимо условное, потому что зачем нам что-то брать из ячейки B2? Вместо B2 видимо можно написать как угодно по другому, например B213 или N3. Правильно понимаю? И соответственно в скобках значение тоже изменится на (B213=$E$13), где $E$13 это как раз координаты образца. ?...
Изменено: Алексей П - 21.03.2018 08:31:24
 
=СУММПРОИЗВ((СЧЁТЕСЛИ(B$2:B$9;B2)>1)*(B2=$E$13)*(СЧЁТЕСЛИ(УФ2;СТОЛБЕЦ(B2)-1)))
СЧЁТЕСЛИ(B$2:B$9;B2)>1 проверка значения ячейки на дубль
(B2=$E$13) проверка значения ячейки на условие по которому ищем
СЧЁТЕСЛИ(УФ2;СТОЛБЕЦ(B2)-1) проверка на заданные столбцы которые должны просмотреть
Лень двигатель прогресса, доказано!!!
 
Если я правильно понял, то >1 это как раз проверка значения ячейки на дубль, т.е. больше одного значения - обработка. (B2=$E$13) - там в ячейке E13 лежит эталон, с которым сравниваем. А вот B2 как-то не совсем понял.Написал в своем файле формулу по Вашему образцу - не работает. Исходил из того, что B2 условное обозначение и написал по другому.  Собственно, исправил на B2 = тоже не работает. Ищу где накосячил.
 
скопируйте из уф формулу и проставьте её в ячейку рядом с вашей таблицей растяните формулу проекционно по размерам с вашей таблицей и смотрите где она покажет значение не равное 0 значит проекционно там ячейка которая будет окрашиваться УФ, если что то не срабатывает но вы уверены что тут должно быть окрашивание прогоните формулу через инструмент вычисление и посмотрите какое условие не отрабатывается
Лень двигатель прогресса, доказано!!!
 
Не пойму, что не так делаю... Все по образцу, не работает. Что-то упускаю.
 
Цитата
Алексей П написал:
Не пойму
потому что не поняли принцип работы формулы
1. УФ1 и УФ2 в моей формуле это именованные диапазоны в которых указывается какие столбцы проверять (они находятся в диспетчере имен), в вашем последнем примере их нет соответственно формула находящаяся в УФ всегда будет выдавать ошибку #ИМЯ? но вы этого не видите так как не сделали так как я вам предлагал в своем последнем сообщении
2. собственно где вы для формулы указали это
Цитата
Алексей П написал:
Сравнение должно выполнятся по колонкам, т.е. не всю таблицу сравнивать,
Лень двигатель прогресса, доказано!!!
 
Скопировал формулу, растянул - сплошное #ИМЯ? Да, получается не понял принципа. А показалось понял.
Цитата
Сергей написал:
УФ1 и УФ2 в моей формуле это именованные диапазоны в которых указывается какие столбцы проверять (они находятся в диспетчере имен)
Диспетчер имен это где копать? Я все делал в Управлении правилами. Там диапазоны указал, формулы вставил...  
 
Цитата
Алексей П написал:
Диспетчер имен это где копать?
вкладка формулы
Лень двигатель прогресса, доказано!!!
 
О как! Век живи, век учись. Не догадался бы ни за что. Спасибо, чуть позже займусь.  
 
Еще вопрос, в Вашем файле есть пояснение "задаем столбцы" и напротив цифры в столбцах. В этих столбцах что указано? Это для взаимодействия с Диспетчером имен?
 
в моем примере с начало задаем искомые параметры это 12 и 3 под ними задаем столбцы которые надо проверить формула в диспетчере проверяет заполненный диапазон под искомым параметрам с и формирует динамический диапазон по последней заполненной ячейке и передает уже этот диапазон функции счетесли в Условном форматировании
Лень двигатель прогресса, доказано!!!
 
Цитата
Сергей написал:
под ними задаем столбцы которые надо проверить формула в диспетчере проверяет заполненный диапазон под искомым параметрам
Там под 12 есть 1, 4 и 3. А откуда отсчет? И почему 3 после 4? В смысле 1 я так понимаю означает первый столбец, но откуда считать? С колонки B?
Если можно, поясните пож по
=Лист1!$D$14:ИНДЕКС(Лист1!$D$14:$D$17;ПОИСКПОЗ(9^9;Лист1!$D$14:$D$17;1))
Создавая свой файл, хочется понимания процесса...
 
у вас табличка в примере состояла  из 4 колонок вот оттуда и числа, в формуле СЧЁТЕСЛИ(УФ1;СТОЛБЕЦ(B2)-1)  
УФ1 и есть =Лист1!$D$14:ИНДЕКС(Лист1!$D$14:$D$17;ПОИСКПОЗ(9^9;Лист1!$D$14:$D$17;1)) который после вычисления становится $D$14:$D$16
у вас табличка начинается со столбца В поэтому СТОЛБЕЦ(B2) что равно 2 но для вашей таблицы это первая колонка по этому проводим корректировку СТОЛБЕЦ(B2)-1 или 2-1 = 1
Лень двигатель прогресса, доказано!!!
 
А 9^9 это что?
 
это 9 в 9 степени или большое число которое не может быть в нашем диапазоне и ПОИСКПОЗ найдет -остановит свой поиск просматривая диапазон снизу вверх на первом же попавшемся числе
Лень двигатель прогресса, доказано!!!
 
Спасибо за помощь! Вроде сделал.
Страницы: 1
Наверх