Excel自动化办公

本章概览

自动化是提升办公效率的关键。本章综合运用前面学到的知识,介绍如何构建完整的Excel自动化解决方案,涵盖数据采集、处理、分析到报表生成的全流程。

学习目标

• 掌握自动化工作流设计
• 学会数据采集和更新自动化
• 熟练应用报表自动生成
• 掌握定时任务和邮件发送
• 学会构建完整自动化系统

---

30.1 自动化工作流设计

30.1.1 需求分析

典型场景

1. 每日销售报表
   数据源 → 清洗 → 统计 → 生成报表 → 发送邮件

2. 月度财务汇总
   多个Excel → 合并 → 分析 → 生成图表 → 归档

3. 库存预警
   实时数据 → 对比阈值 → 预警通知 → 生成报告

4. 人力资源统计
   考勤数据 → 计算工时 → 计算工资 → 生成报表

流程拆解

1. 数据输入
   - 手动录入
   - 文件导入
   - 数据库查询
   - API获取

2. 数据处理
   - 数据清洗
   - 格式转换
   - 计算分析
   - 数据验证

3. 输出展示
   - 生成报表
   - 创建图表
   - 导出文件
   - 发送通知

30.1.2 工具选择

Excel原生

- 公式和函数
- 数据透视表
- Power Query
- Power Pivot
- VBA宏

适用: 中小规模,Excel内完成

Python集成

- pandas(数据处理)
- openpyxl(格式控制)
- xlwings(双向交互)
- schedule(定时任务)
- smtplib(邮件发送)

适用: 复杂逻辑,大数据量

云服务

- Power Automate
- Google Apps Script
- Zapier

适用: 多平台集成,无需编程

---

30.2 数据自动采集

30.2.1 从Web获取数据

Power Query Web连接

数据 → 获取数据 → 从Web
输入URL
选择表格
转换数据(清洗)
关闭并上载

刷新:
数据 → 全部刷新(自动获取最新数据)

VBA爬取网页

Sub GetWebData()
    Dim xhr As Object
    Set xhr = CreateObject("MSXML2.XMLHTTP")

    xhr.Open "GET", "https://example.com/api/data", False
    xhr.send

    If xhr.Status = 200 Then
        ' 处理返回的数据
        Range("A1").Value = xhr.responseText
    End If
End Sub

Python爬虫

import requests
import pandas as pd
from bs4 import BeautifulSoup

def scrape_data(url):
    response = requests.get(url)
    soup = BeautifulSoup(response.text, 'html.parser')

    # 提取表格
    table = soup.find('table')
    df = pd.read_html(str(table))[0]

    # 保存到Excel
    df.to_excel('web_data.xlsx', index=False)

scrape_data('https://example.com/data')

30.2.2 从数据库获取

Power Query连接SQL

数据 → 获取数据 → 从数据库 → 从SQL Server
服务器名: localhost
数据库名: SalesDB
SQL语句(可选):
  SELECT * FROM Orders
  WHERE OrderDate >= '2024-01-01'

优势: 数据刷新即可获取最新数据

Python查询数据库

import pandas as pd
import pyodbc

def get_sql_data():
    conn = pyodbc.connect(
        'DRIVER={SQL Server};'
        'SERVER=localhost;'
        'DATABASE=SalesDB;'
        'UID=username;'
        'PWD=password'
    )

    query = """
    SELECT ProductName, SUM(Amount) as TotalSales
    FROM Sales
    WHERE SaleDate >= '2024-01-01'
    GROUP BY ProductName
    """

    df = pd.read_sql(query, conn)
    df.to_excel('sales_report.xlsx', index=False)
    conn.close()

get_sql_data()

30.2.3 从API获取

示例: 获取汇率数据

import requests
import pandas as pd
from datetime import datetime

def get_exchange_rate():
    url = "https://api.exchangerate-api.com/v4/latest/USD"
    response = requests.get(url)
    data = response.json()

    # 转换为DataFrame
    df = pd.DataFrame(data['rates'].items(), columns=['货币', '汇率'])
    df['更新时间'] = datetime.now()

    # 保存到Excel
    df.to_excel('exchange_rates.xlsx', index=False)

get_exchange_rate()

---

30.3 数据自动处理

30.3.1 批量清洗

VBA批量处理

Sub BatchCleanData()
    Dim ws As Worksheet
    Dim lastRow As Long

    Application.ScreenUpdating = False

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "汇总" Then
            With ws
                ' 去除空格
                .Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart

                ' 统一日期格式
                lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
                .Range("A2:A" & lastRow).NumberFormat = "yyyy-mm-dd"

                ' 删除空行
                On Error Resume Next
                .Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
                On Error GoTo 0
            End With
        End If
    Next ws

    Application.ScreenUpdating = True
    MsgBox "清洗完成!"
End Sub

Python批量清洗

import pandas as pd
import glob

def batch_clean(folder_path):
    files = glob.glob(f"{folder_path}/*.xlsx")

    for file in files:
        df = pd.read_excel(file)

        # 去除空格
        df = df.applymap(lambda x: x.strip() if isinstance(x, str) else x)

        # 删除重复
        df.drop_duplicates(inplace=True)

        # 填充缺失值
        df.fillna(method='ffill', inplace=True)

        # 保存
        output_file = file.replace('.xlsx', '_cleaned.xlsx')
        df.to_excel(output_file, index=False)

        print(f"已处理: {file}")

batch_clean('C:/数据/')

30.3.2 自动计算和更新

动态公式(Excel)

# OFFSET动态引用
销售总额 = SUM(OFFSET(A1, 0, 0, COUNTA(A:A)-1, 1))

# INDIRECT动态引用
=SUM(INDIRECT("A1:A"&COUNTA(A:A)))

优势: 新增数据自动包含在计算中

VBA自动计算

Sub AutoCalculate()
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    ' 批量公式
    Range("D2:D" & lastRow).Formula = "=B2*C2"

    ' 计算汇总
    Range("D1").Value = "=SUM(D2:D" & lastRow & ")"
End Sub

---

30.4 报表自动生成

30.4.1 模板化报表

Excel模板

1. 创建报表模板(template.xlsx)
   - 设置好格式、图表、公式
   - 数据区域留空或用示例数据

2. VBA填充数据
Sub GenerateReport()
    Dim templatePath As String
    Dim outputPath As String

    templatePath = "C:\template.xlsx"
    outputPath = "C:\reports\report_" & Format(Date, "yyyymmdd") & ".xlsx"

    ' 复制模板
    FileCopy templatePath, outputPath

    ' 打开并填充数据
    Dim wb As Workbook
    Set wb = Workbooks.Open(outputPath)

    ' 填充数据
    wb.Worksheets("数据").Range("A2").CopyFromRecordset rs  ' 从数据库

    ' 刷新数据透视表和图表
    wb.RefreshAll

    wb.Save
    wb.Close
End Sub

Python模板

from openpyxl import load_workbook
import pandas as pd

def generate_report_from_template(template_path, data_df, output_path):
    # 加载模板
    wb = load_workbook(template_path)
    ws = wb['数据']

    # 写入数据
    for i, row in enumerate(data_df.itertuples(index=False), start=2):
        for j, value in enumerate(row, start=1):
            ws.cell(row=i, column=j, value=value)

    # 保存
    wb.save(output_path)
    print(f"报表生成: {output_path}")

# 使用
df = pd.read_sql(query, conn)
generate_report_from_template(
    'template.xlsx',
    df,
    f'report_{pd.Timestamp.now():%Y%m%d}.xlsx'
)

30.4.2 动态图表

VBA更新图表

Sub UpdateChart()
    Dim ws As Worksheet
    Dim cht As ChartObject
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("数据")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ' 更新图表数据源
    For Each cht In ws.ChartObjects
        cht.Chart.SetSourceData Source:=ws.Range("A1:D" & lastRow)
    Next cht
End Sub

Python创建图表

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

def create_chart_report(data_df, output_file):
    wb = Workbook()
    ws = wb.active

    # 写入数据
    for r in dataframe_to_rows(data_df, index=False, header=True):
        ws.append(r)

    # 创建图表
    chart = BarChart()
    data = Reference(ws, min_col=2, min_row=1, max_row=len(data_df)+1)
    cats = Reference(ws, min_col=1, min_row=2, max_row=len(data_df)+1)

    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    chart.title = "销售额统计"

    ws.add_chart(chart, "E5")
    wb.save(output_file)

---

30.5 定时任务

30.5.1 Windows任务计划程序

设置步骤

1. 打开"任务计划程序"
2. 创建基本任务
   - 名称: 每日销售报表
   - 触发器: 每天早上8点
   - 操作: 启动程序
   - 程序: python.exe
   - 参数: C:\scripts\daily_report.py

3. 测试运行

VBA + Windows计划

' 创建 run_macro.vbs
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open "C:\workbook.xlsm"
objExcel.Run "GenerateReport"
objExcel.ActiveWorkbook.Save
objExcel.Quit

' 任务计划程序调用 run_macro.vbs

30.5.2 Python定时任务

schedule库

import schedule
import time

def daily_report():
    print("生成每日报表...")
    # 报表生成代码
    generate_report()
    print("报表生成完成!")

# 定时任务
schedule.every().day.at("08:00").do(daily_report)
schedule.every().monday.at("09:00").do(weekly_report)
schedule.every(10).minutes.do(check_status)

# 持续运行
while True:
    schedule.run_pending()
    time.sleep(60)

APScheduler(推荐)

from apscheduler.schedulers.blocking import BlockingScheduler
from datetime import datetime

def daily_report():
    print(f"[{datetime.now()}] 生成报表")
    # 报表逻辑

scheduler = BlockingScheduler()

# 每天8点执行
scheduler.add_job(daily_report, 'cron', hour=8, minute=0)

# 每周一9点执行
scheduler.add_job(weekly_report, 'cron', day_of_week='mon', hour=9)

print("定时任务启动...")
scheduler.start()

---

30.6 邮件自动发送

30.6.1 Outlook邮件(VBA)

发送邮件

Sub SendEmail()
    Dim OutlookApp As Object
    Dim OutlookMail As Object

    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)

    With OutlookMail
        .To = "recipient@example.com"
        .CC = "cc@example.com"
        .Subject = "每日销售报表 - " & Format(Date, "yyyy-mm-dd")
        .Body = "附件为今日销售报表,请查收。"
        .Attachments.Add "C:\reports\daily_report.xlsx"
        .Send  ' 或 .Display 显示邮件
    End With

    Set OutlookMail = Nothing
    Set OutlookApp = Nothing

    MsgBox "邮件已发送!"
End Sub

HTML格式邮件

Sub SendHTMLEmail()
    Dim OutlookMail As Object
    Dim htmlBody As String

    Set OutlookMail = CreateObject("Outlook.Application").CreateItem(0)

    htmlBody = "<html><body>" & _
               "<h2>销售报表</h2>" & _
               "<p>总销售额: <strong>¥100,000</strong></p>" & _
               "<p>同比增长: <span style='color:green;'>+15%</span></p>" & _
               "</body></html>"

    With OutlookMail
        .To = "boss@company.com"
        .Subject = "销售报表"
        .HTMLBody = htmlBody
        .Attachments.Add "C:\report.xlsx"
        .Send
    End With
End Sub

30.6.2 Python发送邮件

SMTP发送

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication

def send_email(subject, body, attachment_path):
    sender = "your_email@gmail.com"
    receiver = "recipient@example.com"
    password = "your_password"

    # 创建邮件
    msg = MIMEMultipart()
    msg['From'] = sender
    msg['To'] = receiver
    msg['Subject'] = subject

    # 邮件正文
    msg.attach(MIMEText(body, 'plain', 'utf-8'))

    # 附件
    with open(attachment_path, 'rb') as f:
        part = MIMEApplication(f.read(), Name='report.xlsx')
        part['Content-Disposition'] = f'attachment; filename="report.xlsx"'
        msg.attach(part)

    # 发送
    server = smtplib.SMTP('smtp.gmail.com', 587)
    server.starttls()
    server.login(sender, password)
    server.send_message(msg)
    server.quit()

    print("邮件发送成功!")

# 使用
send_email(
    subject="每日销售报表",
    body="附件为今日报表,请查收。",
    attachment_path="daily_report.xlsx"
)

HTML邮件

from email.mime.text import MIMEText

html = """
<html>
  <body>
    <h2>销售报表</h2>
    <table border="1">
      <tr><th>产品</th><th>销售额</th></tr>
      <tr><td>产品A</td><td>¥10,000</td></tr>
      <tr><td>产品B</td><td>¥15,000</td></tr>
    </table>
  </body>
</html>
"""

msg.attach(MIMEText(html, 'html', 'utf-8'))

---

30.7 实战案例

案例1: 每日销售报表自动化

需求 每天早上8点自动生成前一天的销售报表并发送给相关人员

Python完整方案

import pandas as pd
import pyodbc
from datetime import datetime, timedelta
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication

def daily_sales_report():
    # 1. 数据库查询
    conn = pyodbc.connect('...')
    yesterday = (datetime.now() - timedelta(days=1)).strftime('%Y-%m-%d')

    query = f"""
    SELECT ProductName, SUM(Amount) as Sales, COUNT(*) as Orders
    FROM Sales
    WHERE SaleDate = '{yesterday}'
    GROUP BY ProductName
    ORDER BY Sales DESC
    """

    df = pd.read_sql(query, conn)
    conn.close()

    # 2. 生成报表
    template_path = 'templates/daily_report_template.xlsx'
    output_path = f'reports/daily_report_{yesterday}.xlsx'

    wb = load_workbook(template_path)
    ws = wb['数据']

    # 写入数据
    for i, row in enumerate(df.itertuples(index=False), start=2):
        for j, value in enumerate(row, start=1):
            ws.cell(row=i, column=j, value=value)

    # 更新图表
    chart = BarChart()
    data = Reference(ws, min_col=2, min_row=1, max_row=len(df)+1)
    cats = Reference(ws, min_col=1, min_row=2, max_row=len(df)+1)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    ws.add_chart(chart, "E5")

    wb.save(output_path)

    # 3. 发送邮件
    send_report_email(output_path, yesterday, df['Sales'].sum())

    print(f"[{datetime.now()}] 报表生成并发送完成")

def send_report_email(file_path, date, total_sales):
    sender = "system@company.com"
    receivers = ["sales@company.com", "manager@company.com"]

    msg = MIMEMultipart()
    msg['From'] = sender
    msg['To'] = ", ".join(receivers)
    msg['Subject'] = f"每日销售报表 - {date}"

    body = f"""
    各位领导,

    附件为{date}的销售报表。

    销售总额: ¥{total_sales:,.2f}

    请查收。

    ----
    自动发送 请勿回复
    """

    msg.attach(MIMEText(body, 'plain'))

    # 附件
    with open(file_path, 'rb') as f:
        part = MIMEApplication(f.read(), Name=f'daily_report_{date}.xlsx')
        part['Content-Disposition'] = f'attachment; filename="daily_report_{date}.xlsx"'
        msg.attach(part)

    # 发送
    server = smtplib.SMTP('smtp.company.com', 587)
    server.starttls()
    server.login(sender, 'password')
    server.send_message(msg)
    server.quit()

# 定时任务
from apscheduler.schedulers.blocking import BlockingScheduler

scheduler = BlockingScheduler()
scheduler.add_job(daily_sales_report, 'cron', hour=8, minute=0)
scheduler.start()

案例2: 多文件合并汇总

需求 每月底合并各分公司的Excel报表

Python方案

import pandas as pd
import glob
from datetime import datetime

def monthly_consolidation(folder_path):
    all_files = glob.glob(f"{folder_path}/*.xlsx")
    all_data = []

    for file in all_files:
        df = pd.read_excel(file)
        # 提取分公司名(从文件名)
        branch = file.split('_')[1].replace('.xlsx', '')
        df['分公司'] = branch
        all_data.append(df)

    # 合并
    consolidated = pd.concat(all_data, ignore_index=True)

    # 统计
    summary = consolidated.groupby('分公司').agg({
        '销售额': 'sum',
        '订单数': 'sum',
        '客户数': 'nunique'
    }).reset_index()

    # 输出
    with pd.ExcelWriter(f'consolidated_{datetime.now():%Y%m}.xlsx') as writer:
        consolidated.to_excel(writer, sheet_name='明细', index=False)
        summary.to_excel(writer, sheet_name='汇总', index=False)

    print("合并完成!")

# 每月最后一天执行
scheduler.add_job(
    lambda: monthly_consolidation('C:/monthly_reports/'),
    'cron',
    day='last'
)

案例3: 库存预警系统

需求 每小时检查库存,低于安全库存时发送预警

Python方案

import pandas as pd

def inventory_alert():
    # 查询当前库存
    df = pd.read_excel('inventory.xlsx')

    # 筛选低库存产品
    low_stock = df[df['当前库存'] < df['安全库存']]

    if not low_stock.empty:
        # 生成预警报告
        report_path = f'alerts/inventory_alert_{datetime.now():%Y%m%d_%H%M}.xlsx'
        low_stock.to_excel(report_path, index=False)

        # 发送预警邮件
        send_alert_email(report_path, low_stock)

        print(f"库存预警: {len(low_stock)}个产品低于安全库存")
    else:
        print("库存正常")

def send_alert_email(file_path, data):
    products = data['产品名称'].tolist()
    body = f"""
    紧急提醒:

    以下产品库存不足,请及时补货:
    {', '.join(products)}

    详情见附件。
    """

    # 发送邮件逻辑...

# 每小时检查
scheduler.add_job(inventory_alert, 'interval', hours=1)

---

30.8 最佳实践

30.8.1 代码规范

模块化

# config.py - 配置
DATABASE_CONFIG = {
    'server': 'localhost',
    'database': 'SalesDB',
    'username': 'user',
    'password': 'pass'
}

# database.py - 数据库操作
def get_sales_data(date):
    conn = connect_db(DATABASE_CONFIG)
    df = pd.read_sql(query, conn)
    conn.close()
    return df

# report.py - 报表生成
def generate_report(data):
    # 生成逻辑
    pass

# email_sender.py - 邮件发送
def send_email(subject, body, attachment):
    # 发送逻辑
    pass

# main.py - 主程序
if __name__ == '__main__':
    data = get_sales_data(yesterday)
    report_path = generate_report(data)
    send_email("报表", "请查收", report_path)

错误处理

import logging

logging.basicConfig(
    filename='automation.log',
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)

def daily_report():
    try:
        logging.info("开始生成报表")
        # 报表生成逻辑
        logging.info("报表生成成功")
    except Exception as e:
        logging.error(f"报表生成失败: {str(e)}")
        # 发送错误通知邮件
        send_error_notification(str(e))

30.8.2 监控和通知

状态监控

def health_check():
    """定期健康检查"""
    status = {
        '数据库连接': check_database(),
        '文件路径': check_paths(),
        '邮件服务': check_email_server()
    }

    if not all(status.values()):
        send_alert(f"系统异常: {status}")

# 每30分钟检查
scheduler.add_job(health_check, 'interval', minutes=30)

执行日志

def log_execution(func):
    """装饰器记录执行"""
    def wrapper(*args, **kwargs):
        start = datetime.now()
        logging.info(f"开始执行: {func.__name__}")

        try:
            result = func(*args, **kwargs)
            duration = (datetime.now() - start).total_seconds()
            logging.info(f"执行成功: {func.__name__}, 耗时{duration}秒")
            return result
        except Exception as e:
            logging.error(f"执行失败: {func.__name__}, 错误: {str(e)}")
            raise

    return wrapper

@log_execution
def daily_report():
    # 报表逻辑
    pass

---

本章小结

核心要点

1. 工作流设计: 需求分析 → 流程拆解 → 工具选择
2. 数据采集: Web/数据库/API自动获取
3. 数据处理: 批量清洗、计算、更新
4. 报表生成: 模板化、动态图表
5. 定时任务: Windows计划/Python调度器
6. 邮件发送: VBA Outlook/Python SMTP
7. 最佳实践: 模块化、错误处理、监控日志

自动化层次

Level 1: 公式和函数
Level 2: 数据透视表、Power Query
Level 3: VBA宏
Level 4: Python集成
Level 5: 完整自动化系统

学习路径

1. 掌握Excel基础功能
2. 学习Power Query/VBA
3. 学习Python数据处理
4. 设计自动化流程
5. 构建完整系统
6. 持续优化维护

注意事项

• 数据安全和备份
• 错误处理和日志
• 性能优化
• 用户友好的错误提示
• 文档和代码注释

下一步学习

• 数据库知识深化
• 云服务集成(Azure/AWS)
• Power Automate深入
• 机器学习预测模型

---

思考练习

1. 设计一个每日销售报表自动化流程
2. 实现多个Excel文件的自动合并和汇总
3. 创建库存监控和预警系统
4. 开发考勤统计和工资计算自动化
5. 构建包含数据采集、处理、报表、邮件的完整系统

项目建议

• 从小项目开始(单一功能)
• 逐步集成多个功能
• 注意错误处理
• 做好测试验证
• 编写使用文档

---

Excel篇完结

恭喜你完成Excel篇的学习!从基础操作到高级自动化,你已经掌握了Excel的核心技能。

回顾学习内容

• 16章: Excel界面与数据输入
• 17章: 公式与引用基础
• 18章: 数据分析工具
• 19章: 图表可视化
• 20章: 常用函数详解
• 21章: 数据清洗与整理
• 22章: 数据透视表精通
• 23章: 高级函数应用
• 24章: 商业图表制作
• 25章: 财务函数应用
• 26章: Power Query数据处理
• 27章: Power Pivot数据建模
• 28章: VBA编程基础
• 29章: Python集成
• 30章: 自动化办公

继续学习PPT篇、协作工具篇等内容,全面掌握办公软件技能!