[미친 활용 87] 숨겨진 오류, 참조 오류 빠르게 정리하기
엑셀 업무를 하다가 자주 맞닥뜨리는 귀찮은 상황 중 하나는 데이터 중간중간 수식 오류가 포함되어 있는 경우가 아닐까 합니다. 데이터양이 많지 않을 때는 수식 하나씩 직접 검토하며 수정할 수 있지만, 양이 많다면 하나씩 찾아 수정하는 것은 엄청난 업무 부하로 이어집니다. 이렇게 대량 작업, 단순 반복 작업, 오류 수정 작업에 AI를 활용하면 시간을 대폭 절약할 수 있고, 직접 확인해서 수정하는 것보다 정확한 결괏값을 얻을 수 있습니다.
01 오류가 포함되어 있는 셀 범위를 모두 선택합니다.
02 ➊ 지정한 범위가 제대로 인식되었는지 확인 후 ➋ 오류 수정을 위한 프롬프트를 입력합니다.
[나] : B2:F16 범위 값 중에서 수식 오류(#VALUE!, #REF!, #DIV/0!)에 대한 부분을 오류가 발생하지 않도록 수정해.
03 이때 클로드가 파일 수정을 위한 권한을 요청하면 [Always allow] 또는 [Allow once]를 눌러 승인합니다.
수정에 아주 민감한 파일이 아니라면 [Always allow]를 눌러 항상 허용을 설정하는 것이 편할 겁니다.
04 그러면 다음과 같이 클로드가 분석한 내용과 변경할 내용을 알려줍니다.
입력한 프롬프트에 따라 클로드가 지정된 범위의 오류 셀을 찾아 올바르게 수정하는지 확인해보세요.
AI에게 명령어를 전달할 때, 범위를 정확히 명시하지 않으면 원하지 않는 범위까지 작업할 수 있습니다. 작업 대상 범위를 프롬프트에도 반드시 명시하여 명령어를 전달하는 것이 중요합니다.
[AI 치트키] 수정 전 변경 내용 먼저 확인하고 싶다면?
시트가 바로 수정되는 것을 원치 않는다면 프롬프트 마지막에 다음 문장을 추가하세요. AI가 값을 바로 바꾸지 않고 수정 대상과 변경 내용을 먼저 알려주기 때문에 작업 결과를 확인한 뒤 안전하게 수정할 수 있습니다.
[나] : 기존 값을 수정하지 말고 수정할 값이 맞는지 내가 먼저 확인할 수 있게 알려줘.
[미친 활용 88] 복잡한 수식, 빠르게 분석하기
직장 선배가 갑자기 퇴사하여 인수인계받은 엑셀 파일에 이 파일이 어떤 의도로 만들어졌는지, 언제 사용하는지 설명 한 줄 없이 수식만 덩그러니 있다면 가슴이 철렁 내려앉을 겁니다. 엑셀을 잘하지도 못하는데 상사는 수식이 틀린 것 같다고 수정하라고 하는 상황이라면, 적혀 있는 수식의 동작 방식을 일일이 확인해가며 파악해야 합니다. 이때 AI를 통해 해당 시트의 수식을 분석해달라고 요청하면, 직접 수식에 대한 내용을 찾아가며 확인하지 않아도 아주 빠르게 정리된 결과를 받아볼 수 있습니다.
01 수식 분석이 필요한 셀을 모두 선택합니다.
02 ➊ 지정한 범위가 제대로 인식되었는지 확인 후 ➋ 수식 분석을 위한 프롬프트를 입력합니다.
[나] : 선택한 셀 범위에 적용되어 있는 수식의 동작 원리를 설명해줘.
03 입력한 프롬프트에 따라 클로드가 지정된 범위의 수식을 제대로 분석하는지 확인합니다.
[AI 치트키] 수식 분석과 개선을 함께 요청해보세요
수식의 동작 원리만 확인하는 데서 끝내지 않고 같은 결과를 더 효율적으로 구하는 수식까지 함께 요청할 수도 있습니다. 함수를 학습할 때, 응용이 필요할 때 활용하면 좋겠죠?
[나] : C2:C4 범위에 적용된 수식의 동작 원리를 분석해 설명해주고, 해당 수식과 같은결괏값을 도출하되 조금 더 효율적인 수식을 E3 셀에 작성해줘.
[미친 활용 89] 원하는 수식 빠르게 만들기
회사 상사에게 일자별 판매 데이터가 적혀 있는 파일을 전달받으며 ‘월별, 상품별 판매 금액 총계 데이터를 뽑아줘.’라는 지시를 받았다고 가정해봅시다. 자료를 보니 SUMIFS 수식을 활용하면 구할 수 있을 것도 같은데, 막상 테스트해보니 생각한 결과가 나오지 않습니다. 어떤 형태로 데이터를 표현해야 할지, 어떤 수식을 써야 정확한 결과를 도출할 수 있을지 막막하다면 AI에게 내용만 전달해보세요. AI가 빠르고 깔끔하게 정리해줄 겁니다.
01 일자별 판매 내역 데이터를 모두 선택합니다.
02 지정한 범위가 제대로 인식되었는지 확인하고 결과 도출을 위한 프롬프트를 입력합니다.
[나] : 선택한 범위의 데이터를 '월별/상품별 판매 금액 총액'을 계산하여 보여줄 수 있도록 하고 싶어. 가로에는 '품목', 세로에는 '월'을 표기하고 각 항목의 판매금액을 구하는 수식을 작성해줘.
[AI] :
03 요구사항에 따라 클로드가 작업한 결과가 정상적으로 동작하는지 확인합니다.
[AI 치트키] 결과 표의 위치와 서식까지 지정하기
수식으로 계산할 내용만 전달하면 결과는 맞더라도 표의 위치나 모양이 기대와 다를 수 있습니다. 결과를 시작할 셀, 행과 열에 배치할 항목, 셀의 색상 등 구체적인 정보를 함께 지정하면 바로 보고서에 활용하기 좋은 형태로 결과를 만들 수 있습니다.
[나] : 선택한 범위의 데이터를 ‘월별/상품별 판매금액 총계’를 계산하여 보여줄 수 있게 하고 싶어.
요구사항은 다음과 같아.
F3 셀부터 가로에는 ‘품목’, 세로에는 ‘월’을 표시하고 각 항목의 판매금액을 구하는 수식을 작성
행의 헤더 배경색은 ‘회색’, 글자 색상은 ‘검정색’으로 처리
각 행/열의 합계를 표시하고, 셀 배경색은 ‘노란색’, 글자 색상은 ‘검은색’으로 처리
작업 후 적용한 수식에 대한 동작 원리를 설명해줄 것
[미친 활용 90] 데이터에서 원하는 값만 추출하기
항목별로 정리되어 있지 않은 원본 데이터에서 이름, 연락처 등을 추출해야 할 때가 있습니다. 원본 데이터가 콜론(:), 쉼표(,) 등 특정한 구분 기호로 분리되어 있거나, 특정 규칙이 있다면 LEFT 함수와 LEN 함수와 MID 함수 등을 조합해서 추출하면 됩니다. 하지만 특정 규칙이나 구분 기호가 명확하게 분리되어 있지 않고 데이터의 순서도 규칙적이지 않다면, 데이터를 추출하기가 난감합니다. 이런 비정형 데이터를 가공하거나 추출할 때는, AI를 활용해 원하는 항목의 데이터만을 빠르게 추출할 수 있습니다.
01 분석할 원본 데이터를 모두 선택합니다.
02 지정한 범위가 제대로 인식되었는지 확인 후 결과 도출을 위한 프롬프트를 입력합니다.
[나] : 선택한 범위에서 [이름, 직급, 회사명, 연락처, 주소]를 구분해서 D2셀부터 표 형태로 추출해주고, 정보가 없는 항목은 빈칸으로 처리해줘.
[AI] :
03 요구사항에 따라 클로드가 작업한 결과를 확인합니다.
[AI 치트키] 예외 처리 기준까지 함께 전달하기
비정형 데이터는 원하는 항목이 빠져 있거나 정해둔 항목에 넣기 애매한 내용이 섞여 있는 경우도 많습니다. 추출할 항목뿐만 아니라 빈 값 처리 방식, 형식 통일 기준, 남는 정보의 기록 위치까지 지정하면 결과를 다시 손보는 시간을 줄일 수 있습니다.
[나] : 선택한 범위의 데이터에서 [이름, 직급, 회사명, 연락처, 주소]를 추출해야 하는데, 아래 요구사항을 준수할 것
위 항목에 해당하지 않는 데이터는 [특이사항] 열에 별도로 기록할 것
위 항목에 해당되는 데이터가 없다면 ‘공백’으로 처리
연락처는 ‘010-1111-2222’ 형식으로 통일하여 표기 ④ 작업 결과는 D2 셀부터 표현
[미친 활용 91] 원하는 피벗 테이블 빠르게 만들기
일자별 판매 내역이 기록된 원본 데이터 테이블을 이용해 월별/지역별 판매 추이를 만들어야 합니다. 일반적으로 피벗 테이블을 활용하지만 피벗 테이블을 만들기 위해 행과 열, 값 영역 중 어디에 어떤 필드를 넣어야 원하는 데이터를 볼 수 있는지 한 번에 감이 오지 않습니다. 이때 AI의 도움을 받아보세요. 원하는 피벗 테이블을 쉽게 만들 수 있습니다.
01 분석할 원본 데이터를 모두 선택합니다.
02 ➊ 지정한 범위가 제대로 인식되었는지 확인 후 ➋ 결과 도출을 위한 프롬프트를 입력합니다.
[나] : 선택한 범위의 데이터를 기반으로 '월별/지역별 판매 내역 추이'를 나타내는 피벗 테이블을 만들어줘.
[AI] :
04 요구사항에 따라 클로드가 작업한 결과를 확인합니다.
[AI 치트키] 피벗 테이블 배치 기준을 구체적으로 정하기
피벗 테이블은 같은 원본 데이터라도 행, 열, 값 영역에 어떤 필드를 배치하느냐에 따라 전혀 다른 결과가 만들어집니다. 그래서 배치 기준을 함께 알려주면 AI가 임의로 필드를 판단해 엉뚱한 형태의 피벗 테이블을 만드는 일을 줄일 수 있습니다. 또한 날짜 데이터가 텍스트로 입력되어 있거나 금액에 문자와 숫자가 섞여 있으면 그룹화나 합계가 제대로 되지 않을 수 있으므로 피벗 테이블을 만들기 전 원본 데이터의 날짜와 숫자 형식이 올바른지도 함께 확인해달라고 요청하면 더 안전한 결과물을 받을 수 있습니다.
[나] :
선택한 범위의 데이터에서 아래 요구사항을 만족하는 피벗 테이블을 생성해줄 것. 다음 요구사항을 따라줘.
가로(행)은 날짜, 세로(열)은 지역을 배치할 것
날짜는 월을 기준으로 그룹화하고, 월별/지역별 주문금액을 합산한 데이터가 보이도록 생성
피벗 테이블은 새로운 시트를 생성하고, 생성된 시트의 A1 셀에 배치