SurDA

为什么想再谈SUM函数咧？

/*===============================================================

excel boy() 17:18:01
我想请教个问题，在编辑负数的时候，不小心点到了别的单元格，发现编辑
负数的这个单元格出现了引用公式的形式，不知道这是怎么回事，能有办法取消么？
SurDA<i@surda.cn> 17:18:39
esc
excel boy() 17:19:42
我的意思是，以后再编辑负数的单元格的时候怎么避免这样的情况，
天佑汶川() 17:20:06
好像没法避免吧
excel boy() 17:20:15
再点击其他单元格的时候不会出现应用公式这种现象
天佑汶川() 17:20:40
编辑负数和编辑单元格计算公式应该都是一样的开始内容，本来就有可能出现
SurDA<i@surda.cn> 17:20:42
麻烦些 输入强制文本符'
excel boy() 17:20:43
是不是在“选项”中设置
天佑汶川() 17:21:03
如果要当数字计算呢
excel boy() 17:21:10
负数本身要是数字格式
excel boy() 17:21:20
因为要后续计算
SurDA<i@surda.cn> 17:21:41
计算用 - 号转换下就好

天佑汶川() 17:22:08
输入强制文本符'后还能直接计算吗？

================================================================*/

Read More...

COUNTIF,SUM,SUMIF，我把这三个函数称之为EXCEL统计"铁三角"。前面就SUM,SUMIF都做了相应介绍，今天就举例谈谈COUNTIF函数用法。

实例目录

一、求各种类型单元格的个数

二、求>,<,=某个值的单无格个数

三、等于或包含某N个特定字符的单元格个数

四、不连续区域求符合条件的单元格个数

五、两个条件求符合条件的单元格个数

六、动态区域求符合条件的单元格个数

七、用COUNTIF()排序

八、用COUNTIF()的三维引用取得不重复值

九、用COUNTIF()进行多表汇总统计

PS:由于网页不能直观展示实际效果，接下来就一、三、七等3个实例展开。其余请跳至文末下载Excel实例研究。

Read More...

上篇文章跟大家分享了SUM函数非一般的用法，本文我们来品味SUMIF的魅力。

SUMIF,根据指定条件对若干单元格求和。其函数原型：SUMIF(range,criteria,sum_range),参数：

range   为用于条件判断的单元格区域

criteria  为确定哪些单元格将被相加的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、"32"、">32"或"surda"

sum_range 求和的实际单元格区域

我们知道,SUMIF函数是一个很常用且有用的条件求和函数,但估计大家常用只是单条件求和.

Read More...

表a

如图所示：

1、G1单元格设置下拉按钮，用来选择输入1-12月份。（通过数据有效性解决即可）

2、G1单元格选择好具体的月份后，C列自动计算出当月任务指标数值，其中：月指标=全年指标 x 月指标完成率

3、问题计算的关键是如何根据G1选择的月份自动在C列计算出对应月份的指标完成率。

4、1-12月指标完成率计划要求：

表b

5、例如G1选择6月时，C3=40*12%，照此类推。问题关键是自动把完成率取出来，剩下的就是简单的换算即可。

函数实现方法：

• 可以直接用Hlookup等查找函数实现查找b表完成率，今天我们使用查找函数附带数组参数来实现，从而就不用增加辅助表（b表）。

1. Vlookup() :C3=B3*VLOOKUP($G$1,{1,4;2,2;3,9;4,8;5,7;6,12;7,6;8,7;9,10;10,7;11,10;12,8},2,0)*0.01  公式说明：“*0.01”解决换算问题（下同）；“{1,4;2,2;3,9;4,8;5,7;6,12;7,6;8,7;9,10;10,7;11,10;12,8}”此数组参数为定义了一个列数组，其数据格式等同于下图，相当于在该数据区域使用Vlookup函数查找。
2. Hlookup():C3=B3*HLOOKUP($G$1,{1,2,3,4,5,6,7,8,9,10,11,12;4,2,9,8,7,12,6,7,10,7,10,18},2,0)*0.01 公式说明：Hlookup()跟Vlookup()函数不同的是其执行查找的区域是行,因此查找区域的数组参数与上面不同，{1,2,3,4,5,6,7,8,9,10,11,12;4,2,9,8,7,12,6,7,10,7,10,18}，其数据格式等同于下图，相当于在该区域执行Hlookup()
3. Index() :C3=B3*INDEX({4,2,9,8,7,12,6,7,10,7,10,18},$G$1)*0.01公式说明:"INDEX({4,2,9,8,7,12,6,7,10,7,10,18},6）"返回数组参数“{4,2,9,8,7,12,6,7,10,7,10,18}”第六个值，也就是6月的指标完成比率。

PS:｛｝用来定义数组，$G$1为了填充公式而采取绝对应用格式。（测试平台 MS Excel2010)

我们在操作Excel时，可能需要了解上一次的数据更新时间。比如共享协同操作，我甚至需要知道哪条行数据更新时间。可能大家第一时间会想到利用NOW()函数取当前时间就OK了，诚然你对了第一步。NOW()是易失性函数，你会发现只要保存后重新打开，时间即刻更新为当前时间（或可按F9测试）。如何解决呢？先上效果图---->

如图在【B】那一列分别记录了C列输入1、2、3、4、5、6的时间。这里仍然是利用了人NOW()函数取了系统时间，但稍微加工了。请看公式

=IF(C2="","",IF(B2<>"",B2,TEXT(NOW(),"yyyy-mm-dd hh:mm:ss")))

公式中“B2”单元格重复引用了，默认情况下可能会报警。这里需要启用Excel迭代计算-设置-公式-启用迭代计算（EXCEL2010)。

赶快测试一下吧，看是不是可以自动记录行数据更新时间了。上例是单例数据更新，如是一行中有多列数据咧？

这里只需要引入COUNTBLANK（）函数计算行内是否存在空格，公式如下：

=IF(COUNTBLANK(C12:F12),"",IF(B12="",TEXT(NOW(),"yyyy-mm-dd hh:mm:ss"),B12))

公式中的单元引用视情况修改就可以了。

那要记录某单元格区域或是整个工作表的修改时间呢？方法同样：

公式如下：

F2=IF(COUNTBLANK(E2:F7),"",IF(F1="",TEXT(NOW(),"yyyy-mm-dd hh:mm:ss"),F1))

ps:此例有个缺憾，公式的执行条件是对监视单元格的“非空”判断，所以修改数据都需要一个“清空”操作。

下载此文实例，请点这里。

这一篇是扫盲用的，主要讲解了几个最常用的函数IF、AND、OR、COUNT、COUNTA、COUNTIF和SUM，会用的同学就可以跳过了。以后会逐渐讲解其他函数的应用。

1. 逻辑判断

逻辑判断所用的函数不多，IF、AND、OR三个就足以应付日常工作了。

IF函数可以用来转换值，如将1和0转换为OK和NG：

=IF(A1=1,"OK","NG")

与AND和OR组合使用可以判断多个条件，如判断是否是周末：

=IF(OR(WEEKDAY(A1)=0, WEEKDAY(A1)=6),"周末","工作日")

2. 统计数量的COUNT、COUNTA、COUNTIF

COUNT和COUNTA统计对象不同，COUNTA统计所有非空单元格的数量（包括出错的单元格）， COUNT仅统计看起来像数字的单元格。

COUNTIF则可以添加搜索条件，这个特性可以用来做统计。如

=COUNTIF(F:F,"OK")     统计F列中OK的个数
=COUNTIF(F:F,"NG")     统计F列中NG的个数

3. 求和的SUM

这个函数简单得不能再简单了：

=SUM(A10:A254)         对A10～A254的范围求和

没了。别看Excel函数成百上千，常用的就这几个。充其量再加上其他几个信息函数，如求日期的DATE、YEAR、MONTH、DAY、NOW、WEEKDAY，数值计算的FLOOR、INT、MOD、ROUND，字符串操作的CHAR、LEFT、RIGHT、MID（具体使用方法参见帮助），几乎可以应付全部的日常应用。

下面举几个例子来说明这些函数的应用。

1. 测试用例。一般测试用例的表格会是这样：

那么统计OK和NG的个数就分别用

=COUNTIF(E:E,"OK")     统计OK个数
=COUNTIF(E:E,"NG")     统计NG个数

如果测试用例分成好几个工作表，那么可以在最前面加一个统计用的工作表，并用SUM求出所有用例的状况。

2. 日历。这个日历是用在项目进度管理上的，格式类似于下面这种横向的日历。

可按以下格式输入：

然后隐藏掉第一行即可。

VLOOKUP用于查找信息，更确切地说，它用来“翻译”信息。比如员工名单上有员工号和姓名的对应关系，那么给出一个员工号的列表，就可以通过VLOOKUP将其“翻译”成姓名列表。先来看个例子：

这个表的$A$2:$B$8区域为参考数据（原始对应关系），C列为需要翻译的原始数据，D列为翻译结果。具体的结果大家可以实际放到Excel里面执行一下。

VLOOKUP有三个参数，分别如下：

VLOOKUP(原始数据, 参考数据, 搜索结果在参考数据中的列位置)

Excel会在参考数据的第一列中搜索原始数据（上例中，在$A$2:$B$8的第一列即$A$2:$A$8中搜索原始数据C2），找到后，返回第三个参数——列位置所对应的数据（上例中，在$A$2:$A$8中找到与C2相等的3之后，返回3所对应的第2列——韩宝驹，这个“第2列”即为第三个参数）。

那么找不到时怎么办？其实VLOOKUP还有第四个参数，值为TRUE或FALSE，默认为TRUE。取值TRUE时，VLOOKUP会返回不大于原始数据的最小值所对应的结果，例如=VLOOKUP(2.5,$A$2:$B$8,2,TRUE)会返回“朱聪” （朱聪的“2”为不大于2.5的最大值）。取值为FALSE时，VLOOKUP会返回#N/A。

通常在处理字符串时，大多情况下希望在找不到时返回错误或空串。这时可以为VLOOKUP加上第四个参数FALSE，再用ISNA函数将#N/A转换为空串。如：

[D2]=VLOOKUP(C2,$A$2:$B$8,2,FALSE)
[D3]=IF(ISNA(D2),"",D2)

EXCEL是很强大的数据分析工具，数据的来源一是可以从数据库或者是其他的数据格式（例如TXT）转换而来，以及用户手工输入。但像下表一堆原始数据，其展现方式事实对数据分析效果在不太直观。我想大家也经常需要通过一个杂乱的原始表里通过函数得出一个可以给领导直观阅读的报表吧，下面就简单介绍一下使用Office2010新增的Excel函数countifs()做出较有“阅感”的报表。先上效果图（如下）

----->表"Compay"存储原始数据

----->表"Countifs"生成的报表

首先了解一下countifs()函数功能：计算区域内符合多个条件的单元格的数量；

语法：COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

ps:详细说明请阅Excel 2010帮助文档（F1-搜索“countifs” ~_^)

以表"Countifs"中的"D4"单元格为例，所统计的注册数为同时满足条件：注册日期为【4月21日】+业务员姓名为【CC】单元格的个数

公式=COUNTIFS(Company!B:B,Countifs!$B4,Company!E:E,Countifs!D$2)

以表"Countifs"中的"E4"单元格为例，所统计的是为同时满足条件：注册日期为【4月21日】+业务员姓名为【CC】+是否通过审核为【是】单元格的个数

公式=COUNTIFS(Company!$B:$B,Countifs!$B4,Company!$E:$E,Countifs!D$2,Company!$D:$D,Countifs!E$3)

确定这两个单元格的公式，先向右再向下填充公式就OK了。以后只要表"Compay"数据有更新，此报表就会自动更新了。（实例只截取了部分日期，如需要再往下增加就好，点些下载本文EXCEL实例）当然本文重点是介绍函数countifs()的应用，事实上此实例还运用了如：countif()、choose()、weekday()、rank()等函数。

读者可以举一反三，数据演示和截图平台为MS-Excel 2010版本

先看上表数据系某‘office老手’整理的单位联系簿，粗一看就不符合EXCEL整规则。大家都知道，姓名、电话两项数据应该分别在A、B两列分别列示，以便方便使用EXCEL的数据分析工具和查找工具等，这样的数据是不能在EXCEL中进行分析的。在实际的工作中，比如ERP实施过程中，非财务人员报送数据的时候，经常会遇到这种哭笑不得的数据。那么我们第一想法就是将姓名、电话号码分开。用什么方法分开成两列的数据？

大家可能首先会想到EXCEL数据-分列功能，但分列功能要么按分隔符、要么按困定宽度进行截取，显然在这里是不方便的。

下面将介绍如何运用公式做到姓名与手机号分离，效果图下：

大致思路是运用LEFT()、MID()、RIGHT()等取姓名、手机号，用函数截取都存在一个问题---就是截取多长。拿【吴维18702107910】来说，姓名=LEFT(A3,2）。但手机号码的起始位也不确定，有时第三位、有时第四位甚至第五位，如何判定截取的位数呢？

这里可以用一个组合函数定位数字开始位数=MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A3&"0123456789"))  PS:MIN()　、FIND()使用方法大家可以打开EXCEL-按F1-搜索。

确定了就容易解决了。

截取姓名公式=LEFT(A3,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A3&"0123456789"))-1)

截取手机公式=RIGHT(A3,LEN(A3)-MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A3&"0123456789"))+1)

或者=MID(A3,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A3&"0123456789")),LEN(A3))

另外：提供一种更简单的公式

截取姓名公式=LEFT(A3,LENB(A3)-LEN(A3))

截取手机公式=RIGHT(A3,2*LEN(A3)-LENB(A3))

【附实例下载】

上周三有群友询问如何使用 EXCLE VLOOKUP比对查寻两表中符合两条件的值。都知道vlookup一般只能做单条件查寻，具体用法=vlookup(比对值，查寻区域，所取值列，【0，1】）。

下面就罗列几种运用EXCEL函数返回符合两个条件的值。 Read More...