엑셀 중복 데이터 방지하는 방법 feat. 데이터유효성 검사| 진짜 쓰는 실무 엑셀

빠른 핵심 요약

✅ 데이터유효성 검사: [데이터]> [데이터 유효성 검사] > [유효성 조건] >[사용자 지정]> [수식] 에서 조건 작성

✅ VLOOKUP 함수: 수직 방향으로 데이터 검색하는 함수

✅ IFERROR 함수: 오류 처리를 담당하는 함수

✅ COUNTIF 함수: 선택된 범위에서 조건을 만족하는 셀의 개수를 구하는 함수

✅ AND 함수: 주어진 여러 조건이 모두 참(True)인 경우에만 참을 반환하는 함수

 

---

엑셀에서 중복 데이터 관리 방법 

사원번호, 쿠폰 번호 등 고유 데이터 값으로 데이터 관리해야 할 때가 있습니다. 하지만 일반적인 데이터베이스 프로그램과 달리, 엑셀은 중복 값 제한하는 기능이 취약합니다. 이런 문제를 해결하기 위해서 엑셀에서 중복되는 데이터를 처리하는 방법 중 하나가 [데이터 유효성 검사]를 사용하여 중복을 방지하는 것입니다.   [데이터 유효성 검사] 기능을 사용하면 데이터 범위에 중복된 값을 입력할 수 없게 되어 엑셀에서 중복을 간단하게 처리할 수 있습니다.

 

📢 데이터 유효성 검사 부분만 읽고 싶다면 "엑셀 데이터 유효성 검사 적용 방법"으로 스크롤 내려주세요!

 

엑셀에서 중복값 방지하기: 데이터 유효성 검사

쿠폰 발송 및 사용여부 예시를 통해 살펴봅시다. 예를 들어 발송된 쿠폰과 사용된 쿠폰번호 비교한다면, 쿠폰번호의 경우 중복 사용을 방지하기 위해 쿠폰 코드 당 한 번만 입력할 수 있도록 중복 값 입력을 제한해야 합니다. 

이 경우, 엑셀 데이터 유효성 검사를 통해서 중복 값 입력을 제한할 수 있습니다. 즉, 쿠폰을 한 번만 사용할 수 있게 만들 수 있습니다. 

IFERROR & VLOOKUP 함수를 이용한 중복값 방지

 

➕ 쿠폰 중복사용 방지를 위해 사용한 수식: IFERROR & VLOOKUP 함수

쿠폰번호 기준 HRRK 기준(스샷 속 파란색 셀)으로 살펴보도록 하겠습니다. ​

• 고객 이름에 사용된 함수: =IFERROR(VLOOKUP(B6,$F$4:$H$17,2,0),"-")
• 전화번호에 사용된 함수: =IFERROR(VLOOKUP(B6,$F$4:$H$17,3,0),"-")

​➕ col_index_num

 

 

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
​col_index_num: The column number in the table from which it will return.

=VLOOKUP(찾을_값, 테이블_배열, 열_인덱스_번호, [범위_검색])

열_인덱스_번호: 반환할 테이블의 열 번호

출처: exceldemy.com

 

➕ IFERROR & VLOOKUP 함수 분석하기

VLOOKUP(B6,$F$4:$H$17,2,0)

VLOOKUP 함수는 수직 방향으로 데이터를 검색합니다. VLookup 함수를 사용하면 특정 값을 검색하고 해당 값을 포함하는 행에서 지정한 열의 값을 가져올 수 있습니다. 위에 VLookup 함수의 매개변수는 다음과 같습습니다. 

1. B6: 검색할 값을 나타냅니다. 즉, 함수가 이 셀에서 값을 찾습니다.
2. $F$4:$H$17: 데이터 범위를 나타냅니다. 함수가 이 범위에서 데이터를 찾습니다. $ 기호는 범위를 절대로 고정시키는 것을 의미하므로, 범위가 복사되거나 이동할 때 참조가 변경되지 않습니다.
3. 2: 데이터가 포함된 열의 위치를 나타냅니다. 여기서는 데이터 범위 표에서 두 번째 열에 해당합니다.
4. 0: 이는 정확한 일치를 의미합니다. 즉, 검색된 값이 정확히 일치해야 합니다. 일반적으로 이 값을 사용하여 정확한 일치를 원할 때 사용합니다.

따라서 이 VLOOKUP 함수는 B6 셀에서 시작하여 데이터 범위 $F$4:$H$17에서 두 번째 열에서 값을 찾고, 정확한 일치를 찾습니다.

 

IFERROR(VLOOKUP(B6,$F$4:$H$17,2,0),"-")

IFERROR 함수는 특정 함수의 결과가 오류인 경우를 처리하기 위해 사용됩니다.  이 함수는 첫 번째 매개변수로 받은 함수의 결과가 오류인 경우 두 번째 매개변수로 지정한 값을 반환합니다.

 

=IFERROR(첫 번째 매개변수, 두 번째 매개변수)

1. 첫 번째 매개변수 (VLOOKUP 함수): 이 매개변수는 실제로 실행하고자 하는 함수입니다. 여기서는 VLOOKUP 함수가 사용되었습니다. 이 함수를 사용하여 데이터를 찾는 작업을 수행합니다.
2. 두 번째 매개변수 (-): 이 매개변수는 첫 번째 매개변수로 전달된 함수의 결과가 오류일 때 반환할 값을 나타냅니다. 이 경우에는 대시(-)를 반환하여 데이터를 찾을 수 없는 경우 대시를 표시합니다.

따라서 IFERROR 함수는 B6 셀에서 값이 검색될 때까지 $F$4:$H$17 범위에서 데이터를 검색하고, 검색된 값이 없으면 대시(-)를 반환합니다. 이렇게 함으로써 데이터를 처리할 때 발생할 수 있는 오류를 미리 대비할 수 있습니다.

 

IFERROR 함수에서 "-"대신 검색된 값이 없으면 "없어유~"를 입력

 

IFERROR 함수에서 발송된 쿠폰 리스트에 없으므로 "없어유~"로 반환된 것을 볼 수 있음.

 

엑셀 데이터 유효성 검사 적용 방법

엑셀 중복 데이터를 입력하지 못하게 하는 방법

1. 범위 선택: 중복 값을 제한할 열 전체를 선택합니다.

• 중복 값을 제한할 전체 열에 규칙을 적용합니다.
• 제한할 범위만 데이터 유효성 검사를 적용할 경우, 새롭게 추가되는 데이터는 규칙이 적용되지 않기 때문입니다.

2. 데이터 유효성 검사 설정: [데이터]> [데이터 도구] 영역> [데이터 유효성 검사] 클릭

[데이터] 탭에서 [데이터 도구] 영역의 [데이터 유효성 검사]를 클릭합니다.

3. 유효성 조건 설정: 유효성 조건] >[제한 대상] > [사용자 지정] 선택> [수식:]에서 조건 작성

[유효성 조건] 창이 열리면, [제한 대상]에서 [사용자 지정]을 선택합니다.

[수식:] 란에 아래 예시와 같은 수식을 입력하거나 원하는 수식에서 조건을 작성합니다. 

 

수식 예시:

• =A1 <1: 1보다 작은 값만 입력 가능
• =LEN(A1)=4: 글자 수가 4글자인 값만 입력 가능

➕ 데이터 유효성 검사로 수식 작성 시, 수식의 조건을 만족하는 데이터만 입력되도록 값을 제한할 수 있습니다.

➕ LEN(함수): 값의 문자 수를 반환합니다.

 

 

[데이터]> [데이터 도구] 영역> [데이터 유효성 검사] 클릭 > [유효성 조건] >[제한 대상] > [사용자 지정] 선택> [수식:] 에서 조건 작성

 

엑셀 COUNTIF 함수로 중복 값 방지하기

엑셀에서 COUNTIF 함수를 사용하여 중복 값을 방지하는 방법을 설명드리겠습니다. 이 방법은 특정 열(예: B 열)에서 중복된 값을 입력하지 못하도록 하는 데이터 유효성 검사를 설정합니다. 새로운 값을 입력할 때마다 해당 값이 B 열에서 유일한 값인지 확인하며, 중복된 값을 입력하려고 하면 경고 메시지가 표시됩니다. COUNTIF함수가 아래 과정을 통해 결괏값을 보여줄 것입니다. 

1. B 열 전체 범위 선택
2. 새롭게 추가한 값(쿠폰 코드)이 몇 개 인지 개수를 센 후,
3. 개수가 1인 경우에만 입력 가능 (= 값이 한 게임, 고윳값인 경우)

COUNTIF 함수 수식 작성 방법

 

완성 수식: =COUNTIF($B:$B,B1)<=1

​

1. B 열 전체 선택: B 열 전체를 선택합니다. 클릭한 셀이 B1이라면, Ctrl + Shift + ↓를 눌러 B 열 전체를 선택
2. 절대 참조 설정: F4 키를 눌러 절대 참조로 변경. B:B가 $B:$B로 변경됨.
3. 이름 상자에서 활성화된 셀 확인
4. 비교할 셀이 활성화된 셀이 B1인지 확인합니다.
5. 결괏값이 1 이하인 경우에만 입력이 가능하도록 설정합니다: <=

1. =COUNTIF
2. =COUNTIF(B:B
3. =COUNTIF($B:$B,
4. =COUNTIF($B:$B,B1)
5. =COUNTIF($B:$B,B1)<=1

 

➕수식 설명

COUNTIF($B:$B, B1) <= 1

• $B:$B: B 열 전체를 절대 참조합니다.
• B1: 현재 셀 값을 기준으로 합니다.
• <= 1: 값이 한 번만 입력될 수 있도록 제한합니다.

➕ 완성 수식 다른 버전: =COUNTIF($B:$B,b1) =1

1과 같다로 입력해도 동일하게 동작합니다.

​

➕ COUNTIF 함수: 통계 함수

설명: 선택된 범위에서 조건을 만족하는 셀의 개수를 구할 수 있다

구문: = COUNTIF(범위, 조건)

 

중복된 쿠폰번호 확인하기

기존에 없는/사용하지 않은 쿠폰번호를 입력하면 입력 가능합니다.

사용된 쿠폰 번호를 입력하면 아래와 같은 오류 팝업이 나타납니다.

 

 

“이 값은 이 셀에 정의된 데이터 유효성 검사 제한에 부합되지 않습니다”

엑셀 중복값 에러

 

 

중복값 방지 사용 시, 주의사항

중복값을 방지하는 데이터 유효성 검사를 적용할 때, 주의할 점이 있습니다. 바로 새로운 데이터에만 적용된다는 점입니다. 기존에 입력된 데이터에는 이 기능이 적용되지 않기 때문에, 이미 입력된 중복값을 확인하거나 방지할 수 없습니다. 이 점을 고려하여 데이터를 관리하고 새로운 데이터를 입력할 때 유효성 검사를 적용하는 것이 좋습니다. 즉, 새롭게 추가되는 데이터에만 데이터 유효성 검사 기능이 적용되고 기존에 입력된 데이터에는 적용되지 않습니다.

 

➕=AND(COUNTIF($B;$B, B1)<=1,COUNTIF($F;$F, B1)>=1) 설명

AND 함수는 지정한 조건을 모두 만족할 때 TRUE값을 반환.

AND 함수의 조건으로 추가한 첫 번째 COUNTIF 는 사용된 쿠폰에 중복 값이 있는지 검사하고

두번 째 COUNTIF 함수에 입력된값이 F열에 발송된 쿠폰 중 하나인지 검사.

따라서 입력된 값이 B열에는 없고, F열에는 있어야만 정상적으로 입력됨.

​

➕=AND(COUNTIF($B;$B, B1)<=1,COUNTIF($F;$F, B1)>=1) 분석

엑셀 AND 함수: 주어진 여러 조건이 모두 참(True)인 경우에만 참을 반환하는 논리 함수입니다.

이 함수는 두 개 이상의 조건을 함께 사용하여 논리적인 "AND" 연산을 수행합니다.

 

🤯 COUNTIF($B:$B, B1)<=1

열 B에서 B1 셀의 값과 일치하는 값을 찾아 카운트

COUNTIF($B:$B, B1)은 열 B에서 B1 셀의 값과 일치하는 항목의 수를 세는 함수

값이 1보다 작거나 같으면 참(True)을 반환

즉, B1 셀의 값이 열 B에 한 번 이하로 나타난다면 이 조건은 참(True)이 됩니다.

​

🤯 COUNTIF($F:$F, B1)>=1

열 F에서 B1 셀의 값과 일치하는 값을 찾아 카운트

COUNTIF($F:$F, B1)은 열 F에서 B1 셀의 값과 일치하는 항목의 수를 세는 함수

값이 1 이상이면 참(True)을 반환

즉, B1 셀의 값이 열 F에 한 번 이상 나타난다면 이 조건은 참(Ture)이 됩니다.

새롭게 추가되는 쿠폰 번호가 이미 사용된 쿠폰 번호인지 확인하는 역할을 합니다. ​

 

🤯 AND(COUNTIF($B:$B, B1)<=1, COUNTIF($F:$F, B1)>=1)

1. COUNTIF($B:$B, B1)<=1: 열 B에서 현재 셀 (B1)의 값이 1번 이하로 나타나는지 확인합니다. 즉, 현재 값이 중복되지 않는지 확인합니다.
2. COUNTIF($F:$F, B1)>=1: 열 F에서 현재 셀 (B1)의 값이 1번 이상 나타나는지 확인합니다. 즉, 현재 값이 이미 사용된 쿠폰인지 확인합니다.

AND 함수는 두 개의 조건이 모두 TRUE일 때만 TRUE를 반환하므로, 이 함수는 열 B에서 중복되지 않으면서도 열 F에서 이미 사용된 경우에만 TRUE를 반환합니다. 이것은 주로 데이터에서 중복을 방지하고 유효한 데이터를 필터링하기 위해 사용됩니다. 예를 들어, 열 B에서 값이 한 번 이하로 나타나고 동시에 열 F에서는 한 번 이상 나타나는 경우에 해당되는 데이터를 찾는 데 사용할 수 있습니다.

 

---

 

엑셀 중복 데이터 방지, 함수 하나로 쉽게 해결하는 방법 | 진짜쓰는 실무엑셀 2-3-2 

 

공부한 것 정리하고 추가하였습니다.

 

 

---