学而不思则罔，思而不学则殆。

lrlxxqxa

1、返回包含值12的单元格数量

=COUNTIF（DATA,12）

2、返回包含负值的单元格数量

=COUNTIF（DATA，"<0"）

3、返回不等于0的单元格数量

=COUNTIF（DATA，"<>0"）

4、返回大于5的单元格数量

=COUNTIF（DATA，">5"）

5、返回等于单元格A1中内容的单元格数量

=COUNTIF（DATA，A1）

6、返回大于单元格A1中内容的单元格数量

=COUNTIF（DATA，“>”&A1）

7、返回包含文本内容的单元格数量

=COUNTIF（DATA，“*”）

8、返回包含三个字符内容的单元格数量

=COUNITF（DATA，“???”）

9、返回包含单词"GOOD"(不分大小写)内容的单元格数量

=COUNTIF（DATA，“GOOD”）

10、返回在文本中任何位置包含单词"GOOD"字符内容的单元格数量

=COUNTIF（DATA，“*GOOD*”）

11、返回包含以单词"AB"(不分大小写)开头内容的单元格数量

=COUNTIF（DATA，“AB*”）

12、返回包含当前日期的单元格数量

=COUNTIF（DATA，TODAY()）

13、返回大于平均值的单元格数量

=COUNTIF（DATA，">"&AVERAGE(DATA))

14、返回平均值上面超过三个标准误差的值的单元格数量

=COUNTIF（DATA，“>"&AVERAGE(DATA)+STDEV(DATA)*3)

15、返回包含值为3或-3的单元格数量

=COUNTIF（DATA，3）+COUNIF（DATA，-3）

16、返回包含值;逻辑值为TRUE的单元格数量

=COUNTIF（DATA，TRUE）

dawin

嘿嘿，有看完一遍，发现个小错误。
一、公式概述：
里面加减乘除的除是/不是\。
接着看。

dawin

虽然也能看出and、or与*、+的一些区别，不过在具体的函数应用的时候还是会晕。

dawin

3、SUBTOTAL 函数适用于数据列或垂直区域。不适用于数据行或水平区域。
统计垂直区域的时候，隐藏行会对109有影响；
统计水平区域的时候，隐藏列不对109有影响。

这里是不是写反了啊？ 应该垂直区域没影响，水平区域有影响吧。
4以后得东西，稍微高点的理解不同了，虽然知道数组很强大，不过也太复杂了。

dawin

此小结可是得到两个红花的呀，从成果上看也花费了很多心思。。赞一个。

lrlxxqxa

原帖由 dawin 于 2010-8-2 20:32 发表
嘿嘿，有看完一遍，发现个小错误。
一、公式概述：
里面加减乘除的除是/不是\。
接着看。

谢谢指正，已更改错误。

lrlxxqxa

原帖由 dawin 于 2010-8-2 22:23 发表
3、SUBTOTAL 函数适用于数据列或垂直区域。不适用于数据行或水平区域。
统计垂直区域的时候，隐藏行会对109有影响；
统计水平区域的时候，隐藏列不对109有影响。

这里是不是写反了啊？ 应该垂直区域没影响，水平 ...

关于SUBTOTAL那里，没有写反，你再测试一下，很简单，也挺好玩。

lrlxxqxa

总结一下常用的27种文本函数，不在正文展开了，详见附件吧



文本函数.rar (24 KB, 下载次数: 45)

2010-8-3 23:36 上传

点击文件名下载附件

值得提的几点：
1、MIDB,LEFTB等截取中文时，如果参数是奇数，那么会产生空格；
2、SEARCH 和 SEARCHB 在查找文本时不区分大小写，但支持通配符，包括问号 (?)和星号 (*)。问号可匹配任意的单个字符，星号可匹配任意一串字符。如果要查找真正的问号或星号，请在该字符前键入波形符 (~)；
3、 FIND 和 FINDB 区分大小写，不支持通配符；

文本函数的六种应用：

文本函数6种应用.rar (30.41 KB, 下载次数: 50)

2010-8-3 23:36 上传

点击文件名下载附件

包括：
1、提取超过15位数字；
2、判断是否含有数字；
3、提取重复数字、提取重复数字个数；
4、按需求提取特定数字；
5、判断首个文字出现的位置（忽略全角字符）；
6、提取缺失的数字。

扩展说一下PHONETIC：

PHONETIC函数能够将除纯数据(数字、日期、时间)、公式结果（包括错误信息）外的所有字符进行连接。连接顺序：按先行后列，从左向右，由上到下的顺序连接。
PHONETIC 函数新解 http://www.exceltip.net/thread-8041-1-1-25594.html
支持定义，支持联合区域（并联、串联），最棒的是她的参数支持引用，这个reference可了不起，这就意味着她能接受从其他函数比如index，indirect，offset等传过来的引用继续处理，极大地增强了其延展性。

关于TEXT

你也许未曾见过这样的Text函数用法-chrisfang
http://www.exceltip.net/thread-4355-1-1-25594.html

变化多端的TEXT函数应用
http://www.exceltip.net/thread-8286-1-1-25594.html

函数text提高新知道
http://www.exceltip.net/thread-392-1-1-25594.html

关于EVALUATE

EVALUATE宏表函数妙用 http://www.exceltip.net/thread-4142-1-1-25594.html

http://club.excelhome.net/viewth ... mp;page=1#pid581818

关于REPT的一个小技巧

1. =TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",LEN($A2))),LEN($A2)*(COLUMN(A:A)-1)+1,LEN($A2)))

复制代码

【公式解析系列】之数据按分隔符“分列”函数解法 http://www.exceltip.net/thread-2188-1-1-25594.html

关于身份证号

15位：
1-6位代表地区
7-12位代表出生日期
13-14位是一个序列号
15位代表性别 单是男，双是女

18位：
1-6位代表地区
7-14位代表出生日期
15-16位是一个序列号
17位代表性别 单是男，双是女
18位是一个校验位

提取出生日期：=MID(A2,7,LEN(A2)/2.2)
提取性别：=RIGHT(LEFT(A2,17))
提取地区需下面的区域代码配合查找：

行政区划代码:http://www.stats.gov.cn/tjbz/xzqhdm/t20080215_402462675.htm


应用（详见附件吧，不多说）：

分部门核算明细信息.rar (45.96 KB, 下载次数: 31)

2010-8-3 23:36 上传

点击文件名下载附件

[ 本帖最后由 lrlxxqxa 于 2010-8-3 23:36 编辑 ]

sanim

拜雨为师学习,
追随雨中贴,漫步雨中路,学习雨中识,探寻雨中迷!!!!!!

谢谢,老师的好贴,今夜太晚了,没有看完老师之精作,寝不能寐,唉明天继续.....................................................吧!

lrlxxqxa

如何利用excel的规划求解解决货郎担问题？回答这个分三步走：

    1、什么是货郎担问题？
    2、什么是规划求解？
    3、如何解决？

1、什么是货郎担问题？

    货郎担问题是运筹学中一个古老而著名的问题，有重要的研究和使用价值，货郎担问题是指货郎从一个城市出发，经过其他所有城市，并且一个城市只能经过一次，最后回到出发点，求解货郎在城市间销售的最短回路问题。

    货郎担问题又称为旅行商问题，即TSP问题（Traveling Salesman Problem）是数学领域中著名问题之一。假设有一个旅行商人要拜访n个城市，他必须选择所要走的路径，路经的限制是每个城市只能拜访一次，而且最后要回到原来出发的城市。路径的选择目标是要求得的路径路程为所有路径之中的最小值。

       TSP问题是一个组合优化问题。因此，任何能使该问题的求解得以简化的方法，都将受到高度的评价和关注。找出货郎问题最优解的算法可以通过完全枚举，即通过完全枚举城市集合的全排列，计算出每种排列相应销售回路的长度，从中找出最短的回路，这种完全枚举算法的时间复杂性函数是城市N的指数形式。还有贪心算法、动态规划、回溯法、分支定界法等，条条大路通罗马。

    下文要阐述的是利用excel的规划求解功能如何解决此问题。

2、什么是规划求解？

     规划求解是在一定的限制条件下，利用科学方法进行运算，使对前景的规划达到最优的方法，他是现代管理科学的一种重要手段，是运筹学的一个分支。利用规划求解，可以解决产品组合问题、配料问题、下料问题、物资调运问题、任务分配问题、投资效益问题、合理布局问题等。

    线性规划模型由3个基本部分组成：

决策变量（variable）
目标函数（objective）
约束条件（constraint）

    单纯的概念太抽象，下面我结合一个实例来说明

3、货郎担问题如何利用规划求解？

（实例）某货郎要从A城市分别去B、C、D、E城市卖货，最后再回到A城市，路线要形成一个封闭回路，如何才能使路线最短？

步骤1：设计电子表格

表格中的999代表无限远（即某城市作为出发地就不能作为到达地）

  

使用Excel求解线性规划问题时，电子表格是输入和输出的载体，因此设计良好的布局，更加易于阅读。本例的电子表格设计布局及公式如下图所示：

C12：G16区域的每个单元格代表每两个城市之间的路程长度，用二进制的1表示最短路程；
某个出发地只对应一个到达地,所以C17:G17区域的每个单元格最大为1；
某个到达地只对应一个出发地，所以H12：H16区域的每个单元格最大为1；





步骤2：加载宏安装规划求解；

2003版本：
单击菜单“工具”--“加载宏”，出现“加载宏”对话框，如下图所示。选择“规划求解加载项”，单击“确定”。



2007版本：
单击左上角Office按钮--》excel选项--》加载项--》规划求解加载项--》转到--》勾选“规划求解加载项”--》确定。

然后在菜单的数据--》分析--》规划求解



步骤3：将设计表格的各个单元格与规划求解的三个组成部分对号入座；

1： 决策变量（variable）    C12:G16
2： 目标函数（objective）  I17
3： 约束条件（constraint）C17:G17,H12:H16

步骤4：应用规划求解；

单击工具--规划求解--设计相应的参数：



选项可以根据具体需求来设置。这里我勾选“采用线性模型”和“假定非负”，其余保持默认；



步骤5：求解；

     设置好参数后，单击“规划求解参数”对话框中的“求解”按钮；单击“确定”可以保存解决方案。



     如果问题没有可行解，规划求解将会显示明确的信息“规划求解找不到有用的解”。如果最优目标值超出界限，规划求解将会显示不太明确的信息“设置目标单元格的值未收敛”。这些情况都表明模型构造的公式有错误。

     附上操作动画及实例以帮助理解。

2003版本操作：



2007版本操作：



        最后需要说明的是，对于用规划求解出来的最优方案，并不一定是唯一的，但差异仅存在于路线选择，最终的最优路线长度一定是一致的（假设最优路线长度为m，m1=m2）；

     而对于excel给出的最优方案m，我们要进一步分析是不是符合实际要求，比如是不是一个封闭回路？

     如果出现分支（2各回路），一般的方法是对较短回路进行条件限制，使其并入较长的回路中。这样出现的结果（n）才是满足实际需要的最短路程，但这个值n一定是大于m的。

[ 本帖最后由 lrlxxqxa 于 2010-8-8 18:57 编辑 ]