Excel Task Automation
Overview
As the saying goes, "half of office life is Excel," this automates the repetitive tasks in Excel, an essential tool for Korean office workers.
In conjunction with the xlsx skill, it generates practical Excel files containing formulas, formatting, and charts.
Note: When actually generating Excel files, the xlsx skill is used together.
Supported Task Templates
| Category |
Template |
Key Features |
| Sales Management |
Sales Summary Sheet |
Daily/Monthly/Quarterly aggregation, comparison with previous period |
| Sales Management |
Sales Analysis Dashboard |
Charts, trendlines, KPI status |
| HR/Payroll |
Payroll Statement |
Base salary, allowances, deductions, net pay |
| HR/Payroll |
Annual Leave/Vacation Management |
Automatic calculation of accrual/usage/balance |
| Clients |
Client Management Ledger |
Company information, transaction history |
| Clients |
Accounts Receivable Management |
Tracking of billing/payment/balance |
| Inventory |
Inventory Management Sheet |
Automatic calculation of inbound/outbound/inventory |
| Project |
Project Schedule |
Gantt chart, progress rate |
| Expenses |
Expense Report |
Aggregation by item, comparison with budget |
| General |
Data Cleaning/Merging |
Duplicate removal, format unification |
Sales Summary Sheet 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 Payment |
|
Total Deductions |
|
|
Net Pay |
|
|
Four Major Insurance Rates (as of 2026)
| Insurance |
Total Rate |
Employee Contribution |
Employer Contribution |
Remarks |
| National Pension |
9.5% |
4.75% |
4.75% |
Increased from 9% to 9.5% in 2025, scheduled for phased increase to 13% by 2033 |
| Health Insurance |
7.19% |
3.595% |
3.595% |
Increased from 7.09% to 7.19% in 2025 |
| Long-Term Care |
Health Insurance Premium × 13.14% |
50% |
50% |
Increased from 12.95% to 13.14% in 2025 |
| Employment Insurance |
1.8% |
0.9% |
0.9~1.65% |
Freeze |
Payroll Formulas (Applied 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 Practical Work
| Function |
Purpose |
Alternative Feature |
XLOOKUP |
Bidirectional Lookup |
Replaces VLOOKUP/HLOOKUP/INDEX+MATCH |
FILTER |
Conditional Filtering |
Replaces Advanced Filter, preserves source data |
SORT / SORTBY |
Dynamic Sorting |
Replaces sort feature |
UNIQUE |
Duplicate Removal |
Replaces removing duplicate items |
TAKE / DROP |
Extract Top/Bottom N |
TOP N analysis |
LET |
Variable Definition |
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
- Duplicate Removal: Consolidate identical clients/items
- Format Standardization: Date (YYYY-MM-DD), Amount (thousands separator), Phone Number
- Empty Cell Handling: Fill with 0 or "-"
- Text Splitting: Separate city/county/district from address
- XLOOKUP Matching: Automatic name matching based on code table (VLOOKUP backward compatible)
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")&"원" // 부가세 포함 금액
Practical Frequently Used Formula Cheat Sheet
# 조건부 집계
=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 |
| Overdue |
=마감일<TODAY() |
Red background |
| Negative number display |
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 스킬로 엑셀 생성 → 수식/서식 적용 → 전달
