01.JAVA/Java2012. 9. 20. 07:02
반응형

출처: http://realcool.egloos.com/3924002

 

엑셀의 함수를 자바로 구현할 일이 있어서 찾아본 결과

http://www.formulacompiler.org/doc/reference/index.htm

위의 사이트를 어렵게 찾았습니다.

위의 사이트에서 라이브러리 다운받으시고

필요한 엑셀함수를 호출해서 사용하면 됩니다.

하나만 예로 들어 만들어보면 다음과 같습니다.

엑셀의 chiinv를 예로 들면 다음과 같습니다.

import org.formulacompiler.runtime.internal.RuntimeDouble_v2;

/**
* 카이제곱분포
* @author realcool@empal.com
*
*/
public class Chiinv {
/**
* 카이제곱분포
* @param a
* @param b
* @return
*/
public double chiinv(double _x,double _degFreedom){
return RuntimeDouble_v2.fun_CHIINV(_x, _degFreedom);
}
final double NORMDIST(double input1,double input2,double input3,double input4) {
double d;
if (input3 <= 0.0) {
Runtime_v2.fun_ERROR("#NUM! because sigma <= 0 in NORMDIST");
d = (double) -1;
} else
d = (input4 != 0.0
? (Math.abs((input1 - input2) / input3
* 0.7071067811865476) < 0.7071067811865476
? (0.5
+ 0.5 * RuntimeDouble_v2.fun_ERF((input1 - input2)
/ input3
* 0.7071067811865476))
: (input1 - input2) / input3 * 0.7071067811865476 > 0.0
? 1.0 - 0.5 * (RuntimeDouble_v2.fun_ERFC
(Math.abs((input1 - input2) / input3
* 0.7071067811865476)))
: 0.5 * (RuntimeDouble_v2.fun_ERFC
(Math.abs((input1 - input2) / input3
* 0.7071067811865476))))
: (Math.exp((input1 - input2) * (input1 - input2)
/ (-2.0 * input3 * input3))
/ (2.5066282746310002 * input3)));
return d;
}
public static void main(String[] args){
Chiinv c = new Chiinv();
double d = c.chiinv(0.05, 2);
System.out.println(d);
}
}

Since AFC only supports Microsoft Excel spreadsheet semantics at the moment, this reference uses Excel as a baseline for expected behaviour of the supported functions.

Cells, Values, Names
Strings, numbers, dates, booleans; blank cells; cell names; ranges; numeric precision
Numeric Operators
%, *, +, -, /, <, <=, <>, =, >, >=, ^
Numeric Functions
ABS, ACOS, ACOSH, ASIN, ASINH, ATAN, ATAN2, ATANH, CEILING, COMBIN, COS, COSH, DEGREES, EVEN, EXP, FACT, FLOOR, GEOMEAN, HARMEAN, INT, LN, LOG, LOG10, MOD, ODD, PERMUT, PI, POWER, RADIANS, RAND, ROUND, ROUNDDOWN, ROUNDUP, SIGN, SIN, SINH, SQRT, TAN, TANH, TRUNC
Financial Functions
DB, DDB, FV, IRR, MIRR, NPER, NPV, PMT, PV, RATE, SLN, SYD, VDB
Statistical Functions
AVEDEV, BETADIST, BETAINV, BINOMDIST, CHIDIST, CHIINV, CHITEST, CONFIDENCE, CORREL, CRITBINOM, DEVSQ, EXPONDIST, FDIST, FINV, FISHER, FISHERINV, FORECAST, FTEST, GAMMADIST, GAMMAINV, GAMMALN, HYPGEOMDIST, INTERCEPT, KURT, LARGE, LOGINV, LOGNORMDIST, MEDIAN, MODE, NEGBINOMDIST, NORMDIST, NORMINV, NORMSDIST, NORMSINV, PEARSON, PERCENTILE, PERCENTRANK, POISSON, PROB, QUARTILE, RANK, RSQ, SKEW, SLOPE, SMALL, STANDARDIZE, STDEV, STDEVP, STDEVPA, STEYX, SUMX2MY2, SUMX2PY2, SUMXMY2, TDIST, TINV, TRIMMEAN, TTEST, VAR, VARA, VARP, WEIBULL, ZTEST
String Support
&, <, <=, <>, =, >, >=, CLEAN, CONCATENATE, EXACT, FIND, LEFT, LEN, LOWER, MATCH, MID, PROPER, REPLACE, REPT, RIGHT, SEARCH, SUBSTITUTE, TRIM, UPPER
Boolean Functions And IF
AND, IF, NOT, OR, true
Date Functions
DATE, DAY, DAYS360, HOUR, MINUTE, MONTH, NOW, SECOND, TIME, TODAY, WEEKDAY, YEAR
Conversion Functions
CHAR, CODE, DATEVALUE, DOLLAR, FIXED, N, ROMAN, T, TEXT, TIMEVALUE, VALUE
Type Functions
ISNONTEXT, ISNUMBER, ISTEXT
Aggregators
AVEDEV, AVERAGE, COUNT, COUNTA, COVAR, DEVSQ, KURT, MAX, MIN, PRODUCT, SKEW, STDEV, STDEVP, SUM, SUMIF, SUMSQ, VAR, VARP
Database Table Aggregators
COUNTIF, DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP, SUMIF
Lookup Functions
CHOOSE, HLOOKUP, INDEX, LOOKUP, MATCH, VLOOKUP
Limitations
Things that are known to not work quite as expected yet.

Note On The Sample Expressions

To ensure its correctness, all the example expressions and their results have been cited from automated tests that are run with every release build.

이 글과 관련있는 글을 자동검색한 결과입니다 [?]

by 수평선 | 2009/01/18 19:54 | 자바코드 | 트랙백 | 덧글(2)

트랙백 주소 : http://realcool.egloos.com/tb/3924002
☞ 내 이글루에 이 글과 관련된 글 쓰기 (트랙백 보내기) [도움말]
Commented by 노태광 at 2009/03/11 11:07
정말 좋은 자료를 찾았어요. 너무 감사드립니다.
한가지 여쭤보고 싶은게 있는데요.
NORMDIST 함수를 사용하고 싶은데. 라이브러리에 포함되어 있지 않은 것 같아서요.
제가 잘 몰라서 못찾는 것 같은데. 좀 알려주시면 고맙겠습니다.^^*
Commented by 수평선 at 2009/03/23 10:26
찾으시는 자료가 혹시 다음일거라 생각되네요..
package JSci.maths.statistics;

import JSci.maths.*;

/**
* The NormalDistribution class provides an object for encapsulating normal distributions.
* @version 1.1
* @author Jaco van Kooten
*/
public final class NormalDistribution extends ProbabilityDistribution implements NumericalConstants {
private double mean,variance;
private double pdfDenominator,cdfDenominator;

/**
* Constructs the standard normal distribution (zero mean and unity variance).
*/
public NormalDistribution() {
this(0.0,1.0);
}
/**
* Constructs a normal distribution.
* @param mu the mean.
* @param var the variance.
*/
public NormalDistribution(double mu,double var) {
mean=mu;
if(var<=0.0)
throw new OutOfRangeException("The variance should be (strictly) positive.");
variance=var;
pdfDenominator=SQRT2PI*Math.sqrt(variance);
cdfDenominator=SQRT2*Math.sqrt(variance);
}
/**
* Constructs a normal distribution from a data set.
* @param array a sample.
* @author Mark Hale
*/
public NormalDistribution(double array[]) {
double sumX=array[0];
double sumX2=array[0]*array[0];
for(int i=1;i<array.length;i++) {
sumX+=array[i];
sumX2+=array[i]*array[i];
}
mean=sumX/array.length;
variance=(sumX2 - array.length*mean*mean)/(array.length-1);
pdfDenominator=SQRT2PI*Math.sqrt(variance);
cdfDenominator=SQRT2*Math.sqrt(variance);
}
/**
* Returns the mean.
*/
public double getMean() {
return mean;
}
/**
* Returns the variance.
*/
public double getVariance() {
return variance;
}
/**
* Probability density function of a normal (Gaussian) distribution.
* @return the probability that a stochastic variable x has the value X, i.e. P(x=X).
*/
public double probability(double X) {
return Math.exp(-(X-mean)*(X-mean)/(2*variance))/pdfDenominator;
}
/**
* Cumulative normal distribution function.
* @return the probability that a stochastic variable x is less then X, i.e. P(x&lt;X).
*/
public double cumulative(double X) {
return SpecialMath.complementaryError(-(X-mean)/cdfDenominator)/2;
}
/**
* Inverse of the cumulative normal distribution function.
* @return the value X for which P(x&lt;X).
*/
public double inverse(double probability) {
checkRange(probability);
if(probability==0.0)
return -Double.MAX_VALUE;
if(probability==1.0)
return Double.MAX_VALUE;
if(probability==0.5)
return mean;
// To ensure numerical stability we need to rescale the distribution
double meanSave=mean,varSave=variance;
double pdfDSave=pdfDenominator,cdfDSave=cdfDenominator;
mean=0.0;
variance=1.0;
pdfDenominator=Math.sqrt(TWO_PI);
cdfDenominator=SQRT2;
double X=findRoot(probability, 0.0, -100.0, 100.0);
// Scale back
mean=meanSave;
variance=varSave;
pdfDenominator=pdfDSave;
cdfDenominator=cdfDSave;
return X*Math.sqrt(variance)+mean;
}
}

http://jsci.sourceforge.net/api/JSci/maths/statistics/NormalDistribution.html
위 주소입니다.
http://jsci.sourceforge.net/

소스다운로드 해서 분석하면 될것 같네요..
Posted by 1010