☰

두 Excel 스프레드시트 간의 차이점 강조

tag-icon • tag-icon microsoft-excel worksheet-function conditional-formatting
두 Excel 스프레드시트 간의 차이점 강조

스프레드시트 A의 데이터에는 여러 열이 있지만 비교만 하면 됩니다.

Column A - order #
Column C - count
Column D - date

스프레드시트 B에도 많은 열이 있지만 다음을 비교해야 합니다.

Column A - order #
Column B - count
Column C - date

행의 데이터가 해당 3개 열에 대해서만 동일한 경우 두 스프레드시트의 행에서 A열을 강조 표시해야 합니다. 이 예에서는 6행과 14행의 날짜가 다르고 7행과 15행의 수량이 다르기 때문에 강조 표시되지 않습니다.

예를 들어 클릭

답변1

이 방법은 첫 번째 스프레드시트 또는 원하는 경우 별도 시트의 일부 도우미 열을 사용합니다.

MATCH()는 배열의 주어진 값에 대한 행 번호를 반환합니다.

INDEX()는 다른 지정된 배열에서 이 값을 찾습니다.

따라서 도우미 열의 1행(X열이라고 가정)에서 원하는 =MATCH(A1,SheetName!A:A,0) 것은 A1의 주문 번호를 포함하는 다른 시트의 행을 반환하는 것입니다.

그런 다음 다음 2개 열에서 =INDEX(SheetName!C:C,$X1) =INDEX(SheetName!D:D,$X1). 그러면 해당 행에 있는 다른 시트의 개수와 날짜가 반환됩니다.

그런 다음 원하는 마지막 열에서 =And(Y1=C1,Z1=D1). 모든 것이 동일하면 TRUE를 반환합니다.

그런 다음 원하는 경우 조건부 서식을 사용하여 강조 표시를 만드세요.

이 모든 것을 단일 수식으로 압축하고 원하는 경우 조건부 서식 조건으로 사용할 수 있습니다. 또한 vlookup은 여기에서 인덱스 일치와 마찬가지로 잘 작동합니다. 무슨 일이 일어나고 있고 왜 작동하는지 따라가는 것이 더 쉽다고 생각하여 모든 것을 분리하여 제공했지만 연습으로 압축해 보시기를 권장합니다.

이것이 도움이 되었기를 바랍니다.

참고로, 주문 번호가 조회 목록에 중복된 경우 테이블에서 첫 번째 항목만 조회하므로 이 기능은 작동하지 않습니다.

답변2

"수식을 사용하여 서식을 지정할 셀 결정"을 사용하여 조건부 서식을 사용할 수 있습니다. 짧은 버전에서는 A1부터 시작하여 Sheet1에 있는 첫 번째 테이블과 Sheet2에 있는 다른 테이블에 다음 수식을 사용해야 합니다.

=AND(MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A)>0; INDEX(Sheet1!$A:$D;ROW();3)=INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);2); INDEX(Sheet1!$A:$D;ROW();4)=INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);3))

이 공식은 다음을 가정합니다.

  • 두 테이블 모두 Sheet1과 Sheet2라는 두 개의 별도 시트에 있습니다.
  • Sheet1에는 A, C 및 D 열이 Sheet2 A, B 및 C 열의 두 번째 테이블과 비교되는 첫 번째 테이블이 포함되어 있습니다.
  • 시트 1에서 조건부 서식 영역으로 $A:$A 또는 $A2:$AXX를 선택합니다(여기서 XX는 모든 값을 포함할 수 있을 만큼 큰 숫자입니다). 위 방정식이 참인 경우에만 색상이 중요합니다.

'예상'대로 작동하지 않는 경우:

  • 두 번째 테이블에 동일한 주문 번호가 여러 개 있는 경우 첫 번째 일치하는 항목만 계산하여 비교합니다.
  • Sheet2 테이블에 적용하면 올바른 색상이 반환되지 않으며 일부 참조 값을 변경해야 합니다.

사용된 기능:

  • AND()- 모든 매개변수가 true인 경우에만 true를 반환합니다.
  • MATCH()- 선택한 범위(이 경우에는 열)에서 값을 찾습니다.
  • INDEX()- 행 및 열 번호를 명시적으로 말하여 행렬/범위에서 셀 참조를 반환합니다.
  • ROW()- 매개변수 없이 현재 행 번호를 반환합니다.

이제 몇 가지 구성요소를 살펴보겠습니다.

  • INDEX(Sheet1!$A:$A;ROW();1)조건부 서식이 실행될 때 현재 비교된 셀 참조를 반환합니다.
  • MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A)여기서는 일치하는 항목이 있으면 현재 셀과 일치하는 두 번째 시트의 셀을 찾고, 반환된 값은 0보다 크고, 그렇지 않으면 경고/오류가 발생합니다.
  • INDEX(Sheet1!$A:$D;ROW();3)- 여기에서 첫 번째 테이블(Sheet1)의 세 번째 열(별칭 C 열) 값을 찾고 있습니다.
  • INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);2)- Sheet2 테이블에서 두 번째 열을 가져옵니다. 우리는 이미 주문 ID가 있는 행을 갖고 있으며 해당 방정식은 행을 결정하는 함수의 중간에 있습니다.
  • 세 번째 AND()매개변수는 이전 두 지점과 동일하며 열 번호만 변경됩니다.

메모:

  • 올바른 행을 찾기 위해 열만 사용했습니다 Sheet1!$A:$A.Sheet2!$A:$A
  • 다른 열을 가져와야 할 때 마지막 열이 참조할 마지막 열인 행렬/테이블로 열을 확장해야 합니다. sheet1의 경우 네 번째 별칭 D 열입니다.Sheet1!$A:$D

위의 내용을 알고 있으면 Sheet2 테이블에서도 작동하도록 방정식을 수정할 수 있어야 합니다.

답변3

이것은 다음과 같이 조건부 서식을 사용하여 훌륭하고 쉽게 수행됩니다.

설정

아래 예제 수식의 경우 데이터는 Sheet1!A1:C3 및 Sheet2!A1:C3 셀에 있습니다.

조건부 서식 수식

셀 범위를 강조 표시합니다.시트2, 수식으로 조건부 서식을 추가하고 다음 형식을 입력합니다. =AND(Sheet1!$A1=$A1,Sheet1!$B1=$B1,Sheet1!$C1=$C1) ...그리고 결과를 강조할 색상을 선택합니다. .

행이 아니라 열을 잠그는 것이 중요합니다. 즉, 문자 앞에 달러 기호가 있지만 숫자는 없습니다.

조건부 서식 수식에 사용되는 참조와 관련하여 셀 참조는 해당 데이터 열의 최상위 셀이어야 합니다. 따라서 주문 번호의 두 열이 모두 A1에서 시작하는 경우 위의 내용에 따라 A1로 두십시오. 그러나 첫 번째 탭에서 고려해야 할 첫 번째 주문 번호는 행 4에 있지만 두 번째 탭에서는 행 7에 있는 경우 수식을 AND(Sheet1!$A4=A7...) 등으로 수정합니다.

설명이 필요한 것이 있으면 알려주세요!

관련 정보