[Emily 分享]

zz5151353

真是好东西,收藏研究,感谢 Emily 版主.

Emily

Updated on 24 Oct 2004, 2/F

jame

只是有一个疑问，为什么斑竹用繁体字啊，没有简体字嘛？

我们想看起来方便一点。

Emily

ExcelHome 不是自動將繁體轉成簡體的嗎？如果不是，我每次都要將繁體轉成簡體。

ExcelHome 不是自动将繁体转成简体的吗？如果不是，我每次都要将繁体转成简体。[em06]

忘記了，我是從香港來的，用繁體

[此贴子已经被作者于2004-12-21 11:05:49编辑过]

ab007

多谢了，从香江来的版主！[em27]

Emily

Mathematical ATP functions

ATP Function	Description	ATP Syntax	Replacement Formula	Array formula
DOLLARDE	Converts a dollar price (expressed as a fraction) into a dollar price expressed as a decimal number	=DOLLARDE(fractional_dollar,fraction)	=TRUNC(fractional_dollar)+((fractional_dollar-TRUNC(fractional_dollar))*10)/fraction	No
DOLLARFR	Converts a dollar price (expressed as a decimal number) into a dollar price expressed as a fraction	=DOLLARFR(decimal_dollar,fraction)	=TRUNC(decimal_dollar)+((decimal_dollar-TRUNC(decimal_dollar))/10)*fraction	No
ERF	Returns the error function integrated between lower_limit and upper_limit	=ERF(lower_limit,upper_limit)	=GAMMADIST(upper_limit^2,0.5,1,TRUE)-GAMMADIST(lower_limit^2,0.5,1,TRUE)	No
ERFC	Returns the complementary ERF function integrated between x and infinity	=ERFC(x)	=ChiDist(2*x^2,1)	No
FACTDOUBLE	Returns the double factorial of a number	=FACTDOUBLE(number)	=PRODUCT(IF(MOD(ROW(INDIRECT("1:"&number)),2)=MOD(number,2),(ROW(INDIRECT("1:"&number)))))	Yes
GCD	Returns the greatest common divisor of 2 - 29 integers	=GCD(number1,number2,…)	=MAX(IF((MOD(number2,ROW(INDIRECT("1:"&number1)))&MOD(number1,ROW(INDIRECT("1:"&Anumber1))))="00″,ROW(INDIRECT("1:"&number1))))	Yes
GESTEP	Tests whether a number is greater than a threshold value	=GESTEP(number,step)	=--(number>=step)	No
LCM	Returns the least common multiple of 1 - 29 integers	=LCM(number1,number2, …)	=number2*MATCH(0,MOD(number2*ROW(INDIRECT("1:"&number1)),number1),0)	Yes
MROUND	Returns a number rounded to the desired multiple. Midway points are rounded away from 0	=MROUND(number,multiple)	=ROUND(number/multiple,0)*multiple	No
MULTINOMIAL	Returns the ratio of the factorial of a sum of values to the product of factorials	=MULTINOMIAL(number1,number2, …)	=FACT(SUM(number1,number2,…))/PRODUCT(FACT(number1,number2,…))	Yes
QUOTIENT	Returns the integer portion of a division	=QUOTIENT(numerator,denominator)	=TRUNC(numerator/denominator)	No
RANDBETWEEN	Returns a random number between (and inclusive of) two specified numbers	=RANDBETWEEN(bottom,top)	=INT((top - bottom + 1) * RAND() + bottom)	No
SERIESSUM	Returns the sum of a power series expansion	=SERIESSUM(x,n,m,coefficients)	=SUMPRODUCT(coefficients,x^(n+m*(ROW(INDIRECT("1:"&ROWS(coefficients)))-1)))	No
SQRTPI	Returns the square root of a number multiplied by pi	=SQRTPI(number)	SQRT(PI()*number)	No

Emily

Date ATP functions

ATP Function	Description	ATP Syntax	Replacement Formula	Array formula
EDATE	Returns the Excel date / time serial number of the date that is the indicated number of months before or after the specified number of months from the start_date	=EDATE(start_date,months)	=DATE(YEAR(start_date),MONTH(start_date)+months,MIN(DAY(start_date),DAY(DATE(YEAR(start_date),MONTH(start_date)+months+1,0))))	No
EOMONTH	Returns the Excel date / time serial number of the last day of the month before or after a specified number of months from start_date	=EOMONTH(start_date,months)	=DATE(YEAR(start_date),MONTH(start_date)+months+1,0)	No
NETWORKDAYS	Returns the number of whole working days between two dates excluding specified holidays	=NETWORKDAYS(start_date,end_date,holidays)	=SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)))<>1),(–(WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)))<>7)),–(COUNTIF(holidays,ROW(INDIRECT(start_date&":"&end_date)))=0))*(1-2*(start_date>end_date))	No
WEEKNUM	Returns the weeknumber in the year. The 1st week starts Jan-1; the 2nd week starts the following Sunday (return_type = 1) or Monday (return_type = 2). Replacement formula returns the ISO weeknumber.	=WEEKNUM(serial_num,return_type)	=1+INT((serial_num-DATE(YEAR(serial_num+4-WEEKDAY(serial_num+6)),1,5)+ WEEKDAY(DATE(YEAR(serial_num+4-WEEKDAY(serial_num+6)),1,3)))/7)	No
WORKDAY	Returns the Excel date / time serial number of the date before or after a specified number of workdays excluding holidays	=WORKDAY(start_date,days,holidays)	=start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKDAY(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(days)*10))),2)<6)*ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(days)*10))),holidays,0)),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days)))	Yes

Emily

Numeric System Conversion ATP functions

ATP Function	Description	ATP Syntax	Replacement Formula	Array formula
BIN2DEC	Converts binary number to decimal	=BIN2DEC(Number)	=SUMPRODUCT(MID("0"&Number,ROW(INDIRECT("1:"&LEN("0"&Number))),1)*2^(LEN("0"&Number)-ROW(INDIRECT("1:"&LEN("0"&Number)))))	No
BIN2OCT	Converts binary number to octal	=BIN2OCT(Number,Places)	Combine solutions for BIN2DEC and DEC2OCT	No
DEC2BIN	Converts a decimal number to binary	=DEC2BIN(Number)	=SUMPRODUCT(INT(MOD(Number/2^(COLUMN(1:1)-1),2))*10^(COLUMN(1:1)-1))	No
DEC2OCT	Converts a decimal number to octal	=DEC2OCT(Number)	=SUMPRODUCT(INT(MOD(Number/8^(COLUMN(1:1)-1),8))*10^(COLUMN(1:1)-1))	No
HEX2BIN	Converts a hexadecimal to a binary	=HEX2BIN(Number,Places)	Combine solutions for HEX2DEC and DEC2BIN	No
HEX2DEC	Converts a hexadecimal to a decimal	=HEX2DEC(Number)	=SUMPRODUCT((MATCH(MID("0"&Number,ROW(INDIRECT("1:"&LEN("0"&Number))),1),{"0","1","2","3","4","5","6","7","8","9","A","B","C","D","E","F"},0)-1)*16^(LEN("0"&Number)-ROW(INDIRECT("1:"&LEN("0"&Number)))))	No
HEX2OCT	Converts a hexadecimal to an octal	=HEX2OCT(Number,Places)	Combine solutions for HEX2DEC and DEC2OCT	No
OCT2BIN	Converts an octal number to binary	=OCT2BIN(number,places)	Combine solutions for OCT2DEC and DEC2BIN	No
OCT2DEC	Converts an octal number to decimal	=OCT2DEC(number)	=SUMPRODUCT(MID("0"&Number,ROW(INDIRECT("1:"&LEN("0"&Number))),1)*8^(LEN("0"&Number)-ROW(INDIRECT("1:"&LEN("0"&Number)))))	No

Emily

Other ATP functions

ATP Function	Description	ATP Syntax	Replacement Formula	Array formula
DELTA	Test whether two values are equal	=DELTA(number1,number2)	=--(number1=number2)	No
ISEVEN	Returns TRUE if the number is even	=ISEVEN(Number)	=MOD(TRUNC(Number),2)=0	No
ISODD	Returns TRUE if the number is odd	=ISODD(Number)	=MOD(TRUNC(Number),2)=1	No

------- End

[此贴子已经被作者于2004-12-24 19:25:30编辑过]

ryan_tyw

辛苦啊