【原创】Excel之：EXCEL巴科斯诺尔范式

faunus

1、公式

[size=14.399999618530273px]==tmp==
澄清一些概念
引用
单元格引用
区域引用
向量引用
行向量引用
列向量引用
三维表区域引用
注：以下表是在excel2000的基础上整理的，仍然存在很多问题，对于各种函数的引用认知，正在学习中，请等待一段时间。欢迎指正...
===end of tmp====

公式 ::= "=" <表达式>
数组公式? ::= "=" "{" <表达式> "}"
        表达式 ::= <基础值> | <单元格引用> | <函数> | ...

三维表引用? ::= <表名><联合操作符?><表><单元格引用>
带前缀引用? ::= (<工作簿> | <表> | ε ) <引用>    注1
        工作簿 ::= "["路径/文件名.xls"]" <表>
                表 ::= <表名>"!"
                        表名 ::= <工作表名称>
        引用 ::= <单元格引用> | <区域引用?>
                区域引用? ::= <区域> | <交集> | <联合> | <向量>
                单元格引用 ::= ["$"]<列>["$"]<行>
                        行 ::= x | x ε Int,  0 < x ≤ 65536    注2
                        列 ::= [A-Z] | [A-H][A-Z] | I[A-Z]    注3



注1：ε表示""或NULL
注2：行值范围在[1..65536]（EXCEL2003）
注3：列值范围在[1..256]由'A' 到 'Z'的字符来表示（EXCEL2003）

2、引用风格[size=14.399999618530273px]相对/绝对引用
帮助中都有描述，不写了。

R1C1引用
帮助中都有描述，不写了。

[size=14.399999618530273px]3、区域引用操作
[size=14.399999618530273px]区域 ::= <单元格引用><区域操作符?><单元格引用>
交集 ::= <区域引用?><交集运算符?><区域引用?>
联合 ::= <区域引用?><联合运算符?><区域引用?>
向量 ::= <列向量区域?> | <行向量区域?>
        列向量区域? ::= <列><区域操作符?><列>(注1)
        行向量区域? ::= <行><区域操作符?><行>(注2)

注1：第一列必须≤第二列.
注2：第一行必须≤第二行.

[size=14.399999618530273px]4、运算符
[size=14.399999618530273px]运算符 ::=  <比较运算符?> | <数术运算符?> | <文本连接运算符?> | <引用运算符?>
        比较运算符? ::= ">=" | "<=" | "≥" | "≤" | "<>" | "="
        数术运算符? ::= <加减运算符?> | <负号运算符?> | <乘方运算符?> | <百分比运算符?>
                加减运算符? ::= "+" | "-"
                负号运算符? ::= "-"
                乘方运算符? ::= "^"
                百分比运算符? ::= "%"
        文本连接运算符? ::= "&"
        引用运算符? ::= <区域运算符?> | <联合运算符?> | <交集运算符?>
                区域运算符? ::= ":"
                联合运算符? ::= ","
                交集运算符? ::= " "

[size=14.399999618530273px]5、基础值
[size=14.399999618530273px]基础值 ::= 数值 | 逻辑值 | 字符串 | 日期

[size=14.399999618530273px]6、函数函数 ::= (Function_LOGICAL | Function_FINANCIAL | Function_STATISTICAL | Function_MATHANDTRIG |

Function_DATABASE | Function_DATEANDTIME | Function_ENGINEERING | Function_TEXT |  Function_INFORMATION | Function_LOOKUPANDREFERENCE)

Function_LOOKUPANDREFERENCE ::= ("ADDRESS" | "AREAS" | "CHOOSE" | "COLUMN" | "COLUMNS" | "HLOOKUP" | "HYPERLINK" | "INDEX" | "INDIRECT" | "LOOKUP" | "MATCH" | "OFFSET" | "ROW" | "ROWS" | "TRANSPOSE" | "VLOOKUP")

Function_INFORMATION ::= ("CELL" | "COUNTBLANK" | "ERROR.TYPE" | "INFO" | "ISBLANK" | "ISERR" | "ISERROR" | "ISEVEN" | "ISLOGICAL" | "ISNA" | "ISNONTEXT" | "ISNUMBER" | "ISODD" | "ISREF" | "ISTEXT" | "N" | "NA" | "TYPE")

Function_FINANCIAL ::= ("ACCRINT" | "ACCRINTM" | "AMORDEGRC" | "AMORLINC" | "COUPDAYBS" | "COUPDAYS" | "COUPDAYSNC" | "COUPNCD" | "COUPNUM" | "COUPPCD" | "CUMIPMT" | "CUMPRINC" | "DB" | "DDB" | "DISC" | "DOLLARDE" | "DOLLARFR" | "DURATION" | "EFFECT" | "FV" | "FVSCHEDULE" | "INTRATE" | "IPMT" | "IRR" | "MDURATION" | "MIRR" | "NOMINAL" | "NPER" | "NPV" | "ODDFPRICE" | "ODDFYIELD" | "ODDLPRICE" | "ODDLYIELD" | "PMT" | "PPMT" | "PRICE" | "PRICEDISC" | "PRICEMAT" | "PV" | "RATE" | "RECEIVED" | "SLN" | "SYD" | "TBILLEQ" | "TBILLPRICE" | "TBILLYIELD" | "VDB" | "XIRR" | "XNPV" | "YIELD" | "YIELDDISC" | "YIELDMAT")

Function_ENGINEERING ::= ("BESSELI" | "BESSELJ" | "BESSELK" | "BESSELY" | "BIN2DEC" | "BIN2HEX" | "BIN2OCT" | "COMPLEX" | "CONVERT" | "DEC2BIN" | "DEC2HEX" | "DEC2OCT" | "DELTA" | "ERF" | "ERFC" | "GESTEP" | "HEX2BIN" | "HEX2DEC" | "HEX2OCT" | "IMABS" | "IMAGINARY" | "IMARGUMENT" | "IMCONJUGATE" | "IMCOS" | "IMDIV" | "IMEXP" | "IMLN" | "IMLOG10" | "IMLOG2" | "IMPOWER" | "IMPRODUCT" | "IMREAL" | "IMSIN" | "IMSQRT" | "IMSUB" | "IMSUM" | "OCT2BIN" | "OCT2DEC" | "OCT2HEX")

Function_DATEANDTIME ::= ("DATE" | "DATEVALUE" | "DAY" | "DAYS360" | "EDATE" | "EOMONTH" | "HOUR" | "MINUTE" | "MONTH" | "NETWORKDAYS" | "NOW" | "SECOND" | "TIME" | "TIMEVALUE" | "TODAY" | "WEEKDAY" | "WORKDAY" | "YEAR" | "YEARFRAC")

Function_DATABASE ::= ("DAVERAGE" | "DCOUNT" | "DCOUNTA" | "DGET" | "DMAX" | "DMIN" | "DPRODUCT" | "DSTDEV" | "DSTDEVP" | "DSUM" | "DVAR" | "DVARP" | "GETPIVOTDATA")

Function_MATHANDTRIG ::= ("ABS" | "ACOS" | "ACOSH" | "ASIN" | "ASINH" | "ATAN" | "ATAN2" | "ATANH" | "CEILING" | "COMBIN" | "COS" | "COSH" | "COUNTIF" | "DEGREES" | "EVEN" | "EXP" | "FACT" | "FACTDOUBLE" | "FLOOR" | "GCD" | "INT" | "LCM" | "LN" | "LOG" | "LOG10" | "MDETERM" | "MINVERSE" | "MMULT" | "MOD" | "MROUND" | "MULTINOMIAL" | "ODD" | "PI" | "POWER" | "PRODUCT" | "QUOTIENT" | "RADIANS" | "RAND" | "RANDBETWEEN" | "ROMAN" | "ROUND" | "ROUNDDOWN" | "ROUNDUP" | "SERIESSUM" | "SIGN" | "SIN" | "SINH" | "SQRT" | "SQRTPI" | "SUBTOTAL" | "SUMIF"+")" | "("+"SUM" | "SUMPRODUCT" | "SUMSQ" | "SUMX2MY2" | "SUMX2PY2" | "SUMXMY2" | "TAN" | "TANH" | "TRUN")

Function_STATISTICAL ::= ("AVEDEV" | "AVERAGE" | "AVERAGEA" | "BETADIST" | "BETAINV" | "BINOMDIST" | "CHIDIST" | "CHIINV" | "CHITEST" | "CONFIDENCE" | "CORREL" | "COUNT" | "COUNTA" | "COVAR" | "CRITBINOM" | "DEVSQ" | "EXPONDIST" | "FDIST" | "FINV" | "FISHER" | "FISHERINV" | "FORECAST" | "FREQUENCY" | "FTEST" | "GAMMADIST" | "GAMMAINV" | "GAMMALN" | "GEOMEAN" | "GROWTH" | "HARMEAN" | "HYPGEOMDIST" | "INTERCEPT" | "KURT" | "LARGE" | "LINEST" | "LOGEST" | "LOGINV" | "LOGNORMDIST" | "MAX" | "MAXA" | "MEDIAN" | "MIN" | "MINA" | "MODE" | "NEGBINOMDIST" | "NORMDIST" | "NORMINV" | "NORMSDIST" | "NORMSINV" | "PEARSON" | "PERCENTILE" | "PERCENTRANK" | "PERMUT" | "POISSON" | "PROB" | "QUARTILE" | "RANK" | "RSQ" | "SKEW" | "SLOPE" | "SMALL" | "STANDARDIZE" | "STDEV" | "STDEVA" | "STDEVP" | "STDEVPA" | "STEYX" | "TDIST" | "TINV" | "TREND" | "TRIMMEAN" | "TTEST" | "VAR" | "VARA" | "VARP" | "VARPA" | "WEIBULL" | "ZTEST")

Function_TEXT ::= ("CHAR" | "CLEAN" | "CODE" | "CONCATENATE" | "DOLLAR" | "EXACT" | "FIND" | "FIXED" | "LEFT" | "LEN" | "LOWER" | "MID" | "PROPER" | "REPLACE" | "REPT" | "RIGHT" | "SEARCH" | "SUBSTITUTE" | "T" | "TEXT" | "TRIM" | "UPPER" | "VALUE")

Function_LOGICAL ::= ("IF" | "AND" | "OR" | "NOT" | "TRUE" | "FALSE");


[size=14.399999618530273px]

faunus

[size=14.399999618530273px]7、List
[size=14.399999618530273px]1. TRANSLATION-UNIT --> FORMULA
2. FORMULA --> SCALAR-FORMULA
3. FORMULA --> ARRAY-FORMULA
4. SCALAR-FORMULA --> '=' PRIMARY-EXPRESSION
5. ARRAY-FORMULA --> '=' '{' PRIMARY-EXPRESSION '}'
6. PRIMARY-EXPRESSION --> EXPRESSION | '(' EXPRESSION ')'
7. EXPRESSION --> LOGICAL-EXPRESSION
8. LOGICAL-EXPRESSION --> CONCAT-EXPRESSION|
LOGICAL-EXPRESSION '='
CONCAT-EXPRESSION |
LOGICAL-EXPRESSION '<'
CONCAT-EXPRESSION |
LOGICAL-EXPRESSION '<='
CONCAT-EXPRESSION |
LOGICAL-EXPRESSION '>'
CONCAT-EXPRESSION |
LOGICAL-EXPRESSION '>='
CONCAT-EXPRESSION |
LOGICAL-EXPRESSION '<>'
CONCAT-EXPRESSION |
LOGICAL-EXPRESSION '!='
CONCAT-EXPRESSION
9. CONCAT-EXPRESSION --> ADDITIVE-EXPRESSION |CONCAT-EXPRESSION '&'ADDITIVE-EXPRESSION
10. ADDITIVE-EXPRESSION --> MULTIPLICATIVE-EXPRESSION|
ADDITIVE-EXPRESSION '+'
MULTIPLICATIVE-EXPRESSION |
ADDITIVE-EXPRESSION '-'
MULTIPLICATIVE-EXPRESSION
11. MULTIPLICATIVE-EXPRESSION --> EXPONENTIATION-EXPRESSION|
MULTIPLICATIVE-EXPRESSION '*'
EXPONENTIATION-EXPRESSION |
MULTIPLICATIVE-EXPRESSION '/'
EXPONENTIATION-EXPRESSION
12. EXPONENTIATION-EXPRESSION --> PERCENT-EXPRESSION |
EXPONENTIATION-EXPRESSION '^'
PERCENT-EXPRESSION
13. PERCENT-EXPRESSION --> UNARY-EXPRESSION |PERCENT-EXPRESSION '%'
14. UNARY-EXPRESSION --> UNARY-OPERATOR UNARY-EXPRESSION |UNION-EXPRESSION
15. UNION-EXPRESSION --> INTERSECTION-EXPRESSION |UNION-EXPRESSION ','
INTERSECTION-EXPRESSION
16. INTERSECTION-EXPRESSION --> REFERENCE-EXPRESSION |
INTERSECTION-EXPRESSION ' '
REFERENCE-EXPRESSION
17. REFERENCE-EXPRESSION --> '(' EXPRESSION ')' |
PRIMITIVE |
CONST-ARRAY |
REFERENCE |
R1C1-REFERENCE |
FUNCTION-CALL
18. REFERENCE --> REFERENCE-PREFIX AREA-REFERENCE |REFERENCE-PREFIX CELL
19. REFERENCE-PREFIX --> WORKBOOK | *** | e
20. WORKBOOK --> WORKBOOK-NAME ***
21. WORKBOOK-NAME --> '[workbook_path/filename.xls]' |'strsq'
22. SHEET --> SHEETNAME '!'
23. SHEETNAME --> IDENTIFIER | 'strsq'
24. AREA-REFERENCE --> RANGE | VECTOR | INTERSECTION |UNION
25. RANGE --> CELL ':' CELL | *** : CELL
26. VECTOR --> COLUMN-RANGE | ROW-RANGE
27. COLUMN-RANGE --> COLUMN ':' COLUMN
28. ROW-RANGE --> ROW ':' ROW
29. INTERSECTION --> AREA-REFERENCE ' ' AREA-REFERENCE
30. UNION --> AREA-REFERENCE ',' AREA-REFERENCE
31. CELL --> ABSOLUTE-REF-OPT COLUMN-OPT ABSOLUTE-REF-OPT ROW-OPT
32. ABSOLUTE-REF-OPT --> '$' | e
33. COLUMN-OPT --> COLUMN | e
34. COLUMN --> 'column-designator' //[A-Z] | [A-H][A-Z] | I[A-Z]
35. ROW-OPT --> ROW | e
36. ROW --> 'int' // [1...65536]
37. R1C1-REFERENCE --> R1C1-REF-ROW R1C1-REF-COL
38. R1C1-REF-COL --> 'C' R1C1-REF-OFFSET | 'C' | e
39. R1C1-REF-ROW --> 'R' R1C1-REF-OFFSET
40. R1C1-REF-OFFSET --> '[' '-' 'int' ']' | '[' '+' 'int'']' | '[' 'int' ']' | 'int' | e
41. CONST-ARRAY --> '{' ARRAY-ELEMENT-LIST-OPT '}'
42. ARRAY-ELEMENT-LIST-OPT --> ARRAY-ELEMENT-LIST | e
43. ARRAY-ELEMENT-LIST --> ARRAY-ELEMENT|
ARRAY-ELEMENT-LIST ','
ARRAY-ELEMENT |
ARRAY-ELEMENT-LIST ';'
ARRAY-ELEMENT
44. ARRAY-ELEMENT --> PRIMITIVE |
ERROR-ZERO |
ERROR-NOT-AVAILABLE |
ERROR-UNKNOWN-NAME |
ERROR-NULL |
ERROR-NUM |
ERROR-REF |
ERROR-VALUE |
ERROR-TOO-LONG |
e
45. FUNCTION-CALL --> FUNC-NAME-PREFIX-OPT IDENTIFIER '('
ARGUMENT-LIST-OPT ')'
46. FUNC-NAME-PREFIX-OPT --> FUNC-NAME-PREFIX | e
47. ARGUMENT-LIST-OPT --> ARGUMENT-LIST | e
48. ARGUMENT-LIST --> ARGUMENT | ARGUMENT-LIST ','ARGUMENT
49. ARGUMENT --> PRIMARY-EXPRESSION | e
50. PRIMITIVE --> UNARY-OPERATOR-OPT INTEGER |
UNARY-OPERATOR-OPT FLOAT | BOOLEAN | STRING | IDENTIFIER | ERROR
51. INTEGER --> 'int'
52. FLOAT --> 'float'
53. BOOLEAN --> 'TRUE' | 'FALSE'
54. STRING --> 'strsq' | 'strdq' // single and double quoted
55. UNARY-OPERATOR-OPT --> UNARY-OPERATOR | e
56. UNARY-OPERATOR --> '+' | '-'
57. ERROR --> '#DIV/0!' | '#N/A' | '#NAME?' |'#NULL!' | '#NUM!' | '#REF!' | '#VALUE!' | '#####'