Ячейки условного формата на основе соответствия на другом листе

У меня есть 2 листа Excel (использую Microsoft excel 2011 для Mac, но пробовал то же самое и в excel 2010)

sheet1(Column D)
foo
bar

sheet2
abc cde foo
sfd sdf dgf
bar

Я применил 3 разных правила, используя условный формат:
1. Если какие-либо данные на Листе2 совпадают с Листом1(СтолбецD) — сделать текст синим
2. Если на листе2 есть повторяющиеся значения — сделать текст синим желтый
3. Если вышеперечисленных 2 нет - оставьте это поле пустым
4. Если соблюдаются и Правило 1, и Правило 22 - Сделайте текст зеленым

Что может быть правилом 4?

Правила:

 Rule 1 # =ISERROR(MATCH(A1,Sheet1!$D:$D,A1,0))=FALSE
 Rule 2 # Highlight Duplicate values with yellow text
 Rule 3 # =ISBLANK($1:$1048576)
 Rule 4 # (Not sure what to have as formula)

excel conditional-formatting excel-formula excel-2010 excel-2011
person Jill448    schedule 12.12.2014    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Используйте COUNTIF так же, как Excel использует количество дубликатов.

Используя относительную адресацию, чтобы «обмануть» систему, выделите от A1 место, где вы хотите остановить форматирование, затем мы можем использовать относительную адресацию для проверки каждой ячейки.

Правило 1: =(CountIf(Sheet1!$D:$D,A1)>0)
Правило 2: =(CountIf(Sheet2!$A:$XFD,A1)>1) (это то, что делает Excel, когда вы указываете выделять дубликаты)
Правило 3: не требуется, так как если оно не соответствует правилу, форматирование не применяется
Правило 4: =(AND((CountIf(Sheet1!$D:$D,A1)>0),(CountIf(Sheet2!$A:$XFD,A1)>1)))

Если вы начинаете выделение с любой другой ячейки, измените A1 в правилах на любую ячейку, с которой вы начинаете форматирование.

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

person SeanC    schedule 12.12.2014
comment
@pnuts изменен на полный лист. - person SeanC; 13.12.2014
comment
Когда я пытаюсь добавить Rule1, я получаю сообщение об ошибке You may not use unions,intersections,or array constants for Conditional Formatting criteria. - person Jill448; 15.12.2014
comment
изменены ссылки на листы на ! - я случайно поставил двоеточие после имени листа вместо восклицательного знака - person SeanC; 15.12.2014
comment
Rule 4 не работает. Если я размещаю условия по отдельности, они работают по мере необходимости. Но они не работают, когда я комбинирую их с оператором AND. Я также обновил свой вопрос. Сейчас все работает, кроме Rule 4 - person Jill448; 15.12.2014
comment
Я скопировал формулы на новый лист, протестировал и получил все, что ожидал. если правило 1 и 2 работают, то скопируйте те. вы можете настроить формулу для копирования готовой для вставки в условное форматирование. поместите =(AND(1,2)) в ячейку, скопируйте формулу 1 поверх числа 1 и формулу 2 поверх числа 2 и скопируйте всю эту формулу в новый условный формат. - person SeanC; 16.12.2014
comment
Я сделал то же самое, но не уверен, почему это не работает. Я также пытался изменить порядок правил. Но это влияет на Правило 1. - person Jill448; 16.12.2014
comment
правило 4 повлияет на правило 1, только если оно одновременно соответствует правилу 2. Для проверки вы можете переместить правило 4 наверх, и вы обнаружите, что ничего не выделено зеленым цветом, так как другие 2 правила переопределяют выделение. - person SeanC; 16.12.2014

arrow_upward
0
arrow_downward

Я не понимаю вашего вопроса, но подозреваю, что вам все равно нужны только два правила:

  1. правило формулы =NOT(ISERROR(MATCH(A1,Sheet1!$D:$D,0))) с синим шрифтом и
  2. форматировать только уникальные или повторяющиеся значения с выбранным duplicate желтым шрифтом.

Условное форматирование не позволит использовать правило, которое возвращает желтый и синий шрифт.

Правило формулы для, скажем, зеленого шрифта, где применяются оба вышеуказанных условия, при условии, что применяется диапазон A:C :

=AND(COUNTIF($A:$C,A1),NOT(ISERROR(MATCH(A1,Sheet1!$D:$D,0))))

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

person pnuts    schedule 12.12.2014
comment
Ваше понимание правильное. Мне нужно еще одно правило к вышеупомянутому. Ячейка, которая удовлетворяет обоим правилам, имеет разный цвет (скажем, зеленый). Но мне непонятно, почему упомянутое вами условное форматирование не позволяет использовать эти правила? У меня могут быть отдельные правила, верно? - person Jill448; 12.12.2014
comment
Я хочу, чтобы Правило 1 было на полном Листе 2, поэтому сделал его =NOT(ISERROR(MATCH($1:$1048576,Sheet1!$D:$D,0))) для синего шрифта. Но он все еще не работает - person Jill448; 12.12.2014