동일한 열 사이에 다른 값을 가진 셀의 서식을 조건부로 지정해야 합니다.만약에특정 열의 값은 동일합니다.
대본
내 스프레드시트는 각 행이 연락처 기록이고 각 열에 각 연락처 기록에 대한 데이터 유형(성, 거리 1, 거리 2, 도시, 주 등)이 포함되어 있는 연락처 데이터베이스입니다.
스프레드시트는 회사의 여러 사무실에서 보관하는 연락처 정보를 통합한 것입니다. 모든 연락처 정보를 동일한 소스로 결합하여 불일치 사항에 동의하고 앞으로 모든 연락처를 동일한 위치에 유지할 수 있습니다.
일부 연락처에는 하나의 기록만 있습니다. 예를 들어 우리 사무실 중 한 곳에만 해당 연락처에 대한 데이터가 있으므로 다른 사무실에서 얻은 데이터와 일치하지 않을 수 있습니다. 다른 연락처에는 여러 개의 기록이 있습니다(예: Fred Johnson에 대해서는 행이 하나만 있지만 Jane Smith에 대해서는 행이 4개 있을 수 있음). 모든 연락처는 클라우드 데이터베이스에 업로드하기 전에 정확히 동일한 연락처 정보를 가지고 있어야 합니다. 그렇지 않으면 중복이 생성됩니다.
우리는 동일인에 대한 기록 간의 동의 데이터를 찾는데 오랜 시간을 보냈고 이제 남아 있는 불일치가 어디에 있는지 알아내야 합니다.
이 필드에서 동일한 값을 가진 행을 서로 비교할 수 있도록 LastName 및 FirstName 필드를 연결했습니다. 목표는 동일한 값을 가진 다른 레코드와 비교할 때 동일한 열에서 다른 데이터가 있는 셀을 강조 표시하는 것입니다. LastFirst 열(있는 경우)
어떻게 해야 하나요?
지금까지는 Find Special 함수가 지정된 행이나 열과 비교할 때 셀을 강조 표시할 수 있다는 것을 알고 있지만 특정 열에 대해 동일한 값을 가진 행에 대해 비교를 수행해야 합니다.
이 기능을 수행하기 위해 Excel 2010 및 2013에 액세스할 수 있습니다.
감사합니다!
답변1
더 깔끔한 솔루션이 있을 수 있지만 "FullName" 열을 만들기 위해 이름과 성을 연결할 필요는 없습니다.
출력 미리보기
(오른쪽의 흰색 셀은 조건부 서식을 구동하기 위해 수식이 생성할 출력을 보여줍니다.)
여기 우리의 공식이 있습니다
=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)
-COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2,C$1:C$100,IF(C2="","",C2))
데이터세트에 있는 행 수와 A 및 B에 없는 경우 이름 및 성 열의 위치에 맞게 조정해야 합니다.
또한 이름이 아닌 첫 번째 데이터가 C2 셀에서 시작한다고 가정했습니다. 즉, 이는 불일치를 확인하려는 데이터의 가장 왼쪽 상단 비트입니다. 필요한 경우 이 수식에서 "C"를 편집하세요.
조건부 서식 적용
위 수식을 복사한 다음(필요한 사항을 변경한 후) 불일치를 확인하려는 맨 왼쪽 상단 셀을 클릭합니다(여기에서는 "C2"를 참조하겠습니다). 이제 Conditional Formatting리본 버튼을 선택하고 을 선택합니다 New Rule.
을 선택한 Use a formula to determine which cells to format다음 수식을 텍스트 상자에 붙여넣습니다. 공백이 없어야 합니다. 위에서 복사한 경우 몇 개를 제거해야 합니다. 이제 클릭하여 Format불일치 강조 표시 방법을 설정할 수 있습니다. 나는 (화려한!) 빨간색 채우기를 선택했습니다. OK스프레드시트로 돌아갈 때까지 클릭하세요 .
셀 C2에 불일치가 없으면 아무 일도 일어나지 않은 것처럼 보일 수 있지만 이는 여전히 전체 데이터세트에 규칙을 적용해야 하기 때문입니다. C2를 계속 선택한 상태로 리본 메뉴 Manage Rules에서 클릭합니다 Conditional Formatting.
이제 불일치를 확인하려는 전체 범위를 선택할 수 있습니다. 상자 에서 Applies to클릭하고 드래그하여 비교하려는 모든 항목을 선택합니다. 또는 행이 많은 경우 속도를 위해 셀 참조를 입력하면 됩니다 =$C$2:$Z$999.
확인을 클릭하면 끝입니다!
작동 원리
이 공식은 COUNTIFS()해당 사람에 대한 행 수를 계산하는 데 사용됩니다.
=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)
그런 다음 해당 사람의 이름과 확인 중인 열의 값이 포함된 행 수를 계산합니다. 모든 행이 동일한 경우 이 숫자는 첫 번째 와 정확히 일치해야 합니다 COUNTIFS().
COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2,C$1:C$100,IF(C2="","",C2))
전자에서 후자를 빼고 모든 행이 일치하면 수식은 0을 출력하고 조건부 서식을 적용하지 않습니다. 그러나 다른 것이 있으면 출력은 1 이상이 되어 조건부 서식이 트리거됩니다.
메모
공백을 처리하기 위해 명령문 COUNTIFS()에 최종 기준을 포함해야 했습니다 . 공백을 별로 좋아하지 않습니다(0 또는 ""로 계산해야 할지 확실하지 않은 것 같습니다. 이상합니다).IF()COUNTIFS
파일 다운로드
이 예제 문서는 또한다운로드 가능.