엑셀 함수 몰라도 ChatGPT로 해결하기 - Filter, Index, Match

대부분의 사무직에서 엑셀은 필수 도구입니다. 특히 HRD 담당자의 경우, 업무 효율성을 높이는데 엑셀의 역할은 매우 중요합니다. vlookup, sumif 같은 함수나 피벗 테이블을 잘 활용하면 업무 생산성이 크게 향상될 수 있습니다. 하지만 일상적으로 엑셀 함수를 자주 사용하지 않는다면 시간이 지나며 그 사용법을 잊어버리기 쉽습니다. 놀라운 사실은 ChatGPT를 활용하면 복잡한 엑셀 함수를 일일이 배울 필요 없이 원하는 업무를 수행할 수 있게 된다는 점입니다. 오늘은 두 가지 실무예시로 ChatGPT를 활용하면 엑셀을 제대로 다루지 못해도 업무 생산성을 어떻게 높일 수 있는지 확인해 보려고 합니다.

실무예시1: 현재 진행 중인 교육의 수강생만 뽑기

HRD 업무에서는 다수 교육생 정보를 엑셀로 관리하는 경우가 많습니다. 교육을 운영하며 종종 현재 진행 중인 수업의 수강생에게만 문자 등의 알림을 보내야 할 때가 있습니다. 이를 위해 수많은 수강생 중 현재 수업을 듣는 수강생만 골라내야 하는데, 엑셀을 잘 모른다면 많은 시간을 써야만 가능합니다. 하지만 ChatGPT를 활용하면 엑셀을 잘 몰라도 엑셀로 빠르게 현재 진행 중인 수업의 수강생만을 뽑아낼 수 있습니다.

1. 먼저 다음과 같이 ChatGPT의 입력창에 입력합니다.

엑셀 함수를 만들어줘

1. DB라는 시트의 A3열부터 Q열까지를 대상으로 할거야
2. 아래 두 가지 조건을 만족할 때 값을 불러오는 함수를 만들어줘
1) 현재 시트의 A1셀을 기준으로 DB 시트의 C열이 현재 시트의 A1셀보다 작으면 불러와
2) 현재 시트의 A1셀을 기준으로 DB시트의 D열이 현재시트의 A1셀보다 크면 불러와

이때 중요한 점은, 이 업무를 엑셀은 잘 알지만 지금 작업해야 하는 엑셀 시트를 직접 만든 것이 아니어서 시트가 어떻게 구성되어 있는지 잘 모르는 다른 사람에게 부탁한다고 생각하고 최대한 상세하게 안내해야 한다는 것입니다.

2. ChatGPT가 알려주는 함수를 복사해서 엑셀에 붙여넣기

그러면 다음과 같이 엑셀에서 바로 활용할 수 있는 함수와 함께 주의사항까지 친절하게 안내해 줍니다.

=FILTER(DB!A3:Q100, (DB!C3:C100 < A1) * (DB!D3:D100 > A1))

주의할 점은, 이 함수가 배열을 반환하기 때문에, 이 함수를 입력할 때는 충분한 공간이 확보된 상태에서 배열 수식을 입력해야 합니다. Excel 365에서는 자동으로 필요한 셀에 결과가 확장되지만, 다른 버전의 Excel에서는 처음 함수를 입력하는 셀에서 충분한 범위를 선택하고 배열 수식으로 입력해야 할 수도 있습니다. 배열 수식을 입력할 때는 수식을 입력한 후 Ctrl + Shift + Enter를 함께 누릅니다.

데이터의 범위(A3:Q100)는 예시이므로, 실제 데이터의 크기에 맞게 조정해야 합니다. 만약 DB 시트의 데이터 범위가 동적으로 변경된다면, A3:Q100 대신 더 넓은 범위를 지정하거나, Excel의 TABLE 기능을 활용하여 동적 범위를 설정할 수 있습니다.

3. 오류가 나면 오류 내용을 ChatGPT에 붙여넣고 물어보기

물론 ChatGPT가 시키는 대로 해도 아래와 같이 오류가 나는 경우가 있습니다. 이 경우에는 오류 메세지를 그대로 복사해서 ChatGPT에 붙여넣고 어떻게 수정하는게 좋을지 질문합니다.

아래와 같은 오류 메세지가 나오는데, 어떻게 수정해야 할까?

수식이 해당 자체 셀을 직접 또는 간접적으로 참조하는 순환 참조가 하나 이상 있습니다. 이로 인해 순환 참조를 올바르게 계산하지 못할 수 있습니다.

그러면 ChatGPT는 다시 친절하게 해결 방법을 안내해 줍니다.

4. ChatGPT의 안내에 따라 엑셀 수정하기

ChatGPT가 설명하는 오류에 대한 원인을 확인해 보고 수정하면 아래와 같이 원하는 값만을 엑셀에서 추출할 수 있습니다.

만약 안내에 따라 수정을 했음에도 오류가 난다면 오류 상황과 메세지를 최대한 상세히 ChatGPT에 질문하는 작업을 반복해야 합니다.

실무예시2: 수강생의 이메일을 기준으로 고유번호를 찾기

교육 수강생들의 이메일을 기준으로 고유번호를 찾거나 고유번호를 기준으로 이메일을 찾는 일들이 종종 있습니다. 이 경우는 vlookup을 통해서 해결이 가능하지만 vlookup이 불가능하게 데이터가 구성된 경우가 있습니다. 엑셀에서 이메일을 기준으로 vlookup으로 고유번호를 구하려면 이메일의 오른쪽에 고유번호가 있어야 합니다. 하지만 고유번호가 이메일의 왼쪽에 있다면 vlookup을 사용할 수가 없습니다.

이 경우는 index와 match 함수를 조합해서 사용해야 하는데, 이는 자주 사용하는 함수도 아니며 어떻게 조합해야 하는지 바로 알기 어렵습니다. 하지만 ChatGPT를 활용하면 누구나 쉽게 해결할 수 있습니다.

1. 먼저 다음과 같이 ChatGPT의 입력창에 입력합니다.

1. 이메일을 기준으로 FL_NO를 찾고 싶어
2. 이메일은 H열에 있고, FL_NO는 C열에 있어
3. 엑셀에서 사용할 수 있는 함수를 작성해줘

2. ChatGPT가 알려주는 함수를 복사해서 엑셀에 붙여넣기

그러면 다음과 같이 엑셀에서 바로 활용할 수 있는 함수를 안내합니다. 아래 함수를 복사해서 엑셀에 붙여넣기 합니다.

=INDEX(C2:C100, MATCH(찾고자하는이메일, H2:H100, 0))

3. 오류가 나면 오류 내용을 ChatGPT에 붙여넣고 물어보기

함수를 그대로 붙여넣으니 역시나 오류가 납니다. 그러면 다시 오류의 내용을 아래와 같이 ChatGPT에 질문합니다.

NAME 이라는 오류가 나오는데, 무엇을 수정해야 할까?
알려준대로 아래와 같이 입력했어

=INDEX(C2:C100, MATCH(찾고자하는이메일, H2:H100, 0))

그러면 ChatGPT는 다시 친절하게 해결 방법을 안내해 줍니다.

4. ChatGPT의 안내에 따라 엑셀 수정하기

ChatGPT가 설명하는 오류에 대한 원인을 확인해 보고 수정하면 아래와 같이 원하는 값만을 엑셀에서 추출할 수 있습니다.

만약 안내에 따라 진행했음에도 계속해서 오류가 발생한다면, 오류의 내용을 다시 ChatGPT에 물어보는 작업을 원하는 결과가 나올 때까지 반복해야 합니다.

간단히 두 가지의 실무사례로 ChatGPT를 어떻게 활용하면 엑셀의 복잡한 함수를 몰라도 실무의 문제를 해결하고 생산성을 높일 수 있는지 알아보았습니다. 과거에 우리는 엑셀, SQL 등의 기술을 활용하여 업무 문제를 해결하는 방법을 배워야 했습니다. 하지만 ChatGPT가 실무에서 빠르게 활용되면서, 이러한 기술을 심도 있게 배우는 것의 필요성이 줄어들고 있는 것 같습니다. HRD 담당자로서는 조직의 생산성을 높이기 위해 구성원 각자가 ChatGPT를 통해 어떻게 자신의 생산성을 향상시킬 수 있을지 고민할 수 있는 인터벤션을 준비해야할 때가 아닌가 싶습니다. 마치 개인용 컴퓨터(PC)가 각 사원에게 보급됐을 때, PC를 활용해 업무 생산성을 어떻게 높일 수 있을지 상상하고 실행에 옮기는 시기와 유사한 고민을 해야 할 때가 아닌가 싶습니다.

*블로그의 내용을 영상으로도 확인해 보세요

생산성을 높이기 위한 생성형AI 교육이 고민이라면,

조직의 생산성을 높이기 위해 생성형 AI 교육을 고민하고 계시다면 어디서부터 무엇을 시작하면 좋을지 ​자세히 알아보세요.