一键计算华师一卡通的消费记录

date

Apr 5, 2017

note

slug

calculate-ecard-records

type

Post

status

Published

tags

技术

summary

继上次我写了个PHP脚本抓取我的一卡通消费记录（用PHP爬取个人一卡通的消费记录）之后，我遇到了一些奇怪的问题。比如说，我统计上个学期的一卡通的花费的时候竟发现少算了400+RMB，百思不得其解。直到前段时间我才发现，原来学校的这个网站查询到的信息是不完整的，有些记录竟然会有缺失，后来我请教了负责这一块的老师，老师表示大概是学校里面某些一卡通的消费机离线时间太长，消费记录没有同步到服务器上，所以造成了这个问题。

old_url

https://blog.izgq.net/archives/808/

所以我花了一晚上重新设计了一个启用了宏功能的Excel表格，写了个vba脚本一键补全那些缺失的消费记录，顺带加上了一键计算每顿饭的总花费的功能（在华师的食堂一般是先在售饭窗口买完饭之后，再去一个个窗口分别点菜，所以消费记录一般都比较零散，一下子不好计算每顿饭的花费。）

使用方法：把脚本得到的 result.csv 表格复制到这个文档中的对应位置，然后点击按钮运行vba脚本，程序将自动把缺失的记录补齐（只能补全价格和余额，其它信息还是需要自己脑补），并算出每个就餐时段的花费。

使用步骤：

清除文档中原有的内容

复制result.csv的数据

粘贴数据

运行脚本

计算完毕

然后就可以愉快地记账啦！！

有兴趣的小伙伴们可以试试：一卡通账单计算.xlsm

这里不得不吐槽一下，要玩 Office 的话，Windows系统还是离不开的（貌似只有Windows下的office才对vba有完美的支持），无奈的我在这Ubuntu上安了个Windows 7的虚拟机。

VBA代码参考


'
' 一键计算 Macro
'
' 首先删除之前的记录
Sheets("Sheet1").Range(Cells(3, 5), Cells(Sheets("Sheet1").UsedRange.Rows.Count, 5)).Clear
' 查找缺失的消费记录
Dim a, i, j, k, total
a = 0
For i = 2 To Sheets("Sheet1").UsedRange.Rows.Count
    If (Abs(Sheets("Sheet1").Cells(i, 2).Value + Sheets("Sheet1").Cells(i, 3).Value - Sheets("Sheet1").Cells(i + 1, 3).Value) > 0.01) Then
        'MsgBox (i)
        Sheets("Sheet1").Rows(i + 1).Insert
        Sheets("Sheet1").Cells(i + 1, 3).Value = Sheets("Sheet1").Cells(i, 2).Value + Sheets("Sheet1").Cells(i, 3).Value
        Sheets("Sheet1").Cells(i + 1, 2).Value = (Sheets("Sheet1").Cells(i + 2, 3).Value - Sheets("Sheet1").Cells(i + 1, 3).Value)
        a = a + 1
    End If
Next
' 更新状态
Sheets("Sheet1").Cells(2, 5).Formula = "=IF(ABS((C2+B2)-C3)<0.01,""正常"", ""异常"")"
Sheets("Sheet1").Range("E2").Select
Selection.AutoFill Destination:=Range(Cells(2, 5), Cells(Application.WorksheetFunction.Count(Sheet1.Range("C:C")), 5)), Type:=xlFillDefault
' MsgBox ("补全了 " & a & "个数据")

'
' 计算每餐的花费 Macro
'
' 首先删除之前的记录
Sheets("Sheet1").Range(Cells(2, 6), Cells(Sheets("Sheet1").UsedRange.Rows.Count, 6)).Clear

total = Application.WorksheetFunction.Count(Sheet1.Range("C:C"))
'MsgBox (total)
For i = 2 To total
    k = i
    j = Cells(i, 2).Value
    Do While (Abs(DateDiff("n", Cells(i, 1).Value, Cells(k + 1, 1).Value)) < 40)
        k = k + 1
        j = j + Cells(k, 2).Value
    Loop
    'MsgBox (k)
    'Cells(i, 6).Value = j
    Cells(i, 6).Formula = "=sum(B" & CStr(i) & ":B" & CStr(k) & ")"
    i = k
Next

MsgBox ("补全了 " & a & " 个数据并算出了每餐的花费")