用公式提取多个Excel文件的数据
 
待提取数据的Excel文件
新建公式，提取文件名
提取文件名到Excel中
用文件名和地址组成字符串
将引用的字符串以数字类型粘贴
用查找和替换功能在字符串前加“=”
用查找和替换功能更改单元地址的引用方式，提取其他数据
 

 
待提取数据的Excel文件
 
在文件夹 E:\temp\销售清单\ 中是不同公司的销售清单Excel文件。现在要将所有公司地址提取到一新的Excel中进行汇总。
 
 
 
新建公式，提取文件名
 
公式名称为：提取文件名
 引用位置为：=FILES(Sheet5!$A$1)： 
 
提取文件名到Excel中
 
用公式：=Index(提取文件名，row(1:1))将第一个文件名提取出来，再通过下拉单元格将所有文件名提取出来。
 
 
 
用文件名和地址组成字符串
 
用文件名和地址组成字符串，用于对文件的应用。
 ="‘E:\temp\销售清单\["&A2&"]Sheet1’!$A$2"
 
 
将引用的字符串以数字类型粘贴
 

 
 
用查找和替换功能在字符串前加“=”
 
在字符串前加“=”，将字符串变成公式。
 
 
 
用查找和替换功能更改单元地址的引用方式，提取其他数据
 

 
 
昨天一个前端的朋友找我帮忙用excel提取代码中的汉字（字符串），可算费了劲儿了，他要提取的内容均在单引号中，但问题是没有统一的规律，同一个单元格可能存在多个要提取的内容，而且汉字中间也夹杂其他字符。所以总结了一下提取汉字的几种情况。
 


 
一、用公式提取Excel单元格中的汉字
 
对于一个混杂各种字母、数字及其他字符和汉字的文本字符串，要提取其中的汉字，在Excel中通常可用下面的公式。
 
例如下图A列中的字符串，要在B列提取其中的汉字（或词语）。
 

 
如果汉字位于字符串的开头或结尾，用LEFT或RIGHT函数即可提取，例如上图中A2:A4区域中的字符串。在B2中输入下面的公式：
 
=LEFT(A2,LENB(A2)-LEN(A2))
 
就会返回字符串中开头的几个汉字。
 
说明：LENB函数和LEN函数都可用返回文本字符串中的字符数，不同的是，LENB函数会将每个汉字（双字节字符）的字符数按2计数，LEN函数则对所有的字符，无论是单字节还是双字节都按1计数，因而公式中的“LENB(A2)-LEN(A2)”返回文本字符串中的汉字个数。
 
同样对于A6:A8区域中的字符串，在B6中用RIGHT函数即可：
 
=RIGHT(A6,LENB(A6)-LEN(A6))
 
如果汉字位于字符串的中间，可使用下面的数组公式。例如图中A10:A12区域中的字符串，在B10中输入数组公式：
 
=MID(A10,MATCH(2,LENB(MID(A10,ROW(INDIRECT("1:"&LEN(A10))),1)),),LENB(A10)-LEN(A10))
 
公式输入完毕，按Ctrl+Shift+Enter结束。
 
说明：公式先用MID函数将字符串中的每个字符分解到到一个字符数组中，然后用LENB函数返回各字符的的字符数，对于汉字会返回“2”。用MATCH函数取得第一个“2”的位置，即第一个汉字的位置，最后再用MID函数提取汉字。
 
使用上述公式时要求字符串中的汉字是连续的，中间没有其它字符分隔。
 
如果字符串中的汉字之间有其它字符分隔，例如上图中的A14单元格，要提取其中的所有汉字，可用下面的自定义函数。方法是：
 
1. 按Alt+F11，打开VBA编辑器，单击菜单“插入→模块”，在代码窗口中输入粘贴下面的代码：
 

  
Function 提取汉字(sString As String) As String
  
Dim regEx As Object
  
Set regEx = CreateObject("VBScript.RegExp")
  
With regEx
  
'搜索整个字符串
  
.Global = True
  
'匹配非汉字
  
.Pattern = "[^\u4e00-\u9fa5]"
  
'将字符串中的非汉字替换为空
  
提取汉字 = .Replace(sString, "")
  
End With
  
Set regEx = Nothing
  
End Function
 
 
 
 
说明：上述代码定义了一个自定义函数“提取汉字”。代码利用RegExp对象使用正则表达式匹配模式，清除字符串中的所有非汉字，得到其中的汉字。
 
2. 返回Excel工作表界面，在B14单元格中输入公式：
 
=提取汉字(A14)
 
即可取得A14单元格字符串中的所有汉字。
 
 
 
二、用公式提取引号（某2个相同字符）之间的内容
 

 
公式：=MID(A2,FIND("'",A2)+1,FIND("^",SUBSTITUTE(A2,"'","^",2))-FIND("'",A2)-1)
 
 
 
如果要提取第一个字符和最后一个相同字符之间的内容，则修改公式为：
 
=MID(A2,FIND("'",A2)+1,FIND("^",SUBSTITUTE(A2,"'","^",LEN(A2)-LEN(SUBSTITUTE(A2,"'",""))))-FIND("'",A2)-1)
 
在excel中，如何查询字符串的第N次出现位置，或最后一次出现位置，使用公式：
 

  
最后一次出现位置
  
=FIND("这个不重复就行",SUBSTITUTE(A1,"-"," 这个不重复就行",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))
  
第N次出现位置
  
假如查第四个\的位置
  
=find("这个不重复就行",substitute(A1,"\","这个不重复就行",4))
 
 
 
 
三、用FIND函数查找字符串中的双引号
 
有这样一个字符串        AB"CDEFG        想查找到双引号的位置， 公式是这样的
 
=FIND("""",E11)
 
而不是    =FIND(""",E11)
 

 

大家好，又到了Python办公自动化系列。
 
 
 
今天我们来讲解一个比较简单的案例，使用openpyxl从Excel中提取指定的数据并生成新的文件，之后进一步批量自动化实现这个功能，通过本例可以学到的知识点:
 

 
 
openpyxl模块的运用
 
 
 
glob模块建立批处理
 
 
数据源：阿里云天池的电商婴儿数据（可自行搜索并下载，如果要完成进阶难度可直接将该数据Excel拷贝999次即可，当然这个拷贝可以交给代码来实现）
 
 
 
需求说明
 
 
 
初级难度：提取电商婴儿数据.xlsx中购买数buy_mount超过50的记录建立新的Excel表
 
 
最后形成如下的表格：
 

利用pandas可以轻松读取excel数据，那么如何提取某一列数据呢？
 
1.excel数据有列名（表头）
 
import pandas as pd

tr = pd.read_excel(file_path)['列名']
 
2. excel数据为无表头
 
import pandas as pd

tr = pd.read_excel(file_path, header=None, usecol=[2])  # 指定第三列数据
 
希望对小伙伴们有所帮助！