Excel小窍门：高效提取表格数据

Excel小窍门：高效提取表格数据

引用

百度

等

15

来源

1.

https://baijiahao.baidu.com/s?id=1794683301811048494

2.

https://zhuanlan.zhihu.com/p/378353419

3.

https://www.ithome.com/0/760/140.htm

4.

https://baijiahao.baidu.com/s?id=1794118222230097881

5.

https://blog.csdn.net/2302_80061155/article/details/134701617

6.

https://blog.csdn.net/coffeetogether/article/details/121289545

7.

https://blog.csdn.net/weixin_33068055/article/details/113326868

8.

https://blog.csdn.net/BeWorkingMan/article/details/125724468

9.

https://g.pconline.com.cn/x/1804/18049807.html

10.

https://support.microsoft.com/zh-cn/office/%E6%B8%85%E7%90%86%E6%95%B0%E6%8D%AE%E7%9A%84%E5%8D%81%E5%A4%A7%E6%96%B9%E6%B3%95-2844b620-677c-47a7-ac3e-c2e157d1db19

11.

https://support.microsoft.com/zh-cn/office/%E6%96%87%E6%9C%AC%E5%87%BD%E6%95%B0-%E5%8F%82%E8%80%83-cccd86ad-547d-4ea9-a065-7bb697c2a56e

12.

https://ai.wps.cn/cms/qkgr4VHF.html

13.

http://forum.wps.cn/topic/26294

14.

https://www.cnblogs.com/powertoolsteam/p/17669615.html

15.

https://www.excelhome.net/4709.html

在日常工作中，Excel是我们处理数据的重要工具。掌握一些实用的Excel小窍门，可以让我们的工作效率大幅提升。本文将从数据提取、数据清洗、数据合并等多个维度，分享一些实用的Excel技巧。

快捷键操作

Excel提供了许多快捷键，可以帮助我们快速完成数据提取任务。其中最常用的是Ctrl+E智能填充。

假设我们需要从A列的文本中提取特定内容，可以先在B列输入一个示例，然后在下一个单元格中按下Ctrl+E，Excel会根据示例自动填充并提取相似的数据。

文本函数使用

Excel提供了丰富的文本函数，可以满足各种数据提取需求。

• LEFT函数：从字符串左侧提取指定长度的字符。例如，=LEFT(A3,4)将从A3单元格中提取前4个字符。

• RIGHT函数：从字符串右侧提取指定长度的字符。例如，=RIGHT(A3,4)将从A3单元格中提取最后4个字符。

• MID函数：从字符串中间提取指定位置和长度的字符。例如，=MID(A3,5,2)将从A3单元格的第5个字符开始提取2个字符。

对于不规则的文本内容，可以结合使用LEFT、RIGHT、MID与LEN、LENB函数。例如，=MID(A3,5,LEN(A3)-7)可以根据文本长度动态提取内容。

在处理数据时，我们经常会遇到一些常见的问题，如拼写错误、重复值、空格等。Excel提供了多种工具来帮助我们清理这些数据。

删除重复值

使用“删除重复项”功能可以快速清理重复数据。选择数据区域，然后在“数据”选项卡中点击“删除重复项”。

查找和替换

使用“查找和替换”对话框可以批量处理常见的文本问题，如删除前导字符串或后缀。快捷键是Ctrl+H。

更改文本大小写

有时我们需要统一文本的大小写格式。Excel提供了三个函数：

• LOWER：将所有字符转换为小写
• PROPER：将每个单词的首字母大写
• UPPER：将所有字符转换为大写

删除空格和非打印字符

使用TRIM函数可以删除文本中的多余空格。如果需要删除特定字符，可以使用SUBSTITUTE函数。

在实际工作中，我们经常需要将多个工作表或工作簿中的数据合并到一起。以下是几种常用的方法。

工作表合并

• 剪贴板法：适用于少量数据的合并。使用Ctrl+X剪切所有工作表的内容到剪贴板，然后在新工作表中全部粘贴。

• VBA法：适用于任意版本的Excel。通过编写简单的VBA代码可以快速完成合并。

Sub 合并当前工作簿下的所有工作表()
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set st = Worksheets.Add(before:=Sheets(1))
st.Name = "合并"
For Each shet In Sheets:
If shet.Name <> "合并" Then
i = st.Range("A" & Rows.Count).End(xlUp).Row + 1
shet.UsedRange.Copy
st.Cells(i, 1).PasteSpecial Paste:=xlPasteAll
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "已完成"
End Sub

• Power Query法：适用于Excel 2016及以上版本。通过“数据”选项卡中的“获取数据”功能，可以方便地合并多个工作表。

工作簿合并

工作簿合并通常需要借助VBA或批处理命令。以下是一个简单的VBA示例：

Sub xlsxtocsv()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
t = ActiveWorkbook.Name
mypath = ActiveWorkbook.Path & "\"
myfile = Dir(mypath & "*.xlsx")
Do Until Len(myfile) = 0
If myfile <> t Then
    Workbooks.Open Filename:=mypath & myfile
    ActiveWorkbook.SaveAs Filename:=mypath & Left(myfile, InStr(myfile, ".") - 1) & ".csv", FileFormat:=xlCSV
End If
If myfile <> t Then ActiveWorkbook.Close
myfile = Dir
Loop
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

在处理复杂数据时，我们可能需要从混合文本中提取数字。以下是一些实用的技巧。

提取左侧或右侧的数字

使用LEFT和RIGHT函数结合LEN和LENB函数可以轻松提取数字。

• 提取左侧数字：=LEFT(A2,2*LEN(A2)-LENB(A2))
• 提取右侧数字：=RIGHT(C2,2*LEN(C2)-LENB(C2))

提取中间的数字

使用MIDB函数结合SEARCHB函数可以提取中间的数字。

=MIDB(E2,SEARCHB("?",E2),2*LEN(E2)-LENB(E2))

提取混合文本中的数字

对于包含中文、字母、数字的混合文本，可以使用以下公式：

=SUM(MID(0&G2,LARGE(ISNUMBER(--MID(G2,ROW($1:$1024),1))*ROW($1:$1024),ROW($1:$308))+1,1)*10^ROW($1:$308)/10)

输入公式后需要使用Ctrl+Shift+Enter组合键。

通过以上这些技巧，我们可以更高效地处理Excel中的数据。无论是在日常工作中，还是在处理复杂的数据分析任务时，这些技巧都能帮助我们节省大量时间，提高工作效率。