Formulas and functions¶
Odoo Spreadsheet supports functions found in most spreadsheet solutions, allowing you to perform calculations, retrieve information, and manipulate data using formulas. In addition, using Odoo-specific functions allows you to work with live Odoo data from your database.
To help ensure data accuracy, Odoo Spreadsheet offers various ways to troubleshoot errors and inconsistencies in formulas and functions.
小技巧
Use F2 to view the formula behind an active cell, and, if desired, edit it directly in the
cell.
Available functions¶
This section presents the available functions by category. Odoo-specific functions are included both in the relevant category and in a dedicated Odoo category:
注解
在导出电子表格时，包含与 Excel 不兼容的函数的公式会被其评估结果取代。
数组¶
名称和参数 |
说明或链接 |
|---|---|
ARRAY.CONSTRAIN(input_range, rows, columns) |
返回限制为特定宽度和高度的结果数组（与 Excel 不兼容） |
CHOOSECOLS(array, col_num, [col_num2, …]) |
|
CHOOSEROWS(array, row_num, [row_num2, …]) |
|
EXPAND(array, rows, [columns], [pad_with]) |
|
FLATTEN（范围, [范围2, …]） |
将一个或多个范围内的所有值平铺到单列中（与 Excel 不兼容） |
频率（数据、类别） |
|
HSTACK（范围, [范围2, …]） |
|
MDETERM(square_matrix) |
|
MINVERSE(square_matrix) |
|
MMULT(matrix1, matrix2) |
|
SUMPRODUCT（范围, [范围2, …]） |
|
SUMX2MY2(array_x, array_y) |
|
SUMX2PY2(array_x, array_y) |
|
SUMXMY2(array_x, array_y) |
|
TOCOL(array, [ignore], [scan_by_column]) |
|
TOROW(array, [ignore], [scan_by_column]) |
|
TRANSPOSE（范围） |
|
VSTACK（范围, [范围2, …]） |
|
WRAPCOLS(range, wrap_count, [pad_with]) |
|
WRAPROWS(range, wrap_count, [pad_with]) |
数据库¶
名称和参数 |
说明或链接 |
|---|---|
DAVERAGE（数据库、字段、标准） |
|
DCOUNT（数据库、字段、标准） |
|
DCOUNTA（数据库、字段、标准） |
|
DGET（数据库、字段、标准） |
|
DMAX（数据库、字段、标准） |
|
DMIN（数据库、字段、标准） |
|
DPRODUCT（数据库、字段、标准） |
|
DSTDEV（数据库、字段、标准） |
|
DSTDEVP（数据库、字段、标准） |
|
DSUM（数据库、字段、标准） |
|
DVAR（数据库、字段、标准） |
|
DVARP（数据库、字段、标准） |
日期¶
名称和参数 |
说明或链接 |
|---|---|
日期（年、月、日） |
|
DATEDIF（开始_日期、结束_日期、单位） |
|
DATEVALUE（日期_字符串） |
|
天（日期） |
|
DAYS(end_date, start_date) |
|
DAYS360(start_date, end_date, [method]) |
|
EDATE(start_date, months) |
|
EOMONTH(start_date, months) |
|
小时（时间） |
|
ISOWEEKNUM（日期） |
|
分钟（时间） |
|
月份（日期） |
|
MONTH.END(date) |
日期所在月份之后一个月的最后一天（与 Excel 不兼容） |
MONTH.START(date) |
日期所在月份之前一个月的第一天（与 Excel 不兼容） |
NETWORKDAYS(start_date, end_date, [holidays]) |
|
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) |
|
NOW() |
|
QUARTER(date) |
特定日期所属的年度季度（与 Excel 不兼容） |
QUARTER.END(date) |
特定日期所在季度的最后一天（不兼容 Excel） |
QUARTER.START(date) |
特定日期所在季度的第一天（不兼容 Excel） |
SECOND(time) |
|
TIME(hour, minute, second) |
|
TIMEVALUE(time_string) |
|
TODAY() |
|
WEEKDAY(date, [type]) |
|
WEEKNUM(date, [type]) |
|
WORKDAY(start_date, num_days, [holidays]) |
|
WORKDAY.INTL(start_date, num_days, [weekend], [holidays]) |
|
YEAR(date) |
|
YEAR.END(date) |
特定日期所在年份的最後一天（與 Excel 不相容） |
YEAR.START(date) |
特定日期所在年份的第一天（與 Excel 不相容） |
YEARFRAC(start_date, end_date, [day_count_convention]) |
兩個日期之間的確切年數（與 Excel 不相容） |
工程¶
名称和参数 |
说明或链接 |
|---|---|
DELTA(number1, [number2]) |
筛选¶
名称和参数 |
说明或链接 |
|---|---|
FILTER(range, condition1, [condition2, …]) |
|
ODOO.FILTER.LABEL(filter_name) |
Returns the label of the current value of a spreadsheet filter (not compatible with Excel) |
ODOO.FILTER.VALUE(filter_name) |
返回試算表篩選器的當前值（與 Excel 不相容） |
SORT(range, [sort_column, …], [is_ascending, …]) |
|
UNIQUE(range, [by_column], [exactly_once]) |
财务¶
名称和参数 |
说明或链接 |
|---|---|
ACCRINTM(issue, maturity, rate, redemption, [day_count_convention]) |
|
AMORLINC(cost, purchase_date, first_period_end, salvage, period, rate, [day_count_convention]) |
|
COUPDAYBS(settlement, maturity, frequency, [day_count_convention]) |
|
COUPDAYS(settlement, maturity, frequency, [day_count_convention]) |
|
COUPDAYSNC(settlement, maturity, frequency, [day_count_convention]) |
|
COUPNCD(settlement, maturity, frequency, [day_count_convention]) |
|
COUPNUM(settlement, maturity, frequency, [day_count_convention]) |
|
COUPPCD(settlement, maturity, frequency, [day_count_convention]) |
|
CUMIPMT(rate, number_of_periods, present_value, first_period, last_period, [end_or_beginning]) |
|
CUMPRINC(rate, number_of_periods, present_value, first_period, last_period, [end_or_beginning]) |
|
DB(cost, salvage, life, period, [month]) |
|
DDB(cost, salvage, life, period, [factor]) |
|
DISC(settlement, maturity, price, redemption, [day_count_convention]) |
|
DOLLARDE(fractional_price, unit) |
|
DOLLARFR(decimal_price, unit) |
|
DURATION(settlement, maturity, rate, yield, frequency, [day_count_convention]) |
|
EFFECT(nominal_rate, periods_per_year) |
|
FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning]) |
|
FVSCHEDULE(principal, rate_schedule) |
|
INTRATE(settlement, maturity, investment, redemption, [day_count_convention]) |
|
IPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning]) |
|
IRR(cashflow_amounts, [rate_guess]) |
|
ISPMT(rate, period, number_of_periods, present_value) |
|
MDURATION(settlement, maturity, rate, yield, frequency, [day_count_convention]) |
|
MIRR(cashflow_amounts, financing_rate, reinvestment_return_rate) |
|
NOMINAL(effective_rate, periods_per_year) |
|
NPER(rate, payment_amount, present_value, [future_value], [end_or_beginning]) |
|
NPV(discount, cashflow1, [cashflow2, …]) |
|
ODOO.ACCOUNT.GROUP(type) |
返回给定组的会计科目 ID（与 Excel 不兼容） |
ODOO.BALANCE(account_codes, date_range, [offset], [company_id], [include_unposted]) |
返回指定科目和期间的总余额（不兼容Excel） |
ODOO.BALANCE.TAG(account_tag_ids, [date_range], [offset], [company_id], [include_unposted]) |
返回指定标签和期间的账户余额（与 Excel 不兼容） |
ODOO.CREDIT(account_codes, date_range, [offset], [company_id], [include_unposted]) |
返回指定账户和期间的信用总额（与 Excel 不兼容） |
ODOO.CURRENCY.RATE(currency_from, currency_to, [date]) |
接受两个货币代码作为参数，并以浮点数形式返回从第一个货币到第二个货币的汇率（与 Excel 不兼容） |
ODOO.DEBIT(account_codes, date_range, [offset], [company_id], [include_unposted]) |
返回指定账户和期间的借记总额（与 Excel 不兼容） |
ODOO.FISCALYEAR.END(day, [company_id]) |
返回包含所提供日期的会计年度的结束日期（与 Excel 不兼容） |
ODOO.FISCALYEAR.START(day, [company_id]) |
返回包含所提供日期的会计年度的开始日期（与 Excel 不兼容） |
ODOO.PARTNER.BALANCE(partner_ids, [account_codes], [date_range], [offset], [company_id], [include_unposted]) |
返回指定账户和期间的合作伙伴余额（与 Excel 不兼容） |
ODOO.RESIDUAL([account_codes], [date_range], [offset], [company_id], [include_unposted]) |
返回指定账户和期间的剩余金额（与 Excel 不兼容） |
PDURATION(rate, present_value, future_value) |
|
PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning]) |
|
PPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning]) |
|
PRICE(settlement, maturity, rate, yield, redemption, frequency, [day_count_convention]) |
|
PRICEDISC(settlement, maturity, discount, redemption, [day_count_convention]) |
|
PRICEMAT(settlement, maturity, issue, rate, yield, [day_count_convention]) |
|
PV(rate, number_of_periods, payment_amount, [future_value], [end_or_beginning]) |
|
RATE(number_of_periods, payment_per_period, present_value, [future_value], [end_or_beginning], [rate_guess]) |
|
RECEIVED(settlement, maturity, investment, discount, [day_count_convention]) |
|
RRI(number_of_periods, present_value, future_value) |
|
SLN(cost, salvage, life) |
|
SYD(cost, salvage, life, period) |
|
TBILLEQ(settlement, maturity, discount) |
|
TBILLPRICE(settlement, maturity, discount) |
|
TBILLYIELD(settlement, maturity, price) |
|
VDB(cost, salvage, life, start, end, [factor], [no_switch]) |
|
XIRR(cashflow_amounts, cashflow_dates, [rate_guess]) |
|
XNPV(discount, cashflow_amounts, cashflow_dates) |
|
YIELD(settlement, maturity, rate, price, redemption, frequency, [day_count_convention]) |
|
YIELDDISC(settlement, maturity, price, redemption, [day_count_convention]) |
|
YIELDMAT(settlement, maturity, issue, rate, price, [day_count_convention]) |
信息¶
名称和参数 |
说明或链接 |
|---|---|
CELL(info_type, reference) |
|
ISBLANK(value) |
|
ISERR(value) |
|
ISERROR(value) |
|
ISLOGICAL(value) |
|
ISNA(value) |
|
ISNONTEXT(value) |
|
ISNUMBER(value) |
|
ISTEXT(value) |
|
NA() |
逻辑¶
名称和参数 |
说明或链接 |
|---|---|
AND(logical_expression1, [logical_expression2, …]) |
|
FALSE() |
|
IF(logical_expression, value_if_true, [value_if_false]) |
|
IFERROR(value, [value_if_error]) |
|
IFNA(value, [value_if_error]) |
|
IFS(condition1, value1, [condition2, …], [value2, …]) |
|
NOT(logical_expression) |
|
OR(logical_expression1, [logical_expression2, …]) |
|
SWITCH(expression, case1, value1, [case2, …], [value2, …], [default]) |
|
TRUE() |
|
XOR(logical_expression1, [logical_expression2, …]) |
查找¶
名称和参数 |
说明或链接 |
|---|---|
ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet]) |
|
COLUMN([cell_reference]) |
|
COLUMNS(range) |
|
HLOOKUP(search_key, range, index, [is_sorted]) |
|
INDEX(reference, row, column) |
|
INDIRECT(reference, [use_a1_notation]) |
|
LOOKUP(search_key, search_array, [result_range]) |
|
MATCH(search_key, range, [search_type]) |
|
OFFSET(reference, rows, cols, [height], [width]) |
|
PIVOT(pivot_id, [row_count], [include_total], [include_column_titles], [column_count]) |
创建数据透视表（与 Excel 不兼容） |
PIVOT.HEADER(pivot_id, [domain_field_name, …], [domain_value, …]) |
返回数据透视表的标题（与 Excel 不兼容） |
PIVOT.VALUE(pivot_id, measure_name, [domain_field_name, …], [domain_value, …]) |
返回数据透视表中的值（与 Excel 不兼容） |
ROW([cell_reference]) |
|
ROWS(range) |
|
VLOOKUP(search_key, range, index, [is_sorted]) |
|
XLOOKUP(search_key, lookup_range, return_range, [if_not_found], [match_mode], [search_mode]) |
数学¶
名称和参数 |
说明或链接 |
|---|---|
ABS(value) |
|
ACOS(value) |
|
ACOSH(value) |
|
ACOT(value) |
|
ACOTH(value) |
|
ASIN(value) |
|
ASINH(value) |
|
ATAN(value) |
|
ATAN2(x, y) |
|
ATANH(value) |
|
CEILING(value, [factor]) |
|
CEILING.MATH(number, [significance], [mode]) |
|
CEILING.PRECISE(number, [significance]) |
|
COS(angle) |
|
COSH(value) |
|
COT(angle) |
|
COTH(value) |
|
COUNTBLANK(value1, [value2, …]) |
|
COUNTIF(range, criterion) |
|
COUNTIFS(criteria_range1, criterion1, [criteria_range2, …], [criterion2, …]) |
|
CSC(angle) |
|
CSCH(value) |
|
DECIMAL(value, base) |
|
DEGREES(angle) |
|
EXP(value) |
|
FLOOR(value, [factor]) |
|
FLOOR.MATH(number, [significance], [mode]) |
|
FLOOR.PRECISE(number, [significance]) |
|
INT(value) |
|
ISEVEN(value) |
|
ISO.CEILING(number, [significance]) |
|
ISODD(value) |
|
LN(value) |
|
LOG(value, [base]) |
返回给定底数的数字对数（与 Excel 不兼容） |
MOD(dividend, divisor) |
|
MUNIT(dimension) |
|
ODD(value) |
|
PI() |
|
POWER(base, exponent) |
|
PRODUCT(factor1, [factor2, …]) |
|
RAND() |
|
RANDARRAY([rows], [columns], [min], [max], [whole_number]) |
|
RANDBETWEEN(low, high) |
|
ROUND(value, [places]) |
|
ROUNDDOWN(value, [places]) |
|
ROUNDUP(value, [places]) |
|
SEC(angle) |
|
SECH(value) |
|
SEQUENCE(rows, [columns], [start], ][step]) |
|
SIN(angle) |
|
SINH(value) |
|
SQRT(value) |
|
SUBTOTAL(function_code, ref1, [ref2, …]) |
|
SUM(value1, [value2, …]) |
|
SUMIF(criteria_range, criterion, [sum_range]) |
|
SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …]) |
|
TAN(angle) |
|
TANH(value) |
|
TRUNC(value, [places]) |
操作员¶
名称和参数 |
说明或链接 |
|---|---|
ADD(value1, value2) |
兩個數字的總和（與 Excel 不相容） |
CONCAT(value1, value2) |
|
DIVIDE(dividend, divisor) |
一個數字除以另一個數字（與 Excel 不相容） |
EQ(value1, value2) |
等於（與 Excel 不相容） |
GT(value1, value2) |
嚴格大於（與 Excel 不相容） |
GTE(value1, value2) |
大於或等於（與 Excel 不相容） |
LT(value1, value2) |
小於（與 Excel 不相容） |
LTE(value1, value2) |
小於或等於（與 Excel 不相容） |
MINUS(value1, value2) |
兩個數字的差（與 Excel 不相容） |
MULTIPLY(factor1, factor2) |
兩個數字的乘積（與 Excel 不相容） |
NE(value1, value2) |
不等於（與 Excel 不相容） |
POW(base, exponent) |
一個數字的冪次（與 Excel 不相容） |
UMINUS(value) |
带有符号反转的数字（与Excel不兼容） |
UNARY.PERCENT(percentage) |
作为百分比解释的值（与Excel不兼容） |
UPLUS(value) |
指定的数字，保持不变（与Excel不兼容） |
解析器¶
名称和参数 |
说明或链接 |
|---|---|
CONVERT(number, from_unit, to_unit) |
统计¶
名称和参数 |
说明或链接 |
|---|---|
AVEDEV(value1, [value2, …]) |
|
AVERAGE(value1, [value2, …]) |
|
AVERAGEA(value1, [value2, …]) |
|
AVERAGEIF(criteria_range, criterion, [average_range]) |
|
AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …]) |
|
AVERAGE.WEIGHTED(values, weights, [additional_values, …], [additional_weights, …]) |
加权平均（与Excel不兼容） |
CORREL(data_y, data_x) |
|
COUNT(value1, [value2, …]) |
|
COUNTA(value1, [value2, …]) |
|
COVAR(data_y, data_x) |
|
COVARIANCE.P(data_y, data_x) |
|
COVARIANCE.S(data_y, data_x) |
|
FORECAST(x, data_y, data_x) |
|
GROWTH(known_data_y, [known_data_x], [new_data_x], [b]) |
将点拟合到指数增长趋势（与 Excel 不兼容） |
INTERCEPT(data_y, data_x) |
|
LARGE(data, n) |
|
LINEST(data_y, [data_x], [calculate_b], [verbose]) |
|
LOGEST(data_y, [data_x], [calculate_b], [verbose]) |
|
MATTHEWS(data_x, data_y) |
计算数据集的马修斯相关系数（与 Excel 不兼容） |
MAX(value1, [value2, …]) |
|
MAXA(value1, [value2, …]) |
|
MAXIFS(range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …]) |
|
MEDIAN(value1, [value2, …]) |
|
MIN(value1, [value2, …]) |
|
MINA(value1, [value2, …]) |
|
MINIFS(range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …]) |
|
PEARSON(data_y, data_x) |
|
PERCENTILE(data, percentile) |
|
PERCENTILE.EXC(data, percentile) |
|
PERCENTILE.INC(data, percentile) |
|
POLYFIT.COEFFS(data_y, data_x, order, [intercept]) |
计算数据集的多项式回归系数（与 Excel 不兼容） |
POLYFIT.FORECAST(x, data_y, data_x, order, [intercept]) |
通过计算数据集的多项式回归来预测值（与 Excel 不兼容） |
QUARTILE(data, quartile_number) |
|
QUARTILE.EXC(data, quartile_number) |
|
QUARTILE.INC(data, quartile_number) |
|
RANK(value, data, [is_ascending]) |
|
RSQ(data_y, data_x) |
|
SLOPE(data_y, data_x) |
|
SMALL(data, n) |
|
SPEARMAN(data_y, data_x) |
计算数据集的斯皮尔曼等级相关系数（与 Excel 不兼容） |
STDEV(value1, [value2, …]) |
|
STDEV.P(value1, [value2, …]) |
|
STDEV.S(value1, [value2, …]) |
|
STDEVA(value1, [value2, …]) |
|
STDEVP(value1, [value2, …]) |
|
STDEVPA(value1, [value2, …]) |
|
STEYX(data_y, data_x) |
|
TREND(known_data_y, [known_data_x], [new_data_x], [b]) |
通过最小二乘法拟合点到线性趋势（与 Excel 不兼容） |
VAR(value1, [value2, …]) |
|
VAR.P(value1, [value2, …]) |
|
VAR.S(value1, [value2, …]) |
|
VARA(value1, [value2, …]) |
|
VARP(value1, [value2, …]) |
|
VARPA(value1, [value2, …]) |
文本¶
名称和参数 |
说明或链接 |
|---|---|
CHAR(table_number) |
|
CLEAN(text) |
|
CONCATENATE(string1, [string2, …]) |
|
EXACT(string1, string2) |
|
FIND(search_for, text_to_search, [starting_at]) |
|
JOIN(delimiter, value_or_array1, [value_or_array2, …]) |
使用分隔符连接数组元素（与 Excel 不兼容） |
LEFT(text, [number_of_characters]) |
|
LEN(text) |
|
LOWER(text) |
|
MID(text, starting_at, extract_length) |
|
PROPER(text_to_capitalize) |
|
REPLACE(text, position, length, new_text) |
|
REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity]) |
|
RIGHT(text, [number_of_characters]) |
|
SEARCH(search_for, text_to_search, [starting_at]) |
|
SPLIT(text, delimiter, [split_by_each], [remove_empty_text]) |
按特定字符分隔符拆分文本（与 Excel 不兼容） |
SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number]) |
|
TEXT(number, format) |
|
TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) |
|
TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) |
|
TEXTJOIN(delimiter, ignore_empty, text1, [text2, …]) |
|
TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]) |
|
TRIM(text) |
|
UPPER(text) |
|
VALUE(text) |
网络¶
名称和参数 |
说明或链接 |
|---|---|
HYPERLINK(url, [link_label]) |
Odoo 专用函数¶
本节包含直接与 Odoo 数据库交互的函数。
数组¶
名称和参数 |
说明或链接 |
|---|---|
ARRAY.CONSTRAIN(input_range, rows, columns) |
返回限制为特定宽度和高度的结果数组（与 Excel 不兼容） |
FLATTEN（范围, [范围2, …]） |
将一个或多个范围内的所有值平铺到单列中（与 Excel 不兼容） |
日期¶
名称和参数 |
说明或链接 |
|---|---|
MONTH.END(date) |
日期所在月份之后一个月的最后一天（与 Excel 不兼容） |
MONTH.START(date) |
日期所在月份之前一个月的第一天（与 Excel 不兼容） |
QUARTER(date) |
特定日期所属的年度季度（与 Excel 不兼容） |
QUARTER.END(date) |
特定日期所在季度的最后一天（不兼容 Excel） |
QUARTER.START(date) |
特定日期所在季度的第一天（不兼容 Excel） |
YEAR.END(date) |
特定日期所在年份的最後一天（與 Excel 不相容） |
YEAR.START(date) |
特定日期所在年份的第一天（與 Excel 不相容） |
YEARFRAC(start_date, end_date, [day_count_convention]) |
兩個日期之間的確切年數（與 Excel 不相容） |
财务¶
名称和参数 |
说明或链接 |
|---|---|
ODOO.ACCOUNT.GROUP(type) |
返回给定组的会计科目 ID（与 Excel 不兼容） |
ODOO.BALANCE(account_codes, date_range, [offset], [company_id], [include_unposted]) |
返回指定科目和期间的总余额（不兼容Excel） |
ODOO.BALANCE.TAG(account_tag_ids, [date_range], [offset], [company_id], [include_unposted]) |
返回指定标签和期间的账户余额（与 Excel 不兼容） |
ODOO.CREDIT(account_codes, date_range, [offset], [company_id], [include_unposted]) |
返回指定账户和期间的信用总额（与 Excel 不兼容） |
ODOO.CURRENCY.RATE(currency_from, currency_to, [date]) |
接受两个货币代码作为参数，并以浮点数形式返回从第一个货币到第二个货币的汇率（与 Excel 不兼容） |
ODOO.DEBIT(account_codes, date_range, [offset], [company_id], [include_unposted]) |
返回指定账户和期间的借记总额（与 Excel 不兼容） |
ODOO.FISCALYEAR.START(day, [company_id]) |
返回包含所提供日期的会计年度的开始日期（与 Excel 不兼容） |
ODOO.FISCALYEAR.END(day, [company_id]) |
返回包含所提供日期的会计年度的结束日期（与 Excel 不兼容） |
ODOO.PARTNER.BALANCE(partner_ids, [account_codes], [date_range], [offset], [company_id], [include_unposted]) |
返回指定账户和期间的合作伙伴余额（与 Excel 不兼容） |
ODOO.RESIDUAL([account_codes], [date_range], [offset], [company_id], [include_unposted]) |
返回指定账户和期间的剩余金额（与 Excel 不兼容） |
查找¶
名称和参数 |
说明或链接 |
|---|---|
PIVOT(pivot_id, [row_count], [include_total], [include_column_titles], [column_count]) |
创建数据透视表（与 Excel 不兼容） |
PIVOT.HEADER(pivot_id, [domain_field_name, …], [domain_value, …]) |
返回数据透视表的标题（与 Excel 不兼容） |
PIVOT.VALUE(pivot_id, measure_name, [domain_field_name, …], [domain_value, …]) |
返回数据透视表中的值（与 Excel 不兼容） |
数学¶
名称和参数 |
说明或链接 |
|---|---|
COUNTUNIQUE(value1, [value2, …]) |
计算区域中唯一值的数量（与 Excel 不兼容） |
COUNTUNIQUEIFS(range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …]) |
计算范围内唯一值的数量，按一组条件筛选（与 Excel 不兼容） |
杂项¶
名称和参数 |
说明或链接 |
|---|---|
FORMAT.LARGE.NUMBER(value, [unit]) |
应用大数字格式（与 Excel 不相容） |
ODOO.LIST(list_id, index, field_name) |
从列表中返回值（与 Excel 不相容） |
ODOO.LIST.HEADER(list_id, field_name) |
返回列表的标题（与 Excel 不相容） |
操作员¶
名称和参数 |
说明或链接 |
|---|---|
ADD(value1, value2) |
兩個數字的總和（與 Excel 不相容） |
DIVIDE(dividend, divisor) |
一個數字除以另一個數字（與 Excel 不相容） |
EQ(value1, value2) |
等於（與 Excel 不相容） |
GT(value1, value2) |
嚴格大於（與 Excel 不相容） |
GTE(value1, value2) |
大於或等於（與 Excel 不相容） |
LT(value1, value2) |
小於（與 Excel 不相容） |
LTE(value1, value2) |
小於或等於（與 Excel 不相容） |
MINUS(value1, value2) |
兩個數字的差（與 Excel 不相容） |
MULTIPLY(factor1, factor2) |
兩個數字的乘積（與 Excel 不相容） |
NE(value1, value2) |
不等於（與 Excel 不相容） |
POW(base, exponent) |
一個數字的冪次（與 Excel 不相容） |
UMINUS(value) |
带有符号反转的数字（与Excel不兼容） |
UNARY.PERCENT(percentage) |
作为百分比解释的值（与Excel不兼容） |
UPLUS(value) |
指定的数字，保持不变（与Excel不兼容） |
统计¶
名称和参数 |
说明或链接 |
|---|---|
AVERAGE.WEIGHTED(values, weights, [additional_values, …], [additional_weights, …]) |
加权平均（与Excel不兼容） |
GROWTH(known_data_y, [known_data_x], [new_data_x], [b]) |
将点拟合到指数增长趋势（与 Excel 不兼容） |
MATTHEWS(data_x, data_y) |
计算数据集的马修斯相关系数（与 Excel 不兼容） |
POLYFIT.COEFFS(data_y, data_x, order, [intercept]) |
计算数据集的多项式回归系数（与 Excel 不兼容） |
POLYFIT.FORECAST(x, data_y, data_x, order, [intercept]) |
通过计算数据集的多项式回归来预测值（与 Excel 不兼容） |
SPEARMAN(data_y, data_x) |
计算数据集的斯皮尔曼等级相关系数（与 Excel 不兼容） |
TREND(known_data_y, [known_data_x], [new_data_x], [b]) |
通过最小二乘法拟合点到线性趋势（与 Excel 不兼容） |
文本¶
名称和参数 |
说明或链接 |
|---|---|
JOIN(delimiter, value_or_array1, [value_or_array2, …]) |
使用分隔符连接数组元素（与 Excel 不兼容） |
故障排除¶
Understand errors¶
When a formula is unable to return a value, the cell displays an error message that identifies the
type of error. For example, #N/A indicates that a value is not found when using a lookup function,
#NAME? indicates that a function name is misspelled or unrecognised, while #BAD_EXPR indicates
an issue with the arguments of a function.
Hovering over a cell with an error message reveals a card that provides more details about the error.
小技巧
Use the IFERROR function to provide an alternative result when an error occurs. For example, to avoid multiple
#N/Aerrors when using a VLOOKUP function, the following formula returnsNot Foundinstead of#N/A:=IFERROR(VLOOKUP(D1, A:B, 1, 0), "Not Found").In a large dataset, use the Irregularity map to visually highlight cells whose formulas are not consistent with the pattern established by surrounding cells.
Detect formula inconsistencies¶
Issues with formulas, such as accidentally overwriting a formula with a fixed value, breaking a cell reference, or not respecting the arguments of a function, can result in incorrect values being displayed. In a large dataset, such errors can be particularly difficult to identify manually.
Odoo Spreadsheet’s Irregularity map analyzes formulas for patterns and highlights anomalies visually: cells whose formulas follow the same pattern share the same color, while anomalies are shown in a different color.
To use the tool, click from the menu bar, then check the spreadsheet for inconsistencies. To turn off the irregularity map, click Irregularity map or Turn off on the banner below the toolbar.
Example
In the example (in which the formulas have been made visible by selecting from the menu bar), column D contains the amount of commission earned over the first and second quarters. This is calculated by adding the sales of the two quarters, shown in columns B and C, then multiplying it by the bonus rate in cell G1.
The irregularity map identifies the patterns in the formulas in column D. Cells that follow the pattern of the initial formula used in cell D2 have the same red color, while the following anomalies are highlighted separately:
Cell D4: This cell features a static value instead of the formula.
Cell D5: The formula in this cell does not follow the pattern of the initial formula.
Cells D8 and D9: Cell D8 is missing the absolute reference (
$) onG1; when the formula is dragged to cell D9, this creates a referencing error because cell G2 is empty. As the formulas in the two cells follow the same pattern, they share the same color.
