Office中国论坛/Access中国论坛

标题: 水泥做的女人——浅谈Excel函数的调用 [打印本页]
作者: roych    时间: 2011-4-22 22:24
标题: 水泥做的女人——浅谈Excel函数的调用
本帖最后由 roych 于 2015-6-26 11:14 编辑

       Access在计算方面并不如Excel，复杂的统计不该由Access完成，而是在Excel里完成之后再把数据导入到Access里。不过，有些版友希望Access可以完成Excel的一些工作，这样就可以一劳永逸了。
       我一直觉得应该软件之间尽可能交互使用，扬长避短才能优化工作效率。正如男人是泥做的，女人是水做的，世界才更加精彩。而一旦女子偶尔强势一下无可厚非，但如果永远都一片阳刚，那她也不再是水做的了，应该是水泥做的吧。
       言归正传，以下是对成绩高低进行排名的一段代码（附件里有注释）：
  1. Private Sub 排名_Click()
  2. Dim exl As Excel.Application
  3. Dim wb As Excel.Workbook
  4. Dim ws As Excel.Worksheet
  5. Dim rst As New ADODB.Recordset
  6. rst.Open "select * from 成绩表", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  7. Set exl = CreateObject("Excel.Application")
  8. exl.Workbooks.Add
  9. Set wb = exl.ActiveWorkbook
  10. With wb
  11. Set ws = exl.Worksheets(1)
  12. ws.Activate
  13. With ws
  14. .Range("A1").CopyFromRecordset rst
  15. rnk = exl.WorksheetFunction.Rank(Me.成绩, .Range("C:C"))
  16. End With
  17. wb.Close False, Me.Name
  18. End With
  19. MsgBox Me.姓名 & "的成绩排名为:第" & rnk & "名", vbInformation
  20. rst.Close
  21. End Sub
复制代码
      这里的知识点有两三个：1、使用ADO打开数据集。2、导出数据集到工作表。3、调用Excel内置函数Rank。
       此外，还有更简单的办法，不过测试时发现第二种方法运行要慢些：
  1. Private Sub 排名2_Click()
  2. Dim xcl As Object
  3. Dim rs As New ADODB.Recordset
  4. rs.Open "select * from 成绩表", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  5. Set xcl = CreateObject("excel.sheet")
  6. Set xcl = xcl.Application.ActiveWorkbook.ActiveSheet
  7. With xcl
  8. .[a1].CopyFromRecordset rs
  9. .[k1].Formula = "=rank(" & Me.成绩 & ",C:C)"
  10. End With
  11. MsgBox Me.姓名 & "的成绩排名为:第" & xcl.[k1].Value & "名", vbInformation
  12. Set x = Nothing
  13. End Sub
复制代码
[attach]45393[/attach]
作者: powerhao    时间: 2011-4-22 23:33
作者: xie62    时间: 2011-4-23 07:28
学习学习
作者: wdq    时间: 2011-4-23 09:27
作者: ycxchen    时间: 2011-4-23 09:40
学习！
作者: miracle-y    时间: 2011-4-23 10:21
学习学习
作者: yanwei82123300    时间: 2011-4-23 10:28
学习学习
作者: 红尘如烟    时间: 2011-4-23 12:37
本帖最后由 红尘如烟 于 2011-4-23 12:41 编辑

其实没必要调用Excel，直接用记录集就能实现了，不需要打开Excel，效率会高很多：
  1. Private Sub 排名_Click()
  2.     Dim strSQL As String
  3.     Dim rst As Object    'DAO.Recordset
  4.    
  5.     strSQL = "SELECT DISTINCT 成绩 FROM 成绩表 ORDER BY 成绩 DESC"
  6.     Set rst = CurrentDb.OpenRecordset(strSQL, , 4)    ' dbReadOnly = 4
  7.     rst.FindFirst "成绩=" & Me.成绩
  8.     MsgBox Me.姓名 & "的成绩排名为：第" & rst.AbsolutePosition + 1 & "名"
  9.     rst.Close
  10.     Set rst = Nothing
  11. End Sub
复制代码
不记得是谁说过的：编程这件事，更多的是受限于想象力……
作者: ycxchen    时间: 2011-4-23 13:01
8楼红尘版主的方法也很好，但我认为楼主主要的意思是说明调用EXCEL的方法。
作者: 简    时间: 2011-4-23 18:05
我也来学习学习。
作者: roych    时间: 2011-4-23 19:15
本帖最后由 roych 于 2011-4-23 19:23 编辑

回复 红尘如烟 的帖子

O(∩_∩)O~，实际上直接做一个按成绩排名的查询（或调用ADO），再利用RecordCount等属性也是很容易处理的。
——这并不关乎编程思想，还是ycxchen比较理解，我这里只是提出一种调用Excel函数的做法而已。

此外，特别是第一段代码中，用CopyFromRecordset把记录集复制到Excel中，不失为导出数据的一种好办法。——特别是用Excel导入Access数据时。
作者: CIBOO2000    时间: 2011-4-27 19:00
学习
作者: sxb2007    时间: 2011-4-27 21:40
学习
作者: aslxt    时间: 2011-4-28 08:27
用CopyFromRecordset把记录集复制到Excel中，确实为导出数据的一种好办法，不过字符串不能太长了，好像是1024就会出现问题。
作者: xiaowuo2    时间: 2011-4-29 08:44
一直希望有更多的此类功能例子，狂顶
作者: 真主    时间: 2011-4-29 18:42
原来可以这样做，谢了
作者: iamwg    时间: 2011-5-4 15:00
学习学习
作者: wu8313    时间: 2011-5-6 17:01
本帖最后由 wu8313 于 2011-5-6 17:04 编辑

看看，acc自己的聚合函数有些慢，用这个复制数据到excel，而后计算，再读取，也是一个好办法。
用这个，在excel中形成一个图表也是蛮好的。
有一个问题，当数据量多于 256*256 条，就会报错了。一个excel工作表 最多只能容纳 256*256 行数据。
作者: youchytan    时间: 2011-5-13 21:13
wb.Close False, Me.Name
Excel也用me?
作者: roych    时间: 2011-5-13 22:42
Excel也可以用me，虽然有些少见。
作者: leejiner    时间: 2011-5-14 03:36
正需要
作者: goto2008    时间: 2011-5-24 11:19
作者: efcndi    时间: 2011-11-24 12:30
看看
作者: crystalpao    时间: 2011-11-24 12:52
correct!
作者: wang1950317    时间: 2011-11-24 19:14
学习学习！
作者: grq    时间: 2011-11-28 22:47
学习一下先。
作者: greatcf    时间: 2011-12-19 17:03
多谢了
作者: 柯以笙萧默    时间: 2011-12-19 19:04
{:soso_e100:}{:soso_e100:}
作者: yphxsjjlcq    时间: 2012-2-12 23:32
学习学习
作者: JosephTan    时间: 2012-2-13 07:04
我记得我也有一个excel调用的模块.当时是用来计算条码校验位的,后来那个校验位的模块是老头帮忙弄好了,打开excel来计算速度确实很慢:
Function getbc(barcode As String) As String
Dim excelapplication As Object
Dim str As String
str = Application.CurrentProject.path & "\条码计算.xlsx"
Dim oapp As Excel.Application, oappwork As Excel.Workbook, oappwork_Sheet1 As Excel.Worksheet
    Set oapp = CreateObject("Excel.Application")
    oapp.Visible = False
    Set oappwork = oapp.Workbooks.Open(str)
    Set oappwork_Sheet1 = oappwork.Worksheets("Sheet1")
    oappwork_Sheet1.Cells(1, 1) = barcode
    oappwork.Close (True)
    oapp.Quit
    Set oappwork_Sheet1 = Nothing
    Set oappwork = Nothing
    Set oapp = Nothing
Set excelapplication = GetObject(str)
getbc = excelapplication.Sheets(1).[a2].Value
Set excelapplication = Nothing

End Function
作者: xuwenning    时间: 2012-2-13 08:21
学习
作者: dgmeihao168    时间: 2012-6-10 16:55
水泥做的女人——浅谈Excel函数的调用
作者: 老虎是我的    时间: 2012-6-10 19:06
先不下载  ，积分先
作者: zhao__feng    时间: 2012-6-11 14:25
学习学习
作者: yori2007    时间: 2012-6-12 09:02
作者: pascal331    时间: 2013-1-9 23:07
感谢roych
作者: renyucai1963    时间: 2013-1-10 02:24
版主好。
作者: huangli0356    时间: 2013-1-10 08:45
好东西..谢谢分享..
作者: 轻风    时间: 2013-1-10 12:33
学习之
作者: 你是坏蛋    时间: 2013-1-12 20:28
确实不错
作者: dfang    时间: 2013-1-12 22:23
作者: yanghua1900363    时间: 2013-1-13 08:31
学习学习
作者: YXH_YXH    时间: 2013-5-18 11:22
学习了！！！！！！！
作者: smileyoufu    时间: 2013-5-18 21:58
好东西，学习下
作者: 星辉    时间: 2013-7-23 13:30
这个好,要好好学习.我喜欢.
作者: 张玉军    时间: 2013-8-17 22:29
学习
作者: 牧野白狼    时间: 2013-12-6 00:19
我要看看
作者: zzs225    时间: 2015-6-26 11:03
特来学习



欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/) Powered by Discuz! X3.3