函“术”技巧接龙，荟萃最小颗粒的技巧定式

胡剑0227 · 发表于 2011-7-7 09:27

前段时间一直想整理一些函数的小技巧，这些技巧是靠平时积累的，且必须体会深刻才能在需要时及时浮出脑海。函数公式就是一个组装小逻辑的过程，通过组装最后实现对复杂逻辑的描述。哲学史上曾经有人提出“原子论”，一个最小的颗粒能演化出万事万物。这个也类似活字印刷，把颗粒变成一个一个的字，而不是一匡一匡定死木刻那么灵活性就大大加强了。

这是一个倡议帖，希望能集合论坛集体的智慧整理通用的函“术”技巧。

抛砖引玉.
如果有好的技巧可送财富，呵呵
区域1可以是引用，也可以是数组...

请从自然语言表达的逻辑出发，用函数公式尽可能平实的落实逻辑...

[ 本帖最后由胡剑0227 于 2011-7-7 09:37 编辑 ]

胡剑0227 · 发表于 2011-7-7 09:37

最近时间紧，我就先匆匆上一个技巧：

存在性判断
判断某列区域（名称为区域1）中是否包含某数据（名称为标的）。通过MATCH查找标的在区域1 中的位置，如果返回错误表示不存在...

=if(ISERROR(MATCH(标的，区域1,0）),"不存在的后续处理","存在的后续处理")

复制代码

jianbing · 发表于 2011-7-7 09:46

=mid("不存在的后续处理",or(标的={区域1,0})+1,9)

复制代码

胡剑0227：
请先注明实现的逻辑，这样方便同学在思路比较顺的来了解...

[ 本帖最后由胡剑0227 于 2011-7-7 10:53 编辑 ]

cleverzhzhf · 发表于 2011-7-7 09:54

在胡版下面，斗胆献上一个。
求小值，数组公式：

=SMALL(IF(区域1="某条件",区域2),n)

复制代码

其中省略了IF的第三个参数，为了让它返回逻辑值FALSE，大于一切数值。
举例，A列为工作人员（有重复），B列为对应的不同人员做某项工作多次统计的时间：

=SMALL(IF(A1:A10="张三",B1:B10),2)

复制代码

求得张三作此工作第二快的时间是多少。

公式有个致命伤。用IF返回的是一串数组，不是引用，无法在外面接上COUNTIF函数。

胡剑0227：
也可以说我们需要一个更具包容性的COUNTIF数组版，呵呵。

[ 本帖最后由胡剑0227 于 2011-7-7 11:02 编辑 ]

胡剑0227 · 发表于 2011-7-8 06:55

定位文本字符串中某字符第N次出现的位置

精确定位某字符第N次出现的位置.jpg

=FIND("#",
SUBSTITUTE(A1,"弃","#",2),
1)

复制代码

分析：
某字符出现的位置可以使用FIND函数，SUBSTIUTE函数用来指定第N出现的N(这个是亮点）。用SUBSTITUTE对出现次序进行限定是个好技巧，不知道是否还有其他的。如果把写函数公式当成抓药方，每味药各自完成自己的使命，那么需要用到对字符出现次序进行限定时可以用这味药。

# 这里是一个辅助字符，类似解几何题时用到的辅助线。

[ 本帖最后由胡剑0227 于 2011-7-8 07:11 编辑 ]

angellbxsc · 发表于 2011-7-9 06:09

嗯，胡版5楼好方法，学习！

胡剑：0227
那你也分享一个小函“术”，不需要高深，不需要非得很精巧。写一个实际运用时经常性要完成的逻辑...

[ 本帖最后由胡剑0227 于 2011-7-9 07:13 编辑 ]

piny · 发表于 2011-7-9 08:23

原公式之FIND第三參數可略(省略時自動視為從第一值開始找)

=FIND("#",SUBSTITUTE(A1,"弃","#",2))

复制代码

這樣應該更直觀(若只是找第二個的話)

=FIND("弃",A1,FIND("弃",A1)+1)

复制代码

或數組解

=SMALL(IF(MID(A1,ROW(1:99),1)="弃",ROW(1:99)),2)

复制代码

胡剑0227 · 发表于 2011-7-9 12:58

欢迎陈版也奉献一个日常需要完成的小逻辑实现方式，即从自然语言落实到公式

bluexuemei · 发表于 2011-7-9 14:14

原帖由 piny 于 2011-7-9 08:23 发表
原公式之FIND第三參數可略(省略時自動視為從第一值開始找)=FIND("#",SUBSTITUTE(A1,"弃","#",2))這樣應該更直觀(若只是找第二個的話)=FIND("弃",A1,FIND("弃",A1)+1)或數組解=SMALL(IF(MID(A1,ROW(1:99),1)="弃",ROW ...

好方法,精彩,find的第三参数为START_NUM

胡剑0227 · 发表于 2011-7-12 07:02

在公式中设置单元格日期格式

假设
A1单元格：20101004
请在B1单元格返回真正的日期数据，并且显示为“2010-10-4”

注意：不准手工修改单元格格式。
方式0，你得先试试这个，否则你也许看不明白

=VALUE(MID(A1,1,4)&"-"&MID(A1,5,2)&"-"&MID(A1,7,2))

复制代码

=TEXT(A1,"0000-00-00")

复制代码

方式1

=VALUE(MID(A1,1,4)&"-"&MID(A1,5,2)&"-"&MID(A1,7,2))+DATE(1900,1,0)

复制代码

方式2

=TEXT(A1,"0000-00-00")+DATE(1900,1,0)

复制代码

这个需要试试，

主要体会 DATE(1900,1,0) 的作用，这个还是很有意思

[ 本帖最后由胡剑0227 于 2011-7-15 22:31 编辑 ]

		自动登录	找回密码
密码			免费注册

[讨论] 函“术”技巧接龙，荟萃最小颗粒的技巧定式

评分

回复 7楼 piny 的帖子