用R玩转微店汇总报表

生信技能树

发布于 2018-11-30 17:46:41

1.3K0

发布于 2018-11-30 17:46:41

文章被收录于专栏：生信技能树

1.新建一个R-project，名叫 financial statements。

（这个地方就很符合jimmy大神的价值观：只允许用打开R-project的方式打开Rstudio，小本本记下来，小心被怼）

2.把微店批量导出的订单明细表和收支明细表放到这个文件夹

3.读取excel表格

fs <- readxl::read_xls('financial statements.xls')
#订单详情表
fs <- readxl::read_xls('financial statements.xls')
#收支明细表
il <- readxl::read_xls('income_list.xls')

订单明细表

收入明细表

自动导出的表，列数足足有几十列，无用信息很多。所以选出有用的几列，并改为英文列名

4.改掉中文列名

#选列和改列名
fs_s <- fs %>%
  select('商品名称',
         '订单金额（不含退款）',
         '买家确认收货时间',
         '退款金额',
         '运费退款')
colnames(fs_s) <- c('goods',
                    'income',
                    'time',
                    'return1',
                    'return2')
il_s <- il %>%
  select("时间",
         "收入（元）",
         "账单类型",
         "交易摘要")
colnames(il_s) <- c('time',
                    'income',
                    'type',
                    'goods')

5.时间处理

报表可以按天汇总，只留日期，不要具体时间

il_st <- il_s %>%
  separate(time,into=c("time","drop"),sep=" ") %>% 
  select(-drop) 
fs_st <- fs_s %>%
  separate(time,into=c("time","drop"),sep=" ") %>% 
  select(-drop)

6.获得精简版的明细表格

这里有一个问题，除了产生退款的订单，其他的退款数值都是空值，应用complete填充，否则无法完成计算！因为缺失值会传染啊。还有一个坑就是导出报表的数字，它的数据类型是字符，所以计算起来要用as.numeric()来改变！否则就会报错了。

##il需要去掉提现支出，此时少了8行
income_details <- il_st %>%
  filter(type !='提现') %>%
  select('time','goods','income','type') 

#fs需要将退款处的空值改为零,计算收入
fs_details <- fs_st %>%
  complete(nesting(time,goods,income),
           fill=list(return1=0)) %>%
  complete(nesting(time,goods,income,return1),
           fill=list(return2=0)) %>%
  mutate(net_income = as.numeric(income) 
         -as.numeric(return1)
         -as.numeric(return2)
  )%>%#实付
  select(time,goods,net_income)%>%
  arrange(time,goods,net_income)

7.核对订单明细结算的总额和收入明细是否一致

income1 <- sum(fs_details$net_income)
ilcd <- filter(income_details,income>0)#只要>0的
income2 <- sum(as.numeric(ilcd$income))

这两个数字应该相等,对账就完成了。（别看了我編滴）

帅帅地导出xls

write.xlsx(income_details,'收入明细.xls')

接下来就是进行汇总统计。

#先计算薅完羊毛的收入
income <- sum(as.numeric(income_details$income))
#按天数、商品汇总
fs_count <- count(fs_details,time,goods)
fs_sum1 <- summarise(group_by(fs_details,time,goods), 
                     all = sum(as.numeric(net_income), na.rm = F))
day_income <- left_join(fs_count,fs_sum1,by=c('time','goods'))
rm(fs_count,fs_sum1)
#按商品汇总
fs_count <- count(fs_details,goods)
fs_sum1 <- summarise(group_by(fs_details,goods), all = sum(net_income))
goods_income <- left_join(fs_count,fs_sum1,by='goods')%>%
rbind(c('总计',sum(fs_count$n),sum(fs_sum1$all))) %>%
  rbind(c(NA,'支出',outcome),
    c(NA,'净收入',income))
#最后改一下列名
colnames(day_income) <- c('日期','商品','销量','销售额')
colnames(goods_income) <- c('商品','销量','销售额')
colnames(fs_details)<- c('商品','销量','销售额')
#导出
write.xlsx(goods_income,'商品报表.xls')
write.xlsx(day_income,'日营业额报表.xls')