|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 djk1020 于 2018-9-1 17:06 编辑
这是给一个在保险公司上班的亲戚编写的,现分享给有需要的人:
根据上表数据统计出下表数据(搞不懂为何医院所用软件导出的表要用那么多个单元格合并来表示一个数据,难道是给统计设置障碍?!)
- Sub DBZF()
- '大病支付及补偿统计
- Dim Bk As Workbook
- Set Bk = ActiveWorkbook
- If InStr(Bk.Name, "医保住院明细") > 0 Then
- Application.ScreenUpdating = False
- Dim Sht As Worksheet
- Set Sht = Bk.Worksheets(2)
- With Sht
- .Cells.Clear
- .Rows.RowHeight = 30
- .Columns.ColumnWidth = 17
- With .Range("A1:D5")
- .Borders(xlEdgeLeft).LineStyle = xlContinuous
- .Borders(xlEdgeRight).LineStyle = xlContinuous
- .Borders(xlEdgeTop).LineStyle = xlContinuous
- .Borders(xlEdgeBottom).LineStyle = xlContinuous
- .Borders(xlInsideVertical).LineStyle = xlContinuous
- .Borders(xlInsideHorizontal).LineStyle = xlContinuous
- .HorizontalAlignment = xlRight
- End With
- .Cells(2, 1).Value = "大病支付(400元)"
- .Cells(3, 1).Value = "大病支付(其 它)"
- .Cells(4, 1).Value = "大病二次补偿"
- .Cells(5, 1).Value = "合计"
- .Cells(1, 2).Value = "人数"
- .Cells(1, 3).Value = "总金额"
- .Cells(1, 4).Value = "大病支付合计"
- .Range("C2:D5").NumberFormatLocal = "¥#,##0.00;¥-#,##0.00"
- .Cells(2, 2).Value = "=countif(sheet1!au:av,""400"")"
- .Cells(3, 2).Value = "=countif(sheet1!au:av,"">0"")-b2"
- .Cells(4, 2).FormulaArray = "=SUM(IF(ISNUMBER(Sheet1!AZ1:BH4096+0),IF(Sheet1!AZ1:BH4096+0>0,1,0),0))"
- .Cells(2, 3).Value = "=b2*400"
- .Cells(3, 3).Value = "=sum(sheet1!au:av)-c2"
- .Cells(4, 3).FormulaArray = "=SUM(IF(ISNUMBER(Sheet1!AZ1:BH4096+0),Sheet1!AZ1:BH4096+0,0))"
- .Cells(5, 3).Value = "=sum(c2:c4)"
- .Cells(2, 4).Value = "=sum(c2:c3)"
- .Select
- End With
- Application.ScreenUpdating = True
- End If
- End Sub
复制代码
|
|