[미친 활용 39] 서로 다른 데이터를 한 번에 찾아내는 단축키
두 범위의 데이터를 비교해서 서로 다른 값을 찾을 때, 번거롭게 수식을 작성해서 찾으셨나요? 수식을 작성하는 시간, 수식을 다른 셀에 적용하는 시간, 비교 후 수식을 지우는 시간 등 생각보다 시간 소요가 많습니다. 이때는 단축키로 서로 다른 데이터를 한 번에 찾아보세요.
01 ➊ 데이터를 비교할 셀 범위를 선택하고 ➋ 단축키 Ctrl+￦를 누릅니다. [데이터1]과 [데이터2]에서 서로 다른 값이 적힌 셀이 자동으로 선택됩니다.
02 비교할 데이터가 서로 떨어져 있다면 ➊ [데이터1] 열의 데이터 값을 모두 선택하고 ➋ Ctrl을 누른 채 [데이터3] 열의 데이터 값을 모두 선택합니다.
03 단축키 Ctrl+￦를 누릅니다. [데이터1]과 [데이터3]에서 서로 다른 값이 적힌 셀이 자동으로 선택됩니다.
단축키 Ctrl+￦는 첫 번째 선택한 열(기준)과 다른 데이터를 찾아내므로, 비교할 데이터가 서로 붙어 있거나 서로 떨어져 있으면 다른 데이터가 표시되는 위치가 달라집니다. 즉, 비교 데이터가 서로 붙어 있을 때 왼쪽 → 오른쪽으로 드래그해서 선택했다면 다른 데이터가 오른쪽 셀 범위에 표시됩니다. 반면에 오른쪽 → 왼쪽으로 드래그해서 선택했다면 다른 데이터는 왼쪽 셀 범위에 표시됩니다. 비교 데이터가 서로 떨어져 있으면 먼저 선택한 셀 범위에 표시됩니다.
[AI 치트키] 원본 데이터를 비교해 서로 다른 데이터를 빠르게 찾기
수식이나 단축키를 통해 확인하거나 서로 다른 데이터만을 찾아내 별도로 분류해야 하는 상황이라면, 기준 데이터와 비교 데이터를 명시해주고, 데이터 분류를 지시하면 빠르게 결과를 도출할 수 있습니다. 이때 AI에게 엑셀 원본 데이터를 전달해도 되지만 비교한 데이터의 용량이 크다면 CSV 파일로 변환해서 전달해보세요. 더 효율적으로 AI가 정보를 읽어낼 수 있습니다.
[나] : 처음 전달한 데이터는 기준이 되는 원본 데이터이고 두 번째 전달한 데이터는 비교할 원본 데이터야. 비교 데이터에서 기준 데이터와 서로 다른 값이 있는지 찾고, 해당 부분만을 별도로 추출하여 리스트로 만들어줘.
[미친 활용 40] A 테이블에서 B 테이블에 없는 값을 빠르게 찾기
다음과 같이 워크샵 참석 희망자와 참석자가 적혀 있는 데이터 테이블에서 미참석자에 대한 명단을 찾아야 한다면, 어떤 방법이 가장 먼저 떠오르나요?
참석자 셀과 희망자 셀을 합친 다음에 중복된 항목 제거 기능을 사용하는 방법을 생각했나요? 이때는 조건부 서식을 활용하면 클릭 몇 번으로 고윳값이 한눈에 보이도록 강조할 수 있습니다. 강조한 색상을 기준으로 정렬하면 고윳값만 따로 모아볼 수도 있어, 데이터 비교와 분석을 훨씬 빠르게 처리할 수 있습니다.
01 ➊ 데이터가 적혀 있는 셀을 모두 선택하고 ➋ [홈 → 스타일 → 조건부 서식 → 셀 강조 규칙 → 중복 값]을 클릭합니다.
02 [중복 값] 대화상자에서 ➊ 항목을 [고유]로 선택 ➋ [적용할 서식]을 자유롭게 설정한 후 ➌ [확인]을 클릭합니다. 참석 희망자와 참석자 값을 비교해 없는 값(미참석자)만 강조됩니다.
<직트키 노하우> 엑셀 버전에 따라 미참석자 추출 방법이 달라요
엑셀 2021 이상에서는 FILTER 함수로 미참석자만 추출할 수 있습니다.
=FILTER(B6:B13, COUNTIF(C6:C13, B6:B13)=0) : 참석 희망자(B6:B13) 중에서 참석자(C6:C13) 명단을 제외한 결괏값을 추출합니다.
엑셀 2021 이하에서는 보조 열을 추가하고, 다음 함수로 미참석자만 추출할 수 있습니다.
=IF(COUNTIF($C$6:$C$13, B6)>0, "참석", "미참석") : 참석자(C6:C13) 명단에서 참석 희망자(B6)가 있는지 COUNT하여, 있다면 참석을, 없다면 미참석을 표기합니다.
보조열에 필터를 걸어 ‘미참석’으로 필터링하면 참석희망자 명단에는 ‘미참석자’만 표시됩니다.
[AI 치트키] 데이터를 비교해 누락된 데이터를 찾아내기
실제 사용하는 양식의 형태 때문에 수식을 직접 사용해서 비교가 어렵다면 각 원본 데이터를 복사해 AI에게 분류를 지시해보세요. 지시할 때는 ‘기준’이 되는 데이터가 어떤 것인지를 반드시 명시해주세요.
[나] : 전달하는 데이터 중 먼저 전달한 데이터를 기준으로 나중에 전달한 데이터와 비교하여 나중에 전달한 데이터에는 없는 항목을 나열해줘.
[미친 활용 41] 중복 데이터를 빠르게 솎아내기
같은 이름이 반복되는 데이터에서 중복 없는 명단만 추출해야 할 때가 있습니다. 이런 업무는 중복된 항목 제거 기능을 사용해서 처리했나요? 이제는 UNIQUE 함수를 사용해보세요. 범위를 선택하기만 하면 중복이 제거된 목록이 바로 추출되고, 원본 데이터가 바뀌면 결과도 알아서 척척 업데이트됩니다.
01 ➊ 고윳값을 표현할 셀을 선택하고 F2를 누릅니다. ➋ 커서가 깜박이면 =UNIQUE(C6:C17)을 입력하고 Enter를 누릅니다.
02 이름이 적혀 있는 범위에서 중복값을 제외한 값이 추출됩니다.
UNIQUE 함수는 엑셀 2021 버전 이상에서 사용할 수 있습니다. 엑셀 2019 및 그 이전 버전에서는 중복된 항목 제거 기능을 활용하거나 피벗 테이블을 사용해야 합니다.
[미친 활용 42] 중복 데이터를 입력하지 못하도록 설정하기
데이터 테이블에 기록된 데이터가 적다면, 내용을 금세 훑어보고 중복값을 입력하지 않을 수 있습니다. 하지만 데이터 테이블에 기록된 데이터가 많다면, 중복으로 입력하는 불필요한 수고를 해야 할 수 있습니다. 앞에서 다룬 방법들을 활용해 추후에 중복값을 제거하는 것도 한 방법이지만, 처음부터 중복값을 입력하지 못하도록 설정해둔다면, 보다 효율적으로 데이터 테이블을 관리할 수 있습니다.
01 ➊ 중복값을 입력하지 못하도록 설정할 셀 범위(B6:B12)를 모두 선택하고 ➋ [데이터 → 데이터 도구 → 데이터 유효성 검사]를 클릭합니다.
02 ➊ [데이터 유효성] 대화상자의 [제한 대상]에서 [사용자 지정]을 선택 ➋ [수식]에 =COUNTIF(B:B,B6)=1을 입력합니다. ➌ [오류 메시지] 탭을 선택 ➍ [유효하지 않은 데이터를 입력하면 오류 메시지 표시]에 체크하고 ➎ 제목과 오류 메시지를 입력한 후 ➏ [확인]을 클릭합니다.
03 [B11] 셀에 이미 입력된 이름 중에서 홍길동을 입력해봅니다. 다음과 같이 오류 메시지 창이 나타나는지 확인합니다.
수식 =COUNTIF(B:B,B6)=1은 B열 전체 범위에서 [B6] 셀 값과 같은 개수가 1일 때만 TRUE라고 선언합니다. B열에 같은 값이 입력되었을 때, 데이터 유효성 검사의 규칙에 따라 같은 값을 입력하지 못하도록 오류 창이 뜨는 원리입니다.