EXEL常用函数.docx

1、EXEL常用函数常用函数：1.HEX2DEC 将十六进制数转换为十进制数2.DEC2HEX 将十六进制数转换为十进制数3.CELL返回某一引用区域的左上角单元格的格式、位置或内容等信息 如：=CELL(contents, B3) 结果： 单元格B3上的内容 4.IF5.COUNTIF 计算区域中满足给定条件的单元格的个数6.INDEX返回表或区域中的值或对值的引用。INDEX 函数有两种形式：数组 （数组:用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。）形式和引用形式7.MATCH返回在指定方式下与指定数值匹配的数组

2、8.VLOOKUP在表格数组的首列查找值，并由此返回表格数组当前行中其他列的值9.HLOOKUP在表格数组的首行查找值，并由此返回表格数组当前行中其他行的值10.ROW 返回引用的行号 11.COLUMN 返回一引用的列号12.COLUMNS 返回一引用或者数组的列数13.N 转化为数值的值14.LEFT 后15.RIGHT16.TEXT数值转化为文本 参数不能有 * 如：=TEXT(1,0000) 结果 000117.VALUE 文本转化数值 18.EXACT 完全匹配 区分大小写19.TURNC将数字的小数部分截去，返回整数20.MOD 余数 MUD(A/B)21.SIGN 返回数字符号

3、正数为1 0为0 负数= -122.=MID(CELL(filename,A1),FIND(,CELL(filename,A1)+1,FIND(,CELL(filename,A1)-FIND(,CELL(filename,A1)-1)单元格=文件名 filename-包括路径名字的文件名Vb中 cells(1,1)=ThisWorkbook.Name + : + ThisWorkbook.ActiveSheet.NametRow = ThisWorkbook.Sheets(1).Range(a65536).End(xlUp).Row + 1 从当前文档的sheet1的A65536单元格向上移动

4、，定位到第一个没有数据的单元格，并将此行的行号赋予tRowvbCrLf 换行命令VBxls.DisplayAlerts = False23.SUBSTITUTE 替换函数可以使用 ROUND（四舍五入）、ROUNDUP（只入不舍）和 ROUNDDOWN（只舍不入）这 3 个函数来实现您的需求。Sub MyInput() With Workbooks(Book1).Worksheets(Sheet1).Cells(1, 1) .Formula = =SQRT(50) With .Font .Name = Arial .Bold = True .Size = 8 End With End With

5、End Sub24.应用示例=LEFT(A1,LEN(A1)-4)&DEC2HEX(HEX2DEC(RIGHT(A1,4)+1)十六进制自动增加=IF(OR(COUNTIF(G2,基本*)0,COUNTIF(G2,*广播*)0),红,黄)=IF(N(G4)=0,IF(COUNTIF(G4,*增强*)0,黄,红)=IF(OR(LEN(G4)=0,G4=主板),IF(COUNTIF(G4,*增强*)0,黄,红)=LEFT(A1,LEN(A1)-4)&DEC2HEX(HEX2DEC(RIGHT(A1,4)+10)=IF(A20,TRUNC(RIGHT(A2,4)-8540)/10),)CELL全部显

6、示全部隐藏返回某一引用区域的左上角单元格的格式、位置或内容等信息。语法CELL(info_type,reference)Info_type 为一个文本值，指定所需要的单元格信息的类型。下面列出 info_type 的可能值及相应的结果。Info_type返回address引用中第一个单元格的引用，文本类型。col引用中单元格的列标。color如果单元格中的负值以不同颜色显示，则为 1，否则返回 0。contents引用中左上角单元格的值：不是公式。filename包含引用的文件名（包括全部路径），文本类型。如果包含目标引用的工作表尚未保存，则返回空文本 ()。format与单元格中不同的数字格

7、式相对应的文本值。下表列出不同格式的文本值。如果单元格中负值以不同颜色显示，则在返回的文本值的结尾处加“-”；如果单元格中为正值或所有单元格均加括号，则在文本值的结尾处返回“()”。parentheses如果单元格中为正值或全部单元格均加括号，则为 1，否则返回 0。prefix与单元格中不同的“标志前缀”相对应的文本值。如果单元格文本左对齐，则返回单引号 ()；如果单元格文本右对齐，则返回双引号 ()；如果单元格文本居中，则返回插入字符 ()；如果单元格文本两端对齐，则返回反斜线 ()；如果是其他情况，则返回空文本 ()。protect如果单元格没有锁定，则为 0；如果单元格锁定，则为 1。

8、row引用中单元格的行号。type与单元格中的数据类型相对应的文本值。如果单元格为空，则返回“b”。如果单元格包含文本常量，则返回“l”；如果单元格包含其他内容，则返回“v”。width取整后的单元格的列宽。列宽以默认字号的一个字符的宽度为单位。Reference 表示要获取其有关信息的单元格。如果忽略，则在 info_type 中所指定的信息将返回给最后更改的单元格。下表描述 info_type 为“format”，以及引用为用内置数字格式设置的单元格时，函数 CELL 返回的文本值。如果 Microsoft Excel 的格式为CELL 返回值常规G0F0#,#0,00.00F2#,#0.

9、00,2$#,#0_);($#,#0)C0$#,#0_);Red($#,#0)C0-$#,#0.00_);($#,#0.00)C2$#,#0.00_);Red($#,#0.00)C2-0%P00.00%P20.00E+00S2# ?/? 或 # ?/?Gyy-m-d 或 yy-m-d h:mm 或 dd-mm-yyD4d-mmm-yy 或 dd-mmm-yyD1d-mmm 或 dd-mmmD2mmm-yyD3dd-mmD5h:mm AM/PMD7h:mm:ss AM/PMD6h:mmD9h:mm:ssD8如果 CELL 公式中的 info_type 参数为“format”，而且以后又用自定义格

10、式设置了单元格，则必须重新计算工作表以更新 CELL 公式。说明函数 CELL 用于与其他电子表格程序兼容。示例如果您将示例复制到空白工作表中，可能会更易于理解该示例。操作方法1.创建空白工作簿或工作表。 2.请在“帮助”主题中选取示例。不要选取行或列标题。 从帮助中选取示例。3.按 Ctrl+C。 4.在工作表中，选中单元格 A1，再按 Ctrl+V。 5.若要在查看结果和查看返回结果的公式之间切换，请按 Ctrl+（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。 123A数据5-MarTOTAL公式说明（结果）=CELL(row,A20)单元格 A20 的行号 (

11、20)=CELL(format, A2)第一个字符串的格式代码（D2,请参见上面的信息）=CELL(contents, A3)单元格 A3 的内容 (TOTAL)ASCII字符集按ALT键 输入数字如：按ALT键输入176 得到度数的符号十进制字符十进制字符128192129193130194131195132196133197134198135199136200137201138202139203140204141205142206143207144208145209146210147211148212149213150214151215152216153217154218155219156

12、220157221158222159223160224161225162226163227164228165229166230167231168232169233170234171235172236173237174238175239176240177241178242179243180244181245182246183247184248185249186250187251188252189253190254191255SUBSTITUTE全部显示全部隐藏在文本字符串中用 new_text 替代 old_text。如果需要在某一文本字符串中替换指定的文本，请使用函数 SUBSTITUTE；如

13、果需要在某一文本字符串中替换指定位置处的任意文本，请使用函数 REPLACE。语法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。Z自动删

14、除重复项目VB函数Sub 机型分类()x = ActiveCell.Rowy = ActiveCell.ColumnDo While Cells(x, y).Value x = x + 1z = x + 1Do While Cells(z, y).Value z = z + 1If (Cells(x, y).Value = Cells(z, y).Value) Then Cells(z, y).EntireRow.DeleteLoopLoopEnd SubSub 地区软件()Cells(1, 13).Value = 地区软件x = 2Do While Cells(x, 2).Value Cel

15、ls(x, 13).Value = Cells(x, 2).ValueCells(x, 13).Value = Replace(Cells(x, 13).Value, DVN, )Cells(x, 13).Value = Replace(Cells(x, 13).Value, HMC, )Cells(x, 13).Value = Replace(Cells(x, 13).Value, HM-, )Cells(x, 13).Value = Replace(Cells(x, 13).Value, HM_, )Cells(x, 13).Value = Replace(Cells(x, 13).Val

16、ue, DH-, )Cells(x, 13).Value = Replace(Cells(x, 13).Value, DH_, )Cells(x, 13).Value = Replace(Cells(x, 13).Value, STB, )Cells(x, 13).Value = Replace(Cells(x, 13).Value, IPTV, )Cells(x, 13).Value = Replace(Cells(x, 13).Value, IPQAM, )Cells(x, 13).Value = Replace(Cells(x, 13).Value, 华数专用学习型遥控器, )Cells

17、(x, 13).Value = Replace(Cells(x, 13).Value, iPanel3, )Cells(x, 13).Value = Replace(Cells(x, 13).Value, _, )Cells(x, 13).Value = Replace(Cells(x, 13).Value, +, )Cells(x, 13).Value = Replace(Cells(x, 13).Value, 不超频, )Cells(x, 13).Value = Replace(Cells(x, 13).Value, 华数学习型遥控器, )Cells(x, 13).Value = Repl

18、ace(Cells(x, 13).Value, IPANEL2.0, )Cells(x, 13).Value = Replace(Cells(x, 13).Value, 高频头, )Cells(x, 13).Value = Replace(Cells(x, 13).Value, 华数专用遥控器, )If InStrB(Cells(x, 13).Value, 宾馆) 0 And InStrB(Cells(x, 13).Value, B() = 0 ThenCells(x, 13).Value = Replace(Cells(x, 13).Value, (, B()End IfCells(x, 1

19、3).Value = Replace(Cells(x, 13).Value, MK, )If InStrB(Cells(x, 13).Value, 110(V1.0) 0 And InStrB(Cells(x, 13).Value, TM) = 0 ThenCells(x, 13).Value = Replace(Cells(x, 13).Value, 110(V1.0), 110(3.4)End IfIf InStrB(Cells(x, 13).Value, 110(V1.0) 0 And InStrB(Cells(x, 13).Value, LG) = 0 ThenCells(x, 13)

20、.Value = Replace(Cells(x, 13).Value, 110(V1.0), 110(3.5)End IfIf InStrB(Cells(x, 13).Value, 120(V1.0) 0 And InStrB(Cells(x, 13).Value, LG) = 0 ThenCells(x, 13).Value = Replace(Cells(x, 13).Value, 110(V1.0), 120(2.3)End IfCells(x, 13).Value = Replace(Cells(x, 13).Value, LG, )Cells(x, 13).Value = Repl

21、ace(Cells(x, 13).Value, TM, )If InStrB(Cells(x, 13).Value, 120(V1.0) 0 And InStrB(Cells(x, 13).Value, TM) = 0 ThenCells(x, 13).Value = Replace(Cells(x, 13).Value, 110(V1.0), 120(2.04)End IfIf InStrB(Cells(x, 13).Value, 宾馆) 0 And InStrB(Cells(x, 13).Value, B() = 0 And InStrB(Cells(x, 13).Value, 120E)

22、 = 0 ThenCells(x, 13).Value = Replace(Cells(x, 13).Value, (, B()End Ifx = x + 1Loop增加EXCEL表格 名字后面加0End SubSub Macro1()X = Sheets.CountA = 1Do While A = XY = 2 * A - 1Z = Sheets(Y).NameSheets.Add BEFORE:=Sheets(Y)Sheets(Y).Name = Z & 0A = A + 1LoopEnd SubSub Macro1()a = 1b = 1Do While b = Sheets.Coun

23、tSheets(b).SelectIf Right(Sheets(b).Name, 1) = 0 Thenb = b + 1ElseIf Cells(4, 2).Value Thenc = InStrB(Cells(4.2).Value, STB)d = Len(Cells(4, 2)Sheets(b).Name = Right(Cells(4, 2).Value, d - c - 6)b = b + 1ElseSheets(b).DeleteEnd IfEnd IfLoopx = Sheets.CountDo While a 0 ThenCells(16, 2).Value = 台（维护机）

24、a = a + 1Elsea = a + 1End IfEnd IfLoopEnd Sub如何操作Excel文件 全面控制 Excel 首先创建 Excel 对象，使用ComObj: Dim ExcelID as Excel.Application Set ExcelID as new Excel.Application 1) 显示当前窗口： ExcelID.Visible := True; 2) 更改 Excel 标题栏： ExcelID.Caption := 应用程序调用 Microsoft Excel ; 3) 添加新工作簿： ExcelID.WorkBooks.Add; 4) 打开已存

25、在的工作簿： ExcelID.WorkBooks.Open( C:ExcelDemo.xls ); 5) 设置第2个工作表为活动工作表： ExcelID.WorkSheets2.Activate; 或 ExcelID.WorkSheets Sheet2 .Activate; 6) 给单元格赋值： ExcelID.Cells1,4.Value := 第一行第四列 ; 7) 设置指定列的宽度（单位：字符个数），以第一列为例： ExcelID.ActiveSheet.Columns1.ColumnsWidth := 5; 8) 设置指定行的高度（单位：磅）（1磅0.035厘米），以第二行为例： ExcelID.ActiveSheet.Rows2.RowHeight := 1/0.035; / 1厘米 9) 在第8行之前插入分页符： ExcelID.WorkSheets