빠른 핵심 요약
✅ 데이터유효성 검사: [데이터]> [데이터 유효성 검사] > [유효성 조건] >[사용자 지정]> [수식] 에서 조건 작성
✅ VLOOKUP 함수: 수직 방향으로 데이터 검색하는 함수
✅ IFERROR 함수: 오류 처리를 담당하는 함수
✅ COUNTIF 함수: 선택된 범위에서 조건을 만족하는 셀의 개수를 구하는 함수
✅ AND 함수: 주어진 여러 조건이 모두 참(True)인 경우에만 참을 반환하는 함수
엑셀에서 중복 데이터 관리 방법
사원번호, 쿠폰 번호 등 고유 데이터 값으로 데이터 관리해야 할 때가 있습니다. 하지만 일반적인 데이터베이스 프로그램과 달리, 엑셀은 중복 값 제한하는 기능이 취약합니다. 이런 문제를 해결하기 위해서 엑셀에서 중복되는 데이터를 처리하는 방법 중 하나가 [데이터 유효성 검사]를 사용하여 중복을 방지하는 것입니다. [데이터 유효성 검사] 기능을 사용하면 데이터 범위에 중복된 값을 입력할 수 없게 되어 엑셀에서 중복을 간단하게 처리할 수 있습니다.
📢 데이터 유효성 검사 부분만 읽고 싶다면 "엑셀 데이터 유효성 검사 적용 방법"으로 스크롤 내려주세요!
엑셀에서 중복값 방지하기: 데이터 유효성 검사
쿠폰 발송 및 사용여부 예시를 통해 살펴봅시다. 예를 들어 발송된 쿠폰과 사용된 쿠폰번호 비교한다면, 쿠폰번호의 경우 중복 사용을 방지하기 위해 쿠폰 코드 당 한 번만 입력할 수 있도록 중복 값 입력을 제한해야 합니다.
이 경우, 엑셀 데이터 유효성 검사를 통해서 중복 값 입력을 제한할 수 있습니다. 즉, 쿠폰을 한 번만 사용할 수 있게 만들 수 있습니다.
![엑셀 기초 엑셀 함수 데이터유효성 검사 vlookup countif iferror 함수](https://blog.kakaocdn.net/dn/dESlQZ/btsHssmg8rl/f6cefiamtb8ejs7thsSs0k/img.jpg)
➕ 쿠폰 중복사용 방지를 위해 사용한 수식: 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 함수의 매개변수는 다음과 같습습니다.
- B6: 검색할 값을 나타냅니다. 즉, 함수가 이 셀에서 값을 찾습니다.
- $F$4:$H$17: 데이터 범위를 나타냅니다. 함수가 이 범위에서 데이터를 찾습니다. $ 기호는 범위를 절대로 고정시키는 것을 의미하므로, 범위가 복사되거나 이동할 때 참조가 변경되지 않습니다.
- 2: 데이터가 포함된 열의 위치를 나타냅니다. 여기서는 데이터 범위 표에서 두 번째 열에 해당합니다.
- 0: 이는 정확한 일치를 의미합니다. 즉, 검색된 값이 정확히 일치해야 합니다. 일반적으로 이 값을 사용하여 정확한 일치를 원할 때 사용합니다.
따라서 이 VLOOKUP 함수는 B6 셀에서 시작하여 데이터 범위 $F$4:$H$17에서 두 번째 열에서 값을 찾고, 정확한 일치를 찾습니다.
IFERROR(VLOOKUP(B6,$F$4:$H$17,2,0),"-")
IFERROR 함수는 특정 함수의 결과가 오류인 경우를 처리하기 위해 사용됩니다. 이 함수는 첫 번째 매개변수로 받은 함수의 결과가 오류인 경우 두 번째 매개변수로 지정한 값을 반환합니다.
=IFERROR(첫 번째 매개변수, 두 번째 매개변수)
- 첫 번째 매개변수 (VLOOKUP 함수): 이 매개변수는 실제로 실행하고자 하는 함수입니다. 여기서는 VLOOKUP 함수가 사용되었습니다. 이 함수를 사용하여 데이터를 찾는 작업을 수행합니다.
- 두 번째 매개변수 (-): 이 매개변수는 첫 번째 매개변수로 전달된 함수의 결과가 오류일 때 반환할 값을 나타냅니다. 이 경우에는 대시(-)를 반환하여 데이터를 찾을 수 없는 경우 대시를 표시합니다.
따라서 IFERROR 함수는 B6 셀에서 값이 검색될 때까지 $F$4:$H$17 범위에서 데이터를 검색하고, 검색된 값이 없으면 대시(-)를 반환합니다. 이렇게 함으로써 데이터를 처리할 때 발생할 수 있는 오류를 미리 대비할 수 있습니다.
엑셀 데이터 유효성 검사 적용 방법
엑셀 중복 데이터를 입력하지 못하게 하는 방법
1. 범위 선택: 중복 값을 제한할 열 전체를 선택합니다.
- 중복 값을 제한할 전체 열에 규칙을 적용합니다.
- 제한할 범위만 데이터 유효성 검사를 적용할 경우, 새롭게 추가되는 데이터는 규칙이 적용되지 않기 때문입니다.
2. 데이터 유효성 검사 설정: [데이터]> [데이터 도구] 영역> [데이터 유효성 검사] 클릭
[데이터] 탭에서 [데이터 도구] 영역의 [데이터 유효성 검사]를 클릭합니다.
3. 유효성 조건 설정: 유효성 조건] >[제한 대상] > [사용자 지정] 선택> [수식:]에서 조건 작성
[유효성 조건] 창이 열리면, [제한 대상]에서 [사용자 지정]을 선택합니다.
[수식:] 란에 아래 예시와 같은 수식을 입력하거나 원하는 수식에서 조건을 작성합니다.
수식 예시:
- =A1 <1: 1보다 작은 값만 입력 가능
- =LEN(A1)=4: 글자 수가 4글자인 값만 입력 가능
➕ 데이터 유효성 검사로 수식 작성 시, 수식의 조건을 만족하는 데이터만 입력되도록 값을 제한할 수 있습니다.
➕ LEN(함수): 값의 문자 수를 반환합니다.
엑셀 COUNTIF 함수로 중복 값 방지하기
엑셀에서 COUNTIF 함수를 사용하여 중복 값을 방지하는 방법을 설명드리겠습니다. 이 방법은 특정 열(예: B 열)에서 중복된 값을 입력하지 못하도록 하는 데이터 유효성 검사를 설정합니다. 새로운 값을 입력할 때마다 해당 값이 B 열에서 유일한 값인지 확인하며, 중복된 값을 입력하려고 하면 경고 메시지가 표시됩니다. COUNTIF함수가 아래 과정을 통해 결괏값을 보여줄 것입니다.
- B 열 전체 범위 선택
- 새롭게 추가한 값(쿠폰 코드)이 몇 개 인지 개수를 센 후,
- 개수가 1인 경우에만 입력 가능 (= 값이 한 게임, 고윳값인 경우)
COUNTIF 함수 수식 작성 방법
완성 수식: =COUNTIF($B:$B,B1)<=1
- B 열 전체 선택: B 열 전체를 선택합니다. 클릭한 셀이 B1이라면, Ctrl + Shift + ↓를 눌러 B 열 전체를 선택
- 절대 참조 설정: F4 키를 눌러 절대 참조로 변경. B:B가 $B:$B로 변경됨.
- 이름 상자에서 활성화된 셀 확인
- 비교할 셀이 활성화된 셀이 B1인지 확인합니다.
- 결괏값이 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(범위, 조건)
중복된 쿠폰번호 확인하기
기존에 없는/사용하지 않은 쿠폰번호를 입력하면 입력 가능합니다.
사용된 쿠폰 번호를 입력하면 아래와 같은 오류 팝업이 나타납니다.
“이 값은 이 셀에 정의된 데이터 유효성 검사 제한에 부합되지 않습니다”
엑셀 중복값 에러
![엑셀 기초 엑셀 함수 데이터유효성 검사 vlookup countif iferror 데이터유효성 검사 검토과정 테스트](https://blog.kakaocdn.net/dn/lgCLr/btsHtLsV5lP/joKPckPVXiIbWrFxoLuJy0/img.jpg)
중복값 방지 사용 시, 주의사항
중복값을 방지하는 데이터 유효성 검사를 적용할 때, 주의할 점이 있습니다. 바로 새로운 데이터에만 적용된다는 점입니다. 기존에 입력된 데이터에는 이 기능이 적용되지 않기 때문에, 이미 입력된 중복값을 확인하거나 방지할 수 없습니다. 이 점을 고려하여 데이터를 관리하고 새로운 데이터를 입력할 때 유효성 검사를 적용하는 것이 좋습니다. 즉, 새롭게 추가되는 데이터에만 데이터 유효성 검사 기능이 적용되고 기존에 입력된 데이터에는 적용되지 않습니다.
➕=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)
- COUNTIF($B:$B, B1)<=1: 열 B에서 현재 셀 (B1)의 값이 1번 이하로 나타나는지 확인합니다. 즉, 현재 값이 중복되지 않는지 확인합니다.
- COUNTIF($F:$F, B1)>=1: 열 F에서 현재 셀 (B1)의 값이 1번 이상 나타나는지 확인합니다. 즉, 현재 값이 이미 사용된 쿠폰인지 확인합니다.
AND 함수는 두 개의 조건이 모두 TRUE일 때만 TRUE를 반환하므로, 이 함수는 열 B에서 중복되지 않으면서도 열 F에서 이미 사용된 경우에만 TRUE를 반환합니다. 이것은 주로 데이터에서 중복을 방지하고 유효한 데이터를 필터링하기 위해 사용됩니다. 예를 들어, 열 B에서 값이 한 번 이하로 나타나고 동시에 열 F에서는 한 번 이상 나타나는 경우에 해당되는 데이터를 찾는 데 사용할 수 있습니다.
엑셀 중복 데이터 방지, 함수 하나로 쉽게 해결하는 방법 | 진짜쓰는 실무엑셀 2-3-2
공부한 것 정리하고 추가하였습니다.
![](https://blog.kakaocdn.net/dn/dCM2OZ/btsHsHebyN8/RwJyOLNBSZkVJ3I1voDXX0/img.gif)
'엑셀' 카테고리의 다른 글
엑셀에서 여러 줄로 된 데이터를 한 줄로 만드는 단축키 | 진짜쓰는 실무엑셀 (0) | 2024.05.24 |
---|---|
엑셀 빠른채우기, 텍스트 나누기, 양쪽 맞춤 A to Z | 진짜 쓰는 실무 엑셀 (0) | 2024.05.23 |
엑셀 시트 동시 비교 방법 & 단축키 | 진짜쓰는 실무엑셀 (0) | 2024.05.22 |
엑셀을 사용한 대용량 데이터 관리: 이름 범위 활용 | 진짜쓰는 실무엑셀 (0) | 2024.05.21 |
엑셀 데이터 분석: "행/열 바꿈" & 선택하여 붙여넣기 | 진짜 쓰는 실무 엑셀 (0) | 2024.05.18 |
엑셀 천 단위, 만 단위, 억 단위 절사 방법 | 진짜 쓰는 실무 엑셀 (0) | 2024.05.17 |
엑셀 조건부 서식으로 실시간 검색 & 강조하는 방법 | 진짜 쓰는 실무 엑셀 (0) | 2024.05.16 |
엑셀 셀에 한글 또는 영어만 입력되도록 설정하는 방법 | 진짜 쓰는 실무 엑셀 (0) | 2024.05.15 |