計算関数 — TableDI マニュアル

2 種類あります。行ごとの算術(calc の四則、ceil / floor / mod / power / abs)と、行をまたぐ条件付き集計(sumif の一族)です。条件付き集計は明細表から集計表を生やすときの核で、x の付く xsumif / xcountif は主キーが数式でない場合に増分計算を行い、自動化を発火させられます。

メモ

表記の約束:列は列コードで参照します(AA6 など)。関数名は大文字小文字を区別しません。テキストはダブルクォートで囲み、条件では and / or が使えます。例文の "In the result column, enter" は「新しい列の数式バーに書く」という意味です。以下の説明はアプリ内の関数ヘルプと同じ英語の文面です —— 画面に出るものとそのまま照合できるよう、訳さずに載せています。

calc

Basic addition, subtraction, multiplication, and division calculations

Formula Description

Performs basic addition (+), subtraction (-), multiplication (*), and division (/) calculations on columns/numbers.

Formula Syntax

In the result column, enter:
column/value + column/value
column/value - column/value
column/value * column/value
column/value / column/value

Parameters can use data columns, i.e., the CODE at the top of each column in the table data, e.g., AA1

add

Sum of numerical values

Formula Description

Sums numerical values horizontally across columns, supports multi-column calculations, and treats non-numeric data as "0" in calculations.
Adds numbers together.

Formula Syntax

Directly input the column CODE in the result column (i.e., the number at the top of each column in the table data, e.g., AA1)
Directly input the numbers to be calculated in the result column (e.g., 1+2+3)

abs

この関数の詳細説明はアプリに同梱されていません。数式エディターの関数ヘルプが正です。

ceil

Formula Description

Ceiling function, returns the smallest integer greater than or equal to a given number.

Formula Syntax

Input in result column: ceil(value / column number)

Parameters can use data columns, i.e., the CODE at the top of each column in the table data, e.g., AA1

floor

Formula Description

Floor function, returns the largest integer less than or equal to a specified number.

Formula Syntax

Input in result column: floor(value / column number)

Parameters can use data columns, i.e., the CODE at the top of each column in the table data, e.g., AA1

mod

Formula Description

Modulo function, which calculates the remainder after division of two numeric expressions.

Formula Syntax

In the result column, enter: mod(dividend / column number, divisor / column number)

Parameters can use data columns, i.e., the CODE at the top of each column in the table data, e.g., AA1

power

Formula Description

Exponentiation function, calculates a number raised to a power, i.e., a^b.

Formula Syntax

In the result column, enter: power(base / column number, exponent)

Parameters can use data columns, i.e., the CODE at the top of each column in the table data, e.g., AA1

stdevs

Calculate standard deviation

Formula Description

Calculates the standard deviation.

Standard deviation measures the spread of values around the mean.

Formula Syntax

In the result column, enter: stdevs(data column / data column1..n)

if

Returns conditional judgment

Formula Description

Evaluates a condition as "TRUE" or "FALSE" and returns corresponding content based on the logical result.

Compares values against expected values;
Performs logical evaluation based on logical expressions;
Returns different results based on the evaluation, executing conditional checks on values or formulas.

Formula Syntax

In the result column, enter: if(condition, value_if_true, value_if_false)

Given a condition, if a value meets the condition, it returns A; if it doesn't meet the condition, it returns B.

sumif

Filters data rows based on given conditions and sums the specified column

Formula Description

sumif filters data rows based on given conditions and sums the specified column. xsumif [when the serial number column is not a formula] enables incremental updates, replacing the sumif function, and can trigger automation.

Formula Syntax

In the result column, enter: sumif(column_to_sum, condition)

column_to_sum: The column to be summed, e.g., AH2;
condition: Filters data rows in the original table, e.g., AH4>90, AH3="phone" and AH3=AI1

countif

Formula Description

countif: Filters data rows based on given conditions and counts the specified column.

xcountif: When the serial number column is not a formula enables incremental updates, replacing the countif function, and can trigger automation.

Formula Syntax

In the result column, enter: countif(column_to_count, condition)

column_to_count: The column number to be counted, e.g., AA1, AD12;
condition: Filters the values in the count column, e.g., AA1>90, AH3=AI1 and AC1=AD3
Can perform calculations within the current table or across tables

distinctcountif

Formula Description

Filters data rows based on given conditions and counts the number of unique values in the specified column.

Formula Syntax

In the result column, enter: distinctcountif(column_number, condition)

column_number: The column to count unique values, e.g., AA1, AD12;
condition: Filters the values in the data column, e.g., AB2="phone" and AH3=AI1

maxif

Retrieves the maximum value from the specified column

Formula Description

Filters data rows based on given conditions and retrieves the maximum value from the specified column.

Formula Syntax

In the result column, enter: maxif(column_to_evaluate, condition)

column_to_evaluate: The column to evaluate, e.g., AA1, AD12;
condition: Filters the values in the data column, e.g., AB2="phone" and AH3=AI1

minif

Retrieves the minimum value from the specified column

Formula Description

Filters data rows based on given conditions and retrieves the minimum value from the specified column.

Formula Syntax

In the result column, enter: minif(column_to_evaluate, condition)

column_to_evaluate: The column to evaluate, e.g., AA1, AD12;
condition: Filters the values in the data column, e.g., AB2="phone" and AH3=AI1

average

Formula Description

Calculates the average of a set of values.

Formula Syntax

In the result column, enter: average(column_number)

column_number: The column to calculate the average, e.g., AA1, AD12

median

Formula Description

Calculates the median of a set of values.

Formula Syntax

In the result column, enter: median(column_number)

column_number: The column to calculate the median, e.g., AA1, AD12

lowerquartile

Calculates the lower quartile of a set of values

Formula Description

Calculates the lower quartile (25th percentile) of a set of values.

Formula Syntax

In the result column, enter: lowerquartile(column_number)

column_number: The column to calculate the lower quartile, e.g., AA1, AD12

upperquartile

Calculates the upper quartile of a set of values

Formula Description

Calculates the upper quartile (75th percentile) of a set of values.

Formula Syntax

In the result column, enter: upperquartile(column_number)

column_number: The column to calculate the upper quartile, e.g., AA1, AD12

quartileif

Formula Description

Filters data rows based on given conditions and calculates the X/4 quartile for the specified column.

Formula Syntax

quartileif(column_number, quartile, filter_condition)

quartile: Integer from 1 to 3, representing the 1/4, 1/2, and 3/4 quartiles respectively
condition: Data condition for filtering IDs. E.g., AB1 = AD3, AB1 = 13, AB1 < 100.9
All parameters can use data columns, i.e., the CODE at the top of each table data column, e.g., AA1

stdevsif

Formula Description

Filters data rows based on given conditions and calculates the estimated standard deviation for the specified column.

Standard deviation measures the spread of values around the mean.

Formula Syntax

In the result column, enter: stdevsif(column_number, condition)

All parameters can use data columns, i.e., the CODE at the top of each table data column, e.g., AA1
condition: Data condition for filtering IDs. E.g., AB1 = AD3, AB1 = 13, AB1 < 100.9