EXEL常用函数.docx

EXEL常用函数.docx

《EXEL常用函数.docx》由会员分享，可在线阅读，更多相关《EXEL常用函数.docx（18页珍藏版）》请在冰豆网上搜索。

EXEL常用函数

常用函数：

1.HEX2DEC将十六进制数转换为十进制数

2.DEC2HEX将十六进制数转换为十进制数

3.CELL返回某一引用区域的左上角单元格的格式、位置或内容等信息

如：

=CELL（"contents",B3）结果：

单元格B3上的内容

4.IF

5.COUNTIF计算区域中满足给定条件的单元格的个数

6.INDEX返回表或区域中的值或对值的引用。

INDEX函数有两种形式：

数组（数组:

用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。

数组区域共用一个公式；数组常量是用作参数的一组常量。

）形式和引用形式

7.MATCH返回在指定方式下与指定数值匹配的数组

8.VLOOKUP在表格数组的首列查找值，并由此返回表格数组当前行中其他列的值

9.HLOOKUP在表格数组的首行查找值，并由此返回表格数组当前行中其他行的值

10.ROW返回引用的行号

11.COLUMN返回一引用的列号

12.COLUMNS返回一引用或者数组的列数

13.N转化为数值的值

14.LEFT后

15.RIGHT

16.TEXT数值转化为文本参数不能有*如：

=TEXT（1,"0000"）结果0001

17.VALUE文本转化数值

18.EXACT完全匹配区分大小写

19.TURNC将数字的小数部分截去，返回整数

20.MOD余数MUD（A/B）

21.SIGN返回数字符号正数为10为0负数=-1

22.=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.Name

tRow=ThisWorkbook.Sheets

（1）.Range（"a65536"）.End（xlUp）.Row+1'从当前文档的sheet1的A65536单元格向上移动，定位到第一个没有数据的单元格，并将此行的行号赋予tRow

vbCrLf换行命令VB

xls.DisplayAlerts=False

23.SUBSTITUTE替换函数

可以使用ROUND（四舍五入）、ROUNDUP（只入不舍）和ROUNDDOWN（只舍不入）这3个函数来实现您的需求。

SubMyInput（）

WithWorkbooks（"Book1"）.Worksheets（"Sheet1"）.Cells（1,1）

.Formula="=SQRT（50）"

With.Font

.Name="Arial"

.Bold=True

.Size=8

EndWith

EndWith

EndSub

24.

应用示例

=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（A2>0,TRUNC（（RIGHT（A2,4）-8540）/10）,""）

CELL

全部显示

全部隐藏

返回某一引用区域的左上角单元格的格式、位置或内容等信息。

语法

CELL（info_type,reference）

Info_type    为一个文本值，指定所需要的单元格信息的类型。

下面列出info_type的可能值及相应的结果。

Info_type

返回

"address"

引用中第一个单元格的引用，文本类型。

"col"

引用中单元格的列标。

"color"

如果单元格中的负值以不同颜色显示，则为1，否则返回0。

"contents"

引用中左上角单元格的值：

不是公式。

"filename"

包含引用的文件名（包括全部路径），文本类型。

如果包含目标引用的工作表尚未保存，则返回空文本（""）。

"format"

与单元格中不同的数字格式相对应的文本值。

下表列出不同格式的文本值。

如果单元格中负值以不同颜色显示，则在返回的文本值的结尾处加“-”；如果单元格中为正值或所有单元格均加括号，则在文本值的结尾处返回“（）”。

"parentheses"

如果单元格中为正值或全部单元格均加括号，则为1，否则返回0。

"prefix"

与单元格中不同的“标志前缀”相对应的文本值。

如果单元格文本左对齐，则返回单引号（'）；如果单元格文本右对齐，则返回双引号（"）；如果单元格文本居中，则返回插入字符（^）；如果单元格文本两端对齐，则返回反斜线（\）；如果是其他情况，则返回空文本（""）。

"protect"

如果单元格没有锁定，则为0；如果单元格锁定，则为1。

"row"

引用中单元格的行号。

"type"

与单元格中的数据类型相对应的文本值。

如果单元格为空，则返回“b”。

如果单元格包含文本常量，则返回“l”；如果单元格包含其他内容，则返回“v”。

"width"

取整后的单元格的列宽。

列宽以默认字号的一个字符的宽度为单位。

Reference    表示要获取其有关信息的单元格。

如果忽略，则在info_type中所指定的信息将返回给最后更改的单元格。

下表描述info_type为“format”，以及引用为用内置数字格式设置的单元格时，函数CELL返回的文本值。

如果MicrosoftExcel的格式为

CELL返回值

常规

"G"

0

"F0"

#,##0

",0"

0.00

"F2"

#,##0.00

",2"

$#,##0_）;（$#,##0）

"C0"

$#,##0_）;[Red]（$#,##0）

"C0-"

$#,##0.00_）;（$#,##0.00）

"C2"

$#,##0.00_）;[Red]（$#,##0.00）

"C2-"

0%

"P0"

0.00%

"P2"

0.00E+00

"S2"

#?

/?

或#?

?

/?

?

"G"

yy-m-d或yy-m-dh:

mm或dd-mm-yy

"D4"

d-mmm-yy或dd-mmm-yy

"D1"

d-mmm或dd-mmm

"D2"

mmm-yy

"D3"

dd-mm

"D5"

h:

mmAM/PM

"D7"

h:

mm:

ssAM/PM

"D6"

h:

mm

"D9"

h:

mm:

ss

"D8"

如果CELL公式中的info_type参数为“format”，而且以后又用自定义格式设置了单元格，则必须重新计算工作表以更新CELL公式。

说明

函数CELL用于与其他电子表格程序兼容。

示例

如果您将示例复制到空白工作表中，可能会更易于理解该示例。

操作方法

1.创建空白工作簿或工作表。

2.请在“帮助”主题中选取示例。

不要选取行或列标题。

从帮助中选取示例。

3.按Ctrl+C。

4.在工作表中，选中单元格A1，再按Ctrl+V。

5.若要在查看结果和查看返回结果的公式之间切换，请按Ctrl+`（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。

1

2

3

A

数据

5-Mar

TOTAL

公式

说明（结果）

=CELL（"row",A20）

单元格A20的行号（20）

=CELL（"format",A2）

第一个字符串的格式代码（D2,请参见上面的信息）

=CELL（"contents",A3）

单元格A3的内容（TOTAL）

ASCII字符集

按ALT键输入数字

如：

按ALT键输入176得到度数的符号

十进制

字符

十进制

字符

128

Ç

192

└

129

ü

193

┴

130

é

194

┬

131

â

195

├

132

ä

196

─

133

à

197

┼

134

å

198

╞

135

ç

199

╟

136

ê

200

╚

137

ë

201

╔

138

è

202

╩

139

ï

203

╦

140

î

204

╠

141

ì

205

═

142

Ä

206

╬

143

Å

207

╧

144

É

208

╨

145

æ

209

╤

146

Æ

210

╥

147

ô

211

╙

148

ö

212

Ô

149

ò

213

╒

150

û

214

╓

151

ù

215

╫

152

ÿ

216

╪

153

Ö

217

┘

154

Ü

218

┌

155

¢

219

█

156

£

220

▄

157

¥

221

▌

158

₧

222

▐

159

ƒ

223

▀

160

á

224

α

161

í

225

ß

162

ó

226

Γ

163

ú

227

π

164

ñ

228

Σ

165

Ñ

229

σ

166

ª

230

µ

167

º

231

τ

168

¿

232

Φ

169

⌐

233

Θ

170

¬

234

Ω

171

½

235

δ

172

¼

236

∞

173

¡

237

φ

174

«

238

ε

175

»

239

∩

176

░

240

≡

177

▒

241

±

178

▓

242

≥

179

│

243

≤

180

┤

244

⌠

181

╡

245

⌡

182

╢

246

÷

183

╖

247

≈

184

╕

248

≈

185

╣

249

∙

186

║

250

·

187

╗

251

√

188

╝

252

ⁿ

189

╜

253

²

190

╛

254

■

191

┐

255

SUBSTITUTE

全部显示

全部隐藏

在文本字符串中用new_text替代old_text。

如果需要在某一文本字符串中替换指定的文本，请使用函数SUBSTITUTE；如果需要在某一文本字符串中替换指定位置处的任意文本，请使用函数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自动删除重复项目VB函数

Sub机型分类（）

x=ActiveCell.Row

y=ActiveCell.Column

DoWhileCells（x,y）.Value<>""

x=x+1

z=x+1

DoWhileCells（z,y）.Value<>""

z=z+1

If（Cells（x,y）.Value=Cells（z,y）.Value）ThenCells（z,y）.EntireRow.Delete

Loop

Loop

EndSub

Sub地区软件（）

Cells（1,13）.Value="地区软件"

x=2

DoWhileCells（x,2）.Value<>""

Cells（x,13）.Value=Cells（x,2）.Value

Cells（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）.Value,"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（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=Replace（Cells（x,13）.Value,"IPANEL2.0",""）

Cells（x,13）.Value=Replace（Cells（x,13）.Value,"高频头",""）

Cells（x,13）.Value=Replace（Cells（x,13）.Value,"华数专用遥控器",""）

IfInStrB（Cells（x,13）.Value,"宾馆"）>0AndInStrB（Cells（x,13）.Value,"B（"）=0Then

Cells（x,13）.Value=Replace（Cells（x,13）.Value,"（","B（"）

EndIf

Cells（x,13）.Value=Replace（Cells（x,13）.Value,"MK",""）

IfInStrB（Cells（x,13）.Value,"110（V1.0）"）>0AndInStrB（Cells（x,13）.Value,"TM"）=0Then

Cells（x,13）.Value=Replace（Cells（x,13）.Value,"110（V1.0）","110（3.4）"）

EndIf

IfInStrB（Cells（x,13）.Value,"110（V1.0）"）>0AndInStrB（Cells（x,13）.Value,"LG"）=0Then

Cells（x,13）.Value=Replace（Cells（x,13）.Value,"110（V1.0）","110（3.5）"）

EndIf

IfInStrB（Cells（x,13）.Value,"120（V1.0）"）>0AndInStrB（Cells（x,13）.Value,"LG"）=0Then

Cells（x,13）.Value=Replace（Cells（x,13）.Value,"110（V1.0）","120（2.3）"）

EndIf

Cells（x,13）.Value=Replace（Cells（x,13）.Value,"LG",""）

Cells（x,13）.Value=Replace（Cells（x,13）.Value,"TM",""）

IfInStrB（Cells（x,13）.Value,"120（V1.0）"）>0AndInStrB（Cells（x,13）.Value,"TM"）=0Then

Cells（x,13）.Value=Replace（Cells（x,13）.Value,"110（V1.0）","120（2.04）"）

EndIf

IfInStrB（Cells（x,13）.Value,"宾馆"）>0AndInStrB（Cells（x,13）.Value,"B（"）=0AndInStrB（Cells（x,13）.Value,"120E"）=0Then

Cells（x,13）.Value=Replace（Cells（x,13）.Value,"（","B（"）

EndIf

x=x+1

Loop

增加EXCEL表格名字后面加0

EndSub

SubMacro1（）

X=Sheets.Count

A=1

DoWhileA<=X

Y=2*A-1

Z=Sheets（Y）.Name

Sheets.AddBEFORE:

=Sheets（Y）

Sheets（Y）.Name=Z&"0"

A=A+1

Loop

EndSub

SubMacro1（）

a=1

b=1

DoWhileb<=Sheets.Count

Sheets（b）.Select

IfRight（Sheets（b）.Name,1）="0"Then

b=b+1

Else

IfCells（4,2）.Value<>""Then

c=InStrB（Cells（4.2）.Value,"STB"）

d=Len（Cells（4,2））

Sheets（b）.Name=Right（Cells（4,2）.Value,d-c-6）

b=b+1

Else

Sheets（b）.Delete

EndIf

EndIf

Loop

x=Sheets.Count

DoWhilea<=x

IfSheets（a）.Name=Sheets（a+1）.Name&"0"Then

a=a+2

Else

Sheets（2*a-1）.Select

z=Sheets（a*2-1）.Name

Sheets（a*2-1）.Copybefore:

=Sheets（a*2-1）

Sheets（a*2-1）.Name=z&"0"

Sheets（2*a-1）.Select

IfInStrB（Cells（16,2）,Value,"台"）>0Then

Cells（16,2）.Value="台（维护机）"

a=a+1

Else

a=a+1

EndIf

EndIf

Loop

EndSub

如何操作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） 打开已存在的工作簿：

  ExcelID.WorkBooks.Open（ 'C:

\Excel\Demo.xls' ）;

5） 设置第2个工作表为活动工作表：

  ExcelID.WorkSheets[2].Activate;  

 或 ExcelID.WorkSheets[ 'Sheet2' ].Activate;

6） 给单元格赋值：

 ExcelID.Cells[1,4].Value :

= '第一行第四列';

7） 设置指定列的宽度（单位：

字符个数），以第一列为例：

 ExcelID.ActiveSheet.Columns[1].ColumnsWidth :

= 5;

8） 设置指定行的高度（单位：

磅）（1磅＝0.035厘米），以第二行为例：

  ExcelID.ActiveSheet.Rows[2].RowHeight :

= 1/0.035; // 1厘米

9） 在第8行之前插入分页符：

  ExcelID.WorkSheets