Typical function applications

In Cell Attribute>Style>Format, there is only a data display format for percentiles, and there is no data display format for thousandths, so if you want to display decimals as thousandths, you need to solve it through publicity.

The thousandth data is composed of a thousandth sign and decimal data multiplied by 1000, then the thousandth sign can be combined with the data expanded by 1000 times in the formula form, as follows:

Select Cell Attribute>Display>Formula, the formula is: =$$$*1000+"‰", $$$ means to get the value of the current cell.

When performing parameter filtering, it is hoped that only the data of the quarter of the current month will be displayed.

Select the formula in the filter interface and write the formula: =roundup(month(today())/3) to display only the quarter data of the current month.

Example: Input =roundup(month(today())/3) in the cell to return the result 4.

Note: The roundup() function means to round up.

When using the formula month() or day(), if the month or date is a single digit, only one digit is displayed. For example, January 9th, when the month is obtained, 1 is displayed instead of 01. When getting the date, it displays 9 instead of 09. If you need to get 01 or 09, how can this be achieved through a formula?

1. Formula realization

1) right(), concatenate(), MONTH() combined functions, as shown below:

Month formula: =right(concatenate('0',MONTH(today())),2)

Date formula: =right(concatenate('0',day(today())),2)

Formula understanding: Use concatenate to splice a 0 in front of the obtained month, for example, November, then display 011, if it is February, display 02, and then use the right method to obtain the right 2 digits, such as February If you intercept 2 digits, it will be 02. For example, in December, if you intercept the two digits on the right, it will be 12.

2) Format the string through format(), as shown below:

Month formula: = FORMAT(MONTH(TODAY()), "00")

Date formula: = FORMAT(day(TODAY()), "00")

Formula understanding: Return the format format of the object. object needs to be formatted object, which can be String, number, Object (commonly used are Date, Time).

2. Custom function implementation

If you think the formula written above is too troublesome, you can implement it through a custom function, the code is as follows:

package com.fr.function;
import com.fr.script.AbstractFunction;
public class Add0 extends AbstractFunction {      
    public Object run(Object[] args) {      
        String result = args[0].toString();      
        if(result.length() == 1)    
            result = '0' + result;    
        return result;   
    }
}

For the detailed definition steps of the custom function, please refer to Custom Functions.

A string of random strings can be generated through the uuid() function, but it is meaningless. In some cases, it is necessary to generate a string with [year, month, day, hour, minute, and second], and a number similar to china20170726144516 can be implemented directly using time formulas and string splicing functions.

Write the formula in the cell: = "china" + FORMAT(now(), "yyyMMddhhmmss") The result is: china20180101124516, that is, the string "china" is followed by the current [year, month, day, hour, minute, and second].

Note: Generally, when the number is not necessary, do not use all numbers, because the numbers may be wrong when importing and exporting to EXCEL.