EXCEL学习记录集合

52UEXCEL

把文本转换成数值



MID($B5,ROUNDUP(COLUMN(A:A)/10,0),1)取得的结果是一个文本，1C$4 是一个数值，不同类型的数据是不可比的，不能判断它们之间是否相等。

例如，墙壁上写一个8字，你更多关注的是这个字有多高，什么写的，什么颜色，等等，这些都是文本的属性。而当你看到帐簿上一个8字时，你想到的是数，它与7、与9可以比较，它的大小与数位有关等等，这些都是数值型数据的属性。

"12"是一个文本，12是一个数值，如果相比，"12"大于12：

在单元格里写入公式：="12"=12，结果为False，而="12">12，结果为True。

要把文本"12"当作数值与12相比，就要转换类型，

把文本型转换成数值型，有专用的转换函数 VALUE()：

=value("12")，它的结果就是一个数值。

=value("12")=12，它的结果就是True了。

在函数或公式中，运算过程会自动把文本转换为数值(一个隐含过程)，再与数值进行运算，负值运算(-)也是一种运算，能把文本转换成数值：

-"25"=-25

还记得负负得正吧？

-(-"25")=-(-25)=25

简写为：--"25"=25


--可以把文本转换为数值，但它不是标准的转换方式，是借用负运算的隐含功能。

=IF(--MID($B5,ROUNDUP(COLUMN(A:A)/10,0),1)=C$4,"*",IF(ROW()=5,1,N(C4)+1))

在编辑栏中，像上面一样选取Mid()部分，按F9，显示结果为一个文本。

=IF(--MID($B5,ROUNDUP(COLUMN(A:A)/10,0),1)=C$4,"*",IF(ROW()=5,1,N(C4)+1))

而这个返回的就是数值了。

除了用--进行类型转换，你还可以选择下面其中一种：

=IF(Value(MID($B5,ROUNDUP(COLUMN(A:A)/10,0),1))=C$4,"*",IF(ROW()=5,1,N(C4)+1))

=IF(0+MID($B5,ROUNDUP(COLUMN(A:A)/10,0),1)=C$4,"*",IF(ROW()=5,1,N(C4)+1))

=IF(1*MID($B5,ROUNDUP(COLUMN(A:A)/10,0),1)=C$4,"*",IF(ROW()=5,1,N(C4)+1))

相信其中道理你已经明白。

[ 本帖最后由 52UEXCEL 于 2009-12-16 08:18 编辑 ]

52UEXCEL

单元格输入身份证时忘记把格式设置成文本，结果出现了E+n(一个数值)的情况。

——这就是科学记数法了。它表示10^n

所以9E+307就是9*10^307。

为何用9E+307，其实一开始用的时候是9.99999999999999E+307（不知道小数点后具体几个9），因为Excel软件能接受的数值也就是这么大了。所以用这个表示Excel里的无穷大（只要不是搞科研，可以这么不严谨的理解它，如果要搞科研，Excel也只支持15位运算，不够。）。
==========================================================================================
有朋友这样说。

你认识 Lookup 吗？

我认识 Lookup 是在一家叫良缘的婚姻介绍所。那天我也就为那事去了那家婚姻介绍所，呵，比我早的人还有，于是我就站在老L的身边静静地等着。

老L干咳一声，开始为第一位顾客服务了：你今年多大了？这位先生说话还有点脸红，轻轻说：29。

老L看芳名册了，不，是芳龄册，头也不抬，说：成了，姑娘也是29。

年轻人让开了，后面那位有点经理模样的中年人跨一步上前，刚想问点什么，老L又开腔了：你今年多大了？“经理”只得顺着回答：45。老L说，交钱吧，姑娘36。

老L眼睛往上瞄了瞄，话刚开头：“你今年……”又止住了，怪了，为何不说了？原来那人老L认识，你也认识，是大肚子大耳朵猪八戒，猪八戒像懂规矩似的自己报上了年龄：我今年3680岁，老L恢复了常态，说：如此，给你介绍的姑娘62岁。

这下，我也看出点门道来了：

Lookup(29,{18,18,20,22,25,28,29,30,33,36,46,48,60,62})=29

Lookup(45,{18,18,20,22,25,28,29,30,33,36,46,48,60,62})=36

Lookup(3680,{18,18,20,22,25,28,29,30,33,36,46,48,60,62})=62

Lookup 给你找的人一定不会比你大，同龄或者比你小的人中年龄最接近你的。

Lookup的档案中记载：如果函数 LOOKUP 找不到 lookup_value，则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。

新来的是Match

不知为什么，第二天，Lookup 在良缘婚姻介绍所下岗了，新来的是Match，戴眼睛，说话文绉绉的。

无巧不成书，排在前面的还是那三位，我都认识。

Match 跟Lookup一样，也问，你今年多大了？那位还是回答，29。

Match胸有成竹，说，行，给你介绍7号姑娘。先生满心欢喜离开了。

第二位得到的回答是，行，给你介绍10号姑娘。

轮到猪八戒了，听说猪八戒当书记了，这猪书记早清楚，62岁的是高翠花，60岁的是梦中情人嫦娥，他费尽嘴舌，要Match给他换一个，Match就是不同意，说，这是原则。

打开Match的记录本，你看看：

Match(29,{18,18,20,22,25,28,29,30,33,36,46,48,60,62})=7

Match(45,{18,18,20,22,25,28,29,30,33,36,46,48,60,62})=10

Match(3680,{18,18,20,22,25,28,29,30,33,36,46,48,60,62})=14

9E+307是什么概念？

9E+307是科学记数，用普通记数法写是9后面有307个0，长长的……宋丹丹说了，那是相当的长啊。

如果你到这个年龄，还是到良缘婚姻介绍所找老L和小M，还是那本芳龄册，你说，他们给你介绍谁啊？

想找个18的？没门!

言归正传

你会说，这些我懂，不必费这么大劲，我就是想问个明白，为什么用9E+307？

你再返回上面，看看版主为我们写的公式，反正，在构思解题思路时，就有，把那串文本从第一个数字开始，依次取1位、2位、3位、4位……，总共有多长，最后就取多少位。

有一部分，它的结果不是数(这个处理办法你自己看明白)，取出的结果是数值的，一定有一个是最大的，它一定排列在最后面(多举些例子，看看是不是这样)，我们要取的就是最后面那个数。

为了娶到最后面那位，你就得把年龄往最大里吹，吹!

多少合适？9E+307。

=LOOKUP(562,{5;56;562;562;562;562;#VALUE!;#VALUE!;#VALUE!})

=LOOKUP(1000,{5;56;562;562;562;562;#VALUE!;#VALUE!;#VALUE!})

=LOOKUP(9E+307,{5;56;562;562;562;562;#VALUE!;#VALUE!;#VALUE!})

前面也一样返回562，为保险起见，我们用9E+307查找，为了返回数组中最大的值。

注：Lookup、Match都还有第三个参数，这个参数可以改变它的查找方式，如果你还不知道，一定要弄个明白，不要把责任推给山菊花，谢了。

[ 本帖最后由 52UEXCEL 于 2009-12-14 11:01 编辑 ]

52UEXCEL

1、FIND()告诉我，0-9在文本中第一次出现的位置。

用Find()在指定的文本中查找0-9，会返回第一次出现的位置，如果没有会返回一个错误值，聪明的gvntw，在文本后面连接上1234567890，让Find()不再有借口返回错误值，请放心，它不会影响后面的结果。在编辑栏中，像下面一样，用鼠标将这一段文本抹黑，然后按F9。


=LOOKUP(9E+307,--MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)))))

2、对着 Min()，王小丫说：恭喜你，答对了，第一个数字在第3位。

如果你还怀疑，可以这再次抹黑，并按F9。


=LOOKUP(9E+307,--MID(A1,MIN({7;8;5;12;13;3;4;16;17;18}),ROW(INDIRECT("1:"&LEN(A1)))))

3、神奇魔术师 Mid() ，一个文本变出许许多多。

让事实说话，继续抹抹黑，按F9。开始位置不变，长度依次增1，形成一个数组，数组的个数等于文本长度(Row()的结果)。后面的会出现相同的情况，没关系，还是不影响结果的。


=LOOKUP(9E+307,--MID(A1,3,ROW(INDIRECT("1:"&LEN(A1)))))

4、如果忽略了--，还是功亏一篑。

看看，它的结果会是什么呢？


=LOOKUP(9E+307,--{"5";"56";"562";"562.";"562.0";"562.00";"562.00元";"562.00元";"562.00元"})

5、Lookup() 总是那么沉着，他闭着眼睛说，我就要最后面那个数字。


=LOOKUP(9E+307,{5;56;562;562;562;562.00;#VALUE!;#VALUE!;#VALUE!})

9E+307 是一个非常大的数，在任何时候都适用，如果你知道你的文本中最大数的范围，比如1000，文本中的数字不会超过1000的，那么，这个数字你用1000，也是行的，即：

Lookup(1000,……)

[ 本帖最后由 52UEXCEL 于 2010-6-4 10:09 编辑 ]

52UEXCEL

关于IF{1,0}.rar (9.03 KB, 下载次数: 58)

2009-12-14 12:02 上传

点击文件名下载附件

[ 本帖最后由 52UEXCEL 于 2009-12-16 08:19 编辑 ]

52UEXCEL

= SUM(1/COUNTIF(区域,区域))


个公式是计算区域中不重值的个数的经典公式。

准备

区域A1:A10的数据分别是：公式、计算、可以、公式、公式、复制、这个、重复、可以、不可以

B1写入公式：

=SUM(1/COUNTIF(A1:A10,A1:A10))

它是一个数组公式，同时按 hift+Ctrl+Enter 三键结束。

观察

1、在编辑栏，像下面一样选取函数Countif()部分，然后按F9：

=SUM(1/COUNTIF(A1:A10,A1:A10))

你看到的结果会是：

=SUM(1/{3;1;2;3;3;1;1;1;2;1})

如果这时你按下Esc键，公式会还原为原来的状态。

2、在结果的基础上继续选取，=SUM({1/{3;1;2;3;3;1;1;1;2;1})或者在原公式上选取=SUM(1/COUNTIF(A1:A10,A1:A10))，按F9，结果为：

=SUM({0.333333333333333;1;0.5;0.333333333333333;0.333333333333333;1;1;1;0.5;1})

3、选取全部公式按下F9或者直接按三键，你看到的都是同一结果：7。

分析

1、Countif(A1:A10,A1:A10)是数组计算，运算过程相当于：

Countif(A1:A10,A1)

Countif(A1:A10,A2)

……

Countif(A1:A10,A10)

结果如上面所示，是一个数组，表示区域中等于本单元格数据的个数。

2、用1除以个数，是个非常妙的想法，要结合前后计算才能领会好它的作用。把上一步的结果整理一下，用分数代替小数：

=SUM({1/3;1/3;1/3;1/2;1/2;1;1;1;1;1})

区域中有三个重复的数“公式”，那么这一步就有三个1/3，有两个“可以”，那么这一步的结果就有两个1/2，如果有5个重复的数，那么这一步的结果就有5个1/5，如此类推。

3、这一步几乎不用说了。Sum()进行的是加法运算，3个1/3相加，结果为1,2个1/2相加，结果也是1。如果区域中有8个重复的数，那么一定会有8个1/8相加，结果也是1。

重复的数经过三步运算，到最后结果都是1。

把所有结果加起来，就是区域中不重复的个数。

52UEXCEL

[ 本帖最后由 52UEXCEL 于 2009-12-16 08:14 编辑 ]

chun810523

熬半天没明白。。。。

52UEXCEL

无拘无束数据有效性

http://club.excelhome.net/thread-160679-1-1.html


数据有效性绝技.rar (256.45 KB, 下载次数: 228)

2009-12-14 21:51 上传

点击文件名下载附件

[ 本帖最后由 52UEXCEL 于 2010-3-17 20:23 编辑 ]

ningyong58

今天好好学习这个帖子

52UEXCEL

sum就是简单求和;

SUMIF就是有条件的求和;

===========================================
SUMPRODUCT就是数组求和法.

有一个关于计算物理需求领用量的例子，里面有使用到了SUMPRODUCT()函数，而且是挺复杂一个公式运用，计算需领用数量：
写法1（书中写法）
=SUMPRODUCT(SUMIF(INDIRECT("'CP"&ROW(INDIRECT("1:4"))&"'!B:B"),A5,INDIRECT("'CP"&ROW(INDIRECT("1:4"))&"'!F:F"))*N(INDIRECT("'CP"&ROW(INDIRECT("1:4"))&"'!B3")))
-------------------------------------------------------------------------------------------------------------------------------------------------------
而根据我自己理解，我认为这样写：
写法2（我的写法）
=SUMPRODUCT(SUMIF(INDIRECT("'CP"&ROW(INDIRECT("1:4"))&"'!B:B"),A5,INDIRECT("'CP"&ROW(INDIRECT("1:4"))&"'!F:F")),N(INDIRECT("'CP"&ROW(INDIRECT("1:4"))&"'!B3")))
------------------------------------------------------------------------------------------------------------------------------------------------------
经测试，两种写法得出的结果是一样的。
两种写法的区别就在那个“N”那里
------------------------------------------------------------------------------------------------------------------------------------------------------
SUMPRODUCT()函数是计算数组元素相乘的和，写法1好像没有两个数组啊，而写法2，就形成了两个数组。
写法1意义好像是对的：单位数量X计划数量=总需求量
二种书写都是同样效果。不同的人，不同的书写习惯，
====================================================================

[ 本帖最后由 52UEXCEL 于 2009-12-17 09:11 编辑 ]