엑셀·구글시트 두 열 비교: 없는 값에 색칠하기

두 목록을 나란히 놓았는데 순서가 다릅니다. A2와 B2를 비교하니 거의 전부 다르다고 나옵니다. 이럴 때 필요한 것은 같은 줄끼리의 비교가 아니라, A열의 번호가 B열 어디엔가 있는지 확인하는 것입니다.

이번에는 없는 번호 옆에 ‘누락’을 표시하고, 그 번호에 자동으로 색이 들어오게 만들어 보겠습니다. 예제의 정답은 ORD-1004와 ORD-1006, 두 개입니다. 색만 보지 않고 확인 결과 열도 함께 남깁니다.

실행·캡처 확인: 2026년 11월 5일, PC 구글시트. 주문번호와 금액은 가상 데이터입니다. 엑셀과 구글시트의 비교 원리는 비슷하지만, 아래 REGEXMATCH를 포함한 실습 수식과 화면은 구글시트 기준입니다. 엑셀에서 이 전체 예제를 실행 검증한 것은 아닙니다.

이 예제의 번호 규칙: ORD-1001처럼 영문으로 시작하고 영문·숫자·일반 하이픈(-)만 쓰는 번호입니다. 영문 대소문자는 같게 보고 앞뒤 일반 공백은 제거합니다. 숫자로만 된 번호, 한글 번호, 특수문자, 번호 안의 공백은 입력 확인으로 멈춥니다. 해당 형식이 잘못됐다는 뜻이 아니라, 이 실습의 비교 대상이 아니라는 뜻입니다.

1. 두 목록을 한 시트에 놓기

빈 구글시트의 A1을 한 번 클릭하고 다음 표를 붙여넣으세요. 제목까지 포함합니다. B7은 빈칸이고 8·9행은 비워 둡니다. 한 칸 안에 모두 들어갔다면 Esc를 눌러 셀 편집 상태를 나온 뒤 다시 붙여넣으세요.

주문번호	비교할 목록
ORD-1001	ORD-1003
ORD-1002	ORD-1001
ORD-1003	ord-1002
ORD-1004	ORD-1005
ORD-1005	ORD-1005
ORD-1006	

A열에는 6개, B열에는 5개가 들어갑니다. B열의 ORD-1005는 두 번 있지만 있느냐 없느냐만 비교할 것이므로 같은 번호로 봅니다. 중복을 지우거나 건수를 맞추는 작업은 아닙니다.

C1에는 아래 세 제목을 붙여넣습니다. C·D열은 비교에 쓸 번호, E열은 결과입니다.

주문 비교키	상대 비교키	확인 결과

2. 공백과 대소문자를 맞추고, 잘못된 입력은 멈추기

C2에 다음 수식을 넣습니다. 빈칸은 그대로 두고, 규칙에 맞는 번호만 대문자로 정리합니다. 숫자로 들어온 값이나 예상하지 않은 문자는 ‘입력 확인’으로 남깁니다.

=IF(A2="","",IF(ISNUMBER(A2),"입력 확인",IF(REGEXMATCH(TRIM(A2),"^[A-Za-z][A-Za-z0-9-]*$"),UPPER(TRIM(A2)),"입력 확인")))

D2에는 B2를 읽는 수식을 넣습니다.

=IF(B2="","",IF(ISNUMBER(B2),"입력 확인",IF(REGEXMATCH(TRIM(B2),"^[A-Za-z][A-Za-z0-9-]*$"),UPPER(TRIM(B2)),"입력 확인")))

C2:D9를 선택하고 Ctrl+D(맥은 ⌘D)로 아래까지 채우세요. 왼쪽 위 이름 상자에 C2:D9를 입력하면 범위를 바로 선택할 수 있습니다. 실제 데이터가 없는 8·9행에도 수식을 채우는 것이 맞습니다.

‘ord-1002’는 비교키에서 ‘ORD-1002’가 됩니다. 대소문자를 서로 다른 번호로 취급해야 하는 업무에는 이 방법을 쓰지 마세요.

3. 없는 번호를 ‘누락’으로 표시하기

E2에 아래 수식을 넣고 E2:E9를 선택해 아래로 채웁니다.

=IF(COUNTIF($C$2:$D$9,"입력 확인")>0,"입력 확인",IF(C2="","",IF(COUNTIF($D$2:$D$9,C2)=0,"누락","있음")))

COUNTIF가 상대 비교키 D2:D9에서 현재 번호를 찾습니다. 0번 나오면 ‘누락’, 한 번 이상 나오면 ‘있음’입니다. 다만 C·D열에 ‘입력 확인’이 하나라도 있으면 E열 전체가 ‘입력 확인’으로 바뀝니다. 상대 목록의 번호가 깨져 있는데 누락 판정을 계속하지 않기 위해서입니다.

결과는 E5와 E7만 ‘누락’이어야 합니다. 빈 주문 행은 결과도 비어 있어야 합니다.

4. 조건부 서식으로 없는 값에 색칠하기

  1. A2:A9를 선택합니다.
  2. 위 메뉴에서 서식 → 조건부 서식을 엽니다.
  3. ‘다음의 경우 셀 서식 지정’에서 맞춤 수식을 고릅니다.
  4. 아래 수식을 넣고, 원하는 채우기 색을 정한 뒤 완료를 누릅니다.
=$E2="누락"

$E2는 결과를 읽을 E열은 고정하고, 행 번호는 각 줄에 맞게 바꾼다는 뜻입니다. ‘$E$2’로 쓰면 모든 행이 E2 하나만 보므로 원하는 대로 색이 들어가지 않습니다. 여기서는 구글시트의 기본 연녹색을 그대로 사용했습니다.

ORD-1004와 ORD-1006에만 색이 들어가고, E열에도 누락으로 표시됩니다
ORD-1004와 ORD-1006에만 색이 들어가고, E열에도 누락으로 표시됩니다 · 클릭하면 크게 볼 수 있습니다.

한 행 전체를 색칠하려면 적용 범위를 A2:E9로 넓히고 수식은 그대로 두면 됩니다. 여러 탭을 직접 참조하는 조건부 서식 대신, 이 예제처럼 같은 시트의 결과 열을 읽으면 규칙을 확인하기 쉽습니다.

5. 색이 안 들어온다고 모두 맞는 것은 아닙니다

E열에 입력 확인이 보이면 비교가 중단된 상태입니다. 이때 누락 색이 사라져도 ‘누락 0개’로 읽으면 안 됩니다. C·D열에서 입력 확인인 칸을 찾아 원본 번호부터 확인하세요.

숫자로 된 긴 번호를 넣으면 결과가 입력 확인으로 바뀌며 누락 색칠을 멈춥니다
숫자로 된 긴 번호를 넣으면 결과가 입력 확인으로 바뀌며 누락 색칠을 멈춥니다 · 클릭하면 크게 볼 수 있습니다.

웹에서 가져온 번호에는 눈에 잘 안 보이는 공백(NBSP)이 섞일 수 있습니다. 일반 공백처럼 보이더라도 이 예제에서는 제거하지 않고 멈춥니다. 공백이나 특수문자가 실제 번호의 일부인지 먼저 확인한 뒤, 원본에 맞는 번호를 다시 입력하세요.

숫자로만 된 주문번호가 필요한 경우에는 앞자리 0과 긴 주문번호를 문자로 보관하는 방법을 먼저 확인하세요. 이 예제의 형식 제한만 지운다고 숫자 번호 비교까지 검증되는 것은 아닙니다.

6. 데이터를 바꾸면 색도 다시 바뀌는지 확인하기

B7에 ORD-1006, B8에 ORD-1004를 입력해 보세요. 주문 6개의 결과가 모두 ‘있음’으로 바뀌고 A열의 누락 색도 없어져야 합니다.

빠졌던 두 번호를 상대 목록에 넣자 누락 표시와 색이 함께 해소됐습니다
빠졌던 두 번호를 상대 목록에 넣자 누락 표시와 색이 함께 해소됐습니다 · 클릭하면 크게 볼 수 있습니다.
실제로 확인한 경우결과
목록 순서가 다름·상대 번호 중복·빈 행두 번호만 누락, 빈 행은 표시 없음
앞뒤 일반 공백과 소문자정리한 번호로 비교
숫자 문자열 / 숫자 값입력 확인으로 중단
NBSP·와일드카드(*)·번호 중간 공백·전각 문자입력 확인으로 중단
누락된 번호를 추가색과 결과가 다시 계산됨

예제는 2~9행만 비교합니다. 10행 아래에 데이터를 추가하려면 C·D·E열 수식을 새 끝 행까지 채우고, E열 수식의 두 고정 범위와 조건부 서식 적용 범위도 함께 늘려야 합니다. 데이터만 추가하고 범위를 그대로 두면 새 행은 비교에 포함되지 않습니다.

또한 이 결과의 ‘있음’은 상대 목록에 번호가 있다는 뜻입니다. 발송 완료·입금 완료를 뜻하지 않으며 상태나 금액은 비교하지 않습니다. 목록의 반대 방향도 확인하려면 A와 B의 역할을 바꾸어 별도로 비교하세요.

누락 번호를 따로 모으고 금액까지 검산하려면 두 시트 비교해서 없는 값 찾기, 같은 번호가 여러 줄일 때의 판단은 중복값을 바로 지우면 안 되는 이유로 이어집니다.

수식을 하나씩 넣기 전에 누락 비교를 먼저 경험해 보고 싶다면 무료 누락 확인 샘플 안내를 이용하세요. 구글시트 전용이며, 사본을 만들어 자기 드라이브에서 사용합니다. 이 글의 금액 대조 기능과는 다른 샘플입니다.

공식 참고: Google 조건부 서식 도움말, REGEXMATCH 함수. 조건부 서식은 맞춤 수식의 참·거짓에 따라 모양을 바꾸는 기능입니다.

댓글

이 블로그의 인기 게시물

엑셀·구글시트 두 시트 비교해서 없는 값 찾기

엑셀·구글시트 중복값 찾기: 주문번호가 같아도 바로 지우면 안 되는 이유

엑셀·구글시트 VLOOKUP #N/A 오류 해결: 공백·숫자 형식·검색 범위 확인하기