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 不兼容的函数的公式会被其评估结果取代。
数组¶
Name and arguments |
说明或链接 |
|---|---|
ARRAY.CONSTRAIN(input_range, rows, columns) |
Returns a result array constrained to a specific width and height (not compatible with 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]) |
数据库¶
Name and arguments |
说明或链接 |
|---|---|
DAVERAGE（数据库、字段、标准） |
|
DCOUNT（数据库、字段、标准） |
|
DCOUNTA（数据库、字段、标准） |
|
DGET（数据库、字段、标准） |
|
DMAX（数据库、字段、标准） |
|
DMIN（数据库、字段、标准） |
|
DPRODUCT（数据库、字段、标准） |
|
DSTDEV（数据库、字段、标准） |
|
DSTDEVP（数据库、字段、标准） |
|
DSUM（数据库、字段、标准） |
|
DVAR（数据库、字段、标准） |
|
DVARP（数据库、字段、标准） |
日期¶
Name and arguments |
说明或链接 |
|---|---|
日期（年、月、日） |
|
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) |
Last day of the month following a date (not compatible with Excel) |
MONTH.START(date) |
First day of the month preceding a date (not compatible with Excel) |
NETWORKDAYS(start_date, end_date, [holidays]) |
|
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) |
|
NOW() |
|
QUARTER(date) |
Quarter of the year a specific date falls in (not compatible with Excel) |
QUARTER.END(date) |
Last day of the quarter of the year a specific date falls in (not compatible with Excel) |
QUARTER.START(date) |
First day of the quarter of the year a specific date falls in (not compatible with 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) |
Last day of the year a specific date falls in (not compatible with Excel) |
YEAR.START(date) |
First day of the year a specific date falls in (not compatible with Excel) |
YEARFRAC(start_date, end_date, [day_count_convention]) |
Exact number of years between two dates (not compatible with Excel) |
工程¶
Name and arguments |
说明或链接 |
|---|---|
DELTA(number1, [number2]) |
筛选¶
Name and arguments |
说明或链接 |
|---|---|
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) |
Returns the current value of a spreadsheet filter (not compatible with Excel) |
SORT(range, [sort_column, …], [is_ascending, …]) |
|
UNIQUE(range, [by_column], [exactly_once]) |
财务¶
Name and arguments |
说明或链接 |
|---|---|
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) |
Returns the account ids of a given group (not compatible with Excel) |
ODOO.BALANCE(account_codes, date_range, [offset], [company_id], [include_unposted]) |
Returns the total balance for the specified account(s) and period (not compatible with Excel) |
ODOO.BALANCE.TAG(account_tag_ids, [date_range], [offset], [company_id], [include_unposted]) |
Returns the balance of accounts for the specified tag(s) and period (not compatible with Excel) |
ODOO.CREDIT(account_codes, date_range, [offset], [company_id], [include_unposted]) |
Returns the total credit for the specified account(s) and period (not compatible with Excel) |
ODOO.CURRENCY.RATE(currency_from, currency_to, [date]) |
Takes two currency codes as arguments, and returns the exchange rate from the first currency to the second as float (not compatible with Excel) |
ODOO.DEBIT(account_codes, date_range, [offset], [company_id], [include_unposted]) |
Returns the total debit for the specified account(s) and period (not compatible with Excel) |
ODOO.FISCALYEAR.END(day, [company_id]) |
Returns the ending date of the fiscal year encompassing the provided date (not compatible with Excel) |
ODOO.FISCALYEAR.START(day, [company_id]) |
Returns the starting date of the fiscal year encompassing the provided date (not compatible with Excel) |
ODOO.PARTNER.BALANCE(partner_ids, [account_codes], [date_range], [offset], [company_id], [include_unposted]) |
Returns the partner balance for the specified account(s) and period (not compatible with Excel) |
ODOO.RESIDUAL([account_codes], [date_range], [offset], [company_id], [include_unposted]) |
Returns the residual amount for the specified account(s) and period (not compatible with 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]) |
信息¶
Name and arguments |
说明或链接 |
|---|---|
CELL(info_type, reference) |
|
ISBLANK(value) |
|
ISERR(value) |
|
ISERROR(value) |
|
ISFORMULA(cell_reference) |
|
ISLOGICAL(value) |
|
ISNA(value) |
|
ISNONTEXT(value) |
|
ISNUMBER(value) |
|
ISTEXT(value) |
|
NA() |
逻辑¶
Name and arguments |
说明或链接 |
|---|---|
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, …]) |
查找¶
Name and arguments |
说明或链接 |
|---|---|
ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet]) |
|
CHOOSE(index, [choice, …]) |
|
COLUMN([cell_reference]) |
|
COLUMNS(range) |
|
DROP(array, rows, [columns]) |
|
FORMULATEXT(cell_reference) |
|
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]) |
Creates a pivot table (not compatible with Excel) |
PIVOT.HEADER(pivot_id, [domain_field_name, …], [domain_value, …]) |
Returns the header of a pivot table (not compatible with Excel) |
PIVOT.VALUE(pivot_id, measure_name, [domain_field_name, …], [domain_value, …]) |
Returns the value from a pivot table (not compatible with Excel) |
ROW([cell_reference]) |
|
ROWS(range) |
|
TAKE(array, rows, [columns]) |
|
VLOOKUP(search_key, range, index, [is_sorted]) |
|
XLOOKUP(search_key, lookup_range, return_range, [if_not_found], [match_mode], [search_mode]) |
数学¶
Name and arguments |
说明或链接 |
|---|---|
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]) |
Returns the logarithm of a number for a given base (not compatible with 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]) |
操作员¶
Name and arguments |
说明或链接 |
|---|---|
ADD(value1, value2) |
Sum of two numbers (not compatible with Excel) |
CONCAT(value1, value2) |
|
DIVIDE(dividend, divisor) |
One number divided by another (not compatible with Excel) |
EQ(value1, value2) |
Equal (not compatible with Excel) |
GT(value1, value2) |
Strictly greater than (not compatible with Excel) |
GTE(value1, value2) |
Greater than or equal to (not compatible with Excel) |
LT(value1, value2) |
Less than (not compatible with Excel) |
LTE(value1, value2) |
Less than or equal to (not compatible with Excel) |
MINUS(value1, value2) |
Difference of two numbers (not compatible with Excel) |
MULTIPLY(factor1, factor2) |
Product of two numbers (not compatible with Excel) |
NE(value1, value2) |
Not equal (not compatible with Excel) |
POW(base, exponent) |
A number raised to a power (not compatible with Excel) |
UMINUS(value) |
A number with the sign reversed (not compatible with Excel) |
UNARY.PERCENT(percentage) |
Value interpreted as a percentage (not compatible with Excel) |
UPLUS(value) |
A specified number, unchanged (not compatible with Excel) |
解析器¶
Name and arguments |
说明或链接 |
|---|---|
CONVERT(number, from_unit, to_unit) |
统计¶
Name and arguments |
说明或链接 |
|---|---|
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, …]) |
Weighted average (not compatible with 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]) |
Fits points to exponential growth trend (not compatible with 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) |
Computes the Matthews correlation coefficient of a dataset (not compatible with 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]) |
Computes the coefficients of polynomial regression of the dataset (not compatible with Excel) |
POLYFIT.FORECAST(x, data_y, data_x, order, [intercept]) |
Predicts value by computing a polynomial regression of the dataset (not compatible with 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) |
Computes the Spearman rank correlation coefficient of a dataset (not compatible with 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]) |
Fits points to linear trend derived via least-squares (not compatible with Excel) |
VAR(value1, [value2, …]) |
|
VAR.P(value1, [value2, …]) |
|
VAR.S(value1, [value2, …]) |
|
VARA(value1, [value2, …]) |
|
VARP(value1, [value2, …]) |
|
VARPA(value1, [value2, …]) |
文本¶
Name and arguments |
说明或链接 |
|---|---|
ARRAYTOTEXT(array, [format]) |
|
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, …]) |
Concatenates elements of arrays with delimiter (not compatible with 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]) |
|
REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity]) |
|
REGEXTEST(text, pattern, [case_sensitivity]) |
|
RIGHT(text, [number_of_characters]) |
|
SEARCH(search_for, text_to_search, [starting_at]) |
|
SPLIT(text, delimiter, [split_by_each], [remove_empty_text]) |
Splits text by specific character delimiter(s) (not compatible with 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) |
网络¶
Name and arguments |
说明或链接 |
|---|---|
HYPERLINK(url, [link_label]) |
Odoo-specific functions¶
This section contains functions that interact directly with your Odoo database.
数组¶
Name and arguments |
说明或链接 |
|---|---|
ARRAY.CONSTRAIN(input_range, rows, columns) |
Returns a result array constrained to a specific width and height (not compatible with Excel) |
FLATTEN（范围, [范围2, …]） |
将一个或多个范围内的所有值平铺到单列中（与 Excel 不兼容） |
日期¶
Name and arguments |
说明或链接 |
|---|---|
MONTH.END(date) |
Last day of the month following a date (not compatible with Excel) |
MONTH.START(date) |
First day of the month preceding a date (not compatible with Excel) |
QUARTER(date) |
Quarter of the year a specific date falls in (not compatible with Excel) |
QUARTER.END(date) |
Last day of the quarter of the year a specific date falls in (not compatible with Excel) |
QUARTER.START(date) |
First day of the quarter of the year a specific date falls in (not compatible with Excel) |
YEAR.END(date) |
Last day of the year a specific date falls in (not compatible with Excel) |
YEAR.START(date) |
First day of the year a specific date falls in (not compatible with Excel) |
YEARFRAC(start_date, end_date, [day_count_convention]) |
Exact number of years between two dates (not compatible with Excel) |
财务¶
Name and arguments |
说明或链接 |
|---|---|
ODOO.ACCOUNT.GROUP(type) |
Returns the account ids of a given group (not compatible with Excel) |
ODOO.BALANCE(account_codes, date_range, [offset], [company_id], [include_unposted]) |
Returns the total balance for the specified account(s) and period (not compatible with Excel) |
ODOO.BALANCE.TAG(account_tag_ids, [date_range], [offset], [company_id], [include_unposted]) |
Returns the balance of accounts for the specified tag(s) and period (not compatible with Excel) |
ODOO.CREDIT(account_codes, date_range, [offset], [company_id], [include_unposted]) |
Returns the total credit for the specified account(s) and period (not compatible with Excel) |
ODOO.CURRENCY.RATE(currency_from, currency_to, [date]) |
Takes two currency codes as arguments, and returns the exchange rate from the first currency to the second as float (not compatible with Excel) |
ODOO.DEBIT(account_codes, date_range, [offset], [company_id], [include_unposted]) |
Returns the total debit for the specified account(s) and period (not compatible with Excel) |
ODOO.FISCALYEAR.START(day, [company_id]) |
Returns the starting date of the fiscal year encompassing the provided date (not compatible with Excel) |
ODOO.FISCALYEAR.END(day, [company_id]) |
Returns the ending date of the fiscal year encompassing the provided date (not compatible with Excel) |
ODOO.PARTNER.BALANCE(partner_ids, [account_codes], [date_range], [offset], [company_id], [include_unposted]) |
Returns the partner balance for the specified account(s) and period (not compatible with Excel) |
ODOO.RESIDUAL([account_codes], [date_range], [offset], [company_id], [include_unposted]) |
Returns the residual amount for the specified account(s) and period (not compatible with Excel) |
查找¶
Name and arguments |
说明或链接 |
|---|---|
PIVOT(pivot_id, [row_count], [include_total], [include_column_titles], [column_count]) |
Creates a pivot table (not compatible with Excel) |
PIVOT.HEADER(pivot_id, [domain_field_name, …], [domain_value, …]) |
Returns the header of a pivot table (not compatible with Excel) |
PIVOT.VALUE(pivot_id, measure_name, [domain_field_name, …], [domain_value, …]) |
Returns the value from a pivot table (not compatible with Excel) |
数学¶
Name and arguments |
说明或链接 |
|---|---|
COUNTUNIQUE(value1, [value2, …]) |
Counts number of unique values in a range (not compatible with Excel) |
COUNTUNIQUEIFS(range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …]) |
Counts number of unique values in a range, filtered by a set of criteria (not compatible with Excel) |
杂项¶
Name and arguments |
说明或链接 |
|---|---|
FORMAT.LARGE.NUMBER(value, [unit]) |
Applies a large number format (not compatible with Excel) |
ODOO.LIST(list_id, index, field_name) |
Returns the value from a list (not compatible with Excel) |
ODOO.LIST.HEADER(list_id, field_name) |
Returns the header of a list (not compatible with Excel) |
ODOO.SURVEY(survey_id) |
Returns the results of an Odoo survey (not compatible with Excel) |
操作员¶
Name and arguments |
说明或链接 |
|---|---|
ADD(value1, value2) |
Sum of two numbers (not compatible with Excel) |
DIVIDE(dividend, divisor) |
One number divided by another (not compatible with Excel) |
EQ(value1, value2) |
Equal (not compatible with Excel) |
GT(value1, value2) |
Strictly greater than (not compatible with Excel) |
GTE(value1, value2) |
Greater than or equal to (not compatible with Excel) |
LT(value1, value2) |
Less than (not compatible with Excel) |
LTE(value1, value2) |
Less than or equal to (not compatible with Excel) |
MINUS(value1, value2) |
Difference of two numbers (not compatible with Excel) |
MULTIPLY(factor1, factor2) |
Product of two numbers (not compatible with Excel) |
NE(value1, value2) |
Not equal (not compatible with Excel) |
POW(base, exponent) |
A number raised to a power (not compatible with Excel) |
UMINUS(value) |
A number with the sign reversed (not compatible with Excel) |
UNARY.PERCENT(percentage) |
Value interpreted as a percentage (not compatible with Excel) |
UPLUS(value) |
A specified number, unchanged (not compatible with Excel) |
统计¶
Name and arguments |
说明或链接 |
|---|---|
AVERAGE.WEIGHTED(values, weights, [additional_values, …], [additional_weights, …]) |
Weighted average (not compatible with Excel) |
GROWTH(known_data_y, [known_data_x], [new_data_x], [b]) |
Fits points to exponential growth trend (not compatible with Excel) |
MATTHEWS(data_x, data_y) |
Computes the Matthews correlation coefficient of a dataset (not compatible with Excel) |
POLYFIT.COEFFS(data_y, data_x, order, [intercept]) |
Computes the coefficients of polynomial regression of the dataset (not compatible with Excel) |
POLYFIT.FORECAST(x, data_y, data_x, order, [intercept]) |
Predicts value by computing a polynomial regression of the dataset (not compatible with Excel) |
SPEARMAN(data_y, data_x) |
Computes the Spearman rank correlation coefficient of a dataset (not compatible with Excel) |
TREND(known_data_y, [known_data_x], [new_data_x], [b]) |
Fits points to linear trend derived via least-squares (not compatible with Excel) |
文本¶
Name and arguments |
说明或链接 |
|---|---|
JOIN(delimiter, value_or_array1, [value_or_array2, …]) |
Concatenates elements of arrays with delimiter (not compatible with 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.
