Excel에서 불일치 찾기 – 5가지 쉬운 기술 설명

Microsoft Excel에서 대규모 데이터 세트로 작업할 때 데이터 불일치가 불가피합니다. 이러한 불일치를 식별하고 수정하는 것은 정확한 보고 및 분석을 보장하는 데 중요합니다.

좋은 뉴스? Excel 에는 이러한 문제를 찾고 해결하는 데 도움이 되는 다양한 도구와 기술이 함께 제공됩니다. 이 기사에서는 가장 효과적인 몇 가지 방법에 대해 배웁니다. 여기에는 필터링, 조건부 서식 및 고급 수식 사용이 포함됩니다.

이러한 기술을 일상적인 워크플로에 통합하면 데이터를 보다 효율적으로 관리하고 분석할 수 있습니다. 궁극적으로 더 나은 의사 결정과 프로젝트 결과 개선으로 이어집니다.

목차

• Excel의 불일치 이해
• Excel에서 불일치를 찾는 5가지 방법
• #1 – Excel에서 수동 검토를 통해 불일치 찾기
• #2 – Excel에서 필터를 사용하여 일치하지 않는 데이터 감지 및 수정
• 3# – 조건부 서식으로 불일치 강조 표시
• #4 – 고급 Excel 기능으로 불일치 찾기
  • IF 및 IS 함수
  • VLOOKUP, HLOOKUP 및 XLOOKUP
  • 성냥
• #5 – 불일치 감지를 위해 Excel 추가 기능 사용
• 데이터 유효성 검사 규칙을 생성하여 불일치를 방지하려면 어떻게 해야 합니까?
• 우리의 마지막 말

Excel의 불일치 이해

행과 열의 불일치는 사람의 입력 오류, 중복 항목 또는 데이터 형식의 불규칙성으로 인해 발생할 수 있습니다.

다음 데이터를 살펴보십시오. Walter White 와 Wayne Cena 의 성별이 Male 대신 Mael로 잘못 입력되었음을 알 수 있습니다 . 실제로 이 시나리오에서 오류를 확인하고 수정하는 것은 간단합니다. 그러나 수천 개의 행과 열이 포함된 스프레드시트의 경우에는 그렇지 않습니다.

이러한 불일치를 찾아 관리하려면 다음 Excel 기능을 사용할 수 있습니다.

• 필터: 특정 열에 필터를 적용하여 고유한 값을 보고 일관성 없는 데이터를 빠르게 찾아냅니다.
• 조건부 서식: 서식 규칙을 사용하여 특정 기준을 충족하는 셀이나 열 간에 일치하지 않는 데이터를 강조 표시합니다.
• 고급 Excel 기능: MATCH 및 VLOOKUP 과 같은 기능을 활용하여 데이터 세트를 비교하고 불일치를 식별합니다.

Excel에서 이러한 기능을 사용하는 방법을 이해하면 데이터 세트에서 불일치를 효율적으로 찾아 수정할 수 있습니다.

Excel에서 불일치를 찾는 5가지 방법

Excel에서 불일치를 해결하는 5가지 주요 방법이 있습니다.

1. 직접 검토
2. 필터링
3. 조건부 서식
4. 고급 Excel 기능
5. Excel 추가 기능

자신에게 가장 적합한 방법을 찾을 수 있도록 각각을 살펴보겠습니다.

#1 – Excel에서 수동 검토를 통해 불일치 찾기

Excel 로 작업할 때 수동 검토는 데이터의 불일치를 식별하는 데 유용한 방법이 될 수 있습니다. 이 프로세스에서는 불일치나 오류를 발견하기 위해 데이터 세트를 주의 깊게 검사해야 합니다.

다음은 수동 검토를 수행하기 위해 수행할 수 있는 몇 가지 단계입니다.

• 작업 중인 데이터 세트를 명확하게 이해하는 것부터 시작하십시오. 행, 열 및 형식에 익숙해지십시오.
• 데이터를 스크롤하고 각 셀에 표시되는 정보에 주의를 기울이십시오. 채우기 색상, 텍스트 서식 또는 조건부 서식으로 셀 강조 표시와 같은 도구를 사용하여 데이터를 더 쉽게 읽을 수 있습니다.
• 한 행을 다른 행과 비교하거나 한 열을 다른 행과 비교하십시오. Excel 시트에서 부정확성을 유발할 수 있는 일치하지 않는 정보, 중복 항목 등을 찾으십시오.
• 불일치를 식별하면 목록을 만들거나 주석을 사용하여 문서화하십시오. 나중에 이러한 항목은 문제를 수정하는 데 중요한 참고 자료가 될 수 있습니다.

Excel 스프레드시트에서 데이터 불일치를 수동으로 검토할 때 인내심이 핵심입니다. 이 과정은 시간이 많이 걸립니다. 그러나 잠재적인 오류를 식별하기 위한 좋은 출발점입니다.

#2 – Excel에서 필터를 사용하여 일치하지 않는 데이터 감지 및 수정

일관성 없는 데이터를 감지하는 가장 쉬운 방법 중 하나는 Excel의 필터를 사용하는 것입니다 .

필터를 사용하여 다음 데이터 세트의 Gender 열 에서 잘못된 데이터를 분리합니다 .

1. 홈 탭 에서 정렬 및 필터를 선택합니다 .

2. 필터를 선택하십시오 – 커서가 데이터 범위에 있는지 확인하십시오.

3. 데이터가 일치하지 않는 열에서 드롭다운 목록 화살표를 클릭합니다.

4. 이제 올바른 일치 데이터를 모두 선택 취소합니다. 우리의 경우에는 Male 입니다 . 그런 다음 확인을 클릭하여 변경 사항을 저장합니다.

5. 이제 Excel 워크시트에 잘못된 값이 있는 행만 표시됩니다. 문제를 해결하려면 홈 탭 에서 찾기 및 선택을 클릭합니다 .

6. 드롭다운 목록이 나타나면 바꾸기를 클릭합니다.

7. 찾을 내용 에 잘못된 값을 입력 하고 바꿀 내용에 올바른 값을 입력합니다 . 그런 다음 모두 바꾸기 를 클릭합니다 .

8. 변경 사항을 확인하는 대화 상자가 나타납니다. 계속하려면 확인을 클릭하십시오 .

3# – 조건부 서식으로 불일치 강조 표시

열과 행의 차이점을 식별하는 또 다른 방법은 조건부 서식을 사용하는 것입니다.

하이라이트 셀, 상단/하단, 데이터 막대, 색상 척도 및 철 세트와 같은 규칙에 액세스할 수 있습니다. 또한 새 규칙을 만들고 이전 규칙을 관리하거나 지울 수 있습니다.

샘플 표를 사용하여 조건부 서식의 장점을 보여드리겠습니다.

1. 테이블 또는 선호하는 행과 열을 선택합니다. A열(회사)과 B열(국가)을 선택합니다. 그런 다음 홈 탭 에서 조건부 서식을 선택합니다 .

2. 규칙 유형을 선택한 다음 규칙을 선택합니다. 하이라이트 셀 규칙 과 중복 값을 선택하고 있습니다 …

3. 이제 중복 또는 고유 값을 강조 표시할지 여부와 채우기 색상 및 글꼴을 선택하여 셀 서식을 지정 해야 합니다 . 완료되면 확인 을 클릭합니다 .

4. 이제 모든 중복 값이 ​​진한 빨간색 텍스트가 있는 밝은 빨간색 채우기로 강조 표시됩니다.

#4 – 고급 Excel 기능으로 불일치 찾기

IF 및 IS 함수

셀을 비교하고 차이점을 식별하기 위해 IF 및 IS 기능을 활용할 수 있습니다.

=IF(A2=B2,"MATCH","NOT MATCHED")예를 들어 셀 A2와 B2를 비교하는 수식을 사용했습니다 . 그리고 수식을 드래그하여 다른 셀에 적용했습니다. 따라서 동일한 셀이 MATCH를 반환하고 그렇지 않으면 NOT MATCHED를 반환합니다 .

IF를 ISNUMBER 또는 ISTEXT와 같은 IS 함수와 결합하여 특정 데이터 유형을 확인할 수 있습니다. 예를 들어 수식은 B2가 숫자이면 열 c에 Number를=IF(ISNUMBER(B2), "Number", "NaN") 반환 하고 그렇지 않으면 NaN을 반환합니다 .

VLOOKUP, HLOOKUP 및 XLOOKUP

VLOOKUP(수직) 및 HLOOKUP(수평) 함수는 두 테이블의 서로 다른 열 또는 행의 값을 비교할 때 유용합니다. 이를 수행하려면 다음 수식을 사용하십시오. =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) 또는 =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

lookup_value를 다른 테이블에서 찾으려 는 값으로, table_array를 두 번째 테이블의 셀 범위로, col_index_num 또는 row_index_num을 값을 반환하려는 열 또는 행의 인덱스 번호로, [range_lookup] 을 정확히 일치하는 경우 FALSE , 근사 일치하는 경우 TRUE입니다 .

다음 자습서에서는 E열의 VLOOKUP 함수를 사용하여 A열과 D열의 직원 이름을 비교하여 일치하는 항목이 있으면 B열에서 급여를 가져옵니다.

XLOOKUP은 Excel 2021 및 Excel 365에서 사용할 수 있는 VLOOKUP 및 HLOOKUP의 새로워진 버전입니다. XLOOKUP 공식은 다음과 같습니다 =XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found]).

lookup_value를 다른 테이블에서 찾으려는 값으로, lookup_array를 두 번째 테이블에서 검색할 셀 범위로, return_array를 값을 반환하려는 배열로, [if_nout_found] 를 일치하지 않는 경우 텍스트 값으로 대체합니다 . 값이 발견됩니다.

다음은 동일한 테이블이 있는 XLOOKUP의 빠른 샘플입니다.

성냥

MATCH 함수는 두 목록의 불일치를 비교하는 데에도 사용할 수 있습니다.

MATCH 함수 는 =MATCH(lookup_value, lookup_array, [match_type])범위에서 지정된 항목을 검색하고 해당 범위 내에서 항목의 상대적 위치를 반환합니다.

여기서는 MATCH를 사용하여 배열 A2:A8 에서 수박 의 위치를 ​​찾습니다 . lookup_value 와 정확히 동일한 첫 번째 값을 찾기 위해 match_type 을 0 으로 사용하고 있습니다 .

#5 – 불일치 감지를 위해 Excel 추가 기능 사용

Excel은 불일치를 보다 효율적으로 감지하고 분석하는 데 도움이 되는 다양한 추가 기능과 기본 제공 도구를 제공합니다. 이 섹션에서는 몇 가지 유용한 추가 기능에 대해 설명합니다.

먼저 Spreadsheet Inquire 추가 기능 사용을 고려하십시오. 이렇게 하면 두 통합 문서를 비교하고 셀별로 차이점을 강조 표시할 수 있습니다. Spreadsheet Inquire 추가 기능을 활성화하려면:

1. 파일 탭을 클릭합니다.

2. 옵션 을 선택한 다음 추가 기능 범주를 클릭합니다.

3. 관리 상자에서 COM 추가 기능을 선택 하고 이동을 클릭합니다 .

4. 스프레드시트 조회 상자를 체크하고 확인을 클릭합니다 .

5. 활성화되면 조회 탭으로 이동하여 파일 비교 명령을 사용합니다 .

불일치 감지를 위한 또 다른 유용한 도구는 Analysis ToolPak 입니다 . 이 추가 기능은 불일치를 분석할 때 유용할 수 있는 고급 통계 기능 및 데이터 분석 도구를 제공합니다.

Analysis ToolPak을 활성화하려면 Spreadsheet Inquire 추가 기능을 활성화하는 것과 동일한 단계를 따르십시오. 그러나 관리 상자 에서 Excel 추가 기능을 선택 하고 분석 도구 상자를 선택하십시오.

요약하면 Excel은 데이터의 불일치를 감지하고 처리하는 데 도움이 되는 다양한 추가 기능을 제공합니다. 이러한 도구와 공식에 익숙해지면 데이터 정확도를 향상시킬 수 있습니다.

데이터 유효성 검사 규칙을 생성하여 불일치를 방지하려면 어떻게 해야 합니까?

불일치를 방지하는 데 도움이 되는 데이터 유효성 검사 규칙을 Excel에서 만들려면 다음 단계를 따르세요.

1. 제한할 셀을 선택합니다. 단일 셀, 셀 범위 또는 전체 열이 될 수 있습니다.

2. 도구 모음의 데이터 탭 으로 이동 하고 데이터 유효성 검사 버튼(두 개의 가로 상자, 녹색 확인 표시 및 빨간색 교차 원으로 표시됨)을 클릭합니다.

3. 데이터 유효성 검사 창에서 설정 탭 에 있는지 확인합니다 . 여기에서 필요에 따라 검증 기준을 정의할 수 있습니다.

Excel에서 사용할 수 있는 다양한 유형의 유효성 검사 기준이 있으며 그 중 일부는 다음과 같습니다.

• 정수 – 지정된 범위 내의 정수만 허용합니다.
• 10진수 – 지정된 범위 내의 10진수만 허용합니다.
• 목록 – 허용 가능한 값의 미리 정의된 목록으로 입력을 제한합니다.
• 날짜 – 특정 날짜 범위 내의 날짜가 필요합니다.
• 시간 – 특정 시간 범위 내의 시간만 허용합니다.
• 텍스트 길이 – 입력한 텍스트 길이에 제약을 적용합니다.
• 사용자 지정 - Excel 수식을 사용하여 사용자 지정 유효성 검사 규칙을 만들 수 있습니다.

적절한 유효성 검사 기준을 선택한 후 필요에 따라 매개변수를 지정합니다. 예를 들어 정수를 선택한 경우 사이, 같음, 보다 작음 등과 같은 데이터 범위 유효성 검사기 로 최소값 과 최대값을 설정해야 합니다.

유효성 검사 기준 외에도 사용자 지정 오류 메시지 및 입력 힌트를 설정하여 사용자가 유효성 검사 규칙을 이해하고 준수하는 데 도움을 줄 수 있습니다. 이렇게 하려면 입력 메시지 및 오류 경고 탭으로 전환하여 원하는 메시지를 입력합니다.

데이터 유효성 검사 규칙을 구현하면 데이터 불일치 가능성이 크게 줄어들고 Excel 워크시트의 전반적인 정확도가 향상됩니다.

우리의 마지막 말

이 문서에서는 Excel 데이터에서 불일치를 찾고 해결하는 다양한 방법을 배웠습니다. 핵심 전략을 요약해 보겠습니다.

• 열에서 필터 드롭다운을 사용하여 고유한 값과 일관되지 않은 데이터를 식별합니다.
• 여러 규칙 템플릿을 사용하여 불일치를 강조 표시하기 위해 선택한 셀에서 조건부 서식 을 사용합니다 .
• MATCH , VLOOKUP , XLOOKUP 등과 같은 고급 Excel 기능을 적용하여 두 개의 목록 또는 테이블에서 데이터를 교차 확인합니다.
• Excel 추가 기능을 사용하여 불일치를 감지합니다.
• 데이터 오류를 방지하기 위한 데이터 유효성 검사 규칙 설정

Excel 기술 세트에서 이러한 기술을 사용하면 데이터가 더 정확하고 신뢰할 수 있습니다.

스프레드시트에서 불일치를 보다 효율적으로 감지하려면 이러한 방법을 정기적으로 연습해야 합니다.

에 등록하여 기술을 계속 연마 하고 새로운 기능을 계속 탐색하여 Excel에 더욱 능숙해지십시오.