2 Star 2 Fork 2

ToolGood / ToolGood.Algorithm

加入 Gitee
与超过 1200万 开发者一起发现、参与优秀开源项目,私有仓库也完全免费 :)
免费加入
克隆/下载
贡献代码
同步代码
取消
提示: 由于 Git 不支持空文件夾,创建文件夹后会生成空的 .keep 文件
Loading...
README
Apache-2.0

ToolGood.Algorithm

English document

ToolGood.Algorithm是一个功能强大、轻量级、兼容Excel公式的算法类库,旨在提高开发人员在不同业务场景中的生产力。

适用场景: 代码与算法分离,避免项目强制升级

1)项目初期,未确定的算法;

2)项目维护时,经常改动的算法;

3)财务数据、统计数据之中的算法,(注:部分公式会使用double类型,建议使用为单位);

4)报表导出,数据来源使用存储过程,Word文档内设置算法。例 https://github.com/toolgood/ToolGood.WordTemplate

5)可视化规则引擎,如:https://github.com/toolgood/ToolGood.FlowVision

快速上手

    AlgorithmEngine engine = new AlgorithmEngine();
    double a=0.0;
    if (engine.Parse("1+2")) {
        var o = engine.Evaluate();
        a=o.NumberValue;
    }
    var b = engine.TryEvaluate("1=1 && 1<2 and 7-8>1", 0);// Support(支持) && || and or 
    var c = engine.TryEvaluate("2+3", 0);
    var q = engine.TryEvaluate("-7 < -2 ?1 : 2", 0);
    var e = engine.TryEvaluate("count(array(1, 2, 3, 4))", 0);//{} represents array, return: 4 {}代表数组, 返回:4
    var s = engine.TryEvaluate("'aa'&'bb'", ""); //String connection, return: AABB 字符串连接, 返回:aabb
    var r = engine.TryEvaluate("(1=1)*9+2", 0); //Return: 11 返回:11
    var d = engine.TryEvaluate("'2016-1-1'+1", DateTime.MinValue); //Return date: 2016-1-2 返回日期:2016-1-2
    var t = engine.TryEvaluate("'2016-1-1'+9*'1:0'", DateTime.MinValue);//Return datetime:2016-1-1 9:0  返回日期:2016-1-1 9:0
    var j = engine.TryEvaluate("json('{\"Name\":\"William Shakespeare\", \"Age\":51, \"Birthday\":\"04/26/1564 00:00:00\"}').Age", null);//Return 51 返回51
    var k = engine.TryEvaluate("json('{\"Name\":\"William Shakespeare   \", \"Age\":51, \"Birthday\":\"04/26/1564 00:00:00\"}')[Name].Trim()", null);//Return to "William Shakespeare"  返回"William Shakespeare" (不带空格)
    var l = engine.TryEvaluate("json('{\"Name1\":\"William Shakespeare \", \"Age\":51, \"Birthday\":\"04/26/1564 00:00:00\"}')['Name'& 1].Trim().substring(2, 3)", null);//Return "ill"  返回"ill"

支持常量pi, e, true, false

数值转bool,非零为真, 零为假。字符串转bool, 0FALSE为假,1TRUE为真。不区分大小写。

bool转数值,假为0,真为1。bool转字符串,假为FALSE,真为TRUE

索引默认为Excel索引,如果想用c#索引,请设置UseExcelIndexfalse

中文符号自动转成英文符号:括号, 方括号, 逗号, 引号, 双引号

注:字符串拼接使用&

注:find为Excel公式,find(要查找的字符串, 被查找的字符串[, 开始位置])

自定义参数

    //Define cylinder information  定义圆柱信息 
    public class Cylinder : AlgorithmEngine
    {
        private int _radius;
        private int _height;
        public Cylinder(int radius, int height)
        {
            _radius = radius;
            _height = height;
        }

        protected override Operand GetParameter(string parameter)
        {
            if (parameter == "半径")
            {
                return Operand.Create(_radius);
            }
            if (parameter == "直径")
            {
                return Operand.Create(_radius * 2);
            }
            if (parameter == "高")
            {
                return Operand.Create(_height);
            }
            return base.GetParameter(parameter);
        }
    }
    //Call method  调用方法
    Cylinder c = new Cylinder(3, 10);
    c.TryEvaluate("[半径]*[半径]*pi()", 0.0);      //Round bottom area  圆底面积
    c.TryEvaluate("[直径]*pi()", 0.0);            //The length of the circle  圆的长
    c.TryEvaluate("[半径]*[半径]*pi()*[高]", 0.0); //Volume of circle 圆的体积
    c.TryEvaluate("['半径']*[半径]*pi()*[高]", 0.0); //Volume of circle 圆的体积
    c.EvaluateFormula("'圆'-[半径]-高", '-'); // Return: 圆-3-10
    c.GetSimplifiedFormula("半径*if(半径>2, 1+4, 3)"); // Return: 3 * 5

参数以方括号定义,如 [参数名]

注:还可以使用AddParameterAddParameterFromJson添加方法,使用DiyFunction+=来自定义函数。

注2:使用 AlgorithmEngineHelper.GetDiyNames 获取参数名自定义方法名

多公式

    ConditionCache multiConditionCache = new ConditionCache();
    multiConditionCache.LazyLoad = true;
    multiConditionCache.AddFormula("桌面积", "[圆桌]", "[半径]*[半径]*pi()");
    multiConditionCache.AddFormula("桌面积", "[方桌]", "[长]*[宽]");
    multiConditionCache.AddFormula("价格", "[圆桌]&& [半径]<2.5", "[桌面积]*1.3");
    multiConditionCache.AddFormula("价格", "[圆桌]&& [半径]<5", "[桌面积]*1.5");
    multiConditionCache.AddFormula("价格", "[圆桌]&& [半径]<7", "[桌面积]*2");
    multiConditionCache.AddFormula("价格", "[圆桌]", "[桌面积]*2.5");
    multiConditionCache.AddFormula("价格", "[方桌]&& [长]<1.3", "[桌面积]*1.3+[高]*1.1");
    multiConditionCache.AddFormula("价格", "[方桌]&& [长]<2", "[桌面积]*1.5+[高]*1.1");
    multiConditionCache.AddFormula("价格", "[方桌]&& [长]<5", "[桌面积]*2+[高]*1.1");
    multiConditionCache.AddFormula("价格", "[方桌]&& [长]<7", "[桌面积]*2.5");

    var algoEngine = new ToolGood.Algorithm.AlgorithmEngineEx(multiConditionCache);
    algoEngine.JumpConditionError = true;
    algoEngine.AddParameter("方桌", true);
    algoEngine.AddParameter("长", 3);
    algoEngine.AddParameter("宽", 1.3);
    algoEngine.AddParameter("高", 1);

    var p2 = algoEngine.TryEvaluate("价格", 0.0);
    Assert.AreEqual(3 * 1.3 * 2 + 1 * 1.1, p2, 0.0001);

更多功能请看一下单元测试。

自定义参数

    var helper = new ToolGood.Algorithm.AlgorithmEngineHelper();
    helper.IsKeywords("false"); // return true
    helper.IsKeywords("true"); // return true
    helper.IsKeywords("mysql"); // return false

    DiyNameInfo p5 = helper.GetDiyNames("ddd(d1, 22)");
    Assert.AreEqual("ddd", p5.Functions[0]);
    Assert.AreEqual("d1", p5.Parameters[0]);

Excel公式

函数:逻辑函数数学与三角函数文本函数统计函数日期与时间函数

注:函数名不分大小写, 带方括号的参数可省略, 示例的返回值, 为近似值。

注2:函数名带★,表示第一个参数可以前置,如(-1).ISTEXT()

注3:函数名带▲,表示受Excel索引影响,

逻辑函数

函数名 说明 示例
IF if(测试条件, 真值[, 假值])
执行真假值判断, 根据逻辑计算的真假值, 返回不同结果。
if(1=1, 1, 2)
>>1
ifError ifError(测试条件, 真值[, 假值])
如果公式计算出错误则返回您指定的值;否则返回公式结果。
ifError(1/0, 1, 2)
>>1
isError ★ isError(值)
判断是否出错, 返回 TRUE 或 FALSE isError(值, 替换值)
如果出错, 返回替换值,否则返回原值
isError(1)
>>false
isNull ★ isNull(值)
判断是否为空, 返回 TRUE 或 FALSE isNull(值, 替换值)
如果为空, 返回替换值,否则返回原值
isNull(null)
>>true
isNullOrError ★ isNullOrError(值)
判断是否为空或错误, 返回 TRUE 或 FALSE isNullOrError(值, 替换值)
如果为空或错误, 返回替换值,否则返回原值
isNullOrError(null)
>>true
isNumber ★ isNumber(值)
判断是否数值, 返回 TRUE 或 FALSE
isNumber(1)
>>true
isText ★ isText(值)
判断是否文字, 返回 TRUE 或 FALSE
isText('1')
>>true
IsNonText ★ IsNonText(值)
判断是否为非文字, 返回 TRUE 或 FALSE
IsNonText('1')
>>false
IsLogical ★ IsLogical(值)
判断是否为逻辑值, 返回 TRUE 或 FALSE
IsLogical('1')
>>false
IsEven ★ IsEven(值)
如果数值是偶数, 返回 TRUE 或 FALSE
IsEven('1')
>>false
IsOdd ★ IsOdd(值)
如果数值是奇数, 返回 TRUE 或 FALSE
IsOdd('1')
>>true
AND and(逻辑值1, ...)
如果所有参数均为TRUE, 则返回TRUE, 如有错误先报错
and(1, 2=2)
>>true
OR or(逻辑值1, ...)
如果任一参数为TRUE, 则返回TRUE, 如有错误先报错
or(1, 2=3)
>>true
NOT not(逻辑值)
对参数的逻辑值求反
NOT(true())
>>false
TRUE true()
返回逻辑值TRUE
true()
>>true
FALSE false()
返回逻辑值FALSE
false()
>>false

数学与三角函数

分类 函数名 说明 示例






E e()
返回 e 值
E()
>>
PI pi()
返回 PI 值
pi()
>>3.141592654
abs abs(数值)
返回数值的绝对值
abs(-1)
>>1
QUOTIENT quotient(除数, 被除数)
返回商的整数部分, 该函数可用于舍掉商的小数部分。
QUOTIENT(7, 3)
>>2
mod mod(除数, 被除数)
返回两数相除的余数
MOD(7, 3)
>>1
SIGN sign(数值)
返回数值的符号。当数值为正数时返回 1, 为零时返回 0, 为负数时返回 -1。
SIGN(-9)
>>-1
SQRT sqrt(数值)
返回正平方根
SQRT(9)
>>3
TRUNC trunc(数值)
将数值截尾取整
TRUNC(9.222)
>>9
int ★ int(数值)
将数值向下舍入到最接近的整数。
int(9.222)
>>9
gcd gcd(数值, ...)
返回最大公约数
GCD(3, 5, 7)
>>1
LCM lcm(数值, ...)
返回整数参数的最小公倍数
LCM(3, 5, 7)
>>105
combin combin(总数, 排列数)
计算从给定数目的对象集合中提取若干对象的组合数
combin(10, 2)
>>45
PERMUT permut(总数, 排列数)
返回从给定数目的对象集合中选取的若干对象的排列数
PERMUT(10, 2)
>>990
FIXED fixed(数值[, 小数位数[, 有无逗号分隔符]])
将数值设置为具有固定小数位的文本格式
FIXED(4567.89, 1)
>>4, 567.9






degrees degrees(弧度)
将弧度转换为度
degrees(pi())
>>180
RADIANS radians(度)
将度转换为弧度
RADIANS(180)
>>3.141592654
cos cos(弧度)
返回数值的余弦值
cos(1)
>>0.540302305868
cosh cosh(弧度)
返回数值的双曲余弦值
cosh(1)
>>1.54308063481
SIN sin(弧度)
返回给定角度的正弦值
sin(1)
>>0.84147098480
SINH sinh(弧度)
返回数值的双曲正弦值
sinh(1)
>>1.1752011936
TAN tan(弧度)
返回数值的正切值
tan(1)
>>1.55740772465
TANH tanh(弧度)
返回数值的双曲正切值
tanh(1)
>>0.761594155955
acos acos(数值)
返回数值的反余弦值
acos(0.5)
>>1.04719755119
acosh acosh(数值)
返回数值的反双曲余弦值
acosh(1.5)
>>0.962423650119
asin asin(数值)
返回数值的反正弦值
asin(0.5)
>>0.523598775598
asinh asinh(数值)
返回数值的反双曲正弦值。
asinh(1.5)
>>1.1947632172
atan atan(数值)
返回数值的反正切值
atan(1)
>>0.785398163397
atanh atanh(数值)
返回参数的反双曲正切值
atanh(1)
>>0.549306144334
atan2 atan2(数值, 数值)
从X和Y坐标返回反正切
atan2(1, 2)
>>1.10714871779






ROUND round(数值, 小数位数)
返回某个数值按指定位数取整后的数值。
ROUND(4.333, 2)
>>4.33
roundDown roundDown(数值, 小数位数)
靠近零值, 向下(绝对值减小的方向)舍入数值。
roundDown(4.333, 2)
>>4.33
roundUp roundUp(数值, 小数位数)
远离零值, 向上(绝对值增长的方向)舍入数值。
roundUp(4.333, 2)
>>4.34
CEILING ceiling(数值, 舍入基数)
向上舍入(沿绝对值增大的方向)为最接近的 舍入基数 的倍数。
CEILING(4.333, 0.1)
>>4.4
floor floor(数值, 舍入基数)
向下舍入, 使其等于最接近的 Significance 的倍数。
FLOOR(4.333, 0.1)
>>4.3
even even(数值)
返回沿绝对值增大方向取整后最接近的偶数。
EVEN(3)
>>4
ODD odd(数值)
将数值向上舍入为最接近的奇型整数
ODD(3.1)
>>5
MROUND mround(数值, 舍入基数)
返回一个舍入到所需倍数的数值
MROUND(13, 5)
>>15




RAND rand()
返回 0 到 1 之间的随机数
RAND()
>>0.2
randBetween randBetween(最小整数, 最大整数)
返回大于等于指定的最小值, 小于指定最大值之间的一个随机整数。
randBetween(2, 44)
>>9


/





/



fact fact(数值)
返回数的阶乘, 一个数的阶乘等于 1*2*3*…* 该数。
FACT(3)
>>6
factdouble factDouble(数值)
返回数值的双倍阶乘
factDouble(10)
>>3840
POWER power(数值, 幂)
返回数的乘幂结果
POWER(10, 2)
>>100
exp exp(幂)
返回e的指定数乘幂
exp(2)
>>7.389056099
ln ln(数值)
返回数值的自然对数
LN(4)
>>1.386294361
log log(数值[, 底数])
返回数值的常用对数, 如省略底数, 默认为10
LOG(100, 10)
>>2
LOG10 log10(数值)
返回数值的10对数
LOG10(100)
>>2
MULTINOMIAL multinomial(数值, ...)
返回参数和的阶乘与各参数阶乘乘积的比值
MULTINOMIAL(1, 2, 3)
>>60
PRODUCT product(数值, ...)
将所有以参数形式给出的数值相乘, 并返回乘积值。
PRODUCT(1, 2, 3, 4)
>>24
SqrtPi SqrtPi(数值)
返回某数与 PI 的乘积的平方根
SqrtPi(3)
>>3.069980124
SUMSQ sumQq(数值, ...)
返回参数的平方和
SUMSQ(1, 2)
>>5


DEC2BIN ★ DEC2BIN(数值[, 位数])
十进制转二进制
DEC2BIN(100)
>>
DEC2OCT ★ DEC2OCT(数值[, 位数])
十进制转八进制
DEC2OCT(100)
>>
DEC2HEX ★ DEC2HEX(数值[, 位数])
十进制转十六进制
DEC2HEX(100)
>>
HEX2BIN ★ HEX2BIN(数值[, 位数])
十六进制转二进制
HEX2BIN(100)
>>
HEX2OCT ★ HEX2OCT(数值[, 位数])
十六进制转八进制
HEX2OCT(100)
>>
HEX2DEC ★ HEX2DEC(数值)
十六进制转十进制
HEX2DEC(100)
>>
OCT2BIN ★ OCT2BIN(数值[, 位数])
八进制转二进制
OCT2BIN(100)
>>
OCT2DEC ★ OCT2DEC(数值)
八进制转十进制
OCT2DEC(100)
>>
OCT2HEX ★ OCT2HEX(数值[, 位数])
八进制转十六进制
OCT2HEX(100)
>>
BIN2OCT ★ BIN2OCT(数值[, 位数])
二进制转八进制
BIN2OCT(100)
>>
BIN2DEC ★ BIN2DEC(数值)
二进制转十进制
BIN2DEC(100)
>>
BIN2HEX ★ BIN2HEX(数值[, 位数])
二进制转十六进制
BIN2HEX(100)
>>

文本函数

函数名 说明 示例
ASC ★ asc(字符串)
将字符串内的全角英文字母更改为半角字符
asc('abcABC123')
>>abcABC123
JIS ★
WIDECHAR ★
jis(字符串)
将字符串中的半角英文字符更改为全角字符
jis('abcABC123')
>>abcABC123
CHAR ★ CHAR(数值)
返回由代码数值指定的字符
char(49)
>>1
CLEAN ★ clean(字符串)
删除文本中所有打印不出的字符
clean('\r112\t')
>>112
CODE ★ code(字符串)
返回文本字符串中第一个字符的数值代码
CODE("1")
>>49
CONCATENATE ★ concatenate(字符串1, ...)
将若干文本项合并到一个文本项中
CONCATENATE('tt', '11')
>>tt11
EXACT ★ exact(字符串1, 字符串2)
检查两个文本值是否完全相同
EXACT("11", "22")
>>false
FIND ★ ▲ find(要查找的字符串, 被查找的字符串[, 开始位置])
在一文本值内查找另一文本值(区分大小写)
FIND("11", "12221122")
>>5
LEFT ★ left(字符串[, 字符个数])
返回文本值最左边的字符
LEFT('123222', 3)
>>123
LEN ★ len(字符串)
返回文本字符串中的字符个数
LEN('123222')
>>6
MID ★ ▲ mid(字符串, 开始位置, 字符个数)
从文本字符串中的指定位置起返回特定个数的字符
MID('ABCDEF', 2, 3)
>>BCD
PROPER ★ proper(字符串)
将文本值中每一个单词的首字母设置为大写
PROPER('abc abc')
>>Abc Abc
REPLACE ★ ▲ replace(原字符串, 开始位置, 字符个数, 新字符串)
replace(原字符串, 要替换的字符串, 新字符串)
替换文本内的字符
REPLACE("abccd", 2, 3, "2")
>>a2d
REPLACE("abccd", "bc", "2")
>>a2cd
REPT ★ rept(字符串, 重复次数)
按给定次数重复文本
REPT("q", 3)
>>qqq
RIGHT ★ right(字符串[, 字符个数])
返回文本值最右边的字符
RIGHT("123q", 3)
>>23q
RMB ★ RMB(数值)
将数值转换为大写数值文本
RMB(12.3)
>>壹拾贰元叁角
SEARCH ★ ▲ search(要找的字符串, 被查找的字符串[, 开始位置])
在一文本值中查找另一文本值(不区分大小写)
SEARCH("aa", "abbAaddd")
>>4
SUBSTITUTE ★ substitute(字符串, 原字符串, 新字符串[, 替换序号])
在文本字符串中以新文本替换旧文本
SUBSTITUTE("ababcc", "ab", "12")
>>1212cc
T ★ t(数值)
将参数转换为文本
T('123')
>>123
TEXT ★ text(数值, 数值格式)
设置数值的格式并将数值转换为文本
TEXT(123, "0.00")
>>123.00
TRIM ★ trim(字符串)
删除文本中的空格
TRIM(" 123 123 ")
>>123 123
LOWER ★
TOLOWER ★
lower(字符串)
tolower(字符串)
将文本转换为小写形式
LOWER('ABC')
>>abc
UPPER ★
TOUPPER ★
upper(字符串)
toupper(字符串)
将文本转换为大写形式
UPPER("abc")
>>ABC
VALUE ★ value(字符串)
将文本参数转换为数值
VALUE("123")
>>123

日期与时间函数

函数名 说明 示例
NOW now()
返回当前日期和时间的序列号
NOW()
>>2017-01-07 11:00:00
TODAY today()
返回今天日期的序列号
TODAY()
>>2017-01-07
DateValue ★ DateValue(字符串)
将文本格式的日期转换为序列号
DateValue("2017-01-02")
>>2017-01-02
TimeValue ★ TimeValue(字符串)
将文本格式的时间转换为序列号
TimeValue("12:12:12")
>>12:12:12
DATE ★ date(年, 月, 日[, 时[, 分[, 秒]]])
返回特定日期的序列号
DATE(2016, 1, 1)
>>2016-01-01
TIME ★ time(时, 分, 秒)
返回特定时间的序列号
TIME(12, 13, 14)
>>12:13:14
YEAR ★ year(日期)
将序列号转换为年
YEAR(NOW())
>>2017
MONTH ★ month(日期)
将序列号转换为月
MONTH(NOW())
>>1
DAY ★ day(日期)
将序列号转换为月份中的日
DAY(NOW())
>>7
HOUR ★ hour(日期)
将序列号转换为小时
HOUR(NOW())
>>11
MINUTE ★ minute(日期)
将序列号转换为分钟
MINUTE(NOW())
>>12
SECOND ★ second(日期)
将序列号转换为秒
SECOND(NOW())
>>34
WEEKDAY ★ second(日期)
将序列号转换为星期几
WEEKDAY(date(2017, 1, 7))
>>7
dateDIF dateDif(开始日期, 结束日期, 类型Y/M/D/YD/MD/YM)
返回两个日期之间的相隔天数
dateDIF("1975-1-30", "2017-1-7", "Y")
>>41
DAYS360 days360(开始日期, 结束日期[, 选项0/1])
以一年 360 天为基准计算两个日期间的天数
DAYS360('1975-1-30', '2017-1-7')
>>15097
EDATE eDate(开始日期, 月数)
返回用于表示开始日期之前或之后月数的日期的序列号
EDATE("2012-1-31", 32)
>>2014-09-30
EOMONTH eoMonth(开始日期, 月数)
返回指定月数之前或之后的月份的最后一天的序列号
EOMONTH("2012-2-1", 32)
>>2014-10-31
netWorkdays netWorkdays(开始日期, 结束日期[, 假日])
返回两个日期之间的全部工作日数
netWorkdays("2012-1-1", "2013-1-1")
>>262
workDay workday(开始日期, 天数[, 假日])
返回指定的若干个工作日之前或之后的日期的序列号
workDay("2012-1-2", 145)
>>2012-07-23
WEEKNUM weekNum(日期[, 类型:1/2])
将序列号转换为一年中相应的周数
WEEKNUM("2016-1-3")
>>2
扩展函数
函数名 说明 示例
AddYears ★ AddYears(时间, 数值)
增加年。
AddMonths ★ AddMonths(时间, 数值)
增加月。
AddDays ★ AddDays(时间, 数值)
增加日。
AddHours ★ AddHours(时间, 数值)
增加小时。
AddMinutes ★ AddMinutes(时间, 数值)
增加分钟。
AddSeconds ★ AddSeconds(时间, 数值)
增加秒。
DateValue ★ DateValue(值, 数值)
转成时间
DateValue(文本/数值, 0)
解析,自动匹配到当前日期相近的日期
DateValue(文本, 1)
解析年月日文本
DateValue(数值, 2)
转成日期,Excel数值
DateValue(数值, 3)
转成日期,时间戳(毫秒)
DateValue(值数值, 4)
转成日期,时间戳(秒)
Timestamp ★ Timestamp(值[, 数值=0])
转成时间戳,默认为毫秒
Timestamp(时间, 0)
转成时间戳(毫秒)
Timestamp(时间, 1)
转成时间戳(秒)

注:UseLocalTime属性影响 DateValue/Timestamp 转换,设置true,直接转成本地时间。

统计函数

函数名 说明 示例
MAX max(数值, ...)
返回参数列表中的最大值
max(1, 2, 3, 4, 2, 2, 1, 4)
>>4
MEDIAN median(数值, ...)
返回给定数值的中值
MEDIAN(1, 2, 3, 4, 2, 2, 1, 4)
>>2
MIN min(数值, ...)
返回参数列表中的最小值
MIN(1, 2, 3, 4, 2, 2, 1, 4)
>>1
QUARTILE quartile(数值, 四分位:0-4)
返回数据集的四分位数
QUARTILE({1, 2, 3, 4, 2, 2, 1, 4}, 0)
>>1
MODE mode(数值, ...)
返回在数组中出现频率最多的数值
MODE(1, 2, 3, 4, 2, 2, 1, 4)
>>2
LARGE ▲ large(数组, K)
返回数据集中第 k 个最大值
LARGE({1, 2, 3, 4, 2, 2, 1, 4}, 3)
>>3
SMALL ▲ small(数值, K)
返回数据集中第 k 个最小值
SMALL({1, 2, 3, 4, 2, 2, 1, 4}, 3)
>>2
PERCENTILE percentile(数值, K)
返回区域中的第 k 个百分位值
PERCENTILE({1, 2, 3, 4, 2, 2, 1, 4}, 0.4)
>>2
PERCENTRANK percentRank(数值, K)
返回数据集中值的百分比排位
PERCENTRANK({1, 2, 3, 4, 2, 2, 1, 4}, 3)
>>0.714
AVERAGE average(数值, ...)
返回参数的平均值
AVERAGE(1, 2, 3, 4, 2, 2, 1, 4)
>>2.375
averageIf averageIf({数值, ...}, 条件[, {值1, ...}])
返回参数的平均值
averageIf({1, 2, 3, 4, 2, 2, 1, 4}, '>1')
>>2.833333333
GEOMEAN geoMean(数值, ...)
返回正数数组或区域的几何平均值
GEOMEAN(1, 2, 3, 4)
>>2.213363839
HARMEAN harMean(数值, ...)
返回数据集合的调和平均值
HARMEAN(1, 2, 3, 4)
>>1.92
COUNT count(数值, ...)
计算参数列表中数值的个数
COUNT(1, 2, 3, 4, 2, 2, 1, 4)
>>8
countIf countIf({数值, ...}, 条件[, {值1, ...}])
计算参数列表中数值的个数
countIf({1, 2, 3, 4, 2, 2, 1, 4}, '>1')
>>6
SUM sum(数值, ...)
返回所有数值之和。
SUM(1, 2, 3, 4)
>>10
sumIf sumIf({数值, ...}, 条件[, {值1, ...}])
返回所有数值之和
sumIf({1, 2, 3, 4, 2, 2, 1, 4}, '>1')
>>17
AVEDEV aveDev(数值, ...)
返回数据点与其平均值的绝对偏差的平均值
AVEDEV(1, 2, 3, 4, 2, 2, 1, 4)
>>0.96875
STDEV stDev(数值, ...)
基于样本估算标准偏差
STDEV(1, 2, 3, 4, 2, 2, 1, 4)
>>1.1877349391654208
STDEVP stDevp(数值, ...)
计算基于整个样本总体的标准偏差
STDEVP(1, 2, 3, 4, 2, 2, 1, 4)
>>1.1110243021644486
DEVSQ devSq(数值, ...)
返回偏差的平方和
DEVSQ(1, 2, 3, 4, 2, 2, 1, 4)
>>9.875
VAR var(数值, ...)
基于样本估算方差
VAR(1, 2, 3, 4, 2, 2, 1, 4)
>>1.4107142857142858
VARP varp(数值, ...)
基于整个样本总体计算方差
VARP(1, 2, 3, 4, 2, 2, 1, 4)
>>1.234375
normDist normDist(数值, 算术平均值, 标准偏差, 返回类型:0/1)
返回正态累积分布
normDist(3, 8, 4, 1)
>>0.105649774
normInv normInv(分布概率, 算术平均值, 标准偏差)
返回反正态累积分布
normInv(0.8, 8, 3)
>>10.5248637
NormSDist normSDist(数值)
返回标准正态累积分布函数, 该分布的平均值为 0, 标准偏差为 1。
NORMSDist(1)
>>0.841344746
normSInv normSInv(数值)
返回反标准正态累积分布
normSInv(0.3)
>>-0.524400513
BetaDist BetaDist(数值, 分布参数α, 分布参数β)
返回 Beta 累积分布函数
BetaDist(0.5, 11, 22)
>>0.97494877
BetaInv BetaInv(数值, 分布参数α, 分布参数β)
返回指定 Beta 分布的累积分布函数的反函数
BetaInv(0.5, 23, 45)
>>0.336640759
binomDist binomDist(试验成功次数, 试验次数, 成功概率, 返回类型:0/1)
返回一元二项式分布概率
binomDist(12, 45, 0.5, 0)
>>0.000817409
exponDist exponDist(函数值, 参数值, 返回类型:0/1)
返回指数分布
exponDist(3, 1, 0)
>>0.049787068
FDist fDist(数值X, 分子自由度, 分母自由度)
返回 F 概率分布
FDist(0.4, 2, 3)
>>0.701465776
FInv fInv(分布概率, 分子自由度, 分母自由度)
返回 F 概率分布的反函数
FInv(0.7, 2, 3)
>>0.402651432
FISHER fisher(数值)
返回点 x 的 Fisher 变换。该变换生成一个正态分布而非偏斜的函数
FISHER(0.68)
>>0.8291140383
fisherInv fisherInv(数值)
返回 Fisher 变换的反函数值。
fisherInv(0.6)
>>0.537049567
gammaDist gammaDist(数值, 分布参数α, 分布参数β, 返回类型:0/1)
返回 γ 分布
gammaDist(0.5, 3, 4, 0)
>>0.001723627
gammaInv gammaInv(分布概率, 分布参数α, 分布参数β)
返回 γ 累积分布函数的反函数
gammaInv(0.2, 3, 4)
>>6.140176811
GAMMALN gammaLn(数值)
返回 γ 的自然对数
GAMMALN(4)
>>1.791759469
hypgeomDist hypgeomDist(样本成功次数, 样本容量, 样本总体成功次数, 样本总体容量)
返回超几何分布
hypgeomDist(23, 45, 45, 100)
>>0.08715016
logInv logInv(分布概率, 算法平均数, 标准偏差)
返回 x 的对数累积分布函数的反函数
logInv(0.1, 45, 33)
>>15.01122624
LognormDist lognormDist(数值, 算法平均数, 标准偏差)
返回反对数正态分布
lognormDist(15, 23, 45)
>>0.326019201
negbinomDist negbinomDist(失败次数, 成功极限次数, 成功概率)
返回负二项式分布
negbinomDist(23, 45, 0.7)
>>0.053463314
POISSON poisson(数值, 算法平均数, 返回类型:0/1)
返回 Poisson 分布
POISSON(23, 23, 0)
>>0.082884384
TDist tDist(数值, 自由度, 返回类型:1/2)
返回学生的 t 分布
TDist(1.2, 24, 1)
>>0.120925677
TInv TInv(分布概率, 自由度)
返回学生的 t 分布的反分布
TInv(0.12, 23)
>>1.614756561
WEIBULL weibull(数值, 分布参数α, 分布参数β, 返回类型:0/1)
返回 Weibull 分布
WEIBULL(1, 2, 3, 1)
>>0.105160683

查找引用

函数名 说明 示例
VLookUp ★ ▲ VLookUp({数组, ...}, 值, {列索引}[, 模糊匹配:0/1])
纵向查找函数。模糊匹配默认1
VLookUp ★ ▲ VLookUp({Json, ...}, 公式字符串, 属性名)
JSON数组查找函数。

增加函数 类C#方法

函数名 说明 示例
UrlEncode ★ UrlEncode(文本)
对 URL 字符串进行编码。
UrlDecode ★ UrlEncode(文本)
将 URL 编码的字符串转换为已解码的字符串。
HtmlEncode ★ HtmlEncode(文本)
将字符串转换为 HTML 编码的字符串。
HtmlDecode ★ HtmlDecode(文本)
将HTML 编码的字符串转解码。
Base64ToText ★ Base64ToText(文本[, 编码类型])
将Base64转换为字符串。
Base64UrlToText ★ Base64UrlToText(文本[, 编码类型])
将Url类型的Base64 转换为字符串。
TextToBase64 ★ TextToBase64(文本[, 编码类型])
将字符串转换为Base64字符串。
TextToBase64Url ★ TextToBase64Url(文本[, 编码类型])
将字符串 转换为Url类型的Base64 字符串。
Regex ★ ▲ Regex(文本, 匹配文本])
并返回匹配的字符串。
RegexRepalce ★ RegexRepalce(文本, 匹配文本, 替换文本)
匹配替换字符串。
IsRegex ★
IsMatch ★
IsRegex(文本, 匹配文本)
IsMatch(文本, 匹配文本)
判断是否匹配。
Guid Guid()
生成Guid字符串。
Md5 ★ Md5(文本[, 编码类型])
返回Md5的Hash字符串。
Sha1 ★ Sha1(文本[, 编码类型])
返回Sha1的Hash字符串。
Sha256 ★ Sha256(文本[, 编码类型])
返回Sha256的Hash字符串。
Sha512 ★ Sha512(文本[, 编码类型])
返回Sha512的Hash字符串。
Crc32 ★ Crc32(文本[, 编码类型])
返回Crc32的Hash字符串。
HmacMd5 ★ HmacMd5(文本, secret[, 编码类型])
返回HmacMd5的Hash字符串。
HmacSha1 ★ HmacSha1(文本, secret[, 编码类型])
返回HmacSha1的Hash字符串。
HmacSha256 ★ HmacSha256(文本, secret[, 编码类型])
返回HmacSha256的Hash字符串。
HmacSha512 ★ HmacSha512(文本, secret[, 编码类型])
返回HmacSha512的Hash字符串。
TrimStart ★
LTrim ★
TrimStart(文本)
LTrim(文本)
LTrim(文本[, 字符集])
消空字符串左边。
TrimEnd ★
RTrim ★
TrimEnd(文本)
RTrim(文本)
RTrim(文本, 字符集)
消空字符串右边。
IndexOf ★ ▲ IndexOf(文本, 查找文本[, 开始位置[, 索引]])
查找字符串位置。
LastIndexOf ★ ▲ LastIndexOf(文本, 查找文本[, 开始位置[, 索引]])
查找字符串位置。
Split ★ Split(文本, 分隔符)
生成数组
Split(文本, 分隔符, 索引)
返回分割后索引指向的字符串。
Join ★ Join(文本1, 文本2....)
合并字符串。
Substring ★ ▲ Substring(文本, 位置)
Substring(文本, 位置, 数量)
切割字符串。
StartsWith ★ StartsWith(文本, 开始文本[, 是否忽略大小写:1/0])
确定此字符串实例的开头是否与指定的字符串匹配。
EndsWith ★ EndsWith(文本, 开始文本[, 是否忽略大小写:1/0])
确定使用指定的比较选项进行比较时此字符串实例的结尾是否与指定的字符串匹配。
IsNullOrEmpty ★ IsNullOrEmpty(文本)
指示指定的字符串是 null 还是 空字符串。
IsNullOrWhiteSpace ★ IsNullOrWhiteSpace(文本)
指示指定的字符串是 null、空还是仅由空白字符组成。
RemoveStart ★ RemoveStart(文本, 左边文本[, 忽略大小写])
匹配左边,成功则去除左边字符串。
RemoveEnd ★ RemoveEnd(文本, 右边文本[, 忽略大小写])
匹配右边,成功则去除右边字符串。
Json ★ json(文本)
动态json查询。
Apache License Version 2.0, January 2004 http://www.apache.org/licenses/ TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION 1. Definitions. "License" shall mean the terms and conditions for use, reproduction, and distribution as defined by Sections 1 through 9 of this document. "Licensor" shall mean the copyright owner or entity authorized by the copyright owner that is granting the License. "Legal Entity" shall mean the union of the acting entity and all other entities that control, are controlled by, or are under common control with that entity. For the purposes of this definition, "control" means (i) the power, direct or indirect, to cause the direction or management of such entity, whether by contract or otherwise, or (ii) ownership of fifty percent (50%) or more of the outstanding shares, or (iii) beneficial ownership of such entity. "You" (or "Your") shall mean an individual or Legal Entity exercising permissions granted by this License. "Source" form shall mean the preferred form for making modifications, including but not limited to software source code, documentation source, and configuration files. "Object" form shall mean any form resulting from mechanical transformation or translation of a Source form, including but not limited to compiled object code, generated documentation, and conversions to other media types. "Work" shall mean the work of authorship, whether in Source or Object form, made available under the License, as indicated by a copyright notice that is included in or attached to the work (an example is provided in the Appendix below). "Derivative Works" shall mean any work, whether in Source or Object form, that is based on (or derived from) the Work and for which the editorial revisions, annotations, elaborations, or other modifications represent, as a whole, an original work of authorship. For the purposes of this License, Derivative Works shall not include works that remain separable from, or merely link (or bind by name) to the interfaces of, the Work and Derivative Works thereof. "Contribution" shall mean any work of authorship, including the original version of the Work and any modifications or additions to that Work or Derivative Works thereof, that is intentionally submitted to Licensor for inclusion in the Work by the copyright owner or by an individual or Legal Entity authorized to submit on behalf of the copyright owner. For the purposes of this definition, "submitted" means any form of electronic, verbal, or written communication sent to the Licensor or its representatives, including but not limited to communication on electronic mailing lists, source code control systems, and issue tracking systems that are managed by, or on behalf of, the Licensor for the purpose of discussing and improving the Work, but excluding communication that is conspicuously marked or otherwise designated in writing by the copyright owner as "Not a Contribution." "Contributor" shall mean Licensor and any individual or Legal Entity on behalf of whom a Contribution has been received by Licensor and subsequently incorporated within the Work. 2. Grant of Copyright License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable copyright license to reproduce, prepare Derivative Works of, publicly display, publicly perform, sublicense, and distribute the Work and such Derivative Works in Source or Object form. 3. Grant of Patent License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable (except as stated in this section) patent license to make, have made, use, offer to sell, sell, import, and otherwise transfer the Work, where such license applies only to those patent claims licensable by such Contributor that are necessarily infringed by their Contribution(s) alone or by combination of their Contribution(s) with the Work to which such Contribution(s) was submitted. If You institute patent litigation against any entity (including a cross-claim or counterclaim in a lawsuit) alleging that the Work or a Contribution incorporated within the Work constitutes direct or contributory patent infringement, then any patent licenses granted to You under this License for that Work shall terminate as of the date such litigation is filed. 4. Redistribution. You may reproduce and distribute copies of the Work or Derivative Works thereof in any medium, with or without modifications, and in Source or Object form, provided that You meet the following conditions: (a) You must give any other recipients of the Work or Derivative Works a copy of this License; and (b) You must cause any modified files to carry prominent notices stating that You changed the files; and (c) You must retain, in the Source form of any Derivative Works that You distribute, all copyright, patent, trademark, and attribution notices from the Source form of the Work, excluding those notices that do not pertain to any part of the Derivative Works; and (d) If the Work includes a "NOTICE" text file as part of its distribution, then any Derivative Works that You distribute must include a readable copy of the attribution notices contained within such NOTICE file, excluding those notices that do not pertain to any part of the Derivative Works, in at least one of the following places: within a NOTICE text file distributed as part of the Derivative Works; within the Source form or documentation, if provided along with the Derivative Works; or, within a display generated by the Derivative Works, if and wherever such third-party notices normally appear. The contents of the NOTICE file are for informational purposes only and do not modify the License. You may add Your own attribution notices within Derivative Works that You distribute, alongside or as an addendum to the NOTICE text from the Work, provided that such additional attribution notices cannot be construed as modifying the License. You may add Your own copyright statement to Your modifications and may provide additional or different license terms and conditions for use, reproduction, or distribution of Your modifications, or for any such Derivative Works as a whole, provided Your use, reproduction, and distribution of the Work otherwise complies with the conditions stated in this License. 5. Submission of Contributions. Unless You explicitly state otherwise, any Contribution intentionally submitted for inclusion in the Work by You to the Licensor shall be under the terms and conditions of this License, without any additional terms or conditions. Notwithstanding the above, nothing herein shall supersede or modify the terms of any separate license agreement you may have executed with Licensor regarding such Contributions. 6. Trademarks. This License does not grant permission to use the trade names, trademarks, service marks, or product names of the Licensor, except as required for reasonable and customary use in describing the origin of the Work and reproducing the content of the NOTICE file. 7. Disclaimer of Warranty. Unless required by applicable law or agreed to in writing, Licensor provides the Work (and each Contributor provides its Contributions) on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied, including, without limitation, any warranties or conditions of TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or FITNESS FOR A PARTICULAR PURPOSE. You are solely responsible for determining the appropriateness of using or redistributing the Work and assume any risks associated with Your exercise of permissions under this License. 8. Limitation of Liability. In no event and under no legal theory, whether in tort (including negligence), contract, or otherwise, unless required by applicable law (such as deliberate and grossly negligent acts) or agreed to in writing, shall any Contributor be liable to You for damages, including any direct, indirect, special, incidental, or consequential damages of any character arising as a result of this License or out of the use or inability to use the Work (including but not limited to damages for loss of goodwill, work stoppage, computer failure or malfunction, or any and all other commercial damages or losses), even if such Contributor has been advised of the possibility of such damages. 9. Accepting Warranty or Additional Liability. While redistributing the Work or Derivative Works thereof, You may choose to offer, and charge a fee for, acceptance of support, warranty, indemnity, or other liability obligations and/or rights consistent with this License. However, in accepting such obligations, You may act only on Your own behalf and on Your sole responsibility, not on behalf of any other Contributor, and only if You agree to indemnify, defend, and hold each Contributor harmless for any liability incurred by, or claims asserted against, such Contributor by reason of your accepting any such warranty or additional liability. END OF TERMS AND CONDITIONS APPENDIX: How to apply the Apache License to your work. To apply the Apache License to your work, attach the following boilerplate notice, with the fields enclosed by brackets "{}" replaced with your own identifying information. (Don't include the brackets!) The text should be enclosed in the appropriate comment syntax for the file format. We also recommend that a file or class name and description of purpose be included on the same "printed page" as the copyright notice for easier identification within third-party archives. Copyright 2016 ToolGood.com Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

简介

ToolGood.Algorithm是一个功能强大、轻量级、兼容Excel公式的算法类库,旨在提高开发人员在不同业务场景中的生产力。 展开 收起
C# 等 3 种语言
Apache-2.0
取消

发行版

暂无发行版

贡献者

全部

近期动态

加载更多
不能加载更多了
C#
1
https://gitee.com/toolgood/ToolGood.Algorithm.git
git@gitee.com:toolgood/ToolGood.Algorithm.git
toolgood
ToolGood.Algorithm
ToolGood.Algorithm
master

搜索帮助