火鸟excel运用经验及公式整理.docx

火鸟excel运用经验及公式整理.docx

《火鸟excel运用经验及公式整理.docx》由会员分享，可在线阅读，更多相关《火鸟excel运用经验及公式整理.docx（20页珍藏版）》请在冰豆网上搜索。

火鸟excel运用经验及公式整理

火鸟excel运用经验及公式整理

这里整理的经验及公式都是个人的理解很少引用教材里的语言及教程，个人认为书本里的太全面不考虑实际运用的问题，这样很多东西都是实际中不常用或者根本不用的，这些学了也很快淡忘。

在实际工作中我们只取所需反复运用，这样才能实现熟练使用举一反三，从而提高工作效率的目的。

也就是说我的建议是不要全盘接收要先可需要的入手，逐步扩大，进而掌握excel这一工具。

常用快捷键

Ctrl+Z：

用于撤销、恢复上一步等操作。

在实际工作中录入数据错误之后有时会造成单元格大小或者格式也发生了变化，调整起来比较麻烦，倒不如按回车键确认之后用Ctrl+Z来撤销。

Ctrl+S用于保存文档，通常关闭文档的时候也会保存但是适时进行保存总是有备无患。

Alt+F4用于关闭文档，在懒得用鼠标点击的情况下也是挺方便的。

Ctrl+；是用于插入日期的，通常输入的并不是插入的时间但是差的一般也就是几个数字，这样插入现在的日期然后即刻进行修改，要比一点点的输入快得多了。

Ctrl+，是用于复制上一个单元格的，需要注意如果上一单元格是公式的话，那么这个操作是原公式复制相当于绝对引用，而不是相对引用。

如果上一单元格不是公式的话这个快捷键倒是很好用的，对于一些内容差不多的输入就可以复制之后直接进行修改很是方便。

Ctrl+空格调出输入法，或者关闭输入法。

Ctrl+A用于选取邻近连续单元格区域

Ctrl+C复制、Ctrl+X剪切、Ctrl+V粘贴，这些根据具体情况来选择使用。

Ctrl+Home回到工作薄的开始位置

Ctrl+End回到工作簿的末端（根据是否有内容来判断）

当一个工作簿里有很多工作表时可以用Ctrl+PageUp和Ctrl+PageDown来快速切换不同的工作表。

其他的快捷键真心用的不多，这里也就不罗列了。

主要部分还是放在公式的运用上。

常用的部分公式

1.日期公式

一个日期可以简单到只是一个标记时间的数字，也可以藉由日期提取所需的年、月、日用来进行判断分析统计数据，或者计算入职离职时间等等。

=NOW（）获取系统当前日期及时间

=TODAY（）获取当前系统日期

=WEEKDAY（）将日期变成星期几的数字

=TEXT（单元格,"aaaa"）变成星期几的文字

=TEXT（WEEKDAY（A1,2）,"[dbnum1]"）

//将星期数转换为大写数值

用于修饰及标注时间我是这么设置的，在一个单元格内输入一个开始日期，另外一个单元格输入截止日期，然后利用行号及text公式获取到这一时间段得日期显示为英文日期，然后显示出星期几。

在A2单元格输入一个日期2013-8-1

然后在A6单元格输入公式=SUM（$A$2,ROW（）-6）

//这么做的目的是将A2的日期变成数字+行号row（）取得当前行号返回值这里为6，然后减去6，计算结果还是A2的数字，设置改列为英文日期格式，这样得到“1-Aug”的2013-8-5的英文日期也就是8月1日。

该公式被下拉复制到31行后就能得到8月的所有的日期。

因为下面得行号变化就形成了A2+1或者A2+2…的效果。

=IF（TEXT（WEEKDAY（A6,2）,"[dbnum1]"）="七","日",TEXT（WEEKDAY（A6,2）,"[dbnum1]"））

这个公式的目的就是获取到星期的数字然后转化为大写数字，另外当发现“七”的时候替换成“日”。

因为总不会有人把周日叫星期七吧。

以上公式都是表格里复制出来的，只要按照单元格的位置使用即可看到效果。

=DAY（DATE（TEXT（B2,"YY"）,TEXT（B2,"mm"）,0））

用这个公式得到8月有31天，这个是利用了日期的溢出DAY（2013,9,0）实际上这个形式下得到的是8月的天数，正好利用了下一个月是2013-9-1这个B2单元格里的截止日期

=TEXT（MID（H1089,1,SEARCH（":

",H1089,1）-4）,"yyyy-mm-dd"）

有些时候系统导出来的日期是文本，表面上跟数字的日期没什么区别但是我们要提取其中的日期时就很麻烦，往往提出来的数据数以千计人工提取想都不用想了。

这样我们可以利用上面得公式来处理，在单元格中找到一个固定标识，这里我用的是“：

”，我发现这个标识之前的就是日期是我想要的数据，利用search来找到“：

”的位置，用mid提取“：

”位置数-4的数据，然后用text来转化为日期就实现了从文本中提出数字的日期的设想。

=TEXT（MID（H1089,1,SEARCH（":

",H1089,1）-4）,"mm"）

提取的是月份

=TEXT（MID（I1089,1,SEARCH（":

",I1089,1）-4）,"d"）

提取的是天

如果导出的日期就是数字的但是格式不同不是我们想要的也可以直接提取不去改变原数据，相对的要容易得多。

=TEXT（E122,"yyyy-mm-dd"）

提取日期年月日

=TEXT（E122,"mm"）

提取日期月

=TEXT（E122,"d"）

提取日期天

=IF（B23="","",TEXT（H196,"mm-dd-yyh:

mm"））

如果B23为空则空值，否则转换H196的值为时间格式"mm-dd-yyh:

mm"月－日－年－时间格式

有些时候我们知道一笔账务的挂账日期，需要计算一下账龄。

可以利用这个公式来完成

=IF（F2="","",IF（DATEDIF（F2,NOW（）,"y"）>0,DATEDIF（F2,NOW（）,"y"）&"年零",""））&IF（F2="","",IF（AND（DATEDIF（F2,NOW（）,"ym"）>0,DATEDIF（F2,NOW（）,"y"）>=0）,DATEDIF（F2,NOW（）,"ym"）&"月",IF（AND（DATEDIF（F2,NOW（）,"ym"）=0,DATEDIF（F2,NOW（）,"y"）>=0）,""）））&IF（F2="","",IF（AND（DATEDIF（F2,NOW（）,"ym"）>=0,DATEDIF（F2,NOW（）,"y"）>=0）,DATEDIF（F2,NOW（）,"md"）&"天",IF（DATEDIF（F2,NOW（）,"md"）=0,""）））

有点长是吧，如果保持足够的耐心把公式分解开来看还是可以看懂的。

公式里混杂了很多判断的内容在里面主要是为了显示结果的时候不要出现明显的0年0月之类的无用信息。

这样显示出来的结果符合我们说话的习惯，就直接显示出挂了几年几月几天或者直接显示出挂了几月几天，不要一味的0年0月10天等等可笑的结果。

公式的主体是DATEDIF函数，这个在excel的公式里是没有说明的，但是确实存在的。

使用时要求第一参数是个较小的日期，第二个参数是比较大的日期。

这里我第一个参数是挂账的日期的单元格，第二个参数是now（）也就是现在系统时间。

IF条件判断就是用于去掉不合理的显示结果，用“&”将计算结果连在一起。

同理还可以用于计算在职时间。

预计离职时间等等方面。

具体在涉及到身份证号码的利用时再详细讲解。

计算到职时间的公式

=INT（DATEDIF（F1,TODAY（）,"m"）/12）&"年"&MOD（DATEDIF（F1,TODAY（）,"m"）,12）&"月"

2.连接单元格的公式

="日期：

"&TEXT（A3,"yyyy-mm-dd"）"日期：

"为修饰文本，A3为单元格

简单的就用“&”进行连接即可。

=CONCATENATE（D1,E1,F1）

复杂点的就用这个，参数之间用“，”进行间隔

1.=CONCATENATE（G2,H2,I2,J2,K2）

//连接公式，把G2，H2，I2，J2，K2,合并

2.=TEXT（A1,"yyyy"）&"年"&TEXT（A1,"mm"）&"月"&TEXT（A1,"dd"）&"日"&"审计报告"

//格式化A1单元格（日期）显示为“2011年03月01日审计报告”

3.=IF（$A$1="","",VLOOKUP（$A$1,ZC!

$A$2:

$L$33,12,FALSE））

以日期为索引找到需要的数据

4.=IF（A2="","",CELL（"row",A2））

此公式得到A2的行号数字

用=IF（$A$1="","",VLOOKUP（$A$1,ZK!

$A$1:

$B$300,2,FALSE））配合得到该日期的位置

用=IF（$A$1="","",COUNTIF（ZK!

$A$2:

$A$330,$A$1））得到该日期的数量

=IF（ROW（）-25>$B$25,"",ROW（）-25&"."&VLOOKUP（$A$1,INDIRECT（"ZK!

$A"&SUM（$A$25+ROW（）-26）&":

$J$330"）,10,FALSE））

//INDIRECT（"ZK!

$A"&SUM（$A$25+ROW（）-26）&":

$J$330"）得到ZK!

表中该日期的位置得到一个区间

进而得到该数据的精确匹配值

3.条件求和

=SUMIF（$K$23:

$K$192,"散客",$E$23:

$E$192）

=SUMIF（找寻条件的区域,"条件值",取数值的区域）

4.统计一定范围内的某一值的数量：

=COUNTIF（$K$23:

$K$192,"散客"）

=COUNTIF（值的范围,"具体值"）

5.条件公式+取整公式

=IF（C7=0,"",ROUND（C9/C7,2））

=IF（如C7=0,则为空"",ROUND（对C9/C7取小数点后两位,2））

6.行号、列号公式

行号

=ROW（）-22

=ROW（）本行号-22从而得到另外的值，这个较常用于生成序号

列号

=COLUMN（）

这样A、B、C….列号就可以用形成1、2、3…数字加以利用

Row_num在单元格引用中使用的行号。

Column_num在单元格引用中使用的列标。

Abs_num指定返回的引用类型。

Abs_num返回的引用类型

1或省略绝对引用

2绝对行号，相对列标

3相对行号，绝对列标

4相对引用

返回的列号刚好是他对应的英文字母

=char（64+column（））

7.条件判断

=IF（E196=0,"",E196）

如果E196＝0那么为空，否则为E196

条件判断最简单的用法，通常用的时候都是很多条件嵌套在一起，完成一个复杂的判断需要写很长很长。

这里推荐使用excel2010版，因为这个版本可以在一个公式里嵌套64个判断，excel2003只能嵌套7个。

这样如果非要用2003版本来做复杂判断的话就只好将判断条件分开然后再用判断去判断这些分开的条件。

运用的时候要注意，判断的原则是由繁索到简单的顺序，不然先找到简单条件执行完之后之后的就不执行了。

具体还要根据实际情况。

条件成立也就是“true”，不成立就是“false”。

根据这些来设置需要的结果。

多重判断公式：

=IF（D23<=E23,"s",IF（E23=0,"m",IF（F23="团队","t",IF（F23="散客","h",IF（F23="长住","c"）））））

如果D23<=E23则为s；E23=0则为m，F23=团队则为t；F23=散客则为h；F23=长住则为c

复杂跨页判断公式：

=IF（ISNA（IF（Sheet1!

W1="","",VLOOKUP（Sheet1!

W1,黄!

$A$1:

$B$200,2,FALSE）））,"无",IF（Sheet1!

W1="","",VLOOKUP（Sheet1!

W1,黄!

$A$1:

$B$200,2,FALSE）））

VLOOKUP（Sheet1!

W1,黄!

$A$1:

$B$200,2,FALSE）为在黄!

$A$1:

$B$200（“黄”这个工作表的A1-B200中）找到Sheet1!

W1（Sheet1页的W1单元格）然后返回到该行的第2位置的值，对应为准确对应

ISNA（）为判定该值是否为＃ISNA（无数值，公式返回的错误信息）

8.查找引用

横向查找引用

=IF（B23="","",VLOOKUP（B23,$M$23:

$P$192,2,FALSE））

如果B23为空那么为空值，否则从$M$23:

$P$192这个范围中查找B23，并在这一行以找到的与B23相符的为起点自左到右数2个位置的值，FALSE意为条件为“精确对应“。

也有用true的代表近似对应，还有用0的情况，没仔细研究过，反正我需要的都是精确对应。

纵向查找引用

=IF（B23="","",HLOOKUP（B23,$M$23:

$P$192,2,FALSE））

如果B23为空那么为空值，否则从$M$23:

$P$192这个范围中查找B23，并在这一列以找到的与B23相符的为起点自上到下数2个位置的值，FALSE意为条件为“精确对应“。

实际运用中按行查找的情况比较多，偶尔也能遇到需要按列查找的情况。

=HLOOKUP（E68,G68:

$AB$238,239-ROW（）,FALSE）

在G68:

$AB$238区域内查G68值，显示该列的239减该行号ROW（）得到的位置值，FALSE意为精确位置。

#N/A为无此值的显示。

9.计算数值单元格的个数：

="TOTL="&COUNT（$M$23:

$M$192）&"间"

数值类型用count（）忽略0值，文字用counta（）

10.设置单元格格式公式

ISNA值为错误值#N/A（值不存在）。

设置条件格式时使用，例如：

使用条件格式，按下拉键设定为“公式”按下面方法

条件格式1在空格处填入=B68=""然后在下面选为白色或不作变动；

条件格式2在空格处填入=ISNA（F68）然后在下面选为蓝色

则体现为：

B68为空即为白色单元格，如为不存在该值的错误信息则显示为蓝色单元格

11.数据唯一性公式:

点“数据”－“有效性”－“自定义”－“公式”填入如下内容

=COUNTIF（A:

A,A1）=1然后填入适当的错误信息提示，把该单元格进行一下批量复制

这样一旦输入了重复数据就会弹出错误信息，阻止输入

12.数组公式:

{=SUM（IF（$I$7:

$I$20<=I7,$AZ$7:

$AZ$20））}

这个数组公式的意思是在$I$7:

$I$20这个范围内，符合条件<=I7的数据，在$AZ$7:

$AZ$20这个范围内的对应的数值的和。

{=SUM（IF（（$CS$7:

$CS$176="贵宾套房"）*（$CT$7:

$CT$176="双床间"）*（$CR$7:

$CR$176>0）*（$CX$7:

$CX$176="全价"）,$CW$7:

$CW$176））}

该公式为一个多重条件的数组公式，其意义为如果符合以下条件的话$CS$7:

$CS$176中为贵宾套房、$CT$7:

$CT$176中为双床间、$CR$7:

$CR$176大于0，$CX$7:

$CX$176为全价，那么计算$CW$7:

$CW$176中对应数据之和。

数组公式可以同时进行多个计算并返回一种或多种结果。

数组公式对两组或多组被称为数组参数的数值进行运算。

每个数组参数必须有相同数量的行和列。

除了不能用Ctrl+Shift+Enter生成公式外，创建数组公式的方法与创建其他公式的方法相同。

{=IF（OR（$A$1:

$B$10=D1）,"ok","no"）}

数组判断公式，$A$1:

$B$10区域内有等于D1的就判断为true，返回ok；false就返回no。

通常可以用于单据销号，这样就避免了使用vlookup公式比较繁琐的写法。

13.条件求和

=SUMIF（L122:

L140,"人民币",I122:

I140）

在L122:

L140的区域内查找符合“人民币”的值，然后在I122:

I140在这个范围内对应的数值进行求和。

不论写法还是原理都与数组公式中的求和公式相似。

14.统计公式

如果统计一列数字的数据的个数可以使用这个公式

=COUNT（B1:

B6）

非空数据的数量会被统计出来，需要注意的是0也是数据。

但是文字是不算数据的。

如果统计一列数据的个数可以使用这个公式

=COUNTA（B1:

B6）

与上个公式的区别在于多了一个A，只要是非空的都算数据。

如果统计一列数字数据中符合某一条件的数据的个数可以使用这个公式

=COUNTIF（B1:

B6,">0"）

如果统计两列或以上的数据且各自有不同的条件的数据数量可以使用这个公式

=SUMPRODUCT（（D1:

D4="长沙"）*（E1:

E4="党员"））

但是通常很少这种只要符合条件的个数的，实际应用中往往还有另外的数字列，而且一般都是要数字列的数值的和，这样用的较多的还是用数组公式，即便只要符合条件的个数，在数字列里填充数字1，然后出来的结果与上边的公式是一样的。

所以推荐还是这个公式

{=SUM（IF（（D1:

D4="长沙"）*（E1:

E4="党员"）,（F1:

F4）））}

15.用于单元格的公式

=LEN（A1）

A1中的字符数

=RIGHT（A1,2）

A1中右数两个字符

=LEFT（A1,2）

A1中左数两个字符

=RIGHT（A1,LEN（A1）-2）*1

A1中右数所含字符数减掉2个的文本然后乘1，乘1的目的是把文本数字变成数值方便用于计算。

=RIGHT（单元格，几位）*1是变成数字，可以这么理解。

=LEFT（A2,LEN（A2）-2）*1

A1中左数所含字符数减掉2个的文本然后乘1，乘1的目的是把文本数字变成数值方便用于计算。

=MID（J2,SEARCH（"c",J2,1）,9）

//查找c然后确定c在单元格中的位置，以这个位置开始取9个字符

假如A1中123456#1234

取#号左边的数据=MID（A1,1,SEARCHB（"#",A1,1）-1）

或者用=IF（A1="","",LEFT（A1,FIND（"#",SUBSTITUTE（A1,"","#",LEN（A1）-LEN（SUBSTITUTE（A1,"",））））-1））

取#号右边的数据=MID（A1,SEARCH（"#",A1,1）+1,LEN（A1）-SEARCH（"#",A1,1）+1）

或者用=IF（A1="","",RIGHT（A1,FIND（"#",SUBSTITUTE（A1,"","#",LEN（A1）-LEN（SUBSTITUTE（A1,"",））））-1））

=LEN（Sheet8!

A1）

用于计算单元格字节数包含空格

=SEARCHB（"<",B8,1）

用于从单元格第一个字节查找并显示"<"的位置数

//确定一定字节数用于控制数据是否有用

//确定第一个"<"在单元格中的位置为22，并以此作为定位文件夹名第一字节位置

//确定文件名第一个字节的固定位置为37，并以此来定位文件名第一字节位置

//IF函数的用法只是判断条件是否为TRUE或者FALSE以此来显示一个结果

=IF（ISNUMBER（SEARCHB（"