Google Sheets 조건부 서식 사례 - 중복 데이터 검증 또는 표시

현재 구글 폼으로 설문을 받고 있습니다.

• 응답 내용에는 이름과 전화번호가 포함됩니다.
• 스프레드시트 하나를 구글 폼에 연결해두어 응답자의 이름과 전화번호가 각각의 열에 연동됩니다. 
• 그런데 응답자들 중에는 이전에 응답했음에도 불구하고 다시 응답하는 경우도 있고 동명이인도 더러 존재합니다.
• 그래서 이름 열에서 이름이 같은 경우를 표시해두고 이사람이 중복응답한 것인지 단순히 동명이인인지를 보려고 찾아보던 중이었습니다.

위 요구 사항에 맞춰 여러가지 방식으로 데이터를 관리할 수 있습니다. 시트 이름은 다음과 같이 구분합니다.

• 원본 Sheets : 시트1
• 사본 Sheets : 시트0

방법1: "시트0"에서 이름에 색상만 표기

다른 시트의 데이터를 가져온 뒤 대상 셀에서 색상을 다르게 표시하려면 조건부 서식과 COUNTIF 함수를 사용할 수 있습니다. 아래의 과정을 따라해보세요.

1. 시트0에서 A열에 =시트1!A2:A 를 입력하여 시트1의 A열 값을 가져옵니다.
2. 그 다음, 시트0에서 A열 전체를 선택합니다.
3. 메뉴에서 서식 > 조건부 서식을 선택합니다.
4. 새로운 규칙을 만들기 위해 새 규칙 추가를 클릭합니다.
5. 셀 값이 옵션을 선택하고, 오른쪽 드롭다운 메뉴에서 수식이 참인 경우를 선택합니다.
6. 아래에 있는 공백에 다음 수식을 입력하세요. =COUNTIF(A:A, A1) > 1 또는 =COUNTIF(A:A, A:A) > 1 와 같이 사용해도 동일합니다.
7. 이 식은 A 열 전체에서 현재 행(A1)과 동일한 값이 여러 개 있다면 TRUE(참), 그렇지 않다면 FALSE(거짓)을 반환합니다.
8. 서식 스타일 버튼을 클릭하여 원하는 셀 배경색과 글자색 등을 설정하세요.

방법2: "시트0"에서 다른 셀에 중복 여부 체크 표시하기

위 과정으로 사용해도 되지만 개인적으로 추후 관리를 위해서는 아래 내용을 검토하는 것을 권장드립니다.

우선, 아래의 과정을 따라해보세요. 이제 시트 0의 C 열은 중복되는 이름이 있으면 참(TRUE)으로 표시되고 해당 셀은 지정된 색상으로 하이라이트됩니다.

1. 시트0의 A열에 시트1에서 이름을 불러오고, B열에 전화번호를 불러옵니다. 예를 들면 =Sheet1!A2 와 같이 Sheet1의 A2 셀 값을 가져오면 되겠죠.
2. C열에서 COUNTIF 함수를 사용하여 중복되는 이름이 있는지 확인합니다. 예를 들어 이미지 처럼 =COUNTIF(A:A, A2) > 1 와 같이 사용합니다.
   • 이 식은 A 열 전체에서 현재 행(A2)과 동일한 값이 여러 개 있다면 TRUE(참), 그렇지 않다면 FALSE(거짓)을 반환합니다.
3. 조건부 서식 규칙을 설정합니다.
   • 선택 범위: C:C(C열 전체)
   • 조건: 셀 값이 TRUE
   • 서식 스타일 선택 후 적용(선택)

그런데 위 방법은 단순히 동명이인과 중복 응답자 모두 포함될 것입니다. 그래서 만약 동명이인과 중복 응답자를 구분하려면 이름과 전화번호 모두 같은 경우만 찾아내야 합니다.

그럴 경우에는 다음과 같이 해보세요.

1. D열에서 CONCATENATE 함수로 이름과 번호를 합칩니다. 위 그림과 같이 =CONCATENATE(A2, ,B2)이렇게 사용할 수 있습니다.

2. E열에서 COUNTIF 함수로 D열의 값들 중 본인(D행)와 동일한 것들의 개수가 1개 초과인지 확인합니다. 위 이미지와 같이 =COUNTIF(D:D,D2)>1 이렇게 합니다.
3. 다시 조건부 서식 규칙 설정을 거치게 됩니다.
   • 선택 범위: E:E(E열 전체)
   • 조건: 셀 값이 TRUE
   • 서식 스타일 선택 후 적용(선택)

이제 E 열은 동일한 사람(동일한 이름 및 전화번호)가 여러 번 응답했는지를 표시하게 됩니다. 이에 따라 조건부 서식이 적용되어 중복 응답자만을 색상으로 구분할 수 있습니다.