如何用R生成类Excel透视表的统计输出(按学校统计2020年分月注册数)
R实现匹配Excel透视表效果的学校月度注册统计自动化
完全对齐你在Excel里的透视表操作逻辑,不需要重复手动拖拽字段,跑通代码后日常报告可以一键生成,具体实现流程如下:
核心逻辑对应
和你原Excel透视表的规则完全匹配:
- 行维度:固定为School字段
- 列维度:仅保留2020年的注册数据,按自然月聚合Date Registered字段
- 值维度:统计每个学校对应月份的注册学生数(对应你原表把Date Registered拖入值区域的计数逻辑)
依赖工具
都是R生态中日常写报告最常用的基础包,无额外复杂依赖:
- 数据读取:
readr - 数据清洗聚合:
dplyr - 日期格式处理:
lubridate - 透视表宽表转换:
tidyr
可直接复现的完整代码
# 加载所需包 library(readr) library(dplyr) library(lubridate) library(tidyr) # 读入原始数据集,替换为你本地的文件路径即可 # 支持csv、xlsx格式,和你日常在Excel里打开的文件完全兼容 raw_data <- read_csv("本地原始数据集路径.csv") # 内置你提供的示例数据,可直接运行测试效果 raw_data <- tibble( School = c("St John", "Marist", "St John", "St John", "Marist"), `Date Registered` = c("01/02/2020", "01/04/2020", "01/02/2020", "01/02/2019", "01/04/2016"), Student_ID = c("D10460A", "D20546A", "D15468A", "D52194A", "D54798A") ) # 核心统计流程 final_report <- raw_data %>% # 将文本格式的注册日期转换为R可识别的标准日期 mutate(`Date Registered` = dmy(`Date Registered`)) %>% # 筛选条件:仅保留2020年的注册记录 filter(year(`Date Registered`) == 2020) %>% # 生成按月份聚合的列维度字段 mutate(register_month = format(`Date Registered`, "%Y-%m")) %>% # 按学校、月份分组,统计对应区间的注册学生数 group_by(School, register_month) %>% summarise(register_num = n_distinct(Student_ID), .groups = "drop") %>% # 转换为宽表格式,完全匹配Excel透视表的输出结构 pivot_wider( id_cols = School, names_from = register_month, values_from = register_num, values_fill = 0 # 无注册记录的单元格自动填充0,和Excel透视表默认显示一致 )
输出结果说明
运行上述代码后得到的final_report,结构和数值和你手动做的Excel透视表完全一致:
| School | 2020-02 | 2020-04 |
|---|---|---|
| St John | 2 | 0 |
| Marist | 0 | 1 |
日常使用调整技巧
- 如果需要调整列维度的月份显示样式(比如改成“2月”“4月”的中文格式),只需把代码里的月份提取格式从
"%Y-%m"改成"%m月"即可,不需要重新调整透视字段。 - 日常更新报告时,只需要把最新的原始数据替换到指定文件路径,重新运行代码就能得到最新统计结果,不需要重复手动拖拽、调整透视表格式。
- 如果需要把结果导出为Excel文件,直接追加一行代码
writexl::write_xlsx(final_report, "学校注册月度统计报告.xlsx")即可,不需要手动复制粘贴表格内容。
内容的提问来源于stack exchange,提问作者Rolo_Sher
相关产品推荐
相关产品推荐

