#导入需要使用的模块
import pandas as pd
#导入Excel数据
data = pd.read_excel(r"C:\Users\QDM\Desktop\chaifen\打折.xlsx")

#创建一个用于存储多个sheet数据的空集
data_excel = []

#创建一个用于存储需要导出sheet名称的空集
sheetname = []

#创建循环，分别按指定的拆分字段取出数据与sheet名称
#根据部门字段进行分组
for x in data.groupby("部门"): 
    #将拆分的sheet数据存储到data_excle里面    
    data_excel.append(x[1])
#将拆分的sheet名称存储到sheetname里面
    sheetname.append(x[0])
#循环创建多个路径，路径中加入变量工作表名称，并导出Excel数据
for i in range(len(sheetname)): 
    data_excel[i].to_excel(r"C:\Users\QDM\Desktop\拆分\" + str(sheetname[i]) + ".xlsx",index = False)

import pandas as pd
filepath = r"C:\Users\QDM\Desktop\待拆分.xlsx"
df = pd.read_excel(filepath)

df

class_list = list(df["省份"].unique())
class_list

for i in class_list:
    df_cut = df[df["省份"]==i]
#     输出到jupyter 中
#     df_cut.to_excel(r".\%s.xlsx"%(i),encoding = "utf-8")
# 输出到桌面
    df_cut.to_excel(r"C:\Users\QDM\Desktop\%s.xlsx"%(i),encoding = "utf-8", index = False)

Sub split1()
Dim sw As Worksheet, answer, arr, t1%, t2$, t3$, rng As Range, arr1, dic As Object, i%, arr2, i1%, h1%, h2%
Application.DisplayAlerts = False
For Each sw In Worksheets
If sw.Name <> "总表" Then sw.Delete
Next
answer = InputBox("请输入标题所占行数，拆分列列标，数据区域最后一列的列标，中间用英文逗号隔开，如：2,a,e")
arr = VBA.split(answer, ",")
t1 = arr(0) * 1
t2 = arr(1)
t3 = arr(2)
Set rng = Range("a1", t3 & t1)
arr1 = Range(t2 & (t1 + 1), Cells(Rows.Count, t2).End(3))
Set dic = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr1)
    dic(arr1(i, 1)) = ""
Next
arr2 = dic.keys
For i1 = 0 To dic.Count - 1
    h1 = Range(t2 & ":" & t2).Find(arr2(i1)).Row
    h2 = h1 + Application.CountIf(Range(t2 & ":" & t2), arr2(i1)) - 1
    Set rng1 = Range("a" & h1, t3 & h2)
    Worksheets.Add.Name = arr2(i1)
    rng.Copy ActiveSheet.[a1]
    rng1.Copy ActiveSheet.Range("a" & (t1 + 1))
    Sheet1.Activate
Next
MsgBox "已经拆分完毕"
Application.DisplayAlerts = True
End Sub

Sub split()

'根据需要，声明变量
Dim sw As Worksheet, answer, arr, t1%, t2$, t3$, rng As Range, arr1, dic As Object, i%, arr2, i1%, h1%, h2%

'拆分之前先把工作簿中除了总表以外的表全部删除
Application.DisplayAlerts = False
For Each sw In Worksheets
If sw.Name <> "总表" Then sw.Delete
Next

'1、确定标题区域和拆分列的列标
answer = InputBox("请输入标题所占行数，拆分列列标，数据区域最后一列的列标，中间用英文逗号隔开，如：2,a,e")
arr = VBA.split(answer, ",")
t1 = arr(0) * 1
t2 = arr(1)
t3 = arr(2)

'第一步，确定标题区域，确定拆分标记所处的区域并把其装入数组arr中
Set rng = Range("a1", t3 & t1)
arr1 = Range(t2 & (t1 + 1), Cells(Rows.Count, t2).End(3))

'第二步，新建一个字典，把拆分标记写入字典，比如本例中为部门
Set dic = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr1)
    dic(arr1(i, 1)) = ""
Next

'第三步：拆分并生成新的sheet

'循环出拆分标记
arr2 = dic.keys
For i1 = 0 To dic.Count - 1

    '在总表拆分列查找第一个拆分标记所在的首行和尾行行号
    h1 = Range(t2 & ":" & t2).Find(arr2(i1)).Row
    h2 = h1 + Application.CountIf(Range(t2 & ":" & t2), arr2(i1)) - 1
    
    '确定待复制区域，e列为最后一列，可根据实际情况修改
    Set rng1 = Range("a" & h1, t3 & h2)
    '以arr2(i1)为名新建sheet
    Worksheets.Add.Name = arr2(i1)
    '把标题和要复制的内容复制到新建的表里
    rng.Copy ActiveSheet.[a1]
    rng1.Copy ActiveSheet.Range("a" & (t1 + 1))
    '返回总表
    Sheet1.Activate
Next

'提示拆分完成
MsgBox "已经拆分完毕"
Application.DisplayAlerts = True

End Sub