“七待已久”： 一份详细的R语言批量操作Excel实例

 壹 办公自动化知多少

一说起办公自动化，大家立马想到的是python，毕竟到处都是python办公自动化的狂热营销。如果看公众号，没有在文内或文末看到相关的小广告，那就不晓得是不是你住在山上，没玩儿过微信了。

那么问题来了，办公自动化到底是啥呢？

小编我百度了一下，发现众说纷纭，没有统一的定义。于是，这几乎成了一个筐，只要相关的东西，啥都可以往里装。

按小编我的理解，能够和Office套件协作办公，拓展新任务场景，从而提高办公效率，统统都叫办公自动化。而且，办公自动化并不是非要和某一种特别的软件捆绑在一起的，时代新宠Python当然可以，其它符合图灵完备的计算机语言也完全可以，自然也包括 R 啦。

 贰 一个办公自动化任务

前段时间，小编我想统计一下私募基金这几年来在各家上市公司的持股情况。

怎么统计呢？我想了这么个计划。

先分别计算出私募持有的上市公司的数量，持有市值和占流通股的比例，再计算下一个会计年度中私募是否短期持有（低于4个季度），以及是否长期持有（等于4个季度）。

为此，我就吭哧吭哧地下载了2010-2019年的分季度持股Excel表格。

本想的是直接用 Excel 处理。但是，即使作为 Excel 小能手的小编，抓破了头也没想出咋用鼠标点点点{{{(>_<)}}}

特别地，我还要计算私募是否短期持有，或者是否长期持有，这简直要了小命儿了，在Excel中除了用VBA解决，根本没办法批量处理。

幸好小编我灵机一动，就想到了 R 语言。

 叁 R 语言办公自动化

我们从最开始的加载R语言包，一步一步地讲起。

# 预先设置
rm(list = ls())
Sys.setlocale(locale = "chinese")

第一行代码清除工作空间内的变量，排除运行前已有变量对后续代码运行的干扰。

第二行代码设置解码语言，保证代码内的中文在显示时不出现乱码，能够友好地显示。

# 加载包
library(readxl)
library(xlsx)
library(stringr)
library(tidyverse)

前两个包是用来读写Excel文件滴，对这两个包不清楚的小伙伴可以通过链接，查看如何在R中读写Excel表格。

"stringr"是用来处理字符串滴。

最后一个包，是我们的老朋友，大家都清楚，之前用过许多次了🤗

# 读入数据路径
list_file = grep('(.xlsx$)',list.files(),value = TRUE)
> list_file
 [1] "2010Q1.xlsx"   "2010Q2.xlsx"   "2010Q3.xlsx"   "2010Q4.xlsx"   "2011Q1.xlsx"  
 [7] "2011Q2.xlsx"   "2011Q3.xlsx"   "2011Q4.xlsx"   "2012Q1.xlsx"   "2012Q2.xlsx"   "2012Q3.xlsx"  
[13] "2012Q4.xlsx"   "2013Q1.xlsx"   "2013Q2.xlsx"   "2013Q3.xlsx"   "2013Q4.xlsx"   "2014Q1.xlsx"  
[19] "2014Q2.xlsx"   "2014Q3.xlsx"   "2014Q4.xlsx"   "2015Q1.xlsx"   "2015Q2.xlsx"   "2015Q3.xlsx"  
[25] "2015Q4.xlsx"   "2016Q1.xlsx"   "2016Q2.xlsx"   "2016Q3.xlsx"   "2016Q4.xlsx"   "2017Q1.xlsx"  
[31] "2017Q2.xlsx"   "2017Q3.xlsx"   "2017Q4.xlsx"   "2018Q1.xlsx"   "2018Q2.xlsx"   "2018Q3.xlsx"  
[37] "2018Q4.xlsx"   "2019Q1.xlsx"   "2019Q2.xlsx"   "2019Q3.xlsx"   "2019Q4.xlsx" 

小小一行代码，就能列出指定工作空间下所有".xlsx"文件，为后面循环迭代做准备。

list.files()列出了工作目录下的所有文件，而外层的grep()函数则筛选了".xlsx"文件。grep()函数的使用，涉及到了字符串的正则匹配，这是一个天坑，大家如果有需求，就在文末点赞，如果有50个，小编就专门写一篇文章来为大家解释哦。

# 读入数据的函数
read_pe <- function(path) {
  pe = readxl::read_xlsx(path)
  colnames(pe) = pe[1,]
  pe = pe[-1,]
  year = as.numeric(stringr::str_sub(path,1,4))
  Q = stringr::str_sub(path,6,6)
  pe[,dim(pe)[2]+1] = year
  colnames(pe)[dim(pe)[2]] = "year"
  pe[,dim(pe)[2]+1] = Q
  colnames(pe)[dim(pe)[2]] = "Q"
  return(pe)
}

这一步封装了一个用在迭代循环中读取Excel文件的函数，别看他小，功能可是杠杠滴，能够实现三个功能呢。

• 读取一个Excel文件，将第一行的内容（字符串格式）赋值到列名上，并删掉第一行。
• 读取文件名的前四个字符，转换成数字格式后，写到新创建的"year"这一列。- 读取文件名的第六个字符，转换成数字格式，写到新创建的"Q"这一列。

data = read_pe(list_file[1])
for(i in list_file[2:length(list_file)]){
  temp = read_pe(i)
  data = rbind(data,temp)
}

emmm 上面的代码大家看起来是不是怪怪的？本应该是一个循环，不断的append到列表最后面。我倒好，用的是按行合并，这里主要是为了大家理解起来方便，没有考虑计算机的办事儿效率。

于是，按下Run之后，就一直

......数据处理中......

经过了前面的合并处理和其它操作，我们的数据变成了下面这样：

> pe
# A tibble: 32,965 x 12
    year Q      代码 持仓数量 持仓市值 持仓占比         新证监会           同花顺     申万       short  long  占比
   <dbl> <chr> <dbl>    <dbl>    <dbl> <chr>            <chr>              <chr>      <chr>      <dbl> <dbl> <dbl>
 1  2010 1       153     57.1     486. 0.2472676342118~ 医药制造业         化学制剂   化学制剂     888   888 0.247
 2  2010 1       410    198.     2432. 0.3699072798611~ 通用设备制造业     机床工具   机床工具     888   888 0.370
 3  2010 1       419    354.     3339. 1.0106126502949~ 零售业             百货零售   多业态零售   888   888 1.01 
 4  2010 1       504    109.     1370. 0.3523328671016~ 科技推广和应用服~  生物制品Ⅲ  生物制品Ⅲ    888   888 0.352
 5  2010 1       511    220.     1176. 0.1907549840080~ 非金属矿物制品业   非金属新~  非金属新~    888   888 0.191
 6  2010 1       511    205.     1096. 0.1777519931694~ 非金属矿物制品业   非金属新~  非金属新~    888   888 0.178
 7  2010 1       533    407.     4353. 0.8511406554183~ 电气机械和器材制~  输变电设备 中压设备     888   888 0.851
 8  2010 1       533    523.     5601. 1.0950145244075~ 电气机械和器材制~  输变电设备 中压设备     888   888 1.10 
 9  2010 1       546    210.     3392. 1.4369323415382  生态保护和环境治~  水泥制造   水泥制造     888   888 1.44 
10  2010 1       546    114.     1843. 0.7808490112609~ 生态保护和环境治~  水泥制造   水泥制造     888   888 0.781
# ... with 32,955 more rows

先做一个汇总分析：

#汇总同一家企业，不同私募的持有情况
pe %<>% group_by(`代码`,year,Q) %>%
  summarise(clsl = sum(`持仓数量`),
            ccsz = sum(`持仓市值`),
            zb = sum(`占比`),
            short = sum(short),
            long = sum(long)
  )

按照上市公司代码、year和Q来做了一个汇总，其中管道操作符"%<>%"是有两个功效：

• 向左赋值；
• 向右作为函数的第一个参数。

#判断长期和短期的情况
for(i in unique(pe$`代码`)){
  for (j in unique(pe$year)) {
    
    tmp = pe[pe$`代码`== i & pe$year == j,]
    
      if(dim(tmp)[1] == 4){
        pe[pe$`代码`== i & pe$year == j,"long"] = 1
        pe[pe$`代码`== i & pe$year == j,"short"] = 0
      }else if(dim(tmp)[1] < 4){
        pe[pe$`代码`== i & pe$year == j,"long"] = 0
        pe[pe$`代码`== i & pe$year == j,"short"] = 1
      }else if(dim(tmp)[1] == 0){
        pe[pe$`代码`== i & pe$year == j,c("short","long")] = 0
      } 
  }
}

最后一部分功能代码的编写逻辑是：循环每家上市公司的每一年，如果四个季度都有数据，私募就在本年度长期持有该上市公司；如果只有部分季度的数据，私募在本年度短期持有该上市公司；否则私募没有持有该上市公司。

到了这儿，所有功能性处理，就都完成了，接下来就是丰收的时刻啦，把我们的结果写入文档。

xlsx::write.xlsx(pe,"lpe.xlsx")

通过xlsx包，把pe数据框写入到对应的文件内。

这里要提醒大家一句，一定要带上".xlsx"后缀噢，没有它，文档写入就会失败！😲

好了，大家猜猜看，这份代码，得运行多长时间？

没错，25分钟.......

够我好好煮一壶茶了，大家可以体验一把。🙄