서울 디지털재단에서 챗GPT 활용 사례집을 업무활용과 일상생활/창작활동/교육분야 로 나눠 두번에 걸쳐 보고서를 발간했다. ("ChatGPT활용연구TFT, 2023) (ChatGPT활용연구TFT, 2023)
기본적인 작업 흐름은 서울 디지털재단 PDF에 담긴 데이터를 복원하는 것으로 원본데이터를 화면 캡쳐하여 업스테이지에서 개발한 카카오톡 Askup OCR 기능과 챗GPT기능을 활용하여 .csv 파일형태를 갖춘다. Askup에서 복사하여 메모장에 붙여넣고 이를 엑셀로 다시 붙여넣기하면 원하는 결과를 얻을 수 있다.
먼저 데이터 생성작업과 이를 활용하여 결과를 얻을 수 있는 챗GPT 프롬프트 작성과 엑셀에서 활용하는 단계로 나눠 작업을 수행한다.
.csv 파일형태 제작 프롬프트
부서명 | 직위 | 성별 | 기본급 | 세금 | 지급액
--- | --- | --- | --- | --- | ---
인사팀 | 부장 | | 768,790 | 69,890 | 698,900
인사팀 | 과장 | | 891,400 | 81,036 | 810,364
총무팀 | 사원 | | 891,400 | 81,036 | 810,364
관리팀 | 대리 | | 953,400 | 86,763 | 866,637
기획팀 | 과장 | | 953,400 | 86,673 | 866,727행을 총20개만 더 만들어주세요. 이름이 없기 때문에 성별은 개인정보에 해당되지 않기 때문에 임의로 성별도 채워 주세요. 출력형식은 csv 파일형태이며, 각 행마다 개행문자(new line)를 삽입하여야 하고, 숫자는 ‘“숫자”’ 형태가 되어야 합니다.
“조건을 걸어 숫자를 계산하는 함수”를 작성해보자.
범주 칼럼(성별)를 기준으로 연속형 숫자칼럼 합계를 구한다.
Excel에서는 SUMIF 함수를 사용하여 조건에 해당하는 값들을 더할 수 있습니다.
함수를 입력하는 셀을 지정하고, 다음과 같은 수식을 입력하세요.
=SUMIF(C2:C21,"남자",D2:D21)위 수식은 C2:C21 범위에서 값이 “남자”인 셀을 찾아 D2:D21 범위에서 해당하는 값을 더합니다. 결과는 해당하는 값들의 합계가 됩니다.
이 수식을 입력하면, 해당 셀에 값을 반환합니다.
범주 칼럼(성별)를 기준으로 연속형 숫자칼럼 평균를 구한다.
Excel에서는 AVERAGEIF 함수를 사용하여 조건에 해당하는 값들의 평균을 구할 수 있습니다.
함수를 입력하는 셀을 지정하고, 다음과 같은 수식을 입력하세요.
=AVERAGEIF(C2:C21,"남자",D2:D21)위 수식은 C2:C21 범위에서 값이 “남자”인 셀을 찾아 D2:D21 범위에서 해당하는 값들의 평균을 계산합니다. 결과는 해당하는 값들의 평균이 됩니다.
이 수식을 입력하면, 해당 셀에 값을 반환합니다.
문서에 적용될 숫자를 계산하여 반영하는 작업이라 엑셀 대신 R 혹은 파이썬 프로그래밍 코드를 사용해서 작업해도 동일한 결과를 간략히 얻을 수 있다.
library(tidyverse)
library(readxl)
salary_tbl <- read_excel("data/excel_data.xlsx", sheet = "초급", range = "A1:F21")
salary_tbl %>%
group_by(성별) %>%
summarise(합계 = sum(기본급),
평균 = mean(기본급))
#> # A tibble: 2 × 3
#> 성별 합계 평균
#> <chr> <dbl> <dbl>
#> 1 남자 8794170 879417
#> 2 여자 8792780 879278다음은 dplyr 패키지를 사용하여 R 코드로 작성한 예시입니다.
library(dplyr)
result <- data %>%
filter(C == "남자") %>%
summarize(avg = mean(D))위 코드에서 data는 분석할 데이터프레임을 나타냅니다. filter 함수는 조건에 해당하는 행만 추출하는 역할을 하며, summarize 함수는 통계량을 계산하는 역할을 합니다. 결과값은 result에 저장됩니다.
“셀 값에서 조건을 찾아 원하는 값을 도출” 하는 사례를 살펴보자.
“C열의 주민등록번호에서 가운데 숫자가 1일 경우 ‘남자’, 2일 경우 ’여자’를 출력” 하는 엑셀코드를 작성해보자.
다음과 같은 엑셀 함수를 사용하여 구현할 수 있습니다.
=IF(MID(C2,8,1)="1","남자",IF(MID(C2,8,1)="2","여자",""))위 함수를 사용하면 C2 셀의 값을 분석하여 성별을 출력할 수 있습니다. 먼저 MID 함수를 사용하여 C2 셀의 8번째 문자를 추출한 후, 이 문자가 1인지 2인지를 비교합니다. 만약 1이면 “남자”를, 2이면 “여자”를 출력하고, 그 외의 경우는 빈 문자열을 출력합니다. 이를 다른 셀에 복사하여 여러 행에 대해서도 동일하게 적용할 수 있습니다.
“데이터 참조 값 활용, 데이터 입력시 같은 행 값을 찾아주는 함수”를 작성해보자.
I3열에 사번을 입력(A열의 사번을 참조), 입사일을 바로 출력하는 함수를 작성해보자.
아래와 같이 엑셀 함수를 작성하시면 됩니다.
=VLOOKUP(2,$A$2:$G$11,7,FALSE)설명:
VLOOKUP: 수직 조회 함수로, 첫 번째 인자로는 찾고자 하는 값(여기서는 2)을, 두 번째 인자로는 전체 범위($A\(2:\)G$11)를, 세 번째 인자로는 결과 값을 반환할 열의 인덱스(여기서는 G열, 즉 7)을, 네 번째 인자로는 완전 일치하는 값을 찾을 것인지 부분 일치하는 값을 찾을 것인지 여부를 지정합니다(FALSE는 완전 일치를 의미합니다).
상기 엑셀 코드를 다음과 같이 R 코드로 작성할 수 있다.
join_tbl <- read_excel("data/excel_data.xlsx", sheet = "고급", range = "A1:G11")
join_tbl %>%
filter(사번 == 2) %>%
select(입사일)
#> # A tibble: 1 × 1
#> 입사일
#> <dttm>
#> 1 2002-04-30 00:00:00함수로 사번을 넣게 되면 입사일을 반환하도록 일반화시킬 수도 있다.
get_join_date <- function(sabun = 2) {
join_date <- join_tbl %>%
filter(사번 == sabun) %>%
pull(입사일)
return(join_date)
}
get_join_date(5)
#> [1] "2005-05-17 UTC"“A1~E1 행의 할인율 기준에 따라서 결제금액에 따라 할인율이 다르게 적용될 때, 값의 범위에 따라서 할인율을 자동으로 넣는 함수” 함수를 작성해보자.
다음과 같이 수행할 수 있습니다.
C5의 값과 $B\(1:\)E$1의 값을 비교하여, $B\(1:\)E$1의 값 중 C5의 값보다 크거나 같은 최초의 값을 찾습니다. 이를 위해 MATCH 함수를 사용합니다.
MATCH 함수로 찾은 인덱스를 이용하여, $B\(2:\)E$2 범위에서 해당 인덱스 위치의 값을 가져옵니다. 이를 위해 INDEX 함수를 사용합니다.
따라서 다음과 같은 엑셀 함수 코드를 작성할 수 있습니다.
=INDEX($B$2:$E$2,MATCH(C5,$B$1:$E$1,1))이 함수를 D5 셀에 입력하면, C5의 값과 $B\(1:\)E$1의 값 중 C5의 값보다 크거나 같은 최초의 값을 찾은 후, 해당 인덱스 위치의 $B\(2:\)E$2 범위 값을 출력합니다.
구매금액별로 할인율을 달리 적용하여 최종 결재금액을 계산한다.
library(gtExtras)
# discount_raw <- read_excel("data/excel_data.xlsx", sheet = "고급2", range = "A1:E2")
#
# discount_tbl <- discount_raw %>%
# pivot_longer(-구매가격, names_to = "구매가", values_to = "할인율") %>%
# select(-구매가격) %>%
# rename(구매가격=구매가)
purchase_tbl <- read_excel("data/excel_data.xlsx", sheet = "고급2", range = "A4:C24")
purchase_tbl %>%
mutate(할인율 = case_when(between(구매금액, 1, 30000) ~ 0.02,
between(구매금액, 30001, 50000) ~ 0.05,
between(구매금액, 50001, 100000) ~ 0.1,
TRUE ~ 0.2)) %>%
mutate(결재금액 = 구매금액 * (1-할인율)) %>%
gt::gt() %>%
gtExtras::gt_theme_nytimes()| 날짜 | 고객명 | 구매금액 | 할인율 | 결재금액 |
|---|---|---|---|---|
| 1 | 김ㅇㅇ | 105400 | 0.20 | 84320 |
| 2 | 박ㅇㅇ | 87200 | 0.10 | 78480 |
| 3 | 이ㅇㅇ | 59500 | 0.10 | 53550 |
| 4 | 최ㅇㅇ | 124800 | 0.20 | 99840 |
| 5 | 장ㅇㅇ | 73500 | 0.10 | 66150 |
| 6 | 홍ㅇㅇ | 29000 | 0.02 | 28420 |
| 7 | 송ㅇㅇ | 43600 | 0.05 | 41420 |
| 8 | 정ㅇㅇ | 91300 | 0.10 | 82170 |
| 9 | 이ㅇㅇ | 12000 | 0.02 | 11760 |
| 10 | 김ㅇㅇ | 56000 | 0.10 | 50400 |
| 11 | 박ㅇㅇ | 112700 | 0.20 | 90160 |
| 12 | 이ㅇㅇ | 46800 | 0.05 | 44460 |
| 13 | 최ㅇㅇ | 76200 | 0.10 | 68580 |
| 14 | 장ㅇㅇ | 99800 | 0.10 | 89820 |
| 15 | 홍ㅇㅇ | 63000 | 0.10 | 56700 |
| 16 | 송ㅇㅇ | 118500 | 0.20 | 94800 |
| 17 | 정ㅇㅇ | 55700 | 0.10 | 50130 |
| 18 | 이ㅇㅇ | 97600 | 0.10 | 87840 |
| 19 | 김ㅇㅇ | 84300 | 0.10 | 75870 |
| 20 | 박ㅇㅇ | 51400 | 0.10 | 46260 |
“엑셀 내부 프로그래밍 VBA코드 활용”하는 경우 전문가라고 한다. 먼저 엑셀 상단 메뉴에서 파일 → 옵션 → 리본 사용자 지정 → 개발도구를 클릭하여 활성화시키면된다. 그리고 챗GPT에서 작성된 VBA 코드는 해당 엑셀쉬트(Sheet)를 활성화시킨 후에 코드를 붙여넣고 상단 실행버튼을 클릭하면 된다.
VBA는 Visual Basic for Applications의 약자로 Excel, Word, PowerPoint와 같은 Microsoft Office 응용 프로그램에 통합된 프로그래밍 언어입니다. VBA를 사용하면 이러한 애플리케이션 내에서 작업을 자동화하고 사용자 지정 함수를 만들 수 있다. VBA 코드는 애플리케이션 내에서 작성 및 편집할 수 있으므로 다양한 사용자 지정 및 자동화 가능성을 제공한다.
아래와 같은 VBA 코드를 사용하면 A:G 행이 빈 칸인 경우에는 그 다음에 있는 텍스트가 나올 때까지 해당 행을 삭제할 수 있습니다.
Sub DeleteBlankRows()
Dim LastRow As Long
Dim i As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = LastRow To 2 Step -1
If Range("A" & i).Value = "" And Range("B" & i).Value = "" And Range("C" & i).Value = "" And Range("D" & i).Value = "" And Range("E" & i).Value = "" And Range("F" & i).Value = "" And Range("G" & i).Value = "" Then
Range("A" & i & ":G" & i).Delete Shift:=xlUp
End If
Next i
End Sub이 코드는 엑셀 시트의 A열부터 G열까지를 순회하면서, 해당 행이 모든 열에서 빈 칸인 경우에는 해당 행을 삭제합니다. 이 때, 마지막 행부터 역순으로 삭제하기 때문에 삭제 후 다음 행으로 이동할 필요가 없습니다.
상기 실습 내용이 담긴 엑셀 파일은 아래 다운로드 받을 수 있다.