-
Notifications
You must be signed in to change notification settings - Fork 0
/
gg_20230426_excel.qmd
388 lines (252 loc) · 13 KB
/
gg_20230426_excel.qmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
---
title: "chatGPT"
subtitle: "직무교육 - 엑셀"
author:
- name: 이광춘
url: https://www.linkedin.com/in/kwangchunlee/
affiliation: 한국 R 사용자회
affiliation-url: https://github.com/bit2r
- name: 신종화
url: https://modernity.tistory.com/
affiliation: 한국 R 사용자회
affiliation-url: https://github.com/bit2r
date: "2023-04-07"
title-block-banner: true
#title-block-banner: "#562457"
format:
html:
css: css/quarto.css
theme: flatly
code-fold: true
code-overflow: wrap
toc: true
toc-depth: 3
toc-title: 목차
number-sections: true
highlight-style: github
self-contained: false
filters:
- lightbox
lightbox: auto
link-citations: true
knitr:
opts_chunk:
message: false
warning: false
collapse: true
comment: "#>"
R.options:
knitr.graphics.auto_pdf: true
editor_options:
chunk_output_type: console
editor:
markdown:
wrap: 72
---
:::{.callout-note collapse="false"}
[서울 디지털재단](https://sdf.seoul.kr/)에서 챗GPT 활용 사례집을 업무활용과
일상생활/창작활동/교육분야 로 나눠 두번에 걸쳐 보고서를 발간했다. [@sdf2023] [@sdf2023v2]
:::
# 작업흐름
[[데이터셋 검증](https://sharegpt.com/c/Sq21GJi)]{.aside}
기본적인 작업 흐름은 서울 디지털재단 PDF에 담긴 데이터를 복원하는 것으로
원본데이터를 화면 캡쳐하여 업스테이지에서 개발한 카카오톡 Askup OCR 기능과 챗GPT기능을 활용하여
`.csv` 파일형태를 갖춘다.
Askup에서 복사하여 메모장에 붙여넣고 이를 엑셀로 다시 붙여넣기하면 원하는 결과를 얻을 수 있다.
먼저 데이터 생성작업과 이를 활용하여 결과를 얻을 수 있는 챗GPT 프롬프트 작성과 엑셀에서 활용하는 단계로 나눠 작업을 수행한다.
:::{.panel-tabset}
## 데이터셋 생성 {.unnumbered}
## 엑셀 작업 {.unnumbered}
:::
::: {.callout-note collapse="true"}
### 엑셀 친화적인 `.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)를 삽입하여야 하고, 숫자는 '"숫자"' 형태가 되어야 합니다.
:::
[실습코드: [엑셀작업](https://sharegpt.com/c/v5eiTN5)
]{.aside}
# 초중고급 엑셀
# 초급
"조건을 걸어 숫자를 계산하는 함수"를 작성해보자.
## 조건 합
범주 칼럼(성별)를 기준으로 연속형 숫자칼럼 합계를 구한다.
:::{.callout-note collapse="false"}
## 프롬프트: 엑셀에서 C2:C21 값 중 '남'인 값을 찾아 대응하는 D2:D21의 숫자를 모두 더하는 함수를 생성해주세요.
Excel에서는 SUMIF 함수를 사용하여 조건에 해당하는 값들을 더할 수 있습니다.
함수를 입력하는 셀을 지정하고, 다음과 같은 수식을 입력하세요.
```
=SUMIF(C2:C21,"남자",D2:D21)
```
위 수식은 C2:C21 범위에서 값이 "남자"인 셀을 찾아 D2:D21 범위에서 해당하는 값을 더합니다. 결과는 해당하는 값들의 합계가 됩니다.
이 수식을 입력하면, 해당 셀에 값을 반환합니다.
:::
## 조건 평균
범주 칼럼(성별)를 기준으로 연속형 숫자칼럼 **평균**를 구한다.
:::{.callout-note collapse="false"}
## 프롬프트: 엑셀에서 C2:C21 값 중 '남'인 값을 찾아 대응하는 D2:D21의 숫자의 평균을 구하는 함수를 생성해주세요.
Excel에서는 AVERAGEIF 함수를 사용하여 조건에 해당하는 값들의 평균을 구할 수 있습니다.
함수를 입력하는 셀을 지정하고, 다음과 같은 수식을 입력하세요.
```
=AVERAGEIF(C2:C21,"남자",D2:D21)
```
위 수식은 C2:C21 범위에서 값이 "남자"인 셀을 찾아 D2:D21 범위에서 해당하는 값들의 평균을 계산합니다. 결과는 해당하는 값들의 평균이 됩니다.
이 수식을 입력하면, 해당 셀에 값을 반환합니다.
:::
### R 코드
문서에 적용될 숫자를 계산하여 반영하는 작업이라 엑셀 대신 R 혹은 파이썬 프로그래밍 코드를 사용해서 작업해도 동일한 결과를 간략히 얻을 수 있다.
```{r}
library(tidyverse)
library(readxl)
salary_tbl <- read_excel("data/excel_data.xlsx", sheet = "초급", range = "A1:F21")
salary_tbl %>%
group_by(성별) %>%
summarise(합계 = sum(기본급),
평균 = mean(기본급))
```
:::{.callout-note collapse="false"}
## 프롬프트: 상기 엑셀 코드를 dplyr R 코드로 작성해주세요
다음은 dplyr 패키지를 사용하여 R 코드로 작성한 예시입니다.
```{r}
#| eval: false
library(dplyr)
result <- data %>%
filter(C == "남자") %>%
summarize(avg = mean(D))
```
위 코드에서 `data`는 분석할 데이터프레임을 나타냅니다. `filter` 함수는 조건에 해당하는 행만 추출하는 역할을 하며, `summarize` 함수는 통계량을 계산하는 역할을 합니다. 결과값은 `result`에 저장됩니다.
:::
# 중급
"셀 값에서 조건을 찾아 원하는 값을 도출" 하는 사례를 살펴보자.
## 남여식별
"C열의 주민등록번호에서 가운데 숫자가 1일 경우 ‘남자’, 2일 경우 ‘여자’를 출력" 하는 엑셀코드를 작성해보자.
:::{.callout-note collapse="false"}
## 프롬프트: 엑셀에서 C2값의 형식이 'XXXXXX-1XXXXXX' 또는 'XXXXXX-2XXXXXX'이다. 'XXXXXX-1XXXXXX'에서 가운데 - 다음 숫자가 1일 경우 '남자', 2일 경우 '여자'를 출력하는 함수를 만들어 주세요.
다음과 같은 엑셀 함수를 사용하여 구현할 수 있습니다.
```
=IF(MID(C2,8,1)="1","남자",IF(MID(C2,8,1)="2","여자",""))
```
위 함수를 사용하면 C2 셀의 값을 분석하여 성별을 출력할 수 있습니다. 먼저 MID 함수를 사용하여 C2 셀의 8번째 문자를 추출한 후, 이 문자가 1인지 2인지를 비교합니다. 만약 1이면 "남자"를, 2이면 "여자"를 출력하고, 그 외의 경우는 빈 문자열을 출력합니다. 이를 다른 셀에 복사하여 여러 행에 대해서도 동일하게 적용할 수 있습니다.
:::
# 고급
"데이터 참조 값 활용, 데이터 입력시 같은 행 값을 찾아주는 함수"를 작성해보자.
## 입사일 검색
`I3`열에 사번을 입력(A열의 사번을 참조), 입사일을 바로 출력하는 함수를 작성해보자.
:::{.callout-note collapse="false"}
## 프롬프트: 엑셀에서 $A$2:$G$11의 전체 값 중, A열 값이 2인 값을 찾고, G열 값을 출력하는 함수를 만들어주세요.
아래와 같이 엑셀 함수를 작성하시면 됩니다.
```
=VLOOKUP(2,$A$2:$G$11,7,FALSE)
```
설명:
- `VLOOKUP`: 수직 조회 함수로, 첫 번째 인자로는 찾고자 하는 값(여기서는 2)을, 두 번째 인자로는 전체 범위($A$2:$G$11)를, 세 번째 인자로는 결과 값을 반환할 열의 인덱스(여기서는 G열, 즉 7)을, 네 번째 인자로는 완전 일치하는 값을 찾을 것인지 부분 일치하는 값을 찾을 것인지 여부를 지정합니다(FALSE는 완전 일치를 의미합니다).
:::
### R 코드작성
상기 엑셀 코드를 다음과 같이 R 코드로 작성할 수 있다.
```{r}
join_tbl <- read_excel("data/excel_data.xlsx", sheet = "고급", range = "A1:G11")
join_tbl %>%
filter(사번 == 2) %>%
select(입사일)
```
함수로 사번을 넣게 되면 입사일을 반환하도록 일반화시킬 수도 있다.
```{r}
get_join_date <- function(sabun = 2) {
join_date <- join_tbl %>%
filter(사번 == sabun) %>%
pull(입사일)
return(join_date)
}
get_join_date(5)
```
## 할인율 적용
"A1~E1 행의 할인율 기준에 따라서 결제금액에 따라 할인율이 다르게 적용될 때, 값의 범위에 따라서 할인율을 자동으로 넣는 함수" 함수를 작성해보자.
:::{.callout-note collapse="false"}
## 프롬프트: C5의 값이 $B$1:$E$1의 값과 비교하여 1) C5의 값이 $B$1:$E$ 보다 이상이고, $B$1:$E$값과 C5의 값이 근사한 값을 찾는다 2) 위에서 찾은 값의 2행인 $B$2:$E$2 값을 출력하는 함수를 만들어줘
다음과 같이 수행할 수 있습니다.
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 범위 값을 출력합니다.
:::
### R 코드
구매금액별로 할인율을 달리 적용하여 최종 결재금액을 계산한다.
```{r}
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()
```
# 전문가
"엑셀 내부 프로그래밍 VBA코드 활용"하는 경우 전문가라고 한다.
먼저 엑셀 상단 메뉴에서 `파일 → 옵션 → 리본 사용자 지정 → 개발도구`를 클릭하여 활성화시키면된다.
그리고 챗GPT에서 작성된 VBA 코드는 해당 엑셀쉬트(Sheet)를 활성화시킨 후에 코드를 붙여넣고 상단 실행버튼을 클릭하면 된다.
:::{#fig-vba layout-ncol=2}
엑셀 내부 VBA 프로그래밍 언어 실행
:::
VBA는 Visual Basic for Applications의 약자로 Excel, Word, PowerPoint와 같은 Microsoft Office 응용 프로그램에 통합된 프로그래밍 언어입니다. VBA를 사용하면 이러한 애플리케이션 내에서 작업을 자동화하고 사용자 지정 함수를 만들 수 있다. VBA 코드는 애플리케이션 내에서 작성 및 편집할 수 있으므로 다양한 사용자 지정 및 자동화 가능성을 제공한다.
:::{.callout-note collapse="false"}
## 프롬프트: 엑셀 A:G 행이 빈 칸일 경우 그 다음 텍스트가 나올 때까지 각각 행을 삭제하는 VBA코드를 생성해주세요.
아래와 같은 VBA 코드를 사용하면 A:G 행이 빈 칸인 경우에는 그 다음에 있는 텍스트가 나올 때까지 해당 행을 삭제할 수 있습니다.
```vba
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열까지를 순회하면서, 해당 행이 모든 열에서 빈 칸인 경우에는 해당 행을 삭제합니다. 이 때, 마지막 행부터 역순으로 삭제하기 때문에 삭제 후 다음 행으로 이동할 필요가 없습니다.
:::
# 실습 엑셀파일
상기 실습 내용이 담긴 엑셀 파일은 아래 다운로드 받을 수 있다.
```{r}
#| echo: false
xfun::embed_files(c('data/excel_data.xlsx'),
text='다운로드')
```
# 프로그래밍
## 파이썬 : 계산기
- [계산기 프로그램](https://r2bit.com/chatGPT/calculator.html)
## 삼성전자 주가예측
- [삼성전자 주가예측](https://r2bit.com/chatGPT/samsung.html)