[ChatGPT 활용] ChatGPT를 활용한 Excel 함수 사용법

1. Excel 함수

데이터를 계산, 분석 또는 조작하기 위해 미리 정의된 수식입니다.

함수는 특정한 값을 입력받아 결과를 반환하며, SUM, AVERAGE, VLOOKUP 같은 다양한 유형이 있습니다.

 

이를 통해 복잡한 계산을 자동화하고 효율성을 높일 수 있습니다.

 

1-1 Excel 함수를 통해 차트 만들기 

• 데이터 준비: 먼저, 함수를 사용하여 필요한 데이터를 계산합니다.
  예를 들어, SUM, AVERAGE, 또는 VLOOKUP 함수를 이용해 합계, 평균, 특정 값을 계산한 후 결과를 표로 정리합니다.
• 차트 범위 설정: 함수로 계산된 데이터를 포함하는 셀 범위를 선택합니다. 이 데이터는 차트에 표시될 값이 됩니다.
  
  
• 차트 생성: 엑셀의 차트 도구를 사용해 선택한 데이터를 바탕으로 적합한 차트 유형(예: 막대형, 선형, 원형 등)을 생성합니다.
  이때 함수로 계산된 데이터는 자동으로 차트에 반영되어 시각적으로 표현됩니다.

 

 

2. ChatGPT를 활용한 Excel 함수 사용법

2-1 성적처리 

• 업무 설명 

[score.xlsx 파일]을 사용하여 학생들의 국어, 영어, 수학 점수 데이터를 가지고

개인별총점,과목별총점, 석차를 구하고 바차트로 표시해보자 

score.xlsx

0.01MB

 

• 프롬프트 

1. 학생들의 개인별총점을 구할 수 있는 엑셀함수를 알려줘

2. 학생들의 과목별총점을 구할 수 있는 엑셀함수를 알려줘

3. 개인총점에 따른 석차를 구할 수 있는 엑셀함수를 알려줘

4. X축을 학생명으로 하고 Y축을 국어,영어,수학점수로 하는 바차트를 표시해 줘.

단, 8행은 제외해 주고 학생명과 과목명은 영문으로 번역해줘

 

 

• 결과 

# Update the dataframe to use English for student names and subject names
df_english = df.copy()
df_english.columns = ['Name', 'Korean', 'English', 'Math', 'Total Score', 'Rank']

# Manually translating names (assuming these are common Korean names)
name_translation = {
    '홍길동': 'Hong Gil-dong',
    '강감찬': 'Gang Gam-chan',
    '이순신': 'Yi Sun-sin',
    '김길동': 'Kim Gil-dong',
    '최감찬': 'Choi Gam-chan'
}

df_english['Name'] = df_english['Name'].map(name_translation)

# Plot the updated chart with English labels
students_english = df_english['Name'].iloc[:6]

plt.figure(figsize=(10, 6))

bar_width = 0.25
index = range(len(students_english))

plt.bar(index, df_english['Korean'].iloc[:6], bar_width, label='Korean')
plt.bar([i + bar_width for i in index], df_english['English'].iloc[:6], bar_width, label='English')
plt.bar([i + 2 * bar_width for i in index], df_english['Math'].iloc[:6], bar_width, label='Math')

plt.xlabel('Student')
plt.ylabel('Score')
plt.title('Korean, English, Math Scores by Student')
plt.xticks([i + bar_width for i in index], students_english)

plt.legend()
plt.tight_layout()
plt.show()

 

 

• 생성된 Bar차트 

 

 

 

2-2 개별 정산액 계산 

• 업무 설명 

[sample.xlsx 파일]을 사용하여  개별 정산액을 계산하여 I4~I13에 표시해보자 

sample.xlsx

0.01MB

 

• 프롬프트 

엑셀에서 G열에서 H열의 값을 빼는 수식을 작성해줘.

G열은 G2부터 G11까지 있고, H열은 G2부터 H11까지 있어.

그리고 이걸 I2열부터 I11열에 입력하고 싶어.

입력해서 sample_1.xlsx파일로 다운로드 받을 수 있도록 해줘

 

• 결과 

# Perform the subtraction between '결제액' (G) and 'PG수수료' (H), storing the result in a new column 'I'
df['차액'] = df['결제액'] - df['PG수수료']

# Save the updated dataframe to a new Excel file
output_path = '/mnt/data/sample_1.xlsx'
df.to_excel(output_path, index=False)

output_path

 

 

• 생성된 Excel 파일

GPT를 통해 완성된 Excel 파일 결과

2-3 총 정산액 계산

• 업무 설명

[sample_1.xlsx 파일]을 사용하여 총 정산액의 합계를 I13에 표시할 수 있는 함수를 ChatGPT에 요청해보자 

 

sample_1.xlsx

0.01MB

 

• 프롬프트 

I2에서 I11의 값을 합계하여 I13에 표시하려고 해. 사용할 함수를 알려줘

 

• 결과 

=SUM(I2:I11)

 

이 공식을 I13 셀에 입력하면 I2에서 I11까지의 값이 모두 합산되어 표시됩니다.

 

2-4 결제수단에 따른 정산액 계산

• 업무 설명

[sample_1.xlsx 파일]을 사용하여  결제 수단에 따른 정산액의 합계를 구하는 함수를

ChatGPT에게 요청해서 결제 수단에 따른 정산액 계산

sample_1.xlsx

0.01MB

 

• 프롬프트 

C열의 신용카드와 가상계좌에 대해 각각'결과'열의 합계를 계산해서

신용카드합계와 가상계좌합계를 표시하고 sample_2.xlsx로 다운로드할 수 있게 해줘

 

• 결과 

# Load the updated Excel file
file_path_updated = '/mnt/data/sample_1.xlsx'
df_updated = pd.read_excel(file_path_updated)

# Calculate the sum for '신용카드' and '가상계좌' in the '결제수단' column and sum their corresponding '차액'
credit_card_sum = df_updated[df_updated['결제수단'] == '신용카드']['차액'].sum()
virtual_account_sum = df_updated[df_updated['결제수단'] == '가상계좌']['차액'].sum()

# Add these sums as new rows in the dataframe
df_updated.loc[len(df_updated.index)] = ['']*len(df_updated.columns) # Adding an empty row
df_updated.loc[len(df_updated.index)] = ['신용카드합계', '', '', '', '', '', '', '', credit_card_sum]
df_updated.loc[len(df_updated.index)] = ['가상계좌합계', '', '', '', '', '', '', '', virtual_account_sum]

# Save the updated dataframe to a new Excel file
output_path_2 = '/mnt/data/sample_2.xlsx'
df_updated.to_excel(output_path_2, index=False)

output_path_2

 

 

 

• 생성된 Excel 파일

sample_2.xlsx

 

2-5 신용카드 기관의 수 카운트

• 업무 설명

[sample_2.xlsx 파일]을 사용하여  신용카드 기관의 수를 카운트하는 함수를 ChatGPT에 요청

 

sample_2.xlsx

0.01MB

 

• 프롬프트

C열의 신용카드와 가상계좌에 대해 각각'결과'열의 합계를 계산해서

신용카드합계와 가상계좌합계를 표시하고 sample_2.xlsx로 다운로드할 수 있게 해줘

 

 

• 결과 

# Calculate the sum for '신용카드' and '가상계좌' in the '결제수단' column and sum their corresponding '차액'
credit_card_sum_final = df_new[df_new['결제수단'] == '신용카드']['차액'].sum()
virtual_account_sum_final = df_new[df_new['결제수단'] == '가상계좌']['차액'].sum()

# Add these sums as new rows in the dataframe
df_new.loc[len(df_new.index)] = ['']*len(df_new.columns) # Adding an empty row
df_new.loc[len(df_new.index)] = ['신용카드합계', '', '', '', '', '', '', '', credit_card_sum_final]
df_new.loc[len(df_new.index)] = ['가상계좌합계', '', '', '', '', '', '', '', virtual_account_sum_final]

# Save the updated dataframe to a new Excel file
output_path_final = '/mnt/data/sample_2_final.xlsx'
df_new.to_excel(output_path_final, index=False)

output_path_final

 

 

 

• 생성된 Excel 파일