文章标签 ‘Excel’
要求：在C列输入数值，A列和B列自动填充当前系统日期和时间。
步骤：首先定义A列和B列中的内容格式，A列定义为日期，B列定义为时间，
然后使用快捷键ALT+F11调出VBA编辑器，
双击要使用自动填充的Sheet，本例中为Sheet1，在右侧输入下列代码：
1 2 3 4 5 6 | Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then Target.Offset(0, -2) = Date Target.Offset(0, -1) = Time End If End Sub |
代码中的Target.Column = 3为C列，如果是E列则为Target.Column = 5，
Target.Offset(0, -2)为偏移量，本例中为C列向左2列，也就是A列，
如果想在C列填充数值，在E列自动填充日期则把Target.Offset(0, -2) = Date修改为Target.Offset(0, 2) = Date即可。
保存后回到excel表中，在C列中输入数值，A列和B列自动就填充日期和时间了。（注：本excel需要启用宏）
excel中offset函数如何使用
语法：OFFSET(基点单元格，移动的行数，移动的列数，所要引用的高度，所要引用的宽度）
例：
=OFFSET(A3,2,3,1,1)
A3是基点单元格，
2是正数，为向下移动2 行，负为向上移动2 行
3是正数，为向右移动3列，负为向左移动3列
1是引用 1 个单元格的高度
1 是引用 1 个单元格的宽度
它的结果是引用了D5中数值。作用：
以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。
语法：
OFFSET(reference,rows,cols,height,width)
Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值 #VALUE!。
Rows 相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用 5 作为参数 Rows，则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。
Cols 相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用 5 作为参数 Cols，则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。
Height 高度，即所要返回的引用区域的行数。Height 必须为正数。
Width 宽度，即所要返回的引用区域的列数。Width 必须为正数。
说明：
如果行数和列数偏移量超出工作表边缘，函数 OFFSET 返回错误值 #REF!。
如果省略 height 或 width，则假设其高度或宽度与 reference 相同。
函数 OFFSET 实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。函数 OFFSET 可用于任何需要将引用作为参数的函数。例如，公式 SUM(OFFSET(C2,1,2,3,1)) 将计算比单元格 C2 靠下 1 行并靠右 2 列的 3 行 1 列的区域的总值。
示例：
如果您将示例复制到空白工作表中，可能会更易于理解该示例。
公式 说明（结果）
=OFFSET(C3,2,3,1,1) 显示单元格 F5 中的值 (0)
=SUM(OFFSET(C3:E5,-1,0,3,3)) 对数据区域 C2:E4 求和 (0)
=OFFSET(C3:E5,0,-3,3,3) 返回错误值 #REF!，因为引用区域不在工作表中
点击下载理解OFFSET函数.xls
我们经常会遇到需要对满足两个以上条件的数据进行查找并引用的问题，SHEET1工作表内容如图：
现在要求在SHEET2工作表的A、B列输入有关内容后，C列自动从SHEET1工作表中查找并引用相应的C列的内容。SHEET2工作表如图：
SHEET2工作表C1单元格使用以下数组公式，可达到目的：
=IF(OR(A1="",B1=""),"",OFFSET(Sheet1!$C$1,SUM(IF((Sheet1!A$1:A$1000=A1)*(Sheet1!B$1:B$1000=B1),ROW(Sheet1!C$1:C$1000),0))-1,0,1,1))
注意：输入完公式后要按Ctrl+Shift+Enter键,让它自动加上数组公式符号"{}"。
用VLOOKUP函数解决方法：
=IF(OR(A1="",B1=""),"",VLOOKUP(A1&B1,IF({1,0},Sheet1!A$1:$A$1000&Sheet1!B$1:B$1000,Sheet1!C$1:C$1000),2,0))
用INDEX和MATCH函数解决方法：
=IF(OR(A1="",B1=""),"",INDEX(Sheet1!C$1:C$1000,MATCH(A1&B1,Sheet1!A$1:A$1000&Sheet1!B$1:B$1000,0)))
这两个也是数组公式。
另提供两个不用数组公式的解决方法：
=IF(OR(A1="",B1=""),"",INDIRECT("Sheet1!$C"&SUMPRODUCT((Sheet1!A$1:A$1000=A1)*(Sheet1!B$1:B$1000=B1)*ROW(Sheet1!C$1:C$1000))))
=IF(OR(A1="",B1=""),"",LOOKUP(2,1/((Sheet1!A$1:A$1000=A1)*(Sheet1!B$1:B$1000=B1)),Sheet1!C$1:C$1000))
推荐使用VLOOKUP的应用，而且不用太多改变原数据库。
增加对#N/A的判断函数：
更改函数如下（数组函数）
=IF(ISERROR(VLOOKUP(A1&B1,IF({1,0},Sheet1!A$1:$A$1000&Sheet1!B$1:B$1000,Sheet1!C$1:C$1000),2,FALSE)),"",VLOOKUP(A1&B1,IF({1,0},Sheet1!A$1:$A$1000&Sheet1!B$1:B$1000,Sheet1!C$1:C$1000),2,0))
如果该位置显示为0 可以使用“条件格式……” 当该格＝0时，字体颜色同背景色。
以姓名，电话和部门的自动填充为例：
首先在Sheet2中录入相应的数据，例如：姓名，电话，部门。
然后我们在Sheet1中输入一些数据，例如：姓名，性别，年龄，电话，部门。
其中Sheet1和Sheet2中的姓名字段内容相同，Sheet2中的电话和部门已经输入完毕，作为数据源，Sheet1中的电话和部门是我们需要输入Sheet1中的姓名后自动填充的。
Sheet2中的数据：
Sheet1中的数据：
在Sheet1中的D2处输入公式：
IF(COUNTIF(Sheet2!A:A,A2),VLOOKUP(A2,Sheet2!A:B,2,0),"")
在Sheet1中的E2处输入公式：
IF(COUNTIF(Sheet2!A:A,A2),VLOOKUP(A2,Sheet2!A:C,3,0),"")
然后拖拽D2和E2向下，这样在Sheet1的A列中输入姓名，Excel就会自动到Sheet2中查找此姓名相对应的电话和部门了。
在用EXCEL录入表格数据时，常常会遇到数据的值只在几个固定值中选择一个的情况。比如：人的性别只有男或女，学历只可能是初中、高中、中专、大专、本 科等。遇到这样的问题，如果我们手工录入，效率又低又容易出错！所以我们添加一个下拉按钮。下面就通过编排一个表表来教大家如何实现：
（1）创建数据源表
在Sheet2输入所需数据，并选中需要的数据，如：A2:A18，右键选择“命名单元格区域(R)...”
（2）定义名称
在名称中输入您想要设置的名称，如：数据，然后确定。
（3）数据关联
回到Sheet1中，选择要引用数据源的列或行，如：B2:B18，然后选择“数据”——“数据有效性(V)...”。
在有效性条件的允许中选择“序列”，来源输入“=数据”，也就是第二部中定义的名称。
（4）测试
这时选择定义数据有效性的单元格，单元格后面就会有个下拉箭头，点击下拉箭头就可以选择数据源了。
（5）数据源变更
选择“公式”——“名称管理器”，在名称管理器中选择要修改的名称，选择引用位置，修改为新的数据位置。
修改后点击关闭，保持即可。
比如想把以下的ip地址表按升序进行排列
192.1.8.1
192.1.8.2
192.1.8.128
192.1.8.168
192.1.8.233
192.1.82.159
192.1.8.221
192.1.8.6
192.1.8.85
可是当使用"排序"选项进行排序时，就变成以下顺序：
192.1.8.1
192.1.8.128
192.1.8.168
192.1.8.2
192.1.8.221
192.1.8.233
192.1.8.6
192.1.8.85
192.1.82.159
除非把192.1.8.1这样形式的地址都变成192.1.8.001才可以正常排列出来，可是这样手动改起来太麻烦啦，有没有简单的方法呢？
方法如下：
首先添加个辅助列，输入下面的公式，再按该列排序即可（其中A1为原始IP列的坐标）。
=LEFT(A1,FIND("%",SUBSTITUTE(A1,".","%",3))-1)&"."&TEXT(RIGHT(A1,LEN(A1)-FIND("%",SUBSTITUTE(A1,".","%",3))),"000")
SUBSTITUTE语法：
SUBSTITUTE(text,old_text,new_text,instance_num)
Text 为需要替换其中字符的文本，或对含有文本的单元格的引用。
Old_text 为需要替换的旧文本。
New_text 用于替换 old_text 的文本。
Instance_num 为一数值，用来指定以 new_text 替换第几次出现的 old_text。如果指定了 instance_num，则只有满足要求的 old_text 被替换；否则将用 new_text 替换 Text 中出现的所有 old_text。
示例
SUBSTITUTE("Sales Data", "Sales", "Cost") 等于 "Cost Data"
SUBSTITUTE("Quarter 1, 1991", "1", "2", 1) 等于 "Quarter 2, 1991"
SUBSTITUTE("Quarter 1, 1991", "1", "2", 3) 等于 "Quarter 1, 1992"
如果要在名称为 CellCont2 的单元格中，用方括号来替换所有出现的文本常量 "Separator"，请输入：
SUBSTITUTE(CellCont2, Separator, "] [")
一、使用OWC
什么是OWC？
OWC是Office Web Compent的缩写，即Microsoft的Office Web组件，它为在Web中绘制图形提供了灵活的同时也是最基本的机制。在一个intranet环境中，如果可以假设客户机上存在特定的浏览器和一些功能强大的软件（如IE5和Office 2000），那么就有能力利用Office Web组件提供一个交互式图形开发环境。这种模式下，客户端工作站将在整个任务中分担很大的比重。
<%
Option Explicit
Class ExcelGen
Private objSpreadsheet
Private iColOffset
Private iRowOffset
Sub Class_Initialize()
Set objSpreadsheet = Server.CreateObject("OWC.Spreadsheet")
iRowOffset = 2
iColOffset = 2
End Sub
Sub Class_Terminate()
Set objSpreadsheet = Nothing "Clean up
End Sub
Public Property Let ColumnOffset(iColOff)
If iColOff ＞ 0 then
iColOffset = iColOff
Else
iColOffset = 2
End If
End Property
Public Property Let RowOffset(iRowOff)
If iRowOff ＞ 0 then
iRowOffset = iRowOff
Else
iRowOffset = 2
End If
End Property Sub GenerateWorksheet(objRS)
"Populates the Excel worksheet based on a Recordset"s contents
"Start by displaying the titles
If objRS.EOF then Exit Sub
Dim objField, iCol, iRow
iCol = iColOffset
iRow = iRowOffset
For Each objField in objRS.Fields
objSpreadsheet.Cells(iRow, iCol).Value = objField.Name
objSpreadsheet.Columns(iCol).AutoFitColumns
"设置Excel表里的字体
objSpreadsheet.Cells(iRow, iCol).Font.Bold = True
objSpreadsheet.Cells(iRow, iCol).Font.Italic = False
objSpreadsheet.Cells(iRow, iCol).Font.Size = 10
objSpreadsheet.Cells(iRow, iCol).Halignment = 2 "居中
iCol = iCol + 1
Next "objField
"Display all of the data
Do While Not objRS.EOF
iRow = iRow + 1
iCol = iColOffset
For Each objField in objRS.Fields
If IsNull(objField.Value) then
objSpreadsheet.Cells(iRow, iCol).Value = ""
Else
objSpreadsheet.Cells(iRow, iCol).Value = objField.Value
objSpreadsheet.Columns(iCol).AutoFitColumns
objSpreadsheet.Cells(iRow, iCol).Font.Bold = False
objSpreadsheet.Cells(iRow, iCol).Font.Italic = False
objSpreadsheet.Cells(iRow, iCol).Font.Size = 10
End If
iCol = iCol + 1
Next "objField
objRS.MoveNext
Loop
End Sub Function SaveWorksheet(strFileName)
"Save the worksheet to a specified 200512995930.htm
On Error Resume Next
Call objSpreadsheet.ActiveSheet.Export(strFileName, 0)
SaveWorksheet = (Err.Number = 0)
End Function
End Class
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "Select * FROM xxxx", "Provider=SQLOLEDB.1;Persist Security
Info=True;User ID=xxxx;Password=xxxx;Initial Catalog=xxxx;Data source=xxxx;"
Dim SaveName
SaveName = Request.Cookies("savename")("name")
Dim objExcel
Dim ExcelPath
ExcelPath = "Excel\" & SaveName & ".xls"
Set objExcel = New ExcelGen
objExcel.RowOffset = 1
objExcel.ColumnOffset = 1
objExcel.GenerateWorksheet(objRS)
If objExcel.SaveWorksheet(Server.MapPath(ExcelPath)) then
"Response.Write "＜html＞＜body bgcolor="gainsboro" text="#000000"＞已保存为Excel文件.
＜a href="" & server.URLEncode(ExcelPath) & ""＞下载＜/a＞"
Else
Response.Write "在保存过程中有错误!"
End If
Set objExcel = Nothing
objRS.Close
Set objRS = Nothing
%＞
二、用Excel的Application组件在客户端导出到Excel或Word
注意：两个函数中的“data“是网页中要导出的table的 id
＜input type="hidden" name="out_word" onclick="vbscript:buildDoc" value="导出到word" class="notPrint"＞
＜input type="hidden" name="out_excel" onclick="AutomateExcel();" value="导出到excel" class="notPrint"＞
导出到Excel代码
＜SCRIPT LANGUAGE="javascript"＞
＜!--
function AutomateExcel()
{
// Start Excel and get Application object.
var oXL = new ActiveXObject("Excel.Application");
// Get a new workbook.
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
var table = document.all.data;
var hang = table.rows.length;
var lie = table.rows(0).cells.length;
// Add table headers going cell by cell.
for (i=0;i＜hang;i++)
{
for (j=0;j＜lie;j++)
{
oSheet.Cells(i+1,j+1).value = table.rows(i).cells(j).innerText;
}
}
oXL.Visible = true;
oXL.UserControl = true;
}
//--＞
＜/SCRIPT＞
导出到Word代码
＜script language="vbscript"＞
Sub buildDoc
set table = document.all.data
row = table.rows.length
column = table.rows(1).cells.length
Set objWordDoc = CreateObject("Word.Document")
objWordDoc.Application.Documents.Add theTemplate, False
objWordDoc.Application.Visible=True
Dim theArray(20,10000)
for i=0 to row-1
for j=0 to column-1
theArray(j+1,i+1) = table.rows(i).cells(j).innerTEXT
next
next
objWordDoc.Application.ActiveDocument.Paragraphs.Add.Range.InsertBefore("综合查询结果集") //显示表格标题
objWordDoc.Application.ActiveDocument.Paragraphs.Add.Range.InsertBefore("")
Set rngPara = objWordDoc.Application.ActiveDocument.Paragraphs(1).Range
With rngPara
.Bold = True //将标题设为粗体
.ParagraphFormat.Alignment = 1 //将标题居中
.Font.Name = "隶书" //设定标题字体
.Font.Size = 18 //设定标题字体大小
End With
Set rngCurrent = objWordDoc.Application.ActiveDocument.Paragraphs(3).Range
Set tabCurrent = ObjWordDoc.Application.ActiveDocument.Tables.Add(rngCurrent,row,column)
for i = 1 to column
objWordDoc.Application.ActiveDocument.Tables(1).Rows(1).Cells(i).Range.InsertAfter theArray(i,1)
objWordDoc.Application.ActiveDocument.Tables(1).Rows(1).Cells(i).Range.ParagraphFormat.alignment=1
next
For i =1 to column
For j = 2 to row
objWordDoc.Application.ActiveDocument.Tables(1).Rows(j).Cells(i).Range.InsertAfter theArray(i,j)
objWordDoc.Application.ActiveDocument.Tables(1).Rows(j).Cells(i).Range.ParagraphFormat.alignment=1
Next
Next
End Sub
＜/SCRIPT＞
三、直接在IE中打开，再存为EXCEL文件
把读出的数据用＜table＞格式，在网页中显示出来，同时，加上下一句即可把EXCEL表在客客户端显示。
＜%response.ContentType ="application/vnd.ms-excel"%＞
注意：显示的页面中，只把＜table＞输出，最好不要输出其他表格以外的信息。
四、导出以半角逗号隔开的csv
用fso方法生成文本文件的方法，生成一个扩展名为csv文件。此文件，一行即为数据表的一行。生成数据表字段用半角逗号隔开。（有关fso生成文本文件的方法，在此就不做介绍了）
CSV文件介绍 （逗号分隔文件）
选择该项系统将创建一个可供下载的CSV 文件； CSV是最通用的一种文件格式，它可以非常容易地被导入各种PC表格及数据库中。
请注意即使选择表格作为输出格式，仍然可以将结果下载CSV文件。在表格输出屏幕的底部，显示有 "CSV 文件"选项，点击它即可下载该文件。
如果您把浏览器配置为将您的电子表格软件与文本（TXT）/逗号分隔文件（CSV） 相关联，当您下载该文件时，该文件将自动打开。下载下来后，如果本地已安装EXCEL，点击此文件，即可自动用EXCEL软件打开此文件。
