☰

시트1

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
시트1

2개의 워크시트가 포함된 Excel 스프레드시트가 있습니다. 첫 번째는 헤더 행과 항목 이름의 단일 열입니다. 두 번째는 가장 왼쪽 열에 머리글 행과 제목이 있는 항목 그룹 목록입니다. 각 후속 행은 다른 시트의 항목 중 하나 또는 다른 항목입니다.

Sheet1:            Sheet2:
+-------+--+--+    +-------+-------+-------+-------+-------+
| Item  |  |  |    | Group | Item1 | Item2 | Item3 | ...
+-------+--+--+    +-------+-------+-------+-------+-------+
| Shirt |  |  |    | A     | Shirt | Hat   | Tie   |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Hat   |  |  |    | B     | Socks | Shirt | SHOES |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Socks |  |  |    | C     | Hat   | Socks |       |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Tie   |  |  |    | D     | Tie   | Tie   | Socks |
+-------+--+--+    +-------+-------+-------+-------+-------+
| ...   |  |  |
+-------+--+--+

"Sheet1"의 첫 번째 열 값과 일치하지 않는 값이 빨간색 배경으로 표시되도록 "Sheet2"의 모든 셀에 조건부 서식을 적용하고 싶습니다. 해당하는 항목은 녹색 배경으로 표시됩니다. 따라서 이 예에서는 B2에서 시작하는 모든 셀이 "SHOES" 값을 제외하고 녹색이 됩니다. 그 아래 값에는 아무것도 입력되지 않았으므로 형식이 전혀 지정되지 않습니다.

내가 시도한 녹색 서식 규칙은 다음과 같습니다.

=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)>0)

빨간색의 경우 거의 동일합니다.

=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)<1)

두 규칙 모두 다소 임의의 범위에 "적용"됩니다(맨 위 및 가장 왼쪽 행/열을 제외한 전체 시트에 적용하고 싶습니다).

=$C$3:$E$10,$C$36:$Q$50,$E$11,$C$11,$C$2,$E$2:$Q$2,$C$12:$E$35,$F$3:$Q$35

이는 반쯤 작동하지만 결과는 예측할 수 없습니다. 일부 값은 예상한 대로 강조 표시되지만 일부 행에 대해서만 강조 표시되고 다른 값은 강조 표시되지 않습니다. 아마도 내 범위가 어떻게든 제대로 작동하지 않을 수도 있지만 예전만큼 Excel을 거의 사용하지 않습니다. 누구라도 도와줄 수 있나요?

감사해요!

답변1

Doktoro Reichard가 말했듯이, 당신은조건부 서식이것을하기 위해. 이 특정한 경우에는 세 가지 규칙이 필요합니다.

  1. 셀이 비어 있으면 배경을 변경하지 마세요.
  2. 셀에 일치하는 항목이 있으면 배경을 녹색으로 만듭니다.
  3. 셀에 일치하는 항목이 없으면 배경을 빨간색으로 만듭니다.

죄송합니다. 제 Excel은 일본어입니다. 다국어의 날입니다.

이를 위해서는 이러한 각 조건에 대해 TRUE또는 를 반환하는 3개의 수식이 필요합니다. FALSE귀하의 데이터가 다음과 같다고 가정하겠습니다.

시트1

여기에 이미지 설명을 입력하세요

시트2

여기에 이미지 설명을 입력하세요

규칙 #1

다음 수식은 셀이 비어 있는지 여부를 반환합니다. 나는 선택했다

여기에 이미지 설명을 입력하세요

=ISBLANK(B2)

상대 참조가 있는 셀을 선택했습니다 B2:D5. 그러면 선택한 범위의 모든 셀에 대한 셀 참조를 변경하는 동일한 수식이 적용됩니다. 이 조건이 true인 경우 배경색을 흰색(또는 원하는 대로)으로 설정합니다.

규칙 #2

다음 수식은 시트 1의 목록에 완벽하게 일치하는 항목이 있는지 여부를 반환합니다.

여기에 이미지 설명을 입력하세요

=NOT(ISERROR(MATCH(B2,Sheet1!$A:$A,0)))

규칙 #3

다음 수식은 시트 1의 목록에 완벽하게 일치하는 항목이 없는지 여부를 반환합니다.

여기에 이미지 설명을 입력하세요

=ISERROR(MATCH(B2,Sheet1!$A:$A,0))

주문하다

맨 위에 있는 규칙이 먼저 실행됩니다. 따라서 모든 빈 셀은 일치하지 않으므로 빈 규칙을 먼저 넣어야 합니다. 2번과 3번의 순서는 중요하지 않습니다(겹칠 일이 없습니다).

답변2

필요한 것은 셀 조건부 서식인 것 같습니다.

여기에는링크이것이 무엇인지 설명합니다.

내 사진은 포르투갈어 버전의 Excel 2003에서 가져온 것이지만 해당 기능은 Office 2010에도 있어야 합니다. 실제로 Office 2010에서는 시트 사이에 조건부 서식을 적용할 수 있습니다. 2003에서는 할 수 없는 기능이므로 제가 모든 작업을 수행하고 있습니다. 한 장에.

우선, 테이블은 다음과 같습니다.

테이블

당신이 원하는 것은 첫 번째 테이블의 모든 요소에서 두 번째 테이블의 요소를 비교하는 것입니다. 따라서 다음과 같은 함수를 작성해야 합니다.

=OR(EXACT($B$6;E3);EXACT($B$5;E3);EXACT($B$4;E3);EXACT($B$3;E3))

EXACT()두 개의 텍스트 문자열을 비교하는 것은 무엇 입니까? 내부의 논리적 조건이 OR()이면 되는 것은 무엇 입니까 ?TrueTrue

그러면 그림과 같이 두 번째 테이블에서 모든 셀을 선택한 다음 조건부 서식을 누릅니다.

조건부 서식에 액세스하는 방법

Office 2010과 새로운 리본 인터페이스에 대해 알고 있다면 이에 대한 형식 창을 살펴봐야 합니다. 기억해보면 아이콘으로 서 있는 것 같다.

해당 아이콘을 클릭하면 다음과 유사한 창이 나타납니다.

조건부 서식 창의 일부

여기에서 먼저 원하는 수식을 선택한 다음 앞서 언급한 수식을 붙여넣어야 합니다. 조건을 확인하는 모든 셀을 녹색으로 만들려면 형식을 변경하면 됩니다. 빨간색 형식을 지정하려면 NOT(OR(...)); 그러면 설정한 조건의 반대가 반환됩니다.

아무것도 없는 셀의 서식을 지정하지 않도록 하려면 수식이 ISBLANK(E3)(왼쪽 위 모서리가 E3임)인 세 번째 조건을 만듭니다.

초안을 작성하고 나서 확인한 것이 우선순위입니다. 적어도 내 버전에서는 조건 1이 조건 2보다 먼저 확인됩니다. 따라서 스스로 방해하지 않는 방식으로 조건을 주문해야 합니다. 그래서:

1st condition - =ISBLANK(E3)
2nd condition - =OR(...)
3rd condition - =NOT(OR(...))

따라서 다음과 같은 창이 있어야 합니다.

최종 창

이것을 상황에 맞게 조정하십시오. 기억해보면 지금 보여드리는 것과 크게 다르지는 않네요. 결과는 다음과 같아야 합니다.

최종 결과

관련 정보