Excel Work Automation
Overview
As the saying goes, "half of company life is Excel," we automate the repetitive tasks of Excel, an essential tool for Korean office workers.
In connection with the xlsx skill, we generate practical Excel files that include formulas, formatting, and charts.
Note: When actually generating Excel files, use the xlsx skill together.
Supported Work Templates
| Category |
Template |
Key Features |
| Sales Management |
Sales Summary Table |
Daily/Monthly/Quarterly aggregation, comparison with previous period |
| Sales Management |
Sales Analysis Dashboard |
Charts, trend lines, KPI status |
| HR/Payroll |
Payroll Statement |
Base salary, allowances, deductions, net pay |
| HR/Payroll |
Annual Leave/Vacation Management |
Automatic calculation of accrual/usage/remaining |
| Clients |
Client Management Ledger |
Company information, transaction history |
| Clients |
Accounts Receivable Management |
Tracking of billing/payment/balance |
| Inventory |
Inventory Management Table |
Automatic calculation of inbound/outbound/inventory |
| Project |
Project Schedule Table |
Gantt chart, progress rate |
| Expenses |
Expense Expenditure Report |
Aggregation by item, comparison against budget |
| General |
Data Cleanup/Merging |
Duplicate removal, format unification |
Sales Summary Table Structure
Daily Sales Sheet
| Date |
Client |
Item |
Quantity |
Unit Price |
Supply Amount |
VAT |
Total |
Payment Method |
Remarks |
Monthly Summary Sheet
| 월 |
Sales Amount |
Change vs. Previous Month |
Change Rate |
Cumulative Sales |
Target Sales |
Achievement Rate |
Key Formula Patterns
매출합계: =SUMIFS(매출!F:F, 매출!A:A, ">="&DATE(연,월,1), 매출!A:A, "<"&DATE(연,월+1,1))
전월대비: =(당월-전월)/전월*100
달성률: =실적/목표*100
Payroll Statement Structure
Pay Statement
| Item |
Payment |
Item |
Deduction |
| Base Salary |
|
National Pension (4.75%) |
|
| Meal Allowance |
|
Health Insurance (3.595%) |
|
| Transportation Allowance |
|
Long-term Care (13.14% of health insurance) |
|
| Overtime Allowance |
|
Employment Insurance (0.9%) |
|
| Performance Bonus |
|
Income Tax |
|
|
|
Local Income Tax (10% of Income Tax) |
|
| Total Pay |
|
Total Deductions |
|
|
Net Pay |
|
|
Four Major Insurance Rates (2026 기준)
| Insurance |
Total Rate |
Employee Contribution |
Employer Contribution |
Remarks |
| National Pension |
9.5% |
4.75% |
4.75% |
Increased from 9% in 2025 to 9.5%, scheduled for phased increase to 13% by 2033 |
| Health Insurance |
7.19% |
3.595% |
3.595% |
Increased from 7.09% in 2025 to 7.19% |
| Long-term Care |
Health Insurance Premium × 13.14% |
50% |
50% |
Increased from 12.95% in 2025 to 13.14% |
| Employment Insurance |
1.8% |
0.9% |
0.9~1.65% |
Frozen |
Payroll Formulas (Applied for 2026)
국민연금 = 기본급 × 4.75% (상한: 637만원, 하한: 40만원)
건강보험 = 보수월액 × 3.595%
장기요양 = 건강보험료 × (0.9448 / 7.19) ≈ 건강보험료 × 13.14%
고용보험 = 보수월액 × 0.9%
Dashboard Design Principles
KPI Dashboard Composition (5–7 Key Metrics)
┌──────────────┬──────────────┬──────────────┬──────────────┐
│ 매출액 │ 영업이익 │ 달성률 │ 전월대비 │
│ ₩125,000천 │ ₩28,500천 │ 87.5% │ +12.3% │
└──────────────┴──────────────┴──────────────┴──────────────┘
┌────────────────────────────┬──────────────────────────────┐
│ 월별 매출 추이 (라인) │ 품목별 비중 (도넛/파이) │
│ │ │
└────────────────────────────┴──────────────────────────────┘
Dashboard Structure Rules
- Three-Area Separation: Data Sheet → Calculation Sheet → Dashboard Sheet
- KPI Cards: Place 5–7 key metrics at the top
- Chart Area: Place trend/comparison charts at the bottom
- Conditional Formatting: Below target (red), achieved (green), exceeded (blue)
Dynamic Array Functions (Microsoft 365 / Excel 2021+)
Essential New Functions for Work
| Function |
Purpose |
Replaces |
XLOOKUP |
Bidirectional Lookup |
Replaces VLOOKUP/HLOOKUP/INDEX+MATCH |
FILTER |
Conditional Filtering |
Replaces Advanced Filter, preserves source data |
SORT / SORTBY |
Dynamic Sorting |
Replaces the sort feature |
UNIQUE |
Remove Duplicates |
Replaces Remove Duplicates |
TAKE / DROP |
Extract Top/Bottom N |
TOP N analysis |
LET |
Define Variables |
Improves readability of complex formulas |
Dynamic Array Pipeline (Practical Example)
# 서울 지역 2026년 매출 TOP 20 (내림차순)
=TAKE(SORTBY(FILTER(A2:F10000, (YEAR(날짜)=2026)*(지역="서울")), 매출열, -1), 20)
# 거래처별 고유 목록 + 자동 정렬
=SORT(UNIQUE(FILTER(거래처!B:B, 거래처!B:B<>"")))
# 조건부 합계 (부서별, 월별)
=SUMIFS(매출!E:E, 매출!C:C, 부서명, MONTH(매출!A:A), 월)
Data Cleaning Guide
Commonly Used Cleaning Patterns
- Remove Duplicates: Aggregate identical clients/items
- Standardize Formats: Dates (YYYY-MM-DD), amounts (thousands comma), phone numbers
- Handle Blank Cells: Fill with 0 or "-"
- Split Text: Split city/county/district from an address
- XLOOKUP Matching: Automatic name matching based on a code table (superset of VLOOKUP)
Korea-Specific Functions
=TEXT(A1, "yyyy년 m월 d일") // 한국식 날짜
=TEXT(A1, "#,##0")&"원" // 금액 표기
=TEXT(A1, "#,##0")&"천원" // 천단위 표기
=MID(A1,1,3)&"-"&MID(A1,4,4)&"-"&MID(A1,8,4) // 전화번호 포맷
=LEFT(A1, FIND(" ", A1)-1) // 주소에서 시/도 추출
=TEXT(A1*1.1, "#,##0")&"원" // 부가세 포함 금액
Cheat Sheet of Commonly Used Work Formulas
# 조건부 집계
=SUMIFS(금액범위, 날짜범위, ">="&시작일, 날짜범위, "<="&종료일)
=COUNTIFS(상태범위, "완료", 담당자범위, "홍길동")
=AVERAGEIFS(점수범위, 부서범위, "영업팀")
# 날짜 함수
=EOMONTH(TODAY(), 0) // 이번 달 말일
=NETWORKDAYS(시작일, 종료일) // 영업일 수 계산
=DATEDIF(입사일, TODAY(), "Y")&"년" // 근속연수
# 텍스트 가공
=TEXTJOIN(", ", TRUE, 범위) // 텍스트 합치기
=TEXTBEFORE(A1, "@") // @ 앞 텍스트 추출
=TEXTAFTER(A1, "@") // @ 뒤 텍스트 추출
# 오류 처리
=IFERROR(XLOOKUP(검색값, 범위, 반환범위), "-")
=IFNA(VLOOKUP(A1, 범위, 2, 0), "미등록")
Conditional Formatting Guide
| Purpose |
Rule |
Format |
| Achievement rate 100% or higher |
=달성률>=100% |
Green background, bold |
| Achievement rate below 80% |
=달성률<80% |
Red text |
| Past due |
=마감일<TODAY() |
Red background |
| Display negative numbers |
Number format #,##0;[빨강]-#,##0 |
Automatic red |
| Data bars |
Select revenue range |
Gradient bar |
Output format
.xlsx: Excel file (including formulas/formatting, default).csv: CSV (for data exchange)
Workflow
사용자 요청 → 업무 유형 파악 → 데이터/요구사항 확인
→ xlsx 스킬로 엑셀 생성 → 수식/서식 적용 → 전달
