Excel高级办公应用案例与分析

○ 前言

最佳实践说明下：

1） 以下公式如果不会写，可以先在excel普通单元格内，写好、测试好，再抄到对应的操作步骤上的公式输入框中去，如下。

因为这样测试公式，有智能提示，也有直接的数据，方便测试。

 

 

 

更新时间：23.9.9 晚

 

 

一、查找

1.1 查找某个值是否在某一列内

VLOOKUP（查找目标，查找范围，返回值的列数N，[精确,false]/[模糊查找,true])

在所指定的查找范围的第一列中，查找目标文本，如果找到，则返回指定查找范围的第N列(1~N,Excel没有第0列，搞开发的注意了)。

说明：

查找的目标，指代关键字列的值，如XX名。查找范围，指代程序在哪个范围内搜索XX名。最后一个参数是决定函数精确和模糊查找的关键。精确即完全一样，模糊即包含的意思。第4个参数如果指定值是0或FALSE就表示精确查找，而值为1 或TRUE时则表示模糊。这里特别提醒大家切记切记，在使用VLOOKUP时千万不要把这个参数给漏掉了，如果缺少这个参数默为值为模糊查找，我们就无法精确查找到结果了。 实际应用的案例如下。

=IF(ISERROR(VLOOKUP(C2,XX表INFO!$A$2:$BR$5,36,FALSE)),"",VLOOKUP(C2,XX表INFO!$A$2:$BR$5,36,FALSE))

 

 

1.2 统计个数

（1）非空单元格, COUNTA，如 COUNTA(B4:B13)

（2）统计空的单元格个数 COUNTIF， 如 COUNTIF(B4:B13,"")

（3）数字单元格个数, COUNT， 如COUNT(B4:B13)

 

（4）统计选定区域去除重复后的个数。

 =SUMPRODUCT(1/COUNTIF(G348:G356,G348:G356))

       

 

（5）统计包含某个字符的单元格个数，如

=count(A2:A453, "*sm*")

 * 表示0个或多个，上面的"*sm*"中，sm的前后都要有才可以，找到包含sm字符的选定区域的单个个数。

 

 

 

 

1.3 分类汇总数据透视

 

 

 

二、标记与验证

2.1 使用条件格式，标记行、单元格不同的颜色等格式

（1）符合条件的行、单元格格式的标记

编辑规则的状态

  

表格选定区域的一行单元格，是如上图中的“$G2”形式进行，标注一个个单元格是"G2"的形式。

或者这种规则的形式

 

 

 （2）重复值的标记

格式规则

下面，来一个完整的标记选定范围的一行单元格的示例。也可下载文件查看条件格式.rar。

 

 

2.2 数据验证

2.3 下拉列表方式的数据验证

功能位置为：数据选项卡--数据验证--数据验证

在弹出的数据验证对话框中，选择“设置”选项卡，在验证条件中的允许项下，选中序列，然后在来源处框选出数据序列。这样便可在设置的单元格上实现下拉选择的操作。

 

三、数值计算

3.1 测量度分秒、十进制等格式的相互转换

转换的Excel文件见此处下载  测量坐标转换公式.rar。

 

备注：

（1）上图中的dd.mmssss形式的60进制的秒采用的是一种方式，如，F5=B5*10000-(INT(B5*100)*100)。

此外，还可以采用如下的方式：=MID(C2,7,2)&"."&MID(C2,9,8)

 

 

 

 

四、字符串计算

4.1截取、组合字符串

两个字符串相加

= B2&“XXXX”

4.2 字符串替换

=replace(目前的文本，想从第几个数字开始改，共需要改几个字，用什么改）
=substitute(目前的文本，需要替换的文本，新的文本（替换后））

4.3 数字转为文本

整数

将23转换成0023，可以使用公式：=Text (A1, “0000”)

小数

将23转换成23.000，可以使用公式：=Text (A1, “0.000”)

 

五、快速编辑处理

5.1 多个单元格拆分后填充成拆分前的内容

选中要拆分的单元格，然后在菜单元上点击【合并后居中】；

按F5键，弹出 定位 对话框，然后点击该 对话框上的【定位条件】按钮，选择空值选项，确定后将选中所有空值单元格；

在光标出现的地方输入=后，再选下要等于哪个单元格的值。如图，输入=再选中A2单元格（拆分后的第一个有内容的单元格）；

输入完之后，同时按下Ctrl+Enter键，拆分的单元格内就填充成拆分前的内容了。

 

未完待续...

 

 

 

参考资料

VLOOKUP函数的使用方法（入门级）

excel表格中用指定字符替代另一字符串中的指定字符串，Alina_028。

excel拆分单元格怎样使内容相同，cherish_liyh