「 Formula 」标签的归档

Year Conversion

西元年轉民國年,農曆年

轉自該處

'西曆
=2011/11/18

'民國年 100年7月18
=TEXT(A1,"[$-404]e年m月"&IF(LEN(--TEXT(A1,"[$-130000]dd"))=1,"初","")&"d")

'農曆年 辛卯年七月十八日
=CHOOSE(MOD(YEAR(A1)-1900,10)+1,"庚","辛","壬","癸","甲","乙","丙","丁","戊"," 己")&CHOOSE(MOD(YEAR(A1)-1900,12)+1,"子","丑","寅","卯","辰","巳","午","未"," 申","酉","戌","亥")&TEXT(A1,"[dbnum1][$-130000]年m月"&IF(--TEXT(A1," [$-130000]d")<11,"初","")&TEXT(A1,"[dbnum1][$-130000]d日"))

article clipper remember Year Conversion
 
2011
11/26
分类
Excel
标签
发表评论

Number of different characters in a String

Roberto搞的一個公式比賽.
比賽規定:
利用公式從一個字符串中得知包含多少格字符
1.case sensitive
2.case insensitive
3.non-ascii字符

A1=”example”
A2 return 6

=COUNT(FIND(CHAR(ROW(1:255)),UPPER(A1))) '- case insensitive
=COUNT(FIND(CHAR(ROW(2:255)),A1)) '- case sensitive
article clipper remember Number of different characters in a String
 
2011
11/14
分类
Excel
标签
发表评论

Formula – T

Formual : = T(value)

  1. value为text,返回text
  2. value为non-text,返回空string

Source:

http://blog.contextures.com/archives/2011/01/15/30-excel-functions-in-30-days-14-t/

article clipper remember Formula T
 
2011
01/16
分类
Excel
标签
发表评论

Extract Data With Two Criteria


该Video讲述了formula :Lookup()的应用. 它可以用于vlookup不能适用的向左搜索的功能.

article clipper remember Extract Data With Two Criteria
 
2011
01/03
分类
Excel
标签
发表评论

Subtotal – Magic Trick

Excel Fun介绍了用Filter搭配Subtotal的方式,统计数据的数量. 详见视频以及Formula – Subtotal的帮助

article clipper remember Subtotal – Magic Trick
 
2010
12/25
分类
Excel
标签

发表评论