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(range, [range2, …]) |
將一個或多個範圍的所有值扁平化為單列（與 Excel 不相容） |
FREQUENCY(data, classes) |
|
HSTACK(range1, [range2, …]) |
|
MDETERM(square_matrix) |
|
MINVERSE(square_matrix) |
|
MMULT(matrix1, matrix2) |
|
SUMPRODUCT(range1, [range2, …]) |
|
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(range) |
|
VSTACK(range1, [range2, …]) |
|
WRAPCOLS(range, wrap_count, [pad_with]) |
|
WRAPROWS(range, wrap_count, [pad_with]) |
資料庫¶
名稱與引數 |
說明或連結 |
|---|---|
DAVERAGE(database, field, criteria) |
|
DCOUNT(database, field, criteria) |
|
DCOUNTA(database, field, criteria) |
|
DGET(database, field, criteria) |
|
DMAX(database, field, criteria) |
|
DMIN(database, field, criteria) |
|
DPRODUCT(database, field, criteria) |
|
DSTDEV(database, field, criteria) |
|
DSTDEVP(database, field, criteria) |
|
DSUM(database, field, criteria) |
|
DVAR(database, field, criteria) |
|
DVARP(database, field, criteria) |
日期¶
名稱與引數 |
說明或連結 |
|---|---|
DATE(year, month, day) |
|
DATEDIF(start_date, end_date, unit) |
|
DATEVALUE(date_string) |
|
DAY(date) |
|
DAYS(end_date, start_date) |
|
DAYS360(start_date, end_date, [method]) |
|
EDATE(start_date, months) |
|
EOMONTH(start_date, months) |
|
HOUR(time) |
|
ISOWEEKNUM(date) |
|
MINUTE(time) |
|
MONTH(date) |
|
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) |
|
ISFORMULA(cell_reference) |
|
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) |
計算資料集的 Spearman 等級相關係數（與 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(range, [range2, …]) |
將一個或多個範圍的所有值扁平化為單列（與 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 不相容） |
ODOO.SURVEY(survey_id) |
Returns the results of an Odoo survey (not compatible with 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) |
計算資料集的 Spearman 等級相關係數（與 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.
