Excel的VBA多个sheet数据拆分与批注提取及数据列去重复

谁践踏了优雅 2023-05-29 05:09 97阅读 0赞

## 1 Excel的所有文本内容进行合并去重 ##

#### 1.1未进行去重前效果 ####

![在这里插入图片描述][20200328124809261.png]

#### 1.2 VBA代码去重数据列 ####

Sub a()  
Dim d As Object  
Set d = CreateObject(“Scripting.Dictionary”)  
Dim rng As Range  
For Each rng In \[a1:ek35\] '数据源  
If rng <> “” Then d(rng.Value) = “”  
Next  
\[c1\].Resize(d.Count) = Application.Transpose(d.keys)  
Set d = Nothing  
End Sub

#### 1.3 VBA去重后效果 ####

![在这里插入图片描述][20200328124833715.png]  
——————————————————————————————————————————————————————

## 2 Excel的多个sheet拆分成单个sheet文件 ##

#### 2.1 未拆分前sheet ####

![在这里插入图片描述][20200328124944867.png]

#### 2.2 拆分sheet成独立文件的VBA ####

Sub sheet转独立文件()
    Dim sht As Worksheet
    Application.ScreenUpdating = False
    ipath = ThisWorkbook.Path & "\"
    For Each sht In Sheets
    sht.Copy
    ActiveWorkbook.SaveAs ipath & sht.Name
    ActiveWorkbook.Close
    Next
    Application.ScreenUpdating = True
    End Sub

#### 2.3 拆分后sheet的文件效果 ####

——————————————————————————————————————————————————

## 3 Excel批注拆分 ##

#### 批注示例如下： ####

![在这里插入图片描述][20200328123953644.png]

#### VBA程序代码构建Excel自定义函数 ####

Function cmt(X As Range)
    Application.Volatile True '自定义函数自动刷新
    cmt = X.Cells.Comment.Text '提取批注文本
    End Function

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RhbmRlbGlu_size_16_color_FFFFFF_t_70]

#### 使用VBA构建的自定义函数提取批注 ####

直接使用cmt进行判断，外加if判断ISBLANK(P15)空格情况  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RhbmRlbGlu_size_16_color_FFFFFF_t_70 1]

[20200328124809261.png]: https://img-blog.csdnimg.cn/20200328124809261.png
[20200328124833715.png]: https://img-blog.csdnimg.cn/20200328124833715.png
[20200328124944867.png]: https://img-blog.csdnimg.cn/20200328124944867.png
[20200328123953644.png]: https://img-blog.csdnimg.cn/20200328123953644.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RhbmRlbGlu_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200328124107888.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RhbmRlbGlu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RhbmRlbGlu_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200328124232427.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RhbmRlbGlu,size_16,color_FFFFFF,t_70