android scrollow嵌套listview,excel 中IDEX和MATCH函数嵌套应用.doc-CSDN博客

excel 中IDEX和MATCH函数嵌套应用

2007-12-3

1，ADO-SQL

1，数据表表头在第一行；

2，Where子句中，各条件要用括号( )分开；

字符串变量用法：客户=’”& aa & “’ ；

数值用变量：编码=” & bb & “

日期变量：开票日期 between #” & ks & “# And #” & js & “#

日期：#2007-1-1 # ;

7，From 子句中，表格名用变量：From [“ & MysheetName & “$] ; _

2008-3-14

Select FirstName + ‘ ‘ +LastName,Date,Hours From …

将多个列合并为一个结果列

当只须执行单个查询且无须ADO Connection对象的高级属性时，可不创建ADO 的Connection对象。

摘自“Excel专业开发” P.352

2011-5-27

T是时间的字符串格式，用了CDtae函数成时间格式

打开有密码的Access文件：

stpath = ThisWorkbook.Path & Application.PathSeparator & "DataBase.md"

CNN.Open "Provider=Microsoft.Jet.Oledb.4.0;data Source=" & stpath & ";Jet OLEDB:Database Password=" & "woaivba"

2，‘狼版主的代码

Function shijian(ByVal r As Range) As StringDim s, t, i&, v&s = Split(r.Value)For i = 0 To UBound(s)t = Split(s(i), "-")v = v + 24 * 60 * (CDate(t(1)) - CDate(t(0))) ‘转换成分钟数Nextshijian = Format(TimeSerial(0, v, 0), "h小时m分钟")‘Timevalue函数转换成时间格式End Function

已知年月，求当月天数

rq = CDate(aa)

dd = Day(DateSerial(Year(rq), Month(rq) + 1, 0))

2011-5-28

‘/thread-724176-1-1.html###

3，‘数组的一部分赋值给另一个数组应用一念老师的代码

Sub GetSum()? ? Application.ScreenUpdating = False? ? Dim Arr, Ary, k%, x$? ? Dim Dic As Object, Itm? ? Sheet2.Rows("2:65536").Delete? ? Arr = Sheets(1).[A1].CurrentRegion? ? Set Dic = CreateObject("Scripting.Dictionary")? ? For k = 2 To UBound(Arr)? ?? ???x = Arr(k, 1) & "|" & Arr(k, 2)? ?? ???If Not Dic.exists(x) Then? ?? ?? ?? ?Ary = Application.Index(Arr, k) ‘数组的一部分赋值给另一个数组? ?? ???Else? ?? ?? ?? ?Ary = Dic(x)? ?? ?? ?? ?Ary(3) = Ary(3) & "," & Arr(k, 3)? ?? ?? ?? ?Ary(6) = Ary(6) + Arr(k, 6)? ?? ???End If? ?? ???Dic(x) = Ary ‘把数组赋给字典关键字的项? ? Next? ? ? ? For Each Itm In Dic? ?? ???[A65536].End(3)(2).Resize(1, 7) = Dic(Itm) ‘把字典项的值赋给单元格区域? ? Next? ? Set Dic = Nothing? ? Application.ScreenUpdating = TrueEnd Sub

‘2012-2-8

/thread-823801-1-1.html

2011-6-11

4，Union的用法

If rng2 Is Nothing Then Set rng2 = Cells(i, "i") Else Set rng2 = Union(rng2, Cells(i, "i"))

If Not rng2 Is Not